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

 

 

   
 

Calling Functions in PL/SQL
Oracle Tips by Burleson
 

In addition to calls that are made from the system’s front end, functions can be called in three ways:

  • DML statements
  • Anonymous PL/SQL blocks
  • Stored PL/SQL objects

Each method of calling a function is quite similar despite the varied origins of the calls.

DML Statements

Stored functions can be executed as part of a DML statement. An example of calling a function this way is shown in Listing 5.11.

Listing 5.11 Calling a function within a DML statement.

UPDATE FACULTY
SET    base_salary = Raise_Salary (nRaisePercent => 3.5,
                                   nBaseSalary   => base_salary)
WHERE  faculty_id  = 6572;

In this example, the value of the base_salary column is passed to the Raise_Salary() function. The value returned from the function is stored in the base_salary column. The function Raise_Salary() might look like the function shown in Listing 5.12.

Listing 5.12 The Raise_Salary() function called in Listing 5.11.

FUNCTION Raise_Salary (nBaseSalary   IN     number,
                       nRaisePercent IN     number)
 
IS
 
BEGIN
  RETURN (nBaseSalary * nRaisePercent);
END;

When packaged functions are used in this way, a purity level for the function must be defined within the package spec. Purity levels for functions are discussed in Chapter 6.

Anonymous PL/SQL Blocks

Functions can be called from any PL/SQL block, including an anonymous block created during an SQL*Plus session or as part of a script. Consider the PL/SQL block in Listing 5.13, which is part of a unit test for the Raise_Salary() function.

Listing 5.13 An anonymous PL/SQL block that calls a 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;

Stored PL/SQL Objects

Functions can be called from other stored functions and procedures, as shown in Listing 5.14.

Listing 5.14 A stored function calling another stored function.

FUNCTION Calculate_Bonus (nEmployee IN     number)
 
   RETURN number;
 
IS
 
   nSalary   EMPLOYEES.base_salary%TYPE;
 
BEGIN
    IF Check_Bonus_Eligibility (nEmployee => nEmployee) THEN
       SELECT base_salary
       INTO   nSalary
       FROM   EMPLOYEES
       WHERE  employee_num = nEmployee;
       RETURN (nSalary * 0.01);
    END IF;
END;

In this example, the function Check_Bonus_Eligibility() is a boolean function, returning TRUE if the employee is eligible for a bonus and FALSE if the employee isn’t eligible for a bonus. If the response is FALSE, the THEN clause will not be executed.

            
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