Modernizing IBM eServer iSeries Application Data Access - A Roadmap Cornerstone

98 Modernizing IBM Eserver iSeries Application Data Acess - A Roadmap Cornerstone

C+ and year(Order_Date) = year(current date) - 1

C+ join Stock s

C+ on d.product_number = s.product_number

C+ group by customer_number)

C+ with data

C/END-EXEC

C EndIf

C Return

7.8 Using a cursor

When SQL runs a select statement, the resulting rows comprise the result table. A cursor

provides a way to access a result table. It is used within an SQL program to maintain a

position in the result table. SQL uses a cursor to work with the rows in the result table and to

make them available to your program. Your program can have several cursors, although each

must have a unique name. Even if your source code consists of several independent

procedures, the cursor name must be unique in your source member.

Using a cursor can be compared with native I/O, single record access.

Statements related to using a cursor include the following:

򐂰 A DECLARE CURSOR statement to define and name the cursor and specify the rows to

be retrieved with the embedded select statement.

When moving from RPG native I/O to embedded SQL the declare cursor statement

replaces the File specification.

Ascending or descending sequence in SQL is determined through a ORDER BY clause in

the DECLARE statement, like it is fixed through the according logical file in the File

specification.

򐂰 OPEN statement to open the cursor for use within the program. The cursor must be

opened before any rows can be retrieved.

The SQL OPEN statement can be compared with a user-controlled open of the table and

an additional SETLL statement to position the pointer before the first row.

򐂰 A FETCH statement to retrieve rows from the cursor's result table or to position the cursor

on another row.

Note: Host variables can neither be used in the DROP TABLE nor in the CREATE TABLE

statement. If a variable creation is needed, you have to use dynamic SQL.

Note: In your source, the DECLARE statement must always be positioned prior to the

according OPEN, FETCH, and CLOSE statements. This is independent from the order

in which these statements are executed. To put the DECLARE statement into the

Initialization Subroutine (*INZSR), that is coded at the end of the source, will cause a

compile error.

Note: If a predefined sequence is not absolutely necessary, do not use a ORDER BY

clause to let the optimizer evaluate all the options to find the optimal access path.

Chapter 7. Embedded SQL 99

The SQL FETCH statement is the equivalent to all CHAIN, READ, READE, READP, and

READPE operations in RPG. By using a scrollable cursor, it is also possible to move the

cursor forwards and backwards in your result table.

򐂰 CLOSE statement to close the cursor for use within the program.

7.8.1 The DECLARE statement

The declare statement is used to define the cursor name and the associated SELECT

statement.

An example of the DECLARE statement is shown below, which is important for embedded

static SQL. All parts written in parenthesis can be omitted. For more information look at

iSeries DB2 Universal Database for iSeries SQL Reference.

DECLARE Cursor Name (DYNAMIC (SCROLL)) CURSOR (WITH HOLD)

FOR Select Statement

(FOR READ ONLY/FOR FETCH ONLY)

(FOR UPDATE (OF Column1, Column2,.....ColumnN))

(OPTIMIZE FOR n ROWS)

(WITH Isolation Level)

򐂰 Cursor name

Any name can be specified for the cursor, but a cursor name must be unique in the source

member where it is defined.

򐂰 NO SCROLL or (DYNAMIC) SCROLL

This specifies whether the cursor is scrollable or not scrollable. If neither NO SCROLL nor

SCROLL is specified a serial cursor is defined.

– NO SCROLL

Specifies that a serial cursor is defined.

In Example 7-23 a serial cursor is declared by omitting the SCROLL or NO SCROLL

keyword.

Example 7-23 Declaring a serial cursor

C/EXEC SQL

C+ Declare CsrOrdH Cursor for

C+ Select Customer_Number, sum(Order_Total)

C+ from Order_Header

C+ where Order_Number between :FirstOrderNo and :LastOrderNo

C+ and Year(Order_Date) = :PrevYear

C+ group by Customer_Number

Note: Contrary to RPG, more than one row can be received in one FETCH statement,

by using host structure arrays. The next FETCH will receive the next or previous block

or rows.

Note: When using a serial cursor, an OPEN without a preceding CLOSE will not

reposition the cursor on the top of the result table. To be sure that the cursor is really

closed, execute a CLOSE statement before your OPEN statement.

Note: Even if the source member contains several independent procedures, the cursor

name must be unique.