存储过程实例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,'h

2、h24');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

3、 := 'upcdr'|to_char(calendar,'yymmdd');proc_interactionhour (interactionhour,upcdrname ,part_hour ,calendar);if to_char(sysdate,'hh24')='03' thenproc_interactionday (interactionhour ,interactionday ,interactionday1 ,part_day );end if;if to_char(sysdate,'hh24')

4、='04' thenproc_interactiondayshow (interactionday1 ,interactionday );end if;insert into appmsgvalues('成功执行proc_batch','proc_batch',sysdate);commit; EXCEPTION WHEN OTHERS THEN rollback; errmsg:= substr(sqlerrm,1,300); insert into appmsg values ('没有成功执行proc_batch','

5、proc_batch',sysdate); commit; 1 / 15END proc_batch;/二：CREATE OR REPLACE PROCEDURE proc_interactionday (interactionhour varchar,interactionday varchar,interactionday1 varchar,part_day varchar)ISsqltxt1 varchar(2000);sqltxt2 varchar(2000); cur_no number;cur_val number;errmsg varchar(300);BEGINsqlt

6、xt1 :=' 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,

7、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

8、,substr(calendar,1,8) '|' calendar from '| interactionhour |' partition (part_'| part_day|' ) ' |' where 1=1 '|' and substr(rival_no,1,3) in (''130'',''131'',''132'',''133'',''134''

9、; ) '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 values('成功执行proc_interactionday','proc_interactionday',sysdate); commit; EXCEPTION WHEN OTHERS THEN rollback; errmsg

10、 := substr(sqlerrm,1,300); insert into appmsg values('没有成功执行proc_interactionday,原因是:'|errmsg,'proc_interactionday',sysdate) ; commit;END proc_interactionday;/三：CREATE OR REPLACE PROCEDURE proc_interactiondayshow (interactionday1 varchar,interactionday varchar)ISsqltxt1 varchar(2000);

11、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,total,calendar) '|' select b.supplier, b.code,b.tos,b.area_name,b.part_

12、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.open_

13、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,

14、d.total,d.calendar '|' from 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,cale

15、ndar '|' from '| interactionday|' group by substr(rival_no,1,7),calendar '|' union all '|' 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.cod

16、e=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.close_cursor(cur_no);sqltxt3 :='insert into'|' interactionpartdayshow (supplier,area_name,part_name,tos, total,calendar )

17、9; |' 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;dbms_sql.parse(cur_no,sqltxt3,dbms_sql.native);cur_val:=dbms_sql.execute(c

18、ur_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 supplier,tos,calendar,area_name

19、 '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('成功执行proc_interactiondayshow','proc_interactiondayshow',sysdate); commit; EXCEPTION WHEN OTHERS THEN rollback; e

20、rrmsg := substr(sqlerrm,1,300); insert into appmsg values('没有成功执行proc_interactionday,原因是:'|errmsg,'proc_interactionday',sysdate) ; commit;END proc_interactiondayshow;/四：CREATE OR REPLACE PROCEDURE proc_interactionhour (interactionhour varchar,upcdrname varchar,part_hour varchar,calen

21、dar date)ISsqltxt 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&

22、#39;' and length(trim(truncatedcaller)=11 then trim(truncatedcaller) '|' when substr(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(trunca

23、tedcaller)=7 then trim(truncatedcaller) '|' 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&

24、#39;' 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(applix_no) '|'

25、; 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_interaction

26、name s where cdr.opc=s.xpc and netname<>''移动'')'|' and exists (select ''x'' from static_interactionname s where cdr.dpc=s.xpc and netname=''移动'')'|' and cf between 132 and 139 '|' ), ' |' ls2 as ' |' (selec

27、t 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 s

28、ubstr(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

29、(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) betwe

30、en 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 from '|upcdrname |' partition (part_'|part_hour |' ) cdr '|' where 1=1 &#

31、39;|' 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=''移动'')'|' an

32、d cf between 132 and 139 '|' ) '|' 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 '|&#

33、39; select rival_no,darea,calendar '|' from ls2 '|' where darea between 431 and 439 '|' union '|' select applix_no,darea,calendar '|' from ls2 '|' where darea between 431 and 439 ' cur_no:=dbms_sql.open_cursor; dbms_sql.parse(cur_no,sqltxt,dbms_sql

34、.native); cur_val:=dbms_sql.execute(cur_no); dbms_sql.close_cursor(cur_no); commit; insert into appmsg values('成功执行proc_interactionhour','proc_interactionhour',sysdate); commit;EXCEPTION WHEN OTHERS THEN rollback; errmsg := substr(sqlerrm,1,300); insert into appmsg values('没有成功执行

35、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 varchar(20000);cur_no number;cur_val number;errms

36、g 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(truncatedca

37、ller) '|' when substr(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 ''0''|sarea|trim(truncatedcaller)

38、 '|' 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_no)=11

39、 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 '

40、;'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 wher

41、e cdr.opc=s.xpc and netname<>''移动'')'|' and exists (select ''x'' from static_interactionname s where cdr.dpc=s.xpc and netname=''移动'')'|' and cf between 132 and 139 '|' ), ' |' ls2 as ' |' (select case '

42、;|' 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(

43、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

44、_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(appl

45、ix_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,'yyyymmddhh24') |' calendar from '|upcdrname |' partition (part_'|pa

46、rt_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

47、netname=''移动'')'|' and cf between 132 and 139 '|' ) '|' select rival_no,sarea,calendar '|' from ls1 '|' where sarea between 431 and 439 '|' union '|' select applix_no,darea,calendar '|' from ls1 '|' where darea b

48、etween 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 between 431 and 439 ' cur_no:=dbms_sql.open_

49、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('成功执行proc_interactionhour','proc_interactionhour',sysdate); commit;EXCEPTION WHEN OTHERS THEN rollback; errmsg := substr(sqlerrm,

50、1,300); insert into appmsg values('没有成功执行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) ISsqltxt varchar(2000);c

51、ur_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'&#

52、39; and length(trim(truncatedcaller)=11 then trim(truncatedcaller) ' |' when substr(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(truncatedc

53、aller)=7 then trim(truncatedcaller) ' |' else ''8688888'' ' |' 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)='&#

54、39;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)=11 then trim(applix_no) &

55、#39; |' else ''8688888'' ' |' end applix_no,sarea,darea,''20060902'' ' | to_char(calendar,'yyyymmddhh24') |' calendar from ' | upcdrname |' partition (part_ ' | part_hour |' ) ' |' cdr ' |' where 1=1 ' |&

56、#39; and exists (select ''x'' from static_interactionname e where cdr.opc=e.xpc and netname<>''移动'') ' |' and exists (select ''x'' from static_interactionname e where cdr.dpc=e.xpc and netname=''移动'') ' |' and cf b

57、etween 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(tru

58、ncatedcalled)=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)=''13'' and length(trim(applix_no)=11 then trim(applix_no) ' |' when subs