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)
‘Department: ‘ || to_char(deptnum) ||
‘ Employee: ‘ || initcap(empname) ||
‘ Title: ‘ || initcap(title);
To test this out using a SELECT statement, use an example similar to the following:
format_emp(183, ‘CHRYSANTHEMUM’, ‘WIKIPEDIA MAINT’) "Employee Info"
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.