HomeMogDBMogDB StackUqbar
v5.0

Documentation:v5.0

Supported Versions:

Other Versions:

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.

    img 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
Copyright © 2011-2024 www.enmotech.com All rights reserved.