Page 208
Listing 9.12 L_09_12.sqlUsing a Function to Calculate the Average Hiring Age of Employees per Department
. SQL> create function a_age (dept in employee.fk_department%type) 2 return number 3 is 4 avg_age number; 5 begin 6 select avg((employment_date-birth_date)/365) 7 into avg_age 8 from employee 9 where fk_department = dept; 10 return avg_age; 11 end; 12 / Function created. SQL> SQL> set serveroutput on; SQL> declare 2 age number; 3 age_dif number; 4 lname varchar2(15); 5 fname varchar2(15); 6 dept varchar2(4); 7 cursor a is 8 select last_name, first_name, fk_department, 9 (employment_date - birth_date)/365 10 from employee; 11 begin 12 open a; 13 fetch a into lname, fname, dept, age; 14 while (a%found) loop 15 age_dif := age - a_age(dept); 16 dbms_output.put_line (lname||' `||fname||' `||'Age Diff `||a_age(dept)); 17 fetch a into lname, fname, dept, age; 18 end loop; 19 end; 20 / COOLIDGE CALVIN Age Diff 59.00593607305936073059360730593607305933 JOHNSON LYNDON Age Diff 53.46536203522504892367906066536203522514 REAGAN RONALD Age Diff 48.42100456621004566210045662100456621 BUSH GEORGE Age Diff 59.00593607305936073059360730593607305933 JOHNSON ANDREW Age Diff 53.46536203522504892367906066536203522514 CLINTON WILLIAM Age Diff 53.46536203522504892367906066536203522514 CARTER JIMMY Age Diff 48.42100456621004566210045662100456621 FORD GERALD Age Diff 59.00593607305936073059360730593607305933 NIXON RICHARD Age Diff 53.46536203522504892367906066536203522514 KENNEDY JOHN Age Diff 53.46536203522504892367906066536203522514 EISENHOWER DWIGHT Age Diff 59.00593607305936073059360730593607305933 TRUMAN HAROLD Age Diff 59.00593607305936073059360730593607305933 ROOSEVELT FRANKLIN Age Diff 53.46536203522504892367906066536203522514 HOOVER HERBERT Age Diff 48.42100456621004566210045662100456621
Page 209
WILSON WOODROW Age Diff 53.46536203522504892367906066536203522514 TAFT WILLIAM Age Diff 48.42100456621004566210045662100456621 ROOSEVELT THEODORE Age Diff 59.00593607305936073059360730593607305933 ANTHONY SUSAN Age Diff 48.42100456621004566210045662100456621 ROOSEVELT ELEANOR Age Diff 48.42100456621004566210045662100456621 PL/SQL procedure successfully completed. This listing is produced by executing l_09_12a.sql to create the function, and l_09_12b.sql to use the function.
The first block in the listing creates the function. You must store it in the database before an application can call it. To declare or place the function in the database, use the Create Function command. The Header section follows this command. It contains the name of the function (a_age) , an input parameter named dept, and a return data type of number.
The parameter (dept) uses the in mode operator to designate that it is an input variable only. "Dept" is defined with the same data type as the fk_department column in the employee table. To do this, use the %type declaration. The declaration section of the function begins after the keyword is. The avg_age variable is used to hold the value returned to the calling expression. It is declared with a data type of number. The Executable section of the function contains a select statement. It calculates the average hire date of employees in a department. The where clause uses the "Dept" input variable to determine for which department to calculate the average.
The Select statement returns one value. This value is assigned to the avg_age variable. This value is returned to the calling application by the return statement in line 10.
The second section of the listing contains the PL/SQL block that calls the function. It is
an anonymous block that contains several local variables and a cursor that calculates the
hiring age for the employee. In the Executable section, a
While loop processes each record in
the Employee table. The first step of the loop is to calculate and assign the age difference to
the variable age_dif. This value is calculated by using the
a_age function. This function returns the second value in the age_dif computation. When Oracle encounters this function, it calls
the function and passes it the value of the "Dept" variable. You specify this variable is
specified following the name of the function. The function is then executed and returns a value to
be used in the expression. This value is used to complete the age_dif calculation. The output
of the calculation displays at the end of the loop.
Functions enable the developer to use the same calculations in multiple applications. A benefit of having the function in your program is that you do not have to test the code in each application in which the calculation is used. If you test the code when you create the function, it will work correctly in all applications in which it is used.
To discard the function from the database, use the Drop Function command.
Page 210
A package is a structure that brings an element of object-oriented design to PL/SQL. Packages are a collection of PL/SQL objects such as cursors, variables, constants, exception names, procedures, and functions. In object-oriented design, the developer identifies an object or entity such as an employee. The object is set up as a class and its properties are defined and functions programmed. These properties and functions are linked to the object. When the developer wants to manipulate an object, he just looks at the object to determine whether the function exists. He can use any of the predefined functions in the object.
You do not have to tie packages to a specific object. Object-oriented concepts can be used, however. If the developer has an object such as an employee, the developer can place all the functions, procedures, constants, and cursors into one package. The objects in this package can then be used in other applications that are developed. This is the height of reusable code. It reduces coding time because the developer has reusable code, ensures that calculations and properties are the same across applications, and frees the developer from having to test the functions used from the package.
By incorporating packages into your application design, some other benefits accrue, including the following:
Packages consist of two parts: the package specification and the package body. The package specification contains the names of the objects other applications can access. These are called public objects. This section does not contain any of the code that actually performs work.
The body section contains the actual statements that perform work. It also contains objects not available to other applications. These are called private objects. These objects can only be