148-154

Table of Contents

Chapter 5
Functions

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 ,000. The result should be '                          ',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 .

Creating And Dropping Functions

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;