|Previous||Table of Contents||Next|
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 procedures 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.
In the event that you have more than one local declared procedure or function within a block of PL/SQL and the procedures must reference each other, you wont be able to compile your code without getting an error. Each local procedure must be declared before the other.
To get around this problem, you can use a forward declaration to specify the interface for the procedures. This will allow your PL/SQL block to compile without errors. Listing 4.4 illustrates the use of a forward declaration.
Listing 4.4 Using a forward declaration for a local procedure.
CREATE OR REPLACE PROCEDURE Calculate_Lot_Size (nWidth IN number, nLength IN number, nLotSize OUT number) IS PROCEDURE Convert_Feet_To_Yards (nFeet IN number, nYards IN OUT number); PROCEDURE Convert_Yards_To_Feet (nYards IN number, nFeet IN OUT number) IS nCheckCalc number := 0; xBAD_CALCULATION EXCEPTION; BEGIN nFeet := nYards * 3; Convert_Feet_To_Yards (nFeet => nFeet, nYards => iCheckCalc); IF (nCheckCalc != nYards) THEN RAISE xBAD_CALCULATION; END IF; END Convert_Yards_To_Feet; PROCEDURE Convert_Feet_To_Yards (nFeet IN number, nYards IN OUT number) IS nCheckCalc number := 0; xBAD_CALCULATION EXCEPTION; BEGIN nYards := nFeet/3; Convert_Yards_To_Feet (nYards => nYards, nFeet => iCheckCalc); IF (nCheckCalc != nFeet) THEN RAISE xBAD_CALCULATION; END IF; END Convert_Feet_To_Yards; BEGIN <statements> END Calculate_Lot_Size; /
In this example, the first highlighted portion of the code is the forward declaration of the Convert_Feet_To_Yards() procedure, the second highlighted portion of the code is the call to the procedure, and the last highlighted portion of the code is the definition of the procedures logic. If the forward declaration of the procedure Convert_Feet_To_Yards() were removed from this example, the code would not compile.
A dependency is created when one object is referenced by another. The structure of the ALL_DEPENDENCIES view is shown in Listing 4.5.
Listing 4.5 The structure of the ALL_DEPENDENCIES view.
name varchar2 (30) type varchar2 (12) owner varchar2 (30) referenced_name varchar2 (30) referenced_type varchar2 (12) referenced_owner varchar2 (30)
The ALL_DEPENDENCIES view in Oracles data dictionary has a list of all the dependencies between objects stored in the database.
Determining The Effects Of A Code Change
You can use the following query to determine which objects could be affected by modifications to a stored procedure:SELECT type || ' ' || owner || '.' || name FROM ALL_DEPENDENCIES WHERE referenced_owner = upper ('&1') AND referenced_name = upper ('&2') ORDER BY type;
In this example, &1 is the schema of the object that you need to modify, and &2 is the name of the object. The query will produce output like this:PROCEDURE ACCOUNTING.CALCULATE_AGENT_COMMISSION PROCEDURE ACCOUNTING.CALCULATE_AGENT_BONUS PROCEDURE SALES.FIND_AVAILABLE_LOTS PROCEDURE SALES.CALCULATE_LOT_SIZE PACKAGE BODY ACCOUNTING.PERFORMANCE_REVIEWS
A parameter is a value that is passed to and/or returned from a stored procedure or function. Listing 4.6 illustrates how parameters are defined for a stored procedure.
Listing 4.6 Defining parameters for a stored procedure.
PROCEDURE Calculate_Lot_Size (nWidth IN number, nLength IN number, nLotSize OUT number);
|Previous||Table of Contents||Next|