SQL*Plusでデフォルト値設定ありのパラメータを入力させる

月替わり後に前月のデータを調べるためのスクリプトが欲しい、でも必ず前月ってわけじゃなくてすっごい前のデータを調べることもあるんだよ。…という要望があって、前月をデフォルト値として年月をyyyy-mm形式で入力させるスクリプトを作成してみました。

SET TERMOUT OFF
COLUMN slected_month NEW_VALUE v_default_month
SELECT TO_CHAR(ADD_MONTHS(SYSDATE,-1),'yyyy-mm') selected_month
  FROM DUAL;
SET TERMOUT ON
ACCEPT param_month DATE FORMAT 'yyyy-mm' DEFAULT &v_default_month -
PROMPT "調査対象月をyyyy-mm形式で入力してください[&v_default_month]:"
-- 検索
SELECT COUNT(*)
  FROM gl_je_headers
 WHERE TO_CHAR(creation_date,'yyyy-mm') = '&param_month';

変数名にセンスが感じられないのは私の仕様です。そこは見なかったことにしてコマンドのメモ。

コマンド解説

SET TERMOUT OFF

出力結果を画面に表示しないためのコマンドです。前月を検索してる次のSQLが表示されるとかっこ悪いので。最後のSQLの結果は表示してほしいので、前月の検索が終わった後でONに戻しています。

COLUMN slected_month NEW_VALUE v_default_month

slected_monthという列名(もしくは列別名)が検索されたら、v_default_monthという変数にその値をセットしてという意味になるようです。

SELECT TO_CHAR(ADD_MONTHS(SYSDATE,-1),'yyyy-mm') selected_month
  FROM DUAL;

ADD_MONTHS関数で１ヶ月前を検索し、yyyy-mmの形式(例：2009-01)で表示します。TERMOUTがOFFなので表示しないですけど。列別名selected_monthを指定することで、前述のCOLUMN関数により変数v_default_monthに検索結果がセットされます。

ACCEPT param_month DATE FORMAT 'yyyy-mm' DEFAULT &v_default_month -
PROMPT "調査対象月をyyyy-mm形式で入力してください[&v_default_month]:"

• ACCEPT
  SQL*Plus上で入力を受け付けるコマンド。
• DATE
  日付型のみ受け付け。
• FORMAT 'yyyy-mm'
  入力形式の指定です。yyyy-mm形式じゃないとダメだよってところまで指定しているので例えばaとか2009-13とか2009-02-04とかを入力してもエラー表示されて再度入力を促されます。
• DEFAULT
  デフォルト値指定。v_default_month、つまり前月がセットされているので、入力者が何も入力しなかった場合は前月を入力するのと同じ意味になります。
• -
  SQL*Plusコマンドはまだ続いてます、という意味。
• PROMPT
  SQL*Plus上に表示する内容。「調査対象月をyyyy-mm形式で入力してください[2009-01]:」のように表示されます。

ここで受け付けた入力内容は変数param_monthにセットされます。

-- 検索

コメントです。

SELECT COUNT(*)
  FROM gl_je_headers
 WHERE TO_CHAR(creation_date,'yyyy-mm') = '&param_month';

SELECT文です。&は置換変数を表し、param_monthは前で入力値がセットされているので

SELECT COUNT(*)
  FROM gl_je_headers
 WHERE TO_CHAR(creation_date,'yyyy-mm') = '2009-01';

と同じ意味になります。注）このSQL文はパフォーマンス的によろしくないSQLなので真似してはいけません。
SQL*Plusの正確なコマンドと使用方法はSQL*Plusユーザーズガイドを、SQL関数はSQLリファレンスをご参照ください。

ここまでSQL*Plusコマンドで作りこんだ理由

別にスクリプトの種類は指定されていなかったので最初はシェル(k-shell)で入力部分を作って検索だけSQLで、と考えていたのですが

• パスワード入力時に入力内容を非表示にしたい
• 前月を簡単に求めたい
• SQL*Plusを立ち上げるときにsqlplus username/password @file_name みたいな立ち上げ方はしたくない*1

これらをシェルで解決する方法がわからなかったのでSQL*Plusコマンドを多用しました。要するに私が知ってるのがSQL*Plusコマンドだったから、に尽きるわけでして。いい加減他も勉強しよう…

*1:この立ち上げ方をやってしまうと、実行中に他の人がpsコマンドでプロセス検索すると
sqlplus scott/tiger @sql_file.sql
のように表示されます。つまりパスワードが他の人に知られてしまう可能性がありセキュリティ的に危険。