【Oracle】DBMS_SQL完全ガイド｜動的 SQL のカーソル操作・列数不定の SELECT・EXECUTE IMMEDIATE との使い分けまで解説

DECLARE
    v_cursor     INTEGER;
    v_rows_aff   INTEGER;
    v_table_name VARCHAR2(30) := 'EMPLOYEES';
    v_sql        VARCHAR2(500);
BEGIN
    -- SQL 文を組み立てる
    v_sql := 'UPDATE ' || v_table_name ||
             ' SET salary = salary * 1.1 WHERE department_id = :dept_id';

    -- ① OPEN_CURSOR でカーソルを開く
    v_cursor := DBMS_SQL.OPEN_CURSOR;

    -- ② PARSE で SQL をパースする
    DBMS_SQL.PARSE(
        c             => v_cursor,
        statement     => v_sql,
        language_flag => DBMS_SQL.NATIVE   -- NATIVE: データベースのデフォルトSQL方言
    );

    -- ③ BIND_VARIABLE でバインド変数に値をセットする
    DBMS_SQL.BIND_VARIABLE(v_cursor, ':dept_id', 50);

    -- ④ EXECUTE で SQL を実行する
    v_rows_aff := DBMS_SQL.EXECUTE(v_cursor);

    DBMS_OUTPUT.PUT_LINE(v_rows_aff || ' 行を更新しました');

    -- ⑤ CLOSE_CURSOR でカーソルを閉じる（必須）
    DBMS_SQL.CLOSE_CURSOR(v_cursor);

    COMMIT;

EXCEPTION
    WHEN OTHERS THEN
        -- 例外時もカーソルを閉じる（リソースリークを防ぐ）
        IF DBMS_SQL.IS_OPEN(v_cursor) THEN
            DBMS_SQL.CLOSE_CURSOR(v_cursor);
        END IF;
        RAISE;
END;
/

DECLARE
    v_cursor     INTEGER;
    v_col_cnt    INTEGER;
    v_col_desc   DBMS_SQL.DESC_TAB2;   -- 列メタデータを格納する型
    v_col_value  VARCHAR2(4000);
    v_ret        INTEGER;
    v_sql        VARCHAR2(500);
BEGIN
    -- 実行時まで列数・列名が不明な SQL（例: ユーザーが任意の SELECT を入力）
    v_sql := 'SELECT employee_id, last_name, salary FROM employees WHERE rownum <= 5';

    v_cursor := DBMS_SQL.OPEN_CURSOR;
    DBMS_SQL.PARSE(v_cursor, v_sql, DBMS_SQL.NATIVE);

    -- EXECUTE してから DESCRIBE_COLUMNS で列情報を取得する
    v_ret := DBMS_SQL.EXECUTE(v_cursor);
    DBMS_SQL.DESCRIBE_COLUMNS2(v_cursor, v_col_cnt, v_col_desc);

    DBMS_OUTPUT.PUT_LINE('列数: ' || v_col_cnt);

    -- 各列に VARCHAR2 バッファを定義する（型変換は後で行う）
    FOR i IN 1..v_col_cnt LOOP
        DBMS_OUTPUT.PUT_LINE(
            '列 ' || i || ': ' || v_col_desc(i).col_name ||
            ' (type=' || v_col_desc(i).col_type || ')');

        -- DEFINE_COLUMN で各列の受け取りバッファを設定する
        -- すべて VARCHAR2 で受け取る（汎用的な処理のため）
        DBMS_SQL.DEFINE_COLUMN(v_cursor, i, v_col_value, 4000);
    END LOOP;

    -- FETCH_ROWS で行を取得する
    WHILE DBMS_SQL.FETCH_ROWS(v_cursor) > 0 LOOP
        FOR i IN 1..v_col_cnt LOOP
            -- COLUMN_VALUE で各列の値を取り出す
            DBMS_SQL.COLUMN_VALUE(v_cursor, i, v_col_value);
            DBMS_OUTPUT.PUT(v_col_desc(i).col_name || '=' || NVL(v_col_value, 'NULL') || ' ');
        END LOOP;
        DBMS_OUTPUT.NEW_LINE;
    END LOOP;

    DBMS_SQL.CLOSE_CURSOR(v_cursor);

EXCEPTION
    WHEN OTHERS THEN
        IF DBMS_SQL.IS_OPEN(v_cursor) THEN
            DBMS_SQL.CLOSE_CURSOR(v_cursor);
        END IF;
        RAISE;
END;
/

-- 動的 SQL の結果を REF CURSOR で返すファンクション
CREATE OR REPLACE FUNCTION get_dynamic_result(
    p_table_name  IN VARCHAR2,
    p_where_clause IN VARCHAR2 DEFAULT NULL
) RETURN SYS_REFCURSOR AS
    v_cursor   INTEGER;
    v_ref_cur  SYS_REFCURSOR;
    v_sql      VARCHAR2(4000);
    v_ret      INTEGER;
BEGIN
    -- テーブル名をホワイトリストチェックする（SQLインジェクション対策）
    IF p_table_name NOT IN ('EMPLOYEES', 'DEPARTMENTS', 'ORDERS') THEN
        RAISE_APPLICATION_ERROR(-20001, '無効なテーブル名: ' || p_table_name);
    END IF;

    v_sql := 'SELECT * FROM ' || p_table_name;
    IF p_where_clause IS NOT NULL THEN
        v_sql := v_sql || ' WHERE ' || p_where_clause;
    END IF;

    v_cursor := DBMS_SQL.OPEN_CURSOR;
    DBMS_SQL.PARSE(v_cursor, v_sql, DBMS_SQL.NATIVE);
    v_ret := DBMS_SQL.EXECUTE(v_cursor);

    -- DBMS_SQL カーソルを SYS_REFCURSOR に変換する（Oracle 11g 以降）
    v_ref_cur := DBMS_SQL.TO_REFCURSOR(v_cursor);
    -- TO_REFCURSOR を呼んだ後は CLOSE_CURSOR を呼ばない
    -- （REF CURSOR のクローズと二重になる）

    RETURN v_ref_cur;

EXCEPTION
    WHEN OTHERS THEN
        IF DBMS_SQL.IS_OPEN(v_cursor) THEN
            DBMS_SQL.CLOSE_CURSOR(v_cursor);
        END IF;
        RAISE;
END get_dynamic_result;
/