Home arrow MySQL arrow Page 5 - Examining MySQL 5.0

Declaring Variables Within Procedures - MySQL

MySQL 5.0 gives the developer access to features that earlier versions of MySQL do not support. These include stored procedures and stored functions. This article, the second in a series, begins exploring the potential of these two features. It is excerpted from chapter eight of Beginning MySQL Database Design and Optimization: From Novice to Professional, written by Jon Stephens and Chad Russell (Apress, ISBN: 1590593324).

  1. Examining MySQL 5.0
  2. Syntax
  3. Stored Procedure Examples
  4. Stored Function Examples
  5. Declaring Variables Within Procedures
By: Apress Publishing
Rating: starstarstarstarstar / 22
April 27, 2006

print this article



The DECLARE statement is used to create variables for use within a stored procedure or stored function. These can serve as counters or iterators, to provide temporary storage of intermediate values used in calculating a result, or for any other purpose. Such variables are local to the procedure; in order to pass their values back to a calling routine or query, you must RETURN the value in the case of a stored function, or assign the value to an output parameter of a stored procedure. The local variable itself cannot be referenced from outside the stored procedure or function.

Any DECLARE statements that are used in a stored procedure or function must come first in the body, immediately following the BEGIN keyword, and before any other statements. The variable’s type must be included in the declaration, as shown here, where name is the name of the variable and type is its datatype:

DECLARE name type;

Each local variable may be of any datatype supported by MySQL.

It’s also possible to save a bit of time and typing by providing a default value for the variable, using the DEFAULT keyword followed by the desired initial value. For example, you can replace these four statements (two DECLARE and two SET statements):

DECLARE yourvar VARCHAR(40);
SET myvar = 1;
SET yourvar = '';

with these two DECLARE statements using DEFAULT :


You can declare as many local variables as you require in a stored procedure or stored function, using as many DECLARE statements as necessary, so long as all of them precede any other statements following the BEGIN keyword that starts the body of the procedure.


The CASE block provides a somewhat cleaner way of providing multiple responses to conditions than does IF... ELSEIF... ELSE , and comes in two varieties. The first of these allows for different actions to be taken depending upon the value of a single expression that follows the opening CASE keyword. Each value to be tested against resides in its own WHEN clause, of which there can be one or more, and is followed by THEN . The statement or statements to be exe cuted should the expression match the value follow the THEN keyword. Finally, an optional ELSE prefaces one or more statements to be executed if the expression fails to match any of the WHEN values.

When used inside a stored procedure or stored function, a CASE block terminates with END CASE instead of END .

The syntax for the first form of CASE can be summed up as shown here:

CASE expression
WHEN value THEN statement-block
[ELSE statement-block]

We provide an example in the stored function letter, which takes a single character as input, and tells us which of the first three letters of the alphabet it is, or if it isn’t any of these:


Alternatively, we could have written this function using IF... ELSEIF... ELSE :

  SET temp = LCASE(aletter);
IF temp = 'a' THEN SET msg = 'First letter.';
    ELSEIF temp = 'b' THEN SET msg = 'Second letter.'’;
    ELSEIF temp = 'c' THEN SET msg = 'Third letter.';
    ELSE SET msg = 'Some other letter.';

Since we’re testing against the same value in every branch of this SQL block, it’s shorter and simpler just to use CASE .

The other version of CASE takes a separate expression in each WHILE clause, evaluates it, and if it is true, it executes any statements following the accompany ing THEN keyword. The syntax for this form is shown here:

WHEN condition THEN statement
[ELSE statement]

Here’s an example of a stored procedure named divisors that finds the low est divisor of a number if it’s 2, 3, or 5, and lets us know if the number supplied as input isn’t divisible by any of these three values:

Let’s examine the output of this procedure for some different input values and see what we can discern about the behavior of this second form of CASE from the results:

For an input value of 9, we get back “9 is . . . divisible by 3.” For 13, we get the message, “13 is . . . not divisible by 2, 3, or 5.” So far, so good. But what about a number that has two divisors among the set against which we’re testing? When we test 6 as a value, we get only the first of these: “6 is . . . divisible by 3.” This tells you that the WHEN branches of a CASE block are mutually exclusive: Once one of the conditions for which you’re testing is found to be true, only the statements following the corresponding THEN are executed, any remaining WHEN clauses are skipped, and program flow exits the CASE block immediately thereafter.

Programmers used to working in languages such as PHP, Perl, JavaScript, and so on, should take special note that SQL has no statement analogous to break for CASE structures. Every WHEN branch of a CASE behaves as though it were terminated with a break (or, in SQL terms, a LEAVE statement). In other words, you can’t “fall through” from one branch of a CASE block to the next one.

Before we move on, you should also note that the conditions being tested in the WHEN clauses of this form of CASE don’t have to involve the same variables. For example, consider a CASE block such as this one:

  WHEN x > 10 THEN output = output / 2;
  WHEN y < 5 THEN output = output + 10;
  WHEN z = 0 THEN output = -1;

This is perfectly legitimate and may in some instances prove to be useful, just so long as you bear in mind that (in this particular case) if x is greater than 10, then the values of y and z will never be tested.

Please be sure to check in next week for the next part.

>>> More MySQL Articles          >>> More By Apress Publishing

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort


- Oracle Unveils MySQL 5.6
- MySQL Vulnerabilities Threaten Databases
- MySQL Cloud Options Expand with Google Cloud...
- MySQL 5.6 Prepped to Handle Demanding Web Use
- ScaleBase Service Virtualizes MySQL Databases
- Oracle Unveils MySQL Conversion Tools
- Akiban Opens Database Software for MySQL Use...
- Oracle Fixes MySQL Bug
- MySQL Databases Vulnerable to Password Hack
- MySQL: Overview of the ALTER TABLE Statement
- MySQL: How to Use the GRANT Statement
- MySQL: Creating, Listing, and Removing Datab...
- MySQL: Create, Show, and Describe Database T...
- MySQL Data and Table Types
- McAfee Releases Audit Plugin for MySQL Users

Developer Shed Affiliates


Dev Shed Tutorial Topics: