Demonstrating the Power of the Package

A package consists of up to two chunks of code: the specification (required) and the body (optional, but almost always present). The specification defines how a developer can use the package: which programs can be called, what cursors can be opened, and so on. 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 out:

CREATE OR REPLACE PROCEDURE process_employee ( employee_id_in IN employee.employee_id%TYPE)IS l_fullname VARCHAR2(100);BEGIN SELECT last_name || ',' || first_name INTO l_fullname FROM employee WHERE employee_id = employee_id_in; ...END;

Yet there are many problems lurking in this seemingly transparent code:

· I have hardcoded the length of the 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 last_name or first_name columns are expanded.

· I have also hardcoded or explicitly placed in this block the formula (an application rule, really) for creating a full name. What's 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." Yikes! Time to hunt through all my code for the last-comma-first constructions.

· 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 my logic—and optimize its performance.

What's a developer to do? 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 to the rescue!

Take a look at the followingpackage specification:

/* File on web: fullname.pkg */ 1 CREATE OR REPLACE PACKAGE employee_pkg 2 AS 3 SUBTYPE fullname_t IS VARCHAR2 (200); 4 5 FUNCTION fullname ( 6 last_in employee.last_name%TYPE, 7 first_in employee.first_name%TYPE) 8 RETURN fullname_t; 9 10 FUNCTION fullname (11 employee_id_in IN employee.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. On line 3, I declare a "new" datatype using SUBTYPE called fullname_t. It is currently defined to have a maximum of 200 characters, but that can be easily changed if needed.

I then declare a function called fullname (lines 5 through 8). It accepts a last name and a first name and returns the full name. Notice that the way the full name is constructed is not visible in the package specification. That's a good thing, as you will soon see.

Finally, on lines 10-12, I have a second function, also called fullname; this version accepts a primary key for an employee and returns the full name for that employee. This repetition is an example of overloading, which we explored in Chapter 16.

Now, before I even show you the implementation of this package, let's rewrite the original block of code using my packaged elements (notice the use of dot notation, which is very similar to its use in the form table.column):

DECLARE l_name employee_pkg.fullname_t; employee_id_in employee.employee_id%type := 1;BEGIN l_name := employee_pkg.fullname (employee_id_in); ...END;

I declare my variable using the new datatype, and then simply call the appropriate function to do all the work for me. The name formula and the SQL query have been moved from my application code to a separate "container" holding employee-specific functionality. The code is cleaner and simpler. If I need to change the formula for last name or expand the total size of the full name datatype, I can go to the package specification or body, make the changes, and recompile any affected code, and the code will automatically take on the updates.

Speaking of the package body, here is the implementation of employee_pkg:

1 CREATE OR REPLACE PACKAGE BODY employee_pkg 2 AS 3 FUNCTION fullname ( 4 last_in employee.last_name%TYPE, 5 first_in employee.first_name%TYPE 6 ) 7 RETURN fullname_t 8 IS 9 BEGIN10 RETURN last_in || ',' || first_in;11 END;12 13 FUNCTION fullname (employee_id_in IN employee.employee_id%TYPE)14 RETURN fullname_t15 IS16 retval fullname_t;17 BEGIN18 SELECT fullname (last_name, first_name) INTO retval19 FROM employee20 WHERE employee_id = employee_id_in;21 22 RETURN retval;23 EXCEPTION24 WHEN NO_DATA_FOUND THEN RETURN NULL;25 26 WHEN TOO_MANY_ROWS THEN errpkg.record_and_stop;27 END;28 END employee_pkg;

Lines 3-11 are nothing but a function wrapper around the last-comma-first formula. Lines 13-27 showcase a typical single-row query lookup built around an implicit query. Notice, though, that on line 18, the query calls that self-same fullname function to return the combination of the two name components.

So now if my users call and say "first-space-last, please!", I will not groan and work late into the night, hunting down occurrences of || ', ' ||. Instead, I will change the implementation of my employee_pkg.fullname in about five seconds flat and astound my users by announcing that they are good to go.

And that, dear friends, gives you some sense of the beauty and power of packages.