Oracle PL/SQL Best Practices

Name

PKG-10: Consolidate the implementation of related overloaded modules.

Synopsis

In most cases, when you build overloaded programs, each program performs a similar operation, with variations that are usually related to different combinations of parameters. If you aren’t careful about how you implement these overloadings, you will end up with a mess of code that’s difficult to maintain and enhance.

The most important step you can take is to isolate behavior/features common to all overloadings and then move that common code into a separate, usually private program. All the overloadings then call that internal program.

You should also take care to organize the overloaded headers contiguously in the package specification so that they are easily identified.

Example

Suppose that I have decided to build an encapsulation package around the book table. Developers will not write an INSERT statement to add a book; they will call an insert procedure. I can think of several different ways to perform that insert:

• Pass an individual value for each column.

• Pass a record containing a value for each column.

• Pass a collection of multiple records of book data.

Here’s the package specification corresponding to these approaches:

CREATE OR REPLACE PACKAGE te_book IS TYPE book_tt IS TABLE OF book%ROWTYPE; PROCEDURE ins ( isbn_in IN book.isbn%TYPE, title_in IN book.title%TYPE DEFAULT NULL, summary_in IN book.summary%TYPE DEFAULT NULL, author_in IN book.author%TYPE DEFAULT NULL, date_published_in IN book.date_published%TYPE ...