Listing 10.7. Providing a forward declaration for a function.

SQL> set serveroutput on
SQL>
SQL> declare
  2
  2  function Insurable_Patient (Patient_ID IN varchar2) return number;
  3
  3  function Medicare_Patient (Patient_ID IN varchar2)
  4           return number is
  5
  5  status   number;
  6  Pat_ID   varchar2(6);
  7
  7  begin
  8
  8  if Insurable_Patient (Pat_ID) = 2 then
  9     status := 1;
 10  end if;
 11
 11  return status;
 12
 12  end Medicare_Patient;
 13
 13
 13  function Insurable_Patient (Patient_ID IN varchar2)
 14           return number is
 15
 15  status   number;
 16  Pat_ID   varchar2(6);
 17
 17  begin
 18
 18  if Medicare_Patient (Pat_ID) = 2 then
 19     status := 1;
 20  end if;
 21
 21  return status;
 22
 22  end Insurable_Patient;
 23
 23  --  Executable portion of anonymous block.
 24
 24  begin
 25
 25  dbms_output.enable;
 26
 26  end;
 27  /
PL/SQL procedure successfully completed.

Using Stored Functions in a SQL Statement

With release 7.1 of the Oracle RDBMS, it became possible to reference a stored function within a SQL statement. This feature is enormously powerful because it extends the functionality of a single SQL statement to include the logic contained in a stored function. Let's look at an elementary example of how this functionality is achieved.

Because Oracle doesn't offer a built-in function for converting temperature from Fahrenheit to centigrade, you create a stored function to perform the conversion, as demonstrated in Listing 10.8. After the stored function has been successfully created, you can use it in a SELECT statement.

Listing 10.8. Using a stored function in a SELECT statement.

SQL> create or replace function DegF_to_DegC (Deg_F IN number)
  2         return number is
  3
  3  Deg_C  number;
  4
  4  begin
  5
  5  Deg_C := (5.0/9.0)*(Deg_F - 32);
  6
  6  return Deg_C;
  7
  7  end DegF_to_DegC;
  8  /
Function created.
SQL> select body_temp, degf_to_degc(body_temp)
  2  from patient;
BODY_TEMP DEGF_TO_DEGC(BODY_TEMP)
--------- -----------------------
     99.2               37.333333
    100.2               37.888889
    103.8               39.888889

Once you create a stored function, it is always available for use.

Storing Results to a Table

Although PL/SQL doesn't have any built-in support for communicating with the user, you can still use PL/SQL to provide results to a user or another program by

• Writing information to an intermediate table that a user or program can query.

• Using the procedures and functions available in the Oracle-supplied package DBMS_OUTPUT.

You've already seen an example of how PL/SQL can write to an intermediate table. When compiling PL/SQL stored procedures and functions, the PL/SQL engine itself writes error messages to a data dictionary table that can be queried by the developer. If you want to provide output via SQL*Plus, using DBMS_OUTPUT is a good strategy. If you need to pass many values to a user or a program, writing the results to a table makes more sense.

Invoking a Stored Procedure or Function

The method for invoking a stored procedure or function depends on the context.

For SQL*Plus, use the execute command (this syntax is for a stored procedure that doesn't have any arguments) in the following way:

execute      show_inserts;

From a PL/SQL subprogram, simply reference the stored procedure or function with any required arguments. During Day 14, "Developer/2000: Application Development with Oracle Forms," you learn how to invoke a stored procedure or function from within an Oracle Forms application.

Packages

A package is a group of related PL/SQL procedures and functions. Like the Ada programming language, a PL/SQL package consists of a package specification and a package body. You can construct packages that are application specific—for instance, a package named patient_data would contain procedures and functions related to the manipulation and retrieval of hospital patient information. Furthermore, a package could contain procedures and functions that provide a common service, such as the conversion of location information from one coordinate system to another.

NEW TERM
A package is a collection of related PL/SQL procedures and functions that is stored in an Oracle database. To create a package, you must create a package specification and a package body.

Declaring a Package

The general syntax for creating a package specification is as follows:

CREATE [OR REPLACE] PACKAGE package-name IS
declaration-section
END package-name;

The variables are defined as follows:

package-name is the name of the package to be created and is subject to Oracle database object-naming restrictions.

declaration-section consists of type, variable, cursor, procedure, and function declarations.

As an example, Listing 10.9 contains the package specification for the Flugle College Information System.

Listing 10.9. Declaring a package specification.

create or replace package Flugle is

function register_for_class (arg_student_ID IN varchar2,
                             arg_class_ID   IN varchar2)
  return number;
	
function schedule_conflict (arg_student_ID IN varchar2,
                            arg_class_ID   IN varchar2)
  return number;

procedure drop_class (arg_student_ID IN varchar2,
                      arg_class_ID   IN varchar2,
                      status     OUT number);

procedure assign_instructor (arg_class_ID      IN varchar2,
                             arg_instructor_ID IN varchar2,
                             status        OUT number);

procedure assign_grade (arg_student_ID IN varchar2,
                        arg_class_ID   IN varchar2,
                        arg_grade      IN varchar2,
                        status     OUT number);

function student_GPA (arg_student_ID IN varchar2)
   return number;

END;

ANALYSIS
The Flugle package contains seven items: three procedures and three functions:

• Procedure drop_class
• Procedure assign_instructor
• Procedure assign_grade
• Function register_for_class
• Function schedule_conflict
• Function student_GPA
  

Declaring a Package Body

A package body contains the public and private elements of a package. It hides the details of how cursors, procedures, and functions are actually implemented—details that should be hidden from developers.

A package body is declared using the following syntax:

CREATE PACKAGE BODY package-name IS
declaration-section
procedure-bodies;
function-bodies;
initialization-section
END package-name;

The package variables are defined as follows:

package-name is the name of the package to be created and is subject to Oracle database object-naming restrictions.

declaration-section consists of type, variable, and cursor declarations.

procedure-bodies consists of the executable sections of each procedure that was declared in the package specification.

function-bodies consists of the executable sections of each function that was declared in the package specification.

initialization-section is an optional section that is executed once when the package is first referenced.

Listing 10.10 lists the contents of the Flugle package body, which contains the details of each procedure and function that is part of the package.

Listing 10.10. Declaring a package body.

create or replace package flugle is

-- Declare some exceptions.

schedule_conflict_exists exception;
already_registered       exception;