PL/SQL Best Practices
As the discipline of software development evolves, software products grow in size and the code grows in complexity, the need for a set of guidelines and standards becomes increasingly evident. In this article I will enumerate some of the industry best practices that the experts have proposed and emphasized, in the context of PL/SQL programming.
These best practices improve our data marts, data warehouses and programming environments by introducing the features like manageability, maintainability and performance, regardless of the size of organization or the development effort. The list I have provided is neither exhaustive nor complete and I believe that producing such a list is very subjective given the fact that each software development effort and development organization is unique and not all the best practices are applicable in all situations. Here are some of the more common suggestions that you will find useful:
As Oracle 10G gains more ground, a term that we will all hear repeatedly is 'self-managing'. Oracle has incorporated features in this version that would reduce DBA intervention to manage it. It would be nice to introduce some 'self-management' features into the our PL/SQL code as well.
Developers and support personnel alike are faced, quite often, with a situation where the definitions of the tables have to be altered from one data type to another. In an ideal world nothing else would need modification. Realistically, this can break all the PL/SQL code that is based on this table. Modifying all this code can be a daunting task. Using anchored declarations can bring our ideal world a step closer to us.
Let's assume we have a table called ITEMS, defined as follows:
Name Null? Type --------------- ------------ -------- ITEM_ID NUMBER ITM_DESCRIPTION VARCHAR2(60) PRICE NUMBER(5,2) QTY NUMBER
Also, suppose there is a stored procedure that prints out an alert message when the quantity of an item in stock falls below a certain limit.
CREATE OR REPLACE PROCEDURE inventory_update (item_id_in IN NUMBER) IS v_qty NUMBER; BEGIN SELECT qty into v_qty FROM items WHERE item_id = item_id_in; IF (v_qty < 10 ) THEN DBMS_OUTPUT.PUT_LINE ('Warning: Stock low...'); END IF; END inventory_update;Figure 1: Anchored Declarations
What happens if we later decide to change the definition for the item_id field from NUMBER to VARCHAR2(20)? The code above will fail. In order to write the code in a way that it remains un-effected in the face of the changing data type of a column, replace the definition for the parameter item_id_in from NUMBER to an anchored data type items.item_cd%TYPE like this:
CREATE OR REPLACE PROCEDURE inventory_update(item_id_in IN items.item_id%TYPE) IS
Doing this will cause the code to have the same data type as that of the column in the table. Your code will work without any modification.
Performance tuning falls under the realm of DBA duties more than developers. However, developers have an equal stake in the performance and there is a lot that can be done at the code-level to achieve this goal. One of these is the use of bulk collects.
By using bulk collects, we can load multiple rows into the collections rather than one at a time thus reducing strain on database resources by reducing the pass to the database. The code segments in figure 2 and figure 3 demonstrate this point.
CREATE OR replace PROCEDURE no_bulk_proc is CURSOR item_cur IS SELECT items.item_id, qty FROM items; item_rec item_cur%ROWTYPE; BEGIN OPEN item_cur; LOOP FETCH item_cur INTO item_rec; EXIT WHEN item_cur%notfound; dbms_output.put_line(item_rec.item_id); dbms_output.put_line(item_rec.qty); END LOOP; END no_bulk_proc;Figure 2: Without using BULK collects
CREATE OR REPLACE PROCEDURE bulk_proc IS CURSOR item_cur IS SELECT item_id, qty FROM items; TYPE t_item IS TABLE OF ITEMS.item_id%TYPE INDEX BY BINARY_INTEGER; TYPE t_qty IS TABLE OF ITEMS.qty%TYPE INDEX BY BINARY_INTEGER; v_item t_item; v_qty t_qty; BEGIN OPEN item_cur; FETCH item_cur bulk collect INTO v_item, v_qty limit 100; FOR i IN v_item.first .. v_item.last LOOP dbms_output.put_line(v_item(i)); dbms_output.put_line(v_qty(i)); END LOOP; CLOSE item_cur; END bulk_proc;Figure 3: Using BULK collects
Here the 'limit 100' loads only 100 records into memory for processing which provides an added benefit that we have reduced the risk of running out of main memory as opposed to the code in figure 3 which may run into memory issues.
Control flow and conditional statements, if-then-else and loops, are the basic building blocks of any programming language. Both these structures provide several different variations and forms to choose from. But in my observation as a programmer, I have noticed that people grow comfortable with using one kind of structure only and rarely do a comparison when implementing, especially in case of loop structures. Consider the following code in figure 4.
IF quantity <= 5 THEN Shipping_method := standard_shipping; END IF; IF quantity > 5 THEN Shipping_method := expedited_shipping; END IF;Figure 4: Multiple Conditional Statements
This is fine as long as the condition is different in each IF clause. But in our case it is obvious that either one of them will be true at any given time. However the way this code is written, both the condition will be checked resulting in an in-efficient module especially when there are more than a few such situations in the code. Use of ELSIF would improve this situation as shown in figure 5.
IF quantity <= 5 THEN Shipping_method := standard_shipping; ELSIF quantity > 5 THEN Shipping_method := expedited_shipping; END IF;Figure 5: Replacing IF with ELSIF
Here, either one of the two will be evaluate to true. In case it is the first one which evaluates to true the rest of the statement will be ignored.
Use of CASE statements, is a much better alternative to nested if-elsif statements, particularly when number of conditions to check for is large.
CASE quantity WHEN <=5 THEN shipping_method := standard_shipping; WHEN > 5 THEN shipping_method := expedited_shipping; ELSE shipping_method := 0 END CASE;Figure 6: Using CASE for mutually exclusive condition checking
A key to improved code performance is the use of bind variables. It is a simple technique yet powerful enough to improve performance of the PL/SQL code by several orders of magnitude, depending on the type and size of data. The reason for that lies in the manner DML statements are handled in Oracle. When an Oracle SQL statement is issued, the statement is parsed and saved in a shared memory area so that when the same statement is issued again by the program, the system can skip the parsing step, thus saving processing time and resources.
Consider the code in figure 7.
(The code given below is for the purpose of demonstration and comparison only and may not reflect real-life situation.)
DECLARE BEGIN FOR i IN 1 .. 1000 LOOP EXECUTE IMMEDIATE 'SELECT item_id, qty FROM items WHERE qty = ' || i; END LOOP; END;Figure 7: Without bind variables
Every iteration of the loop produces the following statements:
SELECT item_id, qty FROM items WHERE qty = 1;
SELECT item_id, qty FROM items WHERE qty = 2;
SELECT item_id, qty FROM items WHERE qty = 3;
When the system checks the shared memory area (shared pool) it considers each statement to be unique and fails to make use of the parsed information already available. We, however, know that the statements are almost identical. Bind variables can help remedy this situation. Consider the code slightly modified code in Figure 3.
DECLARE BEGIN FOR i IN 1 .. 1000 LOOP EXECUTE IMMEDIATE 'SELECT item_id, qty FROM items WHERE qty = :x' using i; END LOOP; END;Figure 7: With bind variables
In this case the parser will get exactly the same information in every loop iteration and would not have to re-parse the SQL statement.
Not only that the code is faster with the use of the bind variables, it is also less CPU intensive and allows better sharing of resources by decreasing the number of latches resulting in not just better performing code but a better performing system as a whole.
As is evident from the code examples above, bind variables are applicable in situations where dynamic SQL is being used. Since today's graphical user interface programming makes very heavy use of dynamic SQL, there are more opportunities of realizing the advantage of bind variables.
In my personal experience, the hi-tech community tends to ignore standards or a set of best practices if the list gets too long. Also, not all the best practices are applicable in all situations. A practical approach to this would be to keep the list of best practices small, prioritize items in that list, and once done, ensure that the list is referred to at appropriate times. It would be nice if at the end of every PL/SQL development effort the developers would go through a check list to verify if, and how closely, they have followed the best practices.
The need for good programming practices cannot be emphasized enough. This is, arguably, true for database applications more than any other type of programming. Bulk collects, anchored declarations, decision structures and bind variables are just a few of the items from the list of best practices that most experts have proposed to make PL/SQL code more manageable and efficient.