Fichier PDF

Partage, hébergement, conversion et archivage facile de documents au format PDF

Partager un fichier Mes fichiers Convertir un fichier Boite à outils PDF Recherche PDF Aide Contact



oracle .pdf



Nom original: oracle.pdf

Ce document au format PDF 1.4 a été généré par TeX / pdfTeX-1.40.5, et a été envoyé sur fichier-pdf.fr le 24/09/2011 à 15:58, depuis l'adresse IP 41.137.x.x. La présente page de téléchargement du fichier a été vue 3297 fois.
Taille du document: 466 Ko (133 pages).
Confidentialité: fichier public




Télécharger le fichier (PDF)









Aperçu du document


Cours Oracle
Alexandre Mesl´e
13 octobre 2009

Table des mati`
eres
1 Notes de cours
1.1 Introduction . . . . . . . . . . . . . . . . . . . . . . . .
1.1.1 Qu’est-ce qu’un SGBD ? . . . . . . . . . . . . .
1.1.2 Organisation relationnelle des donn´ees . . . . .
1.1.3 Survol de SQL . . . . . . . . . . . . . . . . . .
1.1.4 SQL+ et iSQL . . . . . . . . . . . . . . . . . .
1.2 Contraintes d´eclaratives . . . . . . . . . . . . . . . . .
1.2.1 Valeurs par d´efaut . . . . . . . . . . . . . . . .
1.2.2 Champs non renseign´es . . . . . . . . . . . . .
1.2.3 Cl´e primaire . . . . . . . . . . . . . . . . . . .
1.2.4 Cl´e ´etrang`ere . . . . . . . . . . . . . . . . . . .
1.2.5 Syntaxe alternative . . . . . . . . . . . . . . . .
1.3 Introduction aux requˆetes . . . . . . . . . . . . . . . .
1.3.1 Compl´ements sur SELECT . . . . . . . . . . . .
1.3.2 Instruction WHERE . . . . . . . . . . . . . . . . .
1.3.3 Conditions . . . . . . . . . . . . . . . . . . . .
1.3.4 Suppression . . . . . . . . . . . . . . . . . . . .
1.3.5 Mise `
a jour . . . . . . . . . . . . . . . . . . . .
1.4 Jointures . . . . . . . . . . . . . . . . . . . . . . . . .
1.4.1 Principe . . . . . . . . . . . . . . . . . . . . . .
1.4.2 Produit cart´esien . . . . . . . . . . . . . . . . .
1.4.3 Jointure . . . . . . . . . . . . . . . . . . . . . .
1.4.4 Jointures refl´exives . . . . . . . . . . . . . . . .
1.5 Agr´egation de donn´ees . . . . . . . . . . . . . . . . . .
1.5.1 Fonctions d’agr´egation . . . . . . . . . . . . . .
1.5.2 Groupage . . . . . . . . . . . . . . . . . . . . .
1.6 Vues . . . . . . . . . . . . . . . . . . . . . . . . . . . .
1.6.1 D´efinition . . . . . . . . . . . . . . . . . . . . .
1.6.2 Syntaxe . . . . . . . . . . . . . . . . . . . . . .
1.6.3 Application . . . . . . . . . . . . . . . . . . . .
1.6.4 Suppression . . . . . . . . . . . . . . . . . . . .
1.7 Requˆetes imbriqu´ees . . . . . . . . . . . . . . . . . . .
1.7.1 Sous requˆetes renvoyant une valeur scalaire . .
1.7.2 Sous requˆetes renvoyant une colonne . . . . . .
1.7.3 Sous requˆetes non correl´ees renvoyant une table
1.7.4 Sous requˆetes correl´ees . . . . . . . . . . . . . .
1.8 Compl´ements sur les types . . . . . . . . . . . . . . . .
1.8.1 Types num´eriques . . . . . . . . . . . . . . . .
1.8.2 Types chaine de caract`eres . . . . . . . . . . .
1.8.3 Types date . . . . . . . . . . . . . . . . . . . .
1.8.4 La fonction inclassable . . . . . . . . . . . . . .
1.8.5 Contraintes CHECK . . . . . . . . . . . . . . . .
1.9 Introduction au PL/SQL . . . . . . . . . . . . . . . . .
1.9.1 PL/SQL . . . . . . . . . . . . . . . . . . . . . .
1.9.2 Blocs . . . . . . . . . . . . . . . . . . . . . . .
1.9.3 Affichage . . . . . . . . . . . . . . . . . . . . .
1.9.4 Variables . . . . . . . . . . . . . . . . . . . . .

1

.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.

.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.

.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.

.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.

.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.

.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.

.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.

.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.

.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.

.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.

.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.

.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.

.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.

.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.

.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.

.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.

.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.

.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.

.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.

.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.

.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.

.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.

.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.

.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.

.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.

.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.

.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.

4
4
4
4
5
6
8
8
8
8
8
8
10
10
10
10
11
12
13
13
14
14
15
17
17
19
21
21
21
21
22
23
23
24
25
26
28
28
29
29
30
30
31
31
31
31
31

1.10

1.11

1.12

1.13

1.14

1.15

1.16

1.17

1.9.5 Traitements conditionnels . . . . . . . . . . . .
1.9.6 Traitements r´ep´etitifs . . . . . . . . . . . . . .
Tableaux et structures . . . . . . . . . . . . . . . . . .
1.10.1 Tableaux . . . . . . . . . . . . . . . . . . . . .
1.10.2 Structures . . . . . . . . . . . . . . . . . . . . .
Utilisation du PL/SQL . . . . . . . . . . . . . . . . . .
1.11.1 Affectation . . . . . . . . . . . . . . . . . . . .
1.11.2 Tables et structures . . . . . . . . . . . . . . .
1.11.3 Transactions . . . . . . . . . . . . . . . . . . .
Exceptions . . . . . . . . . . . . . . . . . . . . . . . .
1.12.1 Rattraper une exception . . . . . . . . . . . . .
1.12.2 Exceptions pr´ed´efinies . . . . . . . . . . . . . .
1.12.3 Codes d’erreur . . . . . . . . . . . . . . . . . .
1.12.4 D´eclarer et lancer ses propres exceptions . . . .
Sous-programmes . . . . . . . . . . . . . . . . . . . . .
1.13.1 Proc´edures . . . . . . . . . . . . . . . . . . . .
1.13.2 Fonctions . . . . . . . . . . . . . . . . . . . . .
Curseurs . . . . . . . . . . . . . . . . . . . . . . . . . .
1.14.1 Introduction . . . . . . . . . . . . . . . . . . .
1.14.2 Les curseurs . . . . . . . . . . . . . . . . . . . .
Curseurs parametr´es . . . . . . . . . . . . . . . . . . .
1.15.1 Introduction . . . . . . . . . . . . . . . . . . .
1.15.2 D´efinition . . . . . . . . . . . . . . . . . . . . .
1.15.3 D´eclaration . . . . . . . . . . . . . . . . . . . .
1.15.4 Ouverture . . . . . . . . . . . . . . . . . . . . .
1.15.5 Lecture d’une ligne, fermeture . . . . . . . . . .
1.15.6 Boucle pour . . . . . . . . . . . . . . . . . . . .
1.15.7 Exemple r´ecapitulatif . . . . . . . . . . . . . .
Triggers . . . . . . . . . . . . . . . . . . . . . . . . . .
1.16.1 Principe . . . . . . . . . . . . . . . . . . . . . .
1.16.2 Classification . . . . . . . . . . . . . . . . . . .
1.16.3 Cr´eation . . . . . . . . . . . . . . . . . . . . . .
1.16.4 Acc`es aux lignes en cours de modification . . .
1.16.5 Contourner le probl`eme des tables en mutation
Packages . . . . . . . . . . . . . . . . . . . . . . . . . .
1.17.1 Principe . . . . . . . . . . . . . . . . . . . . . .
1.17.2 Sp´ecification . . . . . . . . . . . . . . . . . . .
1.17.3 Corps . . . . . . . . . . . . . . . . . . . . . . .

2 Exercices
2.1 Contraintes d´eclaratives .
2.2 Introduction aux requˆetes
2.3 Jointures . . . . . . . . .
2.4 Agr´egation de donn´ees . .
2.5 Vues . . . . . . . . . . . .
2.6 Requˆetes imbriqu´ees . . .
2.7 Compl´ements sur les types
2.8 R´evisions . . . . . . . . .
2.9 Introduction au PL/SQL .
2.10 Tableaux et Structures . .
2.11 Utilisation PL/SQL . . .
2.12 Exceptions . . . . . . . .
2.13 Sous-programmes . . . . .
2.14 Curseurs . . . . . . . . . .
2.15 Curseurs parametr´es . . .
2.16 Triggers . . . . . . . . . .
2.17 Packages . . . . . . . . . .
2.18 R´evisions . . . . . . . . .

.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.

.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.

.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.

.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.

.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.

.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.

.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.

.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.

.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.

.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.

.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.

.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.

.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.

2

.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.

.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.

.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.

.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.

.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.

.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.

.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.

.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.

.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.

.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.

.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.

.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.

.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.

.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.

.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.

.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.

.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.

.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.

.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.

.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.

.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.

.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.

.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.

.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.

.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.

.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.

.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.

.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.

.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.

.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.

31
32
33
33
34
36
36
36
37
38
38
39
39
39
41
41
41
43
43
43
45
45
45
45
45
45
46
46
47
47
47
47
48
50
53
53
53
53

.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.

.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.

.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.

.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.

.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.

.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.

.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.

.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.

.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.

.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.

.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.

.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.

.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.

.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.

.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.

.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.

.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.

.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.

.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.

.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.

.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.

.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.

.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.

.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.

.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.

.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.

.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.

55
55
56
58
60
61
62
63
64
65
66
68
69
70
71
72
73
74
75

3 Corrig´
es
3.1 Contraintes d´eclaratives . . . . . . . .
3.2 Introduction aux requˆetes . . . . . . .
3.3 Jointures . . . . . . . . . . . . . . . .
3.4 Agr´egation de donn´ees . . . . . . . . .
3.5 Vues . . . . . . . . . . . . . . . . . . .
3.6 Requˆetes imbriqu´ees . . . . . . . . . .
3.7 Compl´ements sur les types . . . . . . .
3.8 R´evisions . . . . . . . . . . . . . . . .
3.9 Examen Type . . . . . . . . . . . . . .
3.10 Introduction au PL/SQL . . . . . . . .
3.11 Tableaux et Structures . . . . . . . . .
3.12 Application du PL/SQL et Exceptions
3.13 Sous-programmes . . . . . . . . . . . .
3.14 Curseurs . . . . . . . . . . . . . . . . .
3.15 Curseurs param´etr´es . . . . . . . . . .
3.16 Triggers . . . . . . . . . . . . . . . . .
3.17 Packages . . . . . . . . . . . . . . . . .
3.18 R´evisions . . . . . . . . . . . . . . . .

.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.

.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.

.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.

.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.

.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.

.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.

.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.

.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.

.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.

.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.

.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.

.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.

.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.

.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.

.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.

.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.

.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.

.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.

.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.

.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.

.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.

.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.

.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.

.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.

.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.

.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.

.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.

.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.

.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.

.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.

.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.

.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.

.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.

.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.

.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.

.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.

76
76
78
80
82
83
84
86
87
88
92
93
96
100
103
106
107
116
117

A Scripts de cr´
eation de bases
A.1 Livraisons Sans contraintes . . . .
A.2 Modules et prerequis . . . . . . . .
A.3 G´eom´etrie . . . . . . . . . . . . . .
A.4 Livraisons . . . . . . . . . . . . . .
A.5 Arbre g´en´ealogique . . . . . . . . .
A.6 Comptes bancaires . . . . . . . . .
A.7 Comptes bancaires avec exceptions
A.8 Secr´etariat p´edagogique . . . . . .
A.9 Mariages . . . . . . . . . . . . . . .

.
.
.
.
.
.
.
.
.

.
.
.
.
.
.
.
.
.

.
.
.
.
.
.
.
.
.

.
.
.
.
.
.
.
.
.

.
.
.
.
.
.
.
.
.

.
.
.
.
.
.
.
.
.

.
.
.
.
.
.
.
.
.

.
.
.
.
.
.
.
.
.

.
.
.
.
.
.
.
.
.

.
.
.
.
.
.
.
.
.

.
.
.
.
.
.
.
.
.

.
.
.
.
.
.
.
.
.

.
.
.
.
.
.
.
.
.

.
.
.
.
.
.
.
.
.

.
.
.
.
.
.
.
.
.

.
.
.
.
.
.
.
.
.

.
.
.
.
.
.
.
.
.

.
.
.
.
.
.
.
.
.

.
.
.
.
.
.
.
.
.

.
.
.
.
.
.
.
.
.

.
.
.
.
.
.
.
.
.

.
.
.
.
.
.
.
.
.

.
.
.
.
.
.
.
.
.

.
.
.
.
.
.
.
.
.

.
.
.
.
.
.
.
.
.

.
.
.
.
.
.
.
.
.

.
.
.
.
.
.
.
.
.

.
.
.
.
.
.
.
.
.

.
.
.
.
.
.
.
.
.

.
.
.
.
.
.
.
.
.

.
.
.
.
.
.
.
.
.

.
.
.
.
.
.
.
.
.

.
.
.
.
.
.
.
.
.

.
.
.
.
.
.
.
.
.

.
.
.
.
.
.
.
.
.

.
.
.
.
.
.
.
.
.

121
121
122
123
124
125
126
128
130
132

.
.
.
.
.
.
.
.
.

.
.
.
.
.
.
.
.
.

3

Chapitre 1

Notes de cours
1.1
1.1.1

Introduction
Qu’est-ce qu’un SGBD ?

– D´efinition : logiciel qui stocke des donn´ees de fa¸con organis´ee et coh´erente.
– Access : version ´edulcor´ee. Mais mono-utilisateur, et faible capacit´e.
– Les donn´ees sont stock´ees dans des fichiers g´er´es par le serveur de base de donn´ees. Cette op´eration est opaque.
On transmet depuis un client des instructions `a la base par l’interm´ediaire du langage SQL.
Avantages :
– permet de maintenir de fa¸con fiable l’int´egrit´e des donn´ees
– op´erations de bas niveau opaques
– rapide
– multi-utilisateurs
– moins de trafic sur le r´eseau
– s´ecurit´e
Inconv´enient :
– Un peu plus long que bien programm´e en C, et encore...
Plusieurs fa¸cons d’organiser les donn´ees :
– hi´erarchique
– relationnel
– d´eductif
– objet
– etc.
Les gros SGBD-R :
– DB2 (IBM)
– Oracle
– Microsoft SQL Server
– mySQL
SQL
Structured Query Language. SQL est le langage standard de la base de donn´ees. D’un SGBD `a l’autre, le SQL
change tr`es peu. Ce cours est surtout un cours de SQL.

1.1.2

Organisation relationnelle des donn´
ees

– Les donn´es sont stock´ees dans des tables.
– Table : tableau `
a deux entr´ees.
Le MPD nous donne, pour chaque table, les colonnes (i.e. champs) que doit comporter la table.
nomtable ( c o l o n n e 1 , c o l o n n e 2 , . . . , c o l o n n e n )
Par exemple :

4

CLIENT( numero , prenom , nom , e m a i l )
nous donnera la table
num´ero
20505372
...

nom
Alexandre
...

pr´enom
Mesl´e
...

email
alexandre.mesle@gmail.com
...

Supposons que l’on veuille g´erer des commandes ´emises par des clients. Nous souhaitons stocker les donn´ees suivantes :
– nom, pr´enom et adresse compl`ete des clients
– produits propos´es et coˆ
uts unitaires
– pour chaque commande, le d´etail et le montant.
Apr`es maintes p´erip´eties, nous en arrivons au MPD suivant :
CLIENT( n u m e r o c l i e n t , prenom , nom , a d r e s s e 1 , a d r e s s e 2 , #CP)
CODE POSTAL(CP, v i l l e )
prix unitaire )
PRODUIT( numero produit , d e s c r i p t i o n ,
COMMANDE(# n u m e r o c l i e n t , numero commande )
LIGNECOMMANDE(# n u m e r o c l i e n t , #numero commande , #numero produit , q u a n t i t e )
Nous pouvons repr´esenter la base, avec quelques donn´ees ins´er´ees, par des tableaux
numero client
1
2
3
4
CP
75013
75016
95200

prenom
Alexandre
Am´ed´ee
Louis-Hubert

nom
Mesl´e
Morflegroin
M´edor
Martin

ville
Paris
Paris
Sarcelles

numero produit
1
2
3
4
5
6
7
8
9
10

decription
Boite de cornichons
Goupillon
Cotons-tige
Ajax WC
Place concert des Stones
Roue de secours
Compas
Armoire Gl¨
udehnblourf
Pat´e di´et´etique
Croquettes laxatives

#numero client
1
1
2
3

numero commande
1
2
1
1

#numero client
1
1
1
2
3

#numero commande
1
2
2
1
1

1.1.3

adresse1
Cit´e les framboises
29, rue de Choisy
dans sa niche
112, rue de la Pompe

prix unitaire
2.5
6.5
1.2
1.2
145
75
4
146
12
8

#numero produit
5
4
7
1
9

Survol de SQL

Le SQL de base se d´ecline en quatre parties :
5

quantite
2
29
1
4
238

adresse2
28, rue du chemin vert

#CP
75013
95200
75013
75016

– DDL : Data definition language
– DML : Data manipulation language
– DQL : Data query language
– DCL : Data control language
A cela s’ajoute le PL/SQL. Celui-ci permet de g´erer presque toutes les contraintes et de maintenir la coh´erence de
la base de donn´ees. Mais c’est beaucoup plus compliqu´e...
Cr´
eer des tables
Les types pour commencer
– num´eriques : number
– chaˆınes de caract`eres : varchar2(taille)
syntaxe :
CREATE TABLE <nomdelatable >
(< d e s c r i p t i o n c o l o n n e 1 >, . . . , <d e s c r i p t i o n c o l o n n e n >)
Pour chaque colonne :
<nomcolonne> <type> [< o p t i o n s e v e n t u e l l e s >]
exemple :
create table c l i e n t
(
numcli number,
nom varchar2 ( 2 5 6 ) ,
prenom varchar2 ( 2 5 6 )
)
Afficher le contenu d’une table
syntaxe :
SELECT ∗ FROM <nomdelatable >
exemple :
s e l e c t ∗ from c l i e n t
Ajouter une ligne dans une table
syntaxe :
INSERT INTO <nomdelatable > (<nomcolonne 1 >, . . . , <nomcolonne n >)
VALUES (< v a l e u r c o l o n n e 1 >, . . . , <v a l e u r c o l o n n e n >)
exemple :
INSERT INTO CLIENT ( numcli , nom , prenom ) VALUES ( 1 , ’ Mesle ’ , ’ Alexandre ’ )

1.1.4

SQL+ et iSQL

Connection
login : scott
password : tiger
Liste des tables
SELECT t a b l e n a m e FROM u s e r t a b l e s ;
Description des tables
Syntaxe :

6

DESC <nomtable >;
Exemple :
DESC c l i e n t ;
Attention
Les commandes de SQL+ se terminent par un point-virgule !

7

1.2
1.2.1

Contraintes d´
eclaratives
Valeurs par d´
efaut

create table c l i e n t
(
numcli number,
nom varchar2 ( 2 5 6 ) default ’ Moi ’ ,
prenom varchar2 ( 2 5 6 )
)
fait de ’Moi’ le nom par d´efaut.

1.2.2

Champs non renseign´
es

create table c l i e n t
(
numcli number,
nom varchar2 ( 2 5 6 ) NOT NULL,
prenom varchar2 ( 2 5 6 ) NOT NULL
)
force la saisie des champs nom et pr´enom.

1.2.3

Cl´
e primaire

Une cl´e primaire est :
– toujours renseign´ee
– unique
On peut pr´eciser PRIMARY KEY dans la cr´eation de table
create table c l i e n t
(
numcli number PRIMARY KEY,
nom varchar2 ( 2 5 6 ) ,
prenom varchar2 ( 2 5 6 )
)
La colonne numcli est cl´e primaire, toute insertion ne respectant pas la contraine de cl´e primaire sera refus´ee par
Oracle.

1.2.4

Cl´

etrang`
ere

Une cl´e ´etrang`ere r´ef´erence une ligne d’une table quelconque :
Syntaxe :
REFERENCES <nomtable> (<nomcolonne >)
create table c l i e n t
(
numcli number PRIMARY KEY,
nom varchar2 ( 2 5 6 ) ,
prenom varchar2 ( 2 5 6 ) ,
numdept number REFERENCES DEPT ( nd )
)
Une ligne ne pourra ˆetre ins´er´ee dans la table client que s’il existe dans la table DEPT une ligne dont la valeur nd
est la mˆeme que la valeur numdept en cours d’insertion.
On remarque qu’il devient impossible d’´ecraser la table DEPT si elle est r´ef´erenc´ee par une cl´e ´etrang`ere.

1.2.5

Syntaxe alternative

ALTER TABLE <nomtable>
ADD [CONSTRAINT <nomcontrainte >] <d e s c r i p t i o n c o n t r a i n t e >

8

descriptioncontrainte d’une cl´e primaire :
PRIMARY KEY(< c o l o n n e 1 >, . . . , <c o l o n n e n >)
descriptioncontrainte d’une cl´e ´etrang`ere :
FOREIGN KEY(< c o l o n n e 1 >, . . . , <c o l o n n e n >)
REFERENCES <t a b l e r e f e r e n c e e > (< c o l o n n e 1 >, . . . , <c o l o n n e n >)
Il est aussi possible de placer une descriptioncontrainte dans le CREATE TABLE. Par exemple,
create table c l i e n t
(
numcli number,
nom varchar2 ( 2 5 6 ) ,
prenom varchar2 ( 2 5 6 ) ,
numdept number,
PRIMARY KEY (number) ,
FOREIGN KEY ( numdept ) REFERENCES DEPT ( nd )
)
On remarque qu’il est possible de nommer une contrainte. C’est utile si on souhaite la supprimer :
ALTER TABLE <nomtable> DROP CONSTRAINT <nomcontrainte >
Pour lister les contraintes :
SELECT ∗ FROM USER CONSTRAINTS

9

1.3

Introduction aux requˆ
etes

1.3.1

Compl´
ements sur SELECT

Il est possible d’utiliser SELECT pour n’afficher que certaines colonnes d’une table. Syntaxe :
SELECT <c o l o n n e 1 >, <c o l o n n e 2 >, . . . , <c o l o n n e n >
FROM <table>
Cette instruction s’appelle une requˆete, elle affichera pour chaque ligne de la table les valeurs des colonnes colonne1
a colonnen . Il est possible de supprimer les lignes en double `a l’aide du mot-cl´e DISTINCT. Par exemple :
`
SELECT DISTINCT <c o l o n n e 1 >, <c o l o n n e 2 >, . . . , <c o l o n n e n >
FROM <table>
Pour trier les donn´ees, on utilise ORDER BY. Exemple :
SELECT <c o l o n n e 1 >, <c o l o n n e 2 >, . . . , <c o l o n n e n >
FROM <table>
ORDER BY <c o l o n n e 1 b i s >, <c o l o n n e 2 b i s >, . . . , <c o l o n n e n b i s >
Cette instruction trie les donn´ees par colonne1bis croissants. En cas d’´egalit´e, le tri est fait par colonne2bis croissants,
etc. Pour trier par ordre d´ecroissant, on ajoute DESC apr`es le nom de la colonne choisie comme crit`ere d´ecroissant. Par
exemple :
SELECT <c o l o n n e 1 >, <c o l o n n e 2 >, . . . , <c o l o n n e n >
FROM <table>
ORDER BY <c o l o n n e 1 b i s > DESC, <c o l o n n e 2 b i s >, . . . , <c o l o n n e n b i s >

1.3.2

Instruction WHERE

Cette instruction permet de ne s´electionner que certaines lignes de la table. Par exemple la requˆete
SELECT n o m c l i e n t , p r e n o m c l i e n t
FROM c l i e n t
WHERE n u m e r o c l i e n t = 1
va afficher les nom et pr´enom du client dont le num´ero est 1. La syntaxe g´en´erale est
SELECT <c o l o n n e 1 >, <c o l o n n e 2 >, . . . , <c o l o n n e n >
FROM <table>
WHERE <c o n d i t i o n >
condition sera ´evalu´ee pour chaque ligne de la table, et seules celles qui v´efieront cette condition feront partie du
r´esultat de la requˆete.

1.3.3

Conditions

Comparaison
Les conditions peuvent ˆetre des relations d’´egalit´e (=), de diff´erence (<>), d’in´egalit´e (<, >, >= ou <=) sur des
colonnes :
numero client = 2
nom client = ’ Chirac ’
p r e n o m c l i e n t <> ’ Hubert ’
s a l a r y < 230
t a x e s >= 23000

egation
La n´egation d’une condition s’obtient `
a l’aide de NOT. Par exemple, il est possible de r´e-ecrire les conditions ci-avant :
NOT
NOT
NOT
NOT
NOT

( n u m e r o c l i e n t <> 2 )
( n o m c l i e n t <> ’ C h i r a c ’ )
( p r e n o m c l i e n t = ’ Hubert ’ )
( s a l a r y >= 2 3 0 )
( taxes < 23000)

10

Connecteurs logiques
De mˆeme, vous avez `
a votre disposition tous les connecteurs logiques binaires : AND, OR. Ainsi, les deux conditions
suivantes sont les mˆemes :
NOT( ( nom = ’ Bush ’ ) AND ( prenom <> ’ Medor ’ ) )
(nom <> ’ Bush ’ ) OR ( prenom = ’ Medor ’ )
NULLit´
e
Un champ non renseign´e a la valeur NULL, dans une comparaison, NULL n’est jamais ´egal `a quelque valeur qu’il
soit ! La condition suivante est toujours fausse :
NULL = NULL;
La requˆete suivante ne renvoie aucune ligne :
SELECT ∗ FROM EMP WHERE COMM=NULL;
Pour tester la nullit´e d’un champ, on utilise IS NULL, par exemple :
SELECT ∗ FROM EMP WHERE COMM IS NULL;
La non-nullit´e se teste de deux fa¸cons :
WHERE NOT (COMM IS NULL) ;
WHERE COMM IS NOT NULL
Encadrement
Une valeur num´erique peut ˆetre encadr´ee `
a l’aide de l’op´erateur BETWEEN, par exemple les deux conditions suivantes
sont ´equivalentes :
SALAIRE BETWEEN 1000 AND 5000
(SALAIRE >= 1 0 0 0 ) AND (SALAIRE <= 5 0 0 0 )
Inclusion
L’op´erateur IN permet de tester l’appartenance `a une liste de valeurs. Les deux propositions suivantes sont
´equivalentes
NAME IN ( ’ Mesle ’ , ’ Bush ’ , ’ Medor ’ )
(NAME = ’ Mesle ’ ) OR (NAME = ’ Bush ’ ) OR (NAME = ’ Medor ’ )
LIKE
LIKE sert `
a comparer le contenu d’une variable `a un litt´eral g´en´erique. Par exemple, la condition
NAME LIKE ’M%’
sera v´erifi´ee si NAME commence par un ’M’. Ca fonctionne aussi sur les valeurs de type num´erique, la condition
SALARY LIKE ’ %000000000 ’
sera v´erifi´ee si SALARY se termine par 000000000. Le caract`ere % peut remplacer dans le litt´eral n’importe que suite,
vide ou non, de caract`eres ; il a le mˆeme rˆ
ole que * en DOS et en SHELL. Le caract`ere remplace un et un seul
caract`ere dans le litt´eral. Par exemple, la condition
NAME LIKE ’ B s%’
ne sera v´erifi´ee que si NAME commence par un ’B’ et contient un ’s’ en troisi`eme position.

1.3.4

Suppression

L’expression
DELETE FROM <NOMTABLE> WHERE <CONDITION>
efface de la table NOMTABLE toutes les lignes v´erifiant condition. Attention ! La commande
DELETE FROM <NOMTABLE>
efface toutes les lignes de la table NOMTABLE !
11

1.3.5

Mise `
a jour

L’expression
UPDATE <NOMTABLE> SET
<c o l o n n e 1 > = <v a l e u r 1 >,
<c o l o n n e 2 > = <v a l e u r 2 >,
... ,
<c o l o n n e n > = <v a l e u r n >
WHERE <CONDITION>
modifie les lignes de la table NOMTABLE v´erifiant condition. Elle affecte au champ colonnei la valeur valeuri . Par
exemple,
UPDATE CLIENT SET NAME = ’ Medor ’ WHERE LUNCH = ’ Bones ’
affecte la valeur ’M´
edor’ aux champs noms de toutes les lignes dont la valeur LUNCH est ´egale `a ’Bones’. Il est possible,
dans une modification, d’utiliser les valeurs des autres champs de la ligne, voire mˆeme l’ancienne valeur de ce champ.
Par exemple,
UPDATE CLIENT SET SALARY = SALARY + 5000
augmente tous les salaires de 5000 (choisissez l’unit´e !).

12

1.4

Jointures

1.4.1

Principe

Etant donn´e le code ci-dessous,
CREATE TABLE MODULE
(numMod number primary key ,
nomMod varchar2 ( 3 0 )
);
CREATE TABLE PREREQUIS
(
numMod number r e f e r e n c e s MODULE(numMod) ,
numModPrereq number r e f e r e n c e s MODULE(numMod) ,
noteMin number( 2 ) DEFAULT 10 NOT NULL ,
PRIMARY KEY(numMod, numModPrereq )
);
INSERT
INSERT
INSERT
INSERT
INSERT
INSERT
INSERT
INSERT

INTO
INTO
INTO
INTO
INTO
INTO
INTO
INTO

MODULE
MODULE
MODULE
MODULE
MODULE
MODULE
MODULE
MODULE

VALUES
VALUES
VALUES
VALUES
VALUES
VALUES
VALUES
VALUES

INSERT
INSERT
INSERT
INSERT
INSERT
INSERT

INTO
INTO
INTO
INTO
INTO
INTO

PREREQUIS
PREREQUIS
PREREQUIS
PREREQUIS
PREREQUIS
PREREQUIS

(1 ,
(2 ,
(3 ,
(4 ,
(5 ,
(6 ,
(7 ,
(8 ,

’ ORacle ’ ) ;
’C++’ ) ;
’C ’ ) ;
’ Algo ’ ) ;
’ Merise ’ ) ;
’PL/SQL O r a c l e ’ ) ;
’mySQL ’ ) ;
’ Algo avancee ’ ) ;

(numMod, numModPrereq )
(numMod, numModPrereq )
VALUES ( 6 , 1 , 1 2 ) ;
(numMod, numModPrereq )
(numMod, numModPrereq )
(numMod, numModPrereq )

VALUES ( 1 , 5 ) ;
VALUES ( 2 , 3 ) ;
VALUES ( 6 , 5 ) ;
VALUES ( 8 , 5 ) ;
VALUES ( 7 , 5 ) ;

Si on souhaite connaˆıtre les num´eros des modules prerequis pour s’inscrire dans le module ’PL/SQL Oracle’, il
nous faut tout d’abord le num´ero de ce module :
SQL> SELECT numMod FROM module WHERE nomMod = ’PL/SQL O r a c l e ’ ;
NUMMOD
−−−−−−−−−−
6
Ensuite, cherchons les num´eros des modules pr´erequis pour s’inscrire dans le module num´ero 6,
SQL> SELECT numModPrereq FROM p r e r e q u i s WHERE numMod = 6 ;
NUMMODPREREQ
−−−−−−−−−−−−
1
5
Et pour finir, allons r´ecup´erer les noms de ces modules,
SQL> SELECT nomMod FROM module WHERE numMod IN ( 1 , 5 ) ;
NOMMOD
−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−
Oracle
Merise
Vous ˆetes probablement tous en train de vous demander s’il n’existe pas une m´ethode plus simple et plus rapide,
et surtout une fa¸con d’automatiser ce que nous venons de faire. Il existe un moyen de s´electionner des donn´ees dans
plusieurs tables simultan´ement. Pour traiter la question ci-dessus il suffisait de saisir :
13

SQL> SELECT m2 . nomMod
2 FROM module m1, module m2, p r e r e q u i s p
3 WHERE m1 . numMod = p . numMod AND m2 . numMod = p . numModprereq
4 AND m1 . nomMod = ’PL/SQL O r a c l e ’ ;
NOMMOD
−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−
Oracle
Merise
Le but de ce chapitre est d’expliciter ce type de commande.

1.4.2

Produit cart´
esien

L’instruction SELECT ... FROM ... peut s’´etendre de la fa¸con suivante :
SELECT <l i s t e c o l o n n e s >
FROM < l i s t e t a b l e s >
L’exemple ci-dessous vous montre le r´esultat d’une telle commande.
SQL> SELECT ∗ FROM p r o p o s e r , p r o d u i t ;
NUMFOU
NUMPROD
PRIX
NUMPROD NOMPROD
−−−−−−−−−− −−−−−−−−−− −−−−−−−−−− −−−−−−−−−− −−−−−−−−−−−−−−−−−−−−−−−−−−−−−−
1
1
200
1 Roue de s e c o u r s
1
1
200
2 Poupee Batman
1
1
200
3 Cotons t i g e s
1
1
200
4 Cornichons
1
2
15
1 Roue de s e c o u r s
1
2
15
2 Poupee Batman
1
2
15
3 Cotons t i g e s
1
2
15
4 Cornichons
2
2
1
1 Roue de s e c o u r s
2
2
1
2 Poupee Batman
2
2
1
3 Cotons t i g e s
NUMFOU
NUMPROD
PRIX
NUMPROD NOMPROD
−−−−−−−−−− −−−−−−−−−− −−−−−−−−−− −−−−−−−−−− −−−−−−−−−−−−−−−−−−−−−−−−−−−−−−
2
2
1
4 Cornichons
3
3
2
1 Roue de s e c o u r s
3
3
2
2 Poupee Batman
3
3
2
3 Cotons t i g e s
3
3
2
4 Cornichons
16 l i g n e ( s ) s e l e c t i o n n e e ( s ) .
Placer une liste de tables dans le FROM revient `a former toutes les combinaisons de lignes possibles. Cependant, cela
a relativement peu de sens.

1.4.3

Jointure

Il serait plus int´eressant, dans le cas pr´esent, de ne voir s’afficher que des lignes dont les num´eros de produits
concordent. Pour ce faire, il suffit d’utiliser WHERE. Par exemple,
SQL> SELECT ∗ FROM p r o p o s e r , p r o d u i t
2 WHERE p r o p o s e r . numprod = p r o d u i t . numprod ;
NUMFOU
NUMPROD
PRIX
NUMPROD NOMPROD
−−−−−−−−−− −−−−−−−−−− −−−−−−−−−− −−−−−−−−−− −−−−−−−−−−−−−−−−−−−−−−−−−−−−−−
1
1
200
1 Roue de s e c o u r s
2
2
1
2 Poupee Batman
1
2
15
2 Poupee Batman
14

3

3

2

3 Cotons t i g e s

Nous avons mis en correspondance des lignes de la table proposer avec des lignes de la table produit en utilisant le
fait que numprod est une cl´e ´etrang`ere dans proposer. Comme la colonne numprod apparait deux fois dans la requˆete,
il est n´ecessaire de la pr´efixer par le nom de la table de sorte que chaque colonne puisse ˆetre d´esign´ee de fa¸con non
ambigu¨e. Si on veut mettre face `
a face les noms des produits et les noms des fournisseurs, il suffit de saisir la requˆete
SQL> SELECT nomfou , nomprod
2 FROM p r o d u i t , f o u r n i s s e u r , p r o p o s e r
3 WHERE p r o d u i t . numProd = p r o p o s e r . numProd
4 AND f o u r n i s s e u r . numFou = p r o p o s e r . numFou ;
NOMFOU
−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−
f1
f2
f1
f3

1.4.4

NOMPROD
−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−
Roue de s e c o u r s
Poupee Batman
Poupee Batman
Cotons t i g e s

Jointures refl´
exives

En utilisant la syntaxe suivante, il est possible de rennomer les tables,
FROM <t a b l e 1 > <table 1 renommee >, . . . , <t a b l e n > <table n renommee >
Reformulons la requˆete ci-dessus,
SQL> SELECT nomfou , nomprod
2 FROM p r o d u i t p , f o u r n i s s e u r f , p r o p o s e r pr
3 WHERE p . numProd = pr . numProd
4 AND f . numFou = pr . numFou ;
NOMFOU
−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−
f1
f2
f1
f3

NOMPROD
−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−
Roue de s e c o u r s
Poupee Batman
Poupee Batman
Cotons t i g e s

Le renommage permet entre autres de faire des jointures r´eflexives, c’est `a dire entre une table et elle mˆeme. Par
exemple, en reprenant la table intervalle,
SQL> SELECT ∗ FROM i n t e r v a l l e ;
BORNEINF
BORNESUP
−−−−−−−−−− −−−−−−−−−−
0
30
2
3
2
56
5
10
7
32
8
27
12
3
12
30
21
8
34
26
10 l i g n e ( s ) s e l e c t i o n n e e ( s ) .
La commande ci-dessous affiche tous les couples d’intervalles ayant une borne en commun,
SQL> SELECT ∗ FROM i n t e r v a l l e i , i n t e r v a l l e j
2
WHERE ( i . b o r n e I n f = j . b o r n e I n f OR i . borneSup = j . borneSup )
3 AND i . rowid <> j . rowid ;
15

BORNEINF
BORNESUP
BORNEINF
BORNESUP
−−−−−−−−−− −−−−−−−−−− −−−−−−−−−− −−−−−−−−−−
0
30
12
30
2
3
2
56
2
3
12
3
2
56
2
3
12
3
2
3
12
3
12
30
12
30
0
30
12
30
12
3
8 ligne ( s ) selectionnee ( s ).
Que ceux qui ont du courage reformulent la requˆete sans utiliser le rowid !

16

1.5

Agr´
egation de donn´
ees

1.5.1

Fonctions d’agr´
egation

Exemple introductif
Nous voulons connaˆıtre le nombre de lignes de table produit. Deux fa¸cons de proc´eder :
1. Solution moche
SQL> SELECT ∗ FROM PRODUIT;
NUMPROD NOMPROD
−−−−−−−−−− −−−−−−−−−−−−−−−−−−−−−−−−−−−−−−
1 Roue de s e c o u r s
2 Poupee Batman
3 Cotons t i g e s
4 Cornichons
4 ligne ( s ) selectionnee ( s ).
On a la r´eponse avec le nombre de lignes s´electionn´ees.
2. Solution belle
SQL> SELECT count ( ∗ ) FROM PRODUIT;
COUNT( ∗ )
−−−−−−−−−−
4
1 ligne selectionnee .
La r´eponse est le r´esultat de la requˆete.

efinition
Une fonction d’agr´egation retourne une valeur calcul´ee sur toutes les lignes de la requˆete (nombre, moyenne...).
Nous allons utiliser les suivantes :
– COUNT(col) : retourne le nombre de lignes dont le champ col est non NULL.
– AVG(col) : retourne la moyenne des valeurs col sur toutes les lignes dont le champ col est non NULL.
– MAX(col) : retourne la plus grande des valeurs col sur toutes les lignes dont le champ col est non NULL.
– MIN(col) : retourne la plus petite des valeurs col sur toutes les lignes dont le champ col est non NULL.
– SUM(col) : retourne la somme des valeurs col sur toutes les lignes dont le champ col est non NULL.
Exemples d’utilisation
L’exemple suivant retourne le prix du produit propos´e au prix maximal.
SQL> SELECT MAX( p r i x )
2 FROM PROPOSER;
MAX(PRIX)
−−−−−−−−−−
200
1 ligne selectionnee .
Il est possible de renommer la colonne MAX(prix), en utilisant le mot cl´e AS :
SQL> SELECT MAX( p r i x ) AS PRIX MAXIMAL
2 FROM PROPOSER;
PRIX MAXIMAL
−−−−−−−−−−−−
200
17

1 ligne selectionnee .
Les requˆetes suivantes r´ecup`erent le nom du fournisseur proposant l’article ’Poup´ee Batman’ au prix le moins ´elev´e :
SQL> SELECT MIN( p r i x ) AS PRIX MINIMUM
2 FROM PROPOSER PR, PRODUIT P
3 WHERE PR. numprod = P . numprod
4 AND nomprod = ’ Poupee Batman ’ ;
PRIX MINIMUM
−−−−−−−−−−−−
1
1 ligne selectionnee .
SQL> SELECT nomfou
2 FROM FOURNISSEUR F , PROPOSER PR, PRODUIT P
3 WHERE F . numfou = PR. numfou
4 AND PR. numprod = P . numprod
5 AND nomprod = ’ Poupee Batman ’
6 AND p r i x = 1 ;
NOMFOU
−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−
f2
1 ligne selectionnee .
Il est possible de faire cela avec une seule requˆete en r´ecup´erant le prix minimum dans une requˆete imbriqu´ee. Mais
cela sera pour un cours ult´erieur.
Compl´
ements sur COUNT
On r´ecup`ere le nombre de ligne retourn´ees par une requˆete en utilisant COUNT(*). Par exemple, si on souhaite
connaˆıtre le nombre de produits propos´es par le fournisseur ’f1’ :
SQL>
2
3
4

SELECT COUNT( ∗ ) AS NB PROD
FROM FOURNISSEUR F , PROPOSER P
WHERE F . numfou = P . numfou
AND nomfou = ’ f 1 ’ ;

NB PROD
−−−−−−−−−−
2
1 ligne selectionnee .
On aurait aussi pu saisir :
SQL>
2
3
4

SELECT COUNT( numprod ) AS NB PROD
FROM FOURNISSEUR F , PROPOSER P
WHERE F . numfou = P . numfou
AND nomfou = ’ f 1 ’ ;

NB PROD
−−−−−−−−−−
2
1 ligne selectionnee .
Pour connaˆıtre le nombre de produits propos´es, c’est `a dire dont le numprod a une occurence dans la table PROPOSER,
on proc`ede de la fa¸con suivante :

18

SQL> SELECT COUNT(DISTINCT numprod ) AS NB PRODUITS PROPOSES
2 FROM PROPOSER;
NB PRODUITS PROPOSES
−−−−−−−−−−−−−−−−−−−−
3
1 ligne selectionnee .
Le DISTINCT nous sert `
a ´eviter qu’un mˆeme produit propos´e par des fournisseurs diff´erents soit comptabilis´e
plusieurs fois.

1.5.2

Groupage

L’instruction GROUP BY
Les op´eration d’agr´egation consid´er´ees jusqu’`a maintenant portent sur la totalit´e des lignes retourn´ees par les
requˆetes, l’instruction GROUP BY permet de former des paquets `a l’int´erieur desquels les donn´ees seront agr´eg´ees. Cette
instruction s’utilise de la mani`ere suivante
SELECT . . .
FROM . . .
WHERE. . .
GROUP BY < l i s t e c o l o n n e s >
ORDER BY . . .
La liste des colonnes sert de crit`ere pour r´epartir les lignes dans des paquets de lignes. Si par exemple nous
souhaitons afficher la liste des nombres de produits propos´es par chaque fournisseur :
SQL> SELECT nomfou , COUNT(DISTINCT numprod ) AS NB PRODUITS PROPOSES
2 FROM FOURNISSEUR F , PROPOSER P
3 WHERE F . numfou = P . numfou
4 GROUP BY nomfou ;
NOMFOU
NB PRODUITS PROPOSES
−−−−−−−−−−−−−−−−−−−−−−−−−−−−−− −−−−−−−−−−−−−−−−−−−−
f1
2
f2
1
f3
1
3 ligne ( s ) selectionnee ( s ).
L’instruction HAVING
Supposons que de la requˆete pr´ec´edente, nous ne souhaitions garder que les lignes pour lesquelles la valeur
NB PRODUITS PROPOSES est ´egale `
a 1. Ajouter une condition dans WHERE serait inutile, le filtrage occasionn´e par WHERE
est effectu´e avant l’agr´egation. Il nous faudrait une instruction pour n’inclure que des groupes de donn´ees r´epondant
certains crit`eres. L’instruction utilis´ee pour ce faire est HAVING. Son utilisation est la suivante :
SELECT . . .
FROM . . .
WHERE . . .
GROUP BY. . .
HAVING <c o n d i t i o n >
ORDER BY . . .
Par exemple,
SQL> SELECT nomfou , COUNT(DISTINCT numprod ) AS NB PRODUITS PROPOSES
2 FROM FOURNISSEUR F , PROPOSER P
3 WHERE F . numfou = P . numfou
4 GROUP BY nomfou
5 HAVING COUNT(DISTINCT numprod ) = 1

19

6

ORDER BY nomfou DESC;

NOMFOU
NB PRODUITS PROPOSES
−−−−−−−−−−−−−−−−−−−−−−−−−−−−−− −−−−−−−−−−−−−−−−−−−−
f3
1
f2
1
2 ligne ( s ) selectionnee ( s ).
Affichons les noms des fournisseurs qui ont livr´e strictement plus d’un produit diff´erent (toutes livraisons confondues),
SQL> SELECT nomfou
2 FROM FOURNISSEUR F , DETAILLIVRAISON D
3 WHERE F . numfou = D. numfou
4 GROUP BY nomfou
5 HAVING count (DISTINCT D. numprod ) > 1 ;
NOMFOU
−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−
f1
1 ligne selectionnee .

20

1.6
1.6.1

Vues

efinition

Une vue est une table contenant des donn´ees calcul´ees sur celle d’une autre table. Les donn´ees d’une vue sont tout
le temps `
a jour. Si vous modifiez les donn´ees d’une des tables sur lesquelles est calcul´ee la vue, alors les modifications
sont automatiquement r´epercut´ees sur la vue.

1.6.2

Syntaxe

Appr´eciez la simplicit´e de la syntaxe :
CREATE VIEW <nom vue> AS <r e q u e t e >

1.6.3

Application

Par exemple, la requˆete suivante met en correpondance les noms des produits avec le nombre de fournisseurs qui
le proposent :
SQL>
2
3
4
5
6

SELECT nomprod , COUNT( numfou ) AS NB FOURNISSEURS
FROM PRODUIT P
LEFT OUTER JOIN PROPOSER PR
ON P . numprod = PR. numprod
GROUP BY nomprod
ORDER BY COUNT( numfou ) ;

NOMPROD
NB FOURNISSEURS
−−−−−−−−−−−−−−−−−−−−−−−−−−−−−− −−−−−−−−−−−−−−−
Cor n i c h o n s
0
Cotons t i g e s
1
Roue de s e c o u r s
1
Poupee Batman
2
4 ligne ( s ) selectionnee ( s ).
Ce type de requˆete sera explit´e dans un cours ult´erieur. Pour le moment, notez juste que les outils dont vous
disposez pour le moment ne vous permettront pas de formuler une requˆete affichant les noms des produits n’ayant
aucun fournisseur. Cr´eons une vue pour ne pas avoir `a se farcir la requˆete chaque fois que nous aurons besoin de ces
informations :
SQL>
2
3
4
5
6
7

CREATE VIEW NB FOURNISSEURS PAR PRODUIT AS
SELECT nomprod , COUNT( numfou ) AS NB FOURNISSEURS
FROM PRODUIT P
LEFT OUTER JOIN PROPOSER PR
ON P . numprod = PR. numprod
GROUP BY nomprod
ORDER BY COUNT( numfou ) ;

Vue c r e e e .
Une fois cr´e´ee, on peut interroger une vue de la mˆeme fa¸con qu’on interroge une table :
SQL> SELECT ∗
2 FROM NB FOURNISSEURS PAR PRODUIT ;
NOMPROD
NB FOURNISSEURS
−−−−−−−−−−−−−−−−−−−−−−−−−−−−−− −−−−−−−−−−−−−−−
Cor n i c h o n s
0
Cotons t i g e s
1
Roue de s e c o u r s
1
Poupee Batman
2
4 ligne ( s ) selectionnee ( s ).
21

Notez que toute modification dans la table PROPOSER ou PRODUIT sera imm´ediatement r´epercut´ee sur la vue.
SQL> INSERT INTO PROPOSER VALUES ( 3 , 4 , 9 ) ;
1 ligne creee .
SQL> SELECT ∗
2 FROM NB FOURNISSEURS PAR PRODUIT ;
NOMPROD
NB FOURNISSEURS
−−−−−−−−−−−−−−−−−−−−−−−−−−−−−− −−−−−−−−−−−−−−−
Cor n i c h o n s
1
Cotons t i g e s
1
Roue de s e c o u r s
1
Poupee Batman
2
4 ligne ( s ) selectionnee ( s ).
Maintenant, nous souhaitons voir s’afficher, pour tout i, le nombre de produits propos´es par exactement i fournisseurs.
SQL> SET head o f f
SQL> SELECT ’ I l y a ’ | | COUNT(NOMPROD) | | ’ p r o d u i t ( s ) q u i e s t / s o n t ’ | |
2 ’ p r o p o s e ( s ) par ’ | | NB FOURNISSEURS | | ’ f o u r n i s s e u r ( s ) . ’
3 FROM NB FOURNISSEURS PAR PRODUIT
4 GROUP BY NB FOURNISSEURS
5 ORDER BY NB FOURNISSEURS ;
I l y a 3 p r o d u i t ( s ) q u i e s t / s o n t p r o p o s e ( s ) par 1 f o u r n i s s e u r ( s ) .
I l y a 1 p r o d u i t ( s ) q u i e s t / s o n t p r o p o s e ( s ) par 2 f o u r n i s s e u r ( s ) .
2 ligne ( s ) selectionnee ( s ).
SQL> SET head on

1.6.4

Suppression

On supprime une vue avec l’instruction suivante :
DROP VIEW <nom vue >;

22

1.7

Requˆ
etes imbriqu´
ees

Oracle permet d’imbriquer les requˆetes, c’est-`
a-dire de placer des requˆetes dans les requˆetes. Une requˆete imbriqu´ee
peut renvoyer trois types de r´esultats :
– une valeur scalaire
– une colonne
– une table

1.7.1

Sous requˆ
etes renvoyant une valeur scalaire

Le r´esultat d’une requˆete est dit scalaire s’il comporte une seule ligne et une seule colonne. Par exemple :
SQL> SELECT COUNT( ∗ ) FROM PERSONNE;
COUNT( ∗ )
−−−−−−−−−−
21
On peut placer dans une requˆete une sous-requˆete calculant un r´esultat scalaire. Un tel type de sous-requˆete se place
soit comme une colonne suppl´ementaire, soit comme une valeur servant `a ´evaluer des conditions (WHERE ou HAVING).
Colonne fictive
On peut ajouter une colonne dans une requˆete, et choisir comme valeurs pour cette colonne le r´esultat d’une
requˆete. Ce type de requˆete est souvent une alternative `a GROUP BY. Par exemple, la requˆete suivante nous renvoie,
pour tout produit, le nombre de fournisseurs proposant ce produit :
SQL> SELECT nomprod , (SELECT COUNT( ∗ )
2
FROM PROPOSER PR
3
WHERE PR. numprod = P . numprod )
4
AS NB FOURNISSEURS
5 FROM PRODUIT P ;
NOMPROD
NB FOURNISSEURS
−−−−−−−−−−−−−−−−−−−−−−−−−−−−−− −−−−−−−−−−−−−−−
Roue de s e c o u r s
1
Poupee Batman
2
Cotons t i g e s
1
Cor n i c h o n s
0
Conditions complexes
On peut construire une condition en utilisant le r´esultat d’une requˆete. Pour notre exemple, d´eclarons d’abord une
vue contenant le nombe d’articles propos´es par chaque fournisseur,
SQL> CREATE VIEW NB PROD PAR FOU AS
2 SELECT numfou , (SELECT COUNT( ∗ )
3
FROM PROPOSER P
4
WHERE P . numfou = F . numfou )
5
AS NB PROD
6 FROM FOURNISSEUR F ;
Vue c r e e e .
Ensuite, recherchons les noms des fournisseurs proposant le plus de produits :
SQL> SELECT nomfou
2 FROM FOURNISSEUR F , NB PROD PAR FOU N
3 WHERE F . numfou = N. numfou
4 AND NB PROD = (SELECT MAX(NB PROD)
5
FROM NB PROD PAR FOU ) ;
NOMFOU
23

−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−
f1
La requˆete SELECT MAX(NB PROD) FROM NB PROD PAR FOU est ´evalu´ee avant, et son r´esultat lui est substitu´e dans
l’expression de la requˆete. Comme on a
SQL> SELECT MAX(NB PROD) FROM NB PROD PAR FOU ;
MAX(NB PROD)
−−−−−−−−−−−−
2
Alors la requˆete pr´ec´edente, dans ce contexte, est ´equivalente `a
SQL> SELECT nomfou
2 FROM FOURNISSEUR F , NB PROD PAR FOU N
3 WHERE F . numfou = N. numfou
4 AND NB PROD = 2 ;
NOMFOU
−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−
f1
INSERT et UPDATE
On peut placer dans des instructions de mises `a jour ou d’insertions des requˆetes imbriqu´ees. Par exemple,
SQL> INSERT INTO PERSONNE ( numpers , nom , prenom )
2 VALUES ( (SELECT MAX( numpers ) + 1 FROM PERSONNE) ,
3
’ Darth ’ , ’ Vador ’ ) ;
1 ligne creee .
SQL> UPDATE PERSONNE SET
2
p e r e = (SELECT numpers
3
FROM PERSONNE
4
WHERE nom = ’ S o c r a t e ’
5
AND prenom IS NULL) ,
6
mere = (SELECT numpers
7
FROM PERSONNE
8
WHERE nom = ’ Fabian ’
9
AND prenom = ’ Lara ’ )
10
WHERE numpers = (SELECT numpers
11
FROM PERSONNE
12
WHERE nom = ’ Darth ’
13
AND prenom = ’ Vador ’ ) ;
1 l i g n e mise a j o u r .

1.7.2

Sous requˆ
etes renvoyant une colonne

On consid`ere une colonne comme une liste de valeurs, on peut tester l’appartance d’un ´el´ement `a cette liste `
a l’aide
de l’op´erateur IN. On peut s’en servir comme une alternative aux jointures, par exemple, r´e´ecrivons la requˆete de la
section pr´ec´edente. La requˆete suivante nous renvoie le nombre de produits propos´es par les fournisseurs proposant le
plus de produits :
SQL> SELECT MAX(NB PROD) FROM NB PROD PAR FOU ;
MAX(NB PROD)
−−−−−−−−−−−−
2
Maintenant, recherchons les num´eros des fournisseurs proposant un tel nombre de produits :

24

SQL> SELECT N. numfou
2 FROM NB PROD PAR FOU N
3 WHERE NB PROD = (SELECT MAX(NB PROD)
4
FROM NB PROD PAR FOU ) ;
NUMFOU
−−−−−−−−−−
1
Notons que s’il existe plusieurs fournisseurs proposant 2 produits, cette requˆete renverra plusieurs lignes. C’est
donc par hasard qu’elle ne retourne qu’une ligne. Le num´ero du fournisseur proposant le plus de produits est donc le
1. Cherchons ce fournisseur :
SQL> SELECT nomfou
2 FROM FOURNISSEUR F
3 WHERE F . numfou IN ( 1 ) ;
NOMFOU
−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−
f1
Il suffit donc dans la requˆete ci-dessous de remplacer le 1 par la requˆete qui a retourn´e 1. On a finalement :
SQL> SELECT nomfou
2 FROM FOURNISSEUR F
3 WHERE F . numfou IN (SELECT N. numfou
4
FROM NB PROD PAR FOU N
5
WHERE NB PROD = (SELECT MAX(NB PROD)
6
FROM NB PROD PAR FOU ) ) ;
NOMFOU
−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−
f1

1.7.3

Sous requˆ
etes non correl´
ees renvoyant une table

On peut remplacer le nom d’une table dans la clause FROM par une sous-requˆete. Par exemple, la requˆete suivante
renvoie une table.
SQL> SELECT
2
(SELECT COUNT( ∗ )
3
FROM PROPOSER PR
4
WHERE PR. numfou = F . numfou
5
) AS NB PROD
6 FROM FOURNISSEUR F ;
NB PROD
−−−−−−−−−−
2
1
1
0
Cette table contient, pour chaque fournisseur, le nombre de produits propos´es. Si l’on souhaite connaˆıtre le plus
grand nombre de produits propos´es, on se sert du r´esultat de la requˆete ci-dessus comme d’une table :
SQL> SELECT MAX(NB PROD) AS MAX NB PROD
2
FROM
3
(SELECT
4
(SELECT COUNT( ∗ )
5
FROM PROPOSER PR
6
WHERE PR. numfou = F . numfou
7
) AS NB PROD
25

8
9

FROM FOURNISSEUR F
);

MAX NB PROD
−−−−−−−−−−−
2
Ce type de requˆete est une alternative aux vues. R´ecup´erons maintenant les noms des fournisseurs proposant le
plus de produits (sans jointure et sans vue !) :
SQL> SELECT nomfou
2 FROM FOURNISSEUR
3 WHERE numfou IN
4
(SELECT numfou
5
FROM
6
(SELECT numfou ,
7
(SELECT COUNT( ∗ )
8
FROM PROPOSER PR
9
WHERE PR. numfou = F . numfou
10
) AS NB PROD
11
FROM FOURNISSEUR F
12
) N
13
WHERE NB PROD =
14
(SELECT MAX(NB PROD)
15
FROM
16
(SELECT numfou ,
17
(SELECT COUNT( ∗ )
18
FROM PROPOSER PR
19
WHERE PR. numfou = F . numfou
20
) AS NB PROD
21
FROM FOURNISSEUR F
22
) N
23
)
24
);
NOMFOU
−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−
f1
Vous constatez que la solution utilisant les vues est nettement plus simple.

1.7.4

Sous requˆ
etes correl´
ees

Une sous-requˆete peut ˆetre de deux types :
– simple : Elle ´evalu´ee avant la requˆete principale
– correl´
ee : Elle est ´evalu´ee pour chaque ligne de la requˆete principale
Par exemple, la requˆete suivante renvoie le nombre de produits livr´es pour chaque fournisseur. Elle contient une
sous-requˆete correl´ee.
SQL> SELECT numfou ,
2
(SELECT SUM( q t e )
3
FROM DETAILLIVRAISON D
4
WHERE D. numfou = F . numfou
5
) NB PROD L
6 FROM FOURNISSEUR F ;
NUMFOU NB PROD L
−−−−−−−−−− −−−−−−−−−−
1
45
2
3
10
4
26

Cette mˆeme requˆete, une fois ´evalu´ee, peut server de requˆete non correl´ee si on souhaite connaˆıtre les noms de ces
fournisseurs :
SQL> SELECT nomfou , NB PROD L
2 FROM FOURNISSEUR F ,
3
(SELECT numfou ,
4
(SELECT SUM( q t e )
5
FROM DETAILLIVRAISON D
6
WHERE D. numfou = F . numfou
7
) NB PROD L
8
FROM FOURNISSEUR F
9
) L
10 WHERE F . numfou = L . numfou ;
NOMFOU
NB PROD L
−−−−−−−−−−−−−−−−−−−−−−−−−−−−−− −−−−−−−−−−
f1
45
f2
f3
10
f4
Amusons-nous : quel sont, pour chaque fournisseur, les produits qui ont ´et´e les plus livr´es ?
SQL> SELECT nomfou , nomprod
2 FROM FOURNISSEUR F , PRODUIT P ,
3
(SELECT FF . numfou , PP . numprod
4
FROM FOURNISSEUR FF , PRODUIT PP
5
WHERE
6
(SELECT SUM( q t e )
7
FROM DETAILLIVRAISON L
8
WHERE L . numfou = FF . numfou
9
AND L . numprod = PP . numprod
10
)
11
=
12
(SELECT MAX(NB PROD L)
13
FROM
14
(SELECT numfou , SUM( q t e ) AS NB PROD L
15
FROM DETAILLIVRAISON L
16
GROUP BY numprod , numfou
17
) Q
18
WHERE Q. numfou = FF . numfou
19
)
20
GROUP BY numfou , numprod
21
) M
22 WHERE M. numprod = P . numprod
23 AND M. numfou = F . numfou ;
NOMFOU
−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−
f1
f3

NOMPROD
−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−
Roue de s e c o u r s
Cotons t i g e s

Dans la requˆete pr´ec´edente, quelles sous-requˆetes sont correl´ees et lesquelles ne le sont pas ?

27

1.8
1.8.1

Compl´
ements sur les types
Types num´
eriques

NUMBER(p, s) d´efinit un type num´erique de au plus (p − s) chiffres avant la virgule et au plus s chiffres apr`es la
virgule.
SQL> CREATE TABLE TOTO
2 ( t u t u number( 4 , 2 )
3 );
Table c r e e e .
SQL> INSERT INTO TOTO VALUES( 1 0 . 2 ) ;
1 ligne creee .
SQL> INSERT INTO TOTO VALUES( 1 0 ) ;
1 ligne creee .
SQL> INSERT INTO TOTO VALUES( . 0 1 ) ;
1 ligne creee .
SQL> INSERT INTO TOTO VALUES( 2 1 . 0 1 ) ;
1 ligne creee .
SQL> INSERT INTO TOTO VALUES( 2 1 . 0 ) ;
1 ligne creee .
SQL> INSERT INTO TOTO VALUES( 2 1 . 0 1 2 ) ;
1 ligne creee .
SQL> INSERT INTO TOTO VALUES( 3 2 1 . 0 ) ;
INSERT INTO TOTO VALUES( 3 2 1 . 0 )

ERREUR a l a l i g n e 1 :
ORA−01438: v a l e u r i n c o h e r e n t e avec l a p r e c i s i o n i n d i q u e e pour c e t t e c o l o n n e

SQL> INSERT INTO TOTO VALUES( 3 2 1 ) ;
INSERT INTO TOTO VALUES( 3 2 1 )

ERREUR a l a l i g n e 1 :
ORA−01438: v a l e u r i n c o h e r e n t e avec l a p r e c i s i o n i n d i q u e e pour c e t t e c o l o n n e

SQL> SELECT ∗
2 FROM TOTO;
TUTU
−−−−−−−−−−
10 ,2
10
,01
21 ,01

28

21
21 ,01
6 ligne ( s ) selectionnee ( s ).

1.8.2

Types chaine de caract`
eres

Une petite liste de propri´et´es et de fonctions qui peuvent servir :
– Pour concat´ener deux chaˆınes de caract`eres, on utilise l’op´erateur ||
– Il est aussi possible de comparer deux chaines de caract`eres avec >, l’ordre consid´er´e est l’ordre ”dictionnaire”
(ou lexicographique).
– La longueur s’obtient avec la fonction LENGTH.
– On extrait une sous-chaine de caract`eres de longueur l `a partir de l’indice i (les indices commencent `a 1) de la
chaˆıne s avec la fonction SUBSTR(S, i, l). Par exemple, SUBSTR(’oracle’, 3, 2) = ’ac’
– UPPER convertit en majuscules, LOWER convertit en minuscules.

1.8.3

Types date

Une date en SQL est consid´er´e comme un point dans le temps. On le convertit en chaine de carat`eres avec la
u format est une chaˆıne de caract`eres optionnelle. Par exemple,
fonction to char(date, format), o`
SQL> SELECT t o c h a r ( d a t e l i ) AS DT
2 FROM LIVRAISON ;
DT
−−−−−−−−
30/10/06
30/10/06
SQL> SELECT t o c h a r ( d a t e l i , ’ yyyy ’ ) AS ANNEE
2 FROM LIVRAISON ;
ANNE
−−−−
2006
2006
SQL> SELECT t o c h a r ( d a t e l i , ’ yyyy /mm/dd ’ ) AS DT
2 FROM LIVRAISON ;
DT
−−−−−−−−−−
2006/10/30
2006/10/30
SQL> SELECT t o c h a r ( d a t e l i , ’yyyymmdd ’ ) AS DT
2 FROM LIVRAISON ;
DT
−−−−−−−−
20061030
20061030
On convertit une chaine de caract`eres en date avec la fonction to date(date, format). Par exemple :
SQL> UPDATE LIVRAISON
2 SET d a t e l i = t o d a t e ( ’ 1934 ’ | | t o c h a r ( d a t e l i , ’mmdd ’ ) , ’yyyymmdd ’ ) ;
2 l i g n e ( s ) mise ( s ) a j o u r .
SQL> SELECT ∗
2 FROM LIVRAISON ;
NUMFOU

NUMLI DATELI
29

−−−−−−−−−− −−−−−−−−−− −−−−−−−−
1
1 30/10/34
3
1 30/10/34
SQL> UPDATE LIVRAISON
2 SET d a t e l i = t o d a t e ( ’ 2006 ’ | | t o c h a r ( d a t e l i , ’mmdd ’ ) , ’yyyymmdd ’ ) ;
2 l i g n e ( s ) mise ( s ) a j o u r .
SQL> SELECT ∗ FROM LIVRAISON ;
NUMFOU
NUMLI
−−−−−−−−−− −−−−−−−−−−
1
1
3
1

1.8.4

DATELI
−−−−−−−−
30/10/06
30/10/06

La fonction inclassable

nvl(valeur1, valeur2) renvoie valeur1 si valeur1 est non NULL, valeur2 sinon. Par exemple,
SQL> DELETE FROM TOTO;
6 l i g n e ( s ) supprimee ( s ) .
SQL> SELECT SUM( t u t u )
2 FROM TOTO;
SUM(TUTU)
−−−−−−−−−−

SQL> SELECT n v l (SUM( t u t u ) , 0 )
2 FROM TOTO;
NVL(SUM(TUTU) , 0 )
−−−−−−−−−−−−−−−−
0

1.8.5

Contraintes CHECK

La contrainte d´eclarative de type permet de tester une condition portant les lignes de la table prises une par une.
La syntaxe est :
ALTER TABLE nomtable ADD CONSTRAINT n o m c o n t r a i n t e CHECK( c o n d i t i o n ) ;
Par exemple,
SQL> ALTER TABLE EMP ADD CONSTRAINT c k s a l a r y CHECK(SAL > 0 ) ;
Table m o d i f i e e .
SQL> INSERT INTO EMP (EMPNO, SAL) VALUES ( 1 5 , −1);
INSERT INTO EMP (EMPNO, SAL) VALUES ( 1 5 , −1)

ERREUR a l a l i g n e 1 :
ORA−02290: v i o l a t i o n de c o n t r a i n t e s (SCOTT.CK SALARY) de v e r i f i c a t i o n
Une contrainte de type CHECK ne peut pas contenir de requˆetes ni de valeurs non constantes (sysdate par exemple).

30

1.9
1.9.1

Introduction au PL/SQL
PL/SQL

Le PL de PL/SQL signifie Procedural Language. Il s’agit d’une extension proc´edurale du SQL permettant d’effectuer
des traitements complexes sur une base de donn´ees. Les possibilit´es offertes sont les mˆemes qu’avec des langages
imp´eratifs (instructions en s´equence) classiques.
Ecrivez-le dans un ´editeur dont vous copierez le contenu dans SQL+. Un script ´ecrit en PL/SQL se termine obligatoirement par un /, sinon SQL+ ne l’interpr`ete pas. S’il contient des erreurs de compilation, il est possible d’afficher les
messages d’erreur avec la commande SQL+ : SHOW ERRORS.

1.9.2

Blocs

Tout code ´ecrit dans un langage proc´edural est form´e de blocs. Chaque bloc comprend une section de d´eclaration
de variables, et un ensemble d’instructions dans lequel les variables d´eclar´ees sont visibles.
La syntaxe est
DECLARE
/∗ d e c l a r a t i o n de v a r i a b l e s ∗/
BEGIN
/∗ i n s t r u c t i o n s a e x e c u t e r ∗/
END;

1.9.3

Affichage

Pour afficher le contenu d’une variable, les proc´edures DBMS OUTPUT.PUT() et DBMS OUTPUT.PUT LINE() prennent
en argument une valeur `
a afficher ou une variable dont la valeur est `a afficher. Par d´efaut, les fonctions d’affichage
sont desactiv´ees. Il convient, `
a moins que vous ne vouliez rien voir s’afficher, de les activer avec la commande SQL+
SET SERVEROUTPUT ON.

1.9.4

Variables

Une variable se d´eclare de la sorte :
nom type [ : = i n i t i a l i s a t i o n ] ;
L’initisation est optionnelle. Nous utiliserons les mˆemes types primitifs que dans les tables. Par exemple :
SET SERVEROUTPUT ON
DECLARE
c varchar2 ( 1 5 ) := ’ H e l l o World ! ’ ;
BEGIN
DBMS OUTPUT. PUT LINE( c ) ;
END;
/
Les affectations se font avec la syntaxe variable := valeur ;

1.9.5

Traitements conditionnels

Le IF et le CASE fonctionnent de la mˆeme fa¸con que dans les autres langages imp´eratifs :
IF /∗ c o n d i t i o n 1 ∗/ THEN
/∗ i n s t r u c t i o n s 1 ∗/
ELSE
/∗ i n s t r u c t i o n s 2 ∗/
END IF ;
voire
IF /∗ c o n d i t i o n 1 ∗/ THEN
/∗ i n s t r u c t i o n s 1 ∗/
ELSIF /∗ c o n d i t i o n 2 ∗/
/∗ i n s t r u c t i o n s 2 ∗/
31

ELSE
/∗ i n s t r u c t i o n s 3 ∗/
END IF ;
Les conditions sont les mˆemes qu’en SQL. Le switch du langage C s’impl´emente en PL/SQL de la fa¸con suivante :
CASE /∗ v a r i a b l e ∗/
WHEN /∗ v a l e u r 1 ∗/ THEN
/∗ i n s t r u c t i o n s 1 ∗/
WHEN /∗ v a l e u r 2 ∗/ THEN
/∗ i n s t r u c t i o n s 2 ∗/
...
WHEN /∗ v a l e u r n ∗/ THEN
/∗ i n s t r u c t i o n s n ∗/
ELSE
/∗ i n s t r u c t i o n s par d ´e f a u t ∗/
END CASE;

1.9.6

Traitements r´
ep´
etitifs

LOOP ... END LOOP ; permet d’impl´ementer les boucles
LOOP
/∗ i n s t r u c t i o n s ∗/
END LOOP;
L’instruction EXIT WHEN permet de quitter une boucle.
LOOP
/∗ i n s t r u c t i o n s ∗/
EXIT WHEN /∗ c o n d i t i o n ∗/ ;
END LOOP;
La boucle FOR existe aussi en PL/SQL :
FOR /∗ v a r i a b l e ∗/ IN /∗ i n f ∗/ . . /∗ sup ∗/ LOOP
/∗ i n s t r u c t i o n s ∗/
END LOOP;
Ainsi que la boucle WHILE :
WHILE /∗ c o n d i t i o n ∗/ LOOP
/∗ i n s t r u c t i o n s ∗/
END LOOP;
Est-il possible, en bidouillant, d’impl´ementer une boucle DO ... WHILE ?

32

1.10

Tableaux et structures

1.10.1

Tableaux

Cr´
eation d’un type tableau
Les types tableau doivent ˆetre d´efinis explicitement par une d´eclaration de la forme
TYPE /∗ t y p e ∗/ IS VARRAY ( /∗ t a i l l e ∗/ ) OF /∗ t y p e E l e m e n t s ∗/ ;
– type est le nom du type tableau cr´ee par cette instruction
– taille est le nombre maximal d’´el´ements qu’il est possible de placer dans le tableau.
– typeElements est le type des ´el´ements qui vont ˆetre stock´es dans le tableau, il peut s’agir de n’importe quel
type.
Par exemple, cr´eons un type tableau de nombres indic´e de 1 `a 10, que nous appelerons numberTab
TYPE numberTab IS VARRAY ( 1 0 ) OF NUMBER;

eclaration d’un tableau
Dor´enavant, le type d’un tableau peut ˆetre utilis´e au mˆeme titre que NUMBER ou VARCHAR2. Par exemple, d´eclarons
un tableau appel´e t de type numberTab,
DECLARE
TYPE numberTab IS VARRAY ( 1 0 ) OF NUMBER;
t numberTab ;
BEGIN
/∗ i n s t r u c t i o n s ∗/
END;
/
Allocation d’un tableau
La cr´eation d’un type tableau met `
a disposition un constructeur du mˆeme nom que le type cr´e´e. Cette fonction
r´eserve de l’espace m´emoire pour ce tableau et retourne l’adresse m´emoire de la zone r´eserv´ee, il s’agit d’une sorte de
malloc. Si, par exemple, un type tableau numtab a ´et´e cr´ee, la fonction numtab() retourne une tableau vide.
DECLARE
TYPE numberTab IS VARRAY ( 1 0 ) OF NUMBER;
t numberTab ;
BEGIN
t := numberTab ( ) ;
/∗ u t i l i s a t i o n du t a b l e a u ∗/
END;
/
Une fois cette allocation faite, il devient presque possible d’utiliser le tableau...
Dimensionnement d’un tableau
Le tableau retourn´e par le constructeur est vide. Il convient ensuite de r´eserver de l’espace pour stocker les ´el´ements
qu’il va contenir. On utilise pour cela la m´ethode EXTEND(). EXTEND s’invoque en utilisant la notation point´ee. Par
exemple,
DECLARE
TYPE numberTab IS VARRAY ( 1 0 ) OF NUMBER;
t numberTab ;
BEGIN
t := numberTab ( ) ;
t .EXTEND( 4 ) ;
/∗ u t i l i s a t i o n du t a b l e a u ∗/
END;
/

33

Dans cet exemple, t.EXTEND(4) ; permet par la suite d’utiliser les ´el´ements du tableau t(1), t(2), t(3) et t(4).
Il n’est pas possible ”d’´etendre” un tableau `
a une taille sup´erieure `a celle sp´ecifi´ee lors de la cr´eation du type tableau
associ´e.
Utilisation d’un tableau
On acc`ede, en lecture et en ´ecriture, au i-`eme ´el´ement d’une variable tabulaire nomm´e T avec l’instruction T(i).
Les ´el´ements sont indic´es `
a partir de 1.
Effectuons, par exemple, une permutation circulaire vers la droite des ´el´ements du tableau t.
DECLARE
TYPE numberTab IS VARRAY ( 1 0 ) OF NUMBER;
t numberTab ;
i number ;
k number ;
BEGIN
t := numberTab ( ) ;
t .EXTEND( 1 0 ) ;
FOR i IN 1 . . 1 0 LOOP
t ( i ) := i ;
END LOOP;
k := t ( 1 0 ) ;
FOR i in REVERSE 2 . . 1 0 LOOP
t ( i ) := t ( i − 1 ) ;
END LOOP;
t ( 1 ) := k ;
FOR i IN 1 . . 1 0 LOOP
DBMS OUTPUT. PUT LINE( t ( i ) ) ;
END LOOP;
END;
/

1.10.2

Structures

Un structure est un type regroupant plusieurs types. Une variable de type structur´e contient plusieurs variables,
ces variables s’appellent aussi des champs.
Cr´
eation d’un type structur´
e
On d´efinit un type structur´e de la sorte :
TYPE /∗ nomType ∗/ IS RECORD
(
/∗ l i s t e d e s champs ∗/
);
nomType est le nom du type structur´e construit avec la syntaxe pr´ec´edente. La liste suit la mˆeme syntaxe que la
liste des colonnes d’une table dans un CREATE TABLE. Par exemple, construisons le type point (dans IR2 ),
TYPE p o i n t IS RECORD
(
a b s c i s s e NUMBER,
ordonnee NUMBER
);
Notez bien que les types servant `
a d´efinir un type structur´e peuvent ˆetre quelconques : variables scalaires, tableaux,
structures, etc.

eclaration d’une variable de type structur´
e
point est maintenant un type, il devient donc possible de cr´eer des variables de type point, la r`egle est toujours la
mˆeme pour d´eclarer des variables en PL/SQL, par exemple

34

p point ;
permet de d´eclarer une variable p de type point.
Utilisation d’une variable de type structur´
e
Pour acc´eder `
a un champ d’une variable de type structur´e, en lecture ou en ´ecriture, on utilise la notation point´ee :
v.c est le champ appel´e c de la variable structur´e appel´ee v. Par exemple,
DECLARE
TYPE p o i n t IS RECORD
(
a b s c i s s e NUMBER,
ordonnee NUMBER
);
p point ;
BEGIN
p . a b s c i s s e := 1 ;
p . ordonnee := 3 ;
DBMS OUTPUT. PUT LINE( ’ p . a b s c i s s e = ’ | | p . a b s c i s s e | |
’ and p . ordonnee = ’ | | p . ordonnee ) ;
END;
/
Le script ci-dessous cr´ee le type point, puis cr´ee une variable t de type point, et enfin affecte aux champs abscisse
et ordonnee du point p les valeurs 1 et 3.

35

1.11

Utilisation du PL/SQL

Ce cours est une introduction aux interactions possibles entre la base de donn´ees et les scripts PL/SQL.

1.11.1

Affectation

On place dans une variable le r´esultat d’une requˆete en utilisant le mot-cl´e INTO. Les instructions
SELECT champ 1 , . . . , champ n INTO v 1 , . . . , v n
FROM . . .
affecte aux variables v 1, ..., v n les valeurs retourn´ees par la requˆete. Par exemple
DECLARE
num NUMBER;
nom VARCHAR2( 3 0 ) := ’ Poup´e e Batman ’ ;
BEGIN
SELECT numprod INTO num
FROM PRODUIT
WHERE nomprod = nom ;
DBMS OUTPUT. PUT LINE( ’L ’ ’ a r t i c l e ’ | |
nom | | ’ a pour num´ero ’ | | num ) ;
END;
/
Prˆetez attention au fait que la requˆete doit retourner une et une une seule ligne, sinon, une erreur se produit `
a
l’ex´ecution.

1.11.2

Tables et structures

Si vous ne tenez pas `
a vous prendre la tˆete pour choisir le type de chaque variable, demandez-vous ce que vous
allez mettre dedans ! Si vous tenez `
a y mettre une valeur qui se trouve dans une colonne d’une table, il est possible de
vous r´ef´erer directement au type de cette colonne avec le type nomTable.nomColonne%type. Par exemple,
DECLARE
num PRODUIT. numprod%type ;
nom PRODUIT. nomprod%type := ’ Poup´e e Batman ’ ;
BEGIN
SELECT numprod INTO num
FROM PRODUIT
WHERE nomprod = nom ;
DBMS OUTPUT. PUT LINE( ’L ’ ’ a r t i c l e ’ | |
nom | | ’ a pour num´ero ’ | | num ) ;
END;
/
Pour aller plus loin, il est mˆeme possible de d´eclarer une structure pour repr´esenter une ligne d’une table, le type
porte alors le nom suivant : nomTable%rowtype.
DECLARE
nom PRODUIT. nomprod%type := ’ Poup´e e Batman ’ ;
l i g n e PRODUIT%rowtype ;
BEGIN
SELECT ∗ INTO l i g n e
FROM PRODUIT
WHERE nomprod = nom ;
DBMS OUTPUT. PUT LINE( ’L ’ ’ a r t i c l e ’ | |
l i g n e . nomprod | | ’ a pour num´ero ’ | | l i g n e . numprod ) ;
END;
/

36

1.11.3

Transactions

Un des m´ecanismes les plus puissants des SGBD r´ecents r´eside dans le syst`eme des transactions. Une transaction
est un ensemble d’op´erations “atomiques”, c’est-`
a-dire indivisible. Nous consid´ererons qu’un ensemble d’op´erations est
indivisible si une ex´ecution partielle de ces instructions poserait des probl`emes d’int´egrit´e dans la base de donn´ees.
Par exemple, dans le cas d’une base de donn´ees de gestion de comptes en banque, un virement d’un compte `a un autre
se fait en deux temps : cr´editer un compte d’une somme s, et d´ebiter un autre de la mˆeme somme s. Si une erreur
survient pendant la deuxi`eme op´eration, et que la transaction est interrompue, le virement est incomplet et le patron
va vous assassiner.
Il convient donc de disposer d’un m´ecanisme permettant de se prot´eger de ce genre de d´esagr´ement. Plutˆ
ot que
se casser la tˆete `
a tester les erreurs `
a chaque ´etape et `a balancer des instructions permettant de “revenir en arri`ere”,
nous allons utiliser les instructions COMMIT et ROLLBACK.
Voici le squelette d’un exemple :
/∗ i n s t r u c t i o n s ∗/
IF /∗ e r r e u r ∗/ THEN
ROLLBACK;
ELSE
COMMIT;
END;
Le ROLLBACK annule toutes les modifications faites depuis le d´ebut de la transaction (donc depuis le pr´ec´edent
COMMIT), COMMIT les enregistre d´efinitivement dans la base de donn´ees.
La variable d’environnement AUTOCOMMIT, qui peut ˆetre positionn´ee `a ON ou `a OFF permet d’activer la gestion des
transactions. Si elle est positionn´ee `
a ON, chaque instruction a des r´epercussions imm´ediates dans la base, sinon, les
modifications ne sont effectives qu’une fois qu’un COMMIT a ´et´e ex´ecut´e.

37

1.12

Exceptions

Le m´ecanisme des exceptions est impl´ement´e dans la plupart des langages r´ecent, notament orient´es objet. Cette
fa¸con de programmer a quelques avantages imm´ediats :
– obliger les programmeurs `
a traiter les erreurs : combien de fois votre prof de C a hurl´e en vous suppliant
de v´erifier les valeurs retourn´ees par un malloc, ou un fopen ? La plupart des compilateurs des langages `
a
exceptions (notamment java) ne compilent que si pour chaque erreur potentielle, vous avez pr´epar´e un bloc de
code (´eventuellement vide...) pour la traiter. Le but est de vous assurer que vous n’avez pas oubli´e d’erreur.
– Rattraper les erreurs en cours d’ex´
ecution : Si vous programmez un syst`eme de s´ecurit´e de centrale
nucl´eaire ou un pilote automatique pour l’aviation civile, une erreur de m´emoire qui vous afficherait l’´ecran
bleu de windows, ou le message “Envoyer le rapport d’erreur ?”, ou plus simplement le fameux “Segmentation
fault” produirait un effet des plus mauvais. Certaines erreurs d’´execution sont rattrapables, autrement dit, il est
possible de r´esoudre le probl`eme sans interrompre le programme.
– Ecrire le traitement des erreurs `
a part : Pour des raisons fiabilit´e, de lisibilit´e, il a ´et´e consid´er´e que
m´elanger le code “normal” et le traitement des erreurs ´etait un style de programmation perfectible... Dans les
langages `
a exception, les erreurs sont trait´ees `a part.

1.12.1

Rattraper une exception

Je vous ai menti dans le premier cours, un bloc en PL/SQL a la forme suivante :
DECLARE
/∗ d e c l a r a t i o n s ∗/
BEGIN
/∗ i n s t r u c t i o n s ∗/
EXCEPTION
/∗ t r a i t e m e n t d e s e r r e u r s ∗/
END;
Une exception est une “erreur type”, elle porte un nom, au mˆeme titre qu’une variable a une identificateur, par
exemple GLUBARF. Lorsque dans les instructions, l’erreur GLUBARF se produit, le code du BEGIN s’interrompt et le
code de la section EXCEPTION est lanc´e. On dit aussi que quand une exception est lev´
ee (raised) (on dit aussi jet´
ee
(thrown)), on la rattrape (catch) dans le bloc EXCEPTION. La section EXCEPTION a la forme suivante :
EXCEPTION
WHEN E1 THEN
/∗ t r a i t e m e n t
WHEN E2 THEN
/∗ t r a i t e m e n t
WHEN E3 THEN
/∗ t r a i t e m e n t
WHEN OTHERS THEN
/∗ t r a i t e m e n t
END;

∗/
∗/
∗/
∗/

On ´enum`ere les erreurs les plus pertinentes en utilisant leur nom et en consacrant `a chacune d’elle un traitement
particulier pour rattraper (ou propager) l’erreur. Quand un bloc est trait´e, les WHEN suivants ne sont pas ´evalu´es.
OTHERS est l’exception par d´efaut, OTHERS est toujours v´erifi´e, sauf si un cas pr´ec´edent a ´et´e v´erifi´e. Dans l’exemple
suivant :
DECLARE
/∗ d e c l a r a t i o n s ∗/
BEGIN
/∗ i n s t r u c t i o n s ∗/
COMMIT;
EXCEPTION
WHEN GLUBARF THEN
ROLLBACK;
DBMS OUTPUT. PUT LINE( ’GLUBARF e x c e p t i o n r a i s e d ! ’ ) ;
WHEN OTHERS THEN
DBMS OUTPUT. PUT LINE( ’SQLCODE = ’ | | SQLCODE) ;
DBMS OUTPUT. PUT LINE( ’SQLERRM = ’ | | SQLERRM) ;
END;
38

Les deux variables globales SQLCODE et SQLERRM contiennent respectivement le code d’erreur Oracle et un message
d’erreur correspondant `
a la derni`ere exception lev´ee. Chaque exception a donc, en plus d’un nom, un code et un
message.

1.12.2

Exceptions pr´
ed´
efinies

Bon nombre d’exceptions sont pr´ed´efinies par Oracle, par exemple
– NO DATA FOUND est lev´ee quand la requˆete d’une instruction de la forme SELECT ... INTO ... ne retourne
aucune ligne
– TOO MANY ROWS est lev´ee quand la requˆete d’une instruction de la forme SELECT ... INTO ... retourne plusieurs
lignes
– DUP VAL ON INDEX est lev´ee si une insertion (ou une modification) est refus´ee `a cause d’une contrainte d’unicit´e.
On peut enrichir notre exemple de la sorte :
DECLARE
num NUMBER;
nom VARCHAR2( 3 0 ) := ’ Poup´e e Batman ’ ;
BEGIN
SELECT numprod INTO num
FROM PRODUIT
WHERE nomprod = nom ;
DBMS OUTPUT. PUT LINE( ’L ’ ’ a r t i c l e ’ | |
nom | | ’ a pour num´ero ’ | | num ) ;
EXCEPTION
WHEN NO DATA FOUND THEN
DBMS OUTPUT. PUT LINE( ’ Aucun a r t i c l e ne p o r t e l e nom ’
| | nom ) ;
WHEN TOO MANY ROWS THEN
DBMS OUTPUT. PUT LINE( ’ P l u s i e u r s a r t i c l e s p o r t e n t l e nom ’
| | nom ) ;
WHEN OTHERS THEN
DBMS OUTPUT. PUT LINE( ’ I l y a un g r o s probl`e me . . . ’ ) ;
END;
/
SELECT numprod INTO num... l`eve une exception si la requˆete renvoie un nombre de lignes diff´erent de 1.

1.12.3

Codes d’erreur

Je vous encore menti, certaines exceptions n’ont pas de nom. Elle ont seulement un code d’erreur, il est conseill´e
de se reporter `
a la documentation pour les obtenir. On les traite de la fa¸con suivante
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE = CODE1 THEN
/∗ t r a i t e m e n t ∗/
ELSIF SQLCODE = CODE2 THEN
/∗ t r a i t e m e n t ∗/
ELSE
DBMS OUTPUT. PUT LINE( ’ J ’ ’ v o i s pas c ’ ’ que ca
peut e t r e . . . ’ ) ;
END;
C’est souvent le cas lors de violation de contraintes.

1.12.4


eclarer et lancer ses propres exceptions

Exception est un type, on d´eclare donc les exceptions dans une section DECLARE. Une exception se lance avec
l’instruction RAISE. Par exemple,
DECLARE
GLUBARF EXCEPTION;

39

BEGIN
RAISE GLUBARF;
EXCEPTION
WHEN GLUBARF THEN
DBMS OUTPUT. PUT LINE( ’ g l u b a r f r a i s e d . ’ ) ;
END;
/

40

1.13

Sous-programmes

1.13.1

Proc´
edures

Syntaxe
On d´efinit une proc´edure de la sorte
CREATE OR REPLACE PROCEDURE /∗ nom ∗/ ( /∗ p a r a m e t r e s ∗/ ) IS
/∗ d e c l a r a t i o n d e s v a r i a b l e s l o c a l e s ∗/
BEGIN
/∗ i n s t r u c t i o n s ∗/
END;
les param`etres sont une simple liste de couples nom type. Par exemple, la procedure suivante affiche un compte `
a
rebours.
CREATE OR REPLACE PROCEDURE compteARebours ( n NUMBER) IS
BEGIN
IF n >= 0 THEN
DBMS OUTPUT. PUT LINE( n ) ;
compteARebours ( n − 1 ) ;
END IF ;
END;
Invocation
En PL/SQL, une proc´edure s’invoque tout simplement avec son nom. Mais sous SQL+, on doit utiliser le mot-cl´e
CALL. Par exemple, on invoque le compte `
a rebours sous SQL+ avec la commande CALL compteARebours(20).
Passage de param`
etres
Oracle permet le passage de param`etres par r´ef´erence. Il existe trois types de passage de param`etres :
– IN : passage par valeur
– OUT : aucune valeur pass´ee, sert de valeur de retour
– IN OUT : passage de param`etre par r´ef´erence
Par d´efaut, le passage de param`etre se fait de type IN.
CREATE OR REPLACE PROCEDURE i n c r ( v a l IN OUT NUMBER) IS
BEGIN
v a l := v a l + 1 ;
END;

1.13.2

Fonctions

Syntaxe
On cr´ee une nouvelle fonction de la fa¸con suivante :
CREATE OR REPLACE FUNCTION /∗ nom ∗/ ( /∗ p a r a m e t r e s ∗/ ) RETURN /∗ t y p e
∗/ IS
/∗ d e c l a r a t i o n d e s v a r i a b l e s l o c a l e s ∗/
BEGIN
/∗ i n s t r u c t i o n s ∗/
END;
L’instruction RETURN sert `
a retourner une valeur. Par exemple,
CREATE OR REPLACE FUNCTION module ( a NUMBER, b NUMBER) RETURN NUMBER IS
BEGIN
IF a < b THEN
RETURN a ;
ELSE
RETURN module ( a − b , b ) ;

41

END IF ;
END;
Invocation
Tout comme les proc´edures, l’invocation des fonctions ne pose aucun probl`eme en PL/SQL, par contre, sous SQL+,
c’est quelque peu particulier. On passe par une pseudo-table nomm´ee DUAL de la fa¸con suivante :
SELECT module ( 2 1 , 1 2 ) FROM DUAL;
Passage de param`
etres
Les param`etres sont toujours pass´es avec le type IN.

42

1.14

Curseurs

1.14.1

Introduction

Les instructions de type SELECT ... INTO ... manquent de souplesse, elles ne fontionnent que sur des requˆetes
retourant une et une seule valeur. Ne serait-il pas int´eressant de pouvoir placer dans des variables le r´esultat d’une
requˆete retournant plusieurs lignes ? A m´editer...

1.14.2

Les curseurs

Un curseur est un objet contenant le r´esultat d’une requˆete (0, 1 ou plusieurs lignes).

eclaration
Un curseur se d´eclare dans une section DECLARE :
CURSOR /∗ nomcurseur ∗/ IS /∗ r e q u ˆe t e ∗/ ;
Par exemple, si on tient `
a r´ecup´erer tous les employ´es de la table EMP, on d´eclare le curseur suivant.
CURSOR emp cur IS
SELECT ∗ FROM EMP;
Ouverture
Lors de l’ouverture d’un curseur, la requˆete du curseur est ´evalu´ee, et le curseur contient toutes les donn´ees
retourn´ees par la requˆete. On ouvre un curseur dans une section BEGIN :
OPEN /∗ nomcurseur ∗/ ;
Par exemmple,
DECLARE
CURSOR emp cur IS
SELECT ∗ FROM EMP;
BEGIN
OPEN emp cur ;
/∗ U t i l i s a t i o n du c u r s e u r ∗/
END;
Lecture d’une ligne
Une fois ouvert, le curseur contient toutes les lignes du r´esultat de la requˆete On les r´ecup`ere une par une en
utilisant le mot-cl´e FETCH :
FETCH /∗ nom curseur

∗/ INTO /∗ l i s t e v a r i a b l e s ∗/ ;

La liste de variables peut ˆetre remplac´ee par une structure de type nom curseur%ROWTYPE. Si la lecture de la ligne
´echoue, parce qu’il n’y a plus de ligne `
a lire, l’attribut %NOTFOUND prend la valeur vrai.
DECLARE
CURSOR emp cur IS
SELECT ∗ FROM EMP;
l i g n e emp cur%rowtype
BEGIN
OPEN emp cur ;
LOOP
FETCH emp cur INTO l i g n e ;
EXIT WHEN emp cur%NOTFOUND;
DBMS OUTPUT. PUT LINE( l i g n e . ename ) ;
END LOOP;
/∗ . . . ∗/
END;

43

Fermeture
Apr`es utilisation, il convient de fermer le curseur.
CLOSE /∗ nomcurseur ∗/ ;
Compl´etons notre exemple,
DECLARE
CURSOR emp cur IS
SELECT ∗ FROM EMP;
l i g n e emp cur%rowtype ;
BEGIN
OPEN emp cur ;
LOOP
FETCH emp cur INTO l i g n e ;
EXIT WHEN emp cur%NOTFOUND;
DBMS OUTPUT. PUT LINE( l i g n e . ename ) ;
END LOOP;
CLOSE emp cur ;
END;
/
Le programme ci-dessus peut aussi s’´ecrire
DECLARE
CURSOR emp cur IS
SELECT ∗ FROM EMP;
l i g n e emp cur%rowtype ;
BEGIN
OPEN emp cur ;
FETCH emp cur INTO l i g n e ;
WHILE emp cur%FOUND LOOP
DBMS OUTPUT. PUT LINE( l i g n e . ename ) ;
FETCH emp cur INTO l i g n e ;
END LOOP;
CLOSE emp cur ;
END;
Boucle FOR
Il existe une boucle FOR se chargeant de l’ouverture, de la lecture des lignes du curseur et de sa fermeture,
FOR l i g n e IN emp cur LOOP
/∗ Traitement ∗/
END LOOP;
Par exemple,
DECLARE
CURSOR emp cur IS
SELECT ∗ FROM EMP;
l i g n e emp cur%rowtype ;
BEGIN
FOR l i g n e IN emp cur LOOP
DBMS OUTPUT. PUT LINE( l i g n e . ename ) ;
END LOOP;
END;
/

44

1.15

Curseurs parametr´
es

1.15.1

Introduction

A votre avis, le code suivant est-il valide ?
DECLARE
NUMBER n := 1 4 ;
BEGIN
DECLARE
CURSOR C IS
SELECT ∗
FROM PERSONNE
WHERE numpers >= n ;
ROW C%rowType ;
BEGIN
FOR ROW IN C LOOP
DBMS OUTPUT. PUT LINE(ROW. numpers ) ;
END LOOP;
END;
END;
/
R´eponse : non. La requˆete d’un curseur ne peut pas contenir de variables dont les valeurs ne sont pas fix´ees.
Pourquoi ? Parce que les valeurs des ces sont susceptibles de changer entre la d´eclaration du curseur et son ouverture.
Le rem`ede est un curseur param´etr´e.

1.15.2


efinition

Un curseur param´etr´e est un curseur dont la requˆete contient des variables dont les valeurs ne seront fix´ees qu’`
a
l’ouverture.

1.15.3


eclaration

On pr´ecise la liste des noms et des type des param`etres entre parenth`eses apr`es le nom du curseur :
CURSOR /∗ nom ∗/ ( /∗ l i s t e d e s p a r a m`e t r e s ∗/ ) IS
/∗ r e q u ˆe t e ∗/
Par exemple, cr´eeons une requˆete qui, pour une personne donn´ee, nous donne la liste des noms et pr´enoms de ses
enfants :
CURSOR e n f a n t s ( numparent NUMBER) IS
SELECT ∗
FROM PERSONNE
WHERE p e r e = numparent
OR mere = numparent ;

1.15.4

Ouverture

On ouvre un curseur param´etr´e en passant en param`etre les valeurs des variables :
OPEN /∗ nom ∗/ ( /∗ l i s t e d e s p a r a m`e t r e s ∗/ )
Par exemple,
OPEN e n f a n t s ( 1 ) ;

1.15.5

Lecture d’une ligne, fermeture

la lecture d’une ligne suit les mˆemes r`egles qu’avec un curseur non param´etr´e.

45

1.15.6

Boucle pour

La boucle pour se charge de l’ouverture, il convient donc de placer les param`etre dans l’entˆete de la boucle,
FOR /∗ v a r i a b l e ∗/ IN /∗ nom ∗/ ( /∗ l i s t e p a r a m`e t r e s ∗/ ) LOOP
/∗ i n s t r u c t i o n s ∗/
END LOOP;
Par exemple,
FOR e IN e n f a n t s ( 1 ) LOOP
DBMS OUTPUT. PUT LINE( e . nompers | |
END LOOP;

1.15.7

’ ’ | | e . prenompers ) ;

Exemple r´
ecapitulatif

DECLARE
CURSOR p a r e n t IS
SELECT ∗
FROM PERSONNE;
p p a r e n t%rowtype ;
CURSOR e n f a n t s ( numparent NUMBER) IS
SELECT ∗
FROM PERSONNE
WHERE p e r e = numparent
OR mere = numparent ;
e e n f a n t s%rowtype ;
BEGIN
FOR p IN p a r e n t LOOP
DBMS OUTPUT. PUT LINE( ’ Les e n f a n t s de ’ | | p . prenom | |
’ ’ | | p . nom | | ’ s o n t : ’ ) ;
FOR e IN e n f a n t s ( p . numpers ) LOOP
DBMS OUTPUT. PUT LINE( ’ ∗ ’ | | e . prenom
| | ’ ’ | | e . nom
);
END LOOP;
END LOOP;
END;
/

46

1.16

Triggers

1.16.1

Principe

Un trigger est une proc´edure stock´ee qui se lance automatiquement lorsqu’un ´ev´enement se produit. Par ´ev´enement,
on entend dans ce cours toute modification des donn´ees se trouvant dans les tables. On s’en sert pour contrˆ
oler ou
appliquer des contraintes qu’il est impossible de formuler de fa¸con d´eclarative.

1.16.2

Classification

Type d’´
ev´
enement
Lors de la cr´eation d’un trigger, il convient de pr´eciser quel est le type d’´ev´enement qui le d´eclenche. Nous r´ealiserons
dans ce cours des triggers pour les ´ev´enements suivants :
– INSERT
– DELETE
– UPDATE
Moment de l’´
execution
On pr´ecise aussi si le trigger doit ˆetre ´execut´e avant (BEFORE) ou apr`es (AFTER) l’´ev´enement.
Ev´
enements non atomiques
Lors que l’on fait un DELETE ..., il y a une seule instruction, mais plusieurs lignes sont affect´ees. Le trigger doit-il
ˆetre ex´ecut´e pour chaque ligne affect´ee (FOR EACH ROW), ou seulement une fois pour toute l’instruction (STATEMENT) ?
– un FOR EACH ROW TRIGGER est ex´ecut´e `
a chaque fois qu’une ligne est affect´ee.
– un STATEMENT TRIGGER est ´execut´ee `
a chaque fois qu’une instruction est lanc´ee.

1.16.3

Cr´
eation

Syntaxe
On d´eclare un trigger avec l’instruction suivante :
CREATE OR REPLACE TRIGGER n o m t r i g g e r
[BEFORE | AFTER] [INSERT | DELETE | UPDATE] ON nomtable
[FOR EACH ROW | ]
DECLARE
/∗ d e c l a r a t i o n s ∗/
BEGIN
/∗ i n s t r u c t i o n s ∗/
END;
Par exemple,
SQL>
2
3
4
5
6

CREATE OR REPLACE TRIGGER p a s D e D e l e t e D a n s C l i e n t
BEFORE DELETE ON CLIENT
BEGIN
RAISE APPLICATION ERROR( −20555 , ’Va t e f a i r e . . . ’ ) ;
END;
/

D´e c l e n c h e u r c r ´e ´e .
SQL> SELECT COUNT( ∗ )
2 FROM CLIENT ;
COUNT( ∗ )
−−−−−−−−−−
21
SQL> DELETE FROM CLIENT ;

47

DELETE FROM CLIENT

ERREUR `
a la ligne 1 :
ORA−20555: Va t e f a i r e . . .
ORA−06512: `
a ”SCOTT.PASDEDELETEDANSCLIENT” , l i g n e 2
ORA−04088: e r r e u r l o r s d e x´e c u t i o n du d´e c l e n c h e u r ’SCOTT.PASDEDELETEDANSCLIENT ’

SQL> SELECT COUNT( ∗ )
2 FROM CLIENT ;
COUNT( ∗ )
−−−−−−−−−−
21
L’instruction RAISE APPLICATION ERROR(code, message) l`eve une exception sans nom portant un code code et
un message d’erreur message. Vous remarquez que comme l’erreur a ´et´e lev´ee avant la suppression, les donn´ees sont
toujours pr´esentes dans la table CLIENT. Le trigger a contrˆol´e une r`egle, et comme elle n’´etait pas respect´ee, il a lanc´e
une erreur.
Combinaisons d’´
ev´
enements
Il est possible, en s´eparant les types d’´ev´enement par le mot-cl´e OR, de d´efinir un trigger d´eclench´e par plusieurs
´ev´enements. Les variables bool´eennes INSERTING, UPDATING et DELETING permettent d’identifier l’´ev´enement qui a
d´eclench´e le trigger.
CREATE OR REPLACE TRIGGER a f f i c h e E v e n e m e n t
BEFORE INSERT OR UPDATE OR DELETE ON CLIENT
FOR EACH ROW
BEGIN
IF INSERTING THEN
DBMS OUTPUT. PUT LINE( ’ I n s e r t i o n dans CLIENT ’ ) ;
ELSIF UPDATING THEN
DBMS OUTPUT. PUT LINE( ’ Mise a j o u r dans CLIENT ’ ) ;
ELSE
DBMS OUTPUT. PUT LINE( ’ S u p p r e s s i o n dans CLIENT ’ ) ;
END IF ;
END;

1.16.4

Acc`
es aux lignes en cours de modification

Dans les FOR EACH ROW triggers, il est possible avant la modification de chaque ligne, de lire l’ancienne ligne et la
nouvelle ligne par l’interm´ediaire des deux variables structur´ees :old et :new. Par exemple le trigger suivant empˆeche
de diminuer un salaire :
CREATE OR REPLACE TRIGGER p a s D e B a i s s e D e S a l a i r e
BEFORE UPDATE ON EMP
FOR EACH ROW
BEGIN
IF ( : o l d . s a l > : new . s a l ) THEN
RAISE APPLICATION ERROR( −20567 ,
’ Pas de b a i s s e de s a l a i r e ! ’ ) ;
END IF ;
END;
Tables en mutation
Il est impossible, dans un trigger de type FOR EACH ROW de faire un SELECT sur la table en cours de modification.
SQL> CREATE OR REPLACE TRIGGER b e f o r e S t a t e m e n t
2 BEFORE UPDATE ON CLIENT

48


Documents similaires


Fichier PDF oracle et sql 2 exercices corriges pour lp et mastere
Fichier PDF serie bd
Fichier PDF examencorrige sgbd plsql
Fichier PDF psppesymfony
Fichier PDF create or replace trigger triggeer
Fichier PDF tp4mysqlfinal


Sur le même sujet..