Beginning PHP and Oracle From Novice to Professional by W. Jason Gilmore and Bob Bryla Chapter 36

Throughout this book you’ve seen quite a few examples where the Oracle queries are embedded directly into the PHP script. Indeed, for smaller applications this is fine. However, as application
complexity and size increase, continuing this practice could be the source of some grief.
One of the most commonplace solutions to these challenges comes in the form of an Oracle database feature known as a PL/SQL subprogram. PL/SQL subprograms are also called PL/SQL procedures or stored routines; these terms can be used interchangably. A PL/SQL subprogram is a set of PL/SQL and SQL statements stored in the database server and executed by calling an assigned name within a query, much like a function encapsulates a set of commands that is executed when the function name is invoked. The PL/SQL subprogram can then be maintained from the secure confines of the data- base server, without ever having to touch the application code. In addition, separating the PL/SQL code from the PHP code makes both sets of code much easier to read and maintain.

Should You Use PL/SQL Subprograms?

What if you have to deploy two similar applications—one desktop-based and the other Web-based— that use Oracle Database XE and perform many of the same tasks? On the occasion a query changes, you’d need to make modifications wherever that query appears, not in one application but in two. Another challenge that arises when working with complex applications, particularly in a team envi- ronment, involves affording each member the opportunity to contribute his or her expertise without necessarily stepping on the toes of others. Typically, the individual responsible for database devel- opment and maintenance (known as the database architect) is particularly knowledgeable in writing efficient and secure queries. But how can the database architect write and maintain these queries without interfering with the application developer if the queries are embedded in the code? Further- more, how can the database architect be confident that the developer isn’t “improving” upon the queries, potentially opening up the application to penetration through a SQL injection attack (which involves modifying the data sent to the database in an effort to run malicious SQL code)? You can use a PL/SQL subprogram.

■Note PL/SQL stands for Procedural Language/Structured Query Language and is syntactically similar to the Ada
programming language. PL/SQL is Oracle’s proprietary server-based procedural extension to SQL. However, most other database vendors support similar functionality.

PL/SQL subprograms are categorized into three types: procedures, functions, and anonymous PL/SQL blocks. Anonymous PL/SQL blocks are syntactically identical to PL/SQL procedures and functions except that they don’t have a name or any parameters, are not directly stored in an Oracle

633

database, and are typically run as ad hoc blocks of PL/SQL code. You often see anonymous PL/SQL blocks within procedures or functions in addition to their use on an ad hoc basis. We detail these variations on PL/SQL subprograms and where to use them throughout this chapter.
Rather than blindly jumping onto the PL/SQL bandwagon, it’s worth taking a moment to consider the advantages and disadvantages of using PL/SQL subprograms, particularly because their utility is an often debated topic in the database community. The following sections summarize the pros and cons of incorporating PL/SQL into your PHP development strategy.

Subprogram Advantages

Subprograms have a number of advantages, the most prominent of which are highlighted here:

• Consistency: When multiple applications written in different languages are performing the same database tasks, consolidating these like functions within subprograms decreases other- wise redundant development processes.
• Performance: A competent database administrator often is the most knowledgeable member of the team regarding how to write optimized queries. Therefore, it may make sense to leave the creation of particularly complex database-related operations to this individual by main- taining them as subprograms.
• Security: When working in particularly sensitive environments such as finance, health care, and defense, it’s sometimes mandated that access to data is severely restricted. Using subpro- grams is a great way to ensure that developers have access only to the information necessary to carry out their tasks.
• Architecture: Although it’s out of the scope of this book to discuss the advantages of multitier architectures, using subprograms in conjunction with a data layer can further facilitate manageability of large applications. Search the Web for n-tier architecture for more informa- tion about this topic.

Subprogram Disadvantages

Although the preceding advantages may have you convinced that subprograms are the way to go, take a moment to ponder the following drawbacks:

• Performance: Many would argue that the sole purpose of a database is to store data and maintain data relationships, not to execute code that could otherwise be executed by the application. In addition to detracting from what many consider the database’s sole role, executing such logic within the database will consume additional processor and memory resources.
• Maintainability: Although you can use GUI-based utilities such as SQL Developer (see Chapter 29) to manage subprograms, coding and debugging them is considerably more difficult than writing PHP-based functions using a capable IDE.
• Portability: Because subprograms often use database-specific syntax (e.g., PL/SQL code is not easily ported to DB2 or SQL Server), portability issues will surely arise should you need to use the application in conjunction with another database product.

Even after reviewing the advantages and disadvantages, you may still be wondering whether subprograms are for you. Perhaps the best advice is to read on and experiment with the numerous examples provided throughout this chapter and see where you can leverage PL/SQL in your applications.

How Oracle Implements Subprograms

Although the term stored procedures is commonly bandied about, Oracle actually implements three procedural variants, which are collectively referred to as subprograms:

• Stored procedures: Stored procedures support execution of SQL statements such as SELECT, INSERT, UPDATE, and DELETE. They also can set parameters that can be referenced later from outside of the procedure.
• Stored functions: Stored functions support execution only of the SELECT statement, accept only input parameters, and must return one and only one value. Furthermore, you can invoke a stored function directly into a SQL command just like you might do with standard Oracle functions such as COUNT() and TO_DATE().
• Anonymous blocks: Anonymous blocks are much like stored procedures and functions except that they cannot be stored in the database and referenced directly because they are, as the name implies, anonymous. They do not have a name or parameters; you either run them in the SQL Commands or SQL Developer GUI application, or you can embed them within a stored procedure or function to isolate functionality.

Generally speaking, you use subprograms when you need to work with data found in the data- base, perhaps to retrieve rows or insert, update, and delete values; whereas you use stored functions to manipulate that data or perform special calculations. In fact, the syntax presented throughout this chapter is practically identical for both variations, except that the term procedure is swapped out for function. For example, the command DROP PROCEDURE procedure_name is used to delete an existing stored procedure, while DROP FUNCTION function_name is used to delete an existing stored function.

Creating a Stored Procedure

The following abbreviated syntax is available for creating a stored procedure; see the Oracle Data- base XE documentation for a complete definition:

CREATE [OR REPLACE] PROCEDURE procedure_name ([parameter[, ...]]) [characteristics, ...] [IS | AS] plsql_subprogram_body
The following is used to create a stored function:

CREATE [OR REPLACE] FUNCTION function_name ([parameter[, ...]]) RETURNS type
[characteristics, ...] [IS | AS] plsql_subprogram_body

Finally, you create and use anonymous PL/SQL blocks as follows:

DECLARE
declarations;
BEGIN statement1; statement2;
...

END;

The DECLARE section is optional regardless of whether you are writing a procedure, a function, or

an anonymous block. As you can infer from the syntax, you cannot pass variables, return variables, or reference the block from any other procedure or function; you can, however, save the block in a text file and retrieve it from the SQL Commands interface or embed the block within another stored function or procedure.

In this example, you use the SQL Commands interface to calculate an employee’s salary after two consecutive 10 percent raises. Figure 36-1 shows the anonymous block itself and the results after you click the Run button.

Figure 36-1.Running an anonymous PL/SQL block in SQL Commands

Although you could obtain the results in Figure 36-1 by using one or more SQL statements, the advantages of using PL/SQL are evident. The list of steps you use to obtain your results is easy to understand, and the output from the block would be difficult to obtain using just SQL commands. Note the embedded procedure call to DBMS_OUTPUT.PUT_LINE. This predefined stored procedure is included with your installation of Oracle Database XE that produces text output from your proce- dures. We show you more examples of calling procedures from within a procedure later in this chapter in the section “Creating and Using a Stored Function.”
The other advantage of using an anonymous block is clear only if you look at the output line Statement Processed. When you click the Run button, the entire block is sent to Oracle for processing as a unit; you see the results after Oracle executes the block. This minimizes the network traffic to and from the Oracle server in contrast to sending SQL commands one at a time.
For our second introductory example, let’s create a simple stored procedure that returns the static string Hello, World:

create or replace procedure say_hello as begin
dbms_output.put_line('Hello, World');
end;

You don’t need to pass any parameters; the procedure already has the text to print. Note the OR REPLACE clause; if the procedure already exists, it will be replaced. If you do not specify OR REPLACE and the procedure already exists, you will get an error message and the procedure is not replaced.
Now execute the procedure using the following command:

begin say_hello();
end;
Note that from the SQL Commands interface, you must use an anonymous block to call a stored procedure. Executing this procedure within the anonymous block returns the following output:

Hello, World

Statement processed.

0.00 seconds

In contrast to the previous example, once you create the procedure, you can call it repeatedly from different sessions without sending the procedure definition each time.

Parameters
Stored procedures can both accept input parameters and return parameters back to the caller. However, for each parameter, you need to declare the name and the datatype and whether it will be used to pass information into the procedure, pass information back out of the procedure, or perform both duties.

■Note Although stored functions can accept both input and output parameters in the parameter list, they only
support input parameters and must return one and only one value if referenced from a SELECT statement. There- fore, when declaring input parameters for stored functions, be sure to include just the name and type if you are only going to reference the stored functions from SELECT statements. Oracle best practices discourages the use of function parameters returning values to the calling program; if you must return more than one value from a subprogram, a stored procedure is more suitable.

Perhaps not surprisingly, the datatypes supported as parameters or return values for stored procedures correspond to those supported by Oracle, plus a few specific to PL/SQL. Therefore, you’re free to declare a parameter to be of any datatype you might use when creating a table.
To declare a parameter’s purpose, use one of the following three keywords:

• IN: These parameters are intended solely to pass information into a procedure. You cannot modify these values within the procedure.
• OUT: These parameters are intended solely to pass information back out of a procedure. You cannot pass a constant for a parameter defined as OUT.
• IN OUT: These parameters can pass information into a procedure, have its value changed, and then be referenced again from outside of the procedure.

Consider the following example to demonstrate the use of IN and OUT. First, create a stored proce- dure called RAISE_SALARY that accepts an employee ID and a salary increase amount and returns the employee name to confirm the salary increase:

create PROCEDURE raise_salary
(emp_id IN NUMBER, amount IN NUMBER, emp_name OUT VARCHAR2) AS BEGIN
UPDATE employees SET salary = salary + amount WHERE employee_id = emp_id;
SELECT last_name INTO emp_name FROM employees WHERE employee_id = emp_id; END raise_salary;
Next, use an anonymous PL/SQL block to increase the salary of employee number 105 by $200 per month:
DECLARE
emp_num NUMBER(6) := 105; sal_inc NUMBER(6) := 200; emp_last VARCHAR2(25);
BEGIN
raise_salary(emp_num, sal_inc, emp_last); DBMS_OUTPUT.PUT_LINE('Salary has been updated for: ' || emp_last);
END;

The results are as follows:

Salary has been updated for: Austin

Statement processed.

Declaring and Setting Variables

Local variables are often required to serve as temporary placeholders when carrying out tasks within a subprogram. This section shows you how to both declare variables and assign values to variables.

Declaring Variables

Unlike PHP, you must declare local variables within a subprogram before using them, specifying their type by using one of Oracle’s supported datatypes. Variable declaration is achieved with the DECLARE section of the PL/SQL subprogram or anonymous block, and its syntax looks like this:

DECLARE variable_name1 type [:= value];
variable_name2 type [:= value];
. . .

Here is a declaration section for a procedure that initializes some values for the area of a circle:

DECLARE
pi REAL := 3.141592654;
radius REAL := 2.5;
area REAL := pi * radius**2; BEGIN
. . .

There are a few things to note about this example. Variable declarations can refer to other variables already defined. In the previous example, the variable area is initialized to the area of a circle with a radius of 2.5. Note also the datatype REAL; it is one of PL/SQL’s internal datatypes not available for Oracle table columns but is provided as a floating-point datatype within PL/SQL to improve the performance of PL/SQL subprograms that require many high-precision floating point calculations.
Also note that by default any declared variable can be changed within the procedure. If you don’t want the application to change the value of pi, you can add the CONSTANT keyword as follows:

pi CONSTANT REAL := 3.141592654;

Setting Variables

You use the := operator to set the value of a declared subprogram variable. Its syntax looks like this:

variable_name := value;

Here are a couple of examples of assigning values in the body of the subprogram:

BEGIN
radius := 7.7;
area := pi * radius**2;
dbms_output.put_line
('Area of circle with radius: ' || radius || ' is: ' || area);

It’s also possible to set variables from table columns using a SELECT INTO statement. The syntax is identical to a SELECT statement you might run in SQL Commands or SQL*Plus but with the addition of the INTO variable_name clause to specify which PL/SQL variable will contain the table column’s value. We use this construct to retrieve the employee’s last name in the raise_salary procedure created earlier in the chapter:

SELECT last_name INTO emp_name FROM employees WHERE employee_id = emp_id;

PL/SQL Constructs

Single-statement subprograms are quite useful, but the real power lies in a subprogram’s ability to encapsulate and execute several statements, including conditional logic and iteration. In the following sections, we touch on the most important constructs.

Conditionals

Basing task execution on run-time information (e.g., from user input) is key for wielding tight control over the results of the task execution. Subprogram syntax offers two well-known constructs for performing conditional evaluation: the IF-THEN-[ELSIF][-ELSE]-END IF statement and the CASE statement. Both are introduced in this section.

IF-THEN-[ELSIF][-ELSE]-END IF

The IF-THEN-[ELSIF][-ELSE]-END IF statement is one of the most common means for evaluating conditional statements. In fact, even if you’re a novice programmer, you’ve likely already used it on numerous occasions. Therefore, this introduction should be quite familiar. The prototype looks like this:
IF condition THEN statement_list
[ELSIF condition THEN statement_list] . . . [ELSE statement_list]
END IF

■Caution The keyword for specifying alternate condition testing in an IF . . . END IF statement is ELSIF.
In many other programming languages it might be ELSE IF or ELSEIF, but in PL/SQL it’s one word: ELSIF.

For example, let’s say you want to adjust employee’s bonuses in proportion to their sales. Your conditional logic would look somewhat like the following:

IF sales > 50000 THEN
bonus := 1500;
ELSIF sales > 35000 THEN
bonus := 500; ELSE
bonus := 100;
END IF;
UPDATE employees SET salary = salary + bonus WHERE employee_id = emp_id;

For employees who are not in the sales department (sales = 0) or whose sales are $35,000 or less, the conditional logic assigns a bonus of $100.

CASE

The CASE statement is useful when you need to compare a value against an array of possibilities. While doing so is certainly possible using an IF statement, the code readability improves consider- ably by using the CASE statement. The CASE statement has two different forms, CASE-WHEN and the searched CASE statement. The CASE-WHEN statement identifies the variable to be compared in the first line of the CASE statement and performs the comparisons to the variable in subsequent lines. Here is the CASE-WHEN syntax:

CASE expression
WHEN expression THEN statement_list
[WHEN expression THEN statement_list] . . . [ELSE statement_list]
END CASE;

The ELSE condition executes if none of the other WHEN conditions evaluate to TRUE. Consider the following example, which sets a variable containing the appropriate sales tax rate by comparing a customer’s state to a list of values:

CASE state
WHEN 'AL' THEN tax_rate := .04; WHEN 'AK' THEN tax_rate := .00;
...
WHEN 'WY' THEN tax_rate := .06; END CASE;
Alternatively, the searched CASE statement gives you a bit more flexibility (at the expense of more typing). Here is the searched CASE syntax:

CASE
WHEN condition THEN statement_list
[WHEN condition THEN statement_list] . . . [ELSE statement_list]
END CASE;

Consider the following revised example, which sets a variable containing the appropriate sales tax rate by comparing a customer’s state to a list of values:

CASE
WHEN state='AL' THEN tax_rate := .04; WHEN state='AK' THEN tax_rate := .00;
...
WHEN state='WY' THEN tax_rate := .06; END CASE;
The form of the CASE statement is sometimes driven by your programming style. However, when you have complex conditions that cannot be represented in the CASE-WHEN syntax, you have no choice but to use the searched CASE. In either case (no pun intended), the readability of your code is dramatically improved in contrast to representing the same logic using IF-THEN-[ELSIF][-ELSE]-END IF.

Iteration

Some tasks, such as inserting a number of new rows into a table, require the ability to repeatedly execute over a set of statements. This section introduces the various methods available for iterating and exiting loops.

LOOP

The most basic way to loop through a series of statements is with the LOOP-END LOOP construct using this syntax:

LOOP statement1; statement2;
. . .
END LOOP;

The first question that may come to mind is, how useful is this plain LOOP construct if you can’t exit the loop? The DBA will not be too happy if your subprogram runs indefinitely. The EXIT constructs will address this issue in the next section.

EXIT and EXIT-WHEN

The EXIT statement forces a loop to complete unconditionally. As you might expect, you execute the EXIT statement based on a condition in an IF statement. Consider the following example where you display the square roots of the numbers one through ten:

DECLARE
countr NUMBER := 1; BEGIN
LOOP
dbms_output.put_line('Square root of ' || countr || ' is ' || SQRT(countr));
countr := countr + 1; IF countr > 10 THEN
EXIT; END IF;
END LOOP;
dbms_output.put_line('End of Calculations.'); END;

The counter is initialized in the DECLARE section; within the loop, the counter is incremented. Once the counter reaches the threshold value in the IF statement, the loop terminates and continues execution after the END LOOP statement. The output looks like this:

Square root of 1 is 1
Square root of 2 is 1.41421356237309504880168872420969807857
Square root of 3 is 1.73205080756887729352744634150587236694
Square root of 4 is 2
Square root of 5 is 2.23606797749978969640917366873127623544
Square root of 6 is 2.44948974278317809819728407470589139197
Square root of 7 is 2.64575131106459059050161575363926042571
Square root of 8 is 2.82842712474619009760337744841939615714
Square root of 9 is 3
Square root of 10 is 3.16227766016837933199889354443271853372
End of Calculations.

Statement processed.

You can alternatively use the EXIT-WHEN construct to improve the readability of your code if you only use the IF statement to check for a termination condition. You can rewrite the previous code example as follows:

DECLARE
countr NUMBER := 1; BEGIN
LOOP
dbms_output.put_line('Square root of ' || countr || ' is ' || SQRT(countr));
countr := countr + 1; EXIT WHEN countr > 10;
END LOOP;
dbms_output.put_line('End of Calculations.'); END;

WHILE-LOOP

As yet another alternative to EXIT, you can place your loop termination condition at the beginning of the loop using this syntax:

WHILE condition LOOP statement1; statement2;
. . .
END LOOP;

While this syntax may be more readable, it also has one major distinction compared to the previously discussed loop constructs: if the condition in the WHILE clause is not true the first time through the loop, the statements within the loop are not executed at all. In contrast, all previous versions of the LOOP construct execute the code within the loop at least once. Here is the previous example rewritten to use WHILE:

DECLARE
countr NUMBER := 1; BEGIN
WHILE countr < 11 LOOP
dbms_output.put_line('Square root of ' || countr || ' is ' || SQRT(countr));

countr := countr + 1; END LOOP;
dbms_output.put_line('End of Calculations.'); END;

FOR-LOOP

If your application needs to iterate over a range of integers, you can use the FOR-LOOP construct and simplify your code even more. Here is the syntax:

FOR variable IN startvalue..endvalue LOOP
statement1;
statement2;
. . . END LOOP;
Within the loop, the variable variable starts with a value of startvalue and terminates the loop when the value of variable exceeds endvalue. Rewriting our well-worn example from earlier in the chapter (and while we’re at it, dropping the unnecessary variable declaration) looks like this:

BEGIN
FOR i IN 1..10 LOOP
dbms_output.put_line('Square root of ' || i || ' is ' || SQRT(i)); END LOOP;
dbms_output.put_line('End of Calculations.'); END;

Note that you do not need to include the loop variable in the declaration section. You can, however, explicitly declare your loop variables depending on your programming standards.
In our final loop example, you want to iterate your loop in reverse order and produce the square roots starting with ten and ending at one. As you might expect, all you need to add is the REVERSE keyword to your LOOP clause as follows:

BEGIN
FOR i IN REVERSE 1..10 LOOP
dbms_output.put_line('Square root of ' || i || ' is ' || SQRT(i)); END LOOP;
dbms_output.put_line('End of Calculations.');
END;

This produces the following output, as expected:

Square root of 10 is 3.16227766016837933199889354443271853372
Square root of 9 is 3
Square root of 8 is 2.82842712474619009760337744841939615714
Square root of 7 is 2.64575131106459059050161575363926042571
Square root of 6 is 2.44948974278317809819728407470589139197
Square root of 5 is 2.23606797749978969640917366873127623544
Square root of 4 is 2
Square root of 3 is 1.73205080756887729352744634150587236694
Square root of 2 is 1.41421356237309504880168872420969807857
Square root of 1 is 1
End of Calculations.

Statement processed.

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 program-
ming 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 inter- face. 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

Modifying, Replacing, or Deleting Subprograms Unless you are using a more advanced GUI or IDE (integrated development environment), you only have one option to update or replace a stored function or procedure: you redefine the function or procedure by including the OR REPLACE clause, as you saw in Listing 36-1. If the stored function or

procedure does not already exist, it is created; if it exists, it is replaced. This prevents error messages when you don’t care if the subprogram already exists.
To delete a subprogram, execute the DROP statement. Its syntax is as follows:

DROP (PROCEDURE | FUNCTION) proc_name;

For example, to drop the y2k_update stored procedure, execute the following command:

DROP PROCEDURE y2k_update;

Integrating Subprograms into PHP Applications Thus far, all the examples have been demonstrated by way of the Oracle Database XE SQL Commands or SQL Developer client. While this is certainly an efficient means for testing examples, the utility of subprograms is drastically increased by the ability to incorporate them into your application. This
section demonstrates just how easy it is to integrate subprograms into your PHP-driven Web application.
In the first example, you use the function created in Listing 36-1 to format a Web report. See
Listing 36-2 for the PHP application that references the FORMAT_EMP function.

Listing 36-2. Stored Function to Format Employee Data (use_stored_func.php)

<?php
// Connect to Oracle Database XE
$c = oci_connect('hr', 'hr', '//localhost/xe');

// Create and execute the query
$result = oci_parse($c,
'select employee_id "Employee Number", ' .
'format_emp(department_id, last_name, job_id) ' .
'"Employee Info"' .
' from employees where rownum < 11');
oci_execute($result);

// Format the table
echo "<table border='1'>";
echo "<tr>";

// Output the column headers
for ($i = 1; $i <= oci_num_fields($result); $i++) {
echo "<th>".oci_field_name($result, $i)."</th>";
}

echo "</tr>";

// output the results
while ($employee = oci_fetch_row($result)) {
$emp_id = $employee[0];
$emp_info = $employee[1];
echo "<tr>";
echo "<td>$emp_id</td><td>$emp_info</td>";
echo "</tr>";
}

echo "</table>";
oci_close($c);
?>

You can see the results for the first ten rows of the EMPLOYEES table in Figure 36-3.

Figure 36-3. Results from a PHP script using an embedded user-defined function

Invoking a stored procedure in PHP is almost as easy. The key difference is that since you are returning results from a procedure within the PHP script, you must bind the IN and OUT variables in the stored procedure to PHP variables. In this example, you first create a procedure called say_hello_ to_someone, based on the procedure say_hello you created earlier in this chapter, to address a specific person provided as input to the procedure:
create or replace procedure say_hello_to_someone
(who IN VARCHAR2, message OUT VARCHAR2)
as begin
message := 'Hello there, ' || who;
end;

To test this procedure using the SQL Commands interface, try this:

DECLARE
back_at_ya VARCHAR2(100); BEGIN
say_hello_to_someone('JenniferG',back_at_ya);
dbms_output.put_line('Message is: ' || back_at_ya); END;

The results are as follows:

Message is: Hello there, JenniferG

Statement processed.

Listing 36-3 contains the PHP script to call the new procedure say_hello_to_someone and display it on a very simple Web page. Note that you execute the procedure the same way you do from the SQL Commands interface: within an anonymous PL/SQL block.

Listing 36-3. Calling a Stored Procedure from PHP (use_stored_func.php)

<?php
// Connect to Oracle Database XE
$c = oci_connect('hr', 'hr', '//localhost/xe');

// Create and parse the query
$result = oci_parse($c,
'BEGIN ' .
' say_hello_to_someone(:who, :message); ' .
'END;');

oci_bind_by_name($result,':who',$who,32); // IN parameter oci_bind_by_name($result,':message',$message,64); // OUT parameter

$who = 'Dr. Who';

// Execute the query oci_execute($result);

echo "$message\n";

oci_close($c);
?>

After you create the connection to Oracle Database XE and parse the anonymous block, you
bind the PHP variables to the PL/SQL variables (for both input and output), execute the statement, and display the results on the Web page:

Hello there, Dr. Who

Summary

This chapter introduced Oracle PL/SQL, Oracle Database XE’s server-side programming language. You learned about the advantages and disadvantages to consider when determining whether this feature should be incorporated into your development strategy and all about Oracle’s specific implementation and syntax. In addition, you learned how easy it is to incorporate PL/SQL anony- mous blocks, stored functions, and stored procedures into your PHP applications.
The next chapter introduces another server-side feature of Oracle Database XE: triggers.