DECLARE CURSOR statement [ESQL] [SP]

Description

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

Examples

Example 1

Illustrates how to declare a scroll cursor in Embedded SQL:

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

Example 2

Illustrates how to 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

Illustrates the use of 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.

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.

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,

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

When the cursor is fetched, only one row can be fetched from the table.

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.

READ ONLY is the default value of the FOR clause.

• 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
  

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

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)

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.

None

Standards

Permissions

None

See also