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:

Use Anchored Declarations:

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;

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.

Use BULK Collects:

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;

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;

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.

Carefully Select Your Control Structures:

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;

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;

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;

Use Bind Variables:

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;

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;
Etc...

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;

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.

Conclusion:

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.