BASES DE DONNÉES

AVANCÉES
LGI3
2021-2022

Pr Cheikhou THIAM
cthiam@[Link]

Bases de données avancées

1
 INTRODUCTION

B ase s de données avancées 2

2
ITION
Base de données (BD) : Collection de données
cohérentes et structurées

≠
B a s e de données
Fichiers

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

Saisie Traitement Fichier

Fichier

État de
Saisie Traitement sortie

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

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

États de
sortie

5
5
Bases de données avancées
BD
Uniformisation de la saisie

Standardisation des traitements

Contrôle de la validité des données

Partage de données entre plusieurs traitements

6
6
Bases de données avancées
QU’UN S GBD ?

Système de Gestion de B ases 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, SQL Server, PostgreSQL,
MySQL, M a r i a D B …

7
7
Bases de données avancées
BD
Problém a - Indépendant d’un système
tique
de gestion de B D (SGBD)
Cahier des charges

S pécifica-
tions
Rédaction
Modèle Spécifique
conceptuel
Analyse Famille de S G B D

M odèle
log ique
Traduction S G B D particulier

Modèle
phys ique
Traduction

8
8
Bases de données avancées
COURS

Partie 1 : Modélisation conceptuelle

Partie 2 : Modélisation logique Modèle

relationnel

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

B ase s de données avancées 10

10
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 ê m e 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 Dupont ?

14
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/1993
2002 West James 03/09/1994
3333 Martin Marie 05/06/1995
4042 Durand Rachid 05/11/1995
5552 Titgoutte Justine 28/02/1996
6789 Dupont Noémie 18/09/1995
7000 Dupont Albert 23/05/1990

15
15
Bases de données avancées
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
– 1 Un et un seul
– 0..1 Zéro ou un
– 0..* ou * Zéro ou plus
– 1..* Un ou plus
– M..N D e M à N (M, N entiers)
ex. 4..10 (de 4 à 10)

19
[Link] 19
Bases de données avancées
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 Izly ».

20
20
Bases de données avancées
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, p a s une

24
B a ses c
dela snséee
d on s a.
v ancées 25
Bases de données avancées
EXEMPLE : SPÉCIFICATIONS (1/2)

Les étudiant·es sont caractérisé·es par un n u m éro unique, leur

nom , prén om , date de naissance , rue, code postal et ville .

Les étudiant·es po ss èdent une carte Izly caractérisée par un

n u m éro unique et un s olde d’argent utilisable au CROUS.

Selon qu’ils ou elles sont dispensé·es ou non d’assiduité, les

étudiant·es appartiennen à un groupe de TD caractérisé par un
code uniquet.

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

Les étudiant·es passent des épreuves et obtiennent une note

pour chacune.

Les épreuves sont caractérisées par un code unique, ainsi que la

date et le lieu auxquels elles se déroulent.

C haque épreuve relève d' une matière unique (mais une matière
donnée peut donner lieu à plu sieurs épreuves).

Les m atières sont caractérisées par un code unique et un intitulé.

26
26
Bases de données avancées
D É M A R C H E D E MODÉLISATION CONCEPTUELLE

1. Identifier les clas s es

2. Identifier les as s ociations entre les classes
3. Identifier les
attributs de chaque classe
et de chaque classe-association
4. Identifier et souligner l’identifiant de chaque classe
5. Évaluer les m ultiplicités des associations

27
27
Bases de données avancées
EXEMPLE : D I A G R A M M E DE CLASSES

B ase s de données avancées 28

28
 PARTIE 2
MODÉLISATION
LOGIQUE

B ase s de données avancées 29

29
RELATIONNEL

Modèle associé aux S G B D relationnels

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

Objectifs du modèle relationnel

– Indépendance 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 GBD 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
Utilisation interactive ou à partir d’un langage hôte

31
[Link] 31
Bases de données avancées
ATTRIBUTS Modèle
relationnel

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 D101', … }

32
[Link] 32
Bases de données avancées
TS Modèle
relationnel

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 n-uplet

de la relation EPREUVE.

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

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
relationnel

Notations :Clés primaires soulignées, clés étrangères

postfixées par le caractère # .
ex. EPREUVE (CodeEpr, DateEpr, Lieu, CodeMat# )

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
relationnel

EPREUVE MATIERE

CodeEpr DateEpr Lieu Codemat#

CodeMat Intitulé
Amphi
ECOS101 15/01/2016 ECO
Aubrac
ECO Économie
Amphi
ECOS102 16/01/2016 ECO
Aubrac
GES Gestion
GESS201 25/05/2016 Salle 201 GES

INFOS101 20/01/2016 Salle 101 INFO

INFO Informatique

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.CARTE_IZ LY (N um Carte, S oldeCR 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
relationnel

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, C od e Ma t# )

PASSER (NumEtu#, CodeEpr#, Note)

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

ETUDIANT
N um Etu N om Prénom
1110 Dupont Albertine PASSER (table « pont »)
2002 West James NumEtu# C o d e Ep r# Note

1110 INFOS101 15,5

EPR EUVE
2002 ECOS101 8,5
CodeEpr D ateEpr Lieu
2002 ECOS102 13
ECO S 1 0 1 15/01/2016 Aubrac
1110 GESS201 14
ECO S 1 0 2 16/01/2016 Aubrac
2002 GESS201 14,5
G ES S 2 0 1 25/05/2016 D 201
INFOS101 20/01/2016 D 101

42
[Link] 42
Bases de données avancées
REDONDANCE Modèle
relationnel

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
LA REDONDANCE Modèle
relationne

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
E

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…
– C’est tout !

45
[Link] 45
Bases de données avancées
 Partie 3
Interrogation
et manipulation
de bases de données
B ase s de données avancées [Link] 46

46
RELATIONNELLE ? Modèle
relationnel

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
(1/5) Modèle
relationnel

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

ou T = UNION (R, S) (notation fonctionnelle)
R et S doivent avoir m ê m e schéma.
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 a s e s de données avancées
Bases de données avancées
[Link] R S 49
(2/5) Modèle
relationnel

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

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

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

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

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
N Modèle
relationnel

NumEtu Nom CodeEprLieu

ex. 101 E1 INFO1 Aubrac
X
102 E2 ECO1 Aubrac
ECO2 D201

NumEtu Nom CodeEprLieu

101 E1 INFO1 Aubrac
102 E2 INFO1 Aubrac
101 E1 ECO1 Aubrac
= 102 E2 ECO1 Aubrac
101 E1 ECO2 D201
102 E2 ECO2 D201

52
[Link]
Bases de données avancées
(5/5) Modèle
relationnel

Division : T= R ÷S
ou T = DIVISION (R, S)
R (A 1 , A 2 , … , A n ) S (A p+ 1 , … , A n )
T (A1 , A 2 , … , A p ) contient tous les n-uplets tels que leur
concaténation à chacun des n-uplets de S donne
toujours un n-uplet de R.
Notation graphique : T

R S

53
[Link]
Bases de données avancées
SION Modèle
relationnel

ex.

NumEtu CodeEprNote
101 INNFFOO11 11
101 ECO1 15 CodeEprNote
101 ECO2 12 ÷ INFO1 11
102 ECO1 9 ECO2 12
103 INNFFOO11 11
103 ECO2 12
NumEtu
= 101
103

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

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
relationnel

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 graphique : T
C
R

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

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 graphique : T
A B
=
R S

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

Notes des étudiant·es en précisant leurs noms

(et pas seulement leurs numéros)

RESULTAT

Nom,
CodeEpr, Note

NumEtu NumEtu
=

ETUDIANT PASSER

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

Décomposition des opérations

ETUDIANT PASSER
NumEtu Nom NumEtu CodeEpr Note
101 E1 101 INFO1 10
102 E2 X 103 INFO1 15
103 E3 103 ECO1 12

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

[Link] Nom [Link] CodeEpr Note

101 E1 101 INFO 1 10
102 E2 101 INFO1 10
103 E3 101 INFO1 10
101 E1 103 INFO1 15
102 E2 103 INFO1 15
= 103 E3 103 INFO 1 15
101 E1 103 ECO1 12
102 E2 103 ECO1 12
103 E3 103 ECO1 12

60
[Link]
Bases de données avancées
REQUÊTE (4/4) Modèle
relationnel

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 GBD 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.
S Q L se base sur l’algèbre relationnelle.

Standard depuis 1986.

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-AAAA’

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é) REF EREN C ES 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),
Ville VARCHAR(255),

CONSTRAINT EtuClePri PRIMARY KEY (NumEtu) )

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)

REFERENCES Epreuve (CodeEpr),

CONSTRAINT NoteValide CHECK (Note >= 0 AND Note <= 20) )

67
[Link]
Bases de données avancées
(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 DROP COLUMN attribut, ...
ex. ALTER TABLE Etudiant DROP COLUMN tel

68
Bases de données avancées
(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 DROP 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 I N DEX 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 FROM 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 ROM Etudiant E, Passer P
W H E R E [Link] = [Link]

SELECT NumEtu, N o m F ROM 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 sous
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 masquage
des lignes et des colonnes sensibles aux usagers non
habilités

73
Bases de données avancées
VIA 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é).

Les clauses GROUP BY et HAVING sont interdites.

74
Bases de données avancées
S 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, COLUMN_NAME, … )

USER_IND_COLUMNS (INDEX_NAME, TABLE_NAME, COLUMN_NAME, … )

USER_CONSTRAINTS (TABLE_NAME, CONSTRAINT_NAME,
CONSTRAINT_TYPE, SEARCH_CONDITION, ...)
...

75
[Link]
Bases de données avancées
DES VUES SYSTÈMES LDD

Tables qui contiennent un attribut Intitulé

SELECT TABLE_NAME F R O M USER_TAB_COLUMNS
WH E RE COLUMN_NAME = ‘INTITULE’

Attributs de la table Client

SELECT COLUMN_NAME F R O M USER_TAB_COLUMNS
WH E RE TABLE_NAME = ‘CLIENT’

Contraintes des tables de l’utilisateur courant

SELECT TABLE_NAME, CONSTRAINT_NAME
F RO M ALL_CONSTRAINTS
WHERE OWNER = USER

76
[Link]
Bases de données avancées
DES 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 R E NumEtu = 3 3 3 3 3 3
ex. UPDATE Passer SET Note = Note + 1

Suppression de n-uplets
ex. DELETE F R O M Etudiant
WH E RE Ville = ‘Lyon’
ex. DELETE F R O M Epreuve

77
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, C od e Ma t# )
PASSER (NumEtu#, CodeEpr#, Note)

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

dans les transparents suivants.

78
Bases de données avancées
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 (sans valeur)

SELECT * FROM Passer
WHERE Note IS NULL

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
S LOGIQUES LMD

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

SELECT * F ROM 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 ROM 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 ROM 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) FROM 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
RE (1/3) LMD

ex. Liste des notes avec le nom des étudiant·es

SELECT Nom, CodeEpr, Note

RESULTAT
FROM Etudiant, Passer

WHERE [Link] = [Link] Nom,

CodeEpr, Note

NumEtu NumEtu
=

ETUDIANT PASSER

88
Bases de données avancées
RE (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 l’écriture

+ tri par nom (croissant) et note (décroissante).

89
Bases de données avancées
RE (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 IN (
Sous-
SELECT CodeEpr FROM Epreuve requête
WHERE DateEpr = ‘23-09-2016’ )

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 ROM Etudiant E

W H E R E [NOT] EXISTS (
SELECT * F ROM Passer P
W H E R E [Link] = [Link] )

91
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 ROM Passer

W H E R E Note > ALL [ANY] (
SELECT Note F ROM Passer
W H ER E N u m Etu = 1 0 0 0 )

92
Bases de données avancées
NT (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
GROUP BY NumEtu

93
Bases de données avancées
NT (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 résultat

de groupement (a posteriori)
≠ WHERE : évaluation de condition a priori

94
Bases de données avancées
SION 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

! Deux 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 égal

au nombre total d’épreuves.

95
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
WHERE [Link] = [Link]
AND [Link] = [Link] ) )

96
Bases de données avancées
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
HAVING COUNT(CodeEpr) =
( SELECT COUNT(*) F R O M Epreuve )

97
Bases de données avancées
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 Passer
WHERE NumEtu = 102

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

Exemple de hiérarchie Vélo

(nomenclature) :
Cadre Roues

Relation associée :
ELEMENT (No_Elt, Dési, Parent#) Pneu Rayons
1 Vélo NULL
2 Cadre 0
3 Roue1 0 6 Rayon11 2
4 Roue2 0 7 Rayon12 2
5 Pneu1 2 8 Rayon13 2
6 Pneu2 3 9 Rayon21 3

99
Bases de données avancées
(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 la Ordre de parcours de

hiérarchie la hiérarchie

100
Bases de données avancé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 IS NULL;

Résultat : 1 Velo 3 Rayon12

2 Cadre 3 Rayon13
3 Roue1 2 Roue2
4 Pneu1 3 Pneu2
3 Rayon11 3 Rayon21

101
Bases de données avancées
(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 Rayon12

2 Cadre 3 Rayon13
3 Roue1 2 Roue2
4 Pneu1
3 Rayon11

102
Bases de données avancées
(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 No_Elt
START WITH Parent IS NULL
GROUP BY LEVEL;

103
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
– FLOOR(n) : Plus grand entier ≤ n – LTRIM(ch, n) : Troncature à gauche
– MOD(m, n) : Reste de m/n – RTRIM(ch, n) : Troncature à droite
– REPLACE(ch, car) : Remplacement de caractère
– POWER(m, n) : mn
– SUBSTR(ch, pos, lg) : Extraction de chaîne
– SIGN(n) : Signe de n
– SOUNDEX(ch) : Représentation phonétique de ch
– SQRT(n) : Racine carrée de n – LPAD(ch, lg, car) : Compléter à gauche
– ROUND(n, m) : Arrondi à 10-m – RPAD(ch, lg, car) : Compléter à droite
– TRUNC(n, m) : Troncature à 10-m
– CHR(n) : Caractère ASCII n° n
– INITCAP(ch) : 1re lettre en maj.

104
Bases de données avancées
SQL (2/2) LMD

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

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

105
Bases de données avancées
FONCTIONS LMD

SELECT UID, U S E R F ROM DUAL;

SELECT GREATEST(1, 2, 3) F ROM DUAL;

SELECT Nom, Prenom,

FLOOR( MONTHS_BETWEEN(SYSDATE , DateNaiss) / 12) Age
F ROM 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 Validation
la transaction précédente
Validation
Connexion ou annulation ou annulation Déconnexion

Transaction 1 Transaction 2 Transaction 3

107
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
S LCD

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

Suppression
– ex. DROP 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 I N DEX
DRO P VIEW

Droit d'effectuer une action dans tous les schémas

de la base de données
– ex. CREATE ANY TABLE
ALTER ANY INDEX
DRO P ANY VIEW

110
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
S LCD

Rôles prédéfinis
– CONNECT : droit de création de tables, vues, synonymes, etc.
– RESOURC 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
DE 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

– ex. GRANT CREATE ANY TABLE TO toto
– ex. GRANT CONNECT, RESOURCE TO toto
– ex. GRANT role1 TO toto

113
Bases de données avancées
DE PRIVILÈGES LCD

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

Privilèges sur des objets

– ex. REVOKE SELECT O N ma_table FROM toto
– ex. REVOKE SELECT O N ma_table F ROM PUBLIC
– ex. REVOKE SELECT O N ma_table F ROM role1

Privilèges globaux et rôles

– ex. REVOKE CREATE ANY TABLE F ROM toto
– ex. REVOKE CONNECT, RESOURCE FROM toto
– ex. REVOKE role1 F ROM toto

114
Bases de données avancées
EL SQL
Pour approfondir SQL en ligne…

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

116
B ase s de données avancées
UN PROGRAMME
SQL encapsulé : Requêtes SQL incorporées dans le code source C
(PL/SQL, T-SQL, PL/pgSQL, Pro*C…)
U
API : Requêtes SQL via des fonctions du langage R
(Java Persistence API, PHP Data Objects…)
S
Interfaces de niveau appel : intergiciel entre le langage et le S G B D
(ODBC, JDBC, A D O … )
E
U
Procédures stockées : Fonctions SQL stockées dans la base de
données et exécutées par le S G B D R
(écrites en PL/SQL, T-SQL, PL/pgSQL) S

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 comme une extension de SQ L

Déclaration de variables et de constantes

Types abstraits (collections, enregistrements, objets)

Modularité (sous-programmes, paquetages)

Gestion des erreurs (Gestion des erreurs)

Interaction étroite avec Oracle/SQL (types identiques)

118
Bases de données avancées
LANGAGE PL/SQL (2/2)
S Q 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)

Sécurité (procédures stockées, déclencheurs)

119
Bases de données avancées
RE D’ORACLE
Génie
Réseau
log iciel
S QL
S Q L Developer

S erveur
Oracle

PL/ S QL

Logiciels
Administration
tiers

120
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
BA
Procédure stockée
– Compilée a priori
– Stockée dans la base de données D éc
BD
D éclencheur PS
– Procédure stockée associée à une table
– 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 DECL AR E 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]%TYPE; au 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 OM emp W H E R E empno = 5000;

127
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 AN D OR 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]
END IF;

129
Bases de données avancées
(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
S
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 PL/SQL
EXIT W H E N condition;
E N D LOOP;

131
Bases de données avancées
E ÉCRAN
DBMS_OUTPUT.PUT('chaîne'); /* Pas de retour à la ligne */
DBMS_OUTPUT.PUT_LINE('chaîne'); /* Retour à la ligne */
DBMS_OUTPUT.PUT('Hello world !');
DBMS_OUTPUT.PUT_LINE('nom = ' || nom);
DBMS_OUTPUT.PUT_LINE('n = ' || TO_CHAR(n));
DBMS_OUTPUT.PUT_LINE('n = ' || n);

NB : Pour que l’affichage fonctionne, il faut mettre la variable d’environnement

SERVEROUTPUT à ON.
SET SERVEROUTPUT ON dans S QL Developer
En cas de dépassement, la taille du tampon d’affichage doit être augmentée.
ex. DBMS_OUTPUT.ENABLE(10000);

132
Bases de données avancées
BLOC 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
S
Définition : Ensemble ordonné d’éléments de m ê m e type. Chaque
élément est indexé par s a position dans la collection.

De ux types de collections
– Tableau (VARRAY) : 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
DE 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
DE COLLECTION
Collection entière
ex. D EC LAR E TYPE T1 IS TABLE OF INT;
TYPE T2 IS TABLE OF INT;
et11 T1 : = T1(1, 2, 3, 4);
et12 T1 : = T1(5, 6);
et2 T2 : = T2();
BEGIN 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 listes).

EXTEND(n) augmente la taille de la collection de n.

EXTEND(1) ⇔ EXTEN D

137
Bases de données avancées
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
REMENTS
Définition : Ensemble de données liées stockées dans
des champs.

1. Déclarer un type enregistrement

ex. TYPE Etudiant IS REC ORD(
numetu INTEGER,
nom VARCHAR(50),
age INTEGER );

2. Déclarer un enregistrement
ex. un_etudiant Edutiant;

140
Bases de données avancées
NT
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
F ROM student
W H E R E student_number = 12212478;

141
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
TIONS

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 avant

d’être appelé.
* définition dans la section D E CL A R E d’un bloc PL/SQL

Définition et mode de passage des paramètres

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

144
Bases de données avancées
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
DEC L A RE
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 RO 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.

% I S O P E N est égal à TRUE si le curseur est ouvert.

151
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;
CLOSE c;
END;

152
Bases de données avancées
TIONS

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 age d’erreur associé

153
Bases de données avancées
SYSTÈMES
Libellé erreur Code erreur SQLCODE

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
S
Déclaration (section DECLARE)
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)

W H E N nom_exception THEN -- Instruction(s) PL/SQL ;

155
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_APPLICATION_ERROR(-20501, 'Table vide !');
END; -- Code d’erreur compris entre –20999 et -20001

156
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(-20999, SQLERRM);

157
END;
Bases de données avancées
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 PROCED URE nom_proc (paramètres) A S ...
ex. CREATE PROCED UR E HelloWorld A S
BEGIN
DBMS_OUTPUT.PUT_LINE('Hello World!');
END;
Exécution sous S QL Developer en PL/SQL
ex. EXECUTE HelloWorld HelloWorld;
S uppres s ion
ex. D ROP PROCED UR E HelloWorld;

158
Bases de données avancées
PAQ 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 subdivisé en deux parties :
– Spécification : interface (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
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
DE DÉCLENCHEURS

I nserti on D eleti on Update

Before 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 à effectuer

165
Bases de données avancées
VARIABLES SPÉCIFIQUES AU 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

ex. DELETE F ROM client W H E R E NumCli = 33;
:[Link] prend la valeur 3 3 dans le déclencheur.

166
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 avoir une valeur !');

168
END;
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 à jour
* SQL statique (la requête est connue à la compilation)
– Procédure stockée qui met à jour une table dont le n om est un
paramètre
* SQL dynamique (la requête complète n’est pas connue à la
compilation)

Définition du S Q L dynamique :
Construction d’une requête SQ L à 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 ase s de données avancées