Oracled的定时执行存储过程

最新推荐文章于 2024-06-05 16:17:52 发布

RoamDemo

最新推荐文章于 2024-06-05 16:17:52 发布

阅读量874

点赞数 10

文章标签：数据库 oracle

本文链接：https://blog.csdn.net/zf199805/article/details/135815717

版权

Oracled的定时执行存储过程

1. SQL模版

create or replace procedure proc_date as
begin
    你需要执行的SQL包括: insert / update 等,单独执行 select好像没有太大的意义
    commit;
end;
declare
    job number; /*任务的唯一标示号*/
begin
    DBMS_JOB.SUBMIT(
            JOB => job, /*自动生成JOB_ID*/
            WHAT => 'proc_date;', /*需要执行的存储过程名称或SQL语句*/
            NEXT_DATE => sysdate + 1 / (24 * 60), /*初次执行时间-下一个3分钟*/
            INTERVAL => 'trunc(sysdate+1)' /*每隔1分钟执行一次*/
        );
    commit;
end;

2. SQL示例

create or replace procedure proc_date as
begin
    BEGIN
        FOR cur IN (
            select t.id, t1.name, t1.cardid, nvl(t1.mobile, t1.linkman_phone) phone
            from mc_womanhealth_breast t
                     left join mc_personal_info t1 on t1.personalid = t.personalid
            where t.invalid = '0'
              and (t.finaly_Diagnosis = 'DM09-68_4' or t.finaly_Diagnosis = 'DM09-68_5')
              and t1.name is not null
              and t.name is null
            )
            loop
                UPDATE mc_womanhealth_breast SA
                SET SA.name   = cur.name,
                    SA.cardid = cur.cardid,
                    SA.phone  = cur.phone
                WHERE SA.id = cur.id;
            END loop;
    END;

    commit;
end;
declare
    job number;
begin
    DBMS_JOB.SUBMIT(
            JOB => job,
            WHAT => 'proc_date;', 
            NEXT_DATE => sysdate + 1 / (24 * 60),
            INTERVAL => 'trunc(sysdate+1)'
        );
    commit;
end;

3. 上述SQL的解析

这段SQL语句使用PL/SQL语言编写，它包含一个名为 `proc_date` 的存储过程和一个DECLARE块。

**存储过程 `proc_date` **

存储过程 `proc_date` 的功能是更新表 `mc_womanhealth_breast` 中某些列的值，这些列的值来自另一个表 `mc_personal_info` 。

具体来说，存储过程 `proc_date` 将执行以下操作：

1. 使用游标 `cur` 从表 `mc_womanhealth_breast` 和 `mc_personal_info` 中选择数据。
2. 使用循环语句 `LOOP` 遍历游标 `cur` 中的每一行数据。
3. 在循环体中，使用 `UPDATE` 语句更新表 `mc_womanhealth_breast` 中相应行的数据。
4. 在循环结束后，使用 `COMMIT` 语句提交对数据库所做的更改。

**DECLARE块**

DECLARE块定义了一个名为 `job` 的变量，该变量将存储一个任务的唯一标识号。

然后，DECLARE块使用 `DBMS_JOB.SUBMIT` 过程提交一个作业，该作业将执行存储过程 `proc_date` 。

作业的初次执行时间设置为当前时间加1分钟，每隔1分钟执行一次。

最后，DECLARE块使用 `COMMIT` 语句提交对数据库所做的更改。

4. 对于定时作业的简单操作/示例

1: 查看作业  
	select * from USER_JOBS;
2. 删除定时作业: 
    begin
        dbms_job.remove(149);
    end;
3. 暂停作业    
    begin   
 	 	dbms_job.broken(jobId,true,next_date);       
 		 /*停止一个job,jobId, job的ID,里面参数true也可是false，next_date（某一时刻停止）也可是sysdate（立刻停止）。   */
		commit;
	end;  
    	示例: 
    		begin
    			dbms_job.broken(149,true,sysdate);
			end;
4. 修改下一次任务执行的时间      
    begin
      dbms_job.next_date(job，interval);  /*job  job的ID,interval: 计算下一次任务执行的时间表达式*/
      commit;
    end;
    示例:
    	begin
          dbms_job.next_date('149',TRUNC(sysdate,'mi') + 1/ (24*60));  /*job  job的ID,interval: 计算下一次任务执行的时间表达式*/
          commit;
   		end;
5.修改时间间隔:
  begin
      dbms_job.interval(job，interval);  /*job  job的ID,interval: 修改间隔时间*/
      commit;
  end;
    示例
        begin
          dbms_job.interval('149','TRUNC(sysdate) + 1 +1/ (24)');  /*job  job的ID,interval: 修改间隔时间*/
          commit;
        end;
6.修改任务指向
    begin
        dbms_job.what(jobno，'sp_fact_charge_code;');   /*sp_fact_charge_code; 要更改的新操作名称*/
      commit;
    end;
    示例:
    	begin
            dbms_job.what(1423,'proc_date;');/*proc_date; 表示新的业务名称,切记别忘了分号*/
            commit;
		end;

5. 作业表字段的释义

create public synonym USER_JOBS for SYS.USER_JOBS

这条SQL语句使用 `CREATE PUBLIC SYNONYM` 命令创建了一个公共同义词 `USER_JOBS` ，它指向系统表 `SYS.USER_JOBS` 。

这意味着用户可以使用 `USER_JOBS` 这个同义词来访问系统表 `SYS.USER_JOBS` ，而无需输入完整的表名。

这可以简化用户的操作，并提高代码的可读性。

comment on table USER_JOBS is 'All jobs owned by this user' 此用户拥有的所有作业
/

comment on column USER_JOBS.JOB is 'Identifier of job.  Neither import/export nor repeated executions change it.'
作业的标识符。 导入/导出和重复执行都不会更改它  理解成主键id
/

comment on column USER_JOBS.LOG_USER is 'USER who was logged in when the job was submitted' 提交作业时登录的用户
/
 
comment on column USER_JOBS.PRIV_USER is 'USER whose default privileges apply to this job' 默认权限应用于此作业的 USER
/

comment on column USER_JOBS.SCHEMA_USER is 'select * from bar  means  select * from schema_user.bar '
/

comment on column USER_JOBS.LAST_DATE is 'Date that this job last successfully executed' 上次成功执行此作业的日期
/

comment on column USER_JOBS.LAST_SEC is 'Same as LAST_DATE.  This is when the last successful execution started.' 与LAST_DATE相同。 这是上次成功执行开始的时间。
/

comment on column USER_JOBS.THIS_DATE is 'Date that this job started executing (usually null if not executing)' 此作业开始执行的日期（如果未执行，则通常为 null）
/

comment on column USER_JOBS.THIS_SEC is 'Same as THIS_DATE.  This is when the last successful execution started.' 与THIS_DATE相同。 这是上次成功执行开始的时间。
/

comment on column USER_JOBS.NEXT_DATE is 'Date that this job will next be executed' 下次执行此作业的日期
/

comment on column USER_JOBS.NEXT_SEC is 'Same as NEXT_DATE.  The job becomes due for execution at this time.' 与NEXT_DATE相同。 此时，作业将到期执行。
/

comment on column USER_JOBS.TOTAL_TIME is 'Total wallclock time spent by the system on this job, in seconds' 系统在此作业上花费的总挂钟时间（以秒为单位）
/

comment on column USER_JOBS.BROKEN is 'If Y, no attempt is being made to run this job.  See dbms_jobq.broken(job).' 如果为 Y，则不会尝试运行此作业。 请参阅 dbms_jobq.broken（job）
/

comment on column USER_JOBS.INTERVAL is 'A date function, evaluated at the start of execution, becomes next NEXT_DATE' 在执行开始时计算的日期函数将成为下一个NEXT_DATE
/

comment on column USER_JOBS.FAILURES is 'How many times has this job started and failed since its last success?' 自上次成功以来，此作业已启动和失败多少次
/

comment on column USER_JOBS.WHAT is 'Body of the anonymous PL/SQL block that this job executes' 此作业执行的匿名 PL/SQL 块的正文
/

comment on column USER_JOBS.NLS_ENV is 'alter session parameters describing the NLS environment of the job' 更改描述作业的 NLS 环境的会话参数
/

comment on column USER_JOBS.MISC_ENV is 'a versioned raw maintained by the kernel, for other session parameters' 由内核维护的版本化原始数据，用于其他会话参数
/

comment on column USER_JOBS.INSTANCE is 'Instance number restricted to run the job' 限制运行作业的实例编号
/

6. INTERVAL参数常用值示例

Interval => ''TRUNC(SYSDATE + 1)''     -- 每天午夜12点 
Interval => ''TRUNC(SYSDATE + 1) + （8*60+30）/(24*60)''     --每天早上8点30分 
Interval =>  ''NEXT_DAY(TRUNC(SYSDATE ), ''''TUESDAY'''' ) + 12/24''     --每星期二中午12点  
Interval => ''TRUNC(LAST_DAY(SYSDATE ) + 1)''     --每个月第一天的午夜12点
Interval => ''TRUNC(ADD_MONTHS(SYSDATE + 2/24, 3 ), ''Q'' ) -1/24''     --每个季度最后一天的晚上11点 
Interval =>  ''TRUNC(LEAST(NEXT_DAY(SYSDATE, ''''SATURDAY"), NEXT_DAY(SYSDATE, "SUNDAY"))) + （6×60+10）/（24×60）''    --每星期六和日早上6点10分
Interval =>  'sysdate+3/(24*60*60)'   --每3秒钟执行一次     
Interval =>  'sysdate+2/(24*60)'   --每2分钟执行一次   
  
1:每分钟执行  
TRUNC(sysdate,'mi') + 1/ (24*60) --每分钟执行  
interval => 'sysdate+1/（24*60）'  --每分钟执行  
interval => 'sysdate+1'    --每天  
interval => 'sysdate+1/24'   --每小时  
interval => 'sysdate+2/24*60' --每2分钟  
interval => 'sysdate+30/24*60*60'  --每30秒  
2:每天定时执行  
Interval => TRUNC(sysdate+1)  --每天凌晨0点执行  
Interval => TRUNC(sysdate+1)+1/24  --每天凌晨1点执行  
Interval => TRUNC(SYSDATE+1)+(8*60+30)/(24*60)  --每天早上8点30分执行  
3:每周定时执行  
Interval => TRUNC(next_day(sysdate,'星期一'))+1/24  --每周一凌晨1点执行  
Interval => TRUNC(next_day(sysdate,1))+2/24  --每周一凌晨2点执行  
4:每月定时执行  
Interval =>TTRUNC(LAST_DAY(SYSDATE)+1)  --每月1日凌晨0点执行  
Interval =>TRUNC(LAST_DAY(SYSDATE))+1+1/24  --每月1日凌晨1点执行  
5:每季度定时执行  
Interval => TRUNC(ADD_MONTHS(SYSDATE,3),'q')  --每季度的第一天凌晨0点执行  
Interval => TRUNC(ADD_MONTHS(SYSDATE,3),'q') + 1/24  --每季度的第一天凌晨1点执行  
Interval => TRUNC(ADD_MONTHS(SYSDATE+ 2/24,3),'q')-1/24  --每季度的最后一天的晚上11点执行  
6:每半年定时执行  
Interval => ADD_MONTHS(trunc(sysdate,'yyyy'),6)+1/24  --每年7月1日和1月1日凌晨1点  
7:每年定时执行  
Interval =>ADD_MONTHS(trunc(sysdate,'yyyy'),12)+1/24  --每年1月1日凌晨1点执行