Page 287
In this example, the conditional branch transfers control to the clean_up label for further processing. Use of the GOTO statement is discouraged because it could lead to unstructured code. Other constructs within PL/SQL enable you to write code that is easier to understand and maintain.
Mainly limited to improving code readability, the NULL statement is a way to show that all possible logic choices were considered. NULL is considered an executable statement. This example uses NULL to show that there is nothing to do if g_recip_counter is within range of max_lines:
if g_recip_counter > max_lines then g_recip_counter = max_lines; else NULL; end if;
Obviously, you can end this code without the ELSE clause, but using the NULL shows that other options were considered.
Object extensions in the Oracle8 object-relational database allow the creation of new datatypes and simplify the task of data modeling. The PL/SQL extensions are discussed here for completeness, realizing that only large database applications make extensive use of this functionality. You can find details in the Sams titles Teach Yourself PL/SQL in 21 Days and Teach Yourself Oracle8 Database Development in 21 Days. Enhancements in the latest version of PL/SQL to support the object-relational technology of Oracle8 include the following:
Two new datatypes were implemented in the latest release of PL/SQL to support national language conventions. The new datatypes are NCHAR and VARCHAR2; they are explained in detail in Chapter 11.
The Oracle relational model consists of character, number, and date datatypes. Extensions for Oracle8 allow the definition of abstract datatypes with an object-oriented nature. Taken a step further, complex datatypes within Oracle8 can be represented as collections of similar objects in nested tables or array structures.
Page 288
Abstract object types are supported in the new version of PL/SQL. These objects are created using the object-oriented data model that defines an object by associated data called attributes. An object called Business can have attributes such as Street Address, Phone Number, Fax
Number, and E-Mail Address.
Nested tables or array structures are available in Oracle8 as collection types for collections of similar objects. Object type definition and modeling within Oracle8 follow the emerging SQL3 standards. Adherence to standards will allow representations and collections of objects to support C++ and Java object-based applications.
The latest version of PL/SQL allows you to include calls to code written in a 3GL such as C within the database. This opens many avenues for the application developer to interface to external applications or external hardware devices using existing application code. This feature also allows more effective debugging of PL/SQL procedures.
This chapter discusses PL/SQL procedures, subprograms, and the structures associated with this procedural language extension to the Oracle database. A sample SQL script for building a packaged procedure is also included. In the course of this chapter, I developed a sample packaged procedure named order_total.
I continued the development of this procedure with a discussion of error handling through the use of exceptions. Many of the examples presented during discussions of various topics came directly from the order_total procedure. As such, this sample procedure covers the basic elements of the PL/SQL language and is presented in its entirety on the enclosed CD-ROM. Additional topics of interest to PL/SQL developers, including those related to the new Oracle8 object-relational technology, appear at the end of the chapter.