Working with MySQL Variables

Programming in MySQL – Part 5

MySQL Course

Foreword: In this part of the series, I explain how to use variables in MySQL programming.

By: Chrysanthus Date Published: 27 May 2015

Introduction

This is part 5 of my series, Programming in MySQL. In this part of the series, I explain how to use variables in MySQL programming. You should have read the previous parts of the series before reaching here, as this is a continuation.

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. You should have heard 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. 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 nothing.

THE SET Statement in Compound Statement
The SET statement can be used in two forms. It can be used inside a compound statement. 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. To change the value of the variable, just reset it. For example, to change the value for A from 30 to 15 say, type:

    SET A = 15;

SET Statement with Session
You can have a variable whose life is 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 displayed. Note the use of @ here, while using the variable name. Note the use of the SELECT statement to display output.

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;

Calling a Procedure
Assume that you have used the session 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 session 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

Broad Network

Related Articles

Working with MySQL Variables

Programming in MySQL – Part 5

MySQL Course

Introduction

Related Links

Comments