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