查询STUDENT表,只要有年龄小于20岁的学生,就将年龄最小的那个学生删掉,如此循环下去,直到所有的学生的年龄都不小于20岁,或是学生的总人数小于20个了就退出循环。
delimiter //
CREATE PROCEDURE de1()
BEGIN
set @n2=(SELECT min(YEAR(curdate())-YEAR(sbirthday)) FROM student);
set @z1=(SELECT count(sno)FROM student);
WHILE @n2<20 AND @z1>=20 do
DELETE FROM student WHERE YEAR(curdate())-YEAR(sbirthday)=@n2;
set @n2=(SELECT min(YEAR(curdata())-YEAR(sbirthday)) FROM student);
set @z1=(SELECT count(sno) FROM student);
END WHILE;
END //
delimiter //
call de1();