应用Oracle job和存储过程

每月新增数据百万多条，需要定期处理2个主要数据表（test_ad,test_pd），移动非当月数据到历史表中保存数据操作存储过程如下：MYPROC.prccreate or replace procedure MYPROC is
TableName_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 then
select "PA_AD_"||to_char（add_months（sysdate,-1）,"yyyymm"） into TableName_AD from dual;
end if;
if TableName_PD is null then
select "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 patroldate<"""||to_date（to_char（add_months（last_day（sysdate） +1,-1）, "yyyy-mm-dd"）,"yyyy-mm-dd"）||"""";
execute immediate tmp_str;--创建（test_pd）历史表
tmp_str2:="create table "||TableName_PD||" as select * from test_pd where patroldate<"""||to_date（to_char（add_months（last_day（sysdate） +1,-1）, "yyyy-mm-dd"）,"yyyy-mm-dd"）||"""";
execute 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<to_date（to_char（add_months（last_day（sysdate） +1,-1）, "yyyymmdd"）,"yyyymmdd"）;
--删除历史表本月记录
delete from test_pd where patroldate<to_date（to_char（add_months（last_day（sysdate） +1,-1）, "yyyymmdd"）,"yyyymmdd"）;--删除历史表本月记录
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 user，grant drop any table to user
/注：上面有一段被注释的内容，是最初的方案，但是后来了解到：删除大量数据，Oracle并不释放空间！所以用了现在的方案，复制创建表——>删除表——>重命名。以上存储过程每月3号定期执行declare job1 number;
begin
--每月3号午夜12点执行MYPROC
dbms_job.submit（job1,"MYPROC;",sysdate,"TRUNC（LAST_DAY（SYSDATE ） + 3）"）;
commit;
end;为了补救意外导致3号午夜12点没有执行MYPROC使用另外一个存储过程验证MYPROC是否执行PASUPPLYPROC.prccreate or replace procedure PASUPPLYPROC is
isnull integer;
tmp_str varchar2（100）;
begin
--取得本月执行myproc次数
if isnull is null then
select count（*） into isnull from oper_proc_log
where oper_date>=to_date（to_char（add_months（last_day（sysdate） +1,-1）, "yyyymmdd"）,"yyyymmdd"）;
end if;
--无本月执行记录则立即执行myproc
if isnull=0 then
tmp_str:="begin myproc; end;";
execute immediate tmp_str;
commit;
end if;
end PASUPPLYPROC;--=============注意！在存储过程中使用CREATE或DROP需要显示授权==========
--grant create table to user，grant drop table to user
/第二个job定期执行PASUPPLYPROC验证declare job1 number;
begin
--每月15号午夜12点10分执行PASUPPLYPROC
dbms_job.submit（job1,"PASUPPLYPROC;",sysdate,"TRUNC（LAST_DAY（SYSDATE） + 14） +（24*60+10）/（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            正在运行任务的开始时间，如果没有运行任务则为null
THIS_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*60+30）/（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×60+10）/（24×60）"Oracle slash符号的使用一个TB级Oracle数据库的备份时间及备份记录相关资讯 Oracle教程

Oracle中纯数字的varchar2类型和（07/29/2015 07:20:43）
Oracle教程：Oracle中查看DBLink密（07/29/2015 07:16:55）
[Oracle] SQL*Loader 详细使用教程（08/11/2013 21:30:36）

Oracle教程：Oracle中kill死锁进程（07/29/2015 07:18:28）
Oracle教程：ORA-25153 临时表空间（07/29/2015 07:13:37）
Oracle教程之管理安全和资源（04/08/2013 11:39:32）

本文评论查看全部评论（0）