浅析Oracle存储过程触发器在数据同步中的应用

转载于： http://www.gotoread.com/mag/7939/contribution128091.html

内容摘要

本文介绍了Oracle 存储过程以及触发器的概念等基础知识，结合实际项目的实践，总结了触发器和存储过程在Oracle数据库间数据同步中的应用的经验，并附有相关的触发器及存储过程实例供参考。

正文

文字大小：大 中 小

浅析Oracle存储过程触发器在数据同步中的应用

杨 晶 蒋占生

（南阳理工学院，河南 南阳 邮编473004）

摘要：本文介绍了Oracle 存储过程以及触发器的概念等基础知识，结合实际项目的实践，总结了触发器和存储过程在Oracle数据库间数据同步中的应用的经验，并附有相关的触发器及存储过程实例供参考。

关键词：Oracle；存储过程；触发器；数据同步；数据整合；数据库链接；数据库服务

 

On the Application of Oracle Stored Procedure and Trigger in Data Synchronization

YANG jing , JIANG zhan-sheng

(Nanyang Institute of Technology ,Henan Nanyang 473004)

Abstract : the paper introduces the fundamental,such as concept etc, of the oracle Stored Procedure and triggers, combining actual project’s practices experience, summarizes the applications of the oracle Stored Procedure and Trigger in Data Synchronization,and provides the examples for reference.

Key words:oracle;stored procedure;trigger;data synchronization;data integration;database link;database server

1 引言

最近在为某单位做统一平台整合数据的系统，该单位原来拥有的多个业务子系统在不同时期由不同软件公司开发，采用不同的开发平台，所使用的数据库虽然同为Oracle数据库，但是版本不同。众多业务子系统给单位工作人员带来了诸多不便：例如必须牢记不同的密码进入不同的子系统才能完成相应业务；由于各个业务子系统使用的数据库不同，相同的数据几个业务子系统都要输入保存一遍；有时一个业务子系统数据改变了而另外几个业务子系统数据还维持原状等。

因此，开发新系统把所有业务子系统功能以及数据都整合到一起是势在必行。同时业务子系统还要能继续使用。项目组在和用户充分沟通后经过严格的需求分析、系统分析，确定系统结构及采用平台、开发工具：WEB服务器和数据库服务器设在同一台服务器上，服务器采用Linux为操作系统平台 ,前台WEB服务器采用Websphere，WEB系统使用JAVA语言开发，后台数据库服务器用oracle10g数据库（以下称综合库）。多个业务子系统数据库（以下称业务库）数据整合到综合库，随后业务库到综合库数据同步采用触发器和存储过程来实现。

现将有关开发和应用中的一些体会总结成文，供同行参考。

下面先了解一下oracle数据库存储过程和触发器。

2 ORACLE存储过程和触发器

2.1存储过程

2.1.1存储过程定义

类似编程时的函数,将常用的或复杂的操作，预先用PL/SQL语句写好并指定一个名称存储, 以后要数据库实现相同功能时,只需调用存储过程即可自动完成,跟函数调用类似。

2.1.2存储过程的优点

• 存储过程只在创造时进行编译，以后每次执行存储过程都不需再重新编译，而一般SQL语句每执行一次就编译一次,所以使用存储过程可提高数据库执行速度。

• 当对数据库进行复杂操作时(如对多个表进行Update,Insert,Query,Delete时），可将此复杂操作用存储过程封装起来与数据库提供的事务处理结合一起使用。

• 存储过程可以重复使用,可减少数据库开发人员的工作量。

• 安全性高,可设定只有某此用户才具有对指定存储过程的使用权。

2.1.3创建存储过程

不论是使用图形工具管理器还是Transact-SQL语句都需要使用Create Procedure语句创建存储过程，语法规则如下：

CREATE [OR REPLACE] PROCEDURE procedure_name

[(argument[{IN|OUT|IN OUT}]type, ...

argument[{IN|OU|IN OUT}]type)]{IS|AS}

procedure_body

参数含义：

procedure_name：存储过程名。

argument：参数名。

type：参数的类型。

proedure_body：过程代码PL/SQL块。

2.1.4执行存储过程

[[EXEC[UTE]]{ procedure_name|@procedure_name_var

[[@parameter=]{value|@variable[OUTPUT]|[DEFAULT]]

[,...n] }

参数含义：

procedure_name：存储过程名。

@procedure_name_var：局部定义的变量名，代表存储过程。

@parameter：存储过程的参数，在用Create Procedure语句创建存储过程中定义。

value：参数值。

@variable：保存参数或返回值的变量。

OUTPUT：指定存储过程必须返回一个参数。

DEFAULT：根据存储过程的定义，提供参数的默认值。

2.2 触发器

2.2.1 触发器定义

触发器是一种用来保障参照完整性的特殊的存储过程，它维护不同表中数据间关系的有关规则，比数据库本身标准的功能有更精细和更复杂的数据控制能力。当对指定的表进行某种特定操作（如：Insert,Delete或Update）时，触发器产生作用。

2.2.2创建触发器

CREATE [OR REPLACE] TRIGGER trigger_name

{BEFORE|AFTER|INSTEAD OF} {[DELETE][,][INSERT][,][UPDATE]}

ON {table|view}

[WHEN trigger_condition]

[FOR EACH ROW]

trigger_body;

参数含义：

trigger_name：触发器名称。

BEFORE：操作执行前激发。

AFTER：操作执行后激发。不能在视图上定义AFTER 触发器。

INSTEAD OF：触发器替代触发语句执行。在表或视图上，每个INSERT、UPDATE或DELETE 语句最多可以定义一个INSTEAD OF 触发器。

{[DELETE][,][INSERT][,][UPDATE]}：哪些操作语句将激活触发器。至少指定一个，允许任意顺序组合的这些关键字，选项多于一个时，用逗号分隔。

Table|view：触发器表或触发器视图。

[FOR EACH ROW]：行级触发器。

trigger_body：触发器的代码。

2.2.3触发器执行

触发器是一种特殊的存储过程，它在插入、删除或修改触发器表中数据时触发自动执行。

3 综合库和业务库数据整合及同步方案

数据整合目的是要把原来多个业务库数据有机地合并到综合库里面。

首先，创建的综合库，按照业务子系统数据库数目创建用户方案，每个用户方案对应一个业务子系统，业务子系统数据库的数据对象全部在综合库对应的用户方案下重新创建，但是所有业务子系统的共同数据在某个用户方案只存放一次，以视图形式提供给其它用户。

其次，依照原来所有业务子系统的最新备份数据，进行数据库初始化。

最后，要保证旧的业务子系统数据发生改变要同步到新的综合库里面。

数据同步操作可简单分为整表数据同步和增量数据同步两种情况。

3.1整表数据同步

整表数据同步是按照业务库的表数据，通过import语句一次把表数据全部导入综合库。

适合系统初始化阶段全部数据库表，以及系统运行过程中参数表等非主表以及数据量较小的表。每次同步前可以删除或者清空综合库内的相关数据表，然后执行import语句。

3.2增量数据同步

增量数据同步是运用触发器记录发生改变的数据到业务库日志，调用存储过程按照业务库日志，把业务库发生改变的数据同步到综合库里面。

适合主表以及数据量较大而且经常发生变化的数据表。

以下我们重点讨论利用数据库触发器以及存储过程实现的增量数据同步过程。

4使用数据库触发器和存储过程实现增量数据同步

由于原来的业务子系统需要继续运行，保持综合库数据与业务子系统数据一致性，我们要把业务子系统数据库发生改变的数据记录下来，同步到新的综合库里面去，鉴于早些年开发的业务子系统再次修改源程序已经不可能实现，所以我们只能从数据库上面做文章，在相关的数据库表上面建立触发器，使用触发器记录发生改变的数据。

4.1使用数据库触发器记录数据改变日志

为了保存发生改变的数据，需要在每个业务子系统数据库里创建一个系统更新日志表。

4.1.1系统更新日志表Update_Log

create table Update_Log

( tableName varchar(50), /*发生更新的表的名字*/

pk_name_value varchar(50), /*更新记录的主键及值*/

update_Type int, /*更新操作的类型（删0|修1|增2）*/

flag char(1) default ''0'', /*同步标记0未同步1同步完成*/

update_time date default sysdate, /*更新操作的时间*/

primary key (tableName,pk_name_value) )

注释：

1 触发器向Update_Log表记录日志时，如果Update_Log没有记录该表及该主键的任何信息(第一次操作),则按照表1规则处理；如果该表及该主键记录的数据已经存在（非第一次操作，业务库已经更新过该记录，但是同步到综合库的操作没有发生），则应当表2所示规则处理。

表1指定记录第一次日志操作规则

 

表2指定记录非第一次日志操作规则

 

4.1.2创建触发器

在业务子系统数据库需要增量同步的数据表上创建触发器，每当需要增量同步的数据表内数据有修改的时候对应触发器按照上面逻辑，在Update_Log表里面作日志记录。

以人口业务子系统为例，该业务子系统经常对人员基本信表进行操作，因此我们就在人员基本信息表上面创建触发器，记录人员基本信息表数据变化到系统日志表里面。

人员基本信息表名：MLPXXB；主键字段：id 字符型；

我们按功能分把触发器分、为插入、删除、修改触发器，使之更容易理解，。

插入触发器示例：

功能：表内该主键第一次操作，插入记录日志；非第一次，修改原来日志标记为修改1 。

create or replace trigger MLPXXB_insert

after insert on MLPXXB

for each row

declare

oldnum int;

pkNameValue varchar(50);

begin

pkNameValue:=''id='''''' || :new.id || '''''''';

select count(*) into oldnum from Update_Log where tableName=''MLPXXB'' and flag=''0'' and pk_name_value=pkNameValue;

if oldnum>0 then

update Update_Log set update_Type=1 where tableName=''MLPXXB'' and flag=''0'' and pk_name_value=pkNameValue;

else

insert into Update_Log(tableName,pk_name_value,update_Type) values (''MLPXXB'',pkNamevalue,2);

end if;

end;

篇幅有限后面两个触发器，具体代码就不再罗列。

4.1.3 触发器的执行效果

表MLPXXB数据改变时，激发上述触发器执行，在表Update_Log做数据更新日志。

4.2使用存储过程同步数据

同步数据是调用存储过程按照业务子系统数据库内Update_Log表记录的内容，把所有改变的记录逐一同步到综合库，保持综合库数据与业务子系统数据一致性。

存储过程要从业务子系统读取数据并写入的新综合库里面，为了操作方便，可以在综合库里面建立业务子系统的数据库链接，然后再使用数据库链接把业务子系统数据库涉及到的数据表以视图的形式放到综合库里面，即是针对要用到的业务子系统的数据表在综合库里面利用数据库链接创建相应的视图，读取业务库最新数据时可以直接读取视图数据。

我们以人员基本信息表数据改变增量同步综合库为例。

create or replace procedure proc_synchronism

as

pSqlstr varchar(200);

pSql varchar(200);

i integer :=0;

ptype integer;

pPK_name_value varchar(50);

strpPK_name_value varchar(100);

ptablename varchar(50);

strsql varchar(200);

begin

--先求该表需要同步的记录条数,然后一条条处理

select count(*) into i from update_Log where flag=''0'' ;

while (i>=1) loop

--读取标记

select update_Type,pk_name_value,tablename into ptype, pPK_name_value,ptablename from update_Log where rownum <2 and flag=''0'' ;

--处理数据

if ptype=0 then

strsql:='' delete from '' || ptablename || '' where '' || Ppk_Name_Value ;

execute immediate strsql;

end if;

if ptype=2 then

strsql:=''insert into '' || ptablename || '' select * from view_'' || ptablename || '' where '' || Ppk_Name_Value ;

execute immediate strsql;

end if ;

if ptype=1 then

strsql:=''delete from '' || ptablename || '' where '' || Ppk_Name_Value;

execute immediate strsql;

strsql:=''insert into '' || ptablename || '' select * from view_'' || ptablename || '' where '' || Ppk_Name_Value ;

execute immediate strsql;

end if ;

--记录处理完毕

strpPK_name_value:= REPLACE(pPK_name_value,'''''''','''''''''''');

strsql:=''update update_log set flag=''''1'''' where flag=''''0'''' and tableName= '''''' || ptableName || '''''' and pk_name_value='''''' || strpPK_name_value || '''''''' ;

execute immediate strsql;

i:=i-1;

end loop;

commit;

exception

when others then

rollback;

end;

最后,前台程序调用该存储过程实现业务子系统数据库数据到综合库数据增量同步。

结束语

触发器和存储过程在Oracle数据库间数据同步中有很广泛的应用，不仅可以实现复杂的业务逻辑，而且和用前台程序实现同样功能相比可以减少系统执行时间，大大提高系统效率。但是使用过多的触发器将降低整个数据库的性能。因此触发器使用要适当。

参考文献：

Kevin Owens著 欧阳宇 译 清华大学出版社 Oracle触发器与存储过程高级编程(第3版).2004

娄建安 余建华.科学出版社.Oracle SQL&PL/SQL基础教程 .2005

作者简介：杨晶(1978-)南阳理工学院软件学院讲师，解放军信息工程大学硕士研究生，在数据库和软件工程方向有深入研究，参编数据库及软件工程教材各一部。蒋占生(1978-)河南省经济管理学校 助教。