8
juillet
2011
Modélisation euromillions
juillet
2011
Un article de SQLpro
En une seule table, créez le modèle permettant de saisir autant de grille que l’on souhaite. Prévoyez toutes les contraintes afin qu’aucune saisie ne puisse être fausse. Écrivez une procédure pour générer automatiquement n grilles avec des numéros et des étoiles choisies au hasard.
Chaque grille comporte 5 numéros compris entre 1 et 50 inclus et 2 étoiles comprises entre 1 et 11.
Bien entendu, dans une même grille, il ne peut y avoir deux numéros identique, ni deux étoiles identiques.
SOLUTION…
La table :
CREATE TABLE T_GRILLE_EUROMILLION_GEM
(GEM_ID INT NOT NULL IDENTITY PRIMARY KEY,
GEM_GRILLE INT NOT NULL,
GEM_TYPE CHAR(6) NOT NULL CHECK (GEM_TYPE IN ('ETOILE', 'NUMERO')),
GEM_RANG TINYINT NOT NULL CHECK (GEM_RANG > 0),
GEM_NUMERO TINYINT NOT NULL CHECK (GEM_NUMERO > 0),
CONSTRAINT CK_TYPE_NUM CHECK ( (GEM_TYPE = 'ETOILE' AND GEM_NUMERO <=11 )
OR (GEM_TYPE = 'NUMERO' AND GEM_NUMERO <=50)),
CONSTRAINT CK_TYPE_RNG CHECK ( (GEM_TYPE = 'ETOILE' AND GEM_RANG <=2 )
OR (GEM_TYPE = 'NUMERO' AND GEM_RANG <=5)),
CONSTRAINT UK_ID_TYP_NUM UNIQUE (GEM_GRILLE, GEM_TYPE, GEM_NUMERO),
CONSTRAINT UK_ID_TYP_RNG UNIQUE (GEM_GRILLE, GEM_TYPE, GEM_RANG));
GO
La procédure
CREATE PROCEDURE P_LOTO_EUROMILLION @N smallint
AS
DECLARE @MAXGRL INT, @NEWGRL INT,
@NG INT, @NE TINYINT, @NN TINYINT;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRANSACTION;
SELECT @NEWGRL = MAX(GEM_GRILLE) + 1 FROM T_GRILLE_EUROMILLION_GEM;
IF @NEWGRL IS NULL
SET @NEWGRL = 1;
SET @MAXGRL = @NEWGRL
-- boucle sur nombre de grilles
SET @NG = 1;
WHILE @NG <= @N
BEGIN
SET @NN = 1
BEGIN TRY
-- boucle sur nombre de numero (5)
WHILE NOT EXISTS(SELECT *
FROM T_GRILLE_EUROMILLION_GEM
WHERE GEM_GRILLE = @MAXGRL
AND GEM_RANG = 5
AND GEM_TYPE = 'NUMERO')
INSERT INTO T_GRILLE_EUROMILLION_GEM
SELECT @MAXGRL, 'NUMERO', COALESCE(MAX(GEM_RANG) + 1, 1),
CAST(FLOOR(1 + (RAND() * 50)) AS TINYINT)
FROM T_GRILLE_EUROMILLION_GEM
WHERE GEM_GRILLE = @MAXGRL
AND GEM_TYPE = 'NUMERO';
-- boucle sur nombre d'étoile (5)
WHILE NOT EXISTS(SELECT *
FROM T_GRILLE_EUROMILLION_GEM
WHERE GEM_GRILLE = @MAXGRL
AND GEM_RANG = 2
AND GEM_TYPE = 'ETOILE')
INSERT INTO T_GRILLE_EUROMILLION_GEM
SELECT @MAXGRL, 'ETOILE', COALESCE(MAX(GEM_RANG) + 1, 1),
CAST(FLOOR(1 + (RAND() * 11)) AS TINYINT)
FROM T_GRILLE_EUROMILLION_GEM
WHERE GEM_GRILLE = @MAXGRL
AND GEM_TYPE = 'ETOILE';
SELECT @NG = @NG + 1, @MAXGRL = @MAXGRL + 1;
END TRY
BEGIN CATCH
END CATCH
END
COMMIT TRANSACTION ;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SELECT GEM_GRILLE, GEM_TYPE, GEM_NUMERO
FROM T_GRILLE_EUROMILLION_GEM
WHERE GEM_GRILLE >= @NEWGRL
ORDER BY GEM_GRILLE, GEM_TYPE DESC, GEM_NUMERO
GO
--------
Frédéric Brouard, SQLpro - ARCHITECTE DE DONNÉES, http://sqlpro.developpez.com/
Expert bases de données relationnelles et langage SQL. MVP Microsoft SQL Server
www.sqlspot.com : modélisation, conseil, audit, optimisation, tuning, formation
* * * * * Enseignant CNAM PACA - ISEN Toulon - CESI Aix en Provence * * * * *
Frédéric Brouard, SQLpro - ARCHITECTE DE DONNÉES, http://sqlpro.developpez.com/
Expert bases de données relationnelles et langage SQL. MVP Microsoft SQL Server
www.sqlspot.com : modélisation, conseil, audit, optimisation, tuning, formation
* * * * * Enseignant CNAM PACA - ISEN Toulon - CESI Aix en Provence * * * * *
