Log In
Or create an account ->
Imperial Library
Home
About
News
Upload
Forum
Help
Login/SignUp
Index
MySQL Stored Procedure Programming
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 Book's 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
← Prev
Back
Next →
← Prev
Back
Next →