Oracle存储过程中提取触发器INSERT语句并处理TYPE字段需求

我把你整理的需求、代码和相关信息按照清晰的Markdown格式梳理好了，方便你查看和后续处理：

要获取指定触发器的代码，可以使用以下SQL语句：

dbms_metadata.get_ddl('TRIGGER', 'trigger_name');

该语句的输出示例如下：

CREATE OR REPLACE EDITIONABLE TRIGGER "owner"."trigger_name" BEFORE DELETE ON roles FOR EACH ROW BEGIN DBMS_APPLICATION_INFO.read_client_info (acct_utils.trigger_enabled); IF acct_utils.trigger_enabled <> 'F' THEN INSERT INTO qatable (ID,KEY, OBJECT, KEYTYPE, TYPE, OBJ_DEF_ID, WHENCREATED, WHENDELETED, DELETEDBY) VALUES( :old.cny#, :old.record#, 'roles', null, :old.name, null, :old.whencreated, (CURRENT_TIMESTAMP AT TIME ZONE 'GMT'), SYS_CONTEXT('TMCtx', 'USERKEY') ); END IF; END trigger_name; ALTER TRIGGER "owner"."trigger_name" ENABLE

你尝试用以下SQL仅提取触发器中的INSERT INTO qatable语句：

select substr(dbms_metadata.get_ddl('TRIGGER', 'trigger_name'), instr(upper(dbms_metadata.get_ddl('TRIGGER', 'trigger_name')), 'INSERT INTO qatable ('), instr(dbms_metadata.get_ddl('TRIGGER', 'trigger_name'), ';', instr(upper(dbms_metadata.get_ddl('TRIGGER', 'trigger_name')), 'INSERT INTO qatable'), 1) - instr(upper(dbms_metadata.get_ddl('TRIGGER', 'trigger_name')), 'INSERT INTO qatable') + 1) from dual;

上述查询会返回两种格式的INSERT语句之一（取决于触发器名称）：

第一种（包含OBJECT字段）：

INSERT INTO qatable (ID,KEY, OBJECT, KEYTYPE, TYPE, OBJ_DEF_ID, WHENCREATED, WHENDELETED, DELETEDBY) VALUES( :old.ID, :old.KEY, 'roles', null, :old.type, null, :old.whencreated, (CURRENT_TIMESTAMP AT TIME ZONE 'GMT'), SYS_CONTEXT('TMCtx', 'USERKEY') );

第二种（无OBJECT字段）：

INSERT INTO qatable (ID,KEY, KEYTYPE, TYPE, OBJ_DEF_ID, WHENCREATED, WHENDELETED, DELETEDBY) VALUES( :old.ID, :old.KEY, null, :old.type, null, :old.whencreated, (CURRENT_TIMESTAMP AT TIME ZONE 'GMT'), SYS_CONTEXT('TMCtx', 'USERKEY') );

部分触发器的*TYPE*字段插入值为null，其余则会传入有效值。你的需求是：将所有TYPE字段存在有效插入值的内容存入数组或列表，并通过存储过程返回。

qatable的表结构如下：

内容的提问来源于stack exchange，提问作者priyanka hj