0257-0260

Previous Table of Contents Next

Page 257

CHAPTER 12

PL/SQL ”A Procedural Language

IN THIS CHAPTER

• Packaged Procedures 258
• Creating Package Subprograms 260
• Cursors 263
• Procedure Variables 265
• Scalar Datatypes 268
• Composite Datatypes 269
• Processing Control 271
• Exception Handling 274
• Comments 276
• Stored Procedures 277
• Commits 279
• The STANDARD Package 279
• Additional Topics 280
• Oracle8 Enchancements 287

Page 258

This chapter discusses constructs of PL/SQL that enable application developers to use this procedural language interface to the Oracle RDBMS. Constructs within PL/SQL are similar to those found in a 3GL such as C and provide a flexible method to manipulate database information. PL/SQL is a mainstay of SQL*Forms, but writing database procedures outside forms provides access to common database-manipulation routines by other Oracle development tools while limiting the size of Forms applications. Extensions to support the new object-relational technology of Oracle8 are discussed at the end of this chapter.

Packaged Procedures

PL/SQL enables you to group all related programming in one database object, called a package. A complete set of PL/SQL routines that accomplish a certain programming task is called a packaged procedure. Packages also help you develop an application's interface separate from internal procedure code. This concept is discussed in more detail later in this chapter.

Package Creation

Before reading about the various aspects of the PL/SQL language, examine the syntax for creating a procedure and building a script for ease of maintenance as changes occur. This code is the first step in developing a sample package for calculating dollar totals in a merchandise order ”an example that is completed within this chapter. The following example illustrates some commands for creating this PL/SQL packaged procedure and script:

 set echo on spool order_total CREATE OR REPLACE PACKAGE order_total AS      (package specifications) END order_total CREATE OR REPLACE PACKAGE BODY order_total AS      (package body specifications) END order_total; DROP PUBLIC SYNONYM order_total; CREATE PUBLIC SYNONYM order_total for order_total; GRANT EXECUTE ON order_total TO PUBLIC; spool off SELECT   * FROM   user_errors WHERE   name='ORDER_TOTAL' ; 

Page 259

The first command in this script, SET ECHO ON, displays a listing of the package to the screen as it is compiled. ECHO combined with the SPOOL name command creates a list file (order_total.lst) for debugging purposes. This file contains the compilation of the procedure (including errors), complete with line numbers .

CREATE OR REPLACE PACKAGE name is the command that starts the procedure build in the database. Declarations of objects and subroutines within the package area are visible to your applications. Think of this area as the application interface to your PL/SQL code; at the very least, you must define the procedure entry routine here. Modifications to any specifications in this area require rebuilding your applications. The END statement signifies the end of the package specification area.

CAUTION

Any declarations in the package specification area that are incompatible with version 1.0 of PL/SQL (such as unsupported datatypes) cause compilation errors in SQL*Forms applications.

Next is the CREATE OR REPLACE PACKAGE BODY name statement that begins the specification area for declarations of PL/SQL objects and subroutines that only the procedure can "see." This area is invisible to your application and is not required in designing package procedures. However, designing procedures in this manner enables you to modify package body specifications without altering the application interface; as a result, applications do not require recompilation when these internal specifications change. Once again, the END statement marks the end of package body specifications.

NOTE

The name order_total was selected for both the package and package body names in this example, but these names need not be the same.

The next three statements work in conjunction to enable all users to access procedures defined in your package. First, any existing public synonym is dropped and subsequently re-created. The GRANT statement provides public access to the procedure.

NOTE

GRANT is a DDL statement that must be issued from a privileged account.

Page 260

At the end of the script is the statement SPOOL OFF name, which terminates output to the listing file. This is followed by a SELECT statement that displays any compilation errors to the terminal where the script was invoked. The name field in this SELECT statement identifies the name of the package being created and must be in uppercase letters .

TIP

Cross-referencing the line number from the error display to the identified line number in either the package or package body portion of the listing file will make debug sessions go much faster!

Once you create it, you can run the script using a SQL*Plus command as follows :

 sqlplus (username/password)  @ot 

The login specified must be a privileged account. After the @ is the name of the script that contains the package creation text. In this case, the script name is ot.sql, and because SQL is the default file extension for SQL scripts, it need not be included on the sqlplus command line.

Creating Package Subprograms

Creating subprograms within a package is the next step in developing a packaged procedure. You must decide which routines will be application-interface routines and which routines will be available only within the package. This determines where the subprogram specification will reside ”in the package or in the package body. The two types of subprograms in PL/SQL are procedures and functions.

Procedure Definition

To define a procedure, you must specify a routine name and the parameters to be passed in and out of the routine. In the order_total example, the following code defines the application-interface routine and resides in the package specification area:

 PROCEDURE   get_order_total (     in_order_num        IN NUMBER,     out status_code     OUT VARCHAR2,     out_msg             OUT VARCHAR2,     out_merch_total     OUT NUMBER,     out_shipping        IN OUT NUMBER,     out_taxes           IN OUT NUMBER,     out_grand_total     OUT NUMBER   ); 

Previous Table of Contents Next