When Things Go Wrong
The PL/SQL language offers a powerful mechanism for both raising and handling errors. In the following procedure, I obtain the name and balance of an account from its ID. I then check to see if the balance is too low; if it is, I explicitly raise an exception, which stops my program from continuing:
1 CREATE OR REPLACE PROCEDURE check_account ( 2 account_id_in IN accounts.id%TYPE) 3 IS 4 l_balance_remaining NUMBER; 5 l_balance_below_minimum EXCEPTION; 6 l_account_name accounts.name%TYPE; 7 BEGIN 8 SELECT name 9 INTO l_account_name10 FROM accounts11 WHERE id = account_id_in;1213 l_balance_remaining := account_balance (account_id_in);1415 DBMS_OUTPUT.put_line (16 'Balance for ' || l_account_name ||17 ' = ' || l_balance_remaining);1819 IF l_balance_remaining < 100020 THEN21 RAISE l_balance_below_minimum;22 END IF;2324 EXCEPTION25 WHEN NO_DATA_FOUND26 THEN27 -- No account found for this ID28 log_error (...);2930 WHEN l_balance_below_minimum31 THEN32 log_error (...);33 RAISE;34 END;Let's take a more detailed look at the error-handling aspects of this code in the following table:
| Line(s) | Description |
| I declare my own exception, called l_balance_below_minimum. Oracle provides a set of predefined exceptions, such as DUP_VAL_ON_INDEX, but I need something specific to my application, so I must define it myself in this case. | |
| 8-11 | This query retrieves the name for the account. If there is no account for this ID, then Oracle will raise the predefinedNO_DATA_FOUND exception, causing the program to stop. |
| 19-22 | If the balance is too low, I will explicitly raise my own exception, as I have encountered a serious problem with this account. |
| The EXCEPTION keyword denotes the end of the executable section and the beginning of the exception section in which errors are handled. | |
| 25-28 | This is the error-handling section for the situation where the account is not found. If NO_DATA_FOUND was the exception raised, it will be "trapped" here and the error will be logged. |
| 30-33 | This is the error-handling section for the situation where the account balance has gotten too low (my application-specific exception). If l_balance_below_minimum was raised, it will be "trapped" here and the error will be logged. Then, due to the seriousness of the error, I will re-raise the same exception, propagating that error out of the current procedure and into the PL/SQL block that called it. |
Chapter 6 takes you on an extensive tour of the error-handling mechanisms of PL/SQL.
There is, of course, much more that can be said about PL/SQL—which is why you have about another 950 pages of material to study in this book! However, these initial examples should give you a good feel for the kind of code you will write with PL/SQL, some of its most important syntactical elements, and the ease with which one can write—and read—PL/SQL code.
1.4 About PL/SQL Versions
Each version of the Oracle database comes with its own corresponding version of PL/SQL. As you use more up-to-date versions of PL/SQL, an increasing array of functionality will be available to you. One of our biggest challenges as PL/SQL programmers is simply "keeping up." We need to constantly educate ourselves about the new features in each version—figuring out how to use them and how to apply them to our applications, and determining which new techniques are so useful that we should modify existing applications to take advantage of them.
Table 1-1 summarizes the major elements in each of the versions (past and present) of PL/SQL in the database. It offers a very high-level glimpse of the new features available in each version. Following the table, you will find more detailed descriptions of "what's new" in PL/SQL in the latest Oracle versions, Oracle8i and Oracle9i.
|
