一、要求
创建 学生表 student2 (stu_id,stu_name)
要求利用 游标 循环 IF语句完成以下功能:
总分超过240的为 优秀学生,要求调用存储过程,把所有优秀学生的信息,复制到 stu2表中。。
先实现select 查询学生的总分;
SELECT t1.stu_id,t1.stu_name,SUM(t2.point)
FROM student t1 JOIN score t2
ON t1.stu_id = t2.stu_id;
利用 stu_id(学生id) 来排序,来排除小于240的学生
GROUP BY t1.stu_id HAVING SUM(t2.point) >240;
创建student2表(可创建在存储过程中)
CREATE TABLE IF NOT EXISTS student2(
stu_id INT(10) NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '学生id',
stu_name VARCHAR(255) NOT NULL COMMENT '学生姓名'
);
再创建存储过程pro_showTotal() ,游标 ,封装整个过程。如下:
DELIMITER $
CREATE PROCEDURE pro_showDuplicate()
BEGIN
DECLARE sId INT;
DECLARE sName VARCHAR(255);
DECLARE flag INT DEFAULT 0;
DECLARE s_dp CURSOR FOR
SELECT t1.stu_id,t1.stu_name
FROM student t1 JOIN score t2
ON t1.stu_id = t2.stu_id
GROUP BY t1.stu_id
HAVING SUM(t2.point) >240;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET flag = 1;
CREATE TABLE IF NOT EXISTS student2(
stu_id INT(10) NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '学生id',
stu_name VARCHAR(255) NOT NULL COMMENT '学生姓名'
);
OPEN s_dp;
WHILE flag = 0 DO
FETCH s_dp INTO sId,sName;
IF flag = 0 THEN
INSERT INTO student2 VALUES (sId,sName);
END IF;
END WHILE;
CLOSE s_dp;
END;
再调用存储过程
CALL pro_showDuplicate();
查询student2表
SELECT * FROM student2;
查询结果