Corredor

ウェブ、プログラミングの勉強メモ。

PL/SQL の基本的な書き方をまとめてみる

PL/SQL でよくある「SELECT した結果を1行ずつ操作する」という処理を雛形的に書いてみた。その他、よくあるイディオムをまとめてみた。

基本形

Declare
  Cursor csr Is
    SELECT hoge FROM my_table;
  counter Number;
  csr_row csr%ROWTYPE;
Begin
  counter := 0;
  
  Open csr;
  Fetch csr Into csr_row;
  
  If csr_acc%NOTFOUND Then
    Close csr_acc;
    Raise NO_DATA_FOUND;
  End If;
  
  Loop
    Begin
      counter := counter + 1;
      Fetch csr Into csr_row;
      Exit When csr%NOTFOUND;
    End;
  End Loop;
  
  Close csr;
Exception
  When NO_DATA_FOUND Then
    Dbms_Output.Put_Line('カーソル0件のため処理中断・ロールバック');
    Rollback;
  When DUP_VAL_ON_INDEX Then
    Dbms_Output.Put_Line('一意制約違反・ロールバック');
    Rollback;
  When Others Then
    Dbms_Output.Put_Line('その他エラー・ロールバック');
    If csr%ISOPEN Then
      Close csr;
    End If;
    Rollback;
End;
/
  • Declare 以降で変数定義。
    • Cursor カーソル名 Is SELECT 文;
    • 変数名 Number
    • レコード変数 カーソル名%ROWTYPE
  • Begin 以下に処理。
    • 変数名 := 値 で代入。
    • カーソル操作は OpenClose。次の行の読込は Fetch カーソル Into レコード変数
    • カーソル%NOTFOUND で最終行に到達したか、結果が0件だった時。
    • カーソル%ISOPEN で開いているか確認。
    • If 条件 Then 処理 End If;
    • Raise 例外名Exception 句に飛ばす。
    • Loop Begin 処理 End; End Loop;
    • Exit When 条件; でループを抜ける。
    • Commit;Rollback;
  • Exception 以下に例外処理。
  • End; / で即時実行。

ログを出力する

Set serverout on
Dbms_Output.Put_Line('ログ');

Echo 的な。ログとして出力するには Set serverout on が必要。

UPDATE 時の更新行数を知る

UPDATE my_table SET column = csr_row.column WHERE column = hoge;
update_counter := SQL%ROWCOUNT;

UPDATE 文の直後に SQL%ROWCOUNT を参照すると、そのクエリの更新行数が分かる。

暗黙的カーソル処理

For csr_row In csr Loop
  Begin
    処理
  End;
End Loop;

プロとしてのOracle PL/SQL入門 【第3版】(Oracle 12c、11g、10g対応) (Oracle現場主義)

プロとしてのOracle PL/SQL入門 【第3版】(Oracle 12c、11g、10g対応) (Oracle現場主義)