mysql - 没有从存储过程中获取行

Question

我创建了一个像这样的存储过程，当我尝试调用它时显示

call points;
$$
329 (02000): No data - zero rows fetched, selected, or processed

这个存储过程有什么错误？

DELIMITER $$
CREATE PROCEDURE POINTS()

    BEGIN    
        DECLARE NAMEEE VARCHAR(15);    
        declare monthcount int(10);    
        DECLARE CUR2 CURSOR for SELECT * FROM TEMP;    
        open CUR2;   
        read_loop: LOOP   
            FETCH CUR2  INTO NAMEEE;   
            select monthlycount into monthcount from incident  where name=NAMEEE;    
            if monthcount <= 3   then    
                UPDATE INCIDENT SET POINTS="10" WHERE NAME=NAMEEE;    
            elseif (monthcount > 3 and monthcount <=6) then    
                UPDATE INCIDENT SET POINTS="20" WHERE NAME=NAMEEE;   
            elseif (monthcount > 6 and monthcount <=9) then    
                UPDATE INCIDENT SET POINTS="30" WHERE NAME=NAMEEE;   
            elseif (monthcount >9 and monthcount <=12) then    
                UPDATE INCIDENT SET POINTS="40" WHERE NAME=NAMEEE;   
            elseif (monthcount >12 ) then    
                UPDATE INCIDENT SET POINTS="50" WHERE NAME=NAMEEE;    
            end if;   
        END LOOP;   
        CLOSE CUR2;   
    end $$

这是我的表格

CREATE TABLE IF NOT EXISTS INCIDENT(
NAME VARCHAR(45)  NOT NULL,
DAILYCOUNT INT(10),
WEEKLYCOUNT INT(10),
MONTHLYCOUNT INT(10),
POINTS INT(10)  NOT NULL
);

insert into incident values("peter","1","2","3","0");
insert into incident values("thomas","1","2","4","0");
insert into incident values("franklins","1","2","6","0");
insert into incident values("yedhu","1","2","8","0");

CREATE TABLE IF NOT EXISTS TEMP
(
NAME VARCHAR(15)
);

insert into  temp values("peter");
insert into  temp values("thomas");
insert into  temp values("franklins");
insert into  temp values("yedhu");

Answer 1

您的整个存储过程可以使用单个多表更新语句来表达。 update 语句根据 name 和 name 字段连接 2 个表，并根据 Monthcount 字段中的值更新 Points 字段。 ceil() 有效地对除法结果进行四舍五入。 if() 函数引入了 12 的上限。

update incident inner join temp on incident.name=temp.namee
set incident.points=if(incident.monthcount<=12,ceil(incident.monthcount / 3) * 10, 50)

因此，您可以避免使用复杂的存储过程并将其替换为 s