MySQL 游標的作用與使用相關

select studentid,studentname,score from students; 

DECLARE cursor_name CURSOR FOR select_statement; 

OPEN cursor_name; 

FETCH cursor_name INTO var_list;

CLOSE cursor_name; 

mysql> select * from students;
+-----------+-------------+-------+---------+
| studentid | studentname | score | classid |
+-----------+-------------+-------+---------+
| 1 | brand | 97.5 | 1 |
| 2 | helen | 96.5 | 1 |
| 3 | lyn | 96 | 1 |
| 4 | sol | 97 | 1 |
| 5 | b1 | 81 | 2 |
| 6 | b2 | 82 | 2 |
| 7 | c1 | 71 | 3 |
| 8 | c2 | 72.5 | 3 |
| 9 | lala | 73 | 0 |
| 10 | A | 99 | 3 |
| 16 | test1 | 100 | 0 |
| 17 | trigger2 | 107 | 0 |
| 22 | trigger1 | 100 | 0 |
+-----------+-------------+-------+---------+
13 rows in set

mysql>
/*判斷函數(shù)如果存在則刪除*/
DROP FUNCTION IF EXISTS fun_test;
/*聲明結束符為$*/
DELIMITER $
/*創(chuàng)建函數(shù)，對符合條件的每個同學的分數(shù)進行加分，加的分數(shù)不能超過給定的值max_score*/
CREATE FUNCTION fun_test(max_score decimal(10,2))
RETURNS int
BEGIN
/*定義實時StudentId的變量*/
DECLARE var_studentId int DEFAULT 0;
/*定義計算后分數(shù)的變量*/
DECLARE var_score decimal(10,2) DEFAULT 0;
/*定義游標結束標志變量*/
DECLARE var_done int DEFAULT FALSE;
/*創(chuàng)建游標*/
DECLARE cur_test CURSOR FOR SELECT studentid,score from students where classid>0;
/*游標結束時會設置var_done為true，后續(xù)可以使用var_done來判斷游標是否結束*/
DECLARE CONTINUE HANDLER FOR NOT FOUND SET var_done=TRUE;
/*打開游標*/
OPEN cur_test;
/*使用Loop循環(huán)遍歷游標*/
select_loop:LOOP
/*先獲取當前行的數(shù)據(jù)，然后將當前行的數(shù)據(jù)放入var_studentId,var_score中，如果無數(shù)據(jù)行了，var_done會被置為true*/
FETCH cur_test INTO var_studentId,var_score;
/*通過var_done來判斷游標是否結束了，退出循環(huán)*/
IF var_done THEN
LEAVE select_loop;
END IF;
/*對var_score值添加隨機值，不能超過給定的分數(shù)*/
set var_score = var_score + LEAST(ROUND(rand()*10,0),max_score);
update students set score = var_score where studentId= var_studentId;
END LOOP;
/*關閉游標*/
CLOSE cur_test;
/*返回結果:可以根據(jù)實際情況返回需要的內(nèi)容*/
RETURN 1;
END $
/*結束符置為;*/
DELIMITER ;
Query OK, 0 rows affected

mysql>
/* 參數(shù)為8，表示加分上限為8 */
select fun_test(8);
+-------------+
| fun_test(8) |
+-------------+
| 1 |
+-------------+
1 row in set

mysql> select * from students;
+-----------+-------------+-------+---------+
| studentid | studentname | score | classid |
+-----------+-------------+-------+---------+
| 1 | brand | 105.5 | 1 |
| 2 | helen | 98.5 | 1 |
| 3 | lyn | 97 | 1 |
| 4 | sol | 97 | 1 |
| 5 | b1 | 89 | 2 |
| 6 | b2 | 90 | 2 |
| 7 | c1 | 76 | 3 |
| 8 | c2 | 73.5 | 3 |
| 9 | lala | 73 | 0 |
| 10 | A | 100 | 3 |
| 16 | test1 | 100 | 0 |
| 17 | trigger2 | 107 | 0 |
| 22 | trigger1 | 100 | 0 |
+-----------+-------------+-------+---------+
13 rows in set

mysql>
/*上一篇編寫了觸發(fā)器，當修改students表的時候觸發(fā)日志記錄 */
select * from triggerlog;
+----+--------------+---------------+-----------------------------------------+
| id | trigger_time | trigger_event | memo |
+----+--------------+---------------+-----------------------------------------+
| 1 | after | insert | new student info,id:21 |
| 2 | after | update | update student info,id:21 |
| 3 | after | update | delete student info,id:21 |
| 4 | after | update | from:test2,101.00 to:trigger2,106.00 |
| 5 | after | update | from:trigger2,106.00 to:trigger2,107.00 |
| 6 | after | update | delete student info,id:11 |
| 7 | after | update | from:brand,97.50 to:brand,105.50 |
| 8 | after | update | from:helen,96.50 to:helen,98.50 |
| 9 | after | update | from:lyn,96.00 to:lyn,97.00 |
| 10 | after | update | from:sol,97.00 to:sol,97.00 |
| 11 | after | update | from:b1,81.00 to:b1,89.00 |
| 12 | after | update | from:b2,82.00 to:b2,90.00 |
| 13 | after | update | from:c1,71.00 to:c1,76.00 |
| 14 | after | update | from:c2,72.50 to:c2,73.50 |
| 15 | after | update | from:A,99.00 to:A,100.00 |
+----+--------------+---------------+-----------------------------------------+
15 rows in set