ORACLEDBMSjob包的使用方法.docx

ORACLE DBMS_Job包的使用方法一、概述1 确保Oracle的工作模式允许启动任务队列管理器 Oracle定时执行“Job Queue”的后台程序是SNP进程，而要启动SNP进程，首先要确保整个系统的模式是可以启动SNP进程的，这需要以DBA的身份去执行如下命令： svrmgrl alter system enable restricted session; 或sql alter system disenable restricted session; 利用如上命令更改系统的会话方式为disenable restricted，为SNP的启动创造条件。 2确保Oracle的系统已经配置了任务队列管理器的启动参数 SNP的启动参数位于Oracle的初始化文件中，该文件放在ORACLE_HOME/dbs路径下，如果Oracle的SID是myora8的话，则初始化文件就是initmyora8.ora，在文件中对SNP启动参数的描述部分如下： job_queue_process=n job_queue_interval=N 第一行定义SNP进程的启动个数为n。系统缺省值为0，正常定义范围为036，根据任务的多少，可以配置不同的数值。 第二行定义系统每隔N秒唤醒该进程一次。系统缺省值为60秒，正常范围为13600秒。事实上，该进程执行完当前任务后，就进入睡眠状态，睡眠一段时间后，由系统的总控负责将其唤醒。 如果该文件中没有上面两行，请按照如上配置添加。配置完成后，需要重新启动数据库，使其生效。注意：如果任务要求执行的间隔很短的话，N的配置也要相应地小一点。 3将任务加入到数据库的任务队列中 调用Oracle的dbms_job包中的存储过程，将任务加入到任务队列中： dbms_job.submit( job out binary_integer, whatinarchar2, next_date indate， intervalinvarchar2, no_parseinboolean) 其中： job：输出变量，是此任务在任务队列中的编号； what：执行的任务的名称及其输入参数； next_date：任务执行的时间； interval：任务执行的时间间隔。 下面详细讨论一下dbms_job.submit中的参数interval。严格地讲，interval是指上一次执行结束到下一次开始执行的时间间隔，当interval设置为null时，该job执行结束后，就被从队列中删除。假如我们需要该job周期性地执行，则要用sysdatem表示。 将任务加入到任务队列之前，要确定执行任务的数据库用户，若用户是scott, 则需要确保该用户拥有执行包dbms_job的权限；若没有，需要以DBA的身份将权利授予scott用户： svrmgrl grant execute on dbms_job to scott; 4将要执行的任务写成存储过程或其他的数据库可执行的pl/sql程序段 例如，我们已经建立了一个存储过程，其名称为my_job，在sql/plus中以scott用户身份登录，执行如下命令： sql variable n number; sql begin dbms_job.submit(:nmy_job;,sysdate, sysdate1/360); commit; end; / 系统提示执行成功。 使用job =:n 也可Sql print :n; 系统打印此任务的编号，例如结果为300。 如上，我们创建了一个每隔4分钟执行一次的任务号为300的任务。可以通过Oracle提供的数据字典user_jobs察看该任务的执行情况： sql select job,next_date,next_sec,failures,broken from user_jobs; 执行结果如下： job next_date next_sec failures broken 300 2000/10/10 11:45:15 0 N 这表示任务号为300的任务，下一次将在2000/10/10 11:45:15执行，此任务的执行失败记录为0次。注意：当执行job出现错误时，Oracle将其记录在日志里，失败次数每次自动加1。当执行失败次数达到16时，Oracle就将该job标志为broken。此后，Oracle不再继续执行它，直到用户调用过程dbms_job.broken，重新设置为not broken，或强制调用dbms_job.run来重新执行它。 除了以上我们讨论的submit存储过程之外，Oracle还提供了其他许多存储过程来操作任务。例如：dbms_job.change 、 dbms_job.what、dbms_erval可以用来修改提交的任务。要想删除该任务，只需运行dbms_job.remove(n)即可，其中n为任务号。=二、创建方法- SQLPLUS 中执行如下语句创建DBMS_JOB- 注意：job1变量名可以定义为使用job关键字，但不建议，容易造成混淆。variable job1 number;begin sys.dbms_job.submit( job = :job1, what = EQUIPMENT.ml_sb_yjglztj(sysdate-1);, next_date = to_date(28-09-2014 01:00:00, dd-mm-yyyy hh24:mi:ss), interval = TRUNC(SYSDATE+1); commit;end;/-PL/SQL DEVELOPER中执行如下语句创建包- 注意：声明的变量名称不能使用JOB关键字,且与SQLPLUS中的job = :job1不同declare job1 number;begin sys.dbms_job.submit( job = job1, what = EQUIPMENT.ml_sb_yjglztj(sysdate-1);, next_date = to_date(28-09-2014 01:00:00, dd-mm-yyyy hh24:mi:ss), interval = TRUNC(SYSDATE+1); commit;end;/- 以上定义中，next_date 表明第一次运行日期及时间，其中第一次运行的时间决定了定时任务何时开始，如上例为每天1:00:00执行，interval为任务调度间隔，上例中为每天一次，另外sysdate+1/1440表示每分钟执行一次，trunc(sysdate)+10/24表示每天上午10点和下午10点各执行一次- 若参数为字符型变量如 v_name，则可以定义如下declare job1 number; v_name1 varchar2(10):=zhangbegin sys.dbms_job.submit( job = job1, what = EQUIPMENT.ml_sb_yjglztj(v_name = |v_name1| );, next_date = to_date(28-09-2014 01:00:00, dd-mm-yyyy hh24:mi:ss), interval = TRUNC(SYSDATE+1); commit;end;/一个简单例子： 1、创建测试表SQL create table a(a date);表已创建。2、创建一个自定义过程SQL create or replace procedure test as2 begin3 insert into a values(sysdate);4 end;5 /过程已创建。3、创建JOBSQL variable job1 number;SQL SQL begin2 dbms_job.submit(:job1,test;,sysdate,sysdate+1/1440);-每天1440分钟，即一分钟运行test过程一次3 COMMIT;4 end;5 /PL/SQL 过程已成功完成。4、运行JOBSQL begin2 dbms_job.run(:job1);3 end;4 /PL/SQL 过程已成功完成。SQL select to_char(a,yyyy/mm/dd hh24:mi:ss) 时间 from a;时间-2001/01/07 23:51:212001/01/07 23:52:222001/01/07 23:53:245、修改下次执行时间（10分钟后执行）SQLbegin2 dbms_job.next_date (:job1,SYSDATE + 10/(24*60);3 end;4 /6、删除JOBSQL begin2 dbms_job.remove(:job1);3 end;4 /PL/SQL 过程已成功完成。*三、DBMS_Job内容DBMS_Job包含以下子过程：Broken()过程。change()过程。Interval()过程。Isubmit()过程。Next_Date()过程。Remove()过程。Run()过程。Submit()过程。User_Export()过程。What()过程。1、Broken()过程更新一个已提交的工作的状态，典型地是用来把一个已破工作标记为未破工作。这个过程有三个参数：job 、broken与next_date。PROCEDURE Broken (job IN binary_integer,Broken IN boolean,next_date IN date :=SYSDATE)job参数是工作号，它在问题中唯一标识工作。broken参数指示此工作是否将标记为破TRUE说明此工作将标记为破，而FLASE说明此工作将标记为未破。next_date参数指示在什么时候此工作将再次运行。此参数缺省值为当前日期和时间。2、Change()过程用来改变指定工作的设置。这个过程有四个参数：job、what 、next_date与interval。PROCEDURE Change (job IN binary_integer,What IN varchar2,next_date IN date,interval IN varchar2)此job参数是一个整数值，它唯一标识此工作。What参数是由此工作运行的一块PL/SQL代码块。next_date参数指示何时此工作将被执行。interval参数指示一个工作重执行的频度。3、Interval()过程用来显式地设置重执行一个工作之间的时间间隔数。这个过程有两个参数：job与interval。PROCEDURE Interval (job IN binary_integer,Interval IN varchar2)job参数标识一个特定的工作。interval参数指示一个工作重执行的频度。4、ISubmit()过程用来用特定的工作号提交一个工作。这个过程有五个参数：job、what、next_date、interval与no_parse。PROCEDURE ISubmit (job IN binary_ineger,What IN varchar2,next_date IN date,interval IN varchar2,no_parse IN booean:=FALSE)这个过程与Submit()过程的唯一区别在于此job参数作为IN型参数传递且包括一个由开发者提供的工作号。如果提供的工作号已被使用，将产生一个错误。5、Next_Date()过程用来显式地设定一个工作的执行时间。这个过程接收两个参数：job与next_date。PROCEDURE Next_Date(job IN binary_ineger,next_date IN date)job标识一个已存在的工作。next_date参数指示了此工作应被执行的日期与时间。6、Remove()过程来删除一个已计划运行的工作。这个过程接收一个参数：PROCEDURE Remove(job IN binary_ineger);job参数唯一地标识一个工作。这个参数的值是由为此工作调用Submit()过程返回的job参数的值。已正在运行的工作不能由调用过程序删除。7、Run()过程用来立即执行一个指定的工作。这个过程只接收一个参数：PROCEDURE Run(job IN binary_ineger) job参数标识将被立即执行的工作。8、使用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参数何时这个工作将被重执行。dbms_job.submit(:job1,test;,sysdate,sysdate+1/1440);sysdate+1/1440:每天1440分钟，即一分钟运行test过程一次TRUNC(SYSDATE+1)+10/24:每天上午10点、晚上10点执行一次no_parse参数指示此工作在提交时或执行时是否应进行语法分析TRUE指示此PL/SQL代码在它第一次执行时应进行语法分析，而FALSE指示本PL/SQL代码应立即进行语法分析。9、User_Export()过程返回一个命令，此命令用来安排一个存在的工作以便此工作能重新提交。此程序有两个参数：job与my_call。PROCEDURE User_Export(job IN binary_ineger,my_call IN OUT varchar2)job参数标识一个安排了的工作。my_call参数包含在它的当前状态重新提交此工作所需要的正文。10、What()过程应许在工作执行时重新设置此正在运行的命令。这个过程接收两个参数：job与what。PROCEDURE What (job IN binary_ineger,What IN OUT varchar2)job参数标识一个存在