写job 每三个小时 为单元,头一个三个小时 从 1到100 插入测试表 第二个三个小时 从 101到200插入测试表 第三个三个小时 从 301到400插入测试表.
2.1 创建序列. 初始值 1 增量 1 最大值无限制
2.2 创建存储过程, 利用for循环顺序读取序列的nextval 插入 测试表.
2.3 创建job. 定期调用 存储过程, 参数repeat_interval => 'FREQ=minutely;INTERVAL=2',
/* every 2 minute */
SYS @ prod >CREATE SEQUENCE seq1
START WITH 1
INCREMENT BY 1
NOCACHE
NOCYCLE;
Sequence created.
调用序列给测试表 插入数据
SYS @ prod >CREATE OR REPLACE procedure insert_t1 is
begin
for i in 1..100 loop
insert into t1 values(seq1.nextval);
commit;
end loop;
end;
/
Procedure created.
SYS @ prod >exec insert_t1;
PL/SQL procedure successfully completed.
方法一:
SYS @ prod >declare
begin
dbms_scheduler.create_job(
job_name =>'job_insert_t1',
job_type =>'stored_procedure',
job_action =>'sys.insert_t1',
start_date =>'05-JUN-14 10.50.00 AM ',
repeat_interval => 'FREQ=minutely;INTERVAL=2', /* every minute */
enabled=> true,
end_date => '06-JUN-14 10.50.00 AM ',
comments => 'My new job');
END;
/
PL/SQL procedure successfully completed.
方法二:
SYS @ prod >BEGIN
DBMS_SCHEDULER.CREATE_JOB(
job_name =>'job_insert_t1',
job_type =>'STORED_PROCEDURE',
job_action =>'insert_t1',
repeat_interval =>'FREQ=DAILY; BYHOUR=18 ;BYMINUTE=30', /* every day*/
enabled=> true,
comments => 'job');
END;
--每天18/30/00点执行job_insert_t1过程
SYS @ prod >exec DBMS_SCHEDULER.RUN_JOB(JOB_NAME=>'job_insert_t1',USE_CURRENT_SESSION =>true);
PL/SQL procedure successfully completed.
包中分别包含了 运行job、删除job等存储过程
SYS @ prod >desc DBMS_SCHEDULER;
PROCEDURE RUN_JOB
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
JOB_NAME VARCHAR2 IN
USE_CURRENT_SESSION BOOLEAN IN DEFAULT
PROCEDURE DROP_JOB
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
JOB_NAME VARCHAR2 IN
FORCE BOOLEAN IN DEFAULT
SYS @ prod >exec dbms_scheduler.drop_job(job_name=>'job_insert_t1');
PL/SQL procedure successfully completed.
检查 测试表中的数据确实在增加
select * from t1 order by n1 desc;
--最大值 1100
但是 查询 dba_jobs、user_jobs、all_jobs 没有任何信息
select * from dba_jobs;
select * from user_jobs;
select * from dba_jobs_running;
只在all_scheduler_job_log 中看到该job 运行的相关信息
select * from ALL_SCHEDULER_JOB_LOG order by log_date desc;
SYS @ prod >drop procedure insert_t1;
Procedure dropped.
SYS @ prod >drop sequence seq1;
Sequence dropped.
SYS @ prod >exec dbms_scheduler.drop_job(job_name=>'job_insert_t1');
PL/SQL procedure successfully completed.
转载于:https://www.cnblogs.com/iyoume2008/p/4646736.html