Étant données la base de données de [A.10]. Nous comptons implémenter les contraintes suivantes.
Toutes ces contraintes ne sont pas déclaratives, ce qui signifie
qu’il est impossible dans le
create table de les prendre en compte.
Comment faire ?
Le SQL procédural est une extension impérative de SQL. Elle permet d’exécuter des instructions à l’intérieur du serveur de base de données. Par exemple,
delimiter $$
drop procedure compteARebours;
create procedure compteARebours(i integer)
begin
declare j integer;
if i >= 0 then
set j = i;
while j >= 0 do
select j;
set j = j - 1;
end while;
end if;
end;
call compteARebours(3);
$$
delimiter ;
Il est possible de stocker des procédures de la même façon que dans les langages impératifs.
delimiter $$
drop procedure insertAdherent;
create procedure insertAdherent (nom varchar(64), prenom varchar(64), mail varchar(64))
begin
insert into personne (nompers, prenompers) values (nom, prenom);
insert into adherent (numpers, mailadherent) values (last_insert_id(), mail);
end
$$
call insertAdherent('Morflegroin', 'Marcel', 'marcel@morflegroin.com');
call insertAdherent('Le Ballon', 'Gégé', 'gege.m@grosbuveur.com');
call insertAdherent('Couledru', 'Gertrude', 'g.proflechettes@ligue-flechettes.fr');
$$
delimiter ;
insert into personne (nompers, prenompers) values ('Rowlings', 'J. K.');
delimiter $$
drop procedure insertOuvrage;
create procedure insertOuvrage (titre varchar(64), numAuteur integer, nombreExemplaires integer)
begin
declare ouvrage_inserted_id integer;
declare i integer;
insert into ouvrage(numauteur, titreouvrage) values (numAuteur, titre);
set ouvrage_inserted_id = last_insert_id();
set i = 1;
while i <= nombreExemplaires do
insert into exemplaire (numOuvrage, numExemplaire) values (ouvrage_inserted_id, i);
set i = i + 1;
end while;
end
$$
delimiter ;
call insertOuvrage('Harry Potter and the Deathly Hallows', 4, 10);
Un curseur permet de parcourir une à une les lignes résultant d’un SELECT.
delimiter $$
DROP PROCEDURE IF EXISTS AfficheUtilisateurs;
CREATE PROCEDURE AfficheUtilisateurs()
BEGIN
DECLARE num_pers integer;
DECLARE nom_pers varchar(64);
DECLARE prenom_pers varchar(64);
DECLARE nb_a integer;
DECLARE finished boolean DEFAULT FALSE;
DECLARE personnes CURSOR FOR SELECT numpers, nompers, prenompers FROM personne;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = TRUE;
OPEN personnes;
personnesloop: LOOP
FETCH personnes INTO num_pers, nom_pers, prenom_pers;
IF finished THEN
LEAVE personnesloop;
END IF;
SELECT COUNT(*) INTO nb_a FROM adherent WHERE numpers = num_pers;
IF nb_a > 0 THEN
select concat(prenom_pers, ' ', nom_pers, ' est un adherent');
ELSE
select concat(prenom_pers, ' ', nom_pers, ' est un auteur');
END IF;
END LOOP;
CLOSE personnes;
END;
$$
CALL AfficheUtilisateurs();
$$
delimiter ;
Un trigger est une procédure stockée de déclenchant automatiquement à la suite d’un événement.
delimiter $$
drop trigger if exists adherentBeforeInsert;
create trigger adherentBeforeInsert before insert on adherent for each row
begin
declare nb_aut integer;
declare error_msg varchar(128);
/* Met la date système par défaut */
if new.daterenouvellement is null then
set new.daterenouvellement = now();
end if;
/* Vérifie que l'adhérent n'est pas déjà un auteur */
select count(*) into nb_aut
from ouvrage
where new.numpers = numauteur;
if nb_aut > 0 then
set error_msg = concat('L\'adhérent ', new.numpers, 'est déjà un auteur.');
signal sqlstate '45000' set message_text = error_msg;
end if;
end;
$$
delimiter ;