pl-sql中函数的输出

一函数例子：
CREATE OR REPLACE FUNCTION "F_TEST"(                                                    p_areaid           in number,
                                                     p_prodid           in number,
                                                      p_year             in number,
                                                      p_month            in number)
  return table_ztdomesticdemandaudit
/*
  reportnames  */
  pipelined is
  pragma autonomous_transaction;
  v_row_ztdomesticdemandaudit ROW_ZTDOMESTICDEMANDAUDIT;
  months                      varchar2(32);
  v_xilie                     varchar2(128);
  v_pinpai                    varchar2(128);
  v_jihuapinpai               varchar2(128);
  v_prodcode                  varchar2(32);
  v_prodname                  varchar2(512);
  v_gbjx                      number(16) := 0;
  v_gbpt                      number(16) := 0;
  v_bmjx                      number(16) := 0;
  v_bmpt                      number(16) := 0;
  v_qyjx                      number(16) := 0;
  v_qupt                      number(16) := 0;
  v_ywyjx                     number(16) := 0;
  v_ywypt                     number(16) := 0;
  v_khjx                      number(16) := 0;
  v_khypt                     number(16) := 0;
  v_qty1                      number(16);
  v_qty2                      number(16);
  v_qty3                      number(16);
  v_mess                      varchar2(512) := '';
  v_adjustqty1                number(16) := 0;
  v_adjustqty2                number(16) := 0;
  v_prodid                     number(16);
  v_areaid                    number(16);
  v_plancustomertype           number(16);
  ---------------------------------------------------------

begin

  for c1 in (select to_char(a.AUDITDATE, 'yyyy-mm') as months,
                    d.valuename as xl, --系列
                    pb.brandname AS pp,
                    dd.valuename as jhpp,
                    p.prodcode,
                    pl.prdn,
                    p.prodid,ba.areaid,a.plancustomertype
             --a.DEMANDQTY1
            
               from ztdomesticdemandaudit a,
                    product               p,
                    dictionary            d,
                    dictionary            dd,
                    productlang           pl,
                    productbrand          pb,
                    businessarea          ba
            
              where 1 = 1
                and a.prodid = p.prodid
                and p.prodid = pl.prodid
                and p.brandcode = pb.brandcode
                and pl.languagecode = pb.languagecode
                and pl.languagecode = 'cn'
                and a.plancustomertype in (1, 2)
                and a.areaid = ba.areaid
                and p.ztprodseries = d.value
                and d.type = 'ZTTyreSeries'
                and d.languagecode = 'cn'
                and p.ztplanbrand = dd.value(+)
                and dd.type(+) = 'ZTPlanBrand'
                and dd.languagecode(+) = 'cn'
                and ba.areaid in
                    (select areaid
                       from businessarea
                      start with areaid = nvl(p_areaid, ba.areaid)
                     connect by parentareaid = prior areaid)
               -- and a.plancustomertype =
               --     nvl(p_plancustomertype, a.plancustomertype)
                and to_char(a.auditdate, 'yyyy') =
                    nvl(p_year, to_char(a.auditdate, 'yyyy'))
                and to_char(a.auditdate, 'mm') =
                    nvl(p_month, to_char(a.auditdate, 'mm'))
                and p.PRODID = nvl(p_prodid, p.PRODID)
              order by a.prodid) loop
 
    months        := c1.months;
    v_xilie       := c1.xl;
    v_pinpai      := c1.pp;
    v_jihuapinpai := c1.jhpp;
    v_prodcode    := c1.prodcode;
    v_prodname    := c1.prdn;
    v_prodid      := c1.prodid;
    v_areaid      :=c1.areaid;
    v_plancustomertype  :=c1.plancustomertype;
    --v_qty1:=c1.DEMANDQTY1;
    --adjustqty 1
    if v_plancustomertype = 1 then
    begin
      select nvl(adjustqty, 0)
        into v_adjustqty1
        from ztdomesticdemandadjust zt,
             ztdomesticdemand       zc,
             ztdomesticdemanddetail zcd
       where zc.orgid = zt.orgid
         and zc.partnerid = zt.partnerid
         and zcd.prodid = zt.prodid
         and to_char(zt.adjustdate, 'yyyy') =
             nvl(to_char(months,'yyyy'), to_char(zt.adjustdate, 'yyyy'))
         and to_char(zt.adjustdate, 'mm') =
             nvl(to_char(months,'mm'), to_char(zt.adjustdate, 'mm'))
        -- and zt.prodid = nvl(p_prodid, zt.prodid)
          and zt.prodid=nvl(v_prodid,zt.prodid)
         and zc.PLANCUSTYPE = 1;
       exception
                    when others then
                      v_adjustqty1:= 0;
       end;
    elsif v_plancustomertype = 2 then
   
      --adjustqty 2
      begin
        select nvl(adjustqty, 0)
          into v_adjustqty2
          from ztdomesticdemandadjust zt,
               ztdomesticdemand       zc,
               ztdomesticdemanddetail zcd
         where zc.orgid = zt.orgid
           and zc.partnerid = zt.partnerid
           and zcd.prodid = zt.prodid
            and to_char(zt.adjustdate, 'yyyy') =
               nvl(to_char(to_date(months,'yyyy-MM'),'yyyy'), to_char(zt.adjustdate, 'yyyy'))
           and to_char(zt.adjustdate, 'mm') =
               nvl(to_char(to_date(months,'yyyy-MM'),'mm'), to_char(zt.adjustdate, 'mm'))
          -- and zt.prodid = nvl(p_prodid, zt.prodid)
            and zt.prodid=nvl(v_prodid,zt.prodid)
           and zc.PLANCUSTYPE = 2;
       exception
                    when others then
                      v_adjustqty2:= 0;
       end;
    end if;
 
    --gbjx
    begin
      select nvl(a1.balanceqty,0)
        into v_gbjx
        from ztdomesticdemandaudit a1, businessarea ba1, product prod
       where a1.areaid = ba1.areaid
         and a1.prodid = prod.prodid
         and plancustomertype = 1
         and ba1.grade = 2
         and ba1.areaid in
             (select areaid
                from businessarea
               start with areaid = nvl(v_areaid, ba1.areaid)
              connect by parentareaid = prior areaid)
          and to_char(a1.auditdate, 'yyyy') =
               nvl(to_char(to_date(months,'yyyy-MM'),'yyyy'), to_char(a1.auditdate, 'yyyy'))
           and to_char(a1.auditdate, 'mm') =
               nvl(to_char(to_date(months,'yyyy-MM'),'mm'), to_char(a1.auditdate, 'mm'))
        -- and prod.PRODID = nvl(p_prodid, prod.prodid)
         and prod.prodid=nvl(v_prodid,prod.prodid);
      exception
                    when others then
                      v_gbjx:= 0;
       end;
    v_gbjx := v_gbjx + v_adjustqty1;
    --gbpt
       begin
          select nvl(a1.balanceqty,0)
            into v_gbpt
            from ztdomesticdemandaudit a1, businessarea ba1, product prod
           where a1.areaid = ba1.areaid
             and a1.prodid = prod.prodid
             and plancustomertype = 2
             and ba1.grade = 2
                and ba1.areaid in
                 (select areaid
                    from businessarea
                   start with areaid = nvl(v_areaid, ba1.areaid)
                  connect by parentareaid = prior areaid)
         
              and to_char(a1.auditdate, 'yyyy') =
                   nvl(to_char(to_date(months,'yyyy-MM'),'yyyy'), to_char(a1.auditdate, 'yyyy'))
               and to_char(a1.auditdate, 'mm') =
                   nvl(to_char(to_date(months,'yyyy-MM'),'mm'), to_char(a1.auditdate, 'mm'))
            -- and prod.PRODID = nvl(p_prodid, prod.prodid)
             and prod.prodid=nvl(v_prodid,prod.prodid);
            
       exception
                    when others then
                      v_gbpt:= 0;
       end;
    v_gbpt := v_gbpt + v_adjustqty2;
    --bmjx
    begin
        select nvl(a1.balanceqty,0)
          into v_bmjx
          from ztdomesticdemandaudit a1, businessarea ba1, product prod
         where a1.areaid = ba1.areaid
           and a1.prodid = prod.prodid
           and plancustomertype = 1
           and ba1.grade = 3
            and ba1.areaid in
               (select areaid
                  from businessarea
                 start with areaid = nvl(v_areaid, ba1.areaid)
                connect by parentareaid = prior areaid)
         and to_char(a1.auditdate, 'yyyy') =
                 nvl(to_char(to_date(months,'yyyy-MM'),'yyyy'), to_char(a1.auditdate, 'yyyy'))
             and to_char(a1.auditdate, 'mm') =
                 nvl(to_char(to_date(months,'yyyy-MM'),'mm'), to_char(a1.auditdate, 'mm'))
          -- and prod.PRODID = nvl(p_prodid, prod.prodid)
           and prod.prodid=nvl(v_prodid,prod.prodid);
      exception
                    when others then
                      v_bmjx:= 0;
       end;
    v_bmjx := v_bmjx + v_adjustqty1;
    --bmpt
    begin
        select nvl(a1.balanceqty,0)
          into v_bmpt
          from ztdomesticdemandaudit a1, businessarea ba1, product prod
         where a1.areaid = ba1.areaid
           and a1.prodid = prod.prodid
           and a1.plancustomertype = 2
           and ba1.grade = 3
            and ba1.areaid in
               (select areaid
                  from businessarea
                 start with areaid = nvl(v_areaid, ba1.areaid)
                connect by parentareaid = prior areaid)
             and to_char(a1.auditdate, 'yyyy') =
                 nvl(to_char(to_date(months,'yyyy-MM'),'yyyy'), to_char(a1.auditdate, 'yyyy'))
             and to_char(a1.auditdate, 'mm') =
                 nvl(to_char(to_date(months,'yyyy-MM'),'mm'), to_char(a1.auditdate, 'mm'))
          -- and prod.PRODID = nvl(p_prodid, prod.prodid)
           and prod.prodid=nvl(v_prodid,prod.prodid);
      exception
                    when others then
                      v_bmpt:= 0;
       end;
    v_bmpt := v_bmpt + v_adjustqty1;
 
    --qyjx
    begin
          select nvl(a1.balanceqty,0)
            into v_qyjx
            from ztdomesticdemandaudit a1, businessarea ba1, product prod
           where a1.areaid = ba1.areaid
             and a1.prodid = prod.prodid
             and a1.plancustomertype = 1
             and ba1.grade = 4
              and ba1.areaid in
                 (select areaid
                    from businessarea
                   start with areaid = nvl(v_areaid, ba1.areaid)
                  connect by parentareaid = prior areaid)
       and to_char(a1.auditdate, 'yyyy') =
                   nvl(to_char(to_date(months,'yyyy-MM'),'yyyy'), to_char(a1.auditdate, 'yyyy'))
               and to_char(a1.auditdate, 'mm') =
                   nvl(to_char(to_date(months,'yyyy-MM'),'mm'), to_char(a1.auditdate, 'mm'))
            -- and prod.PRODID = nvl(p_prodid, prod.prodid)
             and prod.prodid=nvl(v_prodid,prod.prodid);
       exception
                    when others then
                      v_qyjx:= 0;
       end;
    v_qyjx := v_qyjx + v_adjustqty1;
    --qypt
     begin
        select nvl(a1.balanceqty,0)
          into v_qupt
          from ztdomesticdemandaudit a1, businessarea ba1, product prod
         where a1.areaid = ba1.areaid
           and a1.prodid = prod.prodid
           and a1.plancustomertype = 2
           and ba1.grade = 4
              and ba1.areaid in
               (select areaid
                  from businessarea
                 start with areaid = nvl(v_areaid, ba1.areaid)
                connect by parentareaid = prior areaid)
           and to_char(a1.auditdate, 'yyyy') =
                 nvl(to_char(to_date(months,'yyyy-MM'),'yyyy'), to_char(a1.auditdate, 'yyyy'))
             and to_char(a1.auditdate, 'mm') =
                 nvl(to_char(to_date(months,'yyyy-MM'),'mm'), to_char(a1.auditdate, 'mm'))
          -- and prod.PRODID = nvl(p_prodid, prod.prodid)
           and prod.prodid=nvl(v_prodid,prod.prodid);
       exception
                    when others then
                      v_qupt:= 0;
       end;
    v_qupt := v_qupt + v_adjustqty2;
 
    --ywyjx
    begin
          SELECT nvl(b.SALESBALANCEQTY,0)
            into v_ywyjx
         
            FROM ztdomesticdemand       a,
                 ztdomesticdemanddetail b,
                 organization           c,
                 organization           d,
                 product                e,
                 productlang            f,
                 productbrand           g,
                 businessarea           h,
                 orgrelationdetail      i
         
           WHERE 1 = 1
             AND a.ztdomesticdemandid = b.ztdomesticdemandid
             AND a.orgid = c.orgid
             AND a.partnerid = d.orgid
             AND f.languagecode = g.languagecode
             AND e.brandcode = g.brandcode
             AND e.prodid = f.prodid
             AND b.prodid = e.prodid
             AND c.orgid = i.targetorgid
             AND h.areaid = i.areaid
             AND f.languagecode = 'cn'
               
             and i.globalpccode = 11
             and i.sourceorgid = 100
             and a.partnerid = 100
               
             and i.areaid in (select areaid
                                from businessarea
                               start with areaid = nvl(v_areaid, i.areaid)
                              connect by parentareaid = prior areaid)      
               and to_char(a.submitdate, 'yyyy') =
                   nvl(to_char(to_date(months,'yyyy-MM'),'yyyy'), to_char(a.submitdate, 'yyyy'))
               and to_char(a.submitdate, 'mm') =
                   nvl(to_char(to_date(months,'yyyy-MM'),'mm'), to_char(a.submitdate, 'mm'))
                
            -- and b.PRODID = nvl(p_prodid, b.prodid)
              and b.prodid=nvl(v_prodid,b.prodid)
             and c.ztplancustomertype = 1;
       exception
                    when others then
                      v_ywyjx:= 0;
       end;
 
      --
    /*        select  sum(a1.balanceqty) into v_ywyjx from  ztdomesticdemandaudit a1,businessarea ba1,product prod,ztdomesticdemand zc,ztdomesticdemanddetail zcd
     where  a1.areaid=ba1.areaid  and zc.ztdomesticdemandid=zcd.ztdomesticdemandid and a1.prodid=zcd.prodid and a1.prodid=prod.prodid and a1.plancustomertype=1
      and ba1.areaid in
                 (select areaid
                    from businessarea
                   start with areaid = nvl(p_areaid, ba1.areaid)
                  connect by parentareaid = prior areaid)
    and a1.plancustomertype = nvl(p_plancustomertype, a1.plancustomertype)
    and to_char(a1.auditdate, 'yyyy') = nvl(p_year,to_char(a1.auditdate, 'yyyy'))
    and to_char(a1.auditdate, 'mm') =  nvl(p_month,to_char(a1.auditdate, 'mm'))
    and prod.PRODID=nvl(p_prodid,prod.prodid);*/
    v_ywyjx := v_ywyjx + v_adjustqty1;
    --ywypt
     begin
          SELECT nvl(b.SALESBALANCEQTY,0)
            into v_ywypt
         
            FROM ztdomesticdemand       a,
                 ztdomesticdemanddetail b,
                 organization           c,
                 organization           d,
                 product                e,
                 productlang            f,
                 productbrand           g,
                 businessarea           h,
                 orgrelationdetail      i
         
           WHERE 1 = 1
             AND a.ztdomesticdemandid = b.ztdomesticdemandid
             AND a.orgid = c.orgid
             AND a.partnerid = d.orgid
             AND f.languagecode = g.languagecode
             AND e.brandcode = g.brandcode
             AND e.prodid = f.prodid
             AND b.prodid = e.prodid
             AND c.orgid = i.targetorgid
             AND h.areaid = i.areaid
             AND f.languagecode = 'cn'
               
             and i.globalpccode = 11
             and i.sourceorgid = 100
             and a.partnerid = 100
               
               and i.areaid in (select areaid
                                from businessarea
                               start with areaid = nvl(v_areaid, i.areaid)
                              connect by parentareaid = prior areaid)
               and to_char(a.submitdate, 'yyyy') =
                   nvl(to_char(to_date(months,'yyyy-MM'),'yyyy'), to_char(a.submitdate, 'yyyy'))
               and to_char(a.submitdate, 'mm') =
                   nvl(to_char(to_date(months,'yyyy-MM'),'mm'), to_char(a.submitdate, 'mm'))
            -- and b.PRODID = nvl(p_prodid, b.prodid)
              and b.prodid=nvl(v_prodid,b.prodid)
             and c.ztplancustomertype = 2;
      exception
                    when others then
                      v_ywypt:= 0;
       end;
    --
    /*   select  sum(a1.balanceqty) into v_ywypt from  ztdomesticdemandaudit a1,businessarea ba1,product prod,ztdomesticdemand zc,ztdomesticdemanddetail zcd
     where  a1.areaid=ba1.areaid  and zc.ztdomesticdemandid=zcd.ztdomesticdemandid and a1.prodid=zcd.prodid and a1.prodid=prod.prodid and a1.plancustomertype=2
      and ba1.areaid in
                 (select areaid
                    from businessarea
                   start with areaid = nvl(p_areaid, ba1.areaid)
                  connect by parentareaid = prior areaid)
    and a1.plancustomertype = nvl(p_plancustomertype, a1.plancustomertype)
    and to_char(a1.auditdate, 'yyyy') = nvl(p_year,to_char(a1.auditdate, 'yyyy'))
    and to_char(a1.auditdate, 'mm') =  nvl(p_month,to_char(a1.auditdate, 'mm'))
    and prod.PRODID=nvl(p_prodid,prod.prodid);*/
    v_ywypt := v_ywypt + v_adjustqty2;
    --khjx
    begin
        SELECT nvl(b.CUSDEMANDQTY,0)
          into v_khjx
       
          FROM ztdomesticdemand       a,
               ztdomesticdemanddetail b,
               organization           c,
               organization           d,
               product                e,
               productlang            f,
               productbrand           g,
               businessarea           h,
               orgrelationdetail      i
       
         WHERE 1 = 1
           AND a.ztdomesticdemandid = b.ztdomesticdemandid
           AND a.orgid = c.orgid
           AND a.partnerid = d.orgid
           AND f.languagecode = g.languagecode
           AND e.brandcode = g.brandcode
           AND e.prodid = f.prodid
           AND b.prodid = e.prodid
           AND c.orgid = i.targetorgid
           AND h.areaid = i.areaid
           AND f.languagecode = 'cn'
             
           and i.globalpccode = 11
           and i.sourceorgid = 100
           and a.partnerid = 100
             
              and i.areaid in (select areaid
                              from businessarea
                             start with areaid = nvl(v_areaid, i.areaid)
                            connect by parentareaid = prior areaid)
             and to_char(a.submitdate, 'yyyy') =
                 nvl(to_char(to_date(months,'yyyy-MM'),'yyyy'), to_char(a.submitdate, 'yyyy'))
             and to_char(a.submitdate, 'mm') =
                 nvl(to_char(to_date(months,'yyyy-MM'),'mm'), to_char(a.submitdate, 'mm'))
          -- and b.PRODID = nvl(p_prodid, b.prodid)
            and b.prodid=nvl(v_prodid,b.prodid)
           and c.ztplancustomertype = 1;
      exception
                    when others then
                      v_khjx:= 0;
       end;
        --
    /*    select  sum(a1.balanceqty) into v_khjx from  ztdomesticdemandaudit a1,businessarea ba1,product prod,ztdomesticdemand zc,ztdomesticdemanddetail zcd
     where  a1.areaid=ba1.areaid  and zc.ztdomesticdemandid=zcd.ztdomesticdemandid and a1.prodid=zcd.prodid and a1.prodid=prod.prodid and a1.plancustomertype=1
       and ba1.areaid in
                 (select areaid
                    from businessarea
                   start with areaid = nvl(p_areaid, ba1.areaid)
                  connect by parentareaid = prior areaid)
    and a1.plancustomertype = nvl(p_plancustomertype, a1.plancustomertype)
    and to_char(a1.auditdate, 'yyyy') = nvl(p_year,to_char(a1.auditdate, 'yyyy'))
    and to_char(a1.auditdate, 'mm') =  nvl(p_month,to_char(a1.auditdate, 'mm'))
    and prod.PRODID=nvl(p_prodid,prod.prodid);*/
    v_khjx := v_khjx + v_adjustqty1;
    --khpt
    begin
       
        SELECT nvl(b.CUSDEMANDQTY,0)
          into v_khypt
       
          FROM ztdomesticdemand       a,
               ztdomesticdemanddetail b,
               organization           c,
               organization           d,
               product                e,
               productlang            f,
               productbrand           g,
               businessarea           h,
               orgrelationdetail      i
       
         WHERE 1 = 1
           AND a.ztdomesticdemandid = b.ztdomesticdemandid
           AND a.orgid = c.orgid
           AND a.partnerid = d.orgid
           AND f.languagecode = g.languagecode
           AND e.brandcode = g.brandcode
           AND e.prodid = f.prodid
           AND b.prodid = e.prodid
           AND c.orgid = i.targetorgid
           AND h.areaid = i.areaid
           AND f.languagecode = 'cn'
             
           and i.globalpccode = 11
           and i.sourceorgid = 100
           and a.partnerid = 100
             
              and i.areaid in (select areaid
                              from businessarea
                             start with areaid = nvl(v_areaid, i.areaid)
                            connect by parentareaid = prior areaid)
    and to_char(a.submitdate, 'yyyy') =
                 nvl(to_char(to_date(months,'yyyy-MM'),'yyyy'), to_char(a.submitdate, 'yyyy'))
             and to_char(a.submitdate, 'mm') =
                 nvl(to_char(to_date(months,'yyyy-MM'),'mm'), to_char(a.submitdate, 'mm'))
          -- and b.PRODID = nvl(p_prodid, b.prodid)
            and b.prodid=nvl(v_prodid,b.prodid)
           and c.ztplancustomertype = 2;
      exception
                    when others then
                      v_khypt:= 0;
       end;
        --
        /*   select  sum(a1.balanceqty) into v_khypt from  ztdomesticdemandaudit a1,businessarea ba1,product prod,ztdomesticdemand zc,ztdomesticdemanddetail zcd
         where  a1.areaid=ba1.areaid  and zc.ztdomesticdemandid=zcd.ztdomesticdemandid and a1.prodid=zcd.prodid and a1.prodid=prod.prodid and a1.plancustomertype=2
           and ba1.areaid in
                     (select areaid
                        from businessarea
                       start with areaid = nvl(p_areaid, ba1.areaid)
                      connect by parentareaid = prior areaid)
        and a1.plancustomertype = nvl(p_plancustomertype, a1.plancustomertype)
        and to_char(a1.auditdate, 'yyyy') = nvl(p_year,to_char(a1.auditdate, 'yyyy'))
        and to_char(a1.auditdate, 'mm') =  nvl(p_month,to_char(a1.auditdate, 'mm'))
    and prod.PRODID=nvl(p_prodid,prod.prodid);*/
    v_khypt := v_khypt + v_adjustqty2;
    --- qty1
    begin
        SELECT nvl(b.DEMANDQTY1,0)
          into v_qty1
       
          FROM ztdomesticdemand       a,
               ztdomesticdemanddetail b,
               organization           c,
               organization           d,
               product                e,
               productlang            f,
               productbrand           g,
               businessarea           h,
               orgrelationdetail      i
       
         WHERE 1 = 1
           AND a.ztdomesticdemandid = b.ztdomesticdemandid
           AND a.orgid = c.orgid
           AND a.partnerid = d.orgid
           AND f.languagecode = g.languagecode
           AND e.brandcode = g.brandcode
           AND e.prodid = f.prodid
           AND b.prodid = e.prodid
           AND c.orgid = i.targetorgid
           AND h.areaid = i.areaid
           AND f.languagecode = 'cn'
             
              -- and i.globalpccode = 11
           and i.sourceorgid = 100
           and a.partnerid = 100
             and i.areaid in (select areaid
                              from businessarea
                             start with areaid = nvl(v_areaid, i.areaid)
                            connect by parentareaid = prior areaid)
    and to_char(a.submitdate, 'yyyy') =
                 nvl(to_char(to_date(months,'yyyy-MM'),'yyyy'), to_char(a.submitdate, 'yyyy'))
             and to_char(a.submitdate, 'mm') =
                 nvl(to_char(to_date(months,'yyyy-MM'),'mm'), to_char(a.submitdate, 'mm'))
          -- and b.PRODID = nvl(p_prodid, b.prodid)
            and b.prodid=nvl(v_prodid,b.prodid);
       exception
                    when others then
                      v_qty1:= 0;
       end;
        --
    /*  select sum(zcd.DEMANDQTY1) into v_qty1 from  ztdomesticdemandaudit a1,businessarea ba1,product prod,ztdomesticdemand zc,ztdomesticdemanddetail zcd
     where  a1.areaid=ba1.areaid  and zc.ztdomesticdemandid=zcd.ztdomesticdemandid and a1.prodid=zcd.prodid and a1.prodid=prod.prodid
       and ba1.areaid in
                 (select areaid
                    from businessarea
                   start with areaid = nvl(p_areaid, ba1.areaid)
                  connect by parentareaid = prior areaid)
    and a1.plancustomertype = nvl(p_plancustomertype, a1.plancustomertype)
    and to_char(a1.auditdate, 'yyyy') = nvl(p_year,to_char(a1.auditdate, 'yyyy'))
    and to_char(a1.auditdate, 'mm') =  nvl(p_month,to_char(a1.auditdate, 'mm'))
    and prod.PRODID=nvl(p_prodid,prod.prodid);*/
    --- qty2
   begin
        SELECT  nvl(b.DEMANDQTY2,0)
          into v_qty2
       
          FROM ztdomesticdemand       a,
               ztdomesticdemanddetail b,
               organization           c,
               organization           d,
               product                e,
               productlang            f,
               productbrand           g,
               businessarea           h,
               orgrelationdetail      i
       
         WHERE 1 = 1
           AND a.ztdomesticdemandid = b.ztdomesticdemandid
           AND a.orgid = c.orgid
           AND a.partnerid = d.orgid
           AND f.languagecode = g.languagecode
           AND e.brandcode = g.brandcode
           AND e.prodid = f.prodid
           AND b.prodid = e.prodid
           AND c.orgid = i.targetorgid
           AND h.areaid = i.areaid
           AND f.languagecode = 'cn'
             
              -- and i.globalpccode = 11
           and i.sourceorgid = 100
           and a.partnerid = 100
             
             and i.areaid in (select areaid
                              from businessarea
                             start with areaid = nvl(v_areaid, i.areaid)
                            connect by parentareaid = prior areaid)
    and to_char(a.submitdate, 'yyyy') =
                 nvl(to_char(to_date(months,'yyyy-MM'),'yyyy'), to_char(a.submitdate, 'yyyy'))
             and to_char(a.submitdate, 'mm') =
                 nvl(to_char(to_date(months,'yyyy-MM'),'mm'), to_char(a.submitdate, 'mm'))
          -- and b.PRODID = nvl(p_prodid, b.prodid)
            and b.prodid=nvl(v_prodid,b.prodid);
      exception
                    when others then
                      v_qty2:= 0;
       end;
     
        --
    /*  select sum(zcd.DEMANDQTY2) into v_qty2 from  ztdomesticdemandaudit a1,businessarea ba1,product prod,ztdomesticdemand zc,ztdomesticdemanddetail zcd
     where  a1.areaid=ba1.areaid  and zc.ztdomesticdemandid=zcd.ztdomesticdemandid and a1.prodid=zcd.prodid and a1.prodid=prod.prodid
       and ba1.areaid in
                 (select areaid
                    from businessarea
                   start with areaid = nvl(p_areaid, ba1.areaid)
                  connect by parentareaid = prior areaid)
    and a1.plancustomertype = nvl(p_plancustomertype, a1.plancustomertype)
    and to_char(a1.auditdate, 'yyyy') = nvl(p_year,to_char(a1.auditdate, 'yyyy'))
    and to_char(a1.auditdate, 'mm') =  nvl(p_month,to_char(a1.auditdate, 'mm'))
    and prod.PRODID=nvl(p_prodid,prod.prodid);*/
    --- qty3
  begin
          SELECT  nvl(b.DEMANDQTY3,0)
            into v_qty3
         
            FROM ztdomesticdemand       a,
                 ztdomesticdemanddetail b,
                 organization           c,
                 organization           d,
                 product                e,
                 productlang            f,
                 productbrand           g,
                 businessarea           h,
                 orgrelationdetail      i
         
           WHERE 1 = 1
             AND a.ztdomesticdemandid = b.ztdomesticdemandid
             AND a.orgid = c.orgid
             AND a.partnerid = d.orgid
             AND f.languagecode = g.languagecode
             AND e.brandcode = g.brandcode
             AND e.prodid = f.prodid
             AND b.prodid = e.prodid
             AND c.orgid = i.targetorgid
             AND h.areaid = i.areaid
             AND f.languagecode = 'cn'
               
                -- and i.globalpccode = 11
             and i.sourceorgid = 100
             and a.partnerid = 100
               
                and i.areaid in (select areaid
                                from businessarea
                               start with areaid = nvl(v_areaid, i.areaid)
                              connect by parentareaid = prior areaid)
      and to_char(a.submitdate, 'yyyy') =
                   nvl(to_char(to_date(months,'yyyy-MM'),'yyyy'), to_char(a.submitdate, 'yyyy'))
               and to_char(a.submitdate, 'mm') =
                   nvl(to_char(to_date(months,'yyyy-MM'),'mm'), to_char(a.submitdate, 'mm'))
            -- and b.PRODID = nvl(p_prodid, b.prodid)
              and b.prodid=nvl(v_prodid,b.prodid);
       exception
                    when others then
                      v_qty3:= 0;
       end;
       
    --
    /*    select sum(zcd.DEMANDQTY3) into v_qty3 from  ztdomesticdemandaudit a1,businessarea ba1,product prod,ztdomesticdemand zc,ztdomesticdemanddetail zcd
     where  a1.areaid=ba1.areaid  and zc.ztdomesticdemandid=zcd.ztdomesticdemandid and a1.prodid=zcd.prodid and a1.prodid=prod.prodid
       and ba1.areaid in
                 (select areaid
                    from businessarea
                   start with areaid = nvl(p_areaid, ba1.areaid)
                  connect by parentareaid = prior areaid)
    and a1.plancustomertype = nvl(p_plancustomertype, a1.plancustomertype)
    and to_char(a1.auditdate, 'yyyy') = nvl(p_year,to_char(a1.auditdate, 'yyyy'))
    and to_char(a1.auditdate, 'mm') =  nvl(p_month,to_char(a1.auditdate, 'mm'))
    and prod.PRODID=nvl(p_prodid,prod.prodid);*/
 
    v_row_ztdomesticdemandaudit := ROW_ZTDOMESTICDEMANDAUDIT(months,
v_xilie, v_pinpai,v_jihuapinpai,v_prodcode,v_prodname,v_gbjx,v_gbpt,v_bmjx,
v_bmpt, v_qyjx,v_qupt, v_ywyjx, v_ywypt, v_khjx,v_khypt,v_qty1,v_qty2,
v_qty3);
    pipe row(v_row_ztdomesticdemandaudit);
 
  end loop;

  commit;
  return;
exception
  when others then
    v_mess := sqlerrm;
end F_TEST;


2，通过pipe管道输出到自己新建的type中，type中可以灵活定义输出的列
--type row

CREATE OR REPLACE TYPE "ROW_ZTDOMESTICDEMANDAUDIT" as object
(
  months    varchar2(32),
  v_xilie    varchar2(128),
  v_pinpai   varchar2(128),
  v_jihuapinpai       varchar2(128),
  v_prodcode varchar2(32),
  v_prodname   varchar2(512),
  v_gbjx      number(16),
  v_gbpt       number(16),
  v_bmjx      number(16),
  v_bmpt       number(16),
  v_qyjx      number(16),
  v_qupt       number(16),
  v_ywyjx      number(16),
  v_ywypt       number(16),
  v_khjx      number(16),
  v_khypt       number(16),

  v_qty1 number(16),
  v_qty2 number(16),
  v_qty3 number(16)

);

type table
CREATE OR REPLACE TYPE "TABLE_ZTDOMESTICDEMANDAUDIT" as table of ROW_ZTDOMESTICDEMANDAUDIT;


可以直接组合几个表中的数据，然后简单查询就ok了
select count(1) from table(F_ZTDOMESTICDEMANDAUDITS(null  ,null  ,null  ,null  ))

查询到的数据可以直接放到list中，显示或导出。