In Pursuit of pl/sql programming excellence

John C. Lennon

Utility Partners

Introduction

This paper is not intended to be the definitive reference of how PL/SQL should be written, nor is it intended as an instruction manual. There are many who will disagree with much of what is presented here. It is, rather, intended as a discussion document from which others can learn and develop their own methodology. There is no single right way of writing PL/SQL code, although some ways may be more right than others and some are clearly wrong.

What is described here are standards and techniques that have evolved over a period of some four years and have been adopted by the author’s development team. The aims of the team while developing these standards have been to attain a consistent look and feel and to encourage sound program­m­­ing practices without stifling innovation. The standards that have been adopted are not carved in stone, but are instead open to discussion and change through consensus.

The development stand­ards document is a living, breathing entity, subject to constant, but carefully considered, growth and revision. For any stand­ards document to be effective it must be readily available to the development team. To facilitate this and ensure that developers keep abreast of evolving standards, the System Development Standards Manual is available to them on line in a Microsoft Help format (Figure 1). This online manual includes documentation of generic functions and procedures developed in-house, as well as application-specific code modules. Outside the scope of this paper, the System Development Standards Manual also includes standards and procedures not connected with PL/SQL, such as database design standards, version  control  procedures  and  listings  and descriptions of the forms objects that are used to aid rapid development and to enforce GUI standards such as templates, visual attributes and property classes. 

What is PL/SQL

PL/SQL is Oracle’s procedural language extension to the SQL language, with which it is highly integrated. PL/SQL has its origins in Ada, a high level programming language developed for the U.S. Department of Defense, and shares many common features with that language. 

PL/SQL introduces 3GL style procedural logic into the world of declarative logic. Its first incarnation was in 1991 as the “procedural option” for Oracle version 6.0. At about the same time, PL/SQL debuted on the client side in SQL*Forms version 3. On both client and server it was very basic in its functionality, but it was still warmly welcomed by the Oracle development community. From these humble begin­nings PL/SQL has evolved into a power­ful, complex and flexible programming tool. It is very tightly integrated with the SQL language. SQL can be embedded in PL/SQL and PL/SQL functions, with certain restrictions, can be embedded in SQL.

Unfortunately, the client side versions have not kept pace with the server for quite some time. Only PL/SQL versions 1.x have been support­ed, much to the frustration of some forms develop­ers. However, with the recent introduct­ion of Developer/2000 rel­ease 2, the full range of features offered by PL/SQL version 2.3 are now also available on the client.

As with even the most structured 3GL, it is very easy to abuse PL/SQL and produce unintelligible spaghetti code. Whilst this can and does get the job done, eventually it will become a maintenance programmer’s nightmare. One of the primary objectives in defining programming standards and practices is to avoid this undesirable, and potentially costly, mess.

What’s in a Name

Naming conventions are an integral part of good programming standards. The simple rule is to use meaningful names at every level. Abbreviations should be used only when their meaning is totally clear or in cases where the abbreviation is in common usage and universally recognised and understood. Cryptic abbre­viations should be avoided completely, especially since the limitations imposed on name lengths by some languages, which forced these upon us, do not apply in PL/SQL. In the System Development Standards Manual a list of acceptable abbreviations, some of which are industry specific, is available for developer reference.

 

 

function customer_name(p_customer_id number)

 return varchar2

is

 cursor cur_customer_name is

   select customer_name

     from customer

    where customer_id = p_customer_id;

 

v_return_value customer.customer_name%type;

 

begin

  open cur_customer_name;

    fetch cur_customer_name

     into v_return_value;

  close cur_customer_name;

  return v_return_value;

end;

                  

 

FUNCTION Customer_Name(p_Customer_Id NUMBER)

 RETURN VARCHAR2

IS

 CURSOR cur_Customer_Name IS

   SELECT customer_name

     FROM customer

    WHERE customer_id = p_Customer_Id;

 

 v_Return_Value customer.customer_name%TYPE;

 

BEGIN

  OPEN cur_Customer_Name;

    FETCH cur_Customer_Name

     INTO v_Return_Value;

  CLOSE cur_Customer_Name;

  RETURN v_Return_Value;

END;

Figure 1 - Example of Case Usage

 

Packages should group related procedures and functions and their names should indicate that relationship.

û

Procedure and function names should be descriptive of the purpose of the program unit. Within these program units, the names of elements such as parameters, variables and cursors should reflect their usage, or the real world object that they represent. Following these rules results in code that is easy to read and understand. Also, much of the time, it will result in less work, because the code becomes, to a great extent, self-documenting.

ü

These same basic principles should also be applied when naming database tables and columns. More and more users are being provided with the tools and the knowledge to browse the database themselves. It therefore becomes increasingly important to use names that are meaningful to the average application user.

 

 

Prefixes

TYPE

BAD

GOOD

PACKAGE

PKG0001

Customer_Pkg

PROCEDURE

IINSREC

Insert_Record

FUNCTION

CSTNM

Customer_Name

PARAMETER

custid

p_Customer_Id

VARIABLE

rv

t_Return_Value

CURSOR

cur1

cur_Customer_Name

TABLE

REVRATE

REVENUE_RATE

COLUMN

CUSTID

CUSTOMER_ID

Figure 2  -  Examples of Abbreviations

The use of prefixes to identify the various elements of PL/SQL assists greatly in making code more readable and more easily understood. The table in Figure 3 lists prefixes that not only readily identify the element as a variable, constant cursor, etc., but also indicate where it is declared. When examining code written by another, or even when re-visiting your own code after a period, this can save a lot of time and trouble. It is immediately obvious, for example, that a cursor named pscur_Customer has been declared in a package specification, or that one called cur_Customer is declared locally, and no time is lost searching  elsewhere.

 

 

PREFIX

TYPE

Parent Object

p_

Parameter

Function/Procedure

v_

Variable

Function/Procedure

c_

Constant

Function/Procedure

cur_

Cursor

Function/Procedure

e_

Exception

Function/Procedure

psv_

Variable

Package Specification

psc_

Constant

Package Specification

pscur_

Cursor

Package Specification

pv_

Variable

Package Body

pc_

Constant

Package Body

pcur_

Cursor

Package Body

Figure 3 -  Sample Prefix Reference

 

Case

Another aid to readability of code is the use of upper, lower and title (mixed) case as illustrated in the two examples in Figure 4.

Clearly, the second example is easier to read and comprehend. Each element of the code is quickly and easily identified by its prefix, where applicable, and the case (lower, upper or title) in which it appears. Not only is the type of element immediately identifiable, but also its source.  The precise convention adopted is really of no great importance. What is important is that the convention is consistently followed by every member of the development team throughout the life cycle of a project.  This has been found to be the most difficult of our stand­ards to enforce, but since our developers have learned to discip­line themselves to follow the convention there has been univer­sal agreement that the small add­itional effort is well worth while.

The case conventions implemented by the author’s develop­ment team are shown in Figure 5.

 

TYPE

CaSE

EXAMPLE

SQL and PL/SQL Keywords

UPPER

SELECT WHERE FROM

BEGIN END IF  ELSE

SQL and PL/SQL and forms built in functions, procedures and variables.

lower

instr decode

system.cursor_item

Parameters

Title

p_Customer_Id

Developer- defined Functions and Procedures

Title

Customer_Name

Developer- defined variables, constants, etc.

Title

t_Return_Value

Cursors

Title

cur_Customer_Name

Table names

lower

customer

Column names

lower

customer_id

Figure 5  -  Sample of Case Conventions

Layout

Another component of standards that ensures the consistency and readability of PL/SQL code is layout and, in particular, indentation. There is probably universal agreement that indentation is necessary and desirable. The question is how deeply to indent and when. The trick is to find a level of indentation that allows easy reading whilst still keeping the code horizontally compact. Too little indentation is almost as bad as none at all, too much and the code can scroll off the page to the right. We have found that a two-character indentation is sufficient to achieve a layout that is pleasing to the eye whilst not introducing excessive white space. There are, however, exceptions to this rule.

·         In package specifications and package bodies, each function, procedure or other declaration is indented only one space.

·         In the declaration section of functions and procedures, declarations are indented only one space.

·         Exception clauses are at the same indentation as the BEGIN/END of the block.

General

Some other standards adopted by the author’s development team are:

·         Lists, such as columns in an SQL query and parameter lists, shall have only one element per line and the comma separating elements shall precede the name of the element.

·         In SQL DML, key words and data elements shall be centre justified.

·         In declarations, datatypes shall be left justified to the beginning of the rightmost datatype.

·         Anchored declarations (e.g., %TYPE) shall be used wherever applicable.

·         Explicit cursors shall normally be used rather than implicit cursors. Exceptions, which are rare, require the approval of a team lead.

·         Functions shall have only one RETURN statement.

·         RETURN shall not normally be used to summarily end execution of a procedure.

·         The use of GOTO is discouraged.

Modularisation

Breaking PL/SQL programs down into smaller units has a number of advantages. The first, again, is readability and ease of understanding. A program may be broken out into a number of functions and procedures which appear only as calls in the main program. This results in un-cluttered code which is easy to read and understand. The reader easily discovers what the program is intended to achieve and only has to delve deeper when necessary.

A second advantage of modularisation is that a collection of re-usable code is built, resulting in greater productivity and eliminating the drudgery of re-typing the same code again and again. This re-usable code can also be made available to other developers on the team, who may use a component “as is” or modify it to meet their own needs, thus increasing the efficiency of the entire team.

PACKAGE Order_Totals IS

 PROCEDURE Year_End(p_Customer_Id    IN     customer.customer_id%TYPE

                   ,p_Year_End       IN     DATE

                   ,p_Customer_Name     OUT customer.customer_name%TYPE

                   ,p_Total_Orders      OUT NUMBER

                   ,p_Total_Amount      OUT NUMBER

                   ,p_Error_Level       OUT VARCHAR2(1)

                   ,p_Error_Text        OUT VARCHAR2(255));

 

END Order_Totals;

 


PACKAGE BODY Order_Totals IS

 PROCEDURE Year_End(p_Customer_Id    IN     customer.customer_id%TYPE

                   ,p_Year_End       IN     DATE

                   ,p_Customer_Name     OUT customer.customer_name%TYPE

                   ,p_Total_Orders      OUT NUMBER

                   ,p_Total_Amount      OUT NUMBER

                   ,p_Error_Level       OUT VARCHAR2(1)

                   ,p_Error_Text        OUT VARCHAR2(255))

 IS

  v_Customer_Id   customer.customer_id%TYPE;      -- variables not used, shown

  v_Customer_Name customer.customer_name%TYPE;    -- for illustration only

  v_Year          VARCHAR2(4) := to_char(p_Year_End,’YYYY’);

 

  CURSOR cur_Order_Totals IS

    SELECT sum(1)

          ,sum(amount)

      FROM customer

     WHERE customer_id = p_Customer_Id

       AND to_char(order_date,’YYYY’) = v_Year;

 

 BEGIN

 

   p_Customer_Name := Customer_Pkg.Id_To_Name(p_Customer_Id);

 

   OPEN cur_Order_Totals;

     FETCH cur_Order_Totals

      INTO p_Total_Orders

          ,p_Total_Amount;

   CLOSE cur_Order_Totals;

 END;

 EXCEPTION

   WHEN OTHERS THEN

     p_Error_Level := ‘E’;

     p_Error_Text  := substr(sqlerrm,1,255);

 END Year_End;

END Order_Totals;

 Figure 6 - Example of Layout

ç Exception clause to return error

   to non-Oracle calling procedure

Single ì

Space

Indent

 

Parameters ì

aligned.

Commas to

the left.

 

Two    ì

Space

Indent

Fetch Name From Table Package

í Comment unnecessary

 

ç SQL query,

   centre justified

The author’s development team holds regular meetings to keep in touch with what the team as a whole is doing. During these sessions there is discussion about what each developer is currently working on and peer reviews of code. This is essential to maintain the level of communication necessary for successful code sharing. Possible common modules are identified and, if necessary, modified to make them more generic. In some
cases they will be broken down into client and server modules to conform to a strict rule that all SQL resides on the server. These modules are then migrated to the appropriate place, either a client-side library or a database package.

Single ì

Space

Indent

Packages

 

 

 

Type Declarations

aligned left

í

Packages should be used to group functions, procedures and other PL/SQL objects that are logically related. On the client, this might be all the procedures used to control the behaviour of a forms toolbar. On the server, good examples are the functions and procedures pertaining to
a particular table. This allows for the management of multiple objects as if they were one, reducing considerably the time and effort involved. The purpose of each object and descriptions of the parameters and return values, where applicable, should be documented in the package specification. A developer should be able to use the function or procedure without the need to see the underlying code. Packages also support overloaded objects, which are useful and powerful features of PL/SQL.

Functions

Functions are probably the most potent element of PL/SQL. Because they return a value, they can be used in place of an expression with the same datatype in a PL/SQL statement. A function must have a return clause, one or more return statements and a return value.

Functions should normally have only IN parameters and a return value. A function may have OUT parameters, but if it has them it should, except in rare cases, probably be converted into a procedure. The syntax in the calling module can be quite confusing when only a single return value is normal and expected. Functions that are to be embedded in SQL must not have OUT parameters.

Functions must return a value, even if that value is null. However, functions should have one¾and only one¾return statement. Multiple return statements can lead to confusion, especially if they are nested deep in conditional logic. There is also the possibility that none of the conditions are met and therefore no return statement is executed, resulting in a “Function returned without value” error. Using multiple return statements is in any case an example of inelegant and poorly-structured coding.

Functions can be embedded in SQL, for example to order by a foreign key lookup value. However, functions should only be used in this way if there is no alternative method of achieving the desired result. Alternatives such as joins or sub-queries will almost always be more efficient. If functions are to be embedded in SQL they should not handle exceptions. This should be left to the SQL engine, which will report the error in the same way as any other error in an SQL statement.

Procedures

Procedures differ from functions in that they are standalone executable statements and have no return clause.

Procedures may have IN, OUT and IN/OUT parameters. IN/OUT parameters should be used with care. If, for example, they are used as a workaround to directly populate  forms text items, unless care is taken with error handling, the result could easily populate text items with garbage.

Procedures may have one or more exception handlers. If  a procedure is intended to be called only from an Oracle module, such as forms or reports, exception handing may be omitted and any exception caught by the calling module. The standard for the author’s development team is to assume that non-Oracle modules will call procedures. Exceptions are trapped and information about them is passed back to the calling module in an OUT parameter.

Procedures may have one or more return statements. When a return statement is executed it summarily ends execution of the code and exits the procedure. Except in special cases, return statements should be avoided. The use of return in procedures is usually indicative of poorly written and unstructured code.

PL/SQL Control Structures

PL/SQL has a number of control structures, including:

·         Conditional controls.

·         Sequential controls.

·         Iterative or loop controls.

·         Exception or error controls.

It is these controls, used singly or together, that allow the PL/SQL developer to direct the flow of execution through the program.

Conditional Controls

There are three levels of conditional controls or IF statements, which are similar to those found in most 3GL languages.  These controls are:

1.        IF....THEN....END IF

2.        IF....THEN...ELSE....END IF

3.        IF....THEN...ELSIF....THEN....ELSE....END IF

The statements between the conditions should always be indented and appropriate control should always be used. For instance, in the case of multiple IF....THEN....END IF it is probably appropriate to use IF...THEN..ELSE...END IF or IF....THEN...ELSIF....THEN....ELSE....END IF. 

Multiple IF constructs may be nested and each logical level should also be indented. Again, consideration should be given as to whether the correct control is being used. Often nested IF controls can be simplified by the use of AND or OR. Some examples are shown in Figure 7.

IF a = b THEN

 IF x = y THEN

   Do_Something;

 END IF;

END IF;

 


IF a=b AND x=y THEN

  Do_Something;

END IF;

 


IF a = b THEN

  Do_Something;

END IF;

IF x = y THEN

  do_something_else;

END IF;

 

IF a = b THEN

  Do_Something;

ELSIF x = y THEN

  Do_Something_Else;

END IF;

Figure 7 - Examples of Conditional Controls

û

Sequential Controls

PL/SQL offers two sequential controls, GOTO and NULL.

GOTO branches unconditionally to a named label. The label name has to be enclosed in double angle brackets, as in <<my_label>>. The use of GOTO should be avoided at almost any cost. It is the start of the slippery slope that ends in unstructured, spaghetti code that is difficult, if not impossible, to understand. In his book Oracle PL/SQL Programming1 Steven Feuerstein writes “PL/SQL provides so many different control constructs and modularization techniques that you can almost always find a better way to do something than with a GOTO.”

NULL is used simply to tell PL/SQL to do nothing. It might for instance be used in an exception section to ignore a particular exception condition.

ü

û

ü

 

Iterative or Loop Controls

There are four types of iterative controls in PL/SQL.

1.        The simple, infinite loop.

2.        The WHILE loop.

3.        The numeric FOR loop

4.        The cursor FOR loop.

The author avoids using the simple loop, preferring to use a WHILE or FOR loop which has a definite start and end point. In most cases one of these is better suited for the task and may even result in less coding. There are two methods for exiting a simple loop, EXIT and EXIT WHEN. The EXIT statement should be avoided. EXIT has to be within an IF-THEN construct and EXIT WHEN provides the same conditional logic. FOR loops should always be allowed to complete the specified iterations. EXIT and EXIT WHEN should never be used to jump out of a FOR loop. A RETURN statement must never be used to exit any loop.

<<outer_loop>>

LOOP

  <<inner_loop>>

  LOOP

    my_var := Do_Something;

    EXIT inner_loop WHEN my_var = TRUE;

  END inner_loop;

  my_var2 := Do_Something_Different

  EXIT outer_loop WHEN my_var2 = TRUE;

EXIT outer_loop;

Figure 8 - Examples of Loops

If a loop has a lot of code within it, or if loops are nested, labels may be used to clearly identify the beginning and end of each loop. These labels are created using double angle brackets and can then be used in the END and EXIT WHEN statements of the loop, as shown in Figure 8.

Exception or Error Controls

One of the things that PL/SQL has inherited from Ada is very powerful exception or error handling. There are four types of exception.

1.        Named system exceptions.

2.        Named developer-defined exceptions.

3.        Unnamed system exceptions.

4.        Unnamed developer-defined exceptions.
 (Raise application error)

Excluding functions which are to be embedded in SQL as mentioned earlier, PL/SQL modules should normally contain one or more exception handling sections. PL/SQL makes no distinction between system exceptions and developer-defined exceptions and they are handled in the same way by the exception handling section.

When declaring named developer-defined exceptions, care should be taken not to use the same name as a named system exception. The code will compile without error, but when a runtime error occurs the named system exception will take precedence.

There should always be a WHEN OTHERS clause at the highest level of exception handling. When a PL/SQL module is called from, say, a procedure in Oracle forms, this could be in the calling procedure with the exception handled there. The author’s development team have standardised by handling all exceptions within each module. The error exception is then passed back to the calling module in OUT parameters. This allows for calling PL/SQL modules from non-Oracle sources.

Documentation

Comments

Comments should be succinct to avoid the situation where it becomes difficult to find the code among the comments. As mentioned earlier, using meaningful names can, to a great extent, make the code self-documenting, reducing both clutter and the effort involved. Only if you are doing something unusual or very complex should there be a need for extensive commenting. Comments should be indented to the same level as the code to which they refer, not positioned on the left margin. This increases readability as the reader’s eye is not drawn away from the flow of the code.

There are two types of comment. Multiline comments are enclosed between /* and */, and single line comments are preceded by two dashes. A multiline comment cannot be contained within another multiline comment. For this reason it is probably better to use the single line method within the body of code, as it allows for whole sections of code to be commented out for testing and de-bugging using /* and */.

Headers

Module headers may contain information about what the module is intended to do, the original author, the date it was created and its modification history. Exactly how much detail should be included varies according to what other documentation exists. If modules are generated from Designer/2000 or a similar product or version control software is utilised, the header documentation could be minimal. However, if this is not the case it needs to be more comprehensive, as shown in Figure 9.

/****************************************************************************************************
** Procedure to calculate the sum of direct and indirect costs for all work requests for a project
**
** Author: J.C. Lennon 7-APR-1998
**
** Modification History
**
** Modified By         Date     Remarks
**
**
**
**
**
*****************************************************************************************************
*/

Figure 9 - Example of Header

Conclusion

The maxim that “if a job’s worth doing it’s worth doing well” can well be applied to PL/SQL programming. If we get it right now it will be easier for those who follow. Most of us have probably experienced trying to understand poorly written, sparsely documented legacy code. Let’s not fall into the same trap.

But who knows? We may be dinosaurs already. Oracle CEO Larry Ellison said in an interview on April 3, 1998, "We think apps should be developed through data models and process models, not through coding of any kind."

Bibliography

1.        Feuerstein, Steven, Oracle PL/SQL Programming (O’Reilly and Associates Inc., 1995).

Acknowledgments

Janice Carlson for her invaluable assistance.

About the Author

John Lennon is Project Manager with Dulcian, Inc.  John has been working with Oracle products for a over a decade. He has presented at international and regional Oracle users’ conferences, including previous ODTUG conferences, and has had articles published in technical journals in the United States and Great Britain.

Las Vegas NV 89103    Web site http://members.aol.com