以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”这样可以返回多条记录。
以上,希望对您有帮助,祝好!