------Creating Stored Procedures in MySQL------
--Make sure you have version 5 of MySQL:
1 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:
1 DELIMITER //
--OK, let's get started. Creating procedures is straightforward:
1 CREATE PROCEDURE myFirstProc()
2 SELECT 'Hello World!' AS Output;
3 //
Query OK, 0 rows affected (0.00 sec)
1 --Whenever you create a procedure (successfully) you should get a 'Query OK' message.
2
3 --Calling a procedure is also straightforward:
4
5 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)
1 --Say we only want student ID's and names. To update a procedure, we must
2 --first DROP it:
3
4 DROP PROCEDURE IF EXISTS ListStudents //
5
6 Query OK, 0 rows affected (0.00 sec)
7
8 --Again, whenever you drop a procedure, you should get a 'Query OK' message.
9 --From now on, we will always use "DROP PROCEDURE IF EXISTS procName" as
10 --a standard practice before declaring procedures:
11
12 DROP PROCEDURE IF EXISTS ListStudents //
13 CREATE PROCEDURE ListStudents()
14 SELECT StuID, LName, FName
15 FROM Student;
16 //
17
18 CALL ListStudents() //
19
20 +-------+----------+---------+
21 | StuID | LName | FName |
22 +-------+----------+---------+
23 | 1001 | Smith | Linda |
24 | 1002 | Kim | Tracy |
25 .
26 .
27 .
28 | 1034 | Epp | Eric |
29 | 1035 | Schmidt | Sarah |
30 +-------+----------+---------+
31 34 rows in set (0.00 sec)
32
33
34
35 --OK, let's use some parameters:
36
37 DROP PROCEDURE IF EXISTS sayHello //
38 CREATE PROCEDURE sayHello(IN name VARCHAR(20))
39 SELECT CONCAT('Hello ', name, '!') AS Greeting;
40 //
41
42 --The 'IN' keyword tells MySQL that is should be expecting an input value for
43 --the parameter......hunh? Why would a parameter NOT have an input value? You will
44 --see in a little bit. First, let's see if sayHello works:
45
46 CALL sayHello('Omar') //
47
48 +-------------+
49 | Greeting |
50 +-------------+
51 | Hello Omar! |
52 +-------------+
53 1 row in set (0.00 sec)
54
55
56
57 --Another example:
58
59 DROP PROCEDURE IF EXISTS saySomething //
60 CREATE PROCEDURE saySomething(IN phrase VARCHAR(20), IN name VARCHAR(20))
61 SELECT CONCAT(phrase, ' ', name, '!') AS Output;
62 //
63
64 CALL saySomething('Go','Blue Jays') //
65 CALL saySomething('Do','my homework') //
66
67 +---------------+
68 | Output |
69 +---------------+
70 | Go Blue Jays! |
71 +---------------+
72 1 row in set (0.00 sec)
73
74 +-----------------+
75 | Output |
76 +-----------------+
77 | Do my homework! |
78 +-----------------+
79 1 row in set (0.00 sec)
80
81
82
83 --and another one:
84
85 DROP PROCEDURE IF EXISTS FindStudent //
86 CREATE PROCEDURE FindStudent(IN id INT)
87 SELECT StuID, CONCAT(FName, ' ', LName) AS 'Student Name'
88 FROM Student
89 WHERE StuID = id;
90 //
91
92 CALL FindStudent(1001) //
93
94 +-------+--------------+
95 | StuID | Student Name |
96 +-------+--------------+
97 | 1001 | Linda Smith |
98 +-------+--------------+
99 1 row in set (0.00 sec)
100
101
102
103 --and yet another:
104
105 DROP PROCEDURE IF EXISTS calculate //
106 CREATE PROCEDURE calculate(IN x INT, IN y INT, OUT sum INT, OUT product INT)
107 SET sum = x + y;
108 SET product = x * y;
109 //
110
111 ERROR 1064 (42000): You have an error in your SQL syntax; check the manual ...
112
113 --Well, that wasn't good. The reason is, we must use BEGIN/END if we have
114 --a compound statement:
115
116 DROP PROCEDURE IF EXISTS calculate //
117 CREATE PROCEDURE calculate(IN x INT, IN y INT, OUT sum INT, OUT product INT)
118 BEGIN
119 SET sum = x + y;
120 SET product = x * y;
121 END;
122 //
123
124 --Did you notice the 'OUT' keyword for sum and product? This tells MySQL that those
125 --two parameters are not 'input' parameters but are 'output' parameters instead.
126 --Now, when calling the procedure, we need to provide four parameters: two input
127 --values, and two MySQL *variables* where the results will be stored:
128
129 CALL calculate(4,5,@s,@p) //
130
131 Query OK, 0 rows affected (0.00 sec)
132
133 --Here, @s and @p are MySQL variables. Notice that they start with @, although
134 --procedure *parameters* do not start with @
135
136 SELECT @s //
137 SELECT @p //
138
139 +------+
140 | @s |
141 +------+
142 | 9 |
143 +------+
144 1 row in set (0.00 sec)
145
146 +------+
147 | @p |
148 +------+
149 | 20 |
150 +------+
151 1 row in set (0.00 sec)
152
153 --Note: you can also have INOUT parameters, which serve as both input and output
154 --parameters.
155
156
157
158 --OK, let's do some interesting stuff. First off, flow control:
159
160 DROP PROCEDURE IF EXISTS mySign //
161 CREATE PROCEDURE mySign(IN x INT)
162 BEGIN
163 IF x > 0 THEN
164 SELECT x AS Number, '+' AS Sign;
165 ELSEIF x < 0 THEN
166 SELECT x AS Number, '-' AS Sign;
167 ELSE
168 SELECT x AS Number, 'Zero' AS Sign;
169 END IF;
170 END;
171 //
172
173 CALL mySign(2) //
174 CALL mySign(-5) //
175 CALL mySign(0) //
176
177 +--------+------+
178 | Number | Sign |
179 +--------+------+
180 | 2 | + |
181 +--------+------+
182 1 row in set (0.00 sec)
183
184 +--------+------+
185 | Number | Sign |
186 +--------+------+
187 | -5 | - |
188 +--------+------+
189 1 row in set (0.00 sec)
190
191 +--------+------+
192 | Number | Sign |
193 +--------+------+
194 | 0 | Zero |
195 +--------+------+
196 1 row in set (0.00 sec)
197
198
199
200 --Before we get any further, let's introduce variables:
201
202 DROP PROCEDURE IF EXISTS mySign //
203 CREATE PROCEDURE mySign(IN x INT)
204 BEGIN
205
206 DECLARE result VARCHAR(20);
207
208 IF x > 0 THEN
209 SET result = '+';
210 ELSEIF x < 0 THEN
211 SET result = '-';
212 ELSE
213 SET result = 'Zero';
214 END IF;
215
216 SELECT x AS Number, result AS Sign;
217
218 END;
219 //
220
221 CALL mySign(2) //
222 CALL mySign(-5) //
223 CALL mySign(0) //
224
225 +--------+------+
226 | Number | Sign |
227 +--------+------+
228 | 2 | + |
229 +--------+------+
230 1 row in set (0.00 sec)
231
232 +--------+------+
233 | Number | Sign |
234 +--------+------+
235 | -5 | - |
236 +--------+------+
237 1 row in set (0.00 sec)
238
239 +--------+------+
240 | Number | Sign |
241 +--------+------+
242 | 0 | Zero |
243 +--------+------+
244 1 row in set (0.00 sec)
245
246
247
248 --Using CASE:
249
250 DROP PROCEDURE IF EXISTS digitName //
251 CREATE PROCEDURE digitName(IN x INT)
252 BEGIN
253
254 DECLARE result VARCHAR(20);
255
256 CASE x
257 WHEN 0 THEN SET result = 'Zero';
258 WHEN 1 THEN SET result = 'One';
259 WHEN 2 THEN SET result = 'Two';
260 WHEN 3 THEN SET result = 'Three';
261 WHEN 4 THEN SET result = 'Four';
262 WHEN 5 THEN SET result = 'Five';
263 WHEN 6 THEN SET result = 'Six';
264 WHEN 7 THEN SET result = 'Seven';
265 WHEN 8 THEN SET result = 'Eight';
266 WHEN 9 THEN SET result = 'Nine';
267 ELSE SET result = 'Not a digit';
268 END CASE;
269
270 SELECT x AS Digit, result AS Name;
271
272 END;
273 //
274
275 CALL digitName(0) //
276 CALL digitName(4) //
277 CALL digitName(100) //
278
279 +-------+------+
280 | Digit | Name |
281 +-------+------+
282 | 0 | Zero |
283 +-------+------+
284 1 row in set (0.00 sec)
285
286 +-------+------+
287 | Digit | Name |
288 +-------+------+
289 | 4 | Four |
290 +-------+------+
291 1 row in set (0.00 sec)
292
293 +-------+-------------+
294 | Digit | Name |
295 +-------+-------------+
296 | 100 | Not a digit |
297 +-------+-------------+
298 1 row in set (0.00 sec)
299
300
301
302 --As you'd expect, we have loops. For example, WHILE loops:
303
304 DROP PROCEDURE IF EXISTS fact //
305 CREATE PROCEDURE fact(IN x INT)
306 BEGIN
307
308 DECLARE result INT;
309 DECLARE i INT;
310 SET result = 1;
311 SET i = 1;
312
313 WHILE i <= x DO
314 SET result = result * i;
315 SET i = i + 1;
316 END WHILE;
317
318 SELECT x AS Number, result as Factorial;
319
320 END;
321 //
322
323 CALL fact(1) //
324 CALL fact(2) //
325 CALL fact(4) //
326 CALL fact(0) //
327
328 +--------+-----------+
329 | Number | Factorial |
330 +--------+-----------+
331 | 1 | 1 |
332 +--------+-----------+
333 1 row in set (0.00 sec)
334
335 +--------+-----------+
336 | Number | Factorial |
337 +--------+-----------+
338 | 2 | 2 |
339 +--------+-----------+
340 1 row in set (0.00 sec)
341
342 +--------+-----------+
343 | Number | Factorial |
344 +--------+-----------+
345 | 4 | 24 |
346 +--------+-----------+
347 1 row in set (0.01 sec)
348
349 +--------+-----------+
350 | Number | Factorial |
351 +--------+-----------+
352 | 0 | 1 |
353 +--------+-----------+
354 1 row in set (0.00 sec)
355
356
357
358 --There is also REPEAT/UNTIL loops:
359
360 DROP PROCEDURE IF EXISTS fact //
361 CREATE PROCEDURE fact(IN x INT)
362 BEGIN
363
364 DECLARE result INT DEFAULT 1; /* notice you can declare a variable*/
365 DECLARE i INT DEFAULT 1; /* and give it a value in one line */
366
367 REPEAT
368 SET result = result * i;
369 SET i = i + 1;
370 UNTIL i > x
371 END REPEAT;
372
373 SELECT x AS Number, result as Factorial;
374
375 END;
376 //
377
378 CALL fact(1) //
379 CALL fact(2) //
380 CALL fact(4) //
381 CALL fact(0) //
382
383 +--------+-----------+
384 | Number | Factorial |
385 +--------+-----------+
386 | 1 | 1 |
387 +--------+-----------+
388 1 row in set (0.00 sec)
389
390 +--------+-----------+
391 | Number | Factorial |
392 +--------+-----------+
393 | 2 | 2 |
394 +--------+-----------+
395 1 row in set (0.00 sec)
396
397 +--------+-----------+
398 | Number | Factorial |
399 +--------+-----------+
400 | 4 | 24 |
401 +--------+-----------+
402 1 row in set (0.00 sec)
403
404 +--------+-----------+
405 | Number | Factorial |
406 +--------+-----------+
407 | 0 | 1 |
408 +--------+-----------+
409 1 row in set (0.00 sec)
410
411
412
413 --OK, do you remember this?
414 /*
415 CREATE PROCEDURE FindStudent(IN id INT)
416 SELECT StuID, CONCAT(FName, ' ', LName) AS 'Student Name'
417 FROM Student
418 WHERE StuID = id;
419 //
420 */
421
422 --What if we only want to extract the name without printing it out?
423 --Obviously, we need some OUT parameters. Still, how do you extract
424 --information into those OUT parameters?
425 --
426 --Answer: something called a CURSOR:
427
428 DROP PROCEDURE IF EXISTS FindName //
429 CREATE PROCEDURE FindName(IN id INT, OUT fn VARCHAR(20), OUT ln VARCHAR(20))
430 BEGIN
431 DECLARE cur CURSOR FOR
432 SELECT FName, LName
433 FROM Student
434 WHERE StuID = id;
435 OPEN cur;
436 FETCH cur INTO fn, ln;
437 CLOSE cur;
438 END;
439 //
440
441 CALL FindName(1001,@f,@l) //
442
443 Query OK, 0 rows affected (0.00 sec)
444
445 --Remember that @f and @l are MySQL variables:
446
447 SELECT @f //
448 SELECT @l //
449
450 +-------+
451 | @f |
452 +-------+
453 | Linda |
454 +-------+
455 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
1 --MySQL complains, as expected. It would be nice to handle this more elegantly, however.
2 --We need an error HANDLER. Let's modify FindName:
3
4 DROP PROCEDURE IF EXISTS FindName //
5 CREATE PROCEDURE FindName(IN id INT, OUT fn VARCHAR(20), OUT ln VARCHAR(20))
6 BEGIN
7 DECLARE cur CURSOR FOR
8 SELECT FName, LName
9 FROM Student
10 WHERE StuID = id;
11
12 DECLARE EXIT HANDLER FOR NOT FOUND
13 SELECT 'Sorry; this ID was not found' AS 'Error Message';
14
15 OPEN cur;
16 FETCH cur INTO fn, ln;
17 CLOSE cur;
18 END;
19 //
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:
1 DROP PROCEDURE IF EXISTS MaxMinAge //
2 CREATE PROCEDURE MaxMinAge(OUT maxAge INT, OUT minAge INT)
3 BEGIN
4 DECLARE currAge,maxSoFar,minSoFar,done INT;
5
6 DECLARE cur CURSOR FOR
7 SELECT Age
8 FROM Student;
9
10 DECLARE CONTINUE HANDLER FOR NOT FOUND
11 SET done = 1;
12
13 SET maxSoFar = 0;
14 SET minSoFar = 1000;
15 SET done = 0;
16
17 OPEN cur;
18 WHILE done = 0 DO
19 FETCH cur INTO currAge;
20 IF currAge > maxSoFar THEN
21 SET maxSoFar = currAge;
22 END IF;
23 IF currAge < minSoFar THEN
24 SET minSoFar = currAge;
25 END IF;
26 END WHILE;
27
28 CLOSE cur;
29
30 SET maxAge = maxSoFar;
31 SET minAge = minSoFar;
32
33 END;
34 //
35
36 CALL MaxMinAge(@max,@min) //
Query OK, 0 rows affected (0.00 sec)
1 SELECT @max // 2 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:
1 DROP PROCEDURE IF EXISTS procName //
2 CREATE PROCEDURE procName(parameter list)
3 BEGIN
4 /* variable declarations */
5 /* CURSOR definitions */
6 /* declaring handlers */
7
8 /* procedure body...whatever you want it to do */
9
10 END;
11 //
--In more detail:
1 DROP PROCEDURE IF EXISTS procName //
2 CREATE PROCEDURE procName(IN/OUT/INOUT parName parType, ...)
3 BEGIN
4 /* variable declarations */
5 DECLARE varName,... varType;
6 /* e.g. DECLARE myName VARCHAR(20); DECLARE x,y,z INT; */
7
8 DECLARE varName varType DEFAULT value;
9 /* e.g. DECLARE x INT DEFAULT 0; */
10
11 /* CURSOR definitions */
12 DECLARE curName CURSOR FOR
13 SELECT ...
14
15 /* e.g. DECLARE cur1 CURSOR FOR
16 SELECT FName, LName
17 FROM Student; */
18
19
20 /* declaring handlers */
21 DECLARE EXIT/CONTINUE HANDLER FOR errorType/errorNumber
22 ... action ...
23
24 /* e.g. DECLARE EXIT HANDLER FOR NOT FOUND
25 SELECT 'Sorry; this ID was not found' AS 'Error Message'; */
26
27 /* e.g. DECLARE CONTINUE HANDLER FOR NOT FOUND
28 SET done = 1; */
29
30
31 /* procedure body...whatever you want it to do */
32
33 /* IF statement */
34 IF cond1 THEN
35 action1
36 ELSEIF cond2 THEN
37 action2
38 ELSEIF cond3 THEN
39 action3
40 ELSE
41 elseaction
42 END IF;
43
44 /* e.g. IF x > 0 THEN
45 SET result = '+';
46 ELSEIF x < 0 THEN
47 SET result = '-';
48 ELSE
49 SET result = 'Zero';
50 END IF; */
51
52 /* CASE statement */
53 CASE varName
54 WHEN val1 THEN action1
55 WHEN val2 THEN action2
56 ELSE elseaction
57 END CASE;
58
59 /* e.g. CASE position
60 WHEN 1 THEN SET result = 'Gold Medal';
61 WHEN 2 THEN SET result = 'Silver Medal';
62 WHEN 3 THEN SET result = 'Bronze Medal';
63 ELSE SET result = 'No Medal!';
64 END CASE; */
65
66 /* WHILE loop */
67 WHILE cond DO
68 action1
69 action2
70 ...
71 END WHILE
72
73 /* e.g. WHILE i < 5 DO
74 SET result = result + i;
75 SET i = i + 1;
76 END WHILE; */
77
78 /* REPEAT/UNTIL loop */
79
80 REPEAT
81 action1
82 action2
83 ...
84 UNTIL cond
85 END REPEAT;
86
87 /* e.g. REPEAT
88 SET result = result + i;
89 SET i = i + 1;
90 UNTIL i >= 5
91 END REPEAT; */
92
93
94
95 /* using a CURSOR */
96 OPEN curName;
97 .
98 .
99 FETCH curName INTO var1, var2, ...;
100 .
101 .
102 CLOSE curName;
103
104 /* e.g. Assume cur1 has id's, first names, and last names
105 let's find the name of the student whose StuID is x:
106
107 OPEN cur1;
108
109 SET found = 0;
110 WHILE found = 0 DO
111
112 FETCH cur1 INTO nextID, nextFName, nextLName;
113
114 IF nextID = x THEN
115 SET result = CONCAT(nextFName, ' ', nextLName);
116 SET found = 1;
117 END IF;
118
119 END WHILE;
120
121 CLOSE cur1; */
122
123
124 END;
125 //
原文链接http://www.cs.jhu.edu/~nikhil/proc_examples.txt
本博客文章皆出于学习目的,个人总结或摘抄整理自网络。引用参考部分在文章中都有原文链接,如疏忽未给出请联系本人。另外,作为一名菜鸟程序媛,如文章内容有错误,欢迎点击博客右上方的扣扣链接指导交流。