RETRIEVE A NO. OF ROWS
This presentation is the property of its rightful owner.
Sponsored Links
1 / 14

RETRIEVE A NO. OF ROWS PowerPoint PPT Presentation


  • 77 Views
  • Uploaded on
  • Presentation posted in: General

RETRIEVE A NO. OF ROWS. ¦ Declare a cursor ¦ Open the cursor ¦ Fetch rows of data ¦ Stop fetching rows ¦ Close the cursor. Declaring (defining) a cursor is done in the data division of your program. DATA DIVISION.

Download Presentation

RETRIEVE A NO. OF ROWS

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript


Retrieve a no of rows

RETRIEVE A NO. OF ROWS

¦ Declare a cursor

¦ Open the cursor

¦ Fetch rows of data

¦ Stop fetching rows

¦ Close the cursor


Retrieve a no of rows

Declaring (defining) a cursor is done in the data division

of your program.

DATA DIVISION.

WORKING-STORAGE SECTION.

* * * * * * * * * * * * * * * * *

* INCLUDE DECLEGENED STRUCTURES *

* * * * * * * * * * * * * * * * *

EXEC SQL

INCLUDE SQLCA

END-EXEC.

**** SQL CURSOR DECLARATIONS ****

EXEC SQL

DECLARE CR1 CURSOR FOR

SELECT PROJNO,PROJNAME,

DEPTNO,LEADER

FROM PROJECT

WHERE DEPTNO = :DEPT-NO

END-EXEC.

This statement declares a cursor

which will select the rows from

the PROJECT table which have a

department number equal to the

host variable :DEPT-NO.


Retrieve a no of rows

Open the cursor

Opening the cursor is part of

the PROCEDURE DIVISION of a

program. This actually causes

the SELECT which was coded in

the cursor declaration to be executed.


Retrieve a no of rows

DATA DIVISION.

**** SQL CURSOR DECLARATIONS ****

EXEC SQL

DECLARE CR1 CURSOR FOR

SELECT PROJNO,PROJNAME,

DEPTNO,LEADER

FROM PROJECT

WHERE DEPTNO = :DEPT-NO

END-EXEC.

PROCEDURE DIVISION.

PERFORM SCREEN-RETRIEVE.

EXEC SQL

OPEN CR1

END-EXEC.

When the OPEN statement is encountered , the SELECT in the

cursor declaration is executed.


Retrieve a no of rows

The OPEN cursor statement not only

executes the SELECTion of data from

the DB2 data base, but....

... it also establishes the initial

position of the cursor in the results

table.

The cursor is placed ready to fetch the first row of data.


Retrieve a no of rows

Fetch rows of data

Once the cursor is open, you

can fetch the rows of the

results table into program

variables for calculations

or to be printed.

The cursor name is the same as

the name of the cursor defined

in the DECLARE CURSOR statement.


Retrieve a no of rows

DATA DIVISION.

: : · : .

EXEC SQL

DECLARE CR1 CURSOR FOR

SELECT SALARY FROM EMPLOYEE

WHERE DEPTNO = :DEPT-NO

END-EXEC.

: : · : .

PROCEDURE DIVISION.

PERFORM SCREEN-RETRIEVE.

EXEC SQL OPEN CR1 END-EXEC.

EXEC SQL FETCH CR1

INTO :EMP-SAL

END-EXEC.

IF SQLCODE NOT = 0

PERFORM END-FETCH

ELSE

PERFORM SUM-AND-FETCH

UNTIL SQLCODE NOT = 0.

SUM-AND-FETCH.

ADD EMP-SAL TO TOTAL-SAL.

EXEC SQL FETCH CR1

INTO :EMP-SAL END-EXEC.


Retrieve a no of rows

Rem: The host variable should have matching

data types to the columns returned.

If there is more than one column being

retrieved, the host variables are listed

in the same order as the corresponding

fields listed in the cursor declaration.

The list of host variables are

separated by commas and preceded by

colons.


Retrieve a no of rows

This declaration was used

to define a cursor.

EXEC SQL

DECLARE CR1 CURSOR FOR

SELECT EMPNO, LNAME, DEPTNO

FROM EMPLOYEE

WHERE DEPTNO = :DEPT-NO

END-EXEC.

Which of the following FETCH statements would be correct

to use with this declaration?

FETCH CR1 INTO :EM-NO, :DP-NAME, :DP-NO, :DP-MGR

FETCH CR1 INTO :DP-NAME, :EM-NO, :DP-NO

FETCH CR1 INTO :EM-NO, :EM-NAME, :DP-NO

FETCH CR1 INTO :DP-NO, :EM-NO, :EM-NAME


Retrieve a no of rows

FETCH cursor name INTO host-var1, host-var2 ...

¦ Positions a cursor on the next row and assigns values

of that row to host variables.

¦ Each time the FETCH statement is executed, the position

of the cursor is advanced to the next row of the active

set and the columns of that row are placed into host

variables.

¦ Will be in some kind of loop where each row is dealt with

in turn.


Retrieve a no of rows

Closing the Cursor

¦ Closing the cursor releases the system resources

associated with maintaining the active set.

¦ The active set becomes undefined and the rows

become unavailable to your application.

¦ If you forget to close the cursor DB2 will do it

for you when the job ends, but it is a good idea

to close the cursor when you are done with it.

¦ You can close and reopen a cursor as many times as

you like.

¦ The syntax is: EXEC SQL

CLOSE cursor name

END-EXEC.


Retrieve a no of rows

DATA DIVISION.

: : · : .

EXEC SQL

DECLARE CR1 CURSOR FOR

SELECT SALARY FROM EMPLOYEE

WHERE DEPTNO = :DEPT-NO

END-EXEC.

: : · : .

PROCEDURE DIVISION.

PERFORM SCREEN-RETRIEVE.

EXEC SQL OPEN CR1 END-EXEC.

EXEC SQL FETCH CR1

INTO :EMP-SAL END-EXEC.

IF SQLCODE NOT = 0

PERFORM END-FETCH

ELSE

PERFORM SUM-AND-FETCH

UNTIL SQLCODE NOT = 0.

SUM-AND-FETCH.

ADD EMP-SAL TO TOTAL-SAL. EXEC SQL

EXEC SQL FETCH CR1CLOSE CR1

INTO :EMP-SAL END-EXEC.END-EXEC


Retrieve a no of rows

There are several things to watch for when using a cursor:

¦ The number of names in the host list must be

equal to the number of columns in the SELECT

clause of the DECLARE CURSOR statement.

¦ Check SQLCODE after each FETCH is executed.

- +100 means the active set is empty

- 0 means the FETCH executed successfully


Retrieve a no of rows

Let's review the steps we have learned for using a cursor to

select multiple rows:

¦ Declare a cursor defines a cursor using a standard SQL

SELECT statement.

¦ Open the cursor opens a cursor so that it can be used

to fetch rows from its active set.

¦ Fetch rows of data positions a cursor on the next row of

its active set and assigns the values

of that row to the host variables.

¦ Stop fetching rows by using the value passed in the

SQLCODE to determine when all rows

from the active set have been fetched.

¦ Close the cursor to destroy the temporary table created

when a cursor was opened.


  • Login