User's Manual

Beta Draft Executing Stored Procedures and Functions 6-1

Executing Stored Procedures and Functions

This chapter shows you how to run stored procedures and functions using PHP and

Oracle Express. It has the following topics:

Q Using PL/SQL to Capture Business Logic

Q Using PL/SQL Ref Cursors to Return Result Sets

The Anyco application is extended with a PL/SQL function to calculate remuneration

for each employee, and further extended with a PL/SQL procedure to return a ref

cursor of employee records.

Using PL/SQL to Capture Business Logic

Oracle PL/SQL procedures and functions allow business logic to be stored in the

database for any client program to use. They also reduce the amount of data that needs

to be transferred between the database and PHP.

To display the total remuneration of each employee, perform the following steps to

create a PL/SQL function stored in the database.

1. In a browser, enter the URL for your Oracle Database Express Edition HTMLDB

page:

http://localhost:8080/htmldb

2. At the login screen, in the Username field enter system, and in Password field

enter manager (or the password you entered at the prompt during configuration

of the Oracle Database Express Edition). Click Login.

3. In the Home page, click the arrow on the SQL icon, move the mouse over SQL

Commands and click Enter Command: