《利用函数返回结果集方法总结》由会员分享，可在线阅读，更多相关《利用函数返回结果集方法总结（4页珍藏版）》请在金锄头文库上搜索。
1、利用函数返回结果集方法总结返回结果集函数可以将变量值传递给函数得到指定的结果集，优点在于灵活控制结果集变量及输出，不用将sql嵌入到页面代码里，业务逻辑如有更改可以直接在数据库中维护。现总结三种方法：OBJECT TYPE、OBJECT TYPE+PIPE ROW、RECORD+PIPE ROWOBJECT TYPETYPE定义create type tp_obj_emp as object( empno NUMBER(4), ename VARCHAR2(10), job VARCHAR2(9), mgr NUMBER(4), hiredate DATE, sal NUMBER(7,2),
2、comm NUMBER(7,2), deptno NUMBER(2)create type tp_tab_emp is table of tp_obj_emp;函数定义create or replace function f_test_record(p_deptno number) return tp_tab_emp as v_tab tp_tab_emp;begin select tp_obj_emp(empno, ename, job, mgr, hiredate, sal, comm, deptno) bulk collect into v_tab from emp where dept
3、no = p_deptno; return v_tab;end;调用SQL select * from table(f_test_record(10); EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO- - - - - - - - 7782 CLARK MANAGER 7839 09-6月 -81 2450 10 7839 KING PRESIDENT 17-11月-81 5000 10 7934 MILLER CLERK 7782 23-1月 -82 1300 10已用时间: 00: 00: 00.01OBJECT TYPE+PIPE ROWTYPE
4、定义create type tp_obj_emp 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)create type tp_tab_emp is table of tp_obj_emp;函数定义create or replace function f_test_record_pipe(p_deptno number) return tp_tab_em
5、p pipelined as v_obj tp_obj_emp;begin for cur in (select * from emp where deptno = p_deptno) loop v_obj := tp_obj_emp(cur.empno, cur.ename, cur.job, cur.mgr, cur.hiredate, cur.sal, m, cur.deptno); pipe row(v_obj); end loop;end;调用SQL select * from table(f_test_record_pipe(10); EMPNO ENAME JOB MGR HIR
6、EDATE SAL COMM DEPTNO- - - - - - - - 7782 CLARK MANAGER 7839 09-6月 -81 2450 10 7839 KING PRESIDENT 17-11月-81 5000 10 7934 MILLER CLERK 7782 23-1月 -82 1300 10已用时间: 00: 00: 00.01RECORD+PIPE ROW定义包create or replace package pkg_pipe_test as type t_rec_emp is record( empno number(4), ename varchar2(10),
7、job varchar2(9), mgr number(4), hiredate date, sal number(7, 2), comm number(7, 2), deptno number(2); type t_tab_emp is table of t_rec_emp; function f_test_record_pipe_noc(p_deptno number) return t_tab_emp pipelined;end;create or replace package body pkg_pipe_test is function f_test_record_pipe_noc(
8、p_deptno number) return t_tab_emp pipelined as v_rec t_rec_emp; begin for cur in (select * from emp where deptno = p_deptno) loop v_rec.empno := cur.empno; v_rec.ename := cur.ename; v_rec.job := cur.job; v_rec.mgr := cur.mgr; v_rec.hiredate := cur.hiredate; v_rec.sal := cur.sal; v_m := m; v_rec.dept
9、no := cur.deptno; pipe row(v_rec); end loop; end;end;调用SQL select * from table(pkg_pipe_test.f_test_record_pipe_noc(10); EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO- - - - - - - - 7782 CLARK MANAGER 7839 09-6月 -81 2450 10 7839 KING PRESIDENT 17-11月-81 5000 10 7934 MILLER CLERK 7782 23-1月 -82 1300 10已用时间: 00: 00: 00.01总结对于OBJECT TYPE和OBJECT TYPE+PIPE ROW的方法需要在数据库里定义OBJECT TYPE而RECORD+PIPE ROW需要在包内定义RECORD类型。
