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 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150
| 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`;
-- =====================
-- Création Table `test`
-- =====================
DROP TABLE IF EXISTS `test`;
CREATE TABLE `test` (
`id` int NOT NULL AUTO_INCREMENT PRIMARY KEY,
`date` date NOT NULL,
`kms` int NOT NULL
) ENGINE=InnoDB
DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
ROW_FORMAT=COMPRESSED;
-- ========================
-- Création Table `grouper`
-- ========================
DROP TABLE IF EXISTS `grouper`;
CREATE TABLE `grouper` (
`an` int NOT NULL,
`mois` int NOT NULL,
`kms` int NOT NULL,
PRIMARY KEY (`an`,`mois`)
) ENGINE=InnoDB
DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
ROW_FORMAT=COMPRESSED;
-- =========================
-- Procédure stockée "trait"
-- =========================
drop procedure `trait`;
delimiter $$ ;
create procedure `trait`
(
IN _date date
)
DETERMINISTIC
NO SQL
BEGIN
DECLARE _an int DEFAULT 0;
DECLARE _mois int DEFAULT 0;
DECLARE _kms int DEFAULT 0;
DECLARE _bis int DEFAULT NULL;
set _an = year (_date);
set _mois = month(_date);
select sum(kms) into _kms from test where year(date) = _an and month(date) = _mois group by year(date), month(date);
select kms into _bis from grouper where an = _an and mois = _mois;
if (_bis IS NULL) then
insert into `grouper` (`an`,`mois`,`kms`) values (_an, _mois, _kms);
else update `grouper` set kms = _kms where an = _an and mois = _mois;
end if;
END$$
DELIMITER ;
-- ========================
-- Création Trigger "ajout"
-- ========================
DROP TRIGGER IF EXISTS `ajout`;
DELIMITER $$
CREATE TRIGGER `ajout`
AFTER INSERT ON `test`
FOR EACH ROW
BEGIN
call `trait` (new.date);
END$$
DELIMITER ;
-- ========================
-- Création Trigger "suppr"
-- ========================
DROP TRIGGER IF EXISTS `suppr`;
DELIMITER $$
CREATE TRIGGER `suppr`
AFTER delete ON `test`
FOR EACH ROW
BEGIN
call `trait` (old.date);
END$$
DELIMITER ;
-- ========================
-- Création Trigger "modif"
-- ========================
DROP TRIGGER IF EXISTS `modif`;
DELIMITER $$
CREATE TRIGGER `modif`
AFTER update ON `test`
FOR EACH ROW
BEGIN
call `trait` (old.date);
END$$
DELIMITER ;
-- ===========
-- Jeu d'essai
-- ===========
select * from test;
select * from grouper;
insert into test (`date`, `kms`) values
('2015-12-01', 200), ('2015-11-01', 150), ('2015-11-17', 300), ('2015-10-13', 75), ('2015-10-23', 125), ('2015-12-25', 50);
select * from test;
select * from grouper;
delete from test where date = '2015-11-01';
select * from test;
select * from grouper;
update test set kms = 25 where date = '2015-10-13';
select * from test;
select * from grouper;
-- ===
-- Fin
-- ===
COMMIT;
SET AUTOCOMMIT = 1; |
Partager