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