Outils pour utilisateurs

Outils du site


bases_de_donnees:oracle:pl_sql_labs

Ceci est une ancienne révision du document !


Labo 2

Exo 1

Proposez un bloc PL/SQL qui utilise un curseur pour afficher la liste des ISBN et Titres des document de la bibliothèque.

DECLARE
  CURSOR docs IS SELECT isbn, titre FROM document;
  i NUMBER(10);
  t VARCHAR2(50);
BEGIN
  FOR d IN docs LOOP
    i := d.isbn;
    t := d.titre;
    DBMS_OUTPUT.put_line('ISBN : '||i||' TITRE : '||t);
  END LOOP;
  DBMS_OUTPUT.put_line('Fin de traitement');
END;

Exo 2

Utiliser un curseur pour parcourir tous les exemplaires de la bibliothèque qui ont été empruntés au moins dix fois.

/*
  Labo 2 - exercice 2.
  1ère solution : le filtrage des données est effectué dans le select du curseur.
*/
DECLARE
  CURSOR emps IS SELECT isbn,exemplaire,COUNT(*) AS nb FROM emprunt GROUP BY isbn,exemplaire HAVING COUNT(*)>10;
  i NUMBER(10);
  n NUMBER;
BEGIN
  FOR e IN emps LOOP
    i := e.isbn;
    n := e.exemplaire;
    DBMS_OUTPUT.put_line('ISBN : '||i||' EXEMPLAIRE : '||n||' Nb emprunts : '||e.nb);
  END LOOP;
  DBMS_OUTPUT.put_line('Fin de traitement');
END;
/
/*
  Labo 2 - exercice 2.
  2ème solution : le filtrage des données est effectué dans le traitement effectué sur le curseur.
*/
DECLARE
  CURSOR exemps IS SELECT isbn,numero FROM exemplaire;
  i NUMBER(10);
  n NUMBER;
BEGIN
  FOR e IN exemps LOOP
    SELECT COUNT(*)
    INTO n
    FROM emprunt
    WHERE isbn=e.isbn AND exemplaire=e.numero;
--    dbms_output.put_line('n : '||n);
    IF (n>10) THEN
      DBMS_OUTPUT.put_line('ISBN : '||e.isbn||' EXEMPLAIRE : '||e.numero||' Nb emprunts : '||n);
    END IF;
  END LOOP;
  DBMS_OUTPUT.put_line('Fin de traitement');
END;
/

Exo 3

Proposez un bloc PL/SQL qui utilise un curseur pour mettre à jour les états des exemplaires selon les règles suivantes :

  1. Si le nombre d'emprunts est inférieur à 5, l'état est mis à neuf
  2. Si le nombre d'emprunts est inférieur à 10, l'état est mis à bon
  3. Si le nombre d'emprunts est inférieur à 15, l'état est mis à moyen
  4. Sinon l'état est mis à mauvais
DECLARE
  CURSOR exemps IS SELECT * FROM exemplaire FOR UPDATE OF etat;
  i NUMBER(10);
  n NUMBER;
  s exemplaire.etat%TYPE;
BEGIN
  FOR e IN exemps LOOP
    SELECT COUNT(*)
    INTO n
    FROM emprunt
    WHERE isbn=e.isbn AND exemplaire=e.numero;
    CASE
      WHEN n<1 THEN s:= 'Neuf';
      WHEN n<2 THEN s:= 'Bon';
      WHEN n<3 THEN s:= 'Moyen';
      ELSE s:='Mauvais';
    END CASE;
 
    UPDATE exemplaire
    SET etat = s
    WHERE CURRENT OF exemps;
  END LOOP;
  DBMS_OUTPUT.put_line('Fin de traitement');
END;

Exo 4

Utilise un curseur pour supprimer tous les membres dont l'adhésion a expiré depuis plus d'une année et qui ont rendu tous les document empruntés. Dans la table EMPRUNT, les numéros des membres (champ MEMBRE) supprimés seront mis à NULL.

DECLARE
  CURSOR membres IS SELECT * FROM membre WHERE FINABONNEMENT<SYSDATE-365;
  i NUMBER(10);
  n NUMBER;
  s exemplaire.etat%TYPE;
BEGIN
  FOR m IN membres LOOP
    SELECT COUNT(*)
    INTO n
    FROM emprunt
    WHERE membre=m.numero AND rendule>SYSDATE;
DBMS_OUTPUT.put_line('membre : '||m.numero||' Nb emprunt non rendus : '||n);
    IF (n=0) THEN
      --Mise à jour de la table emprunt :
      UPDATE emprunt
      SET membre=NULL
      WHERE membre=m.numero;
 
      --Suppression du membre :
      DELETE FROM MEMBRE
      WHERE numero = m.numero;
      DBMS_OUTPUT.put_line('membre : '||m.numero||' supprimé.');
    END IF;
  END LOOP;
  DBMS_OUTPUT.put_line('Fin de traitement');
END;
/

Labo 3

Exo 1

Proposez un bloc PL/SQL qui prolonge de 90 jours les abonnements de tous les membres.

DECLARE
  CURSOR mbrs IS SELEC numero,finabonnement FROM Membre FOR UPDATE OF finabonnement;
 
BEGIN
  FOR m IN mbrs LOOP
    UPDATE Membre
    SET finabonnement=m.finabonnement+90
    WHERE CURRENT OF mbrs;
  END FOR;
END;

Exo 2

Proposez un bloc PL/SQL qui affiche la liste des trois membres qui ont emprunté le plus de documents durant la dernière année.

DECLARE
  CURSOR smem IS 
  SELECT nom, prenom, membre.numero, COUNT(membre) AS nombre 
  FROM membre, emprunt
  WHERE MEMBRE.NUMERO>EMPRUNT.MEMBRE
  AND RENDULE > SYSDATE-325
  GROUP BY NOM, PRENOM, MEMBRE.NUMERO
  ORDER BY 4 DESC, 1;
  nom MEMBRE.Nom%TYPE
  prenom MEMBRE.PRENOM%TYPE
  Numero NUMBER(10)
 
BEGIN
  OPEN smem;
  FOR i IN 1..3 LOOP
    FETCH smem INTO nom, prenom, numero.membre;
    DBMS_OUTPUT.put_line('Nombre : '||nom||'Prenom'||'numero:'||TO_CHAR(numero)||'Nombre:'||TO_CHAR(Nombre));
  END LOOP;
  CLOSE smem;
END;

Solution alternative :

DECLARE
  CURSOR mbr IS  
    SELECT membre, COUNT(*) AS cpt
    FROM emprunt
    WHERE rendule >= SYSDATE -365
    GROUP BY membre
    ORDER BY cpt DESC;
  nbr numbre := 0;
  aff_membre MEMBRE%ROWTYPE;
BEGIN
  FOR i IN mbr LOOP
    EXIT WHEN mbr%NOTFOUND;
    SELECT * INTO aff_membre
    FROM membre
    WHERE numero=i.membre;
    DBMS_OUTPUT.put_line('Nombre : '||nom||'Prenom'||'numero:'||TO_CHAR(numero)||'Nombre:'||TO_CHAR(Nombre));
    nbr := nbr + 1;
    EXIT WHEN nbr>=3;
  END LOOP;
END;

Exo 3

Proposez un bloc PL/SQL qui prolonge d'une année les abonnements des trois membres qui ont emprunté le plus de documents durant la dernière année.

DECLARE
  CURSOR mbr IS  
    SELECT membre, COUNT(*) AS cpt
    FROM emprunt
    WHERE rendule >= SYSDATE -365
    GROUP BY membre
    ORDER BY cpt DESC;
  nbr numbre := 0;
  aff_membre MEMBRE%ROWTYPE;
BEGIN
  FOR i IN mbr LOOP
    EXIT WHEN mbr%NOTFOUND;
    SELECT * INTO aff_membre
    FROM membre
    WHERE numero=i.membre;
    UPDATE membre SET finabonnement=finabonnement+365 WHERE numero = mbr.numero;
    DBMS_OUTPUT.put_line('Nombre : '||nom||'Prenom'||'numero:'||TO_CHAR(numero)||'Nombre:'||TO_CHAR(Nombre));
    nbr := nbr + 1;
    EXIT WHEN nbr>=3;
  END LOOP;
END;

Exo 4

Proposez un bloc PL/SQL qui affiche les noms téléphones des membres qui ont un retard de plus de 30 jours dans la remise d'au moins un document emprunté. De plus, le programme doit afficher, pour chaque membre, l'ISBN, le numéro d'exemplaire et le titre de chacun des documents en retard.

SET SERVEROUPUT ON SIZE 50000;
 
DECLARE
  CURSOR emp IS SELECT *FROM emprunt WHERE rendu IS NULL AND rendule > SYSDATE - 30 ORDER BY membre;
  TYPE mbr IS RECORD(n membre.nom%TYPE, t membre.tel%TYPE);
  m mbr;
  tre document.titre%TYPE;
  nummbr membre.numero%TYPE := -1;
 
BEGIN
  FOR i IN emp LOOP
    IF nummbr <> i.membre THEN
      nummbr := i.membre;
      SELECT nom, tel INTO m FROM membre WHERE numero = nummbr;
      DBMS_OUTPUT.put_line('nom :'||m.n||'Telephone : '||m.t);
    END IF;
    SELECT titre INTO tre FROM document WHERE isbn = i.isbn;
    DBMS_OUTPUT.put_line('isbn: '|| t.isbn||'Numero exemplaire : '||i.numero||'Titre :'||tre);
  END LOOP;
END;

Labo 4

Ex 1

PROCEDURE afficheDocParType(DocType document.type%TYPE IN) IS
  CURSOR SDoc IS
    SELECT ISBN, titre, auteur
    FROM document
    WHERE TYPE = DocType;
 
BEGIN
  FOR doc IN SDoc LOOP
    dbms.output.putline('ISBN: ' || doc.isbn || 'Titre: ' || doc.titre || 'Auteur :' || doc.auteur);
  END LOOP;
END;

Ex 2

FUNCTION getNbDocParType(docType DOCUMENT.TYPE%TYPE)
RETURN NUMBER IS
 
DECLARE 
  n NUMBER;
BEGIN
  SELECT COUNT(*) INTO n
  FROM DOCUMENT
  WHERE TYPE = docType;
  RETURN n;
END;

Exemple d'utilisation

Exemple d'utilisation :

DECLARE 
  t DOCUMENT.TYPE%TYPE
  n NUMBER
BEGIN
  t := 'Livre';
  AfficherDocParType(t);
  n := getNbDocParType(t);
  DBMS_OUTPUT.putline('Il y a '|| n ||' ' || t ||' dans la bibliotheque');
END;

Labo 5

Exercice 1

CREATE OR REPLACE TRIGGER stats_emprunts
AFTER INSERT
ON EMPRUNT
FOR EACH ROW
BEGIN
  INSERT INTO stat_emprunt(isbn) VALUES(:NEW.isbn);
EXCEPTION
  WHEN DUP_VAL_ON_INDEX THEN
    UPDATE stat_emprunt
    SET NBEMPRUNT = NBEMPRUNT+1
    WHERE isbn = :NEW.isbn;
END;
/

Exercice 2

SET serveroutput ON;
 
CREATE OR REPLACE TRIGGER supp_emprunt
BEFORE DELETE
ON EMPRUNT
FOR EACH ROW
 
BEGIN
  IF (:old.rendule IS NULL) THEN
    RAISE_APPLICATION_ERROR(-20100,'table emprunt : supp impossible, document non rendu');
  END IF;
 
  INSERT INTO archiv_emprunt VALUES (:old.numero,:old.membre, :old.isbn, :old.exemplaire, :old.rendule);
END;
/

Labo 6

Exercice 1

Définissez un déclencheur qui supprime tous les exemplaires d’un document dès que le document est supprimé de la bibliothèque.

CREATE OR REPLACE TRIGGER supp_exemplaires
BEFORE DELETE
ON BIB_DOCUMENT
FOR EACH ROW
 
BEGIN
  DELETE FROM BIB_EXEMPLAIRE WHERE ISBN = :old.ISBN;
END;

Ce trigger fonctionne, mais comme Oracle permet d'effacer les détails (table exemplaire) depuis la table maître (table document) dû a une contrainte d'intégrité référentielle, l'effacement devrait se faire par CASCADE.

Exercice 2

Définissez un déclencheur qui permet de s’assurer que lors d’un emprunt, l’adhésion du membre est encore valide.

CREATE OR REPLACE TRIGGER verif_emprunt
BEFORE INSERT
ON BIB_EMPRUNT
FOR EACH ROW
 
DECLARE
  fa BIB_MEMBRE.FINABONNEMENT%TYPE;  -- fin abonnement
 
BEGIN
  SELECT finabonnement INTO fa FROM BIB_MEMBRE WHERE NUMERO = :NEW.membre;
  IF (fa <= SYSDATE) THEN
    RAISE_APPLICATION_ERROR(-20100,'table emprunt : insertion impossible, le membre n est pas abonné.');
  END IF;  
 
END;

Exercice 3

Définissez un déclencheur qui interdit la modification du numéro de membre d’un emprunt.

CREATE OR REPLACE TRIGGER interdire_modif
BEFORE UPDATE
ON BIB_EMPRUNT
FOR EACH ROW
 
BEGIN
  IF (:old.membre <> :NEW.membre) THEN
    RAISE_APPLICATION_ERROR(-20100,'table emprunt : mise a jour impossible, le membre n est pas modifiable.');
  ELSE
    UPDATE bib_emprunt
    SET 
        isbn = :NEW.isbn,
        exemplaire = :NEW.exemplaire,
        rendule = :NEW.rendule,
        rendu = :NEW.rendu
    WHERE Numero = :NEW.numero;  
  END IF;     
END;

Exercice 4

Définissez un déclencheur qui met à jour automatiquement l’état des exemplaires en fonction du nombre d’emprunts. Par exemple :

  • Si le nombre d’emprunts est inférieur à 2, l’état est mis à Neuf ;
  • Si le nombre d’emprunts est inférieur à 4, l’état est mis à Bon ;
  • Si le nombre d’emprunts est inférieur à 6, l’état est mis à Moyen ;
  • Sinon, l’état est mis à Mauvais ;
CREATE OR REPLACE TRIGGER maj_etats
AFTER INSERT
ON BIB_EMPRUNT
FOR EACH ROW
 
DECLARE
  n NUMBER;
 
BEGIN
  SELECT COUNT(*) INTO n FROM BIB_EMPRUNT WHERE EXEMPLAIRE = :NEW.exemplaire AND ISBN = :NEW.isbn;
  IF n<2 THEN
    UPDATE BIB_EXEMPLAIRE SET etat = 'Neuf' WHERE ISBN = :NEW.isbn AND EXEMPLAIRE = :NEW.exemplaire;
  ELSE IF n<4 THEN
    UPDATE BIB_EXEMPLAIRE SET etat = 'Bon' WHERE ISBN = :NEW.isbn AND EXEMPLAIRE = :NEW.exemplaire;
  ELSE IF n<6 THEN
    UPDATE BIB_EXEMPLAIRE SET etat = 'Moyen' WHERE ISBN = :NEW.isbn AND EXEMPLAIRE = :NEW.exemplaire;
  ELSE  
    UPDATE BIB_EXEMPLAIRE SET etat = 'Mauvais' WHERE ISBN = :NEW.isbn AND EXEMPLAIRE = :NEW.exemplaire;
  END IF;
END;
bases_de_donnees/oracle/pl_sql_labs.1438386138.txt.gz · Dernière modification : 2022/02/02 00:43 (modification externe)