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.

Similar Reads

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

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

Example of User-defined Variables and Local Variables in MySQL

Example 1: User-Defined Variable...

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