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