Learning Oracle PL/SQL

3.5. Using PL/SQL Packages to Organize Code

At this point we've written PL/SQL to handle a few of the catalog tasks, plus we've written some unit testing code and utilities.

Now it's time to identify some of the shortcuts we have taken with our overall requirements and design, and figure out how we're going to overcome the resulting limitations.

There are a lot of things that we've completely ignored in the code shown so far. For example:

• What happens if the record in the books table already exists? Is that the same thing as adding a new copy of the book?

• How can the librarian modify information in the catalog?

• What if the book gets "weeded," lost, or otherwise removed from the library? How will we use PL/SQL to record that fact in the database?

• What if there are lots of different kinds of database lookups (queries) we'll need to do, such as retrieving books based on various search criteria?

Clearly, by the time this thing is done, we're going to wind up with a lot of bits and pieces of code that support related, but not identical, tasks. Wouldn't it be nice if there were a way to organize this code to make it easier to build and manage? There is, and it's called a package.

A PL/SQL package is a named container that can hold any number of procedures and functions. Packages can hold other constructs too, such as exceptions, variables, and type declarations, and later we'll see how incredibly useful these additional features can be. For now, though, we'll start by putting only ...