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