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中，显示或导出。