111-117 | High Performance Oracle Database Automation: Creating Oracle Applications with SQL and PL/SQL

Table of Contents

Local Procedures

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.

Forward Declarations

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 won t 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 procedure s logic. If the forward declaration of the procedure Convert_Feet_To_Yards() were removed from this example, the code would not compile.

Dependencies

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 Oracle s 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 

Parameters

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);

Table of Contents