Correction exercices les procédures stockées MySQL : Série 01

CREATE OR REPLACE PROCEDURE update_salaire(idemp IN Employe.nuempl%type, sal IN number)
IS
BEGIN
    UPDATE EMPLOYE SET salaire=salaire+sal WHERE nuempl=idemp;
END;

CREATE FUNCTION count_employes_proj (nuproj integer) RETURNS integer AS $$
    SELECT COUNT(*) FROM Travail WHERE nuproj = $1;
$$ LANGUAGE SQL;

CREATE FUNCTION count_projets_supervises (nuserv integer) RETURNS integer AS $$
    SELECT COUNT(*) FROM Projet WHERE resp IN (
        SELECT nuempl FROM Employe WHERE #affect = $1
    );
$$ LANGUAGE SQL;

CREATE FUNCTION count_projets_employe (nuempl integer) RETURNS integer AS $$
    SELECT COUNT(*) FROM Travail WHERE nuempl = $1;
$$ LANGUAGE SQL;

CREATE FUNCTION check_salaire (nuempl integer) RETURNS varchar(20) AS $$
    SELECT CASE WHEN salaire > 2000 THEN 'Salaire faible' ELSE 'Bon salaire' END FROM Employe WHERE nuempl = $1;
$$ LANGUAGE SQL;

CREATE FUNCTION count_employes_plus_proj (n integer) RETURNS integer AS $$
    SELECT COUNT(*) FROM (
        SELECT COUNT(*) as projets, nuempl FROM Travail GROUP BY nuempl
    ) as count_projets WHERE projets > $1;
$$ LANGUAGE SQL;

CREATE PROCEDURE insert_alert_employe (nuempl varchar(20), nomempl varchar(20), hebdo integer, salaire integer, #affect integer) AS $$
    INSERT INTO ALERT_EMPLOYE (nuempl, nomempl, hebdo, salaire, #affect) VALUES ($1, $2, $3, $4, $5);
$$ LANGUAGE SQL;

CREATE FUNCTION jours_restants_avant_retard(numab VARCHAR(3)) RETURNS INT
BEGIN
  DECLARE nb_emprunts INT;
  DECLARE nb_retards INT;
  DECLARE min_jours_restants INT;
  DECLARE max_retard INT;
  DECLARE min_date_retour DATE;
  DECLARE min_duree INT;
  DECLARE i INT DEFAULT 0;
  
  -- compter le nombre d'emprunts en cours de l'adhérent
  SELECT COUNT(*) INTO nb_emprunts FROM pret WHERE numab=numab AND dateretour IS NULL;
  
  -- si l'adhérent n'a pas d'emprunts en cours, on renvoie NULL
  IF nb_emprunts = 0 THEN
    RETURN NULL;
  ELSE
    SET max_retard = 0;
    SET min_jours_restants = NULL;
    
    -- parcourir tous les emprunts en cours de l'adhérent
    WHILE i < nb_emprunts DO
      SELECT MIN(date_add(datepret, INTERVAL duree DAY)), MIN(duree)
      INTO min_date_retour, min_duree
      FROM pret, livre
      WHERE pret.numinv = livre.numinv AND numab = numab AND dateretour IS NULL;
      
      IF min_date_retour < CURDATE() THEN
        SET nb_retards = DATEDIFF(CURDATE(), min_date_retour);
        IF nb_retards > max_retard THEN
          SET max_retard = nb_retards;
        END IF;
      ELSE
        SET nb_retards = DATEDIFF(min_date_retour, CURDATE());
        IF min_jours_restants IS NULL OR nb_retards < min_jours_restants THEN
          SET min_jours_restants = nb_retards;
        END IF;
      END IF;
      
      SET i = i + 1;
    END WHILE;
    
    IF max_retard > 0 THEN
      RETURN -max_retard;
    ELSE
      RETURN min_jours_restants;
    END IF;
  END IF;
END;

SELECT numab, nom, prenom, jours_restants_avant_retard(numab) AS jours_restants FROM abonne;

CREATE PROCEDURE liste_emprunts(numab VARCHAR(3))
BEGIN
  SELECT livre.numinv, titre, datepret, duree
  FROM pret, livre
  WHERE pret.numinv = livre.numinv AND numab = numab AND dateretour IS NULL;
END;

Version OUTER JOIN :

CREATE PROCEDURE ExemplairesDispoOuterJoin (IN titreRecherche VARCHAR(20))
BEGIN
    SELECT exemplaire.numinv, livre.titre, livre.auteur 
    FROM livre 
    LEFT OUTER JOIN exemplaire 
    ON livre.numinv = exemplaire.numinv 
    WHERE livre.titre = titreRecherche 
    AND exemplaire.numinv IS NULL;
END

Version NOT IN :

CREATE PROCEDURE ExemplairesDispoNotIn (IN titreRecherche VARCHAR(20))
BEGIN
    SELECT numinv, titre, auteur 
    FROM livre 
    WHERE titre = titreRecherche 
    AND numinv NOT IN 
    (SELECT numinv FROM pret WHERE dateret IS NULL);
END

CREATE PROCEDURE ExemplairesDispoAuteur (IN nomAuteur VARCHAR(15))
BEGIN
    DECLARE auteurRecherche VARCHAR(15);
    DECLARE curTitres CURSOR FOR SELECT DISTINCT titre FROM livre WHERE auteur = nomAuteur;
    DECLARE curTitresCount CURSOR FOR SELECT COUNT(*) FROM exemplaire WHERE numinv IN (SELECT numinv FROM livre WHERE auteur = auteurRecherche AND titre = titreRecherche AND numinv NOT IN (SELECT numinv FROM pret WHERE dateret IS NULL));
    DECLARE titreRecherche VARCHAR(20);
    DECLARE nbExemplaires INT(2);

    SET auteurRecherche = nomAuteur;

    OPEN curTitres;
    titreLoop: LOOP
        FETCH curTitres INTO titreRecherche;
        IF (titreRecherche IS NULL) THEN
            LEAVE titreLoop;
        END IF;
        OPEN curTitresCount;
        FETCH curTitresCount INTO nbExemplaires;
        CLOSE curTitresCount;
        SELECT CONCAT(titreRecherche, ' (', nbExemplaires, ' exemplaires disponibles)') AS result;
    END LOOP;
    CLOSE curTitres;
END

CREATE PROCEDURE EnregistrerEmprunt (IN numAbonne VARCHAR(3), IN numInv VARCHAR(4), IN datePret DATE)
BEGIN
    INSERT INTO pret (numab, numinv, datepret) VALUES (numAbonne, numInv, datePret);
    UPDATE exemplaire SET qte = qte - 1 WHERE numinv = numInv;
END

SELECT numab, nom, prenom, jours_restants_avant_retard(numab) AS jours_restants FROM abonne;

ALTER TABLE pret ALTER COLUMN dureemax SET DEFAULT 14;