DECLARE CURSOR

The DECLARE CURSOR statement defines an SQL cursor.

Syntax

DECLARE cursor-name CURSOR  FOR select-statement [ FOR UPDATE 
| FOR READ ONLY ] 

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

Cursor loop example

CREATE TRIGGER