Oracle PL/SQL by Example, Fifth Edition (2015)
Chapter 21. Packages
In this chapter, you will learn about
Creating Packages
Cursors Variables
Extending the Package
Package Instantiation and Initialization
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
Create Package Specifications
Create Package Bodies
Call Stored Packages
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
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.
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.”
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.
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
The cursor variable cannot be defined in a package specification.
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.
Do not use FOR UPDATE with OPEN FOR in processing a cursor variable.
You cannot use comparison operators to test cursor variables for equality, inequality, or nullity.
A cursor variable cannot be assigned a null value.
A REF CURSOR type cannot be used in CREATE TABLE or VIEW statements as there is no equivalent data type for a database column.
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.
You cannot store cursor variables in an associative array, nested table, or varray.
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
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:
Each student is enrolled in a course, and this information is captured in the enrollment table.
The table holds the final grade only for each student enrolled in one section.
Each section has its own set of elements that are evaluated to calculate the final grade.
All grades for these elements (which have been entered, meaning there is no NULL value in the database) are in the grade table.
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.
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).
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.
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.
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
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
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:
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.
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.
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:
Closes any open cursors.
Frees some nonreusable memory
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.