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

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

目次

• 基本形
• ログを出力する
• UPDATE 時の更新行数を知る
• 暗黙的カーソル処理

基本形

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 以下に処理。
  • 変数名 := 値 で代入。
  • カーソル操作は Open・Close。次の行の読込は 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;