Previous Table of Contents Next


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


Previous Table of Contents Next