CREATE OR REPLACE Procedure p_wip_916_y AS
User_Err Exception;
Str_Err Varchar2(1000);
t_Sysid Varchar2(1000);
t_packages Varchar2(1000);
t_pddname Varchar2(1000);
t_UNFEED_QTY number; --未投数量
t_FEEDABL_DAYS number;--可投天数
t_REQUIRE_FEED_DAYS VARCHAR2(2000); --要求投料
t_FEED_DATE date; --当前时间
t_FEED_QTY number; --当前时间包含的投入数(取前30天的数据)
i number;
v_sql_temp varchar2(4000);
v_sql_result varchar2(4000);
v_sql varchar2(4000);
/*
Cursor Query1 Is
select Distinct t.pddname,(select p.packages from mes_eng_pdd p where p.sysid=t.pddid) packages,t.FROMQTY,
(select (o.qty-t.FROMQTY) UNFEED_QTY from mes_pc_order o where o.num=t.ponum ) UNFEED_QTY
from rpt_wip_trans_v t where t.custcode='916' and t.STEP='装片1' and t.TYPE='完工'
and t.DATES<sysdate and t.DATES>=trunc(sysdate-30); --group by t.pddname,t.pddid;
*/
Cursor Query1 Is
select t.pddname from rpt_wip_trans_v2 t where t.custcode='916' and t.STEP='装片1' and t.TYPE='完工'
and t.DATES<sysdate and t.DATES>=trunc(sysdate-30) group by t.pddname;
Begin
Delete From mes_wip_916_yj t; --更新之前先把之前的数据
For Rec In Query1 Loop
for i in reverse 1..29 loop
--当天获取的数据
-- select nvl(sum(t.FROMQTY),0) into t_FEED_QTY from rpt_wip_trans_v2 t where t.custcode='916' and t.STEP='装片1' and t.TYPE='完工' and t.DATES=trunc(sysdate-30+i);--and t.DATES<=sysdate and t.DATES>=trunc(sysdate-30+i) ;
--封装形式
select t.packages into t_packages from mes_eng_pdd t where t.name=Rec.Pddname;
--当天获取的数据
select nvl(sum(t.FROMQTY),0) into t_FEED_QTY from rpt_wip_trans_v2 t where t.custcode='916' and t.STEP='装片1' and t.TYPE='完工' and t.packages= t_packages and t.DATES=trunc(sysdate-30+i);--and t.DATES<=sysdate and t.DATES>=trunc(sysdate-30+i) ;
--未投数
select nvl(sum(t.qty),0) into t_unfeed_qty from rpt_wip_trans_v2 t where t.custcode='916' and t.STEP='装片1' and t.TYPE='完工' and t.DATES=trunc(sysdate-30+i);--and t.DATES<=sysdate and t.DATES>=trunc(sysdate-30+i) ;
t_Sysid :=Get_sysid('mes_wip_916_yj');
-- t_packages :=Rec.packages;
t_pddname :=Rec.pddname;
--t_UNFEED_QTY:=Rec.Unfeed_Qty;
t_FEED_DATE := to_char(sysdate-30+i,'yyyy_mm_dd');
-- t_FEED_QTY :=Rec.Fromqty;
Insert Into mes_wip_916_yj
(sysid,pddname,packages,UNFEED_QTY,feed_DATE,FEED_QTY)
values
(t_Sysid,t_pddname,t_packages,t_unfeed_qty,t_FEED_DATE,t_FEED_QTY);
end loop;
end loop;
commit;
-- v_sql_temp :=v_sql_temp || 'select wm_concat('''''''' || t.feed_DATE || '''''''' || '' '' ||t.matrix_field ) from';
--v_sql_temp :=v_sql_temp || 'select wm_concat('''''''' || t.feed_DATE || '''''''' ) from';
v_sql_temp :=v_sql_temp || 'select wm_concat(''''''''|| t.feed_DATE || '''''''' || '' as '' || ''"'' || t.feed_date || ''"'') from';
v_sql_temp :=v_sql_temp || '(select distinct tt.feed_DATE from mes_wip_916_yj_v tt order by tt.feed_date desc ) t';
Execute Immediate v_sql_temp into v_sql_result ;
v_Sql := 'create or replace view ' || 'mes_wip_916' || '_v1 as ';
v_Sql := v_Sql || 'select a.* from (';
v_Sql := v_Sql || 'select t.packages 封装形式, t.pddname 产品名称, t.unfeed_qty 未投数量,t.FEEDABL_DAYS 可投天数,t.REQUIRE_FEED_DAYS 要求投料,t.feed_date,t.FEED_QTY from mes_wip_916_yj_v t order by t.feed_date desc ) ';
v_Sql := v_Sql || 'pivot (max(FEED_QTY) for feed_DATE in (';
v_Sql := v_Sql || v_Sql_Result;
v_Sql := v_Sql || ')) a ';
Execute Immediate v_Sql;
-- return v_sql ;
Exception
When User_Err Then
Raise_Application_Error(-20007, Str_Err);
End;
Oracle利用Procedures创建函数以及DBME_JOBS定时更新每日视图
最新推荐文章于 2024-07-18 14:34:06 发布
![](https://img-home.csdnimg.cn/images/20240711042549.png)