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
| DELIMITER $$
DROP PROCEDURE IF EXISTS `genmess`.`Archiv_annee` $$
CREATE PROCEDURE `genmess`.`Archiv_annee` (in nom varchar(15), in an year)
BEGIN
SET @i = IF(nom="vulco","vul",IF(nom="rossignol","ros",IF(nom="dunlop","dmd",IF(nom="demo","dem",null))));
SET @p = IF(nom="vulco",3,IF(nom="rossignol",5,IF(nom="dunlop",6,IF(nom="demo",4,null))));
SET @n = nom;
SET @a = an;
SET @c = CONCAT('archivenv_', @n,'_',@a);
SET @proc1 = CONCAT('INSERT INTO ', @c,' SELECT env_id AS ', @i,'_id, env_univers AS ', @i,'_univers, env_idaction AS ',
@i,'_idaction, env_modele AS ',@i,'_modele, env_date AS ',@i,'_date, env_codemanaginn AS ',
@i,'_codemanaginn, env_valeur AS ',@i,'_valeur, env_msg AS ',@i,'_msg, env_from AS ',
@i,'_from, env_cout AS ',@i,'_cout, env_periode AS ',@i,'_periode, env_id_expediteur AS ',
@i,'_id_expediteur, env_pro_id AS ',@i,'_pro_id, env_typmedia AS ',@i,'_typmedia, env_pj1 AS ',
@i,'_pj1, env_pj2 AS ',@i,'_pj2, env_pj3 AS ',@i,'_pj3, env_mailto AS ',@i,'_mailto, env_mailcc AS ',
@i,'_mailcc, env_mailbcc AS ',@i,'_mailbcc, env_subject AS ',@i,'_subject, env_fichcsv AS ',
@i,'_fichcsv FROM tbl_msgenv
WHERE ((EXTRACT(YEAR FROM tbl_msgenv.env_date))=',@a,' And (tbl_msgenv.env_univers=',@p,'));');
PREPARE fct FROM @proc1;
EXECUTE fct;
SET @proc2 = CONCAT('DELETE FROM tbl_msgenv
WHERE ((EXTRACT(YEAR FROM tbl_msgenv.env_date))=',@a,' And (tbl_msgenv.env_univers=',@p,'));');
PREPARE p2 FROM @proc2;
EXECUTE p2;
END $$
DELIMITER ; |
Partager