PL/SQL Best Practices - Orafaq Article

Show/Hide Article

By Irfan Haq

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;

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.

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;

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.

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;

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

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;

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;

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;

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.

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.

 

Source: http://www.orafaq.com/articles/archives/000054.htm