Advanced Database Techniques with MySQL Stored Procedures

If you’re seeking a more efficient way to manage your database tasks, rest assured you’re not alone. But hey, you’ve landed in the perfect spot! Unleashing the power of MySQL stored procedures is the gateway to reshaping your database management.

In this tutorial, you’ll learn how to create and use MySQL stored procedures to manage and execute database operations efficiently. You’re not just addressing a problem; you’re transforming into a database hotshot.

Dive into the intricacies, experiment, and witness how your database tasks seamlessly align!

Prerequisites

Ensure you have the following in place before diving into details of using MySQL stored procedures:

• A server that supports MySQL – This tutorial uses Ubuntu 20.04 for demonstrations.

Related:Install Ubuntu Server 20.04: A Step-by-Step Walkthrough

• MySQL installed and configured on your server.

Related:How to Install MySQL on Ubuntu

Preparing a Sample Database

Stored procedures are powerful database objects that encapsulate a series of SQL statements into a reusable and manageable unit. They’re stored on a database server and can be invoked by applications or scripts.

But before you dive into the intricacies of MySQL stored procedures, start by laying the groundwork with a practical touch—your sample database. In this tutorial, you’ll create a sample database to serve as the foundation for various examples.

To prepare a sample database, follow these steps:

1. Connect to your MySQL server.

2. Once connected, run the query below to CREATE a new DATABASE called inventory (arbitrary). This database stores information about electronic products and their respective inventory.

CREATE DATABASE inventory;

3. Next, execute the following command to switch (USE) to the newly-created database (inventory).

USE inventory;

4. With the database in place, run the query below to CREATE a single TABLE named electronics (arbitrary) to hold information about electronic products.

CREATE TABLE electronics (
    brand varchar(100),
    type varchar(100),
    release_year int,
    price decimal(10, 2)
);

5. Lastly, run the queries below to INSERT sample data INTO your electronics table.

INSERT INTO electronics
VALUES
('Sony', 'PlayStation 5', 2020, 499.99),
('Sony', 'Bravia OLED', 2021, 1899.99),
('Apple', 'iPhone 12', 2020, 799.00),
('Apple', 'iPad Pro', 2021, 999.00),
('Samsung', 'Galaxy S21', 2021, 699.99),
('Samsung', 'Galaxy Tab S7', 2020, 649.99),
('LG', 'Gram Laptop', 2021, 1199.99),
('LG', 'UltraGear Monitor', 2020, 399.99),
('Dell', 'XPS 13', 2020, 999.99),
('Dell', 'Alienware M15', 2021, 1499.99);

Creating a Basic Form of MySQL Stored Procedures

Now that your sample database is ready and waiting for action, seamlessly shift gears into the heart of MySQL magic—creating MySQL stored procedures.

Stored procedures offer numerous advantages in database management and application development. One example is streamlining complex database tasks into efficient, manageable components.

To create a MySQL stored procedure, carry out the following:

1. Execute the following query to define a basic form of a stored procedure named get_all_electronics (arbitrary).

This procedure has no parameters (you’ll learn about parameters later) and retrieves all records from the electronics table. The records received are in ascending order by brand and descending (DESC) order by price.

-- Set a custom delimiter to avoid conflicts in the stored procedure definition
DELIMITER //

-- Create a stored procedure named get_all_electronics
CREATE PROCEDURE get_all_electronics()
BEGIN
    -- Select all columns from the 'electronics' table
    -- Order the result by 'brand' in ascending order and 'price' in descending order
    SELECT * FROM electronics ORDER BY brand, price DESC;
END //

-- Reset the delimiter to the default value
DELIMITER ;

💡 Since stored procedures are precompiled, they offer better performance than regular SQL queries that need to be parsed every time they’re executed.

2. Next, run the following command to execute (call) your stored procedure (get_all_electronics()).

CALL get_all_electronics();

💡 A stored procedure is a piece of code you write once and can repeatedly invoke as needed. This functionality is a time-saving gem, sparing you the effort of rewriting intricate queries every time you require their execution.

If successful, you’ll see a result set similar to the one below. The records are ordered by brand in ascending order and descending order by price, as specified in the stored procedure.

Congratulations! You’ve successfully created and executed your first MySQL stored procedure. You’re no longer restricted to writing repetitive code for the same database operations. Instead, you can call your stored procedure whenever needed to perform the task for you.

Leveraging the IN and OUT Parameters in MySQL Stored Procedures

You’ve created your first basic MySQL stored procedure, which works like a charm. But in many scenarios, you’ll need to pass parameters to your procedures, especially when dealing with more intricate database operations.

A MySQL stored procedure can have different types of parameters, including the following:

To leverage the IN and OUT parameters, proceed with the following:

1. Run the below queries to define a MySQL stored procedure named get_electronics_by_year, which performs the following:

• Takes an input parameter (release_year_filter) as an integer number (int) for the release year.
• Retrieves electronic products from the electronics table that match the specified release year.
• Sorts the result by brand in ascending order (a-z) and price in descending (DESC)order.

-- Set a custom delimiter to avoid conflicts in the stored procedure definition
DELIMITER //

-- Create a stored procedure named get_electronics_by_year
-- This procedure takes an IN parameter named release_year_filter of type int
CREATE PROCEDURE get_electronics_by_year(IN release_year_filter int)
BEGIN
    -- Select all columns from the 'electronics' table
    -- Filter the result to include only rows where the 'release_year' matches the input parameter
    -- Order the result by 'brand' in ascending order and 'price' in descending order
    SELECT * FROM electronics WHERE release_year = release_year_filter ORDER BY brand, price DESC;
END //

-- Reset the delimiter to the default value
DELIMITER ;

2. Next, CALL the get_electronics_by_year stored procedure by passing a release_year_filter value (i.e., 2021) to filter the results by the specified release year.

CALL get_electronics_by_year(2021);

The result set displays all electronic products released in 2021, as shown below.

Notice that the result set is ordered by brand in ascending order(a-z) and by price in descending order as described in the procedure.

If you don’t pass the required parameters (i.e., release_year_filter), you’ll encounter an error like the one below. This behavior showcases the security advantage of using stored procedures since they only allow specific parameters to be passed in.

💡 Stored procedures help prevent SQL injection attacks by only allowing specific parameters to be passed into the procedure.

3. Run the below queries to define a MySQL stored procedure named get_electronics_stats_by_year.

This stored procedure takes an IN parameter (release_year_filter) and returns several statistics related to electronic products released in the specified year as an OUT parameter.

Notice that you can combine multiple parameter types in a single stored procedure. This feature offers greater flexibility and functionality when designing and using the procedure.

DELIMITER //

-- This stored procedure retrieves statistics for electronic products released in a specific year.
CREATE PROCEDURE get_electronics_stats_by_year(
    IN release_year_filter INT,    -- Input parameter: The year to filter electronic products.
    OUT electronics_count INT,     -- Output parameter: Count of electronic products.
    OUT min_price DECIMAL(10, 2),  -- Output parameter: Minimum price.
    OUT avg_price DECIMAL(10, 2),  -- Output parameter: Average price.
    OUT max_price DECIMAL(10, 2)   -- Output parameter: Maximum price.
)
BEGIN
    -- Calculate statistics and store them in output parameters.
    SELECT COUNT(*), MIN(price), AVG(price), MAX(price)
    INTO electronics_count, min_price, avg_price, max_price
    FROM electronics
    WHERE release_year = release_year_filter;
END //

DELIMITER ;

4. Now, CALL the get_electronics_stats_by_year stored procedure to perform the following:

• Pass a release_year_filter value (i.e., 2021) to filter the results by the specified release year
• Define the user-defined variables (@count, @min, @avg, and @max) to retrieve the parameter values returned by the procedure.

CALL get_electronics_stats_by_year(2021, @count, @min, @avg, @max);

As you can see in the output below, the result set is not displayed in the terminal, which is expected since this procedure does not return data directly.

Instead, the procedure stores the calculated statistics in the user-defined variables.

5. Lastly, run the following query to SELECT and view the values stored in the user-defined variables (@count, @min, @avg, @max).

SELECT @count, @min, @avg, @max;

You’ll see the values of the user-defined variables calculated by the procedure, as shown below. This output confirms the stored procedure works as expected.

Harnessing the INOUT Parameter in MySQL Stored Procedures

In most cases, the IN and OUT parameters parameters are enough. But when you require the ability to receive and return values seamlessly, the INOUT parameter will do the trick.

An INOUT parameter combines the characteristics of both IN and OUT parameters. You can pass a value into the procedure, and the procedure can modify and return the updated value.

To illustrate the functionality of the INOUT parameter in MySQL stored procedures, complete the steps below:

1. Execute the following query to ADD an id column to the electronics TABLE to identify each item uniquely.

ALTER TABLE electronics ADD id INT AUTO_INCREMENT PRIMARY KEY;

2. Next, CALL the get_all_electronics stored procedure to retrieve the data in the electronics table.

CALL get_all_electronics();

Since an id column has been added to the table, note a specific item’s ID (i.e., id 1 for Sony PlayStation 5) and its price (i.e., 499.99).

This information will verify the price update performed by the apply_discount_to_price stored procedure in the following step.

3. Run the below query to define a MySQL stored procedure named apply_discount_to_price

This stored procedure applies a discount to the price of an electronic item based on the provided item ID and discount percentage.

The new price is calculated by taking the old price and subtracting the discount percentage. The updated price is then stored in the new_price INOUT parameter, which will be used to update the electronic item’s record in the table.

CREATE PROCEDURE apply_discount_to_price(
    IN item_id INT,
    IN discount_percentage DECIMAL(5,2),
    OUT old_price DECIMAL(10,2),
    INOUT new_price DECIMAL(10,2)
)
BEGIN
    -- Select the current price of the electronic item
    SELECT price INTO old_price FROM electronics WHERE id = item_id;
    
    -- Calculate the new price by applying the discount
    SET new_price = old_price - (old_price * (discount_percentage / 100));
    
    -- Update the electronics table with the new price
    UPDATE electronics SET price = new_price WHERE id = item_id;
END //

DELIMITER ;

4. Now, run each query below to SET a discount percentage, the ID of the item whose price you want to update, and a variable to hold the new price.

Ensure you replace the @item_id value with the item ID you noted earlier in step two and a discount percentage of your choice (i.e., 10.0, which is 10%).

-- Example discount percentage
SET @discount_rate = 10.0; 
-- The ID of the item to update
SET @item_id = 1; 
-- Variable to store the new price after applying the discount
SET @new_price = 0; 

5. CALL the apply_discount_to_price stored procedure, passing the @item_id and @discount_rate as parameters.

CALL apply_discount_to_price(@item_id, @discount_rate, @old_price, @new_price);

6. SELECT and view the data stored in the @old_price and @new_price parameters.

SELECT @old_price, @new_price;

Assuming the apply_discount_to_price stored procedure works, you’ll see the value of the old and discounted new prices, as shown below.

7. Lastly, CALL the get_all_electronics() stored procedure to confirm the changes in data.

CALL get_all_electronics();

CALL get_all_electronics();

If everything was successful, you’ll now see the target item’s updated (discounted) price.

Conclusion

Throughout this tutorial, you journeyed to unravel the power of MySQL stored procedures. You started by preparing a sample database, creating a basic form of MySQL stored procedures, and leveraging and harnessing parameters.

Now, take this newfound expertise and dive into practical applications. Why not explore dynamic use cases like conditional logic, loops, and error handling—gracefully anticipating and managing issues? Or consider diving into triggers, allowing your database to respond to events automatically?

Venture into these areas to broaden your skill set and uncover the depth and versatility of MySQL stored procedures!