BD et programmation TD n 41/3 Universit Lumire Lyon 2, Facult de Sciences conomiques et de Gestion Master dInformatique M2 spcialit IUP IDS Anne 2005-2006 Bases de donnes et programmation TD n 4 J . Darmont (http://eric.univ-lyon2.fr/~jdarmont/), 08/11/05 Rappel : Dbogage des procdures stockes Siuneprocdurestockeouunedfinitiondepaquetageoudecorpsdepaquetagenestpas correcte, Oracle indique uniquement quelle a t cre avec des erreurs de compilation . Pour visualiser ces erreurs, utiliser la commande suivante. SHOWERRORS Exercice 1 : Procdure stocke 1. crire un bloc PL/SQL anonyme permettant dafficher les noms des n premiers employs de la table EMP du TD n 2 (il est possible de recopier la table DARMONT.EMP si vous nen disposez plus). Le nombre n pourra tre stock dans une variable. Grer le cas o n est plus grand que le nombre de n-uplets de la table EMP. 2.Transformerleblocanonymeenprocdurestockenomme noms_emp ,lavariablen devenantunparamtredentre.TesterdepuislinvitedecommandeSQL*Plus(commandes EXECUTEnoms_emp( 3) , puis EXECUTEnoms_emp( 45)par exemple). 3.QuitterleclientSQL*Plus,relancerSQL*Plusetexcuter nouveaulaprocdure noms . Conclusion ? 4. crire un bloc PL/SQL anonyme incluant la dclaration et linitialisation de deux variables n1 et n2 et faisant appel la procdure noms_emp en lui passant successivement ces variables en paramtre. Exercice 2 : Paquetage On dsire mettre en place un paquetage logiciel permettant de grer la table PILOTE du TD n 1 (il est possible de recopier la table DARMONT.PILOTE si vous nen disposez plus). Lobjectif est de disposer de procdures permettant de : afficher le contenu de la table au format Numr o: Pr nomNOM ( Vi l l e) - Sal ai r e ; ajouter un pilote ; supprimer un pilote (connaissant son numro) ; modifier un pilote ; compter les pilotes. 1. Dfinir les spcifications dun paquetage nomm pilotes contenant : untypeenregistrementnomm PilNUplet contenantleschampssuivants :num,nom, pr enom, vi l , et sal . Utiliser exactement le types des champs de la table PILOTE ; un curseur nomm les_pilotes retournant un PilNUplet . BD et programmation TD n 42/3 2. Dfinir le corps du paquetage pilotes : dfinir compltement le curseur les_pilotes . Tester la cration du paquetage. 3. Ajouter aux spcifications et au corps du paquetage une procdure nomme afficher (pas de paramtre) permettant dafficher les pilotes lcran au format dsir. Utiliser dans le corps de cette procdure le curseur les_pilotes et une variable locale de type PilNUplet . Tester la procdure (EXECUTEpi l ot es. af f i cher ). 4.Ajouterauxspcificationsetaucorpsdupaquetageuneprocdurenomme ajouter permettant dajouter dans la table PILOTE un pilote dont les numro, nom, prnom, ville et salaire sont passs en paramtres. Tester la procdure. 5.Ajouterauxspcificationsetaucorpsdupaquetageuneprocdurenomme supprimer permettant de supprimer de la table PILOTE un pilote dont le numro est pass en paramtre. Tester la procdure. 6.Ajouterauxspcificationsetaucorpsdupaquetageuneprocdurenomme modifier permettantdemodifierdanslatablePILOTEunpilotedontlesnumro,nom,prnom,villeet salaire sont passs en paramtres. Tester la procdure. 7. Ajouter aux spcifications et au corps du paquetage une fonction nomme compter retournant un entier et permettant de compter le nombre de n-uplets de la table PILOTE. Tester lappel de la fonction laide de la commande EXECUTE de SQL*Plus, puis dans un bloc PL/SQL anonyme. Exercice 3 : Requte dynamique (altration de schma paramtre) 1. Dans un bloc PL/SQL anonyme, dfinir une variable chane de caractres nomme Source et lui affecter le nomdune table de votre compte. Dfinir une seconde variable chane de caractres nomme Destination et lui affecter une valeur quelconque (par exemple, COPIE). Dans le code du bloc PL/SQL, programmer la copie de la table Source dans la table Destination (cration de la table Destination avec tous les attributs et tous les n-uplets de Source ). Tester. 2. Transformer votre bloc PL/SQL anonyme en procdure stocke prenant en paramtres la table source et la table destination. Cela fonctionne-t-il ? 3. Ajouter la mention AUTHI DCURRENT_USER dans votre dfinition de procdure, aprs la dfinition desparamtres(parexemple,CREATEORREPLACEPROCEDUREcopi e( sour ceVARCHAR,dest i nat i onVARCHAR) AUTHI DCURRENT_USERI S). Tester. Quest-ce qui a chang ? Pourquoi cettemanipulationtait-ellesuperfluelorsquevousavezcrdesprocduresstockesdansles exercices prcdents ? Exercice 4 : Requte dynamique (cration de vue paramtre) 1. crire une procdure stocke prenant en paramtre le nomdune table et permettant de crer une vue contenant les noms de tous les attributs de cette table ainsi que leur type. Le nomde la vue devratredelaformeATT_nom_de_l a_t abl e.UtiliserlavuesystmeUSER_TAB_COLUMNS ( TABLE_NAME, COLUMN_NAME, DATA_TYPE)pour accder au nomet au type des attributs. Vrifier le rsultat. BD et programmation TD n 43/3 NB :Danslesvuessystmes,toutesleschanesdecaractres(commelesnomsdetablesou dattributs) sont stockes en majuscules. Exercice 5 : Curseur simple et requte dynamique (requte paramtre sur rsultat de requte) crire une procdure stocke permettant de compter le nombre de n-uplets dans toutes les tables de votre catalogue systme (vue systme TAB( TNAME, TABTYPE) ). Exclure les vues (type VIEW) de ce calcul. Afficher le rsultat tri par ordre alphabtique sous la forme NOM_TABLE: NB_NUPLETS n- upl et ( s) . Grer le pluriel du mot n-uplet , qui prend un s uniquement quand la taille de la table est strictement suprieure 1 n-uplet. Exercice 6 complmentaire crire une procdure stocke permettant de rechercher les tables contenant un attribut dont le nom contient une chane de caractres passe en paramtres, ainsi que le nom, le type de cet attribut et le nombre de valeurs distinctes de cet attribut dans la table. BD et programmation TD n 44/3 Correction -- Ex. 1 CREATEORREPLACEPROCEDUREnoms_emp( nI NTEGER) I S CURSORempl oyesI SSELECTenameFROM emp;eempl oyes%ROWTYPE; BEGI N OPENempl oyes;FETCHempl oyesI NTO e;WHI LEempl oyes%FOUNDANDempl oyes%ROWCOUNT1THEN f i n: = ' n- upl et s' ;ELSE f i n: = ' n- upl et ' ;ENDI F;DBMS_OUTPUT. PUT_LI NE( t . TNAME| | ' : ' | | c| | f i n) ;ENDLOOP;END;/ -- Ex. 6 CREATEORREPLACEPROCEDUREr echer che( at t r i but VARCHAR) I S CURSORl i st eI SSELECTTABLE_NAME, COLUMN_NAME, DATA_TYPEFROM USER_TAB_COLUMNS WHERECOLUMN_NAMELI KEUPPER( ' %' | | at t r i but | | ' %' )ORDERBYTABLE_NAME;t l i st e%ROWTYPE;cI NTEGER; BEGI N FORt I Nl i st eLOOP EXECUTEI MMEDI ATE' SELECTCOUNT( DI STI NCT' | | t . COLUMN_NAME| | ' )FROM ' | | t . TABLE_NAMEI NTO c;DBMS_OUTPUT. PUT_LI NE( t . TABLE_NAME| | ' : ' | | t . COLUMN_NAME| | ' ( ' | |t . DATA_TYPE| | ' ) - ' | | c| | ' val eur sdi st i nct es' ) ;ENDLOOP;END;/