• 5
• PL/SQL—SQL Procedural Language Extension
• Package Creation
• Packaged Procedures
• Package Creation
• Creating Package Subprograms
• Procedure Definition
• Function Definition
• Subprogram Parameter Modes
• Subprogram Specifications
• Default Procedure Parameters
• Stand-Alone Procedures
• Cursors
• Declaring Cursors
• Cursor Control
• Explicit Cursor Attributes
• Cursor Parameters
• Creating Cursor Packages
• Procedure Variables
• Variable Declaration and Assignment
• Local Variables
• Local Constants
• Global Variables
• DEFAULT Keyword
• Variable and Constant Attributes
• Scalar Data Types
• Boolean
• Date/Time
• Character
• Number
• Composite Data Types
• Array Processing
• Building Arrays
• Record Processing
• Processing Control
• Loop Control
• WHILE Loops
• Numeric FOR Loops
• Cursor FOR Loops
• Iterative Control
• Exception Handling
• User-Defined Exceptions
• System-Defined Exceptions
• Comments
• Stored Procedures
• Referencing Stored Procedures
• Stored Procedure States
• Overloading
• Commits
• Package STANDARD
• Referencing Internal Functions
• Internal Functions
• Character Functions
• Conversion Functions
• Date Functions
• Miscellaneous Functions
• Number Functions
• Additional Topics
• There are a few more areas of PL/SQL that I would like discuss for the sake of completeness. These topics have relevance to the general understanding of the PL/SQL language and should be reviewed.
• DECLARE Statement
• Naming Conventions
• Synonyms
• Scope of Reference
• Data Type Conversion
• Database Triggers
• More on Exceptions
• Reraising Exceptions
• Continuing Execution
• Retrying Transactions
• More on Control Structures
• EXIT Statement
• Sequential Control
• Summary

5

PL/SQL—SQL Procedural Language Extension

This chapter discusses constructs of PL/SQL that enable software developers to use this procedural language interface to the Oracle RDBMS. Constructs within PL/SQL are similar to those found in a 3GL and C and provide a flexible method to manipulate database information. PL/SQL is a mainstay of SQL*Forms, but writing database procedures outside forms gives you access to common database manipulation routines by other Oracle development tools while limiting the size of forms applications.

Package Creation

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 applications 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'

;

The first command in this script, SET ECHO ON, displays a listing of the package to the screen as it is being compiled. ECHO combined with the SPOOL name command creates a list file (order_total.lst) for debug purposes. This file will contain 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.

Any declarations in the package specification area that are incompatible with Version 1.0 of PL/SQL (such as unsupported data types) 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 but 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.

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 recreated. The GRANT statement provides "public" access to the procedure.

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

At the end of the script is the statement SPOOL OFF name that 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.

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. There are two types of subprograms in PL/SQL, 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

  );

The PROCEDURE statement begins the definition of the package application-interface routine get_order_total. Enclosed in parentheses are the parameters to be passed between the application and the order_total package. The semicolon marks the end of the procedure definition.

Modularity is the key to successful package design. If you limit the scope of subprograms, your code will be easier to design and debug, too!

Function Definition

Function definition is much the same as procedure definition as illustrated by the following example:

FUNCTION

  calc_ship_charges (

    in_merch_total    IN NUMBER

  ) RETURN NUMBER;

The FUNCTION statement begins the definition of the package function calc_ship_charges. Enclosed in parentheses are the parameters to be passed to the function for calculating shipping charges. The RETURN statement identifies the data type of the calculated value to be returned. The semicolon marks the end of the function definition.

Subprogram Parameter Modes

You can define parameters as IN (the default parameter mode), IN OUT, or OUT, depending on the nature of the information to be passed. The first parameter, in_order_num, is defined as IN, which designates it as a value being passed to the subprogram. Defining a parameter as IN prevents it from being assigned a value in the routine.

Parameters out_status_code, out_msg, out_merch_total, and out_grand_total from the procedure definition example are defined as OUT—values being returned to the caller. These parameters are uninitialized upon entry to the routine and are available for assignment of a value within the routine. Designating a parameter as OUT prevents it from being used in a subprogram expression.

Parameters out_shipping and out_taxes are defined as IN OUT, the last parameter mode. Parameters designated as IN OUT are initialized variables that are available for reassignment within the subprogram.

Subprogram Specifications

After defining a subprogram and its parameters, you develop code for the packaged procedure subprogram. The following example illustrates a few basic constructs to be aware of while coding a subprogram:

PROCEDURE

  init_line_items

IS

  (local variables)

BEGIN

    (subprogram logic)

    EXCEPTION

      (exception handling)

END init_line_items;

In this example, the PROCEDURE name is init_line_items with the local variables specified after the IS statement. The BEGIN statement is the actual start of the procedure (or function) where subprogram code is developed along with any subprogram exception handling. The procedure is finished with the END name statement.

The procedure parameter list must exactly match the specification parameter list for the procedure being developed. This includes data types and parameter modes included in the specification.

Default Procedure Parameters

To add flexibility to procedure calls, you might specify default parameter values in your procedure definition. In this manner, you can call the procedure with all, one, or none of the specified parameters. Defaults are supplied for parameters that are not passed. The following example illustrates a procedure definition using default parameters:

  PROCEDURE

    calc_ship_charges(

      merch_total NUMBER DEFAULT 5.95) IS

    ...

References to the calc_ship_charges procedure can include a merch_total or not. Calls without the merch_total parameter default to 5.95 as shown.

Stand-Alone Procedures

Procedures that are not part of a package are known as stand-alone because they independently defined. A good example of a stand-alone procedure is one written in a SQL*Forms application. These types of procedures are not available for reference from other Oracle tools. Another limitation of stand-alone procedures is that they are compiled at run time, which slows execution.

Cursors

PL/SQL uses cursors for all database information access statements. The language supports the use of both implicit and explicit cursors. Implicit cursors are those established for which explicit cursors are not declared. You must use explicit cursors or cursor FOR loops in all queries that return multiple rows.

Declaring Cursors

You define cursors in the variable definition area of PL/SQL subprograms using the CURSOR name IS statement, as shown in the following example:

CURSOR c_line_item IS

(sql statement)

The cursor SQL statement can be any valid query statement. Subsequent to cursor initialization, you are able to control cursor actions with the OPEN, FETCH, and CLOSE statements.

Cursor Control

To use a cursor for manipulating data, you must use the statement OPEN name to execute the query and identify all rows that meet the select criteria. Subsequent retrieval of rows is accomplished with the FETCH statement. Once all information is processed, the CLOSE statement terminates all activity associated with the opened cursor. The following is an example of cursor control:

OPEN c_line_item;

  ...

   FETCH c_line_item

    INTO li_info;

  ...

  (retrieved row processing)

  ...

CLOSE c_line_item;

The code opens the cursor c_line_item and processes the fetched rows. After it retrieves and processes all the information, the cursor closes. Retrieved row processing is typically controlled by iterative loops as discussed later in the chapter.

Explicit Cursor Attributes

There are four attributes associated with PL/SQL cursors.

• %NOTFOUND
  
  
• %FOUND
  
  
• %ROWCOUNT
  
  
• %ISOPEN
  
  

All cursor attributes evaluate to TRUE, FALSE, or NULL, depending on the situation. The attribute %NOTFOUND evaluates to FALSE when a row is fetched, TRUE if the last FETCH did not return a row, and NULL if the cursor SELECT returned no data. Attribute %FOUND is the logical opposite of %NOTFOUND with respect to TRUE and FALSE but still evaluates to NULL if the cursor FETCH returns no data.

You can use %ROWCOUNT to determine how many rows have been selected at any point in the FETCH. This attribute increments upon successful selection of a row. In addition, %ROWCOUNT is at zero when the cursor first opens.

The final attribute, %ISOPEN, is either TRUE or FALSE, depending on whether the associated cursor is open. Before the cursor opens and after the cursor closes, %ISOPEN is FALSE. Otherwise, it evaluates to TRUE.

Cursor Parameters

You can specify parameters for cursors in the same way you do for subprograms. The following example illustrates the syntax for declaring parameter cursors:

CURSOR c_line_item (order_num IN NUMBER) IS

  SELECT merch_gross, recipient_num

  FROM line_item

  WHERE order_num = g_order_num;

The parameter mode is always IN for cursor parameters, but the data type can be any valid data type. You can reference a cursor parameter, whose value is set when the cursor opens, only during the cursor's declared SQL query.

Flexibility within cursor parameters enables the developer to pass different numbers of parameters to a cursor by using the parameter default mechanism. This is illustrated in the following example:

CURSOR c_line_item

  (order_num INTEGER DEFAULT 100,

    line_num INTEGER DEFAULT 1) IS ...

By using the INTEGER DEFAULT declaration, you can pass all, one, or none of the parameters to this cursor depending on the logic flow of your code.

Creating Cursor Packages

A cursor package is similar to a procedure package in that you specify the cursor and its return attribute, %TYPE or %ROWTYPE, in the package specification area. You then specify the cursor "body" in the package body specification area. Packaging a cursor in this manner gives you the flexibility of changing the cursor body without having to recompile applications that reference the packaged procedure. The following is a cursor package example:

CREATE OR REPLACE PACKAGE order_total

AS

CURSOR c_line_item RETURN line_item.merch_gross%TYPE;

  ...

END order_total;

CREATE OR REPLACE PACKAGE BODY order_total

AS

  CURSOR c_line_item RETURN line_item.merch_gross%TYPE

    SELECT merch_gross

    FROM line_item

    WHERE order_num = g_order_num;

  ...

END order_total;

In this example, the RETURN variable is the same as the line_item.item_merch_gross column. You can use the %ROWTYPE attribute to specify a RETURN record that mirrors a row in a database table.

Procedure Variables

The most important feature of any language is how to define variables. Once you've defined the variables, PL/SQL enables you to use them in SQL statements as well as language statements. Definition of constants within PL/SQL follow the same rules. Also, you can define variables and constants as local to one subprogram or global to the entire package you are creating.

You must declare variables and constants before referencing them in any other statement.

Variable Declaration and Assignment

Any PL/SQL or SQL data type is valid for variable definitions. The most commonly used data types are VARCHAR2, DATE, NUMBER (SQL data types), BOOLEAN, and BINARY_INTEGER (PL/SQL data types). PL/SQL scalar and composite data types are discussed in more detail later in this chapter.

Local Variables

Assume you want to declare two local variables named merch_gross and recip_count. The first, merch_gross, is to hold a ten-digit, floating-point number rounded to two decimal places; recip_count will hold an integer counter. Declare these variables as follows:

merch_gross      NUMBER;

recip_count        BINARY_INTEGER;

You can also declare merch_gross in this example as NUMBER(10,2) to explicitly show total digits and rounding. However, if it's related to a database field, a declaration of this type must change if the database definition changes.

You can use two methods to assign values to variables. The first is using an assignment operator as follows:

merch_gross := 10.50;

The second method is to use a SQL SELECT or FETCH statement that assigns a database value as follows:

SELECT merch_gross

INTO merch_gross

FROM line_item

WHERE order_num = g_order_num;

Local Constants

Constant declaration is similar to variable declaration except that the CONSTANT keyword must follow the variable name. You must immediately assign a value to the CONSTANT.

tax_rate CONSTANT NUMBER := 0.03;

Global Variables

Global variables are defined in the same manner as local variables, but they are defined outside of all procedure definitions. Suppose you want to define variables g_order_num and g_recip_counter to be available to all package subprograms. The following is an example of the syntax:

CREATE OR REPLACE PACKAGE BODY

     order_total

AS

     ...

g_order_num        NUMBER;

g_recip_counter    BINARY_INTEGER;

     ...

PROCEDURE

     ...

Notice that these global variables are defined in the package body specification area so as not to be "seen" by applications that call the order_total packaged procedure.

If you use variable names that are the same as database column names, results are unpredictable when performing any database operations such as SELECT or UPDATE with the variables.

DEFAULT Keyword

The DEFAULT keyword enables you to initialize variables without using the assignment operator as in the following example:

merch_gross    NUMBER  DEFAULT  10.50;

You can also use the DEFAULT keyword to initialize a subprogram's cursor parameters and fields in user-defined records.

Variable and Constant Attributes

The two attributes of PL/SQL variables and constants are %TYPE and %ROWTYPE. The %TYPE attribute enables you to declare variables similar to database columns without knowing the data type of the column. You can define merch_gross from the previous example as follows:

merch_gross    line_item.merch_gross%TYPE;

Defining a variable in this manner enables you to put database changes in effect on the next compilation of a PL/SQL procedure without changing the code.

The %ROWTYPE attribute enables you to represent a row in a table with a record type that masks the database columns. Consider the sample database information in Table 5.1.

Table 5.1. Sample of data in table LINE_ITEM.

Column Name

Data

You can define a cursor inside your procedure (see "Declaring Cursors" earlier in the chapter) to pull information from the LINE_ITEM table. Along with the cursor, define a ROWTYPE variable to store the fields in this row as follows:

CURSOR c_line_item IS

SELECT merch_gross, recipient_num

FROM line_item

WHERE order_num = g_ordnum;

li_info  c_line_item%ROWTYPE;

To retrieve the data, issue a FETCH.

FETCH c_line_item

INTO li_info;

After the FETCH, use dot notation to access the information pulled from the database.

g_order_merch_total := g_order_merch_total + li_info.merch_gross;

Scalar Data Types

PL/SQL supports a wide range of scalar data types for defining variables and constants. Unlike composite data types, scalar data types have no accessible components. These data types fall into one of the following categories:

• Boolean
  
  
• Date/time
  
  
• Character
  
  
• Number
  
  

Now, take a closer look at the data types in each category.

Boolean

The BOOLEAN data type, which takes no parameters, is used to store a binary value, TRUE or FALSE. This data type can also store the non-value NULL. You cannot insert or retrieve data from an Oracle database using this data type.

Date/Time

The data type DATE, which takes no parameters, is used to store date values. These DATE values include time when stored in a database column. Dates can range from 1/1/4712 B.C. to 12/31/4712 A.D. Defaults for the DATE data type are as follows:

• Date: first day of current month
  
  
• Time: midnight
  
  

Character

Character data types include CHAR, VARCHAR2, LONG, RAW, and LONG RAW. CHAR is for fixed-length character data, and VARCHAR2 stores variable-length character data. LONG stores variable-length character strings; RAW and LONG RAW store binary data or byte strings. The CHAR, VARCHAR2, and RAW data types take an optional parameter for specifying length.

datatype(max_len)

This length parameter, max_len, must be an integer literal, not a constant or variable. Table 5.2 shows maximum lengths and database column widths of character data types.

Table 5.2. Character data type maximum lengths and database column widths.

Data Type

Maximum Length

Maximum Database Column Width

From this table, you can see the constraint on inserting CHAR, VARCHAR2, and RAW data into database columns of the same type. The limit is the column width. However, you can insert LONG and LONG RAW data of any length into similar columns because the column width is much greater.

Number

There are two data types in the number data type category: BINARY_INTEGER and NUMBER. BINARY_INTEGER stores signed integers with a range of -2³¹ to 2³¹-1. The most common use for this data type is an index for PL/SQL tables.

Storage for fixed or floating-point numbers of any size is available using the NUMBER data type. For floating-point numbers, you can specify precision and scale in the following format:

NUMBER(10,2)

A variable declared in this manner has a maximum of ten digits, and rounding occurs to two decimal places. The precision default is the maximum integer supported by your system, and 0 is the default for scale. The range for precision is 1 to 38 whereas the scale range is -84 to 127.

Composite Data Types

The two composite data types in PL/SQL are TABLE and RECORD. The TABLE data type enables the user to define a PL/SQL table to be used for array processing. The RECORD data type enables the user to go beyond the %ROWTYPE variable attribute; with it, you specify user-defined fields and field data types.

Array Processing

The TABLE composite data type provides the developer a mechanism for array processing. Although it's limited to one column of information per PL/SQL table, you can store any number of rows for that column. The word from Oracle is that future versions of PL/SQL will provide more flexibility in the use of tables.

In the order_total example, define a PL/SQL table named g_recip_list (the information will be used globally). The following is an illustration of this concept:

TYPE RecipientTabTyp IS TABLE OF NUMBER(22)

  INDEX BY BINARY_INTEGER;

...

g_recip_list           RecipientTabTyp;

To initialize an array, you must first define an array name or TYPE, which in this example is RecipientTabTyp. This TABLE column is defined as NUMBER with a maximum of 22 digits. You can define the column as any valid PL/SQL data type; however, the primary key, or INDEX, must be of type BINARY_INTEGER. After defining the array structure, you can make reference for variable definition as shown with g_recip_list defined as an array of TYPE RecipientTabTyp.

Building Arrays

Arrays are available as information stores subsequent to initialization of the array. To store information in the array g_recip_list that was defined in the last example, you simply reference the array with a numeric value. This is shown in the following example:

g_recip_list(j) := g_recipient_num(i)

In this example, i and j are counters with values 1. . .n. Once information is stored in an array, you can access it, also with numeric values, as shown in the example. In this case, rows of g_recipient_num are referenced for storage in g_recip_list.

Referencing an uninitialized row in a PL/SQL array causes a NO_DATA_FOUND error (see the section "Exception Handling" later in this chapter).

Record Processing

The RECORD composite data type provides the developer a mechanism for record processing as described previously. Although you cannot initialize TABLEs at the time of declaration, you can with RECORDs, as illustrated in the following example:

TYPE LineRecTyp IS RECORD

  (merch_gross  NUMBER := 0,

    recip_num    NUMBER := 0 );

  ...

li_info LineRecTyp;

Defining a RECORD of TYPE LineRecTyp allows declarations such as li_info of that TYPE as shown. You can use this method of RECORD declaration in place of the li_info declaration in the previous %ROWTYPE example. As with %ROWTYPE, references to RECORD information is accomplished with dot notation.

g_order_merch_total := g_order_merch_total + li_info.merch_gross;

You can use one of three methods to assign values to records. First, you can assign a value to a record field as you would assign any variable.

li_info.merch_gross := 10.50;

A second method is to assign all fields at once by using two records that are declared with the same data type. Assume a second LineRecTyp is defined as new_li_info.

new_li_info := li_info;

This statement assigns all fields of new_li_info the values from the same fields of li_info.

You cannot assign records of different types to each other.

A third method of assigning values to fields of a record is through SQL SELECT or FETCH statements.

OPEN c_line_item;

  ...

    FETCH c_line_item

    INTO li_info;

In this case, all fields of li_info are assigned values from the information retrieved by the FETCH of cursor c_line_item.

Processing Control

Every procedural language has control structures that provide processing of information in a logical manner by controlling the flow of information. Available structures within PL/SQL include IF-THEN-ELSE, LOOP, and EXIT-WHEN. These structures provide flexibility in manipulating database information.

Loop Control

Use of the LOOP statement provides iterative processing based on logical choices. The basic construct for PL/SQL LOOPs is shown in the following example:

<<loop_name>>

LOOP

  (repetitive processing)

END LOOP loop_name;

To break out of a loop such as this, you must issue an EXIT or GOTO statement based on some processing condition. If you raise a user-defined exception, the LOOP also terminates. Now, examine three types of PL/SQL loops that expressly define LOOP termination conditions.

You can name a loop as shown in the example by using a label such as <<loop_name>> just before the LOOP statement. Although it's not required, labeling does enable you to keep better track of nested loops.

WHILE Loops

The WHILE loop checks the status of any PL/SQL expression that evaluates to TRUE, FALSE, or NULL at the start of each processing cycle. The following is an example of the use of WHILE loops:

WHILE (expression) LOOP

  (loop processing)

END LOOP;

As stated, the program evaluates the expression at the start of each loop cycle. The program performs the loop processing if the expression evaluates to TRUE. A FALSE or NULL evaluation terminates the loop. Iterations through the loop are exclusively determined by the evaluation of the expression.

Numeric FOR Loops

You can control loop iterations with the use of numeric FOR loops. This mechanism enables the developer to establish a range of integers for which the loop will cycle. The following example from the order_total package illustrates numeric FOR loops:

<<recip_list>>

    FOR i in 1..g_line_counter LOOP

      (loop processing)

    END LOOP recip_list;

In this example, loop processing cycles over the range of integers 1 through the value of g_line_counter. The value of the loop index i is checked at the start of the loop and incremented at the end of the loop. When i is one greater than g_line_counter, the loop terminates.

Cursor FOR Loops

Cursor FOR loops combine cursor control and conditional control for manipulation of database information. The loop index, cursor OPEN, cursor FETCH, and cursor CLOSE are all implicit when using cursor FOR loops. Consider the following example:

CURSOR c_line_item IS

(sql statement)

BEGIN

  FOR li_info IN c_line_item LOOP

    (retrieved record processing)

  END LOOP;

END;

As shown, the program explicitly declares the c_line_item cursor before its reference in the FOR loop. When the program enters the FOR loop, the code implicitly opens c_line_item and implicitly creates the li_info record as if the following declaration were made:

li_info c_line_item%ROWTYPE;

Once inside the loop, the program can reference the fields of the li_info record that are assigned values by the implicit FETCH inside the FOR loop. Fields of li_info mirror the row retrieved by the c_line_item cursor.

When data is exhausted for the FETCH, c_line_item is implicitly closed.

You cannot reference the information contained in li_info outside of the cursor FOR loop.

Iterative Control

The IF-THEN-ELSE structure provides alternative processing paths that depend on certain conditions. For example, consider merchandise orders with multiple-line items where a list of recipients is built. Using conditional and iterative control to build the recipient list, the code is as follows:

PROCEDURE

  init_recip_list

IS

  recipient_num  NUMBER;

  i                       BINARY_INTEGER;

  j                       BINARY_INTEGER := 1;

  k                      BINARY_INTEGER;

  BEGIN

    g_out_msg := 'init_recip_list';

    <<recip_list>>

    FOR i in 1..g_line_counter LOOP

      IF i = 1 THEN

        g_recip_list(j) := g_recipient_num(i);

        j := j + 1;

        g_recip_list(j) := 0;

      ELSE

        FOR k in 1..j LOOP

          IF g_recipient_num(i) = g_recip_list(k) THEN

            exit;

          ELSIF k = j THEN

            g_recip_list(j) := g_recipient_num(i);

            j := j + 1;

            g_recip_list(j) := 0;

          end IF;

        end LOOP;

      end IF;

    end LOOP recip_list;

  END;

In the order_total example, the subprogram init_recip_list builds a list of unique recipient numbers for calculating additional shipping charges. There is a controlling FOR loop that cycles through each recipient number found on a particular order. The g_recip_list array is initialized with the first recipient number, and subsequent numbers are checked against all unique numbers in g_recip_list until a unique list of all recipients is compiled.

Also illustrated in this example is the IF-THEN-ELSE extension ELSIF. This statement provides further conditional control with additional constraint checks within the IF-THEN-ELSE structure. Use of ELSIF also requires a THEN statement in executing logic control.

Another example of iterative control is the use of the EXIT-WHEN statement that allows completion of a LOOP once certain conditions are met. Consider the example of exiting a cursor fetch loop:

open c_line_item;

  loop

    fetch c_line_item

    into li_info;

    EXIT WHEN (c_line_item%NOTFOUND) or (c_line_item%NOTFOUND is NULL);

In this example, the LOOP is terminated when no more data is found to satisfy the select statement of cursor c_line_item.

Use of %NOTFOUND or %FOUND can cause infinite loops if you do not check for these attributes evaluating to NULL on an EXIT-WHEN logical check.

Exception Handling

PL/SQL exception handling is a mechanism for dealing with run-time errors encountered during procedure execution. Use of this mechanism enables execution to continue if the error is not severe enough to cause procedure termination. The decision to enable a procedure to continue after an error condition is one you have to make in development as you consider possible errors that could arise.

You must define the exception handler within a subprogram specification. Errors cause the program to raise an exception with a transfer of control to the exception-handler block. After the exception handler executes, control returns to the block in which the handler was defined. If there are no more executable statements in the block, control returns to the caller.

User-Defined Exceptions

PL/SQL enables the user to define exception handlers in the declarations area of subprogram specifications. You accomplish this by naming an exception as in the following example:

ot_failure            EXCEPTION;

In this case, the exception name is ot_failure. Code associated with this handler is written in the EXCEPTION specification area as follows:

EXCEPTION

      when OT_FAILURE then

        out_status_code := g_out_status_code;

        out_msg         := g_out_msg;

This exception is defined in the order_total example to capture status and associated data for any NO_DATA_FOUND exceptions encountered in a subprogram. The following is an example of a subprogram exception:

EXCEPTION

      when NO_DATA_FOUND then

        g_out_status_code := 'FAIL';

        RAISE ot_failure;

Within this exception is the RAISE statement that transfers control back to the ot_failure exception handler. This technique of raising the exception is used to invoke all user-defined exceptions.

System-Defined Exceptions

Exceptions internal to PL/SQL are raised automatically upon error. NO_DATA_FOUND from the previous example is a system-defined exception. Table 5.3 is a complete list of internal exceptions.

Table 5.3. PL/SQL internal exceptions.

Exception Name

Oracle Error

In addition to this list of exceptions, there is a catch-all exception named OTHERS that traps all errors for which specific error handling has not been established. This exception is illustrated in the following example:

when OTHERS then

        out_status_code := 'FAIL';

        out_msg := g_out_msg || ' ' || SUBSTR(SQLERRM, 1, 60);

This technique is used in the order_total sample procedure to trap all procedure errors other than NO_DATA_FOUND. The information passed back to the caller in out_msg is the subprogram name contained in g_out_msg concatenated with the first 60 characters returned from the SQLERRM function by the SUBSTR function.

Both SQLERRM and SUBSTR are internal PL/SQL functions. You can find a complete list of internal functions later in this chapter.

SQLERRM only returns a valid message when called inside an exception handler unless an argument is passed to the function that is a valid SQL error number. The Oracle error code is the first part of the message returned from SQLERRM. Next is the text associated with that Oracle error code.

In this manner, all errors encountered during procedure execution are trapped and passed back to the application for debug purposes. The following is a sample return error from the order_total procedure:

FAIL: init_line_items ORA-01001: invalid cursor

This error message (formatted by the application) reveals an illegal cursor operation in the subprogram init_line_items. The portion of the message returned from SQLERRM begins with the ORA-01001 SQL error code. Another error message is illustrated in the following example:

FAIL: calc_ship_charges

In this case, the subprogram calc_ship_charges had a NO_DATA_FOUND error. This is determined by the fact that no SQL error messages are concatenated with the message text.

Comments

Although some people think commenting code is unnecessary, there are two methods you can use to place comments within your PL/SQL procedures. The first is for commenting single lines, and the syntax is shown in the following example:

--***************  CREATE PACKAGE ORDER_TOTALING  ***************

A double dash at the start of the line marks the line as a comment. The second method is used to place a sequence of comment statements in a PL/SQL package.

/* The following code generates a list of unique recipient

    numbers from all recipient numbers for a particular order */

A comment block such as this begins with the /* and ends with the */. You can place single-line and multiple-line comments in any portion of PL/SQL code.

PL/SQL blocks that are dynamically compiled in Oracle Precompiler applications do not support use of single-line comments.

Stored Procedures

You can store PL/SQL code in the Oracle database with the RDBMS Procedural Database Extension. Advantages of using stored procedures include easier maintenance, decreased application size, increased execution speed, and greater memory savings, to name a few. With this in mind, explore the various techniques for accessing stored procedures in the following sections.

Referencing Stored Procedures

Another big advantage to using stored procedures is the capability to reference the procedure from many different Oracle applications. You can make reference to stored procedures with other stored procedures, database triggers, applications built with Oracle Precompilers, or Oracle tools such as SQL*Forms. The following example calls the order_total procedure from another procedure:

order_total.get_order_total (order_num,

                                   status_code,

                                   message,

                                   merch_gross,

                                   shipping,

                                   taxes,

                                   grand_total);

The following example shows the same order_total procedure referenced from PRO*C, an Oracle Precompiler application.

EXEC SQL

  BEGIN

    order_total.get_order_total ( :order_num,

                                               :status_code,

                                                :message,

                                                :merch_gross,

                                                :shipping,

                                                :taxes,

                                                :grand_total);

  END;

END-EXEC;

All parameters in this example to the order_total procedure are Oracle bind variables that you must declare before the reference to the package. The final example illustrates a call to the order_total package from a SQL*Forms application.

BEGIN

  ...

  order_total.get_order_total ( order_num,

                                               status_code,

                                               message,

                                               merch_gross,

                                               shipping,

                                               taxes,

                                               grand_total);

  ...

END;

Once again, you must declare all variables passed as parameters before calling the procedure.

Calling stored procedures with COMMIT, ROLLBACK, or SAVEPOINT statements from SQL*Forms is prohibited and is discussed later in this chapter.

Stored Procedure States

After compilation, a stored procedure exists in either a valid or invalid state. If you haven't made any changes to the procedure, it is considered valid and may be referenced. If any subprogram or object referenced within a procedure changes, its state becomes invalid. Only procedures in a valid state are available for reference.

Referencing a procedure that is invalid causes Oracle to recompile any and all objects called by the referenced procedure. If the recompilation does not succeed, Oracle returns a run-time error to the caller, and the procedure remains in an invalid state. Otherwise, Oracle recompiles the referenced procedure, and if the recompilation is successful, execution continues.

Stored procedures are located in the Oracle SGA after compilation. If the SGA is too small for the user base, the procedure might be swapped out and become invalid with no indication to the caller. The first reference to the procedure after it is swapped out causes a recompilation, returning it to a valid state.

Overloading

The concept of overloading in PL/SQL relates to the idea that you can define procedures and functions with the same name. PL/SQL does not look only at the referenced name, however, to resolve a procedure or function call. The count and data types of formal parameters are also considered.

PL/SQL also attempts to resolve any procedure or function calls in locally defined packages before looking at globally defined packages or internal functions. To further ensure calling the proper procedure, you can use the dot notation as illustrated by previous examples on application references to stored procedures. Prefacing a procedure or function name with the package name fully qualifies any procedure or function reference.

Commits

The COMMIT statement is available to PL/SQL procedures unless you are calling the procedure from a SQL*Forms application. To enable commits within a procedure called by a SQL*Forms application, you must issue the DDL statement ALTER SESSION ENABLE COMMIT IN PROCEDURE before you invoke the PL/SQL object. Because you cannot issue this command from SQL*Forms, you must create a user exit from which you can issue the ALTER SESSION statement and subsequently call the procedure. The following is an example of calling the order_total procedure from SQL*Forms through a user exit:

user_exit('order_totl');

In this case, the order_totl routine of the SQL*Forms user exit references the order_total packaged procedure.

Issuing a COMMIT from a PL/SQL procedure that is called from SQL*Forms attempts to commit any changes from the forms application as well.

Package STANDARD

PL/SQL provides various tools in a package named STANDARD for use by developers. These tools include internal functions and internal exceptions. I previously discussed exception handling and two internal functions, SQLCODE and SQLERRM, that provide information for exception reporting and are only valid in exception handlers.

Referencing Internal Functions

Internal PL/SQL functions exemplify the concept of overloading with respect to naming procedures and functions. Remember that PL/SQL resolves a procedure or function call by matching the number and data types of formal parameters in the reference and not just by reference name. Consider the two internal functions named TO_NUMBER in the following example:

function TO_NUMBER (str CHAR [, fmt VARCHAR2, [, nlsparms] ]) return NUMBER

function TO_NUMBER (str VARCHAR2 [, fmt VARCHAR2 [, nlsparms] ]) return NUMBER

Both functions are named TO_NUMBER, but the data type of the first parameter is CHAR in the first definition and VARCHAR2 in the second. Optional parameters are the same in both cases. PL/SQL resolves a call to the TO_NUMBER function by looking at the data type of the first parameter.

You might also have a user-defined procedure or function named TO_NUMBER. In this case, the local definition takes precedence over the internal function definition. You can still access the internal function, however, by using the dot notation as follows:

STANDARD.TO_NUMBER ...

As shown, prefacing the TO_NUMBER function call with the name of the PL/SQL package STANDARD references the internal function.

Internal Functions

The function TO_NUMBER is one example of a PL/SQL internal function. Table 5.4 shows a complete list of PL/SQL internal function categories along with default return values.

Table 5.4. Internal function categories and common return values.

Category

Common Return Value

Character Functions

Although most character functions return a VARCHAR2, some functions return other values. Table 5.5 lists available character functions along with a brief description, argument list, and return value if other than the most likely return value for the set of functions. Optional arguments are enclosed in square brackets. All internal character functions take the following form:

function ASCII (char VARCHAR2) return VARCHAR2

Table 5.5. Character functions.

Function

Description

Argument(s)

Return Value

Conversion Functions

Table 5.6 lists available conversion functions along with a brief description, argument list, and return value. Optional arguments are enclosed in square brackets. All internal conversion functions are of the following form:

function CHARTOROWID (str VARCHAR2) return ROWID

Table 5.6. Conversion functions.

Function

Description

Argument(s)

Return Value

Date Functions

All date functions return a DATE value unless otherwise specified in Table 5.7, which lists available date functions along with a brief description, argument list, and return value. Optional arguments are enclosed in square brackets. All internal date functions are of the following form:

function ADD_MONTHS (dte DATE, num NUMBER) return DATE

Table 5.7. Date functions.

Function

Description

Argument(s)

Return Value

Miscellaneous Functions

Table 5.8 lists miscellaneous functions along with a brief description, argument list, and return value. Optional arguments are enclosed in square brackets.

Table 5.8. Miscellaneous functions.

Function

Description

Argument(s)

Return Value

Number Functions

All number functions return a NUMBER value unless otherwise specified in Table 5.9, which lists available number functions along with a brief description, argument list, and return value. Optional arguments are enclosed in square brackets. All internal number functions are of the following form:

function ABS (n NUMBER) return NUMBER

Table 5.9. Number functions.

Function

Description

Argument(s)

Additional Topics

There are a few more areas of PL/SQL that I would like discuss for the sake of completeness. These topics have relevance to the general understanding of the PL/SQL language and should be reviewed.

DECLARE Statement

Use of the DECLARE statement is limited to the creation of sub-blocks within PL/SQL blocks, as shown in the following example:

BEGIN

  ...

  <<inner>>

  DECLARE

    ...

  BEGIN

    ...

  END inner;

  ...

END;

This code uses DECLARE to declare cursors, variables, and constants local to the sub-block labeled inner.

Naming Conventions

PL/SQL enables you to reference all defined objects (such as variables, cursors, packages, and so on) using simple references, qualified references, remote references, or a combination of qualified and remote references. Case is irrelevant in all object references. A simple reference to the order_total package interface takes the following form:

get_order_total( ... );

A qualified reference to the same package looks as follows:

order_total.get_order_total( ... );

A remote reference to this package is shown in the following example:

get_order_total@concepts( ... );

Finally, using a qualified and remote reference, you reference the order_total package as follows:

order_total.get_order_total@concepts( ... );

The first two instances reference the order_total procedure on the local machine. The last two instances show a remote access to the order_total procedure using the concepts database link.

Synonyms

To further simplify the reference to a procedure, you can use a synonym. You cannot use synonyms to reference PL/SQL objects contained in subprograms or packages, however. An example of synonym creation is provided in the section "Package Creation," which shows a sample script to build the order_total packaged procedure.

Scope of Reference

Another naming convention worth mentioning is scope of reference. This refers to the range over which you can reference a PL/SQL identifier (such as a variable or subprogram). In simple terms, the hierarchy of scope is block, local, global, and application, with respect to the range of reference. The following example illustrates this point.

CREATE OR REPLACE PACKAGE order_total

AS

  PROCEDURE

    get_order_total ( ... );

END order_total

CREATE OR REPLACE PACKAGE BODY order_total

AS

  ot_failure   EXCEPTION;

  ...

  PROCEDURE

    init_line_items

  IS

    i     BINARY INTEGER  :=  0;

      ...

    BEGIN

      ...

      <<inner>>

      DECLARE

        j   BINARY_INTEGER  :=  0;

      BEGIN

        j = i;

        ...

      EXCEPTION

      ...

      raise ot_failure;

      END inner;

      ...

    END;

END order_total;

In this example, the scope of reference for variable j is the inner sub-block where it is defined. Variable i, however, is defined local to the init_line_items procedure. You can reference it in the inner sub-block as shown. The defined exception, ot_failure, is global to the package body and may be referenced by all subprograms but not by the caller. Finally, the get_order_total interface routine and associated variables are available at the application level and throughout the package.

A final note on scope of reference: You can define local identifiers in a sub-block that use the same name as global identifiers. You must then make reference to the global identifier with a qualified name that can be an enclosing block or subprogram name. You then make the qualified reference using the dot notation.

Data Type Conversion

PL/SQL supports both explicit and implicit data type conversions of specified values. Explicit conversions work through the use of an internal function, such as TO_NUMBER described previously. Implicit conversions happen at compile time where one data type is supplied and a different data type is expected. This PL/SQL feature enables you to rely on the compiler instead of using explicit conversion routines. Consider the following SQL statement:

SELECT SUM(grand_total)FROM order

WHERE order_date < '10-SEP-95';

In this case, the order_date column is stored as data type DATE, and it is being compared to '10-SEP-95', which is a literal CHAR value. PL/SQL does an implicit conversion on this literal to data type DATE when the procedure containing this SQL select is compiled.

Database Triggers

Another common use of PL/SQL procedures is the creation of database triggers. These triggers are packaged procedures that act like SQL*Forms triggers in that they "fire" automatically when a database table meets certain criteria as a result of a SQL operation. As such, database triggers are not explicitly referenced by other procedures or applications.

You can link up to 12 database triggers to a given table.

There are three distinct pieces to consider when building a database trigger. First is the event that causes the trigger to fire. This firing leads to the action to be taken, which you can think of as the database trigger code. Finally, you must consider any optional constraints you might want to place on your trigger. Take a closer look at how database triggers are built.

Like packaged procedures, all database triggers follow a standard form in development. The following is an example of creating a database trigger:

CREATE TRIGGER name

  (trigger firing event)

  ...

  (optional trigger constraint)

BEGIN

  (trigger action)

END;

As shown by this example, all database triggers start with the CREATE TRIGGER name statement that is the trigger entry point for the named trigger. The trigger firing event code begins with a keyword to specify when the trigger is to fire. This section of code identifies the SQL operation that passes

control to the trigger action code. Any constraints on the SQL operation are identified in the optional trigger constraint specification area.

If you do not own the table where you are creating the database trigger, you must have the ALTER or ALTER ANY TABLE privilege on the table. Another privilege you need is CREATE TRIGGER, regardless of the table where you are creating the database trigger.

The following example illustrates the creation and use of a database trigger:

CREATE TRIGGER check_order_total

  AFTER UPDATE OF order_total ON order

  FOR EACH ROW

  WHEN (new.status = 'NEW')

BEGIN

  IF :new.order_total = 0 THEN

    INSERT INTO order_log

       values(:new.order);

    UPDATE order SET :new.status = 'ERR';

  END IF;

END;

This example shows that the trigger event specification begins with a keyword, AFTER in this case, that determines when the trigger should fire. The FOR EACH ROW statement has the trigger fire once for each row instead of the default of once per table. A constraint on firing is that the status of the updated order must be 'NEW'. The trigger action is to INSERT a row into the order_log table and UPDATE the order status to 'ERR'.

A correlation name such as :new refers to newly updated column values. You can also reference :old values of a changing column. As shown, you do not use the colon in the optional trigger constraint code. You can find more information on database triggers in Chapter 20, "Enforcing Integrity."

You cannot use COMMIT, ROLLBACK, or SAVEPOINT statements in database triggers.

More on Exceptions

I previously discussed the use of exception handling within PL/SQL, but there are three more areas to consider. These areas are reraising exceptions, continuing procedure execution after an exception, and retrying a transaction.

Reraising Exceptions

I already discussed using the RAISE statement to raise exceptions within your code, but you can also use RAISE to reraise an exception. Consider the following example:

CREATE OR REPLACE PACKAGE order_total

AS

  ot_failure      EXCEPTION;

  ...

  BEGIN

    ...

    BEGIN

      ...

      if g_recip_counter > max_lines then

        RAISE ot_failure;

      end if;

    EXCEPTION

      when OT_FAILURE then

        ...

      RAISE;

    END;

  ...

  EXCEPTION

    when OT_FAILURE then

      ...

  END;

END order_total;

In this example, the exception is raised in a sub-block with an ot_failure exception handler defined. After processing this error inside the handler, the exception is reraised for further processing in the main procedure block. This is accomplished with another ot_failure exception handler.

Continuing Execution

After an exception is raised in a PL/SQL sub-block, it is possible to continue execution before exiting the driving block. Place executable code in the driving block after the exception handler. The following is an illustration of this technique:

<<outer>>

BEGIN

  ...

  <<inner>>

  BEGIN

    ...

    if g_recip_counter > max_lines then

      raise ot_failure;

    end if;

  EXCEPTION

    when OT_FAILURE then

      ...

  END inner;

  UPDATE order SET status = 'SU';

  INSERT INTO suspense_queue

  VALUES (order,g_out_msg);

EXCEPTION

  ...

END outer;

This example shows that the exception was handled in the inner sub-block at which time control was passed to the outer driving block. As the inner exception handler ended, execution resumed with the UPDATE statement in the outer block. In this manner, execution of the procedure can continue after an otherwise fatal error is encountered.

Retrying Transactions

Another method of continuing procedure execution after an exception is raised is known as retrying a transaction. The technique is similar to continuing execution after a raised exception in that the transaction to be retried must exist in a sub-block. Using iterative loop control, you can repeat a transaction as often as you like after an exception is raised. The following example illustrates the use of this technique:

BEGIN

  ...

  FOR i in 1..10 LOOP

    ...

    BEGIN

      SAVEPOINT update_order

        (SQL transactions)

      COMMIT;

      EXIT;

    EXCEPTION

      WHEN ... THEN

        ROLLBACK to update_order

           (fix data problems)

      ...

    END;

  END LOOP;

END;

Under the control of the FOR loop, the SQL transactions can be tried a total of ten times before procedure execution is terminated. The SAVEPOINT update_order is the point of rollback for failed transactions. If an error is encountered during the SQL transactions phase of this sub-block, control transfers to the exception handler, which tries to resolve the data problems. After execution of the error handler, control transfers to the FOR loop for another pass.

More on Control Structures

Two additional topics for discussion of the subject of PL/SQL control structures are the EXIT statement and sequential control statements. Both control structures are rarely used, but you might encounter a situation that requires the functionality.

EXIT Statement

Earlier in the chapter, I mentioned using the EXIT statement as a means to break out of a basic FOR loop. You can also use the EXIT statement in retrying transactions after raised exceptions. In this respect, EXIT provides a mechanism for unconditional transfer of control from one point of code to another and is used selectively, if at all.

Sequential Control

Using sequential control in PL/SQL is not an essential element in successful code development. However, the technique is worth mentioning for a comprehensive view of the language. Two statements are available for sequential control: GOTO and NULL.

GOTO is an unconditional branch statement that transfers control to a label defined within the scope of the branch logic. The label must precede an executable statement or define a PL/SQL block, as shown in the following example:

<<count_lines>>

for i in 1..g_line_counter LOOP

  ...

  if i>max_lines then

    GOTO clean_up;

  end if;

  ...

end LOOP init_lines;

<<clean_up>>

g_recip_counter = i-1;

...

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 have been considered. NULL is considered an executable statement.

if g_recip_counter > max_lines then

  g_recip_counter = max_lines;

else

  NULL;

end if;

This example uses NULL to show that there is nothing to do if g_recip_counter is within range of max_lines. Obviously, you can end this code without the ELSE clause, but using the NULL shows that other options were considered.

Summary

In this chapter, I discussed PL/SQL procedures, subprograms, and the structures associated with this procedural language extension to the Oracle database. You also saw a sample SQL script for building a packaged procedure. In the course of this chapter, I began to develop 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 procedure covers the basic elements of the PL/SQL language and is presented now in its entirety. I discuss additional topics of interest to PL/SQL developers at the end of this sample package code.