無法確定在MySQL中創建存盤程序的語法問題

我正在嘗試創建以下存盤程序（XAMPP > phpMyAdmin）：

        DROP PROCEDURE IF EXISTS changeTurn;
        
        DELIMITER //
        
        create procedure changeTurn(in currentGameID bigint(20))
        begin
            declare turnHasBeenChanged tinyint(1) default 0
            
            select @targetPlayer := player_number from game_players where gameID = currentGameID and current_turn = 1

            set @targetPlayer = @targetPlayer   1

            update game_players set current_turn = 0 where gameID = currentGameID and current_turn = 1

            while turnHasBeenChanged = 0 do
                case
                    when @targetPlayer > 4
                    then set @targetPlayer = 1
                end
                case
                    when (select forfeit_next_turn from game_players where gameID = currentGameID and player_number = @targetPlayer) = 1
                    then
                    update game_players set forfeit_next_turn = 0 where gameID = currentGameID and player_number = @targetPlayer
                    set @targetPlayer = @targetPlayer   1
                    else
                    update game_players set current_turn = 1 where gameID = currentGameID and player_number = @targetPlayer
                    set turnHasBeenChanged = 1
                end
            end while
        end
        
        end //
        DELIMITER ;

無論有沒有分隔符，我都會遇到以下問題：

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'select @targetPlayer := player_number from game_players where gameID = curren...' at line 5

不幸的是，這不是很具體。

在將這個程序拼湊在一起時，我確保我對每個部分都使用了正確的語法。但現在它完全，它很無聊。請你能給我一些關于為什么會出錯的方向嗎？

我檢查了第 4 行（宣告 turnHasBeenChanged tinyint(1) 默認 0），以防它的語法導致后續行失敗，并在其末尾添加了一個分號，并且在該行打破了它，所以它不是那個。

然后我根據https://www.oreilly.com/library/view/mysql-cookbook/0596001452/ch01s15.html驗證了第 5 行的語法，它似乎是正確的......？

uj5u.com熱心網友回復：

每個命令都必須以 ;

也應該由 IF THEN 替換的情況

最后你有很多事情要結束

所以以下不再給出錯誤

    DROP PROCEDURE IF EXISTS changeTurn;
    
    DELIMITER //
    
    create procedure changeTurn(in currentGameID bigint(20))
    begin
        declare turnHasBeenChanged tinyint(1) default 0;
        
        select @targetPlayer := player_number from game_players where gameID = currentGameID and current_turn = 1;

        set @targetPlayer = @targetPlayer   1;

        update game_players set current_turn = 0 where gameID = currentGameID and current_turn = 1;

        while turnHasBeenChanged = 0 do
            IF @targetPlayer > 4
                then set @targetPlayer = 1;
            end IF;
            IF ((select forfeit_next_turn from game_players where gameID = currentGameID and player_number = @targetPlayer) = 1)
                then
                update game_players set forfeit_next_turn = 0 where gameID = currentGameID and player_number = @targetPlayer;
                set @targetPlayer = @targetPlayer   1;
                else
                update game_players set current_turn = 1 where gameID = currentGameID and player_number = @targetPlayer;
                set turnHasBeenChanged = 1;
            end IF;
        end while;
    
    end //
    DELIMITER ;

標籤：

上一篇：每行的PHP洗掉按鈕/更改該行表中的值

下一篇：PHPMySQLGETRESULTfromINSERTINTOIFNOTEXIST