带sys_refcursor输出参数的存储过程

amwdnuff

带sys_refcursor输出参数的存储过程



Sql代码
CREATE OR REPLACE PROCEDURE sp_test (   
       p_outstr  OUT VARCHAR2   
      ,p_outint  OUT NUMBER   
      ,p_ref1    OUT SYS_REFCURSOR   
      ,p_ref2    OUT SYS_REFCURSOR   
      )   
AS   
BEGIN   
   p_outstr := 'abc';   
   p_outint := '56789';   
   OPEN p_ref1 FOR SELECT ROWNUM*2 AS RN FROM DUAL CONNECT BY ROWNUM<=10;   
   OPEN p_ref2 FOR SELECT ROWNUM*2+1 AS RN FROM DUAL CONNECT BY ROWNUM<=10;   
      
END sp_test;   
   
/   
   
过程已创建。  
CREATE OR REPLACE PROCEDURE sp_test (
       p_outstr  OUT VARCHAR2
      ,p_outint  OUT NUMBER
      ,p_ref1    OUT SYS_REFCURSOR
      ,p_ref2    OUT SYS_REFCURSOR
      )
AS
BEGIN
   p_outstr := 'abc';
   p_outint := '56789';
   OPEN p_ref1 FOR SELECT ROWNUM*2 AS RN FROM DUAL CONNECT BY ROWNUM<=10;
   
END sp_test;

/

过程已创建。



调用存储过程：

declare

p_outstr VARCHAR2(10);
p_outint NUMBER;
p_ref1 REFCURSOR;

Result_  number;


begin

sp_test(p_outstr,p_outint,p_ref1);   

loop

fetch p_ref1 into result_;

exit when p_ref1%notfound

dbms_output.put_line(result_);

end loop;

close p_ref1;

end;



利用print客户端打印，sqlplus下：

Sql代码
SET AUTOPRINT ON   
VAR p_outstr VARCHAR2(10);   
VAR p_outint NUMBER;   
VAR p_ref1 REFCURSOR;   
VAR p_ref2 REFCURSOR;  
SET AUTOPRINT ON
VAR p_outstr VARCHAR2(10);
VAR p_outint NUMBER;
VAR p_ref1 REFCURSOR;
VAR p_ref2 REFCURSOR;



Sql代码
scott@ORCL>EXEC sp_test(:p_outstr,:p_outint,:p_ref1,:p_ref2);   
   
PL/SQL 过程已成功完成。   
   
   
        RN   
----------   
         3   
         5   
         7   
         9   
        11   
        13   
        15   
        17   
        19   
        21   
        23   
   
已选择11行。   
   
   
        RN   
----------   
         2   
         4   
         6   
         8   
        10   
        12   
        14   
        16   
        18   
        20   
        22   
   
已选择11行。   
   
   
  P_OUTINT   
----------   
     56789   
   
   
P_OUTSTR   
--------------------------------   
abc  
scott@ORCL>EXEC sp_test(:p_outstr,:p_outint,:p_ref1,:p_ref2);

PL/SQL 过程已成功完成。


        RN
----------
         3
         5
         7
         9
        11
        13
        15
        17
        19
        21
        23

已选择11行。


        RN
----------
         2
         4
         6
         8
        10
        12
        14
        16
        18
        20
        22

已选择11行。


  P_OUTINT
----------
     56789


P_OUTSTR
--------------------------------
abc
也可以在声明输入输出变量之后，依次print p_outstr,...........



2.使用对象类型，同样返回结果集.

Sql代码
scott@ORCL> CREATE TYPE t_test AS OBJECT (   
  2                 id    NUMBER   
  3             ,name VARCHAR2(20)   
  4      )   
  5  ;   
  6  /   
   
类型已创建。   
   
scott@ORCL>CREATE TYPE tb_test AS TABLE OF t_test;   
  2  /   
   
类型已创建。   
   
scott@ORCL>CREATE OR REPLACE FUNCTION f_test RETURN tb_test   
  2  AS   
  3     v_ret tb_test:=tb_test();   
  4  BEGIN   
  5     FOR i IN 1..10 LOOP   
  6         v_ret.EXTEND;   
  7         v_ret(i) := t_test(i,'name'||i);   
  8     END LOOP;   
  9     RETURN v_ret;   
10  END f_test;   
11  /   
   
函数已创建。   
   
scott@ORCL>select * from table(f_test);   
   
        ID NAME   
---------- ------------------------------   
         1 name1   
         2 name2   
         3 name3   
         4 name4   
         5 name5   
         6 name6   
         7 name7   
         8 name8   
         9 name9   
        10 name10   
   
已选择10行。  
scott@ORCL> CREATE TYPE t_test AS OBJECT (
  2                         id    NUMBER
  3                      ,name VARCHAR2(20)
  4      )
  5  ;
  6  /

类型已创建。

scott@ORCL>CREATE TYPE tb_test AS TABLE OF t_test;
  2  /

类型已创建。

scott@ORCL>CREATE OR REPLACE FUNCTION f_test RETURN tb_test
  2  AS
  3     v_ret tb_test:=tb_test();
  4  BEGIN
  5     FOR i IN 1..10 LOOP
  6         v_ret.EXTEND;
  7         v_ret(i) := t_test(i,'name'||i);
  8     END LOOP;
  9     RETURN v_ret;
10  END f_test;
11  /

函数已创建。

scott@ORCL>select * from table(f_test);

        ID NAME
---------- ------------------------------
         1 name1
         2 name2
         3 name3
         4 name4
         5 name5
         6 name6
         7 name7
         8 name8
         9 name9
        10 name10

已选择10行。





3. 用SELECT BULK COLLECT INTO取得数据,返回嵌套表的例子。

Sql代码
scott@ORCL>create table test (id number, name varchar2(20));   
   
表已创建。   
   
scott@ORCL>CREATE TYPE t_test AS OBJECT (   
  2       id    NUMBER   
  3      ,name VARCHAR2(20)   
  4      )   
  5  /   
   
类型已创建。   
   
scott@ORCL>;   
scott@ORCL>CREATE TYPE tb_test AS TABLE OF t_test;   
  2  /   
   
类型已创建。   
   
scott@ORCL>CREATE OR REPLACE FUNCTION f_test RETURN tb_test   
  2  AS   
  3     v_ret tb_test:=tb_test();   
  4  BEGIN   
  5     SELECT t_test(id,name)   
  6       BULK COLLECT INTO v_ret   
  7       FROM TEST;   
  8   
  9     RETURN v_ret;   
10  END f_test;   
11  /   
   
函数已创建。   
   
scott@ORCL>select * from table(f_test);   
   
未选定行   
   
scott@ORCL>insert into test select rownum,'NAME'||rownum FROM DUAL CONNECT BY RO   
WNUM<=10;   
   
已创建11行。   
   
scott@ORCL>select * from table(f_test);   
   
        ID NAME   
---------- ------------------------------   
         1 NAME1   
         2 NAME2   
         3 NAME3   
         4 NAME4   
         5 NAME5   
         6 NAME6   
         7 NAME7   
         8 NAME8   
         9 NAME9   
        10 NAME10   
        11 NAME11   
   
已选择11行。