例子:
CREATE PROCEDURE PROC1(INOUT data1 SMALLINT)
DYNAMIC RESULT SETS 2 LANGUAGE SQL
BEGIN
DECLARE c1 CURSOR WITH RETURN TO CALLER FOR
SELECT name,dept FROM staff WHERE salary>data1;
DECLARE c2 CURSOR WITH RETURN TO CALLER FOR
SELECT name,dept FROM staff WHERE salary<=data1;
OPEN c1;
OPEN c2;
END@
CREATE PROCEDURE Caller_Proc1 ( INOUT data1 SMALLINT)
LANGUAGE SQL
BEGIN
DECLARE result1 RESULT_SET_LOCATOR VARYING;
DECLARE result2 RESULT_SET_LOCATOR VARYING;
DECLARE name_result VARCHAR(9);
DECLARE dept_result SMALLINT;
DECLARE RESULT_SET_END SMALLINT;
DECLARE END_OF_RECORD SMALLINT;
DECLARE CONTINUE HANDLER FOR NOT FOUND
BEGIN
SET RESULT_SET_END=1;
SET name_result = 'LAST';
SET dept_result = 999;
END;
SET RESULT_SET_END = 0;
CALL proc1(data1);
ASSOCIATE RESULT SET LOCATORS (result1 , result2)
WITH PROCEDURE proc1;
ALLOCATE callC1 CURSOR FOR RESULT SET result1;
Begin
delete from result;
end;
WHILE (RESULT_SET_END = 0 ) DO
FETCH FROM callC1 INTO name_result , dept_result;
IF ( RESULT_SET_END = 0 ) THEN
BEGIN
INSERT INTO result
VALUES (name_result , dept_result , current timestamp);
END;
END IF;
END WHILE;
SET RESULT_SET_END = 0;
ALLOCATE callC2 CURSOR FOR RESULT SET result2;
Begin
delete from result2;
end;
WHILE (RESULT_SET_END = 0 ) DO
FETCH FROM callC2 INTO name_result , dept_result;
IF ( RESULT_SET_END = 0 ) THEN
BEGIN
INSERT INTO result2
VALUES (name_result , dept_result , current timestamp);
END;
END IF;
END WHILE;
--CLOSE result1;
--CLOSE result2;
END@