PL/SQLでデータの取得や更新処理（DECLARE、BEGIN、END、EXCEPTION、IF、FOR、COMMIT、ROLLBACK）

OracleではUPDATEのSQLで複雑なサブクエリを使えないため、複数のテーブルからデータを取得したり、取得したデータを使って計算処理をする場合は、PL/SQLを利用します。

ここではPL/SQLを利用する際によく使う基本的なものをまとめておきます。

基本的なPL/SQLの書き方としては、DECLARE、BEGIN～EXECPTION～END、といった形式になります。

簡単なPL/SQLの例を以下に示します。

-- DBMS_OUTPUTによる標準出力のON/OFF
SET SERVEROUTPUT ON;

DECLARE 
  v_count INT := 0;
  v_value INT;
  v_product PRODUCTS%ROWTYPE;
  CURSOR cur_products IS SELECT * FROM PRODUCTS WHERE ID >= 2;
  
BEGIN
  DBMS_OUTPUT.PUT_LINE('execute');

  DBMS_OUTPUT.PUT_LINE('----- update -----');
  FOR rec IN (
    SELECT ID, NAME, PRICE FROM PRODUCTS ORDER BY ID
  ) LOOP
    -- 対象データ
    DBMS_OUTPUT.PUT_LINE(rec.ID || ',' || rec.NAME || ',' || rec.PRICE);
    v_count := v_count + 1;
    
    -- 税込み価格の計算
    IF rec.ID IN (1, 2, 3) THEN
      SELECT TAX(rec.PRICE) INTO v_value FROM DUAL;
    ELSIF rec.ID = 4 THEN 
      SELECT SUM(PRICE) INTO v_value FROM PRODUCTS WHERE ID <> 4;
    ELSE
      v_value := 0;
    END IF;
    
    -- 価格の更新
    UPDATE PRODUCTS SET PRICE = v_value WHERE ID = rec.ID;
    DBMS_OUTPUT.PUT(' >> update count：');
    DBMS_OUTPUT.PUT_LINE(NVL(TO_CHAR(SQL%ROWCOUNT),'NULL') || '件');
  END LOOP;
  DBMS_OUTPUT.PUT_LINE('対象データ件数：' || v_count);
  
  DBMS_OUTPUT.PUT_LINE('----- rowtype select -----');
  SELECT * INTO v_product FROM PRODUCTS WHERE ID = 1;
  DBMS_OUTPUT.PUT_LINE(v_product.ID || ',' || v_product.NAME || ',' || v_product.PRICE);
  
  DBMS_OUTPUT.PUT_LINE('----- cursor select -----');
  FOR rec2 IN cur_products LOOP
    DBMS_OUTPUT.PUT_LINE(rec2.ID || ',' || rec2.NAME || ',' || rec2.PRICE);
  END LOOP;

  COMMIT;
  DBMS_OUTPUT.PUT_LINE('success');
  
EXCEPTION
  WHEN ZERO_DIVIDE THEN
    DBMS_OUTPUT.PUT_LINE('zero divide error');
    ROLLBACK; -- ロールバック
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('error');
    ROLLBACK; -- ロールバック
    RAISE;    -- 呼び出し元に同じ例外情報を伝播
--    NULL;     -- 何もしない
END;

自分用のメモという意味合いも含まれているので、処理的には意味のないものも含まれていますが、そこは適宜、読み取って頂ければと思います。

上記のSQLでは、商品テーブルからデータを取得して、レコード毎に税込み（8％）の金額を算出して、価格をUPDATEしています。

税込み金額の計算は「TAX」という事前に用意したユーザー定義のファンクションを使用しています。

対象となる商品テーブル（PRODUCTS）には以下のデータが格納済みとし、

ID	名称	価格
1	商品A	100
2	商品B	200
3	商品C	300
4	商品D	600

このデータに対して、先ほどのPL/SQLを実行すると、標準出力で以下のログが出力されます。

PL/SQLプロシージャが正常に完了しました。

execute
----- update -----
1,商品A,100
 >> update count：1件
2,商品B,200
 >> update count：1件
3,商品C,300
 >> update count：1件
4,商品D,600
 >> update count：1件
対象データ件数：4
----- rowtype select -----
1,商品A,108
----- cursor select -----
2,商品B,216
3,商品C,324
4,商品D,648
success

EXCEPTIONの処理を確かめたい場合は、税込みの計算処理などで「/ 0」を追加して実行してください。

また、トランザクションの開始は明示的に指定する必要はありません。COMMITが実行されたタイミングでそれまでに処理された内容がDBに反映されます。

EXCEPTIONで指定可能な例外の種類はこちらを参照してください。

個人的にはPL/SQLではそこまで規模の大きいSQLを書いたことがなく、必要になったとしてもデータコンバートで1度しか使わないといったことしかなかったので、例外処理は「OTHERS」でまとめていました。