Home arrow Oracle arrow Oracle PL/SQL Stored Functions

Oracle PL/SQL Stored Functions

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

By: Apress Publishing
Rating: starstarstarstarstar / 2
April 07, 2011

print this article



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

  format_emp (deptnum IN NUMBER, empname IN VARCHAR2, title IN VARCHAR2)
  concat_rslt  VARCHAR2(100);
  concat_rslt :=
    'Department: ' || to_char(deptnum) ||
    '   Employee: ' || initcap(empname) ||
    '   Title: ' || initcap(title);
  RETURN (concat_rslt);

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

   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.  

>>> More Oracle Articles          >>> More By Apress Publishing

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort


- Oracle Java Security Woes Continue
- Oracle's New IaaS Cloud Option: There's a Ca...
- Oracle Acquires Eloqua to Boost Cloud Presen...
- Choosing Innovation: Oracle Survey Insights
- Oracle Fixes Privilege Escalation Bug
- Oracle`s Communications Service Availability...
- Oracle Releases Exalytics, Taleo Plans
- Oracle Releases Communications Network Integ...
- Oracle Releases Communications Data Model 11...
- Oracle Releases PeopleSoft PeopleTools 8.52
- Oracle Integrates Cloudera Apache Distro, My...
- Oracle Releases MySQL 5.5.18
- Oracle Announces NoSQL Database Availability
- Sorting Database Columns With the SELECT Sta...
- Retrieving Table Data with the LIKE Operator

Developer Shed Affiliates


Dev Shed Tutorial Topics: