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 151 152 153 154 155 156 157 158 159 160 161 162
| CREATE DATABASE '..\Data\Base.fdb' page_size 4096 DEFAULT CHARACTER SET WIN1252;
-- =============================
-- Création de la table 'PLAYER'
-- =============================
CREATE TABLE player (
CODEID INTEGER,
ICOM VARCHAR(05),
RCAD VARCHAR(06),
DPL DATE
);
-- =======================
-- Insertion dans 'PLAYER'
-- =======================
insert into player (codeid, icom, rcad, dpl) values (1, 'aaaaa', 'xxxxxx', NULL);
insert into player (codeid, icom, rcad, dpl) values (2, 'bbbbb', 'xxxxxx', '2016-09-13');
insert into player (codeid, icom, rcad, dpl) values (3, 'bbbbb', 'xxxxxx', '2016-09-12');
insert into player (codeid, icom, rcad, dpl) values (4, 'bbbbb', 'xxxxxx', '2016-09-13');
insert into player (codeid, icom, rcad, dpl) values (5, 'bbbbb', 'xxxxxx', '2016-09-10');
insert into player (codeid, icom, rcad, dpl) values (6, 'ddddd', 'yyyyyy', '2016-09-11');
-- ==================
-- Vidage de 'PLAYER'
-- ==================
select * from player;
CODEID ICOM RCAD DPL
============ ====== ====== ===========
1 aaaaa xxxxxx <null>
2 bbbbb xxxxxx 2016-09-13
3 bbbbb xxxxxx 2016-09-12
4 bbbbb xxxxxx 2016-09-13
5 bbbbb xxxxxx 2016-09-10
6 ddddd yyyyyy 2016-09-11
commit;
-- =============================
-- Création de la table 'PACMAN'
-- =============================
CREATE TABLE pacman
( ICOM VARCHAR(05),
RCAD VARCHAR(06),
VIG INTEGER,
DPL DATE
);
-- =======================
-- Insertion dans 'PACMAN'
-- =======================
insert into pacman (icom, rcad, vig, dpl) values ('aaaaa', 'xxxxxx', NULL, '2016-09-01');
insert into pacman (icom, rcad, vig, dpl) values ('bbbbb', 'xxxxxx', NULL, '2016-09-01');
insert into pacman (icom, rcad, vig, dpl) values ('ccccc', 'yyyyyy', NULL, '2016-09-01');
insert into pacman (icom, rcad, vig, dpl) values ('ddddd', 'yyyyyy', NULL, '2016-09-01');
insert into pacman (icom, rcad, vig, dpl) values ('ddddd', 'yyyyyy', NULL, '2016-09-01');
insert into pacman (icom, rcad, vig, dpl) values ('ccccc', 'yyyyyy', NULL, '2016-09-01');
insert into pacman (icom, rcad, vig, dpl) values ('bbbbb', 'xxxxxx', NULL, '2016-09-01');
insert into pacman (icom, rcad, vig, dpl) values ('aaaaa', 'xxxxxx', NULL, '2016-09-01');
-- ==================
-- Vidage de 'PACMAN'
-- ==================
select * from pacman;
ICOM RCAD VIG DPL
====== ====== ============ ===========
aaaaa xxxxxx <null> 2016-09-01
bbbbb xxxxxx <null> 2016-09-01
ccccc yyyyyy <null> 2016-09-01
ddddd yyyyyy <null> 2016-09-01
ddddd yyyyyy <null> 2016-09-01
ccccc yyyyyy <null> 2016-09-01
bbbbb xxxxxx <null> 2016-09-01
aaaaa xxxxxx <null> 2016-09-01
commit;
-- =================
-- procédure stockée
-- =================
SET TERM $;
create procedure maj
as
declare variable v_icom varchar(05);
declare variable v_rcad varchar(06);
declare variable v_test varchar(06);
declare variable v_dpl date;
begin
-- ----------------------------------
-- Suppression des doublons de Player
-- ----------------------------------
delete from player as t1
where exists (
select 1
from player as t2
where t2.icom = t1.icom
and t2.rcad = t1.rcad
and (t2.dpl > t1.dpl or (t2.dpl = t1.dpl and t2.codeid > t1.codeid))
);
-- ---------------------
-- Mise à jour de Pacman
-- ---------------------
for select t1.icom, t1.rcad, max(t2.rcad), max(t2.dpl)
from pacman as t1
left outer join player as t2
on t2.icom = t1.icom and t2.rcad = t1.rcad
group by t1.icom, t1.rcad
into :v_icom, :v_rcad, :v_test, :v_dpl
do if (:v_test is null)
then update pacman set dpl=null, vig=null where icom=:v_icom and rcad=:v_rcad;
else update pacman set dpl=:v_dpl, vig=1 where icom=:v_icom and rcad=:v_rcad;
end$
SET TERM ;$
commit;
execute procedure maj;
-- ========
-- Résultat
-- ========
select * from player;
CODEID ICOM RCAD DPL
============ ====== ====== ===========
1 aaaaa xxxxxx <null>
4 bbbbb xxxxxx 2016-09-13
6 ddddd yyyyyy 2016-09-11
select * from pacman;
ICOM RCAD VIG DPL
====== ====== ============ ===========
aaaaa xxxxxx 1 <null>
bbbbb xxxxxx 1 2016-09-13
ccccc yyyyyy <null> <null>
ddddd yyyyyy 1 2016-09-11
ddddd yyyyyy 1 2016-09-11
ccccc yyyyyy <null> <null>
bbbbb xxxxxx 1 2016-09-13
aaaaa xxxxxx 1 <null>
exit;
Appuyez sur une touche pour continuer... |
Partager