 |
» |
|
|
|
The DECLARE CURSOR statement associates a cursor with a specified SELECT or
EXECUTE PROCEDURE statement.
Scope | |
Application Programs and Procedures
SQL Syntax | |
DECLARE CursorName [IN DBEFileSetName] CURSOR FOR {{ QueryExpression SelectStatementName } [ FOR UPDATE OF {ColumnName} [,...] FOR READ ONLY ] ExecuteProcedureStatement ExecuteStatementName } Parameters | |
- 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.
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.
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.)
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.
Examples | |
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.
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.
|
