oracle使用存储过程实现日志记录.sql

--这段sql语句是用来实现oracle后台记录操作日志的，代替或者补充应用系统的操作日志。

--1.对应的日志记录表
-------------------------------------------------------------------
create table TEST
(
  T_ID   NUMBER(4),
  T_NAME VARCHAR2(20),
  T_AGE  NUMBER(2),
  T_SEX  CHAR(1)
);
----------------------对应的日志记录表---------------------------END

--2.建立触发器(触发器实现日志记录)

--2.1开启屏幕输出：（需要在命令行登陆sqlplus后执行）
sql>set serveroutput on;--在doc窗口和tomcat下可以捕捉到这些输出信息。
------------------------------------------------------------------

CREATE OR REPLACE TRIGGER test_trigger
AFTER DELETE OR INSERT OR UPDATE ON test for each row -- for each row是为了使用:new和old
DECLARE
v_type VARCHAR2(15);
BEGIN
IF INSERTING THEN
  v_type := 'INSERT'||:new.T_NAME;--oracle添加链接字符串使用||
  DBMS_OUTPUT.PUT_LINE('记录'||:new.T_NAME||'已经成功插入，并已记录到日志');--添加 只有new
ELSIF UPDATING THEN
  v_type := 'UPDATE'||:old.T_NAME;
  DBMS_OUTPUT.PUT_LINE('记录'||:old.T_NAME||'已经成功更新，并已记录到日志');--更新有new和old
ELSIF DELETING THEN
  v_type := 'DELETE'||:old.T_NAME;
  DBMS_OUTPUT.PUT_LINE('记录'||:old.T_NAME||'已经成功删除，并已记录到日志');--删除只有old
END IF;
  my_pro(v_type);
END;
----------------------建立触发器------------------------------------END

--3.建立日志表。
----------------------------------------------------------------
create table TEST_LOG_1
(
  L_USER VARCHAR2(15),
  L_TYPE VARCHAR2(15),
  L_DATE VARCHAR2(30)
);
--------------------建立日志表---------------------------------END

--4.建存储过程(注意一定要加上PRAGMA AUTONOMOUS_TRANSACTION;让这个存储过程有自治的事务控制，不然会影响A的事务控制)
----------------------------------------------------------------
create or replace procedure my_pro(v_type varchar2)
as
PRAGMA AUTONOMOUS_TRANSACTION;--设置为自治事务且必须加上commit,否则报commit非法。
begin
       INSERT INTO TEST_LOG_1 VALUES(user,v_type,TO_CHAR(sysdate,'yyyy-mm-dd hh24:mi:ss'));
       commit;
end;
-------------------建存储过程-------------------------------------END

--最后我们在A中输入以下测试语句：
INSERT INTO test VALUES(1,'zhao',22,'M');
UPDATE test SET t_name = '30' WHERE t_id = 1;
DELETE test WHERE t_id = 1;

SELECT * FROM test;
SELECT * FROM TEST_LOG_1;