Page 211
referenced by components of the package body. Figure 9.6 illustrates the basic structure of two package components.
FIG. 9.6
Structure of a package
specification and a
package body.
The package specification begins with the keyword package followed by the name of the package. The specification or Declaration section begins with the keyword is. This section contains the definition of the procedures, functions, variables, and cursors that will be accessible from applications. These are the public elements. The definitions should also include the procedure or function parameters. This part of the package does not contain executable statements or exception handlers. The package specification contains all the information a developer needs to call objects in the package. The developer should never have to see the code behind the objects. The package specification is completed by the keyword end followed by the name of the package.
The package body begins with the keywords package body followed by the name of the package. The package name needs to be the same as in the package specification. The specifications begin following the keyword is.
The package body can also contain its own executable section. This section starts with the keyword begin. It can have an exception section and is completed by the keyword end. The purpose of the package body Executable section is to initialize variables or execute some statements when the package is initiated. The package body is terminated by the keyword end followed by the name of the package.
Page 212
Listing 9.13 illustrates the creation of a package that contains many of the objects that have been developed as examples in this chapter. It contains a cursor similar to Listing 9.3, a procedure similar to Listing 9.11, and a function similar to Listing 9.12. These examples have one thing in common: They all access the Employee table. It enables you to exercise object-oriented design by centralizing Employee objects into one package.
Listing 9.13 L_09_13.sqlCreating an Employee Package
SQL> create package employee_objects 2 is 3 cursor employee_list return employee%rowtype; 4 procedure dpt_employees (dept in char); 5 function avg_age (dept in employee.fk_department%type) return number; 6 end employee_objects; 7 / Package created.
To create the package specification, use the Create Package command. You must create the package specification before the package body. This means that the actual executable statements contained in the package body do not have to exist to create the package body. If the objects are called and code does not exist, the package does not produce any results. It is possible to have packages that do not contain a body. These are called bodiless packages. They may contain objects that do not need code such as exception handler definitions.
The specification in Listing 9.13 defines three objects. The first is a cursor called employee_list. This cursor returns the first and last names of each employee. The second object is a procedure that lists the employees for a particular department. The final object is a function that computes the average hiring age for employee in a particular department.
The cursor definition begins with the keyword cursor followed by the cursor name. Cursor package specifications need a return data type. This specifies the variable that will contain the cursor object. In the case of this cursor, the data definition consists of creating a variable array called a table record. To accomplish this, use the %rowtype declaration preceded by the table name Employee. When the cursor contains multiple values in the select statement, you must use a virtual record in the return variable specification. This can be a cursor record or a table record. To create a cursor record, placing the name of the cursor before the %rowtype. Placing the name of the table creates a table record.
The procedure definition begins with the keyword procedure followed by the procedure name. This procedure is similar to the one contained in Listing 9.11. Because this procedure needs to have the department number value supplied to it, an input variable (dept) is defined. This specification uses the keyword in to denote dept is an input variable. You might notice that the data type size did not need to be specified. In fact, when the size is specified, the package will not compile.
Page 213
The final object defined is the function avg_age. The function definition begins with the keyword function followed by the name of the object. This function is similar to the one illustrated in Listing 9.12. The procedure computes the average employee hiring age for a department. The function needs the department value supplied to it. It also has a return data type because it returns a value to the calling application.
Listing 9.14 illustrates the definition of the package body for the employee_objects package. This script contains the executable statements for the cursor, procedure, and functions in the employee_objects specification.
Listing 9.14 L_09_14.sqlCreating a Package Body for the Employee_objects
SQL> create package body employee_objects is 2 cursor employee_list return employee%rowtype 3 is 4 select * 5 from employee 6 order by 1; 7 procedure dpt_employees (dept in char) 8 is 9 e_rec employee%rowtype; 10 cursor a is select last_name from employee 11 where fk_department = dept 12 order by 1; 13 begin 14 for deptlist in a 15 loop 16 dbms_output.put_line (deptlist.last_name); 17 end loop; 18 end; 19 function avg_age (dept in employee.fk_department%type) return number 20 is 21 avgage number; 22 begin 23 select avg((employment_date-birth_date)/365) 24 into avgage 25 from employee 26 where fk_department = dept; 27 return avgage; 28 end; 29 29 end employee_objects; 30 / Package body created.
Page 214
The first package body definition is for the cursor employee_list. The procedure performs the same function as the cursor definition in Listing 9.3. The cursor selects all the records from the Employee table. The package definition is similar to a regular cursor except it has a return keyword followed by a data type. This specifies the variables and data types that hold the cursor rows. Because the cursor selects all the rows from the employee table, this Listing necessarily defines a device that holds those rows. In this case, the definition used the %rowtype declaration to set up an array of variables that match the Employee table's row.
The second definition consists of a procedure called dpt_employees. It is very similar in functionality to the procedure defined in Listing 9.11. The procedure displays the last names of employees of a specified department. The department number is fed into the procedure by using the "dept" variable. This variable is then used in a cursor internal to the procedure to limit the records to those of the specified department. When declaring the procedure in a package, you do not have to include the name of the procedure following the end keyword.
The third definition is a function. It is based on the function created in Listing 9.12. The function computes and returns the average hiring age for the employees in the specified departmentto the calling application.
You should be careful about several things concerning the creation of packages, including the following:
CAUTION |
I find it somewhat difficult to build packages in the SQL*PLUS environment. The problems occur when your statement are not correct. SQL*PLUS does not give you good error messages. You simply get a warning such as: "Warning: Package Body created with compilation errors." When you see this warning, the object you created will not work. You must then find the problem and correct it. |
The package objects are called by other applications using the name of the package and the name of the object. The names are separated by dot notation. This is the same notation discussed in Chapter 5 to qualify table columns in a join select statement. Listing 9.15 illustrates the use of the cursor "Employee_list" that was defined in the previous section. You do not have