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();
