 |
|
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. |
