Question

mysql 创建存储过程。

从表stugra中统计所有不及格学生数量。但是规定不可以用count函数，要用循环选择语句来写

Answer 1

/*先建表stugra*/
CREATE TABLE `stugra` (
`name` CHAR(20) DEFAULT NULL,
`mark` INT(3) DEFAULT NULL
) ENGINE=INNODB DEFAULT CHARSET=gbk

INSERT INTO stugra VALUES ('aa','59');
INSERT INTO stugra VALUES ('fa','11');
INSERT INTO stugra VALUES ('aga','99');
INSERT INTO stugra VALUES ('v','32');
INSERT INTO stugra VALUES ('da','77');
INSERT INTO stugra VALUES ('gf','44');
INSERT INTO stugra VALUES ('sadf','0');
INSERT INTO stugra VALUES ('eef','32');
INSERT INTO stugra VALUES ('ggg','60');
存储过程：
DELIMITER $$
DROP PROCEDURE IF EXISTS `pro_find`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `pro_find`()
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE a,b INT DEFAULT 0;

DECLARE cur1 CURSOR FOR SELECT mark FROM stugra;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
OPEN cur1;
REPEAT
FETCH cur1 INTO a;
IF NOT done THEN
IF a<60 THEN
SET b=b+1;
END IF;
ELSE
SELECT b;
END IF;
UNTIL done END REPEAT;
CLOSE cur1;
END$$

DELIMITER ;

没使用count ，使用了循环和选择，还使用了游标，没说不能使用游标吧！