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;
/*聲明結(jié)束符為$*/
DELIMITER $
/*創(chuàng)建函數(shù)，對(duì)符合條件的每個(gè)同學(xué)的分?jǐn)?shù)進(jìn)行加分，加的分?jǐn)?shù)不能超過(guò)給定的值max_score*/
CREATE FUNCTION fun_test(max_score decimal(10,2))
RETURNS int
BEGIN
/*定義實(shí)時(shí)StudentId的變量*/
DECLARE var_studentId int DEFAULT 0;
/*定義計(jì)算后分?jǐn)?shù)的變量*/
DECLARE var_score decimal(10,2) DEFAULT 0;
/*定義游標(biāo)結(jié)束標(biāo)志變量*/
DECLARE var_done int DEFAULT FALSE;
/*創(chuàng)建游標(biāo)*/
DECLARE cur_test CURSOR FOR SELECT studentid,score from students where classid>0;
/*游標(biāo)結(jié)束時(shí)會(huì)設(shè)置var_done為true，后續(xù)可以使用var_done來(lái)判斷游標(biāo)是否結(jié)束*/
DECLARE CONTINUE HANDLER FOR NOT FOUND SET var_done=TRUE;
/*打開(kāi)游標(biāo)*/
OPEN cur_test;
/*使用Loop循環(huán)遍歷游標(biāo)*/
select_loop:LOOP
/*先獲取當(dāng)前行的數(shù)據(jù)，然后將當(dāng)前行的數(shù)據(jù)放入var_studentId,var_score中，如果無(wú)數(shù)據(jù)行了，var_done會(huì)被置為true*/
FETCH cur_test INTO var_studentId,var_score;
/*通過(guò)var_done來(lái)判斷游標(biāo)是否結(jié)束了，退出循環(huán)*/
IF var_done THEN
LEAVE select_loop;
END IF;
/*對(duì)var_score值添加隨機(jī)值，不能超過(guò)給定的分?jǐn)?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;
/*關(guān)閉游標(biāo)*/
CLOSE cur_test;
/*返回結(jié)果:可以根據(jù)實(shí)際情況返回需要的內(nèi)容*/
RETURN 1;
END $
/*結(jié)束符置為;*/
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ā)器，當(dāng)修改students表的時(shí)候觸發(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