Oracle管道函数
Oracle管道函数是一类特殊的函数,关键字pipelined表明这是一个Oracle管道函数,Oracle管道函数的返回值类型必须为集合,在函数中,pipe row语句被用来返回该集合的单个元素,函数以一个空的return语句结束,以表明它已经完成。一旦我们创建了上述函数,我们就可以使用table操作符从SQL查询中调用它,从而使Oracle函数返回table集合。
例1:使用隐式游标 for .. in
-------------------------------------------------------------------
create type f_emp_row_type as object
(
empno NUMBER(4),
ename VARCHAR2(10),
sal NUMBER(7,2),
job VARCHAR2(9)
);
/
create type f_emp_tab_type as table of f_emp_row_type;
/
create or replace function f_emp_test return f_emp_tab_type
pipelined as
emp_rows f_emp_row_type;
begin
for tt in (select empno,ename,sal,job from emp) loop
emp_rows := f_emp_row_type(tt.empno, tt.ename, tt.sal, tt.job);
pipe row(emp_rows);
end loop;
return;
end;
/
select * from table(f_emp_test);
-------------------------------------------------------------------
带有参数的管道函数
------------------------------------------------------------------------
create or replace function f_emp_test1(dno number) return f_emp_tab_type
pipelined as
emp_tables f_emp_row_type;
begin
for tt in (select empno,ename,sal,job from emp where deptno=dno) loop
emp_tables := f_emp_row_type(tt.empno, tt.ename, tt.sal, tt.job);
pipe row(emp_tables);
end loop;
return;
end;
/
select * from table(f_emp_test1(10));
------------------------------------------------------------------------
例2:使用集合(要使用包)
------------------------------------------------------------------------
create or replace package test_pkg2 as
type emp_row_type is record(
empno NUMBER(4),
ename VARCHAR2(10),
sal NUMBER(7, 2),
job VARCHAR2(9));
type emp_tab_type is table of emp_row_type;
function f_test_emp return emp_tab_type
pipelined;
end test_pkg2;
/
create or replace package body test_pkg2 as
function f_test_emp return emp_tab_type
pipelined is
emp_rows emp_row_type;
emp_rows_list emp_tab_type;
begin
select empno,ename,sal,job bulk collect into emp_rows_list from emp;
for i in 1 .. emp_rows_list.count loop
emp_rows := emp_rows_list(i);
pipe row(emp_rows);
end loop;
return;
end;
end test_pkg2;
/
select * from table(test_pkg2.f_test_emp);
------------------------------------------------------------------------
例3:使用游标
------------------------------------------------------------------------
create or replace type test_user_record as object
(
test_id number,
test_name varchar2(20),
test_sal number,
test_job varchar2(20)
);
/
create or replace type test_user_table as table of test_user_record;
/
create or replace package test_user_proc is
function select_user return test_user_table
pipelined;
end test_user_proc;
/
create or replace package body test_user_proc is
function select_user return test_user_table
pipelined as
pragma autonomous_transaction;
type t_cursor is ref cursor;
r t_cursor;
v_sql varchar2(3000);
v_test_id number;
v_test_name varchar2(20);
v_test_sal number;
v_test_job varchar2(20);
begin
v_sql := 'select empno,ename,sal,job from emp';
open r for v_sql;
loop
fetch r
into v_test_id, v_test_name, v_test_sal, v_test_job;
exit when r%notfound;
pipe row(test_user_record(v_test_id,
v_test_name,
v_test_sal,
v_test_job));
end loop;
close r;
return;
end;
end test_user_proc;
/
select * from table(test_user_proc.select_user);
------------------------------------------------------------------------
例4:Oracle 中使用 fetch bulk collect into 批量效率的读取游标数据
------------------------------------------------------------------------
create or replace package test_pkg1 as
type emp_row_type is record(
empno NUMBER(4),
ename VARCHAR2(10),
sal NUMBER(7, 2),
job VARCHAR2(9));
type emp_tab_type is table of emp_row_type;
function f_test_emp return emp_tab_type
pipelined;
end test_pkg1;
/
create or replace package body test_pkg1 as
function f_test_emp return emp_tab_type
pipelined is
emp_rows emp_row_type;
emp_rows_list emp_tab_type;
cursor cur_emp is
select empno, ename, sal, job from emp;
begin
open cur_emp;
fetch cur_emp bulk collect
into emp_rows_list;
for i in 1 .. emp_rows_list.count loop
emp_rows.empno := emp_rows_list(i).empno;
emp_rows.ename := emp_rows_list(i).ename;
emp_rows.sal := emp_rows_list(i).sal;
emp_rows.job := emp_rows_list(i).job;
pipe row(emp_rows);
end loop;
close cur_emp;
return;
end;
end test_pkg1;
/
select * from table(test_pkg1.f_test_emp);
------------------------------------------------------------------------
例5:
------------------------------------------------------------------------
create or replace type ty_obj_user as object
(
id number(20),
name varchar2(60)
);
/
create or replace package pkg1 as
type ty_rec_user is record(
id number(20),
name varchar2(60));
type tab_rec_user is table of ty_rec_user;
function f1(x number) return tab_rec_user
pipelined;
type tab_obj_user is table of ty_obj_user;
function f_pie_test(c number) return tab_obj_user
pipelined;
end pkg1;
/
create or replace package body pkg1 as
function f1(x number) return tab_rec_user
pipelined is
user_rec ty_rec_user;
begin
for i in 1 .. x loop
user_rec.id := i;
user_rec.name := 'user' || i;
pipe row(user_rec);
end loop;
return;
end;
function f_pie_test(c number) return tab_obj_user
pipelined is
user_obj ty_obj_user;
begin
for i in 1 .. c loop
user_obj := ty_obj_user(i, 'name' || i);
pipe row(user_obj);
end loop;
return;
end;
end pkg1;
/
select * from table(pkg1.f1(4));
select * from table(pkg1.f_pie_test(4));
select * from table(cast(pkg1.f1(4) as tab_rec_set));
------------------------------------------------------------------------
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/17012874/viewspace-693790/,如需转载,请注明出处,否则将追究法律责任。