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
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;
--------------------------
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;
DELIMITER //
Create Procedure Test2()
BEGIN
DECLARE A INT DEFAULT 100;
DECLARE B INT;
DECLARE C INT;
DECLARE D INT;
set @ram1=500;
SET B = 90;
SET C = 45;
SET D = A + B - C;
SELECT A, B, C, D;
select @ram,@ram1;
END //
DELIMITER ;
-------------
Enter password: *******
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 55
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> DECLARE a,b,c INT DEFAULT 0;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DECLARE a,b,c INT DEFAULT 0' at line 1
mysql> drop procedure test;
Query OK, 0 rows affected (0.42 sec)
mysql> DELIMITER //
mysql> 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 //
Query OK, 0 rows affected (0.29 sec)
mysql> DELIMITER ;
mysql> call test();
+------+------+------+------+
| A | B | C | D |
+------+------+------+------+
| 100 | 90 | 45 | 145 |
+------+------+------+------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.01 sec)
mysql> show a;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'a' at line 1
mysql> show @B;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '@B' at line 1
mysql> set @ram=300;
Query OK, 0 rows affected (0.00 sec)
mysql> DELIMITER //
mysql> Create Procedure Test1()
-> 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;
-> select @ram;
-> END //
Query OK, 0 rows affected (0.11 sec)
mysql> DELIMITER ;
mysql> call test1();
+------+------+------+------+
| A | B | C | D |
+------+------+------+------+
| 100 | 90 | 45 | 145 |
+------+------+------+------+
1 row in set (0.02 sec)
+------+
| @ram |
+------+
| 300 |
+------+
1 row in set (0.03 sec)
Query OK, 0 rows affected (0.04 sec)
mysql> DELIMITER //
mysql> Create Procedure Test2()
-> BEGIN
-> DECLARE A INT DEFAULT 100;
-> DECLARE B INT;
-> DECLARE C INT;
-> DECLARE D INT;
-> set @ram1=500;
-> SET B = 90;
-> SET C = 45;
-> SET D = A + B - C;
-> SELECT A, B, C, D;
-> select @ram,@ram1;
-> END //
Query OK, 0 rows affected (0.16 sec)
mysql> DELIMITER ;
mysql> call test2();
+------+------+------+------+
| A | B | C | D |
+------+------+------+------+
| 100 | 90 | 45 | 145 |
+------+------+------+------+
1 row in set (0.00 sec)
+------+-------+
| @ram | @ram1 |
+------+-------+
| 300 | 500 |
+------+-------+
1 row in set (0.01 sec)
Query OK, 0 rows affected (0.01 sec)
mysql> select @ram1;
+-------+
| @ram1 |
+-------+
| 500 |
+-------+
1 row in set (0.00 sec)
mysql> 92 MySQL Variables -local variables sql videos in telugu
-------------
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.