※上記の広告は60日以上更新のないWIKIに表示されています。更新することで広告が下部へ移動します。


PL/SQL とは?

Oracle社が開発した言語で、「SQLを手続き型の言語に拡張したもの」と定義される。
単独の SQLでは実現できない処理や、複雑な計算を記述することができる。
ストアドプログラムとしてデータベースに格納できるのも SQLに無い特徴。
(SELECT はビューがあるけどね~)

概観

ストアドにしない場合

DECLARE
 ※このへんで変数や関数/サブルーチンの定義を記述
BEGIN
 ※このへんでメイン処理を記述
END;
/
※SQL*Plus で実行する場合、最後の "/" が無いと PL/SQL の記述が正しく認識されない。
※次のようにコマンドラインから実行する。
sqlplus ユーザ/パスワード @スクリプト名
※スクリプト名の最後は ".sql" にすること。

ストアドにする場合

CREATE OR REPLACE PROCEDURE 登録名 IS
 ※このへんで変数や関数/サブルーチンの定義を記述
BEGIN
 ※このへんでメイン処理を記述
END;
/
show errors
※"show errors" で登録時にエラーがあった場合、原因が表示される。
※実行は次のようにする(SQLが実行できるタイミングならどこでもよい)
EXECUTE 登録名;

処理分割

サブルーチン

戻り値が無い場合は次のように
PROCEDURE 処理名
IS
 ※変数の定義など
BEGIN
 ※実際の処理
END 処理名;
※呼び出しは次のように
処理名;

※パラメタがある場合は次のように
PROCEDURE 処理名(パラメタ名 IN 属性, パラメタ名 IN 属性) IS
BEGIN
 ※変数の定義など
BEGIN
 ※実際の処理
END 処理名;
※パラメタの属性定義に桁数は指定しない。
※呼び出しは次のように
処理名(パラメタ, パラメタ);

関数

FUNCTION 関数名(パラメタ IN 属性, パラメタ IN 属性) RETURN 属性
IS
 ※変数定義
BEGIN
 ※処理内容
 RETURN 返却内容;
END 関数名;
※呼び出しは次のように
変数 := 関数名(パラメタ, パラメタ);

カーソル

とある条件で検索した結果を元に処理する場合、カーソルを利用するとよい。
  • カーソル定義
CURSOR カーソル名 IS
  SELECT 参照する項目 FROM テーブル名;
※必要に応じて、その他条件などを指定する。

  • 使い方
FOR バッファ名 IN カーソル名 LOOP
 ※処理
END LOOP;
※"バッファ名.項目名" で、読み込んだ(1行の)内容が参照できる。

変数定義

  • テーブルと同じに
変数名  テーブル名%ROWTYPE;
※各項目は "変数名.項目名" で参照/更新できる。

  • テーブルの項目と同じに
変数名  テーブル名.項目名%TYPE;

処理中断

  • ループ
    • "EXIT;" または "EXIT WHEN 条件;" で抜ける

  • サブルーチン
    • "RETURN;" で抜ける

  • 関数
    • "RETURN 返却値;" で抜ける

  • メイン処理
    • "RETURN;" で抜ける

デバッグ

  • 情報が出ない
    • エラーメッセージには行番号などが表示されるが、論理行なので実際のスクリプトとの対応がつきにくい
      • スクリプト先頭に "set echo on" を記述すると行番号つきのソースが表示される。
      • デフォルトでは echo は off

  • 情報を出したい
    • 次のようにして表示する
DBMS_OUTPUT.PUT_LINE('メッセージ');
改行しない場合は
DBMS_OUTPUT.PUT('メッセージ');
これらを使う場合、スクリプトに先立って "set serveroutput on" を記述しておくこと。

  • エラーになった場合
    • シェルスクリプトから実行したら、(svrmgrl と違って)sqlplus の場合、永久に止まったままになる。
    • おそらく、sqlplus の中で停止したのはいいけど、プロンプトが戻ってこない状態になっていると思われ。
    • そこで、不安のある場合は、シェルスクリプトをバックグラウンド実行させるとよいかも?

注意

  • テーブルの項目名と同じ名称の変数名はつけないこと。
    • SQL式の中で使う場合、変数名ではなく項目名として解釈されてしまうため。

外部とのインタフェース

例えば、シェルスクリプトから PL/SQL を実行する場合、動的に SQLの実行を変えたい場合があるとする。
その場合、シェルスクリプト部分から、PL/SQL部分へ情報を渡すのは可能か?

案1 ユーザ変数を使ってみる

DEFINE 変数 = 値;
  • "&" 付で参照すればよい
set serveroutput on;
define mm = 'MMMMMM';
DECLARE
BEGIN
  DBMS_OUTPUT.PUT_LINE('&mm');
END;
/

案2 バインド変数を使ってみる

VARIABLE 変数 属性;
  • この場合、PL/SQLブロック内で設定した内容を SQL*Plus から参照できる
  • ただし、SQL*Plus から直接値を設定する手段がなさそうな感じ。

案3 バインド変数の設定

VARIABLE 変数 属性;
DECLARE
BEGIN
  変数の設定
END;
/
DECLARE
BEGIN
  変数の参照
END;
/
exit;
  • このように、PL/SQLブロック内でバインド変数を設定するのはできる。
  • ただし外部からの設定という意味では役に立たない。
  • では、変数の設定部分をシェルスクリプト内で修正する案もあるが、インタフェースが美しくないかなあ。

案4 外部ファイルの読込

VARIABLE 変数 属性;
@ファイル名
DECLARE
BEGIN
  変数の参照
END;
/
exit;
(↓)読み込まれるファイル(動的に生成)
DECLARE
BEGIN
  変数の設定
END;
/
  • このように、インタフェース部分を別ファイルにして読み込むのはどうだろう?
  • 読み込まれるファイルを動的に生成して、その時にシェルスクリプトからの情報を埋め込むのは、上記と比べるとまだましかもしれない。
  • 複数本を順次処理する場合などは、一回作って使いまわせるのでいいかも?
  • バインド変数をどちらのファイルで定義するかは、要検討?

案5 シェルスクリプト内部で処理

#!/bin/csh
sqlplus ユーザ/パス <<END
VARIABLE 変数 VARCHAR2(10);
DECLARE
BEGIN
 :変数 := '$シェル変数';
END;
/
@SQLファイル
exit;
END
(↓)SQLファイル
DECLARE
BEGIN
  変数の参照
END;
/
  • このようにシェルスクリプト内でヒアドキュメントを使ったらどうだろう?
  • シェルスクリプトが少し煩雑になるが、一時ファイルの後始末なども不要だし。
  • 呼び出し部分を別ファイル(シェルスクリプト)にすればすっきりまとめられそうかなあ?

案6 案1の応用

#!/bin/csh
sqlplus ユーザ/パス <<END
DEFINE 変数 = '内容';
@スクリプト名;
exit;
END
(↓)スクリプト
DECLARE
  wk VARCHAR2(10);
BEGIN
  wk := '&変数';
END;
/