4-4. 処理起動方式の共通化

プロシージャ(関数)呼出方法の共通化

「2.処理方式の検討」で述べたように、PL/SQLで記述されたプログラムは、OSコマンドから直接実行することはできません。
ここでは、夜間バッチ処理を想定し、OSコマンドから起動する方法を検討します。

Oracleのインストール時には必ずSQL*Plusがインストールされますので、どの環境でも汎用的に使用できる方法としては、
１．シェル(Linuxの場合、Windowsの場合はbatファイル)で記述されたOSコマンドとしてSQL*Plusを起動
２．その引数としてSQLスクリプトを指定して実行
３．SQLスクリプト中に実行したいパッケージの呼び出しを記述する
という方法が考えられます。

図にすると下記のようなイメージです。
バッチ処理起動時の共通機能関連図

この方法を実装しようとすると、特に深く考えなければ、パッケージを呼び出すSQLスクリプトと、SQLスクリプトを呼び出すOSコマンドは、プログラムの本数分作ることになります。
100本のPL/SQLで記述されたバッチプログラムがあれば、100本のSQLスクリプトと100本のシェルスクリプトを作成しなければならないということです。

それでは開発工数もかかりますし、何よりも運用が始まった後のプログラムの増減や引数の変更による保守工数もかかります。

サンプルコード

若干初期工数はかかりますが、1本ずつの共通機能を作り込むことで機能・引数の一覧から動的に処理を起動することができます。
あくまでもイメージですが、ソースを記述してみましょう。

まず、シェルスクリプトは以下のように、決められたSQLスクリプトを実行するようにSQL*Plusを起動するだけです。
仮に呼び出したいSQLスクリプトを「exec.sql」とします。

呼び出したい処理名を引数で渡します。
この処理名は、パッケージやプロシージャ名ではありません。
処理を実行する単位で命名します。

#/bin/sh
$ORACLE_HOME=/xxx/oracle/ →Oracleインストール時に設定されたORACLE_HOME環境変数パス
execname=$1
# SQL*plusの実行
sqlplus user/pwd@host1 @exec.sql execname
if $?<>0 then
# エラーハンドリングを記述
・・・・
fi

プログラム毎に引数が異なるのでは？と思われるのかもしれませんが、引数はSQLスクリプト内で制御します。
SQLスクリプトは次のような内容になります。

DECLARE
  -- プログラムマスタからパッケージ・プロシージャ名と引数情報を取得
  CUSROR cur_program_info(p_exec_name IN VARCHAR2)
  IS
  SELECT package_name, procedure_name, arg1, arg2, arg3, ....arg20
  FROM program_info
  WHERE exec_name = p_exec_name;
--
  rec_program_info cur_program_info%ROWTYPE;
  lv_exec_name program_info.exec_name %TYPE; -- パラメータの処理名格納変数
  lv_exec_sql VARCHAR2(4000);
  EXCEPTION EXECNAME_NOT_EXISTS; -- マスタ未登録例外
BEGIN
  lv_exec_name := '&1';
--
  -- 事前に登録したプログラムマスタから情報を取得
  OPEN cur_program_info(lv_exec_name);
  -- レコードを1件取得
  FETCH cur_program_info INTO rec_program_info;
  -- 取得できなければ例外処理へ
  IF cur_program_info%NOTFOUND THEN
    RAISE EXECNAME_NOT_EXISTS;
  END IF;
  CLOSE cur_program_info;
--
  -- 取得した情報からプロシージャを実行するための動的SQLを生成
  lv_exec_sql := 'BEGIN '
              || rec_program_info.package_name
              || '.' || rec_program_info.procedure_name || '(';
  IF rec_program_info.arg1 IS NOT NULL THEN
    lv_exec_sql := lv_exec_sql || '''' || rec_program_info.arg1 '''';
  ELSEIF rec_program_info.arg2 IS NOT NULL THEN
    lv_exec_sql := lv_exec_sql || ',''' || rec_program_info.arg2 '''';
  ELSEIF rec_program_info.arg3 IS NOT NULL THEN
    lv_exec_sql := lv_exec_sql || ',''' || rec_program_info.arg3 '''';
  :
  :
  ELSEIF rec_program_info.arg20 IS NOT NULL THEN
    lv_exec_sql := lv_exec_sql || ',''' || rec_program_info.arg4 '''';
  END IF;
  lv_exec_sql := lv_exec_sql || '); END;';
--
  -- 動的SQLでの実行
  EXECUTE IMMEDIATE lv_exec_sql;
EXCEPTION
  WHEN EXECNAME_NOT_EXISTS THEN
    CLOSE cur_program_info;
    dbms_output.put_line('Not exists program. [' || lv_exec_name || ']');
    RAISE;
  WHEN OTHERS THEN
    CLOSE cur_program_info;
    dbms_output.put_line(SQLERRM);
    RAISE;
END;

サンプルコードの解説

重要なポイントはprogram_infoテーブルの中身です。
このテーブルには、処理名(exec_name)をキー項目に、パッケージ名(package_name)、プロシージャ名(procedure_name)とそれに付随する引数1～20を保持します。

こうすることで、program_infoテーブルに登録された、処理名に紐付くプロシージャ・引数で、処理を起動することができます。
起動方式が変わった場合に、200本ものシェルやSQLスクリプトを修正する必要もなくなります。

なお、上記の記述は本当に必要最低限のものです。

例えば、引数はマスタから取得した値をそのまま設定する仕様になっています。
日毎に実行されるような場合に、処理日を指定したい場合や、あるマスタ値に基づいた内部IDを指定したい場合などは、もう一工夫必要です。
例えば、マスタには「<SYSTEMDATE>」という文字列を設定しておき、上記のロジック中で「SYSDATE」に置き換える、といった方法です。

また、もう一点、上記のコードは文字列型であることが前提となっています。
特に日付型で暗黙の型変換を使用してしまうと、サーバ設定が変わった際にエラーとなってしまったり、最悪の場合は異なる日付で動作してしまう場合があります。

そのようなことの無いよう、program_infoテーブルに引数の情報としてデータ型の区分も保持するか、引数は常に文字列型でやりとりすることとし、各々のプログラム中で日付型に変換するといった対応を考慮する必要があります。

これらはシステムの要件に応じて柔軟に対応していけば良いでしょう。

2018-07-04

Posted by tfurukaw