RelationalDBDesign RelationalDBDesign


Creating Packages   «Prev 

Package body Syntax and Example in Oracle

Demonstrating the Power of the Package

A package consists of up to two chunks of code:
  1. the specification (required) and
  2. the body (optional, but almost always present).
The specification defines how a developer can use the package:
  1. which programs can be called,
  2. what cursors can be opened.
The body contains the implementation of the programs (and, perhaps, cursors) listed in the specification, plus other code elements as needed. Suppose that I need to write code to retrieve the full name of an employee whose name is in the form "last, first". That seems easy enough to write:
PROCEDURE process_employee (
employee_id_in IN employees.employee_id%TYPE)
IS
l_fullname VARCHAR2(100);
BEGIN
SELECT last_name || ',' || first_name
INTO l_fullname
FROM employees
WHERE employee_id = employee_id_in;
...
END;

Yet there are many problems lurking in this seemingly transparent code:
  1. I have hardcoded the length of the l_fullname variable. I did this because it is a derived value, the concatenation of two column values. I did not, therefore, have a column against which I could %TYPE the declaration. This could cause difficulties over time if the size of last_name and/or first_name columns are expanded.
  2. I have also hardcoded or explicitly placed in this block the formula (an application rule, really) for creating a full name. What is wrong with that, you wonder? What if next week I get a call from the users: We want to see the names in first-space- last format. Now you must hunt through all my code for the last-comma-first constructions.
  3. Finally, this very common query will likely appear in a variety of formats in multiple places in my application.
    This SQL redundancy can make it very hard to maintain app logic and optimize its performance.

Oracle DMBS Packages

How should a developer deal with this challenge?

I would like to be able to change the way I write my code to avoid the above hardcodings. To do that, I need to write these things once (one definition of a "full name" datatype, one representation of the formula, one version of the query) and then call them wherever needed. Packages can solve this challenge.
Consider the following package specification:

/* Files on web: fullname.pkg, fullname.tst */
1 PACKAGE employee_pkg
2 AS
3 SUBTYPE fullname_t IS VARCHAR2 (200);
4
5 FUNCTION fullname (
6 last_in employees.last_name%TYPE,
7 first_in employees.first_name%TYPE)
8 RETURN fullname_t;
9
10 FUNCTION fullname (
11 employee_id_in IN employees.employee_id%TYPE)
12 RETURN fullname_t;
13 END employee_pkg;

What I have done here is essentially list the different elements I want to use. The following table summarizes the important elements of the code.

Package body syntax and example
CREATE [OR REPLACE] PACKAGE BODY packagename {IS | AS} 
{PROCEDURE | FUNCTION} subprogram_name [(parameter_specs)] IS 
BEGIN
  ... PL/SQL commands here ...
END subprogram_name;
{PROCEDURE | FUNCTION} subprogram_name [(parameter_specs)] IS 
BEGIN 
  ...PL/SQL commands here ...
END subprogram_name;
  ...
END [packagename];

The following notes in the table below are with respect to the red rectangles in the syntax diagram above.


Location 1 Specify the same package name here that was used in your package specification command.
Location 2 Each public sub-program is defined here. Parameters must match the package specifications. Separate the sub-programs using the END statement followed by a semicolon. In addition to the public sub-programs, additional private sub-programs, cursors, variables, and exceptions can be defined here.