达梦数据库dbms_job.interval(179, 'sysdate + 1/24')报错该怎么办？50

天真有心锁 2024-04-09 14:43:01  46363

分类专栏：问题 问题分类： 其它未定义问题

已结题

• 

  1843880570 进阶 2024-04-09 15:40:51

  使用DBMS_JOB创建作业
  DBMS_JOB是DM兼容Oracle定时任务功能的系统包，按指定的时间或间隔执行用户定义的作业。并兼容DBA_JOBS、USER_JOBS、DBA_JOBS_RUNNING等Oracle作业相关的数据字典视图。

  创建DBMS_JOB系统包

  DM数据库安装完成之后默认不创建DBMS_JOB系统包，如果要使用DBMS_JOB系统包需要手工创建，创建或删除DBMS_JOB系统包语法如下：

  void SP_INIT_JOB_SYS(
  CREATE_FLAG INT
  )
  参数详解
  CREATE_FLAG为1时表示创建DBMS_JOB包；为0表示删除该系统包。
  DBMS_JOB相关方法

  DBMS_JOB创建JOB有两种过程，ISUBMIT和SUBMIT，两者的区别是ISUBMIT的JOB编码是IN参数，由编码员指定，SUBMIT的JOB编码是OUT参数，由系统自动生成。DBMS_JOB提供创建、修改、删除、禁用JOB等功能，详细功能请参考《DM8系统包使用手册》。

  ISUBMIT过程

  ISUBMIT使用特定的JOB编码提交一个作业。这个过程与SUBMIT过程相似，唯一区别是JOB参数作为IN型参数传递（SUBMIT过程JOB参数是OUT类型，由系统自动生产JOB编号）。如果提供的工作号已被使用，将产生一个错误。语法如下：

  PROCEDUREISUBMIT(
     JOBIN INT,
     WHATIN VARCHAR2(1800),
      NEXT_DATEIN DATETIME,
      "INTERVAL"IN VARCHAR(100),
      NO_PARSEIN BOOLEAN DEFAULTFALSE
  );
  参数详解

  JOB用户指定的工作编号，不能为负数，且必须是不存在的工作编号，唯一。

  WHAT表示将被执行的代码，可以是DMSQL代码块和存储过程。

  NEXT_DATEJOB下次运行时间，即何时将运行这个工作。

  "INTERVAL"作业间隔，即何时这个工作将被重执行。

  NO_PARSE表示此工作在提交时或执行时是否进行语法分析，TRUE指示此PL/SQL代码在它第一次执行时应进行语法分析，而FALSE

  指示本PL/SQL代码应立即进行语法分析。

  使用方法

  假设我们需要创建一个JOB，第二天凌晨开始运行，每5分钟运行一次，运行时调用存储过程p_test_job往t_test表中插入数据。实现步骤参考如下：

  1.创建DBMS_JOB系统包

  调用SP_INIT_JOB_SYS创建DBMS_JOB系统包：

  SP_INIT_JOB_SYS(1);

  创建完成之后，数据库模式下面多了SYSJOB模式，该模式属于SYSDBA用户，SYSJOB模式拥有DBMS_JOB系统包、相关表和视图。如下图所示。

   

  2.赋予用户相关权限

  普通用户创建JOB需要admin job权限，调用DBMS_JOB系统包需要有执行该包的权限，使用管理员SYSDBA用户赋予DMHR用户该权限。语句参考如下：

  grant admin job to dmhr;
  grant execute on dbms_job to dmhr;
  3.创建作业

  ①创建T_TEST表：

  create table t_test(
      id int identity(1,1),
      inserttime datetime,
      jobtypevarchar2(30)
   );
  ②创建存储过程P_TEST_JOB往该表插入数据：

  create or replace procedure p_test_job
  as
  begin
      insert into t_test(inserttime,jobtype)
       values (sysdate,'DBMS_JOB');
      commit;
  end;
  ③创建JOB号编码为1001的定时任务，第二天凌晨开始运行，每5分钟运行一次：

  begin
      dbms_job.isubmit(
          job => 1001,
          what => 'p_test_job;',
          next_date => trunc(sysdate)+1,
          "INTERVAL" => 'trunc(sysdate)+1+5/(24*60)');
      commit;
  end;
  语句执行完成，查看USER_JOBS视图可以看到上述步骤创建的JOB。如果JOB正在执行，可以在DBA_JOBS_RUNNING中看到该JOB运行数据。

  方式二：使用DBMS_SCHEDULER创建作业

  DBMS

  _SCHEDULER包也是兼容Oracle产品主要作业功能的系统包。DBMS_SCHEDULER包提供一系列调度相关的存储过程及方法供DMSQL调用，目前主要兼容ORACLE的DBMS_SCHEDULER包中常用的方法和数据字典视图。

  与DBMS_JOB相比，DBMS_SCHEDULER包的功能要强大很多，但使用起来相对复杂。很学初学者容易被它的复杂性吓跑，其实我们只要理解SCHEDULE，PROGRAM和JOB这三个概念，就很容易学会如何使用它。

  SCHEDULE表示调度计划表。指定调度从什么时间开始，什么时候结束，以什么频度调度。使用DBMS_SCHEDULER.CREATE_SCHEDULE过程创建SCHEDULE。

  创建的SCHEDULE对象可以在DBA_SCHEDULER_SCHEDULES或USER_SCHEDULER_SCHEDULES视图中查看。

  PROGRAM表示调度应该做什么事情，支持DMSQL块和存储过程。使用DBMS_SCHEDULER.CREATE_PROGRAM创建PROGRAM。创建的PROGRAM对象，可以在视图DBA_SCHEDULER_PROGRAMS或USER_SCHEDULER_PROGRAMS中查看。

  JOB表示按照指定的SCHEDULE，执行指定PROGRAM，完成用户指定的工作。使用DBMS_SCHEDULER.CREATE_JOB创建JOB。创建的JOB对象，可以在DBA_SCHEDULER_JOBS或USER_SCHEDULER_JOBS视图中查看。

  创建DBMS_SCHEDULER系统包

  DM数据库安装完成之后默认不创建DBMS_SCHEDULER系统包，如果要使用该系统包需要手工创建，创建或删除DBMS_SCHEDULER系统包语法如下：

  void SP_INIT_DBMS_SCHEDULER_SYS (
  CREATE_FLAG int
  )
  参数详解

  CREATE_FLAG为1时表示创建DBMS_SCHEDULER包；为0表示删除该系统包。

  DBMS_SCHEDULER相关方法

  CREATE_SCHEDULE过程

  CREATE_SCHEDULE用于创建一个调度。语法如下：​​​​​​​

  PROCEDURE DBMS_SCHEDULER.CREATE_SCHEDULE(
  SCHEDULE_NAME IN VARCHAR,
  START_DATE  IN TIMESTAMP WITH TIME ZONE DEFAULT NULL,
  REPEAT_INTERVAL IN VARCHAR,
  END_DATE  IN TIMESTAMP WITH TIME ZONE DEFAULT NULL,
  COMMENTS      IN VARCHAR DEFAULT NULL
  );
  参数详解

  SCHEDULE_NAME调度名称。要求在模式下名称唯一，不能为空。

  START_DATE调度第一次有效日期或者时间。对于重复的调度，START_DATE是个参照值，这种情况START_DATE不是调度的开始时间，调用的开始决定于REPEATE_INTERVAL的设置。

  REPEATE_INTERVAL调度重复间隔，用于指定调用隔多久重复一次。它是基于日历语法的表达式。

  END_DATE作业停止运行时间。如果为未设置，作业则一直有效。

  COMMENTS调度相关的描述或注释。默认为空。

  CREATE_PROGRAM过程

  CREATE_PROGRAM过程用于创建一个程序。语法如下：​​​​​​​

  PROCEDURE DBMS_SCHEDULER.CREATE_PROGRAM(
  PROGRAM_NAME  IN VARCHAR,
  PROGRAM_TYPE  IN VARCHAR,
  PROGRAM_ACTION  IN VARCHAR,
  NUMBER_OF_ARGUMENTS IN INT DEFAULT 0,
  ENABLED    IN BOOLEAN DEFAULT FALSE,
  COMMENTS       IN VARCHAR DEFAULT NULL
  );
  参数详解

  PROGRAM_NAME程序名称。不能为空，要求在包模式下名称中名称唯一。

  PROGRAM_TYPE程序类型。不能为空，支持类型如下：PLSQL_BLOCK：说明程序是一个DMSQL语句块，即DM的SQL程序设计里的语句块。此类型的程序或者作业不允许设置参数个数，也就是说设置的参数个数必须为0；STORED_PROCEURE：说明程序是一个存储过程。支持存储过程，不支持带有返回值的函数。包含输入输出或者输出参数的存储过程不支持。

  PROGRAM_ACTION定义程序的动作。不能为空。可能的动作如下：对于DMSQL语句块，程序动作就是去执行DMSQL代码。对于存储过程，程序动作是存储过程的名称。如果存储过程与作业不属于同一个模式，则需要指定存储过程名称的时候，指定模式名。

  NUMBER_OF_ARGUMENTS定义程序包含的参数的个数。若未设置，则默认为0。一个程序最多可以指定255个参数。

  ENABLED指定程序是否以激活的方式创建。若设置为TRUE，则执行合法性检测，检测成功，则创建程序并置ENABLED状态。默认设置为FALSE，程序不以激活的方式创建，可以在程序使用之前通过调用ENABLE过程来激COMMENTS程序的相关评论或者注释。默认为空。

  CREATE_JOB过程

  CREATE_JOB过程用来创建作业。支持三种方法创建JOB。

  ①不使用已经存在的程序（PROGRAM）或者调度（SCHEDULE）创建作业。语法如下：​​​​​​​

  PROCEDURE DBMS_SCHEDULER.CREATE_JOB(
  JOB_NAME   IN VARCHAR,
  JOB_TYPE   IN VARCHAR,
  JOB_ACTION   IN VARCHAR,
  NUMBER_OF_ARGUMENTS IN INT DEFAULT 0,
  START_DATE   IN TIMESTAMP WITH TIME ZONE DEFAULT NULL,
  REPEAT_INTERVAL  IN VARCHAR DEFAULT NULL,
  END_DATE   IN TIMESTAMP WITH TIME ZONE DEFAULT NULL,
  JOB_CLASS   IN VARCHAR DEFAULT 'DEFAULT_JOB_CLASS',
  ENABLED    IN BOOLEAN DEFAULT FALSE,
  AUTO_DROP   IN BOOLEAN DEFAULT TRUE,
  COMMENTS   IN VARCHAR DEFAULT NULL,
  CREDENTIAL_NAME  IN VARCHAR DEFAULT NULL,
  DESTINATION_NAME        IN VARCHAR DEFAULT NULL
  );
  ②使用命名程序（PROGRAM）和命名调度（SCHEDULE）创建作业。语法如下：​​​​​​​​​​​​​​

  PROCEDURE DBMS_SCHEDULER.CREATE_JOB(
  JOB_NAME   IN VARCHAR,
  PROGRAM_NAME  IN VARCHAR,
  SCHEDULE_NAME  IN VARCHAR,
  JOB_CLASS   IN VARCHAR DEFAULT 'DEFAULT_JOB_CLASS',
  ENABLED    IN BOOLEAN DEFAULT FALSE,
  AUTO_DROP   IN BOOLEAN DEFAULT TRUE,
  COMMENTS   IN VARCHAR DEFAULT NULL,
  JOB_STYLE   IN VARCHAR DEFAULT 'REGULAR',
  CREDENTIAL_NAME  IN VARCHAR DEFAULT NULL,
  DESTINATION_NAME        IN VARCHAR DEFAULT NULL
  );
  ③使用命名程序（PROGRAM）和内置调度（SCHEDULE）创建作业。语法如下：​​​​​​​

  PROCEDURE DBMS_SCHEDULER.CREATE_JOB(
  JOB_NAME   IN VARCHAR,
  PROGRAM_NAME  IN VARCHAR,
  START_DATE   IN TIMESTAMP WITH TIME ZONE DEFAULT NULL,
  REPEAT_INTERVAL  IN VARCHAR DEFAULT NULL,
  END_DATE   IN TIMESTAMP WITH TIME ZONE DEFAULT NULL,
  JOB_CLASS   IN VARCHAR DEFAULT 'DEFAULT_JOB_CLASS',
  ENABLED    IN BOOLEAN DEFAULT FALSE,
  AUTO_DROP   IN BOOLEAN DEFAULT TRUE,
  COMMENTS   IN VARCHAR DEFAULT NULL,
  JOB_STYLE   IN VARCHAR DEFAULT 'REGULAR',
  CREDENTIAL_NAME  IN VARCHAR DEFAULT NULL,
  DESTINATION_NAME        IN VARCHAR DEFAULT NULL
  );
  参数详解

  JOB_NAME 作业名称。要求在包模式下名称中名称唯一，不能为空。可以使用GENERATE_JOB_NAME过程自动生成一个作业名。

  JOB_TYPE、JOB_ACTION同CREATE_PROGRAM中PROGRAM_TYPE、PROGRAM_ACTION参数。不能为空。

  PROGRAM_NAME，SCHEDULE_NAME 作业使用的PROGRAM和调度名称。

  START_DATE，END_DATE、REPEAT_INTERVAL作业起始、结束日期和执行周期间隔。如果 START_DATE 和 REPEAT_INTERVAL 都为 NULL，表示作业在 ENABLE后立即执行。REPEAT_INTERVAL仅支持日历语法格式。

  ENABLED 指定作业创建时是否启用。

  AUTO_DROP 如果为TRUE，作业自动删除。默认为TRUE。自动删除的条件如下：①作业的结束日期已经过期；②作业不是重复执行作业，并且只执行一次。如果为NULL，则取值为FALSE，作业不会自动删除。

  JOB_CLASS，JOB_STYLE，CREDENTIAL_NAME，DESTINATION_NAME不支持，只作兼容参数用。

  日历表达式

  日历表达式是Linux系统的crontab使用的格式，日历表达式分为三部分：

  第一部分是频率，即“FREQ”关键字，它是必须指定的；取值YEARLY| MONTHLY | WEEKLY | DAILY | HOURLY | MINUTELY | SECONDLY。

  第二部分是时间间隔，即“INTERVAL”关键字，取值范围是1-999。它是可选参数；

  第三部分是附加的参数，可用于精确地指定日期和时间，也是可选参数。下面这些值都是合法的：BYMONTH,BYWEEKNO, BYYEARDAY, BYMONTHDAY, BYDAY, BYHOUR, BYMINUTE, BYSECOND。

  举几个简单的日历写法。例如：

  每隔10分钟运行一次：

  repeat_interval=> 'FREQ=MINUTELY; INTERVAL=10'
  每天1点运行一次

  repeat_interval=> 'FREQ=DAILY; BYHOUR=1'
  每周的1,3,5运

  repeat_interval=> 'FREQ=WEEKLY; BYDAY=MON,WED,FRI"
  每年的3,6,9,12月的1号运行：

  repeat_interval=> 'FREQ=YEARLY; BYMONTH=MAR,JUN,SEP,DEC; BYMONTHDAY=1'
  每月的最后一天执行一次：

  repeat_interval=> 'FREQ=MONTHLY; BYMONTHDAY=-1’
  使用方法

  前面介绍那么多，下面分别从CREATE_JOB的三种方法上介绍JOB的创建。实现步骤参考如下：

  1.创建DBMS_SCHEDULER系统包

  调用SP_INIT_DBMS_SCHEDULER_SYS创建DBMS_SCHEDULER系统包：

  SP_INIT_DBMS_SCHEDULER_SYS(1);
  创建完成之后，数据库模式下面多了SCHEDULER模式，该模式属于SYSDBA用户，SCHEDULER模式拥有DBMS_SCHEDULER系统包、相关表和视图。如下图所示。

   

  2.赋予用户相关权限

  调用DBMS_SCHEDULER系统包需要有执行该包的权限，使用管理员SYSDBA用户赋予DMHR用户该权限。语句参考如下：

  grant execute on dbms_scheduler to dmhr;
  3.创建程序和作业

  创建存储过程P_TEST_SCHEDULER往该表插入数据：​​​​​​​

  create or replace procedure p_test_scheduler
  as
  begin
      insertintot_test(inserttime,jobtype)
      values(sysdate,'DBMS_SCHEDULER');
      commit;
  end;
  ①不使用程序和调度来创建JOB

  创建TEST_SCHEDULERJOB1作业，直接使用job_action、job_type、repeat_interval指定作业的类型和动作和执行周期（每10分钟执行一次，30秒时执行）。​​​​​​​

  begin
      dbms_scheduler.create_job(
          job_name => 'test_schedulerjob1',
          job_type => 'STORED_PROCEDURE',
          job_action => 'p_test_scheduler',
          repeat_interval => 'FREQ=minutely;INTERVAL=10;bysecond=30',
          enabled => TRUE);
      commit;
  end;
  ②使用程序、不使用调度来创建JOB

  创建程序TEST_PROGRAM调用P_TEST_SCHEDULER存储过程：​​​​​​​

  begin
      dbms_scheduler.create_program(
          program_name => 'TEST_PROGRAM',
          program_type => 'STORED_PROCEDURE',
          program_action => 'p_test_scheduler',
          enabled => TRUE);
      commit;
  end;
  创建TEST_SCHEDULERJOB2作业，调用TEST_PROGRAM程序，每小时10分时运行一次：​​​​​​​

  begin
      dbms_scheduler.create_job(
          job_name => 'test_schedulerjob2',
          program_name => 'TEST_PROGRAM',
          start_date => null,
          repeat_interval => 'FREQ=hourly;INTERVAL=1;byminute=10',
          enabled => TRUE);
  end;
  ③使用程序、调度来创建JOB

  创建调度TEST_SCHEDULER指定调度周期和间隔（每天10点运行一次）：​​​​​​​​​​​​​​

  begi
      dbms_scheduler.create_schedule(
          schedule_name => 'TEST_SCHEDULER',
          start_date => null,
          repeat_interval => 'FREQ=daily;INTERVAL=1;byhour=10',
          end_date => null);
      commit;
  end;
  创建TEST_SCHEDULERJOB3作业，根据TEST_PROGRAM程序和TEST_SCHEDULER调度来执行作业：​​​​​​​

  begin
      dbms_scheduler.create_job(
          job_name => 'test_schedulerjob3',
          program_name => 'TEST_PROGRAM',
          schedule_name => 'TEST_SCHEDULER',
          enabled => TRUE);
      commit;
  end;
  语句执行完成，查看USER_SCHEDULER_开头的视图中可以看到上述步骤创建的JOB、PROGRAM、SCHEDULER。

  使用如下语句查看USER_SCHEDULER_JOBS视图，可以看到我们新建的三个JOB：​​​​​​​

  selectt.JOB_NAME,t.JOB_ACTION,t.ENABLED,t.START_DATE,t.PROGRAM_NAME,t.SCHEDULE_NAME,t.REPEAT_INTERVAL
   from user_scheduler_jobs t;

   

  DBMS_SCHEDULER创建的JOB运行日志和详情可以在USER_SCHEDULER_JOB_LOG和USER_SCHEDULER_JOB_RUN_DETAILS及对应的DBA开头的视图中查询。DBMS_JOB没有相关的JOB运行日志，这也是DBMS_SCHEDULER比DBMS_JOB强大的地方。

  DBMS_SCHEDULER还具有创建、修改、删除、禁用JOB/SCHEDULER/PROGRAM、邮件通知等功能，其他功能请参考《DM8系统包使用手册》。

  赞同 0 反对 0

  ---