• Data Warehouse Service

dws
  1. Help Center
  2. Data Warehouse Service
  3. Developer Guide
  4. SQL Reference
  5. SQL Syntax
  6. CURSOR

CURSOR

Function

CURSOR defines a cursor. This command retrieves few rows of data in a query.

To process SQL statements, the stored procedure process assigns a memory segment to store context association. Cursors are handles or pointers to context regions. With cursors, stored procedures can control alterations in context regions.

It enables changes in the context area to be controlled during the stored procedure. 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 a text format. If data is returned in text format, the client-end application needs to convert the data back to a binary format for processing. FETCH implements conversion between binary data and text data.

Precautions

CURSOR is used only in transaction blocks.

Use a binary cursor unless necessary, since a text cursor occupies larger storage space than a binary cursor. A binary cursor returns internal binary data, which is easier to operate. To return data in text format, it is advisable to retrieve data in text format, therefore reducing workload at the client end. For example, the value 1 in an integer column of a query is returned as a character string 1 if a default cursor is used, but is returned as a 4-byte binary value (big-endian) if a binary cursor is used.

Syntax

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

Parameter Description

  • cursor_name

    Specifies the name of a cursor to be created.

    Value range: Its value must comply with the database naming convention.

  • BINARY

    Specifies that data retrieved by the cursor will be returned in binary format, not in text format.

  • NO SCROLL

    Specifies the mode of data retrieval by the cursor.

    • NO SCROLL: If NO SCROLL is specified, backward fetches will be rejected.
    • Not stated: The system automatically determines whether the cursor can be used for backward fetches based on the execution plan.
  • WITH HOLD | WITHOUT HOLD

    Specifies whether the cursor can still be used after the cursor creation event.

    • WITH HOLD indicates that the cursor can still be used.
    • WITHOUT HOLD indicates that the cursor cannot be used.
    • If neither WITH HOLD nor WITHOUT HOLD is specified, the default value is WITHOUT HOLD.
  • query

    The SELECT or VALUES clause specifies the row to return the cursor value.

    Value range: SELECT or VALUES clause

Examples

See Examples in FETCH.

Helpful Links

FETCH