execute immediate在存储过程中的使用

execute immediate 在存储过程中使用较为频繁，其作用是巨大的，下面简单介绍其基本用法

1：在存储过程中，我们知道只能使用DML语句，那么如果要使用DDL语句呢，就得使用execute immediate。例如：

   ​WANG@db>create or replace procedure pro1

  2     is 

  3     begin 

  4      create table table1(id int);

  5    end;

​6  /

​Warning: Procedure created with compilation errors.

因为没有存储过程（匿名块也一样），不能使用DDL语句，所以报错，接下来我们使用execute immediate在存储过程中使用DDL语句

WANG@db>create or replace procedure pro1

  2    is 

  3  begin 

  4  execute immediate 'create table table1(id int)';

  5  end;

  6  /

Procedure created.

执行该存储过程就可以创建一个表了，但是这里要注意你的用户必须有创建表的权限，并且这个权限不是在角色里面的赋予的。否则会报错。

WANG@db>conn / as sysdba 

Connected.

SYS@db>grant create table to wang;

Grant succeeded.

SYS@db>conn wang/beijing 

Connected.

WANG@db>exec pro1;  

2：在写存储过程中，可能会碰到你查询的表是变量值，这个是时候直接使用变量是不行的，因外其会把变量名作为表名，而不是把变量值作为表名（这跟SHELL有所不同）。

例：​

​

WANG@db>create or replace procedure pro2 

  2   is 

  3  v_table_name varchar2(20);

  4  v_count varchar2(20);

  5   begin 

  6  v_table_name :='testemp';

  7  select count(*) into v_count from v_table_name;

  8  dbms_output.put_line(v_count);

  9  end;

 10  /

Warning: Procedure created with compilation errors.

可以使用字符串截取你要是使用的SQL语句，在使用execute immediate 这个SQL实现该功能，这里需要说明的是普通的存储过程是通过select xxx into yyy from zz 来进行赋值的，但是使用execute immediate是使用execute immediate sql into zz来进行复制的。

例如：使用exexute immediate 来实现上面的功能

WANG@db>create or replace procedure pro2

  2       is 

  3     v_table_name varchar2(20);

  4     v_count number;

  5     v_sql varchar2(100);

  6     begin 

  7      v_table_name:='testemp';

  8      v_sql:='select count(*) from   ' || v_table_name;

  9     execute immediate v_sql into v_count;

 10     dbms_output.put_line(v_count);

 11     end;

 12    /

Procedure created.

WANG@db>exec pro2;

PL/SQL procedure successfully completed.

WANG@db>set serverout on

WANG@db>exec pro2;

14

这跟SHELL里面是不一样的，我们看到在使用DML语句后面接变量时，其不会使用变量的值，但是在SEHLL里面是使用的、，例如：

脚本内容：​

[oracle@ogg_source /home/oracle]$cat test.sh

#!/bin/bash 

table_name=testemp

fun(){

table_name=$1

sqlplus -s wang/beijing << EOF

set feedback off pagesize 0 linesize 200

select count(*) from $table_name;

exit;

EOF

}

main(){

v_count=`fun $table_name`

echo $v_count

}

main

执行结果：​

[oracle@ogg_source /home/oracle]$sh test.sh

14