【Oracle】EXECUTE IMMEDIATE（動的SQL）完全ガイド｜DDL実行・バインド変数USING・RETURNING INTO・BULK COLLECT・DBMS_SQL比較・SQLインジェクション対策まで実例で解説

-- ① DDL / DML（戻り値なし）
EXECUTE IMMEDIATE 'SQL文字列';

-- ② バインド変数で値を渡す（USING 句）
EXECUTE IMMEDIATE 'SQL文字列' USING 変数1, 変数2, ...;

-- ③ SELECT の結果を受け取る（INTO 句）
EXECUTE IMMEDIATE 'SELECT ...' INTO 変数 [USING 入力変数];

-- ④ DML の影響行を受け取る（RETURNING INTO）
EXECUTE IMMEDIATE 'DML文' RETURNING INTO 変数 [USING 入力変数];

-- ⑤ 複数行を一括取得（BULK COLLECT INTO）
EXECUTE IMMEDIATE 'SELECT ...' BULK COLLECT INTO コレクション変数;

-- ⑥ REF CURSOR として開く（OPEN FOR）
OPEN カーソル変数 FOR 'SELECT ...' [USING 変数];

BEGIN
    -- テーブル作成（静的 SQL では PL/SQL ブロック内に CREATE は書けない）
    EXECUTE IMMEDIATE '
        CREATE TABLE work_log (
            log_id   NUMBER GENERATED ALWAYS AS IDENTITY,
            log_date DATE DEFAULT SYSDATE,
            message  VARCHAR2(4000)
        )';

    -- TRUNCATE（DELETE と違いロールバック不可。DDL なので即コミット）
    EXECUTE IMMEDIATE 'TRUNCATE TABLE work_log';

    -- DROP（存在チェックなしで DROP すると ORA-00942 が発生するため注意）
    EXECUTE IMMEDIATE 'DROP TABLE work_log';

EXCEPTION
    WHEN OTHERS THEN
        -- ORA-00942: テーブルまたはビューが存在しません
        IF SQLCODE = -942 THEN
            DBMS_OUTPUT.PUT_LINE('テーブルが存在しません: ' || SQLERRM);
        ELSE
            RAISE;
        END IF;
END;
/

CREATE OR REPLACE PROCEDURE truncate_table (
    p_table_name IN VARCHAR2
) AS
    v_sql VARCHAR2(200);
BEGIN
    -- テーブル名の入力検証（英数字とアンダースコアのみ許可）
    IF NOT REGEXP_LIKE(p_table_name, '^[A-Za-z0-9_]+$') THEN
        RAISE_APPLICATION_ERROR(-20001,
            '不正なテーブル名: ' || p_table_name);
    END IF;

    -- テーブル名を文字列連結して SQL を組み立てる
    -- DBMS_ASSERT.SIMPLE_SQL_NAME でスキーマオブジェクト名を検証するのも有効
    v_sql := 'TRUNCATE TABLE ' || DBMS_ASSERT.SIMPLE_SQL_NAME(p_table_name);
    EXECUTE IMMEDIATE v_sql;

    DBMS_OUTPUT.PUT_LINE(p_table_name || ' を TRUNCATE しました');
END truncate_table;
/

-- 呼び出し例
BEGIN
    truncate_table('WORK_LOG');
    truncate_table('STAGING_DATA');
END;
/

DECLARE
    v_dept_id   NUMBER := 50;
    v_min_sal   NUMBER := 300000;
    v_emp_name  VARCHAR2(100);
    v_salary    NUMBER;
    v_sql       VARCHAR2(500);
BEGIN
    -- IN パラメータ: USING で値を渡す（:1, :2 はプレースホルダ）
    v_sql := 'SELECT last_name, salary FROM employees '
          || 'WHERE department_id = :1 AND salary > :2 '
          || 'AND ROWNUM = 1 ORDER BY salary DESC';

    EXECUTE IMMEDIATE v_sql
        INTO v_emp_name, v_salary
        USING v_dept_id, v_min_sal;   -- :1=v_dept_id, :2=v_min_sal の順で渡す

    DBMS_OUTPUT.PUT_LINE(v_emp_name || ': ' || v_salary);

    -- OUT パラメータ（プロシージャ呼び出しで使用）
    DECLARE
        v_out NUMBER;
    BEGIN
        EXECUTE IMMEDIATE 'BEGIN :out := :in * 2; END;'
            USING OUT v_out, IN 100;   -- OUT/IN を明示できる
        DBMS_OUTPUT.PUT_LINE('結果: ' || v_out);  -- 200
    END;
END;
/

DECLARE
    v_emp_id    NUMBER := 1001;
    v_new_sal   NUMBER := 550000;
    v_old_sal   NUMBER;
    v_sql       VARCHAR2(200);
BEGIN
    -- UPDATE した後の値や連番を RETURNING INTO で受け取る
    v_sql := 'UPDATE employees SET salary = :new_sal '
          || 'WHERE employee_id = :emp_id '
          || 'RETURNING salary INTO :old_sal';

    -- RETURNING INTO には OUT を明示
    EXECUTE IMMEDIATE v_sql
        USING v_new_sal, v_emp_id, OUT v_old_sal;

    DBMS_OUTPUT.PUT_LINE('更新後の給与: ' || v_old_sal);

    -- INSERT ... RETURNING で採番した ID を取得する例
    DECLARE
        v_new_id NUMBER;
    BEGIN
        EXECUTE IMMEDIATE
            'INSERT INTO orders (order_date, customer_id) '
         || 'VALUES (SYSDATE, :cid) RETURNING order_id INTO :oid'
        USING 999, OUT v_new_id;
        DBMS_OUTPUT.PUT_LINE('新規注文ID: ' || v_new_id);
    END;
END;
/

DECLARE
    TYPE t_names IS TABLE OF employees.last_name%TYPE;
    TYPE t_sals  IS TABLE OF employees.salary%TYPE;

    v_names t_names;
    v_sals  t_sals;
    v_dept  NUMBER := 50;
    v_sql   VARCHAR2(300);
BEGIN
    v_sql := 'SELECT last_name, salary FROM employees '
          || 'WHERE department_id = :1 ORDER BY salary DESC';

    -- BULK COLLECT INTO で複数行を配列に取得
    EXECUTE IMMEDIATE v_sql
        BULK COLLECT INTO v_names, v_sals
        USING v_dept;

    DBMS_OUTPUT.PUT_LINE('取得件数: ' || v_names.COUNT);
    FOR i IN 1 .. v_names.COUNT LOOP
        DBMS_OUTPUT.PUT_LINE(v_names(i) || ': ' || v_sals(i));
    END LOOP;
END;
/

-- 動的 SQL を SYS_REFCURSOR で返すプロシージャ
CREATE OR REPLACE PROCEDURE get_rows_dynamic (
    p_table_name IN  VARCHAR2,
    p_where      IN  VARCHAR2 DEFAULT NULL,
    p_cursor     OUT SYS_REFCURSOR
) AS
    v_sql VARCHAR2(1000);
BEGIN
    -- テーブル名を検証（DBMS_ASSERT でオブジェクト名を安全に引用符付き変換）
    v_sql := 'SELECT * FROM ' || DBMS_ASSERT.SCHEMA_NAME(USER)
          || '.' || DBMS_ASSERT.SIMPLE_SQL_NAME(p_table_name);

    IF p_where IS NOT NULL THEN
        -- WHERE 句を追加する場合は値の部分はバインド変数を使うべきだが、
        -- 列名・演算子部分の検証は呼び出し側の責任
        v_sql := v_sql || ' WHERE ' || p_where;
    END IF;

    OPEN p_cursor FOR v_sql;   -- OPEN FOR で動的 SQL をカーソルとして開く
END get_rows_dynamic;
/

-- NG: ユーザー入力を直接連結すると危険
DECLARE
    v_user_input VARCHAR2(100) := 'Smith' || ' OR 1=1 --';  -- 悪意ある入力例
    v_sql        VARCHAR2(500);
    v_count      NUMBER;
BEGIN
    -- 組み立てられる SQL: SELECT COUNT(*) FROM employees WHERE last_name='Smith' OR 1=1 --'
    -- → WHERE が無効化され全件カウントが返る
    v_sql := 'SELECT COUNT(*) FROM employees WHERE last_name = ''' || v_user_input || '''';
    EXECUTE IMMEDIATE v_sql INTO v_count;
    DBMS_OUTPUT.PUT_LINE(v_count);  -- 本来の件数ではなく全件数が返る
END;
/

-- OK: 値はバインド変数（USING）で渡す → SQL の構造を変えられない
DECLARE
    v_user_input VARCHAR2(100) := 'Smith' || ' OR 1=1 --';  -- 同じ悪意ある入力
    v_count      NUMBER;
BEGIN
    -- バインド変数なら入力値全体が1つの文字列値として扱われる
    -- 組み立てられる SQL: SELECT COUNT(*) FROM employees WHERE last_name = :1
    -- :1 の値は 'Smith' OR 1=1 -- という文字列として安全に処理される
    EXECUTE IMMEDIATE
        'SELECT COUNT(*) FROM employees WHERE last_name = :1'
        INTO v_count
        USING v_user_input;
    DBMS_OUTPUT.PUT_LINE(v_count);  -- 正しく 0 が返る
END;
/