创建 序列 存储过程 job
掌握了 oracle中的 dbms_lock 函数，该函数 主要用于暂停执行的程序
1.用意
|
写job 以10分钟 为单元，前10分钟 从 1到10 插入测试表, 中间10分钟从 11到20插入测试表, 最后10分钟 从 21到30插入测试表. |
2.处理方法
|
2.1 创建序列. 初始值 1 增量 1 最大值无限制 2.2 创建存储过程, 利用for循环顺序读取序列的nextval 插入 测试表.for循环中需要添加 sleep参数 2.3 创建job. 定期调用 存储过程, 参数repeat_interval => 'FREQ=daily;INTERVAL=1', /* every day*/ 2.4 创建测试表 |
3.创建测试表
|
SYS @ prod > CREATE TABLE TEST01(N1 DATE,N2 NUMBER);
Table created. |
4.创建序列
|
--创建序列. 初始值 1 增量 1 最大值无限制 SYS @ prod >CREATE SEQUENCE MY_SEQ01 START WITH 1 INCREMENT BY 1 NOCACHE NOCYCLE;
Sequence created. |
5.创建存储过程
|
--创建存储过程, 利用for循环顺序读取序列的nextval 插入 测试表.for循环中需要添加 sleep参数 SYS @ prod > CREATE OR REPLACE PROCEDURE BATCH_INSERT01 AS BEGIN FOR I IN 1..90 LOOP INSERT INTO TEST01 VALUES(SYSDATE,MY_SEQ01.NEXTVAL); COMMIT; DBMS_LOCK.SLEEP(60); END LOOP; END; /
Procedure created. |
6.测试存储过程
|
SYS @ prod >exec BATCH_INSERT01;
PL/SQL procedure successfully completed. |
7.创建 job
|
方法一： SYS @ prod > DECLARE BEGIN DBMS_SCHEDULER.CREATE_JOB( JOB_NAME =>'JOB_BATCH_INSERT01', JOB_TYPE =>'STORED_PROCEDURE', JOB_ACTION =>'BATCH_INSERT01', START_DATE =>SYSDATE, REPEAT_INTERVAL => 'FREQ=MINUTELY;INTERVAL=1', /* EVERY MINUTE*/ ENABLED=> TRUE, AUTO_DROP=> FALSE, -- END_DATE => ' 25-AUG-15 2.00.00 PM ', COMMENTS => 'MY NEW JOB'); END; /
PL/SQL procedure successfully completed. |
8.强制命令程序暂停
|
--暂停10秒调用 DBMS_LOCK函数强制使程序暂停 BEGIN DBMS_OUTPUT.PUT_LINE(TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS')); DBMS_LOCK.sleep(10); DBMS_OUTPUT.PUT_LINE(TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS')); END;
2015-09-08 11:16:17 2015-09-08 11:16:27 |
9.运行job(不需要运行操作，因为job到指定时间会自动运行哦)
|
SYS @ prod >exec DBMS_SCHEDULER.RUN_JOB(JOB_NAME=>' job_batch_insert01');
PL/SQL procedure successfully completed. |
10.查看对应包
|
包中分别包含了 运行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 |
11.删除job
|
首先停止job SQL> EXEC DBMS_SCHEDULER.STOP_JOB(JOB_NAME=>'JOB_BATCH_INSERT01');
PL/SQL procedure successfully completed.
再删除job SYS @ prod > EXEC DBMS_SCHEDULER.DROP_JOB(job_name=>'job_batch_insert01');
PL/SQL procedure successfully completed. |
12.检查结果
|
检查 测试表中的数据在增加 SQL> SELECT * FROM TEST01;
但是 查询 dba_jobs、user_jobs、all_jobs 没有任何信息
SQL> select * from dba_jobs;
SQL> select * from user_jobs;
no rows selected
SQL> select * from dba_jobs_running;
no rows selected 只在all_scheduler_job_log 中看到该job 运行的相关信息
SQL> set linesize 1000 SQL> set pagesize 1000 SQL> col log_date for a40 SQL> col job_name for a30
SQL> select log_date,job_name,status,destination from all_scheduler_job_log order by log_date desc;
LOG_DATE JOB_NAME STATUS DESTINATION -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 09-9月 -15 09.25.30.060000 上午 +08:00 JOB_BATCH_INSERT01 SUCCEEDED |
13.删除 序列、job、存储过程
|
SYS @ PROD >DROP PROCEDURE BATCH_INSERT01;
PROCEDURE DROPPED. SYS @ PROD >DROP SEQUENCE MY_SEQ01;
SEQUENCE DROPPED. SYS @ PROD >EXEC DBMS_SCHEDULER.DROP_JOB(JOB_NAME=>'JOB_BATCH_INSERT01');
PL/SQL PROCEDURE SUCCESSFULLY COMPLETED. |
记录一下(以下为测试过程中用到的sql语句)
Plsql创建job
|
begin sys.dbms_scheduler.create_job(job_name => 'SJBDZFP.JOB_INSERT_TEST', job_type => 'STORED_PROCEDURE', job_action => 'sjbdzfp.insert_test', start_date => to_date('15-07-2015 10:00:00', 'dd-mm-yyyy hh24:mi:ss'), repeat_interval => 'Freq=Minutely;Interval=2', end_date => to_date('15-07-2015 12:00:00', 'dd-mm-yyyy hh24:mi:ss'), job_class => 'DBMS_JOB$', enabled => true, auto_drop => false, comments => 'My new job'); end; / |
副本1
|
create table test01 (n1 date,n2 number);
DROP TABLE TEST01 PURGE;
CREATE SEQUENCE MY_SEQ01 START WITH 1 INCREMENT BY 1 NOCACHE NOCYCLE;
SELECT * FROM MY_SEQ01.NEXTVAL FROM DUAL;
DROP SEQUENCE MY_SEQ01;
CREATE OR REPLACE PROCEDURE BATCH_INSERT01 AS BEGIN FOR I IN 1..90 LOOP INSERT INTO TEST01 VALUES(SYSDATE,MY_SEQ01.NEXTVAL); COMMIT; DBMS_LOCK.SLEEP(60); END LOOP; END BATCH_INSERT01 ;
DECLARE BEGIN DBMS_SCHEDULER.CREATE_JOB( JOB_NAME =>'JOB_BATCH_INSERT01', JOB_TYPE =>'STORED_PROCEDURE', JOB_ACTION =>'BATCH_INSERT01', START_DATE =>SYSDATE, REPEAT_INTERVAL => 'FREQ=MINUTELY;INTERVAL=1', /* EVERY DAY */ ENABLED=> TRUE, AUTO_DROP=> FALSE, -- END_DATE => ' 25-AUG-15 2.00.00 PM ', COMMENTS => 'MY NEW JOB'); END;
begin DBMS_SCHEDULER.RUN_JOB(JOB_NAME=>'job_batch_insert01',USE_CURRENT_SESSION =>true); end;
BEGIN DBMS_SCHEDULER.job_stopped('job_batch_insert01'); END; |
副本2
|
SELECT * FROM TEST01 ORDER BY N2 ASC;
DELETE FROM TEST01 ;
BEGIN DBMS_SCHEDULER.stop_job('job_batch_insert01'); END;
BEGIN DBMS_SCHEDULER.job_disabled('job_batch_insert01'); END;
BEGIN DBMS_SCHEDULER.drop_job('job_batch_insert01'); END; |
