存储过程的调试和定时任务的设置

最新推荐文章于 2023-06-03 15:32:27 发布

ancky_zhang

最新推荐文章于 2023-06-03 15:32:27 发布

阅读量2k

点赞数

分类专栏： DB 文章标签：任务存储 date null oracle sql

本文链接：https://blog.csdn.net/ancky_zhang/article/details/5658156

版权

DB 专栏收录该内容

8 篇文章 0 订阅

订阅专栏

昨天写了一个存储过程，并设置了定时任务，每天的凌晨1点执行。

今天打开来定时任务一看，next-date 又变为了 “4000-1-1” ，而且，broken 变为了 yes。因为昨天就发现有这个问题，我以为是next-date设置有问题，今天过来一看，果然又 broken了！郁闷。。。

下面就我从发现问题到解决问题的顺序展开来总结吧。

Google吧。。。还能怎么着。。。看看到底这个 next-date 和 interval 有什么区别！ next-date到底该怎么设置。

就像上篇文章说的：

3、next_date -------下次开始执行的时间，这个任务从这个next-date开始生效执行

Next_date参数是用来调度任务队列中该任务下一次运行的时间。这个参数对于DBMS_JOB.SUBMIT和DBMS_JOB.BROKEN这两个过程确省为系统当前时间，也就是说任务将立即运行。

当将一个任务的next_date参数赋值为null时，则该任务下一次运行的时间将被指定为4000年1月1日，也就是说该任务将永远不再运行。在大多数情况下，这可能是我们不愿意看到的情形。但是，换一个角度来考虑，如果想在任务队列中保留该任务而又不想让其运行，将next_date设置为null却是一个非常简单的办法。

Next_date也可以设置为过去的一个时间。这里要注意，系统任务的执行顺序是根据它们下一次的执行时间来确定的，于是将next_date参数设置回去就可以达到将该任务排在任务队列前面的目的。这在任务队列进程不能跟上将要执行的任务并且一个特定的任务需要尽快执行时是非常有用的。

4、Interval ---------在这个任务生效后，每隔 interval的时间定时执行一次任务。

Internal参数是一个表示Oracle合法日期表达式的字符串。这个日期字符串的值在每次任务被执行时算出，算出的日期表达式有两种可能，要么是未来的一个时间要么就是null。这里要强调一点：很多开发者都没有意识到next_date是在一个任务开始时算出的，而不是在任务成功完成时算出的。

当任务成功完成时，系统通过更新任务队列目录表将前面算出的next_date值置为下一次任务要运行的时间。当由interval表达式算出next_date是null时，任务自动从任务队列中移出，不会再继续执行。因此，如果传递一个null值给interval参数，则该任务仅仅执行一次。

通过给interval参数赋各种不同的值，可以设计出复杂运行时间计划的任务。本文后面的“任务间隔和日期算法”将对interval表达式进行详细讨论，并给出一个实际有用interval表达式的例子。

如果定时任务执行存储过程时出现异常，则，定时任务broken掉，并且next-date 自动改为 “4000-1-1”，所以，在建立定时任务时，先把存储过程写好了，保证没有错误了，最好把异常处理掉。才能避免定时任务broken掉。

那就手动执行一下定时任务试试：

SQL> begin
2 dbms_job.run(21); ---21为job 的序号
3 end;
4 /

好像报错为 “sys.dbms_job.run(21); ” 和 “sys.dbms_ijob.run(21); ” 之类的。一开始我以为我登录PL/SQL的用户的权限不够，就试试能不能查到我的job：

SQL> select job,next_date,next_sec,failures,broken from user_jobs;

JOB NEXT_DATE NEXT_SEC FAILURES BROKEN
---------- ----------- ---------------- ---------- ------
21 2010-6-10 1 01:00:00 0 N

有结果，说明有这个权限。

是不是管理定时任务的进程数为0？

      show parameter job_queue_processes;
      必须>0，否则执行下面的命令修改：
      alter system set job_queue_processes=5;

查看一下：

SQL> show parameter job_queue_processes;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
job_queue_processes integer 10

既有权限，而且进程数 > 0 ！那会是什么原因呢？？？？？？？？？？？？？异常郁闷。。。。。。要崩溃了。。。。。。。。。。。。。

后来同事提醒：是不是procedure 出问题了？？我还肯定的说“没有啊！我还执行了呢， successful！”。然后，他让我执行给他看。。我调出来procedure后，按 F8。。。。。。。。。。。。。。。。。。。。。。。。。。。“你根本没执行！那是编译成功！compile successful！”他惊讶的说。。。。。那怎么执行呢？？怎么调用 procedure呢？？？怎么调试procedure呢？？怎么debug呢？？

先执行一下procedure：

SQL> execute update_merinfo_fee_rate

begin update_merinfo_fee_rate; end;

   ORA-06511: PL/SQL: 游标已经打开
   ORA-06512: 在 "PLAT.UPDATE_MERINFO_FEE_RATE", line 2
   ORA-06512: 在 "PLAT.UPDATE_MERINFO_FEE_RATE", line 6
   ORA-06512: 在 line 1

报错了。。。。而且，现在job broken掉了。。。。确实是procedure出问题了！！！

那就debug吧：

在我的procedure上右击 -----“test”。在弹出的窗口中按F9 开始debug。。并逐步执行，发现程序在建立游标和打开游标之间反复跳，就是不往下执行！

继续Google。。。。。。。“ORA-06511: PL/SQL: 游标已经打开”！呵呵。。。。果然还有“同错中人”啊！

帮解决的人就说了一句话：“用for循环遍历游标时，不用手动的 open 和 close 游标！”！！！哦？？似乎找到了问题的症结所在！但是对不对呢？？看看有没有更权威的解释。。继续搜搜。。。。。

忽然看到了新东方的培训课程：游标的用法！

“PL/SQL 语言提供了游标for循环的语句，自动执行游标的 open 、 fetch、close 语句和循环语句的功能；当进入循环体时，游标for循环语句自动打开游标，并提取第一行游标数据，当程序处理完当前行时，自动提取下一行数据供程序处理。当提取完结果集中的所有数据行后，结束循环，并自动close掉游标。”！！！！！！！！！

一语惊醒梦中人啊！！

原来的procedure代码：

create or replace procedure update_merinfo_fee_rate is
       Cursor cusor_1 is select * from pmmerinfo ; --取得商户表的游标，目的是遍历取得商户号和费率
       fee_rate float := 1.5; --定义费率变量，并赋初值
begin
open cusor_1;----打开游标
for cur_result in cusor_1 LOOP--遍历游标
      begin--取得一个商户的费率，并修改订单信息表中的手续费
           fee_rate := to_number(cur_result.transfeerate);
           update ofordertradea ab set ab.transfee = (ab.transamt*fee_rate) where ab.merchantid = cur_result.merchantid and ab.transtime >= to_char((sysdate-1) , 'yyyymmdd') and ab.transtime < to_char((sysdate) , 'yyyymmdd');
      end;

end LOOP;--遍历游标结束
close cusor_1; --关闭游标

end update_merinfo_fee_rate;--存储过程结束