TECHNOLOGY: PL/SQL Practices
The Right Place for PL/SQLBy Steven Feuerstein
Best practices for PL/SQL location, location, location
I write packages and procedures in both Oracle Database and Oracle Developer applications (Oracle Forms). How should I decide where to put my code?
Another way to frame this question is to ask what the scope of your program should be. That is, from which programs in your application can it be called? Within just a single form? All forms? From within a single program on the server? From any schema that connects to your instance?
I make my decision on these issues by following this principle: Implement the program as closely as possible to where it is used (called).
Location and Relocation Demonstration
For this answer, I use the following business requirement to demonstrate the variations possible and appropriate ways to define my code:
My team is building a call support application. Purchasers of my company's product call us when they have a problem, and we put their calls into a queue if they cannot be handled immediately. I must now write a program that distributes unhandled calls to members of the call support team. The package that will hold all of this logic is call_manager. The procedure for distributing unhandled calls is distribute_calls. Listing 1 shows the header and executable section of this program.
Code Listing 1: distribute_calls procedure
PROCEDURE distribute_calls ( department_id_in IN departments.department_id%TYPE) IS BEGIN WHILE ( calls_are_unhandled ( ) ) LOOP FOR emp_rec IN emps_in_dept_cur (department_id_in) LOOP IF current_caseload (emp_rec.employee_id) < avg_caseload_for_dept (department_id_in) THEN assign_next_open_call (emp_rec.employee_id); END IF; END LOOP; END LOOP; END distribute_calls;
As you can see, this executable section is quite readable: While there is still at least one unhandled call, then for each employee in the specified department, if the current caseload is less than the average for that department, assign the next open call to that employee. Then continue only if there are still unhandled calls.
The executable section calls many subprograms to get the work done:
One other thing to note: I haven't yet actually implemented any of these programs. I am using top-down design, also known as stepwise refinement, to stay focused on the overall, high-level logic of the program. That way I avoid getting consumed by all the little details.
I can now go down to the next level of detail and figure out where to put the implementation for these subprograms.
As I said, my rule is: Define the subprogram as closely as possible to its usage. Following that rule without any further analysis, I would define each of the programs as local subprograms within distribute_calls itself, as shown in Listing 2 (the ellipses [...] indicate the implementation of the subprograms).
Code Listing 2: Four local programs in distribute_calls
PROCEDURE distribute_calls ( department_id_in IN departments.department_id%TYPE) IS FUNCTION calls_are_handled RETURN BOOLEAN IS BEGIN ... END calls_are_handled; FUNCTION current_caseload ( employee_id_in IN employees.employee_id%TYPE) RETURN PLS_INTEGER IS BEGIN ... END current_caseload; FUNCTION avg_caseload_for_dept ( employee_id_in IN employees.employee_id%TYPE) RETURN PLS_INTEGER IS BEGIN ... END current_caseload; PROCEDURE assign_next_open_call ( employee_id_in IN employees.employee_id%TYPE) IS BEGIN ... END assign_next_open_call; BEGIN
Procedures and functions directly defined within the declaration section of any PL/SQL block are called local or nested subprograms. In this example, they can be called only within the distribute_calls procedure, and that certainly defines them as closely as possible to their usage.
Yet, as I do this, I find myself thinking about the other programs I have already written in this package and how I might want to use some of this new code in programs I'll write in the future.
I realize, for example, that last week I wrote another function that is very similar to current_caseload. It is now "buried" inside a procedure named show_caseload. Rather than implement the same logic twice (and thereby need to debug and maintain it in both places), it makes more sense for me to move the current_caseload function out of both distribute_calls and show_caseload.
So with a little reshuffling of code, I end up with the package body shown in Listing 3.
Code Listing 3: Relocating the current_caseload function
CREATE OR REPLACE PACKAGE BODY call_manager IS FUNCTION current_caseload ( employee_id_in IN employees.employee_id%TYPE) RETURN PLS_INTEGER IS BEGIN ... END current_caseload; PROCEDURE show_caseload ( department_id_in IN departments.department_id%TYPE) IS BEGIN ... END show_caseload; PROCEDURE distribute_calls ( department_id_in IN departments.department_id%TYPE ) IS BEGIN ... END distribute_calls; END; /
Now I have moved the current_caseload function farther away from distribute_calls, but that is because it is used by two subprograms in the package. So it is now as close as possible to both of its usages. Yet I don't expect or see any need for current_caseload to be used outside of the distribute_calls package, so I do not place the header of current_caseload in the package specification.
Now my attention turns to avg_caseload_for_dept. Something about this program seems so familiar. What is it, what is it? Oh, yeah! My coworker Sandra sent out an e-mail last week letting us all know that she had put together a package named call_util that contained several handy utility programs, including a function that returned the average caseload for an employee.
I slap my forehead, dig out the e-mail, and find that the function is named dept_avg_caseload. I check for the existence of call_util in my PL/SQL editing environment, and—lo and behold—the call_util.dept_avg_caseload function in all its already implemented beauty is there, waiting to be used.
I now go back to my distribute_calls procedure, delete the avg_caseload_for_dept function, and change my executable section as shown in Listing 4.
Code Listing 4: Revised distribute_calls executable section
BEGIN WHILE ( calls_are_unhandled ( ) ) LOOP FOR emp_rec IN emps_in_dept_cur (department_id_in) LOOP IF current_caseload (emp_rec.employee_id) < call_util.dept_avg_caseload (department_id_in) THEN assign_next_open_call (emp_rec.employee_id); END IF; END LOOP; END LOOP; END distribute_calls;
Now one of the subprograms I am using in my procedure is declared so far away that I don't even have control over its implementation and may never even see that implementation. Is that a problem? No. I have more than enough to do and worry about!
The call_util.dept_avg_caseload function is implemented far from my usage, but it is as close as possible to all usages, which span various packages, and so must be declared in the package specification of call_utils.
Whew. I think I have now finished optimizing the location of the definitions of my subprograms. I am left with two local subprograms (calls_are_unhandled and assign_next_open_call), one program (current_caseload) defined privately at the package level (not appearing in the package specification), and another function (call_util.dept_avg_caseload) that someone else wrote and that is available to any schema with execute authority on the call_util package.
I hope the steps I went through to build distribute_calls will help you make your own decisions on where best to place the implementations of your own complex, multilayered programs.
Oracle Developer Code Location
This column has focused on where and how to define code in Oracle Database, but the same rules and logic apply to the Oracle Developer environment. I suggest these guidelines:
Steven Feuerstein (firstname.lastname@example.org) is Quest Software's PL/SQL evangelist. He has published 10 books on Oracle's programming language, including Oracle PL/SQL Programming and Oracle PL/SQL Best Practices (O'Reilly Media), and has created the free Quest Code Tester for Oracle.