HP 3000 Manuals HP 3000 Manuals
DECLARE CURSOR
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 } [FOR UPDATE OF {ColumnName} [,...]]}
{{SelectStatementName} [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
1. 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
2. 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
3. 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
4. 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
5. Refer to the ALLBASE/SQL Advanced Application Programming Guide
for a pseudocode example of procedure cursor usage.