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 ;
with these two DECLARE statements using DEFAULT :
DECLARE myvar INT DEFAULT 1;
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.
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)
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:
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.
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.
blog comments powered by Disqus