Populate a set of variables with temporary table data

Question

I started creating a MySQL procedure for simulating dice play of attack and defense in Risk! game. The procedure takes as input arguments the number of armies (represented by dices) of attacker and defender then plays the dices and returns the number of armies that survived the battle. Attacker and defender have their own dices played, then they are ordered in descending order, then compared couple by couple comparing the attack dice with the highest value with the defense dice with the highest value, then does the same with the other dices if available. I sorted the dices by putting results in two temporary tables then sort them with ALTER TABLE attack_table, ORDER BY dice, DESC but now I don't know how to populate back variables

var_dice_1_attack
var_dice_2_attack
var_dice_3_attack
var_dice_1_defense
var_dice_2_defense
var_dice_3_defense

the code is

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;

for information completeness I attack also the code that runs the comparison among attack and defense dices

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

Please illustrate your issue with original sample data and desired results in data form (not image). Very hard to understand extensive procedural code by itself. — Parfait, Sep 04 '20 at 21:38
For MariaDB, see See https://stackoverflow.com/a/56670844/1766831 — Rick James, Nov 01 '21 at 02:27

score 1 · Accepted Answer · answered Sep 04 '20 at 21:41

1

You can add this after you do the 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;

And do the same for the defense dice variables.

fiddle

answered Sep 04 '20 at 21:41

hsibboni

433
2
8

Thank you. Can I ask you to provide a URL where I can study that `limit` nomenclature? I found some good URLs but they don't exmplain the range in the way you used it – Ctrl_halt Sep 04 '20 at 22:11
1

You need to scroll a little or use the search function of your browser, it is described [in the documentation](https://dev.mysql.com/doc/refman/8.0/en/select.html) : `The LIMIT clause can be used to constrain the number of rows returned by the SELECT statement. LIMIT takes one or two numeric arguments, which must both be nonnegative integer constants` and there is an exemple : `SELECT * FROM tbl LIMIT 5,10; # Retrieve rows 6-15`. What is peculiar here, is that you order the table beforehand, usually I would write `SELECT dice FROM attack_table ORDER BY dice DESC limit 1` . – hsibboni Sep 04 '20 at 22:25

Populate a set of variables with temporary table data

1 Answers1