简单的MySql游标创建

 1 DROP PROCEDURE IF exists proc_updusercourse_statistics_performance;
 2 CREATE PROCEDURE proc_updusercourse_statistics_performance() 
 3 BEGIN
 4   -- 游标返回变量
 5     DECLARE _school_id BIGINT(18);
 6     DECLARE _course_count INT(8);
 7     DECLARE _lw_month INT(10);
 8     DECLARE _lw_week INT(10);
 9 
10   -- 游标状态标志
11     DECLARE flag BOOLEAN DEFAULT TRUE;
12     DECLARE cur CURSOR for 
13       select school_id,sum(course_count) course_count,lw_month,lw_week from tb_teacher_performance group by lw_week,lw_month,school_id;
14 
15     -- 游标状态赋值
16     DECLARE CONTINUE HANDLER FOR NOT FOUND SET flag = FALSE;
17 
18     OPEN cur;
19 
20     FETCH cur INTO _school_id,_course_count,_lw_month,_lw_week;
21     while flag do
22            update tb_usercourse_statistics set performance_course_count = _course_count where school_id = _school_id and lw_month = _lw_month and LW_week = _lw_week;
23   FETCH cur INTO _school_id,_course_count,_lw_month,_lw_week;
24     COMMIT;
25     END while;
26 
27   CLOSE cur;
28 END;
29 -- 调用存储过程
30 call proc_updusercourse_statistics_performance();

 

 1 DROP PROCEDURE IF exists StatisticStore1;
 2 CREATE PROCEDURE StatisticStore1() 
 3 BEGIN
 4     DECLARE c_name VARCHAR(50);
 5     DECLARE c_id INT(6);
    -- 声明结束标志
 6     DECLARE i int default 1; 
 7     DECLARE b int default 0; 
 8     DECLARE cur CURSOR for SELECT name,id FROM tb_sort;
 9     DECLARE CONTINUE HANDLER FOR NOT FOUND SET b = 1;
10 -- 开启游标
11     OPEN cur;
12 
13     FETCH cur INTO c_name,c_id;
14     while b<>1 do
15            INSERT INTO tb_books (NAME,author,buytime,sort_id) VALUES (concat(c_name,'下的图书',i),'ACCP',now(),c_id);
16   FETCH cur INTO c_name,c_id;
17     END while;
18 -- 关闭游标
19   CLOSE cur;
20 END;
21 
22 call StatisticStore1();

 

转载于:https://www.cnblogs.com/jason123/p/6846740.html