Modifying Oracle PL/SQL Subprograms

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 anonymous blocks, stored functions, and stored procedures into your PHP applications.

The next chapter introduces another server-side feature of Oracle Database XE: triggers.