 |
|
PL/SQL Local Procedures
Oracle Tips by
Burleson
|
Procedures can be declared within another block of PL/SQL code.
This is very unusual. Most stored objects are written as standalone
objects to provide greater modularity. This approach does, however,
allow the local procedure to reference variables and constants within
the calling PL/SQL block as global values.
To create a local procedure within a PL/SQL block, simply declare
the procedure at the end of the variable declarations for the block.
Listing 4.3 illustrates the definition of a procedure as part of
another procedure’s declarations.
Listing 4.3 Declaring a local procedure within a procedure.
CREATE OR REPLACE
PROCEDURE Calculate_GPA (nSSN IN OUT number)
IS
iNumHours integer;
iTotalCredits integer;
iHoursForClass integer;
iCreditsForClass integer;
PROCEDURE Course_Credit (nSSN IN number,
nCourseID IN number,
iCredits OUT integer,
iHours OUT integer)
IS
BEGIN
SELECT SC.course_credits, C.course_hours
INTO iCredits, iHours
FROM SCHEDULED_CLASSES SC,
CLASSES C
WHERE C.course_number = nCourseID
AND SC.course_number = C.course_number
AND social_security_number = nSSN
AND audit_flag = 'N'
AND no_credit_flag = 'N';
EXCEPTION
WHEN NO_DATA_FOUND THEN
iCredits := 0;
iHours := 0;
END;
BEGIN
FOR Classes_rec IN Classes_cur LOOP
Course_Credit (nSSN => nStudentSSN,
iCourseID => Classes_rec.course_number,
iCredits => iCreditsForClass,
iHours => iHoursForClass);
IF (iHoursForClass > 0) THEN
iNumHours := iNumHours + iHoursForClass;
iTotalCredits := iTotalCredits + iCreditsForClass;
END IF;
END LOOP;
UPDATE STUDENTS
SET overall_gpa = (iTotalCredits / iNumHours)
WHERE ssn = nStudentSSN;
END;
/
Locally declared functions are somewhat more common than local
procedures, but they are still rather uncommon. Multiple local
procedures and functions can be used, but all of the local programs
must be declared at the end of the variable declarations section of
the main block.
This is an excerpt from the book "High Performance Oracle
Database Automation" by Jonathan Ingram and Donald K.
Burleson, Series Editor. |
