转：oracle触发器存储过程实践

9mfm4ht85l

最近项目中需要做一个数据同步功能,就是要将一个数据库里面的信息定时的同步到另外一个数据库中,这样的话就需要用到储存过程 触发器 定时器 和dblink.

以前没写过储存过程,恰好利用这次机会练练手,特写此博客,方便以后查阅:


序号 对象名称 类型 说明
1 synBaseDataTemp table 临时表
2 synBaseDataHistory table 同步历史记录
3 synBaseDataTemp_Sequence SEQUENCE 设置字段ID自增
4 tri_synBaseDataTemp TRIGGER 当临时表中有新增记录时,ID自动增加1
5 tri_基础数据表名 TRIGGER 当CCS基础数据表有增 删 改操作时,会自动触发在ECCS基础数据
表增加记录(共32个)
6 baseDataSynProc procedure 将临时表中记录的信息同步到ECCS中,同时将同步记录
增加到synBaseDataHistory中,并将同步的synBaseDataTemp信息删除

创建同步相关表:


[sql] view plaincopy

    /*创建表synBaseDataTemp*/  
    CREATE TABLE synBaseDataTemp  
    (   
        id  number(15,0) NOT NULL,   
        condition varchar2(300) not null,  
        tableName  varchar2(40) not null,  
        operateType varchar2(20) not null,  
        operateDate date,  
        failFlag    varchar2(1),  
        failTimes   integer,  
        PRIMARY KEY (id)   
    );   
    comment on table synBaseDataTemp is '同步临时表';  
    comment on column synBaseDataTemp.id is '自增列,主键';  
    comment on column synBaseDataTemp.condition is '表主键条件';  
    comment on column synBaseDataTemp.tableName is '表名';  
    comment on column synBaseDataTemp.operateType is '操作类型';  
    comment on column synBaseDataTemp.operateDate is '操作时间';  
    comment on column synBaseDataTemp.failFlag is '失败标志,1失败';  
    comment on column synBaseDataTemp.failTimes is '失败次数';  
      
    /*创建表synHistory*/  
    CREATE TABLE synBaseDataHistory   
    (   
        id  number(15,0) NOT NULL,   
        condition varchar2(300) not null,  
        tableName  varchar2(40) not null,  
        operateType varchar2(20) not null,  
        operateDate date,  
        PRIMARY KEY (id)   
    );  
    comment on table synBaseDataHistory is '同步历史记录表';  
    comment on column synBaseDataHistory.id is '自增列,主键';  
    comment on column synBaseDataHistory.condition is '表主键条件';  
    comment on column synBaseDataHistory.tableName is '表名';  
    comment on column synBaseDataHistory.operateType is '操作类型';  
    comment on column synBaseDataHistory.operateDate is '操作时间';  
      
    /*为表synBaseDataTemp的ID创建自增列*/  
    --创建自动增长序列   
    CREATE SEQUENCE synBaseDataTemp_Sequence   
    INCREMENT BY 1      
    START WITH 1     
    NOMAXVALUE         
    NOCYCLE   
    CACHE 10;   
    --创建触发器   
    CREATE OR REPLACE TRIGGER tri_synBaseDataTemp BEFORE   
    insert ON  synBaseDataTemp FOR EACH ROW   
    begin   
    select synBaseDataTemp_Sequence.nextval into:New.id from dual;   
    end;  






为每张表创建触发器,共32张表,以一张表作为实例:
[sql] view plaincopy

    </pre><p> </p><pre class="sql" name="code">/*为表GGSYSTEM创建触发器,当有相关操作时,在synBaseDataTemp中存入相关信息*/  
    CREATE OR REPLACE TRIGGER tri_GGSYSTEM  
      AFTER INSERT or DELETE or UPDATE --指定触发时机触发  
    ON GGSYSTEM  
      REFERENCING NEW as new_value  
      FOR EACH ROW --说明创建的是行级触发器   
    BEGIN  
      --将修改前数据插入到临时表synBaseDataTemp,供存储过程调用。  
      if inserting then  
        INSERT INTO synBaseDataTemp  
          (condition, tableName, operateType, operateDate)  
        VALUES  
          ('SYSTEMCODE=' || chr(39) || :new_value.systemCode || chr(39),  
           'GGSYSTEM',  
           'INSERT',  
           sysdate);  
        
      elsif updating then  
        INSERT INTO synBaseDataTemp  
          (condition, tableName, operateType, operateDate)  
        VALUES  
          ('SYSTEMCODE=' || chr(39) || :new_value.systemCode || chr(39),  
           'GGSYSTEM',  
           'UPDATE',  
           sysdate);  
        
      elsif deleting then  
        INSERT INTO synBaseDataTemp  
          (condition, tableName, operateType, operateDate)  
        VALUES  
          ('SYSTEMCODE=' || chr(39) || ld.systemCode || chr(39),  
           'GGSYSTEM',  
           'DELETE',  
           sysdate);  
      end if;  
    END;  




创建触发器后,下面是存储过程:


[sql] view plaincopy

    --包头  
    create or replace package baseDataSyn is  
      -- Author  : zhengfazhen  
      -- Created : 2011-12-31   
      
      procedure baseDataSynProc; --从CCS同步到ECCS  
      
    end baseDataSyn;  
      
      
      
    --包体  
    CREATE OR REPLACE PACKAGE BODY baseDataSyn is  
      -- Author  : zhengfazhen  
      -- Created : 2011-12-31  
      
      --同步 存储过程  
      PROCEDURE baseDataSynProc IS  
        v_id        number(15, 0); --临时表ID  
        condition   varchar2(400); --临时表主键条件  
        tableName   varchar2(50); --临时表中存入的表名  
        operateType varchar2(10); --操作类型:insert update delete  
        insertSql   varchar2(400); --插入sql  
        updateSql   varchar2(2000); --更新sql  
        deleteSql   varchar2(500); --删除sql  
        synLogSql   varchar2(500); --历史记录sql  
        columStr    varchar2(1000); --非主键列名字符串  
        gguserColum varchar2(500); --GGUSER非主键且除去(password,PASSWORDSETDATE,PASSWORDEXPIREDATE,LOCKSTATUS,UPDATEPWDIND,LOGINERRTIMES)字段  
        gguserSql   varchar2(500); --为gguser赋值默认值SQL  
        failSql     varchar2(500); --异常处理SQL  
        flag        varchar2(1);  
        
        --查询临时表中所有记录  
        cursor c_synTemp is  
          select c.id, c.condition, c.tablename, c.operatetype, c.operatedate  
            from synBaseDataTemp c  
           where 1 = 1  
           order by operateDate ASC;  
        c_synTemp_row c_synTemp%rowtype;  
        
        --返回非主键列名  
        cursor c_columName(tableName varchar2) is  
          select uc.COLUMN_NAME  
            from user_tab_columns uc  
           where uc.COLUMN_NAME not in  
                 (select col.column_name  
                    from user_constraints con, user_cons_columns col  
                   where con.constraint_name = col.constraint_name  
                     and con.constraint_type = 'P'  
                     and col.table_name = tableName)  
             and uc.TABLE_NAME = tableName;  
        c_row c_columName%rowtype;  
        
      BEGIN  
        for c_synTemp_row in c_synTemp loop  
        begin  
          v_id        := c_synTemp_row.id;  
          condition   := c_synTemp_row.condition;  
          tableName   := c_synTemp_row.tableName;  
          operateType := c_synTemp_row.operateType;  
         
          flag := '1';  
          /*拼接返回的非主键列名start*/  
          for c_row in c_columName(tableName) loop  
            if flag = '1' then  
              columStr := c_row.COLUMN_NAME;  
              flag     := '0';  
            else  
              columStr := columStr || ',' || c_row.COLUMN_NAME;  
            end if;  
          end loop;  
          /*拼接返回的非主键列名end*/  
          /*同义词 eccs_表名*/  
          insertSql := 'insert into eccs_' || tableName ||  
                       ' (select * from ' || tableName || ' where ' ||  
                       condition || ')';  
          updateSql := 'update eccs_' || tableName || ' set  
                 (' || columStr || ')=(select ' || columStr ||  
                       ' from ' || tableName || ' where ' || condition ||  
                       ') where ' || condition;  
          deleteSql := 'delete from eccs_' || tableName || ' where ' ||  
                       condition;  
          synLogSql := 'insert into synBaseDataHistory (select st.id,st.condition,st.tablename,st.operatetype,st.operatedate from synBaseDataTemp st where id=:1)';  
          failSql   := 'update synBaseDataTemp set failFlag=' || chr(39) || '1' ||  
                       chr(39) || 'where id=' || v_id;  
         
          /*GGUSER表特殊处理*/  
          gguserSql   := 'update eccs_' || tableName ||  
                         ' set LOCKSTATUS=' || chr(39) || '1' || chr(39) ||  
                         ', UPDATEPWDIND=' || chr(39) || '1' || chr(39) ||  
                         ', LOGINERRTIMES=' || chr(39) || '0' || chr(39) ||  
                         ' where ' || condition;  
          gguserColum := 'USERCNAME,USERTNAME,USERENAME,SEAL,COMPANYCODE,ISSUECOMPANY,ACCOUNTCODE,PHONE,MOBILE,' ||  
                         'ADDRESS,POSTCODE,EMAIL,USERIND,LOGINSYSTEM,CREATORCODE,CREATETIME,UPDATERCODE,' ||  
                         'UPDATETIME,VALIDIND,REMARK,FLAG,SEX,ALIASCNAME,ALIASTNAME,ALIASENAME,UWINITIAL';  
         
          if tableName = 'GGUSER' then  
            updateSql := 'update eccs_' || tableName ||  
                         ' set  
                           (' || gguserColum ||  
                         ')=(select ' || gguserColum || ' from ' || tableName ||  
                         ' where ' || condition || ') where ' || condition;  
          end if;  
         
          if operateType = 'INSERT' then  
            EXECUTE IMMEDIATE insertSql; --同步数据  
            if tableName = 'GGUSER' then  
              EXECUTE IMMEDIATE gguserSql; --赋默认值  
            end if;  
            EXECUTE IMMEDIATE synLogSql  
              USING v_id; --插入历史记录表  
            delete from synBaseDataTemp where id = v_id; --删除临时表中的信息  
            commit;  
          elsif operateType = 'UPDATE' then  
            EXECUTE IMMEDIATE updateSql;  
            EXECUTE IMMEDIATE synLogSql  
              USING v_id;  
            delete from synBaseDataTemp where id = v_id;  
            commit;  
          elsif operateType = 'DELETE' then  
            EXECUTE IMMEDIATE deleteSql;  
            EXECUTE IMMEDIATE synLogSql  
              USING v_id;  
            delete from synBaseDataTemp where id = v_id;  
            commit;  
          end if;  
          exception when others  then   
            rollback;  
            EXECUTE IMMEDIATE failSql;--更新异常标志  
          commit;  
      end;  
    end loop;  
    END baseDataSynProc;  
      
    end baseDataSyn;  


创建job,定时执行储存过程:


[sql] view plaincopy

    BEGIN  
      DBMS_SCHEDULER.CREATE_JOB(job_name        => 'jobsynproc', --job名称，自己设  
                                job_type        => 'STORED_PROCEDURE', --类型为存储过程  
                                job_action      => 'ccs_synproc', --存储过程名称为proc  
                                start_date      => to_date('30-12-2011 00:00:00',  
                                                           'dd-mm-yyyy hh24:mi:ss'), --开始执行时间  
                                enabled         => TRUE, --自动启用  
                                auto_drop       => false,  
                                repeat_interval => 'FREQ=Monthly;Interval=1');  
    END;  


在这之前,必须授权用户有增删改的权限,并且创建同义词,以便储存过程能顺利执行:



1.依照DBA用户登录执行sql命令：grant create synonym   to 被授权用户;
2.如果你需要对某表可以修改等权限：需执行：grant select,insert,update on monitor_sys_log to 被授权用户;

3.用"被授权用户"登录创建同义词：create  synonym monitor_sys_log(同义词名称) for 拥有表用户.monitor_sys_log（表名）;