创建ORACLE Job定时任务来跑存储过程

àì夳堔傛蜴生んèń 2022-06-06 08:14 379阅读 0赞

## 背景 ##

报表系统需要创建一个临时表，且临时表要定期更新，临时表包含三部分：  
\- drop table  
\- create table  
\- create index

## 思路： ##

1.  创建存储过程
2.  创建job，设定定时来跑

## 步骤 ##

## 1. 创建存储过程 ##

CREATE OR REPLACE PROCEDURE CREATE_T_SUBS_I AS v_count number(10);
      D_SQL   VARCHAR(3000);
      STR_SQL VARCHAR(3000);
      V_SQL   VARCHAR(3000);
    BEGIN SELECT count(*) into v_count FROM USER_TABLES WHERE TABLE_NAME = UPPER('T_SUBS_bi');
      if v_count >= 1 then
        D_SQL := 'drop table T_SUBS_bi'; execute immediate D_SQL; end if; STR_SQL := 'create table T_SUBS_bi AS SELECT A.SUBS_ID, A.AREA_ID, C.AREA_NAME, PROD_STATE FROM SUBS@LINK_CC A, PROD@LINK_CC B, BFM_AREA@LINK_CC C WHERE A.SUBS_ID = B.PROD_ID AND A.AREA_ID = C.AREA_ID'; execute immediate STR_SQL; V_SQL := 'create index index_t_subs_bi on T_SUBS_bi(SUBS_ID)'; execute immediate V_SQL; commit; END;

创建完毕，测试存储过程

begin -- Call the procedure create_t_subs_i;
    end;

执行时报错，ORA-01031:权限不足，没有权限； 检查了下数据库用户的权限，发现其有DBA role权限，单独执行drop table、create table等操作也没有问题，但是调用存储过程执行就会报错；

查阅相关资料，存储过程会检查用户的显示授权，而不关心其role的权限，故会出现有DBA role，但是存储过程中没办法创建表的错误，有两种解决方法：  
\- 在创建存储过程时，加上AUTHID CURRENT\_USER，让存储过程执行时，以调用者身份执行，具体方法如下：

CREATE OR REPLACE PROCEDURE CREATE_T_SUBS_I AUTHID CURRENT_USER AS ...

*  用sysdba，在sqlplus中给当前用户显式授权，具体方法是sqlplus / as sysdba进入sqlplus，根据需要，grant相关权限给用户：

grant connect,resource to TESTRB; 
    grant create any sequence to TESTRB; 
    grant create any table to TESTRB; 
    grant delete any table to TESTRB; 
    grant insert any table to TESTRB; 
    grant select any table to TESTRB; 
    grant execute any procedure to TESTRB; 
    grant update any table to TESTRB; 
    grant create any view to TESTRB;
    grant create any index to TESTRB;
    grant drop table to TESTRB;

值得注意的是，如果用户已经显式授权过了，那么就不需要在存储过程中加AUTHID CURRENT\_USSER，因为此时用户已经具有相关权限了。

## 2. 创建Oracle Job定时任务 ##

Oracle Job简介

关于Oracle Job的介绍，网上找了一篇文章，讲解得很详细，贴一个地址，有兴趣的可以看看：[Oralce Job简介][1]

\- 创建Oracle Job

declare
      job001 number;
    begin dbms_job.submit(job001, 'CREATE_T_SUBS_I;', sysdate, 'TRUNC(sysdate) + 1 +4/ (24)');
    end;

*  创建完成后，检查job

--相关视图
    select * from dba_jobs;
    select * from all_jobs;
    select * from user_jobs;

*  执行job测试，在command模式下执行

begin dbms_job.run(23);
    end;

其中job number可以通过select \* from user\_jobs查询；

执行测试时，系统提示错误：