Declares a cursor. Cursors are the primary means for manipulating the results of queries.
DECLARE cursor-name [ SCROLL | NO SCROLL | DYNAMIC SCROLL ] CURSOR FOR { select-statement FOR for-clause | statement-name | USING variable-name } for-clause READ ONLY | UPDATE
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, SAP 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, 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
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 SAP 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.
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
EXEC SQL DECLARE cur_employee SCROLL CURSOR FOR SELECT * FROM Employees;
EXEC SQL PREPARE employee_statement FROM 'SELECT emp_lname FROM Employees'; EXEC SQL DECLARE cur_employee CURSOR FOR employee_statement ;
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
The DECLARE CURSOR statement declares a cursor with the specified name for a SELECT statement or a CALL statement.
Embedded SQL statements are named using the PREPARE statement. Cursors can be declared only for a prepared SELECT or CALL.
SAP Sybase IQ supports updatable cursors on single tables.
SAP Sybase IQ supports one type of cursor sensitivity, which is defined in terms of which changes to underlying data are visible. All SAP 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.
LONG VARCHAR and LONG BINARY data types are not supported in updatable cursors.
Scalar user-defined functions and user-defined aggregate functions are not supported in updatable cursors.
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.
SAP Sybase IQ supports inserts only on updatable cursors where all nonnullable, nonidentity columns are both selected and updatable.
In SAP Sybase IQ, COMMIT and ROLLBACK are not allowed inside an open updatable cursor, even if the cursor is opened as a hold cursor. SAP 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
If SAP Sybase IQ fails to set an updatable cursor when requested, see the .iqmsg file for related information.
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)