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>
/*判断函数如果存在则删除*/
DROP FUNCTION IF EXISTS fun_test;
/*声明结束符为$*/
DELIMITER $
/*创建函数，对符合条件的每个同学的分数进行加分，加的分数不能超过给定的值max_score*/
CREATE FUNCTION fun_test(max_score decimal(10,2))
RETURNS int
BEGIN
/*定义实时StudentId的变量*/
DECLARE var_studentId int DEFAULT 0;
/*定义计算后分数的变量*/
DECLARE var_score decimal(10,2) DEFAULT 0;
/*定义游标结束标志变量*/
DECLARE var_done int DEFAULT FALSE;
/*创建游标*/
DECLARE cur_test CURSOR FOR SELECT studentid,score from students where classid<>0;
/*游标结束时会设置var_done为true，后续可以使用var_done来判断游标是否结束*/
DECLARE CONTINUE HANDLER FOR NOT FOUND SET var_done=TRUE;
/*打开游标*/
OPEN cur_test;
/*使用Loop循环遍历游标*/
select_loop:LOOP
/*先获取当前行的数据，然后将当前行的数据放入var_studentId,var_score中，如果无数据行了，var_done会被置为true*/
FETCH cur_test INTO var_studentId,var_score;
/*通过var_done来判断游标是否结束了，退出循环*/
IF var_done THEN
LEAVE select_loop;
END IF;
/*对var_score值添加随机值，不能超过给定的分数*/
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;
/*返回结果:可以根据实际情况返回需要的内容*/
RETURN 1;
END $
/*结束符置为;*/
DELIMITER ;
Query OK, 0 rows affected

mysql>
/* 参数为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>
/*上一篇编写了触发器，当修改students表的时候触发日志记录 */
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