EXECUTE IMMEDIATE 文を使うと、文字列 (引用符で囲む) と変数を使って文を組み立てることができます。次に示すのは、テーブルを作成する EXECUTE IMMEDIATE 文を含むプロシージャの例です。

CREATE PROCEDURE CreateTableProcedure(
      IN tablename CHAR(128) )
BEGIN
   EXECUTE IMMEDIATE 'CREATE TABLE '
   || tablename
   || '( column1 INT PRIMARY KEY )'
END;

EXECUTE IMMEDIATE 文は、結果セットを返すクエリで使用できます。文が結果セットを返すように指定するために、EXECUTE IMMEDIATE 文で WITH RESULT SET ON 句を使用します。デフォルトの動作では、文は結果セットを返しません。WITH RESULT SET ON または WITH RESULT SET OFF を指定することは、プロシージャが作成されるときだけではなく、プロシージャが実行されるときの動作についても影響をもたらすことになります。

次のプロシージャを考えてみます。

CREATE OR REPLACE PROCEDURE test_result_clause()
BEGIN
    EXECUTE IMMEDIATE WITH RESULT SET OFF 'SELECT 1';
END;

プロシージャ定義に RESULT SET 句が含まれていないと、データベースサーバ側ではプロシージャによって結果セットが生成されるのかどうかを判断しようとします。ここでは、EXECUTE IMMEDIATE 文では、結果セットを生成しないように設定されています。そのため、データベースサーバ側では、プロシージャには結果セットのカラムがないと判断されるため、このプロシージャの SYSPROCPARM システムビューにはカラムが表示されません。このプロシージャの呼び出しに DESCRIBE を指定しても、結果カラムは返されません。カーソルを開いたり、文を実行するかどうかを決定するために、Embedded SQL アプリケーションによってこの情報が使用された場合には、文が実行されて、エラーが返されます。

2 番目の例では、上記のプロシージャの修正バージョンを考えてみます。

CREATE OR REPLACE PROCEDURE test_result_clause()
BEGIN
    EXECUTE IMMEDIATE WITH RESULT SET ON 'SELECT 1';
END;

ここでは、WITH RESULT SET ON 句が指定されているため、SYSPROCPARM システムビューにはこのプロシージャのローが存在しています。プロシージャが EXECUTE IMMEDIATE を使用しているため、データベースサーバ側では、実際に結果セットがどのようになるかついては把握できません。ただし、結果セットが存在することは予測できるため、データベースサーバは SYSPROCPARM に、expression という名前の SMALLINT 型のダミーの結果セットのカラムを定義することによって、結果セットに対応していることを示します。作成される結果セット用ダミーカラムが 1 つ だけです。サーバ側では、EXECUTE IMMEDIATE 文が使用されているときに、各結果セットのカラム数や型については判断できません。そのため、さらに少し修正したバージョンの例を考えてみます。

CREATE OR REPLACE PROCEDURE test_result_clause()
BEGIN
    EXECUTE IMMEDIATE WITH RESULT SET ON 'SELECT 1, 2, 3';
END;

ここでは、SELECT 文は 3 つのカラムの結果セットを返しますが、サーバの SYSPROCPARM システムビューには、1 つのローがあるだけです。そこで、次のクエリを見てください。

SELECT * FROM test_result_clause();

このクエリは、SQLCODE -866 エラーになります。これは、実行時の結果セットの定義が SYSPROCPARM に事前に用意された定義と一致しないためです。

上記のクエリを実行するには、次に示すように、結果セットのカラムの名前と型を明示的に指定します。

SELECT * FROM test_result_clause() WITH (x INTEGER, y INTEGER, z INTEGER);

WITH RESULT SET ON が指定されている場合は、実行時に、データベースサーバは EXECUTE IMMEDIATE 文を処理し、結果セットを返します。ただし、WITH RESULT SET OFF が指定されているか、または句が省略されている場合でも、データベースサーバは解析された文字列引数の最初の文の型については、同じように認識することができます。その文が SELECT 文であれば、結果セットを返します。それでは、前述した 2 番目の例を見てください。

CREATE OR REPLACE PROCEDURE test_result_clause()
BEGIN
    EXECUTE IMMEDIATE WITH RESULT SET OFF 'SELECT 1';
END;

Interactive SQL からこのプロシージャを呼び出すとエラーにはなりません。ここで、1 つの SELECT 文ではなく、バッチが含まれたプロシージャに変更するとします。

CREATE OR REPLACE PROCEDURE test_result_clause()
BEGIN
    EXECUTE IMMEDIATE WITH RESULT SET OFF 
    'begin declare v int; set v=1; select v; end';
END;

この場合には、test_result_clause プロシージャを呼び出すと、エラー (SQLCODE -946, SQLSTATE 09W03) が発生します。

次の最後の例では、プロシージャ内で SELECT 文を EXECUTE IMMEDIATE 文の引数として構築して、プロシージャが結果セットを返すようにする方法を示しています。

CREATE PROCEDURE DynamicResult(
   IN Columns LONG VARCHAR,
   IN TableName CHAR(128),
   IN Restriction LONG VARCHAR DEFAULT NULL )
BEGIN
    DECLARE Command LONG VARCHAR;
    SET Command = 'SELECT ' || Columns || ' FROM ' || TableName;
    IF ISNULL( Restriction,'') <> '' THEN
         SET Command = Command || ' WHERE ' || Restriction;
    END IF;
    EXECUTE IMMEDIATE WITH RESULT SET ON Command;
END;

このプロシージャは次のように呼び出されます。

CALL DynamicResult(
   'table_id,table_name',
   'SYSTAB',
   'table_id <= 10');

これは次のような結果になります。

上記の CALL では、プロシージャで EXECUTE IMMEDIATE が使用されていても、正しく結果セットが返されます。ODBC などの一部のサーバ API では、PREPARE-DESCRIBE-EXECUTE-OR-OPEN を利用して要求が結合されており、結果セットを返すかどうかによって、文を実行するか、または開くかが選択されます。文が開かれる必要がある場合には、API またはアプリケーションは DESCRIBE CURSOR を続けて発行することによって、プロシージャが作成されたときに構築された SYSPROCPARM システムビューの内容に依存するのではなく、実際の結果セットを把握できるようにします。この方法は、DBISQL でも DBISQLC でも使用されています。この場合、上記のプロシージャの CALL はエラーにならずに実行されます。ただし、文の DESCRIBE の結果に依存するアプリケーションインタフェースの場合は、どの文でも処理できるというわけではありません。

アトミックな複合文中では、COMMIT を行う EXECUTE IMMEDIATE 文は使えません。COMMIT 文はこのコンテキストでは許可されていません。

 参照