声明关键字错误mysql

Question

这是我写的代码：

delimiter //
create procedure `update_pts`()
begin

set @ctr=1;
set @cnt=(select count(*) from participant);

declare cs1 cursor for select sap from participant;

open cs1

while @ctr<@cnt do

fetch next from cs1 into @sap

set @points=(select sum(players.points) from players where pname in (select player1,player2,player3,player4,player5,player6,player7,player8 from team where sap=@sap));
set @pointsc=(select points from players where sport='cricket' and dept=(select cdept from team where sap=@sap) and year=(select cyear from team where sap=@sap)); 
set @pointschess=(select points from players where sport='chess' and dept=(select chessdept from team where sap=@sap) and year=(select chessyear from team where sap=@sap));
set @pointstt=(select points from players where sport='tt' and dept=(select ttdept from team where sap=@sap) and year=(select ttyear from team where sap=@sap));
set @pointst=(select points from players where sport='throwball' and dept=(select tdept from     team where sap=@sap) and year=(select tyear from team where sap=@sap));
set @pointsf=@points+@pointsc+@pointschess+@pointstt+@pointst;

update participant set points = @pointsf where sap=@sap;

set @ctr=@ctr+1;

end while;

close cs1;
deallocate cs1;
end //;

MySQL给出了声明关键字上的一个错误：‘语法错误，意外声明_SYM’。我试图通过放置2组语句来改变语句的顺序。

set @ctr=1;
set @cnt=(select count(*) from participant);

在declare语句之后，然后MySQL给出while语句中的一个错误：‘语法错误，意外的WHILE_SYM，期待’；‘。

有谁能向我解释一下为什么会发生这种事，我该如何解决？

谢谢

更新代码@Abdul：

delimiter //
create procedure `update_pts`()
begin


 DECLARE noMore BOOLEAN DEFAULT FALSE;   

declare cs1 cursor for select sap from participant;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET noMore = TRUE;
#set @ctr=1;
#set @cnt=(select count(*) from participant);
open cs1;

#while @ctr<=@cnt do
REPEAT
fetch cs1 into @sap;
if not noMore then
set @points=(select sum(players.points) from players where pname in (select player1 from     team where sap=@sap
                    union all
                    select player2
                    from team where sap=@sap
                    union all
                    select player3
                    from team where sap=@sap
                    union all
                    select player4
                    from team where sap=@sap
                    union all
                    select player5
                    from team where sap=@sap
                    union all
                    select player6
                    from team where sap=@sap
                    union all
                    select player7
                    from team where sap=@sap
                    union all
                    select player8
                    from team where sap=@sap));
set @pointsc=(select points from players where sport='cricket' and dept=(select cdept     from team where sap=@sap) and year=(select cyear from team where sap=@sap)); 
set @pointschess=(select points from players where sport='chess' and dept=(select     chessdept from team where sap=@sap) and year=(select chessyear from team where sap=@sap));
set @pointstt=(select points from players where sport='tt' and dept=(select ttdept from     team where sap=@sap) and year=(select ttyear from team where sap=@sap));
set @pointst=(select points from players where sport='throwball' and dept=(select tdept     from team where sap=@sap) and year=(select tyear from team where sap=@sap));
set @pointsf=@points+@pointsc+@pointschess+@pointstt+@pointst;

update participant set points = @pointsf where sap=@sap;
end if;
#set @ctr=@ctr+1;
until noMore
end repeat;

close cs1;
#deallocate cs1;
end //

Answer 1

我相信你的语法不正确，你能按照这个语法吗？

DROP PROCEDURE IF EXISTS update_pts;
DELIMITER //

CREATE PROCEDURE `update_pts`()
BEGIN

DECLARE noMore BOOLEAN DEFAULT FALSE;   
DECLARE cursorsap VARCHAR(255);
DECLARE cs1 CURSOR FOR SELECT sap FROM participant;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET noMore = TRUE;

SET @ctr=1;
SELECT count(*) INTO @cnt FROM participant;

OPEN cs1;

REPEAT
FETCH cs1 INTO cursorsap;
    IF NOT noMore AND (@ctr < @cnt) THEN
        SELECT sum(players.points) INTO @points FROM players WHERE pname in 
        (
        SELECT player1 FROM team WHERE sap=cursorsap
        UNION ALL
        SELECT player2 FROM team WHERE sap=cursorsap
        UNION ALL
        SELECT player3 FROM team WHERE sap=cursorsap
        UNION ALL
        SELECT player4 FROM team WHERE sap=cursorsap
        UNION ALL
        SELECT player5 FROM team WHERE sap=cursorsap
        UNION ALL
        SELECT player6 FROM team WHERE sap=cursorsap
        UNION ALL
        SELECT player7 FROM team WHERE sap=cursorsap
        UNION ALL
        SELECT player8 FROM team WHERE sap=cursorsap
        );

        SELECT points INTO @pointsc FROM players WHERE sport='cricket' AND dept=(SELECT cdept FROM team WHERE sap=cursorsap) AND year=(SELECT cyear FROM team WHERE sap=cursorsap);

        SELECT points INTO @pointschess FROM players WHERE sport='chess' AND dept=(SELECT chessdept FROM team WHERE sap=cursorsap) AND year=(SELECT chessyear FROM team WHERE sap=cursorsap);

        SELECT points INTO @pointstt FROM players WHERE sport='tt' AND dept=(SELECT ttdept FROM team WHERE sap=cursorsap) AND year=(SELECT ttyear FROM team WHERE sap=cursorsap);

        SELECT points INTO @pointst FROM players WHERE sport='throwbALL' AND dept=(SELECT tdept FROM team WHERE sap=cursorsap) AND year=(SELECT tyear FROM team WHERE sap=cursorsap);

        SET @pointsf=@points+@pointsc+@pointschess+@pointstt+@pointst;

        update participant SET points = @pointsf WHERE sap=cursorsap;

        SET @ctr=@ctr+1;
    END IF;
UNTIL noMore
END repeat;
CLOSE cs1;
END //

请使用此更新的代码，如果您还有问题，请告诉我。