What is a PL/SQL API?

As a follow-on to yesterday's post on PL/SQL APIs, I decided to describe what I consider to be an API in PL/SQL. Actually, it was an after-thought and then John T went into further details in comments on that post. I felt it was more than a comment should be so...

First, what is an API?

As defined by Wikipedia:

application programming interface (API) is a set of routines, data structures, object classes and/or protocols provided by libraries and/or operating system services in order to support the building of applications.

Easy enough. I think the vast majority of readers understand that.

What is an PL/SQL API?

The way that I would define a PL/SQL API is (preferably) one package that provides the interface to one or more underlying tables. Let's take the EMP table for instance. If I were to define an API for that it would be something like this (pseudocode):

• Create Employee
• Update Employee
• Update Employee Department
• Update Employee Salary

All other code would call one of these procedures/functions to perform the specific action. No one else would write a direct update on EMP, no matter how small or seemingly trivial. If something outside of the 4 defined actions was needed, then it would be added to the preceding package.

Why use a PL/SQL API?

In all likeliness, the person who is adding the underlying table or tables is going to be the subject matter expert (SME). They should be the ones to define the actions to be performed against that set of tables.

Security is a factor here as well. Would you give INSERT/UPDATE/DELETE to non application users or would you require them to use the PL/SQL API? If I had my way, no one would have any direct DML to any table in the application.

How about support and maintenance? If you make a change to a table you should only have to change in 1 or 2 locations (say the package and a view), not 10, or 20, or 30. If you have it contained in just a couple of locations most minor changes would be relatively easy and quick to complete. If it's spread out across the database, you end up obscuring or hiding many of your data structures.

Finally, to John T's point, there is encapsulation, which hits on a few points noted above. I'm not going to quote the entire page (though it would be appropriate), but I will come close:

...encapsulation is the hiding of the internal mechanisms and data structures of a software component behind a defined interface, in such a way that users of the component (other pieces of software) only need to know what the component does, and cannot make themselves dependent on the details of how it does it. The purpose is to achieve potential for change: the internal mechanisms of the component can be improved without impact on other components, or the component can be replaced with a different one that supports the same public interface.

Encapsulation also protects the integrity of the component, by preventing users from setting the internal data of the component into an invalid or inconsistent state.

Another benefit of encapsulation is that it reduces system complexity and thus increases robustness, by limiting the interdependencies between software components.

...For example, a simple digital alarm clock is a real-world object that a lay person can use and understand. They can understand what the alarm clock does, and how to use it through the provided interface (buttons and screen), without having to understand every part inside of the clock. Similarly, if you replaced the clock with a different model, the lay person could continue to use it in the same way, provided that the interface works the same.

I think that sums it up nicely.

So how about you? PL/SQL APIs?