execute immediate 用法小结

1.常规用法

 v_sql varchar2(1000);
 v_sql := 'update Test set name= ''lw112190'' where id= 1';
 execute immediate v_sql;

2.执行sql语句，并赋值给某个变量

 v_sql := 'select name from Test';
 execute immediate v_sql into v_name;

3.执行带参数的sql

 v_sql:='select * from Test where name=:1 and age=:2'; 
 execute immediate v_sql using 'lw112190',20;

4.带参与赋值结合

v_sql:='select name,code from Test where id= :1';
execute immediate v_sql into v_name, v_code using v_id ;   

5.执行多行sql语句

--\n = chr(10)　换行
v_sql1:='update Test set name=''1'' WHERE id = 2; ';
v_sql2:='update Test set name=''3'' WHERE id = 4; ';
v_sql:='begin'||chr(10)||v_sql1|| chr(10)||v_sql2||chr(10)||'end;';
execute immediate v_sql;