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 Settings\chent>sqlplus "sys/change_on_install@sbox as sysdba"
SQL*Plus: Release 9.2.0.6.0 - Production on Fri Mar 31 17:00:44 2006
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 Settings\chent>sqlplus "sys/change_on_install@sbox as sysdba"
SQL*Plus: Release 9.2.0.6.0 - Production on Fri Mar 31 17:04:11 2006
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> print jdfk
SP2-0552: Bind variable "JDFK" not declared.
参考：
http://download-west.oracle.com/docs/cd/B10501_01/server.920/a90842/ch13.htm#1074195
Posted by Sky at 04:26 PM | Permalink | comments(0) | Edit | Database