ORACLE

 

--create test table
create table AAA_TEST
(aa varchar2(100),bb varchar2(100),cc varchar2(50));

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

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

--使用open-for,fetch和close声明
declare
  type cursorType is ref cursor;
  mycursor cursorType;
  v_sql varchar2(100);
  v_aa varchar2(50);
  v_bb varchar2(100);
begin  v_sql := 'select bb from aaa_test where aa like :1';
  v_aa  := '11%';
  open mycursor for v_sql using v_aa;
  loop    fetch mycursor into v_bb;
    exit when mycursor%notfound;
  end loop;
  close mycursor;
end;

--Using Bulking Dynamic Sql(提高性能,减少上下文等待时间)
declare
  type cursorType is ref cursor;
  mycursor cursorType;
  type numList is table of varchar2(100);
  bbs numList;
  v_sql varchar2(500);
begin  v_sql := 'select bb from aaa_test';
  open mycursor for v_sql;
  fetch mycursor bulk collect into bbs;
  close mycursor;
 
  execute immediate v_sql
    bulk collect into bbs;
end;

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