 |
|
PL/SQL Return Values
Oracle Tips by
Burleson
|
By definition, a PL/SQL function must return a value to any block
of code that calls the procedure. If the function doesn’t return a
value, an exception will be raised. Functions return a value through
the use of the RETURN statement, as shown in Listing 5.7.
Listing 5.7 Use of the RETURN statement in a
function.
FUNCTION Raise_Salary (nBaseSalary IN number,
nRaisePercent IN number)
RETURN number;
IS
BEGIN
RETURN (nBaseSalary * nRaisePercent);
END;
Datatypes
A function’s return value may be of any datatype; however,
user-defined datatypes must exist in a referenced package
specification or globally (if the function is created within another
code module). Consider the function in Listing 5.8, which returns a
PL/SQL table.
Listing 5.8 A return value of a user-defined datatype.
FUNCTION Parse_String (vStringToParse IN varchar2)
RETURN Global_Types.VARCHAR2_TABTYPE
IS
iStringPos integer;
biIndex binary_integer := 0;
vString varchar2 (2000);
Return_tab VARCHAR2_TABTYPE;
BEGIN
vString := vStringToParse;
LOOP
--
-- Get the position of the next delimiter.
--
iStringPos := instr (vString, '^');
--
-- If there are no more elements in the string, return
-- the table.
--
IF (iStringPos = 0) THEN
RETURN Return_tab;
END IF;
Return_tab (biIndex) := substr (vString, 1, (iStringPos - 1));
biIndex := biIndex + 1;
--
-- Chop off the first portion of the string.
--
vString := substr (vString, (iStringPos + 1));
END LOOP;
EXCEPTION
WHEN VALUE_ERROR THEN
Log_System_Error (vErrorLocation => 'Parse_String',
vErrorText => SQLERRM);
END;
In this example, the VARCHAR2_TABTYPE is a type declaration
in the package spec for the package Global_Types. Packages will
be discussed in Chapter 6.
Using %TYPE References
Functions can have parameters defined as %TYPE references to
columns and can also have the datatype of their return value defined
as a %TYPE reference, as shown in Listing 5.9.
Listing 5.9 Using %TYPE definitions for parameters
and return values.
FUNCTION Raise_Salary (nBaseSalary IN EMPLOYEES.base_salary%TYPE,
nRaisePercent IN number)
RETURN EMPLOYEES.base_salary%TYPE;
IS
BEGIN
RETURN (nBaseSalary * nRaisePercent);
END;
Using %ROWTYPE References
Functions can also have parameters and return values that are
defined using %ROWTYPE, as shown in Listing 5.10.
Listing 5.10 Using %ROWTYPE definition of parameters
and return values.
FUNCTION Raise_Salary (Employee_rec IN EMPLOYEES%ROWTYPE,
nRaisePercent IN number)
RETURN number;
IS
BEGIN
RETURN (Employee_rec.base_salary * nRaisePercent);
END;
This is an excerpt from the book "High Performance Oracle
Database Automation" by Jonathan Ingram and Donald K.
Burleson, Series Editor. |
