If a problem occurs in net wages, you can easily narrow down which procedure(s) is broken and then fix the bug immediately. In addition, modules have a more important aspect: You can test the modules independent of one another.

Commenting Your Code

One of the greatest benefits you can provide for yourself and other Oracle developers is to liberally comment your code. Although you could provide documentation manuals, in practice, these manuals tend to get "misplaced" in almost every environment. Adding comments to your code will help whether you are trying to debug the application or you are simply modifying the application to meet new requirements.

Proper labeling of variables is also important. Poorly worded variables confuse the developer and waste valuable time trying to follow the logic of the program. Listing 14.9 reflects code that can be very confusing at a first glance.

INPUT
Listing 14.9. Poorly commented code.

 1: CREATE OR REPLACE FUNCTION RAISE(
 2:           p1 INTEGER,
 3:           p2 NUMBER)
 4:      RETURN NUMBER IS
 5:      p3 NUMBER;
 6: BEGIN
 7:      IF p1 = 1 THEN
 8:           p3 := p2 * 1.10;
 9:      ELSIF p1 = 2 THEN
10:           p3 := p2 * 1.05;
11:      ELSIF p1 = 3 THEN
12:           p3 := p2 * 1.04;
13:      ELSIF p1 = 4 THEN
14:           p3 := p2 * 1.03;
15: ELSIF p1 = 5 THEN
16:           p3 := p2 ;
17:      ELSE
18:           p3 := p2 * 1.02;
19:      END IF;
20:      RETURN p3;
21: END RAISE;

ANALYSIS
On a first glance at this code, there are no comments, and very confusing variable names. In order to follow the code, you would have to first determine what p1, p2, and p3 are. You also do not know what the function raises: An hourly pay rate? The cost of benefits? Someone's GPA? The elevation of a building under construction?

"Raise" can mean almost anything to everyone, so a clarification is very important. If you review the same code in Listing 14.10, the comments easily clarify the function.

INPUT
Listing 14.10. Proper commenting and naming of variables.

 1: CREATE OR REPLACE FUNCTION RAISE(
 2:           p_paylevel INTEGER, -- parameter for input of raise level
 3:           p_payrate NUMBER)   -- parameter for input of pay rate
 4: /* The purpose of this function is to calculate ANNUAL raises
 5:    for all of the hourly employees, based upon their raise level
 6:    values 1-4 and all others.  */
 7:
 8: /* On June 24, 1997, added feature to eliminate consultant raise,
 9:   which is pay level 5 */
10:
11:      RETURN NUMBER IS
12:      v_newrate NUMBER; -- New Hourly Rate After Raise
13: BEGIN
14:      IF p_paylevel = 1 THEN
15:           v_newrate := p_payrate * 1.10; -- Promotion Raise
16:      ELSIF p_paylevel = 2 THEN
17:           v_newrate := p_payrate * 1.05; -- Exceeds Rate
18:      ELSIF p_paylevel = 3 THEN
19:           v_newrate := p_payrate * 1.04; -- Hi Meets Rate
20:      ELSIF p_paylevel = 4 THEN
21:           v_newrate := p_payrate * 1.03; -- Meets Rate
22: ELSIF p_paylevel = 5 THEN
23:           v_newrate := p_payrate ; -- Consultants who get no raise
24:      ELSE
25:           v_newrate := p_payrate * 1.02; -- All Others
26:      END IF;
27:      RETURN v_newrate; -- Returns new paylevel rate to procedure
28: END RAISE;

ANALYSIS
You now can follow the function, its purpose, what the variables are, and any modifications made at a later date. What a difference commenting and proper naming of variables makes!

Formatting Your Code

Another ounce of error prevention is the proper formatting of your code. Proper formatting includes the following guidelines:

• For each new block of code, indent five spaces.
• Use uppercase for keywords.

• Use upper- and lowercase for variable names.
• Precede variable names with a v_ for variable, p_ for parameters, and so on.
• Use one statement per line.

Proper Indentation of Five Spaces
Every time you use a new block of code, such as nesting loops, nesting IF statements, and so on, you should always indent five spaces to make the code more readable. Listing 14.11 shows poorly indented code.

INPUT
Listing 14.11. Code with no indentations.

 1: DECLARE
 2: v_MyNumber NUMBER := 0;
 3: BEGIN
 4: LOOP
 5: IF v_MyNumber = 7 THEN
 6: EXIT;
 7: v_MyNumber := v_MyNumber + 2;
 8: END LOOP;
 9: END;

ANALYSIS
This code is very difficult to follow. At a glance, you cannot easily discern where the declarations begin and end, where the loop ends, or where the IF statement terminates.

If you reformat the code as shown in Listing 14.12, you can follow the program more easily. In fact, by reformatting the code, you can easily spot that the code contains an infinite loop. In addition, you can recognize that the code is missing an END IF statement.

INPUT
Listing 14.12. Proper indentation.

 1: DECLARE
 2:     v_MyNumber NUMBER := 0; -- five spaces to set apart variable names
 3: BEGIN
 4:     LOOP -- five spaces to separate where procedure begins and ends
 5:         IF v_MyNumber = 7 THEN -- Five spaces for new block of code
 6:              EXIT;
 7:         END IF;
 8:     v_MyNumber := v_MyNumber + 2; -- Part of Loop Block
 9:     END LOOP;  -- Aligned under matching loop statement
10: END;

Using Uppercase for Keywords
Using uppercase for reserved words or functions helps to distinguish between regular code and Oracle-provided code. If a keyword is misspelled, you can easily spot the problem. The code in Listing 14.13 shows how unreadable code can become if keywords are not capitalized.