oracle execute immediate create,execute immediate在oracle存储过程中的使用

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

​

​

​

​