 |
|
PL/SQL Procedure
Structure
Oracle Tips by
Burleson
|
Stored procedures contain the following five components:
-
Procedure declaration — Defines the name
of the procedure and the parameters the procedure accepts.
-
Variable declarations — Defines the
variables, constants, and user-defined exceptions used by the
procedure.
-
Executable declarations — Defines local
procedures and functions that can be executed by the procedure.
-
Body — Defines the internal logic of the
procedure.
-
Exception handling — Defines logic for
handling exceptions that might occur during execution.
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;
This is an excerpt from the book "High Performance Oracle
Database Automation" by Jonathan Ingram and Donald K.
Burleson, Series Editor. |
