在plsql字符串拼接，批量生成trigger

本次需要批量编写监控全数据库表的trigger，记录下变更的字段名和变更的值、表名、相对应的主键和做的操作（增删改）。在自动生成脚本sql后，执行execute

immediate sql_str --date：2021-11-05

1. 通过 || 拼接字符串。
2. 通过user_cons_columns , user_constraints 来获取表的主键。以CONSTRAINT_TYPE 选定主键类型，以TABLE_NAME绑定表名，从而获取表的主键名，为后续拼接获取表主键和主键值做准备。最后，将该表所有主键名存放于var_array （防止组合主键），后续能够遍历取出
3. 通过表user_tab_columns 获取表所有字段，后续拼接查看值的更新。
4. 通过new.column <> old.column 来判断前后值是否变更，如若变更，记录下变更的值。
5. if deleting，inserting，updating来判断执行的操作。
6. 最后，生成的trigger将向我们新建的存储表插入执行的操作，变更的列名，变更的值以及主键，以便于我们检索。
7. 同理，可生成批量启动和关闭trigger的脚本。

（1）首先获取数据库中所有表的表名

for tables_name in (
select table_name
from user_tables
) loop

（2）根据约束表，获取各表的主键--需求需要

for primary_key in (
select cu.COLUMN_NAME from user_cons_columns cu , user_constraints au
where cu.constraint_name = au.CONSTRAINT_NAME
and au.CONSTRAINT_TYPE = "P"
and au.TABLE_NAME= tables_name.table_name
)loop
i:=i+1;
var_array(i):=primary_key.column_name;
end loop;

（3）trigger在插入修改和删除前触发

-- the first set sql_str
sql_str := "create or replace trigger "||" task_for_link_"||tables_name.table_name||
" before insert or update or delete"||
" on "||tables_name.table_name||
" for each row"||
" declare "||
"jobName varchar2(200);"||
"tableName varchar2(200);"||
"columnName varchar2(200);"||
"str varchar2(2000);"||
"pk_information varchar2(300);"||
" begin "||
"tableName := """||tables_name.table_name||""";";

for primary_key_count in 1..var_array.count loop
sql_str := sql_str||
"pk_information := """||var_array(primary_key_count)||"""||:new."||var_array(primary_key_count)||";";
end loop;
var_array.delete();
i:=0;
sql_str := sql_str||
"if updating then";

 

（4）拼接，判断字段是否有变更，如若有记录下来

-- get every column_name by table_name
for columns_name in (
select t.table_name,t.column_name
from user_tab_columns t
where t.TABLE_NAME = tables_name.table_name
) loop
-- the second set column to sql_str

sql_str := sql_str||
" if :new."||columns_name.column_name||" <> :old."||columns_name.column_name||" then"||
" str := str||"""||columns_name.column_name||""";"||
"end if;";
end loop;
-- the third set the whole sql_str
sql_str := sql_str||
" insert into t_task ( JOB_NAME, TABLE_NAME, COLUMN_NAME , pk_information)"||
" values ( ""update"", tableName, str, pk_information);"||
" end if;"||
" if inserting then"||
" insert into t_task ( JOB_NAME, TABLE_NAME, COLUMN_NAME, pk_information)"||
" values ( ""insert"", tableName, columnName, pk_information); "||
" end if;"||
" if deleting then"||
" insert into t_task ( JOB_NAME, TABLE_NAME, COLUMN_NAME, pk_information)"||
" values ( ""delete"", tableName, columnName, pk_information); "||
" end if;"||
"end task_for_link_"||table_name||";";

 

（5）执行脚本--when other then null（抛错继续执行）

begin
execute immediate sql_str;
exception
when others then null;
end;
dbms_output.put_line(sql_str);
end loop;
end;