|Previous||Table of Contents||Next|
A functions 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;
In addition to calls that are made from the systems front end, functions can be called in three ways:
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.
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.
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;
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 isnt eligible for a bonus. If the response is FALSE, the THEN clause will not be executed.
|Previous||Table of Contents||Next|