I need to have stored procedure where I can run multiple cursors.
Loop over each cursor and then do some operation on each row.
This way I will have the desired result from these cursors. Result of such multiple cursors then needs to be union with some other rows and then filtered out and return those rows finally from the proc.
Please note that each cusror and another queries will have same columns.
I am not sure how to do this in the oracle.
Please help me out.
create or replace PROCEDURE test_proc
(
-- some inputs
hc_cursor OUT SYS_REFCURSOR
)
IS
cursor cursor_one is
SELECT * FROM table_one ;
BEGIN
FOR current_row in cursor_one
loop
-- do some modification on each row and return each modified row
end loop;
cursor cursor_two is
SELECT * FROM table_one ;
BEGIN
FOR current_row in cursor_two
loop
-- do some modification on each row and return each modified row
-- append to result from first cursor
end loop;
-- union results from both these cusrors with some another query
-- now filter these records on some criterais
-- return finally
END;
My suggestion is going to be insert the rows from your cursor into a temporary table. Then join the temporary table with your existing table for the filter criteria you mention. Psuedocode:
create or replace function my_func
return sysrefcursor
is
cursor cursor_one is
SELECT * FROM table_one ;
cursor cursor_two is
SELECT * FROM table_one ;
BEGIN
FOR current_row in cursor_one
loop
-- do some modification on each row and insert into temporary table
end loop;
FOR current_row in cursor_two
loop
-- do some modification on each row and insert into temporary table
end loop;
-- results from cursor 1 and 2 exist in temporary table
open out_cursor for
select t.* from
my_temp_table t
join
my_other_table tt
on (t.col1 = tt.col1) -- or whatever columns are appropriate
where t.col2 = 'some criteria' -- or whatever filter criteria you like.
return out_cursor;
END;
create type emp_obj AS object
(
empno NUMBER (4)
,ename VARCHAR2(10)
,sal number(7,2)
,job varchar2(9)
);
CREATE TYPE EMP_NT AS TABLE OF emp_OBJ;
create or replace package test_pkg
IS
TYPE abc_cur is REF CURSOR;
procedure test_proc
(
p_rec IN OUT abc_cur
);
END test_pkg;
/
create or replace package body test_pkg
IS
procedure test_proc
(
p_rec IN OUT abc_cur
)
IS
v_emp_nt emp_nt;
BEGIN
SELECT emp_obj(empno,ename,sal,job) BULK COLLECT INTO v_emp_nt FROM EMP;
FOR i in v_emp_nt.first..v_emp_nt.last
LOOP
IF v_emp_nt(i).job='CLERK' THEN
v_emp_nt(i).sal := v_emp_nt(i).sal +200;
ELSIF v_emp_nt(i).job='MANAGER' THEN
v_emp_nt(i).sal := v_emp_nt(i).sal +800;
END IF;
END LOOP;
open p_rec for select * from table(v_emp_nt);
END test_proc;
END test_pkg;
/
As you have seen the code,what i do ,is to get the desired result in nested table
(what your cursor is doing) ,and do some manipulation based on the resultant records ,as well as update the nested table.
At the end i will create a cursor from this updated nested table
and return the cursor after opening.
Now your question :How can you return append cursor
?
It is simple create two nested table ,do some manipulation on both the nested table
Suppose you have v_emp_nt1
as first nested table
,you do some manipulation on that .
you have another v_emp_nt2
as second nested table
,you do some manipulation on that .
Now your cursor
will be like
open p_rec FOR (select * from v_emp_nt1 union select * from v_empnt2);
With this way you can achieve your desired output .
**Note:**The above code is for one nested table ,you need to create another nested table for your code to get complete
create
package my_pkg as
type my_rec is record
(
<list your fields here>
);
type my_rec_tab is table of my_rec;
function get_my_rows
return my_rec_tab pipelined;
end my_pkg;
create
package body my_pkg as
function get_my_rows
return my_rec_tab pipelined
as
begin
for c_cur in (select * from table_one)
loop
-- do some modification on the current row and return the modified row
pipe row (c_cur);
end loop;
for c_cur in (select * from table_one)
loop
-- do some modification on the current row and return the modified row
pipe row (c_cur);
end loop;
return;
end get_my_rows;
end my_pkg;
select * from table(my_pkg.get_my_rows);