存儲過程實例oracle

1、一：CREATE OR REPLACE PROCEDURE proc_batch ISinteractionhour varchar(100);upcdrname varchar(100);part_hour varchar(100);calendar date;interactionday1 varchar(100);interactionday varchar(100);part_day varchar(100);errmsg varchar(300);BEGINcalendar := sysdate-1/24;part_hour :=to_char(sysdate-1/24,hh24);

2、part_day :=to_char(sysdate-1,dd);interactionhour := interactionhour|to_char(calendar,yyyymm);interactionday := interactionday|to_char(calendar,yyyymm);interactionday1 := interactionday1|to_char(calendar,yyyymm);upcdrname := upcdr|to_char(calendar,yymmdd);proc_interactionhour (interactionhour,upcdrna

3、me ,part_hour ,calendar);if to_char(sysdate,hh24)=03 thenproc_interactionday (interactionhour ,interactionday ,interactionday1 ,part_day );end if;if to_char(sysdate,hh24)=04 thenproc_interactiondayshow (interactionday1 ,interactionday );end if;insert into appmsgvalues(成功執(zhí)行proc_batch,proc_batch,sysda

4、te);commit; EXCEPTION WHEN OTHERS THEN rollback; errmsg:= substr(sqlerrm,1,300); insert into appmsg values (沒有成功執(zhí)行proc_batch,proc_batch,sysdate); commit; END proc_batch;/二：CREATE OR REPLACE PROCEDURE proc_interactionday (interactionhour varchar,interactionday varchar,interactionday1 varchar,part_day

5、 varchar)ISsqltxt1 varchar(2000);sqltxt2 varchar(2000); cur_no number;cur_val number;errmsg varchar(300);BEGINsqltxt1 := 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| )

6、| where 1=1 | and substr(rival_no,1,2)=04 ;cur_no:=dbms_sql.open_cursor;dbms_sql.parse(cur_no,sqltxt1,dbms_sql.native);cur_val:=dbms_sql.execute(cur_no);dbms_sql.close_cursor(cur_no);sqltxt2 := insert into | interactionday1| (rival_no,area_no,calendar) | select distinct rival_no,area_no,substr(calen

7、dar,1,8) | calendar from | interactionhour | partition (part_| part_day| ) | where 1=1 | and substr(rival_no,1,3) in (130,131,132,133,134 ) ;cur_no:=dbms_sql.open_cursor;dbms_sql.parse(cur_no,sqltxt2,dbms_sql.native);cur_val:=dbms_sql.execute(cur_no);dbms_sql.close_cursor(cur_no);insert into appmsg

8、values(成功執(zhí)行proc_interactionday,proc_interactionday,sysdate); commit; EXCEPTION WHEN OTHERS THEN rollback; errmsg := substr(sqlerrm,1,300); insert into appmsg values(沒有成功執(zhí)行proc_interactionday,原因是:|errmsg,proc_interactionday,sysdate) ; commit;END proc_interactionday;/三：CREATE OR REPLACE PROCEDURE proc

9、_interactiondayshow (interactionday1 varchar,interactionday varchar)ISsqltxt1 varchar(2000);sqltxt2 varchar(2000);sqltxt3 varchar(2000);sqltxt4 varchar(2000);cur_no number;cur_val number;errmsg varchar(300);BEGINsqltxt1 := insert into | interactioncodedayshow(supplier,code,tos,area_name,part_name,to

10、tal,calendar) | select b.supplier, b.code,b.tos,b.area_name,b.part_name,count(*) total,a.calendar | from | interactionday1| a, |static_interactionno1 b | where 1=1 | 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 ;cur_no:=dbms_sql.op

11、en_cursor;dbms_sql.parse(cur_no,sqltxt1,dbms_sql.native);cur_val:=dbms_sql.execute(cur_no);dbms_sql.close_cursor(cur_no);sqltxt2 := insert into |interactioncodedayshow(supplier,area_name,part_name,tos,code,total,calendar)| select c.supplier,c.area_name,c.part_name,c.tos,c.code,d.total,d.calendar | f

12、rom static_interactionno c, | (select substr(rival_no,1,6) code,count(*) total,calendar | from | interactionday| group by substr(rival_no,1,6),calendar | union all | select substr(rival_no,1,7) code,count(*) total,calendar | from | interactionday| group by substr(rival_no,1,7),calendar | union all |

13、 select substr(rival_no,1,8) code,count(*) total,calendar | from | interactionday| group by substr(rival_no,1,8),calendar) d | where c.area_no|c.code=d.code | order by c.area_no ;cur_no:=dbms_sql.open_cursor;dbms_sql.parse(cur_no,sqltxt2,dbms_sql.native);cur_val:=dbms_sql.execute(cur_no);dbms_sql.cl

14、ose_cursor(cur_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 ;cur_no:=dbms_sql.open_cursor;d

15、bms_sql.parse(cur_no,sqltxt3,dbms_sql.native);cur_val:=dbms_sql.execute(cur_no);dbms_sql.close_cursor(cur_no);sqltxt4 := insert into| interactionareadayshow (supplier,area_name,tos, total,calendar) | select supplier,area_name,tos,sum(total) total,calendar | from interactioncodedayshow | group by sup

16、plier,tos,calendar,area_name ;cur_no:=dbms_sql.open_cursor;dbms_sql.parse(cur_no,sqltxt4,dbms_sql.native);cur_val:=dbms_sql.execute(cur_no);dbms_sql.close_cursor(cur_no);insert into appmsg values(成功執(zhí)行proc_interactiondayshow,proc_interactiondayshow,sysdate); commit; EXCEPTION WHEN OTHERS THEN rollbac

17、k; errmsg := substr(sqlerrm,1,300); insert into appmsg values(沒有成功執(zhí)行proc_interactionday,原因是:|errmsg,proc_interactionday,sysdate) ; commit;END proc_interactiondayshow;/四：CREATE OR REPLACE PROCEDURE proc_interactionhour (interactionhour varchar,upcdrname varchar,part_hour varchar,calendar date)ISsqltx

18、t varchar(20000);cur_no number;cur_val number;errmsg varchar(300);BEGIN sqltxt := insert into |interactionhour | (rival_no,area_no,calendar) | with | ls1 as | (select case | when substr(trim(truncatedcaller),1,2)=13 and length(trim(truncatedcaller)=11 then trim(truncatedcaller) | when substr(trim(tr

19、uncatedcaller),1,3)=153 and length(trim(truncatedcaller)=11 then trim(truncatedcaller) | when substr(trim(truncatedcaller),1,1) between 2 and 8 and length(trim(truncatedcaller)=7 then trim(truncatedcaller) | else 110| end rival_no,| case | when substr(trim(applix_no),1,2)=13 and length(trim(applix_n

20、o)=11 then trim(applix_no) | when substr(trim(applix_no),1,3)=153 and length(trim(applix_no)=11 then trim(applix_no) | when substr(trim(applix_no),1,1)=0 and length(trim(applix_no)=11 then trim(applix_no) | when substr(trim(applix_no),1,2) between 2 and 8 and length(trim(applix_no)=7 then trim(appli

21、x_no) | else 110| end applix_no,sarea,darea,| to_char(calendar,yyyymmddhh24) | calendar from |upcdrname | partition (part_|part_hour|) cdr | where 1=1| and exists (select x from static_interactionname s where cdr.opc=s.xpc and netname移動)| and exists (select x from static_interactionname s where cdr.

22、dpc=s.xpc and netname=移動)| and cf between 132 and 139 | ), | ls2 as | (select case | when substr(trim(truncatedcalled),1,2)=13 and length(trim(truncatedcalled)=11 then trim(truncatedcalled) | when substr(trim(truncatedcalled),1,3)=153 and length(trim(truncatedcalled)=11 then trim(truncatedcalled) |

23、when substr(trim(truncatedcalled),1,1) between 2 and 8 and length(trim(truncatedcalled)=7 then trim(truncatedcalled) | else 110| end rival_no,| case | when substr(trim(applix_no),1,2)=13 and length(trim(applix_no)=11 then trim(applix_no) | when substr(trim(applix_no),1,3)=153 and length(trim(applix_

24、no)=11 then trim(applix_no) | when substr(trim(applix_no),1,1)=0 and length(trim(applix_no)=11 then trim(applix_no) | when substr(trim(applix_no),1,2) between 2 and 8 and length(trim(applix_no)=7 then trim(applix_no) | else 110| end applix_no,sarea,darea,| to_char(calendar,yyyymmddhh24) | calendar f

25、rom |upcdrname | partition (part_|part_hour | ) cdr | where 1=1 | and exists (select xpc from static_interactionname s where cdr.dpc=s.xpc and netname移動) | and exists (select xpc from static_interactionname s where cdr.opc=s.xpc and netname=移動)| and cf between 132 and 139 | ) | select rival_no,sarea

26、,calendar | from ls1 | where sarea between 431 and 439 | union | select applix_no,darea,calendar | from ls1 | where darea between 431 and 439 | union | select rival_no,darea,calendar | from ls2 | where darea between 431 and 439 | union | select applix_no,darea,calendar | from ls2 | where darea betwe

27、en 431 and 439 ; cur_no:=dbms_sql.open_cursor; dbms_sql.parse(cur_no,sqltxt,dbms_sql.native); cur_val:=dbms_sql.execute(cur_no); dbms_sql.close_cursor(cur_no); commit; insert into appmsg values(成功執(zhí)行proc_interactionhour,proc_interactionhour,sysdate); commit;EXCEPTION WHEN OTHERS THEN rollback; errmsg

28、 := substr(sqlerrm,1,300); insert into appmsg values(沒有成功執(zhí)行proc_interactionhour,原因是:|errmsg,proc_interactionhour,sysdate) ; commit;END proc_interactionhour;/五：CREATE OR REPLACE PROCEDURE proc_interactionhouryzg (interactionhour varchar,upcdrname varchar,part_hour varchar,calendar date)ISsqltxt varch

29、ar(20000);cur_no number;cur_val number;errmsg varchar(300);BEGIN sqltxt := insert into |interactionhour | (rival_no,area_no,calendar) | with | ls1 as | (select case | when substr(trim(truncatedcaller),1,2)=13 and length(trim(truncatedcaller)=11 then trim(truncatedcaller) | when substr(trim(truncated

30、caller),1,3)=153 and length(trim(truncatedcaller)=11 then trim(truncatedcaller) | when substr(trim(truncatedcaller),1,1) between 2 and 8 and length(trim(truncatedcaller)=7 then 0|sarea|trim(truncatedcaller) | else 110| end rival_no,| case | when substr(trim(applix_no),1,2)=13 and length(trim(applix_

31、no)=11 then trim(applix_no) | when substr(trim(applix_no),1,3)=153 and length(trim(applix_no)=11 then trim(applix_no) | when substr(trim(applix_no),1,1)=0 and length(trim(applix_no)=11 then trim(applix_no) | when substr(trim(applix_no),1,2) between 2 and 8 and length(trim(applix_no)=7 then 0|darea|t

32、rim(applix_no) | else 110| end applix_no,sarea,darea,| to_char(calendar,yyyymmddhh24) | calendar from |upcdrname | partition (part_|part_hour|) cdr | where 1=1| and exists (select x from static_interactionname s where cdr.opc=s.xpc and netname移動)| and exists (select x from static_interactionname s w

33、here cdr.dpc=s.xpc and netname=移動)| and cf between 132 and 139 | ), | ls2 as | (select case | when substr(trim(truncatedcalled),1,2)=13 and length(trim(truncatedcalled)=11 then trim(truncatedcalled) | when substr(trim(truncatedcalled),1,3)=153 and length(trim(truncatedcalled)=11 then trim(truncatedc

34、alled) | when substr(trim(truncatedcalled),1,1) between 2 and 8 and length(trim(truncatedcalled)=7 then 0|darea|trim(truncatedcalled) | else 110| end rival_no,| case | when substr(trim(applix_no),1,2)=13 and length(trim(applix_no)=11 then trim(applix_no) | when substr(trim(applix_no),1,3)=153 and le

35、ngth(trim(applix_no)=11 then trim(applix_no) | when substr(trim(applix_no),1,1)=0 and length(trim(applix_no)=11 then trim(applix_no) | when substr(trim(applix_no),1,2) between 2 and 8 and length(trim(applix_no)=7 then 0|darea|trim(applix_no) | else 110| end applix_no,sarea,darea,| to_char(calendar,y

36、yyymmddhh24) | calendar from |upcdrname | partition (part_|part_hour | ) cdr | where 1=1 | and exists (select xpc from static_interactionname s where cdr.dpc=s.xpc and netname移動) | and exists (select xpc from static_interactionname s where cdr.opc=s.xpc and netname=移動)| and cf between 132 and 139 |

37、) | select rival_no,sarea,calendar | from ls1 | where sarea between 431 and 439 | union | select applix_no,darea,calendar | from ls1 | where darea between 431 and 439 | union | select rival_no,darea,calendar | from ls2 | where darea between 431 and 439 | union | select applix_no,darea,calendar | fro

38、m ls2 | where darea between 431 and 439 ; cur_no:=dbms_sql.open_cursor; dbms_sql.parse(cur_no,sqltxt,dbms_sql.native); cur_val:=dbms_sql.execute(cur_no); dbms_sql.close_cursor(cur_no); commit; insert into appmsg values(成功執(zhí)行proc_interactionhour,proc_interactionhour,sysdate); commit;EXCEPTION WHEN OTH

39、ERS THEN rollback; errmsg := substr(sqlerrm,1,300); insert into appmsg values(沒有成功執(zhí)行proc_interactionhour,原因是:|errmsg,proc_interactionhour,sysdate) ; commit;END proc_interactionhouryzg;/六：CREATE OR REPLACE PROCEDURE proc_text(interactionhour varchar,upcdrname varchar,part_hour varchar,calendar date)

40、ISsqltxt varchar(2000);cur_no number;cur_val number;errmsg varchar(300);BEGINsqltxt := insert into | interactionhour | (rival_no,area_no,calendar) | with | ls1 as | ( | select | case | when substr(trim(truncatedcaller),1,2)=13 and length(trim(truncatedcaller)=11 then trim(truncatedcaller) | when sub

41、str(trim(truncatedcaller),1,3)=153 and length(trim(truncatedcaller)=11 then trim(truncatedcaller) | when substr(trim(truncatedcaller),1,1) between 2 and 8 and length(trim(truncatedcaller)=7 then trim(truncatedcaller) | else 8688888 | end rival_no, | case | when substr(trim(applix_no),1,2)=13 and len

42、gth(trim(applix_no)=11 then trim(applix_no) | when substr(trim(applix_no),1,3)=153 and length(trim(applix_no)=11 then trim(applix_no) | when substr(trim(applix_no),1,2) between 2 and 8 and length(trim(applix_no)=7 then trim(applix_no) | when substr(trim(applix_no),1,1)=0 and length(trim(applix_no)=1

43、1 then trim(applix_no) | else 8688888 | end applix_no,sarea,darea,20060902 | to_char(calendar,yyyymmddhh24) | calendar from | upcdrname | partition (part_ | part_hour | ) | cdr | where 1=1 | and exists (select x from static_interactionname e where cdr.opc=e.xpc and netname移動) | and exists (select x

44、from static_interactionname e where cdr.dpc=e.xpc and netname=移動) | and cf between 132 and 139), | ls2 as | (select case | when substr(trim(truncatedcalled),1,2)=13 and length(trim(truncatedcalled)=11 then trim(truncatedcalled) | when substr(trim(truncatedcalled),1,3)=153 and length(trim(truncatedcalled)=11 then trim(truncatedcalled) | when substr(trim(truncatedcalled),1,1) between 2 and 8 and length(trim(truncatedcalled)=7 then trim(truncatedcalled) | else 8688888 | end rival_no, | case | when substr(trim(applix_no),1,2)=