钱少少
- 浏览: 161075 次
- 性别:
- 来自: 上海
-
文章分类
最新评论
-
钱少少：
programming 写道和maven相比没优势
ivy只是 ...
ant+ivy管理项目 -
programming：
和maven相比没优势
ant+ivy管理项目 -
钱少少：
kingsfighter 写道和maven相比，有什么优势？
...
ant+ivy管理项目 -
kingsfighter：
和maven相比，有什么优势？
ant+ivy管理项目 -
zhijie_zhang：
搭建java-web框架（一）
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中，显示或导出。
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中，显示或导出。
分享到：
发表评论
相关推荐
内有文件： Oracle PL-SQL入门之慨述.doc Oracle PL-SQL语言基础.doc ORACLE 数据库开发经验总结.doc Oracle9i PL-SQL编程的经验小结.doc ORACLE函数大全.doc Oracle中使用PL-SQL操作...PL-SQL单行函数和组函数详解.doc
本⽂⽬录： 1. PL/SQL 程序设计简介 2. PL/SQL块结构和组成元素 3. PL/SQL流程控制语句 4. 把游标说透 5. 异常错误处理 6. 把过程与函数说透 7. 程序包的创建与应⽤ 8. 把触发器说透
PL-SQL_存储过程与函数.doc 介绍存储过程和函数，技术爱好者必看。
学习PL-SQL的非常好，非常简洁的一本书籍。 第一章 PL/SQL 程序设计简介 第二章 PL/SQL块结构和组成元素 第三章 PL/SQL流程控制语句 第四章 游标的使用 第五章 异常错误处理 第六章 存储函数和过程 第七章 包的创建...
PL/SQL 常用函数,如日期格式转换,数字,常用数学行数,字符串截取,定位,等函数
Oracle PL-SQL语言初级教程,常见的plsql中的单组,多组函数,过程以函数,调试及拓展
oracle PL_SQL编程 创建存储过程、函数、触发器等
PL/SQL 的函数与存储过程练习 PL/SQL 的函数与存储过程练习
12oracle的PL/SQL编程-函数.包.触发器 PPT 12oracle的PL/SQL编程-函数.包.触发器 PPT
全面对比T-SQL与PL/SQL 查询的函数，语句的写法，变量声明、赋值与引用，游标，触发器，过程等。值得一看。
Oracle 函数大全(字符串函数，数学函数，日期函数，逻辑运算函数，其他函数)
主要收集了plｓｑｌ一些常用的函数介绍，有助与日常开发使用
第二章 基本的SQL SELECT语句 1 第三章 限制行和对数据...第十五章 PL/SQL简介 29 基本结构 30 声明部分 30 可执行部分 31 执行控制 31 循环语句 32 第十六章 游标和异常 34 游标 34 异常处理 36
北大青鸟教材pl/sql与函数上课内容，内容精辟，全面含有代码
GKD-Base PL_SQL存储函数实现的关键技术研究.pdf
Oracle_PL(SQL)单行函数和组函数及使用
这一部分概述了 Transact-SQL 和 PL/SQL 语言语法之间的相同点...8. 用 Transact-SQL 过程代替 PL/SQL 过程、函数和包。 9. 把 PL/SQL 触发器转换为 Transact-SQL 触发器。 10. 使用 SET SHOWPLAN 语句，优化查询性能。
1、PL/SQL简介 2、PL/SQL基础 3、记录和表 4、在PL/SQL中使用SQL 5、内置SQL函数 6、游标 7、过程和函数 ...