MySQL Stored Procedure Programming

Spis treści książki

• MySQL Stored Procedure Programming
  • SPECIAL OFFER: Upgrade this ebook with OReilly
  • A Note Regarding Supplemental Files
  • Advance Praise for MySQL Stored Procedure Programming
  • Preface
    • Objectives of This Book
    • Structure of This Book
    • What This Book Does Not Cover
    • Conventions Used in This Book
    • Which Version?
    • Resources Available at the Books Web Site
    • Using Code Examples
    • Safari Enabled
    • How to Contact Us
    • Acknowledgments
  • I. Stored Programming Fundamentals
    • 1. Introduction to MySQL Stored Programs
      • 1.1. What Is a Stored Program?
        • 1.1.1. Why Use Stored Programs?
        • 1.1.2. A Brief History of MySQL
        • 1.1.3. MySQL Stored Procedures, Functions, and Triggers
      • 1.2. A Quick Tour
        • 1.2.1. Integration with SQL
        • 1.2.2. Control and Conditional Logic
        • 1.2.3. Stored Functions
        • 1.2.4. When Things Go Wrong
        • 1.2.5. Triggers
      • 1.3. Resources for Developers Using Stored Programs
        • 1.3.1. Books
        • 1.3.2. Internet Resources
      • 1.4. Some Words of Advice for Developers
        • 1.4.1. Don't Be in Such a Hurry!
        • 1.4.2. Don't Be Afraid to Ask for Help
        • 1.4.3. Take a Creative, Even Radical Approach
      • 1.5. Conclusion
    • 2. MySQL Stored Programming Tutorial
      • 2.1. What You Will Need
      • 2.2. Our First Stored Procedure
        • 2.2.1. Creating the Procedure
        • 2.2.2. Creating the Procedure Using the MySQL Query Browser
      • 2.3. Variables
      • 2.4. Parameters
        • 2.4.1. Parameter Modes
      • 2.5. Conditional Execution
      • 2.6. Loops
      • 2.7. Dealing with Errors
      • 2.8. Interacting with the Database
        • 2.8.1. SELECTing INTO Local Variables
        • 2.8.2. Using Cursors
        • 2.8.3. Returning Result Sets from Stored Procedures
        • 2.8.4. Embedding Non-SELECTs
      • 2.9. Calling Stored Programs from Stored Programs
      • 2.10. Putting It All Together
      • 2.11. Stored Functions
      • 2.12. Triggers
      • 2.13. Calling a Stored Procedure from PHP
      • 2.14. Conclusion
    • 3. Language Fundamentals
      • 3.1. Variables, Literals, Parameters, and Comments
        • 3.1.1. Variables
        • 3.1.2. Literals
        • 3.1.3. Rules for Variable Names
        • 3.1.4. Assigning Values to Variables
        • 3.1.5. Parameters
        • 3.1.6. User Variables
        • 3.1.7. Comments
      • 3.2. Operators
        • 3.2.1. Mathematical Operators
        • 3.2.2. Comparison Operators
        • 3.2.3. Logical Operators
        • 3.2.4. Bitwise Operators
      • 3.3. Expressions
      • 3.4. Built-in Functions
      • 3.5. Data Types
        • 3.5.1. String Data Types
          • 3.5.1.1. The ENUM data type
          • 3.5.1.2. The SET data type
        • 3.5.2. Numeric Data Types
        • 3.5.3. Date and Time Data Types
        • 3.5.4. TEXT and BLOB Data Types
      • 3.6. MySQL 5 "Strict" Mode
        • 3.6.1. Stored Program Behavior and Strict Mode
        • 3.6.2. Program Examples
      • 3.7. Conclusion
    • 4. Blocks, Conditional Statements, and Iterative Programming
      • 4.1. Block Structure of Stored Programs
        • 4.1.1. Structure of a Block
        • 4.1.2. Nested Blocks
      • 4.2. Conditional Control
        • 4.2.1. The IF Statement
          • 4.2.1.1. TRUE or FALSE (or neither)?
          • 4.2.1.2. Simple IF-THEN combinations
          • 4.2.1.3. IF-THEN-ELSE statements
          • 4.2.1.4. IF-THEN-ELSEIF-ELSE statements
        • 4.2.2. The CASE Statement
          • 4.2.2.1. Simple CASE statement
          • 4.2.2.2. "Searched" CASE statement
        • 4.2.3. IF Versus CASE
      • 4.3. Iterative Processing with Loops
        • 4.3.1. LOOP Statement
        • 4.3.2. LEAVE Statement
        • 4.3.3. ITERATE Statement
        • 4.3.4. REPEAT ... UNTIL Loop
        • 4.3.5. WHILE Loop
        • 4.3.6. Nested Loops
        • 4.3.7. Parting Comments on Loops
      • 4.4. Conclusion
    • 5. Using SQL in Stored Programming
      • 5.1. Using Non-SELECT SQL in Stored Programs
      • 5.2. Using SELECT Statements with an INTO Clause
      • 5.3. Creating and Using Cursors
        • 5.3.1. Defining a Cursor
        • 5.3.2. Cursor Statements
        • 5.3.3. Fetching a Single Row from a Cursor
        • 5.3.4. Fetching an Entire Result Set
        • 5.3.5. Types of Cursor Loops
        • 5.3.6. Nested Cursor Loops
        • 5.3.7. Exiting the Cursor Loop Prematurely
        • 5.3.8. Cursor Error Conditions
      • 5.4. Using Unbounded SELECT Statements
        • 5.4.1. Retrieving the Result Sets in the Calling Program
        • 5.4.2. Returning Result Sets to Another Stored Procedure
      • 5.5. Performing Dynamic SQL with Prepared Statements
      • 5.6. Handling SQL Errors: A Preview
      • 5.7. Conclusion
    • 6. Error Handling
      • 6.1. Introduction to Error Handling
        • 6.1.1. A Simple First Example
        • 6.1.2. Handling Last Row Conditions
      • 6.2. Condition Handlers
        • 6.2.1. Types of Handlers
        • 6.2.2. Handler Conditions
        • 6.2.3. Handler Examples
        • 6.2.4. Handler Precedence
        • 6.2.5. Scope of Condition Handlers
      • 6.3. Named Conditions
      • 6.4. Missing SQL:2003 Features
        • 6.4.1. Directly Accessing SQLCODE or SQLSTATE
        • 6.4.2. Creating Your Own Exceptions with the SIGNAL Statement
        • 6.4.3. Emulating the SIGNAL Statement
      • 6.5. Putting It All Together
      • 6.6. Handling Stored Program Errors in the Calling Application
        • 6.6.1. PHP
        • 6.6.2. Perl
        • 6.6.3. Java/JDBC
        • 6.6.4. Python
        • 6.6.5. C# .NET
        • 6.6.6. Visual Basic .NET
      • 6.7. Conclusion
  • II. Stored Program Construction
    • 7. Creating and Maintaining Stored Programs
      • 7.1. Creating Stored Programs
        • 7.1.1. Editing Stored Programs Using a System Editor
        • 7.1.2. Using the MySQL Query Browser
        • 7.1.3. Using Third-Party Tools
        • 7.1.4. Handling Semicolons in Stored Program Code
      • 7.2. Editing an Existing Stored Program
        • 7.2.1. Editing a Program in Place
        • 7.2.2. Maintaining Stored Programs in External Files
      • 7.3. SQL Statements for Managing Stored Programs
        • 7.3.1. CREATE PROCEDURE
        • 7.3.2. CREATE FUNCTION
        • 7.3.3. CREATE TRIGGER
        • 7.3.4. ALTER PROCEDURE/FUNCTION
        • 7.3.5. DROP PROCEDURE/FUNCTION/TRIGGER
      • 7.4. Getting Information About Stored Programs
        • 7.4.1. SHOW PROCEDURE/FUNCTION STATUS
        • 7.4.2. SHOW CREATE PROCEDURE/FUNCTION
        • 7.4.3. INFORMATION_SCHEMA.ROUTINES Table
        • 7.4.4. INFORMATION_SCHEMA.TRIGGERS Table
      • 7.5. Conclusion
    • 8. Transaction Management
      • 8.1. Transactional Support in MySQL
        • 8.1.1. Isolation Levels
        • 8.1.2. Transaction Management Statements
      • 8.2. Defining a Transaction
      • 8.3. Working with Savepoints
      • 8.4. Transactions and Locks
        • 8.4.1. Situations in Which Locks Arise
        • 8.4.2. Deadlocks
        • 8.4.3. Lock Timeouts
        • 8.4.4. Optimistic and Pessimistic Locking Strategies
          • 8.4.4.1. Pessimistic locking strategy
          • 8.4.4.2. Optimistic locking strategy
          • 8.4.4.3. Choosing between strategies
      • 8.5. Transaction Design Guidelines
      • 8.6. Conclusion
    • 9. MySQL Built-in Functions
      • 9.1. String Functions
        • 9.1.1. ASCII
        • 9.1.2. CHAR
        • 9.1.3. CHARSET
        • 9.1.4. CONCAT
        • 9.1.5. CONCAT_WS
        • 9.1.6. INSERT
        • 9.1.7. INSTR
        • 9.1.8. LCASE
        • 9.1.9. LEFT
        • 9.1.10. LENGTH
        • 9.1.11. LOAD_FILE
        • 9.1.12. LOCATE
        • 9.1.13. LPAD
        • 9.1.14. LTRIM
        • 9.1.15. REPEAT
        • 9.1.16. REPLACE
        • 9.1.17. RPAD
        • 9.1.18. RTRIM
        • 9.1.19. STRCMP
        • 9.1.20. SUBSTRING
        • 9.1.21. TRIM
        • 9.1.22. UCASE
        • 9.1.23. Other String Functions
      • 9.2. Numeric Functions
        • 9.2.1. ABS
        • 9.2.2. BIN
        • 9.2.3. CEILING
        • 9.2.4. CONV
        • 9.2.5. FLOOR
        • 9.2.6. FORMAT
        • 9.2.7. HEX
        • 9.2.8. LEAST
        • 9.2.9. MOD
        • 9.2.10. POWER
        • 9.2.11. RAND
        • 9.2.12. ROUND
        • 9.2.13. SIGN
        • 9.2.14. SQRT
        • 9.2.15. Other Numeric Functions
      • 9.3. Date and Time Functions
        • 9.3.1. ADDTIME
        • 9.3.2. CONVERT_TZ
        • 9.3.3. CURRENT_DATE
        • 9.3.4. CURRENT_TIME
        • 9.3.5. CURRENT_TIMESTAMP
        • 9.3.6. DATE
        • 9.3.7. DATE_ADD
        • 9.3.8. DATE_FORMAT
        • 9.3.9. DATE_SUB
        • 9.3.10. DATEDIFF
        • 9.3.11. DAY
        • 9.3.12. DAYNAME
        • 9.3.13. DAYOFWEEK
        • 9.3.14. DAYOFYEAR
        • 9.3.15. EXTRACT
        • 9.3.16. GET_FORMAT
        • 9.3.17. MAKEDATE
        • 9.3.18. MAKETIME
        • 9.3.19. MONTHNAME
        • 9.3.20. NOW
        • 9.3.21. SEC_TO_TIME
        • 9.3.22. STR_TO_DATE
        • 9.3.23. TIME_TO_SEC
        • 9.3.24. TIMEDIFF
        • 9.3.25. TIMESTAMP
        • 9.3.26. TIMESTAMPADD
        • 9.3.27. TIMESTAMPDIFF
        • 9.3.28. WEEK
        • 9.3.29. WEEKDAY
        • 9.3.30. YEAR
        • 9.3.31. YEARWEEK
        • 9.3.32. Other Date and Time Functions
      • 9.4. Other Functions
        • 9.4.1. BENCHMARK
        • 9.4.2. COALESCE
        • 9.4.3. CURRENT_USER
        • 9.4.4. DATABASE
        • 9.4.5. GET_LOCK
        • 9.4.6. IFNULL
        • 9.4.7. INTERVAL
        • 9.4.8. IS_FREE_LOCK
        • 9.4.9. ISNULL
        • 9.4.10. NULLIF
        • 9.4.11. RELEASE_LOCK
        • 9.4.12. SESSION_USER
        • 9.4.13. SYSTEM_USER
        • 9.4.14. USER
        • 9.4.15. UUID
        • 9.4.16. VERSION
      • 9.5. Conclusion
    • 10. Stored Functions
      • 10.1. Creating Stored Functions
        • 10.1.1. The RETURN Statement
        • 10.1.2. Parameters to Stored Functions
        • 10.1.3. The DETERMINISTIC and SQL Clauses
      • 10.2. SQL Statements in Stored Functions
      • 10.3. Calling Stored Functions
      • 10.4. Using Stored Functions in SQL
        • 10.4.1. Using SQL in Stored Functions
      • 10.5. Conclusion
    • 11. Triggers
      • 11.1. Creating Triggers
        • 11.1.1. Referring to Column Values Within the Trigger
        • 11.1.2. Triggering Actions
        • 11.1.3. BEFORE and AFTER Triggers
      • 11.2. Using Triggers
        • 11.2.1. Maintaining Derived Data
        • 11.2.2. Implementing Logging
        • 11.2.3. Validating Data with Triggers
      • 11.3. Trigger Overhead
      • 11.4. Conclusion
  • III. Using MySQL Stored Programs in Applications
    • 12. Using MySQL Stored Programs in Applications
      • 12.1. The Pros and Cons of Stored Programs in Modern Applications
      • 12.2. Advantages of Stored Programs
        • 12.2.1. They Enhance Database Security
        • 12.2.2. They Provide a Mechanism for Data Abstraction
        • 12.2.3. They Reduce Network Traffic
        • 12.2.4. They Allow for Common Routines Across Multiple Application Types
        • 12.2.5. They Facilitate Division of Duties
        • 12.2.6. They May Provide Portability
      • 12.3. Disadvantages of Stored Programs
        • 12.3.1. They Can Be Computationally Inferior
        • 12.3.2. They Can Lead to Logic Fragmentation
        • 12.3.3. They Do Not Provide Portability
      • 12.4. Calling Stored Programs from Application Code
        • 12.4.1. Preparing a Stored Program Call for Execution
        • 12.4.2. Registering Parameters
        • 12.4.3. Setting Output Parameters
        • 12.4.4. Executing the Stored Program
        • 12.4.5. Retrieving Result Sets
        • 12.4.6. Retrieving Output Parameters
        • 12.4.7. Closing or Re-Executing the Stored Program
        • 12.4.8. Calling Stored Functions
      • 12.5. Conclusion
    • 13. Using MySQL Stored Programs with PHP
      • 13.1. Options for Using MySQL with PHP
      • 13.2. Using PHP with the mysqli Extension
        • 13.2.1. Enabling the mysqli Extension
        • 13.2.2. Connecting to MySQL
        • 13.2.3. Checking for Errors
        • 13.2.4. Executing a Simple Non-SELECT Statement
        • 13.2.5. Retrieving a Result Set
        • 13.2.6. Managing Transactions
        • 13.2.7. Using Prepared Statements
        • 13.2.8. Retrieving Result Sets from Prepared Statements
        • 13.2.9. Getting Result Set Metadata
        • 13.2.10. Processing a Dynamic Result Set
        • 13.2.11. Calling Stored Programs with mysqli
        • 13.2.12. Handling Output Parameters
        • 13.2.13. Retrieving Multiple Result Sets
      • 13.3. Using MySQL with PHP Data Objects
        • 13.3.1. Connecting to MySQL
        • 13.3.2. Executing a Simple Non-SELECT Statement
        • 13.3.3. Catching Errors
        • 13.3.4. Managing Transactions
        • 13.3.5. Issuing a One-Off Query
        • 13.3.6. Using Prepared Statements
        • 13.3.7. Binding Parameters to a Prepared Statement
        • 13.3.8. Getting Result Set Metadata
        • 13.3.9. Processing a Dynamic Result Set
        • 13.3.10. Calling Stored Programs with PDO
        • 13.3.11. Binding Input Parameters to Stored Programs
        • 13.3.12. Handling Multiple Result Sets
        • 13.3.13. Handling Output Parameters
        • 13.3.14. A Complete Example
      • 13.4. Conclusion
    • 14. Using MySQL Stored Programs with Java
      • 14.1. Review of JDBC Basics
        • 14.1.1. Installing the Driver and Configuring Your IDE
        • 14.1.2. Registering the Driver and Connecting to MySQL
        • 14.1.3. Issuing a Non-SELECT Statement
        • 14.1.4. Issuing a SELECT and Retrieving a Result Set
        • 14.1.5. Getting Result Set Metadata
        • 14.1.6. Using Prepared Statements
        • 14.1.7. Handling Transactions
        • 14.1.8. Handling Errors
      • 14.2. Using Stored Programs in JDBC
        • 14.2.1. Using the CallableStatement Interface
        • 14.2.2. Registering OUT Variables
        • 14.2.3. Supplying Input Parameters
        • 14.2.4. Executing the Procedure
        • 14.2.5. Retrieving a Result Set
        • 14.2.6. Retrieving Multiple Result Sets
        • 14.2.7. Dynamically Processing Result Sets
        • 14.2.8. Retrieving Output Parameter Values
      • 14.3. Stored Programs and J2EE Applications
        • 14.3.1. Using Stored Programs Within Java Servlets
        • 14.3.2. Using Stored Programs from EJB
      • 14.4. Using Stored Procedures with Hibernate
        • 14.4.1. Hibernate Support for MySQL Stored Procedures
        • 14.4.2. Using a Stored Procedure to Load an Object
        • 14.4.3. Hibernate Queries
        • 14.4.4. Using Stored Procedures for Persistence
      • 14.5. Using Stored Procedures with Spring
      • 14.6. Conclusion
    • 15. Using MySQL Stored Programs with Perl
      • 15.1. Review of Perl DBD::mysql Basics
        • 15.1.1. Installing DBD::mysql
          • 15.1.1.1. Installing DBD::mysql on Linux or Unix
          • 15.1.1.2. Installing DBD::mysql on Windows
        • 15.1.2. Connecting to MySQL
          • 15.1.2.1. Connection attributes
        • 15.1.3. Handling Errors
        • 15.1.4. Issuing a Simple One-off Statement
        • 15.1.5. Preparing a Statement for Reuse
        • 15.1.6. Using Bind Variables
        • 15.1.7. Issuing a Query and Retrieving Results
        • 15.1.8. There's More Than One Way To Do It
          • 15.1.8.1. fetchrow_arrayref method
          • 15.1.8.2. fetchrow_hashref method
          • 15.1.8.3. fetchall_arrayref method
          • 15.1.8.4. dump_results method
          • 15.1.8.5. bind_col and fetch methods
        • 15.1.9. Getting Result Set Metadata
        • 15.1.10. Performing Transaction Management
      • 15.2. Executing Stored Programs with DBD::mysql
        • 15.2.1. Handling Multiple Result Sets
        • 15.2.2. Handling Dynamic Result Sets
        • 15.2.3. Handling Output Variables
        • 15.2.4. A Complete Example
      • 15.3. Conclusion
    • 16. Using MySQL Stored Programs with Python
      • 16.1. Installing the MySQLdb Extension
      • 16.2. MySQLdb Basics
        • 16.2.1. Creating a Connection
        • 16.2.2. Handling Exceptions
        • 16.2.3. Executing a Simple Statement
        • 16.2.4. Passing Parameters to a Statement
        • 16.2.5. Retrieving Rows from a Query
        • 16.2.6. Managing Transactions
        • 16.2.7. Getting Metadata
        • 16.2.8. Dynamically Processing a Result Set
      • 16.3. Using Stored Programs with MySQLdb
        • 16.3.1. Calling Simple Stored Programs
        • 16.3.2. Retrieving a Single Stored Program Result Set
        • 16.3.3. Retrieving Multiple Stored Program Result Sets
        • 16.3.4. Retrieving Dynamic Result Sets
        • 16.3.5. Obtaining Output Parameters
      • 16.4. A Complete Example
      • 16.5. Conclusion
    • 17. Using MySQL Stored Programs with .NET
      • 17.1. Review of ADO.NET Basics
        • 17.1.1. Installing the Connector/Net Driver and Configuring Your IDE
        • 17.1.2. Registering the Driver and Connecting to MySQL
        • 17.1.3. Issuing a Non-SELECT Statement
        • 17.1.4. Reusing a Statement Object
        • 17.1.5. Using Parameters
        • 17.1.6. Issuing a SELECT and Using a DataReader
        • 17.1.7. Getting DataReader Metadata
        • 17.1.8. DataSets
        • 17.1.9. Handling Errors
        • 17.1.10. Managing Transactions
      • 17.2. Using Stored Programs in ADO.NET
        • 17.2.1. Calling a Simple Stored Procedure
        • 17.2.2. Supplying Input Parameters
        • 17.2.3. Using a DataReader with a Stored Program
        • 17.2.4. Processing Multiple Result Sets in a DataReader
        • 17.2.5. Dynamically Processing Result Sets
        • 17.2.6. Using DataSets with Stored Programs
        • 17.2.7. Retrieving Output Parameters
        • 17.2.8. Calling Stored Functions
      • 17.3. Using Stored Programs in ASP.NET
      • 17.4. Conclusion
  • IV. Optimizing Stored Programs
    • 18. Stored Program Security
      • 18.1. Permissions Required for Stored Programs
        • 18.1.1. Granting Privileges to Create a Stored Program
        • 18.1.2. Granting Privileges to Modify a Stored Program
        • 18.1.3. Granting Privileges to Execute a Stored Program
      • 18.2. Execution Mode Options for Stored Programs
        • 18.2.1. The SQL SECURITY Clause
        • 18.2.2. Using Definer Rights to Implement Security Policies
        • 18.2.3. Stored Program or View?
        • 18.2.4. Handling Invoker Rights Errors
      • 18.3. Stored Programs and Code Injection
        • 18.3.1. Protecting Against SQL Injection with Stored Programs
        • 18.3.2. SQL Injection in Stored Programs
      • 18.4. Conclusion
    • 19. Tuning Stored Programs and Their SQL
      • 19.1. Why SQL Tuning Is So Important
        • 19.1.1. An Instructive Example
      • 19.2. How MySQL Processes SQL
        • 19.2.1. Parsing SQL
        • 19.2.2. Caching
          • 19.2.2.1. Buffer pool and key cache
          • 19.2.2.2. Table cache
          • 19.2.2.3. Query cache
          • 19.2.2.4. Table statistics
      • 19.3. SQL Tuning Statements and Practices
        • 19.3.1. EXPLAIN Statement
        • 19.3.2. EXPLAIN and Stored Programs
        • 19.3.3. Details of the EXPLAIN Output
        • 19.3.4. Extended EXPLAIN
        • 19.3.5. Optimizer Hints
        • 19.3.6. Measuring SQL and Stored Program Execution
        • 19.3.7. The Slow Query Log
      • 19.4. About the Upcoming Examples
      • 19.5. Conclusion
    • 20. Basic SQL Tuning
      • 20.1. Tuning Table Access
        • 20.1.1. Index Lookup Versus Full Table Scan
        • 20.1.2. How MySQL Chooses Between Indexes
        • 20.1.3. Manually Choosing an Index
        • 20.1.4. Prefixed ("Partial") Indexes
        • 20.1.5. Concatenated Indexes
          • 20.1.5.1. Merging multiple indexes
          • 20.1.5.2. Covering indexes
        • 20.1.6. Comparing the Different Indexing Approaches
        • 20.1.7. Avoiding Accidental Table Scans
          • 20.1.7.1. Accidentally suppressing an index using a function
          • 20.1.7.2. Accidentally suppressing an index using a substring
          • 20.1.7.3. Creating concatenated indexes with a poor column order
        • 20.1.8. Optimizing Necessary Table Scans
        • 20.1.9. Using Merge or Partitioned Tables
      • 20.2. Tuning Joins
        • 20.2.1. How MySQL Joins Tables
        • 20.2.2. Joins Without Indexes
        • 20.2.3. Joins with Indexes
        • 20.2.4. Join Order
        • 20.2.5. A Simple Join Example
      • 20.3. Conclusion
    • 21. Advanced SQL Tuning
      • 21.1. Tuning Subqueries
        • 21.1.1. Optimizing Subqueries
        • 21.1.2. Rewriting a Subquery as a Join
        • 21.1.3. Using Subqueries in Complex Joins
      • 21.2. Tuning "Anti-Joins" Using Subqueries
        • 21.2.1. Optimizing an Anti-Join
      • 21.3. Tuning Subqueries in the FROM Clause
        • 21.3.1. Using Views
      • 21.4. Tuning ORDER and GROUP BY
        • 21.4.1. Creating an Index to Avoid a Sort
        • 21.4.2. Reducing Sort Overhead by Increasing Sort Memory
      • 21.5. Tuning DML (INSERT, UPDATE, DELETE)
        • 21.5.1. Batching Inserts
        • 21.5.2. Optimizing DML by Reducing Commit Frequency
        • 21.5.3. Triggers and DML Performance
      • 21.6. Conclusion
    • 22. Optimizing Stored Program Code
      • 22.1. Performance Characteristics of Stored Programs
      • 22.2. How Fast Is the Stored Program Language?
      • 22.3. Reducing Network Traffic with Stored Programs
      • 22.4. Stored Programs as an Alternative to Expensive SQL
        • 22.4.1. Avoid Self-Joins with Procedural Logic
        • 22.4.2. Optimize Correlated Updates
      • 22.5. Optimizing Loops
        • 22.5.1. Move Unnecessary Statements Out of a Loop
        • 22.5.2. Use LEAVE or CONTINUE to Avoid Needless Processing
      • 22.6. IF and CASE Statements
        • 22.6.1. Test for the Most Likely Conditions First
        • 22.6.2. Avoid Unnecessary Comparisons
        • 22.6.3. CASE Versus IF
      • 22.7. Recursion
      • 22.8. Cursors
      • 22.9. Trigger Overhead
      • 22.10. Conclusion
    • 23. Best Practices in MySQL Stored Program Development
      • 23.1. The Development Process
        • DEV-01: Set standards and guidelines before writing any code
        • DEV-02: Ask for help after 30 minutes on a problem
        • DEV-03: Walk through each other's code
        • DEV-04: Use independent testers for functional sign-off
        • DEV-05: Use source controlled files to maintain the "reference" copy of your stored routines
      • 23.2. Coding Style and Conventions
        • STYL-01: Adopt a consistent, readable format that is easy to maintain
        • STYL-02: Adopt logical, consistent naming conventions for modules and data structures
        • STYL-03: Self-document using block and loop labels
        • STYL-04: Express complex expressions unambiguously using parentheses
        • STYL-05: Use vertical code alignment to emphasize vertical relationships
        • STYL-06: Comment tersely with value-added information
      • 23.3. Variables
        • DAT-01: Use a consistent and meaningful variable naming style
        • DAT-02: Avoid overriding variable declarations within "inner" blocks
        • DAT-03: Replace complex expressions with functions
        • DAT-04: Remove unused variables and code
        • DAT-05: Don't assume that the result of an expression is TRUE or FALSE; it could be NULL
        • DAT-06: Employ "user" variables for global data sparingly
        • DAT-07: Create stored programs in strict mode to avoid invalid data assignments
      • 23.4. Conditional Logic
        • IF-01: Use ELSEIF with mutually exclusive clauses
        • IF-02: Use IF...ELSEIF only to test a single, simple condition
        • IF-03: Make sure that a CASE statement is inclusive , or construct a handler to catch any unmatched cases
        • IF-04: Use CASE and IF consistently
      • 23.5. Loop Processing
        • LOOP-01: Make sure the loop will terminate
        • LOOP-02: Make the termination conditions of a loop obvious
        • LOOP-03: Use a single LEAVE in simple loops
        • LOOP-04: Use a simple loop to avoid redundant code required by a WHILE or REPEAT UNTIL loop
      • 23.6. Exception Handling
        • EXC-01: Handle exceptions that cannot be avoided but can be anticipated
        • EXC-02: Use named conditions to improve code readability
        • EXC-03: Be consistent in your use of SQLSTATE and MySQL error codes in exception handlers
        • EXC-04: Avoid global SQLEXCEPTION handlers until MySQL implements SIGNAL and SQLCODE features
      • 23.7. SQL in Stored Programs
        • SQL-01: Start a transaction explicitly with the START TRANSACTION statement
        • SQL-02: Don't leave transactions "dangling "
        • SQL-03: Avoid use of savepoints they can obscure program logic and reduce program efficiency
        • SQL-04: Use an appropriate locking strategy
        • SQL-05: Keep transactions small
        • SQL-06: Always reset the NOT FOUND variable after completing a cursor loop
        • SQL-07: Use SELECT FOR UPDATE when retrieving rows for later update
        • SQL-08: Avoid including SQL in functions that may be used in SQL
      • 23.8. Dynamic SQL
        • DYN-01: Bind, do not concatenate, variable values into dynamic SQL strings
        • DYN-02: Carefully validate any parameter values that might be used to construct dynamic SQL
        • DYN-03: Consider the invoker rights method for stored code that executes dynamic SQL
      • 23.9. Program Construction
        • PRG-01: Encapsulate business rules and formulas behind accurately named functions
        • PRG-02: Standardize module structure using function and procedure templates
        • PRG-03: Limit execution section sizes to a single page (50-60 lines) using modularization
        • PRG-04: Avoid side-effects in your programs
        • PRG-05: Avoid deep nesting of conditionals and loops
        • PRG-06: Limit functions to a single RETURN statement in the executable section
        • PRG-07: Use stored programs to implement code common to multiple triggers
      • 23.10. Performance
        • PER-01: Concentrate on tuning SQL to improve stored program performance
        • PER-02: Carefully create the best set of indexes for your application
        • PER-03: Avoid accidental table scans
        • PER-04: Optimize necessary table scans
        • PER-05: Avoid using stored programs for computationally expensive routines
        • PER-06: Move loop invariant expressions outside of loops
        • PER-07: Optimize conditional structures
        • PER-08: Structure IF and CASE statements so more likely expressions appear earliest in the list
      • 23.11. Conclusion
  • About the Authors
  • Colophon
  • SPECIAL OFFER: Upgrade this ebook with OReilly

pokaż cały spis treści

ukryj recenzje