0287-0288

Previous Table of Contents Next

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.

Oracle8 Enhancements

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:

  • National language support
  • Object types
  • Abstract datatypes
  • Collection types
  • External procedure reference

National Language Support

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.

Object Types

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 Datatypes

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. 

Collection Types

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.

External Procedure Reference

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.

Summary

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.

Previous Table of Contents Next


Oracle Unleashed
Oracle Development Unleashed (3rd Edition)
ISBN: 0672315750
EAN: 2147483647
Year: 1997
Pages: 391

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net