Reference Guide > TDV SQL Script > SQL Script Statement Reference > DECLARE CURSOR of Type Variable
 
DECLARE CURSOR of Type Variable
You can define a new cursor variable in SQL Script by providing a unique name and optionally specifying its data type.
For details, see Attributes of Cursors, OPEN, FETCH, and CLOSE.
Syntax
DECLARE <variableName> CURSOR [<dataType>]
Remarks
• The <dataType> is optional and can be a named ROW data type or the syntax for a ROW data type.
• When declared, the cursor variable is initialized to NULL. It cannot be initialized to any other value at declaration.
• You can use the SCROLL keyword in an OPEN statement to open a cursor after a row has been fetched from a cursor, as follows:
DECLARE i INT;
DECLARE x CURSOR (a int) FOR SELECT COUNT(*) FROM /services/databases/system/ALL_USERS;
OPEN x SCROLL;
Examples
The following example returns the first name.
PROCEDURE p (OUT p_name VARCHAR)
BEGIN
  DECLARE c CURSOR (name VARCHAR);
  OPEN c FOR SELECT name FROM /shared/T;
  FETCH c INTO p_name;
  CLOSE c;
END
 
The following example closes and then reopens c with the same query, and later closes it and reopens it with a new query.
PROCEDURE p (OUT p_name VARCHAR)
BEGIN
  DECLARE c CURSOR (name VARCHAR);
  OPEN c FOR SELECT name FROM /shared/T;
  CLOSE c;
  OPEN c;
  CLOSE c;
  OPEN c FOR SELECT name FROM /share/U WHERE birthdate > '2000-01-01';
  CLOSE c;
END