Oracle通过Job调用存储过程

这是本人第一次处理Oracle的存储过程以及计划任务（job）的事情；期间遇到了很多问题在此将这次试用job调用自己的编写的存储过程的过程中遇到的问题做一总结；任务：实时采集系统会每5分钟给中心系统传送一个流量数据，但是没有给出累计流量数据；1、编写一个存储过程WATERQCAL用于计算流量增量以及得出最后的累计流量；2、用一个任务计划每隔5分钟，统一进行执行一次WATERQCAL存储过程；创建存储过程代码如下：CREATEORREPLACEPROCEDUREWaterQCalAS--如果用引号将存储过程名引起来后就会保存成WaterQCal存储过程；如果不引起来的话最终保存的是WATERQCAL；oldACC_WNUMBER;oldACC_W_PQNUMBER;newACC_WNUMBER;BEGINDECLARE--查询出一个集合CURSORmpcd_numberISSELECTDISTINCTMP_CDfromWR_MP_Q_RWHEREACC_WisNULL;--定义行游标变量rcd_numbermpcd_number%ROWTYPE;--循环开始BEGIN--开启游标openmpcd_number;loop--开始游标循环操作fetchmpcd_numberintorcd_number;exitwhenmpcd_number%notfound;--开始查询某一个个测站数据DECLARE--查询出一个某测站的所有未计算浏览的集合CURSORsinSTBISselect*fromWR_MP_Q_RWHEREACC_WisnullandMP_CD=rcd_number.MP_CDorderbyTMASC;--定义行游标变量sinStsinSTB%ROWTYPE;BEGIN--开启单站游标opensinSTB;loopfetchsinSTBintosinSt;exitwhensinSTB%notfound;BEGIN--开始计算流量--查询出最有一条计算过流量的记录BEGINselectACC_WintooldACC_WfromWR_MP_Q_RWHEREACC_WisnotnullandMP_CD=sinSt.MP_CDandrownum=1orderbyTMDESC;exception--处理异常没有数据时；该如何处理；因为有异常处理此处的select语句以及异常都必须单独用beginend括起来；whenno_data_foundthenoldACC_W:=0;END;--计算流量增量oldACC_W_PQ:=ABS(sinSt.MP_Q)/12;--计算当前累计流量newACC_W:=ABS(oldACC_W)+ABS(oldACC_W_PQ);--更新当前累计流量updateWR_MP_Q_RsetACC_W=newACC_WwhereMP_CD=sinSt.MP_CDandTM=sinSt.TM;END;ENDLOOP;closesinSTB;--关闭游标END;ENDloop;closempcd_number;END;END;创建Oracle计划任务的代码如下：declarejobNumNUMBER;beginsys.dbms_job.submit(job=jobNum,what='SWATER.WaterQCal;',next_date=trunc(sysdate)+1+8/1440,interval='trunc(sysdate,''mi'')+1+8/1440');commit;end;/通过plsql工具创建：在编制存储过程时应该注意的是1、每一个语句结束时都必须采用；结束。否则会报sql语句不完整；2、CREATEORREPLACEPROCEDUREPROCEDURE_NAMEAS--该语句部分PROCEDURE_NAME不能够用引号引起来；如果引号引起来的话，名字中的小写字母之类的还会原样保留；但是Oracle默认是需要将存储过程的名字转换成大写的；并且后面创建Job是如果输入之前带小写字母的名字时又会转换成大写的时候就会报找不到存储过程；3、for循环来操作游标，不用open游标;以及close游标操作否则会报错；4、针对每一个包含异常处理语句的sql都必须防止在begin以及end之中；否则可能会出现部分语句不执行，直接跳转到下一个end处；5、循环处理1）、For循环For...in...LOOP--执行语句endLOOP;(1)循环遍历游标createorreplaceproceduretest()asCursorcursorisselectnamefromstudent;namevarchar(20);beginfornameincursorLOOPbegindbms_output.putline(name);end;endLOOP;endtest;(2)循环遍历数组createorreplaceproceduretest(varArrayinmyPackage.TestArray)as--(输入参数varArray是自定义的数组类型，定义方式见标题6)inumber;begini:=1;--存储过程数组是起始位置是从1开始的，与java、C、C++等语言不同。因为在Oracle中本是没有数组的概念的，数组其实就是一张--表(Table),每个数组元素就是表中的一个记录，所以遍历数组时就相当于从表中的第一条记录开始遍历foriin1..varArray.countLOOPdbms_output.putline('TheNo.'||i||'recordinvarArrayis:'||varArray(i));endLOOP;endtest;2)、While循环while条件语句LOOPbeginend;endLOOP;E.gcreateorreplaceproceduretest(iinnumber)asbeginwhilei10LOOPbegini:=i+1;end;endLOOP;endtest;3).游标的使用Oracle中Cursor是非常有用的，用于遍历临时表中的查询结果。其相关方法和属性也很多，现仅就常用的用法做一二介绍：(1)Cursor型游标(不能用于参数传递)createorreplaceproceduretest()iscusor_1Cursorisselectstd_namefromstudentwhere...;--Cursor的使用方式1cursor_2Cursor;beginselectclass_nameintocursor_2fromclasswhere...;--Cursor的使用方式2可使用ForxincursorLOOP....endLOOP;来实现对Cursor的遍历endtest;(2)SYS_REFCURSOR型游标，该游标是Oracle以预先定义的游标，可作出参数进行传递createorreplaceproceduretest(rsCursoroutSYS_REFCURSOR)iscursorSYS_REFCURSOR;namevarhcar(20);beginOPENcursorFORselectnamefromstudentwhere...--SYS_REFCURSOR只能通过OPEN方法来打开和赋值LOOPfetchcursorintoname--SYS_REFCURSOR只能通过fetchinto来打开和遍历exitwhencursor%NOTFOUND;--SYS_REFCURSOR中可使用三个状态属性：---%NOTFOUND(未找到记录信息)%FOUND(找到记录信息)---%ROWCOUNT(然后当前游标所指向的行位置)dbms_output.putline(name);endLOOP;rsCursor:=cursor;endtest;编辑JOB过程中该注意事项1、what='SWATER.WaterQCal;'的WaterQCal的存储过程名必须以;结尾；否则会报如下错误：[Err]ORA-06550:第1行,第110列:PLS-00103:出现符号END在需要下列之一时：:=.(@%;符号;被替换为END后继续。ORA-06512:在SYS.DBMS_JOB,line82ORA-06512:在SYS.DBMS_JOB,line140ORA-06512:在line32、如果前面创建存储过程中将存储过程名用引号引起来后，因为存储郭晨被保存成了WaterQCal；而what中指定的存储过程SWATER.WaterQCal会被转换SWATER.WATERQCAL；所以会报如下错误：[Err]ORA-06550:第1行,第93列:PLS-00201:必须声明标识符'SWATER.WATERQCAL'ORA-06550:第1行,第93列:PL/SQL:StatementignoredORA-06512:在SYS.DBMS_JOB,line82ORA-06512:在SYS.DBMS_JOB,line140ORA-06512:在line33、时间间隔设置必须是在指定的时间必须是当前时间的后面的值（即如果由interval计算出来的nexttime如果在当前时间之前，会怎么处理？就没法处理，会报错），否则会报如下错误ORA-23420:间隔必须以将来的一个时间作评估ORA-06512:在SYS.DBMS_JOB,line57ORA-06512:在SYS.DBMS_JOB,line134ORA-06512:在line34、如果sys.dbms_job.submit(job=:job,用的是:job作为参数，并且是通过查询语句执行的话会报如下错误：[Err]ORA-01008:并非所有变量都已绑定解决办法如本示例：declarejobNUMBER;--定义变量；并采用sys.dbms_job.submit(job=job方式去掉冒号；5、参数说明1)、job参数job是一个整数，用来唯一地标示一个任务。该参数既可由用户指定也可由系统自动赋予，这完全取决于提交任务时选用了那一个任务提交过程。DBMS_JOB.SUBMIT过程通过获得序列SYS.JOBSEQ的下一个值来自动赋予一个任务号。该任务号是作为一个OUT参数返回的，所以调用者随后可以识别出提交的任务。而DBMS_JOB.ISUBMIT过程则由调用者给任务指定一个识别号，这时候，任务号的唯一性就完全取决于调用者了。除了删除或者重新提交任务，一般来说任务号是不能改变的。即使当数据库被导出或者被导入这样极端的情况，任务号也将被保留下来。所以在执行含有任务的数据的导入/导出操作时很可能会发生任务号冲突的现象。2)、whatwhat参数是一个可以转化为合法PL/SQL调用的字符串，该调用将被任务队列自动执行。在what参数中，如果使用文字字符串，则该字符串必须用单引号括起来。what参数也可以使用包含我们所需要字符串值的VARCHAR2变量。实际的PL/SQL调用必须用分号隔开。在PL/SQL调用中如果要嵌入文字字符串，则必须使用两个单引号。what参数的长度在Oracle7.3中限制在2000个字节以内，在Oracle8.0以后，扩大到了4000个字节，这对于一般的应用已完全足够。该参数的值一般情况下都是对一个PL/SQL存储过程的调用。在实际应用中，尽管可以使用大匿名Pl/SQL块，但建议大家最好不要这样使用。还有一个实际经验就是最好将存储过程调用封装在一个匿名块中，这样可以避免一些比较莫名错误的产生。我来举一个例子，一般情况下，what参数可以这样引用：what=’my_procedure(parameter1);’但是比较安全的引用，应该这样写：what=’beginmy_procedure(parameter1);end;’任何时候，我们只要通过更改what参数就可以达到更改任务定义的目的。但是有一点需要注意，通过改