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
| SET AUTOCOMMIT = 0;
START TRANSACTION;
-- ============
-- Base `Essai`
-- ============
DROP DATABASE IF EXISTS `essai`;
CREATE DATABASE `essai`
DEFAULT CHARACTER SET `utf8`
DEFAULT COLLATE `utf8_general_ci`;
USE `essai`;
COMMIT;
-- ================
-- Table `entrepot`
-- ================
DROP TABLE IF EXISTS `entrepot`;
CREATE TABLE `entrepot` (
`Nom` Char(12) NOT NULL,
`Date` date NOT NULL,
`Heure` time NOT NULL,
`Rang` SMALLINT NOT NULL,
`Val` int NOT NULL,
Primary Key (`Nom`,`Date`,`Heure`,`Rang`)
) ENGINE=InnoDB
DEFAULT CHARSET=`utf8` COLLATE=`utf8_general_ci`
ROW_FORMAT=COMPRESSED;
-- ===================
-- Trigger `increment`
-- ===================
DROP TRIGGER IF EXISTS `increment`;
DELIMITER $$
CREATE TRIGGER `increment`
BEFORE INSERT ON `entrepot`
FOR EACH ROW BEGIN
SET NEW.Rang=ifnull((select max(Rang)+1 from `entrepot` where Nom=NEW.Nom and Date=NEW.Date and Heure=NEW.Heure),1);
END$$
DELIMITER ;
-- =====================
-- Procédure `rangement`
-- =====================
DROP PROCEDURE IF EXISTS Remplir;
DELIMITER $$
CREATE PROCEDURE Remplir
(
IN In_Nom CHAR(12),
IN In_Date CHAR(10),
IN In_Heure time,
IN In_Val1 int,
IN In_Val2 int,
IN In_Val3 int,
IN In_Val4 int,
IN In_Val5 int,
IN In_Val6 int
)
DETERMINISTIC
NO SQL
BEGIN
DECLARE Out_Date date;
SET Out_Date = str_to_date(In_Date,'%d/%m/%Y');
insert into `entrepot` (`Nom`,`Date`,`Heure`,`Val`) value (In_Nom,Out_Date, addtime(In_Heure, '00:00:00'), In_Val1);
insert into `entrepot` (`Nom`,`Date`,`Heure`,`Val`) value (In_Nom,Out_Date, addtime(In_Heure, '00:10:00'), In_Val2);
insert into `entrepot` (`Nom`,`Date`,`Heure`,`Val`) value (In_Nom,Out_Date, addtime(In_Heure, '00:20:00'), In_Val3);
insert into `entrepot` (`Nom`,`Date`,`Heure`,`Val`) value (In_Nom,Out_Date, addtime(In_Heure, '00:30:00'), In_Val4);
insert into `entrepot` (`Nom`,`Date`,`Heure`,`Val`) value (In_Nom,Out_Date, addtime(In_Heure, '00:40:00'), In_Val5);
insert into `entrepot` (`Nom`,`Date`,`Heure`,`Val`) value (In_Nom,Out_Date, addtime(In_Heure, '00:50:00'), In_Val6);
END$$
DELIMITER ;
-- =====================
-- Creation vue `result`
-- =====================
create view `result` (Nom, Date, Heure, Val1, Val2, Val3, Val4) as
select nom, date, heure, val, 0, 0, 0 from entrepot where rang = 1 union
select nom, date, heure, 0, val, 0, 0 from entrepot where rang = 2 union
select nom, date, heure, 0, 0, val, 0 from entrepot where rang = 3 union
select nom, date, heure, 0, 0, 0, val from entrepot where rang = 4
;
COMMIT;
SET AUTOCOMMIT = 1; |
Requêtes multiple en Update et insert into
Répondre avec citation
Envoyé par loic3484
Partager