【PL/SQL】カーソル完全ガイド｜4フェーズ・%ROWTYPE・FOR UPDATE+SKIP LOCKED・BULK COLLECT LIMIT・実務10パターン

DECLARE
  -- ① DECLARE: カーソル宣言
  CURSOR cur_active_users IS
    SELECT id, email FROM users WHERE status = 'active';

  -- レコード変数（%ROWTYPEで自動定義）
  v_user cur_active_users%ROWTYPE;
BEGIN
  -- ② OPEN: カーソルを開いてSQL実行
  OPEN cur_active_users;

  -- ③ FETCH: 1行ずつ取得してループ処理
  LOOP
    FETCH cur_active_users INTO v_user;
    EXIT WHEN cur_active_users%NOTFOUND;

    DBMS_OUTPUT.PUT_LINE(v_user.id || ': ' || v_user.email);
  END LOOP;

  -- ④ CLOSE: カーソルを閉じる（重要）
  CLOSE cur_active_users;
END;
/

BEGIN
  FOR rec IN (
    SELECT id, email FROM users WHERE status = 'active'
  ) LOOP
    DBMS_OUTPUT.PUT_LINE(rec.id || ': ' || rec.email);
  END LOOP;
  -- OPEN/CLOSE/FETCH/%NOTFOUND は自動管理
END;

1. 解析済みのSQL文（parsed SQL statement）
2. 結果セットの位置ポインタ（current row pointer）
3. 実行状態（OPEN／CLOSE／属性値）

カーソル ≒ プログラムからの「結果セットへのハンドル」

BEGIN
  UPDATE users SET status = 'inactive' WHERE last_login < SYSDATE - 365;

  -- 暗黙カーソル SQL の属性で更新行数取得
  IF SQL%ROWCOUNT = 0 THEN
    DBMS_OUTPUT.PUT_LINE('更新対象なし');
  ELSE
    DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT || '件更新');
  END IF;

  COMMIT;
END;

DECLARE
  -- シンプルな宣言
  CURSOR cur_users IS
    SELECT id, email FROM users WHERE status = 'active';

  -- パラメータ付き
  CURSOR cur_by_status(p_status VARCHAR2) IS
    SELECT id, email FROM users WHERE status = p_status;

  -- FOR UPDATEで行ロック
  CURSOR cur_pending_orders IS
    SELECT * FROM orders WHERE status = 'pending'
    FOR UPDATE;   -- FETCH時に行ロック

  -- RETURN句で戻り型明示（パッケージ定義向き）
  CURSOR cur_active_user RETURN users%ROWTYPE IS
    SELECT * FROM users WHERE status = 'active';
BEGIN
  NULL;
END;

BEGIN
  -- OPENで宣言済SQLがパース＆実行される
  OPEN cur_users;

  -- パラメータ付きは引数指定
  OPEN cur_by_status('active');

  -- この時点ではまだ結果セットの先頭のポインタが立っているだけ
  -- 実際のデータはFETCHしないと取れない
  NULL;
END;

DECLARE
  CURSOR cur IS SELECT id, email FROM users;
  v_id users.id%TYPE;
  v_email users.email%TYPE;
  v_user users%ROWTYPE;
  TYPE t_users IS TABLE OF users%ROWTYPE;
  v_users t_users;
BEGIN
  OPEN cur;

  -- パターン①：個別変数へFETCH
  FETCH cur INTO v_id, v_email;

  -- パターン②：レコード変数へFETCH（%ROWTYPE）
  FETCH cur INTO v_user;
  DBMS_OUTPUT.PUT_LINE(v_user.id || ' ' || v_user.email);

  -- パターン③：BULK COLLECTで一括取得（後述）
  FETCH cur BULK COLLECT INTO v_users LIMIT 1000;

  CLOSE cur;
END;

DECLARE
  CURSOR cur IS SELECT id FROM users;
BEGIN
  OPEN cur;
  -- ... 処理 ...
  CLOSE cur;  -- 必須：Oracleが管理するメモリを解放

  -- CLOSE忘れるとセッション中リソースリーク
  -- 特にループ内でOPENするカーソルは要注意

  -- 例外安全なパターン（EXCEPTION節でも閉じる）
  OPEN cur;
  BEGIN
    -- ... 処理 ...
    CLOSE cur;
  EXCEPTION
    WHEN OTHERS THEN
      IF cur%ISOPEN THEN
        CLOSE cur;
      END IF;
      RAISE;
  END;
END;

DECLARE
  CURSOR cur IS SELECT id, name FROM users;
  v_row cur%ROWTYPE;
BEGIN
  -- ISOPEN：OPEN前の確認
  IF NOT cur%ISOPEN THEN
    OPEN cur;
  END IF;

  LOOP
    FETCH cur INTO v_row;

    -- NOTFOUND：取得できない＝ループ終了
    EXIT WHEN cur%NOTFOUND;

    -- ROWCOUNT：現在までの取得行数
    DBMS_OUTPUT.PUT_LINE(cur%ROWCOUNT || ': ' || v_row.name);
  END LOOP;

  DBMS_OUTPUT.PUT_LINE('取得総行数: ' || cur%ROWCOUNT);
  CLOSE cur;
END;

BEGIN
  -- UPDATE実行
  UPDATE users SET status = 'inactive'
   WHERE last_login < SYSDATE - 365;

  -- 直前SQLの暗黙カーソル属性を参照
  DBMS_OUTPUT.PUT_LINE('更新行数: ' || SQL%ROWCOUNT);

  -- 0件なら処理を分岐
  IF SQL%NOTFOUND THEN
    DBMS_OUTPUT.PUT_LINE('更新対象なし');
  END IF;
END;

DECLARE
  -- テーブル%ROWTYPE
  v_user users%ROWTYPE;
  -- ↑ usersテーブルの全カラムを持つ構造体

  -- カーソル%ROWTYPE（実務推奨）
  CURSOR cur_users IS
    SELECT id, email, created_at FROM users;
  v_row cur_users%ROWTYPE;
  -- ↑ カーソルのSELECT列だけを持つ構造体（テーブル全体より軽量）
BEGIN
  -- テーブル行を丸ごと取得
  SELECT * INTO v_user FROM users WHERE id = 1;
  DBMS_OUTPUT.PUT_LINE(v_user.email);

  -- カーソル経由
  OPEN cur_users;
  LOOP
    FETCH cur_users INTO v_row;
    EXIT WHEN cur_users%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(v_row.id || ': ' || v_row.email);
  END LOOP;
  CLOSE cur_users;
END;

DECLARE
  v_user users%ROWTYPE;
BEGIN
  -- レコードを組み立てて
  v_user.id := 100;
  v_user.email := 'test@example.com';
  v_user.created_at := SYSDATE;

  -- レコード単位のINSERT
  INSERT INTO users VALUES v_user;

  -- レコード単位のUPDATE
  v_user.email := 'new@example.com';
  UPDATE users SET ROW = v_user WHERE id = v_user.id;

  COMMIT;
END;

DECLARE
  -- 複数の引数可
  CURSOR cur_users(
    p_status   VARCHAR2,
    p_min_age  NUMBER DEFAULT 0   -- デフォルト値も可
  ) IS
    SELECT id, email FROM users
    WHERE status = p_status AND age >= p_min_age;
BEGIN
  -- 条件を変えて何度も使い回せる
  FOR rec IN cur_users('active') LOOP
    DBMS_OUTPUT.PUT_LINE('active: ' || rec.email);
  END LOOP;

  FOR rec IN cur_users('pending', 18) LOOP
    DBMS_OUTPUT.PUT_LINE('pending: ' || rec.email);
  END LOOP;

  FOR rec IN cur_users('suspended') LOOP
    DBMS_OUTPUT.PUT_LINE('suspended: ' || rec.email);
  END LOOP;
END;

DECLARE
  CURSOR cur_pending IS
    SELECT id, amount FROM orders WHERE status = 'pending'
    FOR UPDATE;   -- 該当行を排他ロック
BEGIN
  FOR rec IN cur_pending LOOP
    -- この時点で rec の行はロック済
    IF rec.amount > 10000 THEN
      UPDATE orders SET status = 'review_required'
      WHERE CURRENT OF cur_pending;   -- 現在のカーソル行を更新
    ELSE
      UPDATE orders SET status = 'approved'
      WHERE CURRENT OF cur_pending;
    END IF;
  END LOOP;

  COMMIT;
END;

-- NOWAIT：ロック競合時に即エラー（ORA-00054）
... FOR UPDATE NOWAIT;

-- SKIP LOCKED：ロック中の行を飛ばす（キュー処理に最適）
... FOR UPDATE SKIP LOCKED;

-- WAIT 5：5秒まで待機
... FOR UPDATE WAIT 5;

-- 特定のテーブルだけロック（JOIN時）
... FOR UPDATE OF orders.status;

-- 複数ワーカーが同じキューを並列処理する場合
DECLARE
  CURSOR cur_jobs IS
    SELECT id, payload FROM job_queue
    WHERE status = 'pending'
    ORDER BY priority DESC, created_at
    FOR UPDATE SKIP LOCKED;   -- 他ワーカーが処理中の行はスキップ
BEGIN
  FOR rec IN cur_jobs LOOP
    -- 自分専用の行として処理
    process_job(rec.id, rec.payload);
    
    UPDATE job_queue SET status = 'done'
    WHERE CURRENT OF cur_jobs;
  END LOOP;
  COMMIT;
END;

DECLARE
  TYPE t_users IS TABLE OF users%ROWTYPE;
  v_users t_users;

  CURSOR cur IS SELECT * FROM users WHERE status = 'pending';
BEGIN
  OPEN cur;
  LOOP
    FETCH cur BULK COLLECT INTO v_users LIMIT 1000;
    EXIT WHEN v_users.COUNT = 0;

    -- 1000件ずつ処理
    FORALL i IN 1..v_users.COUNT
      UPDATE users SET status = 'active'
      WHERE id = v_users(i).id;

    COMMIT;    -- 1000件ごとにCOMMIT
  END LOOP;
  CLOSE cur;
END;

-- ❌ LIMIT無しでBULK COLLECT
FETCH cur BULK COLLECT INTO v_users;
-- → 全件をメモリにロード
-- → 1000万行ならPGAメモリ数GB消費でORA-04030

-- ⭕ LIMIT 1000〜10000で分割
FETCH cur BULK COLLECT INTO v_users LIMIT 1000;
-- → 1回1000行ずつで安定処理

CREATE OR REPLACE PROCEDURE get_users(
  p_status IN  VARCHAR2,
  p_rc     OUT SYS_REFCURSOR
) IS
BEGIN
  OPEN p_rc FOR
    SELECT id, email FROM users WHERE status = p_status;
  -- CLOSEは呼び出し側の責任
END;
/

-- 呼び出し側（PL/SQL）
DECLARE
  v_rc   SYS_REFCURSOR;
  v_id   users.id%TYPE;
  v_mail users.email%TYPE;
BEGIN
  get_users('active', v_rc);
  LOOP
    FETCH v_rc INTO v_id, v_mail;
    EXIT WHEN v_rc%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(v_id || ': ' || v_mail);
  END LOOP;
  CLOSE v_rc;   -- 閉じる責任
END;

DECLARE
  CURSOR cur IS SELECT id FROM users;
  v_id users.id%TYPE;
BEGIN
  OPEN cur;
  LOOP
    FETCH cur INTO v_id;
    EXIT WHEN cur%NOTFOUND;

    -- 例外が出そうな処理
    process_user(v_id);
  END LOOP;
  CLOSE cur;
EXCEPTION
  WHEN OTHERS THEN
    -- EXCEPTION節でもCLOSE確認
    IF cur%ISOPEN THEN
      CLOSE cur;
    END IF;
    log_error(SQLCODE, SQLERRM, DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
    RAISE;
END;

-- 暗黙カーソル：NO_DATA_FOUND例外が発生する
BEGIN
  SELECT name INTO v_name FROM users WHERE id = 9999;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('対象なし');
END;

-- 明示カーソル：NO_DATA_FOUNDは発生しない
-- 代わりに %NOTFOUND が TRUE になる
DECLARE
  CURSOR cur IS SELECT name FROM users WHERE id = 9999;
  v_name users.name%TYPE;
BEGIN
  OPEN cur;
  FETCH cur INTO v_name;
  IF cur%NOTFOUND THEN
    DBMS_OUTPUT.PUT_LINE('対象なし');
  END IF;
  CLOSE cur;
END;

DECLARE
  v_total NUMBER := 0;
BEGIN
  FOR rec IN (
    SELECT amount FROM orders WHERE status = 'paid'
  ) LOOP
    v_total := v_total + rec.amount;
  END LOOP;
  DBMS_OUTPUT.PUT_LINE('合計: ' || v_total);
END;

DECLARE
  CURSOR cur_by_dept(p_dept NUMBER) IS
    SELECT id, name FROM employees WHERE dept_id = p_dept;
BEGIN
  FOR rec IN cur_by_dept(10) LOOP
    process_emp(rec.id);
  END LOOP;

  FOR rec IN cur_by_dept(20) LOOP
    process_emp(rec.id);
  END LOOP;
END;

DECLARE
  TYPE t_ids IS TABLE OF orders.id%TYPE;
  v_ids t_ids;
  CURSOR cur IS SELECT id FROM orders WHERE status = 'expired';
BEGIN
  OPEN cur;
  LOOP
    FETCH cur BULK COLLECT INTO v_ids LIMIT 5000;
    EXIT WHEN v_ids.COUNT = 0;

    FORALL i IN 1..v_ids.COUNT
      DELETE FROM orders WHERE id = v_ids(i);
    COMMIT;
  END LOOP;
  CLOSE cur;
END;

BEGIN
  FOR rec IN (
    SELECT id, amount FROM transactions WHERE status = 'pending'
    FOR UPDATE
  ) LOOP
    IF rec.amount > 10000 THEN
      UPDATE transactions SET status = 'hold' WHERE CURRENT OF ???;
      -- ↑ Cursor FOR Loopでは WHERE CURRENT OF 不可
      -- 代わりに id 指定で更新
      UPDATE transactions SET status = 'hold' WHERE id = rec.id;
    END IF;
  END LOOP;
  COMMIT;
END;

DECLARE
  CURSOR cur_jobs IS
    SELECT id, payload FROM job_queue
    WHERE status = 'pending'
    ORDER BY priority DESC
    FOR UPDATE SKIP LOCKED;
BEGIN
  FOR rec IN cur_jobs LOOP
    process_job(rec.id, rec.payload);
    UPDATE job_queue SET status = 'done' WHERE CURRENT OF cur_jobs;
  END LOOP;
  COMMIT;
END;

BEGIN
  FOR dept IN (SELECT id, name FROM departments) LOOP
    DBMS_OUTPUT.PUT_LINE('部署: ' || dept.name);

    FOR emp IN (
      SELECT id, name FROM employees WHERE dept_id = dept.id
    ) LOOP
      DBMS_OUTPUT.PUT_LINE('  ' || emp.name);
    END LOOP;
  END LOOP;
END;

DECLARE
  CURSOR cur IS SELECT id FROM users;
BEGIN
  OPEN cur;
  -- 処理
  CLOSE cur;
EXCEPTION
  WHEN OTHERS THEN
    IF cur%ISOPEN THEN CLOSE cur; END IF;
    RAISE;
END;

CREATE OR REPLACE FUNCTION get_top_users RETURN SYS_REFCURSOR IS
  v_rc SYS_REFCURSOR;
BEGIN
  OPEN v_rc FOR
    SELECT id, email, created_at FROM users
    ORDER BY created_at DESC
    FETCH FIRST 10 ROWS ONLY;
  RETURN v_rc;
END;

-- 在庫引当の排他制御
DECLARE
  v_stock products.stock%TYPE;
BEGIN
  SELECT stock INTO v_stock
  FROM products
  WHERE id = p_id
  FOR UPDATE;     -- ここで行ロック取得

  IF v_stock < p_qty THEN
    RAISE_APPLICATION_ERROR(-20001, '在庫不足');
  END IF;

  UPDATE products SET stock = stock - p_qty WHERE id = p_id;
  COMMIT;
END;

BEGIN
  FOR rec IN (
    SELECT * FROM users_staging WHERE imported = 0
  ) LOOP
    INSERT INTO users VALUES rec;   -- レコード単位INSERT
    UPDATE users_staging SET imported = 1 WHERE id = rec.id;
  END LOOP;
  COMMIT;
END;