甘肃酒泉建设银行网站,企业门户网站开发代码,.net网站源码下载,营销网站建设视频每月新增数据百万多条#xff0c;需要定期处理2个主要数据表(test_ad,test_pd)#xff0c;移动非当月数据到历史表中保存数据操作存储过程如下#xff1a;MYPROC.prccreate or replace procedure MYPROC isTableName_AD char(13);TableName_PD char(13);tmp_str varchar2(10…每月新增数据百万多条需要定期处理2个主要数据表(test_ad,test_pd)移动非当月数据到历史表中保存数据操作存储过程如下MYPROC.prccreate or replace procedure MYPROC isTableName_AD char(13);TableName_PD char(13);tmp_str varchar2(100);tmp_str2 varchar2(100);tmp_str3 varchar2(100);tmp_str4 varchar2(100);tmp_str5 varchar2(100);tmp_str6 varchar2(100);tmp_str7 varchar2(100);tmp_str8 varchar2(100);begin--临时表名赋值if TableName_AD is null thenselect PA_AD_||to_char(add_months(sysdate,-1),yyyymm) into TableName_AD from dual;end if;if TableName_PD is null thenselect PA_PD_||to_char(add_months(sysdate,-1),yyyymm) into TableName_PD from dual;end if;--创建(test_ad)历史表tmp_str:create table ||TableName_AD|| as select * from test_ad where patroldateexecute immediate tmp_str;--创建(test_pd)历史表tmp_str2:create table ||TableName_PD|| as select * from test_pd where patroldateexecute immediate tmp_str2;--commit;--创建本月数据临时表tmp_str3:create table temp_ad as select * from test_ad Where patroldate ||to_date(to_char(add_months(last_day(sysdate) 1,-1), yyyymmdd),yyyymmdd)||;execute immediate tmp_str3;tmp_str4:create table temp_pd as select * from test_pd Where patroldate ||to_date(to_char(add_months(last_day(sysdate) 1,-1), yyyymmdd),yyyymmdd)||;execute immediate tmp_str4;--删除本月数据表(test_ad,test_pd)tmp_str5:drop TABLE test_ad;execute immediate tmp_str5;tmp_str6:drop TABLE test_pd;execute immediate tmp_str6;--本月数据临时表重命名表(test_ad,test_pd)tmp_str7:rename temp_ad to test_ad;tmp_str8:rename temp_pd to test_pd;execute immediate tmp_str7;execute immediate tmp_str8;/*--删除主数据表上非本月记录delete from test_ad where patroldate--删除历史表本月记录delete from test_pd where patroldate--删除历史表本月记录tmp_str2:delete from ||tmp_TableName|| where patroldate||to_date(to_char(add_months(last_day(sysdate) 1,-1),yyyymmdd),yyyymmdd);execute immediate tmp_str2;*/--插入操作记录insert into oper_proc_log values(sysdate,pc,0);commit;end MYPROC;--注意在存储过程中使用CREATE或DROP需要显示授权--grant create table to usergrant drop any table to user/注上面有一段被注释的内容是最初的方案但是后来了解到删除大量数据oracle并不释放空间!所以用了现在的方案复制创建表——删除表——重命名。以上存储过程每月3号定期执行declare job1 number;begin--每月3号午夜12点执行MYPROCdbms_job.submit(job1,MYPROC;,sysdate,TRUNC(LAST_DAY(SYSDATE ) 3));commit;end;为了补救意外导致3号午夜12点没有执行MYPROC使用另外一个存储过程验证MYPROC是否执行PASUPPLYPROC.prccreate or replace procedure PASUPPLYPROC isisnull integer;tmp_str varchar2(100);begin--取得本月执行myproc次数if isnull is null thenselect count(*) into isnull from oper_proc_logwhere oper_dateto_date(to_char(add_months(last_day(sysdate) 1,-1), yyyymmdd),yyyymmdd);end if;--无本月执行记录则立即执行myprocif isnull0 thentmp_str:begin myproc; end;;execute immediate tmp_str;commit;end if;end PASUPPLYPROC;--注意在存储过程中使用CREATE或DROP需要显示授权--grant create table to usergrant drop table to user/第二个job定期执行PASUPPLYPROC验证declare job1 number;begin--每月15号午夜12点10分执行PASUPPLYPROCdbms_job.submit(job1,PASUPPLYPROC;,sysdate,TRUNC(LAST_DAY(SYSDATE) 14) (24*6010)/(24*60));commit;end;DBA_JOBS字段(列) 类型 描述JOB NUMBER 任务的唯一标示号LOG_USER VARCHAR2(30) 提交任务的用户PRIV_USER VARCHAR2(30) 赋予任务权限的用户SCHEMA_USER VARCHAR2(30) 对任务作语法分析的用户模式LAST_DATE DATE 最后一次成功运行任务的时间LAST_SEC VARCHAR2(8) 如HH24:MM:SS格式的last_date日期的小时分钟和秒THIS_DATE DATE 正在运行任务的开始时间如果没有运行任务则为nullTHIS_SEC VARCHAR2(8) 如HH24:MM:SS格式的this_date日期的小时分钟和秒NEXT_DATE DATE 下一次定时运行任务的时间NEXT_SEC VARCHAR2(8) 如HH24:MM:SS格式的next_date日期的小时分钟和秒TOTAL_TIME NUMBER 该任务运行所需要的总时间单位为秒BROKEN VARCHAR2(1) 标志参数Y标示任务中断以后不会运行INTERVAL VARCHAR2(200) 用于计算下一运行时间的表达式FAILURES NUMBER 任务运行连续没有成功的次数WHAT VARCHAR2(2000) 执行任务的PL/SQL块CURRENT_SESSION_LABEL RAW MLSLABEL 该任务的信任Oracle会话符CLEARANCE_HI RAW MLSLABEL 该任务可信任的Oracle最大间隙CLEARANCE_LO RAW MLSLABEL 该任务可信任的Oracle最小间隙NLS_ENV VARCHAR2(2000) 任务运行的NLS会话设置MISC_ENV RAW(32) 任务运行的其他一些会话参数描述 INTERVAL参数值 每天午夜12点 TRUNC(SYSDATE 1) 每天早上8点30分 TRUNC(SYSDATE 1) (8*6030)/(24*60) 每星期二中午12点 NEXT_DAY(TRUNC(SYSDATE ), TUESDAY ) 12/24 每个月第一天的午夜12点 TRUNC(LAST_DAY(SYSDATE ) 1) 每个季度最后一天的晚上11点 TRUNC(ADD_MONTHS(SYSDATE 2/24, 3 ), Q ) -1/24 每星期六和日早上6点10分 TRUNC(LEAST(NEXT_DAY(SYSDATE, SATURDAY), NEXT_DAY(SYSDATE, SUNDAY))) (6×6010)/(24×60)
