oracle创建函数编译错误,oracle – 我可以在函数编译失败时使SQL * Plus退出并出现错误吗？...

这有点令人费解,但你可以.

初始CREATE FUNCTION或CREATE PROCEDURE语句将创建函数或过程.您必须在脚本中检测到存在错误,并在出现错误时显式删除函数和/或过程.但是你必须在删除对象之前捕获错误.这将需要在CREATE语句之后的脚本中的一些代码.

whenever sqlerror exit failure;

create or replace procedure compile_error

as

begin

select count(*)

into no_such_variable

from emp;

end;

/

show error;

declare

l_num_errors integer;

begin

select count(*)

into l_num_errors

from user_errors

where name = 'COMPILE_ERROR';

if( l_num_errors > 0 )

then

execute immediate 'DROP PROCEDURE compile_error';

raise_application_error( -20001, 'Errors in COMPILE_ERROR' );

end if;

end;

/

执行时,此脚本将生成以下输出,其中包含错误并将删除该过程.

SQL> @c:\temp\compile_errors.sql

Warning: Procedure created with compilation errors.

Errors for PROCEDURE COMPILE_ERROR:

LINE/COL ERROR

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

4/3 PL/SQL: SQL Statement ignored

5/10 PLS-00201: identifier 'NO_SUCH_VARIABLE' must be declared

6/5 PL/SQL: ORA-00904: : invalid identifier

declare

*

ERROR at line 1:

ORA-20001: Errors in COMPILE_ERROR

ORA-06512: at line 12

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options