Previous Table of Contents Next

The Header

The header (or prologue) is intended to handle documentation within the procedure itself. While creating and maintaining a header requires some extra work, the trade-off for this work is easier maintenance in the future. Listing 4.25 contains a sample header for the Calculate_GPA() procedure.

Listing 4.25 The Calculate_GPA() procedure with a header.

PROCEDURE Calculate_GPA (iStudentSSN IN    number)

-- ****************************************************************
-- Description: The procedure Calculate_GPA accepts a student's
-- social security number as a parameter, loops through all the
-- classes for the students that are not being audited and are
-- taken for credit, and sums the credit points earned and hours
-- for the class.
-- The procedure then updates the overall_gpa column in the
-- STUDENTS table to the value of the total credit points earned
-- divided by the total hours for all classes taken.
-- Date            Author        Reason for Change
-- ----------------------------------------------------------------
-- 02/28/1997      J. Schmoe     Procedure created.
-- ****************************************************************;


   CURSOR StudentClasses_cur
   SELECT SC.course_number, C.course_hours
          CLASSES           C
   WHERE  SC.ssn         = iStudentSSN
   AND    C.credit_flag  = 'Y'
   AND    C.audit_flag   = 'N';

   iTotalCredits    integer := 0;
   iTotalHours      integer := 0;

   FUNCTION Get_Course_Credits (iCourseID IN     integer)
   RETURN integer


      iCreditsForClass integer := 0;

     SELECT decode (course_grade, 'A', 4,
                                  'B', 3,
                                  'C', 2,
                                  'D', 1, 0)
     INTO   iCreditsForClass
     WHERE  course_number = iCourseID
     AND    ssn           = iStudentSSN;

     RETURN iCreditsForClass;
   END Get_Course_Credits;

  FOR StudentClasses_rec IN StudentClasses_cur LOOP
    iCourse     := StudentClasses_rec.course_number;
    iTotalHours := StudentClasses_rec.course_hours;

    iTotalCredits :=   iTotalCredits
                  + Get_Course_Credits (iCourse);

  SET    overall_gpa = (iTotalCredits / iTotalHours)
  WHERE  ssn = iStudentSSN;

      System.Log_Error (vObjectName =>'Calculate_GPA',
                        vErrorText  => SQLERRM,
                        vParameters => to_char (iCourseID) ||
                                       '^' ||
                                       to_char (iStudentSSN));
END Calculate_GPA;


It’s often useful to write pseudocode when designing a new procedure. Pseudocode should outline the logical steps of a procedure. Listing 4.26 contains pseudocode for the Calculate_GPA() procedure.

Listing 4.26 Pseudocode for the Calculate_GPA() procedure.

for each class taken by the student loop
  if the class is not being audited and earns credit then
     get the credit points earned for the class;
     get the total hours for the class;

     determine the number of credit points earned for the class;

     add hours for the class to credited hours taken total;
     add credit points to a running total;
end loop;

update the STUDENTS table, setting the overall_gpa column
  to the value of the total credit points earned divided by
  the total number of credited hours taken;

Creating this type of logical map for a procedure shortens the time required to write the procedure and also allows nondevelopers (perhaps those who are more familiar with the business rules but are not developers) to look at the logic of the procedure and spot errors.

Pseudocode for procedures often serves as a map for commenting the procedure, as well.


A good comment in source code can describe the functionality of code as effectively as pages and pages of written documentation outside the code. Good comments describe why the code does what it does, often explaining how the code enforces business rules. Consider the following two sample comments:

Comment A:

-- If the account balance isn't at least $100, we can't upgrade the
-- account to Gold level.
IF (nBalance < 100) THEN
  Raise_Application_Error (-20343, 'The balance is too low.');

Comment B:

-- If nBalance < 100, raise an error.
IF (nBalance < 100) THEN
  Raise_Application_Error (-20343, 'The balance is too low.');

Comment A explains why the code is written this way. Comment B paraphrases the code but doesn’t explain the business rules behind the code. There is very little in Comment B that will help you understand the purpose of the code.

TIP:  Single-Line Versus Multi-Line Comments

Although PL/SQL supports both single-line and multi-line comments, it’s a good idea to only use single-line comments in your code, because, at some point, you might need to comment out a large block of code. PL/SQL doesn’t support nested C-style comments.

The exception to this rule is when you’re working in a 3GL language using one of Oracle’s precompilers. In this situation, you should use the commenting style that is specified by your coding standards for the 3GL, because the Oracle precompilers often don’t recognize the single-line style of commenting.

Previous Table of Contents Next