In this fifth part of a six part series, we look at how to create and use a stored function in Oracle PL/SQL. This article is excerpted from chapter 36 of the book Beginning PHP and Oracle: From Novice to Professional, written by W. Jason Gilmore and Bob Bryla (Apress; ISBN: 1590597702).
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.