MySQL Stored Procedure Programming

ISBN: 0596100892
EAN: 2147483647

Year: 2004
Pages: 208

Authors: Guy Harrison, Steven Feuerstein

BUY ON AMAZON

Part I: Stored Programming Fundamentals

Introduction to MySQL Stored Programs

• Introduction to MySQL Stored Programs
• What Is a Stored Program?
• A Quick Tour
• Resources for Developers Using Stored Programs
• Some Words of Advice for Developers
• Conclusion

MySQL Stored Programming Tutorial

• MySQL Stored Programming Tutorial
• What You Will Need
• Our First Stored Procedure
• Variables
• Parameters
• Conditional Execution
• Loops
• Dealing with Errors
• Interacting with the Database
• Calling Stored Programs from Stored Programs
• Putting It All Together
• Stored Functions
• Triggers
• Calling a Stored Procedure from PHP
• Conclusion

Language Fundamentals

• Language Fundamentals
• Variables, Literals, Parameters, and Comments
• Operators
• Expressions
• Built-in Functions
• Data Types
• MySQL 5 Strict Mode
• Conclusion

Blocks, Conditional Statements, and Iterative Programming

• Blocks, Conditional Statements, and Iterative Programming
• Block Structure of Stored Programs
• Conditional Control
• Iterative Processing with Loops
• Conclusion

Using SQL in Stored Programming

• Using SQL in Stored Programming
• Using Non-SELECT SQL in Stored Programs
• Using SELECT Statements with an INTO Clause
• Creating and Using Cursors
• Using Unbounded SELECT Statements
• Performing Dynamic SQL with Prepared Statements
• Handling SQL Errors: A Preview
• Conclusion

Error Handling

• Error Handling
• Introduction to Error Handling
• Condition Handlers
• Named Conditions
• Missing SQL:2003 Features
• Putting It All Together
• Handling Stored Program Errors in the Calling Application
• Conclusion

Part II: Stored Program Construction

Creating and Maintaining Stored Programs

• Creating and Maintaining Stored Programs
• Creating Stored Programs
• Editing an Existing Stored Program
• SQL Statements for Managing Stored Programs
• Getting Information About Stored Programs
• Conclusion

Transaction Management

• Transaction Management
• Transactional Support in MySQL
• Defining a Transaction
• Working with Savepoints
• Transactions and Locks
• Transaction Design Guidelines
• Conclusion

MySQL Built-in Functions

• MySQL Built-in Functions
• String Functions
• Numeric Functions
• Date and Time Functions
• Other Functions
• Conclusion

Stored Functions

• Stored Functions
• Creating Stored Functions
• SQL Statements in Stored Functions
• Calling Stored Functions
• Using Stored Functions in SQL
• Conclusion

Triggers

• Triggers
• Creating Triggers
• Using Triggers
• Trigger Overhead
• Conclusion

Part III: Using MySQL Stored Programs in Applications

Using MySQL Stored Programs in Applications

• Using MySQL Stored Programs in Applications
• The Pros and Cons of Stored Programs in Modern Applications
• Advantages of Stored Programs
• Disadvantages of Stored Programs
• Calling Stored Programs from Application Code
• Conclusion

Using MySQL Stored Programs with PHP

• Using MySQL Stored Programs with PHP
• Options for Using MySQL with PHP
• Using PHP with the mysqli Extension
• Using MySQL with PHP Data Objects
• Conclusion

Using MySQL Stored Programs with Java

• Using MySQL Stored Programs with Java
• Review of JDBC Basics
• Using Stored Programs in JDBC
• Stored Programs and J2EE Applications
• Using Stored Procedures with Hibernate
• Using Stored Procedures with Spring
• Conclusion

Using MySQL Stored Programs with Perl

• Using MySQL Stored Programs with Perl
• Review of Perl DBD::mysql Basics
• Executing Stored Programs with DBD::mysql
• Conclusion

Using MySQL Stored Programs with Python

• Using MySQL Stored Programs with Python
• Installing the MySQLdb Extension
• MySQLdb Basics
• Using Stored Programs with MySQLdb
• A Complete Example
• Conclusion

Using MySQL Stored Programs with .NET

• Using MySQL Stored Programs with .NET
• Review of ADO.NET Basics
• Using Stored Programs in ADO.NET
• Using Stored Programs in ASP.NET
• Conclusion

Part IV: Optimizing Stored Programs

Stored Program Security

• Stored Program Security
• Permissions Required for Stored Programs
• Execution Mode Options for Stored Programs
• Stored Programs and Code Injection
• Conclusion

Tuning Stored Programs and Their SQL

• Tuning Stored Programs and Their SQL
• Why SQL Tuning Is So Important
• How MySQL Processes SQL
• SQL Tuning Statements and Practices
• About the Upcoming Examples
• Conclusion

Basic SQL Tuning

• Basic SQL Tuning
• Tuning Table Access
• Tuning Joins
• Conclusion

Advanced SQL Tuning

• Advanced SQL Tuning
• Tuning Subqueries
• Tuning Anti-Joins Using Subqueries
• Tuning Subqueries in the FROM Clause
• Tuning ORDER and GROUP BY
• Tuning DML (INSERT, UPDATE, DELETE)
• Conclusion

Optimizing Stored Program Code

• Optimizing Stored Program Code
• Performance Characteristics of Stored Programs
• How Fast Is the Stored Program Language?
• Reducing Network Traffic with Stored Programs
• Stored Programs as an Alternative to Expensive SQL
• Optimizing Loops
• IF and CASE Statements
• Recursion
• Cursors
• Trigger Overhead
• Conclusion

Best Practices in MySQL Stored Program Development

• Best Practices in MySQL Stored Program Development
• The Development Process
• Coding Style and Conventions
• Variables
• Conditional Logic
• Loop Processing
• Exception Handling
• SQL in Stored Programs
• Dynamic SQL
• Program Construction
• Performance