作业系统

作业系统JOB
 oracle内部的计划任务
 通过dbms_job包来维护
 
添加JOB的要素
 PROCEDURE SUBMIT
 Argument Name   Type   In/Out Default?
 ------------------------------ ----------------------- ------ --------
 JOB    BINARY_INTEGER  OUT
 WHAT    VARCHAR2  IN
 NEXT_DATE   DATE   IN     DEFAULT
 INTERVAL   VARCHAR2  IN     DEFAULT
 NO_PARSE   BOOLEAN   IN     DEFAULT
 INSTANCE   BINARY_INTEGER  IN     DEFAULT
 FORCE    BOOLEAN   IN     DEFAULT
 1.作业号 通常由系统自建的jobseq序列来取值
 SQL> select jobseq.nextval from dual;

    NEXTVAL
 ----------
  23

 SQL>
 
 2.作业内容
  SQL语句要用单引号括起来
  语句中的单引号要用两个单引号书写 单引号结合性问题
  例如 ename='SCOTT' 写在作业里 ename=''SCOTT''
 3.开始时间
  指定日期时间 启动作业
  
 4.周期性间隔时间

作业相关字典
 dba_jobs dba_jobs_running

查看作业
SQL> select job,LOG_USER,LAST_DATE,NEXT_DATE,INTERVAL,what from user_jobs;

no rows selected

SQL>

留一个为scott涨工资的作业 从当前时间开始 间隔2分钟涨一次
SQL> conn / as sysdba
Connected.
SQL> grant select on jobseq to scott;

Grant succeeded.

SQL> conn scott/seker

SQL> select sys.JOBSEQ.nextval from dual;

   NEXTVAL
----------
 41

SQL> var jobno number
SQL>

begin
 dbms_job.submit(:jobno,
   'update emp set sal=sal+1 where ename=''SCOTT'';',
   sysdate,
   'sysdate+2/24/60');
 commit;
end;
SQL> /

PL/SQL procedure successfully completed.

SQL>
SQL> print jobno

     JOBNO
----------
 42

SQL>

SQL> alter session set nls_date_format='YYYY-MM-DD HH24:MI:SS';

Session altered.

SQL> select job,LOG_USER,LAST_DATE,NEXT_DATE,INTERVAL,what from user_jobs;

       JOB LOG_USER     LAST_DATE       NEXT_DATE
---------- ------------------------------ ------------------- -------------------
INTERVAL
----------------------------------------------------------------------------------------------------
WHAT
----------------------------------------------------------------------------------------------------
 42 SCOTT     2011-04-02 14:10:20 2011-04-02 14:12:20
sysdate+2/24/60
update emp set sal=sal+1 where ename='SCOTT';

SQL>

SQL> !date
2011年 04月 02日 星期六 14:12:10 CST

SQL>
SQL> select ename,sal from emp where ename='SCOTT';

ENAME    SAL
---------- ----------
SCOTT   3011

SQL>

SQL> select ename,sal from emp where ename='SCOTT';

ENAME    SAL
---------- ----------
SCOTT   3012

SQL> !date
2011年 04月 02日 星期六 14:12:47 CST

SQL> select job,LOG_USER,LAST_DATE,NEXT_DATE,INTERVAL,what from user_jobs;

       JOB LOG_USER     LAST_DATE       NEXT_DATE
---------- ------------------------------ ------------------- -------------------
INTERVAL
----------------------------------------------------------------------------------------------------
WHAT
----------------------------------------------------------------------------------------------------
 42 SCOTT     2011-04-02 14:12:25 2011-04-02 14:14:25
sysdate+2/24/60
update emp set sal=sal+1 where ename='SCOTT';

SQL>

 

修改作业
 不想修改的信息直接写NULL
 填写的信息会将原值覆盖
PROCEDURE CHANGE
 Argument Name   Type   In/Out Default?
 ------------------------------ ----------------------- ------ --------
 JOB    BINARY_INTEGER  IN
 WHAT    VARCHAR2  IN
 NEXT_DATE   DATE   IN
 INTERVAL   VARCHAR2  IN
 INSTANCE   BINARY_INTEGER  IN     DEFAULT
 FORCE    BOOLEAN   IN     DEFAULT

 

SQL> select job,LOG_USER,LAST_DATE,NEXT_DATE,INTERVAL,what from user_jobs;

       JOB LOG_USER     LAST_DATE       NEXT_DATE
---------- ------------------------------ ------------------- -------------------
INTERVAL
----------------------------------------------------------------------------------------------------
WHAT
----------------------------------------------------------------------------------------------------
 42 SCOTT     2011-04-02 14:14:30 2011-04-02 14:16:30
sysdate+1
update emp set sal=sal+1 where ename='SCOTT';

SQL> exec dbms_job.change(42,'update emp set sal=sal+10 where ename=''SCOTT'';',NULL,NULL);

PL/SQL procedure successfully completed.

SQL> select job,LOG_USER,LAST_DATE,NEXT_DATE,INTERVAL,what from user_jobs;

       JOB LOG_USER     LAST_DATE       NEXT_DATE
---------- ------------------------------ ------------------- -------------------
INTERVAL
----------------------------------------------------------------------------------------------------
WHAT
----------------------------------------------------------------------------------------------------
 42 SCOTT     2011-04-02 14:14:30 2011-04-02 14:16:30
sysdate+1
update emp set sal=sal+10 where ename='SCOTT';

SQL>

修改下次运行时间
SQL> select job,LOG_USER,LAST_DATE,NEXT_DATE,INTERVAL,what from user_jobs;

       JOB LOG_USER     LAST_DATE       NEXT_DATE
---------- ------------------------------ ------------------- -------------------
INTERVAL
----------------------------------------------------------------------------------------------------
WHAT
----------------------------------------------------------------------------------------------------
 42 SCOTT     2011-04-02 14:14:30 2011-04-02 14:16:30
sysdate+1
update emp set sal=sal+10 where ename='SCOTT';

SQL> exec dbms_job.next_date(42,sysdate+5/24/60);

PL/SQL procedure successfully completed.

SQL> select job,LOG_USER,LAST_DATE,NEXT_DATE,INTERVAL,what from user_jobs;

       JOB LOG_USER     LAST_DATE       NEXT_DATE
---------- ------------------------------ ------------------- -------------------
INTERVAL
----------------------------------------------------------------------------------------------------
WHAT
----------------------------------------------------------------------------------------------------
 42 SCOTT     2011-04-02 14:14:30 2011-04-02 14:24:46
sysdate+1
update emp set sal=sal+10 where ename='SCOTT';

SQL>

将间隔时间改成NULL 既是下次运行后就不在运行

SQL> exec dbms_job.INTERVAL(42,null);

PL/SQL procedure successfully completed.

SQL>

 

停止和启动一个作业

SQL> select job,broken from user_jobs;

       JOB B
---------- -
 42 N

SQL> exec dbms_job.broken(42,TRUE);

PL/SQL procedure successfully completed.

SQL> select job,broken from user_jobs;

       JOB B
---------- -
 42 Y

SQL>
SQL> exec dbms_job.broken(42,false);

PL/SQL procedure successfully completed.

SQL> select job,broken from user_jobs;

       JOB B
---------- -
 42 N

SQL>

立即运行作业

SQL> exec dbms_job.run(42);

PL/SQL procedure successfully completed.

SQL>

移除作业
SQL> exec dbms_job.remove(42);

PL/SQL procedure successfully completed.

SQL>

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/24756465/viewspace-717789/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/24756465/viewspace-717789/