MySQL Stored Procedures

In this section, you will learn about MySQL stored procedures and how to define stored procedures for your database.

Section 1. Basic MySQL Stored procedures

  • Introduction to MySQL stored Procedures– introduce you to MySQL stored procedures, their advantages, and disadvantages.
  • Changing the default delimiter – learn how to change the default delimiter in MySQL.
  • Creating new stored procedures – show you how to create and use the CREATE PROCEDURE statement to create a new stored procedure in the database.
  • Removing stored procedures – show you how to use the DROP PROCEDURE statement to drop an existing stored procedure.
  • Variables – guide on you how to use variables to hold immediate results inside stored procedures.
  • Parameters – introduce you to various types of parameters used in stored procedures including IN, OUT, and INOUT parameter.
  • Altering stored procedure – show you step by step how to alter a stored procedure using a sequence of DROP PROCEDURE and CREATE PROCEDURE statements in MySQL Workbench.
  • Listing stored procedures – provide you with some useful commands to list stored procedures from databases.

Section 2. Conditional Statements

  • IF statement – show you how to use the IF THEN statement in stored procedures.
  • CASE statement – introduce you to the CASE statements including simple CASE and searched CASE statements.

Section 3. Loops

  • LOOP – learn how to execute a list of statements repeatedly based on a condition.
  • WHILE Loop – show you how to execute a loop as long as a condition is true.
  • REPEAT Loop – show you how to execute a loop until a search condition is true.
  • LEAVE statement – guide you on how to exit a loop immediately.

Section 4. Error Handling

  • Show Warnings – Learn how to display errors and warnings of the latest query execution.
  • Show Errors – Learn how to display errors of the latest query execution.
  • DECLARE … HANDLER – Show you how to declare exit or continue handler to handle errors within stored procedures.
  • DECLARE … CONDITION – Guide you on how to associate a name with a condition specified by a MySQL error code or an SQL state value.
  • SIGNAL – Learn how to raise an exception (error or warning) using the SIGNAL statement.
  • RESIGNAL – show you how to re-raise an exception using the RESIGNAL statement.

Section 5. Cursors

  • Cursors – learn how to use cursors to process row by row in a result set.
  • Prepared Statements – guide you on how to use prepared statements to make your queries more secure and faster to execute.

Section 6. Stored Functions

Section 7. Stored Program Security

Section 8. Transactions

  • Transactions – guide you on how to use transactions in stored procedures to ensure data integrity.