MySQL Advanced Stored Procedures

In the MySQL Advanced Stored Procedures course learn to improve the quality of Stored Procedures, Function and Triggers in your applications as well as how to debug and optimize them! Through a hands-on approach, this instructor-led course with focus on labs is designed to teach you how to maximize the use of stored procedures along with the knowledge to discern when an application should contain stored procedures and when they should not.

Students that can benefit from this course:

Database developers and administrators that desire to improve the quality of their applications by creating complex logic within the database itself using stored procedures.

Audience
Course Topics Introduction

• Structure of MySQL AB
• MySQL Products and Services
• MySQL Partners
• Supported Operating Services
• MySQL Certification Program
• Training Curriculum Paths
• MySQL Website

MySQL Stored Procedures Foundation

• Compound Statements
• Assigning Variables
• Parameter Declarations
• MySQL Supported Operators and Built-in Functions in Stored Routines
• Obtain Information on Stored Routines
• Alteration and deletion of Stored Routines
• Backing Up/Recovery Stored Routines
• Binary Logging of Stored Routines

SQL and MySQL Stored Procedures

• Flow Control Statements
• SQL in Stored Routines
• Dynamic SQL

Handling Exceptions and Errors

• Error Handling Capabilities and Limitations
• Implementing Error Handlers
• Other Handler Issues
• Error Handling Limitations

Managing Triggers

• Creating Triggers
• Obtain Information on Triggers
• Delete Triggers
• Trigger Limitations

Data Security

• Set Permissions
• View or Stored Routine?
• Invoker Rights Error
• Preventing Code Injection

Best Practices

• Fundamental Programming Best Practices
• Coding Style and Conventions
• Variable Use Best Practices
• Flow Control Best Practices
• Dynamic SQL Best Practices

Conclusion

• Course Overview
• Training and Certification Website
• Course Evaluation
• Thank You!
• QA Session

Course Objectives

• Improve upon the design and structure of your compound stored procedure statements
• Improve upon your applications by utilizing the various data items allowed in MySQL stored procedures
• Improve upon cursor creation for processing individual rows of data by learning the best practices associated with them
• Improve upon the integration of SQL statements in MySQL stored procedures by taking advantage of the continuity built-in to the two entities
• Utilize the best feature of dynamic SQL to include the prevention of code injection
• Improve your application by effectively utilizing the exceptions and errors capabilities of MySQL stored procedures
• Improve upon the management of MySQL stored procedures by utilizing best practices in their creation, modification and deletion
• Improve your application by integrating the features of database triggers that can improve data integrity and automate database processes
• Utilize best practices to ensure data security with stored procedures
• Develop personal best practices in relation to the creation and maintenance of stored procedures
• Circumvent MySQL stored procedure limitations
• Improve the performance of your applications by optimizing stored procedures