動的 SQL
PL/SQL において動的 SQL を実行する方法は以下の２つの方法がある。
- ネイティブな動的 SQL (正式名：システム固有の動的 SQL(※1))
- 従来からの DBMS_SQL パッケージ
(※1) Oracle の日本語マニュアルの システム固有の動的 SQL(原文:Native Dynamic SQL) の訳は原文に忠実すぎて、
正直、ネイティブのままの方が真意を理解しやすかったと思う。システム固有というとシステムで固有？、固有な機能なのか？、移植や汎用性に制限でもあるのか？との印象があると思われる。(実際は、簡単に動的 SQL を記述できる上、高速化することができる)
とはいっても Native はマニュアル全体で一貫して システム固有 と訳されているので、わかる人はわかるのであろう。
システム固有の動的 SQL のシステム固有とは Oracle 8i から PL/SQL 実行エンジンに動的な SQL を実行する処理が組み込まれた(= native 処理) ことをあらわしている。
システム固有の動的 SQL (EXECUTE IMMEDIATE)
PL/SQL エンジンに動的 SQL を実行するための機能が組み込まれたことによりほとんどの 動的 SQL のパフォーマンスが 10% のオーダで向上している(開発者ガイドによれば DBMS_SQL より 1.5 〜 3 倍パフォーマンスが向上するとも記載されている)。
実験してみたところ 「動的 SQL」 と 「静的 SQL」 において 10 万回ループでの比較では、処理時間の差が検出できないほどのすばらしいパフォーマンスを持っている。
これは従来の DBMS_SQL パッケージがカーソルの準備、PREPARE と EXECUTE、カーソルの終了処理をストアドパッケージの内部処理から PL/SQL エンジンにおいて一括してネイティブ処理されることによる。
しかし DBMS_SQL パッケージによる動的 SQL が有効なケースもある。
例えば PREPARE によってカーソルを準備して、そのカーソルを EXECUTE によって相当な回数(※1)を繰り返して使用する場合である。また VARCHAR2 型の制限によると思われる SQL の長さ 32KB の制限を超過する場合には Oracle 10g より前からあるDBMS_SQL.VARCHAR2S と DBMS_SQL.VARCHAR2A 
を使うことで実行できるという道も用意されている。(※2)
(※1) 比較検証はしていないが数万オーダ程度ならば長大な SQL 文でもない限りシステム固有の動的 SQL においてもレスポンスが逆転することがないと思われる。これ以上オーダの動的 SQL はバルク処理などを考慮するであろうから DBMS_SQL パッケージの出番は色々手を尽くしたが EXECUTE IMMEDIATE では実現できないという状態に使う非常にレアケースなのだろう。
DBMS_SQL パッケージにはバインド変数関連でシステム固有の動的 SQL では実現できないユニーク（固有）な機能があり、自由検索機能や汎用ツールを開発するときには頼もしいパッケージになってくれるかもしれません。
(※2)
Oracle 11g から引数（SQL文）として CLOB もサポート したため EXECUTE IMMEDIATE の弱点の 1つであった 32KB を超える SQL*1 の壁はなくなっている。但し、文字リテラル は CHAR 型ということはお忘れなく。（参照 ORA-01704: 文字列リテラルが長すぎます）
- システム固有の動的 SQL の使用方法
非常に簡単な使用方法(しかし、動的である必要性はない)。
SQL(DML & DDL) および PL/SQL ブロックも実行することができる。
CREATE OR REPLACE PROCEDURE RIVUS.STEP01_EXEC_PUTLINE
IS
BEGIN
EXECUTE IMMEDIATE 'CALL DBMS_OUTPUT.PUT_LINE(''有意義な使い方をしていますか？'')';
EXECUTE IMMEDIATE 'BEGIN DBMS_OUTPUT.PUT_LINE(''いいえ、していません。''); END;';
END;
/
-- 実行結果
SQL> call STEP01_EXEC_PUTLINE();
有意義な使い方をしていますか？
いいえ、していません。
コールが完了しました。
有意義な？使い方として一般的なものは、静的 SQL では実行できない DDL を実行する、テーブル名を動的にするなどが考えられる。
ただし、 DDL で後続の DML にコンパイルの依存関係が発生する SQL を投入すると、後続する DDL の影響を受けるすべての SQL(DML) は静的 SQL として記述できなくなる(※3)。その点には注意しておく必要がある。
(※3) 例えば、テーブル定義などに関する DDL の場合、DDL 実行すると同時に自分自身のサブプログラムの静的 DML が INVALID になるため処理を継続できなくなる。デフォルト値やカラム長の変更などは大丈夫。(自分自身を処理中に自身を再コンパイルするなどはできない)
PL/SQL の プレースホルダ (place holder) と バインド変数 (bind variable)
プレースホルダ（プレイスフォルダ: place folder は間違い）とは、後から値の入れるための定義の器でありプレースホルダを用いることで SQL の文法として完成する。プレースホルダ名の先頭にコロン(:) を付けることで項目名などと区別される。他の言語ではクエスチョン(クエッション)マーク(?) などをプレースホルダとして使用している場合もある。
バインドとは、プレースホルダに紐付け(バインド)することである。１つのバインド変数に複数の値をバインドすることはできない。(※)
(※) 誰でも使用したいと考えることであるが 〜 col IN (:hoge) において :hoge に 'aa','bbb' などの値リストはバインドできない。
PL/SQL 以外の言語で一部のドライバを経由するとバインド変数をリテラル文字列による SQL に展開してくれるものもあるようである。ただし、この実装方法には SQL インジェクション関連（後述）の脆弱性を内包してしまう要素があるので、使用している製品によっては動作を検証することをお勧めする。
参考: IN 条件に複数（1000 要素以上）のバインド変数を割り当てる方法
また、動的 SQL 文において スキーマ に該当する部分をプレースホルダにすることはできない。(例：テーブル名をプレースホルダにする)
これらの記述方法と関係を正しく理解していないと、ORA-01008:バインドされていない変数があります。
が発生する。
単一 SQL 文におけるプレースホルダとバインド変数
この例のバインドではプレースホルダ名(:1、:"2") (※4) には意味がない。その個数と順番に意味があり、
プレースホルダ個数に１対１になるように USING にバインド変数を並べる。
単一行を戻す問い合わせには INTO 句、RETURNING 句を使用している DML には RETURNING INTO 句を使用してバインド変数を記述する。
(※4) 実は、プレースホルダ名の先頭文字として数値がサポートされているかどうかは分からない。Oracle では 非引用識別子として先頭文字列は英字の必要がある。またプレースホルダにアンダースコア(_) やドルマーク($) を単体で非引用識別子として使用するとエラーになり、引用識別子として記述すると使用できる点などから察すると、プレースホルダ名も識別子としての命名規約に従って使用したほうがよいに違いない。(特に日本語を使用している場合には注意)
クォート処理(")は、コロン(:) の前ではなく後につける。:"名前" を ":名前" と誤まると通常のカラム名として扱われるので ORA-01008: 〜 になる。
使用例
CREATE OR REPLACE PROCEDURE RIVUS.STEP01_EXEC_SQL
IS
vID VARCHAR2(4);
vName VARCHAR2(32);
vSQL VARCHAR2(1000);
BEGIN
vID := '0001';
vName := NULL;
vSQL := '';
vSQL := vSQL || 'INSERT INTO USER_MASTER (USER_ID , USER_NAME)';
vSQL := vSQL || ' VALUES ( :1, :"2" )';
DBMS_OUTPUT.PUT_LINE('SQL=' || vSQL);
EXECUTE IMMEDIATE vSQL USING IN vID, vName;
DBMS_OUTPUT.PUT_LINE('インサートした件数は' || SQL%ROWCOUNT || '件です。');
COMMIT ;
END;
/
PL/SQL ブロックにおけるプレースホルダとバインド変数
PL/SQL ブロックの場合では、単一 SQL の仕様とは異なる。プレースホルダ名と、それが最初に登場する順番に意味が発生する。
PL/SQL ブロックの使用例 (呼び出される SQL を含むプロシージャの定義)
CREATE OR REPLACE PROCEDURE RIVUS.STEP01_EXEC_SQL2(
P_ID VARCHAR2, -- １番目が ID
P_NAME VARCHAR2, -- ２番目が NAME
P_DEPT VARCHAR2 -- ３番目が DEPT
)
IS
vID VARCHAR2(4);
vDept VARCHAR2(4);
vName VARCHAR2(32);
vSQL VARCHAR2(1000);
BEGIN
vID := P_ID;
vDept := P_DEPT;
vName := P_NAME;
vSQL := '';
vSQL := vSQL || 'INSERT INTO USER_MASTER (USER_ID, DEPT_NO, USER_NAME)';
vSQL := vSQL || ' VALUES ( :ID, :DEPT, :NAME )';
-- プレースホルダ名は Oracle には価値が無いがメンテのときに内容を理解するために役立つ
DBMS_OUTPUT.PUT_LINE('SQL=' || vSQL);
EXECUTE IMMEDIATE vSQL USING IN vID, vDept, vName;
DBMS_OUTPUT.PUT_LINE('インサートした件数は' || SQL%ROWCOUNT || '件');
COMMIT ;
DBMS_OUTPUT.PUT_LINE('USER_ID は[' || vID || ']');
DBMS_OUTPUT.PUT_LINE('DEPT_NO は[' || vDept || ']');
DBMS_OUTPUT.PUT_LINE('USER_NAME は[' || vName || ']');
END;
/
PL/SQL ブロックの使用例 (呼び出し元の PL/SQL ブロックを含むプロシージャの定義)
すべての :xxx のプレースホルダに、変数 v1 が設定され、:yyy には変数 v2 が設定される。プレースホルダの名前と、その名前が初めて登場する順番によってバインド変数の代入先が確定する。
CREATE OR REPLACE PROCEDURE RIVUS.STEP01_EXEC_PLSQLBLOCK
IS
v1 VARCHAR2(4);
v2 VARCHAR2(32);
BEGIN
v1 := '0000'; -- ID と DEPT
v2 := 'なまえ'; -- NAME
EXECUTE IMMEDIATE 'BEGIN STEP01_EXEC_SQL2(:xxx, :yyy, :xxx); END;' USING IN v1, v2 ;
END;
/
-- 実行結果
SQL> set linesize 100
SQL> call step01_exec_plsqlblock();
SQL=INSERT INTO USER_MASTER (USER_ID , DEPT_NO, USER_NAME) VALUES ( :ID, :DEPT, :NAME )
インサートした件数は1件
USER_ID は[0000]
DEPT_NO は[0000]
USER_NAME は[なまえ]です。
コールが完了しました。
SQL インジェクションの危険性
バインドを適切に使用していない場合には、動的 SQL は SQL インジェクションの対象になる可能性がある。
(SQL インジェクションの詳細や危険性についてはトップページからのリンクの IPA/ISEC のサイトなどで確認してください。)
バインドを使用せずに SQL を組み立てている場合には 100% 信用できる入力源のパラメータ以外
*2は、SQL 文に結合(埋め込む)するのは避ける。
一般公開していないシステムであっても WEB 経由の場合はデータを混入させる方法は C/S システムに比べて容易である。
ユーザーからの入力による動的 SQL を使用する場合にはバインド変数方式に制限しておく方が望ましい。
動的 SQL をプロシージャ化している場合、危険なのが文字列パラメータ(※5)を未チェックで SQL 文字列に埋め込むコーディングをしている場合である。このようなコーディングをしている場合には操作できる内容が飛躍的に増える。
(※5) パラメータの記述には、PL/SQL による制限以外に文字列の長さ制限が適用されない。最低でも別途用意した規定サイズの文字列変数に再代入する配慮が必要である。原則的には(コストが許せれば)、どのような形態のシステムでも動的 SQL 実行部には入力パラメータはサニタイズ処理を施しておくのが望ましい。
関連事項
