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.
|
| Table 1-1. Oracle database and corresponding PL/SQL versions | ||
| Oracle version | PL/SQL version | Characteristics |
| Oracle6 | 1.0 | Initial version of PL/SQL, used primarily as a scripting language in SQL*Plus (it was not yet possible to create named, reusable, and callable programs) and also as a programming language in SQL*Forms 3. |
| Oracle7 | 2.0 | Major upgrade to Version 1. Adds support for stored procedures, functions, packages, programmer-defined records, PL/SQL tables, and many package extensions, including DBMS_OUTPUT and DBMS_PIPE. |
| Oracle 7.1 | 2.1 | Supports programmer-defined subtypes, enables the use of stored functions inside SQL statements, and offers dynamic SQL with the DBMS_SQL package. With Version 2.1, you can now execute SQL DDL statements from within PL/SQL programs. |
| Oracle 7.3 | 2.3 | Enhances functionality of PL/SQL tables, offers improved remote dependency management, adds file I/O capabilities to PL/SQL with the UTL_FILE package, and completes the implementation of cursor variables. |
| Oracle8 | 8.0 | The new version number reflects Oracle's effort to synchronize version numbers across related products. PL/SQL8 is the version of PL/SQL that supports the many enhancements of Oracle8, including large objects (LOBs), object-oriented design and development, collections (VARRAYs and nested tables), and Oracle/AQ (the Oracle/Advanced Queuing facility). |
| Oracle8i | 8.1 | The first of Oracle's i series (the "Internet database"), the corresponding release of PL/SQL offers a truly impressive set of added functionality, including a new version of dynamic SQL (native dynamic SQL), support for Java in the database, the invoker rights model, execution authority option, autonomous transactions, and high-performance "bulk" DML and queries. |
| Oracle9i Release 1 | 9.0 | Oracle9i came fairly quickly on the heels of Oracle8i. The first release of this version showcases the following features for PL/SQL developers: support for inheritance in object types, table functions and cursor expressions (allowing for parallelization of PL/SQL function execution), multi-level collections (collections within collections), and the CASE statement and expression. |
| Oracle9i Release 2 | 9.2 | The most recent version of the Oracle product set, Oracle9i Release 2 puts a major emphasis on XML (Extensible Markup Language), but also has some treats for PL/SQL developers, including associative arrays (index-by tables that can be indexed by VARCHAR2 strings in addition to integers), record-based DML (allowing you to perform an insert using a record, for example), and a thorough rewrite of UTL_FILE (now allowing you to copy, remove, and rename files). |
