· 6 years ago · Jun 07, 2019, 07:40 AM
1-- M2106
2-- Correction du TD1
3
4-- ex1
5create table etat
6(
7 code char(2 char) constraint pk_etat primary key,
8 nom varchar2(15 char) constraint nn_etat_nom not null,
9 capitale varchar2(15 char) constraint nn_etat_capitale not null,
10 nbHab number(8) constraint nn_etat_nbHab not null,
11 constraint un_etat_nom unique(nom),
12 constraint un_etat_capitale unique(capitale),
13 constraint ck_etat_nbHab check(nbHab >= 500000)
14) ;
15
16-- ex2
17create table prenom
18(
19 idP number constraint pk_prenom primary key,
20 libelle varchar2(15 char) constraint nn_prenom_libelle not null,
21 estCelebre char(1 char),
22 constraint un_prenom_libelle unique(libelle),
23 constraint ck_prenom_libelle check(initcap(libelle) = libelle),
24 constraint ck_prenom_estCelebre check(estCelebre in ('O', 'N'))
25) ;
26
27-- ex3
28create table naissance
29(
30 idP number,
31 sexe char(1 char),
32 code char(2 char),
33 annee number(4),
34 nb number default 5 constraint nn_naissance_nb not null,
35 constraint pk_naissance primary key(idP, sexe, code, annee),
36 constraint fk_naissance_idP foreign key(idP) references prenom,
37 constraint fk_naissance_code foreign key(code) references etat,
38 constraint ck_naissance_sexe check(sexe in ('M', 'F')),
39 constraint ck_naissance_annee check(annee between 1910 and 2012),
40 constraint ck_naissance_nb check(nb >= 5)
41) ;
42
43-- ex4
44desc etat
45desc prenom
46desc naissance
47
48-- ex5
49select table_name
50from user_tables ;
51
52-- ex6
53select table_name, constraint_name, constraint_type, status
54from user_constraints
55order by table_name, constraint_name ;
56
57-- ex7
58insert into etat values('CA', 'California', 'Sacramento', 38041430) ;
59insert into etat(code, nom, capitale, nbHab) values('TX', 'Texas', 'Austin', 26059203) ;
60insert into etat(capitale, nbHab, code, nom) values('Tallahasee', 19317568, 'FL', 'Florida') ;
61insert into etat values('MA', 'Massachussets', 'Boston', 6646144) ;
62insert into etat values('CO', 'Colorado', 'Denver', 5187582) ;
63insert into etat values('LA', 'Louisiana', 'Baton Rouge', 4601893) ;
64insert into etat values('UT', 'Utah', 'Salt Lake City', 2855287) ;
65commit ;
66
67-- ex8
68insert into prenom(idP, libelle) select idP, libelle from guillaume_cabanac.prenom ;
69
70-- ex9
71insert into naissance
72 select idP, sexe, code, annee, nb
73 from guillaume_cabanac.naissance
74 where code in ('CA', 'CO', 'FL', 'LA', 'MA', 'TX', 'UT') -- ou in (select code from etat)
75 and annee > 1979 ;
76
77commit ;
78
79-- ex10
80select t.*, bytes/1024/1024 MO_occupes, (max_bytes - bytes)/1024/1024 MO_dispos
81from user_ts_quotas t ;
82
83--ex12
84update PRENOM
85 set ESTCELEBRE = 'N'
86 where LIBELLE = 'Elyn'
87 or LIBELLE = 'Clorissa';
88--ex13
89update PRENOM
90 set ESTCELEBRE = 'O'
91 where LIBELLE in ('Barack' , 'Monica' , 'Marylin' , 'Mickeal' , 'Indiana' , 'Hussain');
92
93--ex14
94--afficher le méta-schéma
95select *
96from user_constraints
97where table_name='ETAT';
98
99
100insert into ETAT (CODE , CAPITALE , NBHAB)
101 values ('RX' , 'Rexor' , '5200000');
102
103insert into ETAT (CODE , NOM , NBHAB)
104 values ('RX' , 'Rexor' , '5200000');
105
106insert into ETAT (CODE , NOM , CAPITALE)
107 values ('RX' , 'Rexor' , 'Rexors');
108
109insert into ETAT (CODE , NOM , CAPITALE , NBHAB)
110 values ('RX' , 'Rexor' , 'Rexors' , '200000');
111
112insert into ETAT (CODE , NOM , CAPITALE , NBHAB)
113 values ('RX' , 'Florida' , 'Rexors' , '5000000');
114
115insert into ETAT (CODE , NOM , CAPITALE , NBHAB)
116 values ('RX' , 'Rexor' , 'Austin' , '5000000');
117
118insert into ETAT
119 values ('RX' , '520000' , 'Rexors' , 'Rexor');
120
121--ex15
122create table naissanceVip as
123select *
124from naissance
125where idP in (select idP
126 from prenom
127 where libelle = ('Elyn')
128 or libelle = ('Clorissa') ) ;
129
130select *
131from user_constraints ;
132
133delete naissanceVip
134 where annee < 2010;
135
136drop table naissanceVip;
137
138--ex16
139
140alter table etat
141 add prixPort number(4,2) constraint ck_etat_prixPort check (prixPort <= 8.50);
142
143alter table Etat modify prixPort default 3.14;
144
145
146--ex17
147
148update etat
149 set prixPort = (6.90)
150 where nom = ('Utah')
151 or NBHAB >= (10000000);
152
153--ex18
154
155insert into prenom (libelle,idp,estcelebre)
156 values('Captain Fantastic Faster Than Superman Spiderman Batman Wolverine Hulk And The Flash Combined', 666666, 'O');
157
158alter table prenom modify
159 (libelle varchar2(100 char));
160
161--ex19
162
163alter table etat drop constraint ck_etat_prixPort;
164
165
166alter table etat modify
167 prixPort constraint ck_etat_prixPort check (prixPort <= 15.0);
168
169
170update etat
171 set prixPort = (12.42)
172 where nom = ('Texas');
173
174--ex 20
175create table echangeTmp as
176 select *
177 from naissance
178 where idP = 666
179 or idP = (select idP from prenom where libelle = 'Lilith' );
180
181delete naissance
182where idP in (select idP from echangeTmp);
183
184insert into naissance
185select (select distinct idP
186 from echangeTmp
187 where idP <> e.idP), sexe, code, annee, nb
188from echangeTmp e ;
189
190update prenom p
191set libelle = (select libelle
192 from prenom
193 where idP in (select idP
194 from echangeTmp)
195 and idP <> p.idP)
196where idP in (select idP from echangeTmp);
197
198drop table echangeTmp;
199
200
201--Feuille 2/5
202-- M2106
203-- Correction du TD2
204
205-- ex1
206select *
207from prenom
208where libelle = initcap('&prenom') ;
209
210-- ex2
211select count(*) nbTotal, count(estCelebre) nbCelebres, count(*) - count(estCelebre) nbInconnus
212from prenom ;
213
214-- ex3
215select count(*) nb
216from prenom
217where estCelebre is null ;
218
219-- ex4a
220select nvl(sum(nb), 0) nbEnfants
221from naissance
222where idP = (select idP from prenom where libelle = initcap('&prenom')) ;
223
224-- ex4b
225select nvl(sum(nb), 0) nbEnfants
226from naissance n, prenom p
227where p.idP = n.idP
228 and libelle = initcap('&prenom') ;
229
230-- ex5
231select libelle
232from prenom
233where libelle like 'Z%rr%'
234order by libelle ;
235
236-- ex6
237select libelle
238from prenom
239where length(replace(lower(libelle), 'e', '')) = length(libelle) - 4
240order by libelle ;
241
242-- ex7a
243create view joursVecus as
244 select sysdate - to_date('08/03/1982 14:30', 'DD/MM/YYYY HH24:MI') jours
245 from dual ;
246
247-- ex7b
248select 24 * jours heuresVecues
249from joursVecus ;
250
251-- ex7c
252select joursDecimal, jours, heures, mod((joursDecimal - jours) * 24 * 60, 60) minutes
253from
254(
255 select joursDecimal, jours, trunc((joursDecimal - jours) * 24) heures,
256 trunc(mod((joursDecimal - jours) * 24, 24)) heuresBis
257 from
258 (
259 select joursDecimal, trunc(joursDecimal) jours
260 from
261 (
262 select sysdate - to_date('08/03/1982 14:30', 'DD/MM/YYYY HH24:MI') joursDecimal
263 from dual
264 )
265 )
266) ;
267
268-- ex8a
269select libelle
270from prenom
271where idP not in (select idP from naissance)
272order by 1 ;
273
274-- ex8b
275select libelle
276from prenom p
277where not exists (select 1 from naissance where idP = p.idP)
278order by 1 ;
279
280-- ex8c
281select libelle
282from prenom
283minus
284select libelle
285from prenom p, naissance n
286where p.idP = n.idP
287order by 1 ;
288
289-- ex8d
290select libelle
291from prenom p
292where 0 = (select count(*) from naissance where idP = p.idP)
293order by 1 ;
294
295-- ex8e
296select distinct libelle
297from prenom p, naissance n
298where p.idP = n.idP (+)
299 and n.idP is null
300order by 1 ;
301
302-- ex9a
303select sum(nb) "Nb naissances"
304from naissance ;
305
306-- ex9b
307select code "Etat", sum(nb) "Nb naissances"
308from naissance
309group by code
310order by 2 desc ;
311
312-- ex9c
313select annee, sum(nb)
314from prenom p, naissance n
315where p.idP = n.idP
316 and libelle = 'Barack'
317group by annee
318order by annee ;
319
320-- ex10
321-- v1 : déclenchement de la sous-requête pour chaque ligne (coût = 1084, tps = 5.9 s)
322select libelle||' est le prénom le plus populaire' resultat
323from naissance n, prenom p
324where n.idP = p.idP
325 and sexe = 'F'
326 and code = 'CA'
327 and annee = 2012
328 and nb = (select max(nb)
329 from naissance
330 where sexe = n.sexe
331 and code = n.code
332 and annee = n.annee) ;
333
334-- v2 : beaucoup plus efficace (coût = 69, tps = 0.2 s)
335with naissanceCal2012 as
336(
337 select idP, nb
338 from naissance
339 where sexe = 'F'
340 and code = 'CA'
341 and annee = 2012
342)
343select libelle||' est le prénom le plus populaire' resultat
344from naissanceCal2012 n, prenom p
345where n.idP = p.idP
346 and nb = (select max(nb) from naissanceCal2012) ;
347
348
349-- ex11
350-- méthode "bourrin" mais qui marche...
351select libelle
352from prenom
353where idP in
354(
355 select idP from naissance where code = 'CA'
356 intersect
357 select idP from naissance where code = 'CO'
358 intersect
359 select idP from naissance where code = 'FL'
360 intersect
361 select idP from naissance where code = 'LA'
362 intersect
363 select idP from naissance where code = 'MA'
364 intersect
365 select idP from naissance where code = 'TX'
366 intersect
367 select idP from naissance where code = 'UT'
368)
369order by 1 ;
370
371-- oui, mais si on avait beaucoup plus que 7 états ? (la bonne méthode)
372select libelle
373from prenom p, naissance n
374where p.idP = n.idP
375group by p.idP, libelle
376having count(distinct code) = (select count(*) from etat)
377order by 1 ;
378
379
380-- ex12a
381select avg(length(libelle)) lgMoyenneUniforme
382from prenom ;
383
384-- ex12b
385select sum(length(libelle) * sum(nb)) / sum(sum(nb)) lgMoyNonUniforme
386from prenom p, naissance n
387where p.idP = n.idP
388group by p.idP, libelle ;
389
390-- Solution alternative avec décomposition, en se basant que le fait que l'algèbre est fermée
391select sum(nbPondere)/sum(nb) lgMoyNonUniforme
392from
393(
394 select libelle, length(libelle) * sum(nb) nbPondere, sum(nb) nb
395 from prenom p, naissance n
396 where p.idP = n.idP
397 group by p.idP, libelle
398) ;
399
400-- ex14
401select count(*) nb
402from prenom
403where estcelebre is not null;
404
405--ex15
406select idp, libelle, estcelebre
407from prenom
408where LOWER(libelle) like '%jesus%';
409
410
411--ex16
412select libelle
413from prenom
414where libelle like '__z%';
415
416select libelle
417from prenom
418where INSTR(LIBELLE, 'z', 3) = 3;
419
420--ex17
421select prenom.libelle, prenom.ESTCELEBRE, naissance.ANNEE
422from prenom,naissance
423where naissance.idp = prenom.idp
424and libelle like 'Messiah';
425
426--ex18
427select naissance.ANNEE,NB as NB_Elvis
428from naissance,prenom,etat
429where naissance.idp = prenom.idp
430and etat.code = naissance.code
431and prenom.LIBELLE like 'Elvis'
432and etat.NOM like 'Texas';
433
434select distinct annee
435from NAISSANCE
436order by annee desc;
437
438
439--ex19
440select prenom.LIBELLE, sum (naissance.NB) as TOTNB
441from naissance,prenom,etat
442where naissance.idp = prenom.idp
443and etat.code = naissance.code
444and LENGTH (prenom.LIBELLE) > 3
445and etat.nom IN ('Texas','Colorado')
446and naissance.SEXE = 'F'
447and naissance.ANNEE >= 1992
448group by prenom.LIBELLE
449order by TOTNB DESC;
450
451--ex20
452
453select naissance.ANNEE,sum (naissance.NB)
454from naissance,prenom,etat
455where naissance.idp = prenom.idp
456and etat.code = naissance.code
457and naissance.sexe = 'F'
458and prenom.LIBELLE like 'Richard'
459group by (naissance.ANNEE)
460order by naissance.ANNEE;
461
462--ex21
463select * from etat where 42 = 42;
464select * from etat where 42 <> 42;
465
466select * from etat where null = null;
467select * from etat where null is null;
468select * from prenom where estCelebre = null;
469select * from prenom where estCelebre is null;
470
471select * from etat where null <> null;
472select * from etat where null is not null;
473select * from prenom where estCelebre <> null;
474select * from prenom where estCelebre is not null;
475
476--ex22
477select libelle
478from prenom
479where SOUNDEX(libelle) = SOUNDEX('Lilith')
480and libelle <> ('Lilith')
481order by 1;
482
483with toto as
484(select p1.libelle, p2.libelle
485from prenom p1, prenom p2
486where SOUNDEX(p2.libelle) = SOUNDEX(p1.libelle)
487and p2.idP < p1.idP)
488select count(*)
489from toto;
490
491--ex23
492--a)
493select libelle
494from prenom p
495where (select count(distinct sexe)
496 from naissance
497 where idp=p.idp)=2
498order by 1;
499
500
501
502--b
503select p.libelle
504from prenom p, naissance n
505where p.idP = n.idP
506group by p.idp , libelle
507having count (distinct sexe) = 2
508order by 1;
509
510--c1
511select libelle
512from prenom p, naissance n
513where p.idP = n.idP
514and n.sexe = 'F'
515intersect
516select libelle
517from prenom p, naissance n
518where p.idP = n.idP
519and n.sexe = 'M'
520order by 1;
521
522--c2
523select libelle
524from prenom
525where idp in
526(
527 select idp from naissance where sexe='M'
528 intersect
529 select idp from naissance where sexe='F'
530)
531order by 1;
532
533
534--d
535select distinct p.libelle
536from prenom p , naissance n1, naissance n2
537where p.idp=n1.idp
538and n1.idp=n2.idp
539and n1.sexe='M'
540and n2.sexe='F';
541
542
543--ex24
544select idp
545from
546(
547 select distinct idP from naissance where sexe = 'M'
548 union all
549 select distinct idP from naissance where sexe = 'F'
550)
551group by idp
552having count(*) = 2
553order by 1;
554
555
556select idP
557from naissance
558group by idP
559having count (distinct sexe) = 2
560order by 1;
561
562
563
564
565--ex 25
566
567select libelle , max(sexe) , sum(nb)
568from prenom p , naissance n
569where n.idp = p.idp
570group by libelle
571having count(distinct sexe) = 1
572order by 3 DESC;
573
574
575--ex26
576create or replace view repartitionParGenre as
577select LIBELLE , sum(case when SEXE= 'M' then NB else 0 end ) NBM , sum(case when SEXE = 'F' then NB else 0 end ) NBF , sum (NB) as NBTOT
578from PRENOM, NAISSANCE
579where PRENOM.IDP = NAISSANCE.IDP
580group by LIBELLE
581having count(distinct SEXE) = 2
582order by 1;
583
584--ex27
585create or replace view repartitionParGenrePct as
586select LIBELLE , NBTOT , NBM , round ((NBM / NBTOT), 4) as PCTM , NBF , round((NBF / NBTOT),4) as PCTF
587from repartitionParGenre;
588
589--ex28
590select libelle , nbTot , nbM , nbf , pctM , pctF
591from repartitionParGenrePct
592order by abs (pctF - pctM) desc;
593--Permet de faire la valeur absolue du % de fille - % de garçon pour voir oùù il y a le plus de différence.
594
595
596--ex29
597select p.libelle , min(n.annee) as DEBUT , max(n.annee) as FIN, count(distinct n.annee) as NBANNEE ,sum(N.NB) as NBTOT
598from PRENOM p , NAISSANCE n
599where p.idp = n.idp
600and n.SEXE = 'F'
601group by p.libelle
602having max(n.annee)- min(n.annee) >= 2
603and min(ANNEE) > 1980
604and max(ANNEE) < 2012
605order by NBTOT desc;
606
607-- M2106
608-- Correction du TD3
609
610-- ex1a
611create or replace procedure majPrixPort(pCode in etat.code%type, pPrixPort in etat.prixPort%type) as
612begin
613 update etat
614 set prixPort = pPrixPort
615 where code = pCode ;
616end majPrixPort ;
617
618-- ex1b
619call majPrixPort('LA', 42.69) ;
620
621call majPrixPort('LA', 4.26) ;
622
623
624select prixPort from etat where code = 'LA' ;
625
626-- ex1c
627rollback ;
628
629-- ex2a
630select replace(translate('Thomas', 'aeiouy', 'aaaaaa'), 'a') from dual ;
631
632-- ex2b
633create or replace function eliminerVoyelles(chaine in varchar2) return varchar2 as
634begin
635 return replace(translate(chaine, 'aeiouyAEIOUY', 'a'), 'a') ;
636end eliminerVoyelles ;
637
638-- ex3
639create or replace function desaccentuer(chaine in varchar2) return varchar2 as
640begin
641 return translate(chaine, 'éêëàïç', 'eeeaic') ;
642end desaccentuer ;
643
644select desaccentuer('François') from dual ;
645
646-- ex4a
647create or replace function nbVoyelles(chaine in varchar2) return integer as
648begin
649 return length(chaine) - length(eliminerVoyelles(chaine));
650end nbVoyelles;
651
652select nbVoyelles('Emmanuelle') from dual;
653
654-- ex4b
655create function nbConsonnes(chaine in varchar2) return integer as
656begin
657 return length(chaine) - nbVoyelles(chaine) ;
658end nbConsonnes ;
659
660select nbVoyelles('Emmanuelle'), nbConsonnes('Emmanuelle') from dual ;
661
662-- ex5a
663create function estAlphabetiqueNumber(ch in varchar2) return number is
664 lettre char(1 char) ;
665 nouvelleLettre char(1 char) ;
666begin
667 if ch is null then
668 return 0 ;
669 else
670 lettre := lower(substr(ch, 1, 1)) ;
671 for i in 2..length(ch) loop
672 nouvelleLettre := lower(substr(ch, i, 1)) ;
673 if nouvelleLettre >= lettre then
674 lettre := nouvelleLettre ;
675 else
676 return 0 ;
677 end if ;
678 end loop ;
679 return 1 ;
680 end if ;
681end estAlphabetiqueNumber ;
682
683-- ex5b
684select libelle
685from prenom
686where estAlphabetiqueNumber(libelle) = 1
687order by length(libelle) desc, libelle ;
688
689-- ex7
690
691execute supprimerPrenom('Richard');
692
693rollback;
694
695--ex8
696
697select libelle
698from prenom
699where estPalindromeNumber(lower(libelle)) = 1
700order by length(libelle) desc, libelle ;
701
702--ex9
703
704select libelle
705from prenom
706where estEquilibreeNumber(libelle) = 1
707order by length(libelle) desc, libelle;
708
709
710
711--ex10
712
713
714select p1.libelle, p2.libelle
715from prenom p1, prenom p2
716where p1.idp < p2.idp
717and p2.idp < 1000
718and ontMemeLettre(lower(p1.libelle), lower(p2.libelle)) = 1
719order by 1;
720
721
722--ex11
723create or replace package chaine as
724function ontMemeLettre(ch1 in varchar2 , ch2 in varchar2) return number;
725function estEquilibreeNumber ( ch in varchar2 ) return number;
726function estPalindromeNumber(pPrenom in varchar2) return number;
727procedure supprimerPrenom(pPrenom in varchar2);
728end chaine;
729
730create or replace package body chaine as
731
732--ex10
733
734function ontMemeLettre(ch1 in varchar2 , ch2 in varchar2) return number as
735chC varchar2(4000):= ch1;
736chB varchar2(4000):= ch2;
737begin
738for j in 1..2 loop
739 for i in 1..length(chC) loop
740 if instr(chB,substr(chC , i, 1)) = 0 then
741 return 0;
742 end if;
743 end loop;
744 chC := ch2;
745 chB := ch1;
746end loop;
747return 1;
748end ontMemeLettre;
749
750--ex9
751
752function estEquilibreeNumber ( ch in varchar2 ) return number as
753begin
754 if ch is null then
755 return 0 ;
756 else
757 if nbvoyelles(ch) = nbconsonnes(ch) then
758 return 1 ;
759 else
760 return 0 ;
761 end if ;
762 end if ;
763end estEquilibreeNumber ;
764
765
766--ex8
767function estPalindromeNumber(pPrenom in varchar2) return number as
768begin
769 for i in 1..length(pPrenom)/2 loop
770 if substr(pPrenom,i,1) <> substr(pPrenom,length(pPrenom)-i+1,1) then
771 return 0;
772 end if;
773 end loop;
774 return 1;
775end estPalindromeNumber;
776
777
778--ex7
779procedure supprimerPrenom(pPrenom in varchar2) as
780 vIdp number;
781begin
782 select prenom.idp into vIdp from prenom p where p.libelle = pPrenom;
783 delete from naissance
784 where naissance.idp = vIdp;
785 delete from prenom
786 where prenom.idp = vIdp;
787end supprimerPrenom;
788
789end chaine;
790
791
792
793drop package chaine;
794
795-- M2106
796-- Correction du TD4
797
798-- ex1
799create or replace function getIdP(pLibellePrenom in prenom.libelle%type) return prenom.idP%type as
800 vIdP prenom.idP%type ;
801begin
802 select idP into vIdP
803 from prenom
804 where libelle = pLibellePrenom ;
805 return vIdP ;
806end getIdP ;
807
808-- ex2
809create or replace procedure afficherTop5 as
810 vI number ;
811 cursor vCurPrenoms(vSexe in naissance.sexe%type) is
812 select libelle, sum(nb) nbTot
813 from naissance n, prenom p
814 where n.idP = p.idP
815 and sexe = vSexe
816 group by n.idP, libelle
817 order by 2 desc ;
818begin
819 for genre in (select distinct sexe from naissance order by sexe) loop
820 vI := 0 ;
821 dbms_output.put_line('Top 5 prénoms de sexe '||genre.sexe) ;
822 for tuple in vCurPrenoms(genre.sexe) loop
823 dbms_output.put_line(' '||tuple.libelle||' est porté par '||tuple.nbTot||' personnes.') ;
824 vI := vI + 1 ;
825 exit when vI = 5 ; -- quitte la boucle (équivalent au break en C)
826 end loop ;
827 dbms_output.put_line('----------------') ;
828 end loop ;
829end afficherTop5 ;
830
831set serveroutput on --REMETTRE DANS TOUTES LES SESSIONS ! ! ! ! ! ! ! ! ! ! ! ! ! ! ! ! ! ! ! ! ! ! ! ! ! ! ! ! ! !
832exec afficherTop5 ;
833
834
835-- ex3
836declare
837 vNbPopulation number ; -- nombre de personnes qui ont reçu un prénom
838 vNbPrenoms number ; -- nombre de prénoms connus
839 vNbPersonnes number := 0 ; -- nombre de personnes traitées (accumulateur)
840 vI number := 0 ; -- indice du prénom traité (compteur)
841begin
842 select sum(nb) into vNbPopulation from naissance ;
843 dbms_output.put_line('Taille de la population : '||vNbPopulation||' personnes.') ;
844
845 select count(distinct p.idP) into vNbPrenoms
846 from prenom p, naissance n
847 where p.idP = n.idP ;
848 dbms_output.put_line('Nombre de prénoms distincts : '||vNbPrenoms||'.') ;
849
850 -- parcours des prénoms triés par fréquence décroissante
851 for tuple in (select sum(nb) nbTot
852 from naissance
853 group by idP
854 order by 1 desc)
855 loop
856 vNbPersonnes := vNbPersonnes + tuple.nbTot ;
857 vI := vI + 1 ;
858 -- Quitte lorsqu'on a additionné les 20 % des prénoms
859 exit when vI > vNbPrenoms * 0.2 ;
860 end loop ;
861 dbms_output.put_line(vNbPersonnes||' personnes portent un prénom courant (top 20 %)') ;
862 dbms_output.put_line('Ils représentent '||round(100*vNbPersonnes/vNbPopulation)||' % de la population.') ;
863end ;
864/
865
866--ex5
867
868create or replace procedure afficherNatalite(plibelle in p.prenom%type, petat in etat.code%type) as
869 vNb number;
870 vAnMin number;
871 vAnMax number;
872 begin
873 Select sum(n.nb) , min(n.annee) , max(n.annee)
874 into vNb, vAnMin , vAnMax
875 from naissance n , prenom p
876 where p.Idp = n.Idp
877 and libelle = plibelle
878 and code = petat;
879 dbms_output.put_line(vNb||''|| plibelle ||'sont né(e)s dans l''état'||petat||'.') ;
880 dbms_output.put_line('Première apparition :'|| vAnMin) ;
881 dbms_output.put_line('Dernière apparition :'|| vAnMax) ;
882
883end afficherNatalite;
884
885execute afficherNatalite('Dick','CA');
886
887--ex6
888create or replace procedure afficherTop5(pSexe in naissance.sexe%type) as
889 v1 number := 0;
890 begin
891 dbms_output.put_line("Top 5 des prenoms de sexe"||pSexe);
892 for tuple in (p.libelle, sum(n.nb) as nb
893 from naissane n, prenom n
894 where n.idP = p.idP
895 and n.sexe = pSexe
896 group by p.libelle
897 order by 2 desc)
898 loop
899 dbms_output.put_line(vPrenom.libelle|| "est porté par"||vPrenom.nb|| "Personne");
900 v1 := v1 + 1;
901 exit when v1 = 5;
902 end loop;
903end afficherTop5;