Declaring Variables Within Procedures - MySQL

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 myvar INT ;
DECLARE yourvar VARCHAR(40);
SET myvar = 1;
SET yourvar = '';

with these two DECLARE statements using DEFAULT :

DECLARE myvar INT DEFAULT 1;
DECLARE yourvar VARCHAR(40) 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.

---

NOTE   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]
END CASE;

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 :

CREATE FUNCTION letter(aletter VARCHAR(1) ) RETURNS VARCHAR(50)
BEGIN
  DECLARE msg VARCHAR(50);
  DECLARE temp VARCHAR(1);
  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.';
  END IF;
  RETURN msg;
END

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:

CAS E
WHEN condition THEN statement
[ELSE statement]
END CASE;

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.

---

CAUTION   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:

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

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.