PROCEDURE+FUNCTION返回游标类型输出参数

 1、procedure返回游标类型输出参数

---sqlplus环境中的游标为refcursor ,但PL/SQL中sys_refcursor   

a、创建存储过程

 create or replace procedure cur_test(c out sys_refcursor)    as
begin
open c for select * from test;
end;

  b、使用

 SQL> variable c refcursor
SQL> begin
  2  cur_test(:c);
  3  end;
  4  /

PL/SQL 过程已成功完成。

SQL> print c

        ID NAME                 LAST_NAME                    TT
---------- -------------------- -------------------- ----------
         1 %66_                                               0
         3 a3                                                 0
         4 ab4                  aab                           0
         5 abc5                 aabc                          0
         2 a                                                  0
         6 ab                                                 0

已选择6行。

2、function返回游标类型输出参数

a、创建存储过程

create or replace function cur_test1 return sys_refcursor is
  Result sys_refcursor;
begin
  open Result for select *   from test where rownum=1;
  return(Result);
end cur_test1;

b、使用

SQL> variable c refcursor
SQL> exec :c :=cur_test1();

PL/SQL 过程已成功完成。

SQL> print c

        ID NAME                 LAST_NAME                    TT
---------- -------------------- -------------------- ----------
         1 %66_                                               0