M1info Bda

Bases de données avancées
Master 1 Informatique
2021-2022
Jérôme Darmont
https://eric.univ-lyon2.fr/jdarmont/
Actualité du cours
https://twitter.com/darmont_lyon2 #bda
Bases de données avancées https://eric.univ-lyon2.fr/jdarmont/ 2

Objectifs du cours
SQL : langage de requêtes (bases de données relationnelles)
– Standard
– Optimiseurs de requêtes
– Non procédural
– Données structurées
Programmation nécessaire pour :

Langage
– Tâches complexes
– Interfaces utilisateurs
PL/pgSQL
80 % des données sont peu ou pas structurées

– Description via le langage XML
Langage
XQuery
Partie 1
PL/pgSQL
Procedural Language/PostgreSQL Structured Query Language
Bases de données avancées 4

Plan
Introduction
Bases du langage
Curseurs
Gestion des erreurs
Déclencheurs
SQL dynamique

Requêtes SQL dans un programme
SQL encapsulé : Requêtes SQL incorporées dans le code

source (PL/SQL, T-SQL, PL/pgSQL, Pro*C…)
C
U
API : Requêtes SQL via des fonctions du langage (Java R
Persistence API, PHP Data Objects…)
S
Interfaces de niveau appel : intergiciel entre le langage et E
le SGBD (ODBC, JDBC, ADO…)
U
Procédures stockées : Fonctions SQL stockées dans la base R
de données et exécutées par le SGBD (écrites en PL/SQL,
T-SQL, PL/pgSQL)
S
Choix de PostgreSQL
SGBD relationnel-objet
Licence libre (BSD)
Le plus conforme au standard SQL
Disponible sur de nombreuses plateformes

– Divers UNIX, dont Linux et Mac OS
– Windows
Historique de PostgreSQL
Michael Stonebraker
1974 : Ingres
(INteractive Graphics REtrieval System)
1985 : Postgres (post-Ingres)
1995 : Postgres95 (fonctionnalités SQL) fr.wikipedia.org
1996 : PostgreSQL (v6)
2021 : v13.3

Caractéristiques de PostgreSQL
Types structurés (enregistrements, tableaux)

possibles dans les tables (relationnel étendu)
Comportement stable
Langage procédural PL/pgSQL proche du PL/SQL

d’Oracle (permet le SQL dynamique)
Interfaçage possible avec des modules externes

d’autres langages (PERL, Python…)
Caractéristiques de PL/pgSQL
Utilisation des types de données, opérateurs et

fonctions de SQL
Stockage du code dans la base de données

– Sécurité liée à celle du SGBD et de ses droits d’accès
Exécution au sein du serveur de BD

– Pas d’allers-retours entre client et serveur
⇒ Performance
Sondage express
A) Jusqu’ici, tout va bien.
B) Je suis déjà perdu.
Répondre sur https://toreply.univ-lille.fr
Question n° 455
Plan
Introduction
Bases du langage
Curseurs
Gestion des erreurs
Déclencheurs
SQL dynamique

Structuration en blocs
[DECLARE
-- Déclarations]
BEGIN
-- Instructions PL/pgSQL
[EXCEPTION
-- Gestion des erreurs]
END [ ] : clause optionnelle
(idem dans tout le document)
Variables et constantes
Déclaration dans la section DECLARE d’un bloc PL/pgSQL
Variables
ex. dateNaissance DATE;
compteur INTEGER := 0; -- Initialisation
compteur2 INTEGER DEFAULT 0; -- Valeur par défaut
id CHAR(5) NOT NULL := ‘AP001’;
Constantes
ex. tauxTVA CONSTANT REAL := 0.2;

Principaux types de données
Type Description
boolean, bool Booléen (vrai/faux)

smallint, int2 Entier de -32768 à +32767
integer, int, int4 Entier de -2147483648 à +2147483647
bigint, int8 Entier de -9223372036854775808 à +9223372036854775807
real, float4 Réel simple précision (6 décimales)
double precision, float8 Réel double précision (15 décimales)
numeric, numeric(P, S), decimal, decimal (P, S) Nombre jusqu'à 131072 chiffres avant la virgule (P), 16383 après (S)
character[N], char[N] Chaîne de caractères de longueur N fixe

varchar, varchar[N] Chaîne de caractères de longueur variable (maximum N si précisé)
text Texte long de longueur variable (nécessite des opérateurs

spécifiques)
date Date (ex. ‘jj/mm/aaaa’)

time Heure (ex. ‘hh:mm:ss’)
timestamp
Bases de données avancées Date et heure (ex. ‘aaaa-mm-jj
https://eric.univ-lyon2.fr/jdarmont/ hh:mm:ss’) 15
Référencer un type existant
Type d’une autre variable

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

ex. numEmp EMP.EMPNO%TYPE; À utiliser
au maximum !
Type des n-uplets d’une table
ex. unEtudiant STUDENT%ROWTYPE;
N-uplet indéfini (enregistrement)

ex. resultat RECORD;
Types structurés
Tableaux
ex. notes NUMERIC(2, 2)[];
matrice INTEGER[][];
Types composites (enregistrements)

ex. CREATE TYPE tEmploye AS ( -- Définition
num NUMERIC,
nom VARCHAR
); -- à définir hors de PL/pgSQL (commande SQL)
unEmploye tEmploye; -- Instanciation

Affectation simple
Variables
ex. n := 0;
n := n + 1;
Tableaux
ex. notes := ARRAY[10.2, 13.3, 15.5, 9.8];
matrice := ARRAY[ ARRAY[4, 2],
ARRAY[1, 9] ];
Enregistrements
ex. unEmploye := (1501, 'DARMONT');

Lecture de valeurs de la base de données
Variables
ex. SELECT custname INTO nomClient
FROM customer WHERE custnum = 10;
SELECT ename, sal INTO nom, salaire
FROM emp WHERE empno = 5000;
Enregistrements
ex. SELECT empno, ename INTO unEmploye
FROM emp WHERE empno = 1501;
SELECT * INTO resultat
FROM customer WHERE custnum = 20;
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/3)
IF-THEN, IF-THEN-ELSE ou IF-THEN-ELSIF
IF condition1 THEN
-- -- Instructions PL/pgSQL
[ELSIF condition2 THEN
-- Instructions PL/pgSQL]
[ELSE
-- Instructions PL/pgSQL]
END IF;

Tests (2/3)
CASE simple
CASE variable
WHEN val1 THEN -- Instructions PL/pgSQL
WHEN val2, val3 THEN -- Instructions PL/pgSQL
WHEN val4 THEN -- Instructions PL/pgSQL
[ELSE -- Instructions par défaut]
END CASE;

Tests (3/3)
CASE par intervalles
CASE
WHEN var BETWEEN val1 AND val2 THEN
WHEN var BETWEEN val2 + 1 AND val3 THEN
END CASE;

Boucles
Pour
FOR iterateur IN [REVERSE] min..max [BY pas] LOOP
END LOOP;
Tant que
WHILE condition LOOP
END LOOP;
Répéter jusqu’à
LOOP
EXIT WHEN condition; -- C’est moche, mais
END LOOP; -- pas le choix...
Boucles sur tableaux
Tableau
FOREACH note IN ARRAY notes LOOP -- note est un NUMERIC(2, 2)
END LOOP;
Matrice
FOREACH n IN ARRAY matrice LOOP -- n est un INTEGER
END LOOP;
-- Eh oui, pas besoin de boucles imbriquées !

Quizz
Combien y a-t-il de sections
dans un bloc PL/pgSQL ?
A) 1
B) 2
C) 3
D) 4
E) 5
Question n° 494
Implémentation d’un bloc
Dans une fonction

ex. CREATE [OR REPLACE] FUNCTION test() RETURNS VOID AS $$
-- bloc PL/pgSQL
$$ LANGUAGE plpgsql;
Exécution de la fonction
ex. SELECT test(); -- La forme du résultat peut différer
SELECT * FROM test(); -- en fonction des clients PostreSQL.

Exemple de fonction
-- Calcul de prix TTC
CREATE OR REPLACE FUNCTION calculPrixTTC(idProduct NUMERIC) RETURNS REAL AS $$
DECLARE
tauxTVA CONSTANT REAL := 0.2;
prixHT demo_product_info.list_price%TYPE;
BEGIN
-- Lire le prix HT
SELECT list_price INTO prixHT FROM demo_product_info
WHERE product_id = idProduct;
-- Retourner le prix TTC
RETURN prixHT * (1 + tauxTVA) ::REAL;
END
-- Exécution
SELECT calculPrixTTC(10);
Fonction retournant plusieurs valeurs
Paramètres de sortie
CREATE OR REPLACE FUNCTION calculs( n1 INT, n2 INT,
OUT somme INT, OUT produit INT) AS $$
-- Pas de RETURN
BEGIN
somme := n1 + n2;
produit := n1 * n2;
END
SELECT calculs(4, 5); -- Sous forme d’enregistrement (valeurs uniquement)

-- ou
SELECT * FROM calculs(4, 5); -- Sous forme de table (avec entêtes somme et produit)

Fonction retournant plusieurs enregistrements
CREATE OR REPLACE FUNCTION serie(taille INT, pas INT) RETURNS SETOF INT AS $$
DECLARE
i INT;
BEGIN
FOR i IN 1..taille BY pas LOOP
RETURN NEXT i;
END LOOP;
RETURN;
END
SELECT serie(10, 2);

Appel de fonction dans une fonction
CREATE OR REPLACE FUNCTION droleDeDiv(n1 INT, n2 INT) RETURNS REAL AS $$

DECLARE
s INT;
p INT;
BEGIN
SELECT * INTO s, p FROM calculs(n1, n2);
RETURN p / s ::REAL;
END
SELECT droleDeDiv(5, 6);

Récursivité
CREATE OR REPLACE FUNCTION factorielle(n INTEGER) RETURNS INTEGER AS $$
DECLARE
f INTEGER;
BEGIN
IF n = 1 THEN -- Condition d'arrêt
RETURN 1;
ELSE
SELECT * INTO f FROM factorielle(n - 1); -- Appel récursif
RETURN n * f;
END IF;
END
SELECT factorielle(10);

Sondage express
Peut-on écrire un bloc PL/pgSQL

en-dehors d’une fonction?
Question n° 454
Plan
Introduction
Bases du langage
Curseurs
Gestion des erreurs
Déclencheurs
SQL dynamique

Contexte d’utilisation des curseurs
Requête qui retourne un seul n-uplet

– SELECT INTO
– Stockage du résultat dans une ou plusieurs variables
ou un enregistrement
Requête qui retourne plusieurs n-uplets

– Nécessité d’un curseur
– Structure de données en mémoire qui stocke le
résultat de la requête (⇔ tableau d’enregistrements)

Curseur implicite (non lié)
-- Parcours complet du curseur
CREATE OR REPLACE FUNCTION listeEmp() RETURNS SETOF tEmploye AS $$
DECLARE
nuplet emp%ROWTYPE;
e tEmploye;
BEGIN
FOR nuplet IN SELECT * FROM emp LOOP
e.num := nuplet.empno;
e.nom := LOWER(nuplet.ename);
RETURN NEXT e;
END LOOP;
RETURN;
END
SELECT listeEmp();
Curseur explicite (lié)
-- Parcours ad-hoc du curseur (même résultat que listeEmp)
CREATE OR REPLACE FUNCTION listeEmp2() RETURNS SETOF tEmploye AS $$
DECLARE
cursEmp CURSOR FOR SELECT * FROM emp;
nuplet emp%ROWTYPE;
e tEmploye;
BEGIN
OPEN cursEmp;
FETCH cursEmp INTO nuplet; -- Lecture du 1er n-uplet
WHILE FOUND LOOP
RETURN NEXT e;
FETCH cursEmp INTO nuplet; -- Lecture du n-uplet suivant
END LOOP;
CLOSE cursEmp;
RETURN;
END
Curseur explicite (lié)
-- Parcours vraiment ad-hoc du curseur (renvoie 1 résultat sur 3)
CREATE OR REPLACE FUNCTION listeEmp3(pas INT) RETURNS SETOF tEmploye AS $$
DECLARE -- Pas de changement
BEGIN
OPEN cursEmp;
FETCH cursEmp INTO nuplet;
WHILE FOUND LOOP
RETURN NEXT e;
MOVE FORWARD pas FROM cursEmp; -- MOVE cursEmp; pour un seul décalage
END LOOP;
CLOSE cursEmp;
RETURN;
END
SELECT * FROM listeEmp3(2);

Curseur paramétré
CREATE OR REPLACE FUNCTION listeEmp4(salPlancher DECIMAL) RETURNS SETOF tEmploye AS $$
DECLARE
cursEmp CURSOR(salMin DECIMAL) FOR SELECT * FROM emp WHERE sal >= salMin;
nuplet emp%ROWTYPE;
e tEmploye;
BEGIN
OPEN cursEmp(salPlancher);
WHILE FOUND LOOP
RETURN NEXT e;
END LOOP;
CLOSE cursEmp;
RETURN;
END
SELECT listeEmp4(2000); https://eric.univ-lyon2.fr/jdarmont/ 39

Quizz
On veut appliquer un échantillonnage sur
les n-uplets d’une table. Quel type de
curseur doit-on utiliser pour les sélectionner ?
A) Curseur implicite
B) Curseur explicite
C) Curseur paramétré

Question n° 790

Plan
Introduction
Bases du langage
Curseurs
Gestion des erreurs
Déclencheurs
SQL dynamique

Signalement d’erreur
RAISE niveauErreur 'message';

Niveau d’erreur Priorité Description
DEBUG 1 Écrit le message dans le log

LOG 2 Écrit le message dans le log
INFO 3 Écrit le message dans le log et l’envoie au client
NOTICE 4 Écrit le message dans le log et l’envoie au client
WARNING 5 Écrit le message dans le log et l’envoie au client
EXCEPTION 6 Interrompt la transaction courante

Exceptions personnalisées
CREATE OR REPLACE FUNCTION testErreur1(noDept INTEGER) RETURNS REAL AS $$
DECLARE
nbEmp INTEGER;
ref CONSTANT INTEGER := 85;
BEGIN
SELECT COUNT(*) INTO nbEmp FROM emp WHERE deptno = noDept;
IF nbEmp = 0 THEN
RAISE EXCEPTION 'Pas d''employé dans ce département’;
-- RAISE EXCEPTION 'Pas d''employé dans le département %', noDept;
END IF;
RETURN ref / nbEmp ::REAL;
END
SELECT testErreur1(99);
Exploitation des exceptions systèmes
DECLARE
nbEmp INTEGER;
BEGIN
EXCEPTION
WHEN division_by_zero THEN
RAISE WARNING 'Pas d''employé dans le département %', noDept;
RETURN NULL;
END
SELECT testErreur2(99);
Traitement par défaut
DECLARE
nbEmp INTEGER;
BEGIN
IF noDept <= 0 THEN
RAISE EXCEPTION 'noDept ne peut pas être négatif';
END IF;
EXCEPTION
WHEN division_by_zero THEN
RAISE WARNING 'Personne dans le département %', noDept;
RETURN NULL;
WHEN others THEN -- On peut aussi remplacer others par raise_exception
RETURN -1; -- Code d'erreur
END
Quelques autres exceptions système
Code Nom
22004 null_value_not allowed
22003 numeric_value_out_of_range
22012 division_by_zero
23503 foreign_key_violation
23505 unique_violation
28P01 invalid_password
42501 insufficient_privilege
42883 undefined column
54011 too_many_columns
P0002 no_data_found
P0003 too_many_rows
Plan
Introduction
Bases du langage
Curseurs
Gestion des erreurs
Déclencheurs
SQL dynamique

Définition des déclencheurs
Définition : Fonction associée à une table et exécutée

automatiquement lorsque des événements liés à des
actions sur la table surviennent (mises à jour,
principalement).
Complètent les contraintes d’intégrité en permettant de

créer des règles d’intégrité complexes.
– Éléments des bases de données actives.

Principaux types de déclencheurs
Insert Delete Update
Before 1 2 3
After 4 5 6

Création/suppression de déclencheur
CREATE TRIGGER nomDeclencheur
BEFORE | AFTER
INSERT | DELETE | UPDATE | [INSERT] [[OR] DELETE] [[OR] UPDATE]
ON nomTable
FOR EACH ROW | FOR EACH STATEMENT | : ou
EXECUTE PROCEDURE nomFonction();
DROP TRIGGER nomDeclencheur
ON nomTable;
Variables systèmes des déclencheurs
NEW : Enregistrement contenant le n-uplet inséré ou modifié
Ex. INSERT INTO client VALUES (1, 'NouveauClient');
NEW.NumCli prend la valeur 1 dans un déclencheur sur client.

NEW.Nom prend la valeur 'NouveauClient' dans le déclencheur.
OLD : Enregistrement contenant l’ancien n-uplet supprimé ou modifié
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 EMP
CREATE OR REPLACE FUNCTION checkEmpPK() RETURNS TRIGGER AS $$

DECLARE
n INTEGER;
BEGIN
-- La clé est-elle vide ?
IF NEW.empno IS NULL THEN
RAISE EXCEPTION 'La clé primaire doit avoir une valeur !’;
END IF;

Exemple de déclencheur (2/2)
-- La clé existe-t-elle déjà ?
SELECT COUNT(empno) INTO n FROM emp
WHERE empno = NEW.empno;
IF n > 0 THEN
RAISE EXCEPTION 'Clé primaire déjà utilisée !’;
END IF;
RETURN NEW;
END
CREATE TRIGGER trigEmpPK

BEFORE INSERT OR UPDATE ON emp
FOR EACH ROW EXECUTE PROCEDURE checkEmpPK();
Quizz
Deux tables T et T’ ont la même structure.
Chaque ajout de n-uplet dans T doit être
répercuté dans T’.
Quel type de déclencheur faut-il utiliser ?
A) BEFORE INSERT FOR EACH STATEMENT
B) BEFORE INSERT FOR EACH ROW
C) AFTER INSERT FOR EACH STATEMENT
D) AFTER INSERT FOR EACH ROW
Question n° 353
Plan
Introduction
Bases du langage
Curseurs
Gestion des erreurs
Déclencheurs
SQL dynamique

SQL statique vs. SQL dynamique
Exemples
– Fonction qui met la table EMP à jour (ex. changement de salaire)

⇒ SQL statique (la requête est connue à la compilation)
– Fonction 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/pgSQL

Requête dynamiques
Exécution : EXECUTE requete [INTO resultat];

– requete est une chaîne de caractères
– resultat est une variable, un ensemble de variables ou un
enregistrement
Note :
– Requêtes paramétrées : valeurs de la base de données.
– Si l’on veut paramétrer des objets (tables, vues, attributs...) :
requête dynamique.

Exemple de requête dynamique
CREATE OR REPLACE FUNCTION tailleTable(nomTable VARCHAR)

RETURNS INTEGER AS $$
DECLARE
n INTEGER;
BEGIN
EXECUTE 'SELECT COUNT(*) FROM ' || nomTable INTO n;
RETURN n;
END
SELECT tailleTable('EMP');

Curseurs dynamiques (1/2)
-- Exemple 1
CREATE OR REPLACE FUNCTION parcoursTable(nomTable VARCHAR) RETURNS VOID AS $$
DECLARE
dynCurs REFCURSOR;
nuplet RECORD;
BEGIN
OPEN dynCurs FOR EXECUTE 'SELECT * FROM ' || nomTable;
FETCH dynCurs INTO nuplet;
WHILE FOUND LOOP
-- Opérations sur nuplet
FETCH dynCurs INTO nuplet;
END LOOP; Une fonction ne peut pas
CLOSE dynCurs;
retourner un SETOF RECORD.
END
SELECT parcoursTable('EMP');

Curseurs dynamiques (2/2)
-- Exemple 2
CREATE FUNCTION contenuTable(nomTable VARCHAR, nomID VARCHAR)
RETURNS SETOF VARCHAR AS $$
DECLARE
dynCurs REFCURSOR;
id VARCHAR;
BEGIN
OPEN dynCurs FOR EXECUTE 'SELECT ' || nomID || ' FROM ' || nomTable;
FETCH dynCurs INTO id;
WHILE FOUND LOOP
RETURN NEXT id;
FETCH dynCurs INTO id;
END LOOP;
CLOSE dynCurs;
RETURN;
END
SELECT contenuTable('EMP', 'EMPNO');

Quizz
Lesquelles de ces requêtes

sont dynamiques ?
A) SELECT * FROM emp;

B) SELECT * FROM emp WHERE empno = n;
C) SELECT COUNT(*) FROM nomTable;
D) DROP TABLE emp;
Répondre sur https://toreply.univ-lille.fr/
Question n° 258
Plan
Introduction
Bases du langage
Curseurs
Gestion des erreurs
Déclencheurs
SQL dynamique

Partie 2
XML/XQuery
Bases de données avancées 63

Plan
Introduction
Documents XML
Langage XQuery
– XPath
– Requêtes FLWOR
– Requêtes complexes

Données structurées
Données organisées en entités
Entités similaires : forment des groupes (classes)
Entités du même groupe : même description (attributs)
Pour toutes les entités d’un groupe :
– Chaque attribut a le même type
– Chaque valeur d’attribut a la même taille
– Tous les attributs sont présents
– Les attributs sont toujours dans le même ordre
Données structurés : décrites par un schéma

– Généralement stockées dans des bases de données
Données non structurées
Données de tous types

Données qui ne suivent aucun schéma ni séquence prédéterminé
Données qui ne suivent aucune règle
Données qui ne sont pas prévisibles
Exemples de données non-structurées :

– Textes
– Images
– Vidéos
– Sons

Données semi-structurées
Bases de Documents
données
Langages de requête
Années 1990
Moteurs de recherche
Données Données Données
structurées semi-structurées non structurées

Données semi-structurées
Données organisées en entités sémantiques
Structurées
Entités similaires : groupes
Entités du même groupe : peuvent ne pas avoir les mêmes attributs
Pour toutes les entités d’un groupe :
Non-structurées
– Un même attribut peut avoir des types différents
– Une même valeur d’attribut peut avoir des tailles différentes
– Des attributs peuvent être manquants ou dupliqués
– L’ordre des attributs n’est pas nécessairement important
Données semi-structurées : autodescriptives

– Pages web, documents XML, courriels…

Exemple de données semi-structurées
Nom Jérôme Darmont

Courriel jerome.darmont@univ-lyon2.fr
jdarmont@eric.univ-lyon2.fr
Courriel sabine.loudcher@univ-lyon2.fr
Nom
– Prénom Loudcher
– Nom de famille Sabine
Nom Julien Velcin

Affiliation Université Lumière Lyon 2

Modèle de données semi-structuré
Avantages
– Peut représenter des informations issues de sources de données qui ne
peuvent pas êtres contraintes par un schéma
– Format flexible pour l’interopérabilité
– Permet de voir des données structurées comme semi-structurées (Web)
– Schéma facilement évolutif
Inconvénients
– Performance des requêtes sur données à grande échelle
Représentations
– Electronic Data Interchange (EDI) : domaine financier
– Object Exchange Model (OEM) : modèle basé sur les graphes
– SGML, HTML et XML
– JSON, YAML...
Exemple de graphe OEM

Gestion de données semi-structurées
Modélisation des données semi-structurées

– Graphes (OEM) – Modèle logique
– XML – Modèle physique
Requêtage des données semi-structurées

– XPath
– XQuery
Stockage des données semi-structurées

– Fichiers plats
– Bases de données relationnelles, relationnelles-objets ou natives XML

Références
Peter Wood, Birkbeck University of London

Semi-Structured Data
http://www.dcs.bbk.ac.uk/~ptw/
Mike Bergman, Structured Dynamics LLC

Semi-structured Data: Happy 10th Birthday!
http://www.mkbergman.com/153/semi-structured-data-happy-10th-birthday/

Sondage express
Pensez-vous avoir compris la

Différence entre données structurées,
non structurées et semi-structurées ?
Question n° 541
Pourquoi pas JSON, YAML et les BD NoSQL ?
SGBD NoSQL type MongoDB : plus efficaces que

XML/XQuery si besoin de stockage distribué
Pour l'analytics, XQuery est plus puissant

(opérateur // inexistant dans MongoDB)
Les SGBD NoSQL sont étudiés en M2 !

Plan
Introduction
Documents XML
Langage XQuery
– XPath
– Requêtes FLWOR

Le langage XML
XML : Extensible Markup Language

– Format de structuration de données et de documents Internet issu
de SGML
– Définition, gestion, création, transmission et partage de
documents
SG ML
XML est un standard du W3C
– 1996 : Brouillon
– 1997 : XML 1.0
– 2004 : XML 1.1 HTML XML

Exemple de document XML
<?xml version="1.1" encoding=“utf-8" ?> 
<annuaire_professeurs> 
<!– Sous-éléments -->
<professeur>
<nom>Jérôme Darmont</nom>
<courriel>jerome.darmont@univ-lyon2.fr</courriel>
<cours>Bases de données avancées</cours>
<cours>Programmation Web</cours> Ensemble d’éléments imbriqués
</professeur> matérialisés par des balises
<professeur>
<nom>Julien Velcin</nom>
<courriel>julien.velcin@univ-lyon2.fr</courriel>
<cours>Programmation orientée objet</cours>
</professeur>

</annuaire_professeurs> 
Règles d’écriture d’un document XML (1/2)
Un document XML a un et un seul élément racine.
Les éléments doivent être correctement emboîtés (les balises

ouvrantes et fermantes ne doivent pas se chevaucher).
Tout élément doit avoir une balise ouvrante et une balise fermante.
Le nom d’un élément doit être identique dans la balise ouvrante et la

balise fermante.
Les noms d’éléments sont sensibles à la casse. Ils doivent

commencer par une lettre ou par _ suivi(e) de lettres, de chiffres,
de . , de – ou de _.
Règles d’écriture d’un document XML (2/2)
Les noms d’éléments commençant par XML (dans toutes

combinaisons de minuscules et majuscules) sont réservés à des fins
de standardisation.
Un document XML respectant ces règles est dit bien formé.
Un document XML doit être bien formé !
Un document XML peut de plus être valide s’il se conforme à la

structure définie dans une DTD ou un Schéma XML.
Document Type Definition XML Schema

Schémas XML
DTD XML Schema
Syntaxe spécifique (non XML) Exprimé dans un document XML
Typage faible Typage fort
Modélisation partielle impossible Modélisation partielle possible
Interprétable par un·e Conçu pour des traitements

utilisateur·trice humain·e automatiques

Éléments XML (1/2)
Caractères non-autorisés : < & ]]>
Eléments emboîtés : profondeur non limitée
– Ex. <annuaire_professeurs>
<professeur>
<nom>
<nom_famille>Zighed</nom_famille>
<prenom>Abdelkader</prenom>
<prenom>Djamel</prenom>
</nom>
</professeur>
</annuaire_professeurs>
Éléments XML (2/2)
Section CDATA : Bloc de texte libre dans lequel seule la chaîne ]]>
est interdite
– Ex. <nom>
<![CDATA[<Darmont> & <Loudcher>]]>
</nom>
Élément vide : sans contenu

– Ex. <courriel></courriel>
– Formulation équivalente <courriel />

Attributs d’éléments XML
Attributs : données associées à un élément, complémentaires du

contenu
Définition : couple nom/valeur dans la balise ouvrante de l’élément
– Ex. <bureau campus="PdA" batiment="K">063</bureau>
attribut valeur attribut valeur
Les attributs sont possibles dans les éléments vides.
– Ex. <image source="ma_bobine.png" />

Contenu d’élément XML vs. attributs (1/2)
Que choisir ?
– <professeur>
<nom>Darmont</nom>
</professeur>
– <professeur nom="Darmont" />
4 principes pour décider

– D’après Uche Ogbuji, Fourthought, Inc.

Contenu d’élément XML vs. attributs (2/2)
Principe Élément Attribut
Contenu principal Information essentielle Information périphérique
Information structurée Information hiérarchisée Information atomique
Lisibilité Utilisateur.trice humain.e Traitement automatique
Relation élément/attribut Information précisée par une autre
Exemple de relation <stock>

élément/attribut <produit quantité="1500">
<nom>Ordinateur</nom>
</produit>
<produit quantité="500">
<nom>Imprimante</nom>
</produit>
</stock>
Quizz
Dans un document XML, les données

sont stockées dans :
A) Les éléments
B) Les attributs
Question n° 252
Plan
Introduction
Documents XML
Langage XQuery
– XPath
– Requêtes FLWOR

Le langage XQuery
Langage de requêtes pour données XML
Similarités avec SQL

Conçu par le W3C
Basé sur des expressions XPath (mêmes modèle de données, fonctions, opérateurs)
Versions
– 2007 : XQuery 1.0 ⊃ XPath 2.0
– 2017 : XQuery 3.1 ⊃ XPath 3.1
Standardisation en cours (standard de fait)

Soutenu par les éditeurs de SGBD (Oracle, Microsoft, IBM…)

Document XML exemple (1/2)
<?xml version="1.1" encoding="utf-8" ?>
<catalogue>
<dvd zone="1">
<titre>Blade runner</titre>
<realisateur>Ridley Scott</realisateur>
<annee>1982</annee>
<langue>Anglais</langue>
<prix>14.79</prix>
</dvd>
<dvd zone="2">
<titre>La grande vadrouille</titre>
<realisateur>Gérard Oury</realisateur>
<annee>1966</annee>
<duree>122</duree>
<langue>Français</langue>
<prix>19.82</prix>
</dvd> 
Document XML exemple (2/2)
<dvd zone="2">
<titre>Le fabuleux destin d'Amélie Poulain</titre>
<realisateur>Jean-Pierre Jeunet</realisateur>
<annee>2001</annee>
<duree>120</duree>
<prix>14.99</prix>
</dvd>
<dvd zone="2">
<titre>The big Lebowski</titre>
<realisateur>Ethan Coen</realisateur>
<realisateur>Joel Coen</realisateur>
<annee>1997</annee>
<duree>112</duree>
<langue>Anglais</langue>
<prix>19.82</prix>
</dvd>
Bases de données avancées </catalogue> https://eric.univ-lyon2.fr/jdarmont/ 91

Plan
Introduction
Documents XML
Langage XQuery
– XPath
– Requêtes FLWOR

Expressions de chemins (1/3)
Document XML entier

doc("dvd.xml")/catalogue
Résultat Par
Tout le document l’exemple
Un élément donné
doc("dvd.xml")/catalogue/dvd
doc("dvd.xml")/catalogue/dvd/titre
Résultat
Un attribut donné
doc("dvd.xml")/catalogue/dvd/data(@zone)
Résultat
1222
Un élément donné quel que soit son niveau hiérarchique

doc("dvd.xml")/catalogue//titre
//titre
Résultat
Tous les sous-éléments d’un élément
doc("dvd.xml")/catalogue/dvd/*
Résultat
<realisateur>Ridley Scott</realisateur>
<annee>1982</annee>
<duree>117</duree>
<langue>English</langue>
<prix>14.79</prix>
<realisateur>Gérard Oury</realisateur>
<annee>1966</annee>
<duree>122</duree>
<langue>French</langue>
<prix>19.82</prix> Etc.
Prédicats XPath (1/2)
ie, dernier, i premiers/derniers éléments
doc("dvd.xml")/catalogue/dvd[1]
doc("dvd.xml")/catalogue/dvd[last()]
doc("dvd.xml")/catalogue/dvd[position() < 3]/titre
Résultat
Éléments possédant un sous-élément ou attribut donné

doc("dvd.xml")/catalogue/dvd[duree]/titre
Résultat
doc("dvd.xml")/catalogue/dvd[@zone]
Prédicats XPath (2/2)
Condition sur un élément ou un attribut

doc("dvd.xml")/catalogue/dvd[prix < 15]
doc("dvd.xml")/catalogue/dvd[@zone = "2" and prix < 15]/titre
Résultat
Combinaison de chemins
doc("dvd.xml")//titre | doc("dvd.xml")//prix
Résultat
<titre>Blade runner</titre><prix>14.79</prix>
<titre>La grande vadrouille</titre><prix>19.82</prix>
<titre>Le fabuleux destin...</titre><prix>14.99</prix>
<titre>The big Lebowski</titre> <prix>19.82</prix>

Quizz
Lesquels de ces chemins sont corrects ?
A) /catalogue/dvd/prix
B) doc("dvd.xml")/catalogue/dvd/prix
C) //prix
D) /catalogue//prix
Question n° 621
Plan
Introduction
Documents XML
Langage XQuery
Xpath
Requêtes FLWOR
Requêtes complexes

Requêtes FLWOR
For, Let, Where, Order by, Return
Clause For (1/3) : lie une variable à chaque élément retourné

par une expression (itération)
Exemple
for $x in (1 to 3) 
return <res>{$x}</res>
Résultat
<res>1</res>
<res>2</res>
<res>3</res>

Clause For (2/3)
Exemple
for $x in (1, 2),
$y in (10, 20) (: Ceci est également un commentaire :)
return <res>x = {$x} et y = {$y}</res>
Résultat
<res>x = 1 et y = 10</res>
<res>x = 1 et y = 20</res>
<res>x = 2 et y = 10</res>
<res>x = 2 et y = 20</res>

Clause For (3/3)
Exemple
for $x at $i in doc("dvd.xml")/catalogue/dvd/titre
return <dvd id="{$i}">{data($x)}</dvd>
Résultat
<dvd id="1">Blade runner</dvd>
<dvd id="2">La grande vadrouille</dvd>
<dvd id="3">Le fabuleux destin d'Amélie Poulain</dvd>
<dvd id="4">The big Lebowski</dvd>

Clause Let
Clause Let : Assigner une ou plusieurs valeurs à une

énumération (pas d’itération)
Exemple
let $x := (1 to 5)
return <res>{$x}</res>
Résultat
<res>1 2 3 4 5</res>

Clause Where
Clause Where : Spécifie une ou plusieurs conditions sur le résultat
Exemple
for $x in doc("dvd.xml")/catalogue/dvd
where $x/prix > 15
return $x/titre
Exemple
where $x/@zone = "2" and $x/prix < 10
return $x/titre

Clauses Order by et Return (1/2)
Clause Order by : Trie le résultat
Exemple
order by $x/titre
return $x/titre
Exemple
order by $x/@zone, $x/titre descending
return $x/titre
Clause Return : Spécifie le résultat

Clause Return (2/2)
Expressions conditionnelles
Exemple
return if ($x/@zone="1")
then <zoneUS>{data($x/titre)}</zoneUS>
else <zoneEU>{data($x/titre)}</zoneEU>
Résultat
<zoneUS>Blade runner</zoneUS>
<zoneEU>La grande vadrouille</zoneEU>
<zoneEU>Le fabuleux destin d’Amélie Poulain</zoneEU>
<zoneEU>The big Lebowski</zoneEU>

Fonctions XPath/XQuery (1/2)
Fonctions d’accès : data()…
Fonctions numériques : abs(), floor(), ceiling(), round(), number()…

Fonctions de chaînes : string-length(), upper-case(), lower-case(),
normalize-space(), substring(), substring-after(), replace(),
contains()…
Fonctions temporelles : day-from-date(), year-from-date()…
Fonctions de séquences : exists(), distinct-values(), reverse()…
Fonctions contextuelles : last(), position()…

Fonctions booléennes : not()…
Fonctions XPath/XQuery (2/2)
Exemple d’appel à une fonction

for $x in doc("dvd.xml")/catalog/dvd/titre
let $titreMAJ := upper-case($x)
return <film>{$titreMAJ}</film>
Résultat
<film>BLADE RUNNER</film>
<film>LA GRANDE VADROUILLE</film>
<film>LE FABULEUX DESTIN D'AMÉLIE POULAIN</film>
<film>THE BIG LEBOWSKI</film>

Plan
Introduction
Documents XML
Langage XQuery
XPath
Requêtes FLWOR
Requêtes complexes

Clause Group by (XQuery 3)
Regroupement sur un critère

for $d in /catalogue/dvd
group by $z := $d/@zone
return <zone value="{$z}">
<prix_moyen>{avg($d/prix)}</prix_moyen>
</zone>
Regroupement multiple
for $d in /catalogue/dvd
group by $z := $d/@zone, $a := $d/annee
return <groupe zone="{$z}" annee="{$a}">
<prix_moyen>{avg($d/prix)}</prix_moyen>
</groupe>
Jointures – Documents exemples (1/3)
<?xml version="1.1" encoding="utf-8" ?> 
<clients>
<client id="1">
<nom>Loudcher</nom>
<prenom>Sabine</prenom>
<addresse>Bureau K073</addresse>
</client>
<client id="2">
<nom>Bentayeb</nom>
<prenom>Fadila</prenom>
</client>
<client id="3">
<nom>Darmont</nom>
<prenom>Jérôme</prenom>
</client>
</clients>
<?xml version="1.1" encoding="utf-8" ?> 
<produits>
<produit id="10">
<nom>Ordinateur</nom>
</produit>
<produit id="20">
<nom>Moniteur</nom>
</produit>
<produit id="30">
<nom>Imprimante</nom>
</produit>
</produits>
<?xml version="1.1" encoding="utf-8" ?> 
<commandes>
<commande cli-id="1" prod-id="10">
<quantite>3</quantite>
</commande>
</commande>
</commande>
</commande>
</commande>
</commandes>
Jointures de documents XML (1/3)
Exemple
for $c in doc("clients.xml")//client,
$o in doc("commandes.xml")//commande
where $c/@id = $o/@cli-id
return <res>{data($c/nom)}, {data($c/prenom)}:
{data($o/quantite)}</res>
Résultat
<res>Loudcher, Sabine : 3</res>
<res>Loudcher, Sabine : 15</res>
<res>Bentayeb, Fadila : 7</res>
<res>Bentayeb, Fadila : 10</res>
<res>Darmont, Jérôme : 5</res>

Exemple
$o in doc("commandes.xml")//commande,
$p in doc("produits.xml")//produit
where $c/@id = $o/@cli-id
and $o/@prod-id = $p/@id
{data($o/quantite)} x {data($p/nom)}</res>
Résultat
<res>Loudcher, Sabine : 3 x Ordinateur</res>
<res>Loudcher, Sabine : 15 x Moniteur</res>
<res>Bentayeb, Fadila : 7 x Ordinateur</res>
<res>Bentayeb, Fadila : 10 x Imprimante</res>
<res>Darmont, Jérôme : 5 x Imprimante</res>

Variantes avec les conditions de jointures exprimées

en prédicats de chemins
$o in doc("commandes.xml")//commande[@cli-id=$c/@id]
{data($o/quantite)}</res>
for $c in //client,
$p in //produit,
$o in //commande[@cli-id=$c/@id and @prod-id=$p/@id]
return <res>{data($c/nom)}, {data($c/prenom)} :
{data($o/quantite)} x {data($p/nom)}</res>

Quizz
Quelles clauses de requêtes FLWOR sont

également exprimables en XPath ?
A) For
B) Where
C) Order by
D) Group by
Question n° 695
Plan
Introduction
Documents XML
Langage XQuery
XPath
Requêtes FLWOR
Requêtes complexes

Sondage express
Que pensez-vous de ce cours ?
Question n° 683

M1info Bda

Transféré par

Droits d'auteur :

Formats disponibles

M1info 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

M1info Bda

Transféré par

Droits d'auteur :

Formats disponibles

Bases de données avancées

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

Programmation nécessaire pour :

80 % des données sont peu ou pas structurées

Bases de données avancées 4

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

SQL encapsulé : Requêtes SQL incorporées dans le code

Licence libre (BSD)

Le plus conforme au standard SQL

Disponible sur de nombreuses plateformes

1985 : Postgres (post-Ingres)

1995 : Postgres95 (fonctionnalités SQL) fr.wikipedia.org

1996 : PostgreSQL (v6)

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

Types structurés (enregistrements, tableaux)

Langage procédural PL/pgSQL proche du PL/SQL

Interfaçage possible avec des modules externes

Utilisation des types de données, opérateurs et

Stockage du code dans la base de données

Exécution au sein du serveur de BD

A) Jusqu’ici, tout va bien.

B) Je suis déjà perdu.

Répondre sur https://toreply.univ-lille.fr

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

Déclaration dans la section DECLARE d’un bloc PL/pgSQL

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

boolean, bool Booléen (vrai/faux)

character[N], char[N] Chaîne de caractères de longueur N fixe

text Texte long de longueur variable (nécessite des opérateurs

date Date (ex. ‘jj/mm/aaaa’)

Type d’une autre variable

Type de l’attribut d’une table

N-uplet indéfini (enregistrement)

Types composites (enregistrements)

unEmploye tEmploye; -- Instanciation

Bases de données avancées https://eric.univ-lyon2.fr/jdarmont/ 18

Opérateurs de comparaison = < > <= >= <>

Opérateurs logiques AND OR NOT

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

IF-THEN, IF-THEN-ELSE ou IF-THEN-ELSIF

Bases de données avancées https://eric.univ-lyon2.fr/jdarmont/ 21

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

CASE par intervalles

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

Bases de données avancées https://eric.univ-lyon2.fr/jdarmont/ 25

Répondre sur https://toreply.univ-lille.fr

Dans une fonction

Bases de données avancées https://eric.univ-lyon2.fr/jdarmont/ 27

SELECT calculs(4, 5); -- Sous forme d’enregistrement (valeurs uniquement)

Bases de données avancées https://eric.univ-lyon2.fr/jdarmont/ 29

SELECT serie(10, 2);

Bases de données avancées https://eric.univ-lyon2.fr/jdarmont/ 30

CREATE OR REPLACE FUNCTION droleDeDiv(n1 INT, n2 INT) RETURNS REAL AS $$

SELECT droleDeDiv(5, 6);

Bases de données avancées https://eric.univ-lyon2.fr/jdarmont/ 31

Bases de données avancées https://eric.univ-lyon2.fr/jdarmont/ 32

Peut-on écrire un bloc PL/pgSQL

Répondre sur https://toreply.univ-lille.fr

Bases de données avancées https://eric.univ-lyon2.fr/jdarmont/ 34