WHENEVER SQLERROR
{EXIT [SUCCESS|FAILURE|WARNING|n|variable|:BindVariable]
[COMMIT|ROLLBACK]|CONTINUE [COMMIT|ROLLBACK|NONE]}
作用:
当 sql、pl/sql 执行过程中出现错误,则执行一些操作。注:不包括 sql*plus 命令的错误。
出现错误时,可以执行两种操作 EXIT 和 CONTINUE,分别为退出 sql*plus 和 不退出。
[SUCCESS|FAILURE|WARNING|n|variable|:BindVariable]的具体作用没有搞清楚,可能是以什么样的方式退出 sql*plus 吧,如 SUCCESS、FAILURE 或者用变量,比如SQL.SQLCODE。
[COMMIT|ROLLBACK]:在退出 SQL*PLUS 前是提交事务还是回滚事务。
[COMMIT|ROLLBACK|NONE]:如果不退出,那么也可以指定提交事务还是回滚事务,或者什么也不做。
SQL> whenever sqlerror exit SQL.SQLCODE rollback
SQL> select sysdate from dual;
SYSDATE
----------
2006-03-31
SQL> update aa set ff=5;
update aa set ff=5
*
ERROR at line 1:
ORA-00942: table or view does not exist
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.6.0 -Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production
WHENEVER SQLERROR 生命周期应该是自该命令执行到 sql*plus 退出前的所有时段,下面实验可以证明:
C:Documents and Settingschent>sqlplus"sys/change_on_install@sbox as sysdba"
SQL*Plus: Release 9.2.0.6.0 - Production on Fri Mar 31 17:00:442006
Copyright (c) 1982, 2002, Oracle Corporation.All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production
SQL> whenever sqlerror continue rollback
SQL> select sysdate from dual;
SYSDATE
----------
2006-03-31
SQL> update aa set ff=5;
update aa set ff=5
*
ERROR at line 1:
ORA-00942: table or view does not exist
Rollback complete.
SQL> disconnect
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.6.0 -Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production
SQL> conn sys/change_on_install@sbox as sysdba
Connected.
SQL> update aa set ff=5;
update aa set ff=5
*
ERROR at line 1:
ORA-00942: table or view does not exist
Rollback complete.
SQL> conn sys/change_on_install@mliv as sysdba
Connected.
SQL> update aa set ff=5;
update aa set ff=5
*
ERROR at line 1:
ORA-00942: table or view does not exist
Rollback complete.
SQL> exit
Disconnected from Oracle8i Enterprise Edition Release 8.1.7.0.0 -Production
With the Partitioning option
JServer Release 8.1.7.0.0 - Production
C:Documents and Settingschent>sqlplus " as sysdba"
SQL*Plus: Release 9.2.0.6.0 - Production on Fri Mar 31 17:04:112006
Copyright (c) 1982, 2002, Oracle Corporation.All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production
SQL> update aa set ff=5;
update aa set ff=5
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL>
下面实验证明 WHENEVER SQLERROR 不影响sql*plus的命令错误:
SQL> whenever sqlerror exit sql.sqlcode rollback
SQL> print jdfk
SP2-0552: Bind variable"JDFK" not declared.
参考:
http://download-west.oracle.com/docs/cd/B10501_01/server.920/a90842/ch13.htm#1074195