oracle管道函数PIPELINED

先列举一个从网上搜到的比较简单的使用管道函数的例子：

 

--首先定义类型，管道函数可以返回多行数据，所以不能使用基础数据类型。   
create or replace type my_type as table of varchar2(4000);   
  
--创建管道函数   
--管道函数关键字： "[b]pipelined[/b]"   
--管道函数返回一行使用："[b]pipe row(...)[/b]"   
create or replace function func_pipe_test return my_type   
  pipelined is  
begin  
  for i in reverse 1 .. 10 loop   
    pipe row(i);   
  end loop;   
  
end;   
  
--使用管道函数   
select * from table(func_pipe_test);  

 

下面结合项目中使用的与大家分享：

 

 

--首先定义类型，管道函数可以返回多行数据，所以不能使用基础数据类型
CREATE OR REPLACE TYPE "IF_SET_TAB"
 AS TABLE OF RET_ROW
--定义每一行内容的类型（此处也可定义成类似表结构）
CREATE OR REPLACE TYPE "RET_ROW"
 
   AS OBJECT(Rval  int)
--

CREATE OR REPLACE FUNCTION FB_IS_SETACCOUNT
 (
 NO   INT
 )

 RETURN  IF_SET_TAB  PIPELINED
 IS
 RET_ROW0  RET_ROW:=RET_ROW(NULL);
 bizType   fb_business.biz_type%TYPE;
 num   int;
 ret   int :=0;
 BEGIN
 SELECT  biz_type  INTO  bizType  FROM  business  WHERE   bid=FB_IS_SETACCOUNT.NO;
 IF  (bizType = '101')  THEN
    SELECT  count(*)  into  num   FROM    ACCOUNTS  WHERE   ACCOUNT_TYPE  IN('CREDIT','DEPOSIT','INTREST.INCOME','WITHHOLDING')
    AND  BID =FB_IS_SETACCOUNT.NO   and   account_no is  not  null;
    if  num = 4   then
    ret := 1;
    end if;
  ELSIF(bizType = '104')    THEN
    SELECT  count(*)  into  num  from   ACCOUNTS  WHERE   ACCOUNT_TYPE  IN('CREDIT','DEPOSIT','CONSIGN.DEPOSIT','CHARGE.PAY','CHARGE.INCOME','CONSIGN.INTREST')
      AND  BID =FB_IS_SETACCOUNT.NO  and   account_no is  not  null;
    if  num = 6   then
    ret := 1;
    end  if;
   END   IF;
   
   RET_ROW0.Rval := FB_IS_SETACCOUNT.ret; 
   pipe row(RET_ROW0);  --加入管道
   return;
 END;

 下面列举管道函数中返回的是类似于数组结构的函数：

  

  

 

--首先定义类型，管道函数可以返回多行数据，所以不能使用基础数据类型

CREATE OR REPLACE TYPE "HN_STAT_KM_BALANCE_TAB"
   AS TABLE OF HN_STAT_KM_BALANCE_ROW      
   
--定义每一行内容的类型（此处类似表结构）
CREATE OR REPLACE TYPE "HN_STAT_KM_BALANCE_ROW"                                                                                                                                                   AS OBJECT
 (no             varchar2(10),
  name            varchar2(100),
  treeNo          varchar2(20),
  currentAmount            NUMBER(20,2),
  notifyAmount     NUMBER(20,2),
  threeMonthAmount      NUMBER(20,2),
  sixMonthAmount     NUMBER(20,2),
  oneYearAmount     NUMBER(20,2),
  twoYearAmount            NUMBER(20,2),
  threeYearAmount             NUMBER(20,2),
  fiveYearAmount         NUMBER(20,2),
  MoreYearAmount          NUMBER(20,2),
  consignDepositAmount        NUMBER(20,2),
  shortLoanAmount          NUMBER(20,2),
  longLoanAmount          NUMBER(20,2),
  consignLoanAmount          NUMBER(20,2),
  totalAmount          NUMBER(20,2)
)

--下面是函数
CREATE OR REPLACE FUNCTION HN_STAT_KM_BALANCE(
qryCltno  varchar2,
qryTreeNo varchar2,
containFlag  Integer,
qrydate IN DATE,
qryType Integer,    --null--

bz IN varchar2
)
RETURN HN_STAT_KM_BALANCE_TAB PIPELINED
IS
RET  HN_STAT_KM_BALANCE_ROW:=HN_STAT_KM_BALANCE_ROW(NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
anothorTreeNo varchar2(20);
qrybz varchar2(1);
BEGIN

  qrybz := bz;
  if(bz is null) then
      qrybz := 'R';
  end if; 
  --存款科目余额
  if qryType is null then
     for rec3 in(select no,treeno,name from table(HN_STAT_SHEET_CLIENT(qryCltno,qryTreeNo,containFlag,null)))
    loop
        RET := HN_STAT_KM_BALANCE_ROW(NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);

        ret.no := rec3.no;
        ret.treeNo := rec3.treeno;
        ret.name := rec3.name;
        ret.totalAmount := 0;
        ret.threeMonthAmount := 0;
        ret.sixMonthAmount := 0;
        ret.oneYearAmount := 0;
        ret.notifyAmount := 0;
        ret.twoYearAmount:= 0;
        ret.threeYearAmount:=0;
        ret.fiveYearAmount := 0;
        ret.MoreYearAmount := 0;
        if ret.treeNo = '001001000%' then
           anothorTreeNo := '001001';
        else
           anothorTreeNo := '000';
        end if;

        --存款统计 包括 通知，短期存款， 中长期存款
           
         for  rec in (select  sum(b.rval) amount,a.kmh  kmh  from  acnt  a,table(NS_GetRval(qrybz,a.no, qrydate, 0)) b,client n
            where    a.clientid=n.cltno  and  a.bz = qrybz  and  b.rval != 0
            and (n.treeno like ret.treeNo or n.treeno = anothorTreeNo)
            group by a.kmh  order by a.kmh)
        loop
           if (rec.kmh = '*') then
                 ret.threeMonthAmount := ret.threeMonthAmount + rec.amount;
           elsif(rec.kmh = '*') then
                 ret.sixMonthAmount := ret.sixMonthAmount + rec.amount;
           elsif(rec.kmh = '*') then
                 ret.oneYearAmount := ret.oneYearAmount + rec.amount;
           elsif(rec.kmh = '*') then
                 ret.notifyAmount := ret.notifyAmount + rec.amount;
           elsif(rec.kmh = '*') then
                 ret.twoYearAmount := ret.twoYearAmount + rec.amount;
           elsif(rec.kmh = '*') then
                 ret.threeYearAmount := ret.threeYearAmount + rec.amount;
           elsif(rec.kmh = '*') then
                 ret.fiveYearAmount := ret.fiveYearAmount + rec.amount;
           elsif(rec.kmh = '*') then
                 ret.MoreYearAmount := ret.MoreYearAmount + rec.amount;
           end if;
           
             ret.totalAmount := ret.totalAmount + rec.amount;
          
        end loop;
         PIPE ROW(RET);
    end loop;
  end if;
  RETURN;
END HN_STAT_KM_BALANCE;