Filter
Top Products
4.2 Declaring a Cursor
Before data can be manipulated using a cursor, the cursor must be declared using a cursor declaration, a
non-executable statement. The cursor declaration must be coded before any SQL statements that use the cursor are
coded, regardless of the execution sequence of the application program.
The following is an example of a cursor declaration:
Example 1:
In this example, a cursor to obtain CUSTOMER, PRODNO, and ORDERQTY from the ORDER table is
declared.
This cursor declaration declares a cursor to fetch values from all rows of the three columns CUSTOMER, PRODNO,
and ORDERQTY in the ORDER table.
The tables to be manipulated by a cursor and the method of fetching data are specified in the query expression of the
cursor declaration. Example 1 is a simple example of data manipulation performed by a query expression. In contrast,
a cursor can be used to fetch data from tables and perform various processes according to the application program
logic. A query expression can be used to group and manipulate tables and fetch data from the aggregate of multiple
tables. The skillful use of query expressions can greatly reduce the amount of processing required to be performed by
application programs.
Query expression
Query expressions can be specified in two ways. A single query specification can be specified in a query expression.
Alternatively, two or more query specifications, joined by UNION, can be specified in a query expression. For
information on how to specify "UNION", see 5.5 "Obtaining the Aggregate for Rows from Multiple Tables".
Query specification
The format of a query specification is the same as that for a single row SELECT statement minus the INTO clause. A
query specification derives a table in the same way as a single row SELECT statement. In both cases, values for
columns specified in a select column list are fetched from rows of a table, derived from a FROM clause, that satisfy
the WHERE clause conditions. However, whereas a single row SELECT statement ends in an error if more than one
row is to be fetched, a query specification can be used to fetch any number of rows.
The GROUP BY clause and the HAVING clause can be specified in query specifications. For details, see 6.1
"Grouping Tables and Manipulating Data", which explains using a cursor declaration containing a GROUP BY clause
in the query specification. The following is an example of specifying a GROUP BY clause:
Example 2:
In this example, a cursor is declared to obtain PRODUCT, the number of customers who have placed
orders, and the totals of STOCKQTY and ORDERQTY for each ITMNO in the STOCK table and the
ORDER table.
44