execute immediate

首先在这里发发牢骚，指责下那些刻板的书写方式，不考虑读者理不理解，感觉就是给专业人员用来复习用的一样，没有前戏，直接就高潮，实在受不了！没基础或基础差的完全不知道发生了什么，一脸懵逼的看着，一星差评！！！

execute immediate

以下引用介绍比较好的例子说明

create or replace procedure proc_test(
--参数区域
)
is 
--变量区域
    --sql脚本
    v_sql varchar2(2000) :='';
    --记录学生数量
    v_num number;
begin
--执行区域

    -- execute immediate用法1：立刻执行sql语句
    v_sql := 'create or replace view myview as select id,name from student';
    execute immediate v_sql;
    
    --- execute immediate用法2：立刻执行sql语句，并赋值给某个变量
    v_sql := 'select count(1) from student';
    execute immediate v_sql into v_num;
    
    -- execute immediate用法3：带参数的sql
    v_sql:='select * from student t where t.name=:1 and t.age=:2'; 
    execute immediate v_sql using 'zhangsan',23;
    
end proc_test;
/

看了上面的代码，是否觉得理解？no,no,no

对execute immediate  的解释如下：

简单来说 就是你一个存储过程当中 创建了一个表 table_a 然后要用insert into将其他的数据插入到这个table_a当中，但是因为你在创建过程的时候 table_a还不存在，过程就会显示有编译错误，因为table_a不存在必然导致过程无法执行，所以无法编译成功，而把insert into语句加如到 execute immediate之后 则oracle不会再去理会这个对象是否存在，因此可以成功编译和执行。

看完了，估计还会有小朋友举手问，跟动态有什么关系，为何扯上动态，哪里动态了，能不能讲明白，等等。。。

敲黑板，人家说的动态，是说execute immediate后面跟的sql代码不固定，你想写就写啥，动态的！

然后又有小朋友，站起来了，你呀的，胡我啊，说最后代码，随便写，我都想笑，这个跟直接写sql代码，不要前面的execute immediate有什么区别？不都执行那条语句么？

哈哈，小朋友你坐下别激动，你问到点上了，execute immediate后边sql代码要用一对单引号的(即' ')，而直接写sql没有的对吧，告诉你动态的秘诀就在于execute immediate后边sql代码可以去拼接，这就实现了所谓动态，会根据不同情况，拼接不同的代码。

简单举例

直接写的   select * from dual;

用execute immediate写    v_sql:='select * from dual';

                                          execute immediate v_sql; 

一般来说直接写sql的性能是高于拼字符串的，因为如果执行拼字符串的需要内部自动调动oracle机制，先解析字符串映射成sql语句然后再执行。

但是拼sql的方式有好处。即sql语句是一个字符串可以动态拼接，根据不同的条件来改变sql语句，这是直接写sql所不能达到的。

拼sql还有个好处就是    v_sql:='select * from tables t where t.c_date=:1 and t.name=:2';  

                                         execute immediate v_sql using '20130304','xiaoming';

可以动态的对参数传递值，这是最大的优势。 

语法结构：

execute immediate dynamic_string
[into {define_variable[,define_variable]…| record}]
[using [in | out | in out] bind_argument[,[in | out \ in out] bind_argumnet]…]
[{returning | return} into bind_argument[,bind_argument]…];

本地动态sql语句一个优点是可以在代码中是使用绑定变量。

先说说绑定变量 ":"称绑定变量指示符，解释如下：

它是用户放入查询中的占位符，它会告诉oracle" 现在生成一个方案框架，实际执行语句的时候，会提供应该使用的实际值"。

例子如下：
select * from emp where dep='sale' ;                   //不使用绑定变量
select * from emp where dep=:sale                    //使用绑定变量

用法
　　　　处理ddl操作(create,alter,drop)
　　　　create or replace procedure drop_table(table_name varchar2)
　　　　is
　　　　 sql_statemet varchar2(100);
　　　　begin 
　　　　 sql_statement:=’drop table’ || table_name;
　　　　 execute immediate sql_statement;
　　　　end;
　　　　/
　　　　建立过程drop_table后，调用如下：
　　　　sql> exec drop_table(‘worker’)
　　　　处理dcl操作(grant revoke)
　　　　sql> conn system/manager
　　　　create or replace procedure grant_sys_priv(priv varchar2,username varchar2)
　　　　is
　　　　 sql_stat varchar2(100);
　　　　begin
　　　　 sql_stat:=’grant “ || priv|| ’ to ’|| username;
　　　　execute immediate sql_stat;
　　　　end;
　　　　/
　　　　调用
　　　　sql> exec grant_sys_priv(‘create session’,’scott’)
　　　　处理dml操作(insert update delete)
　　　　如果dml语句带有占位符，那么在e i语句中则要带using子句
　　　　如果dml语句带有returning子句，那么e i语句中要带有returninginto子句
　　　　例子，处理单行查询：
　　　　declare
　　　　 sql_stat varchar2(100);
　　　　 emp_record tbl%rowtype;
　　　　begin
　　　　 sql-stat:='select * from tbl where tblno=:no';
　　　　 execute immediate sql_stat into emp_record using &1;
　　　　 dbms_output.put_line(emp_record.ename||emp_record.sal);
　　　　end;

execute immediate -- 用法例子


1. 在pl/sql运行ddl语句


begin
   execute immediate 'set role all';
end;


2. 给动态语句传值(using 子句)


declare
   l_depnam varchar2(20) := 'testing';
   l_loc     varchar2(10) := 'dubai';
   begin
   execute immediate 'insert into dept values   (:1, :2, :3)'
     using 50, l_depnam, l_loc;
   commit;
end;


3. 从动态语句检索值(into子句)


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


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


declare
   l_routin    varchar2(100) := 'gen2161.get_rowcnt';
   l_tblnam    varchar2(20) := 'emp';
   l_cnt       number;
   l_status    varchar2(200);
begin
   execute immediate 'begin ' || l_routin || '(:2, :3, :4); end;'
     using in l_tblnam, out l_cnt, in out l_status;

   if l_status != 'ok' then
      dbms_output.put_line('error');
   end if;
end;


5. 将返回值传递到pl/sql记录类型;同样也可用%rowtype变量


declare
   type empdtlrec is record (empno   number(4),
                            ename   varchar2(20),
                            deptno   number(2));
   empdtl empdtlrec;
begin
   execute immediate 'select empno, ename, deptno ' ||
                    'from emp where empno = 7934'
     into empdtl;
end;


6. 传递并检索值.into子句用在using子句前


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


7. 多行查询选项.对此选项用insert语句填充临时表，
用临时表进行进一步的处理,也可以用ref cursors纠正此缺憾.

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;