oracle pipelined 函数 及其替代方法

以oracle自带scott用户下emp表为例：

1.pipelined 方式

（1）先建好record 和table ,其中table是 record类型的。

CREATE OR REPLACE PACKAGE PIPELINES_TEST IS
type emp_record is record (
  EMPNO    NUMBER(4) ,
  ENAME    VARCHAR2(10),
  JOB      VARCHAR2(9),
  MGR      NUMBER(4),
  HIREDATE DATE,
  SAL      NUMBER(7,2),
  COMM     NUMBER(7,2),
  DEPTNO   NUMBER(2)
);

type emp_table is table of emp_record ;

END PIPELINES_TEST;

（2） 建函数 ，功能：根据员工编号和工作查询

CREATE OR REPLACE FUNCTION GET_EMP_RECORD_TABLE
(EMPNO NUMBER,JOB VARCHAR2)
RETURN PIPELINES_TEST.emp_table
PIPELINED IS

V_COUNT INTEGER:=1;
--
V_TABLE  PIPELINES_TEST.emp_table:=PIPELINES_TEST.emp_table();
--传的函数的参数，根据参数查询
CURSOR C(EMPNO_IN NUMBER ,JOB_IN VARCHAR2) IS
SELECT * FROM EMP E WHERE E.EMPNO = EMPNO_IN AND E.JOB = JOB_IN;


BEGIN
  FOR I IN C (EMPNO,JOB) LOOP
    V_TABLE.EXTEND;
    V_TABLE(V_COUNT):=I;
    PIPE ROW (V_TABLE(V_COUNT));
    V_COUNT := V_COUNT + 1;

    END LOOP;
--return
  End;

 (3)测试

select * from table(get_emp_record_table(7369,'CLERK'));

2. pipelined 的替代方法

(1)用object对象，建立object对象

create or replace type emp_object as object(
  EMPNO    NUMBER(4) ,
  ENAME    VARCHAR2(10),
  JOB      VARCHAR2(9),
  MGR      NUMBER(4),
  HIREDATE DATE,
  SAL      NUMBER(7,2),
  COMM     NUMBER(7,2),
  DEPTNO   NUMBER(2)
)

（2）建 table

create or replace type emp_otable is table of emp_object

(3)建函数 功能：根据员工编号和工作查询

CREATE OR REPLACE FUNCTION GET_EMP_OBJECT_TABLE
(EMPNO NUMBER,JOB VARCHAR2)
RETURN EMP_OTABLE
 IS

V_COUNT INTEGER:=1;
--
V_LIST_TABLE EMP_OTABLE:=EMP_OTABLE();
--
CURSOR C(EMPNO_IN NUMBER ,JOB_IN VARCHAR2) IS
SELECT * FROM EMP E WHERE E.EMPNO = EMPNO_IN AND E.JOB = JOB_IN;


BEGIN
  FOR I IN C (EMPNO,JOB) LOOP
    V_LIST_TABLE.EXTEND;

   -- 以下和（1）不同，把object对象放入table
   V_LIST_TABLE(V_COUNT):=EMP_OBJECT(I.EMPNO,I.ENAME,I.JOB,I.MGR,I.HIREDATE,I.SAL,I.COMM,I.DEPTNO);
    V_COUNT := V_COUNT + 1;

    END LOOP;
    
RETURN V_LIST_TABLE;

  End GET_EMP_OBJECT_TABLE;

（4）测试

select * from table(get_emp_object_table(7369,'CLERK'));

3.函数里的参数有主键，所以查询得到结果是一条记录，可以将SQL语句条件改为 “or”这样可以返回多条记录。

以上，希望对您有帮助，祝好！