IMPORTANT

Lenvironnement Oracle doit tre install avant le dbut du TP 6 Notice dinstallation la fin du TP 6 Nom de login Oracle : n info ou iup n 7 premires lettres du nom n initiale du prnom Exemple : infogrinr ou iupgrinr Mot de passe Oracle : le nom de login Oracle (pas le mot de passe Unix)
Richard Grin SQL page 2

SQL
Universit de Nice - Sophia Antipolis Richard Grin
Version 2.15 L3 14/10/07

Prsentation de SQL
SQL = Structured Query Language = langage dinterrogation structur Langage de gestion de bases de donnes relationnelles pour n interroger n mettre jour (LMD ; Langage de Manipulation des Donnes) n dfinir les donnes (LDD ; Langage de Dfinition des Donnes gres) n contrler laccs aux donnes (LCD ; Langage de Contrle de laccs aux Donnes)
page 3 Richard Grin SQL page 4

Introduction

Richard Grin

SQL

Norme SQL92 (ou SQL2)

Norme adopte en 1992 par l ISO (International Organisation for Standardization) Presque compltement implmente par les principaux SGBD : Oracle, DB2, Informix, MySQL, PostsgreSQL, Access, SQL Server, La norme SQL99 (ou SQL3) est dj l depuis longtemps mais elle est loin dtre implmente par tous ces SGBD

Oracle
SGBDR (Relationnel) qui utilise le langage SQL Langage procdural PL/SQL (dit L4G) propritaire Nombreux programmes utilitaires n SQL*PLUS : SQL interactif, avec quelques ajouts n SQL*FORMS : saisir/voir des donnes avec des formulaires n SQL*REPORTWRITER : rapports imprims n WebDB pour linterface avec le Web
n

...
SQL page 6

Richard Grin

SQL

page 5

Richard Grin

Connexion/Dconnexion
Connexion par sqlplus : sqlplus [nom[/mot de passe]] Base de travail INFO pour vous Dconnexion : exit (ou EXIT)

Identificateurs
Identificateurs pour les objets manipuls n 30 caractres au plus n lettres, chiffres, _, $ ou # (commence par une lettre) n pas un mot clef Quelques mots clefs : ASSERT, ASSIGN, AUDIT, COMMENT, DATE, DECIMAL, DEFINITION, FILE, FORMAT, INDEX, LIST, MODE, OPTION, PARTITION, PRIVILEGES, PUBLIC, SELECT, SESSION, SET,TABLE.
page 7 Richard Grin SQL page 8

Richard Grin

SQL

Tables
Relations stockes sous forme de tables composes de lignes et de colonnes SQL92 : le nom dune table est prcd du nom dun schma (pour runir tous les objets lis un mme thme) Sous Oracle, le schma est remplac par le nom de lutilisateur qui a cr la table : [Link] Par dfaut, le schma est le nom de lutilisateur connect
Richard Grin SQL page 9 Richard Grin

Exemple de table ; DEPT

Dept 10 20 30 NomD Finances Recherche Ventes Lieu Paris Grenoble Lyon

SQL

page 10

Exemple de table ; EMP

Matr 1200 2200 1780 Nom Dupond Durand Boisier Sal 2500 3000 2500 Com Sup Dept 10 10 2200 20 300 2200 500

Colonne
Toutes les donnes dune colonne sont dun mme type Identificateur unique pour les colonnes dune table, mais 2 colonnes dans 2 tables diffrentes peuvent avoir le mme nom Le nom complet dune colonne comprend le nom de la table laquelle elle appartient (obligatoire en cas dambigut) : [Link], [Link]
Richard Grin SQL page 12

Richard Grin

SQL

page 11

Types de donnes SQL2

Types numriques Types pour les chanes de caractres Types temporels (dates, heures, minutes,) SQL2 na pas de type pour les donnes trs volumineuses telles que les images et les sons SQL2 ne permet pas lutilisateur de crer ses propres types

Types de donnes

Richard Grin

SQL

page 13

Richard Grin

SQL

page 14

Types numriques (1)

Nombres entiers : n SMALLINT sur 2 octets n INTEGER sur 4 octets virgule flottante : n REAL n DOUBLE PRECISION (ou FLOAT) Constantes : 253.8, -10, 1.3E-5

Types numriques (2)

Nombres dcimaux nombre fixe de dcimales : n DECIMAL(nbChiffres, nbDcimales) n NUMERIC(nbChiffres, nbDcimales)
Prcision impose pour les calculs

NUMERIC(8, 2) ou DECIMAL(8, 2) : 6 chiffres avant la virgule et 2 aprs Constantes : 253.8, -10

page 15 Richard Grin SQL page 16

Richard Grin

SQL

Types chanes de caractres

CHAR(longueur) chanes de caractres avec un nombre fixe de caractres VARCHAR(longueurMaximum) chanes de caractres avec un nombre variable de caractres (mais un nombre maximum de caractres) CHAR(5) : chane de 5 caractres VARCHAR(20) : chane de 20 caractres au plus Constante : 'Comptabilit', 'Aujourdh''ui'
Richard Grin SQL

Types temporels
DATE pour les dates TIME pour les heures, minutes et secondes TIMESTAMP pour un moment prcis : date et

heures, minutes et secondes, avec une prcision jusqu la microseconde (un millionime de seconde)

' doubl

page 17

Richard Grin

SQL

page 18

Type boolen
BIT permet denregistrer un bit

Types numriques d'Oracle

Oracle accepte les types numriques SQL2 mais il les traduit dans ses propres types
NUMBER

Exemples : BIT(1) BIT(4) Pas support par Oracle

nombre virgule flottante avec jusqu' 38 chiffres significatifs NUMBER(nbChiffres [, nb-dcimales]) nombre dcimal d'au plus nbChiffres chiffres dont nbdcimales aprs la virgule

Richard Grin

SQL

page 19

Richard Grin

SQL

page 20

Types chanes de caractres d'Oracle

CHAR, comme la norme SQL2 VARCHAR est accept mais Oracle conseille d'utiliser VARCHAR2 qui a les mmes proprits

Types temporels d'Oracle

Le type DATE remplace les types DATE et TIME de SQL2 DATE correspond une date avec une prcision jusqu' la seconde Constantes : '10/05/1998', '10 MAY 1998'

le format dpend de la localisation de la base

Richard Grin

SQL

page 21

Richard Grin

SQL

page 22

Select simple
SELECT expression1, expression2, FROM table WHERE prdicat
select nomE, poste from emp; select * from dept; select nomE, sal + comm from emp; select matr, nomE, sal * 1.15 from emp where sal + comm >= 12500;

Interrogations simples

Richard Grin

SQL

page 23

Richard Grin

SQL

page 24

Expressions
Ces expressions se trouvent la suite du select ou du where ; elles peuvent comporter des noms de colonnes constantes n oprateurs arithmtiques n concatnations de chanes de caractres (||) n calculs sur les dates (+ et -) n fonctions
n n
Richard Grin SQL page 25

Valeur NULL
Valeur attribue aux attributs qui n'ont pas reu de valeur Les expressions qui contiennent la valeur NULL ont la valeur NULL : comm + sal est NULL si comm est NULL Sauf la fonction COALESCE

Richard Grin

SQL

page 26

COALESCE
COALESCE(expr1, expr2,) retourne la valeur de la 1re expression non NULL parmi les expressions expr1, expr2, COALESCE(comm, 0) COALESCE(comm, salaire, 0) COALESCE(poste, 'simple soldat') Support par Oracle 10g mais pas par Oracle 9i

NVL
NVL peut tre utilis la place de coalesce dans Oracle 9i NVL(expression, valeur) renvoie la valeur de lexpression si elle nest pas NULL, et valeur sinon Exemple : NVL(comm, 0)

Richard Grin

SQL

page 27

Richard Grin

SQL

page 28

NULLIF
Permet de rcuprer des donnes dune BD qui nutilisait pas la valeur NULL Exemple : select nomE, NULLIF(comm, -1) from emp

Signification de NULL
NULL signifie quune donne est manquante et quon ne connat donc pas sa valeur Cependant, dans la pratique il peut signifier (entre autres) : n pas applicable (seuls les commerciaux touchent une commission) ; viter, mais possible pour simplifier le schma de la base n valeur 0 : on peut considrer que la valeur NULL correspond la valeur 0 ; viter !
Richard Grin SQL page 30

Richard Grin

SQL

page 29

Logique 3 valeurs
La valeur NULL implique une logique 3 valeurs : n vrai n faux n on ne sait pas si cest vrai ou faux Par exemple, la condition salaire > 1000 pour un employ peut tre n vrai, si le salaire est 1200 n faux, si le salaire est 700 n on ne sait pas, si le salaire est NULL
Richard Grin SQL page 31

Implication de cette logique

Soit une liste L qui contient NULL : (1, 8, NULL, 78, 7) Est-ce que 1 appartient L ? videmment oui Est-ce que 10 appartient L ? On ne sait pas ! Est-ce que 10 nappartient pas L ? On ne sait pas !
Richard Grin SQL page 32

Pseudo-table DUAL
Particularit d'Oracle Contient une seule ligne et une seule colonne Ne peut tre utilise qu'avec une requte select Pour afficher une expression dont la valeur ne dpend d'aucune table en particulier select user from dual; select sysdate from dual;

Cration dune table (LDD)

Richard Grin

SQL

page 33

Richard Grin

SQL

page 34

Cration dune table

CREATE TABLE table ( colonne1 type1, colonne2 type2, ... . . .) Exemple :
create table article ( ref char(5) not null, nom varchar(20), prix numeric(9,2), dateMAJ date);
Richard Grin SQL

Valeur par dfaut

On peut donner une valeur par dfaut pour une colonne : create table dept ( numDept integer not null, nomDept varchar(20), ville varchar(30) default 'Nice'); On peut aussi donner une fonction comme valeur par dfaut ; par exemple, default sysdate

Option not null si la colonne doit obligatoirement tre renseigne

page 35

Richard Grin

SQL

page 36

DESCRIBE
Cette commande SQL*PLUS dOracle (pas SQL) affiche une description des colonnes dune table : SQL > describe article; Name Null ? Type REF Not null CHAR(5) NOM VARCHAR(20) PRIX NUMBER(9,2) DATEMAJ DATE
Mme si le type donn la cration est DECIMAL page 37

Contraintes dintgrit

Richard Grin

SQL

Richard Grin

SQL

page 38

Dfinition
Une contrainte dintgrit est une contrainte que doivent vrifier les donnes dune table Une commande est annule par le SGBD si son excution viole une des contraintes

Types de contraintes d'intgrit

PRIMARY KEY : cl primaire FOREIGN KEY REFERENCES : cl trangre UNIQUE : 2 lignes ne peuvent avoir la mme valeur pour les colonnes spcifies CHECK : contrainte de domaine, ou autre ; porte sur une seule ligne

Richard Grin

SQL

page 39

Richard Grin

SQL

page 40

Types de contraintes d'intgrit

2 types de contraintes : n contrainte de colonne (concerne une seule colonne) n contrainte de table

Dfinition des contraintes

Les contraintes sont dfinies dans les commandes CREATE (ou ALTER) TABLE n l'intrieur des dfinitions de colonnes pour les contraintes de colonne n au mme niveau que les dfinitions de colonnes pour les contraintes de table CONSTRAINT nomContrainte dfinitionContrainte

Richard Grin

SQL

page 41

Richard Grin

SQL

page 42

Cl primaire
Si la cl primaire nest forme que dune seule colonne, le plus simple est dajouter une contrainte de colonne :
create table emp ( matr integer constraint pkemp primary key, . . .

Une erreur ne pas faire

Si une table a une cl primaire forme de 2 colonnes, il ne faut pas dclarer 2 contraintes de colonne Il faut dclarer une seule contrainte de table portant sur les 2 colonnes : constraint pkpar primary key(matr, codeP)

Sinon, il faut ajouter une contrainte de table :

create table participation ( matr integer, codeP integer, . . ., constraint pkpar primary key(matr, codeP))
Richard Grin SQL page 43 Richard Grin SQL page 44

Contrainte sur les cls primaires

Aucune des colonnes de la cl primaire ne peut avoir la valeur null

Contrainte UNIQUE
2 lignes de la table ne pourront avoir la mme valeur (sauf NULL) Correspond un identificateur (cl candidate si minimal), si on ajoute une contrainte NOT NULL

Richard Grin

SQL

page 45

Richard Grin

SQL

page 46

Cl trangre
Si une seule colonne forme la cl trangre, le plus simple est dutiliser une contrainte de colonne :
create table emp ( Optionnel si colonne . . ., rfrence est cl primaire dept integer constraint r_dept references dept(dept))

Cl trangre
Peut tre une contrainte de table : FOREIGN KEY (colonne1, colonne2,) REFERENCES table-ref [(col1, col2,)] Exemple :
create table emp ( . . ., dept integer, constraint r_dept foreign key(dept) references dept(dept)) Il faut ajouter foreign key

Richard Grin

SQL

page 47

Richard Grin

SQL

page 48

Cls trangres
Les colonnes de lautre table rfrences (col1, col2,) doivent avoir la contrainte PRIMARY KEY ou UNIQUE constraint r_dept references dept(dept)

Option ON DELETE CASCADE (sans)

create table emp ( . . . dept integer constraint r_dept references dept) On ne peut supprimer un dpartement s'il est rfrenc par une ligne de la table emp

dept doit tre cl primaire, ou unique

Richard Grin

SQL

page 49

Richard Grin

SQL

page 50

Option ON DELETE CASCADE (avec)

create table emp ( . . . dept number(2) constraint r_dept references dept on delete cascade) La suppression d'un dpartement entrane automatiquement la suppression de toutes les lignes de la table emp qui rfrencent ce dpartement
Richard Grin SQL page 51

Autres options pour les cls trangres

on delete set null 4 autres options de SQL2 pas implmentes par Oracle : on delete set default on update cascade on update set null on update set default

Richard Grin

SQL

page 52

Exemples divers de contraintes

create table emp ( matr integer constraint pkemp primary key, nomE varchar(30) constraint nom_unique unique constraint maj check (nomE = upper(nomE)), dept smallint constraint r_emp_dept references dept constraint ndept check (dept in (10, 20, 30, 40)));
Richard Grin SQL page 53

Exemples divers de contraintes

create table participation ( matr integer constraint r_part_emp references emp, codeP varchar(5) constraint r_part_projet references projet, , constraint pkpart primary key(matr, codeP));
Richard Grin SQL page 54

Modification des contraintes

ALTER TABLE emp DROP CONSTRAINT nom_unique ADD (CONSTRAINT sal_min check(sal + coalesce(comm, 0) > 5000)) RENAME CONSTRAINT truc TO machin; On ne peut ajouter que des contraintes de table

Vrification des contraintes

En fonctionnement normal les contraintes sont vrifies chaque requte SQL Cette vrification peut tre gnante, en particulier lors de lajout de plusieurs lignes de donnes Exemple : si on a cette contrainte sur la colonne SUP de la table EMP : constraint sup_ref_emp references EMP La contrainte oblige ajouter les suprieurs en premier

Richard Grin

SQL

page 55

Richard Grin

SQL

page 56

Contraintes diffrables
Pour pallier ce problme, la vrification dune contrainte peut tre diffre la fin de la transaction Exemple : CONSTRAINT sup_ref_emp references dept DEFERRABLE

Contraintes diffrables
La syntaxe : CONSTRAINT nom-contrainte def-contrainte [NOT] DEFERRABLE [INITIALLY {DEFERRED | IMMEDIATE}] La valeur par dfaut est NOT DEFERRABLE

Richard Grin

SQL

page 57

Richard Grin

SQL

page 58

Diffrer une contrainte

Indiquer quune contrainte est diffrable ne suffit pas pour la diffrer si elle na pas t dclare INITIALLY DEFERRED Par dfaut, une contrainte diffrable ne lest que si on la diffre par la commande SET CONSTRAINT nom-contrainte DEFERRED; Elle ne sera diffre que pour la dure dune transaction

Invalider des contraintes avec Oracle

Oracle permet aussi dinvalider des contraintes Utile pour, par exemple, amliorer les performances lors de lajout dune grande quantit de donnes dans la base : ALTER TABLE table {DISABLE | ENABLE} constraint nom-contrainte

Richard Grin

SQL

page 59

Richard Grin

SQL

page 60

10

Dfinition
Tables qui stockent les descriptions des objets de la base Tenues jour automatiquement par le SGBD Peuvent tre consultes au moyen du langage SQL

Dictionnaire des donnes

Richard Grin

SQL

page 61

Richard Grin

SQL

page 62

2 types de vues
Vues nommes USER_* dcrivent les objets du schma qui appartiennent lutilisateur ; par exemple, USER_TABLES Vues nommes ALL_* dcrivent les objets du schma qui appartiennent lutilisateur, ou sur lesquels lutilisateur a reu des droits ; ALL_TABLES

Tables ou vues du dictionnaire

DICTIONARY (DICT) : vues du dictionnaire USER_TABLES (TAB) : tables et vues cres par l'utilisateur USER_CATALOG (CAT) : tables et vues sur lesquelles l'utilisateur a des droits, sauf celles du dictionnaire des donnes USER_TAB_COLUMNS (COLS) : colonnes des tables ou vues cres par l'utilisateur USER_INDEXES (IND) : index crs par l'utilisateur ou indexant des tables de l'utilisateur ...
Richard Grin SQL page 64

Richard Grin

SQL

page 63

Exemple dutilisation
Recherche dinformations sur les contraintes On commence par chercher les noms des vues : select table_name from dict where table_name like '%CONSTRAINTS%'; On fait afficher les noms des colonnes : describe USER_CONSTRAINTS On cherche les informations que lon veut : select constraint_name, constraint_type, table_name from user_constraints
Richard Grin SQL page 65 Richard Grin SQL page 66

Langage de manipulation des donnes (LMD)

11

Commandes de manipulation des donnes

INSERT pour ajouter des lignes UPDATE pour modifier des lignes DELETE pour supprimer des lignes

Insertion
INSERT INTO table [(colonne1, colonne2,)] VALUES (valeur1, valeur2,)

ou
INSERT INTO table [(colonne1, colonne2,)] select

La liste des colonnes est optionnelle ; par dfaut, toutes les colonnes sont dans lordre donn lors de la cration de la table Si la commande comporte une liste, les colonnes qui ne sont pas dans la liste auront la valeur NULL
Richard Grin SQL page 67 Richard Grin SQL page 68

Insertion
Dans les programmes, il faut toujours donner la liste des colonnes dont on donne les valeurs pour faciliter la maintenance de lapplication En interactif, on peut sen passer

Exemples
insert into dept values (10, 'Finance', 'Paris'); insert into dept (lieu, nomD, dept) values ('Grenoble', 'Recherche', 20); insert into emp (matr, nomE, dept, sal) select matr + 100, nomE, 60, sal * 0.15 from emp where dept = 10;

Richard Grin

SQL

page 69

Richard Grin

SQL

page 70

Modification
UPDATE table SET colonne1 = expr1, colonne2 = expr2, [ WHERE prdicat ] Toutes les lignes par dfaut ou UPDATE table [ synonyme ] SET (colonne1, colonne2, ) = (select ) [ WHERE prdicat ] Ne doit renvoyer qu'une seule ligne

Exemples
update emp set dept = 10 where nomE = 'Martin'; update emp set sal = sal * 1.1 where poste = 'Commercial';

Richard Grin

SQL

page 71

Richard Grin

SQL

page 72

12

Exemples (2)
update emp set sal = (select avg(sal) * 1.1 from emp where poste = 'Secrtaire') where nomE = 'Clment'; update emp E Synchronisation set (sal, comm) = (select avg(sal), avg(comm) from emp where dept = [Link]) where poste = 'Secrtaire';

Suppressions
DELETE FROM table [ WHERE prdicat ]

Attention ! s'il n'y a pas de clause WHERE, toutes les lignes sont supprimes Exemple :
delete from emp where dept = 10;

Richard Grin

SQL

page 73

Richard Grin

SQL

page 74

Une requte forme un tout indivisible

Si une erreur survient pendant lexcution dune requte SQL, toutes les modifications dj effectues par la requte sont annules automatiquement On peut gnraliser ce comportement un ensemble de requtes SQL en utilisant les transactions

Transactions

Richard Grin

SQL

page 75

Richard Grin

SQL

page 76

Transaction
Ensemble de modifications de la base qui forment un tout indivisible : la fin de la transaction, toutes les modifications effectues pendant la transaction sont sauvegardes ou annules

Dbut d'une transaction

Dans la norme SQL2 toute modification appartient une transaction Une transaction dmarre au dbut de la session Une transaction dmarre automatiquement aprs la fin dune transaction (pas de commande pour dmarrer une transaction) La structure des transactions est plate : les transactions ne peuvent se chevaucher

Richard Grin

SQL

page 77

Richard Grin

SQL

page 78

13

Terminer une transaction

Pour terminer une transaction on peut n valider la transaction (COMMIT) : toutes les modifications deviennent effectives n annuler la transaction (ROLLBACK) : toutes les modifications sont annules Les ordres DDL (create table par exemple) provoquent un COMMIT automatique

Proprits des transactions - ACID

Atomicit : un tout indivisible Cohrence : une transaction doit laisser la base dans un tat cohrent ; elle ne doit pas mettre les donnes dans un tat anormal Isolation : une transaction est isole des autres transactions (dans une certaine mesure) Durabilit : le SGBD doit garantir que les modifications d'une transaction valide seront conserves, mme en cas de panne
Richard Grin SQL page 80

Richard Grin

SQL

page 79

Proprits des transactions - ACID

AID est du ressort du systme transactionnel du SGBD C est du ressort de lutilisateur mais il est aid par I, car il na pas considrer les interactions avec les autres transactions n par la vrification automatique des contraintes dintgrit par le SGBD I est assur par le systme de contrle de la concurrence du SGBD et AD sont supports par les procdures de reprise aprs panne du SGBD
n
Richard Grin SQL page 81

Exemple dannulation dune transaction

insert into dept values (10, 'Finance', 'Paris'); delete from emp; rollback;

Richard Grin

SQL

page 82

Transaction non termine

La dernire transaction est automatiquement valide la sortie de SQL*PLUS si elle ne se termine pas par un ROLLBACK Attention ! ce comportement dpend du logiciel utilis ; avec dautres logiciels une transaction non valide explicitement est annule

Isolation des transactions

En fonctionnement standard les modifications effectues par une transaction T ne sont connues par les autres transactions quaprs validation de T En fait, il existe plusieurs niveaux disolation (voir
cours sur la concurrence)

Toujours terminer explicitement une transaction

Richard Grin

SQL

page 83

Richard Grin

SQL

page 84

14

Transactions longues
Exemple : organisation par une agence de voyage dun voyage Nice Wuhan (Chine) Ncessite la rservation de plusieurs billets davion : Nice Paris ; Paris Beijing ; Beijing Wuhan On commence par rserver les 2 premiers mais si on ne peut trouver de Beijing Wuhan, il faut tout annuler On met donc toutes ces rservations dans une transaction ; a peut tre long si lagence discute avec le client pendant la transaction
Richard Grin SQL page 85

Problmes avec les transactions longues

Manque de souplesse : si on ne trouve pas de voyage Beijing Wuhan, on annule tout On aurait pu garder le Nice Paris et essayer de passer par Shanghai pour aller Wuhan, en annulant seulement le Paris Beijing Autre problme : le contrle de la concurrence effectue des blocages sur les tables et les lignes qui ne sont relchs qu la fin de la transaction Un problme de communication peut provoquer lannulation des premires rservations alors quon pourrait simplement ressayer le lendemain
Richard Grin SQL page 86

Transactions embotes
Extension de la notion de transaction plate vite les annulations compltes de transactions Apporte plus de souplesse dans les transactions longues et multi-sites Permet de limiter la dure des blocages des ressources systme

Dfinition des transactions embotes

Une transaction globale (mre) peut contenir des soustransactions filles qui, elles-mmes, peuvent avoir des filles Lannulation dune transaction nannule pas ncessairement la transaction mre ; celle-ci peut
n n n n

dcider dun traitement substitutif reprendre la transaction annule sannuler ou mme ignorer lannulation (traitement pas indispensable)

Lannulation dune transaction provoque lannulation automatique de toutes ses transactions filles
Richard Grin SQL page 87 Richard Grin SQL page 88

Points de reprise
Sans passer au modle des transactions embotes, on peut assouplir le modle des transactions plates Dsigner des points de reprise dans une transaction : savepoint nomPoint Possible dannuler toutes les modifications effectues depuis un point de reprise : rollback to nomPoint vite dannuler toute la transaction et permet dessayer de pallier le problme
Richard Grin SQL page 89

Exemple
insert into .; savepoint p1; delete from ; update ; savepoint p2; insert into ; -- Problme ! rollback to p2; insert into ; -- on essaie autre chose commit;
Richard Grin SQL page 90

15

Syntaxe gnrale
SELECT FROM WHERE GROUP BY HAVING ORDER BY L'ordre des clauses est impos SELECT et FROM sont obligatoires
Richard Grin SQL page 91 Richard Grin SQL page 92

Interrogation de la base

Clause SELECT
select [distinct] expression1 [ [AS] nom1], expression2 [ [AS] nom2], . . . nom1 :

Exemples
select distinct poste from emp; select nomE, sal + coalesce(comm, 0) as "Salaire Total" from emp;

en-tte de la colonne (entre guillemets si mot-clef ou si contient plusieurs mots) n alias pour dsigner la colonne dans une autre partie du select
n

select *

Toutes les colonnes

Richard Grin

SQL

page 93

Richard Grin

SQL

page 94

select dans une expression

select nomE, sal / (select sum(sal) from emp) * 100 from emp

select dans une expression

On peut utiliser lalias pour trier par ordre dcroissant des salaires :
select nomE, (select count(*) from emp where sal > [Link]) + 1 as rang from emp e1 order by rang

Le select de lexpression peut tre synchronis avec le select principal :

select nomE, (select count(*) from emp where sal > [Link]) + 1 as rang from emp e1 synonyme de emp pour lever lambigut dans le select interne
Richard Grin SQL page 95

Richard Grin

SQL

page 96

16

Clause FROM
FROM table1 [synonyme1], table2 [synonyme2], Produit cartsien des tables sil y en a plusieurs Possible de se restreindre un sous-ensemble du produit cartsien (voir jointure)

select [Link], [Link] from dept A, dept B DEPT ---------30 20 10 30 20 10 30 20 10

Richard Grin

NOMD -----------VENTES VENTES VENTES RECHERCHE RECHERCHE RECHERCHE FINANCE FINANCE FINANCE
SQL

10 VENTES 20 RECHERCHE 30 FINANCE

Richard Grin

SQL

page 97

page 98

Clause FROM
Certains SGBDs (et la norme SQL-2) permettent l'utilisation d'un SELECT la place du nom d'une table :
select nomE, sal, sal / total * 100 Pourcentage from emp, (select sum(sal) as total from emp);

Clause WHERE
La clause WHERE comporte de nombreuses possibilits : n oprateurs de comparaison n oprateurs logiques n jointures n sous-interrogations

Richard Grin

SQL

page 99

Richard Grin

SQL

page 100

Oprateurs de comparaison
=, !=, <, >, <=, >=, BETWEEN, LIKE, NOT LIKE, IN, NOT IN, IS NULL, IS NOT NULL LIKE permet d'utiliser des jokers : n % pour une chane de caractres de longueur quelconque n _ pour un seul caractre Attention, expression = NULL nest jamais vrai, il faut utiliser expression IS NULL
Richard Grin SQL page 101 Richard Grin

Exemples
select * from emp where poste = 'Secrtaire'; select * from emp where sal between 10000 and 15000; select * from emp where dept in (10, 30); select * from emp where comm is not null; select * from emp where nomE like '%A%';

SQL

page 102

17

Oprateurs logiques
AND, OR, NOT Exemples :
select nomE from emp where dept = 30 and (sal > 10000 or comm is null); select * from emp where not (poste = 'Directeur' or poste = 'Secrtaire');

Jointures
Traduction de l'qui-jointure emp J{dept} dept :
select nomE, nomD from emp, dept where [Link] = [Link]

Autre syntaxe :
select nomE, nomD from emp JOIN dept ON [Link] = [Link]

et si on met and la place de or ?

Richard Grin SQL page 103 Richard Grin SQL page 104

Jointure de plus de 2 tables

select nomE, nomP from emp, participation, projet where [Link] = [Link] and [Link] = [Link] Autre syntaxe : select nome, nomp from emp join participation on [Link] = [Link] join projet on [Link] = [Link]
Richard Grin SQL page 105

Jointure naturelle
La jointure seffectue sur toutes les colonnes qui ont le mme nom dans les 2 tables ; ces colonnes ne sont pas rptes dans la jointure Les colonnes qui participent la jointure ne doivent tre prfixes par un nom de table

Richard Grin

SQL

page 106

Exemples de jointure naturelle

select nomE, nomD, dept from emp NATURAL JOIN dept select nome, nomp from emp NATURAL JOIN participation NATURAL JOIN projet

Jointure d'une table avec elle-mme

Alias indispensable pour le nom de la table afin de lever lambigut sur les colonnes : select [Link] "Employ", [Link] "Suprieur" from emp join emp supe on [Link] = [Link]

Richard Grin

SQL

page 107

Richard Grin

SQL

page 108

18

Jointures non qui

Les jointures non qui peuvent tre traduites comme les qui-jointures, en utilisant d'autres oprateurs de comparaison select [Link], [Link] from emp emp1 join emp emp2 on [Link] < [Link]

Jointure externe
Dans une jointure n'apparaissent que les lignes qui ont une ligne correspondante dans l'autre table Dans l'exemple suivant, un dpartement qui n'a pas d'employ n'apparatra pas : select nomE, nomD from emp join dept on [Link] = [Link] Si on veut qu'il apparaisse, on doit utiliser une jointure externe

Richard Grin

SQL

page 109

Richard Grin

SQL

page 110

Syntaxe SQL-2 de la jointure externe

select nomE, nomD from emp RIGHT OUTER JOIN dept ON [Link] = [Link] RIGHT indique que lon veut afficher toutes les lignes de la table de droite (dept) a revient ajouter une ligne fictive dans lautre table emp Cette ligne fictive aura toutes ses colonnes null, sauf la colonne de jointure Il existe de mme LEFT OUTER JOIN et FULL
OUTER JOIN
Richard Grin SQL page 111

Syntaxe Oracle de la jointure externe

Syntaxe dOracle avant la version 9i On ajoute un (+) du ct o il manque une ligne Pour l'exemple prcdent, il manque des employs , donc on ajoute (+) du ct de la table emp :
select nomE, nomD from emp, dept where [Link] (+) = [Link]

Richard Grin

SQL

page 112

Sous-interrogations
Une clause WHERE peut comporter un ordre SELECT embot : select nomE from emp where poste = (select poste from emp where nomE = 'Martin'); Cette sous-interrogation doit ramener une seule ligne et une seule colonne Remplace le select interne par NULL sil ne renvoie aucune ligne (ou erreur, suivant les SGBD)
Richard Grin SQL page 113 Richard Grin SQL page 114

Des variantes de sous-interrogations ramnent plusieurs colonnes ou plusieurs lignes

19

Sous-interrogation ramenant 1 ligne, 1 colonne

WHERE expression op (SELECT ) o op est un des oprateurs de comparaison =, !=, <, >, <=, >= Exemple : select nomE from emp where sal >= (select sal from emp where nomE = 'Mercier')
Richard Grin SQL page 115

Sous-interrogation ramenant plusieurs lignes

WHERE WHERE WHERE WHERE expression expression expression expression op ANY (SELECT ) op ALL (SELECT ) IN (SELECT ) NOT IN (SELECT )

o op est un des oprateurs de comparaison =, !=, <, >, <=, >= ANY : vrai si la comparaison est vraie pour au moins une des valeurs ramenes par le SELECT ALL : vrai si la comparaison est vraie pour toutes les valeurs ramenes par le SELECT
Richard Grin SQL page 116

Exemple
Remarque : n = ANY est quivalent IN n != ALL est quivalent NOT IN select nomE, sal from emp where sal > all (select sal where dept select nomE, sal from emp where sal > all (select sal where dept from emp = 30) from emp = 38888)

Ce dpartement nexiste pas !

Richard Grin SQL page 117 Richard Grin SQL page 118

Rflexion sur ALL

Quand x > all (x1, x2, , xn) est faux ? Quand un xi tel que xi x Si un xi tel que xi x, lexpression est vraie Donc si la liste (x1, x2, , xn) est vide, lexpression est toujours vraie

Retour sur NULL

La condition expression not in (expr1, expr2, null) nest jamais vrifie. Pourquoi ? Est-ce que la condition expression in (expr1, expr2, null) peut tre vrifie ? Rappel : la logique de SQL nutilise pas seulement vrai et faux mais aussi je ne sais pas , reprsent par NULL Utile savoir pour les sous interrogations qui renvoient NULL pour une des lignes
page 119 Richard Grin SQL page 120

Richard Grin

SQL

20

Sous-interrogations ; optimisation
Soit un select qui comporte une sous-interrogation : select nom from emp where dept in (select dept from dept where lieu = NICE) Pour chaque employ, le select peut lancer la sousinterrogation pour savoir si lemploy est dans un dpartement qui se trouve Nice En fait, le moteur de recherche du SGBD va optimiser en lanant dabord la sous-interrogation et en conservant en mmoire les dpartements de Nice
Richard Grin SQL page 121

Sous-interrogations synchronises
Cette optimisation nest pas possible quand la sous-interrogation utilise une des valeurs ramenes par linterrogation principale On dit que la sous-interrogation est synchronise avec linterrogation principale Notation pointe utilise pour se rfrer dans la sous-interrogation une colonne de Considr comme une constante par la linterrogation principale : sous-interrogation select nomE from emp E where dept != (select dept from emp where matr = [Link]);
Richard Grin SQL page 122

Sous-interrogation ramenant 1 ligne de plusieurs colonnes

WHERE (expr1, expr2,) op (SELECT ) o op est = ou != (mais pas <, >, <=, >=) le select renvoie 1 seule ligne

Relation dordre
Il ny a pas de relation dordre totale naturelle sur les tuples 5 > 3, 12 > 7 On ne peut comparer (5, 7) et (3, 12)

Exemple :
select nomE from emp where (poste, sal) = (select poste, sal from emp where nomE = 'Mercier');

Richard Grin

SQL

page 123

Richard Grin

SQL

page 124

Sous-interrogation ramenant plusieurs lignes de plusieurs colonnes

WHERE WHERE WHERE WHERE (expr1, expr2,) op ANY (SELECT ) (expr1, expr2,) op ALL (SELECT ) (expr1, expr2,) IN (SELECT ) (expr1, expr2,) NOT IN (SELECT ) o op est = ou != (mais pas <, >, <=, >=)

EXISTS
La clause EXISTS(select ) est vraie si le select renvoie au moins une ligne La sous-interrogation est le plus souvent synchronise :
select nomE from emp E where exists (select null from emp where sup = [Link]); synchronisation

Exemple :
select nomE from emp where (poste, sal) in (select poste, sal from emp where dept = 10);

Richard Grin

SQL

page 125

Richard Grin

SQL

page 126

21

Division
R
A x y z x B 1 2 1 3

Division avec NOT EXISTS

R B S

S
C 1 3

A x

R B S = { a R[A] / c S, (a, c) R } Une traduction mot mot en SQL donne :

select A from R R1 where not exists (select C from S where not exists (select A, B from R where A = R1.A and B = S.C)

R B S = les A qui sont associs tous les C = {a R[A] / c S, (a, c) R} / = {a R[A] / c S, (a, c) R} /
Richard Grin SQL page 127

Richard Grin

SQL

page 128

Avec le dessin
R
A x y z x B 1 2 1 3

Exemple
Afficher la liste des numros des dpartements qui ont tous les postes La solution est R poste S o R = EMP [ dept, poste ] S = EMP [ poste ]
select distinct dept from emp E where not exists (select poste from emp E2 where not exists (select dept, poste from emp where dept = [Link] and poste = [Link])
page 129 Richard Grin SQL page 130

S
C 1 3

R B S

A x

select A from R R1 where not exists (select C from S where not exists (select A, B from R where A = R1.A and B = S.C)
Richard Grin SQL

Avec le dessin
Dept Poste Poste Dept

Simplification
La traduction mot mot peut souvent tre simplifie :

R = EMP [ dept, poste ] S = EMP [ poste ]

select distinct dept from emp E Possible de simplifier where not exists (select poste from emp E2 where not exists (select dept, poste from emp where dept = [Link] and poste = [Link])
Richard Grin SQL page 131

select dept from dept where not exists (select poste from emp E2 where not exists (select dept, poste from emp where dept = [Link] and poste = [Link])

Richard Grin

SQL

page 132

22

Exemple avec jointure

Quand la table dividende correspond une jointure, on peut souvent simplifier le select le plus externe

Exemple
Donnez les numros des dpartements qui ont particip tous les projets
Dept CodeP CodeP Dept

R S o R = (PARTICIPATION J{matr} EMP) [ dept, codeP ] S = PROJET [ codeP ]

Richard Grin SQL page 133 Richard Grin SQL page 134

PARTICIPATION J{matr} EMP PROJET Dept CodeP CodeP

Avant simplification
Dept Dept CodeP

Aprs simplification
CodeP Dept

Pas besoin de faire une jointure pour avoir un numro de dpartement select distinct dept from participation natural join emp E where not exists (select codep from projet where not exists (select dept, codep from participation natural join emp where dept = [Link] and codep = [Link]))
Richard Grin SQL page 135

select dept from dept where not exists (select codep from projet where not exists Mais ici la jointure est indispensable (select dept, codep from participation natural join emp where dept = [Link] and codep = [Link]))
Richard Grin SQL page 136

Division en comptant
On peut obtenir le mme rsultat en comptant le nombre de valeurs distinctes (exemple venir)

Fonctions de groupe
Les fonctions de groupe peuvent apparatre dans une expression du select ou du having : AVG moyenne SUM somme MIN plus petite valeur MAX plus grande valeur VARIANCE variance STDDEV cart type COUNT(*) nombre de lignes COUNT(col) nombre de valeurs non NULL dans la colonne COUNT(DISTINCT col) nombre de valeurs distinctes
Richard Grin SQL page 138

Richard Grin

SQL

page 137

23

Exemples
select count(*) from emp; select count(comm) from emp where dept = 10; select sum(sal) from emp where dept = 10; select max(sal) from emp where poste = 'INGENIEUR'; select nome, sal from emp where sal = max(sal);

Niveaux de regroupement
A un niveau de profondeur (relativement aux sousinterrogations) d'un SELECT, les fonctions de groupe et les colonnes doivent tre toutes du mme niveau de regroupement Par exemple, si on veut le nom et le salaire des employs qui gagnent le plus dans l'entreprise, la requte suivante provoquera une erreur :
select nome, sal from emp where sal = max(sal)

Interdit, car dept et max(sal) ne sont pas au mme niveau de regroupement

SQL page 139

Solution :
select nome, sal from emp where sal = (select max(sal) from emp)
Richard Grin SQL page 140

Richard Grin

Clause GROUP BY
Permet de regrouper des lignes qui ont les mmes valeurs pour des expressions :
GROUP BY expression1, expression2,

Exemples
select dept, poste, count(*) from emp group by dept, poste; select dept, count(comm) from emp group by dept; select nome, dept, sal from emp where (dept, sal) in (select dept, max(sal) from emp group by dept); Schma retenir pour obtenir des optima sur des regroupements

Il nest affich qu'une seule ligne par regroupement de lignes Exemple :

select dept, count(*) from emp group by dept;

Sans doute moins performant :

select nome, dept, sal from emp E where sal = (select max(sal) from emp where dept = [Link]);
page 141 Richard Grin SQL page 142

Richard Grin

SQL

Exemples (2)
select nomD, avg(sal) from emp natural join dept group by nomD; select dept, count(*) from emp where poste = 'SECRETAIRE' group by dept; select count(*) "Nbre employs", count(comm) "Nbre commissions", count(comm) / count(*) "Ratio" from emp;

Restrictions pour les expressions

Dans la liste des expressions du select ne peuvent figurer que des caractristiques lies aux groupes : n des fonctions de groupes n des expressions figurant dans le GROUP BY
select dept, nomE, sal from emp group by dept;

Richard Grin

SQL

page 143

Richard Grin

SQL

page 144

24

Exemple
select nomd, sum(sal) from emp natural join dept group by [Link]

Clause HAVING
Cette clause sert slectionner les groupes : HAVING prdicat Le prdicat ne peut porter que sur des caractristiques de groupe

Interdit ; il aurait fallu crire :

select nomd, sum(sal) from emp natural join dept group by nomd

Richard Grin

SQL

page 145

Richard Grin

SQL

page 146

Exemples
select dept, count(*) from emp where poste = 'Secrtaire' group by dept having count(*) > 1; select nomd "Dpartement", count(*) "Nombre de secrtaires" from emp natural join dept where poste = 'Secrtaire' group by nomd having count(*) = (select max(count(*)) from emp where poste = 'Secrtaire' group by dept);
Richard Grin SQL page 147 Richard Grin

Exemples (2)
select dept, count(*) from emp group by dept having count(*) = max(count(*)); Interdit ! car pas le mme niveau de regroupement que le reste du select

SQL

page 148

Exemples (2)
select dept, count(*) from emp group by dept having count(*) = (select max(count(*)) from emp group by dept);

La division en comptant
Lorsque la colonne B de la table dividende ne peut contenir que des valeurs de la colonne C de la table diviseur, on peut obtenir le quotient en comptant le nombre de valeurs distinctes : select dept from emp natural join participation group by dept having count(distinct codeP) = (select count(codeP) from projet)
Richard Grin SQL page 150

Richard Grin

SQL

page 149

25

Exemple
10 P1 P1 10 P2 P2 10 P1 20 P1 10 est associ 2 projets distincts 20 est associ 1 seul projet et il y a 2 projets select dept from emp natural join participation group by dept having count(distinct codeP) = (select count(codeP) from projet)
Richard Grin SQL page 151

Contre-exemple
Pas vrai si la table dividende peut contenir des valeurs qui ne sont pas dans la table diviseur Dans lexemple suivant, le dpartement 20 aurait t faussement slectionn : 10 P1 P1 10 P2 P2 20 P1 20 P3

Richard Grin

SQL

page 152

La division en comptant
On pourrait tout de mme sen sortir par une requte un peu plus complexe : select dept from emp natural join participation where codeP in (select codeP from projet) group by dept having count(distinct codeP) = (select count(codeP) from projet)
Richard Grin SQL page 153

Clause ORDER BY
La clause ORDER BY prcise l'ordre des lignes d'un SELECT :
ORDER BY expr1 [DESC], expr2 [DESC],

On peut aussi donner le numro de la colonne qui servira de cl de tri Exemples :

select dept, nomD from dept order by nomD; select dept, nomD from dept order by 2;

Richard Grin

SQL

page 154

Exemples
select nome, poste from emp order by dept, sal desc; select dept, sum(sal) "Total salaires" from emp group by dept order by 2; select dept, sum(sal) "Total salaires" from emp group by dept order by sum(sal);

Fonctions
Fonctions arithmtiques : abs(n), mod(m, n), power(n, e), round(n, p), trunc(n, p), sign(n), sqrt(n), greatest(n1, n2,), least(n1, n2,) Conversions nombre - chane de caractres : to_char(n, format), number(chane) Fonctions date : round(date, prcision), trunc(date, prcision), sysdate Conversions date - chane de caractres : to_char(date, format), to_date(chane, format)
Richard Grin SQL page 156

Richard Grin

SQL

page 155

26

Fonctions (2)
Fonctions sur les chanes de caractres : length(chane), substr(chane, position, longueur), instr(chane, sous-chane, position, n), upper(chane), lower(chane), lpad(chane, long, car), rpad(chane, long, car), ltrim(chane, car), rtrim(chane, car), translate(chane, lesCar1, lesCar2), replace(chane, ancienne, nouvelle)

Exemple
select nomE, to_char(datemb, 'DD/MM/YYYY') from emp where round(sysdate datemb) > 3;

Richard Grin

SQL

page 157

Richard Grin

SQL

page 158

Fonction de choix
2 variantes :
CASE WHEN condition1 THEN resultat1 WHEN condition2 THEN resultat2 ELSE resultat3 END CASE expression WHEN valeur1 THEN resultat1 WHEN valeur2 THEN resultat2 ELSE resultat3 END
Richard Grin SQL page 159

Exemples
SELECT nome, poste FROM emp order by CASE poste WHEN 'Prsident' THEN 1 WHEN 'Directeur' THEN 2 ELSE 3 END; SELECT nome, poste FROM emp order by CASE WHEN poste = 'Prsident' THEN 1 WHEN poste = 'Directeur' THEN 2 ELSE 3 END;
Richard Grin SQL page 160

Oprateurs ensemblistes
On peut effectuer des oprations sur plusieurs select considrs comme des ensembles de lignes : select UNION select select INTERSECT select select MINUS select Ensembles au sens mathmatique : si 2 lignes des 2 select dune union ont les mmes valeurs, lunion naura quune seule ligne avec ces valeurs

Exemple d'oprateur ensembliste

select dept from dept minus select dept from emp;

Richard Grin

SQL

page 161

Richard Grin

SQL

page 162

27

Exemple d'oprateur ensembliste

select nomE, 'salaire' TYPE, sal MONTANT from emp union select nomE, 'commission', comm from emp where comm is not null; nomE Toto Bibi Toto
Richard Grin

UNION ALL
Pour conserver les doublons avec UNION

TYPE salaire salaire commission

SQL

MONTANT 1200 5000 240

page 163 Richard Grin SQL page 164

Oprateurs ensembliste et order by

Seul le dernier select peut recevoir une clause order by

Limiter le nombre de lignes

Problme de portabilit si on veut ne faire afficher quune partie des lignes rcupres par un select

Richard Grin

SQL

page 165

Richard Grin

SQL

page 166

Exemple
MySQL et Postgresql : SELECT matr, nomE FROM emp LIMIT 10 Oracle : SELECT matr, nomE FROM emp WHERE ROWNUM <= 10 SQL Server : SELECT TOP 10 matr, nomE FROM emp
Richard Grin SQL page 167

Difficult avec Oracle

Si le select contient un order by : rownum numrote avant le tri, alors que top et limit numrotent aprs le tri Avec Oracle, il faut donc ruser : utiliser une sousrequte qui trie, alors que la requte principale limite le nombre de lignes : select * from (select nomE, sal from emp order by sal) where rownum <= 10
Richard Grin SQL page 168

28

Cration de table par copie Langage de dfinition des donnes

CREATE TABLE table (col1 type, ) AS select
create table dept2 (cle integer, nom varchar(20)) as select dept, nomd from dept; create table dept10 as select * from emp where dept = 10;

Richard Grin

SQL

page 169

Richard Grin

SQL

page 170

Modifier la dfinition d'une table

ALTER TABLE table ADD (col1 type1, col2 type2,) ALTER TABLE table MODIFY (col1 type1, col2 type2,) ALTER TABLE table DROP COLUMN colonne; On ne peut modifier une colonne que si la colonne ne contient que des valeurs null ou si la nouvelle dfinition est compatible avec les valeurs dj entres dans cette colonne

Richard Grin

SQL

page 171

Richard Grin

SQL

page 172

Exemples
alter table emp add (situ_famille char(1), nbEnfants smallint); alter table emp modify (situ_famille char(2));

Supprimer une colonne

Tous les SGBDs ne permettent pas de supprimer une colonne dune table Si cest impossible, on peut mettre toutes les valeurs de la colonne null pour gagner de la place On peut aussi n utiliser create table as pour transfrer les autres donnes dans une nouvelle table qui na pas cette colonne n supprimer la 1re table n renommer la nouvelle table
page 173 Richard Grin SQL page 174

Richard Grin

SQL

29

Supprimer une colonne (Oracle 9i)

ALTER TABLE emp DROP COLUMN comm nest disponible sous Oracle que depuis la version 9i Il nest pas possible de supprimer une colonne n rfrence par une cl trangre n sur laquelle un index a t construit Exemple : alter table emp drop column situ_famille;
Richard Grin SQL page 175

Supprimer une table

DROP TABLE table

Richard Grin

SQL

page 176

Renommer une table

On peut renommer une table : RENAME ancienNom TO nouveauNom Commande quivalente : ALTER TABLE ancienNom RENAME TO nouveauNom

Synonymes
Si une table doit tre utilise par plusieurs utilisateurs, il peut tre intressant de lui donner un synonyme public : CREATE PUBLIC SYNONYM employe FOR [Link]

Richard Grin

SQL

page 177

Richard Grin

SQL

page 178

Vues
Une vue est une vision virtuelle partielle ou particulire des donnes dune ou plusieurs tables La dfinition dune vue est donne par un select : les donnes de la vue sont celles retournes par le select Les utilisateurs peuvent consulter ou modifier la base travers la vue comme si c'tait une table relle
page 179 Richard Grin SQL page 180

Vues

Richard Grin

SQL

30

Cration et suppression d'une vue

CREATE VIEW vue [(col1, col2,)] AS select [WITH CHECK OPTION] Le select peut contenir toutes les clauses d'un select sauf order by create view emp10 as select * from emp where dept = 10; DROP VIEW vue
Richard Grin SQL page 181

Exemple de cration de vues

create view deptStat (nom, inf, moy, max, total) as select nomd, min(sal), avg(sal), max(sal), sum(sal) from emp natural join dept group by [Link]

Richard Grin

SQL

page 182

Utilisation des vues dans un select

Dans un select on peut utiliser une vue la place d'une table
select * from emp10; select nom, total from deptStat where total > 100000;

Suppression avec une vue

On peut effectuer des delete travers une vue, sous les conditions suivantes sur le select qui dfinit la vue : n une seule table n pas de group by n pas de distinct n pas de fonction de groupe

Richard Grin

SQL

page 183

Richard Grin

SQL

page 184

Modification avec une vue

On peut effectuer des update travers une vue, sous les conditions du delete, et en plus : n les colonnes modifies sont des colonnes relles de la table (pas des expressions)
update emp10 set sal = sal * 1.1;

Insertion avec une vue

On peut effectuer des insert travers une vue, sous les conditions du update, et en plus : n toute colonne not null de la table reprsente par la vue est prsente dans la vue
insert into emp10 (matr, nome, ) values (1200, 'DUBOIS', );

Richard Grin

SQL

page 185

Richard Grin

SQL

page 186

31

Option CHECK
Si la vue a t cre avec "WITH CHECK OPTION", toute modification au travers de la vue ne peut donner des donnes qui ne seraient pas affiches par la vue Par exemple,
update emp10 set dept = 20;

Utilit des vues

Les vues permettent de dissocier n la faon dont les utilisateurs voient les donnes n du dcoupage en tables On favorise ainsi l'indpendance entre les programmes et les donnes Si un programme utilise des vues, on peut, par exemple, remplacer une table par 2 tables sans modifier le programme de consultation des donnes ; il suffit de modifier la dfinition des vues

sera interdit

Richard Grin

SQL

page 187

Richard Grin

SQL

page 188

Utilit des vues (2)

Peuvent simplifier la consultation de la base en enregistrant des select complexes Participent la protection des donnes : n on peut donner accs une vue, sans donner accs la table sous-jacente n une vue peut ne donner accs qu certaines colonnes ou lignes d'une table n les modifications des donnes peuvent tre restreintes avec la clause WITH CHECK OPTION
Richard Grin SQL page 189 Richard Grin

Index

SQL

page 190

Index
Un index utilise des techniques informatiques pour rendre trs rapides les accs aux valeurs d'une colonne
select * from emp where nomE = 'Dupond'

Cration d'un index

CREATE [UNIQUE] INDEX nomIndex ON table (col1, col2,)
create index nomE on emp(nomE);

est trs long si la table emp contient des millions de lignes Un index bien construit permet d'obtenir l'emplacement des informations sur Dupond en quelques accs disques (moins de 5, mme s'il y a des millions de lignes dans la table EMP)
Richard Grin SQL page 191

Le nom choisi doit tre unique parmi tous les index de toutes les tables Oracle cre automatiquement un index sur les colonnes qui ont des contraintes Primary key et Unique

Richard Grin

SQL

page 192

32

Utilisation d'un index

Aprs sa cration un index est gr automatiquement par le SGBD Il est transparent pour l'utilisateur : celui-ci interroge la base de la mme faon que si l'index n'existait pas Le SGBD peut utiliser un index s'il pense que la requte sera acclre Les index ralentissent les modifications des donnes
Richard Grin SQL page 193

Suppression d'un index

DROP INDEX nomIndex

Richard Grin

SQL

page 194

Utilit
Les identifiants de lignes non significatifs sont prfrables Le plus simple est davoir des cls qui sont des nombres entiers Le problme : gnrer des entiers sans que 2 lignes puissent avoir le mme identifiant, mme en situation de concurrence entre plusieurs transactions

Gnration de cls

Richard Grin

SQL

page 195

Richard Grin

SQL

page 196

Une mauvaise solution

Prendre le plus grand nombre dj utilis dans la table comme identifiant et ajouter 1 En pseudo code : lock table; val = select max(cle) from table; insert into table values (val + 1,); commit;

Inconvnients de la solution 1
Cette solution nest pas performante : n ncessite un accs la base n il faut trouver le plus grand n ncessite un blocage de la table pour viter que 2 transactions nobtiennent la mme valeur Si on veut garder un historique, on peut se retrouver avec des lignes qui ont le mme identificateur (si la ligne de plus grand identificateur est supprime)
Richard Grin SQL page 198

Richard Grin

SQL

page 197

33

Solution 2
Une table contient la prochaine cl attribuer La valeur est incrmente chaque nouvelle cl

Variantes
Variante 1 : Une seule table contient une seule valeur utilise pour les identifiants de toutes les tables Variante 2 : Une table par cl Variante 3 : Une seule table qui contient une ligne par table qui a besoin dune cl Quelle colonnes dans la table des cls ?

Richard Grin

SQL

page 199

Richard Grin

SQL

page 200

Pseudo-code de la variante 1
lock table_cle; update table_cle set cle = cle + 1; val = select cle from table_cle; commit; insert into table values (val,); commit;
Richard Grin SQL page 201

Inconvnients de l'utilisation d'une table

Cette solution nest pas trs performante : n ncessite un accs la base (mais cette petite table est conserve en mmoire centrale dans le cache du SGBD) n il est ncessaire de bloquer laccs la valeur

Richard Grin

SQL

page 202

Les squences
Les versions actuelles des SGBD offrent des solutions qui ne ncessitent pas daccs aux donnes de la base Inconvnient : pas les mmes solutions dans tous les SGBDs Les squences sont disponibles avec Oracle,

Crer une squence

CREATE SEQUENCE nom_squence [INCREMENT BY entier1] [START WITH entier2] create sequence seqdept increment by 10 start with 10

DB2 et PostgreSQL

Richard Grin

SQL

page 203

Richard Grin

SQL

page 204

34

Utilisation des squences

Deux pseudo-colonnes permettent d'utiliser les squences : n CURRVAL retourne la valeur courante n NEXTVAL incrmente la squence et retourne la nouvelle valeur insert into dept(dept, nomd) values ([Link], 'Finances')

CURRVAL et NEXTVAL
On ne peut utiliser CURRVAL quaprs avoir utilis NEXTVAL au moins une fois dans la session de travail NEXTVAL modifie immdiatement la valeur future pour les autres transactions, mme sil est lanc dans une transaction non valide La valeur de CURRVAL ne dpend que des NEXTVAL lancs dans la mme transaction

Richard Grin

SQL

page 205

Richard Grin

SQL

page 206

Modification des squences

ALTER SEQUENCE nom_squence INCREMENT BY entier1 alter sequence seqdept increment by 5 On ne peut modifier la valeur de dpart

Rcuprer plusieurs identificateurs

Mettre un incrment suprieur 1 permet d'obtenir plusieurs identificateurs en un seul appel pour obtenir de meilleures performances Par exemple, si on veut des identificateurs pour des lignes de factures, on en a souvent besoin de plusieurs en mme temps

Richard Grin

SQL

page 207

Richard Grin

SQL

page 208

Informations sur les squences

Afficher la valeur dune squence : select [Link] from dual Tables du dictionnaire des donnes : USER_SEQUENCES et ALL_SEQUENCES

Autres solutions
DB2 et SQL Server ont une clause IDENTITY pour dire quune colonne est un identifiant, avec une valeur qui est gnre automatiquement par le SGBD La norme SQL 3 a normalis cette possibilit Pas disponible sous Oracle MySQL permet de dclarer une colonne AUTO_INCREMENT

Richard Grin

SQL

page 209

Richard Grin

SQL

page 210

35

Comparaison squences et autres solutions

Les squences sont souvent plus souples si on veut rcuprer la valeur des identifiants pendant lenregistrement des donnes Exemple : dans lenregistrement dune facture avec ses lignes de factures, on doit disposer de lidentifiant de la facture pour le mettre en cl trangre dans les lignes de facture

Exemple
facture(id, nom_client, date_facture,) ligne_facture(id_facture, nb_ligne, quantite, id_article)

Richard Grin

SQL

page 211

Richard Grin

SQL

page 212

Procdures stockes
Les SGBD modernes fonctionnent en client/serveur Chaque requte issue dun client n transite sur le rseau n est compile lorsquelle arrive au serveur : celuici recherche en particulier la meilleure faon de rpondre la requte La compilation peut tre une tape complexe et longue traiter et coteuse en ressources Les procdures stockes sont des requtes prcompiles et stockes sur le serveur
page 213 Richard Grin SQL page 214

Procdures stockes

Richard Grin

SQL

3 tapes
1. 2. 3.

Langages des procdures stockes

Les procdures stockes peuvent inclure n des variables, des boucles et des tests n plusieurs requtes SQL Le plus souvent, elles sont crites dans un langage spcial n PL/SQL pour Oracle n PSM pour la norme SQL (PL/SQL lui ressemble mais nest pas vraiment compatible) n Java pour les dernires versions dOracle
Richard Grin SQL page 216

criture du code de la procdure Compilation et enregistrement sur le serveur ; on donne un nom la procdure stocke Excution de la procdure en passant au serveur le nom de la procdure

Richard Grin

SQL

page 215

36

Avantages et inconvnients des procdures stockes

Amliorent les performances et diminuent le trafic sur le rseau Encapsulent les processus mtier (botes noires) Mais les langages d'criture et les appels de ces procdures ne sont pas normaliss On perd donc de la portabilit si on utilise des procdures stockes

Exemple sous Oracle

create or replace procedure augmentation (unDept in integer, pourcentage in number, cout out number) is begin select sum(sal) * pourcentage / 100 into cout from emp where dept = unDept; update emp set sal = sal * (1 + pourcentage / 100) where dept = unDept; end;
Richard Grin SQL page 218

Richard Grin

SQL

page 217

Utilisation dune procdure stocke

Depuis un langage de 3me gnration (C, Java,) on les appelle presque comme des procdures ou des fonctions du langage Depuis SQL*PLUS le plus simple est de lancer execute <nom-procdure>

Paramtre out avec SQL*PLUS

Pour rcuprer un paramtre out , appeler la procdure dans une autre procdure ou lancer lexcution dun bloc anonyme :
declare cout float; begin augmentation(10, 5, cout); dbms_output.put_line('cout = ' || cout); end; lancer set serveroutput on pour permettre laffichage sur lcran

Richard Grin

SQL

page 219

Richard Grin

SQL

page 220

Supprimer une procdure

DROP PROCEDURE nomProcdure

Compilation dune procdure

Une procdure stocke est compile ds sa cration Les erreurs ventuelles sont montres par la commande SHOW ERRORS Si le schma de la base ou la rpartition des donnes a chang, il faut recompiler une procdure pour optimiser son excution : ALTER PROCEDURE nomProcdure COMPILE
page 221 Richard Grin SQL page 222

Richard Grin

SQL

37

Informations sur les procdures

Vues USER_PROCEDURES et USER_SOURCE du dictionnaire des donnes

Fonctions
Comme les procdures stockes mais elles renvoient une valeur Elles peuvent tre utilises dans les requtes SQL comme les fonctions prdfinies

Richard Grin

SQL

page 223

Richard Grin

SQL

page 224

Exemple en PL/SQL
create or replace function euro_to_fr(somme in number) return number is taux constant number := 6.55957; begin return somme * taux; end;
Richard Grin SQL page 225

Exemple dutilisation dune fonction

select nome, sal, euro_to_fr(sal) from emp

Richard Grin

SQL

page 226

Triggers (dclencheurs)
Compils et stocks sur le serveur Souvent crits dans le mme langage que les procdures stockes Leur excution est dclenche par des actions sur la base, par exemple une insertion dans une table Ils compltent les contraintes d'intgrit en permettant des contrles et des traitements plus complexes Pas normaliss en SQL2 (normaliss en SQL3)
page 227 Richard Grin SQL page 228

Triggers

Richard Grin

SQL

38

Exemple de trigger
CREATE OR REPLACE TRIGGER totalSalaire AFTER UPDATE OF salaire ON emp REFERENCING OLD AS ancien, NEW AS nouveau FOR EACH ROW Autre possibilit : for each statement update cumul set augmentation = augmentation + [Link] - [Link] where matricule = [Link]
Richard Grin SQL page 229

Utilisation de :OLD et :NEW

CREATE OR REPLACE TRIGGER totalSalaire AFTER UPDATE OF salaire ON emp FOR EACH ROW update cumul set augmentation = augmentation + :[Link] - :[Link] where matricule = :[Link]

Richard Grin

SQL

page 230

Clause WHEN
create or replace trigger modifsalaire before update of sal on emp for each row when ([Link] < [Link]) begin raise_application_error(-20001, 'Interdit de baisser le salaire ! (' || :[Link] || ')'); end;

Afficher les erreurs de compilation : SHOW ERRORS Supprimer un trigger : DROP TRIGGER nomTrigger Tables du dictionnaire des donnes : USER_TRIGGERS, USER_TRIGGER_COLS

Richard Grin

SQL

page 231

Richard Grin

SQL

page 232

Ordres interdits
Les ordres COMMIT et ROLLBACK sont interdits dans un trigger

Scurit

Richard Grin

SQL

page 233

Richard Grin

SQL

page 234

39

Travail en scurit
Plusieurs utilisateurs peuvent travailler en toute scurit sur la mme base Les donnes peuvent tre confidentielles ou partages entre plusieurs utilisateurs

Contrle de l'accs la base

Le contrle de l'accs la base est effectu en associant chaque utilisateur n un nom de login n un mot de passe Chaque utilisateur a des privilges d'accs la base : droit ou non n de crer des tables ou des vues n de lire ou de modifier des tables ou des vues
n

SQL page 236

Richard Grin

SQL

page 235

Richard Grin

Rles
Pour faciliter la cration de nouveaux utilisateurs avec certains droits, la plupart des SGBD permettent de crer des rles Un rle dfinit des droits et interdictions Quand on cre un nouvel utilisateur, on lui affecte un ou plusieurs rles

Propritaire des donnes

Une table (et les donnes quelle contient) appartient celui qui la cr Le propritaire d'une table peut donner d'autres le droit de travailler avec sa table Les vues permettent daffiner les droits que lon donne sur ses propres donnes : on peut donner des droits sur des vues et pas sur les tables sous-jacentes
Richard Grin SQL page 238

Richard Grin

SQL

page 237

Accorder des droits sur des objets

GRANT privilge ON table/vue L'utilisateur qui reoit TO {utilisateur|rle|PUBLIC} le privilge pourra le donner [WITH GRANT OPTION] d'autres utilisateurs Des privilges (il y en a dautres) :
n n n n n n

Accorder des droits sur des actions

GRANT privilge TO {utilisateur|rle|PUBLIC} [WITH GRANT OPTION] Des privilges (il y en a dautres) :
n n n

SELECT INSERT UPDATE [(col1, col2,)] DELETE ALTER ALL PRIVILEGES

SQL page 239

CREATE INDEX CREATE TABLE ALL PRIVILEGES

Richard Grin

Richard Grin

SQL

page 240

40

Accorder des droits (exemples)

grant select on emp to clement; grant select, update on emp to clement, chatel; grant select on emp to public;

Reprendre les droits

REVOKE privilge ON table/vue FROM utilisateur

Changer son mot de passe :

grant connect to bibi identified by motDePasse;

Richard Grin

SQL

page 241

Richard Grin

SQL

page 242

Exemple de cration de rle

create role lmiage identified by lmiage; grant create cluster, create procedure, create session, create sequence, create snapshot, create synonym, create table, create trigger,create view to lmiage;

Exemple de cration dutilisateur Oracle

CREATE USER toto IDENTIFIED BY toto DEFAULT TABLESPACE LINFO TEMPORARY TABLESPACE USERTEMP quota 5M on LINFO profile default / GRANT LMIAGE TO toto /
Richard Grin SQL page 244

Richard Grin

SQL

page 243

Injection de code SQL

Tous les langages de programmation permettent de lancer des requtes SQL pour interagir avec une base de donnes Il est alors possible de construire une requte SQL dont le texte contient une partie entre par l'utilisateur (par concatnation de chanes de caractres) Attention alors l'injection de code SQL

Exemple dinjection de code SQL

Un programme demande son nom et son mot de passe un utilisateur, et les range dans 2 variables nom et mdp Il lance cette requte et accepte lutilisateur si elle renvoie bien une ligne
"select * from utilisateur" + " where nom = '" + nom + "' and mdp = '" + mdp + "'"

Quel est le problme ?

Richard Grin

SQL

page 245

Richard Grin

SQL

page 246

41

Le problme
Un pirate sait quun des utilisateurs autoriss sappelle Dupond Il saisit Dupond' -- pour le nom et a pour le mot de passe La requte devient :
select * from utilisateur where nom = 'Dupond' --' and mdp = 'a'

Les parades
Toujours vrifier la saisie dun utilisateur avant de sen servir pour construire une requte SQL Pour lexemple, il aurait suffit dinterdire le caractre ' ou de le doubler Les API fournies avec les langages pour accder une base de donnes offrent des facilits pour se protger contre linjection de code SQL Avec JDBC par exemple, il suffit dutiliser des paramtres et PreparedStatement ; les caractres spciaux comme ' sont alors traits comme des caractres ordinaires
Richard Grin SQL page 248

Mais -- indique un commentaire avec le SGBD utilis ; donc la requte excute sera :
select * from utilisateur where nom = 'Dupond'
Richard Grin SQL page 247

Un SGBD est prvu pour travailler avec de nombreux utilisateurs/transactions

Gestion des accs concurrents

Richard Grin

SQL

page 249

Richard Grin

SQL

page 250

Quelques questions
A quel moment les modifications sont-elles vues par les autres transactions ? Que se passe-t-il lorsque plusieurs transactions veulent modifier les mmes donnes ? Un ordre SQL (moyenne des salaires, par exemple) tient-il compte des modifications apportes par les autres transactions valides pendant son excution ?

Problmes lis aux accs concurrents

Il peut se produire des pertes de donnes quand plusieurs processus veulent modifier les mmes donnes en mme temps Les principaux cas d'cole sont : n mise jour perdue n lecture inconsistante n lecture non reproductible n ligne fantme

Richard Grin

SQL

page 251

Richard Grin

SQL

page 252

42

Mise jour perdue

S 500 2500 1500 Temps

Eviter les mises jour perdues

S 500 3500 1500 Temps

Transaction T1 s = Lire S s = s + 1000

Transaction T2 s = Lire S s = s + 2000

Transaction T1 Bloquer S s = Lire S s = s + 1000 Enregistrer s Dbloquer S

Transaction T2

s = Lire S ; attente...

Enregistrer s Enregistrer s

s = Lire S

s = s + 2000 Enregistrer s
Richard Grin SQL page 253 Richard Grin SQL page 254

Problmes lis aux blocages : interblocage

Temps

Lecture inconsistante
Temps

Transaction T1 Bloquer A Bloquer B Attente...

Transaction T2 Bloquer B

Transaction T1 V = 100 ROLLBACK

Transaction T2 v = Lire V (100) Travaille avec v = 100

Bloquer A Attente...
Richard Grin SQL page 255

Ce cas n'arrive pas si les modifications ne sont visibles par les autres qu'aprs un COMMIT
Richard Grin SQL page 256

Lecture non rptitive

Temps

Lignes fantmes
Ce problme survient quand une transaction na pas peru la cration dune ligne par une autre transaction Par exemple, une transaction lit dabord le nombre demploys et lance ensuite la lecture dinformations sur chacun de ces employs Si une autre transaction a ajout des employs, le nombre lu auparavant ne correspond plus la ralit
page 257 Richard Grin SQL page 258

Transaction T1 Lire V

Transaction T2 V = V + 100 COMMIT

Lire V Pour viter cela, T1 devrait bloquer les donnes lues (V)
Richard Grin SQL

43

Lignes fantmes
Temps

Transactions srialisables
On vient de voir que lexcution de transactions entrelaces peut provoquer des pertes de donnes ou de cohrence Pour viter ces problmes, le SGBD doit sarranger pour que lexcution de plusieurs transactions entrelaces fournisse les mmes rsultats que lexcution des mmes transactions les unes la suite des autres (dans un ordre quelconque)
Richard Grin SQL page 260

Transaction T1
Rcupre le nombre demploys du dept 10 (35)

Transaction T2

Ajoute un employ au dept 10 COMMIT Rcupre les informations sur les employs du dept 10 (36 lignes) Pour viter cela, T1 devrait bloquer la table des employs au dbut de la transaction (pas possible de bloquer des lignes qui nexistent pas !)
Richard Grin SQL page 259

Moyens de srialiser
Le moyen le plus courant sappelle le verrouillage 2 phases : n on doit bloquer un objet avant dagir sur lui (lecture ou criture) n on ne peut plus faire de blocage aprs avoir dbloquer un objet On a donc 2 phases : 1. acquisitions des verrous 2. abandons des verrous (en pratique, souvent au COMMIT ou ROLLBACK)
Richard Grin SQL page 261

Inter-blocage avec le verrouillage 2 phases

Les situations dentrelacement des transactions qui auraient provoqu des problmes vont se traduire alors par des attentes ou des interblocages (avec redmarrage de certaines transactions)

Richard Grin

SQL

page 262

Estampillage
Lestampillage est une autre stratgie que le verrouillage 2 phases pour assurer la srialisation des transactions Lanciennet des transactions est repre par une estampille donne la cration de la transaction (un nombre incrment chaque attribution) Chaque donne accde par une transaction reoit lestampille de cette transaction

Estampillage
Les algorithmes qui utilisent lestampillage assurent que lexcution concurrente des transactions sera quivalente lexcution squentielle de ces transactions dans lordre de leur estampille

Richard Grin

SQL

page 263

Richard Grin

SQL

page 264

44

Ide pour les algorithmes destampillage

Une transaction T ne peut accder une donne si cette donne a dj t accde par une transaction plus jeune (donc d'estampille plus grande que celle de T) La transaction trop vieille T est annule si elle sest faite doubler par une transaction plus jeune Elle est relance avec une estampille plus grande ; plus jeune, elle a plus de chance de pouvoir accder la donne en passant aprs
Richard Grin SQL page 265

Rsultat de lestampillage
Cet algorithme est trop restrictif et ne va autoriser que peu dexcutions parallles des transactions La transaction redmarre va peut-tre entrer en conflit avec une autre transaction plus jeune Dautres algorithmes destampillage moins simplistes permettent damliorer le paralllisme, en particulier en distinguant les accs en lecture et les accs en criture Malgr tout ce type de traitement est souvent trop restrictif et nuit la concurrence
Richard Grin SQL page 266

Autres traitements
Pour amliorer les performances dans des situations de forte concurrence, les SGBD offrent la possibilit dtre plus permissif et de ne pas srialiser les transactions

Niveaux d'isolation des transactions sous Oracle

SET TRANSACTION ISOLATION LEVEL {SERIALIZABLE | READ COMMITED} SERIALIZABLE : les transactions s'excutent totalement isoles des autres transactions et comme si elles sexcutaient les unes aprs les autres

Richard Grin

SQL

page 267

Richard Grin

SQL

page 268

Niveaux d'isolation des transactions sous Oracle (2)

READ COMMITED : les transactions ne voient les modifications des autres transactions qu'aprs les commit ; empche les principaux problmes de concurrence mais pas les lectures non rptitives ni les lignes fantmes

Autres niveaux d'isolation des transactions de SQL 2

REPEATABLE READ : empche les lectures non rptitives mais pas les lignes fantmes READ UNCOMMITED : les transactions voient les modifications avant mme le commit

Richard Grin

SQL

page 269

Richard Grin

SQL

page 270

45

En rsum
Niveaux par isolation dcroissante : n SERIALIZABLE (souhaitable, mais coteux car provoque des blocages de tables) n REPEATABLE READ (sous Oracle, remplac par les blocages explicites des lignes lues) n READ COMMITED (le plus souvent le niveau par dfaut, comme avec Oracle) n READ UNCOMMITED (trs rarement utilis ; pas possible sous Oracle)
Richard Grin SQL page 271

Au niveau dun seul ordre SQL Oracle

Un instantan de la base est pris au dbut de chaque ordre SQL Cet instantan est utilis pendant toute l'excution de l'ordre, mme si des donnes utilises par l'ordre sont modifies par une transaction valide avant la fin de l'excution

Richard Grin

SQL

page 272

Pratiquement
En fait, linstantan nest que virtuel Oracle sarrange pour que lordre SQL voie les donnes comme elles taient au dbut de lexcution, en gardant plusieurs versions des donnes Sil saperoit quune donne a t modifie depuis le dbut de lordre SQL, il utilise une ancienne version de la donne

Traitement des accs concurrents par les SGBD

Le but : favoriser au maximum les accs concurrents pour permettre l'excution du plus grand nombre de transactions dans un temps donn (argument commercial important) Tous les SGBDs n'ont pas les mmes solutions pour atteindre ce but

Richard Grin

SQL

page 273

Richard Grin

SQL

page 274

Dure des blocages

Un blocage na lieu que le temps dune transaction

Accs concurrents sous Oracle

Oracle, par dfaut, ne met aucun verrou pour effectuer une lecture Les lectures ne sont pas bloques par les critures, et vice-versa Pour cela, un historique des modifications est conserv (dans les segments de rollback) Une transaction est bloque si elle veut modifier des donnes qui sont en cours de modification par une autre transaction La granularit minimum de blocage est la ligne
page 275 Richard Grin SQL page 276

Richard Grin

SQL

46

Autre faon de traiter les accs concurrents

Beaucoup de SGBD bloquent les donnes lues n ce blocage ninterdit pas leur lecture par dautres transactions mais interdit leur modification Une criture bloque les donnes modifies n elles ne peuvent tre modifies par dautres transactions (comme avec Oracle) n mais elles ne peuvent pas non plus tre lues par dautres transactions
Richard Grin SQL page 277

Blocages explicites et implicites

Des blocages sont effectus implicitement par certaines commandes (en particulier UPDATE, INSERT et DELETE) Si le comportement par dfaut du SGBD ne convient pas pour un traitement, on peut effectuer des blocages explicites des lignes ou des tables Les inter-blocages sont dtects par le SGBD qui annule un des ordres qui a provoqu l'inter-blocage

Richard Grin

SQL

page 278

Granularit des blocages

2 types de blocages : n au niveau d'une table n au niveau d'une ligne Dans certains SGBDs le blocage dune ligne implique le blocage de toute la page/bloc (plusieurs lignes) qui contient la ligne Certains SGBD transforment de trop nombreux blocages de lignes dune table en un blocage de toute la table
Richard Grin SQL page 279

Types de blocages
Un blocage peut tre n exclusif : seul celui qui a bloqu peut modifier ou bloquer les donnes bloques n partag : plusieurs transactions peuvent effectuer en mme temps ce type de blocage C'est un blocage dfensif qui marque les donnes pour interdire qu'une autre transaction ne les bloque dans un mode trop restrictif, ou ne les modifie

Richard Grin

SQL

page 280

Blocages sur les tables

Nous allons tudier quelques blocages dont disposent les utilisateurs dOracle Nous ne verrons pas tous les types de blocages fournis par Oracle LOCK TABLE table IN EXCLUSIVE MODE [NOWAIT] blocage complet de la table ( viter si possible) ; on peut tout faire dans la table et les autres transactions ne peuvent que lire LOCK TABLE table IN SHARE MODE [NOWAIT] interdit aux autres transactions toute modification sur la table (sert pour faire des bilans) ; Autorise les autres transactions bloquer elles aussi en mode SHARE
Richard Grin SQL page 282

Richard Grin

SQL

page 281

47

Blocages partags sur les lignes

SELECT colonnes FROM table WHERE condition FOR UPDATE OF colonnes Ce blocage permet de lire des valeurs, et en mme temps de bloquer les lignes lues On peut ainsi travailler avec ces valeurs pour prparer leur modification Plusieurs transactions peuvent effectuer ce blocage sur des lignes diffrentes d'une mme table
Richard Grin SQL page 283

Blocages partags sur les lignes

SELECT FOR UPDATE n'interdit pas un blocage en mode partag de la table mais les lignes bloques ne pourront pas tre modifies ni bloques par une autre transaction On peut modifier ensuite ces valeurs par UPDATE ou DELETE (aprs un ventuel dblocage
d'un blocage partag sur la table, effectu par une autre transaction)

Richard Grin

SQL

page 284

Blocages implicites
Les commandes INSERT, UPDATE et DELETE effectuent implicitement n un blocage exclusif des lignes modifies n un blocage partag sur la table ; ce blocage empche un blocage de la table par une autre transaction, qui interdirait la modification des donnes

Lecture consistante pendant une transaction

Si on veut travailler avec des valeurs inchanges pendant toute la dure de la transaction, on peut dmarrer une transaction par : SET TRANSACTION READ ONLY On ne peut faire aucune modification pendant cette transaction ; les autres transactions peuvent faire ce qu'elles veulent mais leurs modifications ne seront pas visibles de la transaction qui a lanc cette commande
Risque d'erreur si la transaction est trop longue et que les modifications des autres transactions sont trop nombreuses

Richard Grin

SQL

page 285

Richard Grin

SQL

page 286

Problmes lis aux modifications concurrentes

Situation typique : on souhaite
1. lire une donne D 2. utiliser la valeur lue pour calculer une nouvelle

2 types de solutions
Traitement pessimiste Traitement optimiste

valeur
3. affecter D la nouvelle valeur calcule

Risque de modifications perdues si dautres transactions ont modifi D entre ltape 1 et ltape 3
Richard Grin SQL page 287 Richard Grin SQL page 288

48

Traitement pessimiste
Pour viter les problmes lis aux accs concurrents, le comportement le plus simple est pessimiste : 1. D est bloqu pour empcher sa modification par dautres transactions 2. calcul de la nouvelle valeur 3. affectation D de la nouvelle valeur 4. dblocage de D la fin de la transaction

Inconvnients des blocages pessimistes

Si lutilisateur omet de valider rapidement la transaction, les blocages vont nuire aux autres transactions En rgle gnrale, il faut essayer dviter le blocage pessimiste si un traitement interactif (donc long) doit tre plac entre le dbut et la fin dune transaction Des inter-blocages peuvent survenir avec dautres transactions Cot dun blocage pour les performances
Richard Grin SQL page 290

Richard Grin

SQL

page 289

Traitement optimiste
1. Lecture de D sans le bloquer, en esprant que dautres transactions ne le modifieront pas 2. Calcul de la nouvelle valeur 3. Blocage court pendant lequel on vrifie si loptimisme tait justifi : D a t-il t modifi entretemps par dautres transactions ? si a nest pas le cas, on valide la transaction 4. sinon, on agit en consquence ; par exemple en recommenant le traitement, ou en lannulant
Richard Grin SQL page 291

Vrification juste avant la validation (1)

On peut comparer la valeur actuelle de D avec la valeur lue au dbut Mais si D est de grande taille, a donne des mauvaises performances

Richard Grin

SQL

page 292

Vrification juste avant la validation (2)

On peut ajouter une information pour chaque ligne : n soit un timestamp qui indique le moment exact de la dernire modification de la ligne (peu fiable, surtout sur les bases distribues) n soit un numro de version de ligne qui est incrment chaque modification de la ligne

Implmentation
La comparaison peut se faire avec des if du langage hte Souvent, on peut aussi utiliser ce type de update au moment o on effectue la modification ( la fin) : n = update emp set salaire = nouveauSalaire where matr = 8000 and salaire = ancienSalaire Si on rcupre n = 0, loptimisme ntait pas justifi

Richard Grin

SQL

page 293

Richard Grin

SQL

page 294

49

Avantages du traitement optimiste

Plus de blocages longs Plus dinter-blocages Moins coteux en ressources Permet un granularit de blocage fine (au niveau dun attribut et pas dune ligne) en dehors du blocage final Dans certains cas, permet un traitement substitutif si loptimisme ntait pas justifi

Inconvnients du blocage optimiste

Sil y a beaucoup de collisions entre transactions, les annulations de traitements sont nombreux Ne convient pas si les annulations sont coteuses (trop de modifications annuler) Plus complexe mettre en place que le blocage pessimiste

Richard Grin

SQL

page 295

Richard Grin

SQL

page 296

Traitement optimiste ou pessimiste ?

Le choix dpend du contexte Pour les transactions qui comportent une intervention de lutilisateur entre la lecture et la modification des donnes, il faut privilgier le traitement optimiste car le blocage pessimiste va bloquer des donnes pour un trop long moment Sil y a trop de risques de collisions entre transactions, on prfrera souvent le blocage pessimiste pour viter trop de calculs inutiles
Richard Grin SQL page 297

Pratique des blocages pessimistes

Avec Oracle un select simple ne bloque pas les donnes Si on veut effectuer des blocages pessimistes, on doit lire les donnes avec un select for update On peut aussi plus rarement effectuer des blocages de table pour les tables dont on veut bloquer un grand nombre de lignes

Richard Grin

SQL

page 298

Rponses quelques questions

A quel moment les modifications sont-elles vues par les autres transactions ? aprs la validation de la transaction (COMMIT) Que se passe-t-il lorsque plusieurs transactions veulent modifier les mmes donnes ? blocages implicites du SGBD attente

Rponses quelques questions

Quand un ordre SQL tient compte de plusieurs lignes (moyenne des salaires, par exemple), cet ordre tient-il compte des modifications apportes par les autres transactions pendant l'excution de l'ordre ? Ca dpend du SGBD. Avec Oracle, non

Richard Grin

SQL

page 299

Richard Grin

SQL

page 300

50