DECLARE

Description

DECLARE defines a cursor to retrieve a small number of rows at a time out of a larger query and can be the start of an anonymous block.

This section describes usage of cursors. The usage of anonymous blocks is available in BEGIN.

To process SQL statements, the stored procedure thread 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.

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.

Precautions

• CURSOR is used only in transaction blocks.
• 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 value containing the internal representation of the value (in big-endian byte order).

Syntax

• Define a cursor.

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

  

• Enable an anonymous block.

  1 2 3 4 5

  [DECLARE [declare_statements]] BEGIN execution_statements END; /

  

Parameters

• cursor_name

  Specifies the name of the cursor to be created.

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

• BINARY

  Specifies that data retrieved by a cursor will be returned in binary format, not 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.
• 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.
    

    For a cursor declared as WITH HOLD, all data of the cursor is cached when a transaction ends. If the cursor has a large amount of data, this process may take a long time.

• query

  Uses the SELECT or VALUES clause to specify the rows to be returned by a cursor.

  Value range: SELECT or VALUES clause

• declare_statements

  Declares a variable, including its name and type, for example, sales_cnt int.

• execution_statements

  Specifies the statement to be executed in an anonymous block.

  Value range: an existing function name

Examples

For details about how to start an anonymous block, see Examples in section "BEGIN."

For details about how to define a cursor, see Examples in section "FETCH."

Helpful Links

BEGIN and FETCH