ORACLE动态完全数据日志（AUDIT LOG）批处理生成脚本
目标：
1）实现对数据更新CRUD的记录
2）可以对动态调整需要做日志的表/列
3）可对比更新前后的数据差别。
4）记录操作的用户和操作方式等等
效果：
1）在T_AUDIT_LOGS中记录动态指定所有的CRUD操作
2）更新后前后数据对比用HTML <TABLE>呈现,比如：
| 列表值 | VALUE | TestVal | TestVal-修改后 |
| 列表值说明 | F_DESC | 测试值 | 测试值-修改后 |
| 应用模块 | APPLY_TO | SOMEWHERE | SOMEWHERE-修改后 |
| 有效 | ISVALID | 1 | 2 |
| 排序 | F_ORDER | 1 | 2 |
| 创建日期 | CREATE_DATE | 2011/09/12 12:42:46 | 2011/10/12 12:42:46 |
;
此部分实现比较麻烦，请按步骤做。
1）创建日志数据库：
create table T_AUDIT_LOGS
(
id NUMBER(18) not null,
create_date TIMESTAMP(6) default SYSTIMESTAMP not null,
member_id VARCHAR2(30),
table_name VARCHAR2(30) not null,
action VARCHAR2(10) not null,
data CLOB,
record_id VARCHAR2(100),
f_function VARCHAR2(200),
access_ip CHAR(15),
remark VARCHAR2(500)
)
;
comment on table T_AUDIT_LOGS
is '系统CRUD记录';
comment on column T_AUDIT_LOGS.member_id
is '用户ID';
comment on column T_AUDIT_LOGS.table_name
is '表名';
comment on column T_AUDIT_LOGS.action
is '操作';
comment on column T_AUDIT_LOGS.data
is '新旧数据比较';
comment on column T_AUDIT_LOGS.record_id
is '关键ID号';
comment on column T_AUDIT_LOGS.f_function
is '访问模块';
comment on column T_AUDIT_LOGS.access_ip
is '用户IP';
comment on column T_AUDIT_LOGS.remark
is '备注1';
create index AUDIT_LOGS_CREATE_DATE_IX on T_AUDIT_LOGS (CREATE_DATE);
create index AUDIT_LOGS_MEMBER_ID_IX on T_AUDIT_LOGS (MEMBER_ID);
create index AUDIT_LOGS_TABLE_RECORD_ID_IX on T_AUDIT_LOGS (TABLE_NAME, RECORD_ID);
alter table T_AUDIT_LOGS
add constraint AUDIT_LOGS_PK primary key (ID);
/
CREATE SEQUENCE audit_logs_seq;
/
2）创建日志设置数据表（里存在的记录将不做日志记录'）
create table T_AUDITDISABLED
(
table_name VARCHAR2(50) not null,
colum_name VARCHAR2(50) not null
)
;
comment on table T_AUDITDISABLED
is '是否需要记录CRUD, 这里存在的记录将----不---LOG';
comment on column T_AUDITDISABLED.table_name
is '表名，可以使用通配符: *';
comment on column T_AUDITDISABLED.colum_name
is '列名，可以使用通配符: * 表名+列名 不可重复！';
alter table T_AUDITDISABLED
add constraint PK_TABLE_NAME_COLUM_NAME primary key (TABLE_NAME, COLUM_NAME);
3）创建包
CREATE OR REPLACE PACKAGE tsh_audit AS
PROCEDURE insert_log (p_username IN VARCHAR2,
p_object IN VARCHAR2,
p_action IN VARCHAR2,
p_data IN CLOB,
p_record_id IN VARCHAR2,
p_function IN VARCHAR2,
p_access_ip IN VARCHAR2,
p_remark IN VARCHAR2
);
END;
/
CREATE OR REPLACE PACKAGE BODY tsh_audit AS
PROCEDURE insert_log (p_username IN VARCHAR2,
p_object IN VARCHAR2,
p_action IN VARCHAR2,
p_data IN CLOB,
p_record_id IN VARCHAR2,
p_function IN VARCHAR2,
p_access_ip IN VARCHAR2,
p_remark IN VARCHAR2
) IS
BEGIN
INSERT INTO t_audit_logs (
id,
CREATE_DATE,
MEMBER_ID,
TABLE_NAME,
action,
data,
record_id,
F_function,
access_ip,
REMARK
)
VALUES (
audit_logs_seq.NEXTVAL,
SYSTIMESTAMP,
p_username,
p_object,
p_action,
p_data,
p_record_id,
p_function,
p_access_ip,
p_remark
);
END;
END;
/
4) 一些ORACLE 函数
4.1）判断是否需要做日志（根据表名和列名，和表T_AUDITDISABLED设置相关）
create or replace function audit_required(p_tablename varchar2, p_columnname varchar2) return boolean is
Result boolean;
num number;
begin
select count(1) into num from t_auditdisabled where
(table_name = p_tablename and colum_name = p_columnname)
or (table_name = '*' and colum_name = p_columnname)
or (table_name = p_tablename and colum_name = '*')
;
if num > 0 then
Result := false;
else
Result := true;
end if;
return(Result);
end audit_required;
4.2）更新前后数据比较，直接输出是<table>元素
create or replace function Get_Difference(p_tablename varchar2, p_columnname varchar2,p_oldval varchar2, p_newval varchar2) return varchar2 is
Result varchar2(32767);
begin
if audit_required( p_tablename, p_columnname ) -- 此表列的是否需要AUDIT?
and ( (p_oldval is null and p_newval is not null)
or (p_oldval is not null and p_newval is null)
or (p_oldval != p_newval) )
then
Result := '<tr>'||chr(10)
||chr(9)||'<td class="AC">'||GET_COMMENTS(p_tablename,p_columnname)||'</td>'||chr(10)
||chr(9)||'<td class="AD">'||p_columnname||'</td>'||chr(10)
||chr(9)||'<td class="AO">'||p_oldval||'</td>'||chr(10)
||chr(9)||'<td class="AN">'||p_newval||'</td>'||chr(10)
||'</tr>'||chr(10);
else
Result := '';
end if;
return(Result);
end Get_Difference;
4.3）取得表中说明文字（COMMENTS），4.2）和生成的触发器大量使用此函数。COMMENTS的格式是：列名解释|其他注释。
注意：“列名解释”和“其他注释”之间用“|”号分隔。比如：UserID的Comments是：用户ID|此是用户登录的唯一ID
PS：不会没使用过列注释（comment）吧？上面创建的表都有COMMENT的，养成写表/列COMMENT的习惯，可以使得归档容易。本脚本对COMMENT的格式是有要求了，上面已经说了。
create or replace function GET_COMMENTS(p_tablename varchar2, p_columnname varchar2) return varchar2 is
Result varchar2(32767);
begin
select comments INTO Result
from all_col_comments t2
where t2.table_name = p_tablename
and t2.column_name = p_columnname
AND ROWNUM = 1 ORDER BY ROWNUM ASC;
Result := split(Result,1,'|'); -- 取COMMENT中的第一个元素，以“|”分隔
return(Result);
end GET_COMMENTS;
4.4）ORACLE的SPIT函数，很好用！在此大量使用。
比如：split('32|3434.3|aaa', 1, '|') 返回 32，
注意：第一个为字串，第2个参数以1为开始，第三个参数是分隔符号
create or replace function split(input_list varchar2, ret_this_one number, delimiter varchar2)
return varchar2
is
v_list varchar2(32767) := delimiter || input_list;
start_position number;
end_position number;
begin
start_position := instr(v_list, delimiter, 1, ret_this_one);
if start_position > 0 then
end_position := instr( v_list, delimiter, 1, ret_this_one + 1);
if end_position = 0 then
end_position := length(v_list) + 1;
end if;
return(substr(v_list, start_position + 1, end_position - start_position - 1));
else
return NULL;
end if;
end split;
4.5）判断字段类型。此处没有使用DUMP函数，因为数据表中有可能是空表。此处只用于生成器中。
create or replace function is_date(p_tablename varchar2,p_columnname varchar2) return boolean is
Result boolean :=false;
v_typ varchar2(50);
begin
SELECT DATA_TYPE INTO v_typ
FROM all_tab_columns
WHERE TABLE_NAME = p_tablename
AND COLUMN_NAME = p_columnname
AND ROWNUM = 1;
/*
v_typ := SPLIT(p_dump_string,1,' ');
if (v_typ='Typ=13'
OR v_typ='Typ=12'
OR v_typ='Typ=180'
OR v_typ='Typ=181'
OR v_typ='Typ=231'
OR v_typ='Typ=183'
) THEN
*/
if (v_typ='DATE'
OR v_typ='TIMESTAMP'
) THEN
Result := true;
ELSE
Result := false;
END IF;
return(Result);
end is_date;
4.6）设置默认值。在生成的Trigger中使用。
create or replace function SETVALUE(p_val varchar2, p_default varchar2) return varchar2 is
Result varchar2(32767);
begin
if (p_val is null) then
Result := p_default;
elsif ('' = p_val) then
Result := p_default;
else
Result := p_val;
end if;
return(Result);
end SETVALUE;
5）这是生成日志触发器的关键程序
先看b)点!!!
上面准备就绪后，在SQL*PLUS中：@D:\PROJECTS\audit_trigger.sql username
其中username是表的拥有者，生成SQL脚本文件为：d:\projects\all_audit.sql，得到all_audit.sql，应该知道做什么了吧？--运行-生成TRIGGER!
注意：
a）生成脚本all_audit.sql会在非限制表中（在表T_AUDITDISABLED定义）创建新列audit_info VARCHAR2(1000)
此列是'日志用系统字段，CRUD时必须更新此字段，提供给TRIGGER更新信息
audit_info的数据格式为：MEMBER_ID|CONTROLLER.METHOD|ACCESS_IP|REMARK，即是：
用户ID|调用方法(或URL)|登录IP|备注，注意其中以“|”分隔
b）在使用audit_trigger.sql 之前，必须做：用于日志的表T_AUDIT_LOGS和T_AUDITDISABLED不能也做日志记录（会死循环），可以这样在T_AUDIT_LOGS插入两条记录：
insert into T_AUDITDISABLED (table_name, colum_name) values ('T_AUDIT_LOGS', '*');
insert into T_AUDITDISABLED (table_name, colum_name) values ('T_AUDITDISABLED', '*');
-- 所有表的'AUDIT_INFO'字段也不需要日志记录
insert into T_AUDITDISABLED (table_name, colum_name) values ('*', 'AUDIT_INFO');c）同上例，对于不需要审计的表/列可以如法炮制（可在生成触发器后动态调整）。
SET SERVEROUTPUT ON SIZE 1000000
SET VERIFY OFF
SET TRIMSPOOL ON
SPOOL ON
Spool d:\projects\all_audit.sql
DECLARE
v_owner VARCHAR2(100) := UPPER('&1');
v_table VARCHAR2(100);
v_id VARCHAR2(100);
v_dump VARCHAR2(200);
CURSOR c_tables IS
SELECT table_name
FROM user_tables
WHERE table_name not in
(SELECT table_name FROM T_AUDITDISABLED
WHERE COLUM_NAME = '*'); --对于一些表不生成日志用TRIGGER!, 特别是T_AUDIT_LOGS和T_AUDITDISABLED
CURSOR c_columns IS
SELECT column_name
FROM all_tab_columns
WHERE owner = v_owner
AND table_name = v_table;
BEGIN
FOR cur_table IN c_tables LOOP
v_table := cur_table.table_name;
-- Add/modify columns
DBMS_OUTPUT.NEW_LINE;
DBMS_OUTPUT.NEW_LINE;
DBMS_OUTPUT.PUT_LINE('----------------------------------------');
DBMS_OUTPUT.PUT_LINE('-----表：' || v_table || '增加AUDIT_INFO -----');
DBMS_OUTPUT.PUT_LINE('----------------------------------------');
DBMS_OUTPUT.NEW_LINE;
--DBMS_OUTPUT.PUT_LINE('alter table '||v_table||' drop column audit_info;'); --注意这两行的修改。
DBMS_OUTPUT.PUT_LINE('alter table '||v_table||' add audit_info VARCHAR2(1000);');
DBMS_OUTPUT.PUT_LINE('comment on column '||v_table||'.audit_info');
DBMS_OUTPUT.PUT_LINE('is ''日志用系统字段，CRUD时必须更新此字段，格式：MEMBER_ID|CONTROLLER.METHOD|ACCESS_IP|REMARK'';');
DBMS_OUTPUT.NEW_LINE;
DBMS_OUTPUT.NEW_LINE;
DBMS_OUTPUT.PUT_LINE('----------------------------------------');
DBMS_OUTPUT.PUT_LINE('-----表：' || v_table || '的审计触发器 -----');
DBMS_OUTPUT.PUT_LINE('----------------------------------------');
DBMS_OUTPUT.NEW_LINE;
DBMS_OUTPUT.PUT_LINE('CREATE OR REPLACE TRIGGER ' || v_table || '_audit_trg');
DBMS_OUTPUT.PUT_LINE('AFTER INSERT OR UPDATE OR DELETE ON ' || v_table);
DBMS_OUTPUT.PUT_LINE('FOR EACH ROW');
DBMS_OUTPUT.PUT_LINE('DECLARE');
DBMS_OUTPUT.PUT_LINE(' PRAGMA AUTONOMOUS_TRANSACTION;');
DBMS_OUTPUT.PUT_LINE(' v_action VARCHAR2(30) := ''NONE'';');
DBMS_OUTPUT.PUT_LINE(' v_log VARCHAR2(32767) :=''<table class="AT">''||chr(10);');
DBMS_OUTPUT.PUT_LINE('BEGIN');
DBMS_OUTPUT.PUT_LINE(' IF INSERTING THEN');
DBMS_OUTPUT.PUT_LINE(' v_action := ''增加'';');
DBMS_OUTPUT.PUT_LINE(' ELSIF UPDATING THEN');
DBMS_OUTPUT.PUT_LINE(' v_action := ''更新'';');
DBMS_OUTPUT.PUT_LINE(' ELSIF DELETING THEN');
DBMS_OUTPUT.PUT_LINE(' v_action := ''删除'';');
DBMS_OUTPUT.PUT_LINE(' END IF;');
FOR cur_rec IN c_columns LOOP
IF c_columns%ROWCOUNT = 1 THEN
v_id := cur_rec.column_name;
END IF;
--execute immediate 'SELECT DUMP('||cur_rec.column_name||') FROM '||v_table||' WHERE ROWNUM=1'
--INTO v_dump;
DBMS_OUTPUT.PUT_LINE(' v_log := v_log||Get_Difference('''||v_table||''',');
DBMS_OUTPUT.PUT_LINE(' '''||cur_rec.column_name||''',');
if (is_date(v_table,cur_rec.column_name)) then
DBMS_OUTPUT.PUT_LINE(' to_char(:OLD.'||cur_rec.column_name||',''YYYY/MM/DD HH24:MI:SS''),');
DBMS_OUTPUT.PUT_LINE(' to_char(:NEW.'||cur_rec.column_name||',''YYYY/MM/DD HH24:MI:SS''));');
else
DBMS_OUTPUT.PUT_LINE(' to_char(:OLD.'||cur_rec.column_name||'),');
DBMS_OUTPUT.PUT_LINE(' to_char(:NEW.'||cur_rec.column_name||'));');
end if;
END LOOP;
DBMS_OUTPUT.PUT_LINE(' v_log := v_log||''</table>'';');
DBMS_OUTPUT.PUT_LINE(' tsh_audit.insert_log (');
DBMS_OUTPUT.PUT_LINE(' SETVALUE(split(:NEW.AUDIT_INFO,1,''|''),''SYSTEM''), --默认是SYSTEM;
DBMS_OUTPUT.PUT_LINE(' ''' || v_table || ''',');
DBMS_OUTPUT.PUT_LINE(' v_action,');
DBMS_OUTPUT.PUT_LINE(' v_log,');
DBMS_OUTPUT.PUT_LINE(' :NEW.'||v_id||',');
DBMS_OUTPUT.PUT_LINE(' split(:NEW.AUDIT_INFO,2,''|''),'); --对本表中AUDIT_INFO的一些处理
DBMS_OUTPUT.PUT_LINE(' split(:NEW.AUDIT_INFO,3,''|''),');
DBMS_OUTPUT.PUT_LINE(' split(:NEW.AUDIT_INFO,4,''|'')');
DBMS_OUTPUT.NEW_LINE;
DBMS_OUTPUT.PUT_LINE(' );');
DBMS_OUTPUT.PUT_LINE(' COMMIT;');
DBMS_OUTPUT.PUT_LINE('END;');
DBMS_OUTPUT.PUT_LINE('/');
--DBMS_OUTPUT.PUT_LINE('SHOW ERRORS');
END LOOP;
END;
/
SPOOL OFF
有人会问，为什么生成的TIRGGER对每个字段都生成类似以下的代码，一个循环不是搞定了吗？：
v_log := v_log||Get_Difference('T_MEMBER',
'MEMBER_ID',
to_char(:OLD.MEMBER_ID),
to_char(:NEW.MEMBER_ID));
这是因为，在ORACLE中没有办法动态调用“:OLD.字段名”和“:NEW.字段名”，这个是确凿的，所以不能用循环简单解决此问题。
如果可以动态调用，我也不会写这生成器了。
有时，用笨方法，是最好最直接的方法。
转载自：http://greedwind.blog.163.com/blog/static/278310652011812113812782/
