160-165

Previous Table of Contents Next


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; 

Calling Functions

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 ,000. The result should be '                          ',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.


Previous Table of Contents Next


High Performance Oracle Database Automation
High Performance Oracle Database Automation: Creating Oracle Applications with SQL and PL/SQL
ISBN: 1576101525
EAN: 2147483647
Year: 2005
Pages: 92

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net