|
|
The DECLARE CURSOR statement associates a cursor with a specified
SELECT or EXECUTE PROCEDURE statement.
Application Programs and Procedures
DECLARE CursorName [IN DBEFileSetName] CURSOR FOR
{ {QueryExpression
SelectStatementName} [FOR UPDATE OF {ColumnName}[,...]
FOR READ ONLY]
ExecuteProcedureStatement
ExecuteStatementName }
- CursorName
is the name assigned to the newly declared cursor.
Two cursors in an application program cannot have the same name.
The cursor name must conform to the SQL syntax rules for a basic
name, described in the "Names" chapter of this manual, and must
also conform to the requirements of the application programming
language.
- DBEFileSetName
identifies the DBEFileSet in which ALLBASE/SQL is
to store the section associated with the cursor. If not specified,
the default SECTIONSPACE DBEFileSet is used.
- QueryExpression
is a static SELECT statement. It determines the rows and
columns to be processed by means of a select cursor. The rows defined
by the query expression when you open the cursor are called the active
set of the cursor. Parentheses are optional.
The BULK and INTO clauses and dynamic parameters are disallowed.
- SelectStatementName
is specified when declaring a select cursor for a dynamically
preprocessed SELECT statement. It is the
StatementName specified in the related PREPARE
statement.
- FOR UPDATE OF ColumnName
specifies the column or columns which may be updated using this
cursor. The order of the column names is not important. The column(s) to
be updated need not appear in the select list of the SELECT
statement. If you use a FOR UPDATE clause, the query expression must
be updatable.
- FOR READ ONLY
indicates that data is to be read and not updated.
Specify this clause when you preprocess and application using the
FIPS 127.1 flagger, and the cursor you are declaring reads and does
not update columns. FOR READ ONLY assures optimum performance in
this case.
- 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.
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.
For DELETE WHERE CURRENT and UPDATE WHERE
CURRENT statements, the SelectStatement
parameter must not contain any subqueries or reference any 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.)
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.
Deleting with a cursor
The active set of this cursor will contain values for the OrderNumber
stored in :OrdNum.
DECLARE DeleteItemsCursor CURSOR FOR
SELECT ItemNumber,OrderQty FROM PurchDB.OrderItems
WHERE OrderNumber = :OrdNum
Statements setting up a FETCH-DELETE WHERE CURRENT loop appear
here.
OPEN DeleteItemsCursor
Statements for displaying values and requesting whether the user
wants to delete the associated row go here.
FETCH DeleteItemsCursor
INTO :Lin :Linnul, :Orq :Orqnul
DELETE FROM PurchDB.OrderItems
WHERE CURRENT OF DeleteItemsCursor
.
.
.
CLOSE DeleteItemsCursor
Updating with a cursor
A cursor for use in updating values in column QtyOnHand is declared
and opened.
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
Statements for displaying a row to and accepting a new QtyOnHand
value from the user go here. The new value is stored in :NewQty.
UPDATE PurchDB.Inventory
SET QtyOnHand = :NewQty
WHERE CURRENT OF NewQtyCursor
.
.
.
CLOSE NewQtyCursor
Bulk fetching
In some instances, using the BULK option is more efficient than
advancing the cursor a row at a time through many rows, especially
when you want to operate on the rows with non-ALLBASE/SQL statements.
DECLARE ManyRows CURSOR FOR
SELECT *
FROM PurchDB.Inventory
OPEN ManyRows
BULK FETCH ManyRows INTO :Rows, :Start, :NumRow
Dynamically preprocessed SELECT
If you know in advance that the statement to be dynamically
preprocessed is not a SELECT statement, you can prepare it and
execute it in one step. In other instances, it is more appropriate to
prepare and execute the statement in separate operations.
EXECUTE IMMEDIATE :Dynam1
The statement stored in :Dynam1 is dynamically preprocessed.
PREPARE Dynamic1 FROM :Dynam1
If Dynamic1 is not a SELECT statement, the SQLD field of the
SQLDA data structure is 0, and you use the EXECUTE statement
to execute the dynamically preprocessed statement.
DESCRIBE Dynamic1 INTO SQLDA
EXECUTE Dynamic1
If Dynamic1 is a SELECT statement and the language you are
using supports dynamically defined SELECT statements, use a
cursor to manipulate the rows in the query result.
After you open the cursor and place the appropriate values into the
SQL Descriptor Area (SQLDA), use the USING DESCRIPTOR clause of the
FETCH statement to identify where to place the rows selected
and properly display the returned data.
DECLARE Dynamic1Cursor CURSOR FOR Dynamic1
OPEN Dynamic1Cursor
FETCH Dynamic1Cursor USING DESCRIPTOR SQLDA
.
.
.
CLOSE Dynamic1Cursor
Refer to the ALLBASE/SQL Advanced Application Programming
Guide for a pseudocode example of procedure cursor usage.
|