Search the Catalog
Oracle PL/SQL Programming, 2nd Edition

Oracle PL/SQL Programming, 2nd Edition

By Steven Feuerstein with Bill Pribyl
2nd Edition September 1997
1-56592-335-9, Order Number: 3359
1028 pages, $46.95, Includes diskette

Chapter 3.
Effective Coding Style

In this chapter:
Fundamentals of Effective Layout
Formatting SQL Statements
Formatting Control Structures
Formatting PL/SQL Blocks
Formatting Packages
Using Comments Effectively
Documenting the Entire Package

You can learn everything about a programming language--its syntax, high-performance tips, and advanced features--and still write programs that are virtually unreadable, hard to maintain, and devilishly difficult to debug--even by you, the author. You can be very smart and very clever, and yet develop applications that obscure your talent and accomplishments.

This chapter addresses the "look-and-feel" of your code--the aesthetic aspect of programming. I am sure that you have all experienced the pleasure of reading well-structured and well-formatted code. You have also probably experienced a pang of jealousy at that programmer's style and effort, wondering where she or he found the time to do it right. Developers always experience a feeling of intense pride and satisfaction from carefully and artfully designing the visual layout of their code. Yet few of us take the time to develop a style and use it consistently in our work.

Of course, the impact of a coding style goes well beyond the personal satisfaction of any individual. A consistent, predictable approach to building programs makes it easier to debug and maintain that code. If everyone takes her own approach to structuring, documenting, and naming her code, every program becomes its own little pool of quicksand. It is virtually impossible for another person to put in a foot and test the water (find the source of a problem, analyze dependencies, etc.) without being pulled under.

I discuss the elements of an effective coding style in the PL/SQL language at this juncture, before we get to any code, for two reasons:

Views on effective coding style are often religious in nature (similar to programmers' ideas on the use of GOTO)--that is, based largely on faith instead of rationality. I don't expect you to agree with everything in this chapter (actually, in a number of places I suggest several alternatives). Such unanimity is unrealistic and unnecessary. Rather, I hope that this chapter gets you thinking about the style in your own programming.

Fundamentals of Effective Layout

There is really just one fundamental objective of code layout:

Reveal and reinforce the logical structure of your program.

You could come up with ways of writing your code that are very pleasing to the eye, but doing so is less important than choosing a format that shows the structure and intent of the program.

It is easy to address the topic of effective code layout for PL/SQL because it is such a well structured language. It benefits greatly from Ada's block structure approach. Each control construct, such as IF and LOOP, has its own terminator keyword, such as END IF and END LOOP. Each logical block of code has an explicit beginning statement and an associated ending statement. This consistent and natural block style lends itself easily and naturally to standards for indentation and whitespace, which further expose the structure of the code.

Revealing Logical Structure with Indentation

Indentation is one of the most common and effective techniques used to display a program's logic via format. As illustrated in the following examples, programs that are indented are easier to read than those that are not indented, although programs that use excessive indentation are not much more readable than unindented programs. Here is an unindented IF statement:

IF to_number(the_value) > 22
THEN
IF max_totals = 0
THEN
calc_totals;
ELSE
WHILE more_data
LOOP
analyze_results;
END LOOP;
END IF;
END IF;

The lack of indentation in this example makes it very difficult to pick out the statements that go with each clause in the IF statement. Some developers, unfortunately, go to the opposite extreme and use six or more spaces for indentation. (This usually occurs by relying on the tab key, which offers "logical" indentation--a tab can be equivalent to three spaces in one editor and eight in another. I suggest avoiding the use of tabs altogether.)

I have found that a three-space indentation not only adequately reveals the logical structure of the code but also keeps the statements close enough together to read comfortably. And, with deeply nested structures, you won't run off the right margin as quickly! Here is the three-space indented version of the previous nested IF statement:

IF to_number(the_value) > 22  
THEN 
   IF max_totals = 0
   THEN
      calc_totals;
   ELSE
      WHILE more_data
      LOOP
         analyze_results;
      END LOOP;
   END IF;
END IF;

The rest of this chapter presents specific techniques that I have found to be essential in writing attractive, readable code that reveals the logic of my programs.

Using Case to Aid Readability

PL/SQL code is made up of many different components: variables, form items, report fields, procedures, functions, loops, declarations, control elements, etc. But they break down roughly into two types of text: reserved words and application-specific names or identifiers.

Reserved words are those names of language elements that are reserved by PL/SQL and have a special meaning for the compiler. Some examples of reserved words in PL/SQL are:

WHILE
IF
BEGIN
TO_CHAR

Application-specific identifiers are the names that you, the programmer, give to data and program structures that are specific to your application and that vary from system to system.

The compiler treats these two kinds of text very differently. You can improve the readability of your code greatly by reflecting this difference in the way the text is displayed. Many developers make no distinction between reserved words and application-specific identifiers. Consider the following lines of code:

if to_number(the_value)>22 and num1 between lval and hval 
then 
   newval := 100; 
elsif to_number(the_value) < 1 
then 
   calc_tots(to_date('12-jan-95')); 
else 
   clear_vals; 
end if;

While the use of indentation makes it easier to follow the logical flow of the IF statement, all the words in the statements tend to blend together. It is difficult to separate the reserved words and the application identifiers in this code. Changing entirely to uppercase also will not improve matters. Indiscriminate, albeit consistent, use of upper- or lowercase for your code reduces its readability. The distinction between reserved words and application-specific identifiers is ignored in the formatting. This translates into a loss of information and comprehension for a developer.

The UPPER-lower Style

You can easily solve this problem by adopting a guideline for using a mix of upper- and lowercase to your code. I have recoded my previous example below, this time using the UPPER-lower style: all reserved words are written in UPPERCASE and all application names are kept in lowercase:

IF to_number(the_value) > 22 AND 
   num1 BETWEEN lval AND hval 
THEN 
   newval := 100;
ELSIF TO_NUMBER (the_value) < 1
THEN 
   calc_tots (TO_DATE ('12-jan-95'));
ELSE 
   clear_vals;
END IF;

Using a mixture of upper- and lowercase words increases the readability of the code by giving a sense of dimension to the code. The eye can more easily cruise over the text and pick the different syntactical elements of each statement. The uppercase words act as signposts directing the activity in the code. You can focus quickly on the lowercase words for the application-specific content. Consistent use of this method makes the program listings more attractive and accessible at a glance.

Formatting Single Statements

Most of your code consists of individual statements, such as assignments, calls to modules, and declarations. A consistent approach to formatting and grouping such statements will improve the readability of your program as a whole. This section suggests some guidelines.

Use at most one statement per line

As we discussed in Chapter 2, PL/SQL Language Fundamentals, PL/SQL uses the semicolon (;) as the logical terminator for a statement. As a result you can have more than one statement on a line and you can continue a single executable statement over more than one line. You will sometimes be tempted to place several statements on a single line, particularly if they are very simple. Consider the following line:

new_id := 15; calc_total (new_id); max_dollars := 105 * sales_adj; 

It is very difficult to pick out the individual statements in this line, in addition to the fact that a procedure is called in the middle of the line. By placing each statement on its own line you mirror the complexity of a program--the simple lines look simple and the complex statements look complex--and reinforce the top-to-bottom logic of the program:

new_id := 15; 
calc_total (new_id); 
max_dollars := 105 * sales_adj; 

You can scan the left margin (which will move left and right depending on the logic and corresponding indentation) and know that you are reviewing all the lines of code.

Use whitespace inside a statement

You can use all the indentation and blank lines you want to reveal the logic of a program and still end up with some very dense and unreadable code. It is also important to employ whitespace within a single line to make that one statement more comprehensible. Here are two general rules I employ in my code:

Formatting Your Declarations

The declaration section declares the local variables and other structures to be in your PL/SQL block. This section comes right at the top of the block, so it sets the first impression for the rest of the program. If the declaration section has no apparent order and is poorly formatted, it is unlikely that anything else in that program will be easily understood.

The declaration section in PL/SQL can contain many different kinds of declarations: simple, scalar variables; complex data structures like records and tables; exceptions; even entire subprograms which exist only in that program.

The following sections give some guidelines for creating your declaration statements.

Place one declaration on each line

You will be particularly tempted to "double up" declarations on a single line because, in general, declarations are very short in length. Resist that temptation! Which of the following sets of declarations would you prefer to try to understand at a glance?

DECLARE
   comp_type VARCHAR2(3); right_now DATE := SYSDATE; month_num INTEGER; 

or:

DECLARE
   comp_type VARCHAR2(3); 
   right_now DATE := SYSDATE; 
   month_num INTEGER; 

Ignore alignment for declarations

Many programmers like to align their declarations--for example:

DECLARE
   company_name      VARCHAR2(30);
   company_id        INTEGER;
 
   employee_name     VARCHAR2(60);
   hire_date         DATE;
   termination_date  DATE;
 
   min_value         NUMBER;

I am not convinced of the value of declaration alignment. Although alignment makes it easier to scan down the datatypes, the datatype isn't nearly as important as the identifier, which is already left-justified. A commitment to alignment also raises all kinds of questions that consume a developer's time and thought processes: If you have one long variable name, do you have to move all the other datatypes out to match that datatype declaration? What about when you add a new, very long declaration into an existing section? Do you have to go back and add a tab or two to the existing declarations?

The elegance of alignment also breaks down when you include comments above individual declarations, as shown in the following example:

DECLARE
   company_name      VARCHAR2(30);
   /* Primary key into company table */
   company_id        INTEGER;
 
   employee_name     VARCHAR2(60);
   /* Date hired; must be no greater than today's date. */
   hire_date         DATE;
   termination_date  DATE;
 
   min_value         NUMBER;

When the comment text cuts across the vast spaces of the alignment tabs, it just makes the datatype look isolated from its identifier.

I believe that you are better off ignoring alignment for declarations.[1] Keep the elements of the declaration (datatype and default value) close to the identifier.

Formatting Multiline Statements

Because a statement is terminated by a semicolon (;) rather than by the physical end of the line, statements can be continued onto additional lines without any specific continuation symbol or operator. This makes it very easy to spread a statement across more than one line, but it can also make it difficult to read across these lines.

Here are some examples of multiline statements that are hard to follow:

IF total_sales < maximum_sales AND company_type = 'NEW' AND (override
= 'Y' OR total_company_revenue < planned_revenue (SYSDATE))
THEN
   accept_order;
END IF;
 
generate_company_statistics (company_id, last_year_date
, rollup_type, total, average, variance, budgeted, next_year_plan);
 
total_sales := product_sales (company_id) + admin_cutbacks * 
.5 - overhead - golden_parachutes;

The format of these continuation lines highlights a key question: How do you best break up a complex expression so the different parts can be read clearly, but still be connected to the statement as a whole? The following guidelines respond to this question and produce much cleaner code.

Use indentation to offset all continuation lines under the first line.

This is the most important guideline. The best way to identify continuation lines is to use indentation to logically subsume those lines under the main or first line of the statement. The following call to generate_company_statistics is obscured because the continuation line butts right up against the left margin with the module name:

generate_company_statistics (company_id, last_year_date,
rollup_type, total, average, variance, budgeted, next_year_plan);

If I indent the continuation line, the relationship of the second line to the first becomes clear:

generate_company_statistics (company_id, last_year_date,
   rollup_type, total, average, variance, budgeted, next_year_plan);

This attempt to recode, however, shows that simply adding an indentation isn't always enough. While it is clear that the line starting with rollup_type "belongs" to the previous line, the relationship of the text on the continuation line to that of the first line is unclear. We need more than a simple call to "Indent." There are several possible approaches:

Indent module-call continuation lines to align all parameters vertically.
You can place a single parameter on each line for maximum clarity, or include more than one parameter on a line--as long as they are properly indented. You can even break up the parameters so that related parameters go together on separate lines. If the name of the procedure is long and results in pushing the alignment column for parameters too far to the right, start the entire parameter list on the next line (indented) and then align all parameters against that second line. Here are some examples illustrating these rules:

gen_stats (company_id, last_year_date, rollup_type, total, average, variance, budgeted, next_year_plan);   gen_stats (company_id, last_year_date, rollup_type, total, average, variance, budgeted, next_year_plan);   gen_stats (company_id, last_year_date, rollup_type, total, average, variance, budgeted, next_year_plan);

I prefer the third alternative, in which all parameters are moved to the line following the name of the module. You can then place multiple parameters on the same line or place one parameter on each line, but the indentation is always and only the standard three spaces in from the start of the module name.

Make it very obvious that a statement is continued.
If a statement is not going to fit onto a single line, break up the statement so that it is quite obvious, with the most casual glance, that the first line could not possibly finish the statement. The following examples highlight this approach:

  • The IN statement of a loop clearly needs its range:
  • FOR month_index IN first_month .. last_month LOOP ...

  • An assignment could not possibly end with a "+":
  • q1_sales := month1_sales + month2_sales + month3_sales;

  • The last comma in the first line of parameters indicates that other parameters follow:
  • generate_company_statistics (company_id, last_year_date, rollup_type, total, average, variance, budgeted, next_year_plan);

    Formatting SQL Statements

    Because PL/SQL is an extension to the SQL language, you can place SQL statements directly in your PL/SQL programs. You can also define cursors based on SELECT statements. This section summarizes my suggestions for formatting SQL statements and cursors for maximum readability.

    PL/SQL supports the use of four SQL DML (Data Manipulation Language) statements: INSERT, UPDATE, DELETE, and SELECT. Each of these statements is composed of a series of "clauses," as in the WHERE clause and the ORDER BY clause. SQL statements can be very complex, to say the least. Without a consistent approach to indentation and alignment inside these statements, you can end up with a real mess. I have found the following guidelines useful:

    Right-align the reserved words for the clauses against the DML statement.
    I recommend that you visually separate the SQL reserved words which identify the separate clauses from the application-specific column and table names. The following table shows how I use right-alignment on the reserved words to create a vertical border between them and the rest of the SQL statement:

    SELECT

    INSERT

    UPDATE

    DELETE

    SELECT
     
      FROM
     
     WHERE
       AND
        OR
     
     GROUP BY
     
    HAVING
       AND
        OR
     
     ORDER BY
    
    INSERT INTO
         VALUES
     
    INSERT INTO
         SELECT
           FROM
          WHERE
    
    UPDATE
       SET
     WHERE
    
    DELETE
      FROM
     WHERE
    

    Here are some examples of this format in use:

    SELECT last_name, first_name FROM employee WHERE department_id = 15 AND hire_date < SYSDATE;   SELECT department_id, SUM (salary) AS total_salary FROM employee GROUP BY department_id ORDER BY total_salary DESC;   INSERT INTO employee (employee_id, ... ) VALUES (105 ... );   DELETE FROM employee WHERE department_id = 15;   UPDATE employee SET hire_date = SYSDATE WHERE hire_date IS NULL AND termination_date IS NULL;

    Yes, I realize that the GROUP BY and ORDER BY keywords aren't exactly right-aligned to SELECT, but at least the primary words (GROUP and ORDER) are aligned. Notice that within each of the WHERE and HAVING clauses I right-align the AND and OR Boolean connectors under the WHERE keyword.

    This right alignment makes it very easy for me to identify the different clauses of the SQL statement, particularly with extended SELECTs. You might also consider placing a blank line between clauses of longer SQL statements (this is possible in PL/SQL, but is not acceptable in "native" SQL executed in SQL*Plus).

    Don't skimp on the use of line separators.
    Within clauses, such separation makes the SQL statement easier to read. In particular, place each expression of the WHERE clause on its own line, and consider using a separate line for each expression in the select list of a SELECT statement. Place each table in the FROM clause on its own line. Certainly, put each separate assignment in a SET clause of the UPDATE statement on its own line. Here are some illustrations of these guidelines:

    SELECT last_name, C.name, MAX (SH.salary) best_salary_ever FROM employee E, company C, salary_history SH WHERE E.company_id = C.company_id AND E.employee_id = SH.employee_id AND E.hire_date > ADD_MONTHS (SYSDATE, -60);   UPDATE employee SET hire_date = SYSDATE, termination_date = NULL WHERE department_id = 105;

    NOTE: You can place blank lines inside a sql statement when you are coding that sql from within a pl/sql block. You may not, on the other hand, embed white space in sql statements you are executing from the sql*Plus command line.

    SELECT ... select list ...

    FROM employee EMP, company CO, history HIST, bonus,

           profile PROF, sales
     WHERE EMP.company_id = CO.company_id
       AND EMP.employee_id = HIST.employee_id
       AND CO.company_id = SALES.company_id
       AND EMP.employee_id = BONUS.employee_id
       AND CO.company_id = PROF.company_id;
    

    Formatting Control Structures

    The control structures in your program are the most direct representation of the logic needed to implement your specifications. The format of these control structures, therefore, will have a significant impact on the readability of your code.

    Indentation is the most important element of control structure layout. Always keep statements of the same "logical level" at the same indentation level. Let's see what this means for the various control structures of PL/SQL.

    Formatting IF Statements

    This conditional construct comes in three flavors:

    IF <expression>
    END IF;
    
    IF <expression>
    ELSE
    END IF;
    
    IF <expression>
    ELSIF <expression>
    ELSE
    END IF; 
    

    In general, the IF statement is composed of clauses in which there is a Boolean expression or condition and a section of code executed when that condition evaluates to TRUE.

    So if you want to use indentation to reveal the logical structure of the simplest form of the IF statement (IF-END IF), I suggest one of these two styles:

    New Line for THEN

    Same Line for THEN

    IF <expression>
    THEN
       executable_statements;
    END IF;
    
    IF <expression> THEN
       executable_statements
    END IF;
    
    IF <expression>
    THEN
       executable_statements;
    ELSE
       else_executable_statements;
    END IF;
    
    IF <expression> THEN
       executable_statements
    ELSE
       else_executable_statements;
    END IF;
    
    IF <expression1>1
    THEN
       executable_statements1;
     
    ELSIF <expression2>
    THEN
       executable_statements2;
    ...
     
    ELSIF <expressionN>
    THEN
       executable_statementsN;
     
    ELSE
       else_executable_statements;
    END IF;
    
    IF <expression1> THEN
       executable_statements1;
     
    ELSIF <expression2> THEN
       executable_statements2;
    ...
     
    ELSIF <expressionN> THEN
       executable_statementsN;
     
    ELSE
       else_executable_statements;
    END IF;
    

    Notice that in both versions the executable statements are indented three spaces from the column in which the IF and END IF reserved words are found. The only difference between the two formats is the placement of the THEN reserved word. I prefer the new line format, in which the THEN appears on a line by itself after the IF condition. This format provides more whitespace than the other. I could create the whitespace by using a blank, rather than indenting three spaces, but then the executable statements for the IF clause are made distinct from the condition--and they are logically connected. Let's examine some actual code to get a better sense of the differences.

    The following example shows proper IF statement indentation with THEN on the same line:

    IF max_sales > 2000 THEN
       notify_accounting ('over_limit');
       RAISE FORM_TRIGGER_FAILURE;
    END IF;
    

    This code has proper IF statement indentation with THEN on the next line:

    IF max_sales > 2000 
    THEN
       notify_accounting ('over_limit');
       RAISE FORM_TRIGGER_FAILURE;
    END IF;
    

    Formatting Loops

    You are going to be writing many loops in your PL/SQL programs, and they will usually surround some of the most complicated code in your application. For this reason, the format you use to structure your loops will make a critical difference in the overall comprehensibility of your programs.

    PL/SQL offers the following kinds of loops:

    Each loop has a loop boundary (begin and end statements) and a loop body. The loop body should be indented from the boundary (again, I recommend three spaces of indentation).

    As with the IF statement, you can either choose to leave the LOOP reserved word at the end of the line containing the WHILE and FOR statements or place it on the next line. I prefer the latter, because then both the LOOP and END LOOP reserved words appear at the same column position (indentation) in the program.

    Here are my recommendations for formatting your loops:

    LOOP

    executable_statements;

    END LOOP;
    
  • The WHILE loop:
  • WHILE condition LOOP executable_statements; END LOOP;

  • The numeric and cursor FOR loops:
  • FOR for_index IN low_value .. high_value LOOP executable_statements; END LOOP;   FOR record_index IN my_cursor LOOP executable_statements; END LOOP;

    Formatting Exception Handlers

    PL/SQL provides a very powerful facility for dealing with errors. An entirely separate exception section contains one or more "handlers" to trap exceptions and execute code when that exception occurs. Logically, the exception section is structured like a conditional CASE statement (which, by the way, is not supported by PL/SQL).

    As you might expect, the format for the exception section should resemble that of an IF statement. Here is a general example of the exception section:

    EXCEPTION
       WHEN NO_DATA_FOUND
       THEN
          executable_statements1;
     
       WHEN DUP_VAL_ON_INDEX
       THEN
          executable_statements1;
     
    

    ...

    WHEN OTHERS

    THEN

    otherwise_code;

    END;
    

    Instead of an IF or ELSIF keyword, the exception handler uses the word WHEN. In place of a condition (Boolean expression), the WHEN clause lists an exception name followed by a THEN and finally the executable statements for that exception. In place of ELSE, the exception section offers a WHEN OTHERS clause.

    Follow these guidelines:

    Formatting PL/SQL Blocks

    As I've outlined in Chapter 2, every PL/SQL program is structured as a block containing up to four sections:

    The PL/SQL block structure forms the backbone of your code. A consistent formatting style for the block, therefore, is critical. This formatting should make clear these different sections. (See Chapter 15, Procedures and Functions, for more information about the block structure.)

    Consider the following function:

    FUNCTION 
    company_name (company_id_in IN company.company_id%TYPE)    RETURN 
    VARCHAR2 IS cname company.company_id%TYPE; BEGIN
       SELECT name INTO cname FROM company
        WHERE company_id = company_id_in;
       RETURN cname;
    EXCEPTION WHEN NO_DATA_FOUND THEN   RETURN NULL; END;
    

    You know that this program is a function because the first word in the program is FUNCTION. Other than that, however, it is very difficult to follow the structure of this program. Where is the declaration section? Where does the executable section begin and end?

    Here is that same function after we apply some straightforward formatting rules to it:

    FUNCTION company_name (company_id_in IN company.company_id%TYPE)

    RETURN VARCHAR2

    IS
       cname company.company_id%TYPE;
     
    BEGIN
       SELECT name INTO cname FROM company
        WHERE company_id = company_id_in;
       RETURN cname;
     
    EXCEPTION
       WHEN NO_DATA_FOUND 
       THEN
          RETURN NULL;
    END;
    

    Now it is easy to see that the header of the function consists of:

    FUNCTION company_name (company_id_in IN company.company_id%TYPE) 
       RETURN VARCHAR2
    

    The declaration section, which comes after the IS and before the BEGIN, clearly consists of a single declaration of the cname variable. The executable section consists of all the statements after the BEGIN and before the EXCEPTION statement; these are indented in from the BEGIN. Finally, the exception section shows a single specific exception handler and a WHEN OTHERS exception.

    Generally, indent the statements for a given section from the reserved words which initiate the section. You can also include a blank line before each section, as I do above, for the executable section (before BEGIN) and the exception section (before EXCEPTION). I usually place the IS keyword on its own line to clearly differentiate between the header of a module and its declaration section.

    Formatting Packages

    A package is a collection of related objects, including variables, TYPE statements (to define structures for records, tables, and cursors), exceptions, and modules. We have already covered structuring all the different objects which make up a package. Now, let's take a look at how to structure the package itself.

    A package has both a specification and a body. The package specification contains the declarations or definitions of all those objects that are visible outside of the package--the public objects. This means that the objects can be accessed by any account that has been granted EXECUTE authority on the package. The package body contains the implementation of all cursors and modules defined in the specification, and the additional declaration and implementation of all other package objects. If an object, such as a string variable, is declared in the body and not in the package, then any module in the package can reference that variable, but no program outside of the package can see it. That variable is invisible or private to the package.

    The first point to make about the package structure is that all objects declared in the specification exist within the context of the package and so should be indented from the PACKAGE statement itself, as shown below:

    PACKAGE rg_select
    IS
       list_name VARCHAR2(60);
     
       PROCEDURE init_list 
          (item_name_in IN VARCHAR2, 
           fill_action_in IN VARCHAR2 := 'IMMEDIATE');
       PROCEDURE delete_list;
       PROCEDURE clear_list;
     
    END rg_select;
    

    The same is true for the package body. I suggest that you always include a label for the END statement in a package so that you can easily connect up that END with the end of the package as a whole. I place the IS keyword on a new line to set off the first declaration in the package from the name of the package. You could always use a blank line. Notice that I use blank lines in rg_select to segregate different modules which are related by function. I think that logical grouping is always preferable to an arbitrary grouping such as alphabetical order.

    The other important element in formatting a package is the order in which objects are listed in the package. I generally list objects in the order of complexity of their structure, as follows:

    As with simple variable declarations, I sometimes have many different but related objects in my package. If so, I might group those types of objects together. But within that grouping, I still follow the above order.

    Using Comments Effectively

    The object of an effective coding style is to make the program more understandable and maintainable. Most programs will benefit from documentation which explains what is going on inside those programs. There are two forms of code documentation: external and internal. External documentation is descriptive information about a program which is written and stored separately from the program itself. Internal documentation, also known as inline documentation or comments, is placed within the program itself, either at the program level or the statement level. (For an introduction to inline documentation and the types of PL/SQL comments, see the section called "Comments" in Chapter 2.)

    The best kind of internal documentation derives from your programming style. If you apply many of the guidelines in this chapter and throughout this book, you will be able to write code which is, to a great extent, self-documenting. Here are some general tips:

    Do all these things and more, and you will find that you need to write fewer comments to explain your code.

    Reducing the need for comments is important. Few developers make or have the time for extensive documentation in addition to their development efforts, and, more importantly, many comments tend to duplicate the code. This raises a maintenance issue because those comments will have to be changed when the code is changed.

    While it is my hope that after reading this book you will write more self-documenting code, there is little doubt that you will still need to comment your code. The following example shows the use of single- and multiline comments in PL/SQL:

    PROCEDURE calc_totals (company_id IN NUMBER,--The company key
                           total_type IN VARCHAR2--ALL or NET
                          );
     
    /*
    || For every employee hired more than five years ago,
    || give them a bonus and send them an e-mail notification.
    */
    FOR emp_rec IN emp_cur (ADD_MONTHS (SYSDATE, -60))
    LOOP
       apply_bonus (emp_rec.employee_id);
       send_notification (emp_rec.employee_id);
    END LOOP;
     
    -- IF :SYSTEM.FORM_STATUS = 'CHANGED' THEN COMMIT; END IF;
     
    FUNCTION display_user 
       (user_id IN NUMBER /* Must be valid ID */, user_type IN VARCHAR2)
    

    The first example uses the single-line comment syntax to include endline descriptions for each parameter in the procedure specification. The second example uses a multiline comment to explain the purpose of the FOR loop. The third example uses the double-hyphen to comment out a whole line of code. The last example embeds a comment in the middle of a line of code using the block comment syntax.

    These two types of comments offer the developer flexibility in how to provide inline documentation. The rest of this section offers guidelines for writing effective comments in your PL/SQL programs.

    Comment As You Code

    It is very difficult to make time to document your code after you have finished writing your program. Psychologically, you want to (and often need to) move on to the next programming challenge after you get a program working.

    You may also have a harder time writing your comments once you have put some distance between your brain cells and those lines of code. Why exactly did you write the loop that way? Where precisely is the value of that global variable set? Unless you have total recall, post-development documentation can be a real challenge.

    The last and perhaps most important reason to write your comments as you write your code is that the resulting code will have fewer bugs and (independent of the comments themselves) be easier to understand.

    When you write a comment you (theoretically) explain what your code is meant to accomplish. If you find it difficult to come up with that explanation, there is a good chance that you lack a full understanding of what the program does or should do.

    The effort that you make to come up with the right comment will certainly improve your comprehension, and may also result in code correction. In this sense, good inline documentation can be as beneficial as a review of your code by a peer. In both cases, the explanation will reveal important information about your program.

    Explain the Why--Not the How--of Your Program

    What do you think of the comments in the following Oracle Forms trigger code?

    -- If the total compensation is more than the maximum...
    IF :employee.total_comp > maximum_salary
    THEN
       -- Inform the user of the problem.
       MESSAGE ('Total compensation exceeds maximum. Please re-enter!');
     
       -- Reset the counter to zero.
       :employee.comp_counter := 0;
     
       -- Raise the exception to stop trigger processing.
       RAISE FORM_TRIGGER_FAILURE;
    END IF;
    

    None of these comments add anything to the comprehension of the code. Each comment simply restates the line of code, which in most cases is self-explanatory.

    Avoid adding comments simply so that you can say, "Yes, I documented my code!" Rely as much as possible on the structure and layout of the code itself to express the meaning of the program. Reserve your comments to explain the Why of your code: What business rule is it meant to implement? Why did you need to implement a certain requirement in a certain way?

    In addition, use comments to translate internal, computer-language terminology into something meaningful for the application. Suppose you are using Oracle Forms GLOBAL variables to keep track of a list of names entered. Does the following comment explain the purpose of the code or simply restate what the code is doing?

    /* Set the number of elements to zero. */
    :GLOBAL.num_elements := 0;
    

    Once again, the comment adds no value. Does the next comment offer additional information?

    /* Empty the list of names. */
    :GLOBAL.num_elements := 0;
    

    This comment actually explains the purpose of the assignment of the global to zero. By setting the number of elements to zero, I will have effectively emptied the list. This comment has translated the "computer lingo" into a description of the effect of the statement. Of course, you would be even better off hiding the fact that you use this particular global variable to empty a list and instead build a procedure as follows:

    PROCEDURE empty_list IS

    BEGIN

    :GLOBAL.num_elements := 0;

    END;
    

    Then to empty a list you would not need any comment at all. You could simply include the statement:

    empty_list;
    

    and the meaning would be perfectly clear.

    Make Comments Easy to Enter and Maintain

    You shouldn't spend a lot of time formatting your comments. You need to develop a style that is clean and easy to read, but also easy to maintain. When you have to change a comment, you shouldn't have to reformat every line in the comment. Lots of fancy formatting is a good indication that you have a high-maintenance documentation style. The following block comment is a maintenance nightmare:

    /*
    ===========================================================
    | Parameter          Description                          |
    |                                                         |
    | company_id         The primary key to company           |
    | start_date         Start date used for date range       |
    | end_date           End date for date range              |
    ===========================================================
    */
    

    The right-justified vertical lines and column formatting for the parameters require way too much effort to enter and maintain. What happens if you add a parameter with a very long name? What if you need to write a longer description? A simpler and more maintainable version of this comment might be:

    /*
    ===========================================================
    | Parameter - Description               
    |                                                         
    | company_id - The primary key to company 
    | start_date - Start date used for date range 
    | end_date - End date for date range       
    ===========================================================
    */
    

    I like to use the following format for my block comments:

    /*
    || I put the slash-asterisk that starts the comment on a line all by
    || itself. Then I start each line in the comment block with a double
    

    || vertical bar to highlight the presence of the comment. Finally,

    || I place the asterisk-slash on a line all by itself.

    */
    

    On the negative side, the vertical bars have to be erased whenever I reformat the lines, but that isn't too much of an effort. On the positive side, those vertical bars make it very easy for a programmer who is scanning the left side of the code to pick out the comments.

    I put the comment markers on their own lines to increase the whitespace in my program and set off the comment. That way I can avoid "heavy" horizontal lines full of delimiters, such as asterisks or dashes, and avoid having to match the longest line in the comment.

    Maintain Indentation

    Inline commentary should reinforce the indentation and therefore the logical structure of the program. For example, it is very easy to find the comments in the make_array procedures shown below. I do not use any double-hyphens, so the slash-asterisk sequences stand out nicely. In addition, all comments start in the first column, so I can easily scan down the left-hand side of the program and pick out the documentation:

    PROCEDURE make_array (num_rows_in IN INTEGER) 
    /* Create an array of specified numbers of rows */
    IS
    /* Handles to Oracle Forms structures */
       col_id GROUPCOLUMN;
       rg_id RECORDGROUP;
    BEGIN
    /* Create new record group and column */
       rg_id := CREATE_GROUP ('array');
       col_id := ADD_GROUP_COLUMN ('col');
    /* 
    || Use a loop to create the specified number of rows and 
    || set the value in each cell.
    */
       FOR row_index IN 1 .. num_rows_in
       LOOP
    /* Create a row at the end of the group to accept data */
          ADD_GROUP_ROW (return_value, END_OF_GROUP);
          FOR col_index IN 1 .. num_columns_in
          LOOP
    /* Set the initial value in the cell */
             SET_GROUP_NUMBER_CELL (col_id, row_index, 0); 
    

    END LOOP;

       END LOOP;
    END;
    

    The problem with these comments is precisely that they do all start in the first column, regardless of the code they describe. The most glaring example of this formatting "disconnect" comes in the inner loop, repeated below:

          FOR col_index IN 1 .. num_columns_in
          LOOP
    /* Set the initial value in the cell */
             SET_GROUP_NUMBER_CELL (col_id, row_index, 0); 
          END LOOP;
    

    Your eye follows the three-space indentation very smoothly into the loop and then you are forced to move all the way to the left to pick up the comment. This format disrupts your reading of the code and therefore its readability. The code loses some of its ability to communicate the logical flow "at a glance," because the physical sense of indentation as logical flow is marred by the comments. Finally, you may end up writing full-line comments which are much longer than the code they appear next to, further distorting the code.

    Your comments should always be indented at the same level as the code which they describe. Assuming the comments come before the code itself, those lines of descriptive text will initiate the indentation at that logical level, which will also reinforce that structure. The make_array procedure, properly indented, is shown below:

    PROCEDURE make_array (num_rows_in IN INTEGER) 
    /* Create an array of specified numbers of rows */
    IS
       /* Handles to Oracle Forms structures */
       col_id GROUPCOLUMN;
       rg_id RECORDGROUP;
    BEGIN
       /* Create new record group and column */
       rg_id := CREATE_GROUP ('array');
       col_id := ADD_GROUP_COLUMN ('col');
       /* 
       || Use a loop to create the specified number of rows and 
       || set the value in each cell.
       */
       FOR row_index IN 1 .. num_rows_in
       LOOP
          /* Create a row at the end of the group to accept data */
          ADD_GROUP_ROW (return_value, END_OF_GROUP);
          FOR col_index IN 1 .. num_columns_in
          LOOP
             /* Set the initial value in the cell */
             SET_GROUP_NUMBER_CELL (col_id, row_index, 0); 
    

    END LOOP;

    END LOOP;

    END;
    

    Comment Declaration Statements

    I propose the following simple rule for documenting declaration statements:

    Provide a comment for each and every declaration.

    Does that sound excessive? Well, I must admit that I do not follow this guideline at all times, but I bet people who read my code wish I had. The declaration of a variable which seems to me to be perfectly clear may be a source of abiding confusion for others. Like many other people, I still have difficulty understanding that what is obvious to me is not necessarily obvious to someone else.

    Consider the declaration section in the next example. The commenting style is inconsistent. I use double-hyphens for a two-line comment; then I use the standard block format to provide information about three variables all at once. I provide comments for some variables, but not for others. It's hard to make sense of the various declaration statements:

    DECLARE
       -- Assume a maximum string length of 1000 for a line of text.
       text_line VARCHAR2 (1000);
       len_text    NUMBER;
       /*
       || Variables used to keep track of string scan:
       ||    atomic_count - running count of atomics scanned.
       ||    still_scanning - Boolean variable controls WHILE loop.
       */
       atomic_count NUMBER := 1;
       still_scanning BOOLEAN;
    BEGIN
    

    Let's recast this declaration section using my proposed guideline: a comment for each declaration statement. In the result shown below, the declaration section is now longer than the first version, but it uses whitespace more effectively. Each declaration has its own comment, set off by a blank line if a single-line comment:

    DECLARE
       /* Assume a maximum string length of 1000 for a line of text. */
       text_line VARCHAR2 (1000);
     
       /* Calculate length of string at time of declaration */
       len_string NUMBER;
     
       /* Running count of number of atomics scanned */
       atomic_count NUMBER := 1;
     
       /* Boolean variable that controls WHILE loop */
       still_scanning BOOLEAN ;
    BEGIN
    

    Documenting the Entire Package

    A package is often a complicated and long construct. It is composed of many different types of objects, any of which may be public (visible to programs and users outside of the package) or private (available only to other objects in the package). Package structure is described in more detail in Chapter 16, Packages.

    You can use some very simple documentation guidelines to clarify the structure of the package.

    As usual when discussing packages, one must consider the specification separately from the body. As a meta-module or grouping of modules, the specification should have a standard header. This header needn't be as complicated as that of a specific module, because you do not want to repeat in the package header any information which also belongs in specific modules. I suggest using the template header shown in the following example. In the "Major Modifications" section of the header, do not include every change made to every object in the package. Instead note significant changes to the package as a whole, such as an expansion of scope, a change in the way the package and global variables are managed, etc. Place this header after the package name and before the IS statement:

    PACKAGE package_name
    /*
    || Author:
    ||
    || Overview:
    ||
    || Major Modifications (when, who, what)
    ||
    */
    IS
       ...
    END package_name;
    

    Document the Package Specification

    The package specification is, in essence, a series of declaration statements. Some of those statements declare variables, while others declare modules. Follow the same recommendation in commenting a package as you do in commenting a module's declaration section: provide a comment for each declaration. In addition to the comments for a specific declaration, you may also find it useful to provide a banner before a group of related declarations to make that connection obvious to the reader.

    Surround the banner with whitespace (blank lines for the start/end of a multiline comment block). While you can use many different formats for this banner, use the simplest possible design that gets the point across. Everything else is clutter.

    The package specification below illustrates the header and declaration-level comment styles, as well as group banners:

    PACKAGE rg_select
    /*
    || Author: Steven Feuerstein, x3194
    ||
    || Overview: Manage a list of selected items correlated with a 
    ||    block on the screen.
    ||
    || Major Modifications (when, who, what)
    ||    12/94 - SEF - Create package
    ||    3/95  - JRC - Enhance to support coordinated blocks
    ||
    */
    IS
       /*----------------- Modules to Define the List -------------------*/
     
       /* Initialize the list/record group. */
       PROCEDURE init_list (item_name_in IN VARCHAR2);
     
       /* Delete the list */
       PROCEDURE delete_list;
     
       /*------------------ Modules to Manage Item Selections -----------*/
     
       /* Mark item as selected */
       PROCEDURE select_item (row_in IN INTEGER);
     
       /* De-select the item from the list */
       PROCEDURE deselect_item (row_in IN INTEGER);
     
    END rg_select;
    

    Document the Package Body

    The body is even longer and more complex than the specification. The specification contains only declarations, and only the declarations of public or global objects. The body contains the declarations of all private variables, cursors, types, etc., as well as the implementation of all cursors and modules. My suggestion for commenting declarations in the package body is, again, to provide a single line (or more) for each declaration, separated by whitespace. This takes more space, but is very legible.

    Once you get beyond the variables, use banners for any and all of the following:

    The banners for a package body are shown below:

    PACKAGE BODY package_name 
    IS
       /*----------------------- Package Variables ----------------------*/
       ... declarations placed here
     
       /*----------------------- Private Modules ------------------------*/
       FUNCTION ...
       PROCEDURE ...
     
       /*----------------------- Public Modules -------------------------*/
       FUNCTION ...
       PROCEDURE ...
     
    END package_name;
    

    Whether in a package or an individual module, make sure that your comments add value to the code. Do not repeat what the code itself clearly states. When dealing with a structure as complicated as a package, however, you need comments which focus on communicating that structure. If your package has more than a handful of modules, and especially if it uses both private and public modules, you should make sure to use these banners to keep the reader fully informed about the context of the code they are reading in the package.


    1. I recognize, however, that many developers I respect greatly for their code quality and elegance differ with me strongly on this point.

    Back to: Oracle PL/SQL Programming, 2nd Edition


    O'Reilly Home | O'Reilly Bookstores | How to Order | O'Reilly Contacts
    International | About O'Reilly | Affiliated Companies

    © 2001, O'Reilly & Associates, Inc.
    webmaster@oreilly.com