Call (800) 766-1884 for Oracle support & training
Free Oracle Tips


Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance
 

Free Oracle Tips


 

HTML Text

BC Oracle tuning

Oracle training

Oracle support

Remote Oracle

 

 

   
 

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.

  
 

Oracle performance tuning software 
 
 
 
 

Oracle performance tuning book

 

 
 
 
Oracle performance Tuning 10g reference poster
 
 
 
Oracle training in Linux commands
 
Oracle training Excel
 
Oracle training & performance tuning books
 

 

   

Copyright © 1996 -  2014 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation. 

Hit Counter