Cursors
Tags: opengauss
Published: 2023-04-04 01:34:32

To process SQL statements, the stored procedure process 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.

Syntax
Define a cursor.

""
CURSOR cursor_name
    [ BINARY ]  [ NO SCROLL ]  [ { WITH | WITHOUT } HOLD ]
    FOR query ;
Retrieve data by using a created cursor.

""
FETCH [ direction { FROM | IN } ] cursor_name;
The direction clause specifies optional parameters.

""
NEXT
   | PRIOR
   | FIRST
   | LAST
   | ABSOLUTE count
   | RELATIVE count
   | count
   | ALL
   | FORWARD
   | FORWARD count
   | FORWARD ALL
   | BACKWARD
   | BACKWARD count
   | BACKWARD ALL
Reposition a cursor without retrieving any data.

The MOVE statement works exactly like the FETCH statement, except that it only repositions the cursor and does not return rows.

""
MOVE [ direction [ FROM | IN ] ] cursor_name;
The direction clause specifies optional parameters.

""
NEXT
   | PRIOR
   | FIRST
   | LAST
   | ABSOLUTE count
   | RELATIVE count
   | count
   | ALL
   | FORWARD
   | FORWARD count
   | FORWARD ALL
   | BACKWARD
   | BACKWARD count
   | BACKWARD ALL
Close a cursor and release all resources associated with the cursor.

""
CLOSE { cursor_name | ALL } ;
Parameter Description
cursor_name

Specifies the name of the cursor to be created or closed.

BINARY

Specifies that data retrieved by a cursor will be returned in binary format, not in text format.

NO SCROLL

Specifies how a 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.
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.
Cross-node transactions (for example, DDL-contained transactions created in openGauss with multiple DBnode) do not support WITH HOLD.
query

Uses the SELECT or VALUES clause to specify the rows to be returned by a cursor.

Value range: SELECT or VALUES clause

direction_clause

Defines the fetch direction.

Value range:

NEXT (default value)

Fetches the next row.

PRIOR

Fetches the prior row.

FIRST

Fetches the first row of the query (same as ABSOLUTE 1).

LAST

Fetches the last row of the query (same as ABSOLUTE – 1).

ABSOLUTE count

Fetches the _count_th row of the query.

ABSOLUTE fetches are not any faster than navigating to the desired row with a relative move, because the underlying implementation must traverse all the intermediate rows anyway.

Value range: a possibly-signed integer

If count is positive, the _count_th row of the query will be fetched.
If count is negative, the _abs(count)_th row from the end of the query result will be fetched.
If count is set to 0, the cursor is positioned before the first row.
RELATIVE count

Fetches the _count_th succeeding row or the _count_th prior row.

Value range: a possibly-signed integer

If count is positive, the _count_th succeeding rows will be fetched.
If count is negative, the _abs(count)_th prior rows will be fetched.
If the current row contains no data, RELATIVE 0 returns NULL.
count

Fetches the next count rows (same as FORWARD count).

ALL

Fetches all remaining rows (same as FORWARD ALL).

FORWARD

Fetches the next row (same as NEXT).

FORWARD count

Fetches the count succeeding rows or count prior rows.

FORWARD ALL

Fetches all remaining rows.

BACKWARD

Fetches the prior row (same as PRIOR).

BACKWARD count

Fetches the prior count rows (scanning backwards).

Value range: a possibly-signed integer

If count is positive, the prior count rows will be fetched.
If count is a negative, the succeeding abs (count) rows will be fetched.
BACKWARD 0 re-fetches the current row, if any.
BACKWARD ALL

Fetches all prior rows (scanning backwards).

{ FROM | IN } cursor_name

Specifies the cursor name using the keyword FROM or IN.

Value range: an existing cursor name

ALL

Closes all open cursors.

Examples
Assume that the customer_t1 table exists and contains the following data:

""
openGauss=# SELECT * FROM customer_t1;
 c_customer_sk | c_customer_id | c_first_name | c_last_name | amount
---------------+---------------+--------------+-------------+--------
          3769 |               | Grace        |             |
          3769 | hello         |              |             |
          6885 | maps          | Joes         |             |   2200
          4321 | tpcds         | Lily         |             |   3000
          9527 | world         | James        |             |   5000
(5 rows)
Read a table using a cursor.

""
-- Start a transaction.
openGauss=# START TRANSACTION;
START TRANSACTION

-- Set up cursor1.
openGauss=# CURSOR cursor1 FOR SELECT * FROM customer_t1;
DECLARE CURSOR

-- Fetch the first three rows in cursor1.
openGauss=# FETCH FORWARD 3 FROM cursor1;
 c_customer_sk | c_customer_id | c_first_name | c_last_name | amount
---------------+---------------+--------------+-------------+--------
          3769 |               | Grace        |             |
          3769 | hello         |              |             |
          6885 | maps          | Joes         |             |   2200
(3 rows)

-- Close the cursor and commit the transaction.
openGauss=# CLOSE cursor1;
CLOSE CURSOR

-- End the transaction.
openGauss=# END;
COMMIT
Read the VALUES clause using a cursor.

""
-- Start a transaction.
openGauss=# START TRANSACTION;
START TRANSACTION

-- Set up cursor2.
openGauss=# CURSOR cursor2 FOR VALUES(1,2),(0,3) ORDER BY 1;
DECLARE CURSOR

-- Fetch the first two rows in cursor2.
openGauss=# FETCH FORWARD 2 FROM cursor2;
 column1 | column2
---------+---------
       0 |       3
       1 |       2
(2 rows)

-- Close the cursor and commit the transaction.
openGauss=# CLOSE cursor2;
CLOSE CURSOR

-- End the transaction.
openGauss=# END;
COMMIT
Use the WITH HOLD cursor.

""
-- Start a transaction.
openGauss=# START TRANSACTION;

-- Set up the WITH HOLD cursor.
openGauss=# DECLARE cursor1 CURSOR WITH HOLD FOR SELECT * FROM customer_t1;

-- Fetch the first two rows in cursor1.
openGauss=# FETCH FORWARD 2 FROM cursor1;
 c_customer_sk | c_customer_id | c_first_name | c_last_name | amount
---------------+---------------+--------------+-------------+--------
          3769 |               | Grace        |             |
          3769 | hello         |              |             |
(2 rows)

-- End the transaction.
openGauss=# END;
COMMIT

-- Fetch the next row in cursor1.
openGauss=# FETCH FORWARD 1 FROM cursor1;
 c_customer_sk | c_customer_id | c_first_name | c_last_name | amount
---------------+---------------+--------------+-------------+--------
          6885 | maps          | Joes         |             |   2200
(1 row)

-- Close the cursor.
openGauss=# CLOSE cursor1;
CLOSE CURSOR
Use the MOVE statement.

""
-- Start a transaction.
openGauss=# START TRANSACTION;
START TRANSACTION

-- Define cursor1.
openGauss=# CURSOR cursor1 FOR SELECT * FROM customer_t1;
DECLARE CURSOR

-- Skip the first three rows in cursor1.
openGauss=# MOVE FORWARD 1 FROM cursor1;
MOVE 1

-- Fetch the first two rows in cursor1.
openGauss=# FETCH 2 FROM cursor1;
 c_customer_sk | c_customer_id | c_first_name | c_last_name | amount
---------------+---------------+--------------+-------------+--------
          3769 | hello         |              |             |
          6885 | maps          | Joes         |             |   2200
(2 rows)


-- Close the cursor.
openGauss=# CLOSE cursor1;
CLOSE CURSOR

-- End the transaction.
openGauss=# END;
COMMIT
Previous