MySQL Stored Procedure Programming

638 Pages·2006·6.83 MB·English

by  Guy Harrison

Advance Praise for MySQL Stored Procedure Programming
“Ididn’thonestlybelieveabookcouldbewrittenonthistopicthatwouldn’tbe
toodry.ButGuyandStevenshowthedepthofthesubjectandmakethematerial
available to readers. It was a wonderful read.”
—Brian Aker, Director of Architecture, MySQL AB
“ItwasapleasuretoworkwithGuyandtheeditoratO’Reilly,doingthetech
reviewofmanyofthechaptersforthisbook.Theauthorshaveanexcellentgrasp
of the subject matter. I found the material easy to read, with lots of code exam-
ples. MySQL users should find this book an excellent resource.”
—Arjen Lentz, Community Relations Manager, MySQL AB
“BecauseMySQLusageisgrowingsorapidlyamongmodernenterprises,devel-
opers and DBAs alike are desperately looking for expert help that shows them
how to create high-performance stored procedures and other efficient MySQL
code. I doubt that anyone will find better guides than Guy Harrison and Steven
Feuerstein when it comes to advice on writing the absolutely best MySQL code.”
—Robin Schumacher, Director of Product Management,
MySQL AB
“This is the first book I’ve seen that really concentrates on MySQL’s stored
procedures. I found tips here that I’d never seen before.”
—Peter Gulutzan, MySQL Software Architect
“MySQL 5.0 opens up a new world to MySQL users, and this book is a great
tour guide.”
—Andy Dustman, Author of MySQL Python API
“GuyandStevenhaveprovidedMySQLdeveloperswithagem.Theynotonly
coverthenutsandboltsofwritingstoredproceduresinMySQL,butalsoprovide
sound advice on designing database applications in the real world. In addition,
they write with a sense of humor that makes the book a joy to read.”
—James Cooper, Technology Consultant, Seattle, WA

MySQL Stored Procedure Programming
Other resources from O’Reilly
Related titles MySQL in a Nutshell SQL in a Nutshell
MySQL Cookbook™ SQL Cookbook™
High Performance MySQL Learning SQL
Web Database Application SQL Pocket Guide
with PHP and MySQL
oreilly.com oreilly.com is more than a complete catalog of O’Reilly books.
You’llalsofindlinkstonews,events,articles,weblogs,sample
chapters, and code examples.
oreillynet.comistheessentialportalfordevelopersinterestedin
openandemergingtechnologies,includingnewplatforms,pro-
gramming languages, and operating systems.
Conferences O’Reillybringsdiverseinnovatorstogethertonurturetheideas
thatsparkrevolutionaryindustries.Wespecializeindocument-
ing the latest tools and systems, translating the innovator’s
knowledgeintousefulskillsforthoseinthetrenches.Visitcon-
ferences.oreilly.com for our upcoming events.
Safari Bookshelf (safari.oreilly.com) is the premier online refer-
ence library for programmers and IT professionals. Conduct
searchesacrossmorethan1,000books.Subscriberscanzeroin
on answers to time-critical questions in a matter of seconds.
Read the books on your Bookshelf from cover to cover or sim-
ply flip to the page you need. Try it today for free.
MySQL Stored Procedure
Programming
Guy Harrison
with Steven Feuerstein
Beijing • Cambridge • Farnham • Köln • Paris • Sebastopol • Taipei • Tokyo
MySQL Stored Procedure Programming
by Guy Harrison with Steven Feuerstein
Copyright © 2006 O’Reilly Media, Inc. All rights reserved.
Printed in the United States of America.
Published by O’Reilly Media, Inc., 1005 Gravenstein Highway North, Sebastopol, CA 95472.
O’Reilly books may be purchased for educational, business, or sales promotional use. Online editions
are also available for most titles (safari.oreilly.com). For more information, contact our
corporate/institutional sales department: [phone] [e-mail].
Editor: Deborah Russell Cover Designer: Karen Montgomery
Production Editor: Adam Witwer Interior Designer: David Futato
Production Services: Argosy Publishing Illustrators: Robert Romano, Jessamyn Read,
and Lesley Borash
Printing History:
March 2006: First Edition.
Nutshell Handbook, the Nutshell Handbook logo, and the O’Reilly logo are registered trademarks of
O’Reilly Media, Inc.MySQL Stored Procedure Programming, the image of a middle spotted
woodpecker, and related trade dress are trademarks of O’Reilly Media, Inc.
Manyofthedesignationsusedbymanufacturersandsellerstodistinguishtheirproductsareclaimedas
trademarks. Where those designations appear in this book, and O’Reilly Media, Inc. was aware of a
trademark claim, the designations have been printed in caps or initial caps.
While every precaution has been taken in the preparation of this book, the publisher and authors
assume no responsibility for errors or omissions, or for damages resulting from the use of the
information contained herein.
This book uses RepKover™, a durable and flexible lay-flat binding.
ISBN-10: 0-596-10089-2
ISBN-13: 978-0-596-10089-6
[M] [12/07]
Table of Contents
Preface . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .xiii
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
vii
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
viii | Table of Contents