MySQL Stored Procedure Programming

Name: MySQL Stored Procedure Programming | Building High-Performance Web Applications in MySQL
Brand: O'Reilly
Price: 37.49 EUR
Availability: OnlineOnly

Building High-Performance Web Applications in MySQL

Guy Harrison(Author)

O'Reilly (Publisher)

1st Edition

Published on 28. March 2006

636 pages

E-Book

PDF with Adobe-DRM

System requirements

978-0-596-51916-2 (ISBN)

€37.49incl. 7% vat

System requirements

for PDF with Adobe-DRM

E-Book Single Licence

Available for download

Description

Alles über E-Books | Antworten auf Fragen rund um E-Books, Kopierschutz und Dateiformate finden Sie in unserem Info- & Hilfebereich.

Alles über E-Books, Kopierschutz & Dateiformate finden Sie in unserem Info- & Hilfebereich.

The implementation of stored procedures in MySQL 5.0 a hugemilestone -- one that is expected to lead to widespread enterprise adoption ofthe already extremely popular MySQL database. If you are serious aboutbuilding the web-based database applications of the future, you need toget up to speed quickly on how stored procedures work -- and how tobuild them the right way. This book, destined to be the bible of storedprocedure development, is a resource that no real MySQL programmer canafford to do without.In the decade since MySQL burst on the scene, it has become thedominant open source database, with capabilities and performancerivaling those of commercial RDBMS offerings like Oracle and SQLServer. Along with Linux and PHP, MySQL is at the heart of millions ofapplications. And now, with support for stored procedures, functions,and triggers in MySQL 5.0, MySQL offers the programming power neededfor true enterprise use.MySQL's new procedural language has a straightforward syntax, making iteasy to write simple programs. But it's not so easy to write secure,easily maintained, high-performance, and bug-free programs. Few in theMySQL world have substantial experience yet with stored procedures, butGuy Harrison and Steven Feuerstein have decades of combined expertise.In MySQL Stored Procedure Programming, they putthat hard-won experience to good use. Packed with code examples and coveringeverything from language basics to application building to advancedtuning and best practices, this highly readable book is the one-stopguide to MySQL development. It consists of four major sections:MySQL stored programming fundamentals -- tutorial, basicstatements, SQL in stored programs, and error handlingBuilding MySQL stored programs -- transaction handling,built-in functions, stored functions, and triggersMySQL stored programs in applications -- using storedprograms with PHP, Java, Perl, Python, and .NET (C# and VB.NET)Optimizing MySQL stored programs -- security, basic andadvanced SQL tuning, optimizing stored program code, and programmingbest practicesA companion web site contains many thousands of lines of code, that youcan put to use immediately.Guy Harrison is Chief Architect of Database Solutions at Quest Softwareand a frequent speaker and writer on MySQL topics. Steven Feuerstein isthe author of Oracle PL/SQL Programming, the classic reference for Oracle stored programming for more than ten years. Both have decades of experience as database developers, and between them they have authored a dozen books.

More details

Other editions

Content

Intro
Table of Contents
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 Book's Web Site
Using Code Examples
Safari® Enabled
How to Contact Us
Acknowledgments
Part I
Introduction to MySQL Stored Programs
What Is a Stored Program?
Why Use Stored Programs?
A Brief History of MySQL
MySQL Stored Procedures, Functions, and Triggers
A Quick Tour
Integration with SQL
Control and Conditional Logic
Stored Functions
When Things Go Wrong
Triggers
Resources for Developers Using Stored Programs
Books
Internet Resources
Some Words of Advice for Developers
Don't Be in Such a Hurry!
Don't Be Afraid to Ask for Help
Take a Creative, Even Radical Approach
Conclusion
MySQL Stored Programming Tutorial
What You Will Need
Our First Stored Procedure
Creating the Procedure
Creating the Procedure Using the MySQL Query Browser
Variables
Parameters
Parameter Modes
Conditional Execution
Loops
Dealing with Errors
Interacting with the Database
SELECTing INTO Local Variables
Using Cursors
Returning Result Sets from Stored Procedures
Embedding Non-SELECTs
Calling Stored Programs from Stored Programs
Putting It All Together
Stored Functions
Triggers
Calling a Stored Procedure from PHP
Conclusion
Language Fundamentals
Variables, Literals, Parameters, and Comments
Variables
Literals
Rules for Variable Names
Assigning Values to Variables
Parameters
User Variables
Comments
Operators
Mathematical Operators
Comparison Operators
Logical Operators
Bitwise Operators
Expressions
Built-in Functions
Data Types
String Data Types
The ENUM data type
The SET data type
Numeric Data Types
Date and Time Data Types
TEXT and BLOB Data Types
MySQL 5 "Strict" Mode
Stored Program Behavior and Strict Mode
Program Examples
Conclusion
Blocks, Conditional Statements, and Iterative Programming
Block Structure of Stored Programs
Structure of a Block
Nested Blocks
Conditional Control
The IF Statement
TRUE or FALSE (or neither)?
Simple IF-THEN combinations
IF-THEN-ELSE statements
IF-THEN-ELSEIF-ELSE statements
The CASE Statement
Simple CASE statement
"Searched" CASE statement
IF Versus CASE
Iterative Processing with Loops
LOOP Statement
LEAVE Statement
ITERATE Statement
REPEAT . UNTIL Loop
WHILE Loop
Nested Loops
Parting Comments on Loops
Conclusion
Using SQL in Stored Programming
Using Non-SELECT SQL in Stored Programs
Using SELECT Statements with an INTO Clause
Creating and Using Cursors
Defining a Cursor
Cursor Statements
Fetching a Single Row from a Cursor
Fetching an Entire Result Set
Types of Cursor Loops
Nested Cursor Loops
Exiting the Cursor Loop Prematurely
Cursor Error Conditions
Using Unbounded SELECT Statements
Retrieving the Result Sets in the Calling Program
Returning Result Sets to Another Stored Procedure
Performing Dynamic SQL with Prepared Statements
Handling SQL Errors: A Preview
Conclusion
Error Handling
Introduction to Error Handling
A Simple First Example
Handling Last Row Conditions
Condition Handlers
Types of Handlers
Handler Conditions
Handler Examples
Handler Precedence
Scope of Condition Handlers
Named Conditions
Missing SQL:2003 Features
Directly Accessing SQLCODE or SQLSTATE
Creating Your Own Exceptions with the SIGNAL Statement
Emulating the SIGNAL Statement
Putting It All Together
Handling Stored Program Errors in the Calling Application
PHP
Perl
Java/JDBC
Python
C# .NET
Visual Basic .NET
Conclusion
Part II
Creating and Maintaining Stored Programs
Creating Stored Programs
Editing Stored Programs Using a System Editor
Using the MySQL Query Browser
Using Third-Party Tools
Handling Semicolons in Stored Program Code
Editing an Existing Stored Program
Editing a Program in Place
Maintaining Stored Programs in External Files
SQL Statements for Managing Stored Programs
CREATE PROCEDURE
CREATE FUNCTION
CREATE TRIGGER
ALTER PROCEDURE/FUNCTION
DROP PROCEDURE/FUNCTION/TRIGGER
Getting Information About Stored Programs
SHOW PROCEDURE/FUNCTION STATUS
SHOW CREATE PROCEDURE/FUNCTION
INFORMATION_SCHEMA.ROUTINES Table
INFORMATION_SCHEMA.TRIGGERS Table
Conclusion
Transaction Management
Transactional Support in MySQL
Isolation Levels
Transaction Management Statements
Defining a Transaction
Working with Savepoints
Transactions and Locks
Situations in Which Locks Arise
Deadlocks
Lock Timeouts
Optimistic and Pessimistic Locking Strategies
Pessimistic locking strategy
Optimistic locking strategy
Choosing between strategies
Transaction Design Guidelines
Conclusion
MySQL Built-in Functions
String Functions
ASCII
CHAR
CHARSET
CONCAT
CONCAT_WS
INSERT
INSTR
LCASE
LEFT
LENGTH
LOAD_FILE
LOCATE
LPAD
LTRIM
REPEAT
REPLACE
RPAD
RTRIM
STRCMP
SUBSTRING
TRIM
UCASE
Other String Functions
Numeric Functions
ABS
BIN
CEILING
CONV
FLOOR
FORMAT
HEX
LEAST
MOD
POWER
RAND
ROUND
SIGN
SQRT
Other Numeric Functions
Date and Time Functions
ADDTIME
CONVERT_TZ
CURRENT_DATE
CURRENT_TIME
CURRENT_TIMESTAMP
DATE
DATE_ADD
DATE_FORMAT
DATE_SUB
DATEDIFF
DAY
DAYNAME
DAYOFWEEK
DAYOFYEAR
EXTRACT
GET_FORMAT
MAKEDATE
MAKETIME
MONTHNAME
NOW
SEC_TO_TIME
STR_TO_DATE
TIME_TO_SEC
TIMEDIFF
TIMESTAMP
TIMESTAMPADD
TIMESTAMPDIFF
WEEK
WEEKDAY
YEAR
YEARWEEK
Other Date and Time Functions
Other Functions
BENCHMARK
COALESCE
CURRENT_USER
DATABASE
GET_LOCK
IFNULL
INTERVAL
IS_FREE_LOCK
ISNULL
NULLIF
RELEASE_LOCK
SESSION_USER
SYSTEM_USER
USER
UUID
VERSION
Conclusion
Stored Functions
Creating Stored Functions
The RETURN Statement
Parameters to Stored Functions
The DETERMINISTIC and SQL Clauses
SQL Statements in Stored Functions
Calling Stored Functions
Using Stored Functions in SQL
Using SQL in Stored Functions
Conclusion
Triggers
Creating Triggers
Referring to Column Values Within the Trigger
Triggering Actions
BEFORE and AFTER Triggers
Using Triggers
Maintaining Derived Data
Implementing Logging
Validating Data with Triggers
Trigger Overhead
Conclusion
Part III
Using MySQL Stored Programs in Applications
The Pros and Cons of Stored Programs in Modern Applications
Advantages of Stored Programs
They Enhance Database Security
They Provide a Mechanism for Data Abstraction
They Reduce Network Traffic
They Allow for Common Routines Across Multiple Application Types
They Facilitate Division of Duties
They May Provide Portability
Disadvantages of Stored Programs
They Can Be Computationally Inferior
They Can Lead to Logic Fragmentation
They Do Not Provide Portability
Calling Stored Programs from Application Code
Preparing a Stored Program Call for Execution
Registering Parameters
Setting Output Parameters
Executing the Stored Program
Retrieving Result Sets
Retrieving Output Parameters
Closing or Re-Executing the Stored Program
Calling Stored Functions
Conclusion
Using MySQL Stored Programs with PHP
Options for Using MySQL with PHP
Using PHP with the mysqli Extension
Enabling the mysqli Extension
Connecting to MySQL
Checking for Errors
Executing a Simple Non-SELECT Statement
Retrieving a Result Set
Managing Transactions
Using Prepared Statements
Retrieving Result Sets from Prepared Statements
Getting Result Set Metadata
Processing a Dynamic Result Set
Calling Stored Programs with mysqli
Handling Output Parameters
Retrieving Multiple Result Sets
Using MySQL with PHP Data Objects
Connecting to MySQL
Executing a Simple Non-SELECT Statement
Catching Errors
Managing Transactions
Issuing a One-Off Query
Using Prepared Statements
Binding Parameters to a Prepared Statement
Getting Result Set Metadata
Processing a Dynamic Result Set
Calling Stored Programs with PDO
Binding Input Parameters to Stored Programs
Handling Multiple Result Sets
Handling Output Parameters
A Complete Example
Conclusion
Using MySQL Stored Programs with Java
Review of JDBC Basics
Installing the Driver and Configuring Your IDE
Registering the Driver and Connecting to MySQL
Issuing a Non-SELECT Statement
Issuing a SELECT and Retrieving a Result Set
Getting Result Set Metadata
Using Prepared Statements
Handling Transactions
Handling Errors
Using Stored Programs in JDBC
Using the CallableStatement Interface
Registering OUT Variables
Supplying Input Parameters
Executing the Procedure
Retrieving a Result Set
Retrieving Multiple Result Sets
Dynamically Processing Result Sets
Retrieving Output Parameter Values
Stored Programs and J2EE Applications
Using Stored Programs Within Java Servlets
Using Stored Programs from EJB
Using Stored Procedures with Hibernate
Hibernate Support for MySQL Stored Procedures
Using a Stored Procedure to Load an Object
Hibernate Queries
Using Stored Procedures for Persistence
Using Stored Procedures with Spring
Conclusion
Using MySQL Stored Programs with Perl
Review of Perl DBD::mysql Basics
Installing DBD::mysql
Installing DBD::mysql on Linux or Unix
Installing DBD::mysql on Windows
Connecting to MySQL
Connection attributes
Handling Errors
Issuing a Simple One-off Statement
Preparing a Statement for Reuse
Using Bind Variables
Issuing a Query and Retrieving Results
There's More Than One Way To Do It
fetchrow_arrayref method
fetchrow_hashref method
fetchall_arrayref method
dump_results method
bind_col and fetch methods
Getting Result Set Metadata
Performing Transaction Management
Executing Stored Programs with DBD::mysql
Handling Multiple Result Sets
Handling Dynamic Result Sets
Handling Output Variables
A Complete Example
Conclusion
Using MySQL Stored Programs with Python
Installing the MySQLdb Extension
MySQLdb Basics
Creating a Connection
Handling Exceptions
Executing a Simple Statement
Passing Parameters to a Statement
Retrieving Rows from a Query
Managing Transactions
Getting Metadata
Dynamically Processing a Result Set
Using Stored Programs with MySQLdb
Calling Simple Stored Programs
Retrieving a Single Stored Program Result Set
Retrieving Multiple Stored Program Result Sets
Retrieving Dynamic Result Sets
Obtaining Output Parameters
A Complete Example
Conclusion
Using MySQL Stored Programs with .NET
Review of ADO.NET Basics
Installing the Connector/Net Driver and Configuring Your IDE
Registering the Driver and Connecting to MySQL
Issuing a Non-SELECT Statement
Reusing a Statement Object
Using Parameters
Issuing a SELECT and Using a DataReader
Getting DataReader Metadata
DataSets
Handling Errors
Managing Transactions
Using Stored Programs in ADO.NET
Calling a Simple Stored Procedure
Supplying Input Parameters
Using a DataReader with a Stored Program
Processing Multiple Result Sets in a DataReader
Dynamically Processing Result Sets
Using DataSets with Stored Programs
Retrieving Output Parameters
Calling Stored Functions
Using Stored Programs in ASP.NET
Conclusion
Part IV
Stored Program Security
Permissions Required for Stored Programs
Granting Privileges to Create a Stored Program
Granting Privileges to Modify a Stored Program
Granting Privileges to Execute a Stored Program
Execution Mode Options for Stored Programs
The SQL SECURITY Clause
Using Definer Rights to Implement Security Policies
Stored Program or View?
Handling Invoker Rights Errors
Stored Programs and Code Injection
Protecting Against SQL Injection with Stored Programs
SQL Injection in Stored Programs
Conclusion
Tuning Stored Programs and Their SQL
Why SQL Tuning Is So Important
An Instructive Example
How MySQL Processes SQL
Parsing SQL
Caching
Buffer pool and key cache
Table cache
Query cache
Table statistics
SQL Tuning Statements and Practices
EXPLAIN Statement
EXPLAIN and Stored Programs
Details of the EXPLAIN Output
Extended EXPLAIN
Optimizer Hints
Measuring SQL and Stored Program Execution
The Slow Query Log
About the Upcoming Examples
Conclusion
Basic SQL Tuning
Tuning Table Access
Index Lookup Versus Full Table Scan
How MySQL Chooses Between Indexes
Manually Choosing an Index
Prefixed ("Partial") Indexes
Concatenated Indexes
Merging multiple indexes
Covering indexes
Comparing the Different Indexing Approaches
Avoiding Accidental Table Scans
Accidentally suppressing an index using a function
Accidentally suppressing an index using a substring
Creating concatenated indexes with a poor column order
Optimizing Necessary Table Scans
Using Merge or Partitioned Tables
Tuning Joins
How MySQL Joins Tables
Joins Without Indexes
Joins with Indexes
Join Order
A Simple Join Example
Conclusion
Advanced SQL Tuning
Tuning Subqueries
Optimizing Subqueries
Rewriting a Subquery as a Join
Using Subqueries in Complex Joins
Tuning "Anti-Joins" Using Subqueries
Optimizing an Anti-Join
Tuning Subqueries in the FROM Clause
Using Views
Tuning ORDER and GROUP BY
Creating an Index to Avoid a Sort
Reducing Sort Overhead by Increasing Sort Memory
Tuning DML (INSERT, UPDATE, DELETE)
Batching Inserts
Optimizing DML by Reducing Commit Frequency
Triggers and DML Performance
Conclusion
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
Avoid Self-Joins with Procedural Logic
Optimize Correlated Updates
Optimizing Loops
Move Unnecessary Statements Out of a Loop
Use LEAVE or CONTINUE to Avoid Needless Processing
IF and CASE Statements
Test for the Most Likely Conditions First
Avoid Unnecessary Comparisons
CASE Versus IF
Recursion
Cursors
Trigger Overhead
Conclusion
Best Practices in MySQL Stored Program Development
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
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
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
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 unmatche...
IF-04: Use CASE and IF consistently
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
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
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
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
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
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
Conclusion
Index

System requirements

Save as PDF Copy link into clipboard

Schweitzer Fachinformationen

MySQL Stored Procedure Programming

Description

More details

Other editions

Additional editions

Content

System requirements