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
|
CREATE FUNCTION nb_AffairesEnCours
(numTass varchar(10), numSecuriteSociale varchar(13), nomAssure varchar(50), )
RETURNS INTEGER
BEGIN
DECLARE s_numTass VARCHAR(10);
DECLARE s_numSecuriteSociale VARCHAR(13);
DECLARE s_nomAssure VARCHAR(50);
DECLARE i_nbRetour INTEGER;
IF (numTass <> '')
THEN SET s_numTass = numTass;
END IF;
IF (numSecuriteSociale <> '')
THEN SET s_numSecuriteSociale = numSecuriteSociale;
END IF;
IF (nomAssure <> '')
THEN SET s_nomAssure = nomAssure;
END IF;
SELECT
COUNT(num_Tass) INTO i_nbRetour
FROM
tb_affaires
WHERE (Date_Cloture IS NULL) AND
(s_numTass IS NULL OR (s_numTass IS NOT NULL AND num_Tass = s_numTass)) AND
(s_numSecuriteSociale IS NULL OR (s_numSecuriteSociale IS NOT NULL AND num_Securite_Sociale = s_numSecuriteSociale)) AND
(s_nomAssure IS NULL OR (s_nomAssure IS NOT NULL AND nom_Assure LIKE ('%s_nomAssure%')));
RETURN i_nbRetour;
END; |
