CURSOR

Function

CURSOR defines a cursor to retrieve a small number of rows at a time out of a larger query.

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.

Precautions

• CURSOR is used only in transaction blocks.
• Generally, CURSOR and SELECT both have text returns. Since data is stored in binary format in the system, the system needs to convert the data from the binary format to the text format. If data is returned in text format, client applications need to convert the data back to the binary format for processing. FETCH implements conversion between binary data and text data.
• Binary cursors should be used carefully. Text usually occupies larger space than binary data. A binary cursor returns internal binary data, which is easier to operate. A text cursor returns text, which is easier to retrieve and therefore reduces workload on the client. As an example, if a query returns a value of one from an integer column, you would get a string of 1 with a default cursor, whereas with a binary cursor you would get a 4-byte field containing the internal representation of the value (in big-endian byte order).

Syntax

Cursor ::= CURSOR cursor_name
    [ BINARY ]  [ NO SCROLL ]  [ { WITH | WITHOUT } HOLD ]
    FOR query ;

Parameter Description

• cursor_name

  Specifies the name of the cursor to be created.

  Value range: a string. It must comply with the naming convention.

• BINARY

  Causes the cursor to return data in binary rather than in text format.

• NO SCROLL

  Specifies how the 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 the cursor can continue to be used after the transaction that created it successfully commits.

  • WITH HOLD: The cursor can continue to be used after the transaction that created it successfully commits.
  • 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 a SELECT or VALUES clause to specify the rows to be returned by the cursor.

  Value range: SELECT or VALUES clause

Examples

See Examples in FETCH.

Helpful Links

FETCH