Reading Time: 5 minutes

32 Views

In this article, we provide a comprehensive guide to mastering MySQL Stored Procedures. You’ll learn everything from the basics of creating stored procedures to working with cursors, variables, parameters, and conditional statements.

Whether you’re new to databases or looking to enhance your SQL skills, this complete course offers clear, step-by-step explanations and practical examples to help you optimize your database operations efficiently. Let’s dive into the world of advanced MySQL programming!

Let’s get started.

Complete Video Course Syllabus (Free)

Please find the list of course materials in a step-by-step order.

What is MySQL Stored Procedure?

A MySQL stored procedure is a set of SQL statements that are stored in the database and executed as a single unit to perform a specific task. Stored procedures are used to encapsulate repetitive or complex logic within the database to improve performance, maintainability, and security of database operations.

Key Features of MySQL Stored Procedures

Reusable: Once created, a stored procedure can be called multiple times without redefining the logic.
Encapsulation: They encapsulate business logic in the database, reducing code redundancy.
Improved Performance: Execution is faster since the procedure is precompiled.
Security: Access can be restricted to specific users.

Syntax

DELIMITER //

CREATE PROCEDURE GetAllCustomers()
BEGIN
    SELECT * FROM Customers;
END //

DELIMITER ;

MySQL Parameters

MySQL stored procedures support three types of parameters, which control how data is passed into and out of the procedure.

These parameter modes are used to define the direction of data flow:

IN Parameter

Used to pass values into the procedure. The calling program provides input when calling the procedure.

The value of an IN parameter can be used inside the procedure but cannot be modified.
It is the default parameter type if no mode is specified.

CREATE PROCEDURE GetCustomerById(IN customerId INT)
BEGIN
    SELECT * FROM Customers WHERE id = customerId;
END;

Usage

CALL GetCustomerById(1); -- Fetches the customer with ID 1

OUT Parameter

Used to return a value from the procedure to the calling program.

The value of an OUT parameter is modified inside the procedure and returned to the caller.
The caller does not pass an initial value for OUT parameters.

CREATE PROCEDURE GetCustomerCount(OUT total INT)
BEGIN
    SELECT COUNT(*) INTO total FROM Customers;
END;

Usage

CALL GetCustomerCount(@customerCount);
SELECT @customerCount; -- Retrieves the total number of customers

INOUT Parameter

Allows both input and output of data. The caller passes a value to the procedure, which can be modified inside the procedure and returned.

Combines the functionality of IN and OUT.

CREATE PROCEDURE UpdatePrice(INOUT productPrice DECIMAL(10, 2))
BEGIN
    SET productPrice = productPrice * 1.10; -- Increases the price by 10%
END;

Usage

SET @price = 100.00;
CALL UpdatePrice(@price);
SELECT @price; -- @price now holds the updated value after the procedure

That’s it.

We hope this article helped you to learn about MySQL Stored Procedure in a very detailed way.

If you liked this article, then please subscribe to our YouTube Channel for PHP & it’s framework, WordPress, Node Js video tutorials. You can also find us on Twitter and Facebook.