MySQL Stored Procedure Programming

by Harrison, Guy

• ISBN13: 9780596100896
• ISBN10: 0596100892
• Format: Paperback
• Copyright: 2006-03-01
• Publisher: Oreilly & Associates Inc

Summary

Like the bestselling Oracle PL/SQL Programming, also written by database expert Steven Feuerstein, MySQL Stored Procedure Programming covers a lot of ground. The book starts with a thorough introduction to stored procedures programming and functions, covering the fundamentals of data types, operators, and using SQL in stored procedures. You'll learn how to build and maintain stored programs - covering transactions, stored functions, and triggers - and how to call and use MySQL- based stored procedures in a variety of languages, including PHP, Perl, Python, .NET, and Java. The book also includes advanced material that describes what you need to know to write high-quality, performance-oriented programs with an emphasis on tuning and best practices.

Author Biography

Guy Harrison has worked with databases for more than a decade, has conducted many MySQL and Oracle training seminars, and is author of several books on Oracle, including "Oracle Desk Reference" (Prentice Hall PTR). Currently a product architect at Quest Software, Harrison has conducted many training seminars and has authored several articles for the Oracle Technical Journal. He resides in Australia.

Steven Feuerstein is considered one of the world's leading experts on the Oracle PL/SQL language. He is the author or coauthor of "Oracle PL/SQL Programming", "Oracle PL/SQL Best Practices", "Oracle PL/SQL Programming: Guide to Oracle8i Features", "Oracle PL/SQL Developer's Workbook", "Oracle Built-in Packages, Advanced Oracle PL/SQL Programming with Packages", and several pocket reference books (all from O'Reilly). Steven is a Senior Technology Advisor with Quest Software, has been developing software since 1980, and worked for Oracle Corporation from 1987 to 1992. He is currently designing and building Qnxo (www.qnxo.com), the world's first active mentoring software.

Table of Contents

Preface	xiii
Part I. Stored Programming Fundamentals
Introduction to MySQL Stored Programs	3	(17)
What Is a Stored Program?	3	(4)
A Quick Tour	7	(6)
Resources for Developers Using Stored Programs	13	(2)
Some Words of Advice for Developers	15	(4)
Conclusion	19	(1)
MySQL Stored Programming Tutorial	20	(29)
What You Will Need	20	(1)
Our First Stored Procedure	20	(5)
Variables	25	(1)
Parameters	25	(5)
Conditional Execution	30	(1)
Loops	31	(1)
Dealing with Errors	32	(1)
Interacting with the Database	33	(5)
Calling Stored Programs from Stored Programs	38	(1)
Putting It All Together	39	(2)
Stored Functions	41	(2)
Triggers	43	(2)
Calling a Stored Procedure from PHP	45	(1)
Conclusion	46	(3)
Language Fundamentals	49	(28)
Variables, Literals, Parameters, and Comments	49	(11)
Operators	60	(4)
Expressions	64	(1)
Built-in Functions	64	(4)
Data Types	68	(4)
MySQL 5 ``Strict'' Mode	72	(4)
Conclusion	76	(1)
Blocks, Conditional Statements, and Iterative Programming	77	(22)
Block Structure of Stored Programs	77	(5)
Conditional Control	82	(11)
Iterative Processing with Loops	93	(5)
Conclusion	98	(1)
Using SQL in Stored Programming	99	(26)
Using Non-Select SQL in Stored Programs	99	(1)
Using Select Statements with an INTO Clause	100	(1)
Creating and Using Cursors	101	(11)
Using Unbounded Select Statements	112	(6)
Performing Dynamic SQL with Prepared Statements	118	(5)
Handling SQL Errors: A Preview	123	(1)
Conclusion	124	(1)
Error Handling	125	(34)
Introduction to Error Handling	125	(4)
Condition Handlers	129	(10)
Named Conditions	139	(1)
Missing SQL:2003 Features	139	(6)
Putting It All Together	145	(5)
Handling Stored Program Errors in the Calling Application	150	(5)
Conclusion	155	(4)
Part II. Stored Program Construction
Creating and Maintaining Stored Programs	159	(20)
Creating Stored Programs	159	(7)
Editing an Existing Stored Program	166	(4)
SQL Statements for Managing Stored Programs	170	(3)
Getting Information About Stored Programs	173	(3)
Conclusion	176	(3)
Transaction Management	179	(26)
Transactional Support in MySQL	180	(3)
Defining a Transaction	183	(2)
Working with Savepoints	185	(5)
Transactions and Locks	190	(11)
Transaction Design Guidelines	201	(2)
Conclusion	203	(2)
MySQL Built-in Functions	205	(33)
String Functions	205	(13)
Numeric Functions	218	(5)
Date and Time Functions	223	(10)
Other Functions	233	(4)
Conclusion	237	(1)
Stored Functions	238	(11)
Creating Stored Functions	238	(4)
SQL Statements in Stored Functions	242	(1)
Calling Stored Functions	242	(2)
Using Stored Functions in SQL	244	(4)
Conclusion	248	(1)
Triggers	249	(12)
Creating Triggers	249	(2)
Using Triggers	251	(5)
Trigger Overhead	256	(1)
Conclusion	257	(4)
Part III. Using MySQL Stored Programs in Applications
Using MySQL Stored Programs in Applications	261	(14)
The Pros and Cons of Stored Programs in Modern Applications	261	(3)
Advantages of Stored Programs	264	(4)
Disadvantages of Stored Programs	268	(2)
Calling Stored Programs from Application Code	270	(3)
Conclusion	273	(2)
Using MySQL Stored Programs with PHP	275	(34)
Options for Using MySQL with PHP	276	(1)
Using PHP with the mysqli Extension	276	(13)
Using MySQL with PHP Data Objects	289	(19)
Conclusion	308	(1)
Using MySQL Stored Programs with Java	309	(34)
Review of JDBC Basics	310	(7)
Using Stored Programs in JDBC	317	(6)
Stored Programs and J2EE Applications	323	(9)
Using Stored Procedures with Hibernate	332	(5)
Using Stored Procedures with Spring	337	(5)
Conclusion	342	(1)
Using MySQL Stored Programs with Perl	343	(21)
Review of Perl DBD::mysql Basics	344	(10)
Executing Stored Programs with DBD::mysql	354	(8)
Conclusion	362	(2)
Using MySQL Stored Programs with Python	364	(22)
Installing the MySQLdb Extension	364	(1)
MySQLdb Basics	365	(8)
Using Stored Programs with MySQLdb	373	(7)
A Complete Example	380	(5)
Conclusion	385	(1)
Using MySQL Stored Programs with .NET	386	(35)
Review of ADO.NET Basics	386	(15)
Using Stored Programs in ADO.NET	401	(12)
Using Stored Programs in ASP.NET	413	(4)
Conclusion	417	(4)
Part IV. Optimizing Stored Programs
Stored Program Security	421	(21)
Permissions Required for Stored Programs	422	(1)
Execution Mode Options for Stored Programs	423	(11)
Stored Programs and Code Injection	434	(6)
Conclusion	440	(2)
Tuning Stored Programs and Their SQL	442	(21)
Why SQL Tuning Is So Important	443	(2)
How MySQL Processes SQL	445	(4)
SQL Tuning Statements and Practices	449	(10)
About the Upcoming Examples	459	(3)
Conclusion	462	(1)
Basic SQL Tuning	463	(23)
Tuning Table Access	463	(17)
Tuning Joins	480	(5)
Conclusion	485	(1)
Advanced SQL Tuning	486	(22)
Tuning Subqueries	486	(7)
Tuning ``Anti-Joins'' Using Subqueries	493	(2)
Tuning Subqueries in the From Clause	495	(6)
Tuning Order and Group By	501	(2)
Tuning DML (Insert, Update, Delete)	503	(3)
Conclusion	506	(2)
Optimizing Stored Program Code	508	(25)
Performance Characteristics of Stored Programs	508	(1)
How Fast Is the Stored Program Language?	509	(3)
Reducing Network Traffic with Stored Programs	512	(3)
Stored Programs as an Alternative to Expensive SQL	515	(4)
Optimizing Loops	519	(3)
IF and CASE Statements	522	(4)
Recursion	526	(2)
Cursors	528	(1)
Trigger Overhead	529	(3)
Conclusion	532	(1)
Best Practices in MySQL Stored Program Development	533	(50)
The Development Process	533	(6)
Coding Style and Conventions	539	(5)
Variables	544	(7)
Conditional Logic	551	(4)
Loop Processing	555	(4)
Exception Handling	559	(3)
SQL in Stored Programs	562	(4)
Dynamic SQL	566	(3)
Program Construction	569	(8)
Performance	577	(4)
Conclusion	581	(2)
Index	583