MySQL Stored Procedure Programming

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.

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.

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

What is included with this book?

The New copy of this book will include any supplemental materials advertised. Please check the title of the book to determine if it should include any access cards, study guides, lab manuals, CDs, etc.

The Used, Rental and eBook copies of this book are not guaranteed to include any supplemental materials. Typically, only the book itself is included. This is true even if the title states it includes any access cards, study guides, lab manuals, CDs, etc.

FREE SHIPPING

Rent More, Save More! Use code: BBRENTAL

5% off 1 book, 7% off 2 books, 10% off 3+ books

MySQL Stored Procedure Programming

Summary

Author Biography

Table of Contents

Supplemental Materials