B AS ES DE DONNÉES

AVANCÉES
LGI3
2021-2022

Pr Cheikhou THIAM
cthiam@[Link]

Bases de données avancées

1
 INTRODUCTION

B as es de données avancées 2

2
TION
B ase de données (BD) : Collection de
données
cohérentes et
structurées

≠
Base de
données Fichier

3
Bases de données avancées s 3
ORGANISATION EN FICHIERS

Saisi Traitement Fichie

e r

Fichie
r

État
Saisi Traitement de
e sortie

4
4
Bases de données avancées
N EN B D

Saisie

+ B as Traitements
e
de
Contrôles
donn
ées

États
de
sortie

5
5
Bases de données avancées
L’ORGANISATION EN B D
Uniformisation de la saisie

Standardisation des

traitements Contrôle de la

validité des données

Partage de données entre

6
plusieurs
Bases traitements
de données avancées 6
QU’UN SGB D ?

Système de Gestion de B a se s de
Données : Logiciel(s) assurant
structuration, stockage, maintenance,
mise à jour et consultation des données
d’une B D

Exemples
– S G B D « bureautiques » : Access, Base, Filemaker,
– Paradox S G B D serveurs : Oracle, DB2, SQ L Server,
PostgreSQL, MySQL, M a r i a D B …

7
7
Bases de données avancées
CONCEPTION D’UNE B D
Problém a Indépendant d’un
- tique
système de gestion de
Cahier des
B D (SGBD)
charges
S pécifica
- tions
Rédactio
n
Modèle Spécifi qu
conceptu
el e
Analys
e Famille de
M odèl S G B D
e
Traductio log iqu SGBD
n e particulier
Modèle

Traductio phys iqu

n e

8
8
Bases de données avancées
COURS

Partie 1 : Modélisation
conceptuelle

Partie 2 : Modélisation Modèle

relationn
logique el

Partie 3 : Interrogation et manipulation de bases de

données

Partie 4 : Programmation de bases de données

9
9
Bases de données avancées
 PARTIE 1
MODÉLISATION
CONCEPTUELLE

10

10
B as es de données avancées
MODÈLE CONCEPTUEL UML

Standard de l’Object Management

Group

Ensemble de formalismes

graphiques Diagramme de

classes

11
[Link] 11
Bases de données avancées /
CLASSES ET ATTRIBUTS
Classe : Groupe d’entités du monde réel ayant les
m ê m e s caractéristiques et le mê me comportement
ex. ETUDIANT

Attribut : Propriété de la classe

ex. N o m et Prénom de
l’étudiant·e

Représentation graphique :

12
12
Bases de données avancées
ATTRIBUTS
Type
d’attribut
– Nombre :entier
– (Entier) Nombre
– réel (Réel)
– Chaîne de caractères
(Chaîne) Date (Date)

13
13
Bases de données avancées
INSTANCES
Objets (individus) de la classe ETUDIANT = les
étudiant·es
Nom Prénom DateNaiss Etc.
Dupont Albertine 01/06/1993 ...
West James 03/09/1994 ...
Martin Marie 05/06/1995 ...
Abidi Rachid 15/11/1995 ...
Titgoutte Justine 28/02/1996 ...
Dupont Noémie 18/09/1995 ...
Dupont Albert 23/05/1990 ...

Problème : Comment distinguer les

14
Dupont ? 14
Bases de données avancées
IDENTIFIANT (1/2)

Solution : Ajouter un attribut numéro

d’étudiant !
NumEtu Nom Prénom DateNaiss
1110 Dupont Albertine 01/06/199
3
2002 West James 03/09/199
4
3333 Martin Marie 05/06/199
5
4042 Durand Rachid 05/11/199
5

15
5552 Titgoutte Justine 28/02/199 15
Bases de données avancées
6
IDENTIFIANT (2/2)
Le numéro d’étudiant est un attribut
identifiant.

Un identifiant caractérise de façon

unique les instances d’une classe.

Convention graphique :
N B : Ne pas confondre
avec les attributs de
classe UML dont c’est la
notation usuelle

16
16
Bases de données avancées
ASSOCIATIONS

Association : liaison perçue entre des

classes ex. Les étudiant·es passent des
épreuves.

Les classes ETUDIANT et EPREUVE peuvent être

qualifiées de participantes à l’association PASSER.

Degré ou arité d’une association : nombre de

classes participantes.
En général : associations binaires (de degré 2).

17
17
Bases de données avancées
RÉCURSIVES

Une classe peut être associée à elle-même,

chaque instance pouvant jouer plusieurs
rôles dans l’association.
ex. Employés et supérieurs hiérarchiques

Rôle : fonction de
chaque classe
participante (+).

18
18
Bases de données avancées
MULTIPLICITÉ (OU CARDINALITÉ)

Définition : Indicateur qui montre combien

d’instances de la classe considérée peuvent être
liées à une instance de l’autre classe participant
à l’association
Un et un
– 1
seul Zéro
– 0..1 ou un Zéro
– 0..* ou ou plus Un
* ou plus
D e M à N (M, N
– 1..* entiers) ex. 4..10 (de

19
Bases de données avancées 4[Link]
à 10) 19
– M..N
ASSOCIATIONS 1-1
ex. Un·e étudiant·e possède une et une seule
carte Izly. Cette dernière n’est possédée que
par un·e seul·e étudiant·e.

Lire « Un·e étudiant.e possède multiplicité (1) carte

20
Izly ».
Bases de données avancées 20
ASSOCIATIONS 1-N
ex. Une épreuve relève d’une et une seule
matière. Une matière peut donner lieu à
plusieurs épreuves.

N B : La multiplicité un à plusieurs (1..*) peut

aussi être zéro à plusieurs (0..* ou *).

21
21
Bases de données avancées
ASSOCIATIONS 0 OU 1-N
ex. Un·e étudiant·e peut appartenir ou non
à un groupe de TD. Un groupe de TD
réunit plusieurs étudiant·es.

N B : La multiplicité un à plusieurs (1..*) peut

aussi être zéro à plusieurs (0..* ou *).

22
22
Bases de données avancées
ASSOCIATIONS M-N
ex. Un·e étudiant·e peut passer plusieurs
épreuves. Une épreuve peut être passée par
plusieurs étudiant·es.

N B : Les multiplicités un à plusieurs (1..*)

peuvent aussi être zéro à plusieurs (0..*
ou *).

23
23
Bases de données avancées
CLASSES-ASSOCIATIONS
Il est possible de caractériser une association
par des attributs.
ex. Un · e étudiant · e qui passe une épreuve obtient
une note.

N B : Une classe-association est une association,

24
Bases
B
pdeala
a ses cde
sd onune
snsavancées
données ée
25
EXEMPLE : SPÉCIFICATIONS (1/2)

Le étudiant·e sont caractérisé·es par n u m ér uniqu ,

s s, préno , date
no un de , o
code e v ill .leur
et
m m naissance rue, postal e
Les po ss ède une carte Izly caractérisée par
n
étudiant·es nt
u m ér uniqu et s old un
d’argent utilisable au
o e un e CROUS.
Selon qu’ils ou elles dispensé·es ou d’assiduité, les
étudiant·e appartienne non
sont à u groupe de caractérisé par
s
cod uniqu nt
. n TD un
e e

25
25
Bases de données avancées
EXEMPLE : SPÉCIFICATIONS (2/2)

Les étudiant·es passent des épreuves et not

obtiennent
pour une e
chacune.
Le épreuve sont caractérisées par cod uniqu , ainsi que
s s et le lie un
dat auxquels elles se e e la
e u
déroulent.
C haque épreuve
relèv un matière unique (mais une
d' e
donnée peut donner e plumatière
sieur épreuves
lieu à s ).
Les m atière sont caractérisées par cod uniqu et un
un s e e intitulé.

26
26
Bases de données avancées
D É M A RC H E D E MODÉLIS ATION CONCEPTUELLE

1. Identifier clas s e
les s
as s ociatio entre les
2. Identifier ns classes
les attribut de chaque
et de chaque s classe-classe
3. Identifier
association
4. les
Identifier et souligner l’identifiant de chaque
classe
m ultiplicité
5. Évaluer less des associations

27
27
Bases de données avancées
EXEMPLE : D I AG RA M M E DE
CL ASSES

28

28
B as es de données avancées
 PARTIE 2
MODÉLISATION
LOGIQUE

29

29
B as es de données avancées
RELATIONNEL

Modèle associé aux S G B D relationnels

(ex. Oracle, S Q L Server, DB2, PostgreSQL, MariaDB,
MySQL…)

Objectifs du modèle
– Indépendance relationnel
physique
– Traitement du problème de redondance des
– données Langages non procéduraux (faciles à
– utiliser) Devenir un standard

30
[Link] 30
Bases de données avancées /
S GB D RELATIONNELS

Langages d’interrogation puissants et

déclaratifs Accès orienté valeur
Grande simplicité, absence de considérations
physiques Description du schéma très réduite
L D D intégré au L M D
Grande dynamique de
structure Optimisation de
requêtes

31
Utilisation interactive ou à
[Link] 31
Bases de données avancées /
ATTRIBUTS Modèle
relationne
l

Une relation R est un ensemble d’attributs {A 1 , A 2 ,

… , A n }. ex. La relation EPREUVE est l’ensemble des
attributs
{CodeEpr, DateEpr, Lieu}.

Chaque attribut A i prend ses valeurs dans un

domaine dom(A i ).
ex. N ote ∈ [0 , 2 0 ]
Lieu ∈ { ' A m p h i Say', 'Amphi Aubrac', 'Salle

32
D101', … }
Bases de données avancées[Link]
/
32
TS Modèle
relationn
el

Notation d’une relation : R (A 1 , A 2 , … ,

A n ) ex. EPREUVE (CodeEpr, DateEpr,

Lieu)

Un n-uplet t est un ensemble de valeurs t = < V 1 , V 2 ,

… , V n > où V i ∈ dom(A i ) ou bien V i est la valeur nulle
(NULL).

ex. <'InfoS2', '30-06-2016', 'Amphi A u b r a c ' > est un

33
[Link] 33
Bases de données avancées /
D’INTÉGRITÉ (1/2) Modèle
relationn
el

Clé primaire : Ensemble d’attributs dont les valeurs

permettent de distinguer les n-uplets les uns des
autres.
ex. CodeEpr est clé primaire de la relation EPREUVE.

Clé étrangère : Attribut qui est clé primaire d’une

autre relation.
ex. Connaître la matière dont relève chaque épreuve
* ajout de l’attribut CodeMat à la relation EPREUVE

34
[Link] 34
Bases de données avancées /
D’INTÉGRITÉ (2/2) Modèle
relationn
el

Notations :Clés primaires soulignées, clés

étrangères postfi xées par le caractère # .
ex. EPREUVE (CodeEpr, DateEpr, Lieu, C ode Mat# )

Contraintes de domaine : Les attributs doivent

respecter une condition logique.
ex. Note ≥ 0 ET Note ≤ 2 0

35
[Link] 35
Bases de données avancées /
PRATIQUE Modèle
relationn
el

EPREUVE MATIERE

CodeEpr DateEpr Lieu Codemat#

CodeMat Intitulé
Amph
ECOS10 15/01/20 ECO
i
1 16 Aubra ECO Économie
c
Amph
ECOS10 16/01/20 ECO
Aubrac
i
2 16
GES Gestion
GESS201 25/05/2016 Salle 201 GES

INFOS101 20/01/2016 Salle 101 INFO

INFO Informatiqu
e

36
[Link] 36
Bases de données avancées /
RELATIONNEL (1/4)
Chaque classe devient une relation.

Les attributs de la classe deviennent attributs

de la relation.

L’identifiant de la classe devient clé primaire

de la relation.

ex. ETUDIANT (NumEtu, Nom, Prénom, DateNaiss,

Rue, CP, Ville)

37
[Link] 37
Bases de données avancées /
RELATIONNEL (2/4)

Chaque association 1-1 est prise en

compte en incluant la clé primaire d’une
des relations participante comme clé
étrangère dans l’autre relation.

ex. C ARTE_IZ LY (N um Carte, S oldeC R O U S)

ETUDIANT (NumEtu, Nom, Prénom, DateNaiss,

Rue, CP, Ville, NumCarte#)

38
[Link] 38
Bases de données avancées /
RELATIONNEL (3/4)

Chaque association 1-N est prise en compte en

incluant la clé primaire de la relation dont la
multiplicité maximale est 1 comme clé étrangère
dans l’autre relation participante.

ex. EPREUVE (CodeEpr, DateEpr, Lieu,

CodeMat# ) MATIERE (CodeMat, Intitulé)

39
[Link] 39
Bases de données avancées /
RELATIONNEL (4/4)

Chaque association M-N est prise en compte

en créant une nouvelle relation dont la clé
primaire est la concaténation des clés
primaires des relations participantes. Les
attributs de la classe-association sont insérés
dans cette nouvelle relation si nécessaire.

ex. PASSER (NumEtu#, CodeEpr#, Note)

40
[Link] 40
Bases de données avancées /
LOGIQUE RELATIONNEL Modèle
relationn
el

CARTE_IZLY (NumCarte, SoldeCROUS)

GROUPE_TD (CodeGroupe)

ETUDIANT (NumEtu, Nom, Prénom, DateNaiss,

Rue, CP, Ville, NumCarte#,
CodeGroupe# )

MATIERE (CodeMat, Intitulé)

EPREUVE (CodeEpr, DateEpr, Lieu,

CodeMat# ) PASSER (NumEtu#,

41
[Link] 41
Bases de données avancées /
ASSOCIATION M-N Modèle
relationn
el

ETUDIANT
N um Etu N om
Prénom
1110 Dupont Albertine PASSER (table « pont
2002 West James »)
N u m E t u Code E pr# Note
#
EPR E UV 1110 INFOS101 15,5
ECodeEp D ateEp Lie 2002 ECOS101 8,5
r r u
ECO S 1 0 15/01/201 Aubra 2002 ECOS102 13
1 6 c
ECO S 1 0 16/01/201 Aubra 1110 GESS201 14
2 6 c 2002 GESS201 14,5
G ES S 2 25/05/201 D 20
01 6 1
INFOS10 20/01/201 D 10

42
1 6 1
[Link] 42
Bases de données avancées /
REDONDANCE Modèle
relationn
el

Lorsque l’on effectue directement une modélisation

logique ex. Soit la relation PASSER_EPREUVE.

NumEtu Note CodeEpr Lieu

1110 15,5 INFOS101 Amphi Aubrac

1110 14,0 ECOS101 Amphi Aubrac
2002 13,0 ECOS102 Salle D201
3333 10,5 INFOS101 Amphi Aubrac

Cette relation présente différentes

anomalies.

43
[Link] 43
Bases de données avancées /
REDONDANCE Modèle
relationn
e

Anomalies de modification : Si l’on souhaite

mettre à jour le lieu d’une épreuve, il faut le faire
pour tous les n-uplets concernés.

Anomalies d’insertion : Pour ajouter une

nouvelle épreuve, il faut obligatoirement
fournir des valeurs pour NumEtu et Note.

Anomalies de suppression
ex. La suppression de l’étudiant n° 2 0 0 2 fait
perdre toutes les informations concernant
l’épreuve ECOS102.

44
[Link] 44
Bases de données avancées /
REDONDANCE

Pourquoi
?– Suppression des problèmes de mise à
– jour Minimisation de l’espace de
stockage

Comment
?– D a n s le modèle conceptuel, ne spécifier que des
attributs non décomposables (première forme
normale).
–
ex. Une adresse doit être décomposée en rue, code postal,
ville…

45
C’est tout ! [Link] 45
Bases de données avancées /
 Partie 3
Interrogation
et manipulation
de bases de
données
[Link] 46

46
B as es de données avancées
/
RELATIONNELLE ? Modèle
relationn
el

Ensemble d’opérateurs qui s’appliquent aux

relations

Résultat : nouvelle relation qui peut à son tour

être manipulée
⇒ L’algèbre relationnelle permet d’effectuer
des recherches dans les relations.

47
[Link] 47
Bases de données avancées /
ENSEMBLISTES (1/5) Modèle
relationn
el

Union : T = R ∪ S (notation algébrique)

ou T = UNION (notation
R et S doivent avoir mê me schéma.
(R, S) fonctionnelle)

ex. R et S sont les relations ETUDIANT de

deux formations (ex. anciens M 1 Finance et
Eco-Société) fusionnées pour constituer une
liste d’émargement
commune.
T
Notation
graphique : ○

48
B as e s de données
Bases de données
avancées avancées
[Link]
/
R S 49
ENSEMBLISTES (2/5) Modèle
relationn
el

Intersection : T = R ∩ S
ou T = INTERSECT (R, S)
R et S doivent avoir mê me schéma.
ex. Permet de trouver les étudiant·es
commun·es à deux formations.
Notation T
graphique :
�
�
R S

49
[Link]
Bases de données avancées /
ENSEMBLISTES (3/5) Modèle
relationn
el

Différence : T = R -
S ou T = MINU S (R,
R et S doivent
S) avoir m ê m e schéma.
ex. Permet de retirer les étudiant·es de la
relation S existant dans la relation R.
Notation T
graphique : -
R S

50
[Link]
Bases de données avancées /
ENSEMBLISTES (4/5) Modèle
relationn
el

Produit cartésien : T= R x S
ou T = PRODUCT (R, S)
Associe chaque n-uplet de R à chaque n-uplet
de S. Notation graphique :
T
x
R S

51
[Link]
Bases de données avancées /
CARTÉSIEN Modèle
relationn
el

NumEtu Nom CodeEprLieu

ex 10 E X INFO1 Aubra
. 1 1 ECO1 c
10 E ECO2 Aubra
2 2 c
D201
NumEtu Nom CodeEprLieu
101 E1 INFO1 Aubra
c
102 E2 INFO1 Aubra
= c
101 E1 ECO1 Aubra
c
102 E2 ECO1 Aubra

52
Bases de données avancées [Link] c
/
ENSEMBLISTES (5/5) Modèle
relationn
el

Division : T = R ÷ S
ou T=
R (A 1 , DIVISION
A 2 , … , A n ) (R, S) , … , A )
S (A p+ 1 n

T (A1 , A 2 , … , A p ) contient tous les n-uplets tels que

leur concaténation à chacun des n-uplets de S
toujours
donne un n-uplet de
R. Notation graphique
T
: 
R S

53
[Link]
Bases de données avancées /
ION Modèle
relationn
el

ex.

NumEtu CodeEprNote
101 IINNFFO 11
O11 CodeEprNote
101 ECO1 15 ÷ INFO 1
101 ECO2 12 1 1
102 ECO1 9 ECO2 1
103 IINNFFO 11 2
O11 NumEtu
103 ECO2 12 = 101
103

54
[Link]
Bases de données avancées /
SPÉCIFIQUES (1/3) Modèle
relationn
el

Projection : T = π < A , B, C > (R)

ou T = PR O JECT (R / A, B, C)
T ne contient que les attributs A, B et C
de R. ex. N o m s et prénoms des
étudiant·es.
Notation
graphique : T
A, B,
C
R

55
[Link]
Bases de données avancées /
SPÉCIFIQUES (2/3) Modèle
relationn
el

Res triction :T = σ < C > (R)

ou T = R E S TR ICT (R / C)
T ne contient que les attributs de R qui
satisfont la condition C.
ex. C = Étudiant·es qui habitent à Lyon.
Notation T
graphique :
C
R

56
[Link]
Bases de données avancées /
SPÉCIFIQUES (3/3) Modèle
relationn
el

Jointure naturelle : T = R > < S

ou T = JOIN (R, S)
Produit cartésien R x S et restriction A = B
sur les attributs A ∈ R et B ∈ S.
Notation T
graphique :
A B
=
R S

57
[Link]
Bases de données avancées /
REQUÊTE (1/4) Modèle
relationn
el

Notes des étudiant·es en précisant leurs

noms (et pas seulement leurs numéros)

RESULTAT

Nom,
CodeEpr,
Note

NumEt NumEt
u = u

ETUDIANT PASSER

58
[Link]
Bases de données avancées /
REQUÊTE (2/4) Modèle
relationn
el

Décomposition des
opérations
ETUDIANT PASSER
NumEtu NumEtu CodeEpr Note
Nom 101 INFO1
102
101 E2
E1 X 103
10 INFO1 15
103 E3 103 ECO1 12

59
[Link]
Bases de données avancées /
REQUÊTE (3/4) Modèle
relationn
el

[Link] Nom CodeEpr Not

101
u E1 101
[Link] INFFO
10
e
10 10 1
2 E 1 O1 0
10 2 10 INFO 1
3 E 1 1 0
= 10 3 10 INFO 1
1 E 3 1 5
10 1 10 INFO 1
2 E 3 1 5
103 2 103 INFO 15

60
10
Bases de données avancées E3 10
[Link] 1 1
/
REQUÊTE (4/4) Modèle
relationn
el

ETUDIANT ▷ ◁
PASSER
[Link] Nom [Link] CodeEpr Note
101 E1 101 INFO1 10
103 E3 103 INFO1 15
103 E3 103 ECO1 12

π <Nom, CodeEpr, Note> (ETUDIANT ▷ ◁

PASSER)
Nom CodeEpr Note
E1 INFO1 10 (Projection sur les
attributs
E3 INFO1 15 Nom, CodeEpr et Note)
E3 ECO1 12

61
[Link]
Bases de données avancées /
S GB D RELATIONNELS

Niveau 1 : Systèmes non relationnels.

Supportent uniquement la structure
tabulaire.

Niveau 2 : Systèmes relationnellement minimaux.

Permettent les opérations de restriction,
projection et jointure.

Niveau 3 : Systèmes relationnellement

complets. Toutes les opérations de l’algèbre
relationnelle.

62
[Link]
Bases de données avancées /
QUE SQL ?

Structured Query Language

Issu de SEQUEL (Structured English as a Query
Language)
LDD LMD LCD
Permet la définition, la manipulation et le contrôle
d’une
base de données relationnelle.

SQ L se base sur l’algèbre

relationnelle. Standard depuis

63
[Link]
Bases de données avancées /
PRINCIPAUX LDD

NUMBER(n) : nombre entier à n chiffres

NUMBER(n, m) : nombre réel à n chiffres au

total (virgule comprise) et m chiffres après
la virgule

VARCHAR(n) : chaîne de caractères de

taille n DATE : date au format ‘JJ-MM-

64
[Link]
Bases de données avancées /
D’INTÉGRITÉ LDD

Mot clé CONSTRAINT

Identification par un nom de

contrainte Clé primaire :

PRIMARY KEY (clé)

Clé étrangère :
FOREIGN KEY (clé) RE F E RE NC E S
table(attribut)

Contrainte de domaine :
CHECK (condition)

65
[Link]
Bases de données avancées /
DÉFINITION DES LDD
DONNÉES (1/2)
ex
.
CREATE TABLE Etudiant NumEtu NUMBER(8),
( Nom VARCHAR(255),
Prenom
VARCHAR(255),
DateNaiss DATE,
Rue
VARCHAR(255),
CP NUMBER(5),
CONSTRAINT EtuClePri PRIMARY
VilleKEY )
(NumEtu) VARCHAR(255),

66
Bases de données avancées
DÉFINITION DES LDD
DONNÉES
ex. (2/2)
CREATE TABLE Passer ( NumEtu NUMBER(8),
CodeEpr
VARCHAR(10), Note
NUMBER(5, 2),

CONSTRAINT PassClePri PRIMARY KEY (NumEtu,

CodeEpr), CONSTRAINT PassCleEtrEtu FOREIGN KEY

(NumEtu)
REFERENCES Etudiant (NumEtu),

CONSTRAINT PassCleEtrEpr FOREIGN KEY (CodeEpr)

67
Bases de données avancées REFERENCES Epreuve (CodeEpr),
[Link]
/
STRUCTURELLES (1/2) LDD

Ajout d’attributs
ALTER TABLE nom_table A D D (attribut TYPE, … )
ex. ALTER TABLE Etudiant A D D (tel
NUMBER(8))

Modifications d’attributs
ALTER TABLE nom_table MODIFY (attribut TYPE,
…)
ex. ALTER TABLE Etudiant MODIFY (tel
NUMBER(10))

Suppression d'attributs
ALTER TABLE nom_table D R O P COLUMN
attribut, ...

68
Bases de données avancées
STRUCTURELLES (2/2) LDD

Ajout de contrainte
ALTER TABLE nom_table
A D D CONSTRAINT nom_contrainte définition_contrainte
ex. ALTER TABLE Epreuve
A D D CONSTRAINT LieuValide CHECK (Lieu IN (‘Say’,
‘Aubrac’))

Suppression de contrainte
ALTER TABLE nom_table D R O P CONSTRAINT nom_contrainte
ex. ALTER TABLE Epreuve
D R O P CONSTRAINT LieuValide

69
Bases de données avancées
EX LDD

Définition : Structure de données physique

permettant d'accélérer les accès aux données

Exemple : CREATE IND E X IdxNomEtu O N Etudiant (Nom)

N B : La clé primaire d'une relation

est automatiquement indexée.

70
Bases de données avancées
ES LDD

Définition : Une vue est une table virtuelle

calculée à partir d’autres tables grâce à une
requête.

Création d’une vue

CREATE VIEW nom_vue A S requête

ex. CREATE VIEW lesNoms A S

SELECT Nom, Prenom F R O M Etudiant

71
Bases de données avancées
VUES (1/2) LDD

Simplification de l’accès aux

données en masquant les
opérations de jointure
ex. CREATE VIEW notesParEtudiant A S
SELECT [Link], Nom, Prenom, NumEpr,
Note F R O M Etudiant E, Passer P
W H E R E [Link] = [Link]

SELECT NumEtu, N o m F R O M
notesParEtudiant W H E R E Note > 1 0

72
Bases de données avancées
VUES (2/2) LDD

Sauvegarde indirecte de requêtes complexes

Présentation de m ê m e s données sou s
différentes formes adaptées aux différents
usagers particuliers
Support de l’indépendance logique
ex. Si la table Etudiant est remaniée, la vue
notesParEtudiant doit être refaite, mais les
requêtes qui utilisent cette vue n’ont pas à être
remaniées.
Renforcement de la sécurité des données par
m asquage des lignes et des colonnes sensibles
aux usagers non habilités

73
Bases de données avancées
UNE VUE LDD

Le mot clé DISTINCT doit être absent de la

requête.

La clause F R O M doit faire référence à une seule

table.

La clause SELECT doit faire référence

directement aux attributs de la table concernée
(pas d’attribut dérivé).

74
Bases de données avancées
SYSTÈMES LDD
ALL_TABLES (OWNER,
TABLE_NAME, … ) ALL_VIEWS
(OWNER, VIEW_NAME, … )
ALL_CONSTRAINTS (OWNER, TABLE_NAME,
CONSTRAINT_NAME, CONSTRAINT_TYPE,
SEARCH_CONDITION, ...)
ALL_CONS_COLUMNS (OWNER, TABLE_NAME,
CONSTRAINT_NAME, COLUMN_NAME, ...)
USER_CATALOG (TABLE_NAME, TABLE_TYPE)
USER_TAB_COLUMNS (TABLE_NAME,

..
USER_CONSTRAINTS (TABLE_NAME,
COLUMN_NAME,
CONSTRAINT_TYPE,
CONSTRAINT_NAME, …)

75
SEARCH_CONDITION, ...)
BasesUSER_IND_COLUMNS
de données avancées (INDEX_NAME,
[Link]
/ TABLE_NAME,
VUES SYSTÈMES LDD

Tables qui contiennent un attribut Intitulé

SELECT TABLE_NAME F RO M
USER_TAB_COLUMNS W H E RE
COLUMN_NAME = ‘INTITULE’

Attributs de la table Client

SELECT COLUMN_NAME F RO M
USER_TAB_COLUMNS W H E RE TABLE_NAME =
‘CLIENT’

Contraintes des tables de

l’utilisateur courant
SELECT TABLE_NAME,

76
CONSTRAINT_NAME F RO[Link]
M
Bases de données avancées
ALL_CONSTRAINTS /
DONNÉES LMD

Ajout d’un n-uplet

ex. INSERT INTO Matiere
VALUES (‘BDM1MBFA’, ‘Bases de
données’)

Modification de la valeur d’un attribut

ex. UPDATE Etudiant SET Nom=‘Dudule’
W H E RE NumEtu = 3 3 3 3 3 3
ex. UPDATE Passer SET Note = Note + 1

Suppression de n-uplets
ex. DELETE F RO M
Etudiant W H E RE
Ville = ‘Lyon’
ex. DELETE F RO M

77
Epreuve
Bases de données avancées
DES DONNÉES LMD

Par l’exemple, sur la base ETUDIANTS

CARTE_IZLY (NumCarte,
SoldeCROUS) GROUPE_TD
(CodeGroupe)
ETUDIANT (NumEtu, Nom, Prénom, DateNaiss, Rue, CP,
Ville, NumCarte#, CodeGroupe#)
MATIERE (CodeMat, Intitulé)
EPREUVE (CodeEpr, DateEpr, Lieu,
CodeMat#) PASSER (NumEtu#,
CodeEpr#, Note)

Note : Les symboles [ ] indiquent une clause optionnelle d’une

78
Bases de données avancées
requête dans les transparents suivants.
CHAMP CALCULÉ LMD

Tous les n-uplets d’une table : étoile

(*) ex. SELECT * F R O M Etudiant

Tri du résultat
ex. Par ordre alphabétique [inverse] de
nom SELECT * F R O M Etudiant
O R D E R BY N o m [DESC]

Champs calculés
ex. Transformation de notes sur 2 0 en notes
sur 1 0 S ELEC T N ote / 2 FR O M Pas s er

79
[Link]
Bases de données avancées /
RESTRICTION LMD

Projection
ex. N o m s et Prénoms des étudiant·es,
uniquement (pas les autres attributs)
SELECT Nom, Prénom F R O M Etudiant
Suppression des doublons
ex. SELECT DISTINCT N o m F R O M Etudiant
Restriction
ex. Étudiant·es qui habitent à
Lyon SELECT * F R O M
Etudiant W H E R E Ville =
‘Lyon’

80
[Link]
Bases de données avancées /
RESTRICTION (1/3) LMD

ex. Épreuves se déroulant après le

01/01/2016
SELECT * FROM Epreuve
WHERE DateEpr >= '01-01-
2016'

ex. Notes comprises entre

10 et 20
SELECT * FROM Passer
WHERE Note BETWEEN 10
AND 20

ex. Notes indéterminées

81
Bases de données avancées
RESTRICTION (2/3) LMD

ex. Étudiant·es habitant une ville dont le nom se

termine par sur-Saône

SELECT * FROM Etudiant

WHERE Ville LIKE ‘%sur-
Saône’

‘sur-Saône%’ ⇒ commence
par sur-Saône
‘%sur%’ ⇒ contient le
mot sur

82
Bases de données avancées
RESTRICTION (3/3) LMD

ex. Prénoms des étudiant·es dont le nom est

Dupont, Durand ou Martin
SELECT Prénom FROM Etudiant
WHERE Nom IN (‘Dupont’, ‘Durand’, ’Martin’)

NB : Possibilité d’utiliser la négation pour tous ces

prédicats
* NOT BETWEEN, NOT NULL, NOT LIKE, NOT IN.

83
Bases de données avancées
LOGIQUES LMD

ET. ex. Épreuves se déroulant le 15/01/2016 en salle

D 2 0 1 SELECT * F R O M Epreuve
W H E R E DateEpr = ‘15-01-2016’ A N D Lieu = ‘D201’
OU. ex. Étudiant·es né·es avant 1 9 9 0 ou habitant
hors Lyon SELECT * F R O M Etudiant
W H E R E DateNaiss < ‘01-01-1990’ O R Ville < >
‘Lyon’
Combinaisons. ex. Étudiant·es né·es après 1 9 9 0 et
habitant Lyon ou Vienne
SELECT * F R O M Etudiant
W H E R E DateNaiss > ‘31-12-1990’
A N D (Ville = ‘Lyon’ O R Ville = ‘Vienne’)

84
Bases de données avancées
D’AGRÉGAT LMD

Elles opèrent sur un ensemble de valeurs et les

agrègent.
AVG(), VARIANCE(), STDDEV() : moyenne,
variance et écart-type des valeurs
SUM() : s o m m e des valeurs
MIN(), MAX() : valeur minimum, valeur
maximum COUNT() : nombre de valeurs
ex. Moyenne des notes
SELECT AVG(Note) F R O M Passer

85
Bases de données avancées
OPÉRATEUR DISTINCT LMD

ex. Nombre total de notes

SELECT COUNT(*) FROM Passer

SELECT COUNT(NumEtu) FROM Passer

ex. Nombre d'étudiant·es noté·es

SELECT COUNT(DISTINCT NumEtu) FROM

Passer

86
[Link]
Bases de données avancées /
COUNT/DISTINCT LMD

Table PASSER

NumEtu CodeEpr Note

101 INFO1 10
103 INFO1 15
103 ECO1 12

COUNT(NumEtu) ⇒ Résultat = 3

COUNT(DISTINCT NumEtu) ⇒
Résultat = 2

87
Bases de données avancées
(1/3) LMD

ex. Liste des notes avec le nom des

étudiant·es
SELECT Nom, CodeEpr,
RESULTAT
Note FROM Etudiant,
Passer Nom,
WHERE [Link] =
CodeEpr,
[Link]
Note

NumEt NumEt
u = u

ETUDIANT PASSER

88
Bases de données avancées
(2/3) LMD

ex. Idem avec le numéro d'étudiant en plus

SELECT [Link], Nom, CodeEpr,
Note FROM Etudiant E, Passer P
WHERE [Link] =
[Link] ORDER BY Nom,
Note DESC

NB : Utilisation d’alias
(E et P) pour alléger

89
l’écriture
Bases de données avancées
(3/3) LMD

Jointure exprimée avec le prédicat IN

ex. Notes des épreuves passées le 23 septembre

2016
SELECT Note FROM

Passer WHERE CodeEpr

SELECT CodeEpr FROM requêt
IN ( Epreuve e
WHERE DateEpr = ‘23-09-2016’)
Sous-
NB : Il est possible d’imbriquer des
requêtes.

90
Bases de données avancées
D’EXISTENCE LMD

Prédicats E X IS TS / N O T E X IS TS
ex. Étudiant·es qui ont passé au moins une
épreuve [n’ont passé aucune épreuve]

SELECT * F R O M
Etudiant E W H E R E
[NOT] EXISTS (
SELECT * F R O M Passer P
W H E R E [Link] =

91
[Link] )
Bases de données avancées
DÉNOMBREMENT LMD

Prédicats ALL / AN Y

ex. Numéros des étudiant·es qui ont obtenu au

moins une note supérieure à chacune [à au
moins une] des notes obtenues par l'étudiant·e
n° 1000.

SELECT DISTINCT NumEtu F R O M

Passer W H E R E Note > ALL [ANY] (
SELECT Note F R O M
Passer W H ER E N u m Etu
= 1000

92
Bases de données avancées
T (1/2) LMD

ex. Moyenne de chaque

étudiant·e
SELECT NumEtu,
AVG(Note) FROM Passer
GROUP BY NumEtu

ex. Nombre de notes

par étudiant·e
SELECT NumEtu,
COUNT(*)
FROM Passer

93
Bases de données avancées
GROUP BY
T (2/2) LMD

ex. Note moyenne pour les étudiant·es ayant passé

moins de 5 épreuves
SELECT NumEtu,
AVG(Note) FROM Passer
GROUP BY NumEtu
HAVING COUNT(*) <
5

Attention : La clause
HAVING ne s’utilise qu’avec
GROUP BY.

NB : HAVING : évaluation de condition sur un

94
Bases de résultat de groupement (a posteriori)
données avancées
ION LMD

Ex. Numéro des étudiant·es qui ont passé toutes

les épreuves

N B : Il n'existe pas d'opérateur de division en SQL

! De u x stratégies :
– Étudiant·es tels qu'il n'existe pas d’épreuve tel qu'il
n'existe pas de « passage » pour cet étudiant·e et cette
– épreuve.
Étudiant·es qui ont passé un nombre distinct d’épreuves

95
égal au nombre total d’épreuves.
Bases de données avancées
LOGIQUE LMD

SELECT NumEtu
FROM Etudiant Et
WHERE NOT EXISTS
(
SELECT *
FROM Epreuve Ep
WHERE NOT EXISTS
(
SELECT *
FROM Passer
P

96
Bases de données avancées WHERE [Link] = [Link]
COMPTAGE LMD

SELECT NumEtu F R O M
Etudiant E W H E R E ( SELECT
COUNT(CodeEpr)
F R O M Passer P
W H E R E [Link]
= [Link] )
= ( SELECT COUNT(*) F R O M
Epreuve )
ou
SELECT NumEtu F R O M
Passer GROUP BY NumEtu

97
HAVING
Bases de données avancées COUNT(CodeEpr)
ENSEMBLISTES LMD

INTERSECT, MINUS, UNION

ex. Code des épreuves ayant soit lieu dans

l’Amphi Aubrac, soit ayant été passées par
l'étudiant·e n° 102

SELECT CodeEpr FROM

Epreuve WHERE Lieu =
'Amphi Aubrac‘
UNION
SELECT CodeEpr FROM

98
Bases de données avancées
(1/5) LMD

Exemple de V él
o
hiérarchie
(nomenclature) : C ad R ou
re es
Relation associée :
ELEMENT (No_Elt, Dési, P ne R ayo
Parent#) u ns
1 Vélo NULL
2 Cadre 0
3 Roue 0 6 Rayon1 2
1 0 1 2
4 Roue 2 7 Rayon1 2
2 3 2 3

99
5 Pneu 8 Rayon1
Bases de données avancées
1 3
(2/5) LMD

ex. Structure hiérarchique des éléments à partir de

la racine

SELECT Dési FROM Element

CONNECT BY Parent = PRIOR
No_Elt START WITH Parent IS
NULL;
Racine de Ordre de parcours
la de la
hiérarchi hiérarchie

100
e avancées
Bases de données
(3/5) LMD

ex. Idem avec indication du niveau dans la

hiérarchie

SELECT LEVEL, Dési FROM

Element CONNECT BY Parent =
PRIOR No_Elt START WITH Parent
Résultat
IS NULL; 1 Velo 3
: 2 Cadre Rayon12
3 Roue1 3
4 Pneu1 Rayon13
3 2 Roue2

101
Rayon11 3 Pneu2
Bases de données avancées
3
(4/5) LMD

ex. Idem avec élagage d’une branche de la

hiérarchie

SELECT LEVEL, Dési FROM Element

CONNECT BY Parent = PRIOR No_Elt AND Dési <>
‘Roue2' START WITH Parent IS NULL;

Résultat 1 Velo 3
: 2 Cadre Rayon12
3 Roue1 3
4 Pneu1 Rayon13
3 2 Roue2

102
Bases de données avancées
Rayon11
(5/5) LMD

ex. Nombre d'éléments dans chaque

niveau

Il est possible d'utiliser le

groupement. SELECT LEVEL,

COUNT(No_Elt)
FROM Element
CONNECT BY Parent = PRIOR

103
No_Elt START WITH Parent IS
NULL
Bases de données avancées
FONCTIONS SQL (1/2)
– ABS(n) : Valeur absolue de n – LOWER(ch) : c en minuscules
– CEIL(n) : Plus petit entier ≥ n – UPPER(ch) : c en majuscules
– – LTRIM(ch, n) : Troncature à
FLOOR(n) : Plus grand entier
– – gauche RTRIM(ch, n) :
≤ n MOD(m, n) : Reste de
– Troncature à droite
– m/n POWER(m, n) : mn
– REPLACE(ch, car) :
– SIGN(n) : Signe de n – Remplacement de caractère
– SQRT(n) : Racine carrée – SUBSTR(ch, pos, lg) : Extraction de chaîne
– de n – SOUNDEX(ch) : Représentation phonétique
– ROUND(n, m) : Arrondi à 10-m de ch LPAD(ch, lg, car) : Compléter à
– TRUNC(n, m) : Troncature à gauche
– 10-m RPAD(ch, lg, car) : Compléter à droite
CHR(n) : Caractère ASCII n°

104
n INITCAP(ch) : 1re lettre
en de
Bases maj.
données avancées
SQL (2/2) LMD

– ASCII(ch) : Valeur ASCII de ch – TO_NUMBER(ch) : Conversion de ch

– INSTR(ch, ssch) : Recherche de en nombre
ssch dans ch – TO_CHAR(x) : Conversion de x en
– LENGTH(ch) : Longueur de ch – chaîne
– ADD_MONTHS(dte, n) : Ajout de n – TO_DATE(ch) : Conversion de ch en date
mois à dte NVL(x, val) : Remplace par val si x a la
– LAST_DAY(dte) : Dernier jour du – valeur NULL
– mois MONTHS_BETWEEN(dt1, – GREATEST(n1, n2…) : +
dt2) : – grand LEAST (n1, n2…) : +
– Nombre de mois entre dt1 et dt2 – petit
USER : Nom de
– NEXT_DAY(dte) : Date du
lendemain SYSDATE :
UID : Identifiant numérique
l’utilisateur
de l’utilisateur ..

105
Date/heure système
Bases de données avancées
DE FONCTIONS LMD

SELECT UID, U S E R F R O M DUAL;

SELECT GREATEST(1, 2, 3) F R O M DUAL;

SELECT Nom, Prenom,

FLOOR( MONTHS_BETWEEN(SYSDATE , DateNaiss) /
12) Age
FRO M Etudiant;

UPDATE Passer SET Note = NVL(Note, 10);

106
Bases de données avancées
TRANSACTIONS LCD

Transaction : ensemble de mises à jour des

données (⇒ modifications structurelles)

Début de transaction : début de la session de

travail ou fin de la transaction précédente
Connexio Déconnexio
n n

Transaction Transaction Transaction

107
1 2 3
Bases de données avancées
TRANSACTIONS LCD

Validation (et fin) d’une transaction :

COMMIT

Annulation (et fin) d’une transaction :

ROLLBACK

108
Bases de données avancées
TEURS LCD

Création
– ex. CREATE U S E R moi_meme
IDENTIFIED BY mon_mot_de_passe

Suppression
– ex. D R O P U S E R moi_meme C A S C A D E

Modification
– ex. ALTER U S E R moi_meme IDENTIFIED BY
aaaaa

109
Bases de données avancées
GLOBAUX LCD

Droit d'effectuer une action sur les

objets de l'utilisateur seulement
– ex. CREATE TABLE
ALTER
IND E X
D R O P VIEW

Droit d'effectuer une action dans tous les

schémas de la base de données
– ex. CREATE ANY TABLE
ALTER ANY
IND E X D R O P

110
ANY VIEW
Bases de données avancées
LES OBJETS B D LCD

Privilège Signification Tables Vues

ALTER Destruction X
DELETE Suppression X X
INDEX Construction X
INSERT Insertion X X
REFERENCES Clé étrangère X
SELECT Lecture X X
UPDATE Mise à jour X X
ALL Tous X X

111
Bases de données avancées
LES LCD

Rôles
prédéfinis
– CONNECT : droit de création de tables, vues,
– synonymes, etc. RE S OU RC E : droit de création de
procédures stockées, déclencheurs, etc.
– D B A : administrateur de la B D

Création de nouveaux
rôles
– ex. CREATE ROLE role1

112
Bases de données avancées
PRIVILÈGES LCD

Transmission de privilèges
GRANT privilège O N table|vue
TO user|PUBLIC [WITH GRANT
OPTION]

Privilèges sur des objets

– ex. GRANT SELECT O N ma_table TO
toto
– ex. GRANT SELECT O N ma_table TO
PUBLIC
– ex. GRANT SELECT O N ma_table TO
role1

Privilèges globaux et rôles

113
– ex. GRANT CREATE ANY TABLE TO toto
Bases de données avancées
PRIVILÈGES LCD

Suppression de privilèges
REVOKE privilège O N table|vue F R O M user|
PUBLIC

Privilèges sur des

– ex. REVOKE objets
SELECT O N ma_table F R O M
– toto ex. REVOKE SELECT O N ma_table
– FR O M PUBLIC
ex. REVOKE SELECT O N ma_table F R O M
Privilèges
role1 globaux et
rôles
– ex. REVOKE CREATE ANY TABLE F R O M
– toto ex. REVOKE CONNECT, RE S OU RC E
– FR O M toto

114
ex. REVOKE role1 F R O M toto
Bases de données avancées
L SQL
Pour approfondir SQL en
ligne…

115
Bases de données avancées
 Partie 4
Programmation
de bases de
données

116
B as es de données avancées
UN PROGRAMME
SQL encapsulé : Requêtes SQ L incorporées dans le code C
source (PL/SQL, T-SQL, PL/pgSQL, Pro*C…)

API : Requêtes S Q L via des fonctions du

langage (Java Persistence API, PHP Data
U
Objects…)

Interfaces de niveau appel : intergiciel entre le langage et le

R
S G B D (ODBC, JDBC, A D O … )

Procédures stockées : Fonctions S Q L stockées dans la S

base de données et exécutées par le S G B D
(écrites en PL/SQL, T-SQL, PL/pgSQL)
E

117
Bases de données avancées
LANGAGE PL/SQL (1/2)
Langage de 4 e génération (L4G = L3G + syntaxe
type SQL)

Conçu com m e une extension de

S QL Déclaration de variables et de

constantes

Types abstraits (collections, enregistrements,

objets) Modularité (sous-programmes,

118
paquetages)
Bases de données avancées
LANGAGE PL/SQL (2/2)
SQ L dynamique (construction de requêtes à la
volée)

Programmation orientée objet

Performance (traitement par lots)

Productivité (uniformité des outils

Oracle) Portabilité (sur tous

systèmes Oracle)

119
Sécurité (procédures stockées,
Bases de données avancées
D’ORACLE
Génie
Résea
u
log icie SQ
S QlL L
Developer
S erveu
r
Oracle

PL/
Lo g iciel SQL Administratio
s n

120
tiers
Bases de données avancées
ORACLE

Oracle Database
PL/SQL User’s Guide
and Reference

121
Bases de données avancées
BLOCS
Bloc anonyme
– Stocké dans un fichier
– Compilé et exécuté à la
volée B
Procédure A

stockée
– Compilée a priori
– Stockée dans la base de Dé
données c
B
D éclenche D
ur
– Procédure stockée associée à une table PS
– Exécution automatique à la suite d’un
événement

122
Bases de données avancées
D’UN BLOC

[DECLARE
-- Types, constantes et variables]
BEGIN
-- Instructions PL/SQL
[EXCEPTION
-- Gestion des erreurs]
END;

123
Bases de données avancées
CONSTANTES

Déclaration dans la section DE C L ARE d’un bloc

PL/SQL Variables
ex. date_naissance DATE;
compteur INTEGER : = 0; --
Initialisation compteur2 INTEGER
DEFAULT 0;-- Valeur par
défaut
id CHAR(5) NOT NULL : = ‘AP001’;
Constantes
ex. taux_tva CONSTANT REAL : = 0.2;

124
Bases de données avancées
DONNÉES

Oracle Database
PL/SQL User’s Guide
and Reference

125
Bases de données avancées
TYPE EXISTANT

Type d’une autre

variable ex. credit
R EAL;
debit credit
%TYPE;
Type de l’attribut d’une table À utiliser
ex. num_emp [Link] au
%TYPE; maximum !
Type des n-uplets d’une table
ex. un_etudiant S TU D EN T
% R O W TYPE;

126
Bases de données avancées
DE VARIABLE
Affectation simple
ex. n : = 0;
n : = n + 1;

Valeur de la base de données

ex. SELECT custname INTO nom_client
F R O M customer W H E R E custnum
= 10;
SELECT ename, sal INTO nom, salaire
F R O M emp W H E R E empno =

127
5000;
Bases de données avancées
LOGIQUES

Opérateurs + - / *
arithmétiques **
O pérateur de concaténation ||

O pérateurs de com parais on = < > < = > =

IS NULL LIKE <BETW
> EEN
IN
O pérateurs log iques OR
AN D NOT

128
Bases de données avancées
(1/2)
IF-THEN, IF-THEN-ELSE ou IF-THEN-
ELSIF

IF condition1 THEN
-- Statements
[ELSIF condition2 THEN
-- Instructions
PL/SQL]
[ELSE
-- Instructions PL/SQL]

129
Bases de données avancées END IF;
(2/2)
CASE

CASE variable
WHEN val1 THEN -- Instruction PL/SQL
WHEN val2 THEN -- Instruction PL/SQL
WHEN val3 THEN -- Instruction PL/SQL
[ELSE -- Instruction par
défaut]
END CASE;

130
Bases de données avancées
CLES
Pour
FOR iterateur IN [REVERSE] min..max
LOOP
-- Instructions
PL/SQL E N D LOOP;
Tant que
WHILE condition
LOOP
-- Instructions
PL/SQL E N D LOOP;
Répéter
LOOP
-- Instructions

131
PL/SQL EXIT W H E N
Bases de données avancées condition;
ÉCRAN
DBMS_OUTPUT.PUT('chaîne'); /* Pas de retour à la ligne
DBMS_OUTPUT.PUT_LINE('chaîn */
e');DBMS_OUTPUT.PUT('Hello world /* Retour à la ligne */
!');
DBMS_OUTPUT.PUT_LINE('nom = ' || nom);
DBMS_OUTPUT.PUT_LINE('n = ' || TO_CHAR(n));
DBMS_OUTPUT.PUT_LINE('n = ' || n);
NB : Pour que l’affi chage fonctionne, il faut mettre la variable
d’environnement SERVEROUTPUT à ON.
SET SERVEROUTPUT ON dans SQL
Developer
En cas de dépassement, la taille du tampon d’affi chage doit être
augmentée. ex. DBMS_OUTPUT.ENABLE(10000);

132
Bases de données avancées
ANONYME
-- Calcul de prix TTC
DECLARE
taux_tva CONSTANT REAL := 0.2;
prix product.prod_price%TYPE;
BEGIN
-- Affectation du prix
SELECT prod_price INTO prix FROM product
WHERE prod_code = 'Pr345blue';
-- Ajout de la TVA
prix := prix * (1 + taux_tva);
-- Affichage écran
DBMS_OUTPUT.PUT_LINE(prix || ' euros');
END;

133
Bases de données avancées
TIONS
Définition : Ensemble ordonné d’éléments de m ê m e type.
Chaque élément est indexé par sa position dans la
collection.

Deux types(VARRAY)
– Tableau de collections
: taille bornée, dense
– Liste (TABLE) : taille extensible, non-
dense

Oracle Database
PL/SQL User’s Guide
and Reference

134
Bases de données avancées
COLLECTION
1. Déclarer un type
collection
ex TYPE Liste_Chaines IS TABLE OF
. VARCHAR(20);
TYPE Tableau_Entiers IS VARRAY(10) OF
INTEGER;
2. Déclarer une collection et
l’initialiser
ex ma_liste Liste_Chaines : = Liste_Chaines('Aa', 'Bb',
. 'Cc'); t Tableau_Entiers : = Tableau_Entiers();

N B : Une collection peut être déclarée vide (c’est le cas

de t). Il n’est pas obligatoire d’initialiser tous les
éléments d’un tableau.

135
Bases de données avancées
COLLECTION
Collection
entière
ex. TYPE T1 IS TABLE OF
D ECLARE INT; TYPE T2 IS TABLE
OF INT; et11 T1 : =
T1(1, 2, 3, 4);
et12 T1 : = T1(5, 6);
BEGI et2 T2 : = T2();
N et12 : = et11; --
… Légal et2 : = et11; --
Illégal
Elément d’une
collection
ex. et11(1) : = 10;

136
Bases de données avancées
COLLECTION (1/2)
Ensemble de méthodes ( ≈ procédures)
Usage: nom_collection.nom_methode[(paramètres)]

EXISTS(i) renvoie TRUE si le ie élément existe dans la

collection. COUNT renvoie le nombre d’éléments dans la

collection.

LIMIT renvoie la taille maximum de la collection (NULL pour les

137
listes). EXTEND(n) augmente la taille de la collection de n.
Bases EXTEND(1)
de données avancées ⇔ EXTEND
COLLECTION (2/2)

TRIM(n) supprime n éléments en fin de collection (la

taille de la
collection diminue
automatiquement). TRIM ⇔
TRIM(1)

DELETE(i) et DELETE suppriment respectivement le ie

élément et tous les éléments de la collection (listes
seulement).

FIRST et LAST renvoient respectivement l’index du

premier et du dernier élément de la collection.

138
N B : FIRST = 1 et LAST = COUNT dans un tableau.
Bases de données avancées
COLLECTION
DECLARE
TYPE ListeEntiers IS TABLE OF INTEGER;
pile ListeEntiers := ListeEntiers();
element INTEGER;
BEGIN
-- On empile les valeurs 1 et 11
[Link];
pile([Link]) := 1;
[Link];
pile([Link]) :=
11;
-- On dépile
element := pile([Link]); -- element = 11
[Link]; -- Suppression en haut de pile

139
Bases de données avancées
EMENTS
Définition : Ensemble de données liées stockées
dans des champs.

1. Déclarer un type enregistrement

ex TYPE Etudiant IS
. RE C OR D ( numetu
INTEGER, n o m
VARCHAR(50), age
INTEGER );
2. Déclarer un
enregistrement
ex. un_etudiant Edutiant;

140
Bases de données avancées
D’ENREGISTREMENT
Référence directe
ex. un_etudiant.numetu : =
12212478;
un_etudiant.nom : = 'Toto';
un_etudiant.age : = 6;

un_etudiant : = mon_etudiant; --
équivalent à Etudiant
%ROWTYPE

Résultat de requête
ex. SELECT student_number, student_name,
student_age INTO un_etudiant

141
FRO M student
Bases de données avancées
DURES

PROCEDURE nom_proc (param1, param2…) IS

-- Déclarations locales (pas de clause DECLARE)
BEGIN
-- Instructions PL/SQL
[EXCEPTION
-- Gestion des exceptions]
END;

142
Bases de données avancées
IONS

FUNCTION nom_fonction (param1, param2…)

RETURN type_valeur_retour IS
-- Déclarations locales
BEGIN
-- Instructions PL/SQL
RETURN valeur_retour;
[EXCEPTION
-- Gestion des
exceptions]
END;

143
Bases de données avancées
DÉCLARATION/PARAMÉTRAGE DE SOUS-
PROGRAMMES

Déclaration : Tout sous-programme doit être défini

d’être
avant
appelé.
* définition dans la section D E C L ARE d’un bloc
PL/SQL

Définition et mode de passage des

paramètres
– IN: nom d’entrée
Paramètre _param(lecture
[IN | Oseule
UT | /IN
par valeur)
O– UT]
OUT:TYPE Paramètre de sortie (écriture seule / par
– IN
ex. resultat référence)
OUT REAL
OUT: Paramètre d’entrée-sortie (lecture-écriture / par

144
Bases de données avancées
référence)
PROCÉDURE

PROCEDURE Conversion_USD_EUR (prix_USD IN REAL,

prix_EUR OUT REAL) IS

taux CONSTANT REAL := 0.89;

BEGIN
prix_EUR := prix_USD * taux;
END;

145
Bases de données avancées
(RÉCURSIVE)
-- Calcul de n!

FUNCTION facto (n INTEGER) RETURN INTEGER IS

BEGIN
IF n = 1 THEN -- Condition d’arrêt
RETURN 1;
ELSE
RETURN n * facto(n - 1); -- Appel récursif
END IF;
END;

146
Bases de données avancées
PROGRAMMES
-- Exemple
D E C L AR E
hundredBucks CONSTANT REAL : =
100; resEuro REAL;
fact10 INTEGER;
BEGIN
Conversion_USD_EUR(hundredBucks,
resEuro); fact10 : = facto(10);
END;

147
Bases de données avancées
CURSEUR

Définition : Structure de données qui stocke le résultat

d’une requête retournant plusieurs n-uplets.

Déclaration : C U R S O R nom_curseur IS
requete_SQL; ex. C U R S O R calc_TVA IS
SELECT prod_num, price * 1.2 A S
prix_TTC F R O M product;

N B : Les n-uplets du curseur sont de

type calc_TVA%ROWTYPE.

148
Bases de données avancées
IMPLICITE
-- Parcours complet du curseur
DECLARE
CURSOR calc_TVA IS
SELECT prod_num, price * 1.2 AS prix_TTC
FROM product;
nuplet calc_TVA%ROWTYPE;

BEGIN
FOR nuplet IN calc_TVA LOOP
DBMS_OUTPUT.PUT_LINE(
nuplet.prod_num
|| ' : ' ||
nuplet.prix_TTC);

149
END LOOP;
Bases de données avancées
EXPLICITE
-- Parcours ad hoc du curseur

DECLARE
-- Comme précédemment

BEGIN
OPEN calc_TVA;
FETCH calc_TVA INTO nuplet; -- 1re ligne
WHILE calc_TVA%FOUND LOOP
-- Instructions PL/SQL
FETCH calc_TVA INTO nuplet; -- Ligne suivante
END LOOP;
CLOSE calc_TVA;
END;

150
Bases de données avancées
CURSEURS

% N O T F O U N D est égal à FALSE si FETCH renvoie un

résultat.

% F O U N D est égal à TRUE si FETCH renvoie un

résultat.

% R O W C O U N T renvoie le nombre de n-uplets lus.

151
% I S O P E N est égal à TRUE si le curseur est ouvert.
Bases de données avancées
PARAMÉTRÉ
DECLARE
CURSOR c(s number) IS SELECT ename, sal FROM emp WHERE sal
>= s; nuplet c%ROWTYPE;

BEGIN
OPEN c(2500);
FETCH c INTO
nuplet; WHILE c
%FOUND LOOP
DBMS_OUTPUT.PUT_LINE([Link] || ' : ' ||
[Link]); FETCH c INTO nuplet;
END
LOOP;

152
CLOSE
Bases de données avancées
c;
IONS

Quand une erreur survient, une exception est levée

(exécutée). Gestion des erreurs dans des routines
séparées du programme principal
Avantages
– Gestion systématique des erreurs
– Gestion groupée des erreurs
– similaires Lisibilité du code

Fonctions PL/SQL de gestion des

erreurs
– S Q L C O D E : Code de la dernière exception
– levée S Q L E R R M : Me s s a g e d’erreur

153
associé
Bases de données avancées
SYSTÈMES
Libellé erreur Code SQLCODE
erreur
CURSOR_ALREADY_OPEN ORA-06511 -6511
DUP_VAL_ON_INDEX ORA-00001 -1
INVALID_CURSOR ORA-01001 -1001
INVALID_NUMBER ORA-01722 -1722
LOGIN_DENIED ORA-01017 -1017
NO_DATA_FOUND ORA-01403 -1403
NOT_LOGGED_ON ORA-01012 -1012
PROGRAM_ERROR ORA-06501 -6501
STORAGE_ERROR ORA-06500 -6500
TIMEOUT_ON_RESOURCE ORA-00051 -51
TOO_MANY_ROWS ORA-01422 -1422
VALUE_ERROR ORA-06502 -6502
ZERO_DIVIDE ORA-01476 -1476

154
[Link]
Bases de données avancées /
PERSONNALISÉES
Déclaration (section DECL ARE)
nom_e xception EXCEPTION;

Lever l’exception (section BEGIN)

IF condition THEN
RAISE
nom_exception; E N D
IF;

Gérer l’exception
(section EXCEPTION)

155
WHEN
Bases de données avancées
D’EXCEPTION
DECLARE
c INTEGER;
personne EXCEPTION;
BEGIN
SELECT COUNT(*) INTO c FROM
emp; IF c = 0 THEN
RAISE personne;
END IF;
EXCEPTION
WHEN personne
THEN
RAISE_APPLIC
ATION_ERROR(-

156
20501, 'Table
vide !');
Bases de données avancées
GESTION D’EXCEPTIONS IMPRÉVUES
DECLARE
i INTEGER := &saisie;
e1 EXCEPTION;
e2 EXCEPTION;
BEGIN
IF i = 1 THEN
RAISE e1;
ELSIF i = 2 THEN
RAISE e2;
ELSE
i := i / 0;
END IF;
EXCEPTION
WHEN e1 THEN RAISE_APPLICATION_ERROR(-20001, 'Exception
1'); WHEN e2 THEN RAISE_APPLICATION_ERROR(-20002,
'Exception 2'); WHEN OTHERS THEN RAISE_APPLICATION_ERROR(-

157
20999, SQLERRM);
Bases de données avancées
END;
PROCÉDURES STOCKÉES

Définition : Procédures précompilées stockées de

manière permanente dans la base de données
Création
CREATE PROCEDURE nom_proc (paramètres) A S ...
ex. CREATE PROCEDURE
HelloWorld A S BEGIN
DBMS_OUTPUT.PUT_LINE('Hello
World!'); END;
Exécutio sous SQL Developer en PL/SQL
n
ex. EXECUTE HelloWorld HelloWorld
S uppres s i ;
on DRO P PROCEDURE
ex. HelloWorld;

158
Bases de données avancées
PA Q U E TA G E S

Définition : Ensemble de types, curseurs,

variables et sous-programmes interreliés et
stockés ensemble
Un paquetage est
– Spécification subdivisé
: interface en deux parties :
(déclarations
– publiques), Corps : déclarations privées et
code.

Oracle 8
documentation
(Fig. 8-1)

159
Bases de données avancées
DÉFINITION D’UN PAQUETAGE
-- Définition de la spécification
CREATE [OR REPLACE] PACKAGE nom_paquetage AS
[-- Définition de types publics]
[-- Déclaration de curseurs publics]
[-- Déclaration de variables globales publiques (à éviter !)]
[-- Déclaration de sous-programmes publics]
END;
-- Définition du corps (optionnelle)
CREATE [OR REPLACE] PACKAGE BODY nom_paquetage AS
[-- Définition de types privés]
[-- Spécification de curseurs publics et privés]
[-- Déclaration de variables globales privées (à éviter !)]
[-- Spécification de sous-programmes publics et privés]
END;

160
Bases de données avancées
DE PAQUETAGE
CREATE OR REPLACE PACKAGE Employes AS
TYPE nuplet IS RECORD (ename [Link]%TYPE,
salary [Link]%TYPE);
CURSOR salaire_dec RETURN nuplet;
PROCEDURE embaucher (
numemp NUMBER,
nom VARCHAR,
job VARCHAR,
mgr NUMBER,
sal NUMBER,
comm NUMBER,
numdep NUMBER);
PROCEDURE licencier (emp_id NUMBER);
END;

161
Bases de données avancées
DE PAQUETAGE
CREATE OR REPLACE PACKAGE BODY Employes AS
CURSOR salaire_dec RETURN nuplet IS
SELECT empno, sal FROM emp ORDER BY sal DESC;
PROCEDURE embaucher (numemp NUMBER,
nom VARCHAR, job VARCHAR,
mgr NUMBER, sal NUMBER,
comm NUMBER, numdep NUMBER) IS
BEGIN
INSERT INTO emp VALUES (numemp, nom, job,
mgr, SYSDATE, sal, comm, numdep);
END;
PROCEDURE licencier (emp_id NUMBER) IS
BEGIN
DELETE FROM emp WHERE empno = emp_id;
END;
END;

162
Bases de données avancées
DÉCLENCHEURS

Définition : Procédure stockée associée à une

table et exécutée automatiquement lorsque
des événements liés à des actions sur la table
surviennent (mises à jour, principalement).

Les déclencheurs complètent des

contraintes d’intégrité en permettant de
créer des règles d’intégrité complexes. Ce
sont des éléments des bases de données
actives.

163
Bases de données avancées
DÉCLENCHEURS

Inse r t i on De l e t i on Update

Be f or e 1 2 3

After 4 5 6

164
Bases de données avancées
DÉCLENCHEUR

CREATE [OR REPLACE] TRIGGER nom_declencheur

BEFORE | AFTER
INSERT | DELETE | UPDATE
| [INSERT] [[OR] DELETE] [[OR] UPDATE]

ON nom_table [FOR
EACH ROW]
-- Bloc PL/SQL
codant les
actions à

165
effectuer
Bases de données avancées
VARIABLES SPÉCIFIQUES A U X DÉCLENCHEURS

:NEW.nom_attribut : Valeur d’un attribut après mise à

jour
ex. INSERT INTO client (1, 'NouveauClient');
:[Link] prend la valeur 1 dans le déclencheur.
:[Link] prend la valeur 'NouveauClient' dans le
déclencheur.

:OLD.nom_attribut : Valeur d’un attribut avant mise à

jour

166
ex. DELETE F R O M client W H E R E NumCli = 33;
Bases de données avancées
EXEMPLE DE DÉCLENCHEUR (1/2)
-- Emulation de clé primaire sur la table client
CREATE OR REPLACE TRIGGER client_pk
BEFORE INSERT OR UPDATE ON client
FOR EACH ROW
DECLARE
n INTEGER;
cle_existante EXCEPTION;
cle_nulle EXCEPTION;
BEGIN
-- La clé est-elle vide ?
IF :[Link] IS NULL THEN
RAISE cle_nulle;

167
END IF;
Bases de données avancées
EXEMPLE DE DÉCLENCHEUR (2/2)
-- La clé existe-t-elle déjà ?
SELECT COUNT(NumCli) INTO n FROM client
WHERE NumCli = :[Link];
IF n > 0 THEN
RAISE cle_existante;
END IF;
EXCEPTION
WHEN cle_existante THEN
RAISE_APPLICATION_ERROR(-20501,
'Clé primaire déjà utilisée !');
WHEN cle_nulle THEN
RAISE_APPLICATION_ERROR(-
20502,
'Une clé primaire doit

168
avoir une valeur !');
Bases de données avancées
S Q L STATIQUE VS. S Q L DYNAMIQUE

Exemples

– Procédure stockée qui met la table EMP

* SQ L statique (la requête est connue à la compilation)
à jour
– Procédure stockée qui met à jour une table dont le no m
paramètr
este un
compilation)
* SQ L dynamique (la requête complète n’est pas
connue à la
Définition du S Q L dynamique :
Construction d’une requête S QL à la volée dans un bloc
PL/SQL

169
Bases de données avancées
DYNAMIQUES
Exécution : EXECUTE IMMEDIATE requete -- requete est une
chaîne [INTO res1, res2…];

Note :
– Requêtes paramétrées : valeurs de la base de données
– (statiques).
Si l’on veut paramétrer des objets (tables, vues, attributs...) :
requête dynamique.
N B : Les requêtes qui altèrent la structure de la base de
données (CREATE, DROP, ALTER…), m ê m e statiques,
doivent être exécutées en mode dynamique.

170
Bases de données avancées
DYNAMIQUES
DECLARE
requete VARCHAR(250);
nom_table CHAR(4) := 'dept';
numdep [Link]%TYPE := 50;
n INTEGER;
BEGIN
-- Construction de requête par
concatenation
requete := 'DELETE FROM '||nom_table||' WHERE deptno = '||numdep;
EXECUTE IMMEDIATE requete;
-- Récupération d'un résultat de requête dynamique
requete := 'SELECT COUNT(*) FROM ' || nom_table;
EXECUTE IMMEDIATE requete INTO n;
END;

171
Bases de données avancées
DYNAMIQUES
DECLARE -- Exemple
TYPE CursDyn IS REF CURSOR; -- Pointeur vers un curseur
emp_cv CursDyn; -- Curseur dynamique
nom [Link]%TYPE;
salaire [Link]%TYPE := 10000;
BEGIN
OPEN emp_cv FOR -- Le curseur est forcément explicite
'SELECT ename, sal FROM emp
WHERE sal > ' || salaire;
FETCH emp_cv INTO nom, salaire;
WHILE emp_cv%FOUND LOOP
-- Instructions PL/SQL
FETCH emp_cv INTO nom, salaire;
END LOOP;
CLOSE emp_cv;

172
END;
Bases de données avancées
 FIN

173
B as es de données avancées