CURSOR

Function

This statement is used to create a cursor and retrieve specified rows of data from a query.

To process SQL statements, the stored procedure process assigns a memory segment to store context association. Cursors are handles or pointers to context regions. With cursors, stored procedures can control alterations in context regions.

Precautions

• CURSOR is used only in transaction blocks.
• Generally, CURSOR and SELECT both have text returns. Since data is stored in binary format in the system, the system needs to convert the data from the binary format to the text format. If data is returned in text format, the client-end application needs to convert the data back to a binary format for processing. FETCH implements conversion between binary data and text data.
• Use a binary cursor unless necessary, since a text cursor occupies larger storage space than a binary cursor. A binary cursor returns internal binary data, which is easier to operate. To return data in text format, it is advisable to retrieve data in text format, therefore reducing workload at the client end. For example, the value 1 in an integer column of a query is returned as a character string 1 if a default cursor is used, but is returned as a 4-byte binary value (big-endian) if a binary cursor is used.

Syntax

1
2
3

CURSOR cursor_name
    [ BINARY ]  [ NO SCROLL ]  [ { WITH | WITHOUT } HOLD ]
    FOR query ;

Parameter Description

Examples

Start a transaction.

1

START TRANSACTION;

Create a cursor named cursor1:

1

CURSOR cursor1 FOR SELECT * FROM tpcds.customer_address ORDER BY 1;

create a cursor named cursor2:

1

CURSOR cursor2 FOR VALUES(1,2),(0,3) ORDER BY 1;

Usage of the WITH HOLD cursor:

1. Set up the WITH HOLD cursor.

   1	DECLARE cursor3 CURSOR WITH HOLD FOR SELECT * FROM tpcds.customer_address ORDER BY 1;

2. Fetch the first two rows from cursor3.

   1	FETCH FORWARD 2 FROM cursor3;

3. End the transaction.

   1

   END;

4. Fetch the next row from cursor3.

   1	FETCH FORWARD 1 FROM cursor3;

5. Close the cursor.

   1	CLOSE cursor3;

Helpful Links

FETCH