［2022/08/07］誤字訂正、VALIABLE→VARIABLE
［2016/10/17］『➍ SQLのSELECT結果が複数個でも複数行でも取得する方法』を追記
［2016/10/15］本記事公開
こんにちは。kei（@boot_kt）です。
- batからSQL*Plusを呼び出す方法
- SQL*Plusを呼び出す際、PL/SQLを書いたSQLファイルを使う
- SQLファイル（PL/SQL）でSELECTしたものをbatで取得
- （オマケ）batでの日付の加減算をOracleにやらせよう
- Windows の bat
- Oracle（sqlplus、SQL、PL/SQL）
- Windowsのコマンドプロンプトを使える/li>
- Batファイルの知識
- Oracleの知識
- PL/SQLの知識
- SQLの知識
と言っても、難しい内容は全く出てきません。
上記の知識が無くても大丈夫です。
Contents
➊ batからSQL*Plusを呼び出す方法
まず、batとか関係なしにコマンドプロンプトでSQL*Plusを使う普通の方法
- コマンドプロンプトを立ち上げます。
- 以下のように打ちます。
C:\> sqlplus scott/tiger@dbname
基本的にはこれだけです。
この後、SQL*Plusとして動作していますので、SELECT文等のSQLを打ち込んだりします。
sqlplusコマンド実行と同時にSQL文を即実行したい時
- SQL文を書いたファイルを準備します。
ファイル名は何でもいいですが、ここではsql-statement.sqlとしておきます。 - コマンドプロンプトを立ち上げます。
- sql-statement.sqlのある位置までcdします。
C:\> cd /d c:\hoge\fuga
- 以下のように打ちます。
C:\> sqlplus -S scott/tiger@dbname @sql-statement.sql
- sql-statement.sqlに書かれたSQL文が実行されます。
sqlplusコマンドの説明
sqlplusコマンドの事を少しだけ解説します。
| 命令 | 内容 |
|---|---|
sqlplus |
コマンド |
-S |
サイレントモード メッセージとか情報とかを出力させないオプション |
scott/tiger@dbname |
scott：ログインIDを書いてください tiger：ログインパスワードを書いてください @dbname：tnsnames.oraに書いてあるDB情報の名前です
scott/tigerはOracleの初期ユーザーです。興味ある方はググってみてください。
しかしこのscott/tigerをそのまま使うことは無いでしょう。 IDとパスワードを表現する時に使うことがあります。 |
@sql-statement.sql |
ファイル名です。 このファイルの中にSQL文やPL/SQL文を書いてあります。 sqlplusコマンドが実行してくれます。 @ をファイル名の前に付けておきます。 |
もっと詳しく知りたい方は別途ググるか、以下のようなSQLの本をご参照ください。
コマンドプロンプトじゃなくて、batファイルからsqlplusコマンドを呼び出すには？
説明するまでも無いんですけど、sqlplusコマンドをbatファイルに書き込むだけです。
@echo off cd /d %~dp0 sqlplus -S scott/tiger@dbname @sql-statement.sql
SELECT * FROM HOGEHOGE_TABLE ;
注意するとすれば、sql-statement.sqlファイルをbatファイルと同じ場所に置きましょう。
➋ SQL*Plusを呼び出す際、PL/SQLを書いたSQLファイルを使う
SQL文であってもPL/SQL文であっても、前項と同じ書き方でいいです。
batからsqlplusコマンドを使い、実行するsqlファイルを指定するだけです。
@echo off cd /d %~dp0 sqlplus -S scott/tiger@dbname @plsql-statement.sql
set feedback off VARIABLE vClientName varchar2(100); DECLARE BEGIN SELECT client_name into:vClientName FROM CLIENT_LIST_TBL; END; / EXIT;
➌ SQLファイル（PL/SQL）でSELECTしたものをbatで取得
batでsqlplusを使ってSQLを実行するのはいいんですが、取得結果をbatの処理で使いたい事がありますよね。
・・・・と言うことは、sqlplusでSELECT文を実行して、そのSELECT結果をbatで受け取らないといけないのです。
いやいやいやいや・・・・、sqlplusに戻り値なんて仕組みないです！
って事で、これがなかなか難しい。
batはなかなか非力な言語ですので、SQLの実行結果をすんなり取得する方法は無いですね。
で、どうすればいいかと言うと・・・・以下を見てください。
@echo off
cd /d %~dp0
for /F "DELIMS=" %%a in ('sqlplus -S scott/tiger@dbname @plsql-statement.sql') do (
echo %%a
)
set feedback off VARIABLE vClientName varchar2(100); DECLARE BEGIN SELECT client_name into:vClientName FROM CLIENT_LIST_TBL; END; / PRINT vClientName; EXIT;
注目ポイント
batもPL/SQLも前出のものと基本的には同じなんですけど、14行目に PRINT 命令が追加されていますね。
そしてbatの方は for 命令の中から sqlplus を呼び出しています。
この PRINT 命令がこの記事のキモです！
- PRINT 命令は vClientName の値を画面に表示します。：【plsql-statement.sql】
- for命令の %%a に画面に表示された値（この場合vClientNameの値）がセットされます。【sqlcall.bat】
という内容で、BatにSQLの実行結果が入ります。
制限事項
但しっっっっっっっっっ！！！！
- 渡せる値は１つだけ
- 渡せる値は文字列のみ
という制限があります。
それを踏まえた上で使ってください。
後日実験してみます。
2016/10/17変更
SQLのSELECT結果が2個以上でも複数行でも取得できます。
次項【➍ SQLのSELECT結果が複数個でも複数行でも取得する方法】を参照してください。
解説
batのfor命令について
| 命令 | 内容 |
|---|---|
/F |
for命令のオプション 詳しくは説明しませんが、これを付与する事によりテキストデータの解析ができます。 『テキストデータ』と言うのは、in の後にあるsqlplusコマンドの実行結果の事です。 今回提示しているfor命令ではテキストデータが分かりにくいかもしれません。 for /F "DELIMS=," %%i in (hogehoge.txt) do ( echo %%i ) abc,def,ghi,jklmno,pqrstuv,wxyz AB,CDE,,FGH,IJKLMNOP,QRSTUV,WXYZ 1,2,3,4,5,6,7,8,9,0 あ,いう,えおか,きくけ,こ さしすせ,そ た,ちつてと,なにぬ,ね,の in の後に注目してください。
ただし上記を実行させると1個目のカンマの前のワードだけが表示されます。 2ワード以降も表示させるには、"TOKENS=" オプションや %%j、%%k を使う必要があります。 本記事ではそこまで言及しません。 興味ある方は別途調べてください。 pz-linkcard: Incorrect URL specification.(url=)
書籍が良い方はこちら↓をどうぞ |
"DELIMS=" |
/F オプションがもつ解析方法の指定です。 他にも "SKIP=" とか "TOKENS=" とかあります。詳しくは別途お調べください。 DELIMSは区切りの文字を指定します。 :（コロン）を区切り文字にしたければ"DELIMS=:"、,（カンマ）を区切り文字にしたければ "DELIMS=," と書きます。 今回は区切り文字を指定していないため、テキストデータの1行をそのまま取得することになります。 ん？？？ YESです（笑） DELIMS は書かなくてもいいです。 じゃぁ、「なんで書いてんだよっ！？」と思いますよね。 書いた理由は２つあります。
|
%%a in ('sqlplus -S scott/tiger@dbname @plsql-statement.sql') |
('sqlplus -S scott/tiger@dbname @plsql-statement.sql') は前出のhogehoge.txtがこのコマンドに置き換わったと思ってください。 ('sqlplus -S scott/tiger@dbname @plsql-statement.sql') でSELECTを実行してテキストを作っていると考えてみてください。 in は ('sqlplus -S scott/tiger@dbname @plsql-statement.sql') から取れたテキストを %%a に渡しています。 ('sqlplus -S scott/tiger@dbname @plsql-statement.sql')で作られたテキストファイルが %%a にセットされます。 |
➍ SQLのSELECT結果が複数個でも複数行でも取得する方法
あれこれと説明書くのは面倒なのでサンプルコードを示します。
別に難しくないので読んでみてください。
取得したいレコード数は１行＆取得したいフィールドは２個以上
@echo off
cd /d
REM ***************************************************************
REM ORACLEへの接続情報
REM ***************************************************************
set DB_ID=scott
set DB_PASS=tiger
set DB_NAME=dbname
REM ***************************************************************
REM CLIENT_NOには欲しいクライアントの番号を入れる
REM ***************************************************************
set CLIENT_NO=1
for /F "TOKENS=1,2 DELIMS=," %%A in ('sqlplus -S %DB_ID%/%DB_PASS%@%DB_NAME% @plsql-statement.sql %CLIENT_NO%') do (
echo %%A
echo %%B
)
set feedback off VARIABLE vClientInfo varchar2(100); DECLARE BEGIN SELECT client_name || ',' || client_addr into:vClientInfo FROM CLIENT_LIST_TBL; WHERE client_no = &1 ; END; / PRINT :vClientInfo; EXIT;
取得したいレコード数は２行以上＆取得したいフィールドは２個以上
@echo off
cd /d
REM ***************************************************************
REM ORACLEへの接続情報
REM ***************************************************************
set DB_ID=scott
set DB_PASS=tiger
set DB_NAME=dbname
for /F "TOKENS=1,2 DELIMS=," %%A in ('sqlplus -S %scott%/%tiger%@%dbname% @plsql-statement.sql') do (
echo %%A
echo %%B
)
set feedback off set serveroutput on DECLARE BEGIN FOR vClientInfo IN ( SELECT client_name || ',' || client_addr cli_info FROM CLIENT_LIST_TBL ) LOOP DBMS_OUTPUT.PUT_LINE(vClientInfo.cli_info); END LOOP; END; / EXIT;
簡単な解説
BatとSQLが読めれば全然難しく無いと思います。
ポイントはこれだけです。
- 【Bat】TOKENS使用
- 【Bat】DELIMS使用
- 【Bat】%%A、%%Bを使用
TOKENS=1,2,3とすれば%%A、%%B、%%Cが使用可能
TOKENS=1,2,3,4とすれば%%A、%%B、%%C、%%Dが使用可能
sqlplusから戻ってくる値を、DELIMSで指定した ,（カンマ）で区切ります。
%%Aから一つずつずれて%%B、%%C、%%Dという環境変数が自動で作られ、区切られた値は前から順番に入れられます。
- 【SQL】項目と項目をカンマでつなぐ
- 【SQL】into使用
- 【SQL】FOR使用
- 【SQL】DBMS_OUTPUT.PUT_LINE使用
➎ 使い方のご提案：batでの日付の加減算をOracleにやらせよう
今回説明した方法でデータベース（Oracle）からSELECTした結果をbatで取得する事ができます。
そこで、応用ってワケではないですが、こういう使い方はどうでしょうか？
batでの日付・時間の計算をOracleにやらせる！！！
batで日付・時間の加減算をやろうと思うとめちゃくちゃ面倒ですし、長くなります。
ざっと挙げると以下のような事を考えなければいけません。
- 月末は30日なのか31日なのか
- 2月は28日までだ
- 閏年の計算が必要
- 年をまたぐ可能性があるので、年の計算も必要
- 日付の加算と減算の両方考えないとだめ
・・・結構面倒ですよね。
こんなものを実装しろと言われたら『嫌ですっ！！！』『無理ですっ！』『そんなの作っても工数の無駄ですよ！』と断ります。
先程も書きましたが、batはかなり非力な言語です。（まぁ、PowerShell使えばこんな苦労しなくていいんですけど・・・・）
やりたい事は大体はできますので必要十分な命令を持ってはいますが、それでもやっぱり書きづらいし、デバッグしづらい、読みづらいです。
まぁ、1回ちゃんとした日付・時刻計算処理を書けば使いまわせるでしょうけど、結構長くなると思います。
ただでさえbatは分かりづらいし、遅延環境変数とかもあって面倒な仕組みもあるので、あまり長いbatを書くのはよろしくないと思います。
で！
SQLなら日付・時刻計算はそんな難しくないです。
以下のようなサンプルを書いておきますので、もし使ってみたい方がいらっしゃれば使ってみてください。
@echo off
cd /d %~dp0
REM ***************************************************************
REM PAST_DATEには何日前の日付が欲しいかを入れる
REM ***************************************************************
set PAST_DATE=4
for /F "DELIMS=" %%a in ('sqlplus -S scott/tiger@dbname @plsql-calcdate.sql %PAST_DATE%') do (
echo %%a
)
set feedback off VARIABLE vPastDay varchar2(100); DECLARE BEGIN SELECT TO_CHAR(SYSDATE - &1, 'yyyy/mm/dd') into:vPastDay FROM DUAL; END; / PRINT vPastDay; EXIT;
PL/SQLの形になっていますが、PL/SQLの事を知らなくても普通のSQLが書ければ大丈夫ですよね。
また、日付や時刻の計算は必要な式に書き直してくださいね。
上記はあくまでもサンプルです。
っていうか、、、、、
『PowerShell使えよ！』っていうツッコミは無しでお願いします(笑)
僕もPowerShellでいいじゃんって思ってますんで・・・勘弁してください・・・・・・
参考URL
『SQL*PlusのSELECT結果をBatが取得する』件についてはどこで見つけたか忘れてしまいました。
ブラウザを閉じたら履歴を消す設定にしているPCを使ったため、履歴をたどることもできません。
再度検索しまくったのですが、どうしても見つかりません。
まるっと全コード載っていたか、何かを見て自分で思いついたのかも覚えていません。
もしどなたか『もしかして、お前が見たのはこの記事じゃね？』という心当たりがあれば教えて頂けますとありがたいです。
参考ついでに書いておきますが、SQL*PlusからBatへ値を渡す手法はググると結構でてきます。
しかし、その殆どが『EXIT :vHoge;』でSQLファイルを終了し、BatではERRORLEVELで取得する方法ばかりでした。
ちょっとERRORLEVELで受け取るのはなんか気持ち悪いなと思って私はその手法はとっていません。
興味のある方は『sqlplus bat 取得』みたいなワードで検索してみてください。
複数項目・複数行取得についてはこちらをヒントにしました。
まとめ
今回は１つの記事の中に２つの事を詰め込みました。
- SQL*PlusのSELECT結果をBatが取得する
- Batの日付計算をデータベースにやらせてしまう
本来であれば『１記事につき、１テーマ』が原則なんだろうと思うんですけど、どうしても切る事ができませんでした。
『SQL*PlusのSELECT結果をBatが取得する』方法を探している方にとって、ついでに『Batの日付計算をデータベースにやらせてしまう』方法を知ってもらうと応用が効いてより幸せになれるかなと考えたからです。
また、『Batの日付計算をデータベースにやらせてしまう』方法を探している方その手順をお伝えするにあたり、『SQL*PlusのSELECT結果をBatが取得する』方法をまず知っていただく必要があります。
ですので、２つのテーマを詰め込みましｔ．
実際の所この２つの情報を欲している人がどの程度いらっしゃるか分かりませんが、私自身が実際に困って解決した内容です。
同じような問題を持った方が一人でもいらっしゃって、この記事がお役に立てれば嬉しい限りです。
参考書籍
