oracle 在oracle pl/sql 中将游标从内部过程返回到外部过程
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4644613/
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
Returning a cursor from an inner procedure to outer procedure in oracle pl/sql
提问by cherit
I am using oracle PL/SQL procedure. I am calling one procedure inside another. I want to return a cursor from the nested procedure to the outer procedure. Is this possible? How adversely does it affect the procedure?
我正在使用 oracle PL/SQL 程序。我在另一个程序中调用一个程序。我想将游标从嵌套过程返回到外部过程。这可能吗？它对程序有何不利影响？
Below is the calling structure:
下面是调用结构：
Proc1( data1 IN integer, cursor1 OUT SYS_REFCURSOR ) {
Proc2(data2 IN , cursor1 out) {
open cursor1 FOR
select * from table;
}
}
回答by Justin Cave
Here is one example of calling procedures that have REF CURSOR OUT parameters.
下面是调用具有 REF CURSOR OUT 参数的过程的一个示例。
SQL> create or replace procedure p1(
2 p_empno in emp.empno%type,
3 p_rc out sys_refcursor
4 )
5 as
6 begin
7 open p_rc
8 for
9 select *
10 from emp
11 where empno = p_empno;
12 end;
13 /
Procedure created.
SQL> create or replace procedure p2(
2 p_empno in emp.empno%type,
3 p_rc out sys_refcursor
4 )
5 as
6 begin
7 p1( p_empno, p_rc );
8 end;
9 /
Procedure created.
In this case, I'm creating a SQL*Plus substitution variable rc in order to demonstrate how to call p2. If you are calling it in something other than SQL*Plus, the syntax will be a bit different but the general principle will be the same.
在本例中，我创建了一个 SQL*Plus 替换变量 rc 以演示如何调用 p2。如果您使用 SQL*Plus 以外的其他方式调用它，则语法会有所不同，但一般原则是相同的。
SQL> var rc refcursor;
SQL> exec p2( 7900, :rc );
PL/SQL procedure successfully completed.
SQL> print rc
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO FAKE_COL FOO
---------- ---------- ----------
7900 SM2 CLERK 7698 03-DEC-81 950
30 1
