ALLBASE/SQL Reference Manual (36216-90216)

320 Chapter10
SQL Statements A - D
CLOSE
Description
• When it applies to a select cursor (one that is declared for a SELECT statement), the
CLOSE statement can be issued in an application program or in a procedure.
When it applies to a procedure cursor (one that is declared for an EXECUTE
PROCEDURE statement), the CLOSE statement can be issued only in an application
program.
• The CLOSE statement cannot be used in ISQL.
• CLOSE returns an error if the cursor is not in the open state.
• The COMMIT WORK and ROLLBACK WORK statements automatically close all cursors not
opened with the KEEP CURSOR option.
• To close a select cursor opened with the KEEP CURSOR option, you must perform an
explicit CLOSE followed by a COMMIT WORK.
• When you close a select cursor, its active set becomes undefined, and it can no longer be
used in DELETE, FETCH, or UPDATE statements. To use the cursor again you must
reopen it by issuing an OPEN statement.
• When you close a procedure cursor, its active result set becomes undefined, and it can
no longer be used in FETCH statements. To use the procedure cursor again you must
reopen it by issuing an OPEN statement.
• When used with a procedure cursor, CLOSE discards any pending rows or result sets
from the procedure. Execution of the procedure continues with the next statement.
Control returns to the application when the procedure terminates.
Note that following processing of the last multiple row result set, procedure execution
cannot continue until you close or advance the procedure cursor in the application.
• Upon execution of the CLOSE statement used with a procedure cursor, return status and
output parameter values are available to the application in either the SQLDA or the
HostVariableSpecification of the USING clause or in any host variables specified
in the related DECLARE CURSOR statement.
• The USING clause is allowed only for dynamic procedure cursors.
Authorization
You do not need authorization to use the CLOSE statement.
Examples
Declare and open a cursor for use in updating values in column QtyOnHand.
DECLARE NewQtyCursor CURSOR FOR
SELECT PartNumber,QtyOnHand FROM PurchDB.Inventory
FOR UPDATE OF QtyOnHand
OPEN NewQtyCursor
Statements setting up a FETCH-UPDATE loop appear next.
FETCH NewQtyCursor INTO :Num :Numnul, :Qty :Qtynul