Previous | Table of Contents | Next |
Functions may also be declared within another block of stored PL/SQL code. This is somewhat unusual. Most functions are created as standalone objects to provide greater modularity. Listing 5.5 illustrates the definition of a function as part of a procedure s declarations.
Listing 5.5 Declaring a local function within a procedure.
PROCEDURE Annual_Review IS iPerformanceRating integer; iWarningsIssued integer; nBaseSalary number; nOntimeRating number; nTotalRaisePercent number; nTotalBonus number; -- -- The total number of working days in the year. This is -- calculated as follows: -- -- 104 weekend days -- 10 paid holidays (11 in leap year) -- 10 sick days -- TOTAL_WORKING_DAYS CONSTANT integer := 241; -- -- Any employee working for the company for over one year. -- CURSOR All_Employees_cur IS SELECT employee_num, eff_hire_date, base_salary, late_days, warnings, performance_rating FROM EMPLOYEES WHERE (to_date (SYSDATE, 'YYYY') - to_date (eff_hire_date, 'YYYY')) > 1; FUNCTION Raise_Salary (nBaseSalary IN number, nRaiseAmount IN number) RETURN number IS BEGIN RETURN (nBaseSalary * nRaiseAmount); END; BEGIN FOR All_Employees_rec IN All_Employees_cur LOOP -- -- Initialize the variables each time through the -- loop (once for each employee). -- iPerformanceRating := All_Employees_rec.performance_rating; iWarningsIssued := All_Employees_rec.warnings; nBaseSalary := All_Employees_rec.base_salary; nOntimeRating := 0; nTotalRaisePercent := 0.0; nIncreasedSalary := 0.0; nTotalBonus := 0.0; -- -- Calculate the number of days that the employee was on time -- for work. If this percentage is above 98%, the employee -- earns a .5% pay raise. -- nOntimeRating := ( TOTAL_WORKING_DAYS - All_Employees_rec.late_days); nOntimeRating := (nOntimeRating / TOTAL_WORKING_DAYS) * 100; IF (nOntimeRating > 98) THEN nTotalRaisePercent := nTotalRaisePercent + 0.005; -- -- Perfect attendance gets a higher bonus percentage too! -- IF (nOntimeRating = 100) THEN nTotalRaisePercent := nTotalRaisePercent + 0.001; END IF; END IF; . . . -- -- Store the outcome of the analysis in the EMPLOYEES table. -- UPDATE EMPLOYEES SET base_salary = nIncreasedSalary, xmas_bonus = nTotalBonus WHERE CURRENT OF All_Employees_cur; END LOOP; END;
Local functions are accessible only to the procedure or function that declares the local object. Local functions can also reference constants, variables, datatypes, and user -defined exceptions defined within the containing procedure or function. In Listing 5.5, the Raise_Salary() function is accessible only to the procedure Annual_Review() and can access all the variables and constants defined within the procedure.
If a function needs to be referenced from more than one stored object, local definitions aren t appropriate. For maintenance and testing purposes, it s probably better to define most (if not all) objects as standalone objects.
When an object makes a reference to another object, a dependency is created. The ALL_DEPENDENCIES view in Oracle s data dictionary contains a complete listing of the dependencies among objects stored in the database. The structure of the ALL_DEPENDENCIES view is shown in Listing 5.6.
Listing 5.6 The structure of the ALL_DEPENDENCIES view.
name varchar2 (30) type varchar2 (12) owner varchar2 (30) referenced_name varchar2 (30) referenced_type varchar2 (12) referenced_owner varchar2 (30)
Determining The Effects Of A Code Change
You can use the following query to find objects that have a dependency to a block of code that you need to modify.SELECT type ' ' owner '.' name FROM ALL_DEPENDENCIES WHERE referenced_owner = upper ('&1') AND referenced_name = upper ('&2') ORDER BY type;In this example, &1 is the schema of the object that you need to modify, and &2 is the name of the object. The query might produce an output as follows:
FUNCTION HR.RAISE_SALARY FUNCTION HR.ADD_VACATION FUNCTION HR.ADD_BONUS FUNCTION HR.VEST_401K FUNCTION ENGINEERING.ADD_TARDY FUNCTION ENGINEERING.REMOVE_TARDY PROCEDURE HR.ANNUAL_REVIEW PACKAGE BODY HR.PERFORMANCE_LIBRARY
Parameters for functions are handled in the same way as parameters for procedures. Function parameters may be IN parameters, OUT parameters, or IN OUT parameters. Functions may accept no parameters, one parameter, or many parameters ”this is decided by you, the application developer.
Functions are very much like procedures, and the parameters for functions follow the same rules as the parameters for procedures.
Using OUT And IN OUT Parameters
By definition, a PL/SQL function should return a value only through the use of the RETURN statement. However, Oracle allows you to define parameters for functions using the OUT and IN OUT definitions.
Using this approach allows you to have a function return more than one value; however, this is poor programming style and is not recommended. Also, consider that, at some point in the future, Oracle may not allow OUT and IN OUT parameters to be defined for functions. To be safe, it s better to only return values from functions using the RETURN statement.
By definition, a PL/SQL function must return a value to any block of code that calls the procedure. If the function doesn t return a value, an exception will be raised. Functions return a value through the use of the RETURN statement, as shown in Listing 5.7.
Listing 5.7 Use of the RETURN statement in a function.
FUNCTION Raise_Salary (nBaseSalary IN number, nRaisePercent IN number) RETURN number; IS BEGIN RETURN (nBaseSalary * nRaisePercent); END;
Previous | Table of Contents | Next |