Opens a previously declared static cursor. This statement can be used to open any static cursor, including one for a stored procedure.
exec sql [at connection_name] open cursor_name [row_count = size] end-exec
The name of the cursor to be opened.
The number of rows moved in a network roundtrip, not the number fetched into the host variable.
The number of rows that are moved at the same time from Adaptive Server Enterprise to the client. The client buffers the rows until they are fetched by the application. This parameter allows you to tune network efficiency.
EXEC SQL BEGIN DECLARE SECTION END-EXEC.
01 TITLE-ID PIC X(6).
01 BOOK-NAME PIC X(25).
01 BOOK-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-LOOP UNTIL SQLCODE = 100.
EXEC SQL CLOSE titlelist END-EXEC.
EXEC SQL DEALLOCATE CURSOR titlelist END-EXEC.
EXEC SQL COMMIT WORK END-EXEC.
FETCH-LOOP.
EXEC SQL FETCH titlelist INTO :TITLE-ID, :BOOK-NAME END-EXEC.
DISPLAY "TITLE ID : ", TITLE-ID
DISPLAY "TITLE : ", BOOK-NAME
DISPLAY "UPDATE/DELETE THIS RECORD ? "
ACCEPT ANSWER
IF ANSWER = "U"
DISPLAY "ENTER NEW TITLE :"
ACCEPT BOOK-NAME
EXEC SQL UPDATE titles SET title = :TITLE
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-FETCH-LOOP.
open executes the select statement given by the declare cursor statement and prepares results for the fetch statement.
You can have an unlimited number of open cursors.
A static cursor must be opened only in the file where the cursor is declared. The cursor can be closed in any file.
The values of host variables embedded in the declare cursor statement are taken at open time.
When specifying cursor_name, you can use the name of a deallocated static cursor. If you do, the precompiler declares and opens a new cursor having the same name as that of the deallocated cursor. Thus, the precompiler does not reopen the deallocated cursor but instead creates a new one. The results sets for the two cursors can differ.