Oracle通过job定时创建表_ITPUB博客

相关文章推荐

俊逸的围巾 · 关于印发《丰城市纪委市监委普通密码设备 ...· 1 月前 ·

重感情的小熊猫 · 深江铁路项目中山段民众街道用地红线外农用地“ ...· 2 月前 ·

温柔的鞭炮 · 广清城际北延段今年要建成 - ...· 2 月前 ·

至今单身的伤痕 · 佛教史话· 3 月前 ·

慈祥的黄瓜 · 【Ronny ...· 1 年前 ·

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

[@more@]

整个过程总共分为两步。虽然整个过程都非常简单，但是对于初学 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

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 的方法：

² 每天固定时间运行，比如早上 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

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

第三步：异常情况处理

JOB 不能运行情况处理

1. 先来了解一下 JOB 的参数说明：与 job 相关的参数一个是 job_queue_processes ，这个是运行 JOB 时候所起的进程数，当然系统里面 JOB 大于这个数值后，就会有排队等候的，最小值是 0 ，表示不运行 JOB ，最大值是 36 ，在 OS 上对应的进程时 SNPn ， 9i 以后 OS 上管理 JOB 的进程叫 CJQn 。可以使用下面这个 SQL 确定目前有几个 SNP/CJQ 在运行。

select * from v$bgprocess ，这个 paddr 不为空的 snp/cjq 进程就是目前空闲的进程，有的表示正在工作的进程。

另外一个是 job_queue_interval ，范围在 1--3600 之间，单位是秒，这个是唤醒 JOB 的 process ，因为每次 snp 运行完他就休息了，需要定期唤醒他，这个值不能太小，太小会影响数据库的性能。

2. 诊断：先确定上面这两个参数设置是否正确，特别是第一个参数，设置为 0 了，所有 JOB 就不会跑，确认无误后，我们继续向下。

3. 使用下面的 SQL 察看 JOB 的的 broken,last_date 和 next_date ， last_date 是指最近一次 job 运行成功的结束时间， next_date 是根据设置的频率计算的下次执行时间，根据这个信息就可以判断 JOB 上次是否正常，还可以判断下次的时间对不对， SQL 如下：

select * from dba_jobs

有时候我们发现他的 next_date 是 4000 年 1 月 1 日，说明 job 要不就是在 running ，要不就是状态是 break(broken=Y) ，如果发现 JOB 的 broken 值为 Y ，找用户了解一下，确定该 JOB 是否可以 broken ，如果不能 broken ，那就把 broken 值修改成 N ，修改再使用上面的 SQL 察看就发现他的 last_date 已经变了， JOB 即可正常运行，修改 broken 状态的 SQL 如下：

declare

BEGIN

DBMS_JOB.BROKEN( ,FALSE);

END;

4. 使用下面的 SQL 查询是否 JOB 还在 Running

select * from dba_jobs_running

如果发现 JOB 已经 Run 了很久了还没有结束，就要查原因了。一般的 JOB running 时会锁定相关的相关的资源，可以查看一下 v$access 和 v$locked_object 这两个 view ，如果发现其他进程锁定了与 JOB 相关的 Object ，包括 PKG/Function/Procedure/Table 等资源，那么就要把其他进程删除，有必要的话，把 JOB 的进程也删除，再重新跑看看结果。

5. 如果上面都正常，但是 JOB 还不 run ，怎么办？那我们要考虑把 JOB 进程重启一次，防止是 SNP 进程死了造成 JOB 不跑，指令如下：

alter system set job_queue_processes=0 -- 关闭 job 进程，等待 5--10 秒钟

alter system set job_quene_processes=5 -- 恢复原来的值

参考文献：

fyer/blog/item/f0705c22cc4bfca74623e808.html

4shop/blog/item/7b7e7809ebacf3c83bc763bc.html

%C7%ED%D0%A1%C2%BF/blog/item/e746b6a972e31bfa1e17a2b5.html

4shop/blog/item/0d64b418603c040035fa413b.html

xdz66/blog/item/a550212d31518633349bf7e9.html

http://viwo.javaeye.com/blog/58222#comments

附录： Oracle 存储过程基本语法

1. 基本结构

CREATE OR REPLACE PROCEDURE 存储过程名字

(

参数 1 IN NUMBER,

参数 2 IN NUMBER

) IS

变量 1 INTEGER :=0;

变量 2 DATE;

BEGIN

END 存储过程名字

2.SELECT INTO STATEMENT

将 select 查询的结果存入到变量中，可以同时将多个列存储多个变量中，必须有一条

记录，否则抛出异常 ( 如果没有记录抛出 NO_DATA_FOUND)

例子：

BEGIN

SELECT col1,col2 into 变量 1, 变量 2 FROM typestruct where xxx;

EXCEPTION

WHEN NO_DATA_FOUND THEN

xxxx;

END;

...

3.IF 判断

IF V_TEST=1 THEN

BEGIN

do something

END;

END IF;

4.while 循环

WHILE V_TEST=1 LOOP

BEGIN

XXXX

END;

END LOOP;

5. 变量赋值

V_TEST := 123;

6. 用 for in 使用 cursor

...

CURSOR cur IS SELECT * FROM xxx;

BEGIN

FOR cur_result in cur LOOP

BEGIN

V_SUM :=cur_result. 列名 1+cur_result. 列名 2

END;

END LOOP;

END;

7. 带参数的 cursor

CURSOR C_USER(C_ID NUMBER) IS SELECT NAME FROM USER WHERE TYPEID=C_ID;

OPEN C_USER( 变量值 );

LOOP

FETCH C_USER INTO V_NAME;

EXIT FETCH C_USER%NOTFOUND;

do something

END LOOP;

CLOSE C_USER;

8. 用 pl/sql developer debug

连接数据库后建立一个 Test WINDOW

在窗口输入调用 SP 的代码 ,F9 开始 debug,CTRL+N 单步调试

通过一个实际的例子学习 Oracle 存储过程

-- 创建存储过程

CREATE OR REPLACE PROCEDURE xxxxxxxxxxx_p

(

-- 参数 IN 表示输入参数， OUT 表示输入参数，类型可以使用任意 Oracle 中的合法类型。

is_ym IN CHAR

)

-- 定义变量

vs_msg VARCHAR2(4000); -- 错误信息变量

vs_ym_beg CHAR(6); -- 起始月份

vs_ym_end CHAR(6); -- 终止月份

vs_ym_sn_beg CHAR(6); -- 同期起始月份

vs_ym_sn_end CHAR(6); -- 同期终止月份

-- 定义游标 ( 简单的说就是一个可以遍历的结果集 )

CURSOR cur_1 IS

SELECT area_code,CMCODE,SUM(rmb_amt)/10000 rmb_amt_sn,SUM(usd_amt)/10000 usd_amt_sn

FROM BGD_AREA_CM_M_BASE_T

WHERE ym >= vs_ym_sn_beg

AND ym <= vs_ym_sn_end

GROUP BY area_code,CMCODE;

BEGIN

-- 用输入参数给变量赋初值，用到了 Oralce 的 SUBSTR TO_CHAR ADD_MONTHS TO_DATE 等很常用的函数。

vs_ym_beg := SUBSTR(is_ym,1,6);

vs_ym_end := SUBSTR(is_ym,7,6);

vs_ym_sn_beg := TO_CHAR(ADD_MONTHS(TO_DATE(vs_ym_beg,'yyyymm'), -12),'yyyymm');

vs_ym_sn_end := TO_CHAR(ADD_MONTHS(TO_DATE(vs_ym_end,'yyyymm'), -12),'yyyymm');

-- 先删除表中特定条件的数据。

DELETE FROM xxxxxxxxxxx_T WHERE ym = is_ym;

-- 然后用内置的 DBMS_OUTPUT 对象的 put_line 方法打印出影响的记录行数，其中用到一个系统变量 SQL%rowcount

DBMS_OUTPUT.put_line('del 上月记录 ='||SQL%rowcount||' 条 ');

INSERT INTO xxxxxxxxxxx_T(area_code,ym,CMCODE,rmb_amt,usd_amt)

SELECT area_code,is_ym,CMCODE,SUM(rmb_amt)/10000,SUM(usd_amt)/10000

FROM BGD_AREA_CM_M_BASE_T

WHERE ym >= vs_ym_beg

AND ym <= vs_ym_end

GROUP BY area_code,CMCODE;

DBMS_OUTPUT.put_line('ins 当月记录 ='||SQL%rowcount||' 条 ');

-- 遍历游标处理后更新到表。遍历游标有几种方法，用 for 语句是其中比较直观的一种。

FOR rec IN cur_1 LOOP

UPDATE xxxxxxxxxxx_T

SET rmb_amt_sn = rec.rmb_amt_sn,usd_amt_sn = rec.usd_amt_sn

WHERE area_code = rec.area_code

AND CMCODE = rec.CMCODE

AND ym = is_ym;

END LOOP;

COMMIT;

-- 错误处理部分。 OTHERS 表示除了声明外的任意错误。 SQLERRM 是系统内置变量保存了当前错误的详细信息。

EXCEPTION

WHEN OTHERS THEN

vs_msg := 'ERROR IN xxxxxxxxxxx_p('||is_ym||'):'||SUBSTR(SQLERRM,1,500);

ROLLBACK;

-- 把当前错误记录进日志表。

INSERT INTO LOG_INFO(proc_name,error_info,op_date)

VALUES('xxxxxxxxxxx_p',vs_msg,SYSDATE);

COMMIT;

RETURN;

END;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/13923422/viewspace-1017350/，如需转载，请注明出处，否则将追究法律责任。

广播电视节目制作经营许可证(京) 字第1234号中国互联网协会会员