Oracle PL/SQL Subprograms

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.

{mospagebreak title=Subprogram Advantages and Disadvantages}

Subprogram Advantages

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

  1. Consistency: When multiple applications written in different languages are performing the same database tasks, consolidating these like functions within subprograms decreases otherwise redundant development processes.
  2. 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 maintaining them as subprograms. 
  3. 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 subprograms is a great way to ensure that developers have access only to the information necessary to carry out their tasks. 
  4. 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 information 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: 

  1. 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. 
  2. 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. 
  3. 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.

Please check back next week for the continuation of this article.

Google+ Comments

Google+ Comments