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.

  Note:

  In cmdsql, only adding the NO SCROLL keyword or not declaring the scroll option is supported, the SCROLL keyword is not supported.

  In stored procedures, adding NO SCROLL, SCROLL keyword or not declaring the scroll option is supported.

  In explicit cursors, only cursors declared as NO SCROLL can be executed in parallel. It is recommended to set cursors that do not need to retrieve rows of data in reverse order to NO SCROLL.

• 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 MogDB 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:

MogDB=# 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.
  MogDB=# START TRANSACTION;
  START TRANSACTION
  
  -- Set up cursor1.
  MogDB=# CURSOR cursor1 FOR SELECT * FROM customer_t1;
  DECLARE CURSOR
  
  -- Fetch the first three rows in cursor1.
  MogDB=# 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.
  MogDB=# CLOSE cursor1;
  CLOSE CURSOR
  
  -- End the transaction.
  MogDB=# END;
  COMMIT

• Read the VALUES clause using a cursor.

  -- Start a transaction.
  MogDB=# START TRANSACTION;
  START TRANSACTION
  
  -- Set up cursor2.
  MogDB=# CURSOR cursor2 FOR VALUES(1,2),(0,3) ORDER BY 1;
  DECLARE CURSOR
  
  -- Fetch the first two rows in cursor2.
  MogDB=# FETCH FORWARD 2 FROM cursor2;
   column1 | column2
  ---------+---------
         0 |       3
         1 |       2
  (2 rows)
  
  -- Close the cursor and commit the transaction.
  MogDB=# CLOSE cursor2;
  CLOSE CURSOR
  
  -- End the transaction.
  MogDB=# END;
  COMMIT

• Use the WITH HOLD cursor.

  -- Start a transaction.
  MogDB=# START TRANSACTION;
  
  -- Set up the WITH HOLD cursor.
  MogDB=# DECLARE cursor1 CURSOR WITH HOLD FOR SELECT * FROM customer_t1;
  
  -- Fetch the first two rows in cursor1.
  MogDB=# 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.
  MogDB=# END;
  COMMIT
  
  -- Fetch the next row in cursor1.
  MogDB=# 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.
  MogDB=# CLOSE cursor1;
  CLOSE CURSOR

• Use the MOVE statement.

  -- Start a transaction.
  MogDB=# START TRANSACTION;
  START TRANSACTION
  
  -- Define cursor1.
  MogDB=# CURSOR cursor1 FOR SELECT * FROM customer_t1;
  DECLARE CURSOR
  
  -- Skip the first three rows in cursor1.
  MogDB=# MOVE FORWARD 1 FROM cursor1;
  MOVE 1
  
  -- Fetch the first two rows in cursor1.
  MogDB=# 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.
  MogDB=# CLOSE cursor1;
  CLOSE CURSOR
  
  -- End the transaction.
  MogDB=# END;
  COMMIT