MySQL Stored Procedure Programming

Part I. Stored Programming Fundamentals
1. Introduction to MySQL Stored Programs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3
What Is a Stored Program? 3
A Quick Tour 7
Resources for Developers Using Stored Programs 13
Some Words of Advice for Developers 15
Conclusion 19
2. MySQL Stored Programming Tutorial . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 20
What You Will Need 20
Our First Stored Procedure 20
Variables 25
Parameters 25
Conditional Execution 30
Loops 31
Dealing with Errors 32
Interacting with the Database 33
Calling Stored Programs from Stored Programs 38
Putting It All Together 39
Stored Functions 41
Triggers 43
Calling a Stored Procedure from PHP 45
Conclusion 46

3. Language Fundamentals . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 49
Variables, Literals, Parameters, and Comments 49
Operators 60
Expressions 64
Built-in Functions 64
Data Types 68
MySQL 5 “Strict” Mode 72
Conclusion 76
4. Blocks, Conditional Statements, and Iterative Programming . . . . . . . . . . . . 77
Block Structure of Stored Programs 77
Conditional Control 82
Iterative Processing with Loops 93
Conclusion 98
5. Using SQL in Stored Programming . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 99
Using Non-SELECT SQL in Stored Programs 99
Using SELECT Statements with an INTO Clause 100
Creating and Using Cursors 101
Using Unbounded SELECT Statements 112
Performing Dynamic SQL with Prepared Statements 118
Handling SQL Errors: A Preview 123
Conclusion 124
6. Error Handling . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 125
Introduction to Error Handling 125
Condition Handlers 129
Named Conditions 139
Missing SQL:2003 Features 139
Putting It All Together 145
Handling Stored Program Errors in the Calling Application 150
Conclusion 155