PL/SQL subprograms can be thought of as shortcuts that help you to perform certain kinds of Oracle queries less painfully as your applications increase in size and complexity. Keep reading to learn how you can use them to make your programming life a little easier. 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).
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 database 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 environment, 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 development 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? Furthermore, 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 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.