SQL 过程中的游标变量用法

本示例显示下列内容：

• CREATE TYPE 语句，用于创建 ROW 数据类型
• CREATE TYPE 语句，用于根据行数据类型规范来创建强类型游标
• CREATE PROCEDURE 语句，用于创建具有输出游标参数的过程
• CREATE PROCEDURE 语句，用于创建调用另一过程并将游标作为输入参数传递的过程

运行此示例的先决条件是 SAMPLE 数据库必须存在。要创建样本数据库，请从 Db2® 命令窗口发出以下命令：

db2sampl;

以下是示例 CLP 脚本，该脚本演示 SQL 过程中游标变量用法的核心功能。该脚本包含行数据类型定义、游标类型定义及两个 SQL 过程定义。过程 P_CALLER 包含游标变量定义及对过程 P 的调用。过程 P 定义游标，打开该游标并将其作为输出参数值传递。过程 P_CALLER 接收游标参数，将游标值访存到局部变量中，然后根据局部变量值设置以下两个输出参数值：edlvel 和 lastname。

--#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