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).
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 otherwise 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 maintaining 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 subprograms 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 information about this topic.
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.
Please check back next week for the continuation of this article.