Calling Functions in
Oracle Tips by
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.
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.
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
Listing 5.12 The Raise_Salary() function called in
FUNCTION Raise_Salary (nBaseSalary IN number,
nRaisePercent IN number)
RETURN (nBaseSalary * nRaisePercent);
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
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!');
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 Calculate_Bonus (nEmployee IN number)
IF Check_Bonus_Eligibility (nEmployee => nEmployee) THEN
WHERE employee_num = nEmployee;
RETURN (nSalary * 0.01);
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.