126-129

Previous

Table of Contents

Next

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; 

Previous

Table of Contents

Next