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.
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.