ALLBASE/SQL Reference Manual (36216-90216)

Chapter 10 373
SQL Statements A - D
DECLARE CURSOR
view whose view definition contains a subquery.
• For select cursors only, use the UPDATE statement with the CURRENT OF option to
update columns; you can update the columns identified in the FOR UPDATE OF clause
of the DECLARE CURSOR statement. The restrictions that govern updating via a select
cursor are described above.
• For select cursors only, use the DELETE WHERE CURRENT statement to delete a row in
the active set.
• Use the CLOSE statement when you are finished operating on the active set or (for a
procedure cursor) set(s).
• Declaring a cursor causes a section to be stored in the system catalog. A description of
the section appears in the SYSTEM.SECTION view.
• The
ExecuteStatementName , SelectStatementName , and
ExecuteProcedureStatement parameters of the DECLARE CURSOR statement are
not allowed within a procedure.
• Host variables for return status and input and output parameters are allowed in
ExecuteProcedureStatement , which is a static EXECUTE PROCEDURE statement.
The appropriate values for input host variables must be set before the OPEN statement.
The output host variables, including return status and output parameters from
executing the procedure are accessible after the CLOSE statement.
• Dynamic parameters for return status and input and output parameters of the
procedure are allowed in
ExecuteStatementName . The appropriate values for any
input dynamic parameters or host variables must be placed into the SQLDA or host
variables before issuing the OPEN statement. The USING DESCRIPTOR clause of the
FETCH statement is used to identify where to place selected rows and properly display
the returned data. Output host variables or values in the SQLDA, including return
status and output parameters from executing the procedure, are accessible after the
CLOSE statement executes.
• If the IN
DBEFileSetName clause is specified, but the module owner does not have
SECTIONSPACE authority for the specified DBEFileSet, a warning is issued and the
default SECTIONSPACE DBEFileSet is used instead. (Refer to syntax for the GRANT
statement and the SET DEFAULT DBEFILESET statement.)
Authorization
For a select cursor, you must have SELECT or OWNER authority for all the tables or views
listed in the FROM clause, or you must have DBA authority.
For a procedure cursor, you must have OWNER or EXECUTE authority on the procedure
or DBA authority.
If you specify the FOR UPDATE clause, you must also have authority to update the
specified columns.
To specify a
DBEFileSetName for a cursor, the cursor owner must have SECTIONSPACE
authority on the referenced DBEFileSet.