oracle SCHEDULER

2024-06-18 13:12

文章标签 oracle scheduler

本文主要是介绍oracle SCHEDULER，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

从Oracle 10g开始，推荐使用DBMS_SCHEDULER包，因为它提供了更强大的功能和灵活性，包括更复杂的调度规则、依赖管理和事件驱动等

1. 用法

DBMS_SCHEDULER.CREATE_JOB (job_name             IN VARCHAR2,job_type             IN VARCHAR2,job_action           IN VARCHAR2,number_of_arguments  IN PLS_INTEGER              DEFAULT 0,start_date           IN TIMESTAMP WITH TIME ZONE DEFAULT NULL,repeat_interval      IN VARCHAR2                 DEFAULT NULL,end_date             IN TIMESTAMP WITH TIME ZONE DEFAULT NULL,job_class            IN VARCHAR2                 DEFAULT 'DEFAULT_JOB_CLASS',enabled              IN BOOLEAN                  DEFAULT FALSE,auto_drop            IN BOOLEAN                  DEFAULT TRUE,comments             IN VARCHAR2                 DEFAULT NULL,credential_name      IN VARCHAR2                 DEFAULT NULL,destination_name     IN VARCHAR2                 DEFAULT NULL);

2. 案例

GRANT CREATE PROCEDURE TO test;      
GRANT CREATE JOB TO test;    1. 创建测试表
CREATE TABLE job_log (execution_time date
);2. 创建定时任务
BEGINDBMS_SCHEDULER.CREATE_JOB (job_name             => 'MyMinuteJob',job_type             => 'PLSQL_BLOCK',job_action           => 'BEGIN INSERT INTO job_log(execution_time) VALUES (sysdate); END;',start_date           => SYSTIMESTAMP,repeat_interval      => 'FREQ=MINUTELY;', -- 每分钟执行一次end_date             => NULL, -- 未设置结束日期，表示一直持续enabled              => TRUE, -- 创建后立即启用comments             => 'Logs current timestamp every minute.');COMMIT; -- 提交事务以保存更改
END;
/3. 验证任务执行SELECT * FROM job_log ORDER BY execution_time DESC;4.启用任务
BEGINDBMS_SCHEDULER.ENABLE('MyMinuteJob');  -- 启用END;
/5. 查看任务状态
SELECT * FROM DBA_SCHEDULER_JOBS  WHERE JOB_NAME = 'MYMINUTEJOB';6. 查看任务执行日志SELECT * FROM USER_SCHEDULER_JOB_RUN_DETAILS WHERE JOB_NAME = 'MYMINUTEJOB';7. 关闭SCHEDULERBEGINDBMS_SCHEDULER.DISABLE('MyMinuteJob'); 
END;
/8. 删除SCHEDULER
BEGINDBMS_SCHEDULER.DROP_JOB ('MyMinuteJob');
END;
/9. 验证SCHEDULER
SELECT * FROM DBA_SCHEDULER_JOBS  WHERE JOB_NAME = 'MYMINUTEJOB';