外部ストアード・プロシージャーと同様、 SQL プロシージャーは動的 SQL ステートメントを発行できます。動的 SQL ステートメントにパラメーター・マーカーが含まれておらず、それを実行するのが 1 度のみである場合には、EXECUTE IMMEDIATE ステートメントを使用します。
動的 SQL ステートメントにパラメーター・マーカーが含まれている場合には、 PREPARE および EXECUTE ステートメントを使用する必要があります。動的 SQL ステートメントを複数回実行する場合には、単一の PREPARE ステートメントを発行してから EXECUTE ステートメントを複数回発行するほうが、 EXECUTE IMMEDIATE ステートメントをその度に発行するよりも効率的でしょう。 SQL プロシージャーで動的 SQL を発行するのに PREPARE および EXECUTE ステートメントを使用するには、 SQL プロシージャー本体で以下のようなステートメントを含める必要があります。
ステップ 1. | DECLARE ステートメントを使用して、動的 SQL ステートメントを入れるのに十分な大きさの VARCHAR タイプの変数を宣言します。 |
ステップ 2. | SET ステートメントを使用して、ステートメント・ストリングを変数に割り当てます。変数はステートメント・ストリングに直接含めることはできません。その代わりに、疑問符 ('?') 記号を、ステートメントで使用される変数のパラメーター・マーカーとして使用する必要があります。 |
ステップ 3. | PREPARE ステートメントを使用して、ステートメント・ストリングから準備済みステートメントを作成します。 |
ステップ 4. | EXECUTE ステートメントを使用して準備済みステートメントを実行します。ステートメント・ストリングにパラメーター・マーカーが含まれている場合、 USING 文節を使用して変数の値と置換します。
|
注: | SQL プロシージャーの PREPARE ステートメントで定義されているステートメント名は、範囲付き変数として扱われます。 SQL プロシージャーがそのステートメント名を定義した効力範囲を出ると、 DB2 はステートメント名をアクセスできなくなります。複合ステートメント内では、同一のステートメント名を使用する PREPARE ステートメントを 2 つ発行することはできません。 |
例: 動的 SQL ステートメント: 動的 SQL ステートメントを含む SQL プロシージャーを以下の例に示します。
このプロシージャーは、部門番号 (deptNumber) を入力パラメーターとして受け取ります。プロシージャー内では、 3 つのステートメント・ストリングが作成、準備、および実行されます。最初のステートメント・ストリングでは、 DROP ステートメントが実行されて、作成される表が存在していないことが確認されます。この表には、 DEPT_deptno_T という名前が付けられます。ここで、deptno は入力パラメーター deptNumber の値です。 CONTINUE HANDLER は、 DROP ステートメントを実行する際に表が存在しないときに、 DB2 によって戻される SQLSTATE 42704 ("未定義のオブジェクト名です") が検出されても SQL プロシージャーが継続するようにします。 2 番目のステートメント・ストリングは CREATE ステートメントを発行して、 DEPT_deptno_T を作成します。 3 番目のステートメント・ストリングは、部門 deptno 内の従業員の行を DEPT_deptno_T に挿入します。 3 番目のステートメント・ストリングには、 deptNumber を表すパラメーター・マーカーが含まれています。準備済みステートメントが実行されると、パラメーター・マーカーが deptNumber パラメーターに置換されます。
CREATE PROCEDURE create_dept_table (IN deptNumber VARCHAR(3), OUT table_name VARCHAR(30)) LANGUAGE SQL BEGIN DECLARE stmt VARCHAR(1000); -- continue if sqlstate 42704 ('undefined object name') DECLARE CONTINUE HANDLER FOR SQLSTATE '42704' SET stmt = ''; DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET table_name = 'PROCEDURE_FAILED'; SET table_name = 'DEPT_'||deptNumber||'_T'; SET stmt = 'DROP TABLE '||table_name; PREPARE s1 FROM stmt; EXECUTE s1; SET stmt = 'CREATE TABLE '||table_name|| '( empno CHAR(6) NOT NULL, '|| 'firstnme VARCHAR(12) NOT NULL, '|| 'midinit CHAR(1) NOT NULL, '|| 'lastname VARCHAR(15) NOT NULL, '|| 'salary DECIMAL(9,2))'; PREPARE s2 FROM STMT; EXECUTE s2; SET stmt = 'INSERT INTO '||table_name || ' ' || 'SELECT empno, firstnme, midinit, lastname, salary '|| 'FROM employee '|| 'WHERE workdept = ?'; PREPARE s3 FROM stmt; EXECUTE s3 USING deptNumber; END