/
Gists
/
tutorial-Store-Procedures-Mysql.sql
On gists
tutorial-Store-Procedures-Mysql.sql
•
MySql
MySql - advanced
tutorial-Store-Procedures-Mysql. Raw #
------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;
//