存储过程给指定表绑定触发器sql里拼json作存储日志

create or replace procedure Logging_Messages(tableName varchar2 default ‘%‘) is 2 cursor c_cl IS 3 SELECT column_name tcl 4 FROM user_tab_columns 5 WHERE table_name = upper(tableName); 6 newjson clob; 7 oldjson clob; 8 v_sql varchar2(32000); 9 V_TMP varchar2(32000); 10 begin 11 FOR cl IN c_cl LOOP 12 if newjson is null then 13 newjson := ‘{"‘ || cl.tcl || ‘":"‘‘||‘||‘:NEW.‘ || cl.tcl ||‘||‘‘"}‘; 14 else 15 newjson := newjson || ‘,‘ || ‘{"‘ || cl.tcl || ‘":"‘‘||‘||‘:NEW.‘ || cl.tcl ||‘||‘‘"}‘; 16 end if; 17 if oldjson is null then 18 oldjson := ‘{"‘ || cl.tcl || ‘":"‘‘||‘||‘:OLD.‘ || cl.tcl ||‘||‘‘"}‘; 19 else 20 oldjson := oldjson || ‘,‘ || ‘{"‘ || cl.tcl || ‘":"‘‘||‘||‘:OLD.‘ || cl.tcl ||‘||‘‘"}‘; 21 end if; 22 end LOOP; 23 DBMS_OUTPUT.put_line(‘newjson:‘||newjson); 24 DBMS_OUTPUT.put_line(‘oldjson:‘||oldjson); 25 V_TMP := Q‘{CREATE OR REPLACE TRIGGER TR_@TABLE_NAME@ 26 BEFORE INSERT OR UPDATE OR DELETE ON @TABLE_NAME@ FOR EACH ROW 27 DECLARE 28 json clob; 29 BEGIN 30 IF INSERTING THEN 31 json := ‘@newjson@‘; 32 DBMS_OUTPUT.put_line(‘json字符串是insert：‘||json); 33 insert into ddllog values(‘@TABLE_NAME@‘,‘insert‘,json,sysdate); 34 end if; 35 36 IF DELETING THEN 37 json := ‘@oldjson@‘; 38 DBMS_OUTPUT.put_line(‘json字符串是delete：‘||json); 39 insert into ddllog values(‘@TABLE_NAME@‘,‘delete‘,json,sysdate); 40 end if; 41 42 IF UPDATING THEN 43 json := ‘@newjson@‘; 44 DBMS_OUTPUT.put_line(‘json字符串是update：‘||json); 45 insert into ddllog values(‘@TABLE_NAME@‘,‘update‘,json,sysdate); 46 end if; 47 48 END TR_@TABLE_NAME@;}‘; 49 V_TMP := REPLACE(V_TMP, ‘@TABLE_NAME@‘, tableName); 50 V_TMP := REPLACE(V_TMP, ‘@newjson@‘, newjson); 51 V_SQL := REPLACE(V_TMP, ‘@oldjson@‘, oldjson); 52 DBMS_OUTPUT.put_line(V_SQL); 53 EXECUTE IMMEDIATE V_SQL; 54 end Logging_Messages;

为指定表建立监听的触发器，结果如下：

实现了预想的功能，感觉还有很大的完善空间。不过限于水平较低，慢慢来改吧。

 

存储过程给指定表绑定触发器sql里拼json作存储日志

标签：