ORACLE
3 Les expressions du langage SQL
3.1 Introduction
Dans la plupart des commandes SQL, il est possible d'utiliser une expression. Prenons par exemple la commande SELECT dont le syntaxe est le suivant:
SELECT selectionne les lignes pour lesquelles "expression" est vraie et affiche pour chacune d'elles les valeurs de expri.
Dans la plupart des commandes SQL, il est possible d'utiliser une expression. Prenons par exemple la commande SELECT dont le syntaxe est le suivant:
SELECT expr1, expr2, ... from table WHERE expression
Exemples
select prix*1.186 from biblio select to_char(achat,'dd/mm/yy') from biblio select titre from biblio where prix between 100 and 150
opérande1 opérateur opérande2 ou fonction (paramètres)
Exemples
Dans l'expression GENRE = 'ROMAN' - GENRE est l'opérande1 - 'ROMAN' est l'opérande2 - = est l'opérateur Dans l'expression to_char(achat,'dd/mm/yy') - TO_CHAR est une fonction - achat et 'dd/mm/yy' sont les parametres de cette fonction.
3.2 Expressions
3.2.1 Expressions avec opérateur
Nous classifierons les expressions avec opérateur suivant le type de leurs opérandes :
Nous classifierons les expressions avec opérateur suivant le type de leurs opérandes :
. numerique . chaine de caracteres . date . booleen ou logique
3.2.1.1 Les expressions à opérandes de type numérique
Soient nombre1, nombre2, nombre3 des nombres. Les opérateurs utilisables sont les suivants :
opérateurs relationnels nombre1 > nombre2 : nombre1 plus grand que nombre2 nombre1 >= nombre2 : nombre1 plus grand ou egal a nombre2 Les expressions du langage SQL 35 nombre1 < nombre2 : nombre1 plus petit que nombre2 nombre1 <= nombre2 : nombre1 plus petit ou egal a nombre2 nombre1 = nombre2 : nombre1 egal a nombre2 nombre1 != nombre2 : nombre1 different de nombre2 nombre1 <> nombre2 : idem nombre1 BETWEEN nombre2 AND nombre3 : nombre1 dans l'intervalle [nombre2,nombre3] nombre1 IN (liste de nombres) : nombre1 appartient a liste de nombres nombre1 IS NULL : nombre1 n'a pas de valeur nombre1 IS NOT NULL : nombre1 a une valeur ANY,ALL,EXISTS opérateurs arithmetiques nombre1 + nombre2 : addition nombre1 - nombre2 : soustraction nombre1 * nombre2 : multiplication nombre1 / nombre2 : division
3.2.1.2 opérateurs relationnels
Une expression relationnelle exprime une relation qui est vraie ou fausse. Le resultat d'une telle expression est donc un booleen ou valeur logique.
Exemples
SQL> select titre,prix from biblio 2 where prix between 100 and 150; TITRE PRIX ------------------------------------------------------------------- Madame Bovary 136.5 SQL> select titre,prix from biblio 2 where prix not between 100 and 150; TITRE PRIX ------------------------------------------------------------------- Les fleurs du mal 60 Tintin au Tibet 70 La terre 52.5 Manhattan transfer 336 Tintin en Amerique 70 Du cote de ch. Swann 210 Le pere Goriot 200 7 rows selected. SQL> select titre,prix from biblio where prix in (200,210); TITRE PRIX -------------------------------------------------------------------- Du cote de ch. Swann 210 Le pere Goriot 200
3.2.1.3 opérateurs arithmetiques
L'expression arithmetique nous est familiere. Elle exprime un calcul a faire entre des données numeriques. Nous avons déjà rencontre de telles expressions : on suppose que le prix memorise dans les fiches du fichier BIBLIO soit un prix hors taxes. On veut visualiser chaque titre avec son prix TTC pour un taux de TVA de 18.6% :
Lorsque deux opérateurs de mêmepriorite sont presents dans l'expression, c'est celui qui est le plus a gauche dans l'expression qui est evalue tout d'abord.
SELECT TITRE, PRIX*1.186 FROM BIBLIO
Si les prix doivent augmenter de 3%, la commande sera
UPDATE BIBLIO SET PRIX = PRIX*1.03
On peut trouver plusieurs opérateurs arithmetiques dans une expression avec de plus des fonctions et des parentheses. Ces éléments sont traites selon des priorites differentes :
1 fonctions <---- plus prioritaire 2 () 3 * et / 4 + et - <---- moins prioritaire
Exemple
PRIX*TAUX+TAXES
sera evaluee comme (PRIX*TAUX)+TAXES. C'est en effet l'opérateur de multiplication qui sera utilise en premier.
PRIX*TAUX/100
sera evaluee comme (PRIX*TAUX)/100.3.2.2 Les expressions a opérandes de type caracteres
3.2.2.1 Liste des opérateurs
Les opérateurs utilisables sont les suivants :
Les opérateurs utilisables sont les suivants :
opérateurs relationnels Soient chaine1, chaine2, chaine3, modele des chaines de caracteres chaine1 > chaine2 : chaine1 plus grande que chaine2 chaine1 >= chaine2 : chaine1 plus grande ou egale a chaine2 chaine1 < chaine2 : chaine1 plus petite que chaine2 chaine1 <= chaine2 : chaine1 plus petite ou egale a chaine2 chaine1 = chaine2 : chaine1 egale a chaine2 chaine1 != chaine2 : chaine1 differente de chaine2 chaine1 <> chaine2 : idem chaine1 BETWEEN chaine2 AND chaine3 : chaine1 dans l'intervalle [chaine2,chaine3] chaine1 IN liste de chaines : chaine1 appartient a liste de chaines chaine1 IS NULL : chaine1 n'a pas de valeur chaine1 IS NOT NULL : chaine1 a une valeur chaine1 LIKE modele : chaine1 correspond a modele ANY,ALL,EXISTS opérateurs de concatenation chaine1 || chaine2 : chaine2 concatenee a chaine1
3.2.2.2 opérateurs relationnels
Que signifie comparer des chaines avec des opérateurs tels que <, <=, etc ... ?
Tout caractere est code sur un octet par un nombre compris entre 0 et 255. On appelle ce codage, le code ASCII des caracteres. On trouvera ces codes ASCII en annexe. Ce codage permet la comparaison entre deux caracteres. Ce sont en effet les codes ASCII (des nombres donc) des caracteres qui sont compares. Ainsi on dira que le caractere C1 est plus petit que le caractere C2 si le code ASCII de C1 est plus petit que le code ASCII de C2. A partir de la table des codes ASCII, on a les relations suivantes :
blanc<..< 0 < 1 < ...< 9 < ...< A <B <... < Z < ... < a < b < ... < z Dans l'ordre ASCII, les chiffres viennent avant les lettres, et les majuscules avant les minuscules. On remarquera que l'ordre ASCII respecte l'ordre des chiffres ainsi que l'ordre alphabetique auquel on est habitue.
Tout caractere est code sur un octet par un nombre compris entre 0 et 255. On appelle ce codage, le code ASCII des caracteres. On trouvera ces codes ASCII en annexe. Ce codage permet la comparaison entre deux caracteres. Ce sont en effet les codes ASCII (des nombres donc) des caracteres qui sont compares. Ainsi on dira que le caractere C1 est plus petit que le caractere C2 si le code ASCII de C1 est plus petit que le code ASCII de C2. A partir de la table des codes ASCII, on a les relations suivantes :
blanc<..< 0 < 1 < ...< 9 < ...< A <B <... < Z < ... < a < b < ... < z Dans l'ordre ASCII, les chiffres viennent avant les lettres, et les majuscules avant les minuscules. On remarquera que l'ordre ASCII respecte l'ordre des chiffres ainsi que l'ordre alphabetique auquel on est habitue.
3.2.2.3 Comparaison de deux chaines
Soit la relation 'CHAT' < 'CHIEN'. Est-elle vraie ou fausse ?
Pour effectuer cette comparaison, ORACLE compare les deux chaines caractere par caractere sur la base de leurs codes ASCII. Des que deux caracteres sont trouves differents, la chaine a qui appartient le plus petit des deux est dite plus petite que l'autre chaine. Dans notre exemple 'CHAT' est comparee a 'CHIEN'. On a les resultats successifs suivants :
Apres cette comparaison, la relation 'CHAT' < 'chat' est declaree vraie.
Pour effectuer cette comparaison, ORACLE compare les deux chaines caractere par caractere sur la base de leurs codes ASCII. Des que deux caracteres sont trouves differents, la chaine a qui appartient le plus petit des deux est dite plus petite que l'autre chaine. Dans notre exemple 'CHAT' est comparee a 'CHIEN'. On a les resultats successifs suivants :
'CHAT' 'CHIEN' --------------------------------------------------------------------- 'C' = 'C' 'H' = 'H' 'A' < 'I' Apres cette derniere comparaison, la chaine 'CHAT' est declaree plus petite que la chaine 'CHIEN' dans l'ordre ASCII. La relation 'CHAT' < 'CHIEN' est donc vraie. Soit a comparer maintenant 'CHAT' et 'chat'. 'CHAT' 'chat' --------------------------------------------------------------------- 'C' < 'c'
Exemples
SQL> select titre from biblio; TITRE ------------------------------------------------------------------ Les fleurs du mal Tintin au Tibet La terre Madame Bovary Manhattan transfer Tintin en Amerique Du cote de ch. Swann Le pere Goriot 8 rows selected. SQL> select titre from biblio where upper(titre) between 'L' and 'U'; TITRE ------------------------------------------------------------------------------ Les fleurs du mal Tintin au Tibet La terre Madame Bovary Manhattan transfer Les expressions du langage SQL 38 Tintin en Amerique Le pere Goriot 7 rows selected.
3.2.2.4 L'opérateur LIKE
L'opérateur LIKE s'utilise comme suit :
chaine LIKE modele
La relation est vraie si chaine correspond au modele. Celui-ci est une chaine de caracteres pouvant comporter deux caracteres generiques :
le symbole "%" qui designe toute suite de caracteres le symbole "_" qui designe 1 caractere quelconque
Exemples
SQL> select titre from biblio; TITRE ------------------------------------------------------------------ Les fleurs du mal Tintin au Tibet La terre Madame Bovary Manhattan transfer Tintin en Amerique Du cote de ch. Swann Le pere Goriot 8 rows selected. SQL> select titre from biblio where titre like 'M%'; TITRE ------------------------------------------------------------------ Madame Bovary Manhattan transfer
SQL> select titre from biblio where titre like 'L_ %'; TITRE ------------------------------------------------------------------ La terre Le pere Goriot
3.2.2.5 L'opérateur de concatenation
SQL> select 'chat'||'eau'
Exemple from dual; <-- concatenation de 'chat' avec 'eau'
Exemples
-------------------------------------------------------------------- chateau <-- résultat de la concaténation
3.2.3 Les expressions a opérandes de type date
Soient date1, date2, date3 des dates. Les opérateurs utilisables sont les suivants : opérateurs relationnels date1 < date2 est vraie si date1 est anterieure a date2 date1 <= date2 est vraie si date1 est anterieure ou egale a date2 date1> date2 est vraie si date1 est posterieure a date2 Les expressions du langage SQL 39 date1>= date2 est vraie si date1 est posterieure ou egale a date2 date1 = date2 est vraie si date1 et date2 sont identiques date1 <> date2 est vraie si date1 et date2 sont differentes. date1 != date2 idem date1 BETWEEN date2 AND date3 est vraie si date1 est situe entre date2 et date3 date1 IN (liste de dates) est vraie si date1 se trouve dans la liste de dates date1 IS NULL est vraie si date1 n'a pas de valeur date1 IS NOT NULL est vraie si date1 a une valeur date1 LIKE modele est vraie si date1 correspond au modele ALL,ANY,EXISTS opérateurs arithmetiques date1 - date2 : nombre de jours séparant date1 de date2 date1 - nombre : date2 telle que date1-date2=nombre date1 + nombre : date2 telle que date2-date1=nombre
Exemples
SQL> select achat from biblio; ACHAT --------- 01-JAN-78 10-NOV-90 12-JUN-90 12-MAR-88 30-AUG-87 15-MAY-91 08-DEC-78 01-SEP-91 8 rows selected. SQL> select achat from biblio where achat between '01-jan-88' and '31-dec-88'; ACHAT --------- 12-MAR-88 SQL> select achat from biblio where achat like '__-MA%'; ACHAT --------- 12-MAR-88 15-MAY-91 SQL> select sysdate from dual;<-- date & heure du moment SYSDATE --------- 18-OCT-91 SQL> select sysdate-achat age from biblio; <-- age des livres ? AGE ---------- 5038.63328 <-- les chiffres derrière le point correspondent a la composante heure de la date 342.633275 493.633275 1315.63328 1510.63328 156.633275 4697.63328 47.6332755 8 rows selected. SQL> select trunc(sysdate-achat) age from biblio; <-- trunc pour enlever la composante heure AGE ---------- 5038 342 493 1315 1510 156 4697 47 8 rows selected.
3.2.4 Expressions à opérandes booleennes
Rappelons qu'un booléen ou valeur logique à deux valeurs possibles : vrai ou faux. Sous Oracle, l'opérande logique est typiquement le résultat d'une expression relationnelle.
Soient booleen1 et booleen2 deux booleens. Il y a trois opérateurs possibles qui sont par ordre de priorité : booleen1 AND booleen2 est vraie si booleen1 et booleen2 sont vrais tous les deux. booleen1 OR booleen2 est vraie si booleen1 ou booleen2 est vrai. NOT booleen1 a pour valeur l'inverse de la valeur de booleen1.
Soient booleen1 et booleen2 deux booleens. Il y a trois opérateurs possibles qui sont par ordre de priorité : booleen1 AND booleen2 est vraie si booleen1 et booleen2 sont vrais tous les deux. booleen1 OR booleen2 est vraie si booleen1 ou booleen2 est vrai. NOT booleen1 a pour valeur l'inverse de la valeur de booleen1.
Exemples
SQL> select titre,genre,prix from biblio; TITRE GENRE PRIX -------------------- --------------- ---------- Les fleurs du mal POEME 60 Tintin au Tibet BD 70 La terre ROMAN 52.5 Madame Bovary ROMAN 136.5 Manhattan transfer ROMAN 336 Tintin en Amerique BD 70 Du cote de ch. Swann ROMAN 210 Le pere Goriot ROMAN 200 8 rows selected. SQL> select titre,genre,prix from biblio where prix>=50 and prix <=100; TITRE GENRE PRIX -------------------- ----------- ------ Les fleurs du mal POEME 60 Tintin au Tibet BD 70 La terre ROMAN 52.5 Tintin en Amerique BD 70 SQL> select titre,genre,prix from biblio 2 where prix <50 or prix >100; TITRE GENRE PRIX -------------------- --------------- ---------- Madame Bovary ROMAN 136.5 Manhattan transfer ROMAN 336 Du cote de ch. Swann ROMAN 210 Le pere Goriot ROMAN 200 SQL> select titre,genre,prix from biblio 2 where genre='ROMAN' and prix>200 or prix<100; <-- attention a la priorite des opérateurs TITRE GENRE PRIX -------------------- --------------- ---------- Les fleurs du mal POEME 60 Tintin au Tibet BD 70 La terre ROMAN 52.5 Manhattan transfer ROMAN 336 Les expressions du langage SQL 41 Tintin en Amerique BD 70 Du cote de ch. Swann ROMAN 210 6 rows selected. SQL> select titre,genre,prix from biblio 2* where genre='ROMAN' and (prix>200 or prix<100) <-- on met des parentheses TITRE GENRE PRIX -------------------- --------------- ---------- La terre ROMAN 52.5 Manhattan transfer ROMAN 336 Du cote de ch. Swann ROMAN 210
3.3 Les fonctions prédéfinies d'ORACLE
Une expression peut comporter l'appel a des fonctions prédéfinies d'Oracle. Nous presentons ici les plus courantes. Nous les classifions selon le type predominant de leurs parametres ou selon leur rôle :
Dans la syntaxe SELECT fonction FROM table
la fonction est evaluee le plus souvent pour chaque ligne de la table. On a donc affichage de n resultats pour n lignes. Certaines fonctions cependant, sont evaluees a partir des données de toutes les lignes de la table et ne donnent lieu qu'a un seul resultat. On les appellera des fonctions de groupe. On les connait pour la plupart : ce sont les fonctions statistiques COUNT, SUM, AVG, STDDEV, VARIANCE, MAX, MIN.
Le lecteur pourra tester les fonctions presentees ci-apres par la commande :
SELECT fonction FROM DUAL La table DUAL comme il a été déjà explique est une table a une ligne et une colonne qui contient l'unique donnee 'X'.
. fonctions a parametres de type numerique . fonctions a parametres de type chaine de caracteres . fonctions a parametres de type date . fonctions de conversion de type . fonctions diverses
la fonction est evaluee le plus souvent pour chaque ligne de la table. On a donc affichage de n resultats pour n lignes. Certaines fonctions cependant, sont evaluees a partir des données de toutes les lignes de la table et ne donnent lieu qu'a un seul resultat. On les appellera des fonctions de groupe. On les connait pour la plupart : ce sont les fonctions statistiques COUNT, SUM, AVG, STDDEV, VARIANCE, MAX, MIN.
Le lecteur pourra tester les fonctions presentees ci-apres par la commande :
SELECT fonction FROM DUAL La table DUAL comme il a été déjà explique est une table a une ligne et une colonne qui contient l'unique donnee 'X'.
SQL> describe dual; Name Null? Type ----------------- -------- ---- DUMMY CHAR(1) <-- une seule colonne de type caractere SQL> select * from dual; <-- contenu de la table DUAL D-X <-- une seule ligne La commande SELECT fonction FROM DUAL evalue fonction pour chaque ligne de la table DUAL, donc en fait pour une seule ligne. Il y a donc simplement affichage de la valeur de fonction.
3.3.1 Fonctions a parametres de type numerique
abs(nombre) valeur absolue de nombre abs(-15)=15 ceil(nombre) plus petit entier plus grand ou egal a nombre ceil(15.7)=16 Les expressions du langage SQL 42 floor(nombre) plus grand entier inferieur ou egal a nombre floor(14.3)=14 mod(nombre1,nombre2) reste de la division entiere (le quotient est entier) de nombre1 par nombre2 mod(7,3)=1 mod(7.5,3.2)=1.1 power(nombre1,nombre2) nombre1 eleve a la puissance nombre2 qui doitêtre entier power(4,2)=16 round(nombre1,nombre2) arrondit nombre1 a nombre2 chiffres apres la virgule si nombre2>0, a -nombre2 chiffres avant la virgule si nombre2<0. round(13.456,2)=13.46 round(13.456,-1)=10 round(16.456,-1)=20 round(16.456,0)=16 sign(nombre) -1 si nombre<0 0 si nombre=0 +1 si nombre>0 sign(-6)=-1 sqrt(nombre) racine carree de nombre si nombre>=0 NULL si nombre<0 sqrt(16)=4 trunc(nombre1,nombre2) nombre1 est tronque a nombre2 chiffres apres la virgule si nombre2>0 ou a -nombre2 chiffres avant la virgule si nombre2<0. trunc(13.456,2)=13.45 trunc(13.456,-1)=10 trunc(16.456,-1)=10 trunc(16.456,0)=16
3.3.2 Fonctions a parametres de type chaine de caracteres
chr(nombre) caractere de code ASCII nombre
chr(65)='A'
initcap(chaine) Met tous les mots de chaine en minuscules
sauf la premiere lettre mise en majuscule
initcap ('gestion des stocks')='Gestion Des Stocks'
lower(chaine) met chaine en minuscules
lower('INFO')='info'
lpad(chaine1,n,chaine2) met chaine1 sur n positions, chaine1
etant cadree a droite. Les caracteres restant a gauche sont remplis par
chaine2.
lpad('chat',6,'*')='**chat'
lpad('chat,8,'xy')='xyxychat'
ltrim(chaine1,chaine2) Les caracteres de gauche
de chaine1 sont supprimes jusqu'a rencontrer un caractere qui ne se trouve pas
dans chaine2.
ltrim('chaton','ch')='aton'
ltrim('chaton','hc')='aton'
ltrim('chaton','abc')='haton'
replace(chaine1,chaine2,chaine3) remplace chaine2 par chaine3 dans chaine1.
replace('chat et chien','ch','**')='**at et **ien'
rpad(chaine1,n,chaine2) idem lpad mais a droite
rpad('chat',6,'*')='chat**'
rpad('chat,8,'xy')='chatxyxy'
trim(chaine1,chaine2) idem ltrim mais a droite
rtrim('chat','at')='ch'
rtrim('chat','ta')='ch'
Les expressions du langage SQL 43
substr(chaine,p,nombre) sous-chaine de chaine
de nombre caracteres commencant en position p.
substr('chaton',3,2)='at'
translate(chaine,texte,traduction) remplace dans chaine
tout caractere se trouvant dans texte par le caractere correspondant se trouvant dans
traduction
translate('abracadabra,'ab','yz')='yzrycydyzry'
ascii(caractere) code ASCII de caractere
ascii('A')=65
instr(chaine1,chaine2,p,o) position de la o ieme occurrence
de chaine2 dans chaine1, la recherche commencant a la position p de
chaine1.
instr('abracadabra','a',4,2)=6
instr('abracadabra','a',5,3)=11
length(chaine) nombre de caracteres de chaine
length('chaton')=6
3.3.3 Fonctions de conversion
to_char(nombre,format) transforme nombre en chaine de caracteres selon le format indique.
Les formats utilisables sont ceux déjà presentes.
to_char(1000,'99999.99')=' 1000.00'
to_char(1000,'99EEEE')=' 1E+03'
to_char(date,format) convertit date en chaine de caracteres
selon le format indique. Cette fonction a été déjà presentee.
to_date(chaine,format) transforme chaine en date.
Le format decrit la chaine. Les differents formats utilisables ont déjà été presentes.
SQL> select to_date ('01/02/91' , 'dd/mm/yy') from dual;
TO_DATE('
-----------------------------------------------------------------------------------
01-FEB-91
SQL> select to_date('01 january 91','dd month yy') from dual;
TO_DATE('
-----------------------------------------------------------------------------------
01-JAN-91
to_number(chaine) transforme chaine en nombre.
Il faut pour cela que chaine represente un nombre valide.
to_number('1987')=1987
3.3.4 Fonctions de parametres de type date
addate(date,n) date augmentee de n mois. Le resultat est une date.
date('01-jan-91',3)='01-apr-91'
last_day(date) date du dernier jour du mois contenu dans date
last_day('01-jan-91')='31-jan-91'
months_between(date1,date2) nombre de mois entre date1 et date2.
La partie decimale represente le pourcentage d'un mois de 31 jours.
Si date1<date2 le resultat est >0 sinon il est <0.
month_between('01-jan-91','14-feb-91')=-1.4193548
next_day(date,jour) donne la date du jour indique dans la semaine qui suit date.
next_day('01-jan-91','monday')='07-jan-91'
sysdate date du jour maintenue par le système
Les expressions du langage SQL 44
round(date,format) date arrondie selon le format precise
Formats
year : arrondit au 1er janvier le plus proche
month : arrondit au 1er du mois le plus proche
day : arrondit au dimanche le plus proche
Exemples
select sysdate from dual; SYSDATE --------- 21-OCT-91 SQL> select round(sysdate,'year') from dual; ROUND(SYS --------- 01-JAN-92 SQL> select round(sysdate,'month') from dual; ROUND(SYS --------- 01-NOV-91 SQL> select round(sysdate,'day') from dual; ROUND(SYS --------- 20-OCT-91 trunc(date,[format]) tronque date selon le format indique. Par defaut, c'est la composante heure qui est supprimee. Formats year : tronque au 1er janvier de l'annee de date month : tronque au 1er du mois de date day : tronque au dimanche qui precede date.
Exemples
SQL> select sysdate from dual; SYSDATE --------- 21-OCT-91 SQL> select trunc(sysdate,'year') from dual; TRUNC(SYS --------- 01-JAN-91 SQL> select trunc(sysdate,'month') from dual TRUNC(SYS --------- 01-OCT-91 SQL> select trunc(sysdate,'day') from dual TRUNC(SYS --------- 20-OCT-91
3.3.5 Fonctions a parametres de type variable
greatest(expr1, expr2, exprn)
donne la plus "grande" des valeurs expr1, expr2, ..., exprn. Le type de expri peutêtre numerique, caracteres ou date.
least(expr1, expr2, exprn)
donne la plus "petite" des valeurs expr1, expr2, ..., exprn. Le type de expri peutêtre numerique, caracteres ou date.Exemples
SQL> select greatest(100,200,300) grand, least(100,200,300) petit from dual
GRAND PETIT
--------------------
300 100
SQL> select greatest('chat','chien','veau') grand, least('chat','chien','veau')
petit from dual;
GRAND PETIT
-------------------
veau chat
SQL> select greatest('01-jan-91','10-feb-91','15-sep-87') grand, least
('01-jan-91', '10-feb-91', '15-sep-87') petit from dual;
GRAND PETIT
-------------------
15-sep-87 01-jan-91
SQL> select greatest(to_date('01-jan-91'), to_date('10-feb-91'),to_date('15-sep-87')) grand,
least(to_date('01-jan-91'), to_date('10-feb-91'),to_date('15-sep-87')) petit from dual
GRAND PETIT
------------------
10-FEB-91 15-SEP-87
3.3.6 Fonctions diverses
user nom de connexion Oracle de l'utilisateur uid n¢X identifiant chaque utilisateur Oracle userenv(option) options 'terminal' : identificateur du terminal de l'utilisateur 'language' : identificateur de la langue utilisee par Oracle
Exemples
SQL> select user,uid,userenv('terminal'),userenv('language') from dual;
USER UID USERENV( USERENV('LANGUAGE')
---------------------------------------------------
omara 9 ttyc3d13 AMERICAN_AMERICA.US7ASCII
4 Approfondissement du langage SQL
4.1 Introduction
Dans ce chapitre nous voyons
.d'autres syntaxes de la commande SELECT qui en font une commande de consultation tres puissante notamment pour consulter plusieurs tables a la fois.
. des syntaxes elargies de commandes déjà etudiees
. les commandes de gestion de la securite des données.
. Les commandes de gestion des performances des Requêtes
Pour illustrer les diverses commandes etudiees, nous travaillerons avec les tables suivantes utilisees pour la gestion des commandes dans une PME de diffusion de livres :
la table CLIENTS :
Elle memorise des informations sur les clients de la PME. Sa structure est la suivante :
Dans ce chapitre nous voyons
.d'autres syntaxes de la commande SELECT qui en font une commande de consultation tres puissante notamment pour consulter plusieurs tables a la fois.
. des syntaxes elargies de commandes déjà etudiees
. les commandes de gestion de la securite des données.
. Les commandes de gestion des performances des Requêtes
Pour illustrer les diverses commandes etudiees, nous travaillerons avec les tables suivantes utilisees pour la gestion des commandes dans une PME de diffusion de livres :
la table CLIENTS :
Elle memorise des informations sur les clients de la PME. Sa structure est la suivante :
NOM - char(30) : nom du client STATUT - char(1) : I=Individu, E=Entreprise, A=Administration PRENOM - char(20) : prenom dans le cas d'un individu CONTACT - char(30) : Nom de la personne a contacter chez le client (dans le cas d'une entreprise ou d'une administration) RUE - char(25) : Adresse du client - rue VILLE - char(20) : ville CPOSTAL - char(5) : code postal TELEPH - char(20) : Telephone DEPUIS - date : Client depuis quelle date ? IDCLI - char(6) : n¢X identifiant le client de facon unique DEBITEUR - char(1) : O (Oui) si le client doit de l'argent a l'entreprise et N (Non) sinon.
Exemple
NOM : LIBRAIRIE LA NACELLE STATUT : E PRENOM : CONTACT : Catherine Duchemin RUE : 115,Bd du Montparnasse VILLE : PARIS CPOSTAL : 75014 TELEPH : 16-1-45-56-67-78 DEPUIS : 06-APR-81 IDCLI : 001006 Conclusion 47 DEBITEUR : N la table STOCKS
ISBN - char(13) : nº identifiant un livre de facon unique (ISBN= International Standard Book Number)
TITRE - char(30) : Titre du livre CODEDITEUR - char(7) : Code identifiant un editeur de facon unique AUTEUR - char(30) : nom de l'auteur RESUME - char(400) : resume du livre QTEANCOUR - number(4) : Quantite vendue dans l'annee QTEANPREC - number(4) : Quantite vendue l'annee precedente DERNVENTE - date : date de la derniere vente QTERECUE - number(3) : Quantite de la derniere livraison DERNLIV - date : Date de la derniere livraison PRIXVENTE - number(6,2) : Prix de vente COUT - number(6,2) : Cout d'achat MINCDE - number(3) : Quantite minimale a commander MINSTOCK - number(3) : Seuil minimal du stock QTESTOCK - number(3) : Quantite en stock
Exemple
ISBN : 0-913577-03-1 TITRE : La gestion avec DBASE III Plus CODEDITEUR : 104 AUTEUR : BYERS RESUME : QTEANCOUR : 32 QTEANPREC : 187 DERNVENTE : 08-AUG-89 QTERECUE : 40 DERNLIV : 07-JUL-89 PRIXVENTE : 350 COUT : 280 MINCDE : 20 MINSTOCK : 10 QTESTOCK : 32 la table COMMANDES
NOCMD - number(6) : Nº identifiant une commande de facon unique IDCLI - char(6) : Nº du client faisant cette commande ( cf fichier CLIENTS) DATE_CMD - date : Date de saisie de cette commande ANNULE - char(1) : O (Oui) si la commande a été annulee et N (Non) sinon.
Exemple
NOCMD : 100204 IDCLI : 001006 DATE : 15-AUG-89 ANNULE : N
Elle contient les details d'une commande, c'est a dire les references et quantites des livres commandes. Sa structure est la suivante :
NOCMD - number(6) : Nº de la commande (cf fichier COMMANDES) ISBN - char(13) : nº du livre commande ( cf fichier STOCKS) QTE - number(3) : Quantite commandee
Exemple :
pour la commande 100204 presentee ci-dessus, qui referencerait deux livres :
NOCMD ISBN QTE 100204 0-912677-45-7 100 100204 0-912677-16-3 50 Leur contenu est le suivant : SQL> select nom,statut,ville,cpostal,depuis,idcli,debiteur from clients; NOM S VILLE CPOST DEPUIS IDCLI D LIBRAIRIE LA COMété E ANGERS 49000 01-MAR-88 000001 N LIBRAIRIE DU MARCHE E ANGERS 49100 01-APR-89 000002 O TRESOR PUBLIC A ANGERS 49000 01-JAN-87 000003 N MAIRIE D'ANGERS A SAUMUR 49700 01-FEB-78 000004 O TELELOGOS E SEGRE 49500 01-OCT-77 000005 N BARNARD I CHOLET 49800 01-SEP-77 000006 N PREFECTURE DU M & L A ANGERS 49000 10-DEC-66 000007 O ARTUS E AVRILLE 49350 14-JUN-88 000008 N MECAFLUID E SAUMUR 49550 23-JUL-90 000009 N PLUCHOT I SEGRE 49100 21-FEB-89 000010 O 10 rows selected. SQL> select isbn,titre,qteancour,qteanprec,dernvente,qterecue 2 prixvente,cout,mincde,minstock,qtestock from stocks; ISBN TITRE QTEAN COUR QTEAN PREC DERNV ENTE QTERE CUE PRIXV ENTE CO UT MIN CDE MINST OCK QTEST OCK 0-07-881551-7 DVORAK'S GUIDE TOO TELECOM 100 500 14- FEB- 90 50 250 20 0 50 20 100 0-07-881309-3 USING 1-2-3 RELEASE 3 1000 5000 01- AUG- 91 100 200 18 0 100 30 430 0-07-881524-X USING SQL 800 1000 06- SEP- 91 40 320 25 0 50 30 210 0-07-881497-9 DOS - THE COMPLété REFERENCE 670 8000 14- SEP- 91 500 340 27 0 100 100 780 0-07-881520-7 USING QUICK PASCAL 150 600 14- SEP- 91 80 280 23 0 40 40 200 0-07-881537-1 USING WINDOWS 3 45 0 18- SEP- 91 50 450 40 0 30 15 67 6 rows selected. SQL> select * from commandes; NOCMD IDCLI DATE_CMD A 1 000003 21-SEP-91 N 2 000002 21-SEP-91 N 3 000005 23-SEP-91 N 4 000006 24-SEP-91 N 5 000002 25-SEP-91 N 7 000004 25-SEP-91 N 9 000005 26-SEP-91 N 10 000003 26-SEP-91 N SQL> select * from articles; NOCMD ISBN QTE 1 0-07-881551-7 2 1 0-07-881524-X 1 2 0-07-881537-1 4 3 0-07-881520-7 1 3 0-07-881551-7 1 3 0-07-881497-9 4 4 0-07-881551-7 2 4 0-07-881309-3 4 5 0-07-881309-3 1 7 0-07-881551-7 1 9 0-07-881524-X 3 10 0-07-881497-9 12 10 0-07-881520-7 2
4.2 La commande SELECT
Nous nous proposons ici d'approfondir notre connaissance de la commande SELECT en presentant de nouvelles syntaxes. 4.2.1 Requête multi-tables
4.2.2 La jointure entre deux tables
Soient deux tables table1 et table2. table1 a les colonnes col1 et col2 et table2 les colonnes cola, colb. Supposons que le contenu des tables soit le suivant :
Cette Requête extrait des données de deux tables : table1 et table2 avec une condition entre deux colonnes de tables differentes. On appelle ce type de Requête, une jointure. Comment fonctionne-t-elle ?
Une nouvelle table est construite avec pour colonnes, l'ensemble des colonnes des deux tables et pour lignes le produit cartesien des deux tables :
La condition WHERE col2=colb est appliquee a cette nouvelle table. On obtient donc la nouvelle table suivante :
4.2.2 La jointure entre deux tables
Soient deux tables table1 et table2. table1 a les colonnes col1 et col2 et table2 les colonnes cola, colb. Supposons que le contenu des tables soit le suivant :
table1 col1 col2 x 3 y 4 table2 cola colb a 7 b 4 Soit la commande : SELECT col1, cola FROM table1, table2 WHERE table1.col2=table2.colb
Une nouvelle table est construite avec pour colonnes, l'ensemble des colonnes des deux tables et pour lignes le produit cartesien des deux tables :
col1 col2 cola colb x 3 a 7 x 3 b 4 y 4 a 7 y 4 b 4
col1 col2 cola colb y 4 b 4 Il y a ensuite affichage des colonnes demandees : col1 cola y b
4.2.2.1 Syntaxe d'une Requête multi-tables
syntaxe SELECT colonne1, colonne2, ... FROM table1, table2, ..., tablep WHERE condition ORDER BY ...
Fonctionnement
1 La table produit cartesien de table1, table2, ..., tablep est realisee. Si ni est le nombre de lignes de tablei, la table construite a donc n1*n2*...*np lignes comportant l'ensemble des colonnes des differentes tables.
2 La condition du WHERE est appliquee a cette table. Une nouvelle table est ainsi produite
3 Celle-ci est ordonnee selon le mode indique dans ORDER.
4 Les colonnes demandees derriere SELECT sont affichees.
Exemples
On utilise les tables presentees precedemment. On veut connaitre le detail des commandes passees apres le 25 septembre :
On remarquera que derriere FROM, on met le nom de toutes les tables dont on reference les colonnes. Dans l'exemple precedent, les colonnes selectionnees appartiennent toutes a la table articles. Cependant la condition fait reference a la table commandes. D'ou la necessite de nommer cette derniere derriere le FROM. L'operation qui teste l'egalite de colonnes de deux tables differentes est souvent appelee une equi-jointure.
Continuons nos exemples. On desire le mêmeresultat que precedemment mais avec le titre du livre commande plutot que son nº ISBN :
On veut de plus les dates de commande et un affichage par ordre decroissant de ces dates :
Voici quelques regles a observer dans ces jointures :
1 Derriere SELECT, on met les colonnes que l'on desire obtenir a l'affichage. Si la colonne existe dans diverses tables, on la fait preceder du nom de la table.
2 Derriere FROM, on met toutes les tables qui seront explorees par le SELECT, c'est a dire les tables proprietaires des colonnes qui se trouvent derriere SELECT et WHERE.
SQL> 1 select articles.nocmd,isbn,qte from commandes,articles 2 where date_cmd>'25-sep-91' 3 and articles.nocmd=commandes.nocmd; NOCMD ISBN QTE ---------- ------------- ---------- 9 0-07-881524-X 3 10 0-07-881497-9 12 10 0-07-881520-7 2
Continuons nos exemples. On desire le mêmeresultat que precedemment mais avec le titre du livre commande plutot que son nº ISBN :
SQL> 1 select commandes.nocmd, stocks.titre, qte 2 from commandes,articles,stocks 3 where date_cmd>'25-sep-91' 4 and articles.nocmd=commandes.nocmd 5 and articles.isbn=stocks.isbn NOCMD TITRE QTE -------------------------------------------------------------------------- 9 USING SQL 3 10 DOS - THE COMPLété REFERENCE 12 10 USING QUICK PASCAL 2 On veut de plus le nom du client qui fait la commande : SQL> 1 select commandes.nocmd, stocks.titre, qte ,clients.nom 2 from commandes,articles,stocks,clients 3 where date_cmd>'25-sep-91' 4 and articles.nocmd=commandes.nocmd 5 and articles.isbn=stocks.isbn 6 and commandes.idcli=clients.idcli; NOCMD TITRE QTE NOM ----------------------------------- ------ ------------------------------ 10 DOS - THE COMPLété REFERENCE 12 TRESOR PUBLIC 10 USING QUICK PASCAL 2 TRESOR PUBLIC 9 USING SQL 3 TELELOGOS
SQL> 1 select commandes.nocmd, date_cmd, stocks.titre, qte ,clients.nom 2 from commandes,articles,stocks,clients 3 where date_cmd>'25-sep-91' 4 and articles.nocmd=commandes.nocmd 5 and articles.isbn=stocks.isbn 6 and commandes.idcli=clients.idcli 7 order by date_cmd desc; NOCMD DATE_CMD TITRE QTE NOM ----------------------------------------------------------------------------- 10 26-SEP-91 DOS - THE COMPLété REFERENCE 12 TRESOR PUBLIC 10 26-SEP-91 USING QUICK PASCAL 2 TRESOR PUBLIC 9 26-SEP-91 USING SQL 3 TELELOGOS
1 Derriere SELECT, on met les colonnes que l'on desire obtenir a l'affichage. Si la colonne existe dans diverses tables, on la fait preceder du nom de la table.
2 Derriere FROM, on met toutes les tables qui seront explorees par le SELECT, c'est a dire les tables proprietaires des colonnes qui se trouvent derriere SELECT et WHERE.
On veut connaitre les livres qui ont un prix de vente superieur a celui du livre 'USING SQL' :
Les deux tables de la jointure sont ici identiques : la table stocks. Pour les differentier, on leur donne un alias : from stocks a, stocks b L'alias de la premiere table s'appelle a et celui de la seconde, b. Cette syntaxe peut s'utiliser mêmesi les tables sont differentes. Lors de l'utilisation d'un alias, celui-ci doitêtre utilise partout dans la commande SELECT en lieu et place de la table qu'il designe.
SQL> 1 select a.titre from stocks a, stocks b 2 where b.titre='USING SQL' 3 and a.prixvente>b.prixvente TITRE ------------------------------------------------------------------------ DOS - THE COMPLété REFERENCE USING WINDOWS 3
4.2.2.3 Jointure externe
Soit la jointure déjà étudiée :
Le produit cartesien des tables table1 et table2 est fait et le WHERE opere sur cette nouvelle table. Si pour une ligne de table1, on ne trouve aucune ligne de table2 telle que table2.colb=table1.col2, la ligne correspondante de table1 n'apparaitra pas dans le resultat de la jointure. Parfois, on desire cependant que la ligne sorte avec l'indication qu'elle n'a pas de correspondant dans table2. On appelle cela, une jointure externe et sa syntaxe est la suivante :
La syntaxe WHERE table1.col2=table2.colb(+) indique que les lignes de table1 sans correspondant dans table2, seront associees a des lignes vides de table2. De mêmela syntaxe
indique que les lignes de table2 sans correspondant dans table1, seront associees a des lignes vides de table1.
SELECT col1, cola FROM table1, table2 WHERE table1.col2=table2.colb
SELECT col1, cola FROM table1, table2 WHERE table1.col2=table2.colb (+)
SELECT col1, cola FROM table1, table2 WHERE table1.col2(+)=table2.colb
Exemples :
On veut connaitre les clients qui ont achété quelque chose en septembre avec la date de la commande. Les autres clients sont sortis sans cette date :
On est etonne ici de ne pas avoir le bon resultat. Lorsqu'on reflechit au fonctionnement de la jointure externe, on realise que les clients n'ayant pas achété ont été associees a une ligne vide de la table commandes et donc avec une date vide (valeur NULL dans la terminologie SQL). Cette date ne verifie pas alors la condition fixee sur la date et le client correspondant n'est pas affiche. Essayons autre chose :
On obtient cette fois-ci la reponse a notre question. Revenons sur la position du (+) dans la jointure. Que signifierait la commande :
Cette fois-ci ce sont les commandes qui n'auraient pas de correspondant (par idcli) dans la table clients qui seraient associees a des lignes vides de clients. Dans notre exemple, ce cas n'est pas possible : toute commande est faite par un client.
SQL> 1 select nom,date_cmd from clients,commandes 2 where date_cmd between '01-sep-91' and '30-sep-91' 3* and clients.idcli=commandes.idcli (+) NOM DATE_CMD ------------------------------------------------------------- LIBRAIRIE DU MARCHE 21-SEP-91 LIBRAIRIE DU MARCHE 25-SEP-91 TRESOR PUBLIC 21-SEP-91 TRESOR PUBLIC 26-SEP-91 MAIRIE D'ANGERS 25-SEP-91 TELELOGOS 23-SEP-91 TELELOGOS 26-SEP-91 BARNARD 24-SEP-91
SQL> 1 select nom,date_cmd from clients,commandes 2 where ( date_cmd between '01-sep-91' and '30-sep-91' 3 or date_cmd is null) 4* and clients.idcli=commandes.idcli (+) NOM DATE_CMD ------------------------------------------ LIBRAIRIE LA COMété LIBRAIRIE DU MARCHE 21-SEP-91 LIBRAIRIE DU MARCHE 25-SEP-91 TRESOR PUBLIC 21-SEP-91 TRESOR PUBLIC 26-SEP-91 MAIRIE D'ANGERS 25-SEP-91 TELELOGOS 23-SEP-91 TELELOGOS 26-SEP-91 BARNARD 24-SEP-91 PREFECTURE DU M & L ARTUS MECAFLUID PLUCHOT
SQL> 1 select nom,date_cmd from clients,commandes 2 where ( date_cmd between '01-sep-91' and '30-sep-91' 3 or date_cmd is null) 4 and clients.idcli (+)=commandes.idcli
syntaxe SELECT colonne[s] FROM table[s] WHERE expression opérateur Requête ORDER BY ...
expression opérateur (val1, val2, ..., vali)
expression et vali doiventêtre de mêmetype. Si la Requête delivre une seule valeur, on est ramene a une condition du typeexpression opérateur valeur que nous connaissons bien. Si la Requête delivre une liste de valeurs, on pourra employer les opérateurs suivants :
IN expression IN (val1, val2, ..., vali) vraie si expression a pour valeur l'un des éléments de la liste vali. NOT IN inverse de IN ANY doit être precede de =,!=,>,>=,<,<= expression >= ANY (val1, val2, .., valn) vraie si expression est >= a l'une des valeurs vali de la liste ALL doitêtre precede de =,!=,>,>=,<,<= expression >= ALL (val1, val2, .., valn) vraie si expression est >= a toutes les valeurs valide la liste EXISTS Requête vraie si la Requête rend au moins une ligne.
Exemples
On reprend la question déjà resolue par une equi-jointure : Afficher les titres ayant un prix de vente superieur a celui du livre 'USING SQL'.
Cette solution semble plus intuitive que celle de l'equi-jointure. On fait un premier filtrage avec un SELECT, puis un second sur le resultat obtenu. On peut operer ainsi plusieurs filtrages en serie. On veut connaitre les titres ayant un prix de vente superieur au prix moyen de vente :
SQL> 1 select titre from stocks 2 where prixvente > (select prixvente from stocks where titre='USING SQL') TITRE ------------------------------ DOS - THE COMPLété REFERENCE USING WINDOWS 3
SQL> 1 select titre from stocks 2 where prixvente > (select avg(prixvente) from stocks) TITRE ------------------------------ USING SQL DOS - THE COMPLété REFERENCE USING WINDOWS 3 Quels sont les clients ayant commande les titres resultat de la Requête precedente ? SQL> 1 select distinct idcli from commandes ,articles 2 where articles.isbn in (select isbn from stocks where prixvente 3 > (select avg(prixvente) from stocks)) 4 and commandes.nocmd=articles.nocmd IDCLI ------ 000002 000003 000005
Explications
a on selectionne dans la table articles, les codes ISBN se trouvant parmi les livres de prix superieur au prix moyen
b dans les lignes selectionnees de la table articles, il n'y a pas le code client IDCLI. Il se trouve dans la table commandes. Le lien entre les deux tables se fait par le n¢X de commande nocmd, d'ou l'equi-jointure commandes.nocmd = articles.nocmd.
c Un mêmeclient peut avoir achété plusieurs fois l'un des livres concernes, auquel cas on aura son code IDCLI plusieurs fois. Pour eviter cela, on met le mot cle DISTINCT derriere SELECT.
d pour avoir le nom du client, il nous faudrait faire une equi-jointure supplementaire entre les tables commandes et clients.
Trouver les clients qui n'ont pas fait de commande depuis le 24 septembre :
Nous avons vu qu'on pouvait filtrer des lignes autrement par la clause WHERE : en utilisant la clause HAVING en conjonction avec la clause GROUP BY. La clause HAVING filtre des groupes de lignes. De la mêmefacon que pour la clause WHERE, la syntaxe
HAVING expression opérateur Requête
est possible, avec la contrainte déjà presentee que expression doitêtre l'une des expressions expri de la clause GROUP BY expr1, expr2, ...
b dans les lignes selectionnees de la table articles, il n'y a pas le code client IDCLI. Il se trouve dans la table commandes. Le lien entre les deux tables se fait par le n¢X de commande nocmd, d'ou l'equi-jointure commandes.nocmd = articles.nocmd.
c Un mêmeclient peut avoir achété plusieurs fois l'un des livres concernes, auquel cas on aura son code IDCLI plusieurs fois. Pour eviter cela, on met le mot cle DISTINCT derriere SELECT.
d pour avoir le nom du client, il nous faudrait faire une equi-jointure supplementaire entre les tables commandes et clients.
Trouver les clients qui n'ont pas fait de commande depuis le 24 septembre :
SQL> 1 select nom from clients 2 where clients.idcli not in (select distinct commandes.idcli 3 from commandes where date_cmd>='24-sep-91') NOM ------------------------------ LIBRAIRIE LA COMété PREFECTURE DU M & L ARTUS MECAFLUID PLUCHOT
HAVING expression opérateur Requête
est possible, avec la contrainte déjà presentee que expression doitêtre l'une des expressions expri de la clause GROUP BY expr1, expr2, ...
Exemples
Quelles sont les quantites vendues pour les livres de plus de 100F ?
Sortons d'abord les quantites vendues par titre :
Sortons d'abord les quantites vendues par titre :
SQL> 1 select titre,sum(qte) from stocks, articles 2 where articles.isbn=stocks.isbn 3 group by titre TITRE SUM(QTE) --------------------- ---------- DOS - THE COMPLété REFERENCE 16 DVORAK'S GUIDE TOO TELECOM 6 USING 1-2-3 RELEASE 3 5 USING QUICK PASCAL 3 USING SQL 4 USING WINDOWS 3 4 Maintenant, filtrons les titres : SQL> 1 select titre,sum(qte) from stocks,commandes,articles 2 where articles.isbn=stocks.isbn 3 group by titre 4 having titre in (select titre from stocks where prixvente>200) TITRE SUM(QTE) --------------------- ---------- DOS - THE COMPLété REFERENCE 16 DVORAK'S GUIDE TOO TELECOM 6 USING QUICK PASCAL 3 USING SQL 4 USING WINDOWS 3 4 De facon peut-etre plus evidente on aurait pu ecrire : SQL> 1 select titre,sum(qte) from stocks,commandes,articles 2 where articles.nocmd=commandes.nocmd Conclusion 56 3 and articles.isbn=stocks.isbn 4 and prixvente>200 5* group by titre TITRE SUM(QTE) --------------------- ---------- DOS - THE COMPLété REFERENCE 16 DVORAK'S GUIDE TOO TELECOM 6 USING QUICK PASCAL 3 USING SQL 4 USING WINDOWS 3 4
4.2.4 Requêtes corrélées
Dans le cas des Requêtes imbriquees, on a une Requête parent (la Requête la plus externe) et une Requête fille (la Requête la plus interne). La Requête mere n'est evaluee que lorsque la Requête fille l'a été complétément. Les Requêtes correlees ont la mêmesyntaxe au detail pres suivant : la Requête fille fait une jointure sur la table de la Requête mere. Dans ce cas, l'ensemble Requête mere-Requête fille est evaluee de facon repétée pour chaque ligne de la table mere.
Exemple :
Nous reprenons l'exemple ou nous desirons les noms des clients n'ayant pas fait de commande depuis le 24 septembre :
La Requête mere s'exerce sur la table clients. La Requête fille fait une jointure entre les tables clients et commandes. On a donc une Requête correlee. Pour chaque ligne de la table clients, la Requête fille s'execute : elle cherche le code idcli du client dans les commandes faites apres le 24 septembre. Si elle n'en trouve pas (not exists), le nom du client est affiche. Ensuite, on passe a la ligne suivante de la table clients.
SQL> 1 select nom from clients 2 where not exists 3 (select idcli from commandes 4 where date_cmd>='24-sep-91' 5 and commandes.idcli=clients.idcli) NOM ------------------------------ LIBRAIRIE LA COMETE PREFECTURE DU M & L ARTUS MECAFLUID PLUCHOT
4.2.5 Criteres de choix pour l'ecriture du SELECT
Nous avons vu, a plusieurs reprises, qu'il etait possible d'obtenir un mêmeresultat par differentes ecriture du SELECT.
Exemple :
Afficher les clients ayant commande quelque chose :
Les auteurs Christian MAREE et Guy LEDANT, dans leur livre ' SQL, Initiation, Programmation et Maitrise' proposent quelques criteres de choix :
Performances
L'utilisateur ne sait pas comment ORACLE "se debrouille" pour trouver les resultats qu'il demande. Ce n'est donc que par experience, qu'il decouvrira que telle ecriture est plus performante qu'une autre. MAREE et LEDANT affirment par experience que les Requêtes correlees semblent généralement plus lentes que les Requêtes imbriquees ou les jointures. FORMULATION La formulation par Requêtes imbriquees est toujours plus lisible et plus intuitive que la jointure. Elle n'est cependant pas toujours utilisable. La regle est simple : Les tables proprietaires des colonnes arguments du SELECT ( SELECT col1, col2, ...) doivent être nommées derrière FROM. Le produit cartesien de ces tables est alors effectue, ce qu'on appelle une jointure. Lorsque la Requête affiche des resultats provenant d'une seule table, et que le filtrage des lignes de cette dernière impose la consultation d'une autre table, les Requêtes imbriquées s'imposent.
Exemple :
Afficher les clients ayant commande quelque chose :
Jointure SQL> 1 select distinct nom from clients,commandes 2 where clients.idcli=commandes.idcli NOM ------------------------------------------------------------------- BARNARD LIBRAIRIE DU MARCHE MAIRIE D'ANGERS TELELOGOS TRESOR PUBLIC Requêtes IMBRIQUEES
SQL> 1 select nom from clients 2 where idcli in (select idcli from commandes) NOM ------------------------------------------------------------------------------ LIBRAIRIE DU MARCHE TRESOR PUBLIC MAIRIE D'ANGERS TELELOGOS BARNARD Requêtes CORRELEES
SQL> 1 select nom from clients 2 where exists (select * from commandes where commandes.idcli=clients.idcli) NOM ------------------------------------------------------------------------------- LIBRAIRIE DU MARCHE TRESOR PUBLIC MAIRIE D'ANGERS TELELOGOS BARNARD
Performances
L'utilisateur ne sait pas comment ORACLE "se debrouille" pour trouver les resultats qu'il demande. Ce n'est donc que par experience, qu'il decouvrira que telle ecriture est plus performante qu'une autre. MAREE et LEDANT affirment par experience que les Requêtes correlees semblent généralement plus lentes que les Requêtes imbriquees ou les jointures. FORMULATION La formulation par Requêtes imbriquees est toujours plus lisible et plus intuitive que la jointure. Elle n'est cependant pas toujours utilisable. La regle est simple : Les tables proprietaires des colonnes arguments du SELECT ( SELECT col1, col2, ...) doivent être nommées derrière FROM. Le produit cartesien de ces tables est alors effectue, ce qu'on appelle une jointure. Lorsque la Requête affiche des resultats provenant d'une seule table, et que le filtrage des lignes de cette dernière impose la consultation d'une autre table, les Requêtes imbriquées s'imposent.
Pour des questions de commodite, nous avons le plus souvent presente des syntaxes reduites des differentes commandes. Dans cette section, nous en presentons des syntaxes elargies. Elles se comprennent d'elles-memes car elles sont analogues a celles de la commande SELECT largement etudiee.
explication Cette syntaxe a déjà été etudiee. Nous avons déjà vu deux contraintes : UNIQUE et NOT NULL. Il en existe d'autres, mais qui dans la version actuelle d'Oracle ne sont pas testees, quoique la syntaxe les accepte ! Donc nous ne les presentons pas.
explication Cette syntaxe a déjà été etudiee. Rappelons qu'elle permet de creer une table a partir d'une autre table.
explication Cette syntaxe a déjà été presentee. Ajoutons que la condition peut contenir une Requête avec la syntaxe
explication Les valeurs affectees aux differentes colonnes peuvent provenir d'une Requête.
CREATE syntaxe1 CREATE TABLE (colonne1 type1 contrainte1, colonne2 type2 contrainte2 ...)
syntaxe2 CREATE TABLE (colonne1 type1 contrainte1, colonne2 type2 contrainte2 ...) AS Requête
INSERT syntaxe1 INSERT INTO table (col1, col2, ..) VALUES (val1, val2, ...) syntaxe2 INSERT INTO table (col1, col2, ..) (Requête) explication Ces deux syntaxes ont été presentees DELETE syntaxe1 DELETE FROM table WHERE condition explication Cette syntaxe est connue. Ajoutons que la condition peut contenir une Requête avec la syntaxe WHERE expression opérateur (Requête) UPDATE syntaxe1 UPDATE table SET col1=expr1, col2=expr2, ... WHERE condition
WHERE expression opérateur (Requête) syntaxe2 UPDATE table SET (col1, col2, ..)= Requête1, (cola, colb, ..)= Requête2, ... WHERE condition
4.4 Gestion de l'acces concurrent aux données
Nous avons jusqu'a maintenant utilise des tables dont nous etions les seuls utilisateurs. Dans la pratique, sur une machine multi-utilisateurs, les données sont le plus souvent partagees entre differents utilisateurs. Se pose alors la question : qui peut utiliser telle ou telle table et sous quelle forme (consultation, insertion, suppression, ajout, ...) ?
Appelons environnement de l'utilisateur, les tables et vues auxquelles il peut acceder. Cet environnement est decrit dans la table système ACCESSIBLE_TABLES :
On constate que l'utilisateur a acces a un grand nombre de tables. Ce sont pour la plupart des tables du système Oracle formant ce qu'on appelle le "Dictionnaire des données". Nous y reviendrons ulterieurement.
Appelons environnement de l'utilisateur, les tables et vues auxquelles il peut acceder. Cet environnement est decrit dans la table système ACCESSIBLE_TABLES :
SQL> describe accessible_tables Name Null? Type ---------------------------------------------------------------------------- OWNER NOT NULL CHAR(30) TABLE_NAME NOT NULL CHAR(30) TABLE_TYPE CHAR(11) SQL> select * from accessible_tables where rownum<=10; OWNER TABLE_NAME TABLE_TYPE ----------------- ----------------------------------------------------------- SYS AUDIT_ACTIONS TABLE SYS USER_AUDIT_TRAIL VIEW SYS USER_AUDIT_CONNECT VIEW SYS USER_AUDIT_RESOURCE VIEW SYS DUAL TABLE SYS USER_CATALOG VIEW SYS ALL_CATALOG VIEW SYS ACCESSIBLE_TABLES VIEW SYS USER_CLUSTERS VIEW SYS USER_CLU_COLUMNS VIEW 10 rows selected.
4.4.1 Les privileges d'acces aux tables et vues
Lorsqu'un utilisateur cree une table ou une vue, il en est le proprietaire exclusif. Les autres utilisateurs n'y ont pas acces. Il peut cependant en donner un droit d'utilisation a d'autres par la commande GRANT.
action accorde des privileges d'acces privilegei ou tous les privileges (ALL PRIVILEGES) sur la table ou vue aux utilisateurs utilisateuri ou a tous les utilisateurs ( PUBLIC ). La clause WITH GRANT OPTION permet aux utilisateurs ayant recu les privileges de les transmettre a leur tour a d'autres utilisateurs. Les privileges privilegei qui peuventêtre accordes sont les suivants :
UPDATE droit d'utiliser la commande UPDATE sur la table ou vue. Ce droit peutêtre restreint a certaines colonnes par la syntaxe :
syntaxe GRANT privilege1, privilege2, ...| ALL PRIVILEGES ON table/vue TO utilisateur1, utilisateur2, ...| PUBLIC [ WITH GRANT OPTION ]
ALTER droit d'utiliser la commande ALTER TABLE sur la table. DELETE droit d'utiliser la commande DELETE sur la table ou vue. INSERT droit d'utiliser la commande INSERT sur la table ou vue SELECT droit d'utiliser la commande SELECT sur la table ou vue
GRANT update ( col1, col2, ...) ON table/vue TO utilisateur1, utilisateur2, ...| PUBLIC [ WITH GRANT OPTION ] INDEX droit d'utiliser la commande CREATE INDEX sur la table. Trois tables du système donnent des indications sur les privileges accordes ou recus : USER_TAB_GRANTS tables pour lesquelles on a accorde ou recu des privileges USER_TAB_GRANTS_MADE tables pour lesquelles on a accorde des privileges USER_TAB_GRANTS_RECD tables pour lesquelles on a recu des privileges Leur structure est la suivante : SQL> describe user_tab_grants Name Null? Type ---------------------------------------------------------------------- GRANTEE NOT NULL CHAR(30) <-- celui qui recoit le privilege OWNER NOT NULL CHAR(30) <-- le proprietaire de la table ou vue TABLE_NAME NOT NULL CHAR(30) <-- la table ou vue GRANTOR NOT NULL CHAR(30) <-- celui qui accorde les privileges SELECT_PRIV CHAR(1) <-- privilege SELECT (Y : yes ou N : no ) INSERT_PRIV CHAR(1) <-- privilege INSERT DELETE_PRIV CHAR(1) <-- privilege DELETE UPDATE_PRIV CHAR(1) <-- privilege UPDATE REFERENCES_PRIV CHAR(1) ALTER_PRIV CHAR(1) <-- privilege ALTER INDEX_PRIV CHAR(1) <-- privilege INDEX CREATED NOT NULL DATE <-- date d'octroi des privileges SQL> describe user_tab_grants_made Name Null? Type --------------------------------------------------------------------- GRANTEE NOT NULL CHAR(30) TABLE_NAME NOT NULL CHAR(30) GRANTOR NOT NULL CHAR(30) SELECT_PRIV CHAR(1) INSERT_PRIV CHAR(1) DELETE_PRIV CHAR(1) UPDATE_PRIV CHAR(1) REFERENCES_PRIV CHAR(1) ALTER_PRIV CHAR(1) INDEX_PRIV CHAR(1) CREATED NOT NULL DATE SQL> describe user_tab_grants_recd Name Null? Type ---------------------------------------------------------------------- OWNER NOT NULL CHAR(30) TABLE_NAME NOT NULL CHAR(30) GRANTOR NOT NULL CHAR(30) SELECT_PRIV CHAR(1) INSERT_PRIV CHAR(1) DELETE_PRIV CHAR(1) UPDATE_PRIV CHAR(1) REFERENCES_PRIV CHAR(1) ALTER_PRIV CHAR(1) INDEX_PRIV CHAR(1) CREATED NOT NULL DATE SQL> select table_name from tabs; TABLE_NAME ---------------------------------------------------------- ARTICLES BIBLIO BIDON <------------------------------------------------ CLIENTS CMD_ID COMMANDES SAUVEGARDE STOCKS 8 rows selected. SQL> grant select,update on bidon to allo; Grant succeeded. SQL> select grantee,table_name,select_priv,update_priv from user_tab_grants_made; GRANTEE TABLE_NAME S U --------------------------------------------------------------- ALLO BIDON Y A <-- A : ALL (update autorise sur toutes les colonnes)
Remarque:
Un utilisateur ayant recu des droits d'un utilisateur U sur une table T ne peut referencer celle-ci que par la syntaxe U.T sauf si la table T a un synonyme public lequel peutêtre cree par l'administrateur avec la commande
create public synonym nom1 for table1
Par exemple si on veut accorder a tous un droit de lecture sur une table T d'un utilisateur U :
„« le proprietaire U accordera les droits necessaires grant select to public on T „« l'administrateur donnera un synonyme public a la table T de U create public synonym S for U.T „« tout utilisateur a maintenant acces a la table S select * from S
4.4.2 Suppression des privileges accordes
Le proprietaire d'un objet peut revenir sur sa decision d'accorder des privileges d'acces a d'autres utilisateurs par la commande REVOKE :
action supprime des privileges d'acces privilegei ou tous les privileges (ALL PRIVILEGES) sur la table ou vue aux utilisateurs utilisateuri ou a tous les utilisateurs ( PUBLIC ).
syntaxe REVOKE privilege1, privilege2, ...| ALL PRIVILEGES ON table/vue FROM utilisateur1, utilisateur2, ...| PUBLIC
Exemple
SQL> revoke all privileges on bidon from allo; Revoke succeeded. SQL> select grantee,table_name,select_priv,update_priv from user_tab_grants_made no rows selected <-- il n'y a plus de privileges sur la table bidon
Une transaction est l'ensemble des commandes SQL emises entre un COMMIT/ROLLBACK et le COMMIT/ROLLBACK suivant. Rappelons quelques points sur ces commandes :
U2 travaille sur une photo de TAB prise au temps T2a, donc la mêmephoto qu'utilisee par U1 (si d'autres utilisateurs n'ont pas modifie l'original entre-temps). Il ne "voit" pas les modifications qu'a pu apporter l'utilisateur U1 sur TAB. Il ne pourra les voir qu'au temps T1b.
Cet exemple donne une nouvelle dimension a la transaction : une transaction est une suite coherente de commandes SQL qui doit former un tout indissociable. Prenons un exemple en comptabilite : U1 et U2 travaillent sur des comptes. U1 credite comptex d'une somme et debite comptey de la mêmesomme pendant que U2 consulte les comptes.
Supposons que la transaction de U1 consiste en la mise a jour de comptex suivie de la mise a jour de comptey et que la transaction de U2 consiste en la visualisation de l'etat des comptes. Nous considerons les deux cas possibles :
Lorsque U2 consulte, il voit les anciennes valeurs des comptes comptex et comptey. Cependant la table est coherente : le total des CREDITS est egal au total des DEBITS.
Lorsque U2 consulte, il voit les nouvelles valeurs des comptes comptex et comptey. La table est coherente : le total des CREDITS est egal au total des DEBITS.
Supposons maintenant que U1 credite comptex puis valide pour ensuite debiter comptey et valider. Il y alors deux transactions de la part de U1. L'une entre T1a et T1b, l'autre entre T1b et T1c. Supposons que U2 commence sa transaction au temps T2 suivant :
--------+----------+--------+-------+----------------------
T1a T1b T2 T1c
Au temps T2, une photo de la table des comptes est prise pour U2. Sur cette photo, il apparaitra que les comptes ne sont pas equilibres : comptex apparait credite alors que comptey n'apparait pas encore debite. C'est une situation anormale. généralement, ces problemes de transaction seront regles par programme : c'est le concepteur du programme qui choisira les groupes de commandes qui doiventêtre indissociables et donc faire l'objet d'une transaction. Les transactions resteront le plus souvent inconnues des utilisateurs du programme.
1 En consultation, un utilisateur travaille sur la table originale. 2 En modification (update, insert, DELETE), les lignes modifiees sont creees en-dehors de la table originale. Celle-ci n'est donc pas modifiee Original -------> ligne modifiee -------> ligne modifiee 3 les lignes de la table originale ne sont accessibles qu'en lecture aux autres utilisateur (ceux qui ont des droits dessus). Toute modification de leur part est bloquee tant que l'utilisateur ayant le premier modifie la table originale n'a pas valide (COMMIT) ou invalide (ROLLBACK) ses modifications, c.a.d. tant qu'il n'a pas termine sa transaction. Quelques commandes generent un COMMIT implicite : a Les commandes qui modifient la structure des tables : CREATE TABLE, ALTER TABLE, DROP TABLE, GRANT b La deconnexion d'Oracle qu'elle soit normale ou anormale (panne système). Dans un contexte multi-utilisateurs, la transaction revet d'autres aspects. Soient deux utilisateurs U1 et U2 travaillant sur la mêmetable TAB : --------+----------+--------+-------+---------------------- T1a T2a T1b T2b La transaction de l'utilisateur U1 commence au temps T1a et finit au temps T1b. La transaction de l'utilisateur U2 commence au temps T2a et finit au temps T2b. U1 travaille sur une photo de TAB prise au temps T1a. Entre T1a et T1b, il modifie TAB. Les autres utilisateurs n'auront acces a ces modifications qu'au temps T1b, lorsque U1 fera un COMMIT.
Cet exemple donne une nouvelle dimension a la transaction : une transaction est une suite coherente de commandes SQL qui doit former un tout indissociable. Prenons un exemple en comptabilite : U1 et U2 travaillent sur des comptes. U1 credite comptex d'une somme et debite comptey de la mêmesomme pendant que U2 consulte les comptes.
Supposons que la transaction de U1 consiste en la mise a jour de comptex suivie de la mise a jour de comptey et que la transaction de U2 consiste en la visualisation de l'etat des comptes. Nous considerons les deux cas possibles :
1 U1 termine sa transaction apres que U2 ne commence la sienne. 2 U1 termine sa transaction avant que U2 ne commence la sienne. Cas 1 Le schema temporel est le suivant : --------+----------+--------+-------+---------------------- T1a T2a T1b T2b
Cas 2 Le schema temporel est le suivant : --------+----------+--------+-------+---------------------- T1a T1b T2a T2b
Supposons maintenant que U1 credite comptex puis valide pour ensuite debiter comptey et valider. Il y alors deux transactions de la part de U1. L'une entre T1a et T1b, l'autre entre T1b et T1c. Supposons que U2 commence sa transaction au temps T2 suivant :
--------+----------+--------+-------+----------------------
T1a T1b T2 T1c
Au temps T2, une photo de la table des comptes est prise pour U2. Sur cette photo, il apparaitra que les comptes ne sont pas equilibres : comptex apparait credite alors que comptey n'apparait pas encore debite. C'est une situation anormale. généralement, ces problemes de transaction seront regles par programme : c'est le concepteur du programme qui choisira les groupes de commandes qui doiventêtre indissociables et donc faire l'objet d'une transaction. Les transactions resteront le plus souvent inconnues des utilisateurs du programme.
4.4.4 Lecture coherente
Le mecanisme de la photo est appele sous Oracle "Read Consistency" que nous traduirons par "Lecture coherente". Explicitons cette notion sur un exemple. Soit un utilisateur U1 faisant une Requête de consultation sur la table TAB, alors qu'un utilisateur U2 est en train de la modifier selon le schéma temporel suivant :
--------+----------+--------+-------+----------------------
T1a T2a T2b T1b
L'utilisateur U1 fait sa Requête en T1a. Celle-ci est terminee en T1b.
L'utilisateur U2 fait sa modification en T2a. Celle-ci est terminee en T2b.
Entre le temps T1a et le temps T1b, l'etat de la table TAB peut changer : notamment au temps T2b lorsque l'utilisateur U2 valide son travail. Cependant, la Requête de l'utilisateur U1 ne travaille que sur la photo de la table prise au temps T1a. C'est la notion de lecture coherente. Une Requête de consultation travaille sur une photo prise a un instant T ou toutes les données sont dans un etat coherent, stable. Elle ne tient pas compte des changement d'état de la table qui peuvent survenir lors de son exécution.
La lecture coherente s'applique normalement a une commande SQL isolee. On peut l'éténdre a une transaction. Prenons un Exemple :
La commande 1 travaille sur une photo prise au temps T1, et la commande 2 sur une photo prise au temps T2. Entrétémps, la table TAB a pu changer. Ce serait genant dans notre exemple, puisqu'on desire obtenir la moyenne et l'ecart-type sur les memes données. Ce n'est pas garanti ici.
On peut, pour resoudre le probleme utiliser une transaction speciale appelee une transaction a lecture seulement (Read Only Transaction). Cette transaction prend, lorsqu'elle demarre, une photo de la base et travaille ensuite uniquement sur cette photo, jusqu'a la fin de la transaction. Une telle transaction ne doit comporter que des lectures de la base. Sa syntaxe est la suivante :
--------+----------+--------+-------+----------------------
T1a T2a T2b T1b
L'utilisateur U1 fait sa Requête en T1a. Celle-ci est terminee en T1b.
L'utilisateur U2 fait sa modification en T2a. Celle-ci est terminee en T2b.
Entre le temps T1a et le temps T1b, l'etat de la table TAB peut changer : notamment au temps T2b lorsque l'utilisateur U2 valide son travail. Cependant, la Requête de l'utilisateur U1 ne travaille que sur la photo de la table prise au temps T1a. C'est la notion de lecture coherente. Une Requête de consultation travaille sur une photo prise a un instant T ou toutes les données sont dans un etat coherent, stable. Elle ne tient pas compte des changement d'état de la table qui peuvent survenir lors de son exécution.
La lecture coherente s'applique normalement a une commande SQL isolee. On peut l'éténdre a une transaction. Prenons un Exemple :
l'utilisateur U1 fait des statistiques sur la table TAB : 1 select avg(col) from tab;au temps T1 2 select stddev(col) from tab; au temps T2
On peut, pour resoudre le probleme utiliser une transaction speciale appelee une transaction a lecture seulement (Read Only Transaction). Cette transaction prend, lorsqu'elle demarre, une photo de la base et travaille ensuite uniquement sur cette photo, jusqu'a la fin de la transaction. Une telle transaction ne doit comporter que des lectures de la base. Sa syntaxe est la suivante :
SET TRANSACTION READ ONLY; <-- debut de la transaction Commande1; ........................................ Commanden; COMMIT; <-- fin de la transaction
4.4.5 Controle par defaut des acces concurrents
Le mecanisme de la lecture coherente, nous permet de travailler sur un etat stable d'une table et non sur un etat en cours d'evolution et inacheve. Il ne resout pas tous les problemes d'acces concurrents aux tables. Considerons de nouveau l'exemple de nos deux utilisateurs U1 et U2 travaillant sur la mêmetable TAB avec le schema temporel suivant :
--------+----------+--------+-------+----------------------
T1a T2a T1b T2b
La transaction de l'utilisateur U1 commence au temps T1a et finit au temps T1b.
La transaction de l'utilisateur U2 commence au temps T2a et finit au temps T2b.
Supposons que U1 et U2 travaillent avec un système de reservation de places de train. Au temps T1a, une photo de la table des reservations est prise pour U1 : le siege 12 du wagon 20 du TGV 2040 est libre. L'application le reserve pour U1. Au temps T2a, une photo des reservations est prise pour U2. C'est la mêmeque pour U1 qui n'a pas encore termine sa transaction. L'application risque donc de reserver pour U2 le mêmesiege que pour U1.
Oracle evite ces situations de la facon suivante :
a La commande SELECT
Cette commande ne fait que lire des données. C'est le mecanisme de la photo qui assure que les données recuperees sont des données stables et non en cours d'evolution.
b Commandes de modification du contenu des tables : UPDATE, INSERT et DELETE.
1 Une photo coherente de la table est prise
2 La commande de mise a jour obtient un usage exclusif des lignes a mettre a jour. Elle est la seule a pouvoir les modifier jusqu'a ce que la transaction se termine.
3 Les lectures par d'autres transactions des lignes bloquees sont autorisees. Une photo coherente leur sera fournie.
4 Les autres transactions peuvent faire ce qu'elles veulent sur les lignes non bloquees.
5 Les lignes sont liberees des que la transaction qui les a bloquees est terminee.
c Commandes de modification de la structure d'une table : CREATE, ALTER, DROP :
1 Un COMMIT implicite est genere.
2 La commande obtient un usage exclusif de la table modifiee.
3 Les autres transactions ne peuvent que consulter la table (avec une photo de l'ancienne structure) , elles ne peuvent la modifier.
4 Apres execution de la commande, un COMMIT implicite est genere liberant la table pour les autres transactions.
De nouveau, l'importance de la transaction apparait ici : un utilisateur faisant un UPDATE sur une table bloque les lignes mises a jour jusqu'a la fin de la transaction les rendant indisponibles aux autres utilisateurs qui voudraient les modifier. Il est donc souhaitable d'emettre un COMMIT apres modification des données. Le plus souvent, c'est un programme qui le fera.
--------+----------+--------+-------+----------------------
T1a T2a T1b T2b
La transaction de l'utilisateur U1 commence au temps T1a et finit au temps T1b.
La transaction de l'utilisateur U2 commence au temps T2a et finit au temps T2b.
Supposons que U1 et U2 travaillent avec un système de reservation de places de train. Au temps T1a, une photo de la table des reservations est prise pour U1 : le siege 12 du wagon 20 du TGV 2040 est libre. L'application le reserve pour U1. Au temps T2a, une photo des reservations est prise pour U2. C'est la mêmeque pour U1 qui n'a pas encore termine sa transaction. L'application risque donc de reserver pour U2 le mêmesiege que pour U1.
Oracle evite ces situations de la facon suivante :
a La commande SELECT
Cette commande ne fait que lire des données. C'est le mecanisme de la photo qui assure que les données recuperees sont des données stables et non en cours d'evolution.
b Commandes de modification du contenu des tables : UPDATE, INSERT et DELETE.
1 Une photo coherente de la table est prise
2 La commande de mise a jour obtient un usage exclusif des lignes a mettre a jour. Elle est la seule a pouvoir les modifier jusqu'a ce que la transaction se termine.
3 Les lectures par d'autres transactions des lignes bloquees sont autorisees. Une photo coherente leur sera fournie.
4 Les autres transactions peuvent faire ce qu'elles veulent sur les lignes non bloquees.
5 Les lignes sont liberees des que la transaction qui les a bloquees est terminee.
c Commandes de modification de la structure d'une table : CREATE, ALTER, DROP :
1 Un COMMIT implicite est genere.
2 La commande obtient un usage exclusif de la table modifiee.
3 Les autres transactions ne peuvent que consulter la table (avec une photo de l'ancienne structure) , elles ne peuvent la modifier.
4 Apres execution de la commande, un COMMIT implicite est genere liberant la table pour les autres transactions.
De nouveau, l'importance de la transaction apparait ici : un utilisateur faisant un UPDATE sur une table bloque les lignes mises a jour jusqu'a la fin de la transaction les rendant indisponibles aux autres utilisateurs qui voudraient les modifier. Il est donc souhaitable d'emettre un COMMIT apres modification des données. Le plus souvent, c'est un programme qui le fera.
Remarque
Lorsqu'un utilisateur obtient l'usage exclusif d'une table ou de lignes de la table, on dit que la table ou les lignes sont verrouillées.
4.4.6 Controle explicite des acces concurrents
Le comportement par defaut d'Oracle dans la gestion des acces concurrents peutêtre ignore par un verrouillage explicitement demande par l'utilisateur. Il y a peu de cas ou cela s'avere necessaire. Citons-en quelques-uns : 1 La base que vous utilisez est tres demandee. Les temps d'attente deviennent longs. Vous étés prioritaire : vous demandez alors l'usage exclusif des tables que vous utilisez. Les autres n'y auront plus acces.
2 Vous voulez travailler avec un etat stable de la base pendant toute la duree d'une transaction. Vous voulez modifier la base. La transaction a lecture seulement ne suffit donc pas : vous demandez un usage exclusif de la table.
Le verrouillage explicite est demande par :
seules les Requêtes sont autorisees sur la table. Celui qui verrouille a seul la possibilite de modifier son contenu.
ROW SHARE
autorise les acces concurrents a la table comme dans la methode par defaut. Empeche de plus un autre utilisateur de poser un verrou exclusif sur la table.
La clause NOWAIT indique qu'il ne faut pas attendre si le verrouillage demande ne peut être obtenu. En son absence, l'utilisateur est mis en attente de la liberation de l'objet demande.
La fin de la transaction (COMMIT/ROLLBACK) enleve tout verrouillage.
2 Vous voulez travailler avec un etat stable de la base pendant toute la duree d'une transaction. Vous voulez modifier la base. La transaction a lecture seulement ne suffit donc pas : vous demandez un usage exclusif de la table.
Le verrouillage explicite est demande par :
syntaxe LOCK TABLE table1, table2,.. IN mode MODE [NOWAIT] action verrouille table1, table2, ... dans le mode indique par mode : EXCLUSIVE
ROW SHARE
autorise les acces concurrents a la table comme dans la methode par defaut. Empeche de plus un autre utilisateur de poser un verrou exclusif sur la table.
La clause NOWAIT indique qu'il ne faut pas attendre si le verrouillage demande ne peut être obtenu. En son absence, l'utilisateur est mis en attente de la liberation de l'objet demande.
La fin de la transaction (COMMIT/ROLLBACK) enleve tout verrouillage.
4.5 Gestion des performances
Nous l'avons déjà dit : l'utilisateur ne sachant pas comment Oracle procede pour repondre a ses demandes, il lui est difficile de les formuler de facon a optimiser les temps de réponse. Il peut parfois augmenter les performances à l'aide de deux outils :
les index et les clusters. Nous n'evoquons ici que les index.
les index et les clusters. Nous n'evoquons ici que les index.
4.5.1 Les index
Considerons le fichier BIBLIO. Vous voulez des informations sur le livre 'Manhattan Transfer' :
En l'absence d'index, Oracle ira consulter toute la table BIBLIO afin de trouver le livre en question. Le temps de consultation dependra de la taille de la table.
En faisant de la colonne TITRE, un index, un fichier d'index est cree. Oracle ira alors consulter ce fichier pour rechercher le titre 'Manhattan Transfer'. Ce fichier est organise de maniere a favoriser les recherches. Elle est tres rapide et peu dependante de la taille de la table indexee.
L'indexation ralentit la mise a jour des tables, puisque le fichier index doit suivre ces mises a jour.
SELECT * FROM biblio WHERE TITRE='Manhattan Transfer'
En faisant de la colonne TITRE, un index, un fichier d'index est cree. Oracle ira alors consulter ce fichier pour rechercher le titre 'Manhattan Transfer'. Ce fichier est organise de maniere a favoriser les recherches. Elle est tres rapide et peu dependante de la taille de la table indexee.
L'indexation ralentit la mise a jour des tables, puisque le fichier index doit suivre ces mises a jour.
syntaxe CREATE [UNIQUE] INDEX nom_index ON table ( expr1, expr2, ..)
action cree l'index nomme nom_index a partir des expressions expr1, expr2, ... de table. Ameliore les temps de recherche sur les expressions participant a l'index.La clause UNIQUE demande a ce que deux lignes différentes de table aient deux index différents.
Remarques
On indexera en général, sur les colonnes intervenant souvent dans la selection des lignes, c'est à dire se trouvant dans la clause WHERE du SELECT.
Exemples:
SQL> select titre, auteur from biblio; TITRE AUTEUR -------------------- --------------- Les fleurs du mal Baudelaire Tintin au Tibet Herge La terre Zola Madame Bovary Flaubert Manhattan transfer Dos Passos Tintin en Amerique Herge Le pere Goriot Balzac 7 rows selected. SQL> create index titre on biblio(titre); <-- indexation sur les titres des livres Index created. SQL> create index auteur on biblio(auteur); <-- indexation sur la colonne auteur Index created. SQL> create index a date_achat on biblio(achat); <-- indexation sur la date d'achat du livre Index created. SQL> create index genre_prix on biblio (genre,prix) <-- index sur deux colonnes Index created.
4.5.3 Obtenir la liste des index
La vue IND rassemble les index de l'utilisateur :
SQL> describe IND Name Null? Type ----------------------------------------------------------------------- INDEX_NAME NOT NULL CHAR(30) TABLE_OWNER NOT NULL CHAR(30) TABLE_NAME NOT NULL CHAR(30) TABLE_TYPE CHAR(11) UNIQUENESS CHAR(9) TABLESPACE_NAME NOT NULL CHAR(30) INI_TRANS NOT NULL NUMBER MAX_TRANS NOT NULL NUMBER INITIAL_EXTENT NUMBER Conclusion 66 NEXT_EXTENT NUMBER MIN_EXTENTS NOT NULL NUMBER MAX_EXTENTS NOT NULL NUMBER PCT_INCREASE NOT NULL NUMBER SQL> select index_name,table_name,uniqueness from ind; INDEX_NAME TABLE_NAME UNIQUENES --------------------------------------------------------------------- AUTEUR BIBLIO NONUNIQUE DATE_ACHAT BIBLIO NONUNIQUE GENRE_PRIX BIBLIO NONUNIQUE TITRE BIBLIO NONUNIQUE
4.5.4 Abandon d'un index
syntaxe DROP INDEX nom_index action abandonne l'index nomme exemples SQL> drop index genre_prix; <-- abandon d'un index Index dropped. SQL> select index_name from ind; <-- verification INDEX_NAME ------------------------------ <-- il n'est plus la AUTEUR DATE_ACHAT TITRE
4.5.5 Conseils
Nous regroupons ici des conseils glanes dans les ouvrages déjà cites :
1 Creer un index sur les colonnes intervenant souvent dans la selection des lignes, c'est a dire dans les clauses WHERE, GROUP BY, ORDER BY
2 Creer un index sur les colonnes qui servent a faire des jointures entre tables.
3 Creer un index pour les grande tables (plusieurs centaines de lignes)
4 Ne pas creer d'index sur des colonnes ayant peu de valeurs differentes ( la colonne genre de la table BIBLIO par exemple).
5 Ne pas creer d'index pour une Requête qui retournera plus du quart des lignes d'une table.
Un index n'est pas toujours utilise. C'est "l'optimiseur" d'Oracle qui decide de l'utiliser ou pas selon des regles qui lui sont propres.
1 Creer un index sur les colonnes intervenant souvent dans la selection des lignes, c'est a dire dans les clauses WHERE, GROUP BY, ORDER BY
2 Creer un index sur les colonnes qui servent a faire des jointures entre tables.
3 Creer un index pour les grande tables (plusieurs centaines de lignes)
4 Ne pas creer d'index sur des colonnes ayant peu de valeurs differentes ( la colonne genre de la table BIBLIO par exemple).
5 Ne pas creer d'index pour une Requête qui retournera plus du quart des lignes d'une table.
Un index n'est pas toujours utilise. C'est "l'optimiseur" d'Oracle qui decide de l'utiliser ou pas selon des regles qui lui sont propres.
4.6 Le dictionnaire des données
Oracle gere un certain nombre de tables contenant diverses informations sur tous les objets (tables, vues, index, privileges d'acces, ..) de tous les utilisateurs. Elles donnent des ringenieurements souvent interessants pour l'utilisateur. Les informations concernant les objets d'un utilisateur donne sont contenues dans les vues ou les tables suivantes :
De tres nombreuses autres tables et vues sont accessibles a l'utilisateur et lui donnent des ringenieurements souvent interessants. Leur liste se trouve dans la table DICT :
Pour utiliser l'une de ces tables, on peut vérifier sa structure par DESCRIBE table puis consulter le contenu par
TABS vue - liste des tables creees par l'utilisateur USER_VIEWS table - liste des vues creees par l'utilisateur OBJ vue - liste des objets appartenant a l'utilisateur MYPRIVS vue - privileges de l'utilisateur IND vue - index crees par l'utilisateur USER_TAB_GRANTS_MADE table - privileges accordes par l'utilisateur sur des objets lui appartenant USER_TAB_GRANTS_RECD table - privileges recus par l'utilisateur sur des objets ne lui appartenant pas. COLS vue - liste des colonnes appartenant a l'utilisateur
SQL> describe dict Name Null? Type ------------------------------------------------------------- TABLE_NAME CHAR(30) COMMENTS CHAR(255) SQL> select * from dict; TABLE_NAME COMMENTS ACCESSIBLE_COLUMNS Columns of all tables, views and clusters ACCESSIBLE_TABLES Tables and Views accessible to the user ALL_CATALOG All tables, views, synonyms, sequences accesible to the user ALL_COL_COMMENTS Comments on columns of accessible tables and views ALL_COL_GRANTS Synonym for COLUMN_PRIVILEGES ALL_COL_GRANTS_MADE Grants on columns for which the user is owner or grantor ALL_COL_GRANTS_RECD Grants on columns for which the user or PUBLIC is the grantee ALL_CONSTRAINTS Constraint definitions on accessible tables ALL_CONS_COLUMNS Information about accessible columns in constraint definitions ALL_DB_LINKS Database links accessible to the user ALL_DEF_AUDIT_OPTS Auditing options for newly created objects ALL_INDEXES Descriptions of indexes on tables accessible to the user ALL_IND_COLUMNS COLUMNs comprising INDEXes on accessible TABLES ALL_OBJECTS Objects accessible to the user ALL_SEQUENCES Description of SEQUENCEs accessible to the user ALL_SYNONYMS All synonyms accessible to the user ALL_TABLES Description of tables accessible to the user ALL_TAB_COLUMNS Synonym for ACCESSIBLE_COLUMNS ALL_TAB_COMMENTS Comments on tables and views accessible to the user ALL_TAB_GRANTS Synonym for TABLE_PRIVILEGES ALL_TAB_GRANTS_MADE User's grants and grants on user's objects ALL_TAB_GRANTS_RECD Grants on objects for which the user or PUBLIC is the grantee ALL_USERS Information about all users of the database ALL_VIEWS Text of views accessible to the user AUDIT_ACTIONS Description table for audit trail action type codes. Maps action type numbers to action type names CAT Synonym for USER_CATALOG CLU Synonym for USER_CLUSTERS COLS Synonym for USER_TAB_COLUMNS COLUMN_PRIVILEGES Grants on columns for which the user is the grantor, grantee, or owner, or PUBLIC is the grantee CONSTRAINT_COLUMNS Information about accessible columns in constraint definitions CONSTRAINT_DEFS Constraint Definitions on accessible tables DBA_AUDIT_CONNECT Synonym for USER_AUDIT_CONNECT DBA_AUDIT_RESOURCE Synonym for USER_AUDIT_RESOURCE DBA_AUDIT_TRAIL Synonym for USER_AUDIT_TRAIL DICT Synonym for DICTIONARY DICTIONARY Description of data dictionary tables and views DICT_COLUMNS Description of columns in data dictionary tables and views DUAL IND Synonym for USER_INDEXES MYPRIVS Synonym for USER_USERS OBJ Synonym for USER_OBJECTS SEQ Synonym for USER_SEQUENCES SYN Synonym for USER_SYNONYMS TABLE_PRIVILEGES Grants on objects for which the user is the grantor, grantee, or owner, or PUBLIC is the grantee TABS Synonym for USER_TABLES USER_AUDIT_CONNECT Audit trail entries for user logons/logoffs USER_AUDIT_TRAIL Audit trail entries relevant to the user USER_CATALOG Tables, Views, Synonyms, Sequences accessible to the user USER_CLUSTERS Descriptions of user's own clusters USER_CLU_COLUMNS Mapping of table columns to cluster columns USER_COL_COMMENTS Comments on columns of user's tables and views USER_COL_GRANTS Grants on columns for which the user is the owner, grantor or grantee USER_COL_GRANTS_MADE All grants on columns of objects owned by the user USER_COL_GRANTS_RECD Grants on columns for which the user is the grantee USER_CONSTRAINTS Constraint definitions on accessible tables USER_CONS_COLUMNS Information about accessible columns in constraint definitions USER_CROSS_REFS Cross references for user's views, synonyms, and constraints USER_DB_LINKS Database links owned by the user USER_EXTENTS Extents comprising segments owned by the user USER_FREE_SPACE Free extents in tablespaces accessible to the user USER_INDEXES Description of the user's own indexes USER_IND_COLUMNS COLUMNs comprising user's INDEXes or on user's TABLES USER_OBJECTS Objects owned by the user USER_SEGMENTS Storage allocated for all database segments USER_SEQUENCES Description of the user's own SEQUENCEs USER_SYNONYMS The user's private synonyms USER_TABLES Description of the user's own tables USER_TABLESPACES Description of accessible tablespaces USER_TAB_AUDIT_OPTS Auditing options for user's own tables and views USER_TAB_COLUMNS Columns of user's tables, views and clusters USER_TAB_COMMENTS Comments on the tables and views owned by the user USER_TAB_GRANTS Grants on objects for which the user is the owner, grantor or grantee USER_TAB_GRANTS_MADE All grants on objects owned by the user USER_TAB_GRANTS_RECD Grants on objects for which the user is the grantee USER_TS_QUOTAS Tablespace quotas for the user USER_USERS Information about the current user USER_VIEWS Text of views owned by the user V$ACCESS Synonym for V_$ACCESS V$BGPROCESS Synonym for V_$BGPROCESS V$DBFILE Synonym for V_$DBFILE V$FILESTAT Synonym for V_$FILESTAT V$LATCH Synonym for V_$LATCH V$LATCHHOLDER Synonym for V_$LATCHHOLDER V$LATCHNAME Synonym for V_$LATCHNAME V$LOCK Synonym for V_$LOCK V$LOGFILE Synonym for V_$LOGFILE V$PARAMétéR Synonym for V_$PARAMétéR V$PROCESS Synonym for V_$PROCESS V$RESOURCE Synonym for V_$RESOURCE V$ROLLNAME Synonym for V_$ROLLNAME V$ROLLSTAT Synonym for V_$ROLLSTAT V$ROWCACHE Synonym for V_$ROWCACHE V$SESSION Synonym for V_$SESSION V$SESSTAT Synonym for V_$SESSTAT V$SGA Synonym for V_$SGA V$STATNAME Synonym for V_$STATNAME V$SYSSTAT Synonym for V_$SYSSTAT V$TRANSACTION Synonym for V_$TRANSACTION V$_LOCK Synonym for V_$_LOCK
SELECT col1, col2, ... FROM table.
5 Conclusion
Nous avons vu l'essentiel d'Oracle pour l'utilisateur moyen. Nous n'avons en revanche que peu aborde le fonctionnement interne de ce SGBDR. Celui-ci est decrit dans la documentation Oracle "Guide pour l'Administrateur de la Base". On y trouve des ringenieurements precieux permettant une comprehension fine du fonctionnement d'Oracle et donnant la vue d'ensemble dont a besoin l'Administrateur de la base. Sa lecture est tres vivement conseillee pour une utilisation avancee d'Oracle. Enfin, nous n'avons pas aborde la programmation, que ce soit le langage PL/SQL propre a Oracle ou le langage Pro*C permettant d'integrer des ordres SQL dans un programme C.
