Programming Oracle Triggers and Stored Procedures (3rd Edition) (Prentice Hall PTR Oracle Series)

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.