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
| CREATE DATABASE '..\Data\Base.fdb' page_size 4096 DEFAULT CHARACTER SET ISO8859_1;
-- ============================
-- Création de la table 'cotis'
-- ============================
CREATE TABLE cotis (
ID_EMP INTEGER NOT NULL,
MOIS_COTIS SMALLINT NOT NULL,
MONTANT DECIMAL(15,2) NOT NULL,
PRIMARY KEY (ID_EMP,MOIS_COTIS)
);
-- ======================
-- Insertion dans 'cotis'
-- ======================
insert into cotis (id_emp, mois_cotis, montant) values (1, 1, 100.00);
insert into cotis (id_emp, mois_cotis, montant) values (1, 2, 100.00);
insert into cotis (id_emp, mois_cotis, montant) values (1, 3, 100.00);
insert into cotis (id_emp, mois_cotis, montant) values (1, 4, 100.00);
insert into cotis (id_emp, mois_cotis, montant) values (1, 5, 100.00);
insert into cotis (id_emp, mois_cotis, montant) values (2, 1, 150.00);
insert into cotis (id_emp, mois_cotis, montant) values (2, 4, 150.00);
insert into cotis (id_emp, mois_cotis, montant) values (2, 5, 150.00);
-- =================
-- Vidage de 'cotis'
-- =================
select * from cotis;
ID_EMP MOIS_COTIS MONTANT
============ ========== =====================
1 1 100.00
1 2 100.00
1 3 100.00
1 4 100.00
1 5 100.00
2 1 150.00
2 4 150.00
2 5 150.00
-- ==================
-- Creation Procédure
-- ==================
SET TERM $;
create procedure test(emp integer,
deb smallint,
fin smallint)
returns (result char(01))
as
declare diff smallint;
declare calc smallint;
begin
diff = fin - deb + 1;
select count(distinct mois_cotis)
from cotis
where id_emp = :emp
and mois_cotis between :deb and :fin
group by id_emp
into calc;
if (diff = calc) then result = 'T'; else result = 'F';
suspend;
end$
SET TERM ;$
-- ===================
-- Execution procédure
-- ===================
select * from test(1, 1, 4);
RESULT
======
T
select * from test(2, 1, 4);
RESULT
======
F
exit;
Appuyez sur une touche pour continuer... |
Partager