SYS_REFCURSOR

最新推荐文章于 2021-12-18 22:22:04 发布

csdn1284934570

最新推荐文章于 2021-12-18 22:22:04 发布

阅读量833

点赞数

分类专栏： oracle

本文链接：https://blog.csdn.net/csdn1284934570/article/details/50005107

版权

oracle 专栏收录该内容

50 篇文章

订阅专栏

1、使用SYS_REFCURSOR
SQL> Declare
2 Cur Sys_Refcursor;
3 R scott.dept%Rowtype;
4 Begin
5 Open Cur For Select * From scott.dept;
6 Loop
7 Fetch Cur Into R;
8 Exit When Cur%Notfound;
9 dbms_output.put_line('DEPTNO:'||R.deptno||' DNAME:'||R.DNAME||' LOC:'||R.LOC);
10 End Loop;
11 End;
12 /
DEPTNO:10 DNAME:ACCOUNTING LOC:NEW YORK
DEPTNO:20 DNAME:RESEARCH LOC:DALLAS
DEPTNO:30 DNAME:SALES LOC:CHICAGO
DEPTNO:40 DNAME:OPERATIONS LOC:BOSTON
PL/SQL procedure successfully completed

2、使用自定义游标
SQL> Declare
2 Type T_Cur Is Ref Cursor Return scott.dept%Rowtype;
3 Cur T_Cur;
4 R Cur%Rowtype;
5 Begin
6 Open Cur For Select * From scott.dept;
7 Loop
8 Fetch Cur Into R;
9 Exit When Cur%Notfound;
10 dbms_output.put_line('DEPTNO:'||R.deptno||' DNAME:'||R.DNAME||' LOC:'||R.LOC);
11 End Loop;
12 End;
13 /
DEPTNO:10 DNAME:ACCOUNTING LOC:NEW YORK
DEPTNO:20 DNAME:RESEARCH LOC:DALLAS
DEPTNO:30 DNAME:SALES LOC:CHICAGO
DEPTNO:40 DNAME:OPERATIONS LOC:BOSTON
PL/SQL procedure successfully completed

3、使用SYS_REFCURSOR作为输出游标
SQL> Create Or Replace Procedure Proc1(Cur_Out Out Sys_Refcursor)
2 Is
3 Begin
4 Open Cur_Out For Select * From Dept;
5 End;
6 /
Procedure created
SQL> Declare
2 Cur Sys_Refcursor;
3 R dept%Rowtype;
4 Begin
5 Proc1(Cur);
6 Loop
7 Fetch Cur Into R;
8 Exit When Cur%Notfound;
9 dbms_output.put_line('DEPTNO:'||R.deptno||' DNAME:'||R.DNAME||' LOC:'||R.LOC);
10 End Loop;
11 End;
12 /
DEPTNO:10 DNAME:ACCOUNTING LOC:NEW YORK
DEPTNO:20 DNAME:RESEARCH LOC:DALLAS
DEPTNO:30 DNAME:SALES LOC:CHICAGO
DEPTNO:40 DNAME:OPERATIONS LOC:BOSTON
PL/SQL procedure successfully completed

4、使用自定义游标作为输出游标
SQL> Create Or Replace Package pck2
2 Is
3 Type t_csr Is Ref Cursor ;
4 End;
5 /
Package created
SQL> Create Or Replace Procedure Proc2(Cur_Out Out pck2.t_csr)
2 Is
3
4 Begin
5 Open Cur_Out For Select * From Dept;
6 End;
7 /
Procedure created
SQL> Declare
2 Cur pck2.t_csr;
3 R dept%Rowtype;
4 Begin
5 Proc2(Cur);
6 Loop
7 Fetch Cur Into R;
8 Exit When Cur%Notfound;
9 dbms_output.put_line('DEPTNO:'||R.deptno||' DNAME:'||R.DNAME||' LOC:'||R.LOC);
10 End Loop;
11 End;

12 /
DEPTNO:10 DNAME:ACCOUNTING LOC:NEW YORK
DEPTNO:20 DNAME:RESEARCH LOC:DALLAS
DEPTNO:30 DNAME:SALES LOC:CHICAGO
DEPTNO:40 DNAME:OPERATIONS LOC:BOSTON
PL/SQL procedure successfully completed