Oracle
的存储过程，是我们使用数据库应用开发的重要工具手段。在存储过程中，我们大部分应用场景都是使用
DML
语句进行数据增删改操作。本篇中，我们一起探讨一下数据定义语句
DDL
在存储过程中使用的细节和要点。
1
、“借道而行”的
DDL
从
Oracle PL/SQL
和存储过程程序开发原则上，应该是不鼓励在
SP
中使用
DDL
语句的。首先一个表现，就是
Oracle
在编译时就不允许直接在
SP
中使用
DDL
语句。下面我们使用
Oracle 10gR2
作为实验环境。
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE
10.2.0.1.0
Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 – Production
建立存储过程
p_test_nc
，进行简单的数据表创建。
SQL> create or replace procedure P_TEST_NC
2
is
3
begin
4
create table t (id number);
5
end P_TEST_NC;
6
/
Warning: Procedure created with compilation errors
SQL> select name, text from user_errors;
NAME
TEXT
---------- --------------------------------------------------------------------------------
P_TEST_NC
PLS-00103:
出现符号
"CREATE"
在需要下列之一时：
begin case declare exit
for goto if loop mod null pragma raise return select update
while with
close current delete fetch lock insert open rollback
savepoint set sql execute commit forall merge pipe
显然，在编译时
Oracle
就报错不允许存储过程创建。之后的实验
drop
和
truncate table
操作，也都是不允许直接在存储过程中书写
DDL
语句。说
明起码使用直接的
DDL
语句，存储过程是不能编译通过的。
那么，有没有什么折中的方法呢？我们说是有的，就是借助“
execute immediate
”方法，“绕过”编译过程中对
DDL
的屏蔽。我们使用
truncate table DDL
语句实验。
SQL> create or replace procedure P_TEST_NC
2
is
3
begin
4
execute immediate
'truncate table t'
;
5
end P_TEST_NC;
6
/
Procedure created
编译通过了，
DDL
语句以一个字符串的形式避开了编译时
Oracle
的语法检查，编译成功。那么，执行起来会不会报运行时错误呢？
SQL> exec p_test_nc;
PL/SQL procedure successfully completed
执行成功，说明：
在
Oracle
存储过程中，可以使用
exectue immediate
语句绕开编译时对
DDL
语句的检查，生成运行代码。
2
、
SP
中
DDL
权限
任何程序编译执行都会伴随着语法语义的一系列检查。使用
execute immediate
虽然可以回避编译时检查，但是
SQL
语句还是面临着运行时检查的问题。下面看实验的例子。
--
在
scott
用户下进行试验；
SQL> create or replace procedure P_TEST_NC
2
is
3
begin
4
execute immediate '
create table t(id number)
';
5
end P_TEST_NC;
6
/
Procedure created
–
编译时通过；
SQL> exec p_test_nc;
begin p_test_nc; end;
ORA-01031:
权限不足
ORA-06512:
在
"SCOTT.P_TEST_NC", line 4
ORA-06512:
在
line 1
在用户自己的
schema
下创建数据表，难道是不允许的吗？显然不是。
SQL> create table m (id number);
Table created
单独创建是允许的，说明是由于权限机制导致的问题。我们切换到
sys
用户上，提高
scott
用户权限。
Connected as SYS
-
-
赋予最高创建数据表的系统权限；
SQL> grant create any table to scott;
Grant succeeded
切换回
scott
用户，继续实验。
SQL> conn scott/tiger@orcl;
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
Connected as scott
SQL> exec p_test_nc;
PL/SQL procedure successfully completed
SQL> select * from t;
----------
执行成功！这个原因是什么呢？还是由于
存储过程权限体系特点和
DDL
语句特点共同造成的。
在之前笔者的系列文章《所有者权限和调用者权限》（
http://space.itpub.net/17203031/viewspace-692161
）中，介绍了
Oracle
存储过程采用的两种权限体系方式和
role
权限在存储过程执行中的特殊性。
默认情况下，
Oracle
对存储过程是使用所有者权限，也就是说：如果用户
B
调用了用户
A schema
下的一个存储过程，其中使用的对象权限和系统权限，全部都是用户
A
的。如果用户
A
没有权限，用户
B
执行要报错。
同时，用户的角色权限在进入存储过程后，会被剥离掉，是不其效果的。
结合上面的实验，就好解释了：
scott
自身只拥有一个
resource
的角色权限，单独在
SQL
中使用没有问题。进入到
SP
之后，这个
create table
的权限就被剥离掉了。而该
SP
存在被其他用户调用生成数据表的可能。所以会在运行时报错权限不足。
当我们显示的赋予
scott
用户
create any table/create table
之后，系统权限就可以渗透到
SP
中起效果了。
这并不是解决该问题的唯一方法。此处我们可以使用调用者权限机制，改写
SP
代码。首先我们剔除掉
scott
的
create any table
权限。
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
Connected as SYS
SQL> revoke create any table from scott;
Revoke succeeded
SQL> conn scott/tiger@orcl;
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
Connected as scott
SQL> exec p_test_nc;
begin p_test_nc; end;
ORA-01031:
权限不足
ORA-06512:
在
"SCOTT.P_TEST_NC", line 4
ORA-06512:
在
line 1
我们改写代码为：
SQL> create or replace procedure P_TEST_NC
2
Authid Current_User
3
is
4
begin
5
execute immediate 'create table t (id number)';
6
end P_TEST_NC;
7
/
Procedure created
SQL> exec p_test_nc;
PL/SQL procedure successfully completed
执行成功，这里使用“
authid Current_user
”将存储过程转化为调用者权限。每次调用存储过程，都是动态根据调用者的权限构成去判定是否有权限，这样就回避了该问题的出现。
总之：在使用
DDL
在存储过程中时，权限管理和使用的复杂度是在增加。
4
、
DDL
对事务的提交影响
将
DDL
语句放置在存储过程中，潜在最大风险就是对事务管理的破坏。在
Oracle
中，如果调用一个
DDL
语句，潜藏效果就是将当前会话的未提交事务进行提交。这个过程显然是对原有的事务逻辑破坏。
SQL> create table m (id number);
Table created
SQL> select * from m;
----------
SQL> create or replace procedure P_TEST_NC
2
is
3
begin
4
insert into m values (3);
5
execute immediate 'truncate table t';
7
rollback;
8
end P_TEST_NC;
9
/
Procedure created
--
执行代码
SQL> exec p_test_nc;
PL/SQL procedure successfully completed
--
事务提交
SQL> select * from m;
----------
从上面的例子上，我们可以清楚的看到现象。由于中间的
truncate table
操作，引起数据表
m
的插入操作被提交
commit
。而真正的事务逻辑可能是一个
rollback
。
所以，在
SP
中使用
DDL
命令，
可能引起业务逻辑的不可控提交和数据不一致，这个风险在任何应用中是不可以允许的。
那么，有没有方法回避这个过程呢？经一个同事提醒，的确可以使用手段回避。
5
、
DDL
与自治事务
自治事务（
AUTONOMOUS_TRANSACTION
）是保证在事务进行过程中一段独立的事务过程。如果在
DDL
操作外套入一个自治事务过程，是否就可以回避问题了。
SQL> select * from m;
----------
SQL> create or replace procedure P_TEST_NC is
2
procedure p_inner_test
3
is
4
PRAGMA AUTONOMOUS_TRANSACTION;
5
begin
6
--
调用
ddl
7
execute immediate 'truncate table t';
8
end;
9
begin
10
insert into m values (3);
11
p_inner_test;
13
rollback;
14
end P_TEST_NC;
15
/
Procedure created
SQL> exec p_test_nc;
PL/SQL procedure successfully completed
SQL> select * from m;
----------
实验成功，通过自治事务的确可以回避
DDL
的事务问题。
6
、结论
DDL
在
SP
中，与常规的
DML
操作差异很大。这种差异不仅仅是语法上，更多的是权限、事务等更深层次复杂的差异。所以，从
Oracle
的角度看，尽量少在
SP
中使用
DDL
语句，避免出现不可控的问题。
广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员
