Developer Reference : Data Access Methods : SQL Engine Reference : SQL Syntax Reference : DECLARE CURSOR
 
DECLARE CURSOR
The DECLARE CURSOR statement defines a SQL cursor.
Syntax
DECLARE cursor-name CURSOR FOR select-statement [ FOR UPDATE | FOR READ ONLY ]
 
cursor-name ::= user-defined-name
Remarks
The DECLARE statement is only allowed inside of a stored procedure or a trigger, since cursors and variables are only allowed inside of stored procedures and triggers.
The default behavior for cursors is read-only. Therefore, you must use FOR UPDATE to explicitly designate an update (write or delete).
Examples
The following example creates a cursor that selects values from the Degree, Residency, and Cost_Per_Credit columns in the Tuition table and orders them by ID number.
DECLARE BTUCursor CURSOR
FOR SELECT Degree, Residency, Cost_Per_Credit
FROM Tuition
ORDER BY ID;
============ 
The following example uses FOR UPDATE to ensure a delete.
CREATE PROCEDURE MyProc(IN :CourseName CHAR(7)) AS
BEGIN
DECLARE c1 CURSOR FOR SELECT name FROM course WHERE name = :CourseName FOR UPDATE;
OPEN c1;
FETCH NEXT FROM c1 INTO :CourseName;
DELETE WHERE CURRENT OF c1;
CLOSE c1;
END;
CALL MyProc('HIS 305')
============ 
DECLARE cursor1 CURSOR
FOR SELECT Degree, Residency, Cost_Per_Credit
FROM Tuition ORDER BY ID
FOR UPDATE;
See Also
CREATE PROCEDURE, CREATE TRIGGER