|Previous||Table of Contents||Next|
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.
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.
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;
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 arent 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 functions return value is altered, the procedure must also be changed to accommodate the changes to the function.
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.
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 Oracles need to re-parse the DML statement if a match is found.
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.
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 isnt 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;
|Previous||Table of Contents||Next|