MySql Procédures et Fonctions. - Version imprimable

Code :

DROP PROCEDURE IF EXISTS `simulation`;/*$$*/



CREATE PROCEDURE `simulation`()

LANGUAGE SQL

NOT DETERMINISTIC

MODIFIES SQL DATA

SQL SECURITY INVOKER

COMMENT ''

BEGIN

    DECLARE idCase INT UNSIGNED;

    DECLARE elect CURSOR FOR

        SELECT m.id

        FROM map AS m

        WHERE m.`date_election` <= NOW()

    ;

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET @cursorEnd := TRUE;



    -- Player's popularity, by laws

    -- @TODO This is the main part of the game, so these equations must be well thought and done

    UPDATE map m

    INNER JOIN laws AS l ON l.id_player = m.id_player

    SET

        m.insecurite = LEAST(1, GREATEST(0, 1.0*achat_armes_autorise-0.4*etat_urgence-0.15*((1+censure)*(1+propagande))+1.0*(1-m.popularite))),

        m.environnement = LEAST(1, GREATEST(0, 0.5-0.25*(1-CAST(plan_ville_verte AS SIGNED)*2))),

        m.education = LEAST(1, GREATEST(0, 0.8*scolarisation_obligatoire*(1+0.2*etudes_superieures_obligatoires))),

        m.popularite = LEAST(1, GREATEST(0, 0.4*m.environnement+0.6*m.education-0.5*m.insecurite))

    ;



    -- Election's score

    UPDATE election_candidat AS ec

    SET ec.score = IF(

        ec.id_player IS NULL, 

        0.5, 

        IFNULL(

            (SELECT m.popularite

                FROM map AS m

                WHERE m.id = ec.id_case AND m.id_player = ec.id_player),

            IFNULL(

                (SELECT SUM(m.popularite)/SUM(1.5) 

                    FROM map AS m 

                    WHERE m.id_player = ec.id_player),

                0.25)))

    ;



    -- Proceed the elections

    OPEN elect;

    electLoop: LOOP

        FETCH elect INTO idCase;

        IF (@cursorEnd) THEN

            LEAVE electLoop;

        END IF;



        SET @vainqueur := (SELECT ec.id_player FROM election_candidat AS ec WHERE ec.id_case = idCase ORDER BY ec.score DESC LIMIT 1);



        -- Add 1 day to the date election untill we're in the future

        -- @TODO Find better (there certainly is, like by counting number of days between today and this election's date)

        SET @dateElection := (SELECT m.`date_election` FROM map AS m WHERE m.id = idCase);

        WHILE (@dateElection < NOW()) DO

            SET @dateElection := DATE_ADD(@dateElection, INTERVAL 1 DAY);

        END WHILE;

        

        -- Save that next election date in the DB

        UPDATE map AS m

        SET m.id_player = @vainqueur,

            m.`date_election` = @dateElection

        WHERE m.id = idCase;



        DELETE FROM election_candidat

        WHERE id_case = idCase AND id_player IS NOT NULL AND (@vainqueur IS NULL OR id_player != @vainqueur)

        ;

    

    END LOOP;



END$$

Code :

DELIMITER |

CREATE PROCEDURE calcul_trajet (IN int_heure_1 INT, IN int_heure_2 INT)

BEGIN

DECLARE int_id_trajet INT;

DECLARE c_trajet CURSOR FOR SELECT id FROM trajet WHERE trajet.heured >= int_heure_1 AND trajet.heurea < int_heure_2;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;



OPEN c_trajet;

read_loop: LOOP



FETCH c_trajet INTO int_id_trajet;

IF done THEN

LEAVE read_loop;

END IF;



INSERT INTO event( type, heure, val1 ) SELECT 'FinTrajet', trajet.heurea, calcul_passager(trajet.id) FROM trajet WHERE trajet.id = int_id_trajet;



END LOOP;



END |