M1idsm Bda

Bases de données avancées
Master 1 IDSM-Kharkiv
2020-2021
Jérôme Darmont
http://eric.univ-lyon2.fr/jdarmont/
Actualité du cours
http://eric.univ-lyon2.fr/jdarmont/?page_id=3604
http://eric.univ-lyon2.fr/jdarmont/?feed=rss2
https://twitter.com/darmont_lyon2 #idsmbda
Bases de données avancées http://eric.univ-lyon2.fr/jdarmont/ 2

Introduction

Définition
Base de données (BD) : Collection de données

cohérentes et structurées
≠
Base de données
Fichiers
Organisation en fichiers
Saisie Traitement Fichier
Fichier
État de
Saisie Traitement sortie

Organisation en BD
Saisie
Base
+ de Traitements
données
Contrôles
États de
sortie

Avantages de l’organisation en BD
Uniformisation de la saisie
Standardisation des traitements
Contrôle de la validité des données
Partage de données entre plusieurs traitements

Qu’est-ce qu’un SGBD ?
Système de Gestion de Bases de Données :

Logiciel(s) assurant structuration, stockage,
maintenance, mise à jour et consultation des
données d’une BD
Exemples
– SGBD « bureautiques » : Access, Base, Filemaker, Paradox
– SGBD serveurs : Oracle, DB2, SQL Server, PostgreSQL,
MySQL, MariaDB…

Processus de conception d’une BD
Probléma- Indépendant d’un système

tique
de gestion de BD (SGBD)
Cahier des charges
Spécifica-
tions
Rédaction
Modèle Spécifique
conceptuel
Analyse Famille de SGBD
Modèle
logique
Traduction SGBD particulier
Modèle
physique
Traduction
Plan du 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

Partie 1
Modélisation conceptuelle

Modèle conceptuel UML
Standard de l’Object Management Group
Ensemble de formalismes graphiques
Diagramme de classes

Classes et attributs
Classe : Groupe d’entités du monde réel ayant les mêmes

caractéristiques et le même comportement
ex. ETUDIANT
Attribut : Propriété de la classe

ex. Nom et Prénom de l’étudiant·e
Représentation graphique :

Types des attributs
Type d’attribut :
– Nombre entier (Entier)
– Nombre réel (Réel)
– Chaîne de caractères (Chaîne)
– Date (Date)

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 ?

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

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 :
NB : Ne pas confondre avec
les attributs de classe UML
dont c’est la notation usuelle

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).
Associations 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 (+).

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 De M à N (M, N entiers)
ex. 4..10 (de 4 à 10)

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 ».

Associations 1-N
ex. Une épreuve relève d’une et une seule matière.

Une matière peut donner lieu à plusieurs épreuves.
NB : La multiplicité un à plusieurs (1..*) peut aussi

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

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.
NB : La multiplicité un à plusieurs (1..*) peut aussi

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

Associations M-N
ex. Un·e étudiant·e peut passer plusieurs épreuves.

Une épreuve peut être passée par plusieurs
étudiant·es.
NB : Les multiplicités un à plusieurs (1..*)

peuvent aussi être zéro à plusieurs (0..* ou *).
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.
NB : Une classe-association est une association, pas une

classe.
Exemple : Spécifications (1/2)
Les étudiant·es sont caractérisé·es par un numéro unique, leur

nom, prénom, date de naissance, rue, code postal et ville.
Les étudiant·es possèdent une carte Izly caractérisée par un

numéro unique et un solde d’argent utilisable au CROUS.
Selon qu’ils ou elles sont dispensé·es ou non d’assiduité, les

étudiant·es appartiennent à un groupe de TD caractérisé par un
code unique.

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.
Chaque épreuve relève d'une matière unique (mais une matière

donnée peut donner lieu à plusieurs épreuves).
Les matières sont caractérisées par un code unique et un intitulé.

Démarche de modélisation conceptuelle
1. Identifier les classes

2. Identifier les associations 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 multiplicités des associations

Exemple : Diagramme de classes

Partie 2
Modélisation logique

Modèle logique relationnel
Modèle associé aux SGBD relationnels

(ex. Oracle, SQL Server, DB2, PostgreSQL, MariaDB, MySQL…)
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

Caractéristiques des SGBD 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
LDD intégré au LMD
Grande dynamique de structure
Optimisation de requêtes
Utilisation interactive ou à partir d’un langage hôte
Modèle
Relations et attributs relationnel
Une relation R est un ensemble d’attributs {A1, A2, …, An}.

ex. La relation EPREUVE est l’ensemble des attributs
{CodeEpr, DateEpr, Lieu}.
Chaque attribut Ai prend ses valeurs dans un domaine

dom(Ai).
ex. Note ∈ [0, 20]
Lieu ∈ {'Amphi Say', 'Amphi Aubrac', 'Salle D101', …}

Modèle
N-uplets relationnel
Notation d’une relation : R (A1, A2, …, An)

ex. EPREUVE (CodeEpr, DateEpr, Lieu)
Un n-uplet t est un ensemble de valeurs t = <V1, V2, …, Vn>

où Vi ∈ dom(Ai) ou bien Vi est la valeur nulle (NULL).
ex. <'InfoS2', '30-06-2016', 'Amphi Aubrac'> est un n-uplet

de la relation EPREUVE.

Modèle
Contraintes d’intégrité (1/2) 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

Modèle
Contraintes d’intégrité (2/2) 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 ≤ 20

Modèle
Contraintes d’intégrité en pratique 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

Traduction UML-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)

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_IZLY (NumCarte, SoldeCROUS)
ETUDIANT (NumEtu, Nom, Prénom, DateNaiss, Rue,

CP, Ville, NumCarte#)

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é)

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)

Modèle
Exemple : Modèle logique relationnel relationnel
CARTE_IZLY (NumCarte, SoldeCROUS)
GROUPE_TD (CodeGroupe)
ETUDIANT (NumEtu, Nom, Prénom, DateNaiss,

Rue, CP, Ville, NumCarte#, CodeGroupe#)
EPREUVE (CodeEpr, DateEpr, Lieu, CodeMat#)
PASSER (NumEtu#, CodeEpr#, Note)

Modèle
Traduction d’une association M-N relationnel
ETUDIANT
NumEtu Nom Prénom
1110 Dupont Albertine PASSER (table « pont »)

2002 West James NumEtu# CodeEpr# Note
1110 INFOS101 15,5

EPREUVE
2002 ECOS101 8,5
CodeEpr DateEpr Lieu
2002 ECOS102 13
ECOS101 15/01/2016 Aubrac
1110 GESS201 14
ECOS102 16/01/2016 Aubrac
2002 GESS201 14,5
GESS201 25/05/2016 D201
INFOS101 20/01/2016 D101
Modèle
Problème de la redondance 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.

Modèle
Anomalies liées à la redondance relationnel
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° 2002 fait perdre
toutes les informations concernant l’épreuve ECOS102.

Éviter la redondance
Pourquoi ?
– Suppression des problèmes de mise à jour
– Minimisation de l’espace de stockage
Comment ?
– Dans 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 !

Partie 3
Interrogation
et manipulation
de bases de données
Modèle
Qu’est-ce que l’algèbre relationnelle ? 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.

Modèle
Opérateurs ensemblistes (1/5) relationnel
Union : T = R ∪ S (notation algébrique)

ou T = UNION (R, S) (notation fonctionnelle)
R et S doivent avoir même schéma.
ex. R et S sont les relations ETUDIANT de deux
formations (ex. anciens M1 Finance et Eco-Société)
fusionnées pour constituer une liste d’émargement
commune.
T
Notation graphique :

Bases de données avancées http://eric.univ-lyon2.fr/jdarmont/ R S 49
Modèle
Intersection : T = R ∩ S
ou T = INTERSECT (R, S)
ex. Permet de trouver les étudiant·es commun·es à
deux formations.
Notation graphique : T

R S
Modèle
Différence : T = R - S
ou T = MINUS (R, S)
ex. Permet de retirer les étudiant·es de la relation S
existant dans la relation R.

R S
Modèle
Produit cartésien : T = R x S
ou T = PRODUCT (R, S)
Associe chaque n-uplet de R à chaque n-uplet de S.
T
x
R S
Modèle
Produit cartésien relationnel
NumEtu Nom CodeEprLieu

ex. 101 E1 X INFO1 Aubrac
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

Modèle
Division : T=R÷S
ou T = DIVISION (R, S)
R (A1, A2, …, An) S (Ap+1, …, An)
T (A1, A2, …, Ap) contient tous les n-uplets tels que leur
concaténation à chacun des n-uplets de S donne
toujours un n-uplet de R.
T

Bases de données avancées http://eric.univ-lyon2.fr/jdarmont/

R S 54
Modèle
Division relationnel
ex.
NumEtu CodeEprNote
101 INFO1 11
101 ECO1 15 CodeEprNote
101 ECO2 12 ÷ INFO1 11
102 ECO1 9 ECO2 12
103 INFO1 11
103 ECO2 12
NumEtu
= 101
103

Modèle
Opérateurs spécifiques (1/3) relationnel
Projection : T = π <A, B, C> (R)

ou T = PROJECT (R / A, B, C)
T ne contient que les attributs A, B et C de R.
ex. Noms et prénoms des étudiant·es.
T
A, B, C
R
Modèle
Restriction : T = σ <C> (R)

ou T = RESTRICT (R / C)
T ne contient que les attributs de R qui satisfont la
condition C.
ex. C = Étudiant·es qui habitent à Lyon.
C
R
Modèle
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.
A B
=
R S
Modèle
Exemple de requête (1/4) relationnel
Notes des étudiant·es en précisant leurs noms

(et pas seulement leurs numéros)
RESULTAT
Nom,
f
CodeEpr, Note
Hh
NumEtu NumEtu
=
ETUDIANT PASSER

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

Modèle
E.NumEtu Nom P.NumEtu CodeEpr Note

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

Modèle
ETUDIANT ▷◁ PASSER
E.NumEtu Nom P.NumEtu 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
Classification des SGBD 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.
Niveau 4 : Systèmes relationnellement pleins.
Permettent la définition des contraintes d’intégrité.

Qu’est-ce 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.
SQL se base sur l’algèbre relationnelle.
Standard depuis 1986.

Types de donné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’

Contraintes d’intégrité LDD
Mot clé CONSTRAINT
Identification par un nom de contrainte
Clé primaire :
PRIMARY KEY (clé)
Clé étrangère :
FOREIGN KEY (clé) REFERENCES table(attribut)
Contrainte de domaine :
CHECK (condition)

Définition des données (1/2) LDD
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) )

Définition des données (2/2) LDD
ex.
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) )

Modifications structurelles (1/2) LDD
Ajout d’attributs
ALTER TABLE nom_table ADD (attribut TYPE, …)
ex. ALTER TABLE Etudiant ADD (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

Modifications structurelles (2/2) LDD
Ajout de contrainte
ALTER TABLE nom_table
ADD CONSTRAINT nom_contrainte définition_contrainte
ex. ALTER TABLE Epreuve
ADD CONSTRAINT LieuValide CHECK (Lieu IN (‘Say’, ‘Aubrac’))
Suppression de contrainte
ALTER TABLE nom_table DROP CONSTRAINT nom_contrainte
ex. ALTER TABLE Epreuve
DROP CONSTRAINT LieuValide

Index LDD
Définition : Structure de données physique permettant

d'accélérer les accès aux données
Exemple : CREATE INDEX IdxNomEtu ON Etudiant (Nom)
NB : La clé primaire d'une relation est

automatiquement indexée.
Vues 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 AS requête
ex. CREATE VIEW lesNoms AS

SELECT Nom, Prenom FROM Etudiant

Intérêt des vues (1/2) LDD
Simplification de l’accès aux données

en masquant les opérations de jointure
ex. CREATE VIEW notesParEtudiant AS
SELECT E.NumEtu, Nom, Prenom, NumEpr, Note
FROM Etudiant E, Passer P
WHERE E.NumEtu = P.NumEtu
SELECT NumEtu, Nom FROM notesParEtudiant

WHERE Note > 10

Intérêt des vues (2/2) LDD
Sauvegarde indirecte de requêtes complexes

Présentation de mêmes 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
Mise à jour via une vue LDD
Le mot clé DISTINCT doit être absent de la requête.
La clause FROM 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.

Vues systèmes LDD
ALL_TABLES (OWNER, TABLE_NAME, …)
ALL_VIEWS (OWNER, VIEW_NAME, …)
ALL_CONSTRAINTS (OWNER, TABLE_NAME, CONSTRAINT_NAME,
CONSTRAINT_TYPE, SEARCH_CONDITION, ...)
ALL_CONS_COLUMNS (OWNER, TABLE_NAME, CONSTRAINT_NAME,
COLUMN_NAME, ...)
USER_CATALOG (TABLE_NAME, TABLE_TYPE)
USER_TAB_COLUMNS (TABLE_NAME, COLUMN_NAME, …)
USER_IND_COLUMNS (INDEX_NAME, TABLE_NAME, COLUMN_NAME, …)
...
USER_CONSTRAINTS (TABLE_NAME, CONSTRAINT_NAME,
CONSTRAINT_TYPE, SEARCH_CONDITION, ...)
Ex. d’interrogation des vues systèmes LDD
Tables qui contiennent un attribut Intitulé

SELECT TABLE_NAME FROM USER_TAB_COLUMNS
WHERE COLUMN_NAME = ‘INTITULE’
Attributs de la table Client

SELECT COLUMN_NAME FROM USER_TAB_COLUMNS
WHERE TABLE_NAME = ‘CLIENT’
Contraintes des tables de l’utilisateur courant

SELECT TABLE_NAME, CONSTRAINT_NAME
FROM ALL_CONSTRAINTS
WHERE OWNER = USER

Mise à jour 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’
WHERE NumEtu = 333333
ex. UPDATE Passer SET Note = Note + 1
Suppression de n-uplets
ex. DELETE FROM Etudiant
WHERE Ville = ‘Lyon’
ex. DELETE FROM Epreuve

Interrogation 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#)
EPREUVE (CodeEpr, DateEpr, Lieu, CodeMat#)
PASSER (NumEtu#, CodeEpr#, Note)
Note : Les symboles [ ] indiquent une clause optionnelle d’une requête

dans les transparents suivants.
Étoile, tri et champ calculé LMD
Tous les n-uplets d’une table : étoile (*)

ex. SELECT * FROM Etudiant
Tri du résultat
ex. Par ordre alphabétique [inverse] de nom
SELECT * FROM Etudiant
ORDER BY Nom [DESC]
Champs calculés
ex. Transformation de notes sur 20 en notes sur 10
SELECT Note / 2 FROM Passer

Projection et restriction LMD
Projection
ex. Noms et Prénoms des étudiant·es, uniquement (pas
les autres attributs)
SELECT Nom, Prénom FROM Etudiant
Suppression des doublons
ex. SELECT DISTINCT Nom FROM Etudiant
Restriction
ex. Étudiant·es qui habitent à Lyon
WHERE Ville = ‘Lyon’

Opérateurs de 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

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

par sur-Saône

WHERE Ville LIKE ‘%sur-Saône’
‘sur-Saône%’ ⇒ commence par sur-Saône

‘%sur%’ ⇒ contient le mot sur

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.

Opérateurs logiques LMD
ET. ex. Épreuves se déroulant le 15/01/2016 en salle D201

SELECT * FROM Epreuve
WHERE DateEpr = ‘15-01-2016’ AND Lieu = ‘D201’
OU. ex. Étudiant·es né·es avant 1990 ou habitant hors Lyon
WHERE DateNaiss < ‘01-01-1990’ OR Ville <> ‘Lyon’
Combinaisons. ex. Étudiant·es né·es après 1990 et habitant
Lyon ou Vienne
WHERE DateNaiss > ‘31-12-1990’
AND (Ville = ‘Lyon’ OR Ville = ‘Vienne’)

Fonctions 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() : somme des valeurs
MIN(), MAX() : valeur minimum, valeur maximum
COUNT() : nombre de valeurs
ex. Moyenne des notes
SELECT AVG(Note) FROM Passer
Fonction COUNT et 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

Exemple 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

Jointure (1/3) LMD
ex. Liste des notes avec le nom des étudiant·es
SELECT Nom, CodeEpr, Note

RESULTAT
FROM Etudiant, Passer
Nom,
f
WHERE Etudiant.NumEtu = Passer.NumEtu
CodeEpr, Note
Hh
NumEtu NumEtu
=
ETUDIANT PASSER

Jointure (2/3) LMD
ex. Idem avec le numéro d'étudiant en plus

SELECT E.NumEtu, Nom, CodeEpr, Note
FROM Etudiant E, Passer P
WHERE E.NumEtu = P.NumEtu
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).

Jointure (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.

Prédicats d’existence LMD
Prédicats EXISTS / NOT EXISTS

ex. Étudiant·es qui ont passé au moins une épreuve
[n’ont passé aucune épreuve]
SELECT * FROM Etudiant E

WHERE [NOT] EXISTS (
SELECT * FROM Passer P
WHERE E.NumEtu = P.NumEtu )

Prédicats de dénombrement LMD
Prédicats ALL / ANY
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 FROM Passer

WHERE Note > ALL [ANY] (
SELECT Note FROM Passer
WHERE NumEtu = 1000 )

Groupement (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

Groupement (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

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

épreuves
NB : 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.
Solution logique LMD
SELECT NumEtu
FROM Etudiant Et
WHERE NOT EXISTS (
SELECT *
FROM Epreuve Ep
WHERE NOT EXISTS (
SELECT *
FROM Passer P
WHERE Et.NumEtu = P.NumEtu
AND P.CodeEpr = Ep.CodeEpr ) )

Solution par comptage LMD
SELECT NumEtu FROM Etudiant E

WHERE ( SELECT COUNT(CodeEpr)
FROM Passer P
WHERE E.NumEtu = P.NumEtu )
= ( SELECT COUNT(*) FROM Epreuve )
ou
SELECT NumEtu FROM Passer
GROUP BY NumEtu
HAVING COUNT(CodeEpr) =
( SELECT COUNT(*) FROM Epreuve )

Opérations 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
Requêtes hiérarchiques (1/5) LMD
Exemple de hiérarchie V é lo
(nomenclature) :
C a d re R oues
Relation associée :
ELEMENT (No_Elt, Dési, Parent#) P neu R ayon s
0 Vélo NULL
1 Cadre 0
2 Roue1 0 6 Rayon11 2
3 Roue2 0 7 Rayon12 2
4 Pneu1 2 8 Rayon13 2
5 Pneu2 3 9 Rayon21 3
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

ex. Idem avec indication du niveau dans la hiérarchie
SELECT LEVEL, Dési FROM Element

Résultat : 1 Velo 3 Rayon12

2 Cadre 3 Rayon13
2 Roue1 2 Roue2
3 Pneu1 3 Pneu2
3 Rayon11 3 Rayon21
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'
Résultat : 1 Velo 3 Rayon12

2 Cadre 3 Rayon13
2 Roue1 2 Roue2
3 Pneu1
3 Rayon11
ex. Nombre d'éléments dans chaque niveau
Il est possible d'utiliser le groupement.
SELECT LEVEL, COUNT(No_Elt)

FROM Element
START WITH Parent IS NULL
GROUP BY LEVEL;

Fonctions SQL (1/2) LMD
– 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
– RTRIM(ch, n) : Troncature à droite
– MOD(m, n) : Reste de m/n
– 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.

Fonctions 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
...
Exemples d’appels de fonctions LMD
SELECT UID, USER FROM DUAL;
SELECT GREATEST(1, 2, 3) FROM DUAL;
SELECT Nom, Prenom,

FLOOR( MONTHS_BETWEEN(SYSDATE, DateNaiss) / 12) Age
FROM Etudiant;
UPDATE Passer SET Note = NVL(Note, 10);

Gestion des transactions LCD
Transaction : ensemble de mises à jour des données

(⇒ modifications structurelles)
Début de transaction : début de la session de travail
ou fin de la transaction précédente
Validation Validation
Connexion ou annulation ou annulation Déconnexion
Transaction 1 Transaction 2 Transaction 3

Contrôle des transactions LCD
Validation (et fin) d’une transaction :

COMMIT
Annulation (et fin) d’une transaction :

ROLLBACK

Utilisateurs LCD
Création
– ex. CREATE USER moi_meme
IDENTIFIED BY mon_mot_de_passe
Suppression
– ex. DROP USER moi_meme CASCADE
Modification
– ex. ALTER USER moi_meme IDENTIFIED BY aaaaa

Privilèges globaux LCD
Droit d'effectuer une action sur les objets de

l'utilisateur seulement
– ex. CREATE TABLE
ALTER INDEX
DROP VIEW
Droit d'effectuer une action dans tous les schémas

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

Privilèges sur les objets BD 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
Rôles LCD
Rôles prédéfinis
– CONNECT : droit de création de tables, vues, synonymes, etc.
– RESOURCE : droit de création de procédures stockées,
déclencheurs, etc.
– DBA : administrateur de la BD
Création de nouveaux rôles

– ex. CREATE ROLE role1

Attribution de privilèges LCD
Transmission de privilèges
GRANT privilège ON table|vue
TO user|PUBLIC [WITH GRANT OPTION]
Privilèges sur des objets

– ex. GRANT SELECT ON ma_table TO toto
– ex. GRANT SELECT ON ma_table TO PUBLIC
– ex. GRANT SELECT ON 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
Révocation de privilèges LCD
Suppression de privilèges
REVOKE privilège ON table|vue FROM user|PUBLIC
Privilèges sur des objets

– ex. REVOKE SELECT ON ma_table FROM toto
– ex. REVOKE SELECT ON ma_table FROM PUBLIC
– ex. REVOKE SELECT ON ma_table FROM role1
Privilèges globaux et rôles

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

Tutoriel SQL
Pour approfondir SQL en ligne…
http://eric.univ-lyon2.fr/jdarmont/tutoriel-sql/
Partie 4
Programmation
de bases de données

Requêtes SQL dans 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
(Java Persistence API, PHP Data Objects…)
R
S
Interfaces de niveau appel : intergiciel entre le langage et le SGBD
(ODBC, JDBC, ADO…)
E
U
Procédures stockées : Fonctions SQL stockées dans la base de
données et exécutées par le SGBD R
(écrites en PL/SQL, T-SQL, PL/pgSQL) S

Caractéristiques du langage PL/SQL (1/2)
Langage de 4e génération (L4G = L3G + syntaxe type SQL)
Conçu comme une extension de SQL
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)

Caractéristiques du langage PL/SQL (2/2)
SQL 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)

Architecture d’Oracle
Génie
Réseau
logiciel
SQL
SQL Developer
Serveur
Oracle
PL/SQL
Logiciels
Administration
tiers
Moteur Oracle
Oracle Database PL/SQL

User’s Guide and
Reference

Types de 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

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

Variables et constantes
Déclaration dans la section DECLARE 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;

Types de données

User’s Guide and
Reference

Référencer un type existant
Type d’une autre variable

ex. credit REAL;
debit credit%TYPE;
Type de l’attribut d’une table À utiliser

ex. num_emp EMP.EMPNO%TYPE; au maximum !
Type des n-uplets d’une table

ex. un_etudiant STUDENT%ROWTYPE;

Affectation de variable
Affectation simple
ex. n := 0;
n := n + 1;
Valeur de la base de données

ex. SELECT custname INTO nom_client
FROM customer WHERE custnum = 10;
SELECT ename, sal INTO nom, salaire
FROM emp WHERE empno = 5000;

Opérateurs arithmétiques et logiques
Opérateurs arithmétiques + - / * **
Opérateur de concaténation ||
Opérateurs de comparaison = < > <= >= <>

IS NULL LIKE BETWEEN IN
Opérateurs logiques AND OR NOT

Tests (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;
Tests (2/2)
CASE
CASE variable
WHEN val1 THEN -- Instruction PL/SQL
[ELSE -- Instruction par défaut]
END CASE;
Boucles
Pour
FOR iterateur IN [REVERSE] min..max LOOP
END LOOP;
Tant que
WHILE condition LOOP
END LOOP;
Répéter
LOOP
EXIT WHEN condition;
END LOOP;
Affichage é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 SQL Developer
En cas de dépassement, la taille du tampon d’affichage doit être augmentée.

ex. DBMS_OUTPUT.ENABLE(10000);

Exemple de 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;

Collections
Définition : Ensemble ordonné d’éléments de même type. Chaque

élément est indexé par sa position dans la collection.
Deux types de collections

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

User’s Guide and
Reference

Déclaration 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();
NB : 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.

Affectation de collection
Collection entière
ex. DECLARE 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;

Manipulation de 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) ⇔ EXTEND

Manipulation de 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.
NB : FIRST = 1 et LAST = COUNT dans un tableau.
PRIOR(i) et NEXT(i) renvoient respectivement l’index de l’élément

précédent et de l’élément suivant du ie élément.

Exemple de manipulation de collection
DECLARE
TYPE ListeEntiers IS TABLE OF INTEGER;
pile ListeEntiers := ListeEntiers();
element INTEGER;
BEGIN
-- On empile les valeurs 1 et 11
pile.EXTEND;
pile(pile.COUNT) := 1;
pile.EXTEND;
pile(pile.COUNT) := 11;
-- On dépile
element := pile(pile.COUNT); -- element = 11
pile.TRIM; -- Suppression en haut de pile

Enregistrements
Définition : Ensemble de données liées stockées dans

des champs.
1. Déclarer un type enregistrement

ex. TYPE Etudiant IS RECORD(
numetu INTEGER,
nom VARCHAR(50),
age INTEGER );
2. Déclarer un enregistrement
ex. un_etudiant Edutiant;

Affectation d’enregistrement
Référence directe
ex. un_etudiant.numetu := 12212478;
un_etudiant.nom := 'Toto';
un_etudiant.age := 6;
un_etudiant := mon_etudiant; -- équivalent à Etudiant%ROWTYPE
Résultat de requête
ex. SELECT student_number, student_name, student_age
INTO un_etudiant
FROM student
WHERE student_number = 12212478;

Procédures
PROCEDURE nom_proc (param1, param2…) IS

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

Fonctions
FUNCTION nom_fonction (param1, param2…)

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

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 DECLARE d’un bloc PL/SQL
Définition et mode de passage des paramètres

nom_param [IN | OUT | IN OUT] 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)

Exemple de 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;

Exemple de fonction (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;

Appel de sous-programmes
-- Exemple
DECLARE
hundredBucks CONSTANT REAL := 100;
resEuro REAL;
fact10 INTEGER;
BEGIN
Conversion_USD_EUR(hundredBucks, resEuro);
fact10 := facto(10);
END;

Création de curseur
Définition : Structure de données qui stocke le résultat d’une

requête retournant plusieurs n-uplets.
Déclaration : CURSOR nom_curseur IS requete_SQL;

ex. CURSOR calc_TVA IS
SELECT prod_num, price * 1.2 AS prix_TTC
FROM product;
NB : Les n-uplets du curseur sont de type calc_TVA%ROWTYPE.

Curseur 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);
END LOOP;
Curseur 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
FETCH calc_TVA INTO nuplet; -- Ligne suivante
END LOOP;
CLOSE calc_TVA;
END;

Attributs des curseurs
%NOTFOUND est égal à FALSE si FETCH renvoie un résultat.
%FOUND est égal à TRUE si FETCH renvoie un résultat.
%ROWCOUNT renvoie le nombre de n-uplets lus.
%ISOPEN est égal à TRUE si le curseur est ouvert.

Curseur 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(nuplet.ename || ' : ' || nuplet.sal);
FETCH c INTO nuplet;
END LOOP;
CLOSE c;
END;
Exceptions
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

– SQLCODE : Code de la dernière exception levée
– SQLERRM : Message d’erreur associé
Exceptions 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
Exceptions personnalisées
Déclaration (section DECLARE)

nom_exception EXCEPTION;
Lever l’exception (section BEGIN)

IF condition THEN
RAISE nom_exception;
END IF;
Gérer l’exception (section EXCEPTION)

WHEN nom_exception THEN -- Instruction(s) PL/SQL ;

Exemple 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

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);
END;
Procédures stockées
Définition : Procédures précompilées stockées de manière
permanente dans la base de données
Création
CREATE PROCEDURE nom_proc (paramètres) AS ...
ex. CREATE PROCEDURE HelloWorld AS
BEGIN
DBMS_OUTPUT.PUT_LINE('Hello World!');
END;
Exécution sous SQL Developer en PL/SQL
ex. EXECUTE HelloWorld HelloWorld;
Suppression
ex. DROP PROCEDURE HelloWorld;

Paquetages
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)

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;

Exemple de spécification de paquetage
CREATE OR REPLACE PACKAGE Employes AS

TYPE nuplet IS RECORD (ename emp.ename%TYPE,
salary emp.sal%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;

Exemple de corps 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;

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.

Principaux types de déclencheurs
Insertion Deletion Update
Before 1 2 3
After 4 5 6

Définition de 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

Variables spécifiques aux déclencheurs
:NEW.nom_attribut : Valeur d’un attribut après mise à jour

ex. INSERT INTO client (1, 'NouveauClient');
:NEW.NumCli prend la valeur 1 dans le déclencheur.
:NEW.Nom prend la valeur 'NouveauClient' dans le déclencheur.
:OLD.nom_attribut : Valeur d’un attribut avant mise à jour

ex. DELETE FROM client WHERE NumCli = 33;
:OLD.NumCli prend la valeur 33 dans le déclencheur.

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 :NEW.NumCli IS NULL THEN
RAISE cle_nulle;
END IF;
Exemple de déclencheur (2/2)
-- La clé existe-t-elle déjà ?
SELECT COUNT(NumCli) INTO n FROM client
WHERE NumCli = :NEW.NumCli;
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 !');
END;
SQL statique vs. SQL 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 nom est un
paramètre
⇒ SQL dynamique (la requête complète n’est pas connue à la
compilation)
Définition du SQL dynamique :

Construction d’une requête SQL à la volée dans un bloc PL/SQL

Requête 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.
NB : Les requêtes qui altèrent la structure de la base de données

(CREATE, DROP, ALTER…), même statiques, doivent être exécutées
en mode dynamique.

Exemples de requêtes dynamiques
DECLARE
requete VARCHAR(250);
nom_table CHAR(4) := 'dept';
numdep dept.deptno%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;

Curseurs dynamiques
DECLARE -- Exemple
TYPE CursDyn IS REF CURSOR; -- Pointeur vers un curseur
emp_cv CursDyn; -- Curseur dynamique
nom emp.ename%TYPE;
salaire emp.sal%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
FETCH emp_cv INTO nom, salaire;
END LOOP;
CLOSE emp_cv;
END;
Кінець

M1idsm Bda

Transféré par

Droits d'auteur :

Formats disponibles

M1idsm Bda

Transféré par

Informations du document

Titre original

Copyright

Formats disponibles

Partager ce document

Partager ou intégrer le document

Options de partage

Avez-vous trouvé ce document utile ?

Ce contenu est-il inapproprié ?

Droits d'auteur :

Formats disponibles

M1idsm Bda

Transféré par

Droits d'auteur :

Formats disponibles

Bases de données avancées

Bases de données avancées http://eric.univ-lyon2.fr/jdarmont/ 2

Bases de données avancées http://eric.univ-lyon2.fr/jdarmont/ 3

Base de données (BD) : Collection de données

Saisie Traitement Fichier

Bases de données avancées http://eric.univ-lyon2.fr/jdarmont/ 5

Bases de données avancées http://eric.univ-lyon2.fr/jdarmont/ 6

Standardisation des traitements

Contrôle de la validité des données

Partage de données entre plusieurs traitements

Bases de données avancées http://eric.univ-lyon2.fr/jdarmont/ 7

Système de Gestion de Bases de Données :

Bases de données avancées http://eric.univ-lyon2.fr/jdarmont/ 8

Probléma- Indépendant d’un système

Partie 1 : Modélisation conceptuelle

Partie 2 : Modélisation logique Modèle

Partie 3 : Interrogation et manipulation de bases de données

Partie 4 : Programmation de bases de données

Bases de données avancées http://eric.univ-lyon2.fr/jdarmont/ 10

Bases de données avancées http://eric.univ-lyon2.fr/jdarmont/ 11

Standard de l’Object Management Group

Ensemble de formalismes graphiques

Bases de données avancées http://eric.univ-lyon2.fr/jdarmont/ 12

Classe : Groupe d’entités du monde réel ayant les mêmes

Attribut : Propriété de la classe

Bases de données avancées http://eric.univ-lyon2.fr/jdarmont/ 13

Bases de données avancées http://eric.univ-lyon2.fr/jdarmont/ 14

Objets (individus) de la classe ETUDIANT = les étudiant·es

Nom Prénom DateNaiss Etc.

Problème : Comment distinguer les Dupont ?

Solution : Ajouter un attribut numéro d’étudiant !

NumEtu Nom Prénom DateNaiss

Bases de données avancées http://eric.univ-lyon2.fr/jdarmont/ 16

Le numéro d’étudiant est un attribut identifiant.

Un identifiant caractérise de façon unique les

Bases de données avancées http://eric.univ-lyon2.fr/jdarmont/ 17

Association : liaison perçue entre des classes

Les classes ETUDIANT et EPREUVE peuvent être qualifiées de

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

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

Rôle : fonction de chaque

Bases de données avancées http://eric.univ-lyon2.fr/jdarmont/ 19

Définition : Indicateur qui montre combien

Bases de données avancées http://eric.univ-lyon2.fr/jdarmont/ 20

ex. Un·e étudiant·e possède une et une seule carte

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

ex. Une épreuve relève d’une et une seule matière.

NB : La multiplicité un à plusieurs (1..*) peut aussi

Bases de données avancées http://eric.univ-lyon2.fr/jdarmont/ 22

ex. Un·e étudiant·e peut appartenir ou non à un

NB : La multiplicité un à plusieurs (1..*) peut aussi

Bases de données avancées http://eric.univ-lyon2.fr/jdarmont/ 23

ex. Un·e étudiant·e peut passer plusieurs épreuves.

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

Il est possible de caractériser une association par des

NB : Une classe-association est une association, pas une

Les étudiant·es sont caractérisé·es par un numéro unique, leur

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

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