1.查询 当前库中运行的 job
无脑查
SELECT t.* FROM dba_jobs t
常用筛选
SELECT t.JOB,t.BROKEN,t.INTERVAL,t.what,T.LAST_DATE,T.THIS_DATE,T.NEXT_DATE FROM dba_jobs t
WHERE t.LOG_USER='SPOTFIRE'
ORDER BY T.JOB DESC
2. 创建定时任务
example1:
BEGIN
DBMS_JOB.ISUBMIT(
JOB => 1, /*指定JOB_ID*/
WHAT => ' PRO_COPY_COVER_229(''TAB_MDW_YIELD_FAB_MAP_WEEK'',''VIEW_MDW_YIELD_FAB_MAP_WEEK'',''20180101000000000000'',0);
PRO_COPY_COVER_229(''TAB_MDW_YIELD_FAB_MAP_MONTH'',''VIEW_MDW_YIELD_FAB_MAP_MONTH'',''20180101000000000000'',0);
', /*需要执行的存储过程名称或SQL语句*/
NEXT_DATE => sysdate, /*初次执行时间-立即执行*/
INTERVAL => 'SYSDATE+1' /*每天执行一次*/
);
commit;
END;
example2:
BEGIN DBMS_JOB.ISUBMIT(
JOB => 101,
WHAT => 'PRO_AET_LAST1000_G(''TAB'',''VIEW'');',
NEXT_DATE => SYSDATE ,
Interval => 'TRUNC(next_day(sysdate,2))+7/24');
COMMIT;
END;
3.job 调用&停止&删除&修改
调用
begin
DBMS_JOB.RUN(40); /*40 job的id*/
end;
停止
begin
dbms_job.broken(jobId,true,next_date);
/*停止一个job,jobId, job的ID,
next_date(某一时刻停止)也可是sysdate(立刻停止)。 */
commit;
end;
删除Job
begin
dbms_job.remove(83); /*删除自动执行的job,参数是 job的id*/
commit;
end;
–修改 job 的间隔时间
begin
dbms_job.interval(job,interval); /*job job的ID,interval: 计算下一次任务执行的时间表达式*/
commit;
end;
–修改下一次执行时间
begin
dbms_job.next_date(job,next_date); /*job: job 的ID;nex_date:要修改后的计算下一次执行的时间表达式*/
commit;
end;
–修改定时任务 job 要执行的操作
begin
dbms_job.what(job,'newProcedures();'); /*newProcedures(); 要更改的新操作名称*/
commit;
end;
Job执行procedure范例
CREATE OR REPLACE PROCEDURE SPOTFIRE.PRO_AET_FORCAST(tablename in VARCHAR2,viewname in VARCHAR2)
AS
------------------1. 参数申明部分------------------
---------ETL LOG记录--与数据无关------------------------
INCREASE_CNT NUMBER :=0;
DELETE_CNT NUMBER :=0 ;
UPDATE_CNT NUMBER :=0 ;
INSERT_CNT NUMBER :=0;
ETLBEGINT_TIME DATE := SYSDATE ;
ETLEND_TIME DATE ;
ETL_STATEMENT VARCHAR(100);
-------------------------------------------------------
--查询view中新增数据
v_sql1 VARCHAR2(500):= 'SELECT COUNT (*) FROM '||viewname ;
--删除节点重复数据
v_sql2 VARCHAR2(500):= 'DELETE FROM '||tablename ||' WHERE GLASS_ID IN (SELECT MODEL FROM '||viewname||' )' ;
--复制表字符串
v_sql3 VARCHAR2(500):='insert into '||tablename||' SELECT * FROM '||viewname ;
BEGIN
------------------2. 逻辑处理部分------------------
--查询view中新增数据条数
execute immediate v_sql1 into INCREASE_CNT;
IF INCREASE_CNT <1
THEN
ETL_STATEMENT:='无数据更新';
DBMS_OUTPUT.PUT_LINE('There is no data found in CURSOR YMS_YIELD_CURSOR');
ELSE
--执行删除表重复字段字符串
execute immediate v_sql2;
DELETE_CNT:= SQL%ROWCOUNT;
DBMS_OUTPUT.PUT_LINE('DELETE_CNT = '||DELETE_CNT);
--执行复制表字符串
execute immediate v_sql3;
INSERT_CNT:= SQL%ROWCOUNT;
DBMS_OUTPUT.PUT_LINE('INSERT_CNT = '||INSERT_CNT);
ETL_STATEMENT:='抽取成功';
END IF;
COMMIT;--提交事务
ETLEND_TIME:=SYSDATE;
DBMS_OUTPUT.PUT_LINE ('ETLBEGINT_TIME:'||TO_CHAR(ETLBEGINT_TIME,'YYYY/MM/DD HH24:MI:SS')) ;
DBMS_OUTPUT.PUT_LINE ('ETLEND_TIME:' ||TO_CHAR(ETLEND_TIME ,'YYYY/MM/DD HH24:MI:SS') ) ;
------------------正常记录-------------------------
insert into TAB_ETL_LOG_ALERT (TABLENAME ,VIEWNAME ,ETLFLAG , ETLBEGINTIME, ETLENDTIME ,DELETE_CNT, UPDATE_CNT,INSERT_CNT , MSG ) VALUES
( tablename,viewname ,'新增:'||INSERT_CNT||',删除:'||DELETE_CNT||',更新:'||UPDATE_CNT,ETLBEGINT_TIME,ETLEND_TIME,DELETE_CNT, UPDATE_CNT,INSERT_CNT,tablename || ETL_STATEMENT ) ;
COMMIT ;
------------------3. 异常处理部分------------------
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('There is no data found ');
insert into TAB_ETL_LOG_ALERT (TABLENAME ,VIEWNAME ,ETLFLAG , ETLBEGINTIME, ETLENDTIME ,DELETE_CNT, UPDATE_CNT,INSERT_CNT , MSG ) VALUES
( tablename,viewname ,'新增:'||INSERT_CNT||',删除:'||DELETE_CNT||',更新:'||UPDATE_CNT,ETLBEGINT_TIME,ETLEND_TIME,DELETE_CNT, UPDATE_CNT,INSERT_CNT,tablename ||'无数据更新' ) ;
COMMIT;
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('EXECUTE PROCEDURE PRO_YMS_YIELD FAILED.');
insert into TAB_ETL_LOG_ALERT (TABLENAME ,VIEWNAME ,ETLFLAG , ETLBEGINTIME, ETLENDTIME ,DELETE_CNT, UPDATE_CNT,INSERT_CNT , MSG ) VALUES
( tablename,viewname ,'新增:'||INSERT_CNT||',删除:'||DELETE_CNT||',更新:'||UPDATE_CNT,ETLBEGINT_TIME,ETLEND_TIME,DELETE_CNT, UPDATE_CNT,INSERT_CNT,tablename || '抽取失败' ) ;
COMMIT;
END;