User Defined Variables vs Local Variables in MySQL

In MySQL, both user-defined and local variables are important for storing temporary data during SQL query execution. Understanding the distinctions and applications of each type is critical for effective query execution and attaining the appropriate results.

This article delves into the differences between user-defined variables and local variables in MySQL, offering insights into their use and instances in which one may be favored over the other.

User-Defined Variables

The @ sign precedes user-defined variables in MySQL, which may be set and retrieved at any time throughout a session. They are commonly used to temporarily store values and transfer them across queries.

Process:

• Set a user-defined variable with the SET command.
• Access the variable in subsequent queries in the same session.

— Setting a User-Defined Variable

SET @user_var = 100;

— Accessing the User-Defined Variable

SELECT @user_var;

Local Variables

In MySQL, local variables are declared with the DECLARE command within a stored program (such as a stored procedure or function). They have a specific scope inside the block in which they are stated.

Process:

• Declare a local variable with the DECLARE statement.
• Access the local variable from the block where it was declared.

— Declaring a Local Variable in a Stored Procedure

DELIMITER //

CREATE PROCEDURE example_procedure()

BEGIN

DECLARE local_var INT DEFAULT 50;

— Accessing the Local Variable

SELECT local_var;

END //

DELIMITER ;

— Calling the Stored Procedure

CALL example_procedure();

Syntax:

User-Defined Variables:

— Set a User-Defined Variable

SET @user_var = value;

— Access the User-Defined Variable

SELECT @user_var;

Local Variables:

— Declare and Use a Local Variable in a Stored Procedure

DELIMITER //

CREATE PROCEDURE example_procedure()

BEGIN

DECLARE local_var INT DEFAULT 50;

— Accessing the Local Variable

SELECT local_var;

END //

DELIMITER ;

Example of User-defined Variables and Local Variables in MySQL

Example 1: User-Defined Variable

-- Schema and Sample Data
CREATE TABLE sales (
    product VARCHAR(50),
    amount INT
);

INSERT INTO sales VALUES ('Laptop', 1000);
INSERT INTO sales VALUES ('Desktop', 800);
INSERT INTO sales VALUES ('Tablet', 500);

-- Setting a User-Defined Variable
SET @total_sales = 0;

-- Updating the User-Defined Variable
UPDATE sales SET amount = amount + @total_sales;

-- Displaying the Result
SELECT product, amount, @total_sales AS running_total
FROM sales;

Output:

product	amount	running_total
Laptop	1000	1000
Desktop	800	1800
Tablet	500	2300

Explanation:

• The sales table is generated with columns for product and quantity.
• Sample data is entered into the table.
• A user-defined variable, @total_sales, is set to zero.
• The amount column in the sales database is updated with the value of @total_sales.
• The outcome is displayed, including the running total.

Example 2: Local Variable in a Stored Procedure

-- Schema and Sample Data
CREATE TABLE products (
    product VARCHAR(50),
    price INT,
    stock INT,
    discounted_price DECIMAL(8,2)
);

INSERT INTO products VALUES ('Laptop', 1000, 20, NULL);
INSERT INTO products VALUES ('Desktop', 800, 15, NULL);
INSERT INTO products VALUES ('Tablet', 500, 30, NULL);

-- Declaring a Local Variable in a Stored Procedure
DELIMITER //

CREATE PROCEDURE calculate_discounted_price()
BEGIN
    DECLARE discount_percent DECIMAL(5,2) DEFAULT 10.00;

    -- Updating the Price with Discount
    UPDATE products SET discounted_price = price - (price * discount_percent / 100);

    -- Displaying the Result
    SELECT * FROM products;
END //

DELIMITER ;

-- Calling the Stored Procedure
CALL calculate_discounted_price();

Output:

product	price	stock	discounted_price
Laptop	1000	20	900.00
Desktop	800	15	720.00
Tablet	500	30	450.00

Explanation:

• The products table has four columns: product, price, stock, and discounted_price.
• Sample data is entered into the table.
• A saved procedure called calculate_discounted_price is created.
• Within the procedure, a local variable discount_percent is defined with a default value.
• The discounted_price column in the products table is updated according to the discount computation.
• The results are displayed, including the current discounted pricing.

Conclusion

Learning the differences between MySQL’s user-defined variables and local variables is critical for developing efficient and successful SQL queries. User-defined variables are used to hold temporary values during a session, allowing for data manipulation and exchange between queries. Local variables, which are declared within stored procedures or functions, provide a more constrained scope for encapsulating functionality and ensuring data integrity within a given block.