Objectives:

• Understand variables and their use.
• Understand the use of looping statements in stored procedures.

Variables

1. Variables are defined using the DECLARE statement.
2. Variable names must follow the naming conventions for SQL columns (e.g., no spaces or non-alphanumeric characters except for the underscore, variable names must be unique, etc.).
3. Variables must be declared to be of an SQL type.
4. A value is assigned to a variable using the SET keyword.
5. Within a procedure, once a variable has been set to hold a value, the variable will hold that value until the end of the procedure or until the variable is provided a new value with another SET.

CREATE PROCEDURE `police`.`variable` ()
BEGIN
  DECLARE firstValue INT;  -- Variable of type integer to hold the first number
  DECLARE secondValue INT; -- Variable of type integer to hold the second number
  DECLARE total INT;       -- Variable to hold the total

  SET firstValue = 5;
  SET secondValue = 10;
  SET total = firstValue + secondValue;     -- Add firstValue and secondValue together
  select concat("The total is: ", total);   -- Display the total
END $$

CREATE PROCEDURE `police`.`charvariable` ()
BEGIN
  DECLARE fname VARCHAR(20);    -- Variable to hold first name
  DECLARE lname VARCHAR(20);    -- Variable to hold last name

  SET fname = "Heidi";
  SET lname = "Ellis";
  select concat("Full name: ", fname, lname);
END $$

Try it on your own

Looping

• The beginning and end of the loop must be labeled with a name that you define.
• The name that you define must conform to MySQL naming conventions (e.g., no spaces or non-alphanumeric characters except for the underscore, label names must be unique, etc.).
• The name used at the beginning and end of the loop must be exactly the same.
• The beginning label must be followed by a colon (:) and must be before the LOOP keywork.
• The ending label follows the END LOOP keywords and must be followed by a semicolon.
• A LEAVE statement is used to exit the loop.
• The LEAVE statment must use the label defined for the loop which is being exited.

thelabel: LOOP
  -- Put code that is to be repeated here
END LOOP thelabel;

thelabel: LOOP
  -- Put code that is to be repeated here
  if (exit condition)
     then LEAVE thelabel;
  end if;
END LOOP thelabel;

-- This procedure adds one to a counter variable five times and then prints the contents of the variable
CREATE PROCEDURE `printFive`()
BEGIN

DECLARE i INT;      -- Variable to hold the running total
SET i = 0;          -- Initialize the variable to zero before entering loop

helloloop: LOOP               -- Beginning of loop
  SET i = i + 1;              -- Increment loop counter
  if (i = 5)                  -- Check to see if loop counter has reached maximum value (5) 
     then LEAVE helloloop;    -- If so, exit the loop (go to "end LOOP helloloop")
  end if;                     -- Otherwise end the if statement and go back to line "helloloop: LOOP" and repeat
end LOOP helloloop;           -- End the loop 

  select i;                   -- Print the total 
END$$

-- This procedure totals the numbers between one and five
-- E.g., 1 + 2 + 3 + 4 + 5
CREATE PROCEDURE `police`.`totalUpToFive` ()
BEGIN
  DECLARE i, j INT;      -- Two variables to hold the number of times
                         -- through the loop and the running total
  SET i = 1;             -- Variable to hold the number of times to loop
  SET j = 1;             -- Variable to hold the running total. 
  
  myloop: LOOP           -- Start the loop
      SET j = j + i;     -- Add the loop counter to the running total
      SET i = i + 1;     -- Increment the loop counter

      If i = 5           -- Check to see if the maximum number of iterations has been reached
         then LEAVE myloop;             -- If so, leave the loop
     end if;

  end LOOP myloop;       -- End the loop
  select j;              -- Display the results 

END$$

- This procedure totals the numbers up to some maximum m
-- E.g., 1 + 2 + 3 + 4 + 5
CREATE PROCEDURE `police`.`totalUpTo` (m INT)
BEGIN
  DECLARE i, j INT;      -- Two variables to hold the number of times
                         -- through the loop and the running total
  SET i = 1;             -- Variable to hold the number of times to loop
  SET j = 1;             -- Variable to hold the running total. 
  
  myloop: LOOP           -- Start the loop
      SET j = j + i;     -- Add the loop counter to the running total
      SET i = i + 1;     -- Increment the loop counter

      If i = m           -- Check to see if the maximum number of iterations has been reached
         then LEAVE myloop;             -- If so, leave the loop
     end if;

  end LOOP myloop;       -- End the loop
  select j;              -- Display the results 
END$$

Try it on your own

1. Create a procedure called countDown that counts down from five to zero. Print the value of the looping variable after leaving the loop.
2. Now modify the countDown procedure to count down from any number by adding a parameter to the procedure.
3. Now modify the countDown procedure to count how many times the loop is executed. This will require adding a variable to the procedure. Instead of printing the value of the looping variable, print the number of times the loop was executed.
4. If you have time: Create a procedure called multiply that is passed two INT parameters x and y. Use a loop to calculate the multiplication of x and y. Remember that multiplication can be thought of as the process of adding one number, say x, to itself y times. Therefore, the actual calculation should use a loop to add x to a total y times. In other words, your loop should exit when the counting variable is greater than y and you should add x to a variable that keeps track of the total within the loop. Don't forget to declare all variables and to initialize all variables via the SET keyword.