ALLBASE/SQL Reference Manual (36216-90216)

372 Chapter10
SQL Statements A - D
DECLARE CURSOR
ExecuteProcedureStatement is a static EXECUTE PROCEDURE statement. It
determines the rows and columns of the query result set or sets to be
processed by means of a procedure cursor. The rows defined when you open
and advance the cursor are called the active set of the cursor.
ExecuteStatementName is specified when declaring a procedure cursor for a
dynamically preprocessed EXECUTE PROCEDURE statement. It is the
StatementName specified in the related PREPARE statement.
Dynamic parameters are allowed in
ExecuteStatementName .
Description
• There are two types of cursors. A select cursor is a pointer used to indicate the current
row in a set of rows retrieved by a SELECT statement. A procedure cursor is a pointer
used to indicate the current result set and row in result sets retrieved by SELECT
statements in a procedure and returned to a calling application or ISQL.
• The DECLARE CURSOR statement cannot be used interactively.
• A cursor must be declared before you refer to it in other cursor manipulation
statements.
• The active set is defined and the value of any host variables in the associated SELECT or
EXECUTE PROCEDURE statement is evaluated when you issue the OPEN statement.
• Use the FETCH statement to move through the rows of the active set.
• For procedure cursors only, use the ADVANCE statement to move to the next active set
(query) within a procedure.
• For select cursors only, you can operate on the current row in the active set (the most
recently fetched row) with the UPDATE WHERE CURRENT and DELETE WHERE CURRENT
statements.
When using the Read Committed or Read Uncommitted isolation levels, use the
REFETCH statement to verify that the row you want to update or delete still exists.
• A select cursor is said to be updatable when you can use it in DELETE WHERE CURRENT
OF CURSOR or UPDATE WHERE CURRENT OF CURSOR statements to modify the base
table. A select cursor is updatable only if the query from which it is derived matches the
following updatability criteria:
• No ORDER BY, UNION, or UNION ALL operation is specified.
• No DISTINCT, GROUP BY, or HAVING clause is specified in the outermost SELECT
clause, and no aggregate appears in its select list.
• The FROM clause specifies exactly one table, whether directly or through a view. If it
specifies a table, the table must be an updatable table. If it specifies a view, the view
definition must satisfy the cursor updatability rules stated here.
• For the UPDATE WHERE CURRENT statement, you can only update columns in the
FOR UPDATE list.
•ForDELETE WHERE CURRENT and UPDATE WHERE CURRENT statements, the
SelectStatement parameter must not contain any subqueries or reference any