Declares a cursor for a stored procedure.
exec sql declare cursor_name cursor for execute procedure_name ([[@param_name =]:host_var] [,[@param_name =]:host_var]…) 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 name of the stored procedure to be executed.
The name of a parameter in the stored procedure.
The name of a host variable to be passed as a parameter value.
EXEC SQL BEGIN DECLARE SECTION END-EXEC.
01 TITLE-ID PIC X(6).
01 BOOK-NAME PIC X(65).
01 BOOK-TYPE PIC X(15).
EXEC SQL END DECLARE SECTION END-EXEC.
01 ANSWER PIC X(1).
....
* Create the stored procedure.
EXEC SQL create procedure p_titles (@p_type varchar(30))
as
select title_id, substring(title,1,64)
from titles
where type = @p_type
END-EXEC.
* To execute stored procedures, you must disable chained mode.
EXEC SQL SET CHAINED OFF END-EXEC.
DISPLAY "TYPE OF BOOKS TO RETRIEVE ? ".
ACCEPT BOOK-TYPE.
EXEC SQL DECLARE titlelist CURSOR FOR
execute p_titles :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
IF SQLCODE = 100
DISPLAY "NO RECORDS TO FETCH. END OF PROGRAM RUN."
ELSE
DISPLAY "UPDATE/DELETE THIS RECORD ? "
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.
procedure_name must consist of only one select statement.
It is not possible to retrieve output parameter values from a stored procedure executed using a cursor.
It is not possible to retrieve the return status value of a stored procedure executed using a cursor.
close, deallocate cursor, declare cursor (static), declare cursor (dynamic), fetch, open, update
