The Procedure Declaration

The procedure declaration consists of two portions:

•  Procedure name — Name of the procedure, which identifies the procedure to other stored objects.

•  Parameter definitions — Names, parameter types, datatypes, and default values of the procedure’s parameters. These specify how the procedure must be called.

The procedure declaration is highlighted in Listing 4.16.

Listing 4.16 The procedure declaration portion of a procedure.

PROCEDURE Calculate_GPA (iStudentSSN IN    number)

IS

  CURSOR StudentClasses_cur
  IS
  SELECT SC.course_number, C.course_hours
  FROM   SCHEDULED_CLASSES SC,
         CLASSES           C
  WHERE  ssn = iStudentSSN
  AND    credit_flag = 'Y'
  AND    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;

Variable Declarations

The variable declarations section of a procedure allows you to define variables, constants, and user-defined exceptions that will be used by the procedure. The variable declarations section of a procedure is highlighted in Listing 4.17.

Listing 4.17 The variable declarations portion of a procedure.

PROCEDURE Calculate_GPA (iStudentSSN IN    number)

IS

   CURSOR StudentClasses_cur
   IS
   SELECT SC.course_number, C.course_hours
   FROM   SCHEDULED_CLASSES SC,
          CLASSES           C
   WHERE  ssn = iStudentSSN
   AND    credit_flag = 'Y'
   AND    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;

Executable Declarations

The executable declarations portion of a procedure allows the definition of local procedures and functions that will be used by the procedure. The executable declarations portion of the procedure is highlighted in Listing 4.18.

Listing 4.18 The executable declarations portion of a procedure.

PROCEDURE Calculate_GPA (iStudentSSN IN    number)

IS

  CURSOR StudentClasses_cur
  IS
  SELECT SC.course_number, C.course_hours
  FROM   SCHEDULED_CLASSES SC,
         CLASSES           C
  WHERE  ssn = iStudentSSN
  AND    credit_flag = 'Y'
  AND    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;