tutorial-Store-Procedures-Mysql.sql

------Creating Stored Procedures in MySQL------



--Make sure you have version 5 of MySQL:

SELECT VERSION();

+-----------+
| VERSION() |
+-----------+
| 5.0.15-nt |
+-----------+
1 row in set (0.00 sec)



--First pick a database to use (a procedure, like a table, is associated with
--a single database.) For these examples, I will use a database that is populated
--with the tables from HW 2:

USE ozaidan_hw2;



--Next, change the delimiter, because we will use the semicolon WITHIN the
--procedure declarations, and therefore it cannot be the delimiter anymore:

DELIMITER //



--OK, let's get started. Creating procedures is straightforward:

CREATE PROCEDURE myFirstProc()
  SELECT 'Hello World!' AS Output;
//

Query OK, 0 rows affected (0.00 sec)

--Whenever you create a procedure (successfully) you should get a 'Query OK' message.

--Calling a procedure is also straightforward:

CALL myFirstProc() //

+--------------+
| Output       |
+--------------+
| Hello World! |
+--------------+
1 row in set (0.00 sec)

--By the way, procedure names are NOT case sensitive:

CALL myfirstproc() //

+--------------+
| Output       |
+--------------+
| Hello World! |
+--------------+
1 row in set (0.00 sec)



--Another example:

CREATE PROCEDURE ListStudents()
  SELECT *
  FROM Student;
//

CALL ListStudents() //

+-------+----------+---------+------+------+-------+---------+-----------+
| StuID | LName    | Fname   | Age  | Sex  | Major | Advisor | city_code |
+-------+----------+---------+------+------+-------+---------+-----------+
|  1001 | Smith    | Linda   |   18 | F    |   600 |    1121 | BAL       |
|  1002 | Kim      | Tracy   |   19 | F    |   600 |    7712 | HKG       |
.
.
.
|  1034 | Epp      | Eric    |   18 | M    |    50 |    5718 | BOS       |
|  1035 | Schmidt  | Sarah   |   26 | F    |    50 |    5718 | WAS       |
+-------+----------+---------+------+------+-------+---------+-----------+
34 rows in set (0.00 sec)



--Say we only want student ID's and names. To update a procedure, we must
--first DROP it:

DROP PROCEDURE IF EXISTS ListStudents //

Query OK, 0 rows affected (0.00 sec)

--Again, whenever you drop a procedure, you should get a 'Query OK' message.
--From now on, we will always use "DROP PROCEDURE IF EXISTS procName" as
--a standard practice before declaring procedures:

DROP PROCEDURE IF EXISTS ListStudents //
CREATE PROCEDURE ListStudents()
  SELECT StuID, LName, FName
  FROM Student;
//

CALL ListStudents() //

+-------+----------+---------+
| StuID | LName    | FName   |
+-------+----------+---------+
|  1001 | Smith    | Linda   |
|  1002 | Kim      | Tracy   |
.
.
.
|  1034 | Epp      | Eric    |
|  1035 | Schmidt  | Sarah   |
+-------+----------+---------+
34 rows in set (0.00 sec)



--OK, let's use some parameters:

DROP PROCEDURE IF EXISTS sayHello //
CREATE PROCEDURE sayHello(IN name VARCHAR(20))
  SELECT CONCAT('Hello ', name, '!') AS Greeting;
//

--The 'IN' keyword tells MySQL that is should be expecting an input value for
--the parameter......hunh? Why would a parameter NOT have an input value? You will
--see in a little bit. First, let's see if sayHello works:

CALL sayHello('Omar') //

+-------------+
| Greeting    |
+-------------+
| Hello Omar! |
+-------------+
1 row in set (0.00 sec)



--Another example:

DROP PROCEDURE IF EXISTS saySomething //
CREATE PROCEDURE saySomething(IN phrase VARCHAR(20), IN name VARCHAR(20))
  SELECT CONCAT(phrase, ' ', name, '!') AS Output;
//

CALL saySomething('Go','Blue Jays') //
CALL saySomething('Do','my homework') //

+---------------+
| Output        |
+---------------+
| Go Blue Jays! |
+---------------+
1 row in set (0.00 sec)

+-----------------+
| Output          |
+-----------------+
| Do my homework! |
+-----------------+
1 row in set (0.00 sec)



--and another one:

DROP PROCEDURE IF EXISTS FindStudent //
CREATE PROCEDURE FindStudent(IN id INT)
  SELECT StuID, CONCAT(FName, ' ', LName) AS 'Student Name'
  FROM Student
  WHERE StuID = id;
//

CALL FindStudent(1001) //

+-------+--------------+
| StuID | Student Name |
+-------+--------------+
|  1001 | Linda Smith  |
+-------+--------------+
1 row in set (0.00 sec)



--and yet another:

DROP PROCEDURE IF EXISTS calculate //
CREATE PROCEDURE calculate(IN x INT, IN y INT, OUT sum INT, OUT product INT)
  SET sum = x + y;
  SET product = x * y;
//

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual ...

--Well, that wasn't good. The reason is, we must use BEGIN/END if we have
--a compound statement:

DROP PROCEDURE IF EXISTS calculate //
CREATE PROCEDURE calculate(IN x INT, IN y INT, OUT sum INT, OUT product INT)
BEGIN
  SET sum = x + y;
  SET product = x * y;
END;
//

--Did you notice the 'OUT' keyword for sum and product? This tells MySQL that those
--two parameters are not 'input' parameters but are 'output' parameters instead.
--Now, when calling the procedure, we need to provide four parameters: two input
--values, and two MySQL *variables* where the results will be stored:

CALL calculate(4,5,@s,@p) //

Query OK, 0 rows affected (0.00 sec)

--Here, @s and @p are MySQL variables. Notice that they start with @, although
--procedure *parameters* do not start with @

SELECT @s //
SELECT @p //

+------+
| @s   |
+------+
| 9    |
+------+
1 row in set (0.00 sec)

+------+
| @p   |
+------+
| 20   |
+------+
1 row in set (0.00 sec)

--Note: you can also have INOUT parameters, which serve as both input and output
--parameters.



--OK, let's do some interesting stuff. First off, flow control:

DROP PROCEDURE IF EXISTS mySign //
CREATE PROCEDURE mySign(IN x INT)
BEGIN
  IF x > 0 THEN
    SELECT x AS Number, '+' AS Sign;
  ELSEIF x < 0 THEN
    SELECT x AS Number, '-' AS Sign;
  ELSE
    SELECT x AS Number, 'Zero' AS Sign;
  END IF;
END;
//

CALL mySign(2) //
CALL mySign(-5) //
CALL mySign(0) //

+--------+------+
| Number | Sign |
+--------+------+
|      2 | +    |
+--------+------+
1 row in set (0.00 sec)

+--------+------+
| Number | Sign |
+--------+------+
|     -5 | -    |
+--------+------+
1 row in set (0.00 sec)

+--------+------+
| Number | Sign |
+--------+------+
|      0 | Zero |
+--------+------+
1 row in set (0.00 sec)



--Before we get any further, let's introduce variables:

DROP PROCEDURE IF EXISTS mySign //
CREATE PROCEDURE mySign(IN x INT)
BEGIN

  DECLARE result VARCHAR(20);

  IF x > 0 THEN
    SET result = '+';
  ELSEIF x < 0 THEN
    SET result = '-';
  ELSE
    SET result = 'Zero';
  END IF;

  SELECT x AS Number, result AS Sign;

END;
//

CALL mySign(2) //
CALL mySign(-5) //
CALL mySign(0) //

+--------+------+
| Number | Sign |
+--------+------+
|      2 | +    |
+--------+------+
1 row in set (0.00 sec)

+--------+------+
| Number | Sign |
+--------+------+
|     -5 | -    |
+--------+------+
1 row in set (0.00 sec)

+--------+------+
| Number | Sign |
+--------+------+
|      0 | Zero |
+--------+------+
1 row in set (0.00 sec)



--Using CASE:

DROP PROCEDURE IF EXISTS digitName //
CREATE PROCEDURE digitName(IN x INT)
BEGIN

  DECLARE result VARCHAR(20);

  CASE x
    WHEN 0 THEN SET result = 'Zero';
    WHEN 1 THEN SET result = 'One';
    WHEN 2 THEN SET result = 'Two';
    WHEN 3 THEN SET result = 'Three';
    WHEN 4 THEN SET result = 'Four';
    WHEN 5 THEN SET result = 'Five';
    WHEN 6 THEN SET result = 'Six';
    WHEN 7 THEN SET result = 'Seven';
    WHEN 8 THEN SET result = 'Eight';
    WHEN 9 THEN SET result = 'Nine';
    ELSE SET result = 'Not a digit';
  END CASE;

  SELECT x AS Digit, result AS Name;

END;
//

CALL digitName(0) //
CALL digitName(4) //
CALL digitName(100) //

+-------+------+
| Digit | Name |
+-------+------+
|     0 | Zero |
+-------+------+
1 row in set (0.00 sec)

+-------+------+
| Digit | Name |
+-------+------+
|     4 | Four |
+-------+------+
1 row in set (0.00 sec)

+-------+-------------+
| Digit | Name        |
+-------+-------------+
|   100 | Not a digit |
+-------+-------------+
1 row in set (0.00 sec)



--As you'd expect, we have loops. For example, WHILE loops:

DROP PROCEDURE IF EXISTS fact //
CREATE PROCEDURE fact(IN x INT)
BEGIN

  DECLARE result INT;
  DECLARE i INT;
  SET result = 1;
  SET i = 1;

  WHILE i <= x DO
    SET result = result * i;
    SET i = i + 1;
  END WHILE;

  SELECT x AS Number, result as Factorial;

END;
//

CALL fact(1) //
CALL fact(2) //
CALL fact(4) //
CALL fact(0) //

+--------+-----------+
| Number | Factorial |
+--------+-----------+
|      1 |         1 |
+--------+-----------+
1 row in set (0.00 sec)

+--------+-----------+
| Number | Factorial |
+--------+-----------+
|      2 |         2 |
+--------+-----------+
1 row in set (0.00 sec)

+--------+-----------+
| Number | Factorial |
+--------+-----------+
|      4 |        24 |
+--------+-----------+
1 row in set (0.01 sec)

+--------+-----------+
| Number | Factorial |
+--------+-----------+
|      0 |         1 |
+--------+-----------+
1 row in set (0.00 sec)



--There is also REPEAT/UNTIL loops:

DROP PROCEDURE IF EXISTS fact //
CREATE PROCEDURE fact(IN x INT)
BEGIN

  DECLARE result INT DEFAULT 1;  /* notice you can declare a variable*/
  DECLARE i INT DEFAULT 1;       /* and give it a value in one line */

  REPEAT
    SET result = result * i;
    SET i = i + 1;
  UNTIL i > x
  END REPEAT;

  SELECT x AS Number, result as Factorial;

END;
//

CALL fact(1) //
CALL fact(2) //
CALL fact(4) //
CALL fact(0) //

+--------+-----------+
| Number | Factorial |
+--------+-----------+
|      1 |         1 |
+--------+-----------+
1 row in set (0.00 sec)

+--------+-----------+
| Number | Factorial |
+--------+-----------+
|      2 |         2 |
+--------+-----------+
1 row in set (0.00 sec)

+--------+-----------+
| Number | Factorial |
+--------+-----------+
|      4 |        24 |
+--------+-----------+
1 row in set (0.00 sec)

+--------+-----------+
| Number | Factorial |
+--------+-----------+
|      0 |         1 |
+--------+-----------+
1 row in set (0.00 sec)



--OK, do you remember this?
/*
CREATE PROCEDURE FindStudent(IN id INT)
  SELECT StuID, CONCAT(FName, ' ', LName) AS 'Student Name'
  FROM Student
  WHERE StuID = id;
//
*/

--What if we only want to extract the name without printing it out?
--Obviously, we need some OUT parameters. Still, how do you extract
--information into those OUT parameters?
--
--Answer: something called a CURSOR:

DROP PROCEDURE IF EXISTS FindName //
CREATE PROCEDURE FindName(IN id INT, OUT fn VARCHAR(20), OUT ln VARCHAR(20))
BEGIN
  DECLARE cur CURSOR FOR
    SELECT FName, LName
    FROM Student
    WHERE StuID = id;
  OPEN cur;
  FETCH cur INTO fn, ln;
  CLOSE cur;
END;
//

CALL FindName(1001,@f,@l) //

Query OK, 0 rows affected (0.00 sec)

--Remember that @f and @l are MySQL variables:

SELECT @f //
SELECT @l //

+-------+
| @f    |
+-------+
| Linda |
+-------+
1 row in set (0.00 sec)

+-------+
| @l    |
+-------+
| Smith |
+-------+
1 row in set (0.00 sec)



--What if we give an invalid student ID?

CALL FindName(0000,@f,@l) //

ERROR 1329 (02000): No data to FETCH

--MySQL complains, as expected. It would be nice to handle this more elegantly, however.
--We need an error HANDLER. Let's modify FindName:

DROP PROCEDURE IF EXISTS FindName //
CREATE PROCEDURE FindName(IN id INT, OUT fn VARCHAR(20), OUT ln VARCHAR(20))
BEGIN
  DECLARE cur CURSOR FOR
    SELECT FName, LName
    FROM Student
    WHERE StuID = id;

  DECLARE EXIT HANDLER FOR NOT FOUND
    SELECT 'Sorry; this ID was not found' AS 'Error Message';

  OPEN cur;
  FETCH cur INTO fn, ln;
  CLOSE cur;
END;
//

CALL FindName(0000,@f,@l) //

+------------------------------+
| Error Message                |
+------------------------------+
| Sorry; this ID was not found |
+------------------------------+
1 row in set (0.00 sec)



--Another use for handlers: multiple FETCH calls using a CONTINUE handler.
--
--In this case, we use a CONTINUE handler that, instead of exiting the procedure
--upon encountering a NOT FOUND error, simply sets a variable done = 1.
--
--Why would we do that? And how does that help us carry out multiple FETCH calls?
--
--Take a look at this procedure, which traverses all the entries of a table to
--find the maximum and minimum age:

DROP PROCEDURE IF EXISTS MaxMinAge //
CREATE PROCEDURE MaxMinAge(OUT maxAge INT, OUT minAge INT)
BEGIN
  DECLARE currAge,maxSoFar,minSoFar,done INT;

  DECLARE cur CURSOR FOR
    SELECT Age
    FROM Student;

  DECLARE CONTINUE HANDLER FOR NOT FOUND
    SET done = 1;

  SET maxSoFar = 0;
  SET minSoFar = 1000;
  SET done = 0;

  OPEN cur;
  WHILE done = 0 DO
    FETCH cur INTO currAge;
    IF currAge > maxSoFar THEN
      SET maxSoFar = currAge;
    END IF;
    IF currAge < minSoFar THEN
      SET minSoFar = currAge;
    END IF;
  END WHILE;

  CLOSE cur;

  SET maxAge = maxSoFar;
  SET minAge = minSoFar;
 
END;
//

CALL MaxMinAge(@max,@min) //

Query OK, 0 rows affected (0.00 sec)

SELECT @max //
SELECT @min //

+------+
| @max |
+------+
| 27   |
+------+
1 row in set (0.00 sec)

+------+
| @min |
+------+
| 16   |
+------+
1 row in set (0.00 sec)






--In summary, stored procedures in MySQL look like this:

DROP PROCEDURE IF EXISTS procName //
CREATE PROCEDURE procName(parameter list)
BEGIN
  /* variable declarations */
  /* CURSOR definitions */
  /* declaring handlers */

  /* procedure body...whatever you want it to do */

END;
//


--In more detail:

DROP PROCEDURE IF EXISTS procName //
CREATE PROCEDURE procName(IN/OUT/INOUT parName parType, ...)
BEGIN
  /* variable declarations */
  DECLARE varName,... varType;
      /* e.g. DECLARE myName VARCHAR(20); DECLARE x,y,z INT;      */

  DECLARE varName varType DEFAULT value;
      /* e.g. DECLARE x INT DEFAULT 0;      */

  /* CURSOR definitions */
  DECLARE curName CURSOR FOR
    SELECT ...

      /* e.g. DECLARE cur1 CURSOR FOR
                SELECT FName, LName
                FROM Student;      */


  /* declaring handlers */
  DECLARE EXIT/CONTINUE HANDLER FOR errorType/errorNumber
    ... action ...            

      /* e.g. DECLARE EXIT HANDLER FOR NOT FOUND
                SELECT 'Sorry; this ID was not found' AS 'Error Message';      */

      /* e.g. DECLARE CONTINUE HANDLER FOR NOT FOUND
                SET done = 1;      */


  /* procedure body...whatever you want it to do */

    /* IF statement */
    IF cond1 THEN
      action1
    ELSEIF cond2 THEN
      action2
    ELSEIF cond3 THEN
      action3
    ELSE
      elseaction
    END IF;

        /* e.g. IF x > 0 THEN
                  SET result = '+';
                ELSEIF x < 0 THEN
                  SET result = '-';
                ELSE
                  SET result = 'Zero';
                END IF;      */

    /* CASE statement */
    CASE varName
      WHEN val1 THEN action1
      WHEN val2 THEN action2
      ELSE elseaction
    END CASE;

        /* e.g. CASE position
                  WHEN 1 THEN SET result = 'Gold Medal';
                  WHEN 2 THEN SET result = 'Silver Medal';
                  WHEN 3 THEN SET result = 'Bronze Medal';
                  ELSE SET result = 'No Medal!';
                END CASE;      */

    /* WHILE loop */
    WHILE cond DO
      action1
      action2
      ...
    END WHILE

        /* e.g. WHILE i < 5 DO
                  SET result = result + i;
                  SET i = i + 1;
                END WHILE;      */

    /* REPEAT/UNTIL loop */

    REPEAT
      action1
      action2
      ...
    UNTIL cond
    END REPEAT;

        /* e.g. REPEAT
                  SET result = result + i;
                  SET i = i + 1;
                UNTIL i >= 5
                END REPEAT;      */



    /* using a CURSOR */
    OPEN curName;
    .
    .
    FETCH curName INTO var1, var2, ...;
    .
    .
    CLOSE curName;

        /* e.g. Assume cur1 has id's, first names, and last names
                let's find the name of the student whose StuID is x:

                OPEN cur1;

                SET found = 0;
                WHILE found = 0 DO

                  FETCH cur1 INTO nextID, nextFName, nextLName;

                  IF nextID = x THEN
                    SET result = CONCAT(nextFName, ' ', nextLName);
                    SET found = 1;
                  END IF;                    

                END WHILE;

                CLOSE cur1;      */


END;
//