Referencing cursor variables

Cursor variables can be referenced in multiple ways as part of cursor operations related to retrieving and accessing a result set or when calling a procedure and passing cursor variables as parameters.

About this task

The following statements can be used to reference cursor variables within an SQL PL context:

CALL
SET
OPEN
FETCH
CLOSE

The OPEN, FETCH, and CLOSE statements are most often used together when accessing the result set associated with a cursor variable. The OPEN statement is used to initialize the result set associated with the cursor variable. Upon successful execution of this statement, the cursor variable is associated with the result set and the rows in the result set can be accessed. The FETCH statement is used to specifically retrieve the column values in the current row being accessed by the cursor variable. The CLOSE statement is used to end the processing of the cursor variable.

The following is an example of a created row data type definition and an SQL procedure definition that contains a cursor variable definition. Use of the OPEN, FETCH, and CLOSE statements with the cursor variable are demonstrated within the SQL procedure:

CREATE TYPE simpleRow AS ROW (c1 INT, c2 INT, c3 INT);

CREATE PROCEDURE P(OUT p1 INT, OUT p2 INT, PUT p3 INT, OUT pRow simpleRow)
LANGUAGE SQL
BEGIN

      CREATE TYPE simpleCur AS CURSOR RETURNS simpleRow
      DECLARE c1 simpleCur;
      DECLARE localVar1 INTEGER;
      DECLARE localVar2 INTEGER;
      DECLARE localVar3 INTEGER;
      DECLARE localRow simpleRow;

      SET c1 = CURSOR FOR SELECT * FROM T;

      OPEN C1;

      FETCH c1 INTO localVar1, localVar2, localVar3;

      FETCH c1 into localRow;

      SET p1 = localVar1;

      SET p2 = localVar2;
 
      SET p3 = localVar3;

      SET pRow = localRow;

      CLOSE c1;

END;

Cursor variables can also be referenced as parameters in the CALL statement. As with other parameters, cursor variable parameters are simply referenced by name. The following is an example of a CALL statement within an SQL procedure that references a cursor variable named curVar which is an output parameter:

CALL P2(curVar);