bases_de_donnees:oracle:pl_sql
Différences
Ci-dessous, les différences entre deux révisions de la page.
Prochaine révision | Révision précédente | ||
bases_de_donnees:oracle:pl_sql [2015/08/01 01:42] – modification externe 127.0.0.1 | bases_de_donnees:oracle:pl_sql [2022/02/02 00:42] (Version actuelle) – modification externe 127.0.0.1 | ||
---|---|---|---|
Ligne 685: | Ligne 685: | ||
END; | END; | ||
</ | </ | ||
+ | |||
+ | |||
+ | ====== 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.txt · Dernière modification : 2022/02/02 00:42 de 127.0.0.1