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

 

 

   
 

The Package Body
Oracle Tips by Burleson
 

The package body includes the definition of procedures and functions declared in the package spec and, like the package spec, can also include variables, constants, user-defined exceptions, and user-defined datatypes.

Unlike objects declared in a package spec, variables and other constructs defined within a package body are private to the package body. These constructs can only be referenced by procedures and functions within the package body.

Figure 6.2 illustrates the relationship of objects defined within a package body to a package spec.

Figure 6.2 Relating a package body to a package spec.

Procedures

Procedures are created inside a package without using the CREATE PROCEDURE command. Instead, a procedure’s definition is defined as part of the CREATE PACKAGE BODY command, as shown in Listing 6.7.

Listing 6.7 Creating a procedure inside a package body.

CREATE OR REPLACE
PACKAGE BODY GPA_Calculations
 
AS
 
FUNCTION Overall_GPA (iStudentSSN IN     integer)
 
RETURN number
 
IS
 
   iTotalCredits   integer := 0;
   iTotalHours     integer := 0;
   nGPA            number  := 0;
 
   CURSOR StudentClasses_cur
   IS
   SELECT course_credits, course_grade
   FROM   SCHEDULED_CLASSES
   WHERE  ssn            = iStudentSSN
   AND    audit_flag     = 'N'
   AND    no_credit_flag = 'N';
 
BEGIN
   FOR StudentClasses_rec IN StudentClasses_cur (iStudentSSN) LOOP
      iTotalCredits :=   iTotalCredits
                       + StudentClasses_rec.course_credits;
      iTotalHours   :=   iTotalHours
                       + StudentClasses_rec.course_hours;
   END LOOP;
 
   nGPA := (iTotalCredits / iTotalHours);
 
   RETURN nGPA;
END Overall_GPA;
 
 PROCEDURE Calculate_GPA (iStudentSSN IN     integer)
 IS
 
    CURSOR Students_cur
    IS
    SELECT ssn
    FROM   STUDENTS;
 
 BEGIN
    FOR Students_rec IN Students_cur LOOP
       UPDATE STUDENTS
       SET    overall_gpa = Overall_GPA (iStudentSSN => iStudentSSN)
       WHERE CURRENT OF Students_cur;
    END LOOP;
 END Calculate_GPA;
 
END GPA_Calculations;

Functions

Like procedures, functions are created within a package body as part of the CREATE PACKAGE BODY command, as shown in Listing 6.8.

Listing 6.8 Creating a function inside a package body.

 FUNCTION Overall_GPA (iStudentSSN IN     integer)
 
 RETURN number
 
 IS
 
    iTotalCredits   integer := 0;
    iTotalHours     integer := 0;
    nGPA            number  := 0;
 
    CURSOR StudentClasses_cur
    IS
    SELECT course_credits, course_grade
    FROM   SCHEDULED_CLASSES
    WHERE  ssn            = iStudentSSN
    AND    audit_flag     = 'N'
    AND    no_credit_flag = 'N';
 
 BEGIN
    FOR StudentClasses_rec IN StudentClasses_cur (iStudentSSN) LOOP
       iTotalCredits :=   iTotalCredits
                        + StudentClasses_rec.course_credits;
       iTotalHours   :=   iTotalHours
                        + StudentClasses_rec.course_hours;
    END LOOP;
 
    nGPA := (iTotalCredits / iTotalHours);
 
    RETURN nGPA;
 END Overall_GPA;
 
PROCEDURE Calculate_GPA (iStudentSSN IN     integer)
 
IS
 
   CURSOR Students_cur
   IS
   SELECT ssn
   FROM   STUDENTS;
 
BEGIN
   FOR Students_rec IN Students_cur LOOP
      UPDATE STUDENTS
      SET    overall_gpa = Overall_GPA (iStudentSSN => iStudentSSN)
      WHERE CURRENT OF Students_cur;
   END LOOP;
END Calculate_GPA;
 
END GPA_Calculations;

Initializing a Package

It’s not unusual for a package to contain one or more variables that must be initialized when the package is first loaded into memory. Consider the package in Listing 6.9.

Listing 6.9 Initializing packaged variables.

PACKAGE BODY System_Errors
.
.
.
 
   BEGIN
      vLastError := 'No error condition exists';
   END;
 
END System_Errors;

The highlighted code in this example is executed the first time the package is loaded into memory. Thus, vLastError will always contain the string ‘No error condition exists’ when the package is first executed by a user. The initialization code for the package must follow the declaration of all procedures and functions within the package.

 

            
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