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
| CREATE OR REPLACE PROCEDURE pi_ajout_candidat
(
INOUT id_personne_physique INT, -- Identifiant du candidat
IN nom_usuel VARCHAR(48), -- Nom usuel du candidat
IN prenom VARCHAR(32), -- Prénom principal candidat
IN id_civilite INT, -- Identifiant de la civilité du candidat
IN id_sexe INT, -- Identifiant du sexe du candidat
IN id_nationalite INT, -- Identifiant du pays de nationalité du candidat
IN id_ville_naissance INT, -- Identifiant de la ville de naissance du candidat
IN nom_naissance VARCHAR(48), -- Nom de naissance du candidat
IN date_naissance DATE, -- Date de naissance du candidat
IN autres_prenoms VARCHAR(48), -- Autres prénoms du candidat (séparés par une virgule et un espace)
IN num_ine CHAR(11) -- Numéro INE du candidat
)
BEGIN
DECLARE nombre INT DEFAULT 0; -- Utilisé pour les comptages
DECLARE msg_erreur VARCHAR(64); -- Texte du message d'erreur éventuel retourné
-- Handler d'erreur de clé étrangère non satisfaite
DECLARE fk_exception CONDITION FOR SQLSTATE '23000'; -- Erreur de clé étrangère
DECLARE EXIT HANDLER FOR fk_exception
BEGIN
ROLLBACK TO SAVEPOINT debut;
SIGNAL SQLSTATE VALUE '23000'
SET MESSAGE_TEXT = msg_erreur;
END;
-- On vérifie par les infos fournies si le candidat existe déjà
-- (identifiant du candidat, INE ou triplet {nom (usuel ou naissance), prénom usuel, date naissance})
SELECT COUNT(*) INTO nombre
FROM v_candidat
WHERE canId = id_personne_physique
OR canIne = num_ine
OR
(
canPrenomUsuel = prenom
AND canDateNaissance = date_naissance
AND
(
canNomUsuel = nom_usuel
OR canNomNaissance = nom_naissance
)
);
IF nombre > 0 THEN
-- Candidat existant => erreur
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Candidat déjà existant';
END IF;
/***** Vérification et traitement des données *****/
SET AUTOCOMMIT = 0;
START TRANSACTION;
SAVEPOINT debut;
IF id_personne_physique IS NULL THEN
-- Si l'identifiant de la personne n'est pas fourni, on lance la procédure d'ajout d'une personne physique
CALL pi_ajout_personne_physique(nom_usuel, prenom, id_civilite, @id);
SET id_personne_physique = @id;
ELSE
-- On vérifie que l'id_personne_physique fourni existe en tant que personne physique
SELECT COUNT(*) INTO nombre
FROM th_personne_physique_pph
WHERE pph_id_personne = id_personne_physique;
IF nombre = 0 THEN
-- Personne non trouvée, on lance la procédure d'ajout d'une personne physique
CALL pi_ajout_personne_physique(nom_usuel, prenom, id_civilite, @id);
SET id_personne_physique = @id;
END IF;
END IF; -- Fin du ELSE de IF id_personne_physique IS NULL THEN
-- Vérification de l'id_sexe
-- Vérification de la nationalité
-- Vérification de la ville de naissance
-- Vérification du nom de naissance
-- Vérification de la cohérence de la date de naissance (age >= 18)
-- Vérification du numéro d'INE (11 caractères oblibatoires si non null ou vide)
-- Enregistrement du candidat
INSERT INTO th_candidat_can (can_id_personne_physique, can_id_sexe, can_id_nationalite,
can_id_ville_naissance, can_nom_naissance, can_date_naissance, can_autres_prenoms, can_ine)
VALUES (id_personne_physique, id_sexe, id_nationalite, id_ville_naissance, nom_naissance, date_naissance,
autres_prenoms, num_ine);
COMMIT;
SET AUTOCOMMIT = 1;
END; |
