--新建一个临时空间
CREATE GLOBAL TEMPORARY TABLE TAB_STORAGE_RECORD_TEMP(
recorddate date ,
showname varchar2(100),
showid varchar2(30),
money number(12,2),
number_t number(14)
)
ON COMMIT PRESERVE ROWS;
create or replace package productInStore3
as type myrctype is ref cursor;
--根据条件统计公司指定年份每月的库存情况
function getProductInStoreForYearByCom(companyIds varchar2, yeart varchar2 , montht varchar2 ,comflag varchar2)return myrctype ;
end productInStore3;
create or replace package productInStore
as type myrctype is ref cursor;
--根据条件统计公司指定月份每天的库存情况-函数定义
function getProductInStoreForMonthByCom(companyIds varchar2, yeart varchar2 , montht varchar2 , datet varchar2,comflag varchar2)return myrctype ;
end productInStore;
----根据条件统计公司指定年份每月的库存情况-函数
create or replace package body productInStore3 as
function getProductInStoreForYearByCom(companyIds varchar2,
yeart varchar2,
montht varchar2,
comflag varchar2) return myrctype is
rc myrctype;
rccc myrctype;
sqlstr varchar2(500);
TYPE ProIdrec IS RECORD(
articleid tab_storage_record.articleid%TYPE);
myProIdrec ProIdrec;
type ReportBean IS RECORD(
money number(12, 2),
lastnumber number(12, 2),
recorddate date,
showname varchar2(30) := '所选公司单位',
showid varchar2(30) := '0');
myReportBean ReportBean;
myReportBean2 ReportBean;
TYPE TAB_ReportBean IS TABLE OF ReportBean INDEX BY binary_integer;
tabRerortBean TAB_ReportBean;
j BINARY_INTEGER := 1;
TYPE EMP_TAB_ProIdrec IS TABLE OF ProIdrec INDEX BY binary_integer;
vempProId EMP_TAB_ProIdrec;
V2 VARCHAR2(100) := ',' || companyIds || ',';
selectProIdSor myrctype;
productid varchar2(2000 CHAR) := '';
sqlrecord varchar2(2000) := '';
sqlrecord2 varchar2(3000) := '';
TYPE myrctypecur IS REF CURSOR;
mycur1 myrctypecur;
mycur2 myrctypecur;
lsdate2 date;
allsize number(8) := 1;
monthtInts number(3) := montht;
dateStr varchar2(12) := '';
tempSize number(8);
productIdSql varchar2(800) := '';
begin
FOR monthInt in 1 .. monthtInts loop
---检索出当天买的产品ID
productid := '';
productIdSql := ' select distinct t.articleid
from tab_storage_record t, b_companyinfo tt
where t.companyid = tt.unit_id and tt.unit_id in (' ||
companyIds ||
') and extract(year from t.recorddate) = ''' ||
yeart ||
'''
and extract(month from t.recorddate) = ''' ||
monthInt || ''' ';
open selectProIdSor for productIdSql;
fetch selectProIdSor
into myProIdrec;
j := 1;
while selectProIdSor%found loop
vempProId(j) := myProIdrec;
fetch selectProIdSor
into myProIdrec;
j := j + 1;
end loop;
FOR i IN 1 .. j - 1 LOOP
IF i = 1 then
productid := ' (' || vempProId(i).articleid;
elsif mod(i, 901) = 0 then
productid := productid || ') and tt.articleid not in (' ||
vempProId(i).articleid;
else
productid := productid || ',' || vempProId(i).articleid;
end if;
if i = j - 1 then
productid := productid || ')';
end if;
END LOOP;
DBMS_OUTPUT.PUT_line(' LENGTH(productid)= ' || LENGTH(productid) || ' ');
--检索 当天买的产品 cprice成本价格 lastnumber 最后的库存数量
sqlrecord := 'select nvl(sum(t.lastnumber*t.cprice),0.00),nvl(sum(t.lastnumber),0.00),trunc(t.recorddate,''MM'') ';
if comflag = '2' then
sqlrecord := sqlrecord || ' ,t.companyname,t.companyid ';
end if;
sqlrecord := sqlrecord ||
' from tab_storage_record t where t.rid=(SELECT max(tt.rid) from tab_storage_record tt ';
sqlrecord := sqlrecord ||
' where t.articleid=tt.articleid and trunc(t.recorddate,''MM'')=trunc(tt.recorddate,''MM'') ';
if length(companyIds) > 0 then
sqlrecord := sqlrecord || ' and tt.companyid in ' || '(' ||
companyIds || ')';
end if;
if length(yeart) > 0 then
sqlrecord := sqlrecord ||
' and extract(year from t.recorddate)= ''' || yeart ||
''' and extract(month from t.recorddate)=''' ||
monthInt || ''' ';
end if;
sqlrecord := sqlrecord ||
'group by trunc(tt.recorddate,''MM'')) group by trunc(t.recorddate,''MM'')';
if comflag = '2' then
sqlrecord := sqlrecord || ' ,t.companyname,t.companyid ';
end if;
sqlrecord := sqlrecord || ' order by ';
if comflag = '2' then
sqlrecord := sqlrecord || ' t.companyid asc, ';
end if;
sqlrecord := sqlrecord || ' trunc(t.recorddate,''MM'') asc ';
open mycur1 for sqlrecord;
fetch mycur1
into myReportBean;
while mycur1%found loop
tabRerortBean(allsize) := myReportBean;
allsize := allsize + 1;
fetch mycur1
into myReportBean;
end loop;
--检索 非当天买的产品 cprice成本价格 lastnumber 最后的库存数量
sqlrecord2 := ' select nvl(sum(t.lastnumber*t.cprice),00),nvl(sum(t.lastnumber),0.00),trunc(sysdate,''MM'') ';
if comflag = '2' then
sqlrecord2 := sqlrecord2 || ' ,t.companyname,t.companyid ';
end if;
sqlrecord2 := sqlrecord2 ||
' from tab_storage_record t where t.rid=(SELECT max(tt.rid) from tab_storage_record tt where t.articleid=tt.articleid ';
if length(productid) > 0 then
sqlrecord2 := sqlrecord2 || ' and tt.articleid not in ' ||
productid;
end if;
if length(companyIds) > 0 then
sqlrecord2 := sqlrecord2 || ' and tt.companyid in ' || ' (' ||
companyIds || ')';
end if;
if length(yeart) > 0 then
sqlrecord2 := sqlrecord2 ||
' and (extract(year from tt.recorddate)<''' || yeart ||
''' ' || ' or (extract(year from tt.recorddate)=''' ||
yeart ||
''' and extract(month from tt.recorddate)<''' ||
monthInt || '''))';
end if;
sqlrecord2 := sqlrecord2 || ') ';
if comflag = '2' then
sqlrecord2 := sqlrecord2 ||
' group by t.companyname,t.companyid order by t.companyid asc ';
end if;
dateStr := yeart;
if monthInt < 10 then
dateStr := dateStr || '0' || monthInt;
else
dateStr := dateStr || monthInt;
end if;
dateStr := dateStr || '01';
lsdate2 := to_date(dateStr, 'yyyymmdd');
open mycur2 for sqlrecord2;
fetch mycur2
into myReportBean2;
while mycur2%found loop
myReportBean2.recorddate := lsdate2;
tempSize := allsize;
for k in 1 .. tempSize - 1 loop
if tabRerortBean(k)
.showid = myReportBean2.showid and tabRerortBean(k)
.recorddate = myReportBean2.recorddate then
tabRerortBean(k).lastnumber := tabRerortBean(k)
.lastnumber +
myReportBean2.lastnumber;
tabRerortBean(k).money := tabRerortBean(k)
.money + myReportBean2.money;
exit;
end if;
if k = tempSize - 1 then
tabRerortBean(allsize) := myReportBean2;
allsize := allsize + 1;
end if;
end loop;
if allsize = 1 then
tabRerortBean(1) := myReportBean2;
allsize := allsize + 1;
end if;
fetch mycur2
into myReportBean2;
end loop;
close mycur2;
close mycur1;
close selectProIdSor;
end loop;
DBMS_OUTPUT.PUT_line('最后 allsize = ' || allsize);
for mm in 1 .. allsize - 1 loop
DBMS_OUTPUT.PUT_line(mm || ' ' || tabRerortBean(mm)
.money || ' ' || tabRerortBean(mm)
.recorddate || ' ' || tabRerortBean(mm)
.lastnumber || ' ' || tabRerortBean(mm)
.showname);
insert into TAB_STORAGE_RECORD_TEMP
(recorddate, showname, showid, money, number_t)
values
(tabRerortBean(mm).recorddate,
'' || tabRerortBean(mm).showname,
'' || tabRerortBean(mm).showid,
tabRerortBean(mm).money,
tabRerortBean(mm).lastnumber);
end loop;
COMMIT;
open rc for
select nvl(money, 0.00) money,
nvl(number_t, 0.00) number_t,
trunc(recorddate, 'dd') recorddate,
showname,
showid
from TAB_STORAGE_RECORD_TEMP;
return rc;
end getProductInStoreForYearByCom;
end productInStore3;
--根据条件统计公司指定月份每天的库存情况-函数
create or replace package body productInStore as
function getProductInStoreForMonthByCom(companyIds varchar2,
yeart varchar2,
montht varchar2,
datet varchar2,
comflag varchar2)
return myrctype is
rc myrctype;
rccc myrctype;
sqlstr varchar2(500);
TYPE ProIdrec IS RECORD(
articleid tab_storage_record.articleid%TYPE);
myProIdrec ProIdrec;
type ReportBean IS RECORD(
money number(12, 2),
lastnumber number(12, 2),
recorddate date,
showname varchar2(30) := '所选公司单位',
showid varchar2(30) := '0');
myReportBean ReportBean;
myReportBean2 ReportBean;
TYPE TAB_ReportBean IS TABLE OF ReportBean INDEX BY binary_integer;
tabRerortBean TAB_ReportBean;
j BINARY_INTEGER := 1;
TYPE EMP_TAB_ProIdrec IS TABLE OF ProIdrec INDEX BY binary_integer;
vempProId EMP_TAB_ProIdrec;
V2 VARCHAR2(100) := ',' || companyIds || ',';
selectProIdSor myrctype;
--cursor selectProIdSor is
productid varchar2(2000 CHAR) := '';
sqlrecord varchar2(2000) := '';
sqlrecord2 varchar2(3000) := '';
TYPE myrctypecur IS REF CURSOR;
mycur1 myrctypecur;
mycur2 myrctypecur;
lsdate2 date;
allsize number(8) := 1;
monthtInts number(3) := montht;
datetInts number(3) := datet;
dateStr varchar2(12) := '';
tempSize number(8);
productIdSql varchar2(800) := '';
begin
FOR dayInt in 1 .. datetInts loop
---检索出当天买的产品ID
productid := '';
productIdSql := ' select distinct t.articleid
from tab_storage_record t, b_companyinfo tt
where t.companyid = tt.unit_id and tt.unit_id in (' ||
companyIds ||
') and extract(year from t.recorddate) = ''' ||
yeart ||
'''
and extract(month from t.recorddate) = ''' ||
montht ||
''' and extract(day from t.recorddate) =''' ||
dayInt || ''' ';
open selectProIdSor for productIdSql;
fetch selectProIdSor
into myProIdrec;
j := 1;
while selectProIdSor%found loop
vempProId(j) := myProIdrec;
fetch selectProIdSor
into myProIdrec;
j := j + 1;
end loop;
FOR i IN 1 .. j - 1 LOOP
IF i = 1 then
productid := ' (' || vempProId(i).articleid;
elsif mod(i, 901) = 0 then
productid := productid || ') and tt.articleid not in (' ||
vempProId(i).articleid;
else
productid := productid || ',' || vempProId(i).articleid;
end if;
if i = j - 1 then
productid := productid || ')';
end if;
END LOOP;
DBMS_OUTPUT.PUT_line(' productid= ' || productid || ' ');
DBMS_OUTPUT.PUT_line(' LENGTH(productid)= ' || LENGTH(productid) || ' ');
--检索 当天买的产品 cprice成本价格 lastnumber 最后的库存数量
sqlrecord := 'select nvl(sum(t.lastnumber*t.cprice),0.00),nvl(sum(t.lastnumber),0.00),trunc(t.recorddate,''dd'') ';
if comflag = '2' then
sqlrecord := sqlrecord || ' ,t.companyname,t.companyid ';
end if;
sqlrecord := sqlrecord ||
' from tab_storage_record t where t.rid=(SELECT max(tt.rid) from tab_storage_record tt ';
sqlrecord := sqlrecord ||
' where t.articleid=tt.articleid and trunc(t.recorddate,''dd'')=trunc(tt.recorddate,''dd'') ';
if length(companyIds) > 0 then
sqlrecord := sqlrecord || ' and tt.companyid in ' || '(' ||
companyIds || ')';
end if;
if length(yeart) > 0 then
sqlrecord := sqlrecord ||
' and extract(year from t.recorddate)= ''' || yeart ||
''' and extract(month from t.recorddate)=''' ||
montht || ''' and extract(day from t.recorddate)=''' ||
dayInt || '''';
end if;
sqlrecord := sqlrecord ||
'group by trunc(tt.recorddate,''dd'')) group by trunc(t.recorddate,''dd'')';
if comflag = '2' then
sqlrecord := sqlrecord || ' ,t.companyname,t.companyid ';
end if;
sqlrecord := sqlrecord || ' order by ';
if comflag = '2' then
sqlrecord := sqlrecord || ' t.companyid asc, ';
end if;
sqlrecord := sqlrecord || ' trunc(t.recorddate,''dd'') asc ';
open mycur1 for sqlrecord;
--open mycur2 for sqlrecord ;
fetch mycur1
into myReportBean;
while mycur1%found loop
tabRerortBean(allsize) := myReportBean;
allsize := allsize + 1;
fetch mycur1
into myReportBean;
end loop;
--检索 非当天买的产品 cprice成本价格 lastnumber 最后的库存数量
sqlrecord2 := ' select nvl(sum(t.lastnumber*t.cprice),00),nvl(sum(t.lastnumber),0.00),trunc(sysdate,''dd'') ';
if comflag = '2' then
sqlrecord2 := sqlrecord2 || ' ,t.companyname,t.companyid ';
end if;
sqlrecord2 := sqlrecord2 ||
' from tab_storage_record t where t.rid=(SELECT max(tt.rid) from tab_storage_record tt where t.articleid=tt.articleid ';
if length(productid) > 0 then
sqlrecord2 := sqlrecord2 || ' and tt.articleid not in ' ||
productid;
end if;
if length(companyIds) > 0 then
sqlrecord2 := sqlrecord2 || ' and tt.companyid in ' || ' (' ||
companyIds || ')';
end if;
sqlrecord2 := sqlrecord2 ||
' and (extract(year from tt.recorddate)<''' || yeart ||
''' ' || ' or (extract(year from tt.recorddate)=''' ||
yeart || ''' ' ||
' and extract(month from tt.recorddate)<''' || montht ||
''') ' || ' or (extract(year from tt.recorddate)=''' ||
yeart || ''' ' ||
' and extract(month from tt.recorddate)=''' || montht ||
''' ' || ' and extract(day from tt.recorddate)<''' ||
dayInt || '''))';
sqlrecord2 := sqlrecord2 || ') ';
if comflag = '2' then
sqlrecord2 := sqlrecord2 ||
' group by t.companyname,t.companyid order by t.companyid asc ';
end if;
dateStr := yeart;
if monthtInts < 10 then
dateStr := dateStr || '0' || monthtInts;
else
dateStr := dateStr || monthtInts;
end if;
if dayInt < 10 then
dateStr := dateStr || '0' || dayInt;
else
dateStr := dateStr || dayInt;
end if;
lsdate2 := to_date(dateStr, 'yyyymmdd');
open mycur2 for sqlrecord2;
fetch mycur2
into myReportBean2;
while mycur2%found loop
myReportBean2.recorddate := lsdate2;
--DBMS_OUTPUT.PUT_line('allsize='||allsize);
tempSize := allsize;
-- DBMS_OUTPUT.PUT_line(' 外面 tempSize='||tempSize);
for k in 1 .. tempSize - 1 loop
-- DBMS_OUTPUT.PUT_line(m || '------ ' || tabRerortBean(m).lastnumber || ' ' || tabRerortBean(m).showid || ' ' || tabRerortBean(m).showname);
--DBMS_OUTPUT.PUT_line(' --- myReportBean2.showid='||myReportBean2.showid||' myReportBean2.recorddate='||myReportBean2.recorddate);
if tabRerortBean(k)
.showid = myReportBean2.showid and tabRerortBean(k)
.recorddate = myReportBean2.recorddate then
tabRerortBean(k).lastnumber := tabRerortBean(k)
.lastnumber +
myReportBean2.lastnumber;
tabRerortBean(k).money := tabRerortBean(k)
.money + myReportBean2.money;
--allsize:=allsize+1;
--DBMS_OUTPUT.PUT_line(' exit 推出 tempSize='||tempSize||' allsize='||allsize);
exit;
end if;
--DBMS_OUTPUT.PUT_line(' 里面 tempSize='||tempSize);
---DBMS_OUTPUT.PUT_line(' 里面 k='||k||' tempSize ='||tempSize);
if k = tempSize - 1 then
---DBMS_OUTPUT.PUT_line(' 尾部添加里面 k='||k||' tempSize ='||tempSize);
tabRerortBean(allsize) := myReportBean2;
allsize := allsize + 1;
end if;
end loop;
if allsize = 1 then
tabRerortBean(1) := myReportBean2;
allsize := allsize + 1;
--DBMS_OUTPUT.PUT_line(' 里面 添加第一个 allsize='||allsize);
end if;
fetch mycur2
into myReportBean2;
end loop;
close mycur2;
close mycur1;
close selectProIdSor;
end loop;
DBMS_OUTPUT.PUT_line('最后 allsize = ' || allsize);
for mm in 1 .. allsize - 1 loop
DBMS_OUTPUT.PUT_line(mm || ' ' || tabRerortBean(mm)
.money || ' ' || tabRerortBean(mm)
.recorddate || ' ' || tabRerortBean(mm)
.lastnumber || ' ' || tabRerortBean(mm)
.showname);
insert into TAB_STORAGE_RECORD_TEMP
(recorddate, showname, showid, money, number_t)
values
(tabRerortBean(mm).recorddate,
'' || tabRerortBean(mm).showname,
'' || tabRerortBean(mm).showid,
tabRerortBean(mm).money,
tabRerortBean(mm).lastnumber);
end loop;
COMMIT;
open rc for
select nvl(money, 0.00) money,
nvl(number_t, 0.00) number_t,
trunc(recorddate, 'dd') recorddate,
showname,
showid
from TAB_STORAGE_RECORD_TEMP;
return rc;
end getProductInStoreForMonthByCom;
end productInStore;