FETCH

Syntax

FETCH [ [NEXT] FROM ]cursor-name INTO variable-name

cursor-name ::= user-defined-name

Remarks

A FETCH statement positions a SQL cursor on a specified row of a table and retrieves values from that row by placing them into the variables in a target list.

You may choose to omit the NEXT and FROM keywords while fetching data from any cursor.

Note PSQL supports only the forward-only cursor. So, you will not be able to control the flow of the cursor records even by omitting NEXT FROM.

Examples

The FETCH statement in this example retrieves values from cursor c1 into the CourseName variable. The Positioned UPDATE statement in this example updates the row for Modern European History (HIS 305) in the Course table in the Demodata sample database:

CREATE PROCEDURE UpdateClass();

BEGIN

DECLARE :CourseName CHAR(7);

DECLARE :OldName CHAR(7);

DECLARE c1 cursor FOR SELECT name FROM course WHERE name = :CourseName;

OPEN c1;

SET :CourseName = 'HIS 305';

FETCH NEXT FROM c1 INTO :OldName;

UPDATE SET name = 'HIS 306' WHERE CURRENT OF c1;

END;

============

CREATE PROCEDURE MyProc(OUT :CourseName CHAR(7)) AS

BEGIN

DECLARE cursor1 CURSOR

FOR SELECT Degree, Residency, Cost_Per_Credit FROM Tuition ORDER BY ID;

OPEN cursor1;

FETCH NEXT FROM cursor1 INTO :CourseName;

CLOSE cursor1;

END