シェルスクリプトからPL／SQLのプロシージャを実行する方法

投稿日：2020年10月21日更新日：2021年2月27日

＜目次＞

(１) シェルスクリプトからPL／SQLのプロシージャを実行する方法
　 (１-１) 処理の概要
　 (１-２) サンプルプログラム
　　 (１-２-１) 呼び出し元：シェルスクリプト側
　　 (１-２-２) 呼び出し先：PL/SQL側
　 (１-３) プログラム実行結果

(１) シェルスクリプトからPL／SQLのプロシージャを実行する方法

(１-１) 処理の概要

表題の通りシェルスクリプトからPL/SQLプロシージャを実行して、DBのテーブルに対するCRUDを行う方法を紹介します。PL/SQLを呼び出せる事で、SQLに対する手続き処理も可能となり、出来る事の幅も広がる可能性があります。

今回紹介するサンプルプログラムは次の図のように、シェルスクリプトからPL/SQLストアドプロシージャを呼び出し、オンラインテーブル「ACCESS_COUNTER」にあるデータを、ヒストリテーブルである「ACCESS_COUNTER_HISTORY」に退避する処理のサンプルプログラムをご紹介します。

（図１１１）

＞目次にもどる

(１-２) サンプルプログラム

上記の処理概要の「シェルスクリプト」と「PL/SQLプロシージャ」のプログラムを紹介します。

(１-２-１) 呼び出し元：シェルスクリプト側

（サンプルプログラム）

・「TENNISDBUSR」の箇所はご自身のスキーマ名に置き換えてください。
・「XXX.XXX.XXX.XXX」の部分はIPアドレスやホスト名に置き換えてください。
・「1521/orcl」の部分はご自身のポート番号/SID名に置き換えてください。

#!/bin/sh

#### 環境変数の値をセット（ORACLE_HOME,ORACLE_SID）
#### イコール「=」以降にはご自身の値をセットします（※下記はあくまで例です）
export ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1
export ORACLE_SID=orcl

#### Oracleデータベースのユーザー情報を設定
#### 値は上からDBユーザー、DBパスワード、接続文字列です。
l_user='TENNISDBUSR'
l_psw='[ご自身のパスワード]'
l_conn_string='//XXX.XXX.XXX.XXX:1521/orcl'

#### PLSQLプロシージャの第一引数を宣言します。
#### （※シェルスクリプト内の変数として宣言）
var1=''

#### 前半（<<の前まで）でSQL*Plusを起動します。
#### 後半の「<< END」は埋め込みスクリプトの最終地点の文言「END」を予告宣言
#### 埋め込みスクリプトは具体的には、起動したSQL*Plus内で実行するコマンドです。
/u01/app/oracle/product/11.2.0/dbhome_1/bin/sqlplus $l_user/$l_psw@$l_conn_string << END

  #### PLSQLプロシージャの第二引数を宣言します。
  #### （※SQL*Plus内のバインド変数として宣言）
  var var2 varchar2(20);

  #### PLSQLプロシージャを実行します。
  #### SQL*Plusのバインド変数は代入時に前にコロン「:」を付与します（お作法）
  execute TENPKS_UTILS_ACCESS_COUNTER_T('$var1',:var2);

  #### シェルスクリプトの終了
  exit;

#### here-documentの終了地点（ここでSQL*Plusから切断されます）
END

＞目次にもどる

(１-２-２) 呼び出し先：PL/SQL側

（サンプルプログラム）

create or replace PROCEDURE TENPKS_UTILS_ACCESS_COUNTER_T (var1 IN varchar2, var2 IN OUT varchar2) AS
BEGIN
  --access_counterテーブルのレコード数だけLOOPする
  FOR o IN (select counter_id, function_id, access_count from access_counter)    
  LOOP
    --変数var2に次のINSERT文の条件で使う値を代入
    var2:=o.function_id;
 
    --access_counterテーブルのレコードをACCESS_COUNTER_HISTORYにINSERT
    INSERT INTO ACCESS_COUNTER_HISTORY_TEST (COUNTER_ID,FUNCTION_ID,ACCESS_COUNT,ACCESS_DATE) 
    VALUES (ACCESS_HISTORY_SEQ.NEXTVAL,var2,o.access_count,SYSDATE); 
  END LOOP;
END TENPKS_UTILS_ACCESS_COUNTER_T;

こちらに関しては、シェルスクリプトから呼び出す以前に、単体でちゃんと機能するか？を事前に疎通確認しました（↓動画）
（動画）PL/SQLの単体疎通確認