ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 在两个数据库之间进行数据同步

在两个数据库之间进行数据同步

原创 Linux操作系统 作者:壹頁書 时间:2013-11-25 00:26:55 0 删除 编辑
OLAP的第一步就是从业务系统中抽取数据到数据仓库系统。
除了ETL工具Kettle,也可以使用PL/SQL

创建Source表,模拟业务系统的数据表。
create table source as select sys_guid() id ,o.* from dba_objects o where rownum<1000;
alter table source add constraint PK_source primary key (id);

创建Target表,模拟数据仓库中的表。
create table target as select * from source where 1=0;
alter table target add constraint PK_target primary key (id);

创建DB LINK,remote模拟业务系统的数据库
create database link remote connect to username identified by xxxxxx using 'remote';

因为业务系统的数据是变化的,相较于数据仓库的表,他可能更新了一些数据,也可能修改了一些数据。
比对业务表和数据仓库表的数据,
    如果ID相同,并且数据有变化
        则根据ID更新数据仓库的表(target)
    如果业务系统的数据ID(source),还没有出现在数据仓库的表中(target)
        则在数据仓库的表中新增这个记录。

本质都是Oracle Merge的功能,只不过尝试另外几种方法。

1.merge

  1. merge into target t using (select * from source@remote) s
  2. on(t.id=s.id)
  3. when matched
  4. then
  5.     update set  t.owner=s.owner,
  6.                 t.object_name=s.object_name,
  7.                 t.subobject_name=s.subobject_name,
  8.                 t.object_id=s.object_id,
  9.                 t.data_object_id=s.data_object_id,
  10.                 t.object_type=s.object_type,
  11.                 t.created=s.created,
  12.                 t.last_ddl_time=s.last_ddl_time,
  13.                 t.timestamp=s.timestamp,
  14.                 t.status=s.status,
  15.                 t.temporary=s.temporary,
  16.                 t.generated=s.generated,
  17.                 t.secondary=s.secondary,
  18.                 t.namespace=s.namespace,
  19.                 t.edition_name=s.edition_name
  20. when not matched
  21. then
  22.     insert values
  23.     (    
  24.                 s.id,
  25.                 s.owner,
  26.                 s.object_name,
  27.                 s.subobject_name,
  28.                 s.object_id,
  29.                 s.data_object_id,
  30.                 s.object_type,
  31.                 s.created,
  32.                 s.last_ddl_time,
  33.                 s.timestamp,
  34.                 s.status,
  35.                 s.temporary,
  36.                 s.generated,
  37.                 s.secondary,
  38.                 s.namespace,
  39.                 s.edition_name
  40.     );
2.全局临时表。
    首先将远程业务系统的数据放入临时表,
    然后根据ID更新数据,如果数据的内容没有变化,则不更新。
    最后插入业务系统中新建的数据。

  1. create global temporary table tmp
  2. on commit preserve rows
  3. as
  4. select * from target where 1=0;

  5. insert into tmp select * from source@remote;

  6. update target t set
  7.             (
  8.                 t.owner,
  9.                 t.object_name,
  10.                 t.subobject_name,
  11.                 t.object_id,
  12.                 t.data_object_id,
  13.                 t.object_type,
  14.                 t.created,
  15.                 t.last_ddl_time,
  16.                 t.timestamp,
  17.                 t.status,
  18.                 t.temporary,
  19.                 t.generated,
  20.                 t.secondary,
  21.                 t.namespace,
  22.                 t.edition_name
  23.             )
  24.             =
  25.             (select
  26.                 tmp.owner,
  27.                 tmp.object_name,
  28.                 tmp.subobject_name,
  29.                 tmp.object_id,
  30.                 tmp.data_object_id,
  31.                 tmp.object_type,
  32.                 tmp.created,
  33.                 tmp.last_ddl_time,
  34.                 tmp.timestamp,
  35.                 tmp.status,
  36.                 tmp.temporary,
  37.                 tmp.generated,
  38.                 tmp.secondary,
  39.                 tmp.namespace,
  40.                 tmp.edition_name            
  41.             from tmp where t.id=tmp.id)
  42.     where exists(
  43.                     select * from tmp where tmp.id=t.id and not (
  44.                                 tmp.owner=t.owner and
  45.                                 tmp.object_name=t.object_name and
  46.                                 tmp.subobject_name=t.subobject_name and
  47.                                 tmp.object_id=t.object_id and
  48.                                 tmp.data_object_id=t.data_object_id and
  49.                                 tmp.object_type=t.object_type and
  50.                                 tmp.created=t.created and
  51.                                 tmp.last_ddl_time=t.last_ddl_time and
  52.                                 tmp.timestamp=t.timestamp and
  53.                                 tmp.status=t.status and
  54.                                 tmp.temporary=t.temporary and
  55.                                 tmp.generated=t.generated and
  56.                                 tmp.secondary=t.secondary and
  57.                                 tmp.namespace=t.namespace and
  58.                                 tmp.edition_name=t.edition_name
  59.                     )
  60.                 );                                    

  61. insert into target
  62.     select * from tmp where not exists(
  63.         select * from target t where t.id=tmp.id);
3.集合处理
    为了简单,没有进行内容变化的判断
  1. declare
  2.     type tab is table of target%rowtype;
  3.     l_row tab;
  4.     cursor cur is select * from source@remote;
  5. begin
  6.     open cur;
  7.     fetch cur bulk collect into l_row;
  8.     close cur;
  9.     forall i in 1..l_row.count
  10.         update target set row=l_row(i) where id=l_row(i).id;
  11.     insert into target select * from source@remote s
  12.         where not exists (select * from target t where t.id=s.id);
  13.     commit;
  14. end;
  15. /
4.内联视图更新
    没有写更新后插入的步骤,插入的实现是相同的。
  1. update(
  2.     select
  3.         s.id s1,
  4.         s.owner s2,
  5.         s.object_name s3,
  6.         s.subobject_name s4,
  7.         s.object_id s5,
  8.         s.data_object_id s6,
  9.         s.object_type s7,
  10.         s.created s8,
  11.         s.last_ddl_time s9,
  12.         s.timestamp s10,
  13.         s.status s11,
  14.         s.temporary s12,
  15.         s.generated s13,
  16.         s.secondary s14,
  17.         s.namespace s15,
  18.         s.edition_name s16,
  19.         t.id t1,
  20.         t.owner t2,
  21.         t.object_name t3,
  22.         t.subobject_name t4,
  23.         t.object_id t5,
  24.         t.data_object_id t6,
  25.         t.object_type t7,
  26.         t.created t8,
  27.         t.last_ddl_time t9,
  28.         t.timestamp t10,
  29.         t.status t11,
  30.         t.temporary t12,
  31.         t.generated t13,
  32.         t.secondary t14,
  33.         t.namespace t15,
  34.         t.edition_name t16
  35.     from target t inner join source@remote s on(s.id=t.id)
  36.     where     
  37.         not
  38.         (
  39.             s.owner=t.owner and
  40.             s.object_name=t.object_name and
  41.             s.subobject_name=t.subobject_name and
  42.             s.object_id=t.object_id and
  43.             s.data_object_id=t.data_object_id and
  44.             s.object_type=t.object_type and
  45.             s.created=t.created and
  46.             s.last_ddl_time=t.last_ddl_time and
  47.             s.timestamp=t.timestamp and
  48.             s.status=t.status and
  49.             s.temporary=t.temporary and
  50.             s.generated=t.generated and
  51.             s.secondary=t.secondary and
  52.             s.namespace=t.namespace and
  53.             s.edition_name=t.edition_name
  54.         )
  55. )
  56. set
  57.     t1=s1,
  58.     t2=s2,
  59.     t3=s3,
  60.     t4=s4,
  61.     t5=s5,
  62.     t6=s6,
  63.     t7=s7,
  64.     t8=s8,
  65.     t9=s9,
  66.     t10=s10,
  67.     t11=s11,
  68.     t12=s12,
  69.     t13=s13,
  70.     t14=s14,
  71.     t15=s15,
  72.     t16=s16
  73. ;
5.Minus
    先插入业务表中新增的记录,然后对比修改。
  1. declare
  2.     type tab is table of target%rowtype;
  3.     l_row tab;
  4.     cursor cur is select * from source@remote minus select * from target;
  5. begin
  6.     insert into target select * from source@remote s
  7.         where not exists (select * from target t where t.id=s.id);
  8.     open cur;
  9.     fetch cur bulk cllect into l_row;
  10.     close cur;
  11.     forall i in 1..l_row.count
  12.         update target set row=low(i) where id=l_row(i).id;
  13.     commit;
  14. end;
  15. /

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29254281/viewspace-777461/,如需转载,请注明出处,否则将追究法律责任。

上一篇: 维护树状数据
下一篇: 单例模式
请登录后发表评论 登录
全部评论

注册时间:2013-10-19

  • 博文量
    621
  • 访问量
    5939721