MySQL Stored Procedure Programming

Conclusion

There is no "one-size-fits-all" answer to the question "Should I use stored programs in my application?" There are those who believe that virtually all of an application's database interactions should be made through stored program calls, and those who believe that stored programs should be used only in very special circumstances. You will need to make your own determination as to the value of using MySQL stored programs in your application.

As we've discussed in this chapter, the use of stored programs can provide significant advantages:

• Stored programs can substantially improve the security of your application.

• Stored programs can be used to provide an abstract data access layer that can improve the separation between business logic and data access logic (of course, stored programs are not required to do this—they are just one means to do so).

• Stored programs can reduce network traffic.

• Stored programs can be used to implement common routines accessible from multiple applications.

• Stored programs allow for a convenient division of duties between those whose skills are database-centric and those whose skills are programming-centric.

• The use of stored programs can (sometimes) improve application portability.

But you also need to consider the potential disadvantages of using stored programs:

• Stored programs are often slower—especially for computationally expensive operations—than equivalent middle-tier code.

• The use of stored programs can lead to fragmentation of your application ...