oracle 如何从多个游标的oracle存储过程返回多行？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10764229/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to return multiple rows from oracle stored procedure from multiple cursors?
提问by ashishjmeshram
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.
这样我将从这些游标中获得所需的结果。这样多个游标的结果需要与其他一些行合并，然后过滤掉并最终从 proc 返回这些行。
Please note that each cusror and another queries will have same columns.
请注意，每个光标和另一个查询将具有相同的列。
I am not sure how to do this in the oracle.
我不确定如何在 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;
回答by Nick
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;
回答by archimede
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);
回答by Gaurav Soni
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.
正如您所看到的代码，我所做的是获得所需的结果nested table（您的光标正在做什么），并根据结果记录进行一些操作，以及更新嵌套表。
At the end i will create a cursor from this updated nested tableand return the cursor after opening.
最后，我将由此创建一个游标updated nested table并在打开后返回游标。
Now your question :How can you return append cursor?
现在你的问题：How can you return append cursor？
It is simple create two nested table ,do some manipulation on both the nested table
很简单 create two nested table ,do some manipulation on both the nested table
Suppose you have v_emp_nt1as first nested table,you do some manipulation on that .
you have another v_emp_nt2as second nested table,you do some manipulation on that .
假设你有v_emp_nt1作为first nested table，你做一些操作。您有其他v_emp_nt2的second nested table，你做一些操作。
Now your cursorwill be like
现在你cursor会像
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
**注意：**上面的代码是针对一个嵌套表的，你需要为你的代码创建另一个嵌套表才能完整
