 |
|
PL/SQL Functions
Oracle Tips by
Burleson
|
A function is a simple procedure that accomplishes a very
specific task and returns a value to the calling procedure. In C,
every subprogram is a function and has a return value. PL/SQL supports
both functions and procedures (discussed in Chapter 4).
This chapter will discuss the uses and creation of functions using
PL/SQL. Several detailed examples are provided, and you are guided
through the process of creating a new function, from design to
testing. By the end of this chapter, you will be prepared to design,
create, and test your own functions.
Advantages of Functions
The ability to store functions within the Oracle database allows
you to reach great heights in modularity, maintainability, and
performance improvement. Using functions to accomplish specific tasks
improves the reliability of other modules and reduces development
time.
Embedded SQL
PL/SQL functions can execute any DML statement that can be executed
in SQL*Plus, as shown in Listing 5.1.
Listing 5.1 A PL/SQL function that utilizes a DML statement.
FUNCTION Grant_Vacation (nAddDays IN number,
nFacultyMember IN number)
RETURN number
IS
BEGIN
SELECT base_vacation
INTO nVacationDays
FROM FACULTY
WHERE faculty_id = nFacultyMember;
RETURN (nVacationDays + nAddDays);
END;
Maintainability
Because functions are ideally small blocks of code, they are easier
to code, test, and correct. Assuming that the parameters and return
value of a function aren’t changed, modifications to the code inside
the function will not affect modules that call the function. Consider
the example in Listing 5.2.
Listing 5.2 A procedure calling the Raise_Salary()
function.
PROCEDURE Annual_Review (nEmployee IN number)
IS
nReviewScore number;
BEGIN
SELECT review_score
INTO nReviewScore
FROM PERFORMANCE_REVIEWS
WHERE employee_num = nEmployee;
IF (nReviewScore > 90) THEN
SELECT base_salary
INTO nEmployeeSalary
FROM EMPLOYEES
WHERE employee_num = nEmployee;
UPDATE EMPLOYEES
SET base_salary = Raise_Salary
(nBaseSalary => nEmployeeSalary,
nRaisePercent => 1);
END IF;
END;
Altering the internal logic of the Raise_Salary() function
will not affect the internal logic of the Annual_Review()
procedure. If a parameter is altered, added to, or removed from the
function, or the datatype of the function’s return value is altered,
the procedure must also be changed to accommodate the changes to the
function.
Modularity
Creating functions to perform specific tasks is an inherently
modular approach. Consider again the Raise_Salary() function.
If more than one procedure needs to be able to give an employee a
raise, each of these procedures can call the function.
Performance Improvement
A stored PL/SQL object helps improve performance because the object
has already been compiled into p-code (the machine executable
version of the code) when the function is called. The database does
not have to compile the object again before executing it.
If the stored objects contain DML statements, another level of
potential performance improvement is gained. This is because the DML
statements are more likely to be cached in the SGA when the function
is executed, eliminating Oracle’s need to re-parse the DML statement
if a match is found.
Reliability
Testing a function like Raise_Salary() is accomplished
easily with a testing script, like the one shown in Listing 5.3.
Listing 5.3 A testing script for the Raise_Salary()
function.
DECLARE
nResult number;
BEGIN
DBMS_Output.Put_Line ('Calling Raise_Salary for 3.5 % ' ||
'of $20,000. The result should be ' ||
'$20,700.');
nResult := Raise_Salary (nBaseSalary => 20000,
nRaisePercent => 3.5);
DBMS_Output.Put_Line (to_char (nResult));
IF (nResult <> 20700) THEN
DBMS_Output.Put_Line ('ERROR: Function returned wrong value!');
END IF;
END;
Once the test has been successfully run on the function, any
procedure or function can call the Raise_Salary() function with
the knowledge that the function has already been tested.
This is an excerpt from the book "High Performance Oracle
Database Automation" by Jonathan Ingram and Donald K.
Burleson, Series Editor. |
