PL/SQLを業務で使うことがあったので簡単なまとめ

※使った範囲でひとまず

SQLを手続き型の処理で実行するためのOracleの仕組み。
MATERIALIZED VIEW を作るときに必要だったので、理解のため、備忘のために残します。

基本構造

DECLARE
/* 宣言部分（任意） */

BIGIN
/* 実行部分 */

EXCEPTION
/*  例外処理部分（任意）　*/

END;

• BIGINの中に BIGIN-END; をネストさせることは可能

DECLARE
/* 宣言部分（任意） */
BIGIN
   BIGIN
   /* 実行部分 */
   
   EXCEPTION
   /*  例外処理部分（任意）　*/
   END;
END;

要素

• BGIN
  • 処理の開始
  • Delphiにも似た構造がある
• END;
  • 処理の終了
  • ; （セミコロン）必要
  • Delphiにも似た構造がある
• IF isHoge THEN hoge ELSE fuga END IF;
  • 分岐の記法

IF ishoge THEN 
   hoge
ELSE
   fuga
END IF;

• EXECUTE IMMEDIATE
  • 動的SQLの実行

/* バインド変数なし */
BIGIN
   sql := 'select * from hoge';
   EXECUTE IMMEDIATE sql;
END;


/* バインド変数ari */
BIGIN
   sql := 'select * from hoge where fuga = :FUGA';
   fuga := '123';
   EXECUTE IMMEDIATE sql USING fuga;
END;

• RAISE
  • 常の実行を停止させ、例外ハンドラに制御を移す

BIGIN
   sql := 'select * from hoge';
   EXECUTE IMMEDIATE sql;
EXCEPTION
   RAISE <例外処理>;
END;

• OTHERS
  • 定義していない例外が発生しても適切に処理を正常終了させたい場合に利用

BIGIN
   sql := 'select * from hoge';
   EXECUTE IMMEDIATE sql;
EXCEPTION
   WHEN OTHERS THEN
        IF SQLCODE != -942 THEN
          RAISE;
        END IF;
END;

小ネタ

• SQLCODE
  • エラーコードなどを履いている場合はこの変数？に入っているので比較演算子が使える。
  • EXCEPTIONなどで利用できる
• DBMS_OUTPUT.PUT_LINE
  • sqlplusなどで実行している場合にコンソールに出力できるログの記法
  • JavaScriptなら console.log() など
  • Javaなら System.out.println() など
• 代入
  • := という記法

実践

HOGE, BK_HOGE というテーブルを UNION した HOGE＿MVIEW を生成したい

前提

• カラムは全く同じとする
• HOGEのバックアップ（累積）テーブルとしてBK_HOGEを利用している
• UNIONした情報を使ってselectしたい場合がある
• bk_flgというバックアップ時に１が立つカラムの状態によってUNIONする

BEGIN
    BEGIN
      EXECUTE IMMEDIATE 'DROP MATERIALIZED VIEW HOGE_MVIEW';
    EXCEPTION
      WHEN OTHERS THEN
        IF SQLCODE != -12003 THEN
          /* 12003はMATERIALIZED VIEW　が存在しない場合のエラーコード*/
          RAISE;
        END IF;
    END;
    
    EXECUTE IMMEDIATE 'create materialized view HOGE_MVIEW REFRESH FORCE AS 
        SELECT * FROM HOGE WHERE bk_fkg= 0
        UNION
        SELECT * FROM BK_HOGE WHERE bk_fkg= 1';
    
END;

最後に

知りたてほやほやなので間違い等のご指摘は喜びます。

参考ドキュメント

一部です