oracle触发器字符串变量赋值,怎么用触发器new值替换select字段变量

求助各位高手，每一次触发生成一条json格式插入表里，

我的思路写一个函数，生成json模块， 由触发器调用此函数，

关键问题：没法替换：old   ：new  值 ，还是一条字符串

(数据库版本:11G)

：

select  '{"ID":"'||:new.ID||'","NAME":"'||:new.NAME||'"}' into str  from dual

我要结果：

{"id":"1","name":"a"}

-- 表

-- Create table

create table TR_LOG

(

json        VARCHAR2(4000),

operation   VARCHAR2(20),

insert_date DATE

)

-- Create table

create table TEST

(

id   INTEGER,

name VARCHAR2(100)

)

-- 函数

create or replace function fun_json_str(tab varchar2, flag varchar2)

return varchar2 is

Result varchar2(4000);

begin

if flag = 'insert' then

select '''{' || list || '}'''

into Result

from (select listagg('"' || COLUMN_NAME || '":"''||:new.' ||

COLUMN_NAME ||'||''"',

',') within GROUP(order by COLUMN_NAME) as list

From user_tab_cols

where table_name = upper(tab)

order by column_id asc);

elsif flag = 'delete' then

select '''{' || list || '}'''

into Result

from (select listagg('"' || COLUMN_NAME || '":"''||:new.' ||

COLUMN_NAME ||'||''"',

',') within GROUP(order by COLUMN_NAME) as list

From user_tab_cols

where table_name = upper(tab)

order by column_id asc);

elsif flag = 'update' then

select '''[{' || old_list || '},{' || new_list || '}]'''

into Result

from (select listagg('"' || COLUMN_NAME || '":"''||

ld.' ||

COLUMN_NAME ||'||''"',

',') within GROUP(order by COLUMN_NAME) as old_list,

listagg('"' || COLUMN_NAME || '":"''||:new.' ||

COLUMN_NAME ||'||''"',

',') within GROUP(order by COLUMN_NAME) as new_list

From user_tab_cols

where table_name = upper(tab)

order by column_id asc);

end if;

return(Result);

end fun_json_str;

--  触发器

create or replace trigger test_trigger

after delete or insert or update on test

FOR EACH ROW

declare

list     varchar2(4000);

json_str varchar2(4000);

vresult  varchar2(4000);

begin

if inserting then

json_str := fun_json_str('TEST', 'insert');

/*insert into tr_log values (json_str, 'insert', sysdate);

dbms_output.put_line('{"ID":"' || :new.ID || '","NAME":"' || :new.NAME || '"}');*/

dbms_output.put_line('select  '||json_str||' into str  from dual');

execute immediate 'select  '||json_str||' into str  from dual';

insert into tr_log values (vresult, 'insert', sysdate);

elsif updating then

/*

dbms_output.put_line('[{"ID":"' ||

ld.ID || '","NAME":"' ||

ld.NAME || '"},{"ID":"' || :new.ID ||

'","NAME":"' || :new.NAME || '"}]');*/

execute immediate 'select  fun_json_str(''TEST'', ''insert'') into vresult  from dual';

insert into tr_log values (vresult, 'update', sysdate);

elsif deleting then