[Windows]SQL*PlusのSELECT結果をBatが取得する方法

sqlplus-bat-catch2


更新履歴
[2016/10/17]『➍ SQLのSELECT結果が複数個でも複数行でも取得する方法』を追記
[2016/10/15]本記事公開

こんにちは。kei(@boot_kt)です。

この記事の内容

  1. batからSQL*Plusを呼び出す方法
  2. SQL*Plusを呼び出す際、PL/SQLを書いたSQLファイルを使う
  3. SQLファイル(PL/SQL)でSELECTしたものをbatで取得
  4. (オマケ)batでの日付の加減算をOracleにやらせよう
この2つを使います

  • Windows の bat
  • Oracle(sqlplus、SQL、PL/SQL)
この記事を読むためにあると望ましい知識

  • Windowsのコマンドプロンプトを使える/li>
  • Batファイルの知識
  • Oracleの知識
  • PL/SQLの知識
  • SQLの知識

と言っても、難しい内容は全く出てきません。
上記の知識が無くても大丈夫です。

 




➊ batからSQL*Plusを呼び出す方法

まず、batとか関係なしにコマンドプロンプトでSQL*Plusを使う普通の方法

  1. コマンドプロンプトを立ち上げます。
  2. 以下のように打ちます。
    C:\> sqlplus scott/tiger@dbname

基本的にはこれだけです。

この後、SQL*Plusとして動作していますので、SELECT文等のSQLを打ち込んだりします。

sqlplusコマンド実行と同時にSQL文を即実行したい時

  1. SQL文を書いたファイルを準備します。
    ファイル名は何でもいいですが、ここではsql-statement.sqlとしておきます。
  2. コマンドプロンプトを立ち上げます。
  3. sql-statement.sqlのある位置までcdします。
    C:\> cd /d c:\hoge\fuga
  4. 以下のように打ちます。
    C:\> sqlplus -S scott/tiger@dbname @sql-statement.sql
  5. 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

VALIABLE 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

VALIABLE vClientName  varchar2(100);

DECLARE

BEGIN
	SELECT
		client_name  into:vClientName
	FROM
		CLIENT_LIST_TBL;
END;
/
PRINT vClientName;
EXIT;

注目ポイント

14行目に注目してください。

batもPL/SQLも前出のものと基本的には同じなんですけど、14行目に PRINT 命令が追加されていますね。
そしてbatの方は for 命令の中から sqlplus を呼び出しています。

この PRINT 命令がこの記事のキモです!

  1. PRINT 命令は vClientName の値を画面に表示します。:【plsql-statement.sql】
  2. for命令の %%a に画面に表示された値(この場合vClientNameの値)がセットされます。【sqlcall.bat】

という内容で、BatにSQLの実行結果が入ります。

 

制限事項

但しっっっっっっっっっ!!!!

  • 渡せる値は1つだけ
  • 渡せる値は文字列のみ

という制限があります。
それを踏まえた上で使ってください。

これを書きながら思いましたが、もしかしたら2個以上渡せるかも知れません。
後日実験してみます。

2016/10/17変更
SQLのSELECT結果が2個以上でも複数行でも取得できます。
次項【➍ SQLのSELECT結果が複数個でも複数行でも取得する方法】を参照してください。

解説

batのfor命令について

命令 内容
/F for命令のオプション
詳しくは説明しませんが、これを付与する事によりテキストデータの解析ができます。
『テキストデータ』と言うのは、in の後にあるsqlplusコマンドの実行結果の事です。

今回提示しているfor命令ではテキストデータが分かりにくいかもしれません。
こんな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 の後に注目してください。
テキストファイルを指定しています。
hogehoge.txtには文字が沢山入っています。
それを一行ずつ読み込んで、DELIMS で指摘した区切り文字( : )があるかどうかをチェックしています。

ただし上記を実行させると1個目のカンマの前のワードだけが表示されます。
2ワード以降も表示させるには、"TOKENS=" オプションや %%j%%k を使う必要があります。
本記事ではそこまで言及しません。

興味ある方は別途調べてください。
下記記事が分かりやすいでしょう。

pz-linkcard: Incorrect URL specification.(url=)
>> .bat(バッチファイル)のforコマンド解説。 – Qiita

書籍が良い方はこちら↓をどうぞ

"DELIMS=" /F オプションがもつ解析方法の指定です。
他にも "SKIP=" とか "TOKENS=" とかあります。詳しくは別途お調べください。

DELIMSは区切りの文字を指定します。

:(コロン)を区切り文字にしたければ"DELIMS=:",(カンマ)を区切り文字にしたければ "DELIMS=," と書きます。

今回は区切り文字を指定していないため、テキストデータの1行をそのまま取得することになります。

ん???
区切らないならDELIMSは要らないんじゃない????

YESです(笑)

DELIMS は書かなくてもいいです。

じゃぁ、「なんで書いてんだよっ!?」と思いますよね。

書いた理由は2つあります。

  • FOR命令使ってテキストデータを解析するのって、区切り文字が必要になる事が多いだろうと思います。
    で、この記事読んでくださった方が、今後FOR命令を使う時にDELIMSの事を思い出せるように。
  • FOR命令の説明だけだと書く量が少なく説明があっさりするので 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の実行結果をBatファイルで取得する方法は分かりましたでしょうか?

 

 




 

➍ SQLのSELECT結果が複数個でも複数行でも取得する方法

あれこれと説明書くのは面倒なのでサンプルコードを示します。
別に難しくないので読んでみてください。

取得したいレコード数は1行&取得したいフィールドは2個以上

@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;

取得したいレコード数は2行以上&取得したいフィールドは2個以上

@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

VALIABLE 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 取得』みたいなワードで検索してみてください。

複数項目・複数行取得についてはこちらをヒントにしました。

 




 

まとめ

今回は1つの記事の中に2つの事を詰め込みました。

  • SQL*PlusのSELECT結果をBatが取得する
  • Batの日付計算をデータベースにやらせてしまう

本来であれば『1記事につき、1テーマ』が原則なんだろうと思うんですけど、どうしても切る事ができませんでした。

『SQL*PlusのSELECT結果をBatが取得する』方法を探している方にとって、ついでに『Batの日付計算をデータベースにやらせてしまう』方法を知ってもらうと応用が効いてより幸せになれるかなと考えたからです。

また、『Batの日付計算をデータベースにやらせてしまう』方法を探している方その手順をお伝えするにあたり、『SQL*PlusのSELECT結果をBatが取得する』方法をまず知っていただく必要があります。

ですので、2つのテーマを詰め込みましt.

実際の所この2つの情報を欲している人がどの程度いらっしゃるか分かりませんが、私自身が実際に困って解決した内容です。
同じような問題を持った方が一人でもいらっしゃって、この記事がお役に立てれば嬉しい限りです。

参考書籍

 


 

コメントを残す

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です

ABOUTこの記事をかいた人

Windows/Mac/Linuxを使う現役システムエンジニア&プログラマ。オープン系・組み込み系・制御系・Webシステム系と幅広い案件に携わる。C言語やC#やJava等数多くのコンパイラ言語を経験したが、少し飽きてきたので、最近はRubyやPython、WordPressなどのWeb系を修得中。初心者向けのプログラミング教室も運営中。オンライン・対面・出張等でプログラミングをレッスンします。