你的位置:首页 > 软件开发 > 数据库 > 利用函数返回结果集

利用函数返回结果集

发布时间:2016-07-04 10:00:06
利用函数返回结果集方法总结返回结果集函数可以将变量值传递给函数得到指定的结果集,优点在于灵活控制结果集变量及输出,不用将sql嵌入到页面代码里,业务逻辑如有更改可以直接在数据库中维护。现总结三种方法:OBJECT TYPE、OBJECT TYPE+PIPE ROW、RECORD+ ...

利用函数返回结果集方法总结

返回结果集函数可以将变量值传递给函数得到指定的结果集,优点在于灵活控制结果集变量及输出,不用将sql嵌入到页面代码里,业务逻辑如有更改可以直接在数据库中维护。

现总结三种方法:OBJECT TYPE、OBJECT TYPE+PIPE ROW、RECORD+PIPE ROW

OBJECT TYPE

TYPE定义

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

OBJECT TYPE+PIPE ROW

TYPE定义

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

RECORD+PIPE ROW

定义包

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类型。


原标题:利用函数返回结果集

关键词:函数

*特别声明:以上内容来自于网络收集,著作权属原作者所有,如有侵权,请联系我们: admin#shaoqun.com (#换成@)。