Oracle中的动态查询

--create test table
create tableAAA_TEST
(aavarchar2(100),bbvarchar2(100),ccvarchar2(50));

--简单的动态执行
begin
 executeimmediate 'insert into table(field1) values(value)';
end;

--代参数和返回的动态执行
declare
  v_strvarchar2(100);
  v_sqlvarchar2(500);
  v_outvarchar2(100);
begin
  v_str := '111';
  v_sql := 'update aaa_test set aa=''ttt'' where aa=:1 returning aa into :2';
 executeimmediatev_sqlusingv_strreturningintov_out;
end;

--使用open-for,fetch和close声明
declare
 typecursorTypeisrefcursor;
  mycursor cursorType;
  v_sqlvarchar2(100);
  v_aavarchar2(50);
  v_bbvarchar2(100);
begin
  v_sql := 'select bb from aaa_test where aa like :1';
  v_aa  := '11%';
 openmycursorforv_sqlusingv_aa;
 loop
   fetchmycursorintov_bb;
   exitwhenmycursor%notfound;
 endloop;
 closemycursor;
end;

--Using Bulking Dynamic Sql(提高性能,减少上下文等待时间)
declare
 typecursorTypeisrefcursor;
  mycursor cursorType;
 typenumLististableofvarchar2(100);
  bbs numList;
  v_sqlvarchar2(500);
begin
  v_sql := 'select bb from aaa_test';
 openmycursorforv_sql;
 fetchmycursorbulkcollectintobbs;
 closemycursor;
 
 executeimmediatev_sql
   bulkcollectintobbs;
end;

--第二种参数方式的动态Sql性能更优
execute immediate 'delete from table1 where empno =' || v_str;
executeimmediate 'delete from table1 where empno = :1' 
--但参数绑定方式对表,视图等对象不适用,因此下面报错
execute immediate 'drop table :tab' usingv_tablename