MySQL Stored Procedures

 MySQL Stored Procedures

In this section, you will learn how to develop stored programs including stored procedures and stored functions in MySQL.

Section 1. Stored procedure basics

• Introduction to Stored Procedures in MySQL  – introduce you to stored procedures, their advantages, and disadvantages.
• Changing the default delimiter in MySQL – learn how to change the default delimiter in MySQL.
• Creating new stored procedures – show you how to create 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 – a 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

• Handing exceptions – show you how to handle exceptions and errors in stored procedures.
• Raising errors  – learn how to use SIGNAL and RESIGNAL to raise errors in stored procedures.

Section 5. Cursors

• Cursors – learn how to use cursors to process row by row in a result set.

Section 6. Stored Functions

• Creating a stored function – show you how to use the create stored functions in the database.
• Removing a stored function – use the DROP FUNCTION statement to remove a stored function.
• Listing stored functions – learn how to list all stored functions in the database by using  SHOW FUNCTION STATUS or querying from the data dictionary.

Section 7. Stored Program Security

• Stored object access control – learn how to control the security of the stored objects.