[Oracle] 動的SQLの実行（その２）

EXECUTE IMMEDIATE文を使用して動的SQLを実行する際には、INTO句、RETURNING INTO句、USING句を使った値の受け渡しが可能です。

INTO句

INTO句を使用すると、問い合わせによって取得した列リストを受け取ることができます。

DECLARE
  wk_sql    VARCHAR2(1000);
  wk_max_sal  NUMBER(7,2);
BEGIN
  wk_sql := 'SELECT MAX(SAL) FROM EMP WHERE JOB = ''SALESMAN''';
  EXECUTE IMMEDIATE wk_sql INTO wk_max_sal;
  DBMS_OUTPUT.PUT_LINE( wk_max_sal );
END;
/

ただし、単一行問い合わせでないとエラーが発生するので注意が必要です。

USING句

USING句を使用すると、SQL文中のプレースホルダに値をバインドすることができます。

DECLARE
  wk_sql    VARCHAR2(1000);
  wk_max_sal  NUMBER(7,2);
BEGIN
  wk_sql := 'SELECT MAX(SAL) FROM EMP WHERE JOB = :1';
  EXECUTE IMMEDIATE wk_sql INTO wk_max_sal USING 'SALESMAN';
  DBMS_OUTPUT.PUT_LINE( wk_max_sal );
END;
/

このようなSQLは条件をSQLに直接埋め込んで編集することによってバインド引数を使用せずに実行することも可能ですが、条件を変えて繰り返し実行するようなSQLの場合はバインド引数を使用することによってSQLを共有することができるようになるため、パフォーマンス面で若干有利になります。

そのため、バインド引数が使える場合は極力使うよう心掛けた方がいいでしょう。

ただし、バインド引数の使い方にはいくつか注意すべき点があります。

・バインド引数が複数ある場合は、SQL文中のプレースホルダの登場順にUSING句に列記する

プレースホルダは便宜上名前を付けているだけでバインド先を識別する意味は持ちません

・バインド引数はデータベース・オブジェクト名には使用できない

例えば、インデックスを再作成する動的SQLを'ALTER INDEX :1 REBUILD ONLINE'というように編集しても実行時にエラーとなります。

・TRUE、FALSE、NULLを直接バインドすることはできない

これらの値をバインドする場合は変数を介してバインドしなければなりません。

RETURNING句

RETURNING INTO句を使用すると、RETURNING句のあるDML（INSERT文など）の結果を受け取ることができます。

DECLARE
  wk_sql    VARCHAR2(1000);
  wk_deptno  NUMBER(2);
BEGIN
  wk_sql := 'INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES( :1, :2, :3 ) RETURNING DEPTNO INTO :4';
  EXECUTE IMMEDIATE wk_sql USING 90, 'DEVELOPMENT','TOKYO' RETURNING INTO wk_deptno;
  DBMS_OUTPUT.PUT_LINE( wk_deptno );
END;
/

【関連エントリ】
[Oracle] 動的SQLの実行（その１） 2005/01/18
[Oracle] 動的SQLの実行（その２） 2005/01/19
[Oracle] 動的SQLでDDL文を実行しているPL/SQLでORA-6508エラーが発生 2005/02/15