DECLARE CURSOR
Defines a cursor for row-at-a-time data retrieval.
Syntax
DECLARE cursor_name [INSENSITIVE] [SCROLL] CURSOR FOR {select_stmt | prepared_stmt_name} [FOR { READ ONLY | UPDATE [ OF column_list ] } ]
Arguments
cursor_name
Is the cursor name in subsequent statements. Cursor names can have as many as 30 characters, and can include alphanumeric characters and any symbols that are legal in file names. Hyphens (-) are not permitted. The first character must be a letter. Optionally, the cursor_name parameter can be enclosed in quotation marks (' ').
INSENSITIVE
Specifies creating a standard, read-only cursor that is a snapshot of the cursor result set at open time. It is equivalent to the INSENSITIVE option of the Transact-SQL DECLARE CURSOR statement.
SCROLL
Specifies allowing first, last, and backward fetch operations. It is equivalent to issuing the SET CURSORTYPE CUR_STANDARD and SET SCROLLOPTION KEYSET statements.
select_stmt
Is any valid Transact-SQL SELECT statement. Browse cursors can also use a stored procedure that contains a SELECT statement. This SELECT statement must not contain any aggregates.
prepared_stmt_name
Is the name of a prepared SQL SELECT statement.
FOR READ ONLY
Specifies the use of standard DB-Library read-only cursors. This is equivalent to issuing both the SET CONCURRENCY READONLY and the SET CURSORTYPE CUR_STANDARD statements. Using the FOR READ ONLY option overrides the SET CONCURRENCY statement.
FOR UPDATE
Specifies that cursors are updatable by default; therefore, the DECLARE statement does not require a FOR UPDATE option. However, if the DECLARE statement contains the FOR UPDATE option, the effect is equivalent to issuing both the SET CONCURRENCY LOCKCC and the SET CURSORTYPE CUR_STANDARD statements. Using the FOR UPDATE option overrides the SET CONCURRENCY statement.
Remarks
The DECLARE CURSOR statement associates the cursor name with the specified SELECT statement and enables you to retrieve rows of data by using the FETCH statement.
Cursor names are global within a program module (source code file). Cursors cannot be shared by separately compiled programs that are linked into a single executable module, or by a program and dynamic-link libraries that run in a single process.
The DECLARE CURSOR statement must appear before the first reference of the cursor. The SELECT statement runs when the cursor is opened.
The following rules apply to the SELECT statement:
- It cannot contain an INTO clause or parameter markers (?).
- It can contain input host variables that were previously identified in a host variable declaration section.
- It must include a HOLDLOCK option to enable repeatable reads. Additionally, standard cursors require that an explicit user-defined transaction is open (opened by using BEGIN TRANSACTION).
- For a standard cursor, use the SET CURSORTYPE CUR_STANDARD if you do not use the FOR UPDATE option.
- For a browse cursor, include the FOR BROWSE option and use the SET CURSORTYPE CUR_BROWSE statement if positioned updates or deletes will be performed on a browse cursor. If the SET CURSORTYPE statement is not used, the FOR BROWSE option makes the cursor read-only. Do not use the FOR UPDATE option.
You must declare a dynamic cursor by using the DECLARE CURSOR statement before you prepare a SELECT statement.
Examples
EXEC SQL DECLARE c1 CURSOR FOR
SELECT au_fname, au_lname FROM authors FOR BROWSE;