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;