MySQL游标无法获取数据-1329 - No data - zero rows fetched, selected, or processed

最新推荐文章于 2024-07-23 19:15:50 发布

念ืุ白ืุ

最新推荐文章于 2024-07-23 19:15:50 发布

阅读量1.4k

点赞数

文章标签： mysql 数据库

本文链接：https://blog.csdn.net/qq_65072297/article/details/128144747

版权

创建存储过程“get_count_by_limit_total_salary()”，声明IN参数 limit_total_salary，
#DOUBLE类型；声明OUT参数total_count，INT类型。函数的功能可以实现累加薪资最高的几个员工的薪资值，
#直到薪资总和达到limit_total_salary参数的值，返回累加的人数给total_count。


DELIMITER //
CREATE PROCEDURE get_count_by_limit_total_salary(IN limit_total_salary DOUBLE,OUT total_count INT)
BEGIN
    DECLARE counts INT DEFAULT 0;
    DECLARE sals  DOUBLE ;
    DECLARE sal DOUBLE DEFAULT 0.0;
    DECLARE e CURSOR FOR SELECT salary FROM employees ORDER BY salary DESC;
    OPEN e;
    REPEAT
        FETCH e INTO sal;
        SET sals =sals+sal;
        SET counts= counts+1;
        UNTIL sals>=limit_total_salary
    END REPEAT;
    SET total_count=counts;
    CLOSE e;
END//
DELIMITER ;

CALL get_count_by_limit_total_salary(200000,@total_count);
SELECT @total_count;

错误原因:

游标传入值的变量 sal 设置了default 值为0

改正:

游标传入值的变量 sal 不能设值default 值

DELIMITER //
CREATE PROCEDURE get_count_by_limit_total_salary(IN limit_total_salary DOUBLE,OUT total_count INT)
BEGIN
	DECLARE counts INT DEFAULT 0;
	DECLARE sals  DOUBLE DEFAULT 0.0;
	DECLARE sal DOUBLE ;
	DECLARE e CURSOR FOR SELECT salary FROM employees ORDER BY salary DESC;
	OPEN e;
	REPEAT
		FETCH e INTO sal;
		SET sals =sals+sal;
		SET counts= counts+1;
		UNTIL sals>=limit_total_salary
	END REPEAT;
	SET total_count=counts;
	CLOSE e;
END//
DELIMITER ;

CALL get_count_by_limit_total_salary(200000,@total_count);
SELECT @total_count;