When we run SQL statements interactively in SQL*Plus or SQL Developer or another utility, we decide how to react to an error. If a SQL statement fails from a syntax error or from an unexpected data condition such as ORA-01403: no rows found, do we want to proceed and run the next SQL statement, or do we want to simply roll back all work that has been done and exit? When executing interactively, we can decide interactively. But what about when we’re running a script? SQL*Plus provides the WHENEVER command to direct SQL*Plus how to react to failures. WHENEVER is particularly useful when running a script. Table 11-7 describes two variations of the command.
Table 11-7. WHENEVER Error-Handling Conditions
Error condition Description
WHENEVER OSERROR Triggers whenever a SQL*Plus command like CONNECT, DISCONNECT, SPOOL, HOST, START, or any other command which interacts with the operating system fails. WHENEVER SQLERROR Triggers whenever a SQL statement like SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER, DROP, TRUNCATE, GRANT, REVOKE, or any other SQL command fails.
Table 11-8. WHENEVER Error-Handing Directives
Error condition Description
EXIT [ exit-status | txn-directive ] Exit from SQL*Plus with the specified exit status after committing or rolling back the current transaction as directed.CONTINUE [ txn-directive ] Continue executing SQL*Plus after committing, rolling back, or doing nothing.
Exit-status Can be one of: [ SUCCESS | FAILURE | n | substitution-variable | bind-variable ] where SUCCESS is an operating-system dependent exit status signifying successful completion, FAILURE is an operating-system dependent exit status signifying failure, and n is a number value. SQL*Plus substitution variables and SQL*Plus bind variables containing numeric values can also be used as return statuses. SUCCESS is the default. Txn-directive Can be one of: [ COMMIT | ROLLBACK | NONE ] where NONE can be used only with the CONTINUE directive. When used with the EXIT directive, COMMIT is the default and when used with the CONTINUE directive, NONE is the default.
So, if a SQL*Plus script. contains five UPDATE statements in a row and you want the script. to stop executing, roll back any work already performed, and then exit to the operating system with a failure status, your script. might look something like that shown in Listing 11-45.
Listing 11-45. Error-Handling in a SQL*Plus Script
whenever oserror exit failure rollback whenever sqlerror exit failure rollback set echo on feedback on timing on spool update_script. update … update … update … update … update … exit success commit (*由此可以看出不在前面加whenever条件也是可以的，因为exit、continue都是指令，都可以单独使用的)In Listing 11-45, we see the use of the WHENEVER command directing SQL*Plus to exit back to the operating system with a FAILURE exit status, and perform. a ROLLBACK as it does so, should any OS commands (such as SPOOL) or SQL commands (such as UPDATE) fail. If all of the commands are successful and we reach the very last line of the script, then we will EXIT back to the operating system with SUCCESS exit status and perform. a COMMIT as it does so.
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/27042095/viewspace-735492/，如需转载，请注明出处，否则将追究法律责任。