 |
|
Creating and Dropping
Functions
Oracle Tips by
Burleson
|
Functions are created using a command like the one shown in Listing
5.4.
Listing 5.4 Creating a function.
CREATE OR REPLACE
FUNCTION Raise_Salary (nBaseSalary IN number,
nRaisePercent IN number)
RETURN number;
IS
BEGIN
RETURN (nBaseSalary * nRaisePercent);
END;
/
Using the OR REPLACE clause indicates that Oracle should
replace an existing function of the same name if it exists. If the
object exists and the OR REPLACE clause isn’t used, an error
will occur. The / instructs SQL*Plus to execute the CREATE
command.
Functions are dropped from inside SQL*Plus using the DROP
command:
DROP FUNCTION Raise_Salary;
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.
This is an excerpt from the book "High Performance Oracle
Database Automation" by Jonathan Ingram and Donald K.
Burleson, Series Editor. |
