137-141 | High Performance Oracle Database Automation: Creating Oracle Applications with SQL and PL/SQL

Table of Contents

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. -- -- REVISION HISTORY -- Date            Author        Reason for Change -- ---------------------------------------------------------------- -- 02/28/1997      J. Schmoe     Procedure created. -- ****************************************************************; IS    CURSOR StudentClasses_cur    IS    SELECT SC.course_number, C.course_hours    FROM   SCHEDULED_CLASSES SC,           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    IS       iCreditsForClass integer := 0;    BEGIN      SELECT decode (course_grade, 'A', 4,                                   'B', 3,                                   'C', 2,                                   'D', 1, 0)      INTO   iCreditsForClass      FROM   SCHEDULED_CLASSES      WHERE  course_number = iCourseID      AND    ssn           = iStudentSSN;      RETURN iCreditsForClass;    END Get_Course_Credits; BEGIN   FOR StudentClasses_rec IN StudentClasses_cur LOOP     iCourse     := StudentClasses_rec.course_number;     iTotalHours := StudentClasses_rec.course_hours;     iTotalCredits :=   iTotalCredits                   + Get_Course_Credits (iCourse);   END LOOP;   UPDATE STUDENTS   SET    overall_gpa = (iTotalCredits / iTotalHours)   WHERE  ssn = iStudentSSN; EXCEPTION   WHEN ZERO_DIVIDE THEN       System.Log_Error (vObjectName =>'Calculate_GPA',                         vErrorText  => SQLERRM,                         vParameters => to_char (iCourseID)                                         '^'                                         to_char (iStudentSSN)); END Calculate_GPA;

Pseudocode

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.

Comments

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 0, we can't upgrade the -- account to Gold level. -- IF (nBalance < 100) THEN   Raise_Application_Error (-20343, 'The balance is too low.'); END IF;

Comment B:

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

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.

Table of Contents