【PL/SQL】REF CURSORで柔軟なデータ取得を実装｜SYS_REFCURSOR・強い型付け・Java/Python/.NET連携・動的SQL・パイプライン統合

-- ① 静的カーソル（PL/SQLブロック内のみ）
DECLARE
  CURSOR cur_emp IS SELECT employee_id, last_name FROM employees;
BEGIN
  FOR rec IN cur_emp LOOP
    DBMS_OUTPUT.PUT_LINE(rec.employee_id || ': ' || rec.last_name);
  END LOOP;
END;
/

-- ② SYS_REFCURSOR（柔軟・どんなSELECTでも紐付け可）
CREATE OR REPLACE PROCEDURE get_data(p_cur OUT SYS_REFCURSOR) AS
BEGIN
  OPEN p_cur FOR SELECT employee_id, last_name FROM employees;
  -- 列構成は呼出し側で受け取って解釈する
END;
/

-- ③ 強い型付けREF CURSOR（コンパイル時の列構成チェック）
CREATE OR REPLACE PACKAGE pkg_types AS
  TYPE t_emp_rc IS REF CURSOR RETURN employees%ROWTYPE;
END;
/

CREATE OR REPLACE PROCEDURE get_emps(p_cur OUT pkg_types.t_emp_rc) AS
BEGIN
  -- ✅ employees%ROWTYPE と同じ列構成のSELECTでないとコンパイルエラー
  OPEN p_cur FOR SELECT * FROM employees;

  -- ❌ 一致しないSELECTはコンパイル時に弾かれる
  -- OPEN p_cur FOR SELECT employee_id, last_name FROM employees;
END;
/

import oracle.jdbc.OracleTypes;

try (Connection conn = DriverManager.getConnection(URL, USER, PASS);
     CallableStatement cs = conn.prepareCall(
       "BEGIN pkg_customer_rc.open_active(?, ?); END;")) {
  cs.setLong(1, 100);
  cs.registerOutParameter(2, OracleTypes.CURSOR);   // ← REF CURSOR用
  cs.execute();

  try (ResultSet rs = (ResultSet) cs.getObject(2)) {
    while (rs.next()) {
      long id = rs.getLong("id");
      String name = rs.getString("name");
      System.out.println(id + ": " + name);
    }
  }
}

import oracledb

with oracledb.connect(user=USER, password=PASS, dsn=DSN) as conn:
    with conn.cursor() as cur:
        ref_cur = cur.var(oracledb.CURSOR)
        cur.callproc("pkg_customer_rc.open_active", [100, ref_cur])

        # ref_cur.getvalue() で結果カーソルを取り出す
        result_cursor = ref_cur.getvalue()
        for row in result_cursor:
            print(row)   # (id, name, email) のタプル
        result_cursor.close()

using Oracle.ManagedDataAccess.Client;
using Oracle.ManagedDataAccess.Types;

using (var conn = new OracleConnection(connStr))
{
    conn.Open();
    using (var cmd = new OracleCommand("pkg_customer_rc.open_active", conn))
    {
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.Add("p_min_id", OracleDbType.Int64).Value = 100;
        cmd.Parameters.Add("p_cur", OracleDbType.RefCursor)
                      .Direction = ParameterDirection.Output;
        cmd.ExecuteNonQuery();

        using (var reader = ((OracleRefCursor)cmd.Parameters["p_cur"].Value)
                              .GetDataReader())
        {
            while (reader.Read())
            {
                Console.WriteLine($"{reader["id"]}: {reader["name"]}");
            }
        }
    }
}

-- 検索条件を動的に組み立て、結果セットを返す
CREATE OR REPLACE PROCEDURE search_customers(
  p_country IN  VARCHAR2,
  p_min_age IN  NUMBER,
  p_active  IN  NUMBER,
  p_cur     OUT SYS_REFCURSOR
) AS
  v_sql VARCHAR2(4000);
BEGIN
  v_sql := q'[
    SELECT customer_id, customer_name, country, age, active
      FROM customers
     WHERE 1 = 1
  ]';

  -- 動的にWHEREを追加（バインド変数を活用）
  IF p_country IS NOT NULL THEN
    v_sql := v_sql || ' AND country = :v_country';
  END IF;
  IF p_min_age IS NOT NULL THEN
    v_sql := v_sql || ' AND age >= :v_min_age';
  END IF;
  IF p_active IS NOT NULL THEN
    v_sql := v_sql || ' AND active = :v_active';
  END IF;

  -- ❌ 注意: USINGは「実際にバインドされる順番＝SQLに登場する順番」
  -- 条件によって付与有無が変わるならUSING句も対応する必要がある
  -- 実装的にはバインド常時すべて指定する形（NULL許容にする）が安全
  v_sql := q'[
    SELECT customer_id, customer_name, country, age, active
      FROM customers
     WHERE (:v_country IS NULL OR country = :v_country)
       AND (:v_min_age IS NULL OR age >= :v_min_age)
       AND (:v_active  IS NULL OR active = :v_active)
  ]';

  OPEN p_cur FOR v_sql
    USING p_country, p_country,
          p_min_age, p_min_age,
          p_active,  p_active;
END;
/

-- 行型の定義
CREATE OR REPLACE TYPE t_sales_row AS OBJECT(
  sales_id    NUMBER,
  product_cd  VARCHAR2(20),
  amount_inc  NUMBER       -- 税込加算後
);
/
CREATE OR REPLACE TYPE t_sales_tab IS TABLE OF t_sales_row;
/

-- REF CURSOR入力のパイプライン関数（並列対応）
CREATE OR REPLACE FUNCTION f_apply_tax(
  p_cur IN SYS_REFCURSOR
) RETURN t_sales_tab
  PIPELINED PARALLEL_ENABLE(PARTITION p_cur BY ANY) AS
  rec raw_sales%ROWTYPE;
BEGIN
  LOOP
    FETCH p_cur INTO rec;
    EXIT WHEN p_cur%NOTFOUND;
    PIPE ROW(t_sales_row(
      rec.sales_id, rec.product_cd, rec.amount * 1.10
    ));
  END LOOP;
  CLOSE p_cur;
  RETURN;
END;
/

-- 利用：CURSOR(SELECT ...) で REF CURSOR を流し込む
INSERT INTO sales_with_tax
SELECT * FROM TABLE(f_apply_tax(
  CURSOR(SELECT * FROM raw_sales)
));

DECLARE
  v_cur SYS_REFCURSOR;
  TYPE t_rec IS TABLE OF customers%ROWTYPE;
  v_buf t_rec;
  C_LIMIT CONSTANT PLS_INTEGER := 1000;
  v_total NUMBER := 0;
BEGIN
  pkg_customer_rc.open_active(p_min_id => 100, p_cur => v_cur);

  LOOP
    FETCH v_cur BULK COLLECT INTO v_buf LIMIT C_LIMIT;
    EXIT WHEN v_buf.COUNT = 0;

    -- まとめて処理（FORALLでDMLしてもOK）
    FOR i IN 1 .. v_buf.COUNT LOOP
      v_total := v_total + 1;
      -- 何か処理
    END LOOP;
  END LOOP;
  CLOSE v_cur;

  DBMS_OUTPUT.PUT_LINE('処理件数: ' || v_total);
EXCEPTION
  WHEN OTHERS THEN
    IF v_cur%ISOPEN THEN CLOSE v_cur; END IF;
    RAISE;
END;
/

CREATE OR REPLACE PROCEDURE get_order_full(
  p_order_id IN  NUMBER,
  p_header   OUT SYS_REFCURSOR,
  p_items    OUT SYS_REFCURSOR
) AS
BEGIN
  -- ヘッダ情報
  OPEN p_header FOR
    SELECT order_id, customer_id, order_date, status, total_amount
      FROM orders
     WHERE order_id = p_order_id;

  -- 明細情報
  OPEN p_items FOR
    SELECT line_no, product_id, quantity, unit_price, line_amount
      FROM order_items
     WHERE order_id = p_order_id
     ORDER BY line_no;
END;
/

-- Java側はOUTパラメータを2つ受け取って順に処理
-- cs.registerOutParameter(2, OracleTypes.CURSOR);
-- cs.registerOutParameter(3, OracleTypes.CURSOR);
-- ResultSet rs1 = (ResultSet) cs.getObject(2);
-- ResultSet rs2 = (ResultSet) cs.getObject(3);

-- ✅ セーフCLOSEパターン
DECLARE
  v_cur SYS_REFCURSOR;
  TYPE t_rec IS TABLE OF customers%ROWTYPE;
  v_buf t_rec;
BEGIN
  pkg_customer_rc.open_active(100, v_cur);

  LOOP
    FETCH v_cur BULK COLLECT INTO v_buf LIMIT 1000;
    EXIT WHEN v_buf.COUNT = 0;
    -- 処理
  END LOOP;

  CLOSE v_cur;                     -- 通常終了でのCLOSE
EXCEPTION
  WHEN OTHERS THEN
    IF v_cur%ISOPEN THEN
      CLOSE v_cur;                 -- 例外時もCLOSEを保証
    END IF;
    RAISE;
END;
/

-- ❌ アンチパターン: CLOSE忘れ
DECLARE
  v_cur SYS_REFCURSOR;
  v_id  NUMBER;
BEGIN
  pkg_customer_rc.open_active(100, v_cur);
  FETCH v_cur INTO v_id;
  -- ↓ CLOSE がない！セッション内でカーソルが残り続ける
  -- 大量に呼ぶと ORA-01000 (max open cursors exceeded)
END;
/