Call (800) 766-1884 for Oracle support & training
Free Oracle Tips


Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance
 

Free Oracle Tips


 

HTML Text

BC Oracle tuning

Oracle training

Oracle support

Remote Oracle

 

 

   
 

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.

  
 

Oracle performance tuning software 
 
 
 
 

Oracle performance tuning book

 

 
 
 
Oracle performance Tuning 10g reference poster
 
 
 
Oracle training in Linux commands
 
Oracle training Excel
 
Oracle training & performance tuning books
 

 

   

Copyright © 1996 -  2014 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation. 

Hit Counter