1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134
| SET AUTOCOMMIT = 0;
START TRANSACTION;
-- ======================
-- Base de Données `base`
-- ======================
DROP DATABASE IF EXISTS `base`;
CREATE DATABASE `base`
DEFAULT CHARACTER SET `latin1`
DEFAULT COLLATE `latin1_general_ci`;
USE `base`;
-- ============
-- Table `test`
-- ============
DROP TABLE IF EXISTS `test`;
create table `test` (
`id` tinyint unsigned auto_increment NOT NULL PRIMARY KEY,
`prenom` char(10) NOT NULL,
`course` char(12) NOT NULL,
`clagen` tinyint unsigned NOT NULL,
`sexe` char(01) NOT NULL,
`clahom` char(10) NOT NULL default '-',
`clafem` char(10) NOT NULL default '-'
) ENGINE=InnoDB
DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
ROW_FORMAT=COMPRESSED;
-- =======================
-- Insertion dans `test`
-- =======================
insert into `test` (`prenom`, `course`,`clagen`,`sexe`) values
('Marc', 'le Mans 2005', 1, 'H'),
('Jeanne', 'le Mans 2005', 2, 'F'),
('Mireille', 'le Mans 2005', 3, 'F'),
('Bruno', 'le Mans 2005', 4, 'H'),
('Maurice', 'Monaco 2006', 1, 'H'),
('Christian', 'Monaco 2006', 2, 'H'),
('Sylvie', 'Monaco 2006', 3, 'F'),
('corinne', 'Monaco 2006', 4, 'F'),
('Bernadette', 'Monaco 2006', 4, 'F'),
('Elise', 'Monaco 2006', 6, 'F');
-- ================
-- Vidage de `test`
-- ================
select * from test;
-- =================
-- Procédure stockée
-- =================
drop procedure `trait`;
delimiter $$
create procedure `trait` ()
DETERMINISTIC
NO SQL
BEGIN
DECLARE _rupt tinyint unsigned DEFAULT 0;
DECLARE _clas tinyint unsigned DEFAULT NULL;
DECLARE _prec CHAR(12) DEFAULT NULL;
DECLARE _cour CHAR(12) DEFAULT NULL;
DECLARE _sex CHAR(01) DEFAULT NULL;
DECLARE _chom CHAR(10) DEFAULT '';
DECLARE _cfem CHAR(10) DEFAULT '';
DECLARE _hom tinyint unsigned DEFAULT 0;
DECLARE _fem tinyint unsigned DEFAULT 0;
DECLARE _fin INTEGER DEFAULT 1;
DECLARE _tab CURSOR FOR SELECT course, sexe, clagen FROM test ORDER BY course, clagen;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET _fin = 0;
-- =========================================================================================================
OPEN _tab;
FETCH _tab INTO _cour, _sex, _clas;
WHILE (_fin)
DO
if (_prec != _cour) then SET _hom = 0; SET _fem = 0; end if;
if (_sex = 'H') then SET _hom = _hom + 1; SET _chom = _hom; SET _cfem = ''; end if;
if (_sex = 'F') then SET _fem = _fem + 1; SET _chom = ''; SET _cfem = _fem; end if;
if (_rupt != _clas) then
update `test` set clahom = _chom, clafem = _cfem
where course = _cour and clagen = _clas and sexe = _sex;
end if;
SET _prec = _cour;
SET _rupt = _clas;
FETCH _tab INTO _cour, _sex, _clas;
END WHILE;
CLOSE _tab;
END $$
DELIMITER ;
-- ===============
-- Appel Procédure
-- ===============
CALL trait();
-- ================
-- Vidage de `test`
-- ================
select id,
prenom,
course,
clagen as 'Classement Général',
clahom as 'Classement Hommes',
clafem as 'Classement femmes'
from test;
-- ===
-- Fin
-- ===
COMMIT;
SET AUTOCOMMIT = 1; |
Partager