【PL/SQL】動的SQLのバインド変数設計｜カーソル共有とハードパースを改善

【PL/SQL】動的SQLとBIND変数の最適設計：SQL Injection防止とキャッシュ効率の両立 PL/SQL
2025.10.14

PL/SQLの動的SQLでは、値を文字列連結するとSQLインジェクションの原因になるだけでなく、値が変わるたびにSQLテキストも変わり、ハードパースと子カーソルの増加を招きます。安全性と性能を同時に改善する基本は、値をバインドし、識別子は許可リストで制限することです。

この記事では、動的SQLを性能面から設計するために、EXECUTE IMMEDIATEの正しい入出力バインド、可変長IN条件、FORALL、カーソル共有の診断までを実行可能な形で整理します。攻撃例やセキュリティテストを詳しく確認したい場合は、動的SQLのセキュアな書き方完全ガイドを参照してください。

先に結論

  • 静的SQLで書ける処理は静的SQLを使い、構造が変わる場合だけ動的SQLを選びます。
  • 検索値や更新値は必ずバインドし、表名・列名・並び順は許可リストで決定します。
  • DMLの戻り値は RETURNING INTO、複数行SELECTは BULK COLLECT INTO またはREF CURSORで受け取ります。
  • 可変長IN条件はプレースホルダの個数を変えず、JSON_TABLEや一時表で値集合を渡します。
  • parse_calls、version_count、V$SQL_SHARED_CURSORを確認し、共有できない理由を特定します。
スポンサーリンク

静的SQL・EXECUTE IMMEDIATE・DBMS_SQLを使い分ける

値だけが変わる処理に動的SQLは不要です。静的SQLはコンパイル時にオブジェクト名や権限を検査でき、依存関係も追跡されます。表名や列構成などSQLの構造が実行時に変わる場合に限って、動的SQLを選びます。

  • 静的SQL：表・列・条件構造が固定で、値だけが変わる処理
  • EXECUTE IMMEDIATE：列構成が既知の単発DML、DDL、単一行または一括取得
  • OPEN FOR：動的SELECTの結果を複数行ずつ処理する場合
  • DBMS_SQL：列数やデータ型が実行時まで分からない汎用ツール

値はバインドし、RETURNINGは専用句で受け取る

DML内の入力値は USING へ渡し、RETURNINGで返す値は動的SQL文の外側にある RETURNING INTO で受け取ります。出力値を USING OUT へ混在させないことが重要です。

execute-immediate-returning.sql
DECLARE
  v_customer_id app.orders.customer_id%TYPE := 1101;
  v_amount      app.orders.amount%TYPE := 1200.50;
  v_order_id    app.orders.order_id%TYPE;
  v_sql         VARCHAR2(1000) :=
    'INSERT INTO app.orders(order_id, customer_id, amount)
     VALUES (orders_seq.NEXTVAL, :customer_id, :amount)
     RETURNING order_id INTO :order_id';
BEGIN
  EXECUTE IMMEDIATE v_sql
    USING v_customer_id, v_amount
    RETURNING INTO v_order_id;

  DBMS_OUTPUT.PUT_LINE('created order_id=' || v_order_id);
END;
/
プレースホルダ名への注意

SQL文中の :customer_id などの名前は読みやすさのためのものです。SQL文では原則として出現順に USING の値が対応します。同じ名前を複数回書いても、自動的に1つの値へまとめられるとは限りません。

複数行の動的SELECTはBULK COLLECTまたはOPEN FORで取得する

EXECUTE IMMEDIATEをカーソルFORループの問い合わせ部分へ直接書くことはできません。結果を一括取得できる件数なら BULK COLLECT INTO、大量データを順次処理するなら OPEN FOR と FETCH を使います。

dynamic-select-multiple-rows.sql
DECLARE
  TYPE t_order_id_tab IS TABLE OF app.orders.order_id%TYPE;
  TYPE t_amount_tab   IS TABLE OF app.orders.amount%TYPE;

  v_order_ids t_order_id_tab;
  v_amounts   t_amount_tab;
  v_min_date  app.orders.created_at%TYPE := SYSTIMESTAMP - INTERVAL '1' DAY;
  v_sql       VARCHAR2(1000) :=
    'SELECT order_id, amount
       FROM app.orders
      WHERE created_at >= :min_date
      ORDER BY order_id';
BEGIN
  EXECUTE IMMEDIATE v_sql
    BULK COLLECT INTO v_order_ids, v_amounts
    USING v_min_date;

  FOR i IN 1 .. v_order_ids.COUNT LOOP
    DBMS_OUTPUT.PUT_LINE(v_order_ids(i) || ': ' || v_amounts(i));
  END LOOP;
END;
/
dynamic-select-ref-cursor.sql
DECLARE
  v_cursor    SYS_REFCURSOR;
  v_is_open   BOOLEAN := FALSE;
  v_order_id  app.orders.order_id%TYPE;
  v_amount    app.orders.amount%TYPE;
  v_min_date  app.orders.created_at%TYPE := SYSTIMESTAMP - INTERVAL '1' DAY;
  v_sql       VARCHAR2(1000) :=
    'SELECT order_id, amount
       FROM app.orders
      WHERE created_at >= :min_date
      ORDER BY order_id';
BEGIN
  OPEN v_cursor FOR v_sql USING v_min_date;
  v_is_open := TRUE;
  LOOP
    FETCH v_cursor INTO v_order_id, v_amount;
    EXIT WHEN v_cursor%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(v_order_id || ': ' || v_amount);
  END LOOP;
  CLOSE v_cursor;
  v_is_open := FALSE;
EXCEPTION
  WHEN OTHERS THEN
    IF v_is_open THEN
      CLOSE v_cursor;
    END IF;
    RAISE;
END;
/

識別子はバインドできないため許可リストで決定する

表名、列名、ORDER BYの方向などの識別子や構文要素はバインドできません。DBMS_ASSERT.SIMPLE_SQL_NAMEは単純SQL名の形式を検査しますが、引用符付き識別子も許可し、業務上アクセスしてよい対象かまでは判定しません。そのため、先に許可リストで候補を限定し、その後に安全な識別子として引用します。

identifier-whitelist.sql
CREATE OR REPLACE FUNCTION allowed_order_column(
  p_column IN VARCHAR2
) RETURN VARCHAR2
  AUTHID DEFINER
IS
  v_column VARCHAR2(128) := UPPER(TRIM(p_column));
BEGIN
  IF v_column NOT IN ('ORDER_ID', 'AMOUNT', 'CREATED_AT') THEN
    RAISE_APPLICATION_ERROR(-20001, 'column not allowed');
  END IF;

  RETURN DBMS_ASSERT.ENQUOTE_NAME(
           DBMS_ASSERT.SIMPLE_SQL_NAME(v_column),
           FALSE
         );
END;
/

並び順だけを切り替えるなら、動的SQLを使わずCASE式で静的SQLに保つ方法もあります。SQLテキストが一定になるため、カーソル共有の観点ではこちらが優先です。

static-order-by-switch.sql
DECLARE
  v_sort VARCHAR2(20) := 'AMOUNT_DESC';
BEGIN
  FOR r IN (
    SELECT order_id, amount, created_at
      FROM app.orders
     ORDER BY
       CASE WHEN v_sort = 'AMOUNT_ASC'  THEN amount END ASC,
       CASE WHEN v_sort = 'AMOUNT_DESC' THEN amount END DESC,
       CASE WHEN v_sort = 'DATE_DESC'   THEN created_at END DESC,
       order_id
  ) LOOP
    DBMS_OUTPUT.PUT_LINE(r.order_id || ': ' || r.amount);
  END LOOP;
END;
/

可変長IN条件はSQLテキストを変えずに渡す

IN (:1, :2, :3)のプレースホルダ数を入力件数に応じて変えると、件数ごとに異なるSQLが共有プールへ作られます。Oracle 12.2以降でJSONを利用できる環境なら、JSON配列を1つのCLOBとしてバインドし、JSON_TABLEで行へ展開するとSQLテキストを一定に保てます。

variable-in-list-json-table.sql
DECLARE
  TYPE t_order_id_tab IS TABLE OF app.orders.order_id%TYPE;
  TYPE t_amount_tab   IS TABLE OF app.orders.amount%TYPE;

  v_ids_json CLOB := '[1001,1002,1003]';
  v_order_ids t_order_id_tab;
  v_amounts   t_amount_tab;
  v_sql       VARCHAR2(2000) := q'[
    SELECT o.order_id, o.amount
      FROM app.orders o
      JOIN JSON_TABLE(
             :ids_json,
             '$[*]' COLUMNS (
               order_id NUMBER PATH '$'
             )
           ) j
        ON j.order_id = o.order_id
     ORDER BY o.order_id
  ]';
BEGIN
  EXECUTE IMMEDIATE v_sql
    BULK COLLECT INTO v_order_ids, v_amounts
    USING v_ids_json;

  FOR i IN 1 .. v_order_ids.COUNT LOOP
    DBMS_OUTPUT.PUT_LINE(v_order_ids(i) || ': ' || v_amounts(i));
  END LOOP;
END;
/

大量のIDを繰り返し使う場合は、JSON変換よりグローバル一時表へ投入して結合する方が適することもあります。JSONの型変換やエラー処理は、JSON_TABLEでJSONを取り込む方法で詳しく解説しています。

型を合わせて暗黙変換と子カーソル増加を防ぐ

NUMBER列へ文字列、TIMESTAMP列へ書式付き文字列を渡すと、NLS設定やバインド型の差によって不具合や共有失敗が起きやすくなります。列の %TYPE を使い、データベース列と同じPL/SQL型でバインドします。

bind-with-column-types.sql
DECLARE
  v_min_amount app.orders.amount%TYPE := 1200.50;
  v_min_date   app.orders.created_at%TYPE :=
    TO_TIMESTAMP_TZ(
      '2026-06-01 00:00:00 +09:00',
      'YYYY-MM-DD HH24:MI:SS TZH:TZM'
    );
  v_count      PLS_INTEGER;
  v_sql        VARCHAR2(1000) :=
    'SELECT COUNT(*)
       FROM app.orders
      WHERE amount >= :min_amount
        AND created_at >= :min_date';
BEGIN
  EXECUTE IMMEDIATE v_sql
    INTO v_count
    USING v_min_amount, v_min_date;

  DBMS_OUTPUT.PUT_LINE('count=' || v_count);
END;
/

FORALLで動的DMLを一括実行する

FORALLは動的DMLでも使用できます。コレクション型は宣言部に置き、EXECUTE IMMEDIATEへ各要素をバインドします。SAVE EXCEPTIONSを付ける場合は、失敗行とエラーコードを SQL%BULK_EXCEPTIONSから記録します。

forall-dynamic-update.sql
DECLARE
  TYPE t_order_id_tab IS TABLE OF app.orders.order_id%TYPE
    INDEX BY PLS_INTEGER;
  TYPE t_amount_tab IS TABLE OF app.orders.amount%TYPE
    INDEX BY PLS_INTEGER;

  v_order_ids t_order_id_tab;
  v_amounts   t_amount_tab;
  v_sql       VARCHAR2(1000) :=
    'UPDATE app.orders
        SET amount = :amount
      WHERE order_id = :order_id';
  e_bulk_errors EXCEPTION;
  PRAGMA EXCEPTION_INIT(e_bulk_errors, -24381);
BEGIN
  v_order_ids(1) := 1001;
  v_amounts(1) := 1500;
  v_order_ids(2) := 1002;
  v_amounts(2) := 1800;

  FORALL i IN INDICES OF v_order_ids SAVE EXCEPTIONS
    EXECUTE IMMEDIATE v_sql
      USING v_amounts(i), v_order_ids(i);
EXCEPTION
  WHEN e_bulk_errors THEN
    FOR i IN 1 .. SQL%BULK_EXCEPTIONS.COUNT LOOP
      DBMS_OUTPUT.PUT_LINE(
        'index=' || SQL%BULK_EXCEPTIONS(i).ERROR_INDEX ||
        ', error=' || SQL%BULK_EXCEPTIONS(i).ERROR_CODE
      );
    END LOOP;
END;
/

FORALLのRETURNINGはBULK COLLECTで受け取る

一括INSERTで採番値を取得する場合は、動的SQL内に RETURNINGを記述し、外側では RETURNING BULK COLLECT INTOへコレクションを指定します。

forall-returning-bulk-collect.sql
DECLARE
  TYPE t_customer_tab IS TABLE OF app.orders.customer_id%TYPE
    INDEX BY PLS_INTEGER;
  TYPE t_amount_tab IS TABLE OF app.orders.amount%TYPE
    INDEX BY PLS_INTEGER;
  TYPE t_order_id_tab IS TABLE OF app.orders.order_id%TYPE;

  v_customers t_customer_tab;
  v_amounts   t_amount_tab;
  v_order_ids t_order_id_tab;
  v_sql       VARCHAR2(1000) :=
    'INSERT INTO app.orders(order_id, customer_id, amount)
     VALUES (orders_seq.NEXTVAL, :customer_id, :amount)
     RETURNING order_id INTO :order_id';
BEGIN
  v_customers(1) := 10;
  v_amounts(1) := 100;
  v_customers(2) := 11;
  v_amounts(2) := 200;
  v_customers(3) := 12;
  v_amounts(3) := 300;

  FORALL i IN INDICES OF v_customers
    EXECUTE IMMEDIATE v_sql
      USING v_customers(i), v_amounts(i)
      RETURNING BULK COLLECT INTO v_order_ids;

  FOR i IN 1 .. v_order_ids.COUNT LOOP
    DBMS_OUTPUT.PUT_LINE('inserted=' || v_order_ids(i));
  END LOOP;
END;
/

DBMS_SQLは列構成が不明な場合に限定する

DBMS_SQLでは、パース後に DESCRIBE_COLUMNS2で列数とデータ型を調べ、型ごとに DEFINE_COLUMNと COLUMN_VALUEを対応させます。すべての列をVARCHAR2として定義すると、CLOBやDATE、NUMBERなどで変換エラーや情報欠落が起きるため、汎用処理では列型ごとの分岐が必要です。

選択基準

戻り列が事前に分かるなら、EXECUTE IMMEDIATEまたはREF CURSORの方が短く安全です。DBMS_SQLは、SQLコンソールや汎用エクスポートなど、結果構造そのものが入力によって変わる用途へ限定すると保守しやすくなります。

カーソル共有とハードパースを診断する

バインド化の効果は、SQLテキストを眺めるだけでなく共有プールで確認します。executionsに対して parse_callsやloadsが多いSQL、version_countが大きいSQLを優先して調査します。

diagnose-cursor-sharing.sql
SELECT
  sql_id,
  plan_hash_value,
  executions,
  parse_calls,
  loads,
  invalidations,
  version_count,
  ROUND(parse_calls / NULLIF(executions, 0), 4) AS parses_per_exec,
  SUBSTR(sql_text, 1, 100) AS sql_text
FROM v$sqlarea
WHERE parsing_schema_name = 'APP'
  AND executions > 0
ORDER BY parses_per_exec DESC, version_count DESC
FETCH FIRST 30 ROWS ONLY;

SELECT
  child_number,
  unbound_cursor,
  bind_mismatch,
  optimizer_mismatch,
  auth_check_mismatch
FROM v$sql_shared_cursor
WHERE sql_id = :sql_id
ORDER BY child_number;

V$SQLAREAやV$SQL_SHARED_CURSORの参照には、環境に応じた動的パフォーマンスビューの権限が必要です。子カーソル増加は必ずしも障害ではなく、バインド値の選択度に応じてAdaptive Cursor Sharingが複数計画を作る場合もあります。まず型不一致、リテラル混在、権限差、オプティマイザ環境差を切り分けます。

共有プールとセッションキャッシュの仕組みは、カーソルキャッシュとメモリ管理も合わせて確認してください。

SQLインジェクションと性能劣化を同じテストで検出する

  • 検索値へ引用符、コメント記号、長い文字列を渡してもSQL構造が変わらない
  • 表名・列名・並び順へ許可外の値を渡すと実行前に拒否される
  • NLS_DATE_FORMATやNLS_NUMERIC_CHARACTERSを変えても結果が変わらない
  • 入力値だけを変えて繰り返し実行し、同じSQL_IDと少ないPARSE_CALLSを維持する
  • NUMBER、DATE、TIMESTAMP、文字列でバインド型と列型が一致する
  • FORALLの一部失敗時にERROR_INDEXから元データを特定できる

動的DDLや権限境界をパッケージへ閉じ込める設計は、権限分離とセキュリティパッケージが参考になります。

まとめ

動的SQLの最適化は、SQL文字列を巧みに組み立てることではありません。静的SQLを優先し、動的にする部分を最小化したうえで、値をバインドし、識別子を許可リストで制限することが中心です。

単一行DMLの戻り値は RETURNING INTO、複数行SELECTは BULK COLLECT INTOまたはREF CURSOR、動的な一括DMLは FORALLを使います。可変長IN条件ではSQLテキストを固定し、診断時はパース回数と子カーソルの共有失敗理由を確認します。この流れを守れば、SQLインジェクション対策とカーソル共有の改善を同じ設計で実現できます。