PL/SQL に関する覚え書き 1

何年かぶりにアプリケーションエンジニアに戻ったのだが， なんかもう色々忘れちゃってて。 歳はとりたくないものである。

特に忘れちゃってたのが PL/SQL。 設計段階で計画して使っていれば別だけど， こういうのって大抵抜き差しならない状態になって慌てて検討を始めるんだよね。 デスマーチ一歩手前のときとか。 で， そのときになって必死に思い出そうとするんだけど， 過去の悪夢も一緒によみがえって精神的なダメージを受けたり。

まぁ今回は幸いにも「抜き差しならない状態」ではなかったのでじっくり（といっても10人日ほど）取り組めたのでよかったのだが， もしかして「次回」があるかもしれないので， 忘れないうちに覚え書きの形で残しておこうと思う。 といっても PL/SQL の言語仕様を列挙してもしょうがないのでごくさわりの部分だけ。 こんだけ書いときゃ「そういえばそうだっけ」と（私が）思い出せる程度の内容。 PL/SQL についてちゃんと勉強したいなら以下のコンテンツのほうがお薦め。

• SHIFT the Oracle - oracle tips and tricks
• SQL プログラミング言語資料
• 忘れっぽいエンジニアのＯＲＡＣＬＥ／オラクルＳＱＬリファレンス
• Oracle Database オンライン・ドキュメント 10g リリース2 (10.2)

また私が再学習用に買った『プロとしてのOracle PL/SQL入門』もひそかにお薦め。 入門用としては必要十分な内容だと思う。 「入門」は卒業された方々も， あのときのほろ苦い思い出をよみがえらせるトリガとしてご利用いただければと（笑）

プロとしてのＯｒａｃｌｅ　ＰＬ ＳＱＬ入門

posted with 簡単リンクくん at 2006.12.26

アシスト教育センター著
ソフトバンククリエイティブ (2006.5)
通常2-3日以内に発送します。

オンライン書店ビーケーワンで詳細を見る

PL/SQL は名前どおりの手続き型言語（Procedural Language）で， その構造は Pascal によく似ている。 まぁ PL/SQL は Ada をベースにしているらしいので似てても不思議はない。 PL/SQL は以下のようなブロック構造になっている。

DECLARE
  /* 宣言部 */
BEGIN
  /* 実行部 */
EXCEPTION
  /* 例外処理部 */
END;

このうち宣言部と例外処理部は省略できる。 つまり一番簡単なコードは以下のようになる。

BEGIN
  NULL; -- No operation
END;

このコードは SQL*Plus 等を使ってリモートから実行できる。 実行する際には最後にスラッシュ（/）を入れるのを忘れずに。 （コードをファイルに記述して実行する場合も同じ）

SQL> BEGIN
  2    NULL;
  3  END;
  4  /
PL/SQL procedure successfully completed.

テスト等でデータベースにデータを流し込む場合に DML 文をファイルに列挙することこともあると思うが， PL/SQL 形式なら使い回しが少し楽になる。

宣言部には変数や定数，変数タイプやサブプログラム（Subprogram）を定義できる。 以下は宣言部にサブプログラムを定義した例。

DECLARE
PROCEDURE MYPROC – 仕様部
IS
/* 宣言部 /
BEGIN
/ 実行部 /
EXCEPTION
/ 例外処理部 */
END MYPROC;
FUNCTION MYFUNC RETURN NUMBER – 仕様部
IS
/* 宣言部 /
BEGIN
/ 実行部 /
EXCEPTION
/ 例外処理部 */
END MYFUNC;
BEGIN
NULL;
END;

前者がプロシージャ（Procedure）で後者がファンクション（Function）である。 プロシージャやファンクションの定義は， それ自体がブロック構造になっている（プロシージャやファンクションの詳細については後述）。 このように PL/SQL ではブロックをネストすることができる。 上の例では宣言部がネストしているが， 実行部もブロックをネストできる。

BEGIN
  ...
  BEGIN
    ...
  END;
  ...
END;

これで， 例えばある特定の処理をブロックで囲んで例外をキャッチしたりできる。 （実例はまた次回にでも）

PL/SQL のコードを直接リモートから実行するのではなく， ストアド・サブプログラム（Stored Subprogram）として定義しサーバに登録する場合には以下の構文になる。

CREATE OR REPLACE PROCEDURE MYPROC -- 仕様部
IS
  /* 宣言部 */
BEGIN
  /* 実行部 */
EXCEPTION
  /* 例外処理部 */
END;
CREATE OR REPLACE FUNCTION MYFUNC RETUREN NUMBER – 仕様部
IS
/* 宣言部 /
BEGIN
/ 実行部 /
EXCEPTION
/ 例外処理部 */
END;

前者がストアド・プロシージャ（Stored Procedure）で後者がストアド・ファンクション（Stored Function）である。 宣言部（IS ～ BEGIN の間）は省略できない（ただし空のままでもOK）。 ファンクションには必ず返り値があるが， プロシージャにはない。

サブプログラムでは呼び出し時にパラメータを付けることができる。

CREATE OR REPLACE PROCEDURE MYPROC(
  var1 OUT NUMBER,
  var2 IN OUT NOCOPY VARCHAR2,
  var3 IN NUMBER DEFAULT 0 )
IS
BEGIN
  NULL;
END;

パラメータには IN, OUT, IN OUT の3つのモードを指定できる。 指定がない場合は IN モード。 IN モードではデフォルト値を設定できる（OUT, IN OUT モードでは不可）。 また OUT および IN OUT モードでは NOCOPY を指定できる（IN モードで指定するとエラーになる）。 NOCOPY はコンパイラヒントで， パラメータを参照渡しにすることができる（条件によって参照渡しができない場合もある）。 ちなみにデフォルトでは IN モードは参照渡しで OUT, IN OUT モードは値渡しである。 ただし IN モードのパラメータに値を代入することはできない。

ストアド・サブプログラムや変数タイプや定数などの定義をパッケージとしてまとめることができる。

/* パッケージ仕様部 */
CREATE OR REPLACE PACKAGE PKG
IS
  PROCEDURE MYPROC;
  FUNCTION MYFUNC RETUREN NUMBER;
END;
/* パッケージ本体部 */
CREATE OR REPLACE PACKAGE BODY PKG
IS
PROCEDURE MYPROC
IS
BEGIN
NULL;
END MYPROC;
CREATE OR REPLACE FUNCTION MYFUNC RETUREN NUMBER
IS
BEGIN
RETURN 0;
END MYFUNC;
END;

パッケージを登録する際には必ず仕様部と本体部をセットで登録する。 このとき仕様部で宣言したストアド・サブプログラムは本体部でも必ず定義されていなければならない （仕様部にないものを本体部で定義することは可能。 つまりパッケージ化によって中の構造を隠蔽できる）。

SQL*Plus などを使ってストアド・サブプログラムやパッケージを登録する際に， コードを解析して何か問題があればエラーを返すが， エラーの詳細までは表示されない。 直前の処理のエラー内容を知るには "SHOW ERRORS" コマンドを使う。

SQL> SHOW ERRORS

パッケージ内のオブジェクトを呼び出すには以下の形式で記述する。

DECLARE
  ret NUMBER;
BEGIN
  PKG.MYPROC();
  ret := PKG.MYFUNC();
END;

あるいは

SQL> EXECUTE PKG.MYPROC();

SQL*Plus でも実行時の様子は表示されない。 ただし "SET SERVEROUT ON" なら DBMS_OUTPUT.PUT_LINE プロシージャで出力した内容が表示される。 これで間接的にデバッグすることは可能。 DBMS_OUTPUT.PUT_LINE プロシージャは受け取った文字列をバッファに溜めてセッション終了時に一度に出力する仕様になっている。 セッション中にバッファがいっぱいになると例外を吐くので注意。

あう， PL/SQL の構造の説明で終わってしまった。 続きは別の機会で。