Outils pour utilisateurs

Outils du site


bases_de_donnees:oracle:pl_sql_labs

Différences

Ci-dessous, les différences entre deux révisions de la page.

Lien vers cette vue comparative

bases_de_donnees:oracle:pl_sql_labs [2015/07/31 19:42] (Version actuelle)
Ligne 1: Ligne 1:
 +====== 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.
 +<code plsql>
 +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;
 +</​code>​
 +
 +
 +===== Exo 2 =====
 +Utiliser un curseur pour parcourir tous les exemplaires de la bibliothèque qui ont été empruntés au moins dix fois.
 +
 +<code plsql>
 +/*
 +  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;
 +/
 +</​code>​
 +<code plsql>
 +/*
 +  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;
 +/
 +</​code>​
 +
 +===== Exo 3 =====
 +Proposez un bloc PL/SQL qui utilise un curseur pour mettre à jour les états des exemplaires selon les règles suivantes :
 +  - Si le nombre d'​emprunts est inférieur à 5, l'​état est mis à neuf
 +  - Si le nombre d'​emprunts est inférieur à 10, l'​état est mis à bon
 +  - Si le nombre d'​emprunts est inférieur à 15, l'​état est mis à moyen
 +  - Sinon l'​état est mis à mauvais
 +
 +<code plsql>
 +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;
 +</​code>​
 +
 +===== 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''​.
 +<code plsql>
 +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;
 +/
 +</​code>​
 +
 +
 +====== Labo 3 ======
 +===== Exo 1 =====
 +Proposez un bloc PL/SQL qui prolonge de 90 jours les abonnements de tous les membres.
 +<code plsql>
 +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;
 +</​code>​
 +
 +===== 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.
 +<code plsql>
 +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;
 +</​code>​
 +
 +Solution alternative :
 +<code plsql>
 +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;
 +</​code>​
 +
 +
 +
 +===== 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.
 +<code plsql>
 +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;
 +</​code>​
 +
 +
 +===== 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.
 +<code plsql>
 +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;
 +</​code>​
 +
 +
 +====== Labo 4 ======
 +===== Ex 1 =====
 +
 +
 +<code plsql>
 +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;
 +</​code>​
 +
 +
 +
 +===== Ex 2 =====
 +
 +<code plsql>
 +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;
 +</​code>​
 +
 +===== Exemple d'​utilisation =====
 +Exemple d'​utilisation :
 +<code plsql>
 +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;
 +</​code>​
 +
 +====== Labo 5 ======
 +===== Exercice 1 =====
 +
 +
 +<code plsql>
 +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;
 +/
 +</​code>​
 +===== Exercice 2 =====
 +<code plsql>
 +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;
 +/
 +</​code>​
 +
 +
 +====== 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.
 +<code plsql>
 +CREATE OR REPLACE TRIGGER supp_exemplaires
 +BEFORE DELETE
 +ON BIB_DOCUMENT
 +FOR EACH ROW
 +
 +BEGIN
 +  DELETE FROM BIB_EXEMPLAIRE WHERE ISBN = :old.ISBN;
 +END;
 +</​code>​
 +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. ​
 +<code plsql>
 +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;
 +</​code>​
 +
 +===== Exercice 3 =====
 +Définissez un déclencheur qui interdit la modification du numéro de membre d’un emprunt.
 +<code plsql>
 +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;
 +</​code>​
 +
 +===== 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 ; 
 +
 +<code plsql>
 +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;
 +</​code>​
bases_de_donnees/oracle/pl_sql_labs.txt · Dernière modification: 2015/07/31 19:42 (modification externe)