book

MySQL Stored Procedure Programming

by Guy Harrison, Steven Feuerstein

March 2006

Intermediate to advanced

640 pages

17h 8m

English

O'Reilly Media, Inc.

Read now

Unlock full access

Objectives of This Book

1.1. What Is a Stored Program?1.1.1. Why Use Stored Programs?1.1.2. A Brief History of MySQL1.1.3. MySQL Stored Procedures, Functions, and Triggers
1.2.1. Integration with SQL1.2.2. Control and Conditional Logic1.2.3. Stored Functions1.2.4. When Things Go Wrong1.2.5. Triggers
1.3.1. Books1.3.2. Internet Resources
1.4.1. Don't Be in Such a Hurry!1.4.2. Don't Be Afraid to Ask for Help1.4.3. Take a Creative, Even Radical Approach
2.1. What You Will Need
2.2.1. Creating the Procedure2.2.2. Creating the Procedure Using the MySQL Query Browser
2.4.1. Parameter Modes
2.8.1. SELECTing INTO Local Variables2.8.2. Using Cursors2.8.3. Returning Result Sets from Stored Procedures2.8.4. Embedding Non-SELECTs
3.1. Variables, Literals, Parameters, and Comments3.1.1. Variables3.1.2. Literals3.1.3. Rules for Variable Names3.1.4. Assigning Values to Variables3.1.5. Parameters3.1.6. User Variables3.1.7. Comments
3.2.1. Mathematical Operators3.2.2. Comparison Operators3.2.3. Logical Operators3.2.4. Bitwise Operators
3.5.1. String Data Types3.5.1.1. The ENUM data type3.5.1.2. The SET data type3.5.2. Numeric Data Types3.5.3. Date and Time Data Types3.5.4. TEXT and BLOB Data Types
3.6.1. Stored Program Behavior and Strict Mode3.6.2. Program Examples
4.1. Block Structure of Stored Programs4.1.1. Structure of a Block4.1.2. Nested Blocks
4.2.1. The IF Statement4.2.1.1. TRUE or FALSE (or neither)?4.2.1.2. Simple IF-THEN combinations4.2.1.3. IF-THEN-ELSE statements4.2.1.4. IF-THEN-ELSEIF-ELSE statements4.2.2. The CASE Statement4.2.2.1. Simple CASE statement4.2.2.2. "Searched" CASE statement4.2.3. IF Versus CASE
4.3.1. LOOP Statement4.3.2. LEAVE Statement4.3.3. ITERATE Statement4.3.4. REPEAT ... UNTIL Loop4.3.5. WHILE Loop4.3.6. Nested Loops4.3.7. Parting Comments on Loops
5.1. Using Non-SELECT SQL in Stored Programs
5.3.1. Defining a Cursor5.3.2. Cursor Statements5.3.3. Fetching a Single Row from a Cursor5.3.4. Fetching an Entire Result Set5.3.5. Types of Cursor Loops5.3.6. Nested Cursor Loops5.3.7. Exiting the Cursor Loop Prematurely5.3.8. Cursor Error Conditions
5.4.1. Retrieving the Result Sets in the Calling Program5.4.2. Returning Result Sets to Another Stored Procedure
6.1. Introduction to Error Handling6.1.1. A Simple First Example6.1.2. Handling Last Row Conditions
6.2.1. Types of Handlers6.2.2. Handler Conditions6.2.3. Handler Examples6.2.4. Handler Precedence6.2.5. Scope of Condition Handlers
6.4.1. Directly Accessing SQLCODE or SQLSTATE6.4.2. Creating Your Own Exceptions with the SIGNAL Statement6.4.3. Emulating the SIGNAL Statement
6.6.1. PHP6.6.2. Perl6.6.3. Java/JDBC6.6.4. Python6.6.5. C# .NET6.6.6. Visual Basic .NET
7.1. Creating Stored Programs7.1.1. Editing Stored Programs Using a System Editor7.1.2. Using the MySQL Query Browser7.1.3. Using Third-Party Tools7.1.4. Handling Semicolons in Stored Program Code
7.2.1. Editing a Program in Place7.2.2. Maintaining Stored Programs in External Files
7.3.1. CREATE PROCEDURE7.3.2. CREATE FUNCTION7.3.3. CREATE TRIGGER7.3.4. ALTER PROCEDURE/FUNCTION7.3.5. DROP PROCEDURE/FUNCTION/TRIGGER
7.4.1. SHOW PROCEDURE/FUNCTION STATUS7.4.2. SHOW CREATE PROCEDURE/FUNCTION7.4.3. INFORMATION_SCHEMA.ROUTINES Table7.4.4. INFORMATION_SCHEMA.TRIGGERS Table
8.1. Transactional Support in MySQL8.1.1. Isolation Levels8.1.2. Transaction Management Statements
8.4.1. Situations in Which Locks Arise8.4.2. Deadlocks8.4.3. Lock Timeouts8.4.4. Optimistic and Pessimistic Locking Strategies8.4.4.1. Pessimistic locking strategy8.4.4.2. Optimistic locking strategy8.4.4.3. Choosing between strategies
9.1. String Functions9.1.1. ASCII9.1.2. CHAR9.1.3. CHARSET9.1.4. CONCAT9.1.5. CONCAT_WS9.1.6. INSERT9.1.7. INSTR9.1.8. LCASE9.1.9. LEFT9.1.10. LENGTH9.1.11. LOAD_FILE9.1.12. LOCATE9.1.13. LPAD9.1.14. LTRIM9.1.15. REPEAT9.1.16. REPLACE9.1.17. RPAD9.1.18. RTRIM9.1.19. STRCMP9.1.20. SUBSTRING9.1.21. TRIM9.1.22. UCASE9.1.23. Other String Functions
9.2.1. ABS9.2.2. BIN9.2.3. CEILING9.2.4. CONV9.2.5. FLOOR9.2.6. FORMAT9.2.7. HEX9.2.8. LEAST9.2.9. MOD9.2.10. POWER9.2.11. RAND9.2.12. ROUND9.2.13. SIGN9.2.14. SQRT9.2.15. Other Numeric Functions
9.3.1. ADDTIME9.3.2. CONVERT_TZ9.3.3. CURRENT_DATE9.3.4. CURRENT_TIME9.3.5. CURRENT_TIMESTAMP9.3.6. DATE9.3.7. DATE_ADD9.3.8. DATE_FORMAT9.3.9. DATE_SUB9.3.10. DATEDIFF9.3.11. DAY9.3.12. DAYNAME9.3.13. DAYOFWEEK9.3.14. DAYOFYEAR9.3.15. EXTRACT9.3.16. GET_FORMAT9.3.17. MAKEDATE9.3.18. MAKETIME9.3.19. MONTHNAME9.3.20. NOW9.3.21. SEC_TO_TIME9.3.22. STR_TO_DATE9.3.23. TIME_TO_SEC9.3.24. TIMEDIFF9.3.25. TIMESTAMP9.3.26. TIMESTAMPADD9.3.27. TIMESTAMPDIFF9.3.28. WEEK9.3.29. WEEKDAY9.3.30. YEAR9.3.31. YEARWEEK9.3.32. Other Date and Time Functions
9.4.1. BENCHMARK9.4.2. COALESCE9.4.3. CURRENT_USER9.4.4. DATABASE9.4.5. GET_LOCK9.4.6. IFNULL9.4.7. INTERVAL9.4.8. IS_FREE_LOCK9.4.9. ISNULL9.4.10. NULLIF9.4.11. RELEASE_LOCK9.4.12. SESSION_USER9.4.13. SYSTEM_USER9.4.14. USER9.4.15. UUID9.4.16. VERSION
10.1. Creating Stored Functions10.1.1. The RETURN Statement10.1.2. Parameters to Stored Functions10.1.3. The DETERMINISTIC and SQL Clauses
10.4.1. Using SQL in Stored Functions
11.1. Creating Triggers11.1.1. Referring to Column Values Within the Trigger11.1.2. Triggering Actions11.1.3. BEFORE and AFTER Triggers
11.2.1. Maintaining Derived Data11.2.2. Implementing Logging11.2.3. Validating Data with Triggers
12.1. The Pros and Cons of Stored Programs in Modern Applications
12.2.1. They Enhance Database Security12.2.2. They Provide a Mechanism for Data Abstraction12.2.3. They Reduce Network Traffic12.2.4. They Allow for Common Routines Across Multiple Application Types12.2.5. They Facilitate Division of Duties12.2.6. They May Provide Portability
12.3.1. They Can Be Computationally Inferior12.3.2. They Can Lead to Logic Fragmentation12.3.3. They Do Not Provide Portability
12.4.1. Preparing a Stored Program Call for Execution12.4.2. Registering Parameters12.4.3. Setting Output Parameters12.4.4. Executing the Stored Program12.4.5. Retrieving Result Sets12.4.6. Retrieving Output Parameters12.4.7. Closing or Re-Executing the Stored Program12.4.8. Calling Stored Functions
13.1. Options for Using MySQL with PHP
13.2.1. Enabling the mysqli Extension13.2.2. Connecting to MySQL13.2.3. Checking for Errors13.2.4. Executing a Simple Non-SELECT Statement13.2.5. Retrieving a Result Set13.2.6. Managing Transactions13.2.7. Using Prepared Statements13.2.8. Retrieving Result Sets from Prepared Statements13.2.9. Getting Result Set Metadata13.2.10. Processing a Dynamic Result Set13.2.11. Calling Stored Programs with mysqli13.2.12. Handling Output Parameters13.2.13. Retrieving Multiple Result Sets
13.3.1. Connecting to MySQL13.3.2. Executing a Simple Non-SELECT Statement13.3.3. Catching Errors13.3.4. Managing Transactions13.3.5. Issuing a One-Off Query13.3.6. Using Prepared Statements13.3.7. Binding Parameters to a Prepared Statement13.3.8. Getting Result Set Metadata13.3.9. Processing a Dynamic Result Set13.3.10. Calling Stored Programs with PDO13.3.11. Binding Input Parameters to Stored Programs13.3.12. Handling Multiple Result Sets13.3.13. Handling Output Parameters13.3.14. A Complete Example
14.1. Review of JDBC Basics14.1.1. Installing the Driver and Configuring Your IDE14.1.2. Registering the Driver and Connecting to MySQL14.1.3. Issuing a Non-SELECT Statement14.1.4. Issuing a SELECT and Retrieving a Result Set14.1.5. Getting Result Set Metadata14.1.6. Using Prepared Statements14.1.7. Handling Transactions14.1.8. Handling Errors
14.2.1. Using the CallableStatement Interface14.2.2. Registering OUT Variables14.2.3. Supplying Input Parameters14.2.4. Executing the Procedure14.2.5. Retrieving a Result Set14.2.6. Retrieving Multiple Result Sets14.2.7. Dynamically Processing Result Sets14.2.8. Retrieving Output Parameter Values
14.3.1. Using Stored Programs Within Java Servlets14.3.2. Using Stored Programs from EJB
14.4.1. Hibernate Support for MySQL Stored Procedures14.4.2. Using a Stored Procedure to Load an Object14.4.3. Hibernate Queries14.4.4. Using Stored Procedures for Persistence
15.1. Review of Perl DBD::mysql Basics15.1.1. Installing DBD::mysql15.1.1.1. Installing DBD::mysql on Linux or Unix15.1.1.2. Installing DBD::mysql on Windows15.1.2. Connecting to MySQL15.1.2.1. Connection attributes15.1.3. Handling Errors15.1.4. Issuing a Simple One-off Statement15.1.5. Preparing a Statement for Reuse15.1.6. Using Bind Variables15.1.7. Issuing a Query and Retrieving Results15.1.8. There's More Than One Way To Do It15.1.8.1. fetchrow_arrayref method15.1.8.2. fetchrow_hashref method15.1.8.3. fetchall_arrayref method15.1.8.4. dump_results method15.1.8.5. bind_col and fetch methods15.1.9. Getting Result Set Metadata15.1.10. Performing Transaction Management
15.2.1. Handling Multiple Result Sets15.2.2. Handling Dynamic Result Sets15.2.3. Handling Output Variables15.2.4. A Complete Example
16.1. Installing the MySQLdb Extension
16.2.1. Creating a Connection16.2.2. Handling Exceptions16.2.3. Executing a Simple Statement16.2.4. Passing Parameters to a Statement16.2.5. Retrieving Rows from a Query16.2.6. Managing Transactions16.2.7. Getting Metadata16.2.8. Dynamically Processing a Result Set
16.3.1. Calling Simple Stored Programs16.3.2. Retrieving a Single Stored Program Result Set16.3.3. Retrieving Multiple Stored Program Result Sets16.3.4. Retrieving Dynamic Result Sets16.3.5. Obtaining Output Parameters
17.1. Review of ADO.NET Basics17.1.1. Installing the Connector/Net Driver and Configuring Your IDE17.1.2. Registering the Driver and Connecting to MySQL17.1.3. Issuing a Non-SELECT Statement17.1.4. Reusing a Statement Object17.1.5. Using Parameters17.1.6. Issuing a SELECT and Using a DataReader17.1.7. Getting DataReader Metadata17.1.8. DataSets17.1.9. Handling Errors17.1.10. Managing Transactions
17.2.1. Calling a Simple Stored Procedure17.2.2. Supplying Input Parameters17.2.3. Using a DataReader with a Stored Program17.2.4. Processing Multiple Result Sets in a DataReader17.2.5. Dynamically Processing Result Sets17.2.6. Using DataSets with Stored Programs17.2.7. Retrieving Output Parameters17.2.8. Calling Stored Functions
18.1. Permissions Required for Stored Programs18.1.1. Granting Privileges to Create a Stored Program18.1.2. Granting Privileges to Modify a Stored Program18.1.3. Granting Privileges to Execute a Stored Program
18.2.1. The SQL SECURITY Clause18.2.2. Using Definer Rights to Implement Security Policies18.2.3. Stored Program or View?18.2.4. Handling Invoker Rights Errors
18.3.1. Protecting Against SQL Injection with Stored Programs18.3.2. SQL Injection in Stored Programs
19.1. Why SQL Tuning Is So Important19.1.1. An Instructive Example
19.2.1. Parsing SQL19.2.2. Caching19.2.2.1. Buffer pool and key cache19.2.2.2. Table cache19.2.2.3. Query cache19.2.2.4. Table statistics
19.3.1. EXPLAIN Statement19.3.2. EXPLAIN and Stored Programs19.3.3. Details of the EXPLAIN Output19.3.4. Extended EXPLAIN19.3.5. Optimizer Hints19.3.6. Measuring SQL and Stored Program Execution19.3.7. The Slow Query Log
20.1. Tuning Table Access20.1.1. Index Lookup Versus Full Table Scan20.1.2. How MySQL Chooses Between Indexes20.1.3. Manually Choosing an Index20.1.4. Prefixed ("Partial") Indexes20.1.5. Concatenated Indexes20.1.5.1. Merging multiple indexes20.1.5.2. Covering indexes20.1.6. Comparing the Different Indexing Approaches20.1.7. Avoiding Accidental Table Scans20.1.7.1. Accidentally suppressing an index using a function20.1.7.2. Accidentally suppressing an index using a substring20.1.7.3. Creating concatenated indexes with a poor column order20.1.8. Optimizing Necessary Table Scans20.1.9. Using Merge or Partitioned Tables
20.2.1. How MySQL Joins Tables20.2.2. Joins Without Indexes20.2.3. Joins with Indexes20.2.4. Join Order20.2.5. A Simple Join Example
21.1. Tuning Subqueries21.1.1. Optimizing Subqueries21.1.2. Rewriting a Subquery as a Join21.1.3. Using Subqueries in Complex Joins
21.2.1. Optimizing an Anti-Join
21.3.1. Using Views
21.4.1. Creating an Index to Avoid a Sort21.4.2. Reducing Sort Overhead by Increasing Sort Memory
21.5.1. Batching Inserts21.5.2. Optimizing DML by Reducing Commit Frequency21.5.3. Triggers and DML Performance
22.1. Performance Characteristics of Stored Programs
22.4.1. Avoid Self-Joins with Procedural Logic22.4.2. Optimize Correlated Updates
22.5.1. Move Unnecessary Statements Out of a Loop22.5.2. Use LEAVE or CONTINUE to Avoid Needless Processing
22.6.1. Test for the Most Likely Conditions First22.6.2. Avoid Unnecessary Comparisons22.6.3. CASE Versus IF
23.1. The Development Process

Content preview from MySQL Stored Procedure Programming

Defining a Transaction

The default behavior of MySQL is to perform a COMMIT after the execution of each individual SQL statement, effectively turning every statement into an individual transaction. This approach is inadequate for most complex applications.

To enable transactions, allowing multiple SQL statements to be executed before a COMMIT or ROLLBACK is performed, you must take one of the following two steps:

Set the MySQL autocommit property or variable to 0. The default setting for AUTOCOMMIT is 1.
Explicitly initiate a transaction with the START TRANSACTION statement.

Since it is dangerous to assume that the MySQL environment is running with the necessary transaction setting, you should generally include either a SET AUTOCOMMIT=0 or START TRANSACTION statement in any transactional stored program.

The SET autocommit=0 statement simply ensures that MySQL will not implicitly issue a COMMIT after every SQL statement. Note, however, that if you have already initiated a transaction, issuing SET autocommit will have no effect. START TRANSACTION, on the other hand, implicitly commits any currently outstanding changes in your session, terminating the existing transaction and starting a new one.

We recommend that you leave nothing to chance when programming transactions in MySQL stored programs. Therefore, we suggest that you always explicitly commence a transaction with a START TRANSACTION statement and explicitly end your transaction with a COMMIT or ROLLBACK.