sqlplusで引数渡しを行い、sql内でさらにその引数の一部の使用法

質問

sqlplusで引数渡しを行い、sql内でさらにその引数の一部の使用法
Oracle:10.2.0.1.0
Windows:XP

例えば
sqlplus test/test @hoge.sql 2010 10M
にて実行しますが（年・月の意味の引数をわたしています）

hoge.sqlのCD1_xの数値1が引数の一部10を取得できるようにSQLに埋め込みたいと考えています。年と月で抽出するパターンが12カ月分ありまして、このCSV作成時に10Mというデータも埋め込みが必要なため、引数を単純に10にはしたくはないのです。

良い方法がありましたらお教えして頂きたくどうぞ宜しくお願いいたします。

＜TESTテーブルのレイアウト＞
CD1_1～CD1_6、CD2_1～CD2_6、・・・CD12_1～CD12_6の12カ月分の項目があります。

<バッチファイルにて引数を指定し実行する>
sqlplus test/test @hoge.sql %1 %2

＜呼び出すhoge.sqlの内容＞
spool c:\test\data_&1&2.csv
select
PRODUCT_ID||','||
'&1'||','||
'&2'||','||
CD1_1||','|| <-- ここはCD1_1ではなくてCDｘ_1的な。ｘは引数の一部にしたいのです。
CD1_2||','||
CD1_3||','||
CD1_4||','||
CD1_5||','||
CD1_6
from TEST;
spool off
exit

回答No.3

SaKaKashi

提示した例は引数を元にSQL文を作成して、作成したSQL文を実行しています。
あなたは、引数を直接展開して項目名にしようとしてませんか？

SUBSTRを使うには一度SQL文として実行する必要があるので、実行したいSQL文を生成するSQL文を実行しています。

引数がそのまま展開されるなら&1で直接書けるのですけど。例えば
SELECT &1 FROM &2
に引数A10とTABなら
SELECT A10 FROM TAB
で実行できますが、
SELECT SUBSTR('&1',1,1) FROM &2
は
SELECT SUBSTR('A10',1,1) FROM TAB
にしかなりません。SUBSTR('A',1,1)を
SELECT A FROM TAB
にしたいなら、
SELECT
'SELECT '||SUBSTR('&1',1,1)||' FROM '||&2
FROM DUAL
を実行すると
SELECT A FROM TAB
になるので、これを実行するとお望みのSQL文ができると思います。

お礼

SaKaKashi様

懇切丁寧に教えて下さり、
貴重なお時間を頂戴してしまいすみません。
本当にどうもありがとうございました。

上記の方法で実行したいと思います。

サンプルをいくつも提示頂きまして勉強になりました。
非常に感謝しております。

本当にどうもありがとうございました！

回答No.2

SaKaKashi

項目名を引数の一部で生成するって事ですか？
それをするには、SELECT文を文字列として作成して、spoolに書き込んで、
その書き込んだspoolを実行することです。

Unixでの例を提示します。これは指定の表の項目名をuser_tablesから参照して、
select文を生成してspoolに書いて、最後にできあがったspoolを実行してます。
参考にしてみてください。

#!/usr/bin/ksh
#
DBOWNER=gx
DBPASS=gx
DBSV=
SQLDIR=${HOME}/tmp
CSVDIR=${HOME}/tmp
SQLDIR=.
CSVDIR=.
#
if [ $# = 0 ]
then
echo usage : db2csv table-name [ condition ]
exit 9
fi
tabfrom=$1
if [ $# -ge 2 ]
then
tabwhere="where $2"
else
tabwhere=""
fi
#
echo "db2csv from ${tabfrom} by ${tabwhere}"
#
sql1=${SQLDIR}/desc${tabfrom}.sql
sql2=${CSVDIR}/csv${tabfrom}.csv
sqlplus -s ${DBOWNER}/${DBPASS}${DBSV} <<EOT
SET TRIMSPOOL ON
set pages 0
set hea off
set termout off
set feedback off
set echo off
set showmode off
set verify off
set trimspool on
spool ${sql1}
select 'SET HEA OFF' from dual;
select 'SET PAGES 0' from dual;
select 'SET TERMOUT OFF' from dual;
select 'SET TRIMSPOOL ON' from dual;
select 'SET FEEDBACK OFF' from dual;
select 'SET LINES 4096' from dual;
select 'SPOOL ${sql2}' from dual;
select 'ALTER SESSION SET NLS_DATE_FORMAT=''YYYYMMDDHH24MISS'';' from dual;
select 'SELECT '''||chr(34)||'''||' from dual;
select column_name||'||' from user_tab_columns
where table_name = upper('${tabfrom}') AND COLUMN_ID = 1 ;
select ''''||chr(34)||','||chr(34)||'''||'|| column_name||'||' from user_tab_columns
where table_name = upper('${tabfrom}') AND COLUMN_ID > 1 order by COLUMN_ID;
select ''''||chr(34)||'''' from dual;
select 'FROM ${tabfrom} ${tabwhere}' from dual;
select '/' from dual;
EOT
###
sqlplus -s ${DBOWNER}/${DBPASS}${DBSV} <<EOT
@${sql1}
EOT
###

お礼

sakakashiさん

サンプルまで頂いてしまい、すみません。
どうもありがとうございました。
最初のアドバイスにて以下のように記述すると、惜しいところまで行くのですが
concatした部分が文字列になってしまいまして編集した項目名がそのまま文字列になってcsvにはかれてしまいました。

concat(concat('CD',substr('&2',1,2)),'_1')
のこの部分を何とか変数名として認識させる方法はご存じでしょうか。

度々の質問とても恐縮なのですがアドバイス頂けたら幸いです。
宜しくお願いいたします。

＜変数名が文字列と認識されてしまうSQL＞
select
PRODUCT_ID||','||
'&1'||','||
'&2'||','||
concat(concat('CD',substr('&2',1,2)),'_1')||','||
concat(concat('CD',substr('&2',1,2)),'_2')||','||
concat(concat('CD',substr('&2',1,2)),'_3')||','||
concat(concat('CD',substr('&2',1,2)),'_4')||','||
concat(concat('CD',substr('&2',1,2)),'_5')||','||
concat(concat('CD',substr('&2',1,2)),'_6')
from TEST;
spool off
exit

回答No.1

SaKaKashi

SUBSTR('&1',3,2)

お礼

早速ご回答ありがとうございます。

CDｘ_1等の項目にｘ部分へSUBSTRを連結し埋め込むにはどうやればよいのでしょうか。
度々の質問になりすみません。
宜しくお願いいたします。