Working with SQL Variables in MySQL
Handling MySQL Events with Triggers and Procedures Using SQL – Part 9
Division 5
Forward: In this part of the series, we see how to work with SQL variables in MySQL.
By: Chrysanthus Date Published: 7 Aug 2012
Introduction
Note: If you cannot see the code or if you think anything is missing (broken link, image absent), just contact me at forchatrans@yahoo.com. That is, contact me for the slightest problem you have about what you are reading.
Variable in a compound Statement
The BEGIN statement is a compound statement. The purpose of the BEGIN statement is to group normal SQL statements together. You can declare a variable inside a compound statement but only the SQL statements in the compound statement will be able to see (use) the variable. Such a variable cannot be seen (used) outside the compound statement. We have seen this before, but here we shall look at it in more detail.
A simplified form of the declaration syntax is:
DECLARE var_name [, var_name] ... type [DEFAULT value]
Var_name is a name of your choice that you give. The value can be a string in double or single quotes if the type is a string type like CHAR. It can be a number preceded by the negative sign if the number is negative. It can also be NULL. Consider NULL as empty.
The SET statement can be used in two forms. It can be used inside a compound statement as we have seen. Any variable it declares inside a compound statement can only be seen inside the compound statement. Whether used inside or outside a compound statement, it can be used to assign a value to a variable. Inside a compound statement, you can have:
SET A = 30;
SET B = A;
The life of a variable from a SET statement inside a compound statement ends when the compound statement ends its operation in the program.
SET Statement with Connection
You can have a variable whose life will be as long as the user is connected to the database server. You still use the SET statement for this, but it has a slightly different syntax, which is:
SET @var_name = expr;
Here, you precede the variable name with @. expr stands for Expression. In many cases it is a literal (number or string). The good news with this SET statement is that you do not need to specify the data type.
Try the following code:
SET @myVar= 123;
SELECT @myVar;
123 is finally displayed. Note the use of @ when using the variable name.
If you want a variable with the SET statement and you do not want to assign any value to it yet, assign NULL to it as in:
SET @var = NULL;
Assume that you have used the connection SET statement to create variables and you want to call a procedure, with the variables. You would type something like:
CALL pro (@va1, @var2);
where pro is the name of the procedure and @var1 and @var2 are the connection variable names you have used the SET statements to create.
That is it for this part of the series. We stop here and continue in the next part.
Chrys
Related Links
Major in Website DesignWeb Development Course
HTML Course
CSS Course
ECMAScript Course
NEXT