bases_de_donnees:oracle:pl_sql_labs
Différences
Ci-dessous, les différences entre deux révisions de la page.
bases_de_donnees:oracle:pl_sql_labs [2015/08/01 01:42] – modification externe 127.0.0.1 | bases_de_donnees:oracle:pl_sql_labs [2020/02/13 00:49] (Version actuelle) – supprimée sgariepy | ||
---|---|---|---|
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(' | ||
- | END LOOP; | ||
- | dbms_output.put_line(' | ||
- | END; | ||
- | </ | ||
- | |||
- | |||
- | ===== 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, | ||
- | i number(10); | ||
- | n number; | ||
- | BEGIN | ||
- | FOR e IN emps LOOP | ||
- | i := e.isbn; | ||
- | n := e.exemplaire; | ||
- | dbms_output.put_line(' | ||
- | END LOOP; | ||
- | dbms_output.put_line(' | ||
- | END; | ||
- | / | ||
- | </ | ||
- | <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(' | ||
- | IF (n>10) THEN | ||
- | dbms_output.put_line(' | ||
- | END IF; | ||
- | END LOOP; | ||
- | dbms_output.put_line(' | ||
- | END; | ||
- | / | ||
- | </ | ||
- | |||
- | ===== 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' | ||
- | - Si le nombre d' | ||
- | - Si le nombre d' | ||
- | - Sinon l' | ||
- | |||
- | <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:= ' | ||
- | WHEN n<2 THEN s:= ' | ||
- | WHEN n<3 THEN s:= ' | ||
- | ELSE s: | ||
- | END CASE; | ||
- | | ||
- | UPDATE exemplaire | ||
- | SET etat = s | ||
- | WHERE CURRENT OF exemps; | ||
- | END LOOP; | ||
- | dbms_output.put_line(' | ||
- | END; | ||
- | </ | ||
- | |||
- | ===== Exo 4 ===== | ||
- | Utilise un curseur pour supprimer tous les membres dont l' | ||
- | <code plsql> | ||
- | DECLARE | ||
- | CURSOR membres IS select * from membre WHERE FINABONNEMENT< | ||
- | 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> | ||
- | dbms_output.put_line(' | ||
- | 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(' | ||
- | END IF; | ||
- | END LOOP; | ||
- | dbms_output.put_line(' | ||
- | END; | ||
- | / | ||
- | </ | ||
- | |||
- | |||
- | ====== 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, | ||
- | |||
- | 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. | ||
- | <code plsql> | ||
- | DECLARE | ||
- | CURSOR smem IS | ||
- | SELECT nom, prenom, membre.numero, | ||
- | FROM membre, emprunt | ||
- | WHERE MEMBRE.NUMERO> | ||
- | 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(' | ||
- | END LOOP; | ||
- | CLOSE smem; | ||
- | END; | ||
- | </ | ||
- | |||
- | 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(' | ||
- | 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. | ||
- | <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(' | ||
- | 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é. | ||
- | <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, | ||
- | 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(' | ||
- | end if; | ||
- | select titre into tre from document where isbn = i.isbn; | ||
- | dbms_output.put_line(' | ||
- | end loop; | ||
- | END; | ||
- | </ | ||
- | |||
- | |||
- | ====== 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(' | ||
- | END LOOP; | ||
- | END; | ||
- | </ | ||
- | |||
- | |||
- | |||
- | ===== 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; | ||
- | </ | ||
- | |||
- | ===== Exemple d' | ||
- | Exemple d' | ||
- | <code plsql> | ||
- | DECLARE | ||
- | t DOCUMENT.TYPE%TYPE | ||
- | n NUMBER | ||
- | BEGIN | ||
- | t := ' | ||
- | AfficherDocParType(t); | ||
- | n := getNbDocParType(t); | ||
- | DBMS_OUTPUT.putline(' | ||
- | END; | ||
- | </ | ||
- | |||
- | ====== 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(: | ||
- | EXCEPTION | ||
- | WHEN DUP_VAL_ON_INDEX THEN | ||
- | UPDATE stat_emprunt | ||
- | SET NBEMPRUNT = NBEMPRUNT+1 | ||
- | WHERE isbn = :new.isbn; | ||
- | END; | ||
- | / | ||
- | </ | ||
- | ===== Exercice 2 ===== | ||
- | <code plsql> | ||
- | set serveroutput on; | ||
- | |||
- | CREATE OR REPLACE TRIGGER supp_emprunt | ||
- | BEFORE DELETE | ||
- | ON EMPRUNT | ||
- | FOR EACH ROW | ||
- | |||
- | BEGIN | ||
- | IF (: | ||
- | RAISE_APPLICATION_ERROR(-20100,' | ||
- | END IF; | ||
- | | ||
- | INSERT INTO archiv_emprunt VALUES (: | ||
- | 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. | ||
- | <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; | ||
- | </ | ||
- | Ce trigger fonctionne, mais comme Oracle permet d' | ||
- | |||
- | ===== 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; | ||
- | |||
- | BEGIN | ||
- | SELECT finabonnement INTO fa FROM BIB_MEMBRE WHERE NUMERO = : | ||
- | IF (fa <= SYSDATE) THEN | ||
- | RAISE_APPLICATION_ERROR(-20100,' | ||
- | END IF; | ||
- | |||
- | END; | ||
- | </ | ||
- | |||
- | ===== 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 (: | ||
- | RAISE_APPLICATION_ERROR(-20100,' | ||
- | ELSE | ||
- | UPDATE bib_emprunt | ||
- | SET | ||
- | isbn = :new.isbn, | ||
- | exemplaire = : | ||
- | rendule = : | ||
- | rendu = :new.rendu | ||
- | WHERE 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 ; | ||
- | |||
- | <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 = : | ||
- | IF n<2 THEN | ||
- | UPDATE BIB_EXEMPLAIRE SET etat = ' | ||
- | ELSE IF n<4 THEN | ||
- | UPDATE BIB_EXEMPLAIRE SET etat = ' | ||
- | ELSE IF n<6 THEN | ||
- | UPDATE BIB_EXEMPLAIRE SET etat = ' | ||
- | ELSE | ||
- | UPDATE BIB_EXEMPLAIRE SET etat = ' | ||
- | END IF; | ||
- | END; | ||
- | </ |
bases_de_donnees/oracle/pl_sql_labs.1438386138.txt.gz · Dernière modification : 2022/02/02 00:43 (modification externe)