Encapsulating Data Manipulation

Rather than have developers write their own SQL statements, you should provide an interface to those SQL statements. This is one of the most important motivations for building packages, yet is only rarely employed by developers.

With this approach, PL/SQL developers as a rule will not write SQL in their applications. Instead, they will call predefined, tested, and optimized code that does all the work for them; for example, an "add" procedure (overloaded to support records) that issues the INSERT statement and follows standard error-handling rules; a function to retrieve a single row for a primary key; and a variety of cursors that handle the common requests against the data structure (which could be a single table or a "business entity" consisting of multiple tables).

If you take this approach, developers will not necessarily need to understand how to join three or six different highly normalized tables to get the right set of data. They can just pick a cursor and leave the data analysis to someone else. They will not have to figure out what to do when they try to insert and the row already exists. The procedure has this logic inside it (and can be easily upgraded to use the Oracle9i MERGE statement).

Perhaps the biggest advantage of this approach is that as your data structures change, the maintenance headaches of updating application code are both minimized and centralized. The person who is expert at working with that table or object type makes the necessary changes within that single package, and the changes are then "rolled out" more or less automatically to all programs relying on that package.

Data encapsulation is a big topic and can be very challenging to implement in a comprehensive way. You will find an example of a table encapsulation package (built around the employee table) in the te_employee.pks and te_employee.pkb files on the O'Reilly web site. Let's take a look at what sort of impact this kind of package can have on your code. The givebonus1.sp file on the web site contains a procedure that gives the same bonus to each employee in the specified department, but only if he or she has been with the company for at least six months. Here are the parts of the give_bonus program that contains the SQL (see givebonus1.sp for the complete implementation):

CREATE OR REPLACE PROCEDURE give_bonus ( dept_in IN employee.department_id%TYPE, bonus_in IN NUMBER)IS v_name VARCHAR2(50); CURSOR by_dept_cur IS SELECT * FROM employee WHERE department_id = dept_in;BEGIN /* Retrieve all information for the specified department. */ SELECT name INTO v_name FROM department WHERE department_id = dept_in; /* For each employee in the specified department... */ FOR rec IN by_dept_cur LOOP IF ADD_MONTHS (SYSDATE, -6) > rec.hire_date THEN UPDATE employee SET salary = rec.salary + bonus_in WHERE employee_id = rec.employee_id; END IF; END LOOP;END;

Now let's compare that to the encapsulation alternative, which you will find in its entirety in givebonus2.sp:

CREATE OR REPLACE PROCEDURE give_bonus ( dept_in IN employee.department_id%TYPE, bonus_in IN NUMBER)IS dept_rec department%ROWTYPE; fdbk INTEGER;BEGIN dept_rec := te_department.onerow (dept_in); /* Make sure packaged cursor is closed. */ te_employee.close_emp_dept_lookup_all_cur; FOR rec IN te_employee.emp_dept_lookup_all_cur ( dept_in) LOOP IF ADD_MONTHS (SYSDATE, -6) > rec.hire_date THEN te_employee.upd$salary ( rec.employee_id, rec.salary + bonus_in, fdbk); END IF; END LOOP;END;/

All the SQL has been removed from the program, replaced with calls to reusable procedures and functions. This optimizes the SQL in my application and allows me to write more robust code in a more productive manner.

It is by no means a trivial matter to build (or generate) such packages and I recognize that most of you will not be willing or able to adopt a 100% encapsulated approach. You can, however, gain many of the advantages of data encapsulation without having to completely revamp your coding techniques. At a minimum, I suggest that you:

· Hide all your single-row queries behind a function interface. That way, you can make sure that error handling is performed and you can choose the best implementation (implicit or explicit cursors, for example).

· Identify the tables that are most frequently and directly manipulated by developers and build layers of code around them.

· Create packaged programs to handle complex transactions. If "add a new order" involves inserting two rows, updating six others, and so on, make sure to embed this logic inside a procedure that handles the complexity. Don't rely on individual developers to figure it out (and write it more than once!).