oracle通过job定时执行任务

oracle通过job定时执行任务2010年12月11日星期六17:29在项目中，经常会遇到需要定时完成的任务，比如定时更新数据，定义统计数据生成报表等等，其实这些事情都可以使用Oracle的Job来完成。下面考试大就结合我们实验室项目实际，简单介绍一下在Oracle数据库中通过Job完成自动创建表的方法。整个过程总共分为两步。虽然整个过程都非常简单，但是对于初学Oracle的生手还是有很多地方需要注意的。首先介绍一下，创建该JOB的背景，因为每天更新的直播和点播节目信息比较多，为了方便处理，需要每天创建一张表来记录更新的节目信息，当前数据库中已经有一张tbl_programme的表，每天创建的表的字段需要同tbl_programme保持一致，每天新创建的表的名称格式为tbl_programme_日期(例如：tbl_programme_20090214)规定每天晚上1点钟生成该天的新表。第一步：创建一个执行创建操作的存储过程在这一步首先要解决的问题就是构造表名。在Oracle中格式话输出时间可以用to_char函数来处理，例如：SQL>selectto_char(sysdate,'yyyy/mm/ddhh24:mi:ss')fromdual;TO—CHARISYSDATE’YYYY/MM/DDHHZ2009/02/1417:22:41以上SQL格式化输出了时间，要得到我们所需要的格式直接修改一下SQL即可SQL>selectto_char(sysdate,'yyyymmdd')什omdual;TO_CHAR(SYSDATE,ZYYYYMMDD/)20090214得到时间格式字符串后我们就可以将表名的前缀和时间连接在一起形成完整的表名。这里需要注意，在Oracle中链接两个字符串需要使用、||'符号，而在SqlServer中直接使用'+'号就可以了，因为我以前一直在SqlServer下编程，好久都没编写Oracle的SQL所以费了很大的功夫才发现这个问题。完整的Sql就是SQL>select'tbl_programme_，||to_char(sysdate,'yyyymmdd。fromdual;TBL_PROGRAMME_1|TO_CHAR(SYSDtbl_programme_20090214接下来就是创建表的代码了，因为新表需要tbl_programme保持一致，所以直接CTAS来创建表那是非常适合的了，代码如下：Createtabletablenameasselect*fromtbl_programme如果需要指定一个TableSpace则将该SQL做适当修改：Createtabletablenametablespacep2passelect*fromtbl_programme所以整个创建存储过程的SQL就是createorreplaceproceduresp_createtab_tbl_programmeAuthidCurrent_Userastabnamevarchar(200);beginselect'TBL_PROGRAMME_'||to_char(sysdate,'yyyymmdd')intotabnamefromdual;--createtabletabnameasselect*fromtbl_programmewhere1!=1;executeimmediate'createtable'||tabname||'tablespacep2passelect*fromtbl_programmewhere1!=1';commit;end;/这里还需要注意一下在Oracle里面如果要对一个变量赋值的话有两种方式：使用：=进行赋值使用select„xjkxj'into变量名称fromtabname另外，在存储过程中定义变量的时候一般放在as/is后begin前面。在存储过程一般是不能直接使用createtable，truncatetable这类似的语句的，如果要使用这些语句必须使用excuteimmediate+所要执行的sql语句来实现。注意上面用红色标志的语句：AuthidCurrent_User这个语句比较重要，如果我们在创建存储过程的时候不添加这条语句执行该存储过程将不会成功，原因是默认情况向存储过程是没有Createtable等权限的，即使当前用户有DBA的权限也不行，如果存储过程中存在创建表的操作，可以有以下两种方式来解决该问题。显示的赋予该用户Createtable的权限，grantcreatetabletouser.在存储过程中使用AuthidCurrent_User标识使用当前用户的权限。第二步：创建JOB创建JOB就比较简单了，下面就是创建JOB的代码每天晚上1电job启动一次，执行sp_createtab_tbl_programme存储过程。VARIABLEtestjobidnumber;beginsys.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：notallvariablesbound就是没有定义变量的意思。一定记的在使用submit方法时定义jobid变量。下面是常有的设置Interval的方法：2每天固定时间运行，比如早上8：10分钟：Trunc(Sysdate+1)+8/242每天：trunc(sysdate+1)2每周：trunc(sysdate+7)2每月：trunc(sysdate+30)2每个星期日：next_day(trunc(sysdate)，'SUNDAY')2每天6点：trunc(sysdate+1)+6/242半个小时：sysdate+30/1440需要用到的完整SQL如下：--ExportfileforuserP2P---CreatedbyAdministratoron2009-2-14,15:45:18--spoolgjgdp2p(v1.3).logpromptpromptCreatingprocedureSP_CREATETAB_TBL_PROGRAMMEpromptpromptcreateorreplaceproceduresp_createtab_tbl_programmeAuthidCurrent_Userastabnamevarchar(200);beginselectTBL_PROGRAMME_'||to_char(sysdate,'yyyymmdd')intotabnamefromdual;--createtabletabnameasselect*fromtbl_programmewhere1!=1;executeimmediate'createtable'||tabname||'tablespacep2passelect*fromtbl_programmewhere1!=1';commit;end;/VARIABLEtestjobidnumber;beginsys.dbms_job.submit(:testjobid,'sp_createtab_tbl_programme;',trunc(sysdate+1)+1/24,'trunc(sysdate+1)+1/24');commit;end;/spooloff第三步：异常情况处理JOB不能运行情况处理1.先来了解一下JOB的参数说明：与job相关的参数一个是job_queue_processes，这个是运行JOB时候所起的进程数，当然系统里面JOB大于这个数值后，就会有排队等候的，最小值是0，表示不运行JOB，最大值是36,在OS上对应的进程时SNPn，9i以后OS上管理JOB的进程叫CJQn.可以使用下面这个SQL确定目前有几个SNP/CJQ在运行。select*fromv$bgprocess这个paddr不为空的snp/cjq进程就是目前空闲的进程，有的表示正在工作的进程。另外一个是job_queue_interval，范围在13600之间，单位是秒，这个是唤醒JOB的process，因为每次snp运行完他就休息了，需要定期唤醒他，这个值不能太小，太小会影响数据库的性能。诊断：先确定上面这两个参数设置是否正确，特别是第一个参数，设置为0了，所有JOB就不会跑，确认无误后，我们继续向下。使用下面的SQL察看JOB的的broken，last_date和next_date，last_date是指最近一次job运行成功的结束时间，next_date是根据设置的频率计算的下次执行时间，根据这个信息就可以判断JOB上次是否正常，还可以判断下次的时间对不对，SQL如下：select*fromdba_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如下：declareBEGINDBMS_JOB.BROKEN（＜JOB_ID＞，FALSE）；END；使用下面的SQL查询是否JOB还在Runningselect*fromdba_jobs_running如果发现JOB已经Run了很久了还没有结束，就要查原因了。一般的JOBrunning时会锁定相关的相关的资源，可以查看一下v$access和v$locked_object这两个view，如果发现其他进程锁定了与JOB相关的Object，包括PKG/Function/Procedure/Table等资源，那么就要把其他进程删除，有必要的话，把JOB的进程也删除，再重新跑看看结果。如果上面都正常，但是JOB还不run，怎么办？那我们要考虑把JOB进程重启一次，防止是SNP进程死了造成JOB不跑，指令如下：altersystemsetjob_queue_processes=0关闭job进程，等待510秒钟altersystemsetjob_quene_processes=5恢复原来的值附录：Oracle存储过程基本语法1.基本结构CREATEORREPLACEPROCEDURE存储过程名字（参数1INNUMBER,参数2INNUMBER）is变量1INTEGER:=0;变量2DATE;BEGINEND存储过程名字2.SELECTINTOSTATEMENT将select查询的结果存入到变量中，可以同时将多个列存储多个变量中，必须有一条记录，否则抛出异常（如果没有记录抛出NO_DATA_FOUND）例子：BEGINSELECTcol1,col2into变量1,变量2FROMtypestructwherexxx;EXCEPTIONWHENNO_DATA_FOUNDTHENxxxx;END;・・・3.IF判断IFV_TEST=1THENBEGINdosomethingEND;ENDIF;while循环WHILEV_TEST=1LOOPBEGINXXXXEND;ENDLOOP;变量赋值V_TEST:=123;用forin使用cursor・・・ISCURSORcurISSELECT*FROMxxx;BEGINFORcur_resultincurLOOPBEGINV_SUM:=cur_result.列名1+cur_result.列名2END;ENDLOOP;END;带参数的cursorCURSORC_USER(C_IDNUMBER)ISSELECTNAMEFROMUSERWHERETYPEID=C_ID;OPENC_USER(变量值)；LOOPFETCHC_USERINTOV_NAME;EXITFETCHC_USER%NOTFOUND;dosomethingENDLOOP;CLOSEC_USER;用pl/sqldeveloperdebug连接数据库后建立一个TestWINDOW在窗口输入调用SP的代码，F9开始debug，CTRL+N单步调试通过一个实际的例子学习Oracle存储过程创建存储过程CREATEORREPLACEPROCEDURExxxxxxxxxxx_p(--参数IN表示输入参数，OUT表示输入参数，类型可以使用任意Oracle中的合法类型。is_ymINCHAR)AS--定义变量vs_msgVARCHAR2(4000);--错误信息变量vs_ym_begCHAR(6);--起始月份vs_ym_endCHAR(6);--终止月份vs_ym_sn_begCHAR(6);--同期起始月份vs_ym_sn_endCHAR(6);--同期终止月份--定义游标(简单的说就是一个可以遍历的结果集)CURSORcur_1ISSELECTarea_code,CMCODE,SUM(rmb_amt)/10000rmb_amt_sn,SUM(usd_amt)/10000usd_amt_snFROMBGD_AREA_CM_M_BASE_TWHEREym>=vs_ym_sn_begANDym<=vs_ym_sn_endGROUPBYarea_code,CMCODE;BEGIN--用输入参数给变量赋初值，用到了Oralce的SUBSTRTO_CHARADD_MONTHSTO_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)--先删除表中特定条件的数据。DELETEFROMxxxxxxxxxxx_TWHEREym=is_ym;--然后用内置的DBMS_OUTPUT对象的put_line方法打印出影响的记录行数，其中用到一个系统变量SQL%rowcountDBMS_OUTPUT.put_line(，del上月记录=，||SQL%rowcount||条')；INSERTINTOxxxxxxxxxxx_T(area_code,ym,CMCODE,rmb_amt,usd_amt)SELECTarea_code,is_ym,CMCODE,SUM(rmb_amt)/10000,SUM(usd_amt)/10000FROMBGD_AREA_CM_M_BASE_TWHEREym>=vs_ym_begANDym<=vs_ym_endGROUPBYarea_code,CMCODE;DBMS_OUTPUT.put_line(，ins当月记录='||SQL%rowcount||条。；--遍历游标处理后更新到表。遍历游标有几种方法，用for语句是其中比较直观的一种。FORrecINcur_1LOOPUPDATExxxxxxxxxxx_TSETrmb_amt_sn=rec.rmb_amt_sn,usd_amt_sn=rec.usd_amt_s