Cours SQL pour débutant


CHAPITRE4

L’algèbre relationnelle

Le premier langage étudié dans ce cours est l’algèbre relationnelle. Elle consiste en un ensemble d’opérations qui permettent de manipuler des relations, considérées comme des ensembles de nuplets : on peut ainsi faire l’union ou la différence de deux relations, sélectionner une partie de la relation, effectuer des produits cartésiens ou des projections, etc.

Une requête est une expression algébrique qui s’applique à un ensemble de relations (la base de données) et produit une relation finale (le résultat de la requête). On peut voir l’algèbre relationnelle comme un langage de programmation très simple qui permet d’exprimer des requêtes sur une base de données relationnelle. Les requêtes du langage SQL peuvent se transposer en expressions algébriques, ce qui nous donnera un moyen d’interpréter leur signification d’une part, et de comprendre comment les exécuter d’autre part.

Dans tout ce chapitre on va prendre l’exemple de la (petite) base de données d’un organisme de voyage. Cet organisme propose des séjours (sportifs, culturels, etc) se déroulant dans des stations de vacances. Chaque station propose un ensemble d’activités (ski, voile, tourisme). Enfin on maintient une liste des clients et des séjours auxquels ils ont participé avec leurs semaines de début et de fin.

Voici le schéma de la base. Les clés primaires sont en gras, les clés étrangères en italiques.

— Station (id, nom, capacité, lieu, région, tarif)

— Activité (idStation, libellé, prix)

— Client (id, nom, prénom, ville, région, solde)

— Séjour (id, idClient, idStation, début, fin, nbPlaces)

La table Station

Voici le contenu de la table Station. La clé est un code synthétisant le nom de la station.

id

nom

capacité

lieu

région

tarif

va

Venusa

350

Guadeloupe

Antilles

1200

fa

Farniente

200

Seychelles

Océan Indien

1500

sa

Santalba

150

Martinique

Antilles

2000

pa

Passac

400

Alpes

Europe

1000

La table Activité

Cette table contient les activités proposées par les stations. La clé est la paire consituée de (idStation,libellé).

idStation

libellé

prix

va

Voile

150

va

Plongée

120

fa

Plongée

pa

Ski

200

pa

Piscine

20

La table des clients

Les clients sont identifiés par un numéro séquentiel incrémenté de 10 en 10.

id

nom

prénom

ville

région

solde

10

Fogg

Phileas

Londres

Europe

12465

20

Pascal

Blaise

Paris

Europe

6763

30

Kerouac

Jack

New York

Amérique

9812

La table des séjours

Les séjours sont identifiés par un numéro séquentiel incrémenté par unités. Le début et la fin sont des numéros de semaine dans l’année.

id

idClient

idStation

début

fin

nbPlaces

1

10

Passac

20

20

2

2

30

Santalba

21

21

5

3

20

Santalba

21

22

4

4

30

Passac

2

3

3

5

30

Venusa

19

23

3

6

20

Venusa

23

23

6

7

30

Farniente

22

24

5

8

10

Farniente

23

25

3

Nous allons découvrir comme appliquer des opérations de l’algèbre aux relations de cette base, afin de construire une nouvelle relation. Exprimer une requête, c’est simplement construire une expression combinant plusieurs opérations afin d’obtenir une relation représentant le résultat cherché.

4.1 Les opérateurs de l’algèbre

L’algèbre se compose d’un ensemble d’opérateurs, parmi lesquels 5 sont nécessaires et suffisants et permettent de définir les autres par composition. Ce sont :

— La sélection, dénotée ?;

— La projection, dénotée ?;

— Le produit cartésien, dénoté ×;

— L’union, ?; — La différence, ?.

Ces opérateurs sont maintenant présentés tour à tour.

4.1.1 La sélection, ?

La sélection ?F(R) s’applique à une relation, R, et extrait de cette relation les nuplets qui satisfont un critère de sélection, F. Ce critère peut être :

— La comparaison entre un attribut de la relation, A, et une constante a. Cette comparaison s’écrit A?a, où ? appartient à {=,<,>,?,?}.

— La comparaison entre deux attributs A1 et A2, qui s’écrit A1?A2 avec les mêmes opérateurs de comparaison que précédemment.

Premier exemple : exprimer la requête qui donne toutes les stations aux Antilles.

?region=?Antilles?(Station)

On obtient donc le résultat :

id

nom

capacité

lieu

région

tarif

va

Venusa

350

Guadeloupe

Antilles

1200

sa

Santalba

150

Martinique

Antilles

2000

La sélection a pour effet de supprimer des lignes, mais chaque ligne garde l’ensemble de ses attributs.

4.1.2 La projection, ?

La projection ?A1,A2, ,Ak(R) s’applique à une relation R, et construit une relation contenant toutes les lignes de R, dans lesquelles seuls les attributs A1,A2, Ak sont conservés. Donc, contrairement à la sélection, on ne supprime pas des lignes mais des colonnes. Par exemple : on veut le nom des stations, et leur région.

?nom,region(Station)

On obtient le résultat suivant, après suppression des colonnes capacité, lieu et tarif} :

nom

région

Venusa

Antilles

Farniente

Océan Indien

Santalba

Antilles

Passac

Europe

Exemple : on souhaite connaître toutes les régions où il y a des stations. On exprime cette requête par :

?region(Station)

et on obtient :

région

Antilles

Océan Indien

Europe

La ligne ‘Antilles’ était présente deux fois dans la relation Station, et n’apparaît plus qu’en un seul exemplaire dans le résultat.

4.1.3 Le produit cartésien, ×

Le premier opérateur binaire, et le plus utilisé, est le produit cartésien, ×. Le produit cartésien entre deux relations R et S se note R × S, et permet de créer une nouvelle relation où chaque nuplet de R est associé à chaque nuplet de S. Voici deux relations, la première, R, contient

4.1. Les opérateurs de l’algèbre

A

B

a

b

x

y

et la seconde, S, contient :

C

D

c

d

u

v

x

y

Et voici le résultat de R × S :

A

B

C

D

a

b

c

d

a

b

u

v

a

b

x

y

x

y

c

d

x

y

u

v

x

y

x

y

Le nombre de lignes dans le résultat est exactement |R| × |S| (|R| dénote le nombre de lignes dans la relation R).

En lui-même, le produit cartésien ne présente pas un grand intérêt puisqu’il associe aveuglément chaque ligne de R à chaque ligne de S. Il ne prend vraiment son sens qu’associé à l’opération de sélection, ce qui permet d’exprimer des jointures, opération fondamentale qui sera détaillée plus loin.

Voici par exemple une table T qui a les mêmes noms d’attributs que R.

A

B

m

n

o

p

Le schéma du résultat du produit cartésien R × T a pour schéma (A,B,A,B) et présente donc des ambiguités, avec les colonnes A et B en double.

La première solution pour lever l’ambiguité est d’adopter une convention par laquelle chaque attribut est préfixé par le nom de la table d’où il provient. Le résultat de R × T devient alors :

R.A

R.B

T.A

T.B

a

b

m

n

a

b

n

p

x

y

m

n

x

y

n

p

Cette convention pose quelques problèmes quand on crée des expressions complexes. Il existe une seconde possibilité, plus rigoureuse, pour résoudre les conflits de noms : le renommage. Il s’agit d’un opérateur particulier, dénoté ?, qui permet de renommer un ou plusieurs attributs d’une relation. L’expression ?A?C,B?D(T) permet ainsi de renommer A en C et B en D dans la relation T. Le produit cartésien

R × ?A?C,B?D(T)

ne présente alors plus d’ambiguités. Le renommage est une solution très générale, mais asez lourde à utiliser

Il est tout à fait possible de faire le produit cartésien d’une relation avec elle-même. Dans ce cas le renommage où l’utilisation d’un préfixe distinctif est impératif. Voici par exemple le résultat de R ×R, dans lequel on préfixe par R1 et R2 respectivement les attributs venant de chacune des opérandes.

R1.A

R1.B

R1.A

R2.B

a

b

a

b

a

b

x

y

x

y

a

b

x

y

x

y

4.1.5 L’union, ?

Il existe deux autres opérateurs binaires, qui sont à la fois plus simples et moins fréquemment utilisés.

L’union des relations R(A,B) et S(C,D) données en exemple ci-dessus est donc interdite (on ne saurait pas comment nommer les attributs dans le résultat). En revanche, en posant S? = ?C?A,D?B(S), il devient possible de calculer R ? S?, avec le résultat suivant :

A

B

a

b

x

y

c

d

u

v

Comme pour la projection, il faut penser à éviter les doublons. Donc le nuplet (x,y) qui existe à la fois dans R et dans S? ne figure qu’une seule fois dans le résultat.

4.1.6 La différence, ?

Comme l’union, la différence s’applique à deux relations qui ont le même schéma. L’expression R ? S a alors pour résultat tous les nuplets de R qui ne sont pas dans S.

Voici la différence de R et S?, les deux relations étant définies comme précédemment.

A

B

a

b

La différence est le seul opérateur qui permet d’exprimer des requêtes comportant une négation (on veut “rejeter” quelque chose, on “ne veut pas” des lignes ayant telle propriété). Il s’agit d’une fonctionnalité importante et difficile à manier : elle sera détaillée plus loin.

4.1.7 Jointure, on

Toutes les requêtes exprimables avec l’algèbre relationnelle peuvent se construire avec les 5 opérateurs présentés ci-dessus. En principe, on pourrait donc s’en contenter. En pratique, il existe d’autres opérations, très couramment utilisées, qui peuvent se contruire par composition des opérations de base. La plus importante est la jointure.

Afin de comprendre l’intérêt de cet opérateur, regardons le produit cartésien Station × Activit.

4.1. Les opérateurs de l’algèbre

id

nom

capacité

lieu

région

tarif

idStation

libellé

prix

va

Venusa

350

Guadeloupe

Antilles

1200

va

Voile

150

va

Venusa

350

Guadeloupe

Antilles

1200

va

Plongée

120

va

Venusa

350

Antilles

1200

fa

Plongée

130

va

Venusa

350

Guadeloupe

Antilles

1200

pa

Ski

200

va

Venusa

350

Guadeloupe

Antilles

1200

pa

Piscine

20

fa

Farniente

200

Seychelles

Océan Indien

1500

va

Voile

150

fa

Farniente

200

Seychelles

Océan Indien

1500

va

Plongée

120

fa

Farniente

200

Seychelles

Océan Indien

1500

fa

Plongée

130

fa

Farniente

200

Seychelles

Océan Indien

1500

pa

Ski

200

fa

Farniente

200

Seychelles

Océan Indien

1500

pa

Piscine

20

sa

Santalba

150

Martinique

Antilles

2000

va

Voile

150

sa

Santalba

150

Martinique

Antilles

2000

va

Plongée

120

sa

Santalba

150

Martinique

Antilles

2000

fa

Plongée

130

sa

Santalba

150

Martinique

Antilles

2000

pa

Ski

200

sa

Santalba

150

Martinique

Antilles

2000

pa

Piscine

20

pa

Passac

400

Alpes

Europe

1000

va

Voile

150

pa

Passac

400

Alpes

Europe

1000

va

Plongée

120

pa

Passac

400

Alpes

Europe

1000

fa

Plongée

130

pa

Passac

400

Alpes

Europe

1000

pa

Ski

200

pa

Passac

400

Alpes

Europe

1000

pa

Piscine

20

Le résultat comprend manifestement un grand nombre de lignes qui ne nous intéressent pas. Cela ne présente pas beaucoup de sens de rapprocher des informations sur Santalba, aux Antilles et sur l’activité de ski à Passac.

La sélection qui effectue une rapprochement pertinent est la suivante :

?id=idStation(Station × Activit)

Prenez bien le temps de méditer cette opération de sélection : nous ne voulons conserver que les lignes de Station × Activit pour lesquelles l’identifiant de la station (provenant de Station) est identique à celui provenant de Activité. En regardant le produit cartésien ci-dessous, vous devriez pouvoir vous convaincre que cela revient à conserver les lignes qui ont un sens : chacune contient des informations sur une station et sur une activité dans cette même station. Si vous saisissez cette logique, vous avez fait un grand pas dans la connaissance des bases relationelles : consacrez-y le temps de réflexion nécessaire.

On obtient le résultat ci-dessous.

id

nom

capacité

lieu

région

tarif

idStation

libellé

prix

va

Venusa

350

Guadeloupe

Antilles

1200

va

Voile

150

va

Venusa

350

Guadeloupe

Antilles

1200

va

Plongée

120

fa

Farniente

200

Seychelles

Océan Indien

1500

fa

Plongée

130

pa

Passac

400

Alpes

Europe

1000

pa

Ski

200

pa

Passac

400

Alpes

Europe

1000

pa

Piscine

20

On a donc effectué une composition de deux opérations (un produit cartésien, une sélection) afin de rapprocher des informations réparties dans plusieurs tables, mais ayant des liens entre elles (toutes les informations dans un nuplet du résultat sont relatives à une seule station). Cette opération est une jointure, que l’on peut directement, et simplement, noter :

Station onid=idStation Activit

Note : La station Santalba, qui ne propose pas d’activité, n’apparaît pas dans le résultat de la jointure. C’est normal et conforme à la définition que nous avons donnée, mais peut parfois apparaître comme une contrainte. Nous verrons que SQL propose une variante, la jointure externe, qui permet de la contourner.

La notation de la jointure, R onFS, est un racourci pour ?F(R × S). Le critère de rapprochement, F, peut être n’importe quelle opération de comparaison liant un attribut de R à un attribut de S. En pratique, on emploie peu les ?= ou ‘<‘ qui sont difficiles à interpréter, et on effectue des égalités.

Note : Si on n’exprime pas de critère de rapprochement, la jointure est équivalente à un produit cartésien.

Il faut être attentif aux ambiguités dans le nommage des attributs qui peut survenir dans la jointure au même titre que dans le produit cartésien. Les solutions à employer sont les mêmes : on préfixe par le nom de la relation ou par un synonyme clair, ou bien on renomme des attributs avant d’effectuer la jointure.

4.2 Expression de requêtes avec l’algèbre

Cette section est consacrée à l’expression de requêtes algébriques complexes impliquant plusieurs opérateurs. On utilise la composition des opérations, rendue possible par le fait que tout opérateur produit en sortie une relation sur laquelle on peut appliquer à nouveau des opérateurs.

4.2.1 Sélection généralisée

Regardons d’abord comment on peut généraliser les critères de sélection de l’opérateur ?. Jusqu’à présent on a vu comment sélectionner des lignes satisfaisant un critère de sélection, par exemple : “les stations aux Antilles”. Maintenant supposons que l’on veuille retrouver les stations qui sont aux Antilles et dont la capacité est supérieure à 200. On peut exprimer cette requête par une composition :

?capacite>200(?region=?Antilles?(Station))

?capacite>200?region=?Antilles?(Station)

La composition de plusieurs sélections revient à exprimer une conjonction de critères de recherche. De même la composition de la sélection et de l’union permet d’exprimer la disjonction. Voici la requête qui recherche les stations qui sont aux Antilles, ou dont la capacité est supérieure à 200.

?capacite>200(Station) ? ?region=?Antilles?(Station)

Ce qui permet de s’autoriser la syntaxe suivante, où le ‘?‘ dénote le ‘ou’.

?capacite>200 ? region=?Antilles?(Station)

4.2. Expression de requêtes avec l’algèbre

Enfin la différence permet d’exprimer la négation et “d’éliminer” des lignes. Par exemple, voici la requête qui sélectionne les stations dont la capacité est supérieure à 200 mais qui ne sont pas aux Antilles.

?capacite>200(Station) ? ?region=?Antilles?(Station)

Cette requête est équivalente à une sélection où on s’autorise l’opérateur ‘?=‘ :

?capacite>200?region?=?Antilles?(Station)

Important : Attention avec les requêtes comprenant une négation, dont l’interprétation est parfois subtile. D’une manière générale, l’utilisation du ‘?=‘ n’est pas équivalente à l’utilisation de la différence. Voir la prochaine section.

En résumé, les opérateurs d’union et de différence permettent de définir une sélection ?F où le critère F est une expression booléenne quelconque. Attention cependant : si toute sélection avec un ‘ou’ peut s’exprimer par une union, l’inverse n’est pas vrai (exercice).

Les plus simples sont celles où on n’utilise que ? et ?. En voici quelques exemples. — Nom des stations aux Antilles :

?nom(?region=?Antilles?(Station))

— Id des stations où l’on pratique la voile. ?idStation(?libelle=?V oile?(Activite))

— Nom et prénom des clients européens ?nom,prenom(?region=?Europe?(Client))

Des requêtes légèrement plus complexes - et extrêmement utiles - sont celles qui impliquent la jointure. On doit utiliser la jointure dès que les attributs nécessaires pour évaluer une requête sont réparties dans au moins deux tables. Ces “attributs nécessaires” peuvent être :

— Soit des attributs qui figurent dans le résultat;

— Soit des attributs sur lesquels on exprime un critère de sélection.

Considérons par exemple la requête suivante : “Donner le nom et la région des stations où l’on pratique la voile”. Une analyse très simple suffit pour constater que l’on a besoin des attributs région et nom qui apparaîssent dans la relation Station, et de libellé qui apparaît dans Activité.

Donc il faut faire une jointure, de manière à rapprocher les lignes de Station et de Activité. Il reste donc à déterminer le (ou les) attribut(s) sur lesquels se fait ce rapprochement. Ici, comme dans la plupart des cas, la jointure permet de “recalculer” l’association entre les relations Station et Activité. Elle s’effectue donc par appariement de la clé primaire d’une part (dans Station), de la clé étrangère d’autre part.

?nom,region(Station onid=idStation?libelle=?V oile?(Activité))

Voici quelques autres exemples qui illustrent cet état de fait :

— Nom des clients qui sont allés à Passac : ?nom(Client onid=idClient?nom=?Passac?(Séjour)) — Quelles régions a visité le client 30 :

?region(?idClient=30(Sejour) onidStation=id (Station))

— Nom des clients qui ont eu l’occasion de faire de la voile : ?nom(Client onid=idClient (Sejour onidStation=idStation?libelle=?V oile?(Activite)))

La dernière requête comprend deux jointures, portant à chaque fois sur des clés primaires et/ou étrangères. Encore une fois ce sont les clés qui définissent les liens entre les relations, et elle servent donc naturellement de support à l’expression des requêtes.

Voici maintenant un exemple qui montre que cette règle n’est pas systématique. On veut exprimer la requête qui recherche les noms des clients qui sont partis en vacances dans leur région, ainsi que le nom de cette région. Ici on a besoin des informations réparties dans les relations Station, Séjour et Client. Voici l’expression algébrique :

?nom,client.region(Client onid=idClient?region=region (Sejour onidStation=idStation))

Les jointures avec la table Séjour se font sur les couples (clé primaire, clé étrangère), mais on a en plus un critère de rapprochement relatif à l’attribut région de Client et de Station.

Note : Dans la projection finale, on utilise la notation client.region pour éviter toute ambiguité.

Illlustration concrète sur la base de données avec la requête suivante : quelles sont les identifiants des stations qui ne proposent pas de voile?

?id(Station) ? ?idStation(?libelle=?V oile?(Activite))

Comme le suggère cet exemple, la démarche générale pour construire une requête du type “Tous les O qui ne satisfont pas la propriété p” est la suivante :

— Construire une première requête A qui sélectionne tous les O.

— Construire une deuxième requête Bquislectionnetousles : math : ‘Oqui satisfont :math :‘p‘. — Finalement, faire A ? B.

Les requêtes A et B peuvent bien entendu être arbitrairement complexes et mettre en oeuvre des jointures, des sélections, etc. La seule contrainte est que le résultat de A et de B comprenne le même nombre d’attributs.

Important : Attention à ne pas considérer que l’utilisation du comparateur ?= est équivalent à la différence. La requête suivante par exemple ne donne pas les stations qui ne proposent pas de voile

?idStation(?libelle ?= ?V oile?(Activite))

Pas convaincu(e)? Réfléchissez un peu plus, faites le calcul concret. C’est l’un de pièges à éviter.

Voici quelques exemples complémentaires qui illustrent ce principe. — Régions où il y a des clients, mais pas de station.

?region(Client) ? ?region(Station)

4.2. Expression de requêtes avec l’algèbre

— Identifiant des stations qui n’ont pas reçu de client américain.

?id(Station) ? ?idStation(Sejour onidClient=id?region=?Amerique?(Client))

— Id des clients qui ne sont pas allés aux Antilles.

?idClient(Client) ? ?idClient(?region=?Antilles?(Station) onid=idStationSejour)

La dernière requête construit l’ensemble des idClient pour les clients qui ne sont pas allés aux Antilles. Pour obtenir le nom de ces clients, il suffit d’ajouter une jointure (exercice).

C’est un des rares cas où le produit cartésien seul est utile : il permet justement de constituer “toutes les associations possibles”. Il reste ensuite à en soustraire celles qui sont dans la base avec l’opérateur ?.

(?id(Client) × ?id(Station)) ? ?idClient,idStation(Sejour)

4.2.5 Quantification universelle

Enfin la différence est nécessaire pour les requêtes qui font appel à la quantification universelle : celles où l’on demande par exemple qu’une propriété soit toujours vraie. A priori, on ne voit pas pourquoi la différence peut être utile dans de tels cas. Cela résulte simplement de l’équivalence suivante : une propriété est vraie pour tous les éléments d’un ensemble si et seulement si il n’existe pas un élément de cet ensemble pour lequel la propriété est fausse. La quantification universelle s’exprime par une double négation.

En pratique, on se ramène toujours à la seconde forme pour exprimer des requêtes. Prenons un exemple : quelles sont les stations dont toutes les activités ont un prix supérieur à 100? On l’exprime également par ‘quelles sont stations pour lesquelles il n’existe pas d’activité avec un prix inférieur à 100’. Ce qui donne l’expression suivante :

?id(Station) ? ?idStation(?prix<100(Activite))

Pour finir, voici une des requêtes les plus complexes, la division. L’énoncé (en français) est simple, mais l’expression algébrique ne l’est pas du tout. L’exemple est le suivant : on veut les ids des clients qui sont allés dans toutes les stations.

Traduit avec (double) négation, cela donne : les ids des clients tels qu’il n’existe pas de station où ils ne soient pas allés. Ce qui donne l’expression algébrique suivante :

?id(Client) ? ?id((?id(Client) × ?id(Station)) ? ?idClient,idStation(Sejour))

On réutilise l’expression donnant les clients et les stations où ils ne sont pas allés (voir plus haut) :

On obtient un ensemble B. Il reste à prendre tous les clients, sauf ceux qui sont dans B.

?id(Client) ? B

Ce type de requête est rare (heureusement) mais illustre la capacité de l’algèbre à exprimer par de simples manipulations ensemblistes des opérations complexes.


CHAPITRE5

SQL

Le langage SQL est l’outil standard pour effectuer des recherches ou mises à jour dans une base de données relationnelle. Ce chapitre est consacré à la partie Langage de Manipulation de Données (LMD) complémentaire de la partie Langage de Définition de Données (LDD) présentée précédemment.

SQL est un langage relativement facile dans la mesure où il ne permet que des opérations assez limitées : manipuler plusieurs tables en entrée (celles de la base) pour obtenir une table en sortie (le résultat). La seule véritable difficulté réside dans l’interprétation des requêtes complexes qui font parfois appel à des logiques sophistiquées. Par ailleurs la multiplicité des variantes syntaxiques offre de nombreuses manières d’exprimer la même interrogation ce qui peut parfois troubler. Nous avons donc choisi d’insister sur la signification des opérations SQL et sur la démarche de conception d’une requête.

Nous utilisons le schéma de la base Immeuble du chapitre précédent et l’instance de base donnée ci-dessous. Ce schéma et cette base sont fournis respectivement dans les scripts et sur notre site si vous souhaitez effectuer réellement les requêtes proposées parallèlement à votre lecture.

La table Immeuble

Voici le contenu de la table Immeuble.

id

nom

adresse

1

Koudalou

3 Rue Blanche

2

Barabas

2 Allée Nikos

La table Appart

Voici le contenu de la table Appart.

id

no

surface

étage

id_immeuble

100

1

150

14

1

101

34

50

15

1

102

51

200

2

1

103

52

50

5

1

201

1

1

2

202

2

250

2

2

La table Personne

Voici le contenu de la table Personne.

id

prénom

nom

profession

id_appart

1

Ross

Informaticien

202

2

Alice

Black

Cadre

103

3

Rachel

Verte

Stagiaire

100

4

William

Dupont

Acteur

102

5

Doug

Ramut

Rentier

201

La table Possede

Voici le contenu de la table Possede.

id_personne

id_appart

quote_part

1

100

33

5

100

67

1

101

100

5

102

100

1

202

100

5

201

100

2

103

100

5.1 Principes de SQL

Nous commençons par un abrégé d’interprétation des requêtes SQL qui va nous guider pendant tout ce chapitre. Il existe quelques principes généraux qui s’appliquent quelle que soit la complexité de la requête et auxquels il est très utile de pouvoir se ramener en présence d’un cas complexe.

5.1.1 Forme de base et interprétation

La recherche s’effectue avec la commande select dont la forme de base est :

select liste_expressions from source

[where liste_conditions]

L’ordre des trois clauses selectfrom et where est trompeur pour la signification d’une requête. En fait l’inteprétation s’effectue toujours de la manière suivante :

— la clause from définit l’espace de recherche en fonction d’un ensemble de sources de données : cet espace a toujours conceptuellement la forme d’une table relationnelle que nous appellerons Tfrom;

— la clause where exprime un ensemble de conditions sur les lignes de la table Tfrom : seules les lignes pour lesquelles ces conditions sont satisfaites sont conservées;

— enfin la clause select consiste en une liste d’expressions appliquées à chaque ligne de Tfrom ayant passé le filtre du where.

select nom adresse from Immeuble where id=1

Cette requête renvoie le résultat suivant.

nom

adresse

Koudalou

3 Rue Blanche

L’espace de recherche est ici constitué d’une seule table de la base Immeuble. Il s’agit du cas le plus simple et le plus courant. On évalue ensuite pour toutes les lignes de cette table les conditions exprimées dans le where : seules les lignes (une seule en l’occurrence) pour lesquelles l’attribut id vaut 1 satisfont cette condition. Finalement on extrait de cette ligne les valeurs des attributs nom et adresse.

5.1.2 L’espace de recherche : clause from

L’espace de recherche est défini dans la clause from par une table ou une combinaison de plusieurs tables. Par “table” il ne faut pas ici comprendre forcément “une des tables de la base” courante même si c’est le cas le plus souvent rencontré. SQL est beaucoup général que cela : une table dans un from peut également être résultat d’une autre requête. On parlera de table basée et de table calculée pour distinguer ces deux cas. Ce peut également être une table stockée dans une autre base ou une table calculée à partir de tables basées dans plusieurs bases ou une combinaison de tout cela.

La première requête effectuée ci-dessus ramène les immeubles dont l’id vaut 1. Il n’aura pas échappé au lecteur attentif que le résultat est lui-même une table calculée. Pourquoi ne pourrait-on pas interroger cette table calculée comme une autre? C’est possible en SQL comme le montre l’exemple suivant :

select *

from (select nom adresse from Immeuble where id=1) as Koudalou

On peut aller un peu plus loin et donner définivement un nom à cette requête qui sélectionne l’immeuble. En SQL cela s’appelle une vue. On crée une vue dans un schéma avec la commande create view (les vues sont traitées en détail plus loin). Par exemple :

create view Koudalou as select nom adresse from Immeuble where id=1

Une fois créée une vue peut être utilisée comme espace de recherche exactement comme une table basée. Le fait que son contenu soit calculé reste transparent pour l’utilisateur.

select nom adresse from Koudalou

nom

adresse

Koudalou

3 Rue Blanche

Encore une fois, l’interprétation du from est indépendante de l’origine des tables : tables basées, tables calculées, et vues.

Note : On peut également interroger les tables ou vues d’une autre base pourvu que l’utilisateur connecté ait les droits suffisants. Dans ce cas on préfixe le nom de la table par le nom de la base.

Venons-en maintenant au cas où le from est défini par plusieurs tables. Dans un tel cas les contenus des tables sont toujours combinées de manière à définir une table virtuelle (le Tfrom évoqué précédemment) qui tient lieu d’espace de recherche par la suite. La combinaison la plus simple de deux tables A et B, obtenue en séparant les noms des tables par une virgule, consiste à effectuer toutes les associations possibles d’une ligne de A et d’une ligne de B. Voici par exemple ce que cela donne pour les tables Immeuble et Appart.

select * from Immeuble, Appart

L’affichage ci-dessus nous montre quel est l’espace de recherche Tfrom considéré quand on place ces deux tables dans un from.

5.1. Principes de SQL

id

nom

adresse

id

surface

etage

id_immeuble

no

1

Koudalou

3 Rue Blanche

100

150

14

1

1

2

Barabas

2 Allée Nikos

100

150

14

1

1

1

Koudalou

3 Rue Blanche

101

50

1

34

2

Barabas

2 Allée Nikos

101

50

15

1

34

1

Koudalou

3 Rue Blanche

102

200

2

1

51

2

Barabas

2 Allée Nikos

102

200

2

1

51

1

Koudalou

3 Rue Blanche

103

50

5

1

52

2

Barabas

2 Allée Nikos

103

50

5

1

52

1

Koudalou

3 Rue Blanche

201

250

1

2

1

2

Barabas

2 Allée Nikos

201

250

1

2

1

1

Koudalou

3 Rue Blanche

202

250

2

2

2

2

Barabas

2 Allée Nikos

202

250

2

2

2

La virgule est en fait un synonyme de cross join, terme qui indique que l’on effectue un produit cartésien (cross product) des deux tables vues comme des ensembles. Reportez-vous au chapitre L’algèbre relationnelle (page 41) pour des explications détaillées. La requête donnant le même résultat est donc :

select*from Immeuble cross join Appart

D’autre types de combinaisons plus restrictives et souvent plus utiles sont les jointures. Regardons à nouveau le résultat précédent. Le fait d’associer chaque ligne de Immeuble à chaque ligne de Appart nous donne des résultats peu utiles à priori. La seconde ligne associe par exemple le Barabas à un appartement du Koudalou. On ne voit pas quelle application peut trouver intérêt à un tel regroupement. En revanche, associer un appartement à l’immeuble où il se situe est très utile (ne serait-ce que pour trouver son adresse).

table1 join table2 on (conditions)

Donc on peut effectuer la jointure dans la clause from. On obtient un espace de recherche constitué des paires de lignes pour lesquelles la condition de jointure est vraie.

select *

from Immeuble join Appart on (=Appart.id_immeuble)

On obtient le résultat suivant.

id

nom

adresse

id

surface

etage

id_immeuble

no

1

Koudalou

3 Rue Blanche

100

150

14

1

1

1

Koudalou

3 Rue Blanche

101

50

15

1

34

1

Koudalou

3 Rue Blanche

102

200

2

1

51

1

Koudalou

3 Rue Blanche

103

50

5

1

52

2

Barabas

2 Allée Nikos

201

250

1

2

1

2

Barabas

2 Allée Nikos

202

250

2

2

2

Les jointures seront revues plus loin. Comment souvent en SQL il existe plusieurs moyens de les exprimer, la plus utilisée consistant à effectuer un produit cartésien (virgules dans le from) suivi de conditions de jointures exprimées dans le where.

Ce qu’il faut retenir : le from consiste à définir un espace de recherche qui est constitué par combinaison d’une ou plusieurs tables basées ou calculées provenant du même schéma ou d’un autre schéma. Quel que soit le cas cet espace est lui-même une table (calculée) dont on va chercher à extraire une ou plusieurs lignes (par le where) puis une ou plusieurs colonnes (par le select).

Beaucoup de difficultés d’interprétation proviennent d’une mauvaise compréhension de l’espace dans lequel on cherche les données. La première étape pour les résoudre consiste à se poser correctement la question : que signifie le from de ma requête.

select*from Appart where surface > 50

id

surface

etage

id_immeuble

no

100

150

14

1

1

102

200

2

1

51

201

250

1

2

1

202

250

2

2

2

select * from Appart where surface > 50 and etage > 3

id

surface

etage

id_immeuble

no

100

150

14

1

1

select * from Appart where (surface > 50 and etage > 3) or id_immeuble=2

id

surface

etage

id_immeuble

no

100

150

14

1

1

201

250

1

2

1

202

250

2

2

2

Il est également possible d’exprimer des conditions sur des tables calculées par d’autre requêtes SQL incluses dans la clause where et habituellement désignées par le terme de “requêtes imbriquées”. On pourra par exemple demander la liste des personnes dont l’appartement fait partie de la table calculée des appartements situés au-dessus du troisième étage.

select * from Personne

where id_appart in (select id from Appart where etage < 3)

id

prenom

nom

profession

id_appart

1

null

Ross

Informaticien

202

4

William

Dupont

Acteur

102

5

Doug

Ramut

Rentier

201

Avec les requêtes imbriquées on entre dans le monde incertain des requêtes qui semblent claires mais finissent par ne plus l’être du tout. La difficulté vient souvent du fait qu’il faut raisonner simultanément sur plusieurs requêtes qui, de plus, sont souvent interdépendantes (les données sélectionnées dans l’une servent de paramètre à l’autre). Il est très souvent possible d’éviter les requêtes imbriquées comme nous l’expliquons dans ce chapitre.

5.1. Principes de SQL

select surface etage 18 as 'Euros/m2' from Appart

surface

etage

Euros/m2

150

14

18

50

15

18

200

2

18

50

5

18

250

1

18

250

2

18

Les attributs surface et etage proviennent de Appart alors que 18 est une constante qui sera répétée autant de fois qu’il y a de lignes dans le résultat. De plus, on peut donner un nom à cette colonne avec la commande as. Voici un second exemple qui montre une expression plus complexe. L’utilisateur (certainement un agent immobilier avisé et connaissant bien SQL) calcule le loyer d’un appartement en fonction d’une savante formule qui fait intervenir la surface et l’étage.

select no, surface, etage,

(surface * 18) * (1 (0.03 * etage)) as loyer from Appart

no

surface

etage

loyer

1

150

14

3834.00

34

50

15

1305.00

51

200

2

3816.00

52

50

5

1035.00

1

250

1

4635.00

2

250

2

4770.00

SQL fournit de très nombreux opérateurs et fonctions de toute sorte qui sont clairement énumérées dans la documentation de chaque système. Elles sont particulièrement utiles pour des types de données un peu délicat à manipuler comme les dates.

Une extension rarement utilisée consiste à effectuer des tests sur la valeur des attributs à l’intérieur de la clause select avec l’expression case dont la syntaxe est :

case when test then expression

[when ] else expression end

Ces tests peuvent être utilisés par exemple pour effectuer un décodage des valeurs quand celles-ci sont difficiles à interpréter ou quand on souhaite leur donner une signification dérivée. La requête ci-dessous classe les appartements en trois catégories selon la surface.

select no etage surface case when surface <= 50 then 'Petit' when surface > 50 and surface <= 100 then 'Moyen' else 'Grand'

from Appart

no

etage

surface

catégorie

1

14

150

Grand

34

15

50

Petit

51

2

200

Grand

52

5

50

Petit

65

12

70

Moyen

65

12

70

Moyen

1

1

250

Grand

2

2

250

Grand

Voici donc tout ce qu’il faut savoir sur l’interprétation des requêtes SQL exprimées “à plat”, sans requête imbriquée. Nous reprenons maintenant la présentation du langage en apportant des précisions sur chaque partie.

5.2 Recherche avec SQL

La recherche la plus simple consiste à récupérer le contenu complet d’une table. On n’utilise pas la clause where et le * désigne tous les attributs.

select * from Immeuble

id

nom

adresse

1

Koudalou

3 Rue Blanche

2

Barabas

2 Allée Nikos

5.2.1 Construction d’expressions

Si on indique explicitement les attributs au lieu d’utiliser *, leur nombre détermine le nombre de colonnes de la table calculée. Le nom de chaque attribut dans cette table est par défaut l’expression du select mais on peut indiquer explicitement ce nom avec as. Voici un exemple qui illustre également une fonction assez utile, la concaténation de chaînes.

select prenom || nom as 'Prenom et nom' from Personne

Prenom et nom

null

Alice Black

Rachel Verte

William Dupont

Doug Ramut

Le résultat montre que l’une des valeurs est à null. Le null en SQL correspond à l’absence de valeur. Logiquement toute opération appliquée à un null renvoie un null en sortie puisqu’on ne peut calculer aucun résultat à partir d’une valeur inconnue. Ici c’est le prénom de l’une des personnes qui manque. La concaténation du prénom avec le nom est une opération qui “propage” cette valeur à null.

5.2. Recherche avec SQL

Les conditions se combinent avec les connecteurs booléens andor et not. SQL propose également un prédicat in qui teste l’appartenance d’une valeur à un ensemble. Il s’agit (du moins tant qu’on n’utilise pas les requêtes imbriquées) d’une facilité d’écriture pour remplacer le or. La requête

select * from Personne where profession='Acteur' or profession='Rentier'

s’écrit de manière équivalente avec un in comme suit :

select * from Personne

where profession in ('Acteur', 'Rentier')

id

prenom

nom

profession

id_appart

4

William

Dupont

Acteur

102

5

Doug

Ramut

Rentier

201

Pour les chaînes de caractères, SQL propose l’opérateur de comparaison like, avec deux caractères de substitution : — le “%” remplace n’importe quelle sous-chaîne; — le “_” remplace n’importe quel caractère.

L’expression _ou%ou est donc interprétée par le like comme toute chaîne commençant par un caractère suivi de “ou” suivi de n’importe quelle chaîne suivie une nouvelle fois de “ou”.

select * from Immeuble where nom like '_ou%ou'

id

nom

adresse

1

Koudalou

3 Rue Blanche

5.2.3 Valeurs nulles

Il est impossible de déterminer quoi que ce soit à partir d’une valeur à null. Nous avons vu que toute opération appliquée à un null renvoie null. Dans le cas des comparaisons, la présence d’un null renvoie un résultat qui n’est ni true ni false mais unknown, une valeur booléenne intermédiaire. Reprenons à nouveau la table Personne avec un des prénoms à null. La requête suivante devrait ramener toutes les lignes.

select * from Personne where prenom like '%'

Mais la présence d’un null empêche l’inclusion de la ligne correspondante dans le résultat.

id

prenom

nom

profession

id_appart

2

Alice

Black

Cadre

103

3

Rachel

Verte

Stagiaire

4

William

Dupont

Acteur

102

5

Doug

Ramut

Rentier

201

Cependant la condition like n’a pas été évaluée à false comme le montre la requête suivante.

select * from Personne where prenom not like

'%'

On obtient un résultat vide, ce qui montre bien que le like appliqué à un null ne renvoie pas false (car sinon on aurait not false = true). C’est d’ailleurs tout à fait normal puisqu’il n’y a aucune raison de dire qu’une absence de valeur ressemble à n’importe quelle chaîne.

Les tables de vérité de la logique trivaluée de SQL sont définies de la manière suivante. Tout d’abord on affecte une valeur aux trois constantes logiques :

— true vaut 1

— false vaut 0

— unknown vaut 0.5

Les connecteurs booléens s’interprètent alors ainsi :

— val1 and val2 = max(val1 val2) — val1 or val2 = min(val1 val2) — not val1 = 1 - val1.

On peut vérifier notamment que not unknown vaut toujours unknown. Ces définitions sont claires et cohérentes. Cela étant il faut mieux prévenir de mauvaises surprises avec les valeurs à null, soit en les interdisant à la création de la table avec les options not null ou default, soit en utilisant le test is null (ou son complément is not null). La requête ci-dessous ramène toutes les lignes de la table, même en présence de null.

select * from Personne where prenom like '%' or prenom is null

id

prenom

nom

profession

id_appart

1

null

Ross

Informaticien

202

2

Alice

Black

Cadre

103

3

Rachel

Verte

Stagiaire

100

4

William

Dupont

Acteur

102

5

Doug

Ramut

Rentier

201

Attention le test valeur = null n’a pas de sens. On ne peut pas être égal à une absence de valeur.

select surface from Appart

on obtient le résultat suivant.

surface

150

50

200

50

250

250

5.2. Recherche avec SQL

On a autant de fois une valeur qu’il y a de lignes dans le résultat intermédiaire après exécution des clauses from et where. En général, on ne souhaite pas conserver ces lignes identiques dont la répétition n’apporte aucune information. Le mot-clé distinct placé juste après le select permet d’éliminer ces doublons.

select distinct surface from Appart

surface

150

50

200

250

Le distinct est à éviter quand c’est possible car l’élimination des doublons peut entraîner des calculs coûteux. Il faut commencer par calculer entièrement le résultat, puis le trier ou construire une table de hachage, et enfin utiliser la structure temporaire obtenue pour trouver les doublons et les éliminer. Si le résultat est de petite taille cela ne pose pas de problème. Sinon, on risque de constater une grande différence de temps de réponse entre une requête sans distinct et la même avec distinct.

On peut demander explicitement le tri du résultat sur une ou plusieurs expressions avec la clause order by qui vient toujours à la fin d’une requête select. La requête suivante trie les appartements par surface puis, pour ceux de surface identique, par l’étage.

select * from Appart order by surface etage

id

surface

etage

id_immeuble

no

103

50

5

1

52

101

50

15

1

34

100

150

14

1

1

102

200

2

1

51

201

250

1

2

1

202

250

2

2

2

Par défaut, le tri est en ordre ascendant. On peut inverser l’ordre de tri d’un attribut avec le mot-clé desc .

select * from Appart

order by surface desc, etage desc

id

surface

etage

id_immeuble

no

202

250

2

2

2

250

1

2

1

102

200

2

1

51

100

150

14

1

1

101

50

15

1

34

103

50

5

1

52

Bien entendu, on peut trier sur des expressions au lieu de trier sur de simples noms d’attribut.

5.3 Jointures

La jointure est une opération indisoensable dès que l’on souhaite combiner des données réparties dans plusieurs tables.

À la base de la jointure, on trouve le produit cartésien qui consiste à trouver toutes les associations possibles d’une


ligne de la première table avec une ligne de la seconde. La jointure consiste à restreindre le résultat du produit cartésien en ne conservant que les associations qui sont intéressantes pour la requête.

Il existe beaucoup de manières différentes d’exprimer les jointures en SQL. Pour les débutants, il est recommandé de se limiter à la forme de base donnée ci-dessous qui est plus facile à interpréter et se généralise à un nombre de tables quelconques.

5.3.1 Syntaxe classique

La méthode la plus courante consiste à effectuer un produit cartésien dans le from puis à éliminer les lignes inutiles avec le where

Important : Attention, nous parlons ici de la manière dont on conçoit la requête pas de l’évaluation par le système qui essaiera en général d’éviter le calcul complet du produit cartésien.

Prenons l’exemple d’une requête cherchant la surface et l’étage de l’appartement de M. William Dupont. On doit associer deux tables. La première est une table calculée contenant les informations sur William Dupont. On l’obtient par la requête suivante.

select * from Personne where prenom='William' and nom='Dupont'

id

prenom

nom

profession

id_appart

4

William

Dupont

Acteur

102

select p.id, nom, prenom, id_appart, a.id, surface, etage from Personne as p, Appart as a where prenom='William' and nom='Dupont'

Ce qui donne le résultat suivant :

id

nom

prenom

id_appart

id

surface

etage

4

Dupont

William

102

100

150

14

4

Dupont

William

102

101

50

15

4

Dupont

William

102

102

200

2

4

Dupont

William

102

103

50

5

4

Dupont

William

102

201

250

1

4

Dupont

William

102

202

250

2

Une première difficulté à résoudre quand on utilise plusieurs tables est la possibilité d’avoir des attributs de même nom dans l’union des schémas, ce qui soulève des ambiguités dans les clauses where et select. On résout cette ambiguité en préfixant les attributs par le nom des tables dont ils proviennent. Ici, on a même simplifié l’écriture en donnant des alias aux nom des tables avec le mot-clé as. La table Personne et la table Appart sont donc respectivement référencées par p et a.

Notez que la levée de l’ambiguité en préfixant par le nom ou l’alias de la table n’est nécessaire que pour les attributs qui apparaissent en double soit ici id qui peut désigner l’identifiant de la personne ou celui de l’appartement.

select p.id, p.nom, p.prenom, p.id_appart, a.id, a.surface, a.etage from Personne as p Appart as a where prenom='William' and nom='Dupont' and             a.id = p.id_appart

id

nom

prenom

id_appart

id

surface

etage

4

Dupont

William

102

102

200

2

Comme dans la très grande majorité des cas la jointure consiste à exprimer une égalité entre la clé primaire de l’une des tables et la clé étrangère correspondante de l’autre. Un peu de réflexion permet de réaliser que l’on recrée ainsi le lien défini au niveau du modèle entitéassociation. Mais SQL est plus puissant que cette reconstitution puisqu’on peut exprimer des conditions de jointure sur n’importe quel attribut et pas seulement sur ceux qui sont des clés.

Imaginons que l’on veuille trouver les appartements d’un même immeuble qui ont la même surface. On veut associer une ligne de Appart à une autre ligne de Appart avec les conditions suivantes :

— elles sont dans le même immeuble (attribut id_immeuble); — elles ont la même valeur pour l’attribut surface;

— elles correspondent à des appartements distincts (attributs id).

La requête exprimant ces conditions est donc :

select , a1.surface, a1.etage, , a2.surface, a2.etage from Appart a1 Appart a2 where != and a1.surface = a2.surface and a1.id_immeuble = a2.id_immeuble

Ce qui donne le résultat suivant :

id

surface

etage

id

surface

etage

101

50

15

103

50

5

103

50

5

101

50

15

201

250

1

202

250

2

202

250

2

201

250

1

On peut noter que dans le résultat la même paire apparaît deux fois avec des ordres inversés. On peut éliminer cette redondance en remplaçant != par < .

Voici quelques exemples complémentaires de jointures impliquant parfois plus de deux tables exprimées par produit cartésien, puis restriction avec where.

— Qui habite un appartement de plus de 200 m2?

select prenom, nom, profession from Personne, Appart where id_appart = and surface >= 200

Attention à lever l’ambiguité sur les noms d’attributs quand ils peuvent provenir de deux tables (c’est le cas ici pour id).

— Qui habite le Barabas?

select prenom, p.nom, no, surface, etage from         Personne as p, Appart as a, Immeuble as i whereand              and    i.nom='Barabas'

— Qui habite un appartement qu’il possède et avec quelle quote-part?

select prenom, nom, quote_part from Personne as p, Possede as p2, Appart as a where p.id=p2.id_personne /* p est propriétaire */and               /* de l'appartement a */and       /* et il y habite        */

— De quel(s) appartement(s) Alice Black est-elle propriétaire et dans quel immeuble?

Voici la requête sur les quatre tables avec des commentaires inclus montrant les jointures.

select i.nom, no, etage, surface

from Personne as p, Appart as a, Immeuble as i, Possede as p2 where p.id=p2.id_personne /* Jointure PersonnePossede */and      p2.id_appart = a.id /* Jointure PossedeAppart */and          a.id_immeuble= i.id /* Jointure AppartImmeuble */and        p.nom='Black' and p.prenom='Alice'

Attention à lever l’ambiguité sur les noms d’attributs quand ils peuvent provenir de deux tables (c’est le cas ici pour id).

Toutes ces jointures peuvent s’exprimer avec d’autres syntaxes : tables calculées dans le from opérateur de jointure dans le from ou (pas toujours) requêtes imbriquées. À l’exception notable des jointures externes et des requêtes imbriquées avec négation, elles n’apportent aucune expressivité supplémentaire. Toutes ces variantes constituent des moyens plus ou moins commodes d’exprimer différemment la jointure.

5.3.2 Tables calculées dans le from

La possibilité de placer des requêtes dans le from est prise en compte depuis peu de temps par les SGBD relationnels. Cette option n’apporte rien en terme d’expressivité mais elle peut (question de goût) parfois être considérée comme plus claire ou plus proche du raisonnement intuitif. Elle ne sert que dans les cas où il existe un critère de sélection sur une table. En revanche les conditions de jointure restent dans le where.

Nous reprenons quelques exemples de requêtes que nous avons déjà résolues avec des jointures “à plat”. — Quel est l’appartement où habite M. William Dupont?

select no, surface, etage

from Appart, (select id_appart from Personne

where prenom='William' and nom='Dupont') as Dupont

where id=id_appart

no

surface

etage

51

200

2

Un (léger) avantage est de supprimer dans la table calculée les attributs qui soulèvent des ambiguités (par exemple l’id de la personne qui ne sert à rien par la suite). Il faut toujours donner un alias avec le mot-clé as à la table calculée.

— Qui habite un appartement de plus de 200 m2?

select prenom, nom, profession from Personne as p, (select id from Appart where surface>=200) as a where

— Qui habite le Barabas?

Dernier exemple montrant la possibilité d’effectuer une jointure pour obtenir la table calculée.

select prenom, nom, no, surface, etage from       Personne p, (select , no, surface, etage

where

5.3.3 Opérateurs de jointure

La virgule séparant deux tables dans un from exprime un produit cartésien : toutes les combinaisons de lignes sont considérées puis soumises aux conditions du where. Comme nous l’avons vu il s’agit d’un moyen d’effectuer la jointure. Mais SQL propose également des opérateurs de jointure dans le from avec la syntaxe suivante : table1

opérateur table2 [condition]

Les opérateurs disponibles sont :

— cross join le produit cartésien synonyme de la virgule;

— join la jointure accompagnée de conditions;

— straight join jointure forçant l’ordre d’accès aux tables (déconseillé : mieux vaut laisser faire le système);

— left [outer] join et right [outer] join : jointures externes; — natural [left,right [outer]] join : jointure “naturelle”.

Le seul opérateur qui n’est pas redondant avec des syntaxes présentées précédemment est la jointure externe (outer). On peut à bon droit considérer que cette multiplication de variantes syntaxiques ne fait que compliquer le langage. Nous présentons dans ce qui suit les opérateurs join et outer join. La jointure naturelle est une variante possible quand la clé primaire et la clé étrangère ont le même nom.

L’opérateur join permet d’exprimer directement dans le from les conditions de jointure. Il est très proche dans sa conception des jointures à plat étudiées précédemment. Quelques exemples reprenant des requêtes déjà exprimées suffiront pour comprendre le mécanisme.

— Quel est l’appartement où habite M. William Dupont?

select no, surface, etage from Personne join Appart on () where prenom='William' and nom='Dupont'

Il suffit de comparer avec la version combinant produit cartésien et where pour se rendre compte que la différence est minime. On peut tout à fait considérer que la version avec join est plus claire.

— Qui habite un appartement de plus de 200 m2?

Cet exemple illustre un “détournement” de la condition de jointure. On y a inclus une condition de sélection (la surface supérieure à 200). La requête est correcte mais il est possible que la méthode d’évaluation ne soit pas optimale.

Il faut souligner (nous y reviendrons) que ces variantes alternatives représentent un défi pour le module chargé d’optimiser l’évaluation des requêtes. Un avantage potentiel des jointures à plat où tout est exprimé dans le where est qu’elles offrent une forme canonique à partir de laquelle un optimiseur peut travailler avec un maximum de liberté.

— Qui habite le Barabas?

On peut composer deux opérateurs de jointure comme le montre ce dernier exemple.

select prenom, p.nom, no, surface, etage from Personne p join (Appart a join Immeuble i on )

on ()

where i.nom='Barabas'

Contrairement aux jointures exprimées avec join, les jointures externes ne peuvent pas s’exprimer avec un where. Qu’est-ce qu’une jointure externe? Effectuons la requête qui affiche tous les appartements avec leur occupant.

select id_immeuble, no, etage, surface, nom, prenom from Appart as a join Personne as p on ()

Voici ce que l’on obtient :

id_immeuble

no

etage

surface

nom

prenom

2

2

2

250

Ross

null

1

52

5

50

Black

Alice

1

1

14

150

Verte

Rachel

1

51

2

200

Dupont

William

2

1

1

250

Ramut

Doug

Il manque un appartement le 34 du Koudalou. En effet cet appartement n’a pas d’occupant. Il n’y a donc aucune possibilité que la condition de jointure soit satisfaite.

Si pour une ligne de la table de gauche on trouve une ligne satisfaisant le critère de jointure dans la table de droite, alors la jointure s’effectue normalement. Sinon, les attributs provenant de la table de droite sont affichés à null. Voici la jointure externe entre Appart et Personne. Le mot-clé outer est optionnel.

select id_immeuble, no etage, surface, nom, prenom

from Appart as a left outer join Personne as p on ()

id_immeuble

no

etage

surface

nom

prenom

1

1

14

150

Verte

Rachel

1

34

15

50

null

null

1

51

2

200

Dupont

William

1

52

5

50

Black

Alice

2

1

1

250

Ramut

Doug

2

2

2

250

Ross

null

Notez les deux attributs prenom et nom à null pour l’appartement 34.

Il existe un right outer join qui prend la table de droite comme table directrice. On peut combiner la jointure externe avec des jointures normales des sélections des tris etc. Voici la requête qui affiche le nom de l’immeuble en plus des informations précédentes et trie par numéro d’immeuble et numéro d’appartement.

select i.nom, no, etage, surface, p.nom, prenom from Immeuble as i

join

(Appart as a left outer join Personne as p on ())

on (i.id=a.id_immeuble)

order by i.id, a.no

5.3.4 Opérations ensemblistes

La norme SQL ANSI comprend des opérations qui considèrent les tables comme des ensembles et effectuent des intersections des unions ou des différences avec les mots-clé union, intersect ou except. Chaque opérateur s’applique à deux tables de schéma identique (même nombre d’attributs mêmes noms mêmes types).

L’union est un opérateur peu utilisé car il est difficile de trouver des cas où son application est justifiée. Voici un exemple montrant le calcul de l’union des noms d’immeuble et des noms de personne.

select nom from Immeuble union

Le except exprime la différence entre deux ensembles. Il est avantageusement remplacé par l’utilisation des requêtes imbriquées et des not in et not exists présentés ci-dessous.

5.3.5 Requêtes imbriquées

Les requêtes peuvent être imbriquées les unes dans les autres de deux manières :

— dans la clause from : la requête est placée entre parenthèses et son résultat est vu comme une table;

— dans la clause where avec l’opérateur in qui permet de tester l’appartenance de la valeur d’un attribut à un ensemble calculé par une requête.

Reprenons l’exemple de la requête trouvant la surface et l’étage de l’appartement de M. Dupont. On peut l’exprimer avec une requête imbriquée de deux manières :

select surface, etage from Appart (select id_appart

from Personne where prenom='William' and nom='Dupont') as ri

where = ri.id_appart

select surface, etage from Appart where id in (select id_appart

from Personne where prenom='William' and nom='Dupont')

Le mot-clé in exprime la condition d’appartenance de l’identifiant de l’appartement à l’ensemble d’identifiants constitué avec la requête imbriquée. Il doit y avoir correspondance entre le nombre et le type des attributs auxquels s’applique la comparaison par in. L’exemple suivant montre une comparaison entre des paires d’attributs (ici on cherche des informations sur les propriétaires).

select prenom, nom, surface, etage from Appart as a join Personne as p on (a.id=p.id_appart) where (p.id p.id_appart)

in (select id_personne id_appart from Possede)

prenom

nom

surface

etage

null

Ross

250

2

Alice

Black

50

5

Doug

Ramut

250

1

select * from Immeuble

where id in (select id_immeuble from Appart where surface=50)

id

nom

adresse

1

Koudalou

3 Rue Blanche

La requête directement réécrite en jointure donne le résultat suivant :

select i.* from Immeuble as i join Appart as a

on (i.id=a.id_immeuble)

where surface=50

id

nom

adresse

1

Koudalou

3 Rue Blanche

1

Koudalou

3 Rue Blanche

On obtient deux fois le même immeuble puisqu’il peut être associé à deux appartements différents de 50 m2. Il suffit d’ajouter un distinct après le select pour régler le problème, mais on peut considérer que dans ce cas la requête imbriquée est plus appropriée. Attention cependant : il n’est pas possible d’obtenir dans le résultat des attributs appartenant aux tables des requêtes imbriquées.

Le principe général des requêtes imbriquées est d’exprimer des conditions sur des tables calculées par des requêtes.

Ces conditions sont les suivantes :

— exists R : renvoie true si R n’est pas vide false sinon.

t in R où est une ligne dont le type (le nombre et le type des attributs) est celui de R : renvoie true si t appartient à R false sinon.

v cmp any R où cmp est un comparateur SQL (< > = etc.) : renvoie true si la comparaison avec au moins une des lignes de la table R renvoie true.

v cmp all R où cmp est un comparateur SQL (< > = etc.) : renvoie true si la comparaison avec toutes les lignes de la table R renvoie true.

De plus toutes ces expressions peuvent être préfixées par not pour obtenir la négation. La richesse des expressions possibles permet d’effectuer une même interrogation en choisissant parmi plusieurs syntaxes possibles. En général, tout ce qui n’est pas basé sur une négation not in ou not exists peut s’exprimer sans requête imbriquée.

select * from Appart where id_immeuble=1

and                                   etage >= all (select etage from Appart where id_immeuble=1)

Le all exprime une comparaison qui vaut pour toutes les lignes ramenées par la requête imbriquée. Attention aux valeurs à null dans ce genre de situation : toute comparaison avec une de ces valeurs renvoie UNKNOWN et cela peut entraîner l’échec du all. Il n’existe pas d’expression avec jointure qui puisse exprimer ce genre de condition.

5.3.6 Requêtes correlées

Les exemples de requêtes imbriquées donnés précédemment pouvaient être évalués indépendamment de la requête principale, ce qui permet au système (s’il le juge nécessaire) d’exécuter la requête en deux phases. La clause exists fournit encore un nouveau moyen d’exprimer les requêtes vues précédemment en basant la sous-requête sur une ou plusieurs valeurs issues de la requête principale. On parle alors de requêtes correlées.

Voici encore une fois la recherche de l’appartement de M. William Dupont exprimée avec exists :

select * from Appart where exists (select*from Personne where prenom='William' and nom='Dupont' and  )

On obtient donc une nouvelle technique d’expression qui permet d’aborder le critère de recherche sous une troisième perspective : on conserve un appartement si, pour cet appartement, l’occupant s’appelle William Dupont. Il s’agit assez visiblement d’une jointure mais entre deux tables situées dans des requêtes (ou plutôt des “blocs”) distinctes. La condition de jointure est appelée corrélation d’où le nom de ce type de technique.

Toutes les jointures peuvent d’exprimer avec exists ou in. Voici quelques exemples reprenant des requêtes déjà vues précédemment.

— Qui habite un appartement de plus de 200 m2?

Avec in :

select prenom nom profession from Personne

Avec exists :

select prenom nom profession from Personne p

where exists (select * from Appart a

where a.id=p.id_appart and surface >= 200)

— Qui habite le Barabas? Avec in :

select prenom p.nom no surface etage from Personne as p Appart as a whereand a.id_immeuble in

(select id from Immeuble where nom='Barabas')

Avec exists :

select prenom p.nom no surface etage from Personne as p Appart as a whereand exists (select*from Immeuble i where i.id=a.id_immeuble and i.nom='Barabas')

Important : dans une sous-requête associée à la clause exists peu importe les attributs du select puisque la condition se résume à : cette requête ramène-t-elle au moins une ligne ou non? On peut donc systématiquement utiliser select *.

Enfin rien n’empêche d’utiliser plusieurs niveaux d’imbrication au prix d’une forte dégradation de la lisibilité. Voici la requête “De quel(s) appartement(s) Alice Black est-elle propriétaire et dans quel immeuble?” écrite avec plusieurs niveaux.

select i.nom no etage surface from Immeuble as i Appart as a

where

a.id_immeuble= i.id

and

a.id in

(select id_appart from Possede where id_personne in

(select id from Personne where nom='Black' and prenom='Alice'))

En résumé une jointure entre les tables R et S de la forme :

select R.* from R S where R.a = S.b

peut s’écrire de manière équivalente avec une requête imbriquée :

select [distinct] *from R

where R.a in (select S.b from S)

ou bien encore sous forme de requête corrélée :

select [distinct] *from R

where exists (select S.b from S where S.b = R.a)

Le choix de la forme est matière de goût ou de lisibilité, ces deux critères relevant de considérations essentiellement subjectives.

select * from Appart

where id not in (select id_appart from Personne)

id

surface

etage

id_immeuble

no

101

50

15

1

34

La négation est aussi un moyen d’exprimer des requêtes courantes comme celle recherchant l’appartement le plus élevé de son immeuble. En SQL, on utilisera typiquement une sous-requête pour prendre l’étage maximal d’un immeuble, et on utilisera cet étage pour sélectionner un ou plusieurs appartements, le tout avec une requête correlée pour ne comparer que des appartements situés dans le même immeuble.

select * from Appart as a1

where etage = (select max(etage) from Appart as a2 where a1.id_immeuble=a2.id_immeuble)

id

surface

etage

id_immeuble

no

101

50

15

1

34

202

250

2

2

2

Il existe en fait beaucoup de manières d’exprimer la même chose. Tout d’abord cette requête peut en fait s’exprimer sans la fonction max() avec la négation : si a est l’appartement le plus élevé, c’est qu’il n’existe pas d’étage plus elevé que a. On utilise alors habituellement une requête dite “corrélée” dans laquelle la sous-requête est basée sur une ou plusieurs valeurs issues des tables de la requête principale.

select * from Appart as a1

where not exists (select * from Appart as a2

where a2.etage > a1.etage and a1.id_immeuble = a2.id_immeuble)

Autre manière d’exprimer la même chose : si l’étage est le plus élevé, tous les autres sont situés à un étage inférieur. On peut utiliser le mot-clé all qui indique que la comparaison est vraie avec tous les éléments de l’ensemble constitué par la sous-requête.

select * from Appart as a1 where etage >= all (select etage from Appart as a2

where a1.id_immeuble=a2.id_immeuble)

select * from Personne

where id not in (select id_personne from Possede)

select * from Personne as p1

where not exists (select * from Possede as p2 where =p2.id_personne)

5.4 Agrégats

Les requêtes agrégat en SQL consistent à effectuer des regroupements de lignes en fonction des valeurs d’une ou plusieurs expressions. Ce regroupement est spécifié par la clause group by. On obtient une structure qui n’est pas une table relationnelle puisqu’il s’agit d’un ensemble de groupes de lignes. On doit ensuite ramener cette structure à une table en appliquant des fonctions de groupes qui déterminent des valeurs agrégées calculées pour chaque groupe.

Enfin il est possible d’exprimer des conditions sur les valeurs agrégées pour ne conserver qu’un ou plusieurs des groupes constitués. Ces conditions portent sur des groupes de lignes et ne peuvent donc être obtenues avec where. On utilise alors la clause having.

Les agrégats s’effectuent toujours sur le résultat d’une requête classique select -from. On peut donc les voir comme une extension de SQL consistant à partitionner un résultat en groupes selon certains critères, puis à exprimer des conditions sur ces groupes, et enfin à appliquer des fonctions d’agrégation.

Il existe un groupe par défaut : c’est la table toute entière. Sans même utiliser group by, on peut appliquer les fonctions d’agrégation au contenu entier de la table comme le montre l’exemple suivant.

select count(*), count(prenom), count(nom) from Personne

Ce qui donne :

count(*)

count(prenom)

count(nom)

5

4

5

5.4.1 La clause group by

Le rôle du group by est de partitionner une table (calculée ou basée) en fonction d’un critère (un attribut ou plus généralement une expression sur des attributs). Pour bien analyser ce qui se passe pendant une requête avec group by on peut décomposer l’exécution d’une requête en deux étapes. Prenons l’exemple de celle permettant de vérifier que la somme des quote-part des propriétaires est bien égale à 100 pour tous les appartements.

select id_appart sum(quote_part) from Possede

group by id_appart

id_appart

sum(quote_part)

100

100

101

100

102

100

103

100

201

100

202

100

Dans une première étape le système va constituer les groupes. On peut les représenter avec un tableau comprenant, pour chaque ligne, d’une part la (ou les) valeur(s) du (ou des) attribut(s) de partitionnement (ici id_appart), d’autre part l’ensemble de lignes dans lesquelles on trouve cette valeur. Ces lignes “imbriquées” sont séparées par des pointsvirgule dans la représentation ci-dessous.

id_appart

Groupe

count

100

(id_personne=1 quote_part=33; id_personne=5 quote_part=67)

2

101

(id_personne=1 quote_part=100)

1

102

(id_personne=5 quote_part=100)

1

202

(id_personne=1 quote_part=100)

1

201

(id_personne=5 quote_part=100)

1

103

(id_personne=2 quote_part=100)

1

Le groupe associé à l’appartement 100 est constitué de deux copropriétaires. Le tableau ci-dessus n’est donc pas une table relationnelle dans laquelle chaque cellule ne peut contenir qu’une seule valeur.

— count(expression), Compte le nombre de lignes pour lesquelles expression est not null.

— avg(expression), Calcule la moyenne de expression.

— min(expression), Calcule la valeur minimale de expression.

— max(expression), Calcule la valeur maximale de expression.

— sum(expression), Calcule la somme de expression.

— std(expression), Calcule l’écart-type de expression.

Dans la norme SQL l’utilisation de fonctions d’agrégation pour les attributs qui n’apparaissent pas dans le group by est obligatoire. Une requête comme :

select id, surface, max(etage) from       Appart

group by surface

5.4. Agrégats

srea rejetée parce que le groupe associé à une même surface contient deux appartements différents (et donc deux valeurs différentes pour id), et qu’il n’y a pas de raison d’afficher l’un plutôt que l’autre.

5.4.2 La clause having

Finalement, on peut faire porter des conditions sur les groupes, ou plus précisément sur le résultat de fonctions d’agrégation appliquées à des groupes avec la clause having. La clause where ne peut exprimer des conditions que sur les lignes prises une à une. Par exemple, on peut sélectionner les appartements pour lesquels on connaît au moins deux copropriétaires.

select id_appart, count(*) from                Possede

group by id_appart having count(*) >= 2

On voit que la condition porte ici sur une propriété de l’ensemble des lignes du groupe et pas de chaque ligne prise individuellement. La clause having est donc toujours exprimée sur le résultat de fonctions d’agrégation.

Pour conclure, voici une requête sélectionnant la surface possédée par chaque copropriétaire pour l’immeuble 1. La surface possédée est la somme des surfaces d’appartements possédés par un propriétaire, pondérées par leur quote-part.

On regroupe par propriétaire et on trie sur la surface possédée.

from (Personne as p1 join Possede as p2 on (=p2.id_personne) ) join Appart as a on (a.id=p2.id_appart)

where id_immeuble = 1 group by

order by sum(quote_part * surface / 100)

On obtient le résultat suivant.

prénom

nom

Surface possédée

Alice

Black

50.00000

null

Ross

125.00000

Doug

Ramut

275.00000

5.5 Mises à jour

Les commandes de mise à jour (insertion, destruction, modification) sont considérablement plus simples que les interrogations.

5.5.1 Insertion

L’insertion s’effectue avec la commande insert, avec trois variantes. Dans la première on indique la liste des valeurs à insérer sans donner explicitement le nom des attributs. Le système suppose alors qu’il y a autant de valeurs que d’attributs, et que l’ordre des valeurs correspond à celui des attributs dans la table. On peut indiquer null pour les valeurs inconnues.

insert into Immeuble values (1 'Koudalou' '3 Rue Blanche')

Si on veut insérer dans une partie seulement des attributs, il faut donner la liste explicitement.

insert into Immeuble (id nom adresse) values (1 'Koudalou' '3 Rue Blanche')

Il est d’ailleurs préférable de toujours donner la liste des attributs. La description d’une table peut changer par ajout d’attribut, et l’ordre insert qui marchait un jour ne marchera plus le lendemain.

Enfin avec la troisième forme de insert il est possible d’insérer dans une table le résultat d’une requête. Dans ce cas la partie values est remplacée par la requête elle-même. Voici un exemple avec une nouvelle table Barabas dans laquelle on insère uniquement les informations sur l’immeuble “Barabas”.

create table Barabas (id int not null, nom varchar(100) not null, adresse varchar(200), primary key (id)

)

insert into Barabas

select*from Immeuble where nom='Barabas'

5.5.2 Destruction

La destruction s’effectue avec la clause delete dont la syntaxe est :

table étant bien entendu le nom de la table, et condition toute condition ou liste de conditions valide pour une clause where. En d’autres termes, si on effectue avant la destruction la requête

select * from table where condition

on obtient l’ensemble des lignes qui seront détruites par delete. Procéder de cette manière est un des moyens de s’assurer que l’on va bien détruire ce que l’on souhaite.

5.5.3 Modification

La modification s’effectue avec la clause update. La syntaxe est proche de celle du delete :

update table set A1=v1, A2=v2, An=vn where condition

Comme précédemment table est la table, les Ai sont les attributs les vi les nouvelles valeurs, et condition est toute condition valide pour la clause where.

5.6 Les vues

Une requête SQL produit toujours une table. Cela suggère la possibilité d’ajouter au schéma des tables calculées, qui ne sont rien d’autre que le résultat de requêtes stockées. De telles tables sont nommées des vues dans la terminologie relationnelle. On peut interroger des vues comme des tables stockées et, dans certaines limites, faire des mises à jour des tables stockées au travers de vues.

5.6. Les vues

Une vue n’induit aucun stockage puisqu’elle n’existe pas physiquement. Elle permet d’obtenir une représentation différente des tables sur lesquelles elle est basée avec deux grands avantages :

— on peut faciliter l’interrogation de la base en fournissant sous forme de vues des requêtes prédéfinies;

— on peut masquer certaines informations en créant des vues et en forçant par des droits d’accès l’utilisateur à passer par ces vues pour accéder à la base.

Les vues constituent donc un moyen complémentaire de contribuer à la sécurité (par restriction d’accès) et à la facilité d’utilisation (en offrant une “schéma virtuel” simplifié).

Une vue est essentiellement une requête à laquelle on a donné un nom. La syntaxe de création d’une vue est très simple :

create view nomvue ([listeattributs])

as                           requete

[with check option]

Nous avons déjà vu au début de ce chapitre vl’exemple d’une vue sur la table Immeuble montrant uniquement le Koudalou. En voici une nouvelle version plus complète avec le nombre d’appartements.

create Koudalou as select nom, adresse, count(*) as nb_apparts from Immeuble as i join Appart as a on (i.id=a.id_immeuble) where i.id=1

group by i.id, nom, adresse

La destruction d’une vue a évidemment beaucoup moins de conséquences que pour une table puisqu’on supprime uniquement la définition de la vue pas son contenu.

On interroge la vue comme n’importe quelle table.

select * from Koudalou

nom

adresse

nb_apparts

Koudalou

3 Rue Blanche

4

La vue fait maintenant partie du schéma. On ne peut d’ailleurs évidemment pas créer une vue avec le même nom qu’une table (ou vue) existante. La définition d’une vue peut consister en un requête SQL aussi complexe que nécessaire, avec jointures, regroupements, tris.

Allons un peu plus loin en définissant sous forme de vues un accès aux informations de notre base Immeuble, mais restreint uniquement à tout ce qui concerne l’immeuble Koudalou. On va en profiter pour offrir dans ces vues un accès plus facile à l’information. La vue sur les appartements, par exemple, va contenir contrairement à la table Appart le nom et l’adresse de l’immeuble et le nom de son occupant.

create or replace view AppartKoudalou as select no, surface, etage, i.nom as immeuble, adresse,

p.prenom || p.nom as occupant

from (Immeuble as i join Appart as a on (i.id=a.id_immeuble)) join Personne as p on (a.id=p.id_appart)

where i.id=1

select * from AppartKoudalou

no

surface

etage

immeuble

adresse

occupant

52

50

5

Koudalou

3 Rue Blanche

Alice Black

1

150

14

Koudalou

3 Rue Blanche

Rachel Verte

51

200

2

Koudalou

3 Rue Blanche

William Dupont

Le nom des attributs de la vue est celui des expressions de la requête associée. On peut également donner ces noms après le create view à condition qu’il y ait correspondance univoque entre un nom et une expression du select. On peut ensuite donner des droits en lecture sur cette vue pour que cette information limitée soit disponible à tous.

grant select on Immeuble.Koudalou Immeuble.AppartKoudalou to adminKoudalou

Pour peu que cet utilisateur n’ait aucun droit de lecture sur les tables de la base Immeuble, on obtient un moyen simple de masquer et restructurer l’information.

5.6.2 Mise à jour d’une vue

L’idée de modifier une vue peut sembler étrange puisqu’une vue n’a pas de contenu. En fait il s’agit bien entendu de modifier la table qui sert de support à la vue. Il existe de sévères restrictions sur les droits d’insérer ou de mettre à jour des tables au travers des vues. Un exemple suffit pour comprendre le problème. Imaginons que l’on souhaite insérer une ligne dans la vue AppartKoudalou.

insert into AppartKoudalou (no, surface, etage, immeuble, adresse, occupant) values (1, 12, 4, 'Globe', '2 Avenue Leclerc', 'Palamède')

— la vue doit être basée sur une seule table; toute colonne non référencée dans la vue doit pouvoir être mise à null ou disposer d’une valeur par défaut;

— on ne peut pas mettre à jour un attribut qui résulte d’un calcul ou d’une opération.

On ne peut donc pas insérer ou modifier la vue Koudalou à cause de la jointure et de l’attribut calculé. La requête suivante serait rejetée.

insert into Koudalou (nom, adresse) values ('Globe', '2 Avenue Leclerc')

En revanche une vue portant sur une seule table avec un select * est modifiable.

create view PossedeAlice as select * from Possede

where id_personne=2

insert into PossedeAlice values (2 100 20) insert into PossedeAlice values (3 100 20)

5.6. Les vues

Maintenant, si on fait :

select * from PossedeAlice

On obtient :

id_personne

id_appart

quote_part

2

100

20

2

103

100

L’insertion précédente illustre une petite subtilité : on peut insérer dans une vue sans être en mesure de voir la ligne insérée au travers de la vue par la suite! On a en effet inséré dans la vue le propriétaire 3 qui est ensuite filtré quand on interroge la vue.

SQL propose l’option with check option qui permet de garantir que toute ligne insérée dans la vue satisfait les critères de sélection de la vue.

create view PossedeAlice as select*from Possede where id_personne=2 with check option

SQL permet également la modification de vues définies par des jointures. Les restrictions sont essentielement les même que pour les vues mono-tabulaires : on ne peut insérer que dans une des tables (il faut donc préciser la liste des attributs) et tous les attributs not null doivent avoir une valeur. Voici un exemple de vue modifiable basée sur une jointure.

create or replace view ToutKoudalou as select i.id as id_imm, nom, adresse, a.* from Immeuble as i join Appart as a on (i.id=a.id_immeuble) where i.id=1 with check option

insert into ToutKoudalou (id, surface, etage, id_immeuble, no) values (104, 70, 12, 1, 65)

En conclusion, l’intérêt principal des vues est de permettre une restructuration du schéma en vue d’interroger et/ou de protéger des données. L’utilisation de vues pour des mises à jour devrait rester marginale.


CHAPITRE6

Le langage PL/SQL

Le langage SQL n’est pas un langage de programmation au sens courant du terme. Il ne permet pas, par exemple, de définir des fonctions ou des variables, d’effectuer des itérations ou des instructions conditionnelles. Il ne s’agit pas d’un défaut dans la conception du langage, mais d’une orientation délibérée de SQL vers les opérations de recherche de données dans une base volumineuse, la priorité étant donnée à la simplicité et à l’efficacité*. Ces deux termes ont une connotation forte dans le contexte d’un langage d’interrogation, et correspondent à des critères (et à des contraintes) précisément définis. La simplicité d’un langage est essentiellement relative à son caractère déclaratif, autrement dit à la capacité d’exprimer des recherches en laissant au système le soin de déterminer le meilleur moyen de les exécuter. L’efficacité est, elle, définie par des caractéristiques liées à la complexité d’évaluation sur lesquelles nous ne nous étendrons pas ici. Signalons cependant que la terminaison d’une requête SQL est toujours garantie, ce qui n’est pas le cas d’un programme écrit dans un langage plus puissant, .

Pour certaines fonctionnalités, le recours à un langage de programmation “externe” s’avère cependant inadapté ou insatisfaisant. Une évolution des SGBD consiste donc à proposer, au sein même du système, des primitives de programmation qui viennent pallier le manque relatif d’expressivité des langages relationnnels. Le présent chapitre décrit ces évolutions et leur application à la création de procédures stockées et de triggers. Les premières permettent d’enrichir un schéma de base de données par des calculs ou des fonctions qui ne peuvent pas - parfois même dans des cas très simples - être obtenus avec SQL; les seconds étendent la possibilité de définir des contraintes.

Parallèlement à ces applications pratiques, les procédures stockées illustrent simplement les techniques d’intégration de SQL à un langage de programmation classique, et soulignent les limites d’utilisation d’un langage d’interrogation, et plus particulièrement du modèle relationnel.

6.1 S1. Procédures stockées

Supports complémentaires :

— Diapositives: PL/SQL et triggers

Comme mentionné ci-dessus, les procédures stockées constituent une alternative à l’écriture de programmes avec une langage de programmation généraliste. Commençons par étudier plus en détail les avantages et inconvénients respectifs des deux solutions avant d’entrer dans les détails techniques.

Fig. 6.1 – Comparaison programmes externes/procédures stockées

Le recours à une procédure stockée permet de regrouper du côté serveur l’ensemble des requêtes SQL et le traitement des données récupérées. La procédure est compilée une fois par le SGBD, au moment de sa création, ce qui permet de l’exécuter rapidement au moment de l’appel. De plus les échanges réseaux ne sont plus nécessaires puisque la logique de l’application est étroitement intégrée aux requêtes SQL. Le rôle du programme externe se limite alors à se connecter au serveur et à demander l’exécution de la procédure, en lui passant au besoin les paramètres nécessaires.

Bien entendu, en pratique, les situations ne sont pas aussi tranchées et le programme externe est en général amené à appeler plusieurs procédures, jouant en quelque sorte le rôle de coordinateur. Si les performances du système sont en cause, un recours judicieux aux procédures stockées reste cependant un bon moyen de réduire le trafic client-serveur.

L’utilisation de procédures stockées est par ailleurs justifiée, même en l’absence de problèmes de performance, pour des fonctions très “sensibles”, terme qui recouvre (non exclusivement) les cas suivants :

1.    la fonction est basée sur des règles complexes qui doivent être implantées très soigneusement;

2.    la fonction met à jour des données dont la correction et la cohérence sont indispensable au bon fonctionnement de l’application;

3.    la fonction évolue souvent.

C’est facile avec une procédure stockée, et cela permet d’une part d’implanter une seule fois des fonctions “sensibles”, d’autre part de garantir la correction, la cohérence et l’évolutivité en imposant l’utilisation systématique de ces fonctions au lieu d’un accès direct aux données.

Enfin, le dernier avantage des procédures stockées est la relative facilité de programmation des opérations de bases de données, en grande partie à cause de la très bonne intégration avec SQL. Cet aspect est favorable à la qualité et à la rapidité du développement, et aide également à la diffusion et à l’installation du logiciel puisque les procédures sont compilées par les SGBD et fonctionnent donc de manière identique sur toute les plateformes.

Il existe malheureusement une contrepartie à tous ces avantages : chaque éditeur de SGBD propose sa propre extension procédurale pour créer des procédures stockées, ce qui rend ces procédures incompatibles d’un système à un autre. Cela peut être dissuasif si on souhaite produire un logiciel qui fonctionne avec tous les SGBD relationnels.

La description qui suit se base sur le langage PL/SQL d’Oracle (“PL” signifie Procedural Language) qui est sans doute le plus riche du genre. Le même langage, simplifié, avec quelques variantes syntaxiques mineures, est proposé par PostgreSQL, et les exemples que nous donnons peuvent donc y être transposés sans trop de problème. Les syntaxes des langages utilisés par d’autres systèmes sont un peu différentes, mais tous partagent cependant un ensemble de concepts et une proximité avec SQL qui font de PL/SQL un exemple tout à fait représentatif de l’intérêt et de l’utilisation des procédures stockées.

-- Exemple de bloc PL/SQL donnant des informations sur la base

DECLARE

-- Quelques variables v_nbFilms           INTEGER; v_nbArtistes INTEGER;

BEGIN

-- Compte le nombre de films

SELECT COUNT(*) INTO v_nbFilms FROM Film;

-- Compte le nombre d'artistes

SELECT COUNT(*) INTO v_nbArtistes FROM Artiste;

-- Affichage des résultats

DBMS_OUTPUT.PUT_LINE ('Nombre de films: ' || v_nbFilms);

DBMS_OUTPUT.PUT_LINE ('Nombre d''artistes: ' || v_nbArtistes);

EXCEPTION

WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE ('Problème rencontré dans StatsFilms');

END;

/

Le code est structuré en trois parties qui forment un “bloc” : déclarations des variables, instructions (entre BEGIN et END) et gestion des exceptions. La première remarque importante est que les variables sont typées, et que les types sont exactement ceux de SQL (ou plus largement les types supportés par le SGBD, qui peuvent différer légèrement de la norme). Un autre aspect de l’intégration forte avec SQL est la possibilité d’effectuer des requêtes, d’utiliser dans cette requête des critères basés sur la valeur des variables de la procédure, et de placer le résultat dans une (ou plusieurs) variables grâce à la clause INTO. En d’autres termes on transfère directement des données représentées selon le modèle relationnel et accessibles avec SQL, dans des unités d’information manipulables avec les structures classiques (test ou boucles) d’un langage impératif.

Les fonctions SQL fournies par le SGBD sont également utilisables, ainsi que des librairies spécifiques à la programmation procédurale (des packages chez ORACLE). Dans l’exemple ci-dessus on utilise le package DBMS_OUTPUT qui permet de produire des messages sur la sortie standard (l’écran en général).

Note : Un programme Pl/SQL peut être placé dans un fichier et executé avec la commande start sous l’utilitaire de commandes. par exemple :

SQL>start StatsFilms

En cas d’erreur de compilation, la commande SHOW ERRORS donne la liste des problèmes rencontrés. Sinon le code est exécuté. Voici par exemple ce que l’on obtient avec le code donné précédemment en exemple (La commande set serveroutput on assure que les messages sont bien affichés à l’écran..

SQL> set serveroutput on

SQL> start StatsFilms

Nombre de films: 48

Nombre d'artistes: 126

Voici maintenant un exemple de procédure stockée. On retrouve la même structuration que précédemment (déclaractions, instructions, exception), mais cette fois ce “bloc” est nommé, stocké dans la base au moment de la compilation, et peut ensuite être appelé par son nom. La procédure implante la règle suivante : l’insertion d’un texte dans la table des genres s’effectue toujours en majuscules, et on vérifie au préalable que ce code n’existe pas déjà.

-- Insère un nouveau genre, en majuscules, et en vérifiant

-- qu'il n'existe pas déjà

CREATE OR REPLACE PROCEDURE InsereGenre (p_genre VARCHAR) AS

-- Déclaration des variables v_genre_majuscules VARCHAR(20); v_count INTEGER; genre_existe EXCEPTION;

BEGIN

-- On met le paramètre en majuscules v_genre_majuscules := UPPER(p_genre);

-- On vérifie que le genre n'existe pas déjàSELECT COUNT(*) INTO v_count

FROM Genre WHERE code = v_genre_majuscules;

-- Si on n'a rien trouvé: on insère

IF (v_count = 0) THEN

INSERT INTO Genre (code) VALUES (v_genre_majuscules);

ELSE

RAISE genre_existe; END IF;

EXCEPTION

WHEN genre_existe THEN

DBMS_OUTPUT.PUT_LINE('Le genre existe déjà en ' || v_count || ' exemplaire(s).');

END;

/

La requête SQL garantit que l’on obtient un et un seul n-uplet. Nous verrons plus loin comment traiter le cas où le résultat de la requête est une table contenant un nombre quelconque de n-uplets. Dans l’exemple ci-dessus on obtient toujours un attribut donnant le nombre de n-uplets existants dans la table pour le code que l’on veut insérer. Si ce nombre n’est pas nul, c’est que le genre existe déjà dans la table, et on produit une “exception” avec la clause RAISE EXCEPTION, sinon c’est que le genre n’existe pas et on peut effectuer la clause d’insertion, en indiquant comme valeurs à insérer celles contenues dans les variables appropriées.

On peut appeler cette procédure à partir de n’importe quelle application connectée au SGBD. Sous SQL*Plus on utilise l’instruction execute. Voici par exemple ce que l’on obtient avec deux appels successifs.

SQL> execute InsereGenre('Policier');

SQL> execute InsereGenre('Policier');

Le genre existe déjà en 1 exemplaire(s).

Le premier appel s’est correctement déroulé puisque le genre “Policier” n’existait pas encore dans la table. Le second en revanche a échoué, ce qui a déclenché l’exception et l’affichage du message d’erreur.

On peut appeler InsereGenre() depuis un programme C, Java, ou tout autre outil. Si on se fixe comme règle de toujours passer par cette procédure pour insérer dans la table Genre, on est donc sûr que les contraintes implantées dans la procédure seront toujours vérifiées.

Note : Pour forcer les développeurs à toujours passer par la procédure, on peut fixer les droits d’accès de telle sorte que les utilisateurs ORACLE aient le droit d’exécuter InsereGenre(), mais pas de droit de mise à jour sur la table Genre elle-même.

-- Fonction retournant la liste des acteurs pour un film donné

CREATE OR REPLACE FUNCTION MesActeurs(v_idFilm INTEGER) RETURN VARCHAR IS

-- Le résultat resultat VARCHAR(255);

BEGIN

-- Boucle prenant tous les acteurs du films

FOR art IN

(SELECT Artiste.* FROM Role, Artiste

WHERE idFilm = v_idFilm AND idActeur=idArtiste) LOOP

IF (resultat IS NOT NULL) THEN resultat := resultat || ', ' || art.prenom || ' ' || ;

ELSE resultat := art.prenom || ' ' || ; END IF;

END LOOP;

return resultat;

END;

/

La fonction effectue une requête SQL pour rechercher tous les acteurs du film dont l’identifiant est passé en paramètre. Contrairement à l’exemple précédent, cette requête renvoie en général plusieurs n-uplets. Une des caractéristiques principales des techniques d’accès à une base de données avec un langage procédural est que l’on ne récupère pas d’un seul coup le résultat d’un ordre SQL. Il existe au moins deux raisons à cela :

1.    le résultat de la requête peut être extrêmement volumineux, ce qui poserait des problèmes d’occupation mémoire si on devait tout charger dans l’espace du programme client;

2.    les langages de programmation ne sont en général pas équipés nativement des types nécessaires à la représentation d’un ensemble de n-uplets.

Le concept utilisé, plus ou moins implicitement, dans toutes les interfaces permettant aux langages procéduraux d’accéder aux bases de données est celui de curseur. Un curseur permet de parcourir, à l’aide d’une boucle, l’ensemble des n-uplets du résultat d’une requête, en traitant le n-uplet courant à chaque passage dans la boucle. Ici nous avons affaire à la version la plus simple qui soit d’un curseur, mais nous reviendrons plus loin sur ce mécanisme.

SQL> SELECT titre, MesActeurs(idFilm) FROM Film WHERE idFilm=5;

TITRE                          MESACTEURS(IDFILM)

------------ -----------------------------

Volte/Face                        John Travolta, Nicolas Cage

On peut noter que le résultat de la fonction MesActeurs() ne peut pas être obtenu avec une requête SQL. Il est d’ailleurs intéressant de se demander pourquoi, et d’en tirer quelques conclusions sur certaines limites de SQL. Il est important de mentionner également qu’ORACLE ne permet pas l’appel, dans un ordre SELECT de fonctions effectuant des mises à jour dans la base : une requête n’est pas censée entraîner des modifications, surtout si elles s’effectuent de manière transparente pour l’utilisateur.

6.1.3 Syntaxe de PL/SQL

Voici maintenant une présentation plus systématique du langage PL/SQL. Elle vise à expliquer et illustrer ses principes les plus intéressants et à donner les éléments nécessaires à une expérimentation sur machine mais ne couvre cependant pas toutes ses possibilités, très étendues.

Types et variables

PL/SQL reconnaît tous les types standard de SQL, plus quelques autres dont le type Boolean qui peut prendre les valeurs TRUE ou FALSE. Il propose également deux constructeurs permettant de créer des types composés :

1.    le constructeur RECORD est comparable au schéma d’une table; il décrit un ensemble d’attributs typés et nommés;

2.    le constructeur TABLE correspond aux classiques tableaux unidimensionnels.

Le constructeur RECORD est particulièrement intéressant pour représenter un n-uplet d’une table, et donc pour définir des variables servant à stocker le résultat d’une requête SQL. On peut définir soit-même un type avec RECORD, avec une syntaxe très similaire à celle du CREATE TABLE.

DECLARE

-- Déclaration d'un nouveau type

TYPE adresse IS RECORD

codePostal VARCHAR(10)

);

Mais PL/SQL offre également un mécanisme extrêmement utile consistant à dériver automatiquement un type RECORD en fonction d’une table ou d’un attribut d’une table. On utilise alors le nom de la table ou de l’attribut, associées respectivement au qualificateur %ROWTYPE ou à %TYPE pour désigner le type dérivé. Voici quelques exemples :

1.    Film.titre%TYPE est le titre de l’attribut titre de la table Film;

2.    Artiste%ROWTYPE est un type RECORD correspondant aux attributs de la table Artiste.

Le même principe de dérivation automatique d’un type s’applique également aux requêtes SQL définies dans le cadre des curseurs. Nous y reviendrons au moment de la présentation de ces derniers.

La déclaration d’une variable consiste à donner son nom, son type, à indiquer si elle peut être NULL et a donner éventuellement une valeur initiale. Elle est de la forme :

<nomVariable> <typeVariable> [NOT NULL] [:= <valeurDéfaut>]

Il est possible de définir également des constantes, avec la syntaxe :

<nomConstante> CONSTANT <typeConstante> := <valeur>

Toutes les déclarations de variables ou de constantes doivent être comprises dans la section DECLARE. Toute variable non initialisée est à NULL. Voici quelques exemples de déclarations. Tous les noms de variables sont systématiquement préfixés par v_. Ce n’est pas une obligation mais ce type de convention permet de distinguer plus facilement les variables de PL/SQL des attributs des tables dans les ordres SQL.

DECLARE

-- Constantes

v_aujourdhui CONSTANT DATE := SYSDATE; v_pi CONSTANT NUMBER(7,5) := 3.14116;

-- Variables scalaires v_compteur      INTEGER NOT NULL := 1; v_nom               VARCHAR(30);

-- Variables pour un n-uplet de la table Film et pour le résumé v_film Film%ROWTYPE; v_resume Film.resume%TYPE;

<nomVariable> := <expression>;

où expression est toute expression valide retournant une valeur de même type que celle de la variable. Rappelons que tous les opérateurs SQL (arithmétiques, concaténation de chaînes, manipulation de dates) et toutes les fonctions du SGBD sont utilisables en PL/SQL. Un autre manière d’affecter une variable est d’y transférer tout ou partie d’un n-uplet provenant d’une requête SQL avec la syntaxe :

SELECT <nomAttribut1>, [<nomAttribut2>, ]

INTO        <nomVariable1>, [<nomVariable2>, ] FROM     [ ]

La variable doit être du même type que l’attribut correspondant de la clause SELECT, ce qui incite fortement à utiliser le type dérivé avec %TYPE. Dès que l’on veut transférer plusieurs valeurs d’attributs dans des variables, on a sans doute intérêt à utiliser un type dérivé %ROWTYPE qui limite le nombre de déclarations à effectuer. L’exemple suivant illustre l’utilisation de la clause SELECT INTO associée à des types dérivés. La fonction renvoie le titre du film concaténé avec le nom du réalisateur.

-- Retourne une chaîne avec le titre du film et sont réalisateur

CREATE OR REPLACE FUNCTION TitreEtMES(v_idFilm INTEGER) RETURN VARCHAR IS

-- Déclaration des variables v_titre Film.titre%TYPE; v_idMES Film.idMES%TYPE; v_mes Artiste%ROWTYPE;

BEGIN

-- Recherche du film

SELECT titre, idMES

INTO v_titre, v_idMES

FROM Film

WHERE idFilm=v_idFilm;

-- Recherche du metteur en scène

SELECT * INTO v_mes FROM Artiste WHERE idArtiste = v_idMES;

return v_titre || ', réalisé par ' || v_mes.prenom

|| ' ' || ;

END;

/

L’association dans la requête SQL de noms d’attributs et de noms de variables peut parfois s’avérer ambiguë d’où l’utilité d’une convention permettant de distinguer clairement ces derniers.

IF <condition> THEN <instructions1>;

ELSE

<instruction2>;

END IF;

Les conditions sont exprimées comme dans une clause WHERE de SQL, avec notamment la possibilité de tester si une valeur est à NULL, des opérateurs comme LIKE et les connecteurs usuels AND, OR et NOT. Le ELSE est optionnel, et peut éventuellement être associé à un autre IF, selon la syntaxe généralisée suivante :

IF <condition 1> THEN

<instructions 1>;

ELSIF <condition 2> THEN

<instruction 2>;

[ ]

ELSIF <condition n> THEN

<instruction n>;

ELSE

<instruction n+1>;

END IF;

Il existe trois formes de boucles : LOOP, FOR et WHILE. Seules les deux dernières sont présentées ici car elles suffisent à tous les besoins et sont semblables aux structures habituelles.

La boucle WHILE répète un ensemble d’instructions tant qu’une condition est vérifiée. La condition est testée à chaque entrée dans la boucle. Voici la syntaxe :

WHILE <condition> LOOP

<instructions>;

END LOOP;

Rappelons que les expressions booléennes en SQL peuvent prendre trois valeurs : TRUE, FALSE et UNKNOWN quand l’évaluation de l’expression rencontre une valeur à NULL. Une condition est donc vérifiée quand elle prend la valeur TRUE, et une boucle WHILE s’arrête en cas de FALSE ou UNKNOWN.

La boucle FOR permet de répéter un ensemble d’instructions pour chaque valeur d’un intervalle de nombres entiers. La syntaxe est donnée ci-dessous. Notez les deux points entre les deux bornes de l’intervalle, et la possibilité de parcourir cet intervalle de haut en bas avec l’option REVERSE.

FOR <variableCompteur> IN [REVERSE] <min>..<max> LOOP

<instructions>; END LOOP;

Des itérations couramment utilisées en PL/SQL consistent à parcourir le résultat d’une requête SQL avec un curseur.

[DECLARE]

-- Déclaration des variables, constantes, curseurs et exceptions

BEGIN

-- Instructions, requêtes SQL, structures de contrôle

EXCEPTION

-- Traitement des erreurs

END;

Le bloc est l’unité de traitement de PL/SQL. Un bloc peut être anonyme. Il commence alors par l’instruction DECLARE, et ORACLE le compile et l’exécute dans la foulée au moment où il est rencontré. Le premier exemple que nous avons donné est un bloc anonyme.

Un bloc peut également être nommé (cas des procédures et fonctions) et stocké. Dans ce cas le DECLARE est remplacé par l’instruction CREATE. Le SGBD stocke la procédure ou la fonction et l’exécute quand on l’appelle dans le cadre d’un langage de programmation. La syntaxe de création d’une procédure stockée est donnée ci-dessous.

CREATE [OR REPLACE] PROCEDURE <nomProcédure>

[(<paramètre 1>, <paramètre n>)] AS

[<déclarations<]

BEGIN

<instructions>;

[EXCEPTION

<gestionExceptions>;

]

END;

La syntaxe des fonctions est identique, à l’exception d’un RETURN <type> précédant le AS et indiquant le type de la valeur renvoyée. Procédures et fonctions prennent en entrée des paramètres selon la syntaxe suivante :

<nomParamètre> [IN | OUT | IN OUT] <type> [:= <valeurDéfaut>]

La déclaration des paramètres ressemble à celle des variables. Tous les types PL/SQL sont acceptés pour les paramètres, notamment les types dérivés avec %TYPE et %ROWTYPE, et on peut définir des valeurs par défaut. Cependant la longueur des chaînes de caractères (CHAR ou VARCHAR) ne doit pas être précisée pour les paramètres. La principale différence avec la déclaration des variables est le mode d’utilisation des paramètres qui peut être IN, OUT ou IN OUT. Le mode détermine la manière dont les paramètres servent à communiquer avec le programme appelant :

2.    OUT indique que la valeur du paramètre peut être modifée mais ne peut pas être lue;

3.    IN OUT indique que la valeur du paramètre peut être lue et modifiée.

En d’autres termes les paramètres IN permettent au programme appelant de passer des valeurs à la procédure, les paramètres OUT permettent à la procédure de renvoyer des valeurs au programme appelant, et les paramètres IN OUT peuvent jouer les deux rôles. L’utilisation des paramètres OUT permet à une fonction ou une procédure de renvoyer plusieurs valeurs.

Gestion des erreurs

Les exceptions en PL/SQL peuvent être soit des erreurs renvoyées par le SGBD lui-même en cas de manipulation incorrecte des données, soit des erreurs définies par le programmeur lui-même. Le principe est que toute erreur rencontrée à l’exécution entraîne la levée} (RAISE) d’une exception, ce qui amène le flux de l’exécution à se dérouter vers la section EXCEPTION du bloc courant. Cette section rassemble les actions (exception handlers) à effectuer pour chaque type d’exception rencontrée. Voici quelques-unes des exceptions les plus communes levées par le SGBD.

1.    INVALID_NUMBER, indique une conversion impossible d’une chaîne de caractères vers un numérique;

2.    INVALID_CURSOR, indique une tentative d’utiliser un nom de curseur inconnu;

3.    NO_DATA_FOUND, indique une requête SQL qui ne ramène aucun n-uplet;

4.    TOO_MANY_ROWS, indique une requête SELECT INTO qui n’est pas traitée par un curseur alors qu’elle ramène plusieurs n-uplets.

Les exceptions utilisateurs doivent être définies dans la section de déclaration avec la syntaxe suivante.

<nomException> EXCEPTION;

On peut ensuite lever, au cours de l’exécution d’un bloc PL/SQL, les exceptions, systèmes ou utilisateurs, avec l’instruction RAISE.

RAISE <nomException>;

WHEN <nomException> THEN

<traitementException>;

Si le nom de l’exception levée correspond à l’une des conditions de la liste, alors le traitement correspondant est exécuté. Sinon c’est la section OTHERS qui est utilisée. S’il n’y a pas de section gérant les exceptions, l’exception est passée au programme appelant. La procédure suivante montre quelques exemples d’exceptions.

-- Illustration des exceptions. La procédure prend un -- identifiant de film, et met le titre en majuscules.

-- Les exceptions suivantes sont levées:

--                            Exception système: NO_DATA_FOUND si le film n'existe pas

--                             Exception utilisateur: DEJA_FAIT si le titre

--                                       est déjà en majuscule

CREATE OR REPLACE PROCEDURE TitreEnMajuscules (p_idFilm INT) AS

-- Déclaration des variables v_titre Film.titre%TYPE; deja_fait EXCEPTION;

BEGIN

-- Recherche du film. Une exception est levée si on ne trouve rien

SELECT titre INTO v_titre

FROM Film WHERE idFilm = p_idFilm;

-- Si le titre est déjà en majuscule, on lève une autre

-- exception

IF (v_titre = UPPER(v_titre)) THEN

RAISE deja_fait; END IF;

-- Mise à jour du titre

UPDATE Film SET titre=UPPER(v_titre) WHERE idFilm=p_idFilm;

EXCEPTION

WHEN NO_DATA_FOUND THEN

DBMS_OUTPUT.PUT_LINE('Ce film n''existe pas');

WHEN deja_fait THEN

DBMS_OUTPUT.PUT_LINE('Le titre est déjà en majuscules');

WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE('Autre erreur ');

END;

/

Voici quelques exécutions de cette procédure qui montrent comment les exceptions sont levées selon le cas. On peut noter qu’ORACLE considère comme une erreur le fait un ordre SELECT ne ramène aucun n-uplet, et lève alors l’exception NO_DATA_FOUND.

SQL> execute TitreEnMajuscules(900);

Le film n'existe pas

SQL> execute TitreEnMajuscules(5);

Le titre est déjà en majuscules

6.2 Les curseurs

Comme nous l’avons indiqué précédemment, les curseurs constituent un mécanisme de base dans les programmes accèdant aux bases de données. Ce mécanisme repose sur l’idée de traiter un n-uplet à la fois dans le résultat d’une requête, ce qui permet notamment d’éviter le chargement, dans l’espace mémoire du client, d’un ensemble qui peut être très volumineux.

Le traitement d’une requête par un curseur a un impact sur le style de programmation et l’intégration avec un langage procédural, sur les techniques d’évaluation de requêtes, et sur la gestion de la concurrence d’accès. Ces derniers aspects sont traités dans d’autres chapitres. La présentation qui suit est générale pour ce qui concerne les concepts, et s’appuie sur PL/SQL pour les exemples concrets. L’avantage de PL/SQL est de proposer une syntaxe très claire et un ensemble d’options qui mettent bien en valeur les points importants.

6.2.1 Déclaration d’un curseur

Un curseur doit être déclaré dans la section DECLARE du bloc PL/SQL. En général on déclare également une variable dont le type est dérivé de la définition du curseur. Voici un exemple de ces deux déclarations associées :

-- Déclaration d'un curseur

CURSOR MonCurseur IS SELECT * FROM Film, Artiste

WHERE idMES = idArtiste;

-- Déclaration de la variable v_monCurseur MonCurseur%ROWTYPE;

En général on utilise des curseurs paramétrés qui, comme leur nom l’indique, intègrent dans la requête SQL une ou plusieurs variables dont les valeurs, au moment de l’exécution, déterminent le résultat et donc l’ensemble de n-uplets à parcourir. Enfin on peut, optionnellement, déclarer l’intention de modifier les n-uplets traités par le curseur avec un UPDATE, ce qui entraîne au moment de l’exécution quelques conséquences importantes sur lesquelles nous allons revenir. La syntaxe générale d’un curseur est donc la suivante :

CURSOR <nomCurseur> [(<listeParamètres>)]

IS <requête>

[FOR UPDATE]


Les paramètres sont indiqués comme pour une procédure, mais le mode doit toujours être IN (cela n’a pas de sens de modifier le paramètre d’un curseur). Le curseur suivant effectue la même jointure que précédemment, mais les films sont sélectionnés sur l’année de parution grâce à un paramètre.

-- Déclaration d'un curseur paramétré

CURSOR MonCurseur (p_annee INTEGER) IS

SELECT * FROM Film, Artiste

WHERE idMES = idArtiste

AND            annee = p_annee;

Une déclaration complémentaire est celle des variables qui vont permettre de recevoir les n-uplets au fur et à mesure de leur parcours. Le type dérivé d’un curseur est obtenu avec la syntaxe <nomCurseur>\%ROWTYPE. Il s’agit d’un type RECORD avec un champ par correspondant à expression de la clause SELECT. Le type de chaque champ est aisément déterminé par le système. Déterminer le nom du champ est un peu plus délicat car la clause SELECT peut contenir des attributs (c’est le plus courant) mais aussi des expressions construites sur ces attributs comme, par exemple, AVG(annee). Il est indispensable dans ce dernier cas de donner un alias à l’expression, qui deviendra le nom du champ dans le type dérivé. Voici un exemple de cette situation :

-- Déclaration du curseur

CURSOR MonCurseur IS

FROM Film, Artiste

WHERE idMES = idArtiste

-- Déclaration d'une variable associée v_realisateur MonCurseur%ROWTYPE;

Le type dérivé a trois champs, nommés respectivement nomRealisateur, anneeNaiss et nbFilms.

6.2.2 Exécution d’un curseur

Un curseur est toujours exécuté en trois phases :

1.    ouverture du curseur (ordre OPEN);

2.    parcours du résultat en itérant des ordres FETCH autant de fois que nécessaire;

3.    fermeture du curseur (CLOSE).

Il faut bien être conscient de la signification de ces trois phases. Au moment du OPEN, le SGBD va analyser la requête, construire un plan d’exécution (un programme d’accès aux fichiers) pour calculer le résultat, et initialiser ce programme de manière à être en mesure de produire un n-uplet dès qu’un FETCH est reçu. Ensuite, à chaque FETCH, le n-uplet courant est envoyé par le SGBD au curseur, et le plan d’exécution se prépare à produire le n-uplet suivant.

En d’autres termes le résultat est déterminé au moment du OPEN, puis exploité au fur est à mesure de l’appel des FETCH. Quelle que soit la période sur laquelle se déroule cette exploitation (10 secondes, 1 heure ou une journée entière), le SGBD doit assurer que les données lues par le curseur refléteront l’état de la base au moment de l’ouverture du curseur. Cela signifie notamment que les modifications effectuées par d’autres utilisateurs, ou par le programme client (c’est-à-dire celui qui exécute le curseur) lui-même, ne doivent pas être visibles au moment du parcours du résultat.

-- Un curseur qui s'exécute indéfiniment

OPEN du curseur sur la table T;

WHILE (FETCH du curseur ramène un n-upletdans T) LOOP

Insérer un nouveau n-uplet dans T;

6.2. Les curseurs

END LOOP;

CLOSE du curseur;

Chaque passage dans le WHERE entraîne l’insertion d’un nouveau n-uplet, et on se sortirait donc jamais de la boucle si le curseur prenait en compte ce dernier.

D’autres situations, moins caricaturales, et résultant d’actions effectuées par d’autres utilisateurs, poseraient des problèmes également. Le SGBD assure que le résultat est figé au moment du OPEN puisque c’est à ce moment-là que la requête est constituée et – au moins conceptuellement – exécutée.

Une solution triviale pour satisfaire cette contrainte est le calcul complet du résultat au moment du OPEN, et son stockage dans une table temporaire. Cette technique présente cependant de nombreux inconvénients :

1.    il faut stocker le résultat quelque part, ce qui est pénalisant s’il est volumineux;

2.    le programme client doit attendre que l’intégralité du résultat soit calculé avant d’obtenir le premièr n-uplet;

3.    si le programme client souhaite effectuer des mises à jour, il faut réserver des n-uplets qui ne seront peut-être traités que dans plusieurs minutes voire plusieurs heures.

Dire que le résultat est figé ou déterminé à l’avance ne signifie par forcément qu’il est calculé et matérialisé quelque part. Les chapitres consacrés à l’évaluation de requêtes et à la concurrence d’accès dans décrivent en détail les techniques plus sophistiquées pour gérer les curseurs. Ce qu’il faut retenir ici (et partout où nous parlerons de curseur), c’est que le résultat d’une requête n’est pas forcément pré-calculé dans son intégralité mais peut être construit, utilisé puis détruit au fur et à mesure de l’itération sur les ordres FETCH.

Il existe dans la norme une option SCROLL indiquant que l’on peut choisir d’aller en avançant ou en reculant sur l’ensemble des n-uplets. Cette option n’est disponible dans aucun système, du moins à l’heure où ces lignes sont écrites. Le SCROLL est problématique puisqu’il impose de conserver au SGBD le résultat complet pendant toute la durée de vie du curseur, l’utilisateur pouvant choisir de se déplacer d’avant en arrière sur l’ensemble des n-uplets. Le SCROLL est difficilement compatible avec la technique d’exécution employés dans tous les SGBD, et qui ne permet qu’un seul parcours séquentiel sur l’ensemble du résultat.

6.2.3 Les curseurs PL/SQL

La gestion des curseurs dans PL/SQL s’appuie sur une syntaxe très simple et permet, dans un grand nombre de cas, de limiter au maximum les déclarations et instructions nécessaires. La manière la plus générale de traiter un curseur, une fois sa déclaration effectuée, et de s’appuyer sur les trois instructions OPEN, FETCH et CLOSE dont la syntaxe est donnée ci-dessous.

OPEN <nomCurseur>[(<valeursParamètres>)];

FETCH <nomCurseur> INTO <variableRéceptrice>;

CLOSE <nomCurseur>;

La (ou les) variable(s) qui suivent le INTO doivent correspondre au type d’un n-uplet du résultat. En général on utilise une variable déclarée avec le type dérivé du curseur, <nomCurseur>%ROWTYPE.

Une remarque importante est que les curseurs ont l’inconvénient d’une part de consommer de la mémoire du côté serveur, et d’autre part de bloquer d’autres utilisateurs si des n-uplets doivent être réservés en vue d’une mise à jour (option FOR UPDATE). Une bonne habitude consiste à effectuer le OPEN le plus tard possible, et le CLOSE le plus tôt possible après le dernier FETCH.

1.    <nomCurseur>%FOUND est un booléen qui vaut TRUE si le dernier FETCH a ramené un n-uplet;

2.    <nomCurseur>%NOTFOUND est un booléen qui vaut TRUE si le dernier FETCH n’a pas ramené de n-uplet;

3.    <nomCurseur>%ROWCOUNT est le nombre de n-uplets parcourus jusqu’à l’état courant (en d’autres termes c’est le nombre d’appels FETCH);

4.    <nomCurseur>%ISOPEN est un boolén qui indique si un curseur a été ouvert.

Cela étant dit, le parcours d’un curseur consiste à l’ouvrir, à effectuer une boucle en effectuant des FETCH tant que l’on trouve des n-uplets (et qu’on souhaite continuer le traitement), enfin à fermer le curseur. Voici un exemple assez complet qui utilise un curseur paramétré pour parcourir un ensemble de films et leurs metteur en scène pour une année donnée, en afichant à chaque FETCH le titre, le nom du metteur en scène et la liste des acteurs. Remarquez que cette liste est elle-même obtenu par un appel à la fonction PL/SQL MesActeurs.

-- Exemple d'un curseur pour rechercher les films

-- et leur metteur en scène pour une année donnée

CREATE OR REPLACE PROCEDURE CurseurFilms (p_annee INT) AS

-- Déclaration d'un curseur paramétré

CURSOR MonCurseur (v_annee INTEGER) IS

SELECT idFilm, titre, prenom, nom

FROM Film, Artiste

WHERE idMES = idArtiste

AND            annee = v_annee;

-- Déclaration de la variable associée au curseur v_monCurseur MonCurseur%ROWTYPE;

-- Déclaration de la variable pour la liste des acteurs v_mesActeurs VARCHAR(255);

BEGIN

-- Ouverture du curseur

OPEN MonCurseur(p_annee);

-- On prend le premier n-uplet

FETCH MonCurseur INTO v_monCurseur;

-- Boucle sur les n-uplets

WHILE (MonCurseur%FOUND) LOOP

-- Recherche des acteurs avec la fonction MesActeurs v_mesActeurs := MesActeurs (v_monCurseur.idFilm);

DBMS_OUTPUT.PUT_LINE('Ligne ' || MonCurseur%ROWCOUNT ||

-- Passage au n-uplet suivant

FETCH MonCurseur INTO v_monCurseur;

END LOOP;

-- Fermeture du curseur

CLOSE MonCurseur;

EXCEPTION

WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE('Problème dans CurseurFilms : ' || sqlerrm);

END;

/

Le petit extrait d’une session sous SQL*Plus donné ci-dessous montre le résultat d’un appel à cette procédure pour

6.2. Les curseurs

l’année 1992.

SQL> set serveroutput on

SQL> execute CurseurFilms(1992);

Ligne 1 Film: Impitoyable, de Clint Eastwood, avec

Clint Eastwood, Gene Hackman, Morgan Freeman

Ligne 2 Film: Reservoir dogs, de Quentin Tarantino, avec

Quentin Tarantino, Harvey Keitel, Tim Roth, Chris Penn

La séquence des instructions OPEN, FETCH et CLOSE et la plus générale, notamment parce qu’elle permet de s’arrêter à tout moment en interrompant la boucle. On retrouve cette structure dans les langages de programmations comme C, Java et PHP. Elle a cependant l’inconvénient d’obliger à écrire deux instructions FETCH, l’une avant l’entrée dans la boucle, l’autre à l’intérieur. PL/SQL propose une syntaxe plus concise, basée sur la boucle FOR, en tirant partie de sa forte intégration avec SQL qui permet d’inférer le type manipulé en fonction de la définition d’un curseur. Cette variante de FOR se base sur la syntaxe suivante :

FOR <variableCurseur> IN <nomCurseur> LOOP

<instructions;

END LOOP;

L’économie de cette construction vient du fait qu’il n’est nécessaire ni de déclarer la variable variableCurseur, ni d’effectuer un OPEN, un CLOSE ou des FETCH. Tout est fait automatiquement par PL/SQL, la variable étant définie uniquement dans le contexte de la boucle. Voici un exemple qui montre également comment traiter des mises sur les n-uplets sélectionnés.

-- Exemple d'un curseur effectuant des mises à jour

-- On parcourt la liste des genres, et on les met en majuscules,

CREATE OR REPLACE PROCEDURE CurseurMAJ AS

-- Déclaration du curseur

CURSOR CurseurGenre IS

SELECT * FROM Genre FOR UPDATE;

BEGIN

-- Boucle FOR directe: pas de OPEN, pas de CLOSE

FOR v_genre IN CurseurGenre LOOP

IF ( IS NULL) THEN

DELETE FROM Genre WHERE CURRENT OF CurseurGenre;

ELSE

UPDATE Genre SET code=UPPER(code)

WHERE CURRENT OF CurseurGenre;

END IF;

END LOOP;

END;

/

Notez que la variable v_genre n’est pas déclarée explicitement. Le curseur est défini avec une clause FOR UPDATE qui indique au SGBD qu’une mise à jour peut être effectuée sur chaque n-uplet. Dans ce cas – et dans ce cas seulement – il est possible de faire référence au n-uplet courant, au sein de la boucle FOR, avec la syntaxe WHERE CURRENT OF <nomCurseur>.

Si on n’a pas utilisé la clause FOR UPDATE, il est possible de modifier (ou détruire) le n-uplet courant, mais en indiquant dans le WHERE de la clause UPDATE la valeur de la clé. Outre la syntaxe légèrement moins concise, cette désynchronisation entre la lecture par le curseur, et la modification par SQL, entraîne des risques d’incohérence (mise à jour par un autre utilisateur entre le OPEN et le FETCH) qui sont développés dans le chapitre consacré à la concurrence d’accès ().

Il existe une syntaxe encore plus simple pour parcourir un curseur en PL/SQL. Elle consiste à ne pas déclarer explicitement de curseur, mais à placer la requête SQL directement dans la boucle FOR, comme par exemple :

FOR v_genre IN (SELECT * FROM Genre) LOOP

<instructions;

END LOOP;

1.    SQL%FOUND vaut TRUE si la mise à jour a affecté au moins un n-uplet;

2.    SQL%NOTFOUND vaut TRUE si la mise à jour n’a affecté aucun n-uplet;

3.    SQL%ROWCOUNT est le nombre de n-uplets affecté par la mise à jour;

4.    SQL%ISOPEN renvoie systématiquement FALSE puisque les trois phases (ouverture, parcours et fermeture) sont effectuées solidairement.

Le cas du SELECT est un peu différent : une exception est toujours levée quand une recherche sans curseur ne ramène pas de n-uplet (exception NO_DATA_FOUND) ou en ramène plusieurs (exception TOO_MANY_ROWS). Il faut donc être prêt à traiter ces exceptions pour ce type de requête. Par exemple, la recherche :

SELECT * INTO v_film

FROM Film WHERE titre LIKE 'V%';

devrait être traitée par un curseur car il y n’y a pas de raison qu’elle ramène un seul n-uplet.

6.3 Triggers

Le mécanisme de triggers (que l’on peut traduire par “déclencheur” ou “réflexe”) est implanté dans les SGBD depuis de nombreuses années, et a été normalisé par SQL99. Un trigger est simplement une procédure stockée dont la particularité principale est de se déclencher automatiquement sur certains événements mise à jour spécifiés par le créateur du trigger.

On peut considérer les triggers comme une extension du système de contraintes proposé par la clause CHECK : à la différence de cette dernière, l’événement déclencheur est explicitement indiqué, et l’action n’est pas limitée à la simple alternative acceptation/rejet. Les possibilités offertes par les triggers sont très intéressantes. Citons :

— la gestion des redondances; l’enregistrement automatique de certains évèvenements (auditing);

— la spécification de contraintes complexes liées à l’évolution des données (exemple : le prix d’une séance ne peut qu’augmenter);

Les triggers sont discutés dans ce qui suit de manière générale, et illustrés par des exemples ORACLE. Il faut mentionner que la syntaxe de déclaration des triggers est suivie par la plupart des SGBD, les principales variantes se situant au niveau du langage permettant d’implanter la partie procédurale. Dans ce qui suit, ce langage sera bien entendu PL/SQL.

6.3.1 Principes des triggers

Le modèle d’exécution des triggers est basé sur la séquence événement-Condition-Action (ECA) que l’on peut décrire ainsi :

— un trigger est déclenché par un évènement, spécifié par le programmeur, qui est en général une insertion, destruction ou modification sur une table;

6.3. Triggers

— la première action d’un trigger est de tester une condition : si cette condition ne s’évalue pas à TRUE, l’exécution s’arrête;

— enfin l’action proprement dite peut consister en toute ensemble d’opérations sur la base de données, effectuée si nécessaire à l’aide du langage procédural supporté par le SGBD.

Une caractéristique importante de cette procédure (action) est de pouvoir manipuler simultanément les valeurs ancienne et nouvelle de la donnée modifiée, ce qui permet de faire des tests sur l’évolution de la base.

Parmi les autres caractéristiques importantes, citons les deux suivantes. Tout d’abord un trigger peut être exécuté au choix une fois pour un seul ordre SQL, ou à chaque n-uplet concerné par cet ordre. Ensuite l’action déclenchée peut intervenir avant l’événement, ou après.

L’utilisation des triggers permet de rendre une base de données dynamique : une opération sur la base peut en déclencher d’autres, qui elles-mêmes peuvent entraîner en cascade d’autres réflexes. Ce mécanisme n’est pas sans danger à cause des risques de boucle infinie.

Maintenant il est facile d’imaginer une situation où on se retrouve avec des triggers en cascade. Prenons le cas d’une table Ville (nom,capacité) donnant le nombre de places de cinéma dans la ville.

Maintenant, supposons que la ville gère l’heure de la première séance d’une salle : on aboutit à un cycle infini!

6.3.2 Syntaxe

La syntaxe générale de création d’un trigger est donnée ci-dessous.

CREATE [OR REPLACE] TRIGGER <nomTrigger>

{BEFORE | AFTER}

{DELETE | INSERT | UPDATE [of column, [, column] ]}

[ OR {DELETE | INSERT | UPDATE [of column, [, column] ]}] ON <nomTable> [FOR EACH ROW] [WHEN <condition]

<blocPLSQL>

On peut distinguer trois parties dans cette construction syntaxique. La partie événement est spécifiée après BEFORE ou AFTER, la partie condition après WHEN et la partie action correspond au bloc PL/SQL. Voici quelques explications complémentaires sur ces trois parties.

— “Evénement”, peut être ‘ BEFORE‘‘ ou AFTER, suivi de DELETE, UPDATE ou INSERT séparés par des OR.

— “Condition”, FOR EACH ROW est optionnel. En son absence le trigger est déclenché une fois pour toute requête modifiant la table, et ce sans condition.

Sinon <condition> est toute condition booléenne SQL. De plus on peut rérférencer les anciennes et nouvelles valeurs du tuple courant avec la syntaxe new.attribut et ‘‘ old.attribut‘‘ respectivement.

— “Action” est une procédure qui peut être implantée, sous Oracle, avec le langage PL/SQL. Elle peut contenir des ordres SQL mais pas de mise à jour de la table courante.

Les anciennes et nouvelles valeurs du tuple courant sont référencées par et . Il est possible de modifier new et old. Par exemple =500; forcera l’attribut prix à 500 dans un BEFORE trigger.

La disponibilité de new et old dépend du contexte. Par exemple new est à NULL dans un trigger déclenché par DELETE.

CREATE TRIGGER CumulCapacite

AFTER UPDATE ON Salle

FOR EACH ROW

WHEN (new.capacite != old.capacite)

BEGIN

UPDATE Cinema

SET capacite = capacite - :old.capacite

WHERE nom = :new.nomCinema;

END;

+ :new.capacite

Pour garantir la validité du cumul, il faudrait créer des triggers sur les événements UPDATE et INSERT. Une solution plus concise (mais plus coûteuse) est de recalculer systématiquement le cumul : dans ce cas on peut utiliser un trigger qui se déclenche globalement pour la requête :

CREATE TRIGGER CumulCapaciteGlobal

AFTER UPDATE OR INSERT OR DELETE ON Salle

BEGIN

UPDATE Cinema C

SET capacite = (SELECT SUM (capacite)

FROM          Salle S

WHERE C.nom = S.nomCinema);

END;

6.3. Triggers

CHAPITRE7

Indices and tables

— genindex

— modindex

— search



.1. Schéma d’une base de données