155-160 | High Performance Oracle Database Automation: Creating Oracle Applications with SQL and PL/SQL

Table of Contents

Local Functions

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.

Dependencies

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

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.

Datatypes

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.

Return Values

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;

Table of Contents