Declares a cursor for processing multiple rows returned by a select statement.
exec sql declare cursor_name cursor for select_statement [for update [of col_name_1 [, col_name_n]…]| for read only] end-exec
The cursor’s name, used to reference the cursor in open, fetch, and close statements. A cursor’s name must be unique on each connection and must have no more than 255 characters.
The Transact-SQL select statement to be executed when the cursor is opened. See the description of the select statement in the Adaptive Server Enterprise Reference Manual for more information.
Specifies that the cursor’s result list can be updated. (To update the result list, you use the update statement.
The name of a column to be updated.
Specifies that the cursor’s result list cannot be updated.
EXEC SQL BEGIN DECLARE SECTION END-EXEC.
01 TITLE-ID PIC X(6).
01 BOOK-NAME PIC X(25).
01 TYPE PIC X(15).
EXEC SQL END DECLARE SECTION END-EXEC.
01 ANSWER PIC X(1).
....
DISPLAY "TYPE OF BOOKS TO RETRIEVE ? ".
ACCEPT BOOK-TYPE.
EXEC SQL DECLARE titlelist CURSOR FOR
SELECT title_id, substring(title,1,25) FROM
titles WHERE type = :BOOK-TYPE END-EXEC.
EXEC SQL OPEN titlelist END-EXEC.
PERFORM FETCH-PARA UNTIL SQLCODE = 100.
EXEC SQL CLOSE titlelist END-EXEC.
EXEC SQL DEALLOCATE CURSOR titlelist END-EXEC.
EXEC SQL COMMIT WORK END-EXEC.
FETCH-PARA.
EXEC SQL FETCH titlelist INTO
:TITLE-ID, :BOOK-NAME END-EXEC.
DISPLAY "TITLE ID : ",TITLE-ID
DISPLAY "TITLE : ",BOOK-NAME
IF SQLCODE = 100
DISPLAY "NO RECORDS TO FETCH. END OF PROGRAM RUN."
ELSE
DISPLAY "UPDATE/DELETE THIS RECORD (U/D)? "
ACCEPT ANSWER.
IF ANSWER = "U"
DISPLAY "ENTER NEW TITLE :"
ACCEPT BOOK-NAME
EXEC SQL UPDATE titles SET title = :BOOK-NAME
WHERE CURRENT OF titlelist END-EXEC
ELSE
IF ANSWER = "D"
EXEC SQL DELETE titles
WHERE CURRENT OF titlelist END-EXEC
END-IF
END-IF
END-IF.
END-FETCH-PARA.
The Embedded SQL precompiler generates no code for the declare cursor statement.
The select_statement does not execute until your program opens the cursor by using the open cursor statement.
The syntax of the select_statement is identical to that shown in the Adaptive Server Enterprise Reference Manual, except that you cannot use the compute clause in Embedded SQL.
The select_statement can contain host variables. The values of the host variables are substituted when your program opens the cursor.
If you omit either the for update or read only clause, Adaptive Server determines whether the cursor is updatable.
close, connect, deallocate cursor, declare cursor (stored procedure), declare cursor (dynamic), fetch, open, update
