When you use a cursor the program can find each row one after another
from the outcome table until the end of data .
In this example i will show how to do sample database cursor in Ibm db2 database.
The SELECT statment must be within a DECLARE CURSOR statement and it can not include an INTO condition. The DECLARE CURSOR statement
defines and names the cursor, describes the set of rows to be retrieved with the SELECT statement of the cursor.
We are using SQLCODE to determine if retrieved data from cursor is empty or not .
When SQLCODE =100 it means that data not found
For Example we have Test table "USER" with 4 columns
"USER_ID" (INTEGER),
"FIRSTNAME" (VARCHAR) ,
"LASTNAME" (VARCHAR),
"EMPLOYER_ID" (INTEGER)"
BEGIN
DECLARE SQLCODE INTEGER DEFAULT 0;
DECLARE P_USER_ID INTEGER;
DECLARE P_FIRSTNAME VARCHAR(50) ;
DECLARE P_LASTNAME VARCHAR(50) ;
DECLARE P_EMPLOYER_ID INTEGER;
DECLARE MY_CURSOR CURSOR FOR
SELECT USER.USER_ID, USER.FIRSTNAME,USER.LASTNAME,USER.EMPLOYER_ID FROM USER ;
OPEN MY_CURSOR;
FETCH MY_CURSOR INTO P_USER_ID , P_FIRSTNAME, P_LASTNAME ,P_EMPLOYER_ID;
WHILE (SQLCODE=0)
DO
--------------------------------------
-- DO SOME MANIPULATION THERE ----------
----------------------------------------
FETCH MY_CURSOR INTO P_USER_ID , P_FIRSTNAME, P_LASTNAME ,P_EMPLOYER_ID;
END WHILE;
CLOSE MY_CURSOR;
END;
No comments:
Post a Comment