简单的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();