3.3.1 Formatting IF Statements

This conditional construct comes in three flavors:

IF <expression> END IF;

IF <expression> ELSE END IF;

IF <expression> ELSEIF <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 <expression>1 THEN executable_statements1; ELSEIF <expression2> THEN executable_statements2; ... ELSEIF <expressionN> THEN executable_statementsN; ELSE else_executable_statements; END IF;	IF <expression>1 THEN executable_statements1; ELSEIF <expression2> THEN executable_statements2; ... ELSEIF <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;

3.3.2 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:

• Infinite or simple loop

• WHILE loop

• Indexed FOR loop (numeric and cursor)

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:

• The infinite or simple loop:

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;

3.3.3 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:

• Indent each WHEN clause in from the EXCEPTION keyword that indicates the start of the exception section, as I've shown above. Place the THEN directly below the WHEN.

• Indent all the executable statements for that handler in from the THEN keyword.

• Place a blank line before each WHEN (except for the first).