10.5. Package Body

The following is the body for the student's package. It includes just procedure code for the insert and a select for a student count.

This code could be enhanced with error handling logic; for example, it should include exception-handling logic for the case where the V_STATUS parameter violates the CHECK constraint (refer to pp. 103–104 for CHECK constraint exception handling.)

CREATE OR REPLACE PACKAGE BODY students_pkg IS
  PROCEDURE add_student
    (v_student_name   IN  students.student_name%TYPE,
     v_college_major  IN  students.college_major%TYPE,
     v_status         IN  students.status%TYPE,
     v_state          IN  students.state%TYPE DEFAULT NULL,
     v_license_no     IN  students.license_no%TYPE DEFAULT NULL)
  IS
  BEGIN
      INSERT INTO students VALUES
        ('A'||students_pk_seq.NEXTVAL,
          v_student_name,
          v_college_major,
          v_status,
          v_state,
          v_license_no);
  END add_student;

  FUNCTION NO_OF_STUDENTS
    (v_major  IN major_lookup.major_desc%TYPE DEFAULT NULL,
     v_status IN students.status%TYPE DEFAULT NULL)
  RETURN NUMBER
  IS
    ccount INTEGER;
  BEGIN
      SELECT COUNT (*) INTO ccount
      FROM   students, major_lookup
      WHERE  students.college_major = major_lookup.major
      AND    major_lookup.major_desc =
                   nvl(v_major,major_lookup.major_desc)
      AND   students.status = nvl(v_status,students.status);
    RETURN ccount;
  END NO_OF_STUDENTS;
END students_pkg;

The development of the body can lead to other local procedures and functions. These are hidden, also called private. Consider the structure of the student's package body, which is shown next. The notation in the package body, declarative part, identifies the region were we can optionally declare variables global to all procedures in the body. In this region we can declare types, exceptions, and procedures—everything in this region is local to the package body, yet global to all procedures declared beneath it.

PACKAGE BODY students_pkg IS

   Declarative part.

   PROCEDURE add_student( etc, )
   END;
   FUNCTION no_of_student( etc, )
   END;
END students_pkg;

There are two options when adding local subprograms to the declarative part. One is to code the subprogram bodies sequentially; however, there can be no forward referencing among the subprogram bodies. Consider two local procedures. The body looks like this.

PACKAGE BODY students_pkg IS
   PROCEDURE local_1 ( etc, ) IS
   BEGIN
       body code
   END;
   PROCEDURE local_2 ( etc, ) IS
   BEGIN
       body code
   END;
   PROCEDURE add_student( etc, ) IS
       body code
   END;
   FUNCTION no_of_student( etc, ) RETURN etc IS
       body code
   END;
END students_pkg;

In this package body, ADD_STUDENT and NO_OF_STUDENTS can reference LOCAL_1 and LOCAL_2. The procedure LOCAL_2 can reference LOCAL_1 but LOCAL_1 cannot reference LOCAL_2—the compiler, at the time it compiles LOCAL_1, has no known declaration of LOCAL_2.

The style of the preceding package is most common. Local procedures frequently exist at the top of the package. There is usually not much interdependency among local procedures in a body. An alternative is to first declare the definition of local procedures (called an early declaration), then the body. This code would look like the following:

PACKAGE BODY students_pkg IS

   PROCEDURE local_1 ( etc, );
   PROCEDURE local_2 ( etc, );

   PROCEDURE local_1 ( etc, ) IS
   BEGIN
       body code
   END;
   PROCEDURE local_2 ( etc, ) IS
   BEGIN
       body code
   END;
   PROCEDURE add_student( etc, ) IS
       body code
   END;
   FUNCTION no_of_student( etc, ) RETURN etc IS
       body code
   END;
END students_pkg;

Because all subprogram definitions occur prior to subprogram bodies, any subprogram can call any other subprogram. Furthermore, the local subprogram bodies can be placed in any order. For this package body, LOCAL_1 and LOCAL_2 subprogram bodies can be placed last in the package body.