Variable Declaration in MySQL Compound Statement

Handling MySQL Events with Triggers and Procedures Using SQL – Part 3

Forward: In this part of the series, we look at variable declaration in compound statements in MySQL.

By: Chrysanthus Date Published: 6 Aug 2012

Introduction

This is part 3 of my series, Handling MySQL Events with Triggers and Stored Procedures Using SQL. You must have read all the different parts of the series (or equivalent tutorials from elsewhere) up to this point. In this part of the series, we look at variable declaration in compound statements in MySQL.

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.

Declaration
A compound statement is a group of statements that behave as a unit to accomplish a particular task. Sometimes a compound statement would need data to work with. Any data used by a compound statement has to be declared first before it can be used. This declaration is done just below the reserved word, BEGIN.

You can declare the following classes of data just below the BEGIN reserved word:

- Variables
- Cursors
- Conditions and handlers

There are three items above. We shall look at variables in this part of the series. I may not treat the other items in this series, since they are not appropriate to other DBMS.

Variable
A variable here is similar to what goes on in mathematics, but not quite. You can have something like,

DECLARE myVar INTERGER DEFAULT 5;

You must begin a declaration with the reserved word, DECLARE. This is followed by a variable name of your choice that will hold the datum (singular for data). Then you have the data type of the datum. Then you can have the DEFAULT reserved word and then the datum itself. It is also possible to have a declaration as follows:

DECLARE myVar INTERGER;

In this case the DEFAULT reserved word and the datum are omitted; in this case the initial value of the datum is NULL. A datum can be placed into the variable, later down in the compound statement. Instead of a datum you can type an expression (see later).

The syntax of a variable declaration is:

DECLARE var_name [, var_name] ... type [DEFAULT value]

As the syntax indicates, you can declare more than one variable in the same declaration statement; separate the variable names with commas, like

DECLARE myVar, yourVar, hisVar INTEGER

Here, the three variables are of the same type, INTEGER

Note: A DECLARE statement ends with a semicolon.

You can have many variable declaration statements in a compound statement. They come at the top in the compound statement, just below the BEGIN reserved word.

Local Declaration
Anything that you declare in a compound statement can only be seen (used) in the compound statement in which it is declared. It cannot be seen outside the compound statement.

Well, let us end here. We continue in the next part of the series.

Chrys

Broad Network

Related Articles

Variable Declaration in MySQL Compound Statement

Handling MySQL Events with Triggers and Procedures Using SQL – Part 3

Introduction

Related Links

Comments