(Tutorial : SQL) Selecting & Processing Multiple Rows

Steps to access multiple rows:
1. Declare cursor
2. Open cursor
3. Fetch a row (record)
4. Process row (UPDATE, INSERT, etc)
5. IF last row: go to Step 6,
- ELSE go to Step 3
6. Close cursor

DECLARE CURSOR statement

Similar in function to HLL file declarations (F-specs or FD's)
-No processing actually takes place - just definition

Host variables may be included in the statement

Created using an embedded SELECT command
-most SELECT clauses may be used - ORDER BY, GROUP BY, etc

Must be declared before being referenced

Sample:

C/EXEC SQL
C+ DECLARE empcsr CURSOR FOR
C+ SELECT nbr, nam, sal
C+ FROM emp
C+ WHERE dpt = :dept
C+
C/END-EXEC

DECLARE CURSOR - more clauses

With Hold clause useful with Commitment Control
-By default, cursors are closed when Commit/Rollback commands
execute
-With Hold - keeps cursor open
-With Hold also an optional clause on the Commit/Rollback commands

Sample:

C/EXEC SQL
C+
C+ DECLARE empcsr CURSOR FOR
C+ WITH HOLD
C+ SELECT nbr, nam, sal
C+ FROM emp
C+ WHERE dpt = :dept
C+ FOR UPDATE OF sal
C+
C/END-EXEC

OPEN statement

Actually executes the SQL Select statement

Builds the access path if necessary

Successful Open places the file cursor before the first row of
the result table

Cursor must be closed before it can be opened

Syntax: OPEN cursor-name

Sample:

C/EXEC SQL
C+
C+ OPEN empcsr
C+
C/END-EXEC

FETCH statement:

Two functions
-position the cursor for the next operation

C/EXEC SQL
C+
C+ FETCH NEXT FROM empcsr
C+
C/END-EXEC

-bring rows into the program

C/EXEC SQL
C+
C+ FETCH NEXT FROM empcsr
C+ INTO :number, :name, :salary
C+
C/END-EXEC

FETCH statement

Alternatives to Next processing:
-must define the cursor as a scrollable cursor in the declare statement

C/EXEC SQL
C+
C+ DECLARE empcsr SCROLL CURSOR FOR
C+ SELECT nbr, nam, sal
C+ FROM emp
C+ ORDER BY empid
C+
C/END-EXEC
C/EXEC SQL
C+
C+ FETCH PRIOR FROM empcsr
C+ INTO :number, :name, :salary
C+
C/END-EXEC

Positioned Update and Delete Stmts

Update or delete the current row of an updatable cursor
-Can only be done after successful Fetch operation
-Add a "Where Current of" clause to the Update and Delete statements

C/EXEC SQL
C+ DECLARE empcsr CURSOR FOR
C+ SELECT nbr, nam, sal
C+ FROM emp
C+ ORDER BY empid
C+ FOR UPDATE OF sal
C/END-EXEC
C/EXEC SQL
C+ FETCH NEXT FROM empcsr
C+ INTO :number, :name, :salary
C/END-EXEC
C/EXEC SQL
C+ UPDATE emp
C+ SET sal = sal + :raise
C+ WHERE CURRENT OF empcsr
C/END-EXEC

Close Statement

Close the cursor
-Cursor must be opened in order to be closed
DB2/400 may close cursors for other reasons also:
-job end
-activation group ends
-program ends
-modules ends
-commit or rollback without a 'with hold' clause
-error handling......

C/EXEC SQL
C+
C+ CLOSE empcsr
C+
C/END-EXEC


You Might Also Want To Visit The Following Tutorial Posts:
AS/400 For Dummies
AS/400 Tutorial
SQL/400 Tutorial
Query/400 Tutorial