关于oracle存储过程

最新推荐文章于 2024-09-24 11:36:02 发布

置顶 zfz1214

最新推荐文章于 2024-09-24 11:36:02 发布

阅读量739

点赞数

文章标签：存储 oracle insert delete null constraints

本文链接：https://blog.csdn.net/zfz1214/article/details/7187032

版权

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

创建同步相关表:

/*创建表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张表,以一张表作为实例:

/*为表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) || :old.systemCode || chr(39),
       'GGSYSTEM',
       'DELETE',
       sysdate);
  end if;
END;

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

--包头
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,定时执行储存过程:

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（表名）;