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
    
Feedback
编组 3备份
    openGauss 2024-04-12 00:46:38
    cancel