Oracle PL/SQL Stored Functions

Creating and Using a Stored Function

As we mentioned earlier in this chapter, a stored function is similar to a stored procedure with one key difference: a stored function returns a single value. This makes a stored function available in your SQL SELECT statements, unlike stored procedures that you must call within an anonymous PL/SQL block or another stored procedure.

--------------------------------------------------------------------------------

Note  Although you can specify OUT parameters in a stored function, this is generally considered a bad programming practice, and they are not allowed within SELECT statements. If you truly need multiple values returned from a subprogram, use a stored procedure.

--------------------------------------------------------------------------------

In the example in Listing 36-1, you create a new stored function to format the employee data from the EMPLOYEES table (or any other source containing the same datatypes) to be more readable for Web applications or other reporting purposes.

Listing 36-1. Stored Function to Format Employee Data

CREATE OR REPLACE FUNCTION
  format_emp (deptnum IN NUMBER, empname IN VARCHAR2, title IN VARCHAR2)
RETURN VARCHAR2
IS
  concat_rslt  VARCHAR2(100);
BEGIN
  concat_rslt :=
    'Department: ' || to_char(deptnum) ||
    '   Employee: ' || initcap(empname) ||
    '   Title: ' || initcap(title);
  RETURN (concat_rslt);
END;

To test this out using a SELECT statement, use an example similar to the following:

select
   format_emp(183, 'CHRYSANTHEMUM', 'WIKIPEDIA MAINT') "Employee Info"
from DUAL;

The output looks like that shown in Figure 36-2 when you run it using the SQL Commands interface. We show you how to use this function within a PHP application in the section “Integrating Subprograms into PHP Applications.”

Figure 36-2.  Running a SELECT statement containing a user-defined function.

Please check back for the conclusion to this series.