SYS_REFCURSOR一例

1.看见很多人调试过程依然使用DBMS_OUTPUT.PUT_LINE进行着输出，或是对oracle procedure返回resultset比较疑惑，下面的例子仅供参考。

 

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;
   OPEN p_ref2 FOR SELECT ROWNUM*2+1 AS RN FROM DUAL CONNECT BY ROWNUM<=10;
   
END sp_test;

/

过程已创建。

 

 

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

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;

 

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.使用对象类型，同样返回结果集.

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

http://diegoball.iteye.com/blog/518604