DECLARE CURSOR Statement [ESQL] [SP]

Declares a cursor. Cursors are the primary means for manipulating the results of queries.

Syntax

DECLARE cursor-name
[   SCROLL
  | NO SCROLL 
  | DYNAMIC SCROLL 
]
CURSOR FOR
{ select-statement
| statement-name 
  [ FOR { READ ONLY | UPDATE [ OF column-name-list ] } ] 
| USING variable-name }

Parameters

cursor-name: – identifier
statement-name: – identifier | host-variable
column-name-list: – identifiers
variable-name: – identifier

Examples

Example 1 – Declare a scroll cursor in Embedded SQL:

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

Example 2 – Declare a cursor for a prepared statement in Embedded SQL:

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

Example 3 – Use cursors in a stored procedure:

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

Usage

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

SCROLL—A cursor declared as SCROLL supports the NEXT, PRIOR, FIRST, LAST, ABSOLUTE, and RELATIVE options of the FETCH statement. A SCROLL cursor lets you fetch an arbitrary row in the result set while the cursor is open.

NO SCROLL—A cursor declared as NO SCROLL is restricted to moving forward through the result set using only the FETCH NEXT and FETCH ABSOLUTE (0) seek operations.

DYNAMIC SCROLL—A cursor declared as DYNAMIC SCROLL supports the NEXT, PRIOR, FIRST, LAST, ABSOLUTE, and RELATIVE options of the FETCH statement. A DYNAMIC SCROLL cursor lets you fetch an arbitrary row in the result set while the cursor is open.

Since 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, Sybase IQ supplies the most efficient kind of cursor, which is an asensitive cursor.

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

FOR READ ONLY—A cursor declared FOR READ ONLY may not be used in a positioned UPDATE or a positioned DELETE operation. READ ONLY is the default value of the FOR clause.

A cursor declared FOR READ ONLY sees the version of table(s) on which the cursor is declared when the cursor is opened, not the version of table(s) at the time of the first FETCH.

For example, when the cursor is fetched, only one row can be fetched from the table:

CREATE TABLE t1 ( c1 INT );
INSERT t1 VALUES ( 1 );

BEGIN
DECLARE t1_cursor CURSOR FOR SELECT * FROM t1
FOR READ ONLY;
OPEN t1_cursor;
INSERT t1 VALUES ( 2 );
FETCH T1_CURSOR;
END

FOR UPDATE—You can update the cursor result set of a cursor declared FOR UPDATE. Only asensitive behavior is supported for updatable cursors; any other sensitivity is ignored.

When the cursor is opened, exclusive table locks are taken on all tables that are opened for update. Standalone LOAD TABLE, UPDATE, INSERT, DELETE, and TRUNCATE statements are not allowed on tables that are opened for update in the same transaction, since Sybase IQ permits only one statement to modify a table at a time. You can open only one updatable cursor on a specific table at a time.

Updatable cursors are allowed to scroll, except over Open Client.

OF column-name-list—The list of columns from the cursor result set (specified by the select-statement) defined as updatable.

USING variable-name—You can declare a cursor on a variable in stored procedures and user-defined functions. 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:

A parameter to the procedure. For example:

create function get_row_count(in qry varchar)
returns int
begin
    declare crsr cursor using qry;
    declare rowcnt int;

    set rowcnt = 0;
    open crsr;
    lp: loop
        fetch crsr;
        if SQLCODE <> 0 then leave lp end if;
        set rowcnt = rowcnt + 1;
    end loop;
    return rowcnt;
end

Nested inside another BEGIN…END after the variable has been assigned a value. For example:

create procedure get_table_name(
  in id_value int, out tabname char(128))

begin
  declare qry varchar;

  set qry = 'select table_name from SYS.ISYSTAB ' ||
        'where table_id=' || string(id_value);
  begin
    declare crsr cursor using qry;

    open crsr;
    fetch crsr into tabname;
    close crsr;
 end
end

Embedded SQL

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

Updatable Cursor Support

Sybase IQ support of updatable cursors is similar to SQL Anywhere support of updatable cursors. For a full discussion of cursor types and working with cursors, see SQL Anywhere 11.0.1 > SQL Anywhere Server – Programming > Introduction to Programming with SQL Anywhere > Using SQL in applications > Introduction to cursors.

Sybase IQ supports one type of cursor sensitivity, which is defined in terms of which changes to underlying data are visible. All Sybase IQ cursors are asensitive, which means that changes might be reflected in the membership, order, or values of the result set seen through the cursor, or might not be reflected at all.

With an asensitive cursor, changes effected by positioned UPDATE and positioned DELETE statements are visible in the cursor result set, except where client-side caching prevents seeing these changes. Inserted rows are not visible.

Rows that are updated so that they no longer meet the requirements of the WHERE clause of the open cursor are still visible.

When using cursors, there is always a trade-off between efficiency and consistency. Asensitive cursors provide efficient performance at the expense of consistency.

Sybase IQ supports updatable cursors on single tables.

LONG VARCHAR and LONG BINARY data types are not supported in updatable cursors. For information on the LONG VARCHAR and LONG BINARY data types in Sybase IQ, see Unstructured Data Analytics in Sybase IQ.

Scalar user-defined functions and user-defined aggregate functions are not supported in updatable cursors.

Supported query specifications for updatable cursors in Sybase IQ are:

Expressions in the select list against columns that are not functionally dependent on columns being updated
Arbitrary subqueries with asensitive behavior, that is, changes to data referenced by subqueries are not visible in the cursor result set
ORDER BY clause; the ORDER BY columns may be updated, but the result set does not reorder
Columns that meet these requirements:
- No CAST on a column
- Base columns of a base table in the SELECT clause
- There are no expressions or functions on that column in the SELECT clause and it is not duplicated in the select list (for example, SELECT c1, c1).
- Base columns of a base table restricted to those listed in the FOR UPDATE OF column-name-list clause, if the clause is specified.

Sybase IQ does not permit updatable cursors on queries that contain any operator that precludes a one-to-one mapping of result set rows to rows in a base table; specifically:

SELECT DISTINCT
Operator that has a UNION
Operator that has a GROUP BY
Operator that has a SET function
Operator that has an OLAP function, with the exception of RANK()

See the description of the UPDATE (positioned) Statement [ESQL] [SP] for information on the columns and expressions allowed in the SET clause for the update of a row in the result set of a cursor.

Sybase IQ supports inserts only on updatable cursors where all nonnullable, nonidentity columns are both selected and updatable.

In Sybase IQ, COMMIT and ROLLBACK are not allowed inside an open updatable cursor, even if the cursor is opened as a hold cursor. Sybase IQ does support ROLLBACK TO SAVEPOINT inside an updatable cursor.

Any failure that occurs after the cursor is open results in a rollback of all operations that have been performed through this open cursor.

Updatable Cursor Limitations

A declared cursor is read-only and not updatable in cases where:

The data extraction facility is enabled with the TEMP_EXTRACT_NAME1 option set to a pathname
As a join index, or within a join index
ANSI_CLOSE_CURSORS_ON_ROLLBACK is set OFF
CHAINED is set OFF
The statement is INSERT SELECT or SELECT INTO
More than one table is included
No updatable columns exist

If Sybase IQ fails to set an updatable cursor when requested, see the .iqmsg file for related information.

There is a limitation regarding updatable cursors and ODBC. A maximum of 65535 rows or records can be updated, deleted, or inserted at a time using these ODBC functions:

SQLSetPos SQL_UPDATE, SQL_DELETE, and SQL_ADD
SQLBulkOperations SQL_ADD, SQL_UPDATE_BY_BOOKMARK, and SQL_DELETE_BY_BOOKMARK

There is an implementation-specific limitation to the maximum value in the statement attribute that controls the number of effected rows to the largest value of an UNSIGNED SMALL INT, which is 65535.

SQLSetStmtAttr(HANDLE,SQL_ATTR_ROW_ARRAY_SIZE, VALUE,0)

Updatable Cursor Differences

Sybase IQ updatable cursors differ from ANSI SQL3 standard behavior as follows:

Hold cursor update close on commit.
Sybase IQ locks tables when the cursor is open.
All updates, deletes, and insert operations are applied when the cursor is closed, in this order: deletes first, then updates, then inserts.

See also Reference: Building Blocks, Tables, and Procedures > System Procedures > System Stored Procedures > sp_iqcursorinfo Procedure.

Standards

SQL—Vendor extension to ISO/ANSI SQL grammar.
Sybase—Supported by Open Client/Open Server.

Permissions

None