create 执行存储过程报错出现符号_DDL语句在存储过程中使用的细节和要点

的存储过程，是我们使用数据库应用开发的重要工具手段。在存储过程中，我们大部分应用场景都是使用DML语句进行数据增删改操作。本篇中，我们一起探讨一下数据定义语句在存储过程中使用的细节和要点。

1、“借道而行”的DDL

从Oracle PL/SQL和存储过程程序开发原则上，应该是不鼓励在SP中使用DDL语句的。首先一个表现，就是Oracle在编译时就不允许直接在SP中使用DDL语句。下面我们使用Oracle 10gR2作为实验环境。

SQL> select * from v$version;

BANNER

----------------------------------------------------------------

Oracle Database10gEnterpriseEdition Release10.2.0.1.0 - Prod

PL/SQL Release10.2.0.1.0 - Production

CORE10.2.0.1.0Production

TNS for 32-bit Windows: Version10.2.0.1.0 - Production

NLSRTL Version10.2.0.1.0 – Production

建立存储过程p_test_nc，进行简单的数据表创建。

SQL> create or replace procedure P_TEST_NC

2is

3begin

4create table t (id number);

5end P_TEST_NC;

6/

Warning: Procedure created with compilation errors

SQL> select name, text from user_errors;

NAMETEXT

---------- --------------------------------------------------------------------------------

P_TEST_NCPLS-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

2is

3begin

4execute immediate'truncate table t';

5end 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

2is

3begin

4execute immediate 'create table t(id number)';

5end 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 Database10gEnterpriseEdition Release10.2.0.1.0

Connected as scott

SQL> exec p_test_nc;

PL/SQL procedure successfully completed

SQL> select * from t;

ID

----------

执行成功！这个原因是什么呢？还是由于存储过程权限体系特点和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 Database10gEnterpriseEdition Release10.2.0.1.0

Connected as SYS

SQL> revoke create any table from scott;

Revoke succeeded

SQL> conn scott/tiger@orcl;

Connected to Oracle Database10gEnterpriseEdition Release10.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

2Authid Current_User

3is

4begin

5execute immediate 'create table t (id number)';

6end 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;

ID

----------

SQL> create or replace procedure P_TEST_NC

2is

3begin

4insert into m values (3);

5execute immediate 'truncate table t';

6

7rollback;

8end P_TEST_NC;

9/

Procedure created

--执行代码

SQL> exec p_test_nc;

PL/SQL procedure successfully completed

--事务提交

SQL> select * from m;

ID

----------

3

从上面的例子上，我们可以清楚的看到现象。由于中间的truncate table操作，引起数据表m的插入操作被提交commit。而真正的事务逻辑可能是一个rollback。

所以，在SP中使用DDL命令，可能引起业务逻辑的不可控提交和数据不一致，这个风险在任何应用中是不可以允许的。

那么，有没有方法回避这个过程呢？经一个同事提醒，的确可以使用手段回避。

5、DDL与自治事务

自治事务(AUTONOMOUS_TRANSACTION)是保证在事务进行过程中一段独立的事务过程。如果在DDL操作外套入一个自治事务过程，是否就可以回避问题了。

SQL> select * from m;

ID

----------

SQL> create or replace procedure P_TEST_NC is

2procedure p_inner_test

3is

4PRAGMA AUTONOMOUS_TRANSACTION;

5begin

6--调用ddl

7execute immediate 'truncate table t';

8end;

9begin

10insert into m values (3);

11p_inner_test;

12

13rollback;

14end P_TEST_NC;

15/

Procedure created

SQL> exec p_test_nc;

PL/SQL procedure successfully completed

\

SQL> select * from m;

ID

----------

实验成功，通过自治事务的确可以回避DDL的事务问题。

6、结论

DDL在SP中，与常规的DML操作差异很大。这种差异不仅仅是语法上，更多的是权限、事务等更深层次复杂的差异。所以，从Oracle的角度看，尽量少在SP中使用DDL语句，避免出现不可控的问题。

PLS-00157:   AUTHIDonly allowed on schema-level programs

查了下错误原因 An AUTHID clause was specified for a subprogram inside a package or type. These clauses are only supported for top-level stored procedures, packages, and types.

大致意思就是authid只能用在顶级的存储过程、包、类型上，不能用在包或类型的子程序上。

在包上加入authid，执行正常了。

create or replace package rule_execute

authid current_user