CREATE/REPLACE PROCEDURE文のDYNAMIC RESULT SETS句を使用して、最大15の結果セットをストアド プロシージャの呼び出し側(外部ストアド プロシージャ)またはクライアント(BTEQなどのアプリケーション)に返すことができます。このストアド プロシージャは、複文の応答スプール形式で結果セットを返します。
句を省略した場合、ストアド プロシージャはゼロの結果セットを想定します。ストアド プロシージャは結果セットを返さない可能性があります。あるいは、DYNAMIC RESULT SETS句で指定された場合よりも少ない結果セットを返します。
この句はオプションです。ストアド プロシージャで結果セットを返さない場合は、この句を使用しないでください。
ストアド プロシージャの作成と呼び出し側またはクライアントへの結果セットの返送
- CREATE/REPLACE PROCEDURE文でDYNAMIC RESULT SETS句を使用し、ストアド プロシージャが返す結果セットの数を指定します。たとえば、次に示す文では、1つの結果セットを返すストアド プロシージャを定義しています。
CREATE PROCEDURE sp1 (IN SqlStr VARCHAR(50), IN a INT) DYNAMIC RESULT SETS 1
- DECLARE CURSOR文を使用して、ストアド プロシージャが返す結果セットごとの結果セット カーソルを宣言します。
- ストアド プロシージャに対してWITH RETURN ONLY TO CALLERまたはWITH RETURN ONLYを指定して、対象プロシージャの呼び出し側にのみ結果セット(複数可)が返されるようにします。クライアントからプロシージャを呼び出すと、ストアド プロシージャは結果セットをクライアント アプリケーションに返します。あるストアド プロシージャから対象プロシージャを呼び出すと、ストアド プロシージャは結果セットを呼び出し側のプロシージャに返します。このプロシージャは、SQLが許可された外部ストアド プロシージャの場合もあります。
- ストアド プロシージャに対してWITH RETURN TO CALLERまたはWITH RETURNを指定して、対象プロシージャの呼び出し側と対象プロシージャ(カーソルを開いたプロシージャ)の両方に結果セット(複数可)が返されるようにします。
- ストアド プロシージャに対してWITH RETURN TO CLIENTを指定して、クライアント(BTEQなどのアプリケーション)と対象プロシージャ(カーソルを開いたプロシージャ)の両方に結果セット(複数可)が返されるようにします。
- ストアド プロシージャに対してWITH RETURN ONLY TO CLIENTを指定して、結果セットがアプリケーションにのみ返されるようにします。
- 結果セットを作成するSELECT文が静的な場合、その文をDECLARE CURSOR文のSELECT部分に組み込みます。たとえば、次のようになります。
DECLARE cur1 CURSOR WITH RETURN ONLY FOR SELECT * FROM m1;
- DECLARE CURSOR文の動的形式を使用して結果セットを返すには、SELECTの代わりに文名を使用します。たとえば、次のようになります。
DECLARE c1 CURSOR WITH RETURN ONLY FOR s1
- 指定された文名を準備するには、DECLARE CURSORの動的形式それぞれに対して、PREPARE文を使用します。たとえば、次のようになります。
PREPARE s1 FROM SqlStr;
- OPEN文を使用して、静的または動的SELECT文を実行するために、各結果セット カーソルを開きます。動的SELECT文でパラメータ マーカー(疑問符)を使用する場合は、USING句を指定して入力に使用する変数を特定します。たとえば、次のようになります。
OPEN c1 USING a;
USING句に指定する変数の数と、パラメータ マーカーの数は、必ず同じでなければなりません。注意してください。
- FETCH文を使用して、結果セット カーソルの位置を決め、結果セットから読み込みます。
- 結果セットを呼び出し側またはクライアントに返すために、結果セット カーソルは開いたままにします。ストアド プロシージャで結果セット カーソルを閉じると、その結果セットは削除され、返されなくなります。 結果セットは、開かれた順に返されます。
例: 静的形式のDECLARE CURSOR文
結果セットを返すストアド プロシージャを作成するための、DYNAMIC RESULT SETS句と静的形式のDECLARE CURSOR文の使用例を次に示します。
CREATE PROCEDURE Sample_p (INOUT c INTEGER) DYNAMIC RESULT SETS 2 BEGIN DECLARE cur1 CURSOR WITH RETURN ONLY FOR SELECT * FROM m1; DECLARE cur2 CURSOR WITH RETURN ONLY FOR SELECT * FROM m2 WHERE m2.a > c; SET c = c +1; OPEN cur1; OPEN cur2; END;
次に、サンプルのストアド プロシージャの結果セットを、BTEQ (クライアント)が読み取る方法の例を示します。
BTEQ -- Enter your DBC/SQL request or BTEQ command: CALL sample_p(1); *** Procedure has been executed. *** Warning: 3212 The stored procedure returned one or more result sets. *** Total elapsed time was 1 second. 1 ----------- 2 *** Procedure dynamic result set. One row found. 2 columns returned. *** Starting Row Number: 1 *** Database Name: FSK *** Procedure Name: SAMPLE_P a b ----------- ---------------------- 1 2.00000000000000E 000 *** Procedure dynamic result set. One row found. 2 columns returned. *** Starting Row Number: 1 *** Database Name: FSK *** Procedure Name: SAMPLE_P a b ----------- ---------------------- 2 4.00000000000000E 000
例: 動的形式のDECLARE文
次の例では、DECLARE CURSOR文に文名s1が含まれます。PREPARE文では、s1を参照して、CREATE PROCEDURE文のSqlStrに含まれる動的SELECT文を準備します。OPEN文では、CREATE PROCEDUREで指定したUSINGのパラメータc1を使用して、DECLARE CURSOR文で指定した結果セット カーソルのaを開きます。
CREATE PROCEDURE sp1 (IN SqlStr VARCHAR(50), IN a INT) DYNAMIC RESULT SETS 1 BEGIN DECLARE c1 CURSOR WITH RETURN ONLY FOR s1; PREPARE s1 FROM SqlStr; OPEN c1 USING a; END;
次に示す例は、BTEQから入力することになる動的SELECT文です。CALL文には、USING句の変数と同じ数のパラメータ マーカーを含めるように注意してください。
CALL sp1(‘sel * from tab1 where a = ? order by 1;’,1);
関連情報
- 外部ストアド プロシージャでの結果セットの読み取りについては、<Teradata Vantage™ - SQL外部ルーチン プログラミング、B035-1147>を参照してください。
- DECLARE CURSOR文について、DECLARE CURSORを参照してください。