oracle job 及存储过程案例-优快云博客

使用Submit()过程，工作被正常地计划好。
这个过程有五个参数：job、what、next_date、interval与no_parse。

PROCEDURE Submit ( job OUT binary_ineger,
What IN varchar2,
next_date IN date,
interval IN varchar2,
no_parse IN booean:=FALSE)

job参数是由Submit()过程返回的binary_ineger。这个值用来唯一标识一个工作。
what参数是将被执行的PL/SQL代码块。
next_date参数指识何时将运行这个工作。
interval参数何时这个工作将被重执行。
no_parse参数指示此工作在提交时或执行时是否应进行语法分析——TRUE
指示此PL/SQL代码在它第一次执行时应进行语法分析，
而FALSE指示本PL/SQL代码应立即进行语法分析。

在command window窗口中执行下面脚本

    Oracle代码 
    
  
 declarejob1number; 
 begin 
 dbms_job.submit(job=>:job1, 
 what=>'CLEAN_DATA_P;', 
 next_date=>sysdate, 
 interval=>'sysdate+1/1440');--每天1440分钟，即一分钟运行CLEAN_DATA_P存储过程一次 
 commit; 
 dbms_job.run(:job1); 
 end; 
 / 

查看创建的job
查看相关job信息
1、相关视图
dba_jobs
all_jobs
user_jobs
dba_jobs_running 包含正在运行job相关信息。
如：

    Oracle代码 
    
 select*fromdba_jobs

2、运行JOB
说明：Run()过程用来立即执行一个指定的工作。这个过程只接收一个参数：

    Oracle代码 
    
  
 begin 
 dbms_job.run(:job);--:job为dba_jobs里面自己需要运行的jobid 
 end; 
 / 

3、删除JOB

    Oracle代码 
    
  
 begin 
 dbms_job.remove(:job);--:job为dba_jobs里面自己需要运行的jobid 
 end; 
 / 

    Oracle代码 
    
  
 --绑定jobid 
 variablejob1number; 
 / 
  
 --创建JOB每分钟执行一次 
 begin 
 dbms_job.submit(job=>:job1, 
 what=>'CLEAN_DATA_P;', 
 next_date=>sysdate, 
 interval=>'sysdate+1/1440'); 
 commit; 
 end; 
 / 
  
 --执行JOB 
 begin 
 dbms_job.run(:job1); 
 end; 
 / 
  
 --删除JOB 
 begin 
 dbms_job.remove(:job1);--:job为dba_jobs里面自己需要运行的jobid 
 end; 
 / 
  
 --创建JOB每天凌晨1点执行一次 
 begin 
 dbms_job.submit(job=>:job1, 
 what=>'CLEAN_DATA_P;', 
 next_date=>sysdate, 
 interval=>'TRUNC(sysdate)+1+1/(24)'); 
 commit; 
 end; 
 / 

附：CLEAN_DATA_P存储过程

    Oracle代码 
    
  
 CREATEORREPLACEPROCEDURECLEAN_DATA_PIS 
 cursor_nameINTEGER; 
 rows_processedINTEGER; 
 R_CONFIGEIP_CLEAN_DATA_CONFIG%ROWTYPE;--记录当前行 
  
 R_TABLE_SPACEEIP_CLEAN_DATA_CONFIG%ROWTYPE;--记录tablespace数据配置当前行 
  
 R_CLEAN_DATAvarchar2(255); 
 CURSORC_CONFIGIS 
 SELECTC.ID, 
 C.TABLE_NAME, 
 C.CLEAN_INTERVAL, 
 C.LAST_UPDATE_DATE, 
 C.ENABLED_FLAG 
 FROMEIP_CLEAN_DATA_CONFIGC 
 WHEREC.ENABLED_FLAG='Y' 
 ANDC.TABLE_NAME!='EIP_SWITCH_TABLE_SPACE' 
 ORDERBYC.ID;--定义读取配置表不包含表空间切换的游标 
  
 CURSORC_TABLE_SPACEIS 
 SELECTC.ID, 
 C.TABLE_NAME, 
 C.CLEAN_INTERVAL, 
 C.LAST_UPDATE_DATE, 
 C.ENABLED_FLAG 
 FROMEIP_CLEAN_DATA_CONFIGC 
 WHEREC.ENABLED_FLAG='Y' 
 ANDC.TABLE_NAME='EIP_SWITCH_TABLE_SPACE' 
 ORDERBYC.ID;--定义读取配置表表空间切换的游标 
 BEGIN 
 BEGIN 
 OPENC_CONFIG; 
 LOOP 
 FETCHC_CONFIG 
 INTOR_CONFIG; 
 EXITWHENC_CONFIG%NOTFOUND; 
 selectto_char(sysdate-R_CONFIG.CLEAN_INTERVAL/24/60, 
 'yyyy-mm-ddHH24:MI:SS') 
 intoR_CLEAN_DATA 
 fromdual;--获得当前时间减去时间间隔的时间 
 cursor_name:=dbms_sql.open_cursor;--动态执行删除SQL 
 dbms_sql.parse(cursor_name, 
 'DELETEFROM'||R_CONFIG.TABLE_NAME|| 
 'WHERELAST_UPDATE_DATE<=:x', 
 dbms_sql.native); 
 dbms_sql.bind_variable(cursor_name, 
 ':x', 
 to_date(R_CLEAN_DATA,'yyyy-mm-ddHH24:MI:SS')); 
 rows_processed:=dbms_sql.execute(cursor_name); 
 dbms_sql.close_cursor(cursor_name); 
 ENDLOOP; 
 CLOSEC_CONFIG; 
  
 OPENC_TABLE_SPACE; 
 LOOP 
 FETCHC_TABLE_SPACE 
 INTOR_TABLE_SPACE; 
 EXITWHENC_TABLE_SPACE%NOTFOUND; 
  
 IF0=R_TABLE_SPACE.CLEAN_INTERVAL 
 THEN 
  
 --对表空间作转移 
 dbms_utility.exec_ddl_statement('ALTERTABLEEIP_PERFORMANCE_CPUMOVETABLESPACEHQGK_DATA_MV1'); 
 dbms_utility.exec_ddl_statement('ALTERTABLEEIP_PERFORMANCE_MEMORYMOVETABLESPACEHQGK_DATA_MV1'); 
 dbms_utility.exec_ddl_statement('ALTERTABLEEIP_PERFORMANCE_PROCESSMOVETABLESPACEHQGK_DATA_MV1'); 
  
 --更新当前操作标识，用于表空间转移 
 UPDATEEIP_CLEAN_DATA_CONFIGCSETC.CLEAN_INTERVAL=1WHEREC.TABLE_NAME='EIP_SWITCH_TABLE_SPACE'; 
 ELSE 
 dbms_utility.exec_ddl_statement('ALTERTABLEEIP_PERFORMANCE_CPUMOVETABLESPACEHQGK_DATA_MV'); 
 dbms_utility.exec_ddl_statement('ALTERTABLEEIP_PERFORMANCE_MEMORYMOVETABLESPACEHQGK_DATA_MV'); 
 dbms_utility.exec_ddl_statement('ALTERTABLEEIP_PERFORMANCE_PROCESSMOVETABLESPACEHQGK_DATA_MV'); 
  
 UPDATEEIP_CLEAN_DATA_CONFIGCSETC.CLEAN_INTERVAL=0WHEREC.TABLE_NAME='EIP_SWITCH_TABLE_SPACE'; 
 ENDIF; 
  
 --重建索引 
 dbms_utility.exec_ddl_statement('ALTERINDEXPK_EIP_PERFORMANCE_CPUREBUILD'); 
 dbms_utility.exec_ddl_statement('ALTERINDEXPK_EIP_PERFORMANCE_MEMORYREBUILD'); 
 dbms_utility.exec_ddl_statement('ALTERINDEXPK_EIP_PERFORMANCE_PROCESSREBUILD'); 
 COMMIT; 
 ENDLOOP; 
 CLOSEC_TABLE_SPACE; 
 EXCEPTION 
 WHENOTHERSTHEN 
 dbms_sql.close_cursor(cursor_name); 
 ROLLBACK; 
 END; 
 COMMIT; 
 ENDCLEAN_DATA_P; 
 / 