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;