SQL プロシージャーにおけるカーソル変数の使用

この例では、以下について説明します。

• CREATE TYPE ステートメントによる ROW データ・タイプの作成
• CREATE TYPE ステートメントによる、行データ・タイプ指定に基づく厳密に型付けされたカーソルの作成
• CREATE PROCEDURE ステートメントによる、出力カーソル・パラメーターを持つプロシージャーの作成
• CREATE PROCEDURE ステートメントによる、別のプロシージャーを呼び出し、入力パラメーターとしてカーソルを引き渡すプロシージャーの作成

この例を実行するための前提条件として、SAMPLE データベースが存在していなければなりません。 このサンプル・データベースを作成するには、Db2® コマンド・ウィンドウから以下のコマンドを実行してください。

db2sampl;

以下は、SQL プロシージャーで使用するカーソル変数の中核となるフィーチャーについて示している CLP スクリプトの例です。 このスクリプトには、行データ・タイプ定義、カーソル・タイプ定義、および 2 つの SQL プロシージャー定義が含まれています。 プロシージャー P_CALLER には、カーソル変数定義と、P という名前のプロシージャーへの呼び出しが含まれています。 プロシージャー P はカーソルを定義し、そのカーソルをオープンして、カーソルを出力パラメーター値として引き渡します。 プロシージャー P_CALLER はカーソル・パラメーターを受け取り、カーソル値をローカル変数にフェッチしてから、そのローカル変数値に基づいて edlvel と lastname という 2 つの出力パラメーター値を設定します。

--#SET TERMINATOR @
update command options using c off @
connect to sample @

CREATE TYPE myRowType AS ROW (edlevel SMALLINT, name VARCHAR(128))@

CREATE TYPE myCursorType AS myRowType CURSOR@

CREATE PROCEDURE P(IN pempNo VARCHAR(8), OUT pcv1 SYSCURSOR)
LANGUAGE SQL
BEGIN
  SET pcv1 = CURSOR FOR SELECT edlevel, lastname FROM employee WHERE empNo = pempNo;
  OPEN pcv1;

END@

CREATE PROCEDURE P_CALLER( IN pempNo VARCHAR(8) ,
                           OUT edlevel SMALLINT,
                           OUT lastname VARCHAR(128))
LANGUAGE SQL
BEGIN
  DECLARE rv1 myRowType;
  DECLARE c1 SYSCURSOR;

  CALL P (pempNo,c1);
  FETCH c1 INTO rv1;
  CLOSE c1;

  SET edlevel = rv1.edlevel;
  SET lastname = rv1.name;

END @

CALL P_CALLER('000180',?,?) @

このスクリプトを実行すると、以下の出力が生成されます。

update command options using c off 
Db20000I  The UPDATE COMMAND OPTIONS command completed successfully.

connect to sample 

   Database Connection Information

 Database server        = Db2/LINUXX8664 9.7.0
 SQL authorization ID   = REGRESS5
 Local database alias   = SAMPLE


CREATE TYPE myRowType AS ROW (edlevel SMALLINT, name VARCHAR(128)) 
Db20000I  The SQL command completed successfully.

CREATE TYPE myCursorType AS CURSOR RETURNS myRowType 
Db20000I  The SQL command completed successfully.

CREATE PROCEDURE P(IN pempNo VARCHAR(8),OUT pcv1 SYSCURSOR)
LANGUAGE SQL
BEGIN
  SET pcv1 = CURSOR FOR SELECT edlevel, lastname FROM employee WHERE empNo = pempNo;
  OPEN pcv1;

END
Db20000I  The SQL command completed successfully.

CREATE PROCEDURE P_CALLER( IN pempNo VARCHAR(8) ,
                           OUT edlevel SMALLINT,
                           OUT lastname VARCHAR(128))
LANGUAGE SQL
BEGIN
  DECLARE rv1 myRowType;
  DECLARE c1 SYSCURSOR;

  CALL P (pempNo,c1);
  FETCH c1 INTO rv1;
  CLOSE c1;

  SET EDLEVEL = rv1.edlevel;
  SET LASTNAME = rv1.name;

END 
Db20000I  The SQL command completed successfully.

CALL P_CALLER('000180',?,?) 

  Value of output parameters
  --------------------------
  Parameter Name  : EDLEVEL
  Parameter Value : 17

  Parameter Name  : LASTNAME
  Parameter Value : SCOUTTEN

  Return Status = 0