在oracle pl/sql中将游标从内部过程返回到外部过程

发布于 2024-10-11 04:39:39 字数 294 浏览 0 评论 0原文

我正在使用 oracle PL/SQL 程序。我正在另一个程序中调用一个程序。我想将游标从嵌套过程返回到外部过程。这可能吗？它对程序有多大不利影响？

下面是调用结构：

  Proc1( data1 IN integer, cursor1 OUT SYS_REFCURSOR ) {
       Proc2(data2 IN , cursor1 out) {
           open cursor1 FOR
           select * from table;
       }
  }

原文

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?

Below is the calling structure:

  Proc1( data1 IN integer, cursor1 OUT SYS_REFCURSOR ) {
       Proc2(data2 IN , cursor1 out) {
           open cursor1 FOR
           select * from table;
       }
  }

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

云裳 2024-10-18 04:39:39

下面是调用具有 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.

在本例中，我创建一个 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

Here is one example of calling procedures that have REF CURSOR OUT parameters.

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> 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

回复收藏 0 原文

~没有更多了~