execute immediate 使用同一条动态更新语句，绑定变量不同，只更新最新的一条

Question

ORACLE

--创建表

create table  test 
(
    text  clob,
sub   varchar(10),
sub2  varchar(10)
);

--在表中插入数据

insert  test into values 
('select  id,name from cn_student_cnm','RRR','XXX');


---创建过程 
create or replace procedure  pro_test  as
 v_sql  clob;
 v_sql2 clob;
 v_text  text.text%type;
 v_sub  text.sub%type;
 v_sub2  text.sub2%type;
begin
    select  text,sub,sub2  into  v_text, v_sub,v_sub2   from test ;

 v_sql :='update test set text = replace(:1,''id'',:2)';
 v_sql2 :='update test set text = replace(:1,''name'',:2)';

 execute immediate v_sql using  v_text,v_sub;
 commit;

 execute immediate v_sql2 using v_text,v_sub2;
 commit;

end;

执行后，预期中，两次更新，最后，text 字段中的值，应该是：

select  RRR,XXX from cn_student_cnm;


但是实际执行结果是 ：select  id,XXX from cn_student_cnm;

两个execute中，只会最后一个有效。

求大神们帮忙看看，这是为什么，要怎么改，才能两个都更新呢 ？

Answer 1

第二次执行时需要对v_text重新赋值。

/*
create table test1
(
text clob,
sub varchar(10),
sub2 varchar(10)
);
insert into test1 values
('select id,name from cn_student_cnm','RRR','XXX');
delete from test1;
select * from test1; select id,name from cn_student_cnm
select * from test1; select id,XXX from cn_student_cnm
*/
DECLARE
v_sql CLOB;
v_sql2 CLOB;
v_text test1.text%TYPE;
v_sub test1.sub%TYPE;
v_sub2 test1.sub2%TYPE;
BEGIN
SELECT text, sub, sub2 INTO v_text, v_sub, v_sub2 FROM test1;

v_sql := 'update test1 set text = replace(:1,''id'',:2)';
v_sql2 := 'update test1 set text = replace(:1,''name'',:2)';
dbms_output.put_line(v_sql||'#'||v_text||'#'||v_sub); /*update test1 set text = replace(:1,'id',:2)#select id,name from cn_student_cnm#RRR*/
EXECUTE IMMEDIATE v_sql
USING v_text, v_sub;
COMMIT;

/*

v_text 需要重新赋值

/
SELECT text INTO v_text FROM test1;
dbms_output.put_line(v_sql2||'#'||v_text||'#'||v_sub); /*update test1 set text = replace(:1,'name',:2)#select id,name from cn_student_cnm#RRR/

EXECUTE IMMEDIATE v_sql2
USING v_text, v_sub2;
COMMIT;

END;