pdf - e-book - archive

1.8  Procédures stockées

1.8.1  Exemple

É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 ?

1.8.2  SQL Procédural

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 ;

Télécharger le fichier

1.8.3  Procédures

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

Télécharger le fichier

1.8.4  Curseurs

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 ;

Télécharger le fichier

1.8.5  Triggers

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 ;

Télécharger le fichier