前提条件:
-
确保每张表对有设置唯一的主键,暂不支持联合主键;
-
确保数据库用户能访问Oracle的系统表;
-
系统无外键设置;
-
系统对事物强一致性没有太高的要求;
-
变更记录库与重做库用户能
准备工作
- pl/sql连接数据库;
- 检测系统表是否都设置主键,可参考如下存储过程checkNoPKTable
create or replace procedure checkNoPKTable is str varchar2(1000); begin for v in (SELECT * FROM user_tables ut where not exists (select 1 from user_constraints au where ut.TABLE_NAME = au.table_name and au.constraint_type = 'P')) loop begin str := ' table ' || v.table_name || ' not set Pk, Error'; dbms_output.put_line('sql:' || str); end; end loop; end checkNoPKTable;
- 创建日志记录表,data_op_log
--drop table
drop table DATA_OP_LOG;
-- Create table
create table DATA_OP_LOG
(
id VARCHAR2(32) not null,
target_table VARCHAR2(512) not null,
reference_id VARCHAR2(32) not null,
reference_column VARCHAR2(128) not null,
op_type VARCHAR2(1) not null,
op_time TIMESTAMP(6) not null,
redo_flag VARCHAR2(1) default '0' not null
);
-- Add comments to the table
comment on table DATA_OP_LOG
is '数据操作记录表';
-- Add comments to the columns
comment on column DATA_OP_LOG.id
is '主键id';
comment on column DATA_OP_LOG.target_table
is '操作目标表';
comment on column DATA_OP_LOG.reference_id
is '操作目标记录id';
comment on column DATA_OP_LOG.reference_column
is '操作目标记录主键字段';
comment on column DATA_OP_LOG.op_type
is '操作类型:0 删除;1 新增;2 修改';
comment on column DATA_OP_LOG.op_time
is '操作时间';
comment on column DATA_OP_LOG.redo_flag
is '重做标识:0 未重做;1 已重做';
- 创建函数createTriggerSql
create or replace function createTriggerSql(v_tableName in varchar2,
v_columnName in varchar2)
return varchar2 as
v_sql varchar2(4000);
begin
v_sql:= 'create or replace trigger ' || v_tableName ||'_Trigger before insert or update or delete on ' || v_tableName ||' for each row
declare ids varchar2(128); nums number:=0; begin ids:= '''';
case when INSERTING then insert into data_op_log(ID,TARGET_TABLE,REFERENCE_COLUMN,REFERENCE_ID,op_type,op_time) values(SYS_GUID(),''' || v_tableName || ''',''' || v_columnName || ''',:NEW.'||v_columnName||',''1'',sysdate);
when UPDATING then select count(1) into nums from data_op_log dol where dol.target_table = ''' || v_tableName || ''' and dol.reference_id=:old.'||v_columnName||' and dol.reference_column=''' || v_columnName || ''' and dol.op_type=''2'';
if(nums > 0) then select dol.id into ids from data_op_log dol where dol.target_table = ''' || v_tableName || ''' and dol.reference_id=:old.'||v_columnName||' and dol.reference_column=''' || v_columnName || ''' and dol.op_type=''2''; update DATA_OP_LOG t set t.op_time=sysdate,t.redo_flag=''0'' where t.id=ids;
else insert into data_op_log(ID,TARGET_TABLE,REFERENCE_COLUMN,REFERENCE_ID,op_type,op_time) values(SYS_GUID(),''' || v_tableName || ''',''' || v_columnName || ''',:NEW.'||v_columnName||',''2'',sysdate);end if;
when DELETING then insert into data_op_log(ID,TARGET_TABLE,REFERENCE_COLUMN,REFERENCE_ID,op_type,op_time) values(SYS_GUID(),''' || v_tableName || ''',''' || v_columnName || ''',:NEW.'||v_columnName||',''0'',sysdate);
end case;
end;';
return v_sql;
end;
- 创建生成触发器的存储过程createTrigger
create or replace procedure createTrigger Authid Current_User is
str varchar2(4000); --执行sql
v_error_code VARCHAR2(10); --错误码
v_description VARCHAR2(160); --错误信息
begin
for v in (SELECT ut.TABLE_NAME,cu.COLUMN_NAME
FROM user_tables ut left join user_cons_columns cu on ut.TABLE_NAME = cu.TABLE_NAME
where exists (select 1
from user_constraints au
where ut.TABLE_NAME = au.table_name
and au.constraint_type = 'P'
and au.CONSTRAINT_NAME = cu.CONSTRAINT_NAME)
and not regexp_like(ut.table_name, '^TEMPLATE_', 'i')
and not regexp_like(ut.table_name, '^QRTZ_', 'i')) loop
begin
str:=createTriggerSql(v.TABLE_NAME,v.COLUMN_NAME);
--dbms_output.put_line('sql:' || str);
execute immediate str;
exception
when others then
v_error_code := SQLCODE;
v_description := substr(sqlerrm, 1, 160);
ROLLBACK;
dbms_output.put_line('sql:' || str || '执行异常错误码:' || v_error_code || '; 错误信息:' || v_description);
end;
end loop;
-- commit;
end createTrigger;
- 创建重做日志的函数createRedoSql
--创建重做sql脚本 create or replace function createRedoSql(fromUser in varchar2,toUser in varchar2,tableName in varchar2,columnName in varchar2,referenceId in varchar2,opType in varchar2) return varchar2 as v_sql varchar2(4000); begin --判断操作类型 if('0'=opType) then v_sql:='delete from '||toUser||'.'||tableName||' where '||columnName||' = '''||referenceId||''';'; else if ('1'=opType) then v_sql:='insert into '||toUser||'.'||tableName||' select * from '||fromUser||'.'||tableName||''';'; else v_sql:='delete from '||toUser||'.'||tableName||' where '||columnName||' = '''||referenceId||'''; insert into '||toUser||'.'||tableName||' select * from '||fromUser||'.'||tableName||''';'; end if; return v_sql; end createRedoSql;
- 创建重做日志的存储过程redoWithLog
--创建重做存储过程 create or replace procedure redoWithLog(fromUser varchar2,toUser varchar2) Authid Current_User is str varchar2(4000); --执行sql v_error_code VARCHAR2(10); --错误码 v_description VARCHAR2(160); --错误信息 begin for v in (SELECT * FROM data_op_log dol where dol.redo_flag='0' order by dol.op_time asc) loop begin str:=createRedoSql(fromUser,toUser,v.TABLE_NAME,v.COLUMN_NAME,v.reference_id,v.op_type); --dbms_output.put_line('sql:' || str); execute immediate str; update data_op_log l set l.redo_flag='1' where l.id = v.id; exception when others then v_error_code := SQLCODE; v_description := substr(sqlerrm, 1, 160); ROLLBACK; dbms_output.put_line('sql:' || str || '执行异常错误码:' || v_error_code || '; 错误信息:' || v_description); end; end loop; commit; end redoWithLog;