1、= sysdate-1/24;part_hour :=to_char(sysdate-1/24,hh24);part_day :=to_char(sysdate-1,ddinteractionhour := interactionhour|to_char(calendar,yyyymminteractionday :interactiondayinteractionday1 :interactionday1upcdrname :upcdryymmddproc_interactionhour (interactionhour,upcdrname ,part_hour ,calendar);if
2、to_char(sysdate,)=03 thenproc_interactionday (interactionhour ,interactionday ,interactionday1 ,part_day );end if;04proc_interactiondayshow (interactionday1 ,interactionday );insert into appmsgvalues(成功执行proc_batch,proc_batch,sysdate);commit; EXCEPTION WHEN OTHERS THEN rollback; errmsg:= substr(sqle
3、rrm,1,300); insert into appmsg values (没有成功执行proc_batch commit;END proc_batch;/二:CREATE OR REPLACE PROCEDURE proc_interactionday (interactionhour varchar,interactionday varchar,interactionday1 varchar,part_day varchar)sqltxt1 varchar(2000);sqltxt2 varchar(2000);cur_no number;cur_val number;sqltxt1 :
4、= insert into | interactionday | (rival_no,area_no,calendar) select distinct rival_no,area_no,substr(calendar,1,8) calendar from | interactionhour partition (part_ | part_day ) | where 1=1 and substr(rival_no,1,2)= ;cur_no:=dbms_sql.open_cursor;dbms_sql.parse(cur_no,sqltxt1,dbms_sql.native);cur_val:
5、=dbms_sql.execute(cur_no);dbms_sql.close_cursor(cur_no);sqltxt2 : | interactionday1 | interactionhour and substr(rival_no,1,3) in (130131132133134dbms_sql.parse(cur_no,sqltxt2,dbms_sql.native); values(成功执行proc_interactiondayproc_interactionday errmsg : insert into appmsg 没有成功执行proc_interactionday,原因
6、是:|errmsg,sysdate) ;END proc_interactionday;三:CREATE OR REPLACE PROCEDURE proc_interactiondayshow (interactionday1 varchar,interactionday varchar)sqltxt3 varchar(2000);sqltxt4 varchar(2000); interactioncodedayshow(supplier,code,tos,area_name,part_name,total,calendar) select b.supplier, b.code,b.tos,
7、b.area_name,b.part_name,count(*) total,a.calendar from a, static_interactionno1 b and to_number(substr(a.rival_no,1,7)=b.code group by b.code,b.supplier,b.tos,b.area_name,b.part_name,a.calendar interactioncodedayshow(supplier,area_name,part_name,tos,code,total,calendar) select c.supplier,c.area_name
8、,c.part_name,c.tos,c.code,d.total,d.calendar from static_interactionno c, (select substr(rival_no,1,6) code,count(*) total,calendar group by substr(rival_no,1,6),calendar union all select substr(rival_no,1,7) code,count(*) total,calendar group by substr(rival_no,1,7),calendar select substr(rival_no,
9、1,8) code,count(*) total,calendar group by substr(rival_no,1,8),calendar) d where c.area_no|c.code=d.code order by c.area_no sqltxt3 :insert into interactionpartdayshow (supplier,area_name,part_name,tos, total,calendar ) select supplier,area_name,part_name,tos,sum(total) total,calendar from interactioncodedayshow group by part_name,supplier,tos,calendar,area_name dbms_sql.parse(cur_no,sqltxt3,dbms_sql.native);sqltxt4 : insert into interact
copyright@ 2008-2022 冰豆网网站版权所有
经营许可证编号:鄂ICP备2022015515号-1