CURSOR

Description

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

To process SQL statements, the stored procedure thread assigns a memory segment to store context association. Cursors are handles or pointers pointing 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, client applications need to convert the data back to the binary format for processing. FETCH implements conversion between binary data and text data.
Binary cursors should be used carefully. Text usually occupies larger space than binary data. A binary cursor returns internal binary data, which is easier to operate. A text cursor returns text, which is easier to retrieve and therefore reduces workload on the client. As an example, if a query returns a value of one from an integer column, you would get a string of 1 with a default cursor, whereas with a binary cursor you would get a 4-byte field containing the internal representation of the value (in big-endian byte order).
Parameters can be defined for static cursors. The parameters support default values. However, the default value of a parameter cannot be a variable outside the package. You can define a variable in the package, assign the variable outside the package to the defined variable, and use the variable as the default value.
The definition of a cursor generates a query object. Currently, stored procedures do not support the creation of dependencies on query objects.

Syntax

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

Parameters

cursor_name
Specifies the name of the cursor to be created.

Value range: a string. It must comply with the naming convention.
BINARY
Specifies that data retrieved by a cursor will be returned in binary format, not in text format.
NO SCROLL
Specifies how the cursor retrieves rows.
- NO SCROLL: specifies that the cursor cannot be used to retrieve rows in a nonsequential fashion.
- Unspecified: Based on the query's execution plan, the system automatically determines whether the cursor can be used to retrieve rows in a nonsequential fashion.
In cmdsql, only the NO SCROLL keyword can be added or the scrolling option is not added. The SCROLL keyword cannot be added.

In a stored procedure, the keywords NO SCROLL and SCROLL can be added, or the scrolling option is not added.

Among explicit cursors, only cursors declared as NO SCROLL can be concurrently executed. You are advised to set this parameter to NO SCROLL for cursors that do not need to retrieve data rows in reverse order.
WITH HOLD | WITHOUT HOLD
Specifies whether a cursor can be used after the transaction that created it ends.
- WITH HOLD: The cursor can be used after the transaction that created it ends.
- WITHOUT HOLD: The cursor cannot be used outside of the transaction that created it.
- If neither WITH HOLD nor WITHOUT HOLD is specified, the default is WITHOUT HOLD.
query
Uses the SELECT or VALUES clause to specify the rows to be returned by a cursor.

Value range: SELECT or VALUES clause

Examples

Example 1:

-- Create a table and insert data into the table.
gaussdb=# CREATE TABLE tbl_test(c1 int);
gaussdb=# INSERT INTO tbl_test VALUES (generate_series(1,20));

-- Set up cursor1.
gaussdb=# BEGIN;
gaussdb=# CURSOR cursor1 FOR SELECT * FROM tbl_test ORDER BY 1;

-- Run the FETCH command to retrieve three rows of data.
gaussdb=# FETCH FORWARD 3 FROM cursor1;
 c1 
----
  1
  2
  3
(3 rows)

-- Run the MOVE command to move the cursor backwards by two rows. No result is returned.
gaussdb=# MOVE FORWARD 2 FROM cursor1;
MOVE 2

-- Run the FETCH command to retrieve two rows of data.
gaussdb=# FETCH FORWARD 2 FROM cursor1;
 c1 
----
  6
  7
(2 rows)

-- Close the cursor and end the transaction.
gaussdb=# CLOSE cursor1;
gaussdb=# END;

Example 2: Cursor with the WITH HOLD attribute

-- Set up a WITH HOLD cursor named cursor2.
gaussdb=# BEGIN;
gaussdb=# CURSOR cursor2 WITH HOLD FOR SELECT * FROM tbl_test ORDER BY 1;

-- Run the FETCH command to retrieve three rows of data.
gaussdb=# FETCH FORWARD 3 FROM cursor2;
 c1 
----
  1
  2
  3
(3 rows)

-- Different from common cursors, cursors with the WITH HOLD attribute can still be used after a transaction ends.
gaussdb=# END;
gaussdb=# FETCH FORWARD 2 FROM cursor2;
 c1 
----
  4
  5
(2 rows)

-- Close the cursor.
gaussdb=# CLOSE cursor2;

-- Delete.
gaussdb=# DROP TABLE tbl_test;