Oracle PL/SQL by Example, Fifth Edition (2015)

Chapter 21. Packages


In this chapter, you will learn about

Image Creating Packages

Image Cursors Variables

Image Extending the Package

Image Package Instantiation and Initialization

Image SERIALLY_REUSABLE Packages


A package is a collection of PL/SQL objects grouped together under one package name. Packages may include procedures, functions, cursors, declarations, types, and variables. Collecting objects into a package has numerous benefits. In this chapter, you will learn what these benefits are and how to take advantage of them.

Lab 21.1: Creating Packages


After this lab, you will be able to

Image Create Package Specifications

Image Create Package Bodies

Image Call Stored Packages

Image Create Private Objects


There are numerous benefits of using packages as a method to bundle your functions and procedures, the first being that a well-designed package is a logical grouping of objects such as functions, procedures, global variables, and cursors. All of the code (parse tree and pseudocode [p-code]) is loaded into memory (shared global area [SGA] of the Oracle server) on the first call of the package. This means that the first call to the package is very expensive (it involves a lot of processing on the server), but all subsequent calls will result in improved performance. For this reason, packages are often used in applications where procedures and functions are called repeatedly.


Example of a Basic Currency Conversion

Once you have the same calculation written in multiple places, you have a large maintenance job every time the calculation in enhanced in complexity. For example, basic currency conversion is fairly simple: An amount is multiplied by an exchange rate. In actuality, currency conversion has become more complex. Once the European Union was formed, individual national currencies were phased out when a country adopted the euro as its currency. The European Union then adopted a complex policy on how these “dead” currencies would be converted. This consideration would be important if contracts were set up when the currency was in place but later the currency was phased out. If you had an old contract in German deutschemarks that needed to be converted into U.S. dollars, for example, it would have to go through this process. First it would be converted from German deutschemarks to euros based on the prevailing rate. Then it would be rounded based on a standard rounding mechanism for German deutschemarks to euros, and then it would be converted from euros to U.S. dollars at the prevailing rate. If your programs had many places where currency was converted, it would make more sense to encapsulate the conversion process into one function that encompassed this euro scenario. This function could be a public or private (explained later in this chapter) function that all other procedures in the same package called.


Packages allow you to make use of some of the concepts involved in object-oriented programming, even though PL/SQL is not a “true” object-oriented programming language. With the PL/SQL package, you can collect functions and procedures and provide them with a context. Because all the package code is loaded into memory, you can also write your code so that similar code is placed into the package in a manner that allows multiple procedures and functions to call them. You would want to do this if the logic for calculation is fairly intensive and you want to keep it in one place.

Creating Package Specifications

An additional level of security applies when using packages. When a user executes a procedure in a package (or stored procedures and functions), the procedure operates with the same permissions as its owner. Packages allow the creation of private functions and procedures, which can be called only from other functions and procedures in the package. This enforces information hiding. The structure of the package thus encourages top-down design.

The Package Specification

The package specification contains information about the contents of the package, but not the code for the procedures and functions. It also contains declarations of global/public variables. Anything placed in the declaration section of a PL/SQL block may be coded in a package specification. All objects placed in the package specification are called public objects. Any function or procedure not in the package specification but coded in a package body is called a private function or procedure.

When public procedures and functions are being called from a package, the programmer writing the “calling” process needs only the information in the package specification, as it provides all the information needed to call one of the procedures or functions within the package. The syntax for the package specification is as follows:

PACKAGE package_name
IS
 [ declarations of variables and types ]
 [ specifications of cursors ]
 [ specifications of modules ]
END [ package_name ];

The Package Body

The package body contains the actual executable code for the objects described in the package specification. It contains the code for all procedures and functions described in the specification and may additionally contain code for objects not declared in the specification; the latter type of packaged object is invisible outside the package and is referred to as “hidden.” When creating stored packages, the package specification and body can be compiled separately.

PACKAGE BODY package_name
IS
 [ declarations of variables and types ]
 [ specification and SELECT statement of cursors ]
 [ specification and body of modules ]
 [ BEGIN
executable statements ]
 [ EXCEPTION
exception handlers ]
END [ package_name ];

Rules for the Package Body

A number of rules must be followed in package body code. First, there must be an exact match between the cursor and module headers and their definitions in package specification. Second, declarations of variables, exceptions, type, or constants in the specification cannot be repeated in the body. Third, any element declared in the specification can be referenced in the body.

Referencing Package Elements

You use the following notation when calling packaged elements from outside the package: package_name.element.

You do not need to qualify elements when they are declared and referenced inside the body of the package or when they are declared in a specification and referenced inside the body of the same package.

The following example shows the package specification for the package manage_students. Later in this chapter, a section will describe the creation of the body of the same package.

For Example  ch21_1.sql

 1  CREATE OR REPLACE PACKAGE manage_students
 2  AS
 3    PROCEDURE find_sname
 4     (i_student_id IN student.student_id%TYPE,
 5      o_first_name OUT student.first_name%TYPE,
 6      o_last_name OUT student.last_name%TYPE
 7     );
 8    FUNCTION id_is_good
 9     (i_student_id IN student.student_id%TYPE)
10     RETURN BOOLEAN;
11  END manage_students;

Upon running this script, the specification for the package manage_students will be compiled into the database. The specification for the package now indicates that there is one procedure and one function. The procedure find_sname requires one IN parameter, the student ID; it returns two OUT parameters, the student’s first name and the student’s last name. The function id_is_good takes in a single parameter, a student ID, and returns a Boolean value (true or false). Although the body has not yet been entered into the database, the package is still available for other applications. For example, if you included a call to one of these procedures in another stored procedure, that procedure would compile (but would not execute). This is illustrated by the following example.

For Example  ch21_2.sql

SET SERVEROUTPUT ON
DECLARE
  v_first_name student.first_name%TYPE;
  v_last_name student.last_name%TYPE;
BEGIN
  manage_students.find_sname
    (125, v_first_name, v_last_name);
  DBMS_OUTPUT.PUT_LINE(v_first_name||' '||v_last_name);
END;

This procedure cannot run because only the specification for the procedure exists in the database, not the body. The SQL*Plus session returns the following output:

ERROR at line 1:
ORA-04068: existing state of packages has been discarded
ORA-04067: not executed, package body
           "STUDENT.MANAGE_STUDENTS" does not exist
ORA-06508: PL/SQL: could not find program
           unit being called
ORA-06512: at line 5

The following example creates a package specification for a package named school_api. This package contains the procedure discount_cost from Chapter 19 and the function new_instructor_id from Chapter 20.

For Example  ch21_3.sql

CREATE OR REPLACE PACKAGE  school_api as
   PROCEDURE discount_cost;
   FUNCTION new_instructor_id
     RETURN instructor.instructor_id%TYPE;
END school_api;

Creating Package Bodies

Now we will create the body of the manage_students and school_api packages, which were specified in the previous section.

For Example  ch21_4.sql

 1  CREATE OR REPLACE PACKAGE BODY manage_students
 2  AS
 3    PROCEDURE find_sname
 4      (i_student_id IN student.student_id%TYPE,
 5       o_first_name OUT student.first_name%TYPE,
 6       o_last_name OUT student.last_name%TYPE
 7       )
 8    IS
 9     v_student_id  student.student_id%TYPE;
10     BEGIN
11       SELECT first_name, last_name
12         INTO o_first_name, o_last_name
13         FROM student
14        WHERE student_id = i_student_id;
15      EXCEPTION
16       WHEN OTHERS
17       THEN
18         DBMS_OUTPUT.PUT_LINE
19      ('Error in finding student_id: '||v_student_id);
20      END find_sname;
21      FUNCTION id_is_good
22       (i_student_id IN student.student_id%TYPE)
23       RETURN BOOLEAN
24      IS
25       v_id_cnt number;
26      BEGIN
27       SELECT COUNT(*)
28         INTO v_id_cnt
29         FROM student
30        WHERE student_id = i_student_id;
31       RETURN 1 = v_id_cnt;
32      EXCEPTION
33      WHEN OTHERS
34      THEN
35       RETURN FALSE;
36      END id_is_good;
37    END manage_students;

This script compiles the package manage_students into the database. The specification for the package indicates that there is one procedure and one function. The procedure find_sname requires one IN parameter, the student ID; it returns two OUT parameters, the student’s first name and the student’s last name. The function id_is_good takes in a single parameter of a student ID and returns a Boolean value (true or false). Although the body has not yet been entered into the database, the package is still available for other applications. For example, if you included a call to one of these procedures in another stored procedure, that procedure would compile (but would not execute).

The next example creates the package body for the package named school_api that was created in the previous example. It contains the procedure discount_cost from Chapter 19 and the function new_instructor_id from Chapter 20.

For Example  ch21_5.sql

 1 CREATE OR REPLACE PACKAGE BODY school_api AS
 2    PROCEDURE discount_cost
 3    IS
 4       CURSOR c_group_discount
 5       IS
 6       SELECT distinct s.course_no, c.description
 7         FROM section s, enrollment e, course c
 8        WHERE s.section_id = e.section_id
 9       GROUP BY s.course_no, c.description,
10               e.section_id, s.section_id
11       HAVING COUNT(*) >=8;
12    BEGIN
14       FOR r_group_discount IN c_group_discount
14       LOOP
15       UPDATE course
16          SET cost = cost * .95
17        WHERE course_no = r_group_discount.course_no;
18         DBMS_OUTPUT.PUT_LINE
19           ('A 5% discount has been given to'
20           ||r_group_discount.course_no||'
21          '||r_group_discount.description);
22       END LOOP;
23      END discount_cost;
24     FUNCTION new_instructor_id
25        RETURN instructor.instructor_id%TYPE
26     IS
27        v_new_instid instructor.instructor_id%TYPE;
28     BEGIN
29        SELECT INSTRUCTOR_ID_SEQ.NEXTVAL
30          INTO v_new_instid
31          FROM dual;
32        RETURN v_new_instid;
33     EXCEPTION
34        WHEN OTHERS
35         THEN
36          DECLARE
37             v_sqlerrm VARCHAR2(250) :=
                  SUBSTR(SQLERRM,1,250);
38          BEGIN
39            RAISE_APPLICATION_ERROR(-20003,
40            'Error in   instructor_id: '||v_sqlerrm);
41          END;
42     END new_instructor_id;
43   END school_api;

Calling Stored Packages

Now we will use elements of the manage_students package in another code block.

For Example  ch21_6.sql

SET SERVEROUTPUT ON
DECLARE
  v_first_name student.first_name%TYPE;
  v_last_name student.last_name%TYPE;
BEGIN
  IF manage_students.id_is_good(&&v_id)
  THEN
    manage_students.find_sname(&&v_id, v_first_name,
       v_last_name);
  DBMS_OUTPUT.PUT_LINE('Student No. '||&&v_id||' is '
      ||v_last_name||', '||v_first_name);
ELSE
   DBMS_OUTPUT.PUT_LINE
   ('Student ID: '||&&v_id||' is not in the database.');
END IF;
END;

This is a correct PL/SQL block for running the function and the procedure in the package manage_students. If an existing student_id is entered, then the name of the student is displayed. If the student ID is not valid, then an error message is displayed. The following example shows the result when 145 is entered for the variable v_id in SQL Developer. The script output shows the original script and then the script once all variables have been replaced with the number entered (in this case 145). The final line (in bold) is the result.

old:DECLARE
  v_first_name student.first_name%TYPE;
  v_last_name student.last_name%TYPE;
BEGIN
  IF manage_students.id_is_good(&&v_id)
  THEN
    manage_students.find_sname(&&v_id, v_first_name,
       v_last_name);
  DBMS_OUTPUT.PUT_LINE('Student No. '||&&v_id||' is '
      ||v_last_name||', '||v_first_name);
ELSE
   DBMS_OUTPUT.PUT_LINE
   ('Student ID: '||&&v_id||' is not in the database.');
END IF;
END;
new:DECLARE
  v_first_name student.first_name%TYPE;
  v_last_name student.last_name%TYPE;
BEGIN
  IF manage_students.id_is_good(145)
  THEN
    manage_students.find_sname(145, v_first_name,
       v_last_name);
  DBMS_OUTPUT.PUT_LINE('Student No. '||145||' is '
      ||v_last_name||', '||v_first_name);
ELSE
   DBMS_OUTPUT.PUT_LINE
   ('Student ID: '||145||' is not in the database.');
END IF;
END;
anonymous block completed
Student No. 145 is Lefkowitz, Paul

The function id_is_good returns TRUE for an existing student_id such as 145. Control then passes to the first part of the IF statement and the procedure manage_students.find_sname finds the first and last names for student_id of 145—specifically, Paul Lefkowitz.

The following is an example of a test script for the school_api package.

For Example  ch21_7.sql

SET SERVEROUTPUT ON
DECLARE
  V_instructor_id instructor.instructor_id%TYPE;
BEGIN
  School_api.Discount_Cost;
  v_instructor_id := school_api.new_instructor_id;
  DBMS_OUTPUT.PUT_LINE
    ('The new id is: '||v_instructor_id);
END;

Creating Private Objects

Public elements are elements defined in the package specification. If an object is defined only in the package body, then it is private. Private elements cannot be accessed directly by any programs outside the package. You can think of the package specification as being a “menu” of packaged items that are available to users; there may be other objects working behind the scenes, but they aren’t accessible. They cannot be called or utilized in any way; they are available as part of the internal “menu” of the package and can be called only by other elements of the package.

The following steps show how to transform the package manage_students so that the function student_count_priv becomes a private function. The public procedure display_student_count then calls this private function.

Step 1: Replace the last lines of the manage_students package specification with the following code and recompile the package specification:

11    PROCEDURE display_student_count;
12  END manage_students;

Step 2: Replace the end of the body with the following code and recompile the package body. Lines 1–36 are unchanged from lines 1–36 in example ch21_4.sql:

37   FUNCTION student_count_priv
38     RETURN NUMBER
39    IS
40     v_count NUMBER;
41    BEGIN
42     select count(*)
43     into v_count
44     from student;
45     return v_count;
46    EXCEPTION
47     WHEN OTHERS
48       THEN
49       return(0);
50    END student_count_priv;
51    PROCEDURE display_student_count
52     is
53     v_count NUMBER;
54    BEGIN
55     v_count := student_count_priv;
56     DBMS_OUTPUT.PUT_LINE
57        ('There are '||v_count||' students.');
58    END display_student_count;
59  END manage_students;

Now run the following script:

DECLARE
  V_count NUMBER;
BEGIN
  V_count := Manage_students.student_count_priv;
  DBMS_OUTPUT.PUT_LINE(v_count);
END;

Because the private function student_count_priv cannot be called from outside the package, you will receive the following error message:

ERROR at line 1:
ORA-06550: line 4, column 31:
PLS-00302: component 'STUDENT_COUNT_PRIV' must be declared
ORA-06550: line 4, column 3:
PL/SQL: Statement ignored

It appears as if the private function does not exist. This point is important to keep in mind—it can be useful when you are writing PL/SQL packages used by other developers. Those developers need to see only the package specification, not the inner workings of the package. That is, they need to know what is being passed into the procedures and functions and what is being returned. If a number of procedures will make use of the same logic, it may make more sense to put that logic into a private function called by the procedures. This is also a good approach to keep in mind if one calculation will be used by many other procedures in the same package. For example, we just created a function to count students. Perhaps other procedures will need to make use of this function—such as if a change in the price of all courses should occur once the student count reaches a certain number.

The following example shows a valid method of running a procedure. The result would be a line indicating the number of students in the database. Note that the procedure in the package manage_students uses the private function student_count_priv to retrieve the student count.

SET SERVEROUTPUT ON
Execute manage_students.display_student_count;

If you forget to include a procedure or function in a package specification, it becomes private. If you declare a procedure or function in the package specification, but then do not define it when you create the body, you will receive the following error message:

PLS-00323: subprogram or cursor 'procedure_name' is
declared in a package specification and must be
defined in the package body

The following updated script for the manage_students package adds a private function to the school_api called get_course_descript_private. It accepts a course.course_no%TYPE and returns a course.description%TYPE. It searches for and returns the course description for the course number passed to it. If the course does not exist or if an error occurs, it returns NULL. Nothing needs to be added to the package specification, because you are simply adding a private object.

For Example  ch21_8.sql

CREATE OR REPLACE PACKAGE manage_students
AS
   PROCEDURE find_sname
     (i_student_id IN student.student_id%TYPE,
      o_first_name OUT student.first_name%TYPE,
      o_last_name OUT student.last_name%TYPE
     );
   FUNCTION id_is_good
     (i_student_id IN student.student_id%TYPE)
     RETURN BOOLEAN;
  PROCEDURE display_student_count;
END manage_students;

The package body for manage_students now has the following form:

For Example  ch21_9.sql

CREATE OR REPLACE PACKAGE BODY manage_students
AS
  PROCEDURE find_sname
    (i_student_id IN student.student_id%TYPE,
     o_first_name OUT student.first_name%TYPE,
     o_last_name OUT student.last_name%TYPE
     )
  IS
   v_student_id  student.student_id%TYPE;
   BEGIN
      SELECT first_name, last_name
        INTO o_first_name, o_last_name
        FROM student
       WHERE student_id = i_student_id;
    EXCEPTION
      WHEN OTHERS
      THEN
        DBMS_OUTPUT.PUT_LINE
    ('Error in finding student_id: '||v_student_id);
    END find_sname;
    FUNCTION id_is_good
      (i_student_id IN student.student_id%TYPE)
      RETURN BOOLEAN
    IS
      v_id_cnt number;
    BEGIN
      SELECT COUNT(*)
        INTO v_id_cnt
        FROM student
       WHERE student_id = i_student_id;
      RETURN 1 = v_id_cnt;
    EXCEPTION
    WHEN OTHERS
    THEN
      RETURN FALSE;
    END id_is_good;
FUNCTION student_count_priv
  RETURN NUMBER
 IS
  v_count NUMBER;
 BEGIN
  select count(*)
  into v_count
  from student;
  return v_count;
 EXCEPTION
  WHEN OTHERS
    THEN
    return(0);
 END student_count_priv;
 PROCEDURE display_student_count
  is
  v_count NUMBER;
 BEGIN
  v_count := student_count_priv;
  DBMS_OUTPUT.PUT_LINE
     ('There are '||v_count||' students.');
 END display_student_count;
 FUNCTION get_course_descript_private
    (i_course_no  course.course_no%TYPE)
    RETURN course.description%TYPE
  IS
     v_course_descript course.description%TYPE;
  BEGIN
     SELECT description
       INTO v_course_descript
       FROM course
      WHERE course_no = i_course_no;
     RETURN v_course_descript;
  EXCEPTION
     WHEN OTHERS
     THEN
        RETURN NULL;
  END get_course_descript_private;
END manage_students;

Lab 21.2: Cursor Variables


After this lab, you will be able to

Image Make Use of Cursor Variables


Up to this point in this book, you have seen cursors used to gather specific data from a single SELECT statement. At the beginning of this chapter, you learned how to bring a number of procedures together into a large program called a package. A package may have one cursor that is used by a few procedures. In this case, each of the procedures that uses the same cursor would have to declare, open, fetch, and close the cursor. In the current version of PL/SQL, cursors can be declared and manipulated like any other PL/SQL variable. This type of variable is called a cursor variable or a REF CURSOR. A cursor variable is just a reference or a handle to a static cursor. It permits a programmer to pass this reference to the same cursor among all the program’s units that need access to the cursor. A cursor variable binds the cursor’s SELECT statement dynamically at run time.

Explicit cursors are used to name a work area that holds the information of a multirow query. A cursor variable may be used to point to the area in memory where the result of a multirow query is stored. The cursor always refers to the same information in a work area, whereas a cursor variable can point to different work areas. Cursors are static, but cursor variables can be seen as dynamic because they are not tied to any one specific query. Cursor variables give you easy access to centralized data retrieval.

You can use a cursor variable to pass the result set of a query between stored procedures and various clients. A query work area remains accessible as long as a cursor variable points to it. As a consequence, you can freely pass a cursor variable from one scope to another. Two types of cursor variables exist: strong and weak.

To execute a multirow query, the Oracle server opens a work area called a cursor to store processing information. To access this information, you either name the work area or use a cursor variable that points to the work area. A cursor always refers to the same work area, whereas a cursor variable can refer to different work areas. Hence, cursors and cursor variables are not interoperable. An explicit cursor is static and is associated with one SQL statement. A cursor variable, in contrast, can be associated with different statements at run time. Primarily you use a cursor variable to pass a pointer to query result sets between PL/SQL stored subprograms and various clients, such as a client Oracle Developer Forms application. None of them owns the result set; they simply share a pointer to the query work area that stores the result set. You can declare a cursor variable on the client side, open and fetch from it on the server side, and then continue to fetch from it on the client side.

Cursor variables differ from cursors in much the same way that constants differ from variables. A cursor is static; a cursor variable is dynamic. In PL/SQL, a cursor variable has a REF CURSOR data type, where REF stands for reference and CURSOR stands for the class of the object. You will now learn the syntax for declaring and using a cursor variable.

To create a cursor variable, you first need to define a REF CURSOR type and then declare a variable of that type. Before you declare the REF CURSOR to be of a strong type, you must declare a record that has the data types of the result set of the SELECT statement that you plan to use (note that this step is not necessary for a weak REF CURSOR).

TYPE inst_city_type IS RECORD
 (first_name instructor.first_name%TYPE;
 last_name   instructor.last_name%TYPE;
 city        zipcode.city%TYPE;
 state       zipcode.state%TYPE)

Second, you must declare a composite data type for the cursor variable that is of the type REF CURSOR. The syntax is as follows:

TYPE ref_type_name is REF CURSOR [RETURN return_type];

The ref_type_name is a type specified in subsequent declarations. The return type represents a record type for a strong cursor; a weak cursor does not have a specific return type but can handle any combination of data items in a SELECT statement. The REF CURSOR keywords indicate that the new type will be a pointer to the defined type. The return_type indicates the types of SELECT lists that are eventually returned by the cursor variable. The return type must be a record type.

TYPE inst_city_cur IS REF CURSOR RETURN inst_city_type;

A cursor variable can be strong (restrictive) or weak (nonrestrictive). A strong cursor variable is a REF CURSOR type definition that specifies a return_type; a weak definition does not. PL/SQL enables you to associate a strong type with type-comparable queries only, while a weak type can be associated with any query. This makes a strong cursor variable less error prone but renders weak REF CURSOR types more flexible.

Following are the key steps for handling a cursor variable:

1. Define and declare the cursor variable.

Open the cursor variable. Associate a cursor variable with a multirow SELECT statement, execute the query, and identify the result set. An OPEN FOR statement can open the same cursor variable for different queries. You do not need to close a cursor variable before reopening it. Keep in mind that when you reopen a cursor variable for a different query, the previous query is lost. A good programming technique is to close the cursor variables before reopening them later on in the program.

2. Fetch rows from the result set.

Retrieve rows from the result set, one at a time. Note that the return type of the cursor variable must be compatible with the variable named in the INTO clause of the FETCH statement.

The FETCH statement retrieves rows from the result set, one at a time. PL/SQL verifies that the return type of the cursor variable is compatible with the INTO clause of the FETCH statement. For each query column value returned, there must be a type-comparable variable in theINTO clause. Also, the number of query column values must equal the number of variables. In case of a mismatch in number or type, an error occurs at compile time for strongly typed cursor variables and at run time for weakly typed cursor variables.

3. Close the cursor variable.

The next example shows the use of a cursor variable in a package.

For Example  ch21_10.sql

CREATE OR REPLACE PACKAGE course_pkg AS
  TYPE course_rec_typ IS RECORD
    (first_name   student.first_name%TYPE,
     last_name    student.last_name%TYPE,
     course_no    course.course_no%TYPE,
     description  course.description%TYPE,
     section_no   section.section_no%TYPE
     );
  TYPE course_cur IS REF CURSOR RETURN course_rec_typ;
  PROCEDURE get_course_list
    (p_student_id    NUMBER ,
     p_instructor_id NUMBER ,
     course_list_cv IN OUT course_cur);
END course_pkg;
/

CREATE OR REPLACE PACKAGE BODY course_pkg AS
  PROCEDURE get_course_list
    (p_student_id    NUMBER ,
     p_instructor_id NUMBER ,
     course_list_cv IN OUT course_cur)
  IS
  BEGIN
    IF p_student_id IS NULL AND p_instructor_id
      IS NULL THEN
      OPEN course_list_cv FOR
        SELECT 'Please choose a student-' First_name,
               'instructor combination'  Last_name,
          NULL   course_no,
          NULL   description,
          NULL   section_no
          FROM dual;
    ELSIF p_student_id IS NULL  THEN
      OPEN course_list_cv FOR
        SELECT s.first_name   first_name,
          s.last_name    last_name,
          c.course_no    course_no,
          c.description  description,
          se.section_no  section_no
   FROM  instructor i, student s,
         section se, course c, enrollment e
  WHERE  i.instructor_id = p_instructor_id
    AND  i.instructor_id = se.instructor_id
    AND  se.course_no    = c.course_no
    AND  e.student_id    = s.student_id
    AND  e.section_id    = se.section_id
     ORDER BY  c.course_no, se.section_no;
    ELSIF p_instructor_id IS NULL  THEN
      OPEN course_list_cv FOR
           SELECT i.first_name   first_name,
          i.last_name     last_name,
          c.course_no     course_no,
          c.description   description,
          se.section_no   section_no
   FROM  instructor i, student s,
         section se, course c, enrollment e
  WHERE  s.student_id = p_student_id
    AND  i.instructor_id = se.instructor_id
    AND  se.course_no    = c.course_no
    AND  e.student_id    = s.student_id
    AND  e.section_id    = se.section_id
        ORDER BY  c.course_no, se.section_no;
        END IF;
     END get_course_list;
END course_pkg;

You can pass query result sets between PL/SQL stored subprograms and various clients. This approach works because PL/SQL and its clients share a pointer to the query work area identifying the result set. This can be done in a client program like SQL*Plus by defining a host variable with a data type of REF CURSOR to hold the query result generated from a REF CURSOR in a stored program. To see what is being stored in the SQL*Plus variable, use the SQL*Plus PRINT command. Optionally, you can use the SQL*Plus command SET AUTOPRINT ON to display the query results automatically.

In script ch21_10, the package specification includes two declarations of a TYPE. The first is for the record type course_rec_type. This record type is declared to define the result set of the SELECT statements that will be used for the cursor variable. When data items in a record do not match a single table, it is necessary to create a record type. The second TYPE declaration is for the cursor variable, REF CURSOR. This variable has the name course_cur and is declared as a strong cursor, meaning that it can be used only for a single record type. The record type iscourse_rec_type. The procedure get_course_list in the course_pkg returns a cursor variable that holds three different result sets. Each of the result sets is of the same record type.

Image The first type is for when both IN parameters—that is, the student ID and instructor ID—are null. This will produce a result set that consists of a message, “Please choose a student-instructor combination.”

Image The second way the procedure runs is if the instructor_id is passed in but the student_id is null (note that the logic of the procedure is a reverse negative; saying in the second clause of the IF statement p_student_id IS NULL, means “when the instructor_id is passed in”). This will run a SELECT statement to populate the cursor variable that holds a list of all courses this instructor teaches and the students enrolled in these classes.

Image The third way this procedure runs is with a student_id and no instructor_id. This will produce a result set containing all the courses the student is enrolled in and the instructor for each section.

Be aware that once the cursor variable is opened, it is not closed until you specifically close it.

The following SQL statement will create a variable that is a cursor variable type:

VARIABLE course_cv REF CURSOR

There are three ways to execute this procedure. The first way would be to pass a student ID and not an instructor ID:

exec course_pkg.get_course_list(102, NULL, :course_cv);

The contents of the variable course_cv can then be displayed in SQL*Plus with the following command:

SQL> print course_cv

FIRST_NAME LAST_NAME  COURSE_NO DESCRIPTION       SECTION_NO
---------- ---------  --------- ----------------- ----------
Charles    Lowry             25 Intro to Programming       2
Nina       Schorin           25 Intro to Programming       5

The next method would be to pass an instructor ID and not a student ID:

SQL> exec course_pkg.get_course_list(NULL, 102, :course_cv);
PL/SQL procedure successfully completed.

SQL> print course_cv
FIRST_NAME  LAST_NAME   COURSE_NO  DESCRIPTION         SECTION_NO
----------  ----------  ---------  ------------------  ----------
Jeff        Runyan             10  Technology Concepts          2
Dawn        Dennis             25  Intro to Programming         4
May         Jodoin             25  Intro to Programming         4
Jim         Joas               25  Intro to Programming         4
Arun        Griffen            25  Intro to Programming         4
Alfred      Hutheesing         25  Intro to Programming         4
Lula        Oates             100  Hands-On Windows             1
Regina      Bose              100  Hands-On Windows             1
Jenny       Goldsmith         100  Hands-On Windows             1
Roger       Snow              100  Hands-On Windows             1
Rommel      Frost             100  Hands-On Windows             1
Debra       Boyce             100  Hands-On Windows             1
Janet       Jung              120  Intro to Java Programming    4
John        Smith             124  Advanced Java Programming    1
Charles     Caro              124  Advanced Java Programming    1
Sharon      Thompson          124  Advanced Java Programming    1
Evan        Fielding          124  Advanced Java Programming    1
Ronald      Tangaribuan       124  Advanced Java Programming    1
N           Kuehn             146  Java for C/C++ Programmers   2
Derrick     Baltazar          146  Java for C/C++ Programmers   2
Angela      Torres            240  Intro to the Basic Language  2

The last method would be not to pass either the student ID or the instructor ID:

SQL> exec course_pkg.get_course_list(NULL, NULL,  :course_cv);
PL/SQL procedure successfully completed.

SQL>  print course_cv

FIRST_NAME              LAST_NAME            C DESCRIPTION      S
----------------------- ------------------------- - ---------------
Please choose a student-  instructor combination

The next example creates another package called student_info_pkg that has a single procedure called get_student_info. The get_student_info package will have three parameters: the student_id, a number called p_choice, and a weak cursor variable. Thep_choice parameter indicates which information will be delivered about the student. If it is 1, then the procedure will return the information about the student from the STUDENT table. If it is 2, then the procedure will list all the courses in which the student is enrolled, along with the student names of the fellow students enrolled in the same section as the student with the student_id that was passed in. If it is 3, then the procedure will return the instructor name for that student, with the information about the courses in which the student is enrolled.

For Example  ch21_11.sql

CREATE OR REPLACE PACKAGE student_info_pkg AS
  TYPE student_details IS REF CURSOR;
  PROCEDURE get_student_info
    (p_student_id  NUMBER ,
     p_choice      NUMBER ,
     details_cv IN OUT student_details);
END student_info_pkg;
/
CREATE OR REPLACE PACKAGE BODY student_info_pkg AS
  PROCEDURE get_student_info
    (p_student_id  NUMBER ,
     p_choice      NUMBER ,
     details_cv IN OUT student_details)
  IS
  BEGIN
    IF p_choice = 1  THEN
      OPEN details_cv FOR
        SELECT s.first_name  first_name,
               s.last_name   last_name,
               s.street_address address,
               z.city        city,
               z.state       state,
               z.zip         zip
         FROM  student s, zipcode z
        WHERE  s.student_id = p_student_id
          AND  z.zip = s.zip;
    ELSIF p_choice = 2 THEN
      OPEN details_cv  FOR
        SELECT c.course_no  course_no,
               c.description             description,
               se.section_no             section_no,
               s.first_name first_name,
               s.last_name  last_name
         FROM  student s,  section se,
               course c, enrollment e
        WHERE  se.course_no = c.course_no
          AND  e.student_id = s.student_id
          AND  e.section_id = se.section_id
          AND  se.section_id in (SELECT e.section_id
                                   FROM student s,
                                        enrollment e
                                  WHERE s.student_id =
                                        p_student_id
                                    AND s.student_id =
                                        e.student_id)
     ORDER BY  c.course_no;
    ELSIF p_choice = 3 THEN
      OPEN details_cv FOR
        SELECT i.first_name    first_name,
               i.last_name     last_name,
               c.course_no     course_no,
               c.description   description,
               se.section_no   section_no
        FROM   instructor i, student s,
               section se, course c, enrollment e
        WHERE  s.student_id = p_student_id
        AND    i.instructor_id = se.instructor_id
        AND    se.course_no    = c.course_no
        AND    e.student_id    = s.student_id
        AND    e.section_id    = se.section_id
     ORDER BY  c.course_no, se.section_no;
    END IF;
  END get_student_info;

END student_info_pkg;

To execute the get_student_info procedure, you would first have to create a session variable:

VARIABLE student_cv REF CURSOR

Then execute the procedure with the appropriate values:

SQL> execute student_info_pkg.GET_STUDENT_INFO
    (102, 1, :student_cv);

Finally display the results:

PL/SQL procedure successfully completed.

SQL>  print student_cv
FIRST_ LAST_NAM ADDRESS           CITY           ST ZIP
------ -------- ------------- ---------- -- -----
Fred   Crocitto 101-09 120th St.  Richmond Hill  NY 11419

SQL> execute student_info_pkg.GET_STUDENT_INFO
                          (102, 2,  :student_cv);
PL/SQL procedure successfully completed.

SQL> print student_cv
COURSE_NO DESCRIPTION      SECTION_NO FIRST_NAME LAST_NAME
--------- ---------------- ---------- ---------- -----------
       25 Intro to Programming      2 Fred       Crocitto
       25 Intro to Programming      2 Judy       Sethi
        5 Intro to Programming      2 Jenny      Goldsmith
       25 Intro to Programming      2 Barbara    Robichaud
       25 Intro to Programming      2 Jeffrey    Citron
       25 Intro to Programming      2 George     Kocka
       25 Intro to Programming      5 Fred       Crocitto
       25 Intro to Programming      5 Hazel      Lasseter
       25 Intro to Programming      5 James      Miller
       25 Intro to Programming      5 Regina     Gates
       25 Intro to Programming      5 Arlyne     Sheppard
       25 Intro to Programming      5 Thomas     Edwards
       25 Intro to Programming      5 Sylvia     Perrin
       25 Intro to Programming      5 M.         Diokno
       25 Intro to Programming      5 Edgar      Moffat
       25 Intro to Programming      5 Bessie     Heedles
       25 Intro to Programming      5 Walter     Boremmann
       25 Intro to Programming      5 Lorrane    Velasco

SQL> execute student_info_pkg.GET_STUDENT_INFO
                             (214,  3,  :student_cv);
PL/SQL procedure successfully completed.

SQL> print student_cv
FIRST_NAME LAST_NAME    COURSE_NO  DESCRIPTION        SECTION_NO
---------- ------------ ---------- ---------------------------
Marilyn    Frantzen       120 Intro to Java Programming        1
Fernand    Hanks          122 Intermediate Java Programming    5
Gary       Pertez         130 Intro to Unix                    2
Marilyn    Frantzen       145 Internet Protocols               1

Early versions of Oracle offered the use of only REF CURSOR, where first a type of REF CURSOR would be created with a particular record set and then another variable would have to be created of that type to make use of REF CURSOR in stored procedures and functions. Later versions of Oracle introduced the SYS_REFCURSOR as a predefined type (of type REF CURSOR) that behaves in a similar manner. SYS_REFCURSOR is weakly typed, which means any SELECT statement can be used with different FROM or WHERE clauses, as well as different number and types of columns. The examples in Chapter 24 in the section that covers DBMS_SQL include a syntax example that uses SYS_REFCURSOR instead of REF CURSOR.


Rules for Using Cursor Variables

Image The cursor variable cannot be defined in a package specification.

Image You cannot use cursor variables with remote subprograms on another server, so you cannot pass cursor variables to a procedure that is called through a database link.

Image Do not use FOR UPDATE with OPEN FOR in processing a cursor variable.

Image You cannot use comparison operators to test cursor variables for equality, inequality, or nullity.

Image A cursor variable cannot be assigned a null value.

Image A REF CURSOR type cannot be used in CREATE TABLE or VIEW statements as there is no equivalent data type for a database column.

Image A stored procedure that uses a cursor variable can be used only as a query block data source; it cannot be used for a DML block data source. Using a REF CURSOR is ideal for queries that are dependent only on variations in SQL statements and not on PL/SQL statements.

Image You cannot store cursor variables in an associative array, nested table, or varray.

Image If you pass a host cursor variable to PL/SQL, you cannot fetch from it on the server side unless you also open it there on the same server call.


Lab 21.3: Extending the Package


After this lab, you will be able to

Image Extend the Package with Additional Procedures


In this lab, you will make use of previously introduced concepts to both extend the packages you have created and create new ones. Only by completing extensive exercises will you become more comfortable with programming in PL/SQL. It is very important when writing your PL/SQL code that you carefully take into consideration all aspects of the business requirements. A good rule of thumb is to think ahead and write your code in reusable components so that it will be easy to extend and maintain that PL/SQL code.

Extending the Package with Additional Procedures

This section provides more examples of writing packages by working through a complex package with various complex functions and procedures. It is always a best practice to build up large packages one step at a time and to test each section you create to ensure that it works properly and does not contain any syntax errors. The following set of examples show you how to build a package step by step.

Creating the Manage_Grades Package Specification

The following script creates a new package specification called manage_grades. This package will perform a number of calculations on grades and will need two package cursors. The first step is to create a cursor called c_grade_Type that has an IN parameter of a section ID and provides a list of all grade types for a given section; this information is necessary to calculate a student’s grade in that section. The return items from the cursor will be (1) the grade type code; (2) the number of that grade type for this section; (3) the percentage of the final grade; and (4) the drop lowest indicator (a flag).

The first thing you should always do when building a package cursor is to write the SELECT statement and test it on a known result set. In other words, you hard-code a value for the variable—for example, a student_id and section_id—and then replace the hard-coded values with the appropriate variables. You continue to build the package one step at a time in this manner. Try to build each component of the package with the smallest testable unit of code. Once that unit of code is returning the correct result and the syntax is free of errors, you can then turn to building the next unit.

The following example contains only the SQL SELECT statement. You are well advised to write the SQL SELECT statement first and then test it for a known value. In this case, the student_id is 145 and the section_id is 106.

For Example  ch21_12.sql

      SELECT GRADE_TYPE_CODE,
             NUMBER_PER_SECTION,
             PERCENT_OF_FINAL_GRADE,
             DROP_LOWEST
        FROM grade_Type_weight
       WHERE section_id = 106
         AND section_id IN (SELECT section_id
                             FROM grade
                            WHERE student_id = 145)

This SELECT statement is now put into the package:

For Example  ch21_13.sql

CREATE OR REPLACE PACKAGE MANAGE_GRADES AS
--  Cursor to loop through all grade types for a given section
      CURSOR  c_grade_type
             (pc_section_id  section.section_id%TYPE,
              PC_student_ID  student.student_id%TYPE)
             IS
      SELECT GRADE_TYPE_CODE,
             NUMBER_PER_SECTION,
             PERCENT_OF_FINAL_GRADE,
             DROP_LOWEST
       FROM  grade_Type_weight
      WHERE  section_id = pc_section_id
        AND  section_id IN (SELECT section_id
                              FROM grade
                             WHERE student_id = pc_student_id);
END MANAGE_GRADES;

Creating the c_grade Cursor

The next example shows the expansion of the manage_grades package through the addition of a section cursor called c_grades. This cursor will take a grade type code, a student ID, and a section ID and return all the grades for that student for that section of that grade type. For example, if Alice was enrolled in the Introduction to Java section, this cursor could be used to gather all of her quiz grades.

For Example  ch21_14.sql

CREATE OR REPLACE PACKAGE MANAGE_GRADES AS
  -- Cursor to loop through all grade types for a given section.
      CURSOR  c_grade_type
             (pc_section_id  section.section_id%TYPE,
              PC_student_ID  student.student_id%TYPE)
             IS
      SELECT GRADE_TYPE_CODE,
             NUMBER_PER_SECTION,
             PERCENT_OF_FINAL_GRADE,
             DROP_LOWEST
        FROM grade_Type_weight
       WHERE section_id = pc_section_id
         AND section_id IN (SELECT section_id
                              FROM grade
                             WHERE student_id = pc_student_id);
    -- Cursor to loop through all grades for a given student
    -- in a given section.
     CURSOR  c_grades
              (p_grade_type_code
                   grade_Type_weight.grade_type_code%TYPE,
              pc_student_id  student.student_id%TYPE,
              pc_section_id  section.section_id%TYPE) IS
      SELECT grade_type_code,grade_code_occurrence,
             numeric_grade
      FROM   grade
      WHERE  student_id = pc_student_id
      AND    section_id = pc_section_id
      AND    grade_type_code = p_grade_type_code;
END MANAGE_GRADES;

Creating the Function final_grade

The next step is to add a function to this package specification called final_grade. This function will have two IN parameters: the student ID and the section ID. It will return a number—that student’s final grade in that section—plus an exit code. The reason you add an exit code instead of raise exceptions is because this approach makes the procedure more flexible and allows the calling program to choose how to proceed depending on the specific error code generated.

For Example  ch21_15.sql

CREATE OR REPLACE PACKAGE MANAGE_GRADES AS
  -- Cursor to loop through all grade types for a given section.
      CURSOR   c_grade_type
             (pc_section_id  section.section_id%TYPE,
              PC_student_ID  student.student_id%TYPE)
             IS
      SELECT GRADE_TYPE_CODE,
             NUMBER_PER_SECTION,
             PERCENT_OF_FINAL_GRADE,
             DROP_LOWEST
      FROM   grade_Type_weight
     WHERE   section_id = pc_section_id
       AND section_id IN (SELECT section_id
                            FROM grade
                           WHERE student_id = pc_student_id);
    -- Cursor to loop through all grades for a given student
    -- in a given section.
     CURSOR  c_grades
             (p_grade_type_code
                 grade_Type_weight.grade_type_code%TYPE,
              pc_student_id  student.student_id%TYPE,
              pc_section_id  section.section_id%TYPE) IS
      SELECT  grade_type_code,grade_code_occurrence,
              numeric_grade
       FROM   grade
       WHERE  student_id = pc_student_id
       AND    section_id = pc_section_id
       AND    grade_type_code = p_grade_type_code;
  -- Function to calculate a student's final grade
  -- in one section
     Procedure final_grade
      (P_student_id   IN student.student_id%type,
       P_section_id   IN section.section_id%TYPE,
       P_Final_grade  OUT enrollment.final_grade%TYPE,
       P_Exit_Code    OUT CHAR);
END MANAGE_GRADES;

The next step is to add the function to the package body. To perform this calculation, you will need a number of variables to hold values as the calculation is carried out.

This exercise is also a very good review of the data relationships among the student tables. Before you read through the next step, review Appendix B, which has an entity–relationship diagram (ERD) of the STUDENT schema and descriptions of the tables and their columns.

When calculating the final grade, there are many things that you must keep in mind:

Image Each student is enrolled in a course, and this information is captured in the enrollment table.

Image The table holds the final grade only for each student enrolled in one section.

Image Each section has its own set of elements that are evaluated to calculate the final grade.

Image All grades for these elements (which have been entered, meaning there is no NULL value in the database) are in the grade table.

Image Every grade has a grade type code. These codes represent the grade type. For example, the grade type QZ stands for quiz. The description of each GRADE_TYPE comes from the GRADE_TYPE table.

Image The GRADE_TYPE_WEIGHT table holds key information for this calculation. There is one entry in this table for each grade type that is utilized in a given section (not all grade types exist for each section).

Image In the GRADE_TYPE_WEIGHT table, the NUMBER_PER_SECTION column lists how many times a grade type should be entered to compute the final grade for a particular student in a particular section of a particular course. This helps you determine whether all grades for a given grade type have been entered and whether too many grades for a given grade type have been entered.

Image You must take into consideration the drop_lowest flag. The drop_lowest flag can hold a value of Y or N. If the drop lowest flag is Y [Y = Yes, N = No], then you must drop the lowest grade from the grade type when calculating the final grade. ThePERCENT_OF_FINAL_GRADE column refers to all the grades for a given grade type. If the homework element represents 20 percent of the final grade, and there are five homework assignments and a drop_lowest flag, then each remaining homework is worth 5 percent. When calculating the final grade, you divide the PERCENT_OF_FINAL_GRADE by the NUMBER_PER_SECTION (note that would be NUMBER_PER_SECTION – 1 if drop_lowest = Y).

Exit codes should be defined as one of the following five:

S = Success, the final grade has been computed. If the grade cannot be computed, then the final grade will be NULL and the exit code will be one of the other four options.

 I = Incomplete; not all the required grades have been entered for this student in this section.

T = Too many grades exist for this student. For example, there should be only four homework grades, but instead there are six.

N = No grades have been entered for this student in this section.

E = There was a general computation error (exception When_others). This kind of exit code allows the procedure to compute final grades when it can; if an Oracle error is somehow raised by some of them, the calling program can still proceed with the grades that have been computed.

To calculate the final grade, you will need a number of variables to hold temporary values during the calculation. Initially the code will create all the variables for the procedure final_grade, but then it will leave the main block with just the statement NULL; this allows you to compile the procedure and check all of the syntax for the variable declaration one step at a time.

The student_id, section_id, and grade_type_code will be values carried from one part of the program to another—which is why you created a variable for each of them. Each instance of a grade will be computed to determine what percentage of the final grade it represents. A counter is needed while processing each individual grade to ensure there are enough grades for the given grade count. A lowest grade variable holds each grade during the examination to see whether it is the lowest. In the end, once the lowest grade is known for a given grade type, it can be removed from the final grade. Additionally, two variables are used as row counters to determine whether the cursor was opened.

The next example shows the package body in a stub format; that is, this example includes all of the necessary variables but no actual processing code has been written. The reason you start with this step when writing the package body is to ensure that all of the syntax is correct. Once this stub compiles without errors, you can then work on the rest of the code for the package body.

For Example  ch21_16.sql

CREATE OR REPLACE PACKAGE BODY MANAGE_GRADES AS
    Procedure final_grade
      (P_student_id   IN student.student_id%type,
       P_section_id   IN section.section_id%TYPE,
       P_Final_grade  OUT enrollment.final_grade%TYPE,
       P_Exit_Code    OUT CHAR)
IS
     v_student_id             student.student_id%TYPE;
     v_section_id             section.section_id%TYPE;
     v_grade_type_code        grade_type_weight.grade_type_code%TYPE;
     v_grade_percent          NUMBER;
     v_final_grade            NUMBER;
     v_grade_count            NUMBER;
     v_lowest_grade           NUMBER;
     v_exit_code              CHAR(1) := 'S';
     v_no_rows1               CHAR(1) := 'N';
     v_no_rows2               CHAR(1) := 'N';
     e_no_grade               EXCEPTION;
BEGIN
     NULL;
END;
END MANAGE_GRADES;

The full package body is provided in the next example. Comments have been placed inside the code to explain what is being done at each step. It is a good idea to include comments within your code to help the next person who has to make changes to the package.

For Example  ch21_17.sql

CREATE OR REPLACE PACKAGE BODY MANAGE_GRADES AS
    Procedure final_grade
      (P_student_id  IN student.student_id%type,
       P_section_id  IN section.section_id%TYPE,
       P_Final_grade OUT enrollment.final_grade%TYPE,
       P_Exit_Code   OUT CHAR)
IS
     v_student_id          student.student_id%TYPE;
     v_section_id          section.section_id%TYPE;
     v_grade_type_code     grade_type_weight.grade_type_code%TYPE;
     v_grade_percent       NUMBER;
     v_final_grade         NUMBER;
     v_grade_count         NUMBER;
     v_lowest_grade        NUMBER;
     v_exit_code           CHAR(1) := 'S';
     v_no_rows1            CHAR(1) := 'N';
     v_no_rows2            CHAR(1) := 'N';
     e_no_grade            EXCEPTION;
BEGIN
    v_section_id := p_section_id;
    v_student_id := p_student_id;
    -- Start loop of grade types for the section.
       FOR r_grade in c_grade_type(v_section_id, v_student_id)
       LOOP
    -- Since cursor is open it has a result
    -- set; change indicator.
          v_no_rows1 := 'Y';
    -- To hold the number of grades per section,
    -- reset to 0 before detailed cursor loops.
          v_grade_count := 0;
          v_grade_type_code := r_grade.GRADE_TYPE_CODE;
    -- Variable to hold the lowest grade.
    -- 500 will not be the lowest grade.
          v_lowest_grade := 500;
    -- Determine what to multiply a grade by to
    -- compute final grade; must take into consideration
    -- if the drop lowest grade indicator is Y.
          SELECT (r_grade.percent_of_final_grade /
                  DECODE(r_grade.drop_lowest, 'Y',
                              (r_grade.number_per_section - 1),
                               r_grade.number_per_section
                      ))* 0.01
           INTO  v_grade_percent
           FROM dual;
    -- Open cursor of detailed grade for a student in a
    -- given section.
           FOR r_detail in c_grades(v_grade_type_code,
                             v_student_id, v_section_id) LOOP
        -- Since cursor is open it has a result
        -- set; change indicator.
              v_no_rows2 := 'Y';
              v_grade_count  := v_grade_count + 1;
        -- Handle the situation where there are more
        -- entries for grades of a given grade type
        -- than there should be for that section.
              If v_grade_count > r_grade.number_per_section THEN
                 v_exit_code := 'T';
                 raise e_no_grade;
              END IF;
        -- If drop lowest flag is Y determine which is lowest
        -- grade to drop.
              IF  r_grade.drop_lowest = 'Y' THEN
                   IF nvl(v_lowest_grade, 0) >=
                          r_detail.numeric_grade
                 THEN
                       v_lowest_grade := r_detail.numeric_grade;
                   END IF;
              END IF;
       -- Increment the final grade with percentage of current
       -- grade in the detail loop.
             v_final_grade := nvl(v_final_grade, 0) +
                    (r_detail.numeric_grade * v_grade_percent);
          END LOOP;
       -- Once detailed loop is finished, if the number of grades
       -- for a given student for a given grade type and section
       -- is less than the required amount, raise an exception.
             IF  v_grade_count < r_grade.NUMBER_PER_SECTION THEN
                 v_exit_code := 'I';
                 raise e_no_grade;
             END IF;
       -- If the drop lowest flag was Y, then you need to take
       -- the lowest grade out of the final grade; it was not
       -- known when it was added which was the lowest grade
       -- to drop until all grades were examined.
             IF  r_grade.drop_lowest = 'Y' THEN
                 v_final_grade := nvl(v_final_grade, 0) -
                          (v_lowest_grade *  v_grade_percent);
             END IF;
      END LOOP;
   -- If either cursor had no rows then there is an error.
   IF v_no_rows1 = 'N' OR v_no_rows2 = 'N'  THEN
      v_exit_code := 'N';
      raise e_no_grade;
   END IF;
   P_final_grade  := v_final_grade;
   P_exit_code    := v_exit_code;
   EXCEPTION
     WHEN e_no_grade THEN
       P_final_grade := null;
       P_exit_code   := v_exit_code;
     WHEN OTHERS THEN
       P_final_grade := null;
       P_exit_code   := 'E';
 END final_grade;
END MANAGE_GRADES;

The following example is an anonymous block to test the final_grade procedure. The block asks for a student_id and a section_id and returns the final grade and an exit code.

It is often a good idea to review the parameter order for the procedure before you write the anonymous block to run the code. In SQL*Plus, this can be done by running a describe command on a procedure.

SQL> desc manage_grades
PROCEDURE FINAL_GRADE

Argument Name                  Type                    In/Out Default?
------------------------------ ----------------------- ------ --------
 P_STUDENT_ID                  NUMBER(8)               IN
 P_SECTION_ID                  NUMBER(8)               IN
 P_FINAL_GRADE                 NUMBER(3)               OUT
 P_EXIT_CODE                   CHAR                    OUT

In SQL Developer, you can expand the node for packages and hover your cursor over the procedure to obtain more details. By doing so, you can see both what has been declared in the package header and what is compiled in the package body. This is illustrated in Figure 21.1.

Image

Figure 21.1 Manage_Grades Package as Seen in SQL Developer

The following example is an anonymous block that can be used to run the package manage_grades.

For Example  ch21_18.sql

SET SERVEROUTPUT ON

DECLARE
 v_student_id   student.student_id%TYPE := &sv_student_id;
 v_section_id   section.section_id%TYPE := &sv_section_id;
 v_final_grade  enrollment.final_grade%TYPE;
 v_exit_code    CHAR;
BEGIN
  manage_grades.final_grade(v_student_id, v_section_id,
     v_final_grade, v_exit_code);
  DBMS_OUTPUT.PUT_LINE('The Final Grade is '||v_final_grade);
  DBMS_OUTPUT.PUT_LINE('The Exit Code is '||v_exit_code);
END;

If you were to run this script for a student_id of 102 and a section_id of 89, you would get the following result in SQL*Plus. In SQL Developer, you would see the full code as you ran it and with the variables substituted for 102 and 89. Both outputs have the same final lines that appear after the anonymous block completes.

Enter value for sv_student_id: 102
old  2:  v_student_id  student.student_id%TYPE := &sv_student_id;
new  2:  v_student_id  student.student_id%TYPE := 102;
Enter value for sv_section_id: 86
old  3:  v_section_id  section.section_id%TYPE := &sv_section_id;
new  3:  v_section_id  section.section_id%TYPE := 86;
The Final Grade is 89
The Exit Code is S
PL/SQL procedure successfully completed.

The next step is to add a function to the manage_grades package specification called median_grade that takes in a course number (p_cource_number), a section number (p_section_number), and a grade type (p_grade_type) and returns awork_grade.grade%TYPE. Cursors that will be used by this function also need to be added as well as any types that will be required by the function.

For Example  ch21_19.sql

CREATE OR REPLACE PACKAGE MANAGE_GRADES AS
  -- Cursor to loop through all grade types for a given section.
      CURSOR c_grade_type
              (pc_section_id  section.section_id%TYPE,
               PC_student_ID  student.student_id%TYPE)
              IS
       SELECT GRADE_TYPE_CODE,
              NUMBER_PER_SECTION,
              PERCENT_OF_FINAL_GRADE,
              DROP_LOWEST
         FROM grade_Type_weight
        WHERE section_id = pc_section_id
          AND section_id IN (SELECT section_id
                               FROM grade
                              WHERE student_id = pc_student_id);
    -- Cursor to loop through all grades for a given student
    -- in a given section.
     CURSOR  c_grades
             (p_grade_type_code
                  grade_Type_weight.grade_type_code%TYPE,
              pc_student_id  student.student_id%TYPE,
              pc_section_id  section.section_id%TYPE) IS
       SELECT grade_type_code,grade_code_occurrence,
              numeric_grade
       FROM   grade
       WHERE  student_id = pc_student_id
       AND    section_id = pc_section_id
       AND    grade_type_code = p_grade_type_code;
  -- Function to calculate a student's final grade
  -- in one section.
     Procedure final_grade
       (P_student_id   IN student.student_id%type,
        P_section_id   IN section.section_id%TYPE,
        P_Final_grade  OUT enrollment.final_grade%TYPE,
        P_Exit_Code    OUT CHAR);
    -- ---------------------------------------------------------
    -- Function to calculate the median grade .
      FUNCTION median_grade
         (p_course_number section.course_no%TYPE,
          p_section_number section.section_no%TYPE,
          p_grade_type grade.grade_type_code%TYPE)
        RETURN grade.numeric_grade%TYPE;
    CURSOR c_work_grade
          (p_course_no  section.course_no%TYPE,
           p_section_no section.section_no%TYPE,
           p_grade_type_code grade.grade_type_code%TYPE
           )IS
      SELECT distinct numeric_grade
        FROM grade
       WHERE section_id = (SELECT section_id
                             FROM section
                            WHERE course_no= p_course_no
                              AND section_no = p_section_no)
         AND grade_type_code = p_grade_type_code
      ORDER BY numeric_grade;
    TYPE t_grade_type IS TABLE OF c_work_grade%ROWTYPE
      INDEX BY BINARY_INTEGER;
    t_grade t_grade_type;
END MANAGE_GRADES;

The next step is to add a function to the manage_grades package specification called median_grade that takes in a course number (p_cnumber), a section number (p_snumber), and a grade type (p_grade_type). This function will return the median grade (work_grade.grade%TYPE data type) based on those three components. For example, one might use this function to answer the question, “What is the median grade of homework assignments in Introduction to Java section 2?” A true median can contain two values. Because this function can return only one value, if the median is made of two values, then the function will return the average of the two.

For Example  ch21_20.sql

CREATE OR REPLACE PACKAGE MANAGE_GRADES AS
CREATE OR REPLACE PACKAGE BODY MANAGE_GRADES AS
    Procedure final_grade
      (P_student_id  IN student.student_id%type,
       P_section_id  IN section.section_id%TYPE,
       P_Final_grade OUT enrollment.final_grade%TYPE,
       P_Exit_Code   OUT CHAR)
IS
     v_student_id          student.student_id%TYPE;
     v_section_id          section.section_id%TYPE;
     v_grade_type_code     grade_type_weight.grade_type_code%TYPE;
     v_grade_percent       NUMBER;
     v_final_grade         NUMBER;
     v_grade_count         NUMBER;
     v_lowest_grade        NUMBER;
     v_exit_code           CHAR(1) := 'S';
   --  Next two variables are used to calculate whether a cursor
   --  has no result set.
     v_no_rows1              CHAR(1) := 'N';
     v_no_rows2              CHAR(1) := 'N';
     e_no_grade              EXCEPTION;
BEGIN
    v_section_id := p_section_id;
    v_student_id := p_student_id;
    -- Start loop of grade types for the section.
       FOR r_grade in c_grade_type(v_section_id, v_student_id)
       LOOP
    -- Since cursor is open it has a result
    -- set; change indicator.
          v_no_rows1 := 'Y';
    -- To hold the number of grades per section,
    -- reset to 0 before detailed cursor loops.
          v_grade_count := 0;
          v_grade_type_code := r_grade.GRADE_TYPE_CODE;
    -- Variable to hold the lowest grade.
    -- 500 will not be the lowest grade.
          v_lowest_grade := 500;
    -- Determine what to multiply a grade by to
    -- compute final grade; must take into consideration
    -- if the drop lowest grade indicator is Y.
          SELECT (r_grade.percent_of_final_grade /
                  DECODE(r_grade.drop_lowest, 'Y',
                              (r_grade.number_per_section - 1),
                               r_grade.number_per_section
                 ))* 0.01
           INTO  v_grade_percent
           FROM dual;
    -- Open cursor of detailed grade for a student in a
    -- given section.
           FOR r_detail in c_grades(v_grade_type_code,
                             v_student_id, v_section_id) LOOP
        -- Since cursor is open it has a result
        -- set; change indicator.
              v_no_rows2 := 'Y';
              v_grade_count  := v_grade_count + 1;
        -- Handle the situation where there are more
        -- entries for grades of a given grade type
        -- than there should be for that section.
              If v_grade_count > r_grade.number_per_section THEN
                 v_exit_code := 'T';
                 raise e_no_grade;
              END IF;
        -- If drop lowest flag is Y determine which is lowest
        -- grade to drop.
              IF  r_grade.drop_lowest = 'Y' THEN
                   IF nvl(v_lowest_grade, 0) >=
                          r_detail.numeric_grade
                 THEN
                       v_lowest_grade := r_detail.numeric_grade;
                   END IF;
              END IF;
       -- Increment the final grade with percentage of current
       -- grade in the detail loop.
             v_final_grade := nvl(v_final_grade, 0) +
                    (r_detail.numeric_grade * v_grade_percent);
          END LOOP;
       -- Once detailed loop is finished, if the number of grades
       -- for a given student for a given grade type and section
       -- is less than the required amount, raise an exception.
             IF  v_grade_count < r_grade.NUMBER_PER_SECTION THEN
                 v_exit_code := 'I';
                 raise e_no_grade;
             END IF;
       -- If the drop lowest flag was Y then you need to take
       -- the lowest grade out of the final grade. It was not
       -- known when it was added which was the lowest grade
       -- to drop until all grades were examined.
             IF  r_grade.drop_lowest = 'Y' THEN
                 v_final_grade := nvl(v_final_grade, 0) -
                          (v_lowest_grade *  v_grade_percent);
             END IF;
      END LOOP;
   -- If either cursor had no rows then there is an error.
   IF v_no_rows1 = 'N' OR v_no_rows2 = 'N'  THEN
      v_exit_code := 'N';
      raise e_no_grade;
   END IF;
   P_final_grade  := v_final_grade;
   P_exit_code    := v_exit_code;
   EXCEPTION
     WHEN e_no_grade THEN
       P_final_grade := null;
       P_exit_code   := v_exit_code;
     WHEN OTHERS THEN
       P_final_grade := null;
       P_exit_code   := 'E';
 END final_grade;

FUNCTION median_grade
  (p_course_number section.course_no%TYPE,
  p_section_number section.section_no%TYPE,
  p_grade_type grade.grade_type_code%TYPE)
RETURN grade.numeric_grade%TYPE
  IS
  BEGIN
    FOR r_work_grade
       IN c_work_grade(p_course_number, p_section_number, p_grade_type)
    LOOP
      t_grade(NVL(t_grade.COUNT,0) + 1).numeric_grade := r_work_grade.numeric_grade;
    END LOOP;
    IF t_grade.COUNT = 0
    THEN
      RETURN NULL;
    ELSE
      IF MOD(t_grade.COUNT, 2) = 0
      THEN
        -- There is an even number of work grades. Find the middle
        --   two and average them.
        RETURN (t_grade(t_grade.COUNT / 2).numeric_grade +
                 t_grade((t_grade.COUNT / 2) + 1).numeric_grade
                ) / 2;
      ELSE
        -- There is an odd number of grades. Return the one in the middle.
        RETURN t_grade(TRUNC(t_grade.COUNT / 2, 0) + 1).numeric_grade;
      END IF;
    END IF;
  EXCEPTION
    WHEN OTHERS
    THEN
      RETURN NULL;
  END median_grade;
END MANAGE_GRADES;

The following example is a SELECT statement that makes use of the function median_grade and shows the median grade for all grade types in sections 1 and 2 of course 25.

For Example  ch21_21.sql

SELECT COURSE_NO,
       COURSE_NAME,
       SECTION_NO,
       GRADE_TYPE,
       manage_grades.median_grade
               (COURSE_NO,
                SECTION_NO,
                GRADE_TYPE)
            median_grade
FROM
(SELECT DISTINCT
       C.COURSE_NO        COURSE_NO,
       C.DESCRIPTION      COURSE_NAME,
       S.SECTION_NO       SECTION_NO,
       G.GRADE_TYPE_CODE  GRADE_TYPE
FROM SECTION S, COURSE C, ENROLLMENT E, GRADE G
WHERE C.course_no = s.course_no
AND   s.section_id = e.section_id
AND   e.student_id = g.student_id
AND   c.course_no = 25
AND   s.section_no between 1 and 2
ORDER BY 1, 4, 3) grade_source

The results of the SELECT statement using the median_grade function for all grade types in sections 1 and 2 of course 25 would be as follows:

COURSE_NO  COURSE_NAME    SECTION_NO GRADE_TYPE MEDIAN_GRADE
---------- ------------------------------------ ------------
       25  Intro to Programming    1 FI         98
       25  Intro to Programming    2 FI         71
       25  Intro to Programming    1 HM         76
       25  Intro to Programming    2 HM         83
       25  Intro to Programming    1 MT         86
       25  Intro to Programming    2 MT         89
       25  Intro to Programming    1 PA         91
       25  Intro to Programming    2 PA         97
       25  Intro to Programming    1 QZ         71
       25  Intro to Programming    2 QZ         78

10 rows selected.

Lab 21.4: Package Instantiation and Initialization


After this lab, you will be able to

Image Create Package Variables During Initialization


The first time a session makes any reference to a package, Oracle instantiates the package. If multiple sessions are connected to the database at the same time, each will have its own instantiation of that package. The package is loaded into the SGA of the database instance, which makes all elements of the package available in memory. Anything in the SGA will be accessed more quickly than if the database needs to query tables.

The instantiation process means the following steps will take place:

1. Public constants in the package will be assigned an initial value.

2. Public variables, which have a declaration session, will be assigned an initial value.

3. If there is an initialization section in the package body, it will be executed.

Creating Package Variables During Initialization

The first time a package is called within a user session, the code in the initialization section of the package will be executed if it exists. This step is done only once; it is not repeated if the user calls other procedures or functions for that package. The initialization section encompasses everything between the BEGIN statement and the END statement of the package body. Variables, cursors, and user-defined data types used by numerous procedures and functions can be declared once at the beginning of the package specification and then be used by the functions and procedures within the package without having to declare them again.

The following example creates a package global variable called v_current_date in the student_api package.

For Example  ch21_22.sql

CREATE OR REPLACE PACKAGE  school_api as
  v_current_date DATE;
  PROCEDURE Discount_Cost;
  FUNCTION new_instructor_id
    RETURN instructor.instructor_id%TYPE;
END school_api;

The following script adds an initialization section that assigns the current system date to the variable v_current_date. This variable can then be used in any procedure in the package that needs to make use of the current date.

For Example  ch21_23.sql

CREATE OR REPLACE PACKAGE BODY school_api AS
  PROCEDURE discount_cost
  IS
    CURSOR c_group_discount
    IS
    SELECT distinct s.course_no, c.description
      FROM section s, enrollment e, course c
     WHERE s.section_id = e.section_id
    GROUP BY s.course_no, c.description,
            e.section_id, s.section_id
    HAVING COUNT(*) >=8;
  BEGIN
    FOR r_group_discount IN c_group_discount
    LOOP
    UPDATE course
       SET cost = cost * .95
      WHERE course_no = r_group_discount.course_no;
      DBMS_OUTPUT.PUT_LINE
        ('A 5% discount has been given to'
        ||r_group_discount.course_no||'
       '||r_group_discount.description);
    END LOOP;
   END discount_cost;
  FUNCTION new_instructor_id
    RETURN instructor.instructor_id%TYPE
  IS
    v_new_instid instructor.instructor_id%TYPE;
  BEGIN
    SELECT INSTRUCTOR_ID_SEQ.NEXTVAL
      INTO v_new_instid
      FROM dual;
    RETURN v_new_instid;
  EXCEPTION
    WHEN OTHERS
     THEN
      DECLARE
        v_sqlerrm VARCHAR2(250) :=
           SUBSTR(SQLERRM,1,250);
      BEGIN
        RAISE_APPLICATION_ERROR(-20003,
        'Error in  instructor_id: '||v_sqlerrm);
      END;
  END new_instructor_id;
BEGIN
  SELECT trunc(sysdate, 'DD')
    INTO v_current_date
    FROM dual;
END school_api;

Lab 21.5: SERIALLY_REUSABLE Packages


After this lab, you will be able to

Image Use the SERIALLY_REUSABLE Pragma


In the last section, you learned how to load objects into the SGA as part of the instantiation process. This was done to help improve performance of the package. This process has some drawbacks, however. The objects are held in memory and can produce some undesirable side effects and errors if, for example, a package cursor is left open. Moreover, if package cursors are large, they can hold onto a large amount of the session’s memory and then fail to release it. To avoid these side effects, you can make use of the SERIALLY_REUSABLE pragma.

Using the SERIALLY_REUSABLE Pragma

The SERIALLY_REUSABLE pragma must be used in both the package specification and the package body if you want to take advantage of what it has to offer. This pragma identifies the package as serially reusable. When a package is marked as such, then the package state can be reduced from the entire session to just a call of a program in the package. The result is the opposite of the initialization advantage; it means the values of package variables and other elements will not persist. The syntax to invoke this pragma is to add the following line after IS in the package header and body:

PRAGMA SERIALLY_REUSABLE;

Here are some points to keep in mind when using serialized packages:

Image The global memory for serialized packages is allocated in the SGA, not in the user global area (UGA). This approach allows the package work area to be reused. Each time the package is reused, its package-level variables are initialized to their default values or to NULL, and its initialization section is reexecuted.

Image The maximum number of work areas needed for a serialized package is determined by the number of concurrent users of that package. The increased use of SGA memory is offset by the decreased use of the UGA or program memory. Moreover, the database ages out work areas not in use if it needs to reclaim memory from the SGA for other requests.

Image If a package is not SERIALLY_REUSABLE, its package state is stored in the UGA for each user. Therefore, the amount of UGA memory needed increases linearly with the number of users, limiting scalability. The package state can persist for the life of a session, locking UGA memory until the session ends. In some applications, such as Oracle Office, a typical session lasts several days.

The following script is an extremely simple example that illustrates how the SERIALLY_REUSABLE pragma operates (a longer example would be needed to more clearly show a use case where this pragma would be necessary).

For Example  ch21_24.sql

CREATE OR REPLACE PACKAGE show_date
IS
  PRAGMA SERIALLY_REUSABLE;
  the_date DATE := SYSDATE + 4;
  PROCEDURE display_DATE;
  PROCEDURE set_date;
END show_date;
/
CREATE OR REPLACE PACKAGE BODY show_date
IS
  PRAGMA SERIALLY_REUSABLE;
  PROCEDURE display_DATE IS
  BEGIN
    DBMS_OUTPUT.PUT_LINE ('The date is  ' || show_date.the_date);
  END;
  -- Initialize package state
  PROCEDURE set_date IS
  BEGIN
    show_date.the_date := sysdate;
  END;
END show_date;

The next example shows a PL/SQL block to execute this procedure and illustrate how it behaves.

For Example  ch21_25.sql

begin
     -- initialize and print the package variable
     show_date.display_DATE;
     -- change the value of the variable the_date
     show_date.set_date;
     -- Display the new value of variable the_date
     show_date.display_DATE;
     end;
/
begin
     show_date.display_DATE;
end;
/

If this script were run on July 27, 2014, the result of this would be as follows:

anonymous block completed
The date is  31-JUL-14

The date is  27-JUL-14

anonymous block completed
The date is  31-JUL-14

The example shows how the value of the variable the_date changes depending on how it is called. When the SERIALLY_REUSABLE pragma is not used, the value of a package variable persists in memory and does not change until a program changes it programmatically. In this case, because the package uses the SERIALLY_REUSABLE pragma, the behavior is different. The first time the package is called in a PL/SQL block, the initialization section of the package is called and the value of the variable the_date is set to the system date plus four days. This value is then displayed. Next, the procedure show_date.set_date is executed and the value of the_date is reset to be the system date. Because the SERIALLY_REUSABLE pragma has been used, the value of the_date is not retained. The next time the package is referenced in a second PL/SQL block, the value of the_date is reset by the initialization section of the package.

When the package uses the pragma SERIALLY_REUSABLE, however, the package state is kept in the work area of the system global area. The package state will persist only for the duration of a server call. Once that call completes, the work area is flushed. If another server call references the same package, Oracle will reinstantiate the package—which means it reinitializes the package. Anything that changed the variables in the package will be lost. Once a unit of work is complete, the Oracle database takes care of the following tasks:

Image Closes any open cursors.

Image Frees some nonreusable memory

Image Returns the package instantiation to the pool of reusable instantiations kept for this package

Database triggers, stand-alone SQL statements, and any other type of PL/SQL subprogram cannot access a package that makes use of the SERIALLY_REUSABLE pragma.

Summary

In this chapter, you learned how to create packages. You first investigated the details of the package specification and the package body. You also learned how to call the stored package and explored the various types of package components such as private objects and cursor variables. Then you were introduced to an elaborate package that pulled together many of the concepts discussed in this and other chapters. Initialization of the package was addressed in terms of initialization of variables. Additionally, you saw how to prevent Oracle from holding onto memory by using theSERIALLY_REUSABLE pragma in the package definition.


By the Way

The companion website provides additional exercises and suggested answers for this chapter, with discussion related to how those answers resulted. The main purpose of these exercises is to help you test the depth of your understanding by utilizing all of the skills that you have acquired throughout this chapter.