存储过程实例oracleWord文档下载推荐.docx

存储过程实例oracleWord文档下载推荐.docx

《存储过程实例oracleWord文档下载推荐.docx》由会员分享，可在线阅读，更多相关《存储过程实例oracleWord文档下载推荐.docx（19页珍藏版）》请在冰豆网上搜索。

=sysdate-1/24;

part_hour:

=to_char（sysdate-1/24,'

hh24'

）;

part_day:

=to_char（sysdate-1,'

dd'

interactionhour:

='

interactionhour'

||to_char（calendar,'

yyyymm'

interactionday:

interactionday'

interactionday1:

interactionday1'

upcdrname:

upcdr'

yymmdd'

proc_interactionhour（interactionhour,upcdrname,part_hour,calendar）;

ifto_char（sysdate,'

）='

03'

then

proc_interactionday（interactionhour,interactionday,interactionday1,part_day）;

endif;

04'

proc_interactiondayshow（interactionday1,interactionday）;

insertintoappmsg

values（'

成功执行proc_batch'

'

proc_batch'

sysdate）;

commit;

EXCEPTION

WHENOTHERSTHEN

rollback;

errmsg:

=substr（sqlerrm,1,300）;

insertintoappmsg

values（'

没有成功执行proc_batch'

commit;

ENDproc_batch;

/

二：

CREATEORREPLACEPROCEDUREproc_interactionday（interactionhourvarchar,interactiondayvarchar,interactionday1varchar,part_dayvarchar）

sqltxt1varchar（2000）;

sqltxt2varchar（2000）;

cur_nonumber;

cur_valnumber;

sqltxt1:

='

insertinto'

||interactionday

||'

（rival_no,area_no,calendar）'

selectdistinctrival_no,area_no,substr（calendar,1,8）'

calendarfrom'

||interactionhour

partition（part_'

||part_day

）'

||'

where1=1'

andsubstr（rival_no,1,2）='

'

'

;

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:

||interactionday1

||interactionhour

andsubstr（rival_no,1,3）in（'

130'

131'

132'

133'

134'

dbms_sql.parse（cur_no,sqltxt2,dbms_sql.native）;

values（'

成功执行proc_interactionday'

proc_interactionday'

errmsg:

insertintoappmsg

没有成功执行proc_interactionday,原因是:

||errmsg,'

sysdate）;

ENDproc_interactionday;

三：

CREATEORREPLACEPROCEDUREproc_interactiondayshow（interactionday1varchar,interactiondayvarchar）

sqltxt3varchar（2000）;

sqltxt4varchar（2000）;

interactioncodedayshow（supplier,code,tos,area_name,part_name,total,calendar）'

selectb.supplier,b.code,b.tos,b.area_name,b.part_name,count（*）total,a.calendar'

from'

a,'

static_interactionno1b'

andto_number（substr（a.rival_no,1,7））=b.code'

groupbyb.code,b.supplier,b.tos,b.area_name,b.part_name,a.calendar'

interactioncodedayshow（supplier,area_name,part_name,tos,code,total,calendar）'

selectc.supplier,c.area_name,c.part_name,c.tos,c.code,d.total,d.calendar'

fromstatic_interactionnoc,'

（selectsubstr（rival_no,1,6）code,count（*）total,calendar'

groupbysubstr（rival_no,1,6）,calendar'

unionall'

selectsubstr（rival_no,1,7）code,count（*）total,calendar'

groupbysubstr（rival_no,1,7）,calendar'

selectsubstr（rival_no,1,8）code,count（*）total,calendar'

groupbysubstr（rival_no,1,8）,calendar）d'

wherec.area_no||c.code=d.code'

orderbyc.area_no'

sqltxt3:

insertinto'

interactionpartdayshow（supplier,area_name,part_name,tos,total,calendar）'

selectsupplier,area_name,part_name,tos,sum（total）total,calendar'

from'

interactioncodedayshow'

groupbypart_name,supplier,tos,calendar,area_name'

dbms_sql.parse（cur_no,sqltxt3,dbms_sql.native）;

sqltxt4:

insertinto'

interact