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; |
