execute immediate使用方法

1.常规用法：立刻执行sql语句:将需要执行的sql存入字符串，然后执行

 sql  varchar2(20);
 sql := ' UPDATE Person SET Name = 'tom' WHERE id=9527   ';
 execute immediate sql;

2.从动态语句检索值(INTO子句)

declare   
   l_cnt  varchar2(20);   
begin   
   execute immediate 'select count(1) from emp'   
     into l_cnt;   
   dbms_output.put_line(l_cnt);   
end; 

3.给动态语句传值(USING 子句)

使用标记 :1 :2 :3 代表第一个第二个第三个参数
    declare   
       sql1  varchar2(20) := 'test';   
       sql2  varchar2(10) := 'id';   
       begin   
       execute immediate 'insert into dept vals   (:1, :2, :3)'   
         using 9527, sql1, sql2;   
       commit;   
    end;

4.动态调用例程.例程中用到的绑定变量参数必须指定参数类型.黓认为IN类型,其它类型必须显式指定

declare   
   routin   varchar2(100) := 'gen2161.get_rowcnt';   
   name     varchar2(20) := 'emp';   
   cnt      number;   
   status   varchar2(200);   
begin   
   execute immediate 'begin ' || routin || '(:2, :3, :4); end;'   
   using in name, out cnt, in out status;   
  
   if status != 'OK' then   
      dbms_output.put_line('error');   
   end if;   
end;  

5.带参与赋值结合

INTO子句应该用在USING子句前

declare   
   dept     pls_integer := 20;   
   name     varchar2(20);   
   loc      varchar2(20);   
begin   
   execute immediate 'select dname, loc from dept where deptno = :1'   
     into name, loc   
     using dept ;   
end;

6.执行多行sql语句

    declare   
       l_sal  pls_integer := 2000;   
    begin   
       execute immediate 'insert into temp(empno, ename) '||'select empno, ename from emp '||'where sal > :1'   
         using l_sal;   
       commit;   
    end;