Wiki SGY.io

Outils pour utilisateurs

Outils du site

Piste :
bases_de_donnees:oracle:pl_sql

Différences

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

Lien vers cette vue comparative

Prochaine révision		Révision précédente
bases_de_donnees:oracle:pl_sql [2015/08/01 01:42] – modification externe 127.0.0.1bases_de_donnees:oracle:pl_sql [2022/02/02 00:42] (Version actuelle) – modification externe 127.0.0.1
Ligne 685: Ligne 685:
 END; END;
 </code> </code>
 +
 +
 +====== 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.1438386138.txt.gz · Dernière modification : 2022/02/02 00:43 (modification externe)