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



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
   SELECT base_vacation
   INTO   nVacationDays
   WHERE  faculty_id = nFacultyMember;
   RETURN (nVacationDays + nAddDays);


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)
  nReviewScore   number;
  SELECT review_score
  INTO   nReviewScore
  WHERE  employee_num = nEmployee;
  IF (nReviewScore > 90) THEN
     SELECT base_salary
     INTO   nEmployeeSalary
     WHERE  employee_num = nEmployee;
     SET    base_salary = Raise_Salary
               (nBaseSalary   => nEmployeeSalary,
                nRaisePercent => 1);

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.


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.


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.

  nResult  number;
  DBMS_Output.Put_Line ('Calling Raise_Salary for 3.5 % ' ||
                        'of $20,000. The result should be ' ||
  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!');

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