oracle job 定点,Oracle创建Job，每天定时执行操作

在项目中，经常会遇到需要定时完成的任务，比如定时更新数据，定义统计数据生成报表等等，其实这些事情都可以使用oracle的job来完成。下面考试大就结合我们实验室项目实际，简单介绍一下在oracle数据库中通过job完成自动创建表的方法。

整个过程总共分为两步。虽然整个过程都非常简单，但是对于初学oracle的生手还是有很多地方需要注意的。

首先介绍一下，创建该job的背景，因为每天更新的直播和点播节目信息比较多，为了方便处理，需要每天创建一张表来记录更新的节目信息，当前数据库中已经有一张tbl_programme的表，每天创建的表的字段需要同tbl_programme保持一致，每天新创建的表的名称格式为tbl_programme_日期(例如：tbl_programme_20090214)规定每天晚上1点钟生成该天的新表。

第一步：创建一个执行创建操作的存储过程

在这一步首先要解决的问题就是构造表名。在oracle中格式话输出时间可以用to_char函数来处理，例如：

sql> select to_char(sysdate, ’yyyy/mm/dd hh24:mi:ss’) from dual;

to_char(sysdate,’yyyy/mm/ddhh2

------------------------------

2009/02/14 17:22:41

以上sql格式化输出了时间，要得到我们所需要的格式直接修改一下sql即可

sql> select to_char(sysdate, ’yyyymmdd’) from dual;

to_char(sysdate,’yyyymmdd’)

---------------------------

20090214

得到时间格式字符串后我们就可以将表名的前缀和时间连接在一起形成完整的表名。这里需要注意，在oracle中链接两个字符串需要使用‘||’符号，而在sql server中直接使用‘+’号就可以了，因为我以前一直在sql server下编程，好久都没编写oracle的sql所以费了很大的功夫才发现这个问题。完整的sql就是

sql> select ’tbl_programme_’ || to_char(sysdate, ’yyyymmdd’) from dual;

’tbl_programme_’||to_char(sysd

------------------------------

tbl_programme_20090214

接下来就是创建表的代码了，因为新表需要tbl_programme保持一致，所以直接ctas来创建表那是非常适合的了，代码如下：

create table tablename as select * from tbl_programme

如果需要指定一个tablespace则将该sql做适当修改：

create table tablename tablespace p2p as select * from tbl_programme

所以整个创建存储过程的sql就是

create or replace procedure sp_createtab_tbl_programme

authid current_user

as

tabname varchar(200);

begin

select ’tbl_programme_’ || to_char(sysdate, ’yyyymmdd’) into tabname from dual;

--create table tabname as select * from tbl_programme where 1 != 1;

execute immediate ’create table ’ || tabname ||’ tablespace p2p as select * from tbl_programme where 1 != 1’;

commit;

end;

/

这里还需要注意一下在oracle里面如果要对一个变量赋值的话有两种方式：

(1) 使用：=进行赋值

(2) 使用select ‘xjkxj ’ into 变量名称 from tabname

另外，在存储过程中定义变量的时候一般放在as/is后begin前面。在存储过程一般是不能直接使用create table，truncate table这类似的语句的，如果要使用这些语句必须使用excute immediate + 所要执行的sql语句来实现。

注意上面用红色标志的语句：authid current_user

这个语句比较重要，如果我们在创建存储过程的时候不添加这条语句执行该存储过程将不会成功，原因是默认情况向存储过程是没有create table等权限的，即使当前用户有dba的权限也不行，如果存储过程中存在创建表的操作，可以有以下两种方式来解决该问题。

(1) 显示的赋予该用户create table的权限，grant create table to user.

(2) 在存储过程中使用authid current_user 标识使用当前用户的权限。

第二步：创建job

创建job就比较简单了，下面就是创建job的代码

每天晚上1电job启动一次，执行sp_createtab_tbl_programme存储过程。

variable testjobid number;

begin

sys.dbms_job.submit(:testjobid,’sp_createtab_tbl_programme;’,trunc(sysdate+1)+1/24,’trunc(sysdate+1)+1/24’);

commit;

end;

/

这里需要注意的是，在submit方法的前面一定要先定义job这个变量，另外，submit方法的第二个参数是一个存储过程的名，记得在后面添加“：”号，在next_date是一个时间类型变量而不是一个字符串，所以需要注意不要把它当成字符串，不需要对该参数加引号。最后一个参数interval是一个字符串类型，记得添加引号。最常见的错误如下图所示：

ora-01008： not all variables bound就是没有定义变量的意思。一定记的在使用submit方法时定义jobid变量。

下面是常有的设置interval的方法：

2 每天固定时间运行，比如早上8：10分钟：trunc(sysdate+1) + 8/24

² 每天：trunc(sysdate+1)

² 每周：trunc(sysdate+7)

² 每月：trunc(sysdate+30)

² 每个星期日：next_day(trunc(sysdate)，’sunday’)

² 每天6点：trunc(sysdate+1)+6/24

² 半个小时：sysdate+30/1440

需要用到的完整sql如下：

-----------------------------------------------------

-- export file for user p2p --

-- created by administrator on 2009-2-14, 15:45:18 --

-----------------------------------------------------

spool gjgdp2p(v1.3).log

promptprompt creating procedure sp_createtab_tbl_programme

prompt =============================================

prompt

create or replace procedure sp_createtab_tbl_programme

authid current_user

as

tabname varchar(200);

begin

select ’tbl_programme_’ || to_char(sysdate, ’yyyymmdd’) into tabname from dual;

--create table tabname as select * from tbl_programme where 1 != 1;

execute immediate ’create table ’ || tabname ||’ tablespace p2p as select * from tbl_programme where 1 != 1’;

commit;

end;

/

variable testjobid number;

begin

sys.dbms_job.submit(:testjobid,’sp_createtab_tbl_programme;’,trunc(sysdate+1)+1/24,’trunc(sysdate+1)+1/24’);

commit;

end;

/

spool off