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)
// Create and execute the query
// Format the table
// Output the column headers
// output the results
You can see the results for the first ten rows of the EMPLOYEES table in Figure 36-3.
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
To test this procedure using the SQL Commands interface, try this:
The results are as follows:
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)
// Create and parse the query
oci_bind_by_name($result,':who',$who,32); // IN parameter
$who = 'Dr. Who';
// Execute the query
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
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.
blog comments powered by Disqus