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

最近项目中需要做一个数据同步功能,就是要将一个数据库里面的信息定时的同步到另外一个数据库中,这样的话就需要用到储存过程 触发器 定时器 和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（表名）;