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

Stored procedures contain the following five components:

  • Procedure declaration — Defines the name of the procedure and the parameters the procedure accepts.

  • Variable declarations — Defines the variables, constants, and user-defined exceptions used by the procedure.

  • Executable declarations — Defines local procedures and functions that can be executed by the procedure.

  • Body — Defines the internal logic of the procedure.

  • Exception handling — Defines logic for handling exceptions that might occur during execution.

The Procedure Declaration

The procedure declaration consists of two portions:

  • Procedure name — Name of the procedure, which identifies the procedure to other stored objects.

  • Parameter definitions — Names, parameter types, datatypes, and default values of the procedure’s parameters. These specify how the procedure must be called.

The procedure declaration is highlighted in Listing 4.16.

Listing 4.16 The procedure declaration portion 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;

Variable Declarations

The variable declarations section of a procedure allows you to define variables, constants, and user-defined exceptions that will be used by the procedure. The variable declarations section of a procedure is highlighted in Listing 4.17.

Listing 4.17 The variable declarations portion 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;

Executable Declarations

The executable declarations portion of a procedure allows the definition of local procedures and functions that will be used by the procedure. The executable declarations portion of the procedure is highlighted in Listing 4.18.

Listing 4.18 The executable declarations portion 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;

        
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