Stored Procedure Result Set from multiple select statements
Friends,
I need to return a result set from multiple select statements where the results from each select statement are appended to the previous results. This is for a complex report I'm required to develop.
In my test stored procedure, I can successfully return a result set, but only for the last select statement in the loop. Any help would be appreciated.
In the following scenario, I have two departments, IT and HR, but I only receive results from the HR department.
CREATE OR REPLACE
PROCEDURE employee
(
c_out OUT sys_refcursor )
AS
BEGIN
FOR idx IN
(SELECT department FROM department
)
LOOP
OPEN c_out FOR SELECT employee, department FROM employee WHERE department = idx.department;
END LOOP;
END employee;