Section 1.9. Procedures, Functions, and Packages

1.9. Procedures, Functions, and Packages

PL/SQL offers the following structures to modularize your code in different ways:

Procedure: A program that performs one or more actions and is called as an executable PL/SQL statement. You can pass information into and out of a procedure through its parameter list.
Function: A program that returns a single value and is used just like a PL/SQL expression. You can pass information into a function through its parameter list.
Package: A named collection of procedures, functions, types, and variables. A package is not really a module (it's more of a meta-module), but it is so closely related that I mention it here.
Database trigger: A set of commands that are triggered to execute (e.g., log in, modify a row in a table, execute a DDL statement) when an event occurs in the database.
Object type or instance of an object type.: Oracle's version of (or attempt to emulate) an object-oriented class. Object types encapsulate state and behavior, combining data (like a relational table) with rules (procedures and functions that operate on that data).

In this section, we'll describe procedures, functions, and packages . Triggers are described in a later section. Object types are not covered in this chapter; few developers and even fewer database administrators use Oracle's version of object-oriented language features.

1.9.1. Procedures

A procedure is a module that performs one or more actions. Because a procedure call is a standalone executable statement in PL/SQL, a PL/SQL block could consist of nothing more than a single call to a procedure. Procedures are key building blocks of modular code, allowing you to both consolidate and reuse your program logic.

1.9.1.1. Structure of a procedure

The general format of a PL/SQL procedure is as follows:

     PROCEDURE [schema.]name [( parameter [, parameter ...] ) ]        [AUTHID DEFINER | CURRENT_USER]     IS        [declaration statements]     BEGIN        executable statements     [ EXCEPTION          exception handler statements]     END [name];

where each element is used in the following ways:

schema: Optional name of the schema that will own this procedure. The default is the current user. If different from the current user, that user will need privileges to create a procedure in another schema.
name: Name of the procedure, which comes directly after the keyword PROCEDURE.
parameters: Optional list of parameters that you define to both pass information into the procedure and send information out of the procedure back to the calling program.
AUTHID clause: Determines whether the procedure will execute under the authority of the definer (owner) of the procedure or under the authority of the current user. The former is known as the definer rights model , the latter as the invoker rights model .
declaration statements: Declarations of local identifiers for that procedure. If you do not have any declarations, there will be no statements between the IS and BEGIN statements.
executable statements: Statements that the procedure executes when it is called. You must have at least one executable statement after the BEGIN and before the END or EXCEPTION keywords.
exception handler statements: Optional exception handlers for the procedure. If you do not explicitly handle any exceptions, then you can leave out the EXCEPTION keyword and simply terminate the execution section with the END keyword.

1.9.1.2. Calling a procedure

A procedure is called as an executable PL/SQL statement. In other words, a call to a procedure must end with a semicolon (;) and be executed before and after other SQL or PL/SQL statements (if they exist) in the execution section of a PL/SQL block.

The following executable statement runs the apply_discount procedure:

     BEGIN        apply_discount( new_company_id, 0.15 );  -- 15% discount     END;

If the procedure does not have any parameters, then you call the procedure without any parentheses:

     display_store_summary;

In Oracle8i Database and later, you can also include empty opening and closing parentheses, as in:

     display_store_summary( );

1.9.2. Functions

A function is a module that returns a value. Unlike a procedure call, which is a standalone executable statement, a call to a function can exist only as part of an executable statement, such as an element in an expression or the value assigned as the default in a declaration of a variable.

Because a function returns a value, it is said to have a datatype. A function can be used in place of an expression in a PL/SQL statement having the same datatype as the function.

Functions are particularly important constructs for building modular code. For example, every business rule or formula in your application should be placed inside a function. Every single-row query should also be defined within a function, so that it can be easily and reliably reused.

Some people prefer to rely less on functions and more on procedures that return status information through the parameter list. If this is your preference, make sure that your business rules, formulas, and single-row queries are tucked away into your procedures.

An application short on function definition and usage is likely to be difficult to maintain and enhance over time.

1.9.2.1. Structure of a function

The structure of a function is the same as that of a procedure, except that the function also has a RETURN clause. The general format of a function is as follows:

     FUNCTION [schema.]name [( parameter [, parameter ...] ) ]        RETURN return_datatype        [AUTHID DEFINER | CURRENT_USER]        [DETERMINISTIC]        [PARALLEL ENABLE ...]        [PIPELINED]     IS        [declaration statements]     BEGIN        executable statements     [EXCEPTION        exception handler statements]     END [ name ];

where each element is used in the following ways:

schema: Optional name of the schema that will own this function. The default is the current user. If different from the current user, that user will need privileges to create a function in another schema.
name: Name of the procedure, which comes directly after the keyword FUNCTION.
parameters: Optional list of parameters that you define to both pass information into the procedure and send information out of the procedure back to the calling program.
return_datatype: Datatype of the value returned by the function. This is required in the function header.
AUTHID clause: Determines whether the procedure will execute under the authority of the definer (owner) of the procedure or under the authority of the current user. The former is known as the definer rights model , the latter as the invoker rights model .
DETERMINISTIC clause: Optimization hint that lets the system use a saved copy of the function's return result, if available. The query optimizer can choose whether to use the saved copy or re-call the function.
PARALLEL_ENABLE clause: Optimization hint that enables the function to be executed in parallel when called from within a SELECT statement.
PIPELINED clause: Specifies that the results of this table function should be returned iteratively via the PIPE ROW command .
declaration statements: Declarations of local identifiers for that function. If you do not have any declarations, there will be no statements between the IS and BEGIN statements.
executable statements: Statements the function executes when it is called. You must have at least one executable statement after the BEGIN and before the END or EXCEPTION keywords.
exception handler statements: Optional exception handlers for the function. If you do not explicitly handle any exceptions, then you can leave out the EXCEPTION keyword and simply terminate the execution section with the END keyword.

1.9.2.2. Calling a function

A function is called as part of an executable PL/SQL statement wherever an expression can be used. The following examples illustrate how various functions can be invoked.

Assign the default value of a variable with a function call:

 DECLARE    v_nickname VARCHAR2(100) :=       favorite_nickname ('Steven');

Use a member function for the pet object type in a conditional expression:

 DECLARE    my_parrot pet_t :=       pet_t (1001, 'Mercury', 'African Grey',              TO_DATE ('09/23/1996', 'MM/DD/YYYY')); BEGIN    IF my_parrot.age < INTERVAL '50' YEAR -- 9i INTERVAL type    THEN       DBMS_OUTPUT.PUT_LINE ('Still a youngster!');    END IF;

Retrieve a single row of book information directly into a record:

 DECLARE    my_first_book books%ROWTYPE; BEGIN    my_first_book := book_info.onerow ('1-56592-335-9');    ...

Obtain a cursor variable to overdue book information for a specific user:

 DECLARE    my_overdue_info overdue_rct; BEGIN    my_overdue_info :=       book_info.overdue_info ('STEVEN_FEUERSTEIN');    ...

1.9.3. Parameters

Procedures and functions can both use parameters to pass information back and forth between the module and the calling PL/SQL block.

The parameters of a module are at least as important as the code that implements the module (the module's body). Sure, you have to make certain that your module fulfills its promise. But the whole point of creating a module is that it can be called, ideally by more than one other module. If the parameter list is confusing or badly designed, it will be very difficult for other programmers to make use of the module, and the result is that few will bother. And it doesn't matter how well you have implemented a program if no one uses it.

PL/SQL offers many different features to help you design parameters effectively. This section covers all elements of parameter definition.

1.9.3.1. Defining parameters

Formal parameters are defined in the parameter list of the program. A parameter definition parallels closely the syntax for declaring variables in the declaration section of a PL/SQL block. There are two important distinctions: first, a parameter has a passing mode while a variable declaration does not; and second, a parameter declaration must be unconstrained.

A constrained declaration is one that constrains or limits the kind of value that can be assigned to a variable declared with that datatype. An unconstrained declaration is one that does not limit values in this way. The following declaration of the variable company_name constrains the variable to 60 characters:

     DECLARE        company_name VARCHAR2(60);

When you declare a parameter, however, you must leave out the constraining part of the declaration:

     PROCEDURE display_company (company_name IN VARCHAR2) IS ...

1.9.3.2. Actual and formal parameters

We need to distinguish between two different kinds of parameters: actual and formal parameters. The formal parameters are the names that are declared in the parameter list of the header of a module. The actual parameters are the values or expressions placed in the parameter list of the actual call to the module.

Let's examine the differences between actual and formal parameters using the example of tot_sales. Here, again, is the tot_sales header:

     FUNCTION tot_sales        (company_id_in IN company.company_id%TYPE,         status_in IN order.status_code%TYPE := NULL)     RETURN std_types.dollar_amount;

The formal parameters of tot_sales are:

company_id_in: Primary key of the company
status_in: Status of the orders to be included in the sales calculation

These formal parameters do not exist outside of the function. You can think of them as placeholders for real or actual parameter values that are passed into the function when it is used in a program.

How does PL/SQL know which actual parameter goes with which formal parameter when a program is executed? PL/SQL offers two ways to make the association:

Positional notation: Associates the actual parameter implicitly (by position) with the formal parameter.
Named notation: Associates the actual parameter explicitly (by name) with the formal parameter.

1.9.3.3. Parameter modes

When you define the parameter, you also specify the way in which it can be used. There are three different modes of parameters:

Mode	Description	Parameter usage
IN	Read-only	The value of the actual parameter can be referenced inside the module, but the parameter cannot be changed.
OUT	Write-only	The module can assign a value to the parameter, but the parameter's value cannot be referenced.
IN OUT	Read-write	The module can both reference (read) and modify (write) the parameter.

The mode determines how the program can use and manipulate the value assigned to the formal parameter. You specify the mode of the parameter immediately after the parameter name and before the parameter's datatype and optional default value. The following procedure header uses all three parameter modes:

     PROCEDURE predict_activity        (last_date_in IN DATE,         task_desc_inout IN OUT VARCHAR2,         next_date_out OUT DATE)

The predict_activity procedure takes in two pieces of information: the date of the last activity and a description of the activity. It then returns or sends out two pieces of information: a possibly modified task description and the date of the next activity. Because the task_desc_inout parameter is IN OUT, the program can both read the value of the argument and change the value of that argument.

Positional notation. In every example so far, I have employed positional notation to guide PL/SQL through the parameters. With positional notation, PL/SQL relies on the relative positions of the parameters to make the correspondence: it associates the Nth actual parameter in the call to a program with the Nth formal parameter in the program's header.

With the following tot_sales example, PL/SQL associates the first actual parameter, :order.company_id, with the first formal parameter, company_id_in. It then associates the second actual parameter, N, with the second formal parameter, status_in:

     new_sales := tot_sales (:order.company_id, 'N');     FUNCTION tot_sales        (company_id_in IN company.company_id%TYPE,         status_in IN order.status_code%TYPE := NULL)     RETURN std_types.dollar_amount;

Now you know the name for the way compilers pass values through parameters to modules. Positional notation is certainly the most obvious and common method.

Named notation. With named notation , you explicitly associate the formal parameter (the name of the parameter) with the actual parameter (the value of the parameter) right in the call to the program, using the combination symbol =>.

The general syntax for named notation is:

     formal_parameter_name => argument_value

Because you provide the name of the formal parameter explicitly, PL/SQL no longer needs to rely on the order of the parameters to make the association from actual to formal. So, if you use named notation, you do not need to list the parameters in your call to the program in the same order as the formal parameters in the header. You can call tot_sales for new orders in either of these two ways:

     new_sales :=        tot_sales (company_id_in => order_pkg.company_id, status_in =>'N');     new_sales :=        tot_sales (status_in =>'N', company_id_in => order_pkg.company_id);

You can also mix named and positional notation in the same program call:

     :order.new_sales := tot_sales (order_pkg.company_id, status_in =>'N');

If you do mix notation, however, you must list all of your positional parameters before any named notation parameters, as shown in the preceding example.

1.9.4. Packages

A package is a grouping or packaging together of PL/SQL code elements. Packages provide a structure (both logically and physically) in which you can organize your programs and other PL/SQL elements such as cursors, TYPEs, and variables. They also offer significant, unique functionality, including the ability to hide logic and data from view, and to define and manipulate global, or session-persistent, data.

1.9.4.1. Rules for building packages

The package is a deceptively simple construct. In a small amount of time, you can learn all the basic elements of package syntax and rules, but you can spend weeks (or more) uncovering all the nuances and implications of the package structure. In this section, we review the rules you need to know to build packages.

To construct a package, you must build a specification and, in almost every case, a package body. You must decide which elements go into the specification and which are hidden away in the body. You also can include a block of code that Oracle will use to initialize the package.

The package specification. The specification of a package lists all the elements in that package that are available for use in applications, and it provides all the information a developer needs to use elements defined in the package (often referred to as an API, or application programming interface). A developer should never have to look at the implementation code in a package body to figure out how to use an element in the specification.

Here are some rules to keep in mind for package specification construction:

You can declare elements of almost any datatype, such as numbers, exceptions, types, and collections, at the package level (i.e., not within a particular procedure or function in the package). This is referred to as package-level data. Generally, you should avoid declaring variables in the package specification, although constants are always safe.
You cannot declare cursor variables (variables defined from a REF CURSOR type) in a package specification (or body). Cursor variables are not allowed to persist at the session level (see the section "Package data" later in this chapter for more information about package data persistence).
You can declare almost any type of data structure, such as a collection type, a record type, or a REF CURSOR type.
You can declare procedures and functions in a package specification, but you can include only the header of the program (everything up to but not including the IS or AS keyword).
You can include explicit cursors in the package specification. An explicit cursor can take one of two forms: it can include the SQL query as a part of the cursor declaration, or you can hide the query inside the package body and provide only a RETURN clause in the cursor declaration.
If you declare any procedures or functions in the package specification or if you declare a CURSOR without its query, then you must provide a package body to implement those code elements.
You can include an AUTHID clause in a package specification, which determines whether any references to data objects will be resolved according to the privileges of the owner of the package (AUTHID DEFINER) or of the invoker of the package (AUTHID CURRENT_USER).
You can include an optional package name label after the END statement of the package, as in:
```
 END my_package;
```

Here is a very simple package specification illustrating these rules:

      1  CREATE OR REPLACE PACKAGE favorites_pkg      2     AUTHID CURRENT_USER      3  IS      4     -- Two constants; notice that I give understandable      5     -- names to otherwise obscure values.      6      7     c_chocolate CONSTANT PLS_INTEGER := 16;      8     c_strawberry CONSTANT PLS_INTEGER := 29;      9     10     -- A nested table TYPE declaration     11     TYPE codes_nt IS TABLE OF INTEGER;     12     13     -- A nested table declared from the generic type.     14     my_favorites codes_nt;     15     16     -- A REF CURSOR returning favorites information.     17     TYPE fav_info_rct IS REF CURSOR RETURN favorites%ROWTYPE;     18     19     -- A procedure that accepts a list of favorites     20     -- (using a type defined above) and displays the     21     -- favorite information from that list.     22     PROCEDURE show_favorites (list_in IN codes_nt);     23     24     -- A function that returns all the information in     25     -- the favorites table about the most popular item.     26     FUNCTION most_popular RETURN fav_info_rct;     27     28  END favorites_pkg; -- End label for package

As you can see, a package specification is, in structure, essentially the same as a declaration section of a PL/SQL block. One difference, however, is that a package specification may not contain any implementation code.

The package body. The package body contains all the code required to implement the package specification. A package body is not always needed, but it is required when any of the following conditions is true:

The package specification contains a cursor declaration with a RETURN clause: You will then need to specify the SELECT statement in the package body.
The package specification contains a procedure or function declaration: You will then need to complete the implementation of that module in the package body.
You wish to execute code in the initialization section of the package body: The package specification does not support an execution section (executable statements within a BEGIN...END); you can do this only in the body.

Structurally, a package body is very similar to a procedure definition. Here are some rules particular to package bodies:

A package body can have declaration, execution, and exception sections. The declaration section contains the complete implementation of any cursors and programs defined in the specification, and also the definition of any private elements (not listed in the specification). The declaration section can be empty as long as there is an initialization section .
The execution section of a package is known as the initialization section; this optional code is executed when the package is instantiated for a session.
The exception section handles any exceptions raised in the initialization section. You can have an exception section at the bottom of a package body only if you have defined an initialization section.
A package body may consist of the following combinations: just a declaration section; just an execution section; execution and exception sections; or declaration, execution, and exception sections.
You may not include an AUTHID clause in the package body; it must go in the package specification. Anything declared in the specification may be referenced (used) within the package body.
The same rules and restrictions for declaring package-level data structures apply to the body as well as to the specificationfor example, you cannot declare a cursor variable.
You can include an optional package name label after the END statement of the package, as in:
```
 END my_package;
```

Here is an implementation of the favorites_pkg body:

     CREATE OR REPLACE PACKAGE BODY favorites_pkg     IS        -- A private variable        g_most_popular   PLS_INTEGER := c_strawberry;        -- Implementation of procedure        PROCEDURE show_favorites (list_in IN codes_nt) IS        BEGIN           FOR indx IN list_in.FIRST .. list_in.LAST           LOOP              DBMS_OUTPUT.put_line (list_in (indx));           END LOOP;        END show_favorites;        -- Implement the function        FUNCTION most_popular RETURN fav_info_rct        IS           retval fav_info_rct;           null_cv fav_info_rct;        BEGIN           OPEN retval FOR           SELECT *             FROM favorites            WHERE code = g_most_popular;           RETURN retval;        EXCEPTION           WHEN NO_DATA_FOUND THEN RETURN null_cv;        END most_popular;     END favorites_pkg; -- End label for package

1.9.4.2. Rules for calling packaged elements

It doesn't really make any sense to talk about running or executing a package (after all, it is just a container for code elements). However, you will certainly want to run or reference those elements defined in a package.

A package owns its objects, just as a table owns its columns. To reference an element defined in the package specification outside of the package itself, you must use the same dot notation to fully specify the name of that element. Let's look at some examples.

The following package specification declares a constant, an exception, a cursor, and several modules:

     CREATE OR REPLACE PACKAGE pets_inc     IS        max_pets_in_facility CONSTANT INTEGER := 120;        pet_is_sick EXCEPTION;        CURSOR pet_cur (pet_id_in IN pet.id%TYPE) RETURN pet%ROWTYPE;        FUNCTION next_pet_shots (pet_id_in IN pet.id%TYPE) RETURN DATE;        PROCEDURE set_schedule (pet_id_in IN pet.id%TYPE);     END pets_inc;

To reference any of these objects, I preface the object name with the package name, as follows:

     DECLARE        -- Base this constant on the id column of the pet table.        c_pet CONSTANT pet.id%TYPE:= 1099;        v_next_apppointment DATE;     BEGIN        IF pets_inc.max_pets_in_facility > 100        THEN           OPEN pets_inc.pet_cur (c_pet);        ELSE           v_next_appointment:= pets_inc.next_pet_shots (c_pet);        END IF;     EXCEPTION        WHEN pets_inc.pet_is_sick        THEN           pets_inc.set_schedule (c_pet);     END;

To summarize, there are two rules to follow in order to reference and use elements in a package:

When you reference elements defined in a package specification from outside of that package (an external program), you must use dot notation in the form package_name.element_name.
When you reference package elements from within the package (specification or body), you do not need to include the name of the package. PL/SQL will automatically resolve your reference within the scope of the package.

1.9.4.3. Package data

Package data consists of variables and constants that are defined at the package level that is, not within a particular function or procedure in the package. The scope of the package data is therefore not a single program, but rather the package as a whole. In the PL/SQL runtime architecture, package data structures persist (hold their values) for the duration of a session (rather than the duration of execution for a particular program).

If package data is declared inside the package body, then that data persists for the session but can be accessed only by elements defined in the package itself (private data).

If package data is declared inside the package specification, then that data persists for the session and is directly accessible (to both read and modify the value) by any program that has EXECUTE authority on that package (public data).

If a packaged procedure opens a cursor, that cursor remains open and is available throughout the session. It is not necessary to define the cursor in each program. One module can open a cursor while another performs the fetch. In addition, package variables can carry data across the boundaries of transactions because they are tied to the session rather than to a single transaction.