MySQL Stored Procedure Programming

Advance Praise for MySQL Stored Procedure Programming; Preface; Objectives of This Book; Structure of This Book; What This Book Does Not Cover; Conventions Used in This Book; Which Version?; Resources Available at the Book's Web Site; Using Code Examples; Safari?? Enabled; How to Contact Us; Acknowledgments; Part I: Stored Programming Fundamentals; Chapter 1: Introduction to MySQL Stored Programs; 1.1 What Is a Stored Program?; 1.2 A Quick Tour; 1.3 Resources for Developers Using Stored Programs; 1.4 Some Words of Advice for Developers; 1.5 Conclusion; Chapter 2: MySQL Stored Programming Tutorial; 2.1 What You Will Need; 2.2 Our First Stored Procedure; 2.3 Variables; 2.4 Parameters; 2.5 Conditional Execution; 2.6 Loops; 2.7 Dealing with Errors; 2.8 Interacting with the Database; 2.9 Calling Stored Programs from Stored Programs; 2.10 Putting It All Together; 2.11 Stored Functions; 2.12 Triggers; 2.13 Calling a Stored Procedure from PHP; 2.14 Conclusion; Chapter 3: Language Fundamentals; 3.1 Variables, Literals, Parameters, and Comments; 3.2 Operators; 3.3 Expressions; 3.4 Built-in Functions; 3.5 Data Types; 3.6 MySQL 5 "Strict" Mode; 3.7 Conclusion; Chapter 4: Blocks, Conditional Statements, and Iterative Programming; 4.1 Block Structure of Stored Programs; 4.2 Conditional Control; 4.3 Iterative Processing with Loops; 4.4 Conclusion; Chapter 5: Using SQL in Stored Programming; 5.1 Using Non-SELECT SQL in Stored Programs; 5.2 Using SELECT Statements with an INTO Clause; 5.3 Creating and Using Cursors; 5.4 Using Unbounded SELECT Statements; 5.5 Performing Dynamic SQL with Prepared Statements; 5.6 Handling SQL Errors: A Preview; 5.7 Conclusion; Chapter 6: Error Handling; 6.1 Introduction to Error Handling; 6.2 Condition Handlers; 6.3 Named Conditions; 6.4 Missing SQL:2003 Features; 6.5 Putting It All Together; 6.6 Handling Stored Program Errors in the Calling Application; 6.7 Conclusion; Part II: Stored Program Construction; Chapter 7: Creating and Maintaining Stored Programs; 7.1 Creating Stored Programs; 7.2 Editing an Existing Stored Program; 7.3 SQL Statements for Managing Stored Programs; 7.4 Getting Information About Stored Programs; 7.5 Conclusion; Chapter 8: Transaction Management; 8.1 Transactional Support in MySQL; 8.2 Defining a Transaction; 8.3 Working with Savepoints; 8.4 Transactions and Locks; 8.5 Transaction Design Guidelines; 8.6 Conclusion; Chapter 9: MySQL Built-in Functions; 9.1 String Functions; 9.2 Numeric Functions; 9.3 Date and Time Functions; 9.4 Other Functions; 9.5 Conclusion; Chapter 10: Stored Functions; 10.1 Creating Stored Functions; 10.2 SQL Statements in Stored Functions; 10.3 Calling Stored Functions; 10.4 Using Stored Functions in SQL; 10.5 Conclusion; Chapter 11: Triggers; 11.1 Creating Triggers; 11.2 Using Triggers; 11.3 Trigger Overhead; 11.4 Conclusion; Part III: Using MySQL Stored Programs in Applications; Chapter 12: Using MySQL Stored Programs in Applications; 12.1 The Pros and Cons of Stored Programs in Modern Applications; 12.2 Advantages of Stored Programs; 12.3 Disadvantages of Stored Programs; 12.4 Calling Stored Programs from Application Code; 12.5 Conclusion; Chapter 13: Using MySQL Stored Programs with PHP; 13.1 Options for Using MySQL with PHP; 13.2 Using PHP with the mysqli Extension; 13.3 Using MySQL with PHP Data Objects; 13.4 Conclusion; Chapter 14: Using MySQL Stored Programs with Java; 14.1 Review of JDBC Basics; 14.2 Using Stored Programs in JDBC; 14.3 Stored Programs and J2EE Applications; 14.4 Using Stored Procedures with Hibernate; 14.5 Using Stored Procedures with Spring; 14.6 Conclusion; Chapter 15: Using MySQL Stored Programs with Perl; 15.1 Review of Perl DBD::mysql Basics; 15.2 Executing Stored Programs with DBD::mysql; 15.3 Conclusion; Chapter 16: Using MySQL Stored Programs with Python; 16.1 Installing the MySQLdb Extension; 16.2 MySQLdb Basics; 16.3 Using Stored Programs with MySQLdb; 16.4 A Complete Example; 16.5 Conclusion; Chapter 17: Using MySQL Stored Programs with .NET; 17.1 Review of ADO.NET Basics; 17.2 Using Stored Programs in ADO.NET; 17.3 Using Stored Programs in ASP.NET; 17.4 Conclusion; Part IV: Optimizing Stored Programs; Chapter 18: Stored Program Security; 18.1 Permissions Required for Stored Programs; 18.2 Execution Mode Options for Stored Programs; 18.3 Stored Programs and Code Injection; 18.4 Conclusion; Chapter 19: Tuning Stored Programs and Their SQL; 19.1 Why SQL Tuning Is So Important; 19.2 How MySQL Processes SQL; 19.3 SQL Tuning Statements and Practices; 19.4 About the Upcoming Examples; 19.5 Conclusion; Chapter 20: Basic SQL Tuning; 20.1 Tuning Table Access; 20.2 Tuning Joins; 20.3 Conclusion; Chapter 21: Advanced SQL Tuning; 21.1 Tuning Subqueries; 21.2 Tuning "Anti-Joins" Using Subqueries; 21.3 Tuning Subqueries in the FROM Clause; 21.4 Tuning ORDER and GROUP BY; 21.5 Tuning DML (INSERT, UPDATE, DELETE); 21.6 Conclusion; Chapter 22: Optimizing Stored Program Code; 22.1 Performance Characteristics of Stored Programs; 22.2 How Fast Is the Stored Program Language?; 22.3 Reducing Network Traffic with Stored Programs; 22.4 Stored Programs as an Alternative to Expensive SQL; 22.5 Optimizing Loops; 22.6 IF and CASE Statements; 22.7 Recursion; 22.8 Cursors; 22.9 Trigger Overhead; 22.10 Conclusion; Chapter 23: Best Practices in MySQL Stored Program Development; 23.1 The Development Process; 23.2 Coding Style and Conventions; 23.3 Variables; 23.4 Conditional Logic; 23.5 Loop Processing; 23.6 Exception Handling; 23.7 SQL in Stored Programs; 23.8 Dynamic SQL; 23.9 Program Construction; 23.10 Performance; 23.11 Conclusion; Colophon;