mysql 学习---->存储过程

1. mysql> use test1;  
2. Reading table information for completion of table and column names  
3. You can turn off this feature to get a quicker startup with -A  
4.   
5. Database change  
6.   
7. mysql> select * from emp;  
8. +------------+----------+------+--------+  
9. | ename      | hiredate | sal  | deptno |  
10. +------------+----------+------+--------+  
11. | aaaaa      | NULL     | NULL |      1 |  
12. | cccccccccc | NULL     | NULL |      2 |  
13. | ddddddddd  | NULL     | NULL |      3 |  
14. | ffffff     | NULL     | NULL |      4 |  
15. | ggg        | NULL     | NULL |      5 |  
16. | a1         | NULL     | NULL |      5 |  
17. +------------+----------+------+--------+  
18. 6 rows in set (0.00 sec)  
19.   
20. mysql> show create table emp \G;  
21. *************************** 1. row ***************************  
22.        Table: emp  
23. Create Table: CREATE TABLE `emp` (  
24.   `ename` varchar(10) DEFAULT NULL,  
25.   `hiredate` date DEFAULT NULL,  
26.   `sal` decimal(10,2) DEFAULT NULL,  
27.   `deptno` int(2) DEFAULT NULL  
28. ) ENGINE=InnoDB DEFAULT CHARSET=utf8  
29. 1 row in set (0.01 sec)  
30.   
31. ERROR:   
32. No query specified  
33.   
34. mysql> DELIMITER &&    
35. mysql> CREATE  PROCEDURE  num_from_employee (IN input_deptno int, OUT count_num INT )    
36.     -> READS SQL DATA    
37.     ->     BEGIN    
38.     ->     SELECT  COUNT(*) FROM emp WHERE  deptno=input_deptno ;    
39.     -> END  &&  
40. Query OK, 0 rows affected (0.01 sec)  
41.   
42. mysql> delimiter ;  
43.   
44. mysql> call num_from_employee(5,@a);  
45. +----------+  
46. | COUNT(*) |  
47. +----------+  
48. |        2 |  
49. +----------+  
50. 1 row in set (0.02 sec)  
51.   
52. Query OK, 0 rows affected (0.02 sec)  
53.   
54. mysql> call num_from_employee(1,@a);  
55. +----------+  
56. | COUNT(*) |  
57. +----------+  
58. |        1 |  
59. +----------+  
60. 1 row in set (0.01 sec)  
61.   
62. Query OK, 0 rows affected (0.01 sec)  
63.   
64. mysql> create table inventory(  
65.     -> film_id int(11),  
66.     -> store_id int(11),  
67.     -> inventory_in_stock varchar(50)  
68.     -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;  
69. Query OK, 0 rows affected (0.02 sec)  
70.   
71. mysql> insert into inventory(film_id,store_id,inventory_in_stock) values  (1,2,'aaaaaaaa'), (3,4,'bbbb'), (5,6,'cccccccccc'), (7,8,'dddddd'), (9,10,'fff');  
72. Query OK, 5 rows affected (0.00 sec)  
73. Records: 5  Duplicates: 0  Warnings: 0  
74.    
75. mysql> select * from inventory;  
76. +---------+----------+--------------------+  
77. | film_id | store_id | inventory_in_stock |  
78. +---------+----------+--------------------+  
79. |       1 |        2 | aaaaaaaa           |  
80. |       3 |        4 | bbbb               |  
81. |       5 |        6 | cccccccccc         |  
82. |       7 |        8 | dddddd             |  
83. |       9 |       10 | fff                |  
84. +---------+----------+--------------------+  
85. 5 rows in set (0.00 sec)  
86.   
87. mysql> delimiter $$  
88. mysql> create procedure film_in_stock(in p_film_id int,in p_store_id int,out p_film_count int)   
89.     -> reads sql data  
90.     -> begin  
91.     ->   select film_id  
92.     ->   from inventory  
93.     ->   where film_id = p_film_id  
94.     ->   and store_id = p_store_id;  
95.     ->   select found_rows() into p_film_count;  
96.     -> end $$  
97. Query OK, 0 rows affected (0.01 sec)  
98.   
99. mysql> delimiter ;  
100. mysql> call film_in_stock(5,6,@a);  
101. +---------+  
102. | film_id |  
103. +---------+  
104. |       5 |  
105. +---------+  
106. 1 row in set (0.01 sec)  
107.   
108. Query OK, 1 row affected (0.01 sec)  
109.   
110. mysql> show create procedure film_in_stock \G;  
111. *************************** 1. row ***************************  
112.            Procedure: film_in_stock  
113.             sql_mode:   
114.     Create Procedure: CREATE DEFINER=`root`@`localhost` PROCEDURE `film_in_stock`(in p_film_id int,in p_store_id int,out p_film_count int)  
115.     READS SQL DATA  
116. begin  
117.   select film_id  
118.   from inventory  
119.   where film_id = p_film_id  
120.   and store_id = p_store_id;  
121.   select found_rows() into p_film_count;  
122. end  
123. character_set_client: utf8  
124. collation_connection: utf8_general_ci  
125.   Database Collation: utf8_general_ci  
126. 1 row in set (0.01 sec)  
127.   
128. ERROR:   
129. No query specified  
130.   
131. mysql> create table actor(  
132.     -> actor_id int(11)  NOT NULL AUTO_INCREMENT ,    
133.     -> first_name varchar(30),  
134.     -> last_name varchar(30),  
135.     ->   PRIMARY KEY (actor_id)    
136.     -> ) engine = innodb charset = utf8;  
137. Query OK, 0 rows affected (0.02 sec)  
138.   
139.   
140. mysql> delimiter $$  
141. mysql> create procedure actor_insert()  
142.     -> begin   
143.     -> set @x = 1;  
144.     -> insert into actor(actor_id,first_name,last_name) values (201,'Test',201);  
145.     -> set @x = 2;  
146.     -> insert into actor(actor_id,first_name,last_name) values(1,'Test','1');  
147.     -> set @x = 3;  
148.     -> end $$  
149. Query OK, 0 rows affected (0.01 sec)  
150.   
151. mysql> call actor_insert();  
152. Query OK, 0 rows affected (0.02 sec)  
153.   
154. mysql> call actor_insert();  
155. ERROR 1062 (23000): Duplicate entry '201' for key 'PRIMARY'  
156. mysql> select @x;  
157. +------+  
158. | @x   |  
159. +------+  
160. |    1 |  
161. +------+  
162. 1 row in set (0.00 sec)  
163.   
164. mysql> delimiter $$  
165. mysql> create procedure actor_insert_new()  
166.     -> begin   
167.     -> DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET @x2 = 1;  
168.     -> set @x = 1;  
169.     -> insert into actor(actor_id,first_name,last_name) values (201,'Test',201);  
170.     -> set @x = 2;  
171.     -> insert into actor(actor_id,first_name,last_name) values(1,'Test','1');  
172.     -> set @x = 3;  
173.     -> end $$  
174. Query OK, 0 rows affected (0.02 sec)  
175.   
176. mysql> delimiter ;  
177.   
178. mysql> call actor_insert_new();  
179. Query OK, 0 rows affected, 1 warning (0.01 sec)  
180.   
181. mysql> call actor_insert_new();  
182. Query OK, 0 rows affected, 1 warning (0.00 sec)  
183.   
184. mysql> select @x,@x2;  
185. +------+------+  
186. | @x   | @x2  |  
187. +------+------+  
188. |    3 |    1 |  
189. +------+------+  
190. 1 row in set (0.00 sec)  
191.   
192. mysql> show create table payment \G;  
193. *************************** 1. row ***************************  
194.        Table: payment  
195. Create Table: CREATE TABLE `payment` (  
196.   `staff_id` int(11) DEFAULT NULL,  
197.   `amount` int(11) DEFAULT NULL  
198. ) ENGINE=InnoDB DEFAULT CHARSET=utf8  
199. 1 row in set (0.01 sec)  
200.   
201. ERROR:   
202. No query specified  
203.   
204. mysql> select * from payment;  
205. +----------+--------+  
206. | staff_id | amount |  
207. +----------+--------+  
208. |        1 |  10000 |  
209. |        2 |  20000 |  
210. |        3 |  30000 |  
211. |        4 | 400000 |  
212. |        5 | 500000 |  
213. +----------+--------+  
214. 5 rows in set (0.01 sec)  
215.   
216. mysql> delimiter $$  
217. mysql> create procedure payment_stat()  
218.     -> begin  
219.     -> DECLARE i_staff_id int;  
220.     -> DECLARE d_amount int;  
221.     -> declare tmp_name varchar(30) default "";  
222.     -> DECLARE cur_payment cursor for select staff_id,amount from payment;  
223.     -> DECLARE EXIT HANDLER FOR NOT FOUND CLOSE cur_payment;  
224.     ->   
225.     -> set @x1 = 0 ;  
226.     -> set @x2 = 0 ;  
227.     ->   
228.     -> open cur_payment;  
229.     -> fetch cur_payment into i_staff_id,d_amount;  
230.     -> while(i_staff_id <=3  )  
231.     -> do  
232.     ->     if i_staff_id < 3 then   
233.     ->         select i_staff_id,d_amount;  
234.     ->     end if;  
235.     -> fetch cur_payment into  i_staff_id,d_amount;  
236.     -> end while;  
237.     -> close cur_payment;  
238.     ->   
239.     -> select @x1,@x2;  
240.     -> end;  
241.     -> $$  
242. Query OK, 0 rows affected (0.01 sec)  
243.   
244. mysql> delimiter ;  
245.   
246. mysql> call  payment_stat();  
247. +------------+----------+  
248. | i_staff_id | d_amount |  
249. +------------+----------+  
250. |          1 |    10000 |  
251. +------------+----------+  
252. 1 row in set (0.01 sec)  
253.   
254. +------------+----------+  
255. | i_staff_id | d_amount |  
256. +------------+----------+  
257. |          2 |    20000 |  
258. +------------+----------+  
259. 1 row in set (0.01 sec)  
260.   
261. +------+------+  
262. | @x1  | @x2  |  
263. +------+------+  
264. |    0 |    0 |  
265. +------+------+  
266. 1 row in set (0.01 sec)  
267.   
268. Query OK, 0 rows affected (0.01 sec)  
269.   
270. mysql> drop  procedure payment_stat;  
271. Query OK, 0 rows affected (0.00 sec)  
272.   
273.   
274. mysql> delimiter $$  
275. mysql> create procedure payment_stat()  
276.     -> begin  
277.     -> DECLARE i_staff_id int;  
278.     -> DECLARE d_amount int;  
279.     -> declare tmp_name varchar(30) default "";  
280.     -> DECLARE cur_payment cursor for select staff_id,amount from payment;  
281.     -> DECLARE EXIT HANDLER FOR NOT FOUND CLOSE cur_payment;  
282.     ->   
283.     -> set @x1 = 0 ;  
284.     -> set @x2 = 0 ;  
285.     ->   
286.     -> open cur_payment;  
287.     -> fetch cur_payment into i_staff_id,d_amount;  
288.     -> while(i_staff_id <=3  )  
289.     -> do  
290.     ->     if i_staff_id < 3 then   
291.     ->         set @x1 = @x1+ i_staff_id;  
292.     ->     else  
293.     ->         set @x2 = @x2+ d_amount;  
294.     ->     end if;  
295.     -> fetch cur_payment into  i_staff_id,d_amount;  
296.     -> end while;  
297.     -> close cur_payment;  
298.     ->   
299.     -> select @x1,@x2;  
300.     -> end;  
301.     -> $$  
302. Query OK, 0 rows affected (0.01 sec)  
303.   
304. mysql> call  payment_stat();  
305.     -> $$  
306. +------+-------+  
307. | @x1  | @x2   |  
308. +------+-------+  
309. |    3 | 30000 |  
310. +------+-------+  
311. 1 row in set (0.01 sec)  
312.   
313. Query OK, 0 rows affected (0.01 sec)  
314.   
315.   
316. mysql> delimiter $$  
317. mysql> create procedure payment_stat()  
318.     -> begin  
319.     -> DECLARE i_staff_id int;  
320.     -> DECLARE d_amount int;  
321.     ->   
322.     -> DECLARE cur_payment cursor for select staff_id,amount from payment;  
323.     -> DECLARE EXIT HANDLER FOR NOT FOUND CLOSE cur_payment;  
324.     ->   
325.     -> set @x1 = 0 ;  
326.     -> set @x2 = 0 ;  
327.     ->   
328.     -> open cur_payment;  
329.     -> fetch cur_payment into i_staff_id,d_amount;  
330.     -> while(i_staff_id <=3  )  
331.     -> do  
332.     ->     if i_staff_id < 3 then   
333.     ->         set @x1 = @x1+ i_staff_id + 1;  
334.     ->     else  
335.     ->         set @x2 = @x2+ d_amount ;  
336.     ->     end if;  
337.     -> fetch cur_payment into  i_staff_id,d_amount;  
338.     -> end while;  
339.     -> close cur_payment;  
340.     ->   
341.     -> select @x1,@x2;  
342.     -> end;  
343.     -> $$  
344. Query OK, 0 rows affected (0.00 sec)  
345.   
346. mysql> delimiter ;  
347. mysql> call  payment_stat();  
348. +------+-------+  
349. | @x1  | @x2   |  
350. +------+-------+  
351. |    3 | 30000 |  
352. +------+-------+  
353. 1 row in set (0.00 sec)  
354.   
355. Query OK, 0 rows affected (0.00 sec)  
356.   
357. mysql> drop  procedure payment_stat;  
358. Query OK, 0 rows affected (0.00 sec)  
359.   
360.   
361. mysql> delimiter $$  
362. mysql> create procedure payment_stat()  
363.     -> begin  
364.     -> DECLARE i_staff_id int;  
365.     -> DECLARE d_amount int;  
366.     ->   
367.     -> DECLARE cur_payment cursor for select staff_id,amount from payment;  
368.     -> DECLARE EXIT HANDLER FOR NOT FOUND CLOSE cur_payment;  
369.     ->   
370.     -> set @x1 = 0 ;  
371.     -> set @x2 = 0 ;  
372.     ->   
373.     -> open cur_payment;  
374.     -> fetch cur_payment into i_staff_id,d_amount;  
375.     -> while(i_staff_id <=3  )  
376.     -> do  
377.     ->     if i_staff_id < 3 then   
378.     ->         set @x1 = @x1+ i_staff_id;  
379.     ->     else  
380.     ->         set @x2 = @x2+ d_amount;  
381.     ->     end if;  
382.     -> fetch cur_payment into  i_staff_id,d_amount;  
383.     -> end while;  
384.     -> close cur_payment;  
385.     ->   
386.     -> select @x1,@x2;  
387.     -> end;  
388.     -> $$  
389. Query OK, 0 rows affected (0.00 sec)  
390.   
391.   
392. mysql> delimiter ;  
393. mysql> call  payment_stat();  
394. +------+-------+  
395. | @x1  | @x2   |  
396. +------+-------+  
397. |    3 | 30000 |  
398. +------+-------+  
399. 1 row in set (0.00 sec)  
400.   
401. Query OK, 0 rows affected (0.00 sec)  
402.   
403. mysql> delimiter $$  
404. mysql> create procedure payment_stat()  
405.     -> begin  
406.     -> DECLARE i_staff_id int;  
407.     -> DECLARE d_amount int;  
408.     ->   
409.     -> DECLARE cur_payment cursor for select staff_id,amount from payment;  
410.     -> DECLARE EXIT HANDLER FOR NOT FOUND CLOSE cur_payment;  
411.     ->   
412.     -> set @x1 = 0 ;  
413.     -> set @x2 = 0 ;  
414.     ->   
415.     -> open cur_payment;  
416.     -> fetch cur_payment into i_staff_id,d_amount;  
417.     -> while(i_staff_id <=3  )  
418.     -> do  
419.     ->     if i_staff_id < 3 then   
420.     ->         set @x1 = @x1+ i_staff_id + 1;  
421.     ->     else  
422.     ->         set @x2 = @x2+ d_amount;  
423.     ->     end if;  
424.     -> fetch cur_payment into  i_staff_id,d_amount;  
425.     -> end while;  
426.     -> close cur_payment;  
427.     ->   
428.     -> select @x1,@x2;  
429.     -> end;  
430.     -> $$  
431. Query OK, 0 rows affected (0.01 sec)  
432.   
433. mysql> delimiter ;  
434. mysql> call  payment_stat();  
435. +------+-------+  
436. | @x1  | @x2   |  
437. +------+-------+  
438. |    5 | 30000 |  
439. +------+-------+  
440. 1 row in set (0.00 sec)  
441.   
442. Query OK, 0 rows affected (0.00 sec)  
443.   
444. mysql> drop  procedure payment_stat;  
445. Query OK, 0 rows affected (0.02 sec)  
446.   
447.   
448. mysql> delimiter $$  
449. mysql> create procedure payment_stat()  
450.     -> begin  
451.     -> DECLARE i_staff_id int;  
452.     -> DECLARE d_amount int;  
453.     ->   
454.     -> DECLARE cur_payment cursor for select staff_id,amount from payment;  
455.     -> DECLARE EXIT HANDLER FOR NOT FOUND CLOSE cur_payment;  
456.     ->   
457.     -> set @x1 = 0 ;  
458.     -> set @x2 = 0 ;  
459.     ->   
460.     -> open cur_payment;  
461.     -> fetch cur_payment into i_staff_id,d_amount;  
462.     -> while(i_staff_id <=3  )  
463.     -> do  
464.     ->     if i_staff_id < 3 then   
465.     ->         set @x1 = @x1+ i_staff_id + 6;  
466.     ->     else  
467.     ->         set @x2 = @x2+ d_amount + 5;  
468.     ->     end if;  
469.     -> fetch cur_payment into  i_staff_id,d_amount;  
470.     -> end while;  
471.     -> close cur_payment;  
472.     ->   
473.     -> select @x1,@x2;  
474.     -> end;  
475.     -> $$  
476. Query OK, 0 rows affected (0.01 sec)  
477.   
478. mysql> delimiter ;  
479.   
480. mysql> call  payment_stat();  
481.      
482. +------+-------+  
483. | @x1  | @x2   |  
484. +------+-------+  
485. |   15 | 30005 |  
486. +------+-------+  
487. 1 row in set (0.00 sec)  
488.   
489. Query OK, 0 rows affected (0.00 sec)  
490.   
491. mysql> DELIMITER $$  
492. mysql>   
493. mysql> CREATE PROCEDURE addNum()  
494.     -> BEGIN  
495.     -> DECLARE x  INT;  
496.     -> SET x = 0;  
497.     -> for_loop :  LOOP  
498.     ->   SET  x = x + 1;  
499.     ->   IF  x > 30 THEN  
500.     ->    LEAVE  for_loop;  
501.     ->   END IF;  
502.     ->   IF mod(x,2) = 0 then   
503.     ->     select "num:",x;  
504.     ->   ITERATE for_loop;  
505.     ->   END IF;  
506.     -> END LOOP;  
507.     -> select "count:",x;  
508.     -> END $$  
509. Query OK, 0 rows affected (0.01 sec)  
510.   
511. mysql> call addNum();  
512.     -> $$  
513. +------+------+  
514. | num: | x    |  
515. +------+------+  
516. | num: |    2 |  
517. +------+------+  
518. 1 row in set (0.00 sec)  
519.   
520. +------+------+  
521. | num: | x    |  
522. +------+------+  
523. | num: |    4 |  
524. +------+------+  
525. 1 row in set (0.00 sec)  
526.   
527. +------+------+  
528. | num: | x    |  
529. +------+------+  
530. | num: |    6 |  
531. +------+------+  
532. 1 row in set (0.00 sec)  
533.   
534. +------+------+  
535. | num: | x    |  
536. +------+------+  
537. | num: |    8 |  
538. +------+------+  
539. 1 row in set (0.00 sec)  
540.   
541. +------+------+  
542. | num: | x    |  
543. +------+------+  
544. | num: |   10 |  
545. +------+------+  
546. 1 row in set (0.00 sec)  
547.   
548. +------+------+  
549. | num: | x    |  
550. +------+------+  
551. | num: |   12 |  
552. +------+------+  
553. 1 row in set (0.00 sec)  
554.   
555. +------+------+  
556. | num: | x    |  
557. +------+------+  
558. | num: |   14 |  
559. +------+------+  
560. 1 row in set (0.00 sec)  
561.   
562. +------+------+  
563. | num: | x    |  
564. +------+------+  
565. | num: |   16 |  
566. +------+------+  
567. 1 row in set (0.00 sec)  
568.   
569. +------+------+  
570. | num: | x    |  
571. +------+------+  
572. | num: |   18 |  
573. +------+------+  
574. 1 row in set (0.00 sec)  
575.   
576. +------+------+  
577. | num: | x    |  
578. +------+------+  
579. | num: |   20 |  
580. +------+------+  
581. 1 row in set (0.00 sec)  
582.   
583. +------+------+  
584. | num: | x    |  
585. +------+------+  
586. | num: |   22 |  
587. +------+------+  
588. 1 row in set (0.00 sec)  
589.   
590. +------+------+  
591. | num: | x    |  
592. +------+------+  
593. | num: |   24 |  
594. +------+------+  
595. 1 row in set (0.00 sec)  
596.   
597. +------+------+  
598. | num: | x    |  
599. +------+------+  
600. | num: |   26 |  
601. +------+------+  
602. 1 row in set (0.00 sec)  
603.   
604. +------+------+  
605. | num: | x    |  
606. +------+------+  
607. | num: |   28 |  
608. +------+------+  
609. 1 row in set (0.00 sec)  
610.   
611. +------+------+  
612. | num: | x    |  
613. +------+------+  
614. | num: |   30 |  
615. +------+------+  
616. 1 row in set (0.00 sec)  
617.   
618. +--------+------+  
619. | count: | x    |  
620. +--------+------+  
621. | count: |   31 |  
622. +--------+------+  
623. 1 row in set (0.00 sec)  
624.   
625. Query OK, 0 rows affected (0.00 sec)  
626.   
627. mysql> delimiter $$  
628. mysql> create procedure repeatPractise()  
629.     ->      begin  
630.     ->      set @v = 0 ;  
631.     ->      REPEAT   
632.     ->         set @v =  @v+ 1;  
633.     ->      UNTIL @v >=5    
634.     ->      END REPEAT;  
635.     ->  END   
636.     ->  $$  
637. Query OK, 0 rows affected (0.01 sec)  
638.   
639. mysql> call repeatPractise();  
640.     -> $$  
641. Query OK, 0 rows affected (0.00 sec)  
642.   
643. mysql> select @v;  
644.     -> $$  
645. +------+  
646. | @v   |  
647. +------+  
648. |    5 |  
649. +------+  
650. 1 row in set (0.00 sec)