转：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) || :old.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（表名）;