PL/SQL实现Oracle数据库任务调度

核心提示：在数据库操作中时常会有这样的情况发生，由于一时的疏忽而误删或误改了一些重要的数据，PL/SQL实现Oracle数据库任务调度，另外还有一些重要的任务需要周期性地运行，显然，掌握一定的应用程序开发能力是很有必要的，系统通过本文能够给读者一定的启发，前一类问题主要是数据备份与恢复方面的，而后一类则主要是系统的任务调度

在数据库操作中时常会有这样的情况发生，由于一时的疏忽而误删或误改了一些重要的数据，另外还有一些重要的任务需要周期性地运行。显然，前一类问题主要是数据备份与恢复方面的，而后一类则主要是系统的任务调度。本文将针对这两类问题，从应用程序开发角度给出一个解决方法。

一．技术基础

由于本文是使用PL/SQL作为开发平台来提供解决方案，所以首先了解相关的背景知识。

PL/SQL本身只是作为SQL语句的一个补充，通过引入过程化的概念来增强数据库处理能力。然而，相对于C,C++,JAVA等过程化语言来说，PL/SQL的处理功能依然不够强大。为此，Oracle数据库提供了大量的应用程序开发包，来增强应用程序开发能力。根据本文的主题，介绍如下两个开发包：DBMS_FLASHBACK和DBMS_JOB。

1． DBMS_FLASHBACK包主要是用来进行倒叙查询使用的，即通过设置查询时间来确定该时刻下的查询结果。一般情况下，我们平时使用的查询是查询当前时间（sysdate）下的数据。使用DBMS_FLASHBACK包就可以查询以前数据的状态，这一功能对于误处理的情形而言就显得极为重要。下面是该包中的两个主要函数介绍：

·Enable与disable：分别是启动和关闭倒叙查询功能。应该注意的是，每次启动倒叙查询之前应首先关闭倒叙模式。

·Enable_at_time：设置查询的时间点，它是以当前时间为起点进行设置的。

2． DBMS_JOB包是用来对PL/SQL块进行调度的实用包，它允许PL/SQL块在指定的时间内自动运行，类似于VC中的Settimer这样的定时器。为便于该包的运行，需要首先设置两个init.ora参数：

·JOB_QUEUE_PROCESS指定启动的后台处理数。如果它是0或没有被设置，将没有后台处理进入作业，它们也就不会运行。

·JOB_QUEUE_INTERVAL以秒为单位，指定每一个过程在检查新的作业前等待的时间。在JOB_QUEUE_INTERVA所指定的时间内，一个作业最多只能运行一次。

设置好这两个参数后，就可对程序进行调度了，该包主要使用SUBMIT函数进行调度，该函数的原型为：

submit(返回的作业号,程序过程名,sysdate,下次运行的时间)；

二．数据恢复

数据恢复是数据库本身一个极其重要的功能，通常重要的数据可以通过其系统的数据备份功能来实现，所以在实际的开发中，重要的数据往往容易恢复，反而是一些平常的数据因为误操作而引起一些麻烦。

对于有经验的开发人员来说，往往会对那些开发需要的基表（基表就是提供数据源的数据表）做一些备份。这样，即使以后出现一些数据误操作也不会导致重大的事故。

更为实用而又很少为开发人员所使用的方法就是采用倒叙查询，鉴于前面已经有了一定的技术铺垫，现在就可以使用DBMS_FLASHBACK包来对数据进行恢复了。为方便讲述，假定一个基表emp_table，其表记录如下：

Emp_no	Emp_name	Emp_salary
001	Jacky	5000
002	Rose	6000
003	John	7000

即此表仅有3条记录，那么由于对数据库的误操作，导致第一条记录被删除，那么执行下面的SQL语句：

select * from emp_table;

其执行结果为：

Emp_no Emp_name Emp_salary 002 Rose 6000 003 John 7000

由于已经执行了提交操作（COMMIT），所以无法进行回滚（ROLLBACK），这样原来的数据就无法用正常方法进行恢复。不过，由于误操作的时候在不久以前（假设是5分钟之前），在这种情况下，可以使用DBMS_FLASHBACK包来恢复数据，可以在SQL*PLUS里键入如下代码：

execute dbms_flashback.enable_at_time(sysdate-5/1440);

此时，将数据库调整到5分钟之前的状态，如果再执行查询表的命令就会为如下结果：

Emp_no Emp_name Emp_salary 001　　Jacky 　5000 002　　Rose　　6000 003　　John　　7000

那么就可以在此时将其数据备份到emp_table_bk，即：

create table emp_table_bk as select * from emp_table;

这样，就把以前误操作的数据给恢复回来了。

从上面的结果看的出，调用DBMS_FLASHBACK包的ENABLE_AT_TIME函数，可以将数据库的当前查询时间调整到以前，这样给数据恢复提供了帮助。

在使用DBMS_FLASHBACK包的时候还应该注意以下几点：

·倒叙查询是有前提的，即该数据库必须具有撤消管理功能。具体做法是，DBA应该建立一个撤消表空间，并启动自动撤消管理，并建立一个撤消保留时间窗。这样，Oracle将在撤消表空间中维护足够的撤消信息以便在保留时间内支持倒叙查询。

·由于撤消表空间的大小直接决定了倒叙查询执行的成败。即撤消表空间越大，那么可以查询的时间可以越早，那么对于一般的撤消表空间的大小，为了保证倒叙查询的成功，尽量查询5天以内的数据，这样成功的可能性更高一些。

三．任务调度

在UNIX系统中，任务与进程的概念是等同的，即当系统执行一段程序代码时会自动给其分配一个进程号和任务号，这样使用进程号和任务号就可以对该任务进行操作（如挂起，停止，启动等）。而Oracle数据库内部也存在任务调度，比如，需要对某一操作进行周期性的执行，或者是在某事件发生的时候才执行。一般性的做法是使用触发器，即将所有操作封装在触发器里，然后通过指定触发事件即可将该操作等待执行。另外，还可以直接利用操作系统来实现，比如在Windows平台就可以编写Windows脚本并结合"任务计划"来实施；如果在Unix平台，就可以写Shell来实现任务的周期性的执行操作。

而这里主要是采用ORACLE数据库的DBMS_JOB包来实现的。

例如，由于每个月都需要对员工进行考评以进行薪水的调整，那么就需要对emp_table表进行更新处理。更新处理代码如下：

create or replace procedure salary_upt(v_emp_no varchar2,v_salary number) as begin update emp_table set emp_salary=v_salary where emp_no=v_emp_no; commit; end ; /

为了定期每个月都运行上面的程序，可以执行如下代码：

Variable v_jobNum number; Begin Dbms_job.submit(:v_jobNum,'salary_upt',sysdate,'sysdate+30'); Commit; End; /

submit执行后将使得salary_upt过程马上执行。在上面的代码中，v_jobNum是该作业返回的作业号（任务号），后面两个时间分别为开始时间和结束时间，所以salary_upt过程将每隔30天执行一次salary_upt程序，以此达到了定期更新的目的。

如果要禁止该作业的继续执行，可以执行下面的命令：

dbms_job.remove(:v_jobNum);

使用DBMS_JOB包来实现任务的调度便于跟应用程序集成，有时候这样处理更为的便捷。

四．小结

很多时候，数据库的功能可以通过应用程序来进行扩展，对于进行后台数据库开发操作的用户而言，除了对数据库整体架构熟悉以外，掌握一定的应用程序开发能力是很有必要的。系统通过本文能够给读者一定的启发。

本文的开发环境为：

服务器端：UNIX+ORACLE9.2

客户端：WINDOWS2000 PRO+TOAD（或者SQL*PLUS）

本文中的代码在上述环境已调试通过。