Forked from mikelemus27/tutorial-Store-Procedures-Mysql.sql
Created
May 17, 2023 21:44
-
-
Save Kcko/2c517b5eec21c9de113f41a68f3422a0 to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
------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; | |
// | |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment