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 Procedure Body
Oracle Tips by Burleson
 

The SQL and PL/SQL statements that follow the BEGIN statement and precede the EXCEPTION and/or END statements is the body of the procedure. The highlighted portion of Listing 4.19 is the procedure’s body.

Listing 4.19 The body of a procedure.

PROCEDURE Calculate_GPA (iStudentSSN IN    number)
 
IS
 
  CURSOR StudentClasses_cur
  IS
  SELECT SC.course_number, C.course_hours
  FROM   SCHEDULED_CLASSES SC,
         CLASSES           C
  WHERE  ssn = iStudentSSN
  AND    credit_flag = 'Y'
  AND    audit_flag  = 'N';
 
  iTotalCredits    integer := 0;
  iTotalHours      integer := 0;
 
  FUNCTION Get_Course_Credits (iCourseID IN     integer)
  RETURN integer
 
  IS
 
     iCreditsForClass integer := 0;
 
  BEGIN
    SELECT decode (course_grade, 'A', 4,
                                 'B', 3,
                                 'C', 2,
                                 'D', 1, 0)
    INTO   iCreditsForClass
    FROM   SCHEDULED_CLASSES
    WHERE  course_number = iCourseID
    AND    ssn           = iStudentSSN;
 
    RETURN iCreditsForClass;
  END Get_Course_Credits;
 
BEGIN
  FOR StudentClasses_rec IN StudentClasses_cur LOOP
    iCourse     := StudentClasses_rec.course_number;
    iTotalHours := StudentClasses_rec.course_hours;
 
    iTotalCredits :=   iTotalCredits
                  + Get_Course_Credits (iCourse);
    END LOOP;
 
    UPDATE STUDENTS
    SET    overall_gpa = (iTotalCredits / iTotalHours)
    WHERE  ssn = iStudentSSN;
 
EXCEPTION
  WHEN ZERO_DIVIDE THEN
      System.Log_Error (vObjectName =>'Calculate_GPA',
                        vErrorText  => SQLERRM,
                        vParameters => to_char (iCourseID) ||
                                       '^' ||
                                       to_char (iStudentSSN));
END Calculate_GPA;

Exception Handlers

Exception handlers are defined within the procedure to handle error conditions that could reasonably be expected to occur while the procedure is executing. In Listing 4.20, the developer feels that the ZERO_DIVIDE exception (one of the standard PL/SQL exceptions) could reasonably be expected to occur while calculating the student’s new GPA.

Listing 4.20 The exception handler of a procedure.

PROCEDURE Calculate_GPA (iStudentSSN IN    number)
 
IS
 
  CURSOR StudentClasses_cur
  IS
  SELECT SC.course_number, C.course_hours
  FROM   SCHEDULED_CLASSES SC,
         CLASSES           C
  WHERE  ssn = iStudentSSN
  AND    credit_flag = 'Y'
  AND    audit_flag  = 'N';
 
  iTotalCredits    integer := 0;
  iTotalHours      integer := 0;
 
  FUNCTION Get_Course_Credits (iCourseID IN     integer)
  RETURN integer
 
  IS
 
    iCreditsForClass integer := 0;
 
  BEGIN
    SELECT decode (course_grade, 'A', 4,
                                 'B', 3,
                                 'C', 2,
                                 'D', 1, 0)
    INTO   iCreditsForClass
    FROM   SCHEDULED_CLASSES
    WHERE  course_number = iCourseID
    AND    ssn           = iStudentSSN;
    RETURN iCreditsForClass;
  END Get_Course_Credits;
 
BEGIN
  FOR StudentClasses_rec IN StudentClasses_cur LOOP
    iCourse     := StudentClasses_rec.course_number;
    iTotalHours := StudentClasses_rec.course_hours;
 
    iTotalCredits :=   iTotalCredits
                  + Get_Course_Credits (iCourse);
  END LOOP;
 
  UPDATE STUDENTS
  SET    overall_gpa = (iTotalCredits / iTotalHours)
  WHERE  ssn = iStudentSSN;
 
EXCEPTION
  WHEN ZERO_DIVIDE THEN
      System.Log_Error (vObjectName =>'Calculate_GPA',
                        vErrorText  => SQLERRM,
                        vParameters => to_char (iCourseID) ||
                                       '^' ||
                                       to_char (iStudentSSN));
END Calculate_GPA;

Exceptions occur for one of three reasons:

  • Oracle detects an unexpected error while the object is executing.
  • An exception is explicitly raised using the RAISE statement.
  • An exception is raised using the Raise_Application_Error() procedure.

          
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