函数返回数据集

http://blog.csdn.net/minitoy/article/details/6159575

1.返回cursor
SQL> set serveroutput on
SQL> 
SQL> create or replace function func_return_cursor
  2  return sys_refcursor
  3  as
  4  cv_now sys_refcursor;
  5  begin
  6   open cv_now for select empno from emp;
  7   return cv_now;
  8  end;
  9  /
 
Function created
 
SQL> 
SQL> DECLARE
  2    cv_1    SYS_REFCURSOR;
  3    v_empno emp.empno%TYPE;
  4  BEGIN
  5    SELECT func_return_cursor INTO cv_1  FROM dual;
  6    LOOP
  7      FETCH cv_1
  8        INTO v_empno;
  9      EXIT WHEN cv_1%NOTFOUND;
 10      dbms_output.put_line(v_empno);
 11    END LOOP;
 12    CLOSE cv_1;
 13  END;
 14  /
 
7369
7499
7521
7566
7654
7698
7782
7788
7839
7844
7876
7900
7902
7934
 
PL/SQL procedure successfully completed
 
SQL>

------------------------------------------------------------------------------------------------------------------------------------------------------------

2.返回表对象,表对象生成使用循环精确控制(效率较低);
SQL> create or replace type type_obj_emp as object(empno number(20),ename varchar2(10));
  2  /
 
Type created
 
SQL> create or replace type type_t_emp as table of type_obj_emp;
  2  /
 
Type created
 
SQL> 
SQL> CREATE OR REPLACE FUNCTION func_return_cursor RETURN type_t_emp AS
  2    cv_now  SYS_REFCURSOR;
  3    v_t_emp type_t_emp;
  4    v_empno NUMBER(10);
  5    v_ename VARCHAR2(10);
  6    v_num   NUMBER;
  7  BEGIN
  8    v_num := 1;
  9    OPEN cv_now FOR
 10      SELECT empno, ename FROM emp;
 11    LOOP
 12      FETCH cv_now
 13        INTO v_empno, v_ename;
 14      EXIT WHEN cv_now%NOTFOUND;
 15      IF v_num = 1 THEN
 16        v_t_emp := type_t_emp(type_obj_emp(v_empno, v_ename));
 17      ELSE
 18        v_t_emp.EXTEND;
 19        v_t_emp(v_num) := type_obj_emp(v_empno, v_ename);
 20      END IF;
 21      v_num := v_num + 1;
 22    END LOOP;
 23    CLOSE cv_now;
 24    RETURN v_t_emp;
 25  END;
 26  /
 
Function created
 
SQL> select * from table(func_return_cursor);
 
                EMPNO ENAME
--------------------- ----------
                 7369 SMITH
                 7499 ALLEN
                 7521 WARD
                 7566 JONES
                 7654 MARTIN
                 7698 BLAKE
                 7782 CLARK
                 7788 SCOTT
                 7839 KING
                 7844 TURNER
                 7876 ADAMS
                 7900 JAMES
                 7902 FORD
                 7934 MILLER
 
14 rows selected
 
SQL>

注:需要改进的地方,表变量的初始化可以在变量定义时初始化,这样就不需要在程序中判断,提高效率.

SQL> CREATE OR REPLACE FUNCTION func_return_cursor RETURN type_t_emp AS
  2    cv_now  SYS_REFCURSOR;
  3    v_t_emp type_t_emp := type_t_emp();
  4    v_empno NUMBER(10);
  5    v_ename VARCHAR2(10);
  6    v_num   NUMBER;
  7  BEGIN
  8    v_num := 1;
  9    OPEN cv_now FOR
 10      SELECT empno, ename FROM emp;
 11    LOOP
 12      FETCH cv_now
 13        INTO v_empno, v_ename;
 14      EXIT WHEN cv_now%NOTFOUND;
 15      v_t_emp.EXTEND;
 16      v_t_emp(v_num) := type_obj_emp(v_empno, v_ename);
 17      v_num := v_num + 1;
 18    END LOOP;
 19    CLOSE cv_now;
 20    RETURN v_t_emp;
 21  END;
 22  /
 
Function created
 
SQL> select * from table(func_return_cursor);
 
                EMPNO ENAME
--------------------- ----------
                 7369 SMITH
                 7499 ALLEN
                 7521 WARD
                 7566 JONES
                 7654 MARTIN
                 7698 BLAKE
                 7782 CLARK
                 7788 SCOTT
                 7839 KING
                 7844 TURNER
                 7876 ADAMS
                 7900 JAMES
                 7902 FORD
                 7934 MILLER
 
14 rows selected
 
SQL>

------------------------------------------------------------------------------------------------------------------------------------------------------------

3.返回表对象,使用bulk collect into批量插入(效率较高)
SQL> CREATE OR REPLACE FUNCTION func_return_cursor RETURN type_t_emp AS
  2    v_t_emp type_t_emp;
  3  BEGIN
  4    SELECT type_obj_emp(empno, ename) BULK COLLECT INTO v_t_emp FROM emp;
  5    RETURN v_t_emp;
  6  END;
  7  /
 
Function created
 
SQL> select * from table(func_return_cursor);
 
                EMPNO ENAME
--------------------- ----------
                 7369 SMITH
                 7499 ALLEN
                 7521 WARD
                 7566 JONES
                 7654 MARTIN
                 7698 BLAKE
                 7782 CLARK
                 7788 SCOTT
                 7839 KING
                 7844 TURNER
                 7876 ADAMS
                 7900 JAMES
                 7902 FORD
                 7934 MILLER
 
14 rows selected
 
SQL>

------------------------------------------------------------------------------------------------------------------------------------------------------------

4.使用pipelined返回

SQL> CREATE OR REPLACE FUNCTION func_return_cursor RETURN type_t_emp
  2    PIPELINED AS
  3    cv_now  SYS_REFCURSOR;
  4    v_empno NUMBER(10);
  5    v_ename VARCHAR2(10);
  6  BEGIN
  7    OPEN cv_now FOR
  8      SELECT empno, ename FROM emp;
  9    LOOP
 10      FETCH cv_now
 11        INTO v_empno, v_ename;
 12      EXIT WHEN cv_now%NOTFOUND;
 13      PIPE ROW(type_obj_emp(v_empno, v_ename));
 14    END LOOP;
 15    CLOSE cv_now;
 16    RETURN;
 17  END;
 18  /
 
Function created
 
SQL> select * from table(func_return_cursor);
 
     EMPNO ENAME
---------- ----------
      7369 SMITH
      7499 ALLEN
      7521 WARD
      7566 JONES
      7654 MARTIN
      7698 BLAKE
      7782 CLARK
      7788 SCOTT
      7839 KING
      7844 TURNER
      7876 ADAMS
      7900 JAMES
      7902 FORD
      7934 MILLER
 
14 rows selected
 
SQL>

注:pipelined将表变量声明及表数据插入隐藏,语法简单,但效率与方法2基本无差异.