SQL_中Job相关操作

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;