There is a persistentand often livelydebate in the programming community about the benefits and appropriateness of using stored programs in applications.
Database stored programs first came to prominence in the late 1980s and early 1990s during what might be called the client/server revolution. In the client/server environment of that time, stored programs had some obvious advantages (aspects of which persist in N-tier and Internet-based architectures):
The use of stored programs in client/server applications was, and is, most prevalent in applications that use Microsoft SQL Server (and its technological predecessor, Sybase) and Oracle. The Microsoft SQL Server and Oracle stored program languages (Transact-SQL and PL/SQL, respectively) have substantially different characteristicsespecially regarding the ability of a stored program to return a result set. The differences between the two languages have resulted in somewhat different usage patterns:
SQL Server-based applications
For these applications, the dominant pattern is to encapsulate all database interaction between client and serverincluding queriesinto stored programs. This is cited as providing better security and reduced network traffic.
For these applications, it was initially impossible to return a result set from a stored program and, although this became possible in later releases, it was never particularly convenient or easy to do so. As a result, Oracle-based applications tended to use stored programs to implement transaction processing, but would use native SQL to retrieve result sets.
With the emergence of three-tier architectures and web applications, many of the incentives to use stored programs from within applications disappeared. Application clients are now often browser-based; security is predominantly handled by a middle tier; and the middle tier possesses the ability to encapsulate business logic. Most of the functions for which stored programs were used in client/server applications can now be implemented in middle-tier code (e.g., in PHP, Java, C#, etc.). Transferring processing to the middle tier can also enhance load balancing and scalability.
Even so, many of the original advantages of stored programs (such as enhanced security and reduction in network traffic) still apply, if to a reduced degree. The use of stored programs is still regarded as a "best practice" by many application developers and architects.
Today, there are three schools of thought regarding the use of stored programs in applications:
All stored programs, all the time
This segment of the development community continues to believe that stored programs should be used for all interaction between the client (now the middle tier) and the database. They argue that this pattern provides more security to the database, and also provides a level of abstraction between the underlying data model and the business logic in the middle tier.
Stored programs only when absolutely necessary
This segment believes that stored programs should play only a minor role in a modern application development. They argue that stored programs add additional and unnecessary complexity to the application design; that they fragment the logic between the middle tier and the database; and that they get in the way of object-relational mapping schemes such as Java J2EE's CMP and Hibernate.
Use what works
This segment (probably the quiet majority) is fairly pragmaticthey use stored programs selectively when the use of a stored program seems warranted, but they tend to use native SQL when it is easier and more convenient to do so.
It's up to you to decide which model works best for you and your application. In the next few sections we will try to provide you with as much information as we can to help you make an informed decision. To sum up our personal feelings on the matter, we do think that an application that encapsulates all database interaction within stored programs is employing a valid and effective pattern. In particular, this kind of application can be made virtually immune to SQL injection attacks, and will be much less vulnerable to exploits based on compromised passwords. We also believe in separating data access logic from business logic, and the use of stored programs is a good way to do this. However, stored programs are not a natural choice for all applications; for instance, using stored programs exclusively tends to interfere with object-relational mapping schemes such as J2EE CMP and Hibernate.
In the next few sections, we'll look in some detail at the advantages stored programs offer an application and compare those to possible disadvantages. To summarize here, stored programs offer these advantages:
Against these possible advantages, consider the following disadvantages:
Part I: Stored Programming Fundamentals
Introduction to MySQL Stored Programs
MySQL Stored Programming Tutorial
Blocks, Conditional Statements, and Iterative Programming
Using SQL in Stored Programming
Part II: Stored Program Construction
Creating and Maintaining Stored Programs
MySQL Built-in Functions
Part III: Using MySQL Stored Programs in Applications
Using MySQL Stored Programs in Applications
Using MySQL Stored Programs with PHP
Using MySQL Stored Programs with Java
Using MySQL Stored Programs with Perl
Using MySQL Stored Programs with Python
Using MySQL Stored Programs with .NET
Part IV: Optimizing Stored Programs
Stored Program Security
Tuning Stored Programs and Their SQL
Basic SQL Tuning
Advanced SQL Tuning
Optimizing Stored Program Code
Best Practices in MySQL Stored Program Development