Contents Index DECLARE statement DECLARE CURSOR statement [T-SQL]

ASA SQL Reference
  SQL Statements

DECLARE CURSOR statement [ESQL] [SP]


Description 

Use this statement to declare a cursor. Cursors are the primary means for manipulating the results of queries.

Syntax 1 [ESQL] 

DECLARE cursor-name
UNIQUE ]
[   NO SCROLL
  | DYNAMIC SCROLL
  | SCROLL
  | INSENSITIVE
  | SENSITIVE
]
CURSOR FOR
select-statement
statement-name
      [ FOR { UPDATE [ cursor-concurrency ] | READ ONLY } ]
call-statement }

Syntax 2 [SP] 

DECLARE cursor-name
[   NO SCROLL
  | DYNAMIC SCROLL
  | SCROLL
  | INSENSITIVE
  | SENSITIVE
]
CURSOR FOR
select -statement
FOR { UPDATE [ cursor-concurrency ] | READ ONLY } ]
call-statement
USING variable-name }

cursor-name :  identifier

statement-name :   identifier | hostvar

variable-name :   identifier

cursor-concurrency :
BY { VALUES | TIMESTAMP | LOCK }

Parameters 

UNIQUE    When a cursor is declared UNIQUE, the query is forced to return all the columns required to uniquely identify each row. Often this means ensuring that all columns in the primary key or a uniqueness table constraint are returned. Any columns that are required but were not specified in the query are added to the result set.

A DESCRIBE done on a UNIQUE cursor sets the following additional flags in the indicator variables:

NO SCROLL    A cursor declared NO SCROLL is restricted to moving forwards through the result set using FETCH NEXT and FETCH RELATIVE 0 seek operations.

As rows cannot be returned to once the cursor leaves the row, there are no sensitivity restrictions on the cursor. Consequently, when a NO SCROLL cursor is requested, Adaptive Server Anywhere supplies the most efficient kind of cursor, which is an asensitive cursor.

For more information, see Asensitive cursors.

DYNAMIC SCROLL    DYNAMIC SCROLL is the default cursor type. DYNAMIC SCROLL cursors can use all formats of the FETCH statement.

When a DYNAMIC SCROLL cursor is requested, Adaptive Server Anywhere supplies an asensitive cursor. When using cursors there is always a trade-off between efficiency and consistency. Asensitive cursors provide efficient performance at the expense of consistency.

For more information, see Asensitive cursors.

SCROLL    A cursor declared SCROLL can use all formats of the FETCH statement. When a SCROLL cursor is requested, Adaptive Server Anywhere supplies a value-sensitive cursor.

For more information, see Value-sensitive cursors.

Adaptive Server Anywhere must execute value-sensitive cursors in such a way that result set membership is guaranteed. DYNAMIC SCROLL cursors are more efficient and should be used unless the consistent behavior of SCROLL cursors is required.

INSENSITIVE    A cursor declared INSENSITIVE has its membership fixed when it is opened; a temporary table is created with a copy of all the original rows. FETCHING from an INSENSITIVE cursor does not see the effect of any other INSERT, UPDATE, or DELETE statement, or any other PUT, UPDATE WHERE CURRENT, DELETE WHERE CURRENT operations on a different cursor. It does see the effect of PUT, UPDATE WHERE CURRENT, DELETE WHERE CURRENT operations on the same cursor.

For more information, see Insensitive cursors.

SENSITIVE    A cursor declared SENSITIVE is sensitive to changes to membership or values of the result set.

For more information, see Sensitive cursors.

FOR statement-name    Statements are named using the PREPARE statement. Cursors can be declared only for a prepared SELECT or CALL.

FOR UPDATE | READ ONLY    A cursor declared FOR READ ONLY may not be used in an UPDATE (positioned) or a DELETE (positioned) operation. FOR UPDATE is the default.

In response to any request for a cursor that specifies FOR UPDATE, Adaptive Server Anywhere provides either a value-sensitive cursor or an asensitive cursor. Insensitive and asensitive cursors are not updateable.

USING variable-name    For use within stored procedures only. The variable is a string containing a SELECT statement for the cursor. The variable must be available when the DECLARE is processed, and so must be one of the following:

Usage 

The DECLARE CURSOR statement declares a cursor with the specified name for a SELECT statement or a CALL statement.

Permissions 

None.

Side effects 

None.

See also 

PREPARE statement [ESQL]

OPEN statement [ESQL] [SP]

EXPLAIN statement [ESQL]

SELECT statement

CALL statement

Standards and compatibility 
Example 

The following example illustrates how to declare a scroll cursor in Embedded SQL:

EXEC SQL DECLARE cur_employee SCROLL CURSOR
FOR SELECT * FROM employee;

The following example illustrates how to declare a cursor for a prepared statement in Embedded SQL:

EXEC SQL PREPARE employee_statement
FROM 'SELECT emp_lname FROM employee';
EXEC SQL DECLARE cur_employee CURSOR
FOR employee_statement;

The following example illustrates the use of cursors in a stored procedure:

BEGIN
  DECLARE cur_employee CURSOR FOR
      SELECT emp_lname
      FROM employee;
  DECLARE name CHAR(40);
  OPEN cur_employee;
  LOOP
    FETCH NEXT cur_employee INTO name;
    ...
  END LOOP
  CLOSE cur_employee;
END

Contents Index DECLARE statement DECLARE CURSOR statement [T-SQL]