MySQL Variables local variables sql videos in telugu 92

 https://youtu.be/un8K_Clbfzs

----------------------------------------------------

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