利用函數返回結果集方法總結
返回結果集函數可以將變量值傳遞給函數得到指定的結果集,優點在於靈活控制結果集變量及輸出,不用將sql嵌入到頁面代碼裡,業務邏輯如有更改可以直接在數據庫中維護。
現總結三種方法:OBJECT TYPE、OBJECT TYPE+PIPE ROW、RECORD+PIPE ROW
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(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 deptno = 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.01
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_emp
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,
cur.comm,
cur.deptno);
pipe row(v_obj);
end loop;
end;
SQL> select * from table(f_test_record_pipe(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
create or replace package pkg_pipe_test as
type t_rec_emp 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 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(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_rec.comm := cur.comm;
v_rec.deptno := 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類型。