Cursor variable use within an SQL procedure

This example shows the following:

• CREATE TYPE statement to create a ROW data type
• CREATE TYPE statement to create a strongly-typed cursor based on a row data type specification
• CREATE PROCEDURE statement to create a procedure that has an output cursor parameter
• CREATE PROCEDURE statement to create a procedure that calls another procedure and passes a cursor as an input parameter

A prerequisite to running this example is that the SAMPLE database must exist. To create the sample database, issue the following command from a Db2® Command Window:

db2sampl;

The following is an example CLP script that demonstrates the core features of cursor variable use within SQL procedures. The script contains a row data type definition, a cursor type definition and two SQL procedure definitions. The procedure P_CALLER contains a cursor variable definition and a call to a procedure named P. The procedure P defines a cursor, opens the cursor and passes the cursor as an output parameter value. The procedure P_CALLER receives the cursor parameter, fetches the cursor value into a local variable, and then sets two output parameter values named edlvel and lastname based on the local variable value.

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

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

CREATE TYPE myCursorType AS myRowType CURSOR@

CREATE PROCEDURE P(IN pempNo VARCHAR(8), OUT pcv1 myCursorType)
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 myCursorType;

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

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

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

When this script is run, the following output is generated:

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(15))
DB20000I  The SQL command completed successfully.

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

CREATE PROCEDURE P(IN pempNo VARCHAR(8), OUT pcv1 myCursorType)
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 myCursorType;

  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