Cursors
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
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