MySQL Variables;
The main purpose of the variable is to store data in memory and can be used throughout the program.
MySQL can use variables in three different ways, which are given below:
User-Defined Variable
Local Variable
System Variable
MySQL Variables- User-Defined Variable
Sometimes, we want to pass values from one statement to another statement. The user-defined variable enables us to store a value in one statement and later can refer it to another statement.
By using the SET statement
SET @var_name = value;
NOTE: We can use either '=' or ':=' assignment operator with the SET statement.
2. By using the SELECT statement
SELECT @var_name := value;
SELECT @maxage:= MAX(age) FROM customers;
---------------------
Enter password: *******
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 51
Server version: 8.0.25 MySQL Community Server - GPL
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> use classicmodels;
Database changed
mysql> set @age=88;
Query OK, 0 rows affected (0.00 sec)
mysql> select @age;
+------+
| @age |
+------+
| 88 |
+------+
1 row in set (0.00 sec)
mysql> set @age1 := 66;
Query OK, 0 rows affected (0.00 sec)
mysql> select @age1;
+-------+
| @age1 |
+-------+
| 66 |
+-------+
1 row in set (0.00 sec)
mysql> select @age2=55;
+----------+
| @age2=55 |
+----------+
| NULL |
+----------+
1 row in set (0.00 sec)
mysql> select @age2;
+--------------+
| @age2 |
+--------------+
| NULL |
+--------------+
1 row in set (0.00 sec)
mysql> select @sal := 30000;
+---------------+
| @sal := 30000 |
+---------------+
| 30000 |
+---------------+
1 row in set, 1 warning (0.00 sec)
mysql> select @sal;
+-------+
| @sal |
+-------+
| 30000 |
+-------+
1 row in set (0.00 sec)
mysql> select * from customers limit 2 \G;
*************************** 1. row ***************************
customerNumber: 103
customerName: Atelier graphique
contactLastName: Schmitt
contactFirstName: Carine
phone: 40.32.2555
addressLine1: 54, rue Royale
addressLine2: NULL
city: Nantes
state: NULL
postalCode: 44000
country: France
salesRepEmployeeNumber: 1370
creditLimit: 21000.00
*************************** 2. row ***************************
customerNumber: 112
customerName: Signal Gift Stores
contactLastName: King
contactFirstName: Jean
phone: 7025551838
addressLine1: 8489 Strong St.
addressLine2: NULL
city: Las Vegas
state: NV
postalCode: 83030
country: USA
salesRepEmployeeNumber: 1166
creditLimit: 71800.00
2 rows in set (0.00 sec)
ERROR:
No query specified
mysql> select max(creditlimit) from customers;
+------------------+
| max(creditlimit) |
+------------------+
| 227600.00 |
+------------------+
1 row in set (0.03 sec)
mysql> select @mc := max(creditlimit) from customers;
+-------------------------+
| @mc := max(creditlimit) |
+-------------------------+
| 227600.00 |
+-------------------------+
1 row in set, 1 warning (0.00 sec)
mysql> select @mc;
+-----------+
| @mc |
+-----------+
| 227600.00 |
+-----------+
1 row in set (0.00 sec)
mysql> select * from customers where creditlimit= @mc;
+----------------+------------------------+-----------------+------------------+----------------+--------------------+--------------+--------+-------+------------+---------+------------------------+-------------+
| customerNumber | customerName | contactLastName | contactFirstName | phone | addressLine1 | addressLine2 | city | state | postalCode | country | salesRepEmployeeNumber | creditLimit |
+----------------+------------------------+-----------------+------------------+----------------+--------------------+--------------+--------+-------+------------+---------+------------------------+-------------+
| 141 | Euro+ Shopping Channel | Freyre | Diego | (91) 555 94 44 | C/ Moralzarzal, 86 | NULL | Madrid | NULL | 28034 | Spain | 1370 | 227600.00 |
+----------------+------------------------+-----------------+------------------+----------------+--------------------+--------------+--------+-------+------------+---------+------------------------+-------------+
1 row in set (0.00 sec)
mysql> select * from customers where creditlimit= @mc \G;
*************************** 1. row ***************************
customerNumber: 141
customerName: Euro+ Shopping Channel
contactLastName: Freyre
contactFirstName: Diego
phone: (91) 555 94 44
addressLine1: C/ Moralzarzal, 86
addressLine2: NULL
city: Madrid
state: NULL
postalCode: 28034
country: Spain
salesRepEmployeeNumber: 1370
creditLimit: 227600.00
1 row in set (0.00 sec)
ERROR:
No query specified
mysql>
--------------------------
Local Variable
It is a type of variable that is not prefixed by @ symbol. The local variable is a strongly typed variable. The scope of the local variable is in a stored program block in which it is declared.
Syntax
We can use the DECLARE statement with the following syntax:
DECLARE variable_name datatype(size) [DEFAULT default_value];
DECLARE a,b,c INT DEFAULT 0;
Create Procedure Test()
BEGIN
DECLARE A INT DEFAULT 100;
DECLARE B INT;
DECLARE C INT;
DECLARE D INT;
SET B = 90;
SET C = 45;
SET D = A + B - C;
SELECT A, B, C, D;
END //
DELIMITER ;
-------------
System variables are a special class to all program units, which contains predefined variables.
SHOW VARIABLES;
SHOW VARIABLES LIKE '%version%';
SELECT @@key_buffer_size;
https://www.javatpoint.com/mysql-variables
https://www.oreilly.com/library/view/mysql-stored-procedure/0596100892/ch04s02.html
No comments:
Post a Comment
Note: only a member of this blog may post a comment.