一个Oracle 报表优化运行解决思路
原来有1报表，统计库存，时间比较长
---------------------------------------------------------------------------------------------------------------------------------
create or replace procedure PRO_TJ_STOCK(TRADECODE in varchar2,
User_ID in varchar2) is
row_count integer;
row_count1 integer;
row_count2 integer;
--现已不使用 2009.4.10
cursor cur_stock is
select distinct COP_G_NO,
biz_type,
IE_FLAG,
c.hs_code as HSCODE,
c.G_NAME,
sum(a.qty) as qty,
(select name
from base_dir
where code = biz_type
and type = 'Biz_Type') as BIZ_TYPE_NAME,
(select name
from base_dir
where code = IE_FLAG
and type = 'IE_Mark') as IE_FLAG_NAME
from gj_lineone_list a
inner join sys_merger_relation c on c.part_no = a.cop_g_no
and c.trade_code = TRADECODE
inner join gj_lineone_head b on a.gatejob_no = b.gatejob_no
inner join gj_stock st on st.cop_g_no = a.cop_g_no
and st.trade_code = TRADECODE
where trade_code = TRADECODE
and ((IE_FLAG = '1' and status = 'C06') or
(IE_FLAG = '2' and
(biz_type = 'B' or biz_type = 'C' or biz_type = 'D') and
status in ('K01', 'K02')) or
(IE_FLAG = '2' and biz_type = 'A' and
status in ('C07', 'C06', 'K01', 'K02')) or
(IE_FLAG is null and status = 'C06'))
and st.qty > 0
group by biz_type, IE_FLAG, COP_G_NO, c.hs_code, c.g_name;
CurRow cur_stock%rowtype;
/* FROM 2007-9-4 lily */
--中心内互转 （出）转出（发货）单位
cursor cur_stock1 is
select distinct a.cop_out_no,
biz_type,
--IE_FLAG,
c.hs_code as HSCODE,
c.G_NAME,
sum(a.qty) as qty,
(select name
from base_dir
where code = biz_type
and type = 'Biz_Type') as BIZ_TYPE_NAME,
--(select name from base_dir where code = IE_FLAG and type='IE_Mark') as IE_FLAG_NAME
'' as IE_FLAG_NAME
from gj_center_list a
inner join gj_center_head b on a.gatejob_no = b.gatejob_no
inner join sys_merger_relation c on c.part_no = a.cop_out_no
and c.trade_code = b.ownername
where b.ownername = TRADECODE
and b.status = 'C06'
group by a.cop_out_no,
biz_type,
--IE_FLAG,
c.hs_code,
c.g_name;
CurRow1 cur_stock1%rowtype;
--中心内互转 （进）转入（申请）单位
cursor cur_stock3 is
select distinct a.cop_in_no,
biz_type,
--IE_FLAG,
c.hs_code as HSCODE,
c.G_NAME,
sum(a.qty) as qty,
(select name
from base_dir
where code = biz_type
and type = 'Biz_Type') as BIZ_TYPE_NAME,
--(select name from base_dir where code = IE_FLAG and type='IE_Mark') as IE_FLAG_NAME
'' as IE_FLAG_NAME
from gj_center_list a
inner join gj_center_head b on a.gatejob_no = b.gatejob_no
inner join sys_merger_relation c on c.part_no = a.cop_in_no
and c.trade_code = b.trade_code
where b.trade_code = TRADECODE
and b.status = 'C06'
group by a.cop_in_no,
biz_type,
--IE_FLAG,
c.hs_code,
c.g_name;
CurRow3 cur_stock3%rowtype;
/* TO 2007-9-4 lily */
-- -- 简单加工
cursor cur_stock2 is
select distinct COP_G_NO,
c.hs_code as HSCODE,
c.G_NAME,
sum(a.num) as qty,
a.io_mark
from simpro_info a
inner join sys_merger_relation c on c.part_no = a.cop_g_no
and c.trade_code = TRADECODE
inner join simpro_head b on a.gatejob_no = b.gatejob_no
where b.status = 'C06' and b.trade_code=TRADECODE
group by COP_G_NO, c.hs_code, c.G_NAME, a.io_mark;
CurRow2 cur_stock2%rowtype;
begin
row_count := 0;
delete from TEMP_STOCK where UserID = User_ID;
--commit;
open Cur_Stock;
loop
fetch Cur_Stock
into CurRow;
exit when Cur_Stock%notfound;
--判断有没有重复的料号
select count(*)
into row_count
from TEMP_STOCK
where UserID = User_ID
and COP_G_NO = CurRow.COP_G_NO;
--一线进区业务
if CurRow.biz_type = 'A' and CurRow.IE_FLAG = '1' then
if row_count = 0 then
insert into TEMP_STOCK
(COP_G_NO, ONELINE_IN_COUNT, USERID, HSCODE, G_NAME)
values
(CurRow.COP_G_NO,
CurRow.qty,
User_ID,
CurRow.HSCODE,
CurRow.G_NAME);
end if;
if row_count = 1 then
update temp_stock
set ONELINE_IN_COUNT = CurRow.qty
where USERID = User_ID
and COP_G_NO = CurRow.COP_G_NO;
end if;
end if;
--一线出区业务
if CurRow.biz_type = 'A' and CurRow.IE_FLAG = '2' then
if row_count = 0 then
insert into TEMP_STOCK
(COP_G_NO, ONELINE_OUT_COUNT, Userid, HSCODE, G_NAME)
values
(CurRow.COP_G_NO,
CurRow.qty,
User_ID,
CurRow.HSCODE,
CurRow.G_NAME);
end if;
if row_count = 1 then
update temp_stock
set ONELINE_OUT_COUNT = CurRow.qty
where USERID = User_ID
and COP_G_NO = CurRow.COP_G_NO;
end if;
end if;
--二线进区业务
if CurRow.biz_type = 'B' and CurRow.IE_FLAG = '1' then
if row_count = 0 then
insert into TEMP_STOCK
(COP_G_NO, TWOLINE_IN_COUNT, Userid, HSCODE, G_NAME)
values
(CurRow.COP_G_NO,
CurRow.qty,
User_ID,
CurRow.HSCODE,
CurRow.G_NAME);
end if;
if row_count = 1 then
update temp_stock
set TWOLINE_IN_COUNT = CurRow.qty
where USERID = User_ID
and COP_G_NO = CurRow.COP_G_NO;
end if;
end if;
--二线出区
if CurRow.biz_type = 'B' and CurRow.IE_FLAG = '2' then
if row_count = 0 then
insert into TEMP_STOCK
(COP_G_NO, TWOLINE_OUT_COUNT, Userid, HSCODE, G_NAME)
values
(CurRow.COP_G_NO,
CurRow.qty,
User_ID,
CurRow.HSCODE,
CurRow.G_NAME);
end if;
if row_count = 1 then
update temp_stock
set TWOLINE_OUT_COUNT = CurRow.qty
where USERID = User_ID
and COP_G_NO = CurRow.COP_G_NO;
end if;
end if;
--特殊监管进区业务
if CurRow.biz_type = 'C' and CurRow.IE_FLAG = '1' then
if row_count = 0 then
insert into TEMP_STOCK
(COP_G_NO, SPEC_IN_COUNT, Userid, HSCODE, G_NAME)
values
(CurRow.COP_G_NO,
CurRow.qty,
User_ID,
CurRow.HSCODE,
CurRow.G_NAME);
end if;
if row_count = 1 then
update temp_stock
set SPEC_IN_COUNT = CurRow.qty
where USERID = User_ID
and COP_G_NO = CurRow.COP_G_NO;
end if;
end if;
--特殊监管出区业务
if CurRow.biz_type = 'C' and CurRow.IE_FLAG = '2' then
if row_count = 0 then
insert into TEMP_STOCK
(COP_G_NO, SPEC_OUT_COUNT, Userid, HSCODE, G_NAME)
values
(CurRow.COP_G_NO,
CurRow.qty,
User_ID,
CurRow.HSCODE,
CurRow.G_NAME);
end if;
if row_count = 1 then
update temp_stock
set SPEC_OUT_COUNT = CurRow.qty
where USERID = User_ID
and COP_G_NO = CurRow.COP_G_NO;
end if;
end if;
--分送集报进区业务
if CurRow.biz_type = 'D' and CurRow.IE_FLAG = '1' then
if row_count = 0 then
insert into TEMP_STOCK
(COP_G_NO, Userid, HSCODE, G_NAME, COLL_IN_COUNT)
values
(CurRow.COP_G_NO,
User_ID,
CurRow.HSCODE,
CurRow.G_NAME,
CurRow.qty);
end if;
if row_count = 1 then
update temp_stock
set COLL_IN_COUNT = CurRow.qty
where USERID = User_ID
and COP_G_NO = CurRow.COP_G_NO;
end if;
end if;
--分送集报出区业务
if CurRow.biz_type = 'D' and CurRow.IE_FLAG = '2' then
if row_count = 0 then
insert into TEMP_STOCK
(COP_G_NO, Userid, HSCODE, G_NAME, COLL_OUT_COUNT)
values
(CurRow.COP_G_NO,
User_ID,
CurRow.HSCODE,
CurRow.G_NAME,
CurRow.qty);
end if;
if row_count = 1 then
update temp_stock
set COLL_OUT_COUNT = CurRow.qty
where USERID = User_ID
and COP_G_NO = CurRow.COP_G_NO;
end if;
end if;
commit;
end loop;
close Cur_Stock;
/* 2007-09-04 begin lily */
--中心内互转 （出）发货单位
open Cur_Stock1;
loop
fetch Cur_Stock1
into CurRow1;
exit when Cur_Stock1%notfound;
--判断有没有重复的料号
select count(*)
into row_count1
from TEMP_STOCK
where UserID = User_ID
and COP_G_NO = CurRow1.Cop_Out_No;
--中心内互转 （出）发货单位
--if CurRow1.biz_type='E' then
if row_count1 = 0 then
insert into TEMP_STOCK
(COP_G_NO, Userid, HSCODE, G_NAME, inc_out)
values
(CurRow1.Cop_Out_No,
User_ID,
CurRow1.HSCODE,
CurRow1.G_NAME,
CurRow1.qty);
end if;
if row_count1 = 1 then
update temp_stock
set inc_out = CurRow1.qty
where USERID = User_ID
and COP_G_NO = CurRow1.Cop_Out_No;
end if;
-- end if;
commit;
end loop;
close Cur_Stock1;
--中心内互转 （入）申请单位
open Cur_Stock3;
loop
fetch Cur_Stock3
into CurRow3;
exit when Cur_Stock3%notfound;
--判断有没有重复的料号
select count(*)
into row_count1
from TEMP_STOCK
where UserID = User_ID
and COP_G_NO = CurRow3.Cop_In_No;
--if CurRow1.biz_type='E' then
if row_count1 = 0 then
insert into TEMP_STOCK
(COP_G_NO, Userid, HSCODE, G_NAME, inc_in)
values
(CurRow3.Cop_In_No,
User_ID,
CurRow3.HSCODE,
CurRow3.G_NAME,
CurRow3.qty);
end if;
if row_count1 = 1 then
update temp_stock
set inc_in = CurRow3.qty
where USERID = User_ID
and COP_G_NO = CurRow3.Cop_In_No;
end if;
-- end if;
commit;
end loop;
close Cur_Stock3;
/* 2007-09-04 end lily */
-----简单加工库存查询
open Cur_Stock2;
loop
fetch Cur_Stock2
into CurRow2;
exit when Cur_Stock2%notfound;
--判断有没有重复的料号
select count(*)
into row_count2
from TEMP_STOCK
where UserID = User_ID
and COP_G_NO = CurRow2.COP_G_NO;
if CurRow2.io_mark = '1' then
if row_count2 = 0 then
insert into TEMP_STOCK
(COP_G_NO, Userid, HSCODE, G_NAME, Sip_in)
values
(CurRow2.COP_G_NO,
User_ID,
CurRow2.HSCODE,
CurRow2.G_NAME,
CurRow2.qty);
end if;
if row_count2 = 1 then
update temp_stock
set Sip_in = CurRow2.qty
where USERID = User_ID
and COP_G_NO = CurRow2.COP_G_NO;
end if;
end if;
if CurRow2.io_mark = '2' then
if row_count2 = 0 then
insert into TEMP_STOCK
(COP_G_NO, Userid, HSCODE, G_NAME, Sip_out)
values
(CurRow2.COP_G_NO,
User_ID,
CurRow2.HSCODE,
CurRow2.G_NAME,
CurRow2.qty);
end if;
if row_count2 = 1 then
update temp_stock
set Sip_out = CurRow2.qty
where USERID = User_ID
and COP_G_NO = CurRow2.COP_G_NO;
end if;
end if;
commit;
end loop;
close Cur_Stock2;
Exception
when others then
rollback;
end PRO_TJ_STOCK;
---------------------------------------------------------------------------------------------------------------------------------
这个存储过程实现的功能是把把查询到的符合条件的记录插入的临时表里面插入数据,同时对已经存在的数据，按照查询用户进行更新
由于数据量比较大，每次操作都比较慢
现在考虑使用Job，每次跑一次当天之前的库存，然后每次再跑一个类似的存储过程，每天的业务量总归没有所有的业务量大，
当查询的时候只需要把当天的业务量统计出来
首先建立2个表，用于保存数据
TEMP1_STOCK_1_1 和 temp1_stock_1_1_1 结构与 temp_stock完全一致，用于 保存当天的料号库存(没有经过筛选的) 和 保存当天的料号库存(经过筛选的)的数据
temp_stock 则用于保存今天之前的数据，因为涉及到多个客户，所以需要先把所有客户的的库存跑出来
---------------------------------------------------------------------------------------------------------------------------------
CREATE OR REPLACE PROCEDURE pro_tj_stock_ForJob IS
/******************************************************************************
PURPOSE: 每天跑作业 实现今天之前的报表统计
REVISIONS:
Ver Date Author Description
--------- ---------- --------------- ------------------------------------
1.0 2009-04-10 jack.jia 1. Created this procedure.
******************************************************************************/
cursor cur_stock_trade_code is
select trade_code from Sys_Ent where status = 'E02';
CurRow0 cur_stock_trade_code%rowtype;
begin
EXECUTE IMMEDIATE 'truncate table TEMP_STOCK';
open cur_stock_trade_code;
loop
fetch cur_stock_trade_code
into CurRow0;
exit when cur_stock_trade_code%notfound;
--select trade_code into trade_code1 from Sys_Ent where status = 'E02';
--传递客户代码到内层存储过程 用于统计今天之前的数据 并保存到 TEMP_STOCK里面去
PRO_TJ_STOCK_Before(CurRow0.Trade_Code);
end loop;
close cur_stock_trade_code;
end pro_tj_stock_ForJob;
---------------------------------------------------------------------------------------------------------------------------------
create or replace procedure PRO_TJ_STOCK_Before(TRADECODE nvarchar2) is
/******************************************************************************
PURPOSE: 实现今天之前的报表统计
TRADECODE 是企业代码
REVISIONS:
Ver Date Author Description
--------- ---------- --------------- ------------------------------------
1.0 2009-04-10 jack.jia 1. Created this procedure.
******************************************************************************/
/*TRADECODE nvarchar2(10); */
row_count integer;
row_count1 integer;
row_count2 integer;
/*cursor cur_stock_trade_code is
select trade_code from Sys_Ent where status='E02';
CurRow0 cur_stock_trade_code%rowtype;*/
--row_count123 integer;
cursor cur_stock is
select distinct COP_G_NO,
biz_type,
IE_FLAG,
c.hs_code as HSCODE,
c.G_NAME,
sum(a.qty) as qty,
(select name
from base_dir
where code = biz_type
and type = 'Biz_Type') as BIZ_TYPE_NAME,
(select name
from base_dir
where code = IE_FLAG
and type = 'IE_Mark') as IE_FLAG_NAME,
b.trade_code as trade_code
from gj_lineone_list a
inner join sys_merger_relation c on c.part_no = a.cop_g_no
and c.trade_code = TRADECODE
inner join gj_lineone_head b on a.gatejob_no = b.gatejob_no
where
--b.passdate<sysdate and
TO_DATE(TO_CHAR(b.passdate, 'YYYY-MM-DD'), 'YYYY-MM-DD') <
TO_DATE(TO_CHAR(SYSDATE, 'YYYY-MM-DD'), 'YYYY-MM-DD')
and b.trade_code = TRADECODE
and ((IE_FLAG = '1' and status = 'C06') or
(IE_FLAG = '2' and
(biz_type = 'B' or biz_type = 'C' or biz_type = 'D') and
status in ('K01', 'K02')) or (IE_FLAG = '2' and biz_type = 'A' and
status in ('C07', 'C06', 'K01', 'K02')) or
(IE_FLAG is null and status = 'C06'))
group by biz_type,
IE_FLAG,
COP_G_NO,
c.hs_code,
c.g_name,
b.trade_code;
CurRow cur_stock%rowtype;
/* FROM 2007-9-4 lily */
--中心内互转 （出）转出（发货）单位
cursor cur_stock1 is
select distinct a.cop_out_no,
biz_type,
--IE_FLAG,
c.hs_code as HSCODE,
c.G_NAME,
sum(a.qty) as qty,
(select name
from base_dir
where code = biz_type
and type = 'Biz_Type') as BIZ_TYPE_NAME,
--(select name from base_dir where code = IE_FLAG and type='IE_Mark') as IE_FLAG_NAME
'' as IE_FLAG_NAME,
b.ownername
from gj_center_list a
inner join gj_center_head b on a.gatejob_no = b.gatejob_no
inner join sys_merger_relation c on c.part_no = a.cop_out_no
and c.trade_code = b.ownername
where
--b.passdate<sysdate and
TO_DATE(TO_CHAR(b.passdate, 'YYYY-MM-DD'), 'YYYY-MM-DD') <
TO_DATE(TO_CHAR(SYSDATE, 'YYYY-MM-DD'), 'YYYY-MM-DD')
and b.ownername = TRADECODE
and b.status = 'C06'
group by a.cop_out_no,
biz_type,
--IE_FLAG,
c.hs_code,
c.g_name,
b.ownername;
CurRow1 cur_stock1%rowtype;
--中心内互转 （进）转入（申请）单位
cursor cur_stock3 is
select distinct a.cop_in_no,
biz_type,
--IE_FLAG,
c.hs_code as HSCODE,
c.G_NAME,
sum(a.qty) as qty,
(select name
from base_dir
where code = biz_type
and type = 'Biz_Type') as BIZ_TYPE_NAME,
--(select name from base_dir where code = IE_FLAG and type='IE_Mark') as IE_FLAG_NAME
'' as IE_FLAG_NAME,
b.trade_code
from gj_center_list a
inner join gj_center_head b on a.gatejob_no = b.gatejob_no
inner join sys_merger_relation c on c.part_no = a.cop_in_no
and c.trade_code = b.trade_code
where
--b.passdate<sysdate and
TO_DATE(TO_CHAR(b.passdate, 'YYYY-MM-DD'), 'YYYY-MM-DD') <
TO_DATE(TO_CHAR(SYSDATE, 'YYYY-MM-DD'), 'YYYY-MM-DD')
and b.trade_code = TRADECODE
and b.status = 'C06'
group by a.cop_in_no,
biz_type,
--IE_FLAG,
c.hs_code,
c.g_name,
b.trade_code;
CurRow3 cur_stock3%rowtype;
/* TO 2007-9-4 lily */
-- -- 简单加工
cursor cur_stock2 is
select distinct COP_G_NO,
c.hs_code as HSCODE,
c.G_NAME,
sum(a.num) as qty,
a.io_mark,
b.trade_code
from simpro_info a
inner join sys_merger_relation c on c.part_no = a.cop_g_no
and c.trade_code = TRADECODE
inner join simpro_head b on a.gatejob_no = b.gatejob_no
where
--b.pass_date<sysdate and
TO_DATE(TO_CHAR(b.pass_date, 'YYYY-MM-DD'), 'YYYY-MM-DD') <
TO_DATE(TO_CHAR(SYSDATE, 'YYYY-MM-DD'), 'YYYY-MM-DD')
and b.status = 'C06' and b.trade_code=TRADECODE
group by COP_G_NO, c.hs_code, c.G_NAME, a.io_mark, b.trade_code;
CurRow2 cur_stock2%rowtype;
begin
/*open cur_stock_trade_code;
loop
fetch cur_stock_trade_code into CurRow0;
exit when cur_stock_trade_code%notfound;
select trade_code into TRADECODE from Sys_Ent where status='E02';
end loop;
close cur_stock_trade_code;*/
row_count := 0;
--delete from TEMP_STOCK where UserID=User_ID;
--commit;
open Cur_Stock;
loop
fetch Cur_Stock
into CurRow;
exit when Cur_Stock%notfound;
--判断有没有重复的料号 增加trade_code判断
select count(*)
into row_count
from TEMP_STOCK
where COP_G_NO = CurRow.COP_G_NO
and userid = CurRow.trade_code;
--一线进区业务
if CurRow.biz_type = 'A' and CurRow.IE_FLAG = '1' then
if row_count = 0 then
insert into TEMP_STOCK
(COP_G_NO, ONELINE_IN_COUNT, HSCODE, G_NAME, userid)
values
(CurRow.COP_G_NO,
CurRow.qty,
CurRow.HSCODE,
CurRow.G_NAME,
CurRow.trade_code);
end if;
if row_count = 1 then
update temp_stock
set ONELINE_IN_COUNT = CurRow.qty
where COP_G_NO = CurRow.COP_G_NO
and userid = CurRow.trade_code;
end if;
end if;
--一线出区业务
if CurRow.biz_type = 'A' and CurRow.IE_FLAG = '2' then
if row_count = 0 then
insert into TEMP_STOCK
(COP_G_NO, ONELINE_OUT_COUNT, HSCODE, G_NAME, userid)
values
(CurRow.COP_G_NO,
CurRow.qty,
CurRow.HSCODE,
CurRow.G_NAME,
CurRow.trade_code);
end if;
if row_count = 1 then
update temp_stock
set ONELINE_OUT_COUNT = CurRow.qty
where COP_G_NO = CurRow.COP_G_NO
and userid = CurRow.trade_code;
end if;
end if;
--二线进区业务
if CurRow.biz_type = 'B' and CurRow.IE_FLAG = '1' then
if row_count = 0 then
insert into TEMP_STOCK
(COP_G_NO, TWOLINE_IN_COUNT, HSCODE, G_NAME, userid)
values
(CurRow.COP_G_NO,
CurRow.qty,
CurRow.HSCODE,
CurRow.G_NAME,
CurRow.trade_code);
end if;
if row_count = 1 then
update temp_stock
set TWOLINE_IN_COUNT = CurRow.qty
where COP_G_NO = CurRow.COP_G_NO
and userid = CurRow.trade_code;
end if;
end if;
--二线出区
if CurRow.biz_type = 'B' and CurRow.IE_FLAG = '2' then
if row_count = 0 then
insert into TEMP_STOCK
(COP_G_NO, TWOLINE_OUT_COUNT, HSCODE, G_NAME, userid)
values
(CurRow.COP_G_NO,
CurRow.qty,
CurRow.HSCODE,
CurRow.G_NAME,
CurRow.trade_code);
end if;
if row_count = 1 then
update temp_stock
set TWOLINE_OUT_COUNT = CurRow.qty
where COP_G_NO = CurRow.COP_G_NO
and userid = CurRow.trade_code;
end if;
end if;
--特殊监管进区业务
if CurRow.biz_type = 'C' and CurRow.IE_FLAG = '1' then
if row_count = 0 then
insert into TEMP_STOCK
(COP_G_NO, SPEC_IN_COUNT, HSCODE, G_NAME, userid)
values
(CurRow.COP_G_NO,
CurRow.qty,
CurRow.HSCODE,
CurRow.G_NAME,
CurRow.trade_code);
end if;
if row_count = 1 then
update temp_stock
set SPEC_IN_COUNT = CurRow.qty
where COP_G_NO = CurRow.COP_G_NO
and userid = CurRow.trade_code;
end if;
end if;
--特殊监管出区业务
if CurRow.biz_type = 'C' and CurRow.IE_FLAG = '2' then
if row_count = 0 then
insert into TEMP_STOCK
(COP_G_NO, SPEC_OUT_COUNT, HSCODE, G_NAME, userid)
values
(CurRow.COP_G_NO,
CurRow.qty,
CurRow.HSCODE,
CurRow.G_NAME,
CurRow.trade_code);
end if;
if row_count = 1 then
update temp_stock
set SPEC_OUT_COUNT = CurRow.qty
where COP_G_NO = CurRow.COP_G_NO
and userid = CurRow.trade_code;
end if;
end if;
--分送集报进区业务
if CurRow.biz_type = 'D' and CurRow.IE_FLAG = '1' then
if row_count = 0 then
insert into TEMP_STOCK
(COP_G_NO, HSCODE, G_NAME, COLL_IN_COUNT, userid)
values
(CurRow.COP_G_NO,
CurRow.HSCODE,
CurRow.G_NAME,
CurRow.qty,
CurRow.trade_code);
end if;
if row_count = 1 then
update temp_stock
set COLL_IN_COUNT = CurRow.qty
where COP_G_NO = CurRow.COP_G_NO
and userid = CurRow.trade_code;
end if;
end if;
--分送集报出区业务
if CurRow.biz_type = 'D' and CurRow.IE_FLAG = '2' then
if row_count = 0 then
insert into TEMP_STOCK
(COP_G_NO, HSCODE, G_NAME, COLL_OUT_COUNT, userid)
values
(CurRow.COP_G_NO,
CurRow.HSCODE,
CurRow.G_NAME,
CurRow.qty,
CurRow.trade_code);
end if;
if row_count = 1 then
update temp_stock
set COLL_OUT_COUNT = CurRow.qty
where COP_G_NO = CurRow.COP_G_NO
and userid = CurRow.trade_code;
end if;
end if;
commit;
end loop;
close Cur_Stock;
/* 2007-09-04 begin lily */
--中心内互转 （出）发货单位
open Cur_Stock1;
loop
fetch Cur_Stock1
into CurRow1;
exit when Cur_Stock1%notfound;
--判断有没有重复的料号
select count(*)
into row_count1
from TEMP_STOCK
where COP_G_NO = CurRow1.Cop_Out_No
and userid = CurRow1.Ownername;
/* if (CurRow1.Cop_Out_No='TCM090330B') then
row_count123:=0;
end if;*/
--中心内互转 （出）发货单位
--if CurRow1.biz_type='E' then
if row_count1 = 0 then
insert into TEMP_STOCK
(COP_G_NO, HSCODE, G_NAME, inc_out, userid)
values
(CurRow1.Cop_Out_No,
CurRow1.HSCODE,
CurRow1.G_NAME,
CurRow1.qty,
CurRow1.Ownername);
end if;
if row_count1 = 1 then
update temp_stock
set inc_out = CurRow1.qty
where COP_G_NO = CurRow1.Cop_Out_No
and userid = CurRow1.Ownername;
end if;
-- end if;
commit;
end loop;
close Cur_Stock1;
--中心内互转 （入）申请单位
open Cur_Stock3;
loop
fetch Cur_Stock3
into CurRow3;
exit when Cur_Stock3%notfound;
--判断有没有重复的料号
select count(*)
into row_count1
from TEMP_STOCK
where COP_G_NO = CurRow3.Cop_In_No
and userid = CurRow3.Trade_Code;
--if CurRow1.biz_type='E' then
if row_count1 = 0 then
insert into TEMP_STOCK
(COP_G_NO, HSCODE, G_NAME, inc_in, userid)
values
(CurRow3.Cop_In_No,
CurRow3.HSCODE,
CurRow3.G_NAME,
CurRow3.qty,
CurRow3.Trade_Code);
end if;
if row_count1 = 1 then
update temp_stock
set inc_in = CurRow3.qty
where COP_G_NO = CurRow3.Cop_In_No
and userid = CurRow3.Trade_Code;
end if;
-- end if;
commit;
end loop;
close Cur_Stock3;
/* 2007-09-04 end lily */
-----简单加工库存查询
open Cur_Stock2;
loop
fetch Cur_Stock2
into CurRow2;
exit when Cur_Stock2%notfound;
--判断有没有重复的料号
select count(*)
into row_count2
from TEMP_STOCK
where COP_G_NO = CurRow2.COP_G_NO
and userid = CurRow2.Trade_Code;
if CurRow2.io_mark = '1' then
if row_count2 = 0 then
insert into TEMP_STOCK
(COP_G_NO, HSCODE, G_NAME, Sip_in, userid)
values
(CurRow2.COP_G_NO,
CurRow2.HSCODE,
CurRow2.G_NAME,
CurRow2.qty,
CurRow2.Trade_Code);
end if;
if row_count2 = 1 then
update temp_stock
set Sip_in = CurRow2.qty
where COP_G_NO = CurRow2.COP_G_NO
and userid = CurRow2.Trade_Code;
end if;
end if;
if CurRow2.io_mark = '2' then
if row_count2 = 0 then
insert into TEMP_STOCK
(COP_G_NO, HSCODE, G_NAME, Sip_out, userid)
values
(CurRow2.COP_G_NO,
CurRow2.HSCODE,
CurRow2.G_NAME,
CurRow2.qty,
CurRow2.Trade_Code);
end if;
if row_count2 = 1 then
update temp_stock
set Sip_out = CurRow2.qty
where COP_G_NO = CurRow2.COP_G_NO
and userid = CurRow2.Trade_Code;
end if;
end if;
commit;
end loop;
close Cur_Stock2;
Exception
when others then
rollback;
end PRO_TJ_STOCK_Before;
---------------------------------------------------------------------------------------------------------------------------------
PRO_TJ_STOCK_Before这个存储过程与PRO_TJ_STOCK类似，只是由单用户统计转为了多用户统计，并且把用户名称保存到TEMP_STOCK.userid字段里面了
然后来看今天当天的库存统计
---------------------------------------------------------------------------------------------------------------------------------
create or replace procedure PRO_TJ_STOCK_today(TRADECODE in varchar2, User_ID in varchar2) is
/******************************************************************************
PURPOSE: 每天跑作业 统计今天的料号库存明细
REVISIONS:
Ver Date Author Description
--------- ---------- --------------- ------------------------------------
1.0 2009-04-10 jack.jia 1. Created this procedure.
******************************************************************************/
row_count integer;
row_count1 integer;
row_count2 integer;
cursor cur_stock is
select distinct COP_G_NO,biz_type,IE_FLAG,c.hs_code as HSCODE,c.G_NAME,
sum(a.qty) as qty,
(select name from base_dir where code = biz_type and type = 'Biz_Type') as BIZ_TYPE_NAME,
(select name from base_dir where code = IE_FLAG and type='IE_Mark') as IE_FLAG_NAME
from gj_lineone_list a
inner join sys_merger_relation c
on c.part_no = a.cop_g_no and c.trade_code = TRADECODE
inner join gj_lineone_head b
on a.gatejob_no = b.gatejob_no
where -- b.passdate=sysdate and
TO_DATE(TO_CHAR(b.passdate,'YYYY-MM-DD'),'YYYY-MM-DD')=TO_DATE(TO_CHAR(SYSDATE,'YYYY-MM-DD'),'YYYY-MM-DD') and
trade_code = TRADECODE and (( IE_FLAG ='1' and status ='C06')
or (IE_FLAG ='2' and (biz_type = 'B' or biz_type = 'C' or biz_type = 'D')
and status in('K01','K02')) or (IE_FLAG ='2' and biz_type = 'A' and
status in('C07','C06','K01','K02')) or (IE_FLAG is null and status ='C06'))
group by biz_type, IE_FLAG, COP_G_NO,c.hs_code,c.g_name;
CurRow cur_stock%rowtype;
/* FROM 2007-9-4 lily */
--中心内互转 （出）转出（发货）单位
cursor cur_stock1 is
select distinct a.cop_out_no,biz_type,
--IE_FLAG,
c.hs_code as HSCODE,c.G_NAME,
sum(a.qty) as qty,
(select name from base_dir where code = biz_type and type = 'Biz_Type') as BIZ_TYPE_NAME,
--(select name from base_dir where code = IE_FLAG and type='IE_Mark') as IE_FLAG_NAME
'' as IE_FLAG_NAME
from gj_center_list a
inner join gj_center_head b
on a.gatejob_no = b.gatejob_no
inner join sys_merger_relation c
on c.part_no = a.cop_out_no and c.trade_code = b.ownername
where --b.passdate=sysdate and
TO_DATE(TO_CHAR(b.passdate,'YYYY-MM-DD'),'YYYY-MM-DD')=TO_DATE(TO_CHAR(SYSDATE,'YYYY-MM-DD'),'YYYY-MM-DD') and
b.ownername = TRADECODE and b.status ='C06'
group by a.cop_out_no,biz_type,
--IE_FLAG,
c.hs_code,c.g_name;
CurRow1 cur_stock1%rowtype;
--中心内互转 （进）转入（申请）单位
cursor cur_stock3 is
select distinct a.cop_in_no,biz_type,
--IE_FLAG,
c.hs_code as HSCODE,c.G_NAME,
sum(a.qty) as qty,
(select name from base_dir where code = biz_type and type = 'Biz_Type') as BIZ_TYPE_NAME,
--(select name from base_dir where code = IE_FLAG and type='IE_Mark') as IE_FLAG_NAME
'' as IE_FLAG_NAME
from gj_center_list a
inner join gj_center_head b
on a.gatejob_no = b.gatejob_no
inner join sys_merger_relation c
on c.part_no = a.cop_in_no and c.trade_code = b.trade_code
where --b.passdate=sysdate and
TO_DATE(TO_CHAR(b.passdate,'YYYY-MM-DD'),'YYYY-MM-DD')=TO_DATE(TO_CHAR(SYSDATE,'YYYY-MM-DD'),'YYYY-MM-DD') and
b.trade_code = TRADECODE and b.status ='C06'
group by a.cop_in_no,biz_type,
--IE_FLAG,
c.hs_code,c.g_name;
CurRow3 cur_stock3%rowtype;
/* TO 2007-9-4 lily */
-- -- 简单加工
cursor cur_stock2 is
select distinct COP_G_NO,
c.hs_code as HSCODE,c.G_NAME,
sum(a.num) as qty,a.io_mark
from simpro_info a
inner join sys_merger_relation c
on c.part_no = a.cop_g_no and c.trade_code = TRADECODE
inner join simpro_head b
on a.gatejob_no = b.gatejob_no
where --b.pass_date=sysdate
TO_DATE(TO_CHAR(b.pass_date,'YYYY-MM-DD'),'YYYY-MM-DD')=TO_DATE(TO_CHAR(SYSDATE,'YYYY-MM-DD'),'YYYY-MM-DD') and
status ='C06'
group by COP_G_NO, c.hs_code,c.G_NAME,a.io_mark;
CurRow2 cur_stock2%rowtype;
begin
row_count:=0;
EXECUTE IMMEDIATE 'truncate table TEMP1_STOCK_1_1';
EXECUTE IMMEDIATE 'truncate table temp1_stock_1_1_1';
--delete from TEMP1_STOCK_1_1 where UserID=User_ID;
--commit;
open Cur_Stock;
loop
fetch Cur_Stock into CurRow;
exit when Cur_Stock%notfound;
--判断有没有重复的料号
select count(*) into row_count from TEMP1_STOCK_1_1 where UserID=User_ID and COP_G_NO =CurRow.COP_G_NO ;
--一线进区业务
if CurRow.biz_type='A' and CurRow.IE_FLAG ='1' then
if row_count =0 then
insert into TEMP1_STOCK_1_1 (COP_G_NO,ONELINE_IN_COUNT,USERID,HSCODE,G_NAME) values(CurRow.COP_G_NO ,CurRow.qty,User_ID,CurRow.HSCODE ,CurRow.G_NAME);
end if;
if row_count =1 then
update TEMP1_STOCK_1_1 set ONELINE_IN_COUNT = CurRow.qty where USERID=User_ID and COP_G_NO =CurRow.COP_G_NO;
end if;
end if;
--一线出区业务
if CurRow.biz_type='A' and CurRow.IE_FLAG ='2' then
if row_count =0 then
insert into TEMP1_STOCK_1_1 (COP_G_NO,ONELINE_OUT_COUNT,Userid,HSCODE,G_NAME) values(CurRow.COP_G_NO, CurRow.qty,User_ID,CurRow.HSCODE ,CurRow.G_NAME);
end if;
if row_count =1 then
update TEMP1_STOCK_1_1 set ONELINE_OUT_COUNT = CurRow.qty where USERID=User_ID and COP_G_NO = CurRow.COP_G_NO;
end if;
end if;
--二线进区业务
if CurRow.biz_type='B' and CurRow.IE_FLAG ='1' then
if row_count =0 then
insert into TEMP1_STOCK_1_1 (COP_G_NO,TWOLINE_IN_COUNT, Userid, HSCODE,G_NAME) values(CurRow.COP_G_NO, CurRow.qty, User_ID,CurRow.HSCODE ,CurRow.G_NAME);
commit;
end if;
if row_count =1 then
update TEMP1_STOCK_1_1 set TWOLINE_IN_COUNT = CurRow.qty where USERID=User_ID and COP_G_NO = CurRow.COP_G_NO;
end if;
end if;
--二线出区
if CurRow.biz_type='B' and CurRow.IE_FLAG ='2' then
if row_count =0 then
insert into TEMP1_STOCK_1_1 (COP_G_NO, TWOLINE_OUT_COUNT, Userid ,HSCODE,G_NAME) values(CurRow.COP_G_NO, CurRow.qty,User_ID,CurRow.HSCODE ,CurRow.G_NAME);
end if;
if row_count =1 then
update TEMP1_STOCK_1_1 set TWOLINE_OUT_COUNT = CurRow.qty where USERID=User_ID and COP_G_NO = CurRow.COP_G_NO;
end if;
end if;
--特殊监管进区业务
if CurRow.biz_type='C' and CurRow.IE_FLAG ='1' then
if row_count =0 then
insert into TEMP1_STOCK_1_1 (COP_G_NO, SPEC_IN_COUNT, Userid ,HSCODE,G_NAME) values(CurRow.COP_G_NO, CurRow.qty,User_ID,CurRow.HSCODE ,CurRow.G_NAME);
end if;
if row_count =1 then
update TEMP1_STOCK_1_1 set SPEC_IN_COUNT = CurRow.qty where USERID=User_ID and COP_G_NO = CurRow.COP_G_NO;
end if;
end if;
--特殊监管出区业务
if CurRow.biz_type='C' and CurRow.IE_FLAG ='2' then
if row_count =0 then
insert into TEMP1_STOCK_1_1 (COP_G_NO, SPEC_OUT_COUNT, Userid,HSCODE,G_NAME ) values(CurRow.COP_G_NO, CurRow.qty, User_ID,CurRow.HSCODE ,CurRow.G_NAME);
end if;
if row_count =1 then
update TEMP1_STOCK_1_1 set SPEC_OUT_COUNT = CurRow.qty where USERID=User_ID and COP_G_NO = CurRow.COP_G_NO;
end if;
end if;
--分送集报进区业务
if CurRow.biz_type='D' and CurRow.IE_FLAG ='1' then
if row_count =0 then
insert into TEMP1_STOCK_1_1 (COP_G_NO,Userid,HSCODE,G_NAME,COLL_IN_COUNT) values(CurRow.COP_G_NO, User_ID,CurRow.HSCODE ,CurRow.G_NAME,CurRow.qty);
end if;
if row_count =1 then
update TEMP1_STOCK_1_1 set COLL_IN_COUNT = CurRow.qty where USERID=User_ID and COP_G_NO = CurRow.COP_G_NO;
end if;
end if;
--分送集报出区业务
if CurRow.biz_type='D' and CurRow.IE_FLAG ='2' then
if row_count =0 then
insert into TEMP1_STOCK_1_1 (COP_G_NO,Userid,HSCODE,G_NAME,COLL_OUT_COUNT) values(CurRow.COP_G_NO,User_ID,CurRow.HSCODE ,CurRow.G_NAME,CurRow.qty);
end if;
if row_count =1 then
update TEMP1_STOCK_1_1 set COLL_OUT_COUNT = CurRow.qty where USERID=User_ID and COP_G_NO = CurRow.COP_G_NO;
end if;
end if;
commit;
end loop;
close Cur_Stock;
/* 2007-09-04 begin lily */
--中心内互转 （出）发货单位
open Cur_Stock1;
loop
fetch Cur_Stock1 into CurRow1;
exit when Cur_Stock1%notfound;
--判断有没有重复的料号
select count(*) into row_count1 from TEMP1_STOCK_1_1 where UserID=User_ID and COP_G_NO =CurRow1.Cop_Out_No ;
--中心内互转 （出）发货单位
--if CurRow1.biz_type='E' then
if row_count1 =0 then
insert into TEMP1_STOCK_1_1 (COP_G_NO,Userid,HSCODE,G_NAME,inc_out) values(CurRow1.Cop_Out_No,User_ID,CurRow1.HSCODE ,CurRow1.G_NAME,CurRow1.qty);
end if;
if row_count1 =1 then
update TEMP1_STOCK_1_1 set inc_out = CurRow1.qty where USERID=User_ID and COP_G_NO = CurRow1.Cop_Out_No;
end if;
-- end if;
commit;
end loop;
close Cur_Stock1;
--中心内互转 （入）申请单位
open Cur_Stock3;
loop
fetch Cur_Stock3 into CurRow3;
exit when Cur_Stock3%notfound;
--判断有没有重复的料号
select count(*) into row_count1 from TEMP1_STOCK_1_1 where UserID=User_ID and COP_G_NO =CurRow3.Cop_In_No ;
--if CurRow1.biz_type='E' then
if row_count1 =0 then
insert into TEMP1_STOCK_1_1 (COP_G_NO,Userid,HSCODE,G_NAME,inc_in) values(CurRow3.Cop_In_No,User_ID,CurRow3.HSCODE ,CurRow3.G_NAME,CurRow3.qty);
end if;
if row_count1 =1 then
update TEMP1_STOCK_1_1 set inc_in = CurRow3.qty where USERID=User_ID and COP_G_NO = CurRow3.Cop_In_No;
end if;
-- end if;
commit;
end loop;
close Cur_Stock3;
/* 2007-09-04 end lily */
-----简单加工库存查询
open Cur_Stock2;
loop
fetch Cur_Stock2 into CurRow2;
exit when Cur_Stock2%notfound;
--判断有没有重复的料号
select count(*) into row_count2 from TEMP1_STOCK_1_1 where UserID=User_ID and COP_G_NO =CurRow2.COP_G_NO ;
if CurRow2.io_mark='1' then
if row_count2 =0 then
insert into TEMP1_STOCK_1_1 (COP_G_NO,Userid,HSCODE,G_NAME,Sip_in) values(CurRow2.COP_G_NO,User_ID,CurRow2.HSCODE ,CurRow2.G_NAME,CurRow2.qty);
end if;
if row_count2 =1 then
update TEMP1_STOCK_1_1 set Sip_in = CurRow2.qty where USERID=User_ID and COP_G_NO = CurRow2.COP_G_NO;
end if;
end if;
if CurRow2.io_mark='2' then
if row_count2 =0 then
insert into TEMP1_STOCK_1_1 (COP_G_NO,Userid,HSCODE,G_NAME,Sip_out) values(CurRow2.COP_G_NO,User_ID,CurRow2.HSCODE ,CurRow2.G_NAME,CurRow2.qty);
end if;
if row_count2 =1 then
update TEMP1_STOCK_1_1 set Sip_out = CurRow2.qty where USERID=User_ID and COP_G_NO = CurRow2.COP_G_NO;
end if;
end if;
commit;
end loop;
close Cur_Stock2;
insert into temp1_stock_1_1_1(
cop_g_no,oneline_in_count,oneline_out_count,
twoline_in_count,twoline_out_count,
spec_in_count,spec_out_count,
coll_in_count,coll_out_count,
inc_in,inc_out,
sip_in,sip_out,
hscode,g_name
)
/* select a.*
FROM temp_stock a
left outer join sys_curr_rate r ON r.t_currcode = '502'
left join gj_stock b on a.cop_g_no = b.cop_g_no
WHERE --userid = 'cblc' and
b.TRADE_CODE = TRADECODE;*/
select a.cop_g_no,
sum(a.oneline_in_count) as oneline_in_count,
sum(a.oneline_out_count) as oneline_out_count,
sum(a.twoline_in_count) as twoline_in_count,
sum(a.twoline_out_count) as twoline_out_count,
sum(a.spec_in_count) as spec_in_count,
sum(a.spec_out_count) as spec_out_count,
sum(a.coll_in_count) as coll_in_count,
sum(a.coll_out_count) as coll_out_count,
sum(a.inc_in) as inc_in,
sum(a.inc_out) as inc_out,
sum(a.sip_in) as sip_in,
sum(a.sip_out) as sip_out,
a.hscode,
a.g_name
FROM temp_stock a
left outer join sys_curr_rate r ON r.t_currcode = '502'
left join gj_stock b on a.cop_g_no = b.cop_g_no
WHERE
--a.userid is NULL and
(a.userid = TRADECODE or a.userid is NULL ) and
b.TRADE_CODE = TRADECODE and a.cop_g_no=b.cop_g_no
group by a.cop_g_no, a.hscode,a.g_name;
update temp1_stock_1_1_1 set USERID = User_ID;
commit;
Exception
when others then rollback;
end PRO_TJ_STOCK_today;
---------------------------------------------------------------------------------------------------------------------------------
PRO_TJ_STOCK_today 存储过程把今天的统计数据保存到了 TEMP1_STOCK_1_1 和 temp1_stock_1_1_1 里面
保存当天的料号库存(没有经过筛选的) 和 保存当天的料号库存(经过筛选的)
temp1_stock_1_1_1 保存的的是 temp_stock里面当前用户的今天之前的数据
---------------------------------------------------------------------------------------------------------------------------------
通过视图与temp_stock 联合查询把数据显示出来
create or replace view v_temp1_stock as
select a.cop_g_no,
sum(a.oneline_in_count) as oneline_in_count,
sum(a.oneline_out_count) as oneline_out_count,
sum(a.twoline_in_count) as twoline_in_count,
sum(a.twoline_out_count) as twoline_out_count,
sum(a.spec_in_count) as spec_in_count,
sum(a.spec_out_count) as spec_out_count,
a.userid,
a.hscode,
a.g_name,
sum(a.coll_in_count) as coll_in_count,
sum(a.coll_out_count) as coll_out_count,
sum(a.inc_in) as inc_in,
sum(a.inc_out) as inc_out,
sum(a.sip_in) as sip_in,
sum(a.sip_out) as sip_out
from
(
select * from temp1_stock_1_1_1
/* where cop_g_no='DSD081127-3'*/
union
select * from temp1_stock_1_1
/* where cop_g_no='DSD081127-3'*/
) a
/*where a.cop_g_no='DSD081127-3'
and a.userid='cblc'*/
group by a.cop_g_no, a.userid, a.hscode,a.g_name
---------------------------------------------------------------------------------------------------------------------------------
每天凌晨1点跑Job，把前一天的以前的所有库存数据跑出来保存到 TEMP_STOCK表里面
begin
sys.dbms_job.submit(job => :job,
what => 'begin
pro_tj_stock_forjob();
end;',
next_date => to_date('09-04-2022 23:30:30', 'dd-mm-yyyy hh24:mi:ss'),
interval => 'TRUNC(sysdate) + 1 +1 / (24)');
commit;
end;
/
---------------------------------------------------------------------------------------------------------------------------------
每天需要查看报表的时候只需要运行 PRO_TJ_STOCK_today存储过程，显示 v_temp1_stock 就可以了
