 |
» |
|
|
|
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 NewQtyCursorStatements 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.
|
