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; |