管道函数提高系统性能

管道函数提高系统性能

从emp表中读取，修改工资和雇用日期，把修改过的数据装入emp2.emp表数据量很大，约千万条。

create table emp2 as
select empno,ename,hiredate,sal,deptno
from emp
where rownum=0;

create or replace
type emp2rec is object (
    empno number(10),
    ename varchar2(20),
    hiredate date,
    sal number(10,2),
    deptno number(6));
/

create or replace
type emp2list as table of emp2rec;
/

create or replace
package pkg2 is
    function consumer return sys_refcursor;
    function manipulator(src_rows sys_refcursor) return emp2list pipelined;
    procedure producer;
end;
/

create or replace
package body pkg2 is

function consumer return sys_refcursor is
    src_rows sys-refcursor;
begin
    open src_rows for
        select empno,ename,hiredate,sal,deptno
        from emp;
    return src_rows;
end;

function mainpulator(src_rows sys_refcursor)
        return emp2list pipelined is
    r emp2rec;
    e emp2rowtype;
begin
    loop
        fetch src_rows bulk collect into e limit 100;
        for i in 1..e.count loop
            e(i).sal:=e(i).sal+10;
            e(i).hiredate:=e(i).hiredate+1;
            pipe row (emp2rec(e(i).empno,e(i).ename,e(i).hiredate,e(i).sal,e(i).deptno));
        end loop;
        exit when src_rows%notfound;
    end loop;
    close src_rows;
    return;
end;

procedure producer is
    rc sys_sys_refcursor:=consumer;
begin
    insert into emp2
    select * from table(cast(manipulator(rc) as emp2list));
end;

end;
/

exec pkg2.producer;

可以直接省掉consumer和producer过程，使用标准sql：
insert into emp2
select * from table(
              cast(
              pkg2.manipulator(
               cursor(select empno,ename,hiredate,sal,deptno from emp))
              as emp2list));