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 fromdual;

　　--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 标识使用当前用户的权限。