there are samples with the product in the sqllib/samples directory. Go
there and browse around, there are plenty of examples.


CREATE PROCEDURE median_result_set
-- Declare medianSalary as INOUT so it can be used in DECLARE CURSOR
(OUT medianSalary DOUBLE)
RESULT SETS 2
LANGUAGE SQL
BEGIN
DECLARE v_numRecords INT DEFAULT 1;
DECLARE v_counter INT DEFAULT 0;

DECLARE c1 CURSOR FOR
SELECT salary FROM staff
ORDER BY CAST(salary AS DOUBLE);

-- use WITH RETURN in DECLARE CURSOR to return a result set
DECLARE c2 CURSOR WITH RETURN FOR
SELECT name, job, salary
FROM staff
WHERE CAST(salary AS DOUBLE) > medianSalary
ORDER BY salary;

-- you can return as many result sets as you like, just
-- ensure that the exact number is declared in the RESULT SETS
-- clause of the CREATE PROCEDURE statement

-- use WITH RETURN in DECLARE CURSOR to return another result set
DECLARE c3 CURSOR WITH RETURN FOR
SELECT name, job, salary
FROM staff
WHERE CAST(salary AS DOUBLE) < medianSalary
ORDER BY SALARY DESC;

DECLARE CONTINUE HANDLER FOR NOT FOUND
SET medianSalary = 6666;

-- initialize OUT parameter
SET medianSalary = 0;

SELECT COUNT(*) INTO v_numRecords FROM STAFF;

OPEN c1;
WHILE v_counter < (v_numRecords / 2 + 1) DO
FETCH c1 INTO medianSalary;
SET v_counter = v_counter + 1;
END WHILE;
CLOSE c1;

-- return 1st result set, do not CLOSE cursor
OPEN c2;

-- return 2nd result set, do not CLOSE cursor
OPEN c3;
END @






"Mati Kader Ben
"
<db2udbdba-ezmlms To
hield-x51231447.x "LazyDBA Discussion"
[Email Address Removed] <[Email address protected]
a.com> cc

07/28/2005 04:41 Subject
PM Declare cursor in SQL PL










Hi Gurus,

I'm about learning SQL PL. Can someone give me an example how
declare and manipulate the CURSOR.

Thank you in advance.


kader





---------------------------------------------------------------------
PLEASE CLICK REPLY-ALL TO SEND A REPLY TO EVERYONE
website: http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html




DB2 & UDB email list listserv db2-l LazyDBA home page