Oracleのデータもシェルで：ステップ・バイ・ステップ・シェルスクリプト（7）

標準入出力でOracleを扱う「chcsv」

　Oracleのデータをシェルスクリプトで取り扱うためには、SQL*PlusやPL/SQLのHTP.PRINT命令などを利用して、結果をテキストファイルに出力してからシェルスクリプトで加工するのが一般的です。しかし、ここではOracleを標準入出力から直接扱えるツール「chcsv」を紹介したいと思います。この連載で何度か取り上げているように「標準入出力」でデータを取り扱うのがシェルスクリプトの基本です。

　chcsvはフリーソフトで、http://www.asahi-net.or.jp/~nq7t-kwbtで手に入ります。コンパイルの方法などはhttp://member.nifty.ne.jp/m_tadaに詳しく紹介されていますので参考にするとよいでしょう。

　chcsvの基本動作は、「標準入力」からSQL文を読み込み、「標準出力」へ結果を出力します。結果は「CSV形式」と呼ばれるカンマ区切り（区切り文字はオプションで変更可能）のフォーマットで出力されます。さっそく例を見てみましょう。

$ echo 'select * from emp' | chcsv scott/tiger 7369,SMITH,CLERK,7902,17-DEC-80,800,,20 7499,ALLEN,SALESMAN,7698,20-FEB-81,1600,300,30 7521,WARD,SALESMAN,7698,22-FEB-81,1250,500,30
： 
以下省略

　標準入力から読み込まれたSQL文（SELECT文）に従い、Oracleのテーブルempの内容が標準出力へ出力されています。

　SQL*PLUSと違って、chcsvに渡すSQL文の最後には（;）や（/）をつける必要はありません。Oracleで実行できる単独のSQLであればなんでもchcsvに渡すことができます。それは必ずしもSELECT文である必要はありません。INSERT/UPDATE/DELETE文なども実行することができます。SELECT文以外では標準出力にはなにも出力されませんが、そのSQL文を実行し、最後にCOMMITを実行して終了します。

日付の演算をしてみる

　ここでは、ある程度SQL文をご存じという前提で進めていきましょう。以下の例では、Oracleの機能を使い、シェルスクリプト内で日付の演算を行っています。シェルスクリプトでは日付データの演算を行ういい方法がなかなかないのですが、Oracleの機能を使えば簡単にできますので利用してみます。以下の内容をora_date.shというファイルに保存し実行してみます。

#!/bin/bash
today=\
 `echo "select to_char(sysdate,'yyyy/mm/dd') from dual" |\
  chcsv scott/tiger`
tomorrow=\
  `echo "select to_char(sysdate+1,'yyyy/mm/dd') from dual" |\
  chcsv scott/tiger`
yesterday=\
  `echo "select to_char(sysdate-1,'yyyy/mm/dd') from dual" |\
  chcsv scott/tiger`
onemonthago=\
  `echo "selectto_char(add_months(sysdate,-1),'yyyy/mm/dd') \
  from dual" | chcsv scott/tiger`
echo "Today is " ${today}
echo "Tomorrow is " ${tomorrow}
echo "Yesterday is " ${yesterday}
echo "One month ago is " ${onemonthago}
#---

　実行してみましょう。結果は次のようになります。

$ chmod +x ora_date.sh
$ ora_date.sh
Today is 2000/09/13
Tomorrow is 2000/09/14
Yesterday is 2000/09/12
One month ago is 2000/08/13

結果をシェル変数に格納

　少し長いですが、上記のシェルスクリプトの内容を解説しましょう。

today=\
  `echo "select to_char(sysdate,'yyyy/mm/dd') from dual" |\
  chcsv scott/tiger`

　この1文で、Oracleより日付データを取り出してシェルの変数todayにセットしています。逆シングルクオーテーション(`）で囲まれた部分は、その内容である

echo "select to_char(sysdate,'yyyy/mm/dd') from dual" | \
chcsv scott/tiger

を実行しています。

　これは最初の例と同じで、標準入力よりSELECT文を取り込み、それをchcsvに渡し、結果を標準出力へ出力しています。(`)で囲まれた部分はその実行コマンドの標準出力をコマンドラインに追加する働きをしますので（第4回「制御文で道案内」参照）、その結果がtodayに代入されるしくみになっています。

　SELECT文の中身では、dualという、Oracleに標準に用意されているいわば「ダミー」のテーブルから日付を選択している形になっています。dualはこの場合のように、実際のテーブルに関係ない情報を取り出す場合によく使われます。ここで使われているsysdateは、Oracleの世界でシステム日付を表します。sysdateはdate型という内部形式になっていますのでto_charで文字列に変換して取り出しています。

　その他の行は上記に関連したものですが、sysdateに1足したり（つまり1日を足す）引いたりしています。

　add_monthsは「月」を足す関数で

add_months(date型データ,整数）

のように使います。2番目の引数がマイナスの場合はその月数だけ過去にさかのぼります。このようにOracleでは豊富に日付演算の方法が用意されていますので、シェルスクリプトにも応用すると非常に便利です。

結果を単純に表示する

　次に引数によりOracleからデータを取り出して表示するシェルスクリプトを見てみましょう。例として、引数として与えられた人の給与を表示するシェルスクリプトを作成してみます。

#!/bin/bash
while [ "$1" != "" ] ; do
  sal=`chcsv scott/tiger -n <<EOF
  select sal from emp
  where ename = '$1'
EOF`
sts=$?
if [ ${sts} -eq 0 ]; then
  echo "$1's salary is \$ ${sal}."
  elif [ ${sts} -eq 123 ]; then
  echo "$1 is not on EMP table."
else
  echo "Oracle error."
fi
shift
done
#--

　これをsalary_checkというファイルに保存し、早速実行してみましょう。結果は下記のようになります。

$ chmod +x salary_check
$ salary_check ADAMS ALLEN SAKAI
ALLEN's salary is $ 8000.
ADAMS's salary is $ 1100.
SAKAI is not on EMP table.

結果ステータスをチェック

　上記のスクリプトの中身を見ていきましょう。

while [ "$1" != "" ] ; do
:
:
shift
done

この部分で、引数の数だけ繰り返し処理を行っています。

sal=`chcsv scott/tiger -n <<EOF
  select sal from emp
  where ename = '$1'
EOF`

　最初の例とはちょっと違いますが、「<salにセットしています。

　chcsvの-nオプションにより、SELECT文を実行した結果、データが1件もない場合に0以外のステータスを返すようにしています。このオプションが指定されなかった場合は、データが1件もない場合でも0を返します。もちろんSQLの文法エラーや、その他のOracleエラーが発生した場合は0以外のステータスを戻します。<

where ename = '$1'

は、

where ename = 'ADAMS' 

のように、シングルクオーテーションはそのままで、$1は変換されてchcsvに渡されます。

　次の部分をみてみましょう。

sts=$?
if [ ${sts} -eq 0 ]; then
  echo "$1's salary is \$ ${sal}."
elif [ ${sts} -eq 123 ]; then
  echo "$1 is not on EMP table."
else
  echo "Oracle error."
fi

この部分でchcsvのステータスをチェックし、正常の場合は結果を出力します。該当データが存在しなければその旨のメッセージを表示し、またエラーの場合はエラーメッセージを表示します。chcsvのソースの中を少しのぞいてみると（chcsvはソースも配布されています）、Oracleのエラーコード（sqlcode）をそのままステータスとして返しているようです。

　しかし残念なことにbashのステータスは0から255までしか扱えず、それ以上は“切り捨て”られてしまいます。つまり、chcsvの戻り値はsqlcodeを256で割った「あまり」となります。このスクリプトの場合は、SELECT文の結果１レコードもなかった場合を判定しています。データがなかった場合のSQLCODEは1403ですので、

　よって、chcsvの戻り値が123の場合はデータがなかったと判定しています。もちろんこの方法は完璧な方法ではありません。SQLCODEが256+123=379、512+123=635……など、ほかのエラー番号の場合も、誤ってデータなしと判断してしまいます。でもシェルスクリプトで扱う場合には実質不便はないでしょう。

　この例題のようにSQLとシェルの環境変数を自由に組み合わせることができるのも、標準入力を使えるchcsvとシェルスクリプトの強みなのです。