MySQL SQL Compound Statement

Programming in MySQL – Part 2

MySQL Course

Foreword: In this part of the series, I talk about SQL compound statements in MySQL and variable declaration in it.

By: Chrysanthus Date Published: 27 May 2015

Introduction

This is part 2 of my series, Programming in MySQL. In this part of the series, I talk about SQL compound statements in MySQL and variable declaration in it. You must have read the previous part of the series before reaching here, as this is a continuation.

Compound Statement
You should have seen individual SQL statements in the previous series. There are times when you would want to treat a group of SQL statements as one unit. In that case you would have to group them into what is called a compound statement. It is simple to do this. There is a SQL statement called the BEGIN statement. This statement has two reserved words: BEGIN and END. Just put all the SQL statements you want to treat as a unit between the BEGIN and END reserved words. You place semicolons at the end of each SQL statement as usual. You do not place a semicolon after BEGIN; but you place a semicolon after END since from BEGIN to END is actually one statement, a compound statement.

The Syntax of the BEGIN statement is:

[begin_label:] BEGIN
[statement_list]
END [end_label]

Apart from the reserved words, BEGIN and END, and statement-list, every other item of the BEGIN statement is optional. We shall start by looking at the statement_list.

Use of a Compound Statement
A compound statement is used to accomplish a particular task in SQL Programming. An example of such a task is an event handler, called trigger. When you use SQL, you are doing programming. I have not approach the study of database in terms of programming in order to make the study easy, but that is what you are doing, with SQL.

The reserved word, END actually ends the BEGIN statement that encloses normal SQL statements.

A compound statement may be extended and may have statements before BEGIN and statements after END.

Delimiter
The delimiter for the SQL statement is the semicolon. A compound statement has statements within that end with semicolons and the END reserved word has a semicolon. As you can see, there will be confusion if something is not done. The solution is to create a delimiter for the start and end of the compound statement, as follows:

delimiter //
[begin_label:]
BEGIN
[statement_list]
END;//
delimiter ;

The created delimiter here is //. Everything within the two //s is handled by the server as one statement. Note the positions of the two //s. The word, delimiter, here means, redefine delimiter. So, at the end of the code, you must have “delimiter ;” to restore the default delimiter.

So you begin with “Delimiter //” and you end with “// delimiter ;”as shown above.

Variable Declaration in MySQL Compound Statement

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.

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 for the datum is NULL. A datum can then be placed into the variable, later down in the compound statement. Instead of a datum you can type an expression (see later).

The syntax for 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

MySQL SQL Compound Statement

Programming in MySQL – Part 2

MySQL Course

Introduction

Variable Declaration in MySQL Compound Statement

Related Links

Comments