当前位置 博文首页 > 翁智华:MySQL全面瓦解19:游标相关
1 select studentid,studentname,score from students;
1 DECLARE cursor_name CURSOR FOR select_statement;
1 OPEN cursor_name;
1 FETCH cursor_name INTO var_list;
1 CLOSE cursor_name;
1 mysql> select * from students; 2 +-----------+-------------+-------+---------+ 3 | studentid | studentname | score | classid | 4 +-----------+-------------+-------+---------+ 5 | 1 | brand | 97.5 | 1 | 6 | 2 | helen | 96.5 | 1 | 7 | 3 | lyn | 96 | 1 | 8 | 4 | sol | 97 | 1 | 9 | 5 | b1 | 81 | 2 | 10 | 6 | b2 | 82 | 2 | 11 | 7 | c1 | 71 | 3 | 12 | 8 | c2 | 72.5 | 3 | 13 | 9 | lala | 73 | 0 | 14 | 10 | A | 99 | 3 | 15 | 16 | test1 | 100 | 0 | 16 | 17 | trigger2 | 107 | 0 | 17 | 22 | trigger1 | 100 | 0 | 18 +-----------+-------------+-------+---------+ 19 13 rows in set
这边注释很清晰,关键知识点都已经标红
1 mysql> 2 /*判断函数如果存在则删除*/ 3 DROP FUNCTION IF EXISTS fun_test; 4 /*声明结束符为$*/ 5 DELIMITER $ 6 /*创建函数,对符合条件的每个同学的分数进行加分,加的分数不能超过给定的值max_score*/ 7 CREATE FUNCTION fun_test(max_score decimal(10,2)) 8 RETURNS int 9 BEGIN 10 /*定义实时StudentId的变量*/ 11 DECLARE var_studentId int DEFAULT 0; 12 /*定义计算后分数的变量*/ 13 DECLARE var_score decimal(10,2) DEFAULT 0; 14 /*定义游标结束标志变量*/ 15 DECLARE var_done int DEFAULT FALSE; 16 /*创建游标*/ 17 DECLARE cur_test CURSOR FOR SELECT studentid,score from students where classid<>0; 18 /*游标结束时会设置var_done为true,后续可以使用var_done来判断游标是否结束*/ 19 DECLARE CONTINUE HANDLER FOR NOT FOUND SET var_done=TRUE; 20 /*打开游标*/ 21 OPEN cur_test; 22 /*使用Loop循环遍历游标*/ 23 select_loop:LOOP 24 /*先获取当前行的数据,然后将当前行的数据放入var_studentId,var_score中,如果无数据行了,var_done会被置为true*/ 25 FETCH cur_test INTO var_studentId,var_score; 26 /*通过var_done来判断游标是否结束了,退出循环*/ 27 IF var_done THEN 28 LEAVE select_loop; 29 END IF; 30 /*对var_score值添加随机值,不能超过给定的分数*/ 31 set var_score = var_score + LEAST(ROUND(rand()*10,0),max_score); 32 update students set score = var_score where studentId= var_studentId; 33 END LOOP; 34 /*关闭游标*/ 35 CLOSE cur_test; 36 /*返回结果:可以根据实际情况返回需要的内容*/ 37 RETURN 1; 38 END $ 39 /*结束符置为;*/ 40 DELIMITER ; 41 Query OK, 0 rows affected
1 mysql> 2 /* 参数为8,表示加分上限为8 */ 3 select fun_test(8); 4 +-------------+ 5 | fun_test(8) | 6 +-------------+ 7 | 1 | 8 +-------------+ 9 1 row in set
对比原来的成绩的值,发现成绩添加了随机值,但没超过给定的分数 8
1 mysql> select * from students; 2 +-----------+-------------+-------+---------+ 3 | studentid | studentname | score | classid | 4 +-----------+-------------+-------+---------+ 5 | 1 | brand | 105.5 | 1 | 6 | 2 | helen | 98.5 | 1 | 7 | 3 | lyn | 97 | 1 | 8 | 4 | sol | 97 | 1 | 9 | 5 | b1 | 89 | 2 | 10 | 6 | b2 | 90 | 2 | 11 | 7 | c1 | 76 | 3 | 12 | 8 | c2 | 73.5 | 3 | 13 | 9 | lala | 73 | 0 | 14 | 10 | A | 100 | 3 | 15 | 16 | test1 | 100 | 0 | 16 | 17 | trigger2 | 107 | 0 | 17 | 22 | trigger1 | 100 | 0 | 18 +-----------+-------------+-------+---------+ 19