oracle job 及存储过程案例

来源：互联网发布：网络推广工作基本做法编辑：程序博客网时间：2024/06/05 22:52

使用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代码  
declare job1 number;  
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 * from dba_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  
variable job1 number;  
/  
  
--创建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代码  
CREATE OR REPLACE PROCEDURE CLEAN_DATA_P IS  
  cursor_name    INTEGER;  
  rows_processed INTEGER;  
  R_CONFIG       EIP_CLEAN_DATA_CONFIG%ROWTYPE; --记录当前行  
  
  R_TABLE_SPACE EIP_CLEAN_DATA_CONFIG%ROWTYPE; --记录tablespace数据配置当前行  
  
  R_CLEAN_DATA varchar2(255);  
  CURSOR C_CONFIG IS  
    SELECT C.ID,  
           C.TABLE_NAME,  
           C.CLEAN_INTERVAL,  
           C.LAST_UPDATE_DATE,  
           C.ENABLED_FLAG  
      FROM EIP_CLEAN_DATA_CONFIG C  
     WHERE C.ENABLED_FLAG = 'Y'  
       AND C.TABLE_NAME != 'EIP_SWITCH_TABLE_SPACE'  
     ORDER BY C.ID; --定义读取配置表不包含表空间切换的游标  
  
  CURSOR C_TABLE_SPACE IS  
    SELECT C.ID,  
           C.TABLE_NAME,  
           C.CLEAN_INTERVAL,  
           C.LAST_UPDATE_DATE,  
           C.ENABLED_FLAG  
      FROM EIP_CLEAN_DATA_CONFIG C  
     WHERE C.ENABLED_FLAG = 'Y'  
       AND C.TABLE_NAME = 'EIP_SWITCH_TABLE_SPACE'  
     ORDER BY C.ID; --定义读取配置表表空间切换的游标  
BEGIN  
  BEGIN  
    OPEN C_CONFIG;  
    LOOP  
      FETCH C_CONFIG  
        INTO R_CONFIG;  
      EXIT WHEN C_CONFIG%NOTFOUND;  
      select to_char(sysdate - R_CONFIG.CLEAN_INTERVAL / 24 / 60,  
                     'yyyy-mm-dd HH24:MI:SS')  
        into R_CLEAN_DATA  
        from dual; --获得当前时间减去时间间隔的时间  
      cursor_name := dbms_sql.open_cursor; --动态执行删除SQL  
      dbms_sql.parse(cursor_name,  
                     'DELETE FROM ' || R_CONFIG.TABLE_NAME ||  
                     ' WHERE LAST_UPDATE_DATE <= :x',  
                     dbms_sql.native);  
      dbms_sql.bind_variable(cursor_name,  
                             ':x',  
                             to_date(R_CLEAN_DATA, 'yyyy-mm-dd HH24:MI:SS'));  
      rows_processed := dbms_sql.execute(cursor_name);  
      dbms_sql.close_cursor(cursor_name);  
    END LOOP;  
    CLOSE C_CONFIG;  
    
    OPEN C_TABLE_SPACE;  
    LOOP  
      FETCH C_TABLE_SPACE  
        INTO R_TABLE_SPACE;  
      EXIT WHEN C_TABLE_SPACE%NOTFOUND;  
        
      IF 0 = R_TABLE_SPACE.CLEAN_INTERVAL  
         THEN  
           
           --对表空间作转移  
           dbms_utility.exec_ddl_statement('ALTER TABLE EIP_PERFORMANCE_CPU MOVE TABLESPACE HQGK_DATA_MV1');  
           dbms_utility.exec_ddl_statement('ALTER TABLE EIP_PERFORMANCE_MEMORY MOVE TABLESPACE HQGK_DATA_MV1');  
           dbms_utility.exec_ddl_statement('ALTER TABLE EIP_PERFORMANCE_PROCESS MOVE TABLESPACE HQGK_DATA_MV1');  
             
           --更新当前操作标识，用于表空间转移  
           UPDATE EIP_CLEAN_DATA_CONFIG C SET C.CLEAN_INTERVAL = 1 WHERE C.TABLE_NAME = 'EIP_SWITCH_TABLE_SPACE';  
      ELSE   
           dbms_utility.exec_ddl_statement('ALTER TABLE EIP_PERFORMANCE_CPU MOVE TABLESPACE HQGK_DATA_MV');  
           dbms_utility.exec_ddl_statement('ALTER TABLE EIP_PERFORMANCE_MEMORY MOVE TABLESPACE HQGK_DATA_MV');  
           dbms_utility.exec_ddl_statement('ALTER TABLE EIP_PERFORMANCE_PROCESS MOVE TABLESPACE HQGK_DATA_MV');  
             
           UPDATE EIP_CLEAN_DATA_CONFIG C SET C.CLEAN_INTERVAL = 0 WHERE C.TABLE_NAME = 'EIP_SWITCH_TABLE_SPACE';  
      END IF;  
        
      --重建索引  
      dbms_utility.exec_ddl_statement('ALTER INDEX PK_EIP_PERFORMANCE_CPU REBUILD');  
      dbms_utility.exec_ddl_statement('ALTER INDEX PK_EIP_PERFORMANCE_MEMORY REBUILD');  
      dbms_utility.exec_ddl_statement('ALTER INDEX PK_EIP_PERFORMANCE_PROCESS REBUILD');  
      COMMIT;  
    END LOOP;  
    CLOSE C_TABLE_SPACE;  
  EXCEPTION  
    WHEN OTHERS THEN  
      dbms_sql.close_cursor(cursor_name);  
      ROLLBACK;  
  END;  
  COMMIT;  
END CLEAN_DATA_P;  
/  

0 0