MySQL Stored Procedure Programming

Structure of This Book

MySQL Stored Procedure Programming is divided into four major sections:

Part I, Stored Programming Fundamentals

This first part of the book introduces the MySQL stored program language and provides a detailed description of the language structure and usage.

• Chapter 1, Introduction to MySQL Stored Programs, asks the fundamental questions: Where did the language come from? What is it good for? What are the main features of the language?

• Chapter 2, MySQL Stored Programming Tutorial, is a tutorial that is designed to get you started with the language as quickly as possible; it shows you how to create basic stored programs of each type and provides interactive examples of major language functions.

• Chapter 3, Language Fundamentals, describes how to work with variables, literals, operators, and expressions.

• Chapter 4, Blocks, Conditional Statements, and Iterative Programming, explains how to implement conditional commands (IF and CASE) and looping structures.

• Chapter 5, Using SQL in Stored Programming, discusses how SQL can be used within the language.

• Chapter 6, Error Handling, provides the details of how errors can be handled.

Part II, Stored Program Construction

This part of the book describes how you can use the elements described in Part I to build functional and useful stored programs.

• Chapter 7, Creating and Maintaining Stored Programs, outlines the statements available for creating and modifying stored programs and provides some advice on how to manage your stored program source code.

• Chapter 8, Transaction Management, explains the fundamentals of transaction handling in stored programs.

• Chapter 9, MySQL Built-in Functions, details the built-in functions that can be used in stored programs.

• Chapter 10, Stored Functions, describes how you can create and use one particular type of stored program: the stored function.

• Chapter 11, Triggers, describes another special type of stored program—the database trigger—which is activated in response to DML (Data Manipulation Language) executed on a database table.

Part III, Using MySQL Stored Programs in Applications

Stored programs can be used for a variety of purposes, including the implementation of utility routines for use by MySQL DBAs and developers. However, the most important use of stored programs is within applications, as we describe in this part of the book. Stored programs allow us to move some of our application code into the database server itself; if we do this wisely, we may benefit from an application that will then be more secure, efficient, and maintainable.

• Chapter 12, Using MySQL Stored Programs in Applications, considers the merits of and best practices for using stored programs inside modern—typically, web-based—applications. The other chapters in this part of the book show you how to use stored procedures and functions from within the development languages most commonly used in conjunction with MySQL.

• Chapter 13, Using MySQL Stored Programs with PHP, describes the use of stored programs from PHP. We primarily discuss the mysqli and PDO interfaces—recently bundled by MySQL asConnector/PHP—and their stored program support.

• Chapter 14, Using MySQL Stored Programs with Java, describes the use of stored programs from Java and includes the use of stored programs using JDBC, Servlets, Enterprise JavaBeans?, Hibernate, and Spring.

• Chapter 15, Using MySQL Stored Programs with Perl, describes the use of stored programs from Perl.

• Chapter 16, Using MySQL Stored Programs with Python, describes the use of stored programs from Python.

• Chapter 17, Using MySQL Stored Programs with .NET, describes the use of stored programs from .NET languages such as C# and VB.NET.

Part IV, Optimizing Stored Programs

This final part of the book hopes to take you from “good” to “great.” Getting programs to work correctly is hard enough: any program that works is probably a good program. A great program is one that performs efficiently, is robust and secure, and is easily maintained.

• Chapter 18, Stored Program Security, discusses the unique security concerns and opportunities raised by stored procedures and functions.

• Chapter 19, Tuning Stored Programs and Their SQL. This chapter, along with Chapters 20 through 22, covers the performance optimization of stored programs. This chapter kicks off with a general discussion of performance tuning tools and techniques.

• Chapter 20, Basic SQL Tuning. The performance of your stored programs will be largely dependent on the performance of the SQL inside them, so this chapter provides guidelines for tuning basic SQL.

• Chapter 21, Advanced SQL Tuning. This chapter builds on Chapter 20, describing more advanced tuning approaches.

• Chapter 22, Optimizing Stored Program Code, covers the performance tuning of the stored program code itself.

• Chapter 23, Best Practices in MySQL Stored Program Development, wraps up the book with a look at best practices in stored program development. These guidelines should help you write stored programs that are fast, secure, maintainable, and bug free.

You’ll find that a significant proportion of the book includes material that pertains not only to stored program development, but also to development in other languages such as PHP or Java. For instance, we believe that you cannot write a high-performance stored program without tuning the SQL that the program contains; therefore, we have devoted significant coverage to SQL tuning—material that would also be of benefit regardless of the language in which the SQL is embedded. Likewise, some of the discussions around transaction design and security could be applicable in other languages.