用临时表数据填充一组变量

Question

我开始创建一个MySQL程序来模拟在危险中攻击和防御的骰子游戏！这是游戏。该过程将攻击者和防御者的军队数量(用骰子表示)作为输入参数，然后玩骰子并返回在战斗中幸存下来的军队数量。攻守者玩自己的骰子，然后按降序排列，然后一对一对地比较最高值的攻击骰子和最高值的防御骰子，然后对其他骰子进行相同的比较。我通过将结果放在两个临时表中对骰子进行排序，然后使用ALTER TABLE attack_table, ORDER BY dice, DESC对它们进行排序，但现在我不知道如何填充返回的变量

var_dice_1_attack
var_dice_2_attack
var_dice_3_attack
var_dice_1_defense
var_dice_2_defense
var_dice_3_defense

代码是

CREATE PROCEDURE IF NOT EXISTS `risk`.`play_dices`(INOUT attack INT, INOUT defense INT)
BEGIN
    DECLARE var_dice_1_attack INT;
    DECLARE var_dice_2_attack INT;
    DECLARE var_dice_3_attack INT;
    DECLARE var_dice_1_defense INT;
    DECLARE var_dice_2_defense INT;
    DECLARE var_dice_3_defense INT;
    
    CREATE TEMPORARY TABLE attack_table (dice INT NOT NULL);
    CREATE TEMPORARY TABLE defense_table (dice INT NOT NULL);
    
    -- FLOOR(RAND() * (<max> - <min> + 1)) + <min>
    -- generates a number between <min> and <max> inclusive 
    CASE attack
        WHEN '1' THEN
            SET var_dice_1_attack = FLOOR(RAND() * 6) + 1;
            INSERT INTO attack_table (dice) VALUE (var_dice_1_attack);
        WHEN '2' THEN
            SET var_dice_1_attack = FLOOR(RAND() * 6) + 1;
            SET var_dice_2_attack = FLOOR(RAND() * 6) + 1;
            INSERT INTO attack_table (dice) VALUE (var_dice_1_attack);
            INSERT INTO attack_table (dice) VALUE (var_dice_2_attack);
        WHEN '3' THEN
            SET var_dice_1_attack = FLOOR(RAND() * 6) + 1;
            SET var_dice_2_attack = FLOOR(RAND() * 6) + 1;
            SET var_dice_3_attack = FLOOR(RAND() * 6) + 1;
            INSERT INTO attack_table (dice) VALUE (var_dice_1_attack);
            INSERT INTO attack_table (dice) VALUE (var_dice_2_attack);
            INSERT INTO attack_table (dice) VALUE (var_dice_3_attack);
    END CASE;
    
    ALTER TABLE attack_table, ORDER BY dice, DESC;
    
    CASE defense
        WHEN '1' THEN
            SET var_dice_1_defense = FLOOR(RAND() * 6) + 1;
            INSERT INTO defense_table (dice) VALUE (var_dice_1_defense);
        WHEN '2' THEN
            SET var_dice_1_defense = FLOOR(RAND() * 6) + 1;
            SET var_dice_2_defense = FLOOR(RAND() * 6) + 1;
            INSERT INTO defense_table (dice) VALUE (var_dice_1_defense);
            INSERT INTO defense_table (dice) VALUE (var_dice_2_defense);
        WHEN '3' THEN
            SET var_dice_1_defense = FLOOR(RAND() * 6) + 1;
            SET var_dice_2_defense = FLOOR(RAND() * 6) + 1;
            SET var_dice_3_defense = FLOOR(RAND() * 6) + 1;
            INSERT INTO defense_table (dice) VALUE (var_dice_1_defense);
            INSERT INTO defense_table (dice) VALUE (var_dice_2_defense);
            INSERT INTO defense_table (dice) VALUE (var_dice_3_defense);
    END CASE;
    
    ALTER TABLE defense_table, ORDER BY dice, DESC;

为了信息的完整性，我还攻击了运行攻击和防御骰子之间比较的代码。

IF (var_dice_1_attack IS NOT NULL) AND (var_dice_1_defense IS NOT NULL) THEN
    IF var_dice_1_attack > var_dice_1_defense THEN
        SET defense = defense - 1;
        IF defense < 0 THEN
            signal sqlstate '45003' set message_text = "Error, number of defense armies is negative";
        END IF;
    ELSEIF var_dice_1_attack <= var_dice_1_defense THEN
        SET attack = attack - 1;
        IF attack < 0 THEN
            signal sqlstate '45003' set message_text = "Error, number of attack armies is negative";
        END IF;
    END IF;
END IF;
IF (var_dice_2_attack IS NOT NULL) AND (var_dice_2_defense IS NOT NULL) THEN
    IF var_dice_2_attack > var_dice_2_defense THEN
        SET defense = defense - 1;
        IF defense < 0 THEN
            signal sqlstate '45003' set message_text = "Error, number of defense armies is negative";
        END IF;
    ELSEIF var_dice_2_attack <= var_dice_2_defense THEN
        SET attack = attack - 1;
        IF attack < 0 THEN
            signal sqlstate '45003' set message_text = "Error, number of attack armies is negative";
        END IF;
    END IF;
END IF;
IF (var_dice_3_attack IS NOT NULL) AND (var_dice_3_defense IS NOT NULL) THEN
    IF var_dice_3_attack > var_dice_3_defense THEN
        SET defense = defense - 1;
        IF defense < 0 THEN
            signal sqlstate '45003' set message_text = "Error, number of defense armies is negative";
        END IF;
    ELSEIF var_dice_3_attack <= var_dice_3_defense THEN
        SET attack = attack - 1;
        IF attack < 0 THEN
            signal sqlstate '45003' set message_text = "Error, number of attack armies is negative";
        END IF;
    END IF;
END IF;

DROP TEMPORARY TABLE attack_table;
DROP TEMPORARY TABLE defense_table;

END

Answer 1

您可以在执行ALTER TABLE ... ORDER BY dice DESC;之后添加以下内容：

    ALTER TABLE attack_table ORDER BY dice DESC;

CASE attack
    WHEN '1' THEN
        SET var_dice_1_attack = (SELECT dice FROM attack_table  limit 1);
    WHEN '2' THEN
        SET var_dice_1_attack = (SELECT dice FROM attack_table  limit 1);
        SET var_dice_2_attack = (SELECT dice FROM attack_table  limit 1,1);
    WHEN '3' THEN
        SET var_dice_1_attack = (SELECT dice FROM attack_table  limit 1);
        SET var_dice_2_attack = (SELECT dice FROM attack_table  limit 1,1);
        SET var_dice_3_attack = (SELECT dice FROM attack_table  limit 2,1);
END CASE;

并对防御骰子变量执行相同的操作。

fiddle