SOL-ORACLE

Introduction :

Une base de données est un ensemble organisé d'informations. Pour gérer une base de
données, il faut un système de gestion de bases de données (SGBD).

Un SGBD est un programme qui permet de stocker, d'extraire et de modifier des données à la
demande dans la base de données. Il existe cinq types principaux de bases de données :
Hiérarchique, réseau, relationnel, relationnel objet et objet.

Remarque : Oracle (à partir de la version 8) est un système de gestion de bases de données

relationnelles objet.

Composants du Modèle Relationnel

 Collections d'objets appelés encore relations pour stocker les données

 Ensemble d'opérateurs agissant sur les relations afin de produire d'autres relations

 Des règles d'intégrité pour garantir l'exactitude et la cohérence des données

Base de Données Relationnelle

Une base de données relationnelle stocke l'information au moyen de relations ou tables à
deux dimensions.

Terminologie des Bases de Données Relationnelles

Une base de données relationnelle peut contenir une OU plusieurs tables.
La table est la structure de stockage élémentaire SGBDR. Elle contient toutes les données
nécessaires relatives à des éléments du monde réel, par exemple, des employés, des factures, des
clients. On distingue les éléments suivants (on a pris comme exemple la table ou relation EMP).

1. Une ligne unique ou tuple indiquant toutes les données concernant un employé particulier.
Chaque ligne de la table doit être identifiée par une clé primaire, ce qui permet d'éviter les
doublons. L'ordre des lignes n'a pas d'importance; il peut être spécifié lors de l'extraction, des
données.

2. Une colonne ou attribut contenant le matricule des employés, est la clé primaire. Le matricule
identifie un employé unique dans la table EMP. La colonne clé primaire doit obligatoirement
être renseignée.

3. Une colonne ne contient pas de valeur clé. Dans une table, une colonne représente un type de
données.

4. La clé étrangère. Une clé étrangère est une colonne qui définit la manière dont les tables sont
liées entre elles. Elle fait référence à une clé primaire ou à une clé unique d'une autre table.
Dans l'exemple, DEPTNO identifie de façon unique un département de la table DEPT.

5. Un champ se situe à l'intersection d'une ligne et d'une colonne. Il ne peut contenir qu'une seule
valeur.
1
6. Un champ peut ne contenir aucune valeur. On parle alors de valeur NULL. Dans la table EMP,
seules les lignes des employés ayant le statut de vendeur contiennent une valeur dans le champ
COMM (commission).

Liaison de Plusieurs Tables

Chaque table contient des données qui décrivent une et une seule entité. La table EMP, par
exemple, contient des données sur les employés. Un format de table permet immédiatement de
visualiser, comprendre et utiliser l'information.
Les données concernant chaque entité distincte étant stockées dans différentes tables, il peut
être nécessaire de combiner deux ou plusieurs tables afin de répondre à une question particulière.
Supposons, par exemple, que vous vouliez savoir où se situe le département d'un employé. Vous
avez besoin pour cela des données de la table EMP (contenant des informations sur les employés) et
de la table DEPT (contenant des informations sur les départements).
Un SGBDR permet de lier les données de deux tables au moyen de la clé étrangère. La clé
étrangère est une colonne ou un ensemble de colonnes faisant référence à une clé primaire de la
même table ou d'une autre table.
La possibilité de relier les données d'une table à celles d'une table permet d'organiser
l'information en unités séparées et faciles à gérer. Ainsi, vous pouvez séparer de façon logique les
données concernant les employés de celles concernant les départements, en les stockant dans des
tables différentes.

Règles concernant les Clés Primaires et Etrangères

 La clé primaire ne doit comporter aucun doublon.

 La clé primaire ne peut généralement pas être modifiée.
 Les clés étrangères sont basées sur des valeurs des données. Ce sont des pointeurs purement
logiques et non physiques.
 Une valeur de clé étrangère doit renvoyer à une valeur de clé primaire ou unique existante, ou
bien à une valeur NULL.

Propriétés des Bases de Données Relationnelles

Dans une base de données relationnelle, il est inutile d'indiquer le chemin d'accès aux tables
ni de connaître l'agencement physique des données.
Pur accéder à la base de données, vous exécutez un ordre du langage SQL (Structured Query
Language) qui est le langage standard ANSI des bases de données relationnelles. Il comprend un
grand nombre d'opérateurs qui permettent de partitionner et combiner les relations. La base de
données peut être modifiée au moyen d'ordre SQL.

Système de Gestion de Base de Données Relationnelle

Oracle offre un SGBDR flexible, Oracle Server. Ce système permet de stocker et gérer des
données, avec tous les avantages que procure une structure relationnelle combinée à PL/SQL,
moteur permettant de stocker et d'exécuter des unités de programme.
Oracle Server permet aux utilisateurs des accès optimisés aux données. Il inclut des fonctions
de sécurité qui contrôlent les accès à la base de données et son utilisation. Il comprend aussi des
mécanismes de verrouillage qui assurent la cohérence et la protection des données.
Les applications Oracle peuvent être exécutées sur le même ordinateur qu' Oracle Server.

2
Mais il est également possible d'exécuter les applications sur un ordinateur local et Oracle Server
sur un autre système (architecture client serveur). Un tel environnement client serveur peut faire
appel à de nombreuses ressources de traitement. Par exemple, une application de réservation de
billets d'avion peut tourner sur un PC client, avec accès aux données de vol gérées par un système
Oracle Server sur ordinateur central.

Oracle 8

Oracle 8 Server est la première base de données objet développée par Oracle. Les fonctions
de modélisation des données d'Oracle7 Server ont été étendues pour prendre en charge le nouveau
modèle de base de données relationnelle objet.
Oracle8 Server fonctionne avec un nouveau moteur permettant la programmation orientée
objet, les types de données et les objets de gestion complexes, ainsi qu'une compatibilité totale avec
le monde relationnel.
Oracle 8 Server offre de nombreuses améliorations par rapport à Oracle7 Server. Notamment,
il inclut de nombreuses fonctions destinées à augmenter les performances et fonctionnalités des
applications de traitement transactionnel en ligne (OLTP) comme, par exemple, un meilleur partage
des structures de données lors de l'exécution, des buffers et des caches de taille supérieure, et des
contraintes dont il est désormais possible de différer la vérification.
Les applications de data Warehouse vont bénéficier d'améliorations telles que l'exécution en
parallèle des opérations d'insertion, de mise à jour et de suppression, le partitionnement et
l'optimisation des requêtes en parallèle. Parce qu'il fonctionne dans le cadre de l'architecture NCA
(Network Computing Architecture), Oracle8 Server est compatible avec les applications client
serveur et Internet distribuées et multi niveaux.
Oracle8 Server accepte des dizaines de milliers d'utilisateurs simultanés, prend en charge
jusqu'à 512 péta octets et peut manipuler n'importe quel type de données (texte, spatial, image, son,
image vidéo, série chronologique, mais aussi des données structurées traditionnelles)
Les nouvelles versions 8i et 9i ont apportées beaucoup d'améliorations à ce niveau et surtout
au niveau ouverture vers Internet.

La Solution Complète Oracle

Le système de gestion de bases de données relationnelles Oracle est le principal produit de
l'offre Oracle. Il comprend Oracle Server ainsi que divers outils destinés à aider l'utilisateur pour la
mise à jour, le contrôle et l'utilisation des données. Le dictionnaire de données Oracle est l'un des
composants les plus importants de Oracle Server. Il est constitué d'un ensemble de tables et de vues
permettant un accès en lecture seule à la base de données. Le SGBDR gère des tâches telles que les
suivantes :
 Stockage et définition des données
 Contrôle et restriction des accès aux données
 Sauvegarde et restauration
 Interprétation des ordres SQL et PL/SQL
Remarque : PL/SQL est un langage procédural qui complète les possibilités de SQL avec une
logique d'application.
Les ordres SQL et PL/SQL sont systématiquement utilisés pour rechercher et manipuler les

3
données stockées dans la base Oracle. Cependant, dans le cadre de programmes d'application, il
arrive souvent que vous accédiez à la base de données sans utiliser directement SQL ou PL/SQL :
vous vous contentez de cliquer sur un bouton ou de cocher une case par exemple, mais en fait, les
applications utilisent implicitement SQL ou PL/SQL lorsqu'elles exécutent la requête.
SQL* PIus est un outil Oracle qui reconnaît les ordres SQL et PL/SQL et en lance l'exécution
à partir du serveur. Il a son propre langage de commande.

Les Ordres SQL

Le SQL d'Oracle est conforme aux standards reconnus. La société Oracle garantit la
conformité future avec l'évolution des standards en faisant participer certains de ses principaux
collaborateurs aux comités de normalisation SQL. Les deux organismes reconnus sont l'ANSI
(American Standards Institute) et l'ISO (International Standards Organization). Tous deux ont
adopté SQL comme langage standard des bases de données relationnelles

Ordre Description
SELECT Extraction des données de la base de données.
INSERT Respectivement, ajout, modification et suppression de lignes
UPDATE dans les tables de la base de données. L'ensemble est appelé
DELETE langage de manipulation des données (LMD).

CREATE Initialisation, modification et suppression des structures de

ALTER données dans une table. L'ensemble est appelé langage de
DROP définition des données (LDD).
RENAME
TRUNCATE
COMMIT Gestion des modifications apportées par les ordres LMD.
ROLLBACK Les modifications apportées aux données peuvent être
SAVEP01NT regroupées en transactions logiques.
GRANT Accorde nu retire les droits d'accès à la base de données Oracle
REVOKE et aux structures qu'elle renferme. L'ensemble est appelé
langage de contrôle des données ( LCD).

L'ordre SELECT élémentaire :

Possibilités de l'ordre SQL SELECT

Un ordre SELECT permet d'extraire des informations d'une base de données. L'utilisation
d'un ordre SELECT offre les possibilités suivantes :
 Sélection : SQL permet de choisir dans une table, les lignes que l'on souhaite ramener au moyen
d'une requête. Divers critères de sélection sont disponibles à cet effet.
 Projection : SQL permet de choisir dans une table, les colonnes que l'on souhaite ramener au
moyen d'une requête. Vous pouvez déterminer autant de colonnes que vous le souhaitez.
 Jointure : SQL permet de joindre des données stockées dans différentes tables, en créant un
lien par le biais d'une colonne commune à chacune des tables.
4
Dans sa forme la plus simple, l'ordre SELECT comprend :
 Une clause SELECT précisant les colonnes à afficher
 Une clause FROM spécifiant la table qui contient les colonnes indiquées dans la clause
SELECT
Syntaxe :
SELECT [DISTINCT] {* , column [alias],...}
FROM table ;

SELECT liste d'une ou plusieurs colonnes

DISTINCT suppression des doublons
* sélection de toutes les colonnes
column sélection de la colonne désignée
alias attribue des en-têtes différents aux colonnes sélectionnées
FROM table spécifie la table qui contient les colonnes

Ecriture des Ordres SQL

En suivant les règles et indications simples ci-dessous, vous pourrez créer des ordres
corrects, simples à lire et à éditer.
Sauf indication contraire, les ordres SQL peuvent être écrits indifféremment en majuscules
ou en minuscules.
 Les ordres SQL peuvent être saisis sur plusieurs lignes.
 Les mots-clés ne doivent pas être scindés sur deux lignes différentes, ni abrégés.
 Les clauses se placent généralement sur des lignes distinctes pour en faciliter la lecture et
l'édition.
 L'utilisation de tabulations et d'indentations permet une meilleure lisibilité.
 Généralement, les mots-clés sont saisis en majuscules, et tous les autres termes, tels que les
noms de tables et de colonnes, sont saisis en minuscules.
 Dans le SQL*Plus, un ordre SQL est saisi au prompt SQL, et les lignes qui suivent sont
numérotées. C'est ce qu'on appelle le buffer SQL. Il ne peut y avoir qu'un seul ordre courant à la
fois dans le buffer.

Exécution d'Ordres SQL

 Placer un point-virgule (;) à la fin de la dernière clause.
 Placer un slash (/) seul sur la dernière ligne du buffer.
 Placer un slash (/) au prompt SQL.
 Entrer la commande RUN SQL*Plus au prompt SQL.

Sélection de Toutes les Colonnes et de Toutes les Lignes

Pour afficher toutes les colonnes d'une table, placez un astérisque à la suite du mot-clé
SELECT (*). Dans l'exemple de la diapositive, la table des départements (DEPT) comporte trois
colonnes : DEPTNO, DNAME et LOC. La table comporte également quatre lignes, une pour
chaque département.
Vous pouvez aussi afficher toutes les colonnes de la table en les énumérant toutes à la suite

5
du mot-clé SELECT. Par exemple, l'ordre SQL suivant affiche toutes les colonnes et toutes les
lignes

SQL> SELECT deptno, dname, loc

2 FROM dept;

Sélection de Colonnes Spécifiques et de Toutes les Lignes

L'ordre SELECT peut être utilisé pour afficher des colonnes spécifiques de la table. Pour
cela, indiquez les noms de colonnes séparés par des virgules. L'exemple ci-dessus affiche tous les
numéros de département de la table DEPT, ainsi que leur localisation.
Dans la clause SELECT, indiquez les colonnes dans l'ordre où vous souhaitez qu'elles vous soient
rapportées. Par exemple, si vous voulez que la colonne "LOC" soit placée avant la colonne
"DEPTNO", utilisez l'ordre suivant :

SQL> SELECT loc, deptno

2 FROM dept ;

LOC DEPTNO
-------------------------------------------------------
NEWYORK 10
DALLAS 20
CHICAGO 30
BOSTON 4

Expressions Arithmétiques
Si nécessaire, vous pouvez modifier l'affichage des données, effectuer des calculs ou étudier
différentes hypothèses au moyen d'expressions arithmétiques. Une expression arithmétique peut
contenir des noms de colonnes, des valeurs numériques constantes et des opérateurs arithmétiques.

Opérateurs Arithmétiques
Ce paragraphe présente les opérateurs arithmétiques disponibles dans SQL. Vous pouvez les
utiliser dans n'importe quelle clause d'un ordre SQL, excepté dans la clause FROM.
Les opérateurs disponibles sont : *, /, +, -

Les Valeurs NULL

Lorsqu'il manque une valeur dans une colonne sur une ligne, la valeur est dite NULL.
Une valeur NULL est une valeur non disponible, non affectée, inconnue ou inapplicable. Une
valeur NULL est différente du zéro ou de l'espace. Le zéro est un chiffre et l'espace est un caractère.
Quel que soit le type de données d'une colonne, celle-ci peut contenir des valeurs NULL,
excepté lorsque cette colonne a été définie comme NOT NULL ou comme CLE PRIMAIRE lors de
sa création.

6
 Lorsqu'une valeur NULL est utilisée dans une expression arithmétique, le résultat de cette
expression est NULL. Si vous essayez de diviser par zéro, vous obtenez une erreur. En revanche, si
vous divisez un nombre par NULL, le résultat sera NULL ou inconnu.

Les Alias de Colonnes

Lors de l'affichage des résultats d'une requête, SQL*Plus prend le nom de la colonne
sélectionnée comme en-tête de colonne. La plupart du temps, cet en-tête n'est pas explicite et par
conséquent est difficile à comprendre. L'alias de colonne permet de modifier l'en-tête d'une colonne.
On spécifie l'alias à la suite de la colonne dans la liste SELECT en utilisant le caractère
espace comme séparateur. Par défaut, les en-têtes de colonne sont en majuscules. Si l'alias
contient des espaces ou des caractères spéciaux (tels que # ou $), ou si la différence entre
majuscules et minuscules est importante, placez l'alias entre guillemets (" ").

Exemple :
Sélect ename as name,sal salary
From Emp;

L'Opérateur de Concaténation
L'opérateur de concaténation (||) permet de concaténer des colonnes à d'autres colonnes, à des
expressions arithmétiques ou à des valeurs constantes afin de créer une expression caractère.
Les colonnes situées de part et d'autre de l'opérateur se combinent pour donner une colonne
unique lors de la restitution des données.

Chaînes de Caractères Littérales

Un littéral est un caractère, une expression, ou un nombre quelconque inclus dans la liste
SELECT, et qui n'est ni un nom de colonne ni un alias de colonne. Il apparaît sur chaque ligne
ramenée. Des chaînes de texte littérales en fermat libre peuvent être intégrées au résultat de la
requête. Elles sont traitées comme les colonnes dans une liste SELECT.
Les littéraux date et alphanumérique doivent être inclus entre simples quotes (' '), mais pas les
littéraux numériques.

SQL> SELECT ename || ': 1 Month salary = ' || sal Monthly

2 FROM emp;

7
 MONTHLY
----------------------------------------------------------------------------------------------------------------------------

KING: 1 Month salary = 5000

BLAKE: 1 Month salary = 2850
CLARK: 1 Month salary = 2450
JONES: 1 Month salary = 2975
MARTIN: 1 Month salary = 1250
ALLEN: 1 Month salary = 1600
TURNER: 1 Month salary = 1500

14 rows selected.

SQL et SQL*Plus
SQL est un langage de commande qui permet de communiquer avec Oracle Server quels que
soient l'outil ou l'application utilisés. Le SQL d'Oracle comprend de nombreuses extensions.
SQL*Plus est un outil Oracle possédant son propre langage de commande qui reconnaît les
ordres SQL et les envoie à Oracle Server pour qu'ils soient exécutés. Lorsque vous saisissez
un ordre SQL, celui-ci est stocké dans un espace mémoire appelé buffer SQL où il reste
jusqu'à la saisie d'un nouvel ordre.

Caractéristiques de SQL
 Peut être employé par de nombreux utilisateurs, y compris ceux ayant peu ou pas d'expérience
de programmation

 C'est un langage non procédural

 II réduit le temps de création et de mise à jour des systèmes

 C'est un langage proche de la langue anglaise

Caractéristiques de SQL*Plus

 Accepte la saisie d'ordres ad hoc

 Accepte l'exécution d'ordres SQL à partir de fichiers

 Contient un éditeur de lignes pour la modification des ordres SQL

 Contrôle les paramètres d'environnement

8
 Formate les résultats des requêtes dans des états élémentaires

 Accède aux bases de données locales ou distantes

La table ci-dessous présente un comparatif de SQL et SQL*Plus.

SQL SQL*Plus
Langage de communication avec Oracle Reconnaît les ordres SQL et les envoie au
Server pour accéder aux données serveur

Basé sur le SQL standard ANSI (American Interface propriétaire Oracle pour l'exécution
National Standards Institute) des ordres SQL
Manipule les données et les définitions de Les commandes SQL*Plus ne permettent pas
tables dans la base de données \a manipulation de valeurs dans la base de
données
Est entré dans le buffer SQL sur une ou Entrée des lignes une par une, pas de stockage
plusieurs dans le buffer SQL
lignes
Ne comporte pas de caractère de continuation Le tiret (-) est utilisé comme caractère de
continuation lorsque la commande fait plus
d'une ligne
Ne peut être abrégé Peut être abrégé
Comporte un caractère de fin pour l'exécution Ne nécessite pas de caractère de fin ; les
immédiate des commandes commandes sont immédiatement exécutées
Utilise des fonctions pour effectuer les Utilise des commandes pour formater les
formatages données

Affichage de la Structure d'une Table

Dans SQL*Plus, vous pouvez afficher la structure d'une table à l'aide de la commande
DESCRIBE. Cette commande affiche les noms de colonnes et les types de données, ainsi qu'une
information indiquant si les colonnes doivent obligatoirement contenir des données ou non.
Syntaxe : DESC[RIBE] NOM_TABLE ;

Commandes de Fichiers SQL* Plus

Les ordres SQL communiquent avec Oracle. Les commandes SQL*Plus contrôlent
l'environnement, formatent les résultats des requêtes et gèrent les fichiers. Pour gérer les fichiers,
vous disposez des commandes décrites dans le tableau suivant :

9
 Commande Description
SAV[E] filename [.ext] Sauvegarde dans un fichier le contenu actuel du buffer
[REP[LACE]APP[END]] SQL. APPEND ajoute le contenu à un fichier existant ;
REPLACE écrase un fichier existant. L'extension par
défaut est .sql.
GET filename [.ext] Charge le contenu d'un fichier déjà sauvegardé dans le
buffer SQL. Par défaut, l'extension du fichier est .sql.
STA[RT]y;/e/îawe [.ext] Exécute un fichier de commandes déjà sauvegardé.
@ filename Exécute un fichier de commandes déjà sauvegardé
(identique à START).
ED[IT] Appelle l'éditeur et sauvegarde le contenu du buffer
dans un fichier nommé afîedt.buf.
ED[IT] [fîlename[.ext]] Appelle l'éditeur et édite le contenu d'un fichier déjà
sauvegardé.
SPO[OL] [fîlename[.ext]| OFF| Stocke les résultats de requêtes dans un fichier. OFF
OUT] ferme le fichier spool. OUT ferme le fichier spool et
envoie les résultats du fichier à l'imprimante du
système.
EXIT Quitte SQL*Plus.

SELECTION ET TRI DES DONNEES :

Lors d'une recherche de données dans une base de données, il est parfois nécessaire de
restreindre le nombre de lignes retournées ou de préciser l'ordre d'affichage de ces lignes.

SELECT [DISTINCT] {* , column [alias]....}

FROM table
[WHERE Condition] ;

Vous pouvez limiter le nombre de lignes ramenées par la requête au moyen de la clause
WHERE. La clause WHERE permet de spécifier une condition à satisfaire. Elle se place
immédiatement après la clause FROM
Syntaxe :

WHERE limite la requête aux lignes remplissant la condition spécifiée

condition se compose de noms de colonnes, d'expressions, de constantes et d'un opérateur de

comparaison

La clause WHERE peut comparer des valeurs dans des colonnes, des littéraux, des expressions
arithmétiques ou des fonctions. Elle se compose de trois éléments :
 Nom de colonne
 Opérateur de comparaison
 Nom de colonne, constante ou liste de valeurs

Chaînes de Caractères et Dates

Dans la clause WHERE, les chaînes alphanumériques et les dates doivent être incluses entre
simples quotes (' '), mais pas les constantes numériques. Toutes les recherches de caractères
tiennent compte des majuscules et des minuscules.
10
 SQL> SELECT ename, empno, job, deptno
2 FROM emp
3 WHERE job='CLERK';
Oracle stocke les dates dans un certain format numérique interne, représentant le siècle,
l'année, le mois, le jour, les heures, les minutes et les secondes. Le format de date par défaut est
DD-MON-YY.
Remarque : Les valeurs numériques ne sont pas incluses entre simples quotes.

Opérateurs de Comparaison
Ces opérateurs de comparaison s'utilisent dans les conditions qui comparent deux
expressions. Dans la clause WHERE, ils s'utilisent de la façon suivante:
Syntaxe
... WHERE expr operator value

Exemples

... WHERE hiredate = ' 01-JAN-95'

... WHERE sal>=1500
... WHERE enanie= SMITH

Autres Opérateurs de Comparaison :

BETWEEN ... AND ... : Compris entre ... et... (bornes comprises)
IN(liste) : Correspond à une valeur dans la liste
LIKE : Ressemblance partielle de chaînes de caractère
IS NULL : Correspond à une valeur NUL l
Opérateurs Logiques : AND , OR NOT

La Clause ORDER BY
Les lignes trouvées par une requête sont ramenées dans un ordre quelconque. La clause
ORDER BY sert à trier les ligne. Si vous l'utilisez, vous devez la placer en dernier dans l'ordre
SELECT. Vous pouvez spécifier une expression ou un alias sur lesquels le tri sera effectué.
Syntaxe

SELECT expr
FROM table
[WHERE condition (s)]
[ORDER BY {column, expr} [ASC | DESC]];

11
 Où : ORDER BY précise l'ordre d'affichage des lignes trouvées.
ASC classe les lignes en ordre croissant. C'est l'ordre par défaut.
DESC classe les lignes en ordre décroissant.
Si la clause ORDER BY n'est pas utilisée, l'ordre de tri est indéfini et il peut arriver que Oracle
n'extraie pas deux fois de suite les lignes d'une même requête dans un ordre identique. Pour afficher
les lignes dans un ordre spécifique, il faut spécifier une clause ORDER BY.

Afficher des Données Issues de Plusieurs Tables

On a parfois besoin d'obtenir des données de plusieurs tables. Par exemple :

 EMPNO appartient à la table EMP.
 DEPTNO appartient aux tables EMP et DEPT.
 LOC appartient à la table DEPT.
Pour obtenir cet état, il faut relier les tables EMP et DEPT et accéder aux données de ces
deux tables.

Définition des Jointures

Pour obtenir des données appartenant à différentes tables de la base de données, vous devez
utiliser une condition de jointure. Les lignes d'une table peuvent être reliées aux lignes d'une autre
table en fonction de valeurs communes existant dans des colonnes se correspondant, en général la
colonne clé primaire et la colonne clé étrangère.
Pour afficher les données issues de deux ou plusieurs tables, écrivez une condition de
jointure simple dans la clause WHERE.
Syntaxe :
table.column indique la table et la colonne d'où sont extraites les données
table1.columnl = table2.column2 représente la condition qui joint (ou lie) les tables
entre-elles

 Lorsque vous écrivez un ordre SELECT pour joindre des tables, il est recommandé, par souci
de clarté et de facilité d'accès, de placer le nom de la table avant le nom de la colonne.

 Lorsque le même nom de colonne apparaît dans plusieurs tables, il doit obligatoirement être
préfixé par le nom de la table.

 Pour joindre n tables entre elles, il faut au minimum (n-1) conditions de jointure. C’est
pourquoi, par exemple, trois jointures au moins sont nécessaires pour lier quatre tables. Cette
règle ne s'applique pas si votre table contient une clé primaire concaténée, auquel cas il faut
plus d'une colonne pour permettre d'identifier chaque ligne de manière unique.

Produit Cartésien
Lorsqu'une condition de jointure est incorrecte ou tout simplement omise, on obtient un
produit cartésien dans lequel sont affichées toutes les combinaisons de lignes. Toutes les lignes de
la première table sont jointes à toutes les lignes de la seconde.
 Un produit cartésien fournit en général un nombre important de lignes, donnant un résultat
rarement exploitable. C'est pourquoi il faut toujours inclure une condition de jointure correcte
dans une clause WHERE, à moins que vous n'ayez réellement besoin de combiner toutes les
lignes de toutes les tables.

Types de Jointures
II existe deux principaux types de conditions de jointure :
12
 Les équijointures
 Les non-équijointures
Les autres méthodes de jointures sont les suivantes :
 Jointures externes
 Autojointures
 Les opérateurs ensemblistes

Equijointures
Pour déterminer le département auquel appartient un employé, vous devez comparer les
valeurs de la colonne DEPTNO de la table EMP avec les valeurs de la colonne DEPTNO de la table
DEPT. La relation établie entre les tables EMP et DEPT est une équijointurc : les valeurs de la
colonne DEPTNO appartenant aux deux tables doivent être identiques. Ce type de relation fait
souvent appel aux clés primaires et étrangères.

Remarque : les équijointures sont aussi appelées jointures simples ou jointures internes.

Exemple : Select Emp.empno, emp.ename, emp.sal, dept.dname,

dept.loc
From Emp,Ucpt
Where Dept.deptno=Emp.deptno;

Différenciation des Noms de Colonne

Pour éviter toute ambiguïté, vous devez préfixer dans la clause WHERE les noms de colonne
avec le nom de la table. Ainsi, sans autre précision, la colonne DEPTNO peut tout aussi bien
appartenir à la table DEPT qu'à la table EMP. Il faut donc ajouter le préfixe de table pour pouvoir
exécuter la requête.
Lorsque aucune colonne n'est commune aux deux tables, la qualification n'est pas
indispensable. Toutefois, vous obtiendrez de meilleurs résultats avec les préfixes de table, car ils
indiquent précisément à Oracle où il peut trouver les colonnes.
La nécessité de qualifier les noms de colonne s'applique aussi quand une colonne ambiguë
est présente dans d'autres clauses, par exemple dans les clauses SELECT ou ORDER- BY.

Conditions de Recherche Supplémentaires

Outre la jointure, vous pouvez spécifier des critères supplémentaires dans la clause WHERE.
Par exemple, pour afficher !e matricule, !e nom, le numéro de département et la localisation de
l'employé King, vous devez ajouter une condition dans la clause WHERE.

SQL> SELECT empno, ename, emp.deptno, loc

2 FROM emp, dept
3 WHERE emp.deptno = dept.deptno
4 AND INITCAP(ename) = 'King';

Alias de Table
La qualification des noms de colonne à l'aide des noms de table peut prendre beaucoup de
temps, en particulier si les noms de table sont longs. Vous pouvez substituer des alias de table aux
noms de table. De la même manière qu’un alias de colonne renomme une colonne, un alias de table
donne un nouveau nom à une table. Les alias de table permettent ainsi de réduire le volume du code
13
SQL et donc, de gagner de la place en mémoire.
Notez la manière dont les alias de table sont identifiés dans la clause FROM de l'exemple. Le
nom de la table spécifié en entier est suivi d'un espace puis de l'alias de table. E est l'alias de la table
EMP, et D l'alias de la table DEPT.

Conseils
 Bien qu'un alias de table puisse compter jusqu'à 30 caractères, il est préférable qu'il soit le plus
court possible.
 Lorsqu'un alias de table est substitué à un nom de table dans la clause FROM, cette substitution
doit s'opérer dans la totalité de l'ordre SELECT.
 Choisissez de préférence des alias "parlants".
 Un alias de table ne s'applique que dans l'ordre SELECT courant.

Non-Equijointures
La relation entre la table EMP et la table SALGRADE est une non-équijointure car aucune
colonne de la table EMP ne correspond directement à une colonne de la table SALGRADE. La
relation existant entre les deux tables est la suivante : les valeurs de la colonne SAL de la table
EMP sont comprises entre celles des colonnes LOSAL et HISAL de la table SALGRADE. Il faut
donc utiliser un autre opérateur que le signe égal (=) pour effectuer une jointure.
Exemple : Select e.ename, e.sal, s.grade
From Emp e, Salgrade s
Where e.sal between s.losal and hisal;

Affichage d'Enregistrements sans Lien Direct, au moyen de Jointures Externes

Lorsqu'une ligne ne satisfait pas à une condition de jointure, elle n'apparaît pas dans le
résultat de la requête. Par exemple, quand on fait l'équijointure entre les tables EMP et DEPT, le
département OPERATIONS n'apparaît pas car personne ne travaille dans ce département. Il est
néanmoins possible de ramener la ou les lignes manquantes en plaçant un opérateur de jointure
externe dans la condition de jointure. Cet opérateur se présente sous la forme d'un signe plus inclus
entre parenthèses, et se place du "côté" de la jointure où l'information est incomplète. Il crée une ou
plusieurs lignes NULL, auxquelles une ou plusieurs lignes de la table complète peuvent être liées.
Syntaxe :
Select table.column, table.column
From tablel, table2
Where table1 .column(+)=table2.column
{ou Where table l.column=table2.column(+)}
tablel.column = condition qui joint (ou lie) les tables entre-elles.
table2.column (+) symbole de jointure externe; se place d'un côté ou de l'autre de la condition de
la clause WHERE, jamais des deux côtés. Placez le symbole de jointure externe après le nom de la
colonne appartenant à la table où manquent les lignes correspondantes

Restrictions Applicables aux Jointures Externes

 L'opérateur de jointure externe ne peut être placé que d'un seul côté de l'expression, à savoir
le côté où l'information manque. Il permet de ramener les lignes d'une table n'ayant pas de
jointure directe avec l'autre table.
 Une condition comportant une jointure externe ne peut pas utiliser l'opérateur IN ni être liée
à une autre condition par l'opérateur OR,

14
Liaison d'une Table à Elle-même
II se peut que vous ayez besoin de relier une table à elle-même. Ici, pour retrouver le nom du
manager de chaque employé, il faut que la table EMP soit reliée à elle-même. Par exemple,
pour retrouver le nom du manager de l'employé Blake, vous devez :
 Trouver Blake dans la table EMP en cherchant dans la colonne ENAME
 Trouver le matricule du manager de Blake en cherchant dans la colonne MGR.
 Trouver le nom du manager dont le matricule est 7839 dans la colonne EMPNO, puis regarder
le nom correspondant dans la colonne ENAME. Le matricule 7839 appartient à King, donc King
est le manager de Blake.
Dans ce processus, vous utilisez la même table deux l'ois : la première, pour rechercher le
nom de Blake dans la colonne ENAME et la valeur correspondante dans la colonne MRG ; la
seconde, pour rechercher le matricule 7839 dans la colonne EMPNO et le nom correspondant
(King) dans la colonne ENAME.

Exemple :

Select worker.ename, managcr.ename

From emp worker, emp manager
Where workcr.mgr = manager.empno;
Fonctions SQL

Les fonctions représentent une caractéristique très puissante de SQL et sont utilisées pour :
 Effectuer des calculs sur des données
 Transformer des données
 Effectuer des calculs sur des groupes de lignes
 Formater des dates et des nombres pour l'affichage
 Convertir des types de données de colonnes
Les fonctions SQL acceptent les arguments et ramènent des valeurs.
Il existe deux types de fonctions :
 Les fonctions mono-ligne
 Les fonctions multi-ligne

Fonctions Mono-Ligne
Ces fonctions agissent sur une seule ligne à la fois et ramènent un seul résultat. Il existe
plusieurs types de fonctions mono-ligne. Ce chapitre décrit les quatre suivantes :
 Caractère
 Numérique
 Date
 Conversion

Fonctions Multi-Lignc
Ces fonctions manipulent des groupes de lignes et ramènent un seul résultat par groupe de
lignes

Fonctions caractère

15
 Les fonctions mono-ligne caractère acceptent des données caractère en entrée et ramènent
des données caractère ou numériques. Les fonctions caractère se divisent en deux groupes :
 Les fonctions de conversion majuscules/minuscules
 Les fonctions de manipulation des caractères

Fonction Modification
LO'WER(column|expression) Convertit les caractères alphabétiques en minuscules.
UPPER(COLUMN | expression) Convertit les caractères alphabétiques en majuscules.
INITCAP(column |expression) Convertit l'initiale de chaque mot en majuscule et les caractères
suivants
CONCAT(column1|expression1, en minuscules.
Concatène la première chaîne de caractère à la seconde. Equivaut
column2\expression2) à
l'opérateur de concaténation (||).
SUBS'TR(column\expression,m[,n]) Extrait une partie de la chaîne de caractères en commençant au
caractère situé à la position m et sur une longueur de n caractères.
Si m est une valeur négative, le décompte s'effectue dans le sens
inverse (à partir du dernier caractère de la chaîne).
Si n est omis, tous les caractères jusqu'à la fin de la chaîne sont
ramenés.
LENGTH(COLUMN| expression) Ramène le nombre de caractères d'une chaîne de caractères.
INSTR(COLUMN | expression, m) Ramène une valeur égale à la position du caractère m.
LPAD(co!umn\expression, n, Complète une chaîne de caractère sur la gauche avec la chaîne
'string ') 'string' pour parvenir à une longueur totale de n caractères.

Exemple :

SQL> SELECT ' The job title for ' || INITCAP (ename) || ' is '
2 || LOWER (JOB) as " EMPLOYEE DETAILS"
3 FROM emp;

SQL> SELECT empno, INITCAP (enamme), deptno

2 FROM emp
3 WHERE LOWER (ename) = 'blake' ;

SQL> SELECT ename, CONCAT (ename, job) , LENGTH (ename) , INSTR (ename , 'A' )
2 FROM ename
3 WHERE SUBSTR ( ename , 1, 1) = 'N' ;

Fonctions numériques
Les fonctions numériques utilisent et ramènent des valeurs numériques.

Fonction Modification

16
ROUND(column\expression. N) Arrondit la valeur de la colonne ou de l'expression à une
précision de 10 '".
Si n est positif, le nombre sera arrondi à n décimales.
Si n est omis, il n'y aura pas de décimale.
Si n est négatif, l'arrondi portera sur la partie du nombre
située à gauche de la virgule (dizaine, centaine...)
T'RUNC(column\expression,n) Tronque la valeur de la colonne ou de l'expression à une
précision de 10 -".
Si n est positif, le nombre sera tronqué à n décimales.
Si n est omis, il n'y aura pas de décimale.
Si n est négatif, ce sera la partie du nombre située à gauche
de la virgule (dizaine, centaine...) qui sera tronquée.
MOD(m,n) Ramène le reste de la division de m par n.

Format de Date Oracle

Oracle stocke les dates dans un format numérique interne représentant le siècle, l'année, le
mois, le jour, les heures, les minutes et les secondes. Le format d'entrée et d'affichage par défaut des
dates est DD-MON-YY. Les dates valides pour Oracle sont comprises entre le 1er janvier 4712
av.J.-C. et le 31 décembre 9999 apr.J.-C.

SYSDATE
SYSDATE est une fonction date qui permet d'obtenir la date et l'heure courante. SYSDATE s'utilise
de la même façon qu'un nom de colonne quelconque. Il est usuel d'interroger la table "factice"
DUAL.

DUAL
La table DUAL appartient à l'utilisateur SYS, mais tous les utilisateurs peuvent y accéder.
Elle contient une seule colonne, DUMMY, et une seule ligne contenant la valeur X. La table DUAL
est utile lorsque vous souhaitez ramener une valeur une seule fois, par exemple, la valeur d'une
constante, d'une pseudo-colonne ou d'une expression qui ne dépend pas d'une table de données
utilisateur.

Exemple
Afficher la date courante au moyen de la table DUAL.
SQL> SELECT SYSDATE
2 FROM DUAL;

Opérations Arithmétiques sur les Dates

Comme la base de donnée stocke les dates en tant que données numériques, il est possible
d'effectuer des calculs tels que l'addition ou la soustraction au moyen d'opérateurs arithmétiques. Il
est possible d'ajouter et soustraire des constantes numériques aussi bien que des dates.
Les opérations possibles sont les suivantes

Opération Résultat Description

date + nombre de jours Date Ajoute un certain nombre de jours à une date

date - nombre de jours Date Soustrait un certain nombre de jours d'une date

17
date - date Nombre Soustrait une date d'une autre
de jours

date + (nombre d'heures)/ Date Ajoute un certain nombre d'heures à une date
24

Fonctions Date
Les fonctions date s'appliquent aux données de type DATE. Toutes les fonctions date
ramènent une valeur de type DATE, à l'exception de MONTHS_BETWEEN qui ramène une valeur
numérique.
 MONTHS_BETWEEN (date l, date 2) : Donne le nombre de mois situés entre une date (date l)
et une autre date (date2). Le résultat peut être positif ou négatif. Si date l est postérieure à date2,
le résultat est positif; si date l est antérieure à date l, le résultat est négatif. La partie non entière
du résultat représente une portion de mois.
 ADD_MONTHS (date, n) : Ajoute un nombre n de mois à une date. n doit être un nombre entier
et peut être négatif.
 NEXT_DAY (date, 'char') : Fournit la date de la première occurrence du jour spécifié ('char')
après la date fournie, char peut être, soit un numéro de jour de semaine, soit une chaîne de
caractères.
 LAST_DAY(date) : Indique la date du dernier jour du mois auquel appartient la date indiquée.
 ROUND (date[, ‘fmt’]) : Ramène la date, arrondie à l'unité précisée par le modèle de format
fmt. Lorsque fmt est omis, la date est arrondie au jour le plus proche.
 TRUNC(date[, ‘fmt’]) : Ramène la date, tronquée à l'unité précisée par le modèle de format fmt.
Lorsque fmt est omis, la date est tronquée au jour.

Exemple :
Pour tous les employés ayant moins de 200 mois d'ancienneté, affichez les données suivantes: le
matricule, la date d'embauche, le nombre de mois d'ancienneté, la date correspondant à la révision
de salaire après 6 mois, le premier vendredi suivant la date d'embauche et le dernier jour du mois
d'embauche.
SQL> SELECT empno, hiredate,
2 MONTHS_BETWEEN(SYSDATE, hiredate) TENURE,
3 ADD_MONTHS(hiredate, 6) REVIEW,
4 NEXT_DAY(hiredate, 'FRIDAY'), LAST_DAY(hiredate)
5 FROM emp
6 WHERE MONTHS_BETWEEN (SYSDATE, hiredate)<200;

Les fonctions ROUND et TRUNC peuvent être utilisées avec des valeurs de type numérique ou
date. Utilisées avec des dates, ces fonctions arrondissent ou tronquent au modèle de format spécifié.
Vous pouvez par conséquent arrondir les dates au premier jour du mois ou de l'année les plus
proches.

Exemple
Afficher les dates d'embauche de tous les employés ayant commencé en 1987. Affichez le
matricule, la date d'embauche et le mois de début d'activité en utilisant les fonctions ROUND et
TRUNC.
18
SQL> SELECT empno, hiredate,
2 ROUND(hiredate, 'MONTH'), TRUNC(hiredate, 'MONTH')
3 FROM emp
4 WHERE hiredate like '%87';

Fonctions de Conversion
II est possible d'utiliser des types de données ANSI, DB2 et SQL/DS, en plus des types de données
Oracle, pour définir les colonnes d'une table dans une base de données Oracle8.
Toutefois, Oracle Server convertit en interne ces types de données en types de données Oracle8.
Dans certains cas, Oracle peut accepter des données d'un type différent de celui normalement
attendu, sous réserve qu'Oracle Server puisse effectuer une conversion automatique de ces données.
Cette conversion de types de données est réalisée, soit de manière implicite par Oracle Server, soit
de manière explicite par l'utilisateur.
Les conversions implicites de types de données fonctionnent selon des règles que nous allons
expliquer dans les deux diapositives suivantes.
Les conversions explicites des types de données se font au moyen des fonctions de conversion, qui
convertissent une valeur d'un type de données en un autre type. En principe, le format de la fonction
suit la convention datatype TO datatype, le premier datatype étant le type de données d'entrée, le
second datatype étant le type de données restitué.
Remarque : Bien que la conversion implicite des types de données soit possible, il est recommandé
d'effectuer des conversions explicites afin d'assurer une meilleure efficacité des ordres SQL.

SQL offre trois fonctions pour convertir une valeur d'un certain type de données dans autre type.

Fonction Résultat
TO_CHAR(numher|date, ['fmt']) Convertit un nombre ou une date en une
chaîne de caractères de type VARCHAR2 et
de format fmt.
TO_NUMBER(char) Convertit une chaîne de caractères en
un nombre.
TO_DATE(char,['fmt']) Convertit une chaîne de caractères
représentant une date au format fmt en
une date Oracle. Lorsque fmt est omis, le
format est DD-MON-YY.

Affichage d'une Date dans un Format Spécifique

Nous avons vu que Oracle Server affiche les dates au format DD-MON-YY. La fonction
TO_ CHAR permet de convertir ces dates en un autre format de votre choix.

Conseils
 Le modèle de format doit être placé entre simples quotes et différencie les majuscules et
minuscules.

19
 Il peut comprendre tout élément valide de format date. N'oubliez pas de séparer la date et le
modèle de format par une virgule.

 Les noms de jours et de mois sont automatiquement complétés par des espaces.

 Pour supprimer les espaces de remplissage ou les zéros de tête, utilisez ['élément f m. fm signifie
fill mode, ou mode de remplissage.

 La colonne résultante a une largeur par défaut de 80 caractères.

 Vous pouvez redimensionner la largeur de la colonne résultante avec la commande SQL*Plus

COLUMN.

SQL> SELECT empno, TO_CHAR(hiredate, 'MM/YY') Month_Hired

2 FROM emp
3 WHERE ename = 'BLAKE';

Eléments de Format de Date Valides

SQL> SELECT ename, TO_CHAR(hiredate, ‘fmDay "of" Month YYYY’)

2 HIREDATE
3 FROM emp ;

Elément Description
SCC ou CC Siècle; le S fait précéder les dates av. J.C. d'un signe -
YYYY ou SYYYY Année; le S fait précéder les dates av. J.C. d'un signe -
YYY ou YY ou Y Les 3, 2 ou 1 derniers chiffres de l'année
Y,YYY Année avec une virgule insérée
IYYY, IYY, IY, 1 Les 4, 3, 2 ou 1 derniers chiffres de l'année (norme ISO)
SYEAR ou YEAR Année en toutes lettres ; le S fait précéder les dates av. J.C. d'un signe -
BC ou AD Respectivement, av. JC ou apr. JC
B.C. ou A.D. Respectivement, av. J.C. ou apr. J.C.
Q Numéro du trimestre
MM Mois exprimé avec 2 chiffres
MONTH Mois en toutes lettres complété par des blancs à concurrence de 9
caractères
MON 3 premières lettres du nom du mois
RM Numéro du mois en chiffres romains
WW ou W Numéro de la semaine dans l'année ou le mois
DDD ou DD ou D Numéro du jour dans l'année, le mois ou la semaine
DAY Nom du jour exprimé en toutes lettres et complété par des blancs à
concurrence de 9 caractères
DY 3 premières lettres du nom du jour
J Jour du calendrier Julien . nombre de jours depuis le 31 décembre 4713
av.J.C

Utilisation de la Fonction TO_CHAR avec les Nombres

20
 Pour pouvoir afficher des valeurs numériques sous la forme de chaînes de caractères, il
convient de convertir ces nombres en données de type caractère avec la fonction TO_CHAR, qui
transforme une valeur de type NUMBER en un type VARCHAR2. Cette technique est très utile
pour la concaténation.

Eléments de Format Numérique

Pour convertir un nombre en un type caractère, utilisez les éléments suivants :

Elément Description Exemple Résultat

9 Le nombre de 9 détermine la largeur maximum de F 999999 1234
affichage
0 Affichage des zéros de gauche 099999 001234
$ Si eue dollar flottant $999999 $1234
L Symbole monétaire 1,999999 FF 1234
. local flottant
Point décimal à l'emplacement spécifié 999999.99 1234.00
, Séparateur de milliers à l'emplacement 999,999 1,234
Ml spécifiéSigne moins à droite (valeurs négatives) 999999M1 1234-
PR Place les nombres négatifs entre crochets 999999PR <1234>
EEEE angulaires
Notation scientifique (indiquer 99.999EERE 1.234E403
obligatoirement quatre E)
V Multiplie par 10 n fois (n = nombre de 9999V99 123400
B chiffresN'affiche
après V)pas les zéros non significatifs. B9999.99 1234.00

Fonctions TO_NUMBER et TO_DATE

Vous pouvez convertir une chaîne de caractères en format numérique ou date en utilisant
respectivement les fonctions TO_NUMBER ou TO_DATE. Pour TO_DATE, le modèle de format à
spécifier est basé sur les éléments de format déjà expliqués. Ce modèle de format doit décrire le
format de la chaîne fournie en entrée.

Exemple
Afficher le nom et la date d'embauché de tous les employés entrés le "February 22, 198l".

SQL> SELECT ename, hiredate

2 FROM emp
3 WHERE hiredate = TO_DATE(‘February 22, 198l’, 'Month dd, YYYY') ;

La fonction NVL
Pour transformer une valeur NULL en une valeur réelle, on utilise la fonction NVL.

Syntaxe
NVL (exprl, expr2)
où : exprl est l'expression ou la valeur source susceptible de contenir une valeur NULL
expr2 est la valeur de remplacement pour la valeur NULL
La fonction NVL permet de convertir n'importe quel type de données, mais toutefois, la valeur de
remplacement doit être de même type que la valeur de l'expression exprl. Pour calculer la

21
rémunération annuelle de chaque employé, il faut multiplier son salaire mensuel par 12 puis ajouter
la commission au résultat obtenu.

SQL> SELECT ename, sal, comm, (sal*12) +NVL(comm, 0)

2 FROM emp;

La Fonction DECODE
La fonction DECODE permet de décoder les expressions de la même manière que l'ordre
logique IF-THEN-ELSE utilisé dans de nombreux langages. Elle décode l'expression après l'avoir
comparée à chacune des valeurs de recherche {search). Si l'expression est identique à search, le
résultat (result) est ramené.
Si la valeur par défaut (default) est omise, on obtient une valeur NULL chaque fois que
la colonne ou expression ne correspond à aucune valeur search.

Syntaxe :

Decode(col/expression, search 1, result 1 [, search2, result2, ...,][, default])

Fonctions de Groupe
Contrairement aux fonctions mono-ligne, les fonctions de groupe agissent sur des groupes de
lignes et donnent un résultat par groupe. Un groupe peut être une table entière ou un ensemble de
lignes d'une table.
Chaque fonction accepte un argument. La table suivante présente les différentes options de syntaxe
possibles.

Fonction Description
AVG([DISTINCT|ALL]n) Valeur moyenne de n, en ignorant les valeurs NULL
COUNT({*|[DISTINCT|ALL]expr} ) Nombre de lignes, où expr est différent de NULL. Le
caractère * comptabilise toutes les lignes sélectionnées y
compris les doublons et les lignes NULL
MAX([DISTINCT|ALL]expr) Valeur maximale de expr, en ignorant les valeurs NUL
MIN([DISTINCT|ALL]expr) Valeur minimale de expr, en ignorant les valeurs NULL
STDDEV([DISTINCT|ALL]x) Ecart standard de n, en ignorant les valeurs NULL
SUM([DISTINCT|ALL]n) Somme des valeurs de n, en ignorant les valeurs NULL
VARIANCE([DISTINCT|ALL]n) Variance de n, en ignorant les valeurs NULL

Conseils pour l'Utilisation des Fonctions de Groupe

 Avec DISTINCT, la fonction ne prend en compte que les valeurs distinctes ; avec ALL, elle
tient compte de toutes les valeurs \ compris les doublons. La valeur par défaut est ALL, par
conséquent il n'est pas nécessaire de la spécifier

 Si expr est spécifié, les différents types de données possibles pour les arguments sont CHAR,
VARCHAR2, NUMBER ou DATE

 Toutes les fonctions de groupe, à l'exception de COUNT(*), ignorent les valeurs NULL. Pour
substituer une valeur à une valeur NULL, utilisez la fonction NVL. Vous pouvez utiliser les
22
 fonctions AVG, SUM, MIN et MAX avec des colonnes de données numériques. Vous pouvez
utiliser les fonctions MAX et MIN pour tous les types de données

Fonctions de Groupe et Valeurs Null

Toutes les fonctions de groupe, à l'exception de COUNT (*), ignorent les valeurs NULL des
colonnes

Groupes de Données

Il est parfois nécessaire de diviser les informations d'une table en groupes plus petits. Pour
cela, il faut utiliser la clause GROUP BY.
Vous pouvez utiliser la clause GROUP BY pour diviser une table en groupes de lignes. Vous
pouvez alors utiliser les fonctions de groupe pour effectuer des calculs statistiques sur chaque
groupe.
Syntaxe :

SELECT expr
FROM table
[WHERE condition (s)]
[Group by group_by_expression]
[ORDER BY {colunrn, expr} [ASC|DESC]];

groupe_by_expression spécifie les colonnes dont les valeurs déterminant les différents groupes

Conseils
 Lorsque vous intégrez une fonction de groupe dans une clause SELECT, vous ne pouvez pas en
même temps sélectionner des résultats individuels, à moins que la colonne individuelle ne soit
mentionnée dans la clause GROUP BY. Si vous omettez de spécifier un de ces colonnes, un
message d'erreur s'affichera.
 Avec la clause WHERE, vous pouvez exclure des lignes avant de créer des groupes.
 Vous devez inclure les "column" de la liste SELECT dans la clause GROUP BY.
 Vous ne pouvez pas utiliser l'alias de colonne dans la clause GROUP BY.
 Par défaut, les lignes sont triées dans l'ordre croissant des colonnes incluses dans la liste
GROUP BY. Vous pouvez changer cet ordre en utilisant la clause ORDER BY.

Exemple :

SQL> SELECT deptno, AVG(sal)

2 FROM emp
3 GROUP BY deptno
4 ORDER BY AVG(sal);

Sous-groupes
II est parfois nécessaire d'obtenir des résultats pour des sous-groupes de lignes.

Note :
Lorsque vous utilisez la clause GROUP BY, pensez à inclure dans cette clause toutes les
colonnes de la liste SELECT qui ne figurent pas dans les fonctions de groupe.

23
Exclusion de Groupes
De la même manière que vous utilisez la clause WHERE pour limiter les lignes que vous
sélectionnez, vous pouvez utiliser la clause HAVING pour restreindre des groupes.

La Clause HAVING
Utilisez la clause HAVING pour indiquer les groupes que vous souhaitez afficher.

Syntaxe :
SELECT expr
FROM table
[WHERE condition (s)]
[Group by group_by_expression]
[Having group_condition]
[ORDER BY {column, expr} [ASC|DESC]];
où group_condition limite les groupes de lignes ramenés aux groupes pour lesquels la
condition spécifiée est VRAIE
Oracle Server opère de la manière suivante avec la clause HAVING :
 Les lignes sont groupées.
 La fonction de groupe s'applique au groupe.
 Les groupes qui correspondent aux critères de la clause HAVING sont affichés.

SQL> SELECT deptno, AVG(sal)

2 FROM emp
3 GROUP BY deptno
4 HAVING MAX(sal) > 2900;

Opérateurs Ensemblistes ;
Les opérateurs ensemblistes combinent les résultats de deux ou plusieurs requêtes en un seul résultat.
Une requête composée est une requête contenant des opérateurs ensemblistes.

L'opérateur Ramène
INTERSECT Toutes les lignes communes aux deux requêtes. INTERSECT
combine les deux requêtes et ramène les lignes du premier
ordre SELECT identiques aux lignes du second ordre
SELECT.
UNION Toutes les lignes distinctes ramenées par les deux requêtes.
UNION ALL Toutes les lignes sélectionnées par les deux requêtes, y
compris les doublons.
MINUS Toutes les lignes sélectionnées par le premier ordre SELECT
moins les lignes sélectionnées dans le second ordre SELECT.

Tous les opérateurs ensemblistes ont la même priorité. Si un ordre SQL en contient plusieurs,
la base de données les évalue de gauche à droite ou de haut en bas, si aucune parenthèse
n'indique explicitement un autre ordre. Pour être conforme aux nouvelles normes SQL, la
prochaine version de la base de données accordera une priorité plus importante à l'opérateur
INTERSECT qu'aux autres opérateurs ensemblistes. Vous devez donc indiquer explicitement

24
par des parenthèses l'ordre d'exécution des requêtes contenant l'opérateur INTERSECT et
d'autres opérateurs ensemblistes.

L'Opérateur UNION
Cet opérateur combine le résultat de deux requêtes. Il permet de ramener toutes les lignes
issues de plusieurs requêtes et d'éliminer les doublons.

Instructions
 Le nombre de colonnes et les types de données des colonnes doivent être identiques dans les
deux ordres SELECT. En revanche, les noms de colonnes peuvent être différents.
 L'opérateur UNION intervient sur toutes les colonnes sélectionnées.
 Les colonnes NULL sont ignorées lors du contrôle des doublons.
 L'opérateur IN a une priorité plus élevée que l'opérateur UNION.
 Les requêtes incluant l'opérateur UNION dans la clause WHERE doivent comprendre le même
nombre de colonnes et des colonnes du même type que celles de la clause SELECT.
 Par défaut, les données sont affichées par ordre

L'Opérateur UNION ALL

Cet opérateur permet de ramener toutes les lignes issues de plusieurs requêtes.

Règles
 Contrairement à l'opérateur UNION, les doublons ne sont pas éliminés et le résultat n'est pas trié
par défaut.
 Il n'est pas possible d'utiliser le mot-clé DISTINCT.

Remarque : Les règles relatives aux opérateurs UNION et UNION ALL sont les mêmes, excepté
les deux points ci-dessus.

L'OPÉRATEUR INTERSECT
Cet opérateur permet de ramener toutes les lignes communes aux deux requêtes.
 Le nombre de colonnes et les types de données des deux colonnes doivent être identiques dans
les deux ordres SELECT. En revanche, tes noms de colonnes peuvent être différents.
 L'inversion de l'ordre des tables interrogées ne modifie pas le résultat.
 Comme l'opérateur UNION, l'opérateur INTERSECT ignore les colonnes NULL.
 Les requêtes incluant l'opérateur INTERSECT dans la clause WHERE doivent comprendre le
même nombre et des colonnes du même type que celles de la clause SELECT

L'opérateur MINUS
Cet opérateur ramène les lignes retournées par la première requête, qui ne le sont pas par la
seconde (premier ordre SELECT moins le second).
 Le nombre de colonnes et les types de données des colonnes doivent être identiques dans
les deux ordres SELECT. En revanche, les noms de colonnes peuvent être différents.
 Toutes les colonnes incluses dans la clause WHERE doivent également être incluses dans
la clause SELECT pour que la requête de l'opérateur MINUS puisse être exécutée.
 Les requêtes incluant l'opérateur MINUS dans la clause WHERE doivent comprendre le
même nombre de colonnes et des colonnes GU même type que celles de la clause
SELECT.

25
LES SOUS-INTERROGATIONS

Utilisation d'une Sous Interrogation pour Résoudre un Problème

Supposons que vous souhaitiez écrire une requête pour trouver qui gagne plus que l'employé
Jones.
Pour résoudre ce problème, deux requêtes sont nécessaires : une qui trouve le salaire de
Jones, et l'autre qui trouve quel employé a un salaire supérieur.
A cet effet, vous pouvez combiner deux requêtes, en en plaçant l'une à l'intérieur de l'autre.
La requête interne, ou sous interrogation, ramène une valeur utilisée par la requête externe,
ou principale. Utiliser une sous interrogation revient à exécuter deux requêtes successives en
utilisant le résultat de la première comme valeur de recherche de la seconde.

Sous interrogations
Une sous interrogation est un ordre SELECT imbriqué dans une clause d'un autre ordre
SELECT. Les sous interrogations vous permettent de construire des ordres puissants à partir
d'instructions toutes simples. Elles s'avèrent très utiles pour sélectionner des lignes d'une table
lorsqu'une condition dépend des données de la table elle-même.
Vous pouvez placer une sous interrogation dans les clauses SQL suivantes :
 WHERE
 HAVING
 FROM

Syntaxe :
Select select_list
From table
Where expr operator (select select-list from table);

operator est un opérateur de comparaison tel que >, = ou IN.

Remarque : les opérateurs de comparaison se classent en deux catégories : les opérateurs mono-
ligne (>. =. >-. <. <>, <=) et les opérateurs multi-ligne (IN, ANY, ALL).
Une sous interrogation est souvent désignée sous le nom "d'ordre SELECT imbriqué", "sous- ordre
SELECT", ou encore "ordre SELECT interne". Elle est exécutée en premier, et son résultat sert à
évaluer la condition définie dans l'interrogation principale ou externe.

Convention d'Utilisation des sous interrogations

 Une sous interrogation doit être incluse entre parenthèses.
 Une sous interrogation doit être placée à droite de l’opérateur de comparaison.
 Les sous interrogations ne doivent pas contenir de clause ORDER BY. Il ne peut y avoir qu'une
seule clause ORDER BY par ordre SELECT. Si vous en spécifiez une, elle doit obligatoirement
figurer en dernier dans l'ordre SELECT principal.
 Deux catégories d'opérateurs de comparaison sont utilisées dans les sous interrogations : les
opérateurs mono-ligne et les opérateurs multi-ligne .

Types de sous interrogations

 Sous interrogation mono-ligne : ordre SELECT interne qui ne ramène qu'une seule ligne
 Sous interrogation multi-ligne : ordre SELECT interne qui ramène plusieurs lignes
 Sous interrogation multi-colonne : ordre SELECT interne qui ramène plusieurs colonnes

Exemple :

26
 SQL> SELECT ename, job
2 FROM emp
3 WHERE job =
4 (SELECT job
5 FROM emp
6 WHERE empno = 7369);

Utilisation de Fonctions de Groupe dans une Sous Interrogation

Vous pouvez afficher les données d'une requête principale en utilisant une fonction de groupe
dans une sous interrogation pour ramener une ligne unique. La sous interrogation est incluse entre
parenthèses et placée à la suite de l'opérateur de comparaison.

Utilisation d'une Sous-Interrogation dans la Clause FROM

Vous pouvez utiliser une sous-interrogation dans la clause FROM d'un ordre SELECT. Cette
méthode présente de nombreuses similitudes avec le mode d'utilisation des vues. L'exemple ci-
dessus affiche le nom, le salaire, le numéro de département et le salaire moyen du département pour
tous les employés gagnant plus que le salaire moyen de leur département

Sous-Interrogations Synchronisées
Une Sous-Interrogation Synchronisée est une sous-interrogation imbriquée qui est exécutée
une seule fois pour chaque ligne traitée par la requête principale et qui, pendant son exécution,
utilise une valeur d'une colonne de la requête externe.

sous-interrogations Imbriquées et Sous-Interrogations Synchronisées

Avec une sous-interrogation imbriquée normale, l'ordre SELECT interne est d'abord exécuté pour
retourner des valeurs qui seront récupérées par la requête principale. Par contre, une Sous-
Interrogation Synchronisée est exécutée une fois pour chaque ligne candidate prise en compte par la
requête externe. Autrement dit, la requête interne est pilotée par la requête externe.

Exécution d'une Sous-Interrogation Synchronisée.

1. Récupérer une ligne candidate (extraite par la requête externe).
2. Exécuter la requête interne avec la valeur de la ligne candidate.
3. Utiliser la ou les valeurs résultant de la requête interne pour sélectionner ou ne pas sélectionner
la ligne candidate.
4. Répéter ces étapes jusqu'à ce qu'il ne reste plus de lignes candidates
Une Sous-Interrogation Synchronisée permet de "lire" chaque ligne d'une table et de
comparer les valeurs de chaque ligne aux données associées. Elle est utilisée chaque fois qu'une
sous interrogation doit retourner un résultat ou un ensemble de résultats différent pour chaque ligne
candidate prise en compte par la requête principale. Autrement dit, une Sous-Interrogation
Synchronisée permet de répondre à une question à plusieurs choix, dont la réponse dépend de la
valeur de chaque ligne traitée par l'ordre maître. Oracle exécute une Sous-Interrogation
Synchronisée lorsque la sous-interrogation fait référence à une colonne d'une table de la requête
principale.

27
 Syntaxe :

Select outerl, outer2 , ...

From tablel alias1
Where outerl operator (select innerl
From table2 alias2
Where alias1.outer1=alias2.innerl) ;

L'Opérateur EXISTS
Avec des ordres SELECT imbriqués, tous les opérateurs logiques sont valides. De plus, vous
pouvez recourir à l'opérateur EXISTS. Cet opérateur est souvent utilisé dans les Sous-
Interrogations Synchronisées, car il permet de tester s'il existe une valeur. Si la valeur existe, il
retourne la valeur TRUE (vrai) ; sinon, il retourne la valeur FALSE (faux). De la même façon,
l'opérateur NOT EXISTS garantit qu'il n'existe aucune valeur. Cet opérateur garantit que la
recherche dans la requête interne ne se poursuivra pas s'il existe au moins une correspondance entre
un responsable et des numéros d'employés.

Exemple :

Select empno, ename, job

From emp o
Where exists (Select empno
From emp i
Where i.mgr-= o.empno ) ;

Remarques :

 Dans le cas d'un ordre UPDATE, une sous-interrogation synchronisée permet de mettre à jour
les lignes d'une table basée sur des lignes d'une autre table.
 Avec un ordre DELETE, une Sous-Interrogation Synchronisée permet de ne supprimer que les
lignes existant également dans une autre table

Langage de Manipulation des Données (LMD) :

Le langage de manipulation des données (LMD) joue un rôle central dans SQL. Chaque fois
que vous ajoutez, modifiez ou supprimez des données dans la base de données, vous exécutez un
ordre du LMD. Un ensemble d'ordres du LMD groupé en une unité de travail logique constitue ce
qu'on appelle une transaction.
Considérons une base de données d'opérations bancaires. Quand un client de la banque
transfère de l'argent d'un compte d'épargne vers un compte courant, la transaction doit donner lieu à
trois opérations différentes : débit du compte d'épargne, crédit du compte courant, et enregistrer la
transaction dans le journal des transactions. Oracle Server doit garantir que l'ensemble des trois
ordres SQL sont exécutés pour maintenir la balance des comptes équilibrée. Quand quelque chose
empêche la bonne exécution de l'un des ordres de la transaction, les autres ordres de la même
transaction doivent être annulés.

Ajout d'une Nouvelle Ligne dans une Table

Pour ajouter de nouvelles lignes dans une table, utilisez l'ordre INSERT.

28
Syntaxe :
INSERT INTO table[(column [, column ...])]
VALUES (value [, value ...]);
table nom de la table
column nom de la colonne dans la table à remplir
value valeur qui figurera dans la colonne

Remarque : lorsque cet ordre est utilisé avec la clause VALUES, il n'insère qu'une seule ligne à la
fois.

Etant donné que vous pouvez insérer une nouvelle ligne en précisant une valeur pour chaque
colonne, il n'est pas obligatoire de lister les colonnes dans la clause INSERT. Dans ce cas, les
valeurs doivent être fournies dans l'ordre par défaut des colonnes dans la table

Méthodes d'Insertion de Valeurs NULL

Méthode Description
Implicite Omettez la colonne dans la liste
Explicite Spécifiez le mot-clé NULL dans la liste VALUES
Vous pouvez aussi spécifier une chaîne vide (' ') dans la liste VALUES,
mais uniquement pour les chaînes de caractères et les dates
Assurez-vous que la colonne cible admet les valeurs NULL en vérifiant l'état NULL? Au
moyen de la commande SQL*Plus DESCRIBE. Oracle Server applique automatiquement toutes les
contraintes applicables aux types de données, aux intervalles de données et à l'intégrité. Si une
colonne n'est pas explicitement spécifiée dans la liste, elle reçoit automatiquement une valeur
NULL dans la nouvelle ligne.

Copie de Lignes d'une Autre Table

Vous pouvez utiliser l'ordre INSERT pour ajouter des lignes dans une table lorsque les
valeurs proviennent de tables existantes. Dans ce cas, à la place de la clause VALUES, vous utilisez
une sous-interrogation.

Syntaxe
INSERT INTO table [ column (,column) ]
subquery;

où : table représente le nom de la table

column représente le nom de la colonne dans la table à remplir
subquery représente la sous-requête qui ramène les lignes dans la table

Modification de Lignes
Vous pouvez modifier des lignes existantes au moyen de l'ordre UPDATE. Dans la syntaxe
présentée ci-dessus.
UPDATE table
SET column= value [, column = value]
[WHERE condition]

29
table est le nom de la table
Column est le nom de la colonne à modifier dans la table
value est la nouvelle valeur qui figurera dans la colonne, ou une sous-
interrogation fournissant cette valeur
condition identifie les lignes à mettre à jour. Se compose de noms de colonne,
d'expressions, de constantes, de sous-interrogations et d'opérateurs de
comparaison.

Il est possible d'utiliser des sous-interrogations multi-colonne dans la clause SET d'un ordre
UPDATE.

Syntaxe
UPDATE table
SET (column, column, ...) =
(SELECT column, column,
FROM table WHERE condition)
WHERE condition;

Suppression de Lignes
Vous pouvez supprimer des lignes existantes au moyen de l'ordre DELETE.

Syntaxe :
DELETE [FROM] table
[WHERE condition];

table nom de la table

condition identifie les lignes à supprimer. Se compose de noms de colonnes,
d'expressions, de constantes, de sous-requêtes et d'opérateurs de
comparaison.

Transactions de Base de Données

Oracle garantit la cohérence des données par le biais des transactions. Les transactions vous
offrent davantage de souplesse et un meilleur contrôle lors de la modification de données. Elles
garantissent la cohérence des données en cas d'échec du processus utilisateur ou de panne du
système.
Les transactions consistent en un ensemble d'ordres du LMD qui réalisent une modification
cohérente des données. Par exemple, un transfert de fonds entre deux comptes implique de débiter
un compte et d'en créditer un autre du même montant. Les deux actions doivent, soit réussir, soit
échouer en même temps : un crédit ne peut pas être validé sans le débit correspondant.

Quand Commence et Finit une Transaction ?

Une transaction commence dès le premier ordre SQL exécutable rencontré et se termine
lorsque l'un des événements suivants e produit:
 Un ordre COMMIT ou ROLLBACK est lancé
 Un ordre LDD, tel que CREATF, est lancé
 Un ordre LCD est lancé
 L'utilisateur quitte SQL*P!us
 II se produit une panne de machine ou du système d'exploitation
Lorsqu'une transaction prend fin le prochain ordre SQL exécutable démarrera
automatiquement la transaction savante.

30
 Comme un ordre LDD ou LCD est automatiquement validé, il termine implicitement une
transaction.

Ordres de Contrôle Explicite des Transactions

Vous avez la possibilité de contrôler la logique des transactions au moyen des ordres COMMIT,
SAVEPOFNT et ROLLBACK.
Ordre Description
COMMIT Met fin à la transaction courante en rendant définitives toutes
les modifications de données en instance.
SAVEPOINT name Pose une étiquette dans la transaction courante.
ROLLBACK [TO ROLLBACK met fin à la transaction courante et rejette toutes
SAVEPOW1 name] les modifications de données en instance. ROLLBACK TO
SAVEPOINT name annule toutes les modifications jusqu'au
savepoint et supprime celui-ci.

Traitement Implicite des Transactions

Etat Circonstances
Commit automatique Exécution d'un ordre du LDD ou du LCD
Sortie normale de SQL* Plus, sans précision d'un ordre
COMMIT ou RDI.I.RACK explicite
Rollback automatique Fin anormale de SQL*Plus ou panne du système

Validation des Modifications

Toutes les modifications de données effectuées au cours d'une transaction restent temporaires
tant que la transaction n'est pas validée.

Etat des Données Avant COMMIT ou ROLLBACK

 Les opérations de manipulation des données se déroulant principalement dans le buffer de la
base de données, il est possible de restaurer l'état précédent des données.
 L'utilisateur courant peut afficher le résultat de ses opérations de manipulation de données en
interrogeant les tables.
 Les autres utilisateurs ne peuvent pas voir le résultat des opérations de manipulation de données
réalisées par l'utilisateur courant. Oracle met en œuvre un principe de lecture cohérente qui
garantit que l'utilisateur voit les données telles qu'elles se présentaient lors de la dernière
validation.
 Les lignes concernées par la transaction sont verrouillées ; aucun autre utilisateur ne peut
modifier les données qu'elles contiennent.

Validation des Modifications

Pour enregistrer définitivement les modifications en instance, utilisez l'ordre COMMIT.
Après l'exécution d'un ordre COMMIT :
 Les modifications de données sont écrites définitivement dans la base de données.
 L'état précédent des données est irrémédiablement perdu.
 Tous les utilisateurs peuvent voir les résultats de la transaction.
 Les lignes qui étaient verrouillées sont libérée? e: redeviennent accessibles à d'autres utilisateurs
pour modification.
 Tous les savepoints sont effacés

31
Annulation des Modifications
Vous pouvez annuler toutes les modifications de données en instance au moyen de l'ordre
ROLLBACK. Après l'exécution d'un ordre ROLLBACK :
 Les modifications apportées aux données sont annulées.
 Les données sont retrouvent leur état précédent.
 Les lignes verrouillées sont libérées

Annulation des Modifications Jusqu'à une Etiquette de Savepoint

Vous pouvez poser une étiquette dans la transaction courante en utilisant l'ordre
SAVEPOINT. Cette procédure permet de diviser une transaction en plusieurs parties plus petites. Il
vous est ensuite possible de rejeter les modifications en instance jusqu'à la hauteur de l'étiquette au
moyen de l'ordre ROLLBACK TO SAVEPOINT. Si vous créez un savepoint portant le même nom
qu'un savepoint précédemment défini, celui- ci est supprimé.

Lecture Cohérente
On peut accéder à une base de données de deux manières différentes :
 En lecture (ordre SELECT)
 En écriture (ordres INSERT, UPDATE, DELETE)
Le principe de lecture cohérente a les effets suivants :
 L'utilisateur qui lit la base de données et celui qui y écrit ont une vue cohérente des données
 Les utilisateurs qui accèdent en lecture ne peuvent pas voir les données en cours de modification
 Les utilisateurs qui accèdent en écriture sont sûrs que leurs modifications seront cohérentes
 Les modifications effectuées par un utilisateur ne peuvent pas interrompre ou gêner les
modifications en cours d'un autre utilisateur

L'objectif de la lecture cohérente est de garantir que chaque utilisateur voit les données telles
qu'elles se présentaient lors de la dernière validation, c'est-à-dire avant le démarrage d'une opération
du LMD.

Implémentation de la Lecture Cohérente

Le principe de lecture cohérente s'applique automatiquement. Il consiste à conserver une
copie partielle de la base de données dans des "rollback segments".
Lorsqu'une opération d'insertion, de mise à jour ou de suppression a lieu dans la base de
données, Oracle copie les données avant leur modification dans un rollback segment.
Tous les utilisateurs, excepté celui à l'origine de la modification, continuent à voir les
données telles qu'elles se présentaient avant les modifications. En réalité, ils voient un "cliché" des
données copiées dans les rollback segments.
Tant que les modifications ne sont pas validées dans la base de données, seul l'auteur des
mises à jour voit comment se présentera la base de données une fois modifiée. Tous les autres
continuent à voir des copies des "anciennes" données. Ainsi, les utilisateurs ont toujours une vue
cohérente des données qui ne sont pas en cours de modification.
Après la validation d'un ordre LMD, les modifications effectuées deviennent visibles par tout
utilisateur exécutant un ordre SELECT.
L'espace occupé par les "anciennes" données dans le fichier des rollback segments est libéré
et redevient disponible.
Si un ordre ROLLBACK est exécuté, les modifications sont "annulées" et :
 Les données d'origine présentes dans le rollback segment sont récrites dans la table.
 Tous les utilisateurs voient la base de données telle qu'elle était avant le début de la
transaction.

Définition des Verrous

32
 Les verrous sont des mécanismes qui empêchent les destructions dues aux interactions entre
des transactions qui accèdent à la même ressource, qu'il s'agisse d'un objet utilisateur (comme des
tables ou des lignes), ou d'objets système non visibles par les utilisateurs (tels que des structures de
données partagées et des lignes du dictionnaire de données).

Procédure Oracle de Verrouillage des Données

Dans une base de données Oracle, le processus de verrouillage est entièrement automatique
et ne requiert aucune intervention de l'utilisateur. Un verrouillage implicite a lieu pour tous les
ordres SQL. Le mécanisme de verrouillage d'Oracle s'applique par défaut au niveau de restriction le
plus bas possible, afin de permettre un degré élevé de concurrence transactionnelle tout en offrant
une intégrité maximale des données. Il est également possible de verrouiller les données
manuellement

Objets d'une Base de Données

Une base de données Oracle peut contenir de nombreuses structures de données. Chaque
structure doit être prédéfinie lors de la conception de la base de données pour pouvoir être créée
durant la phase de construction de la base.
 Table : stocke les données
 Vue : sous-groupes de données issues d'une ou de plusieurs tables
 Séquence : génère des valeurs de clés primaires
 Index : améliore les performances de certaines requêtes
 Synonyme : permet de renommer des objets

Structures des Tables Oracle

 Vous avez la possibilité de créer des tables à tout moment, y compris lorsque la base de données
est déjà en cours d'utilisation.
 Vous n'avez pas à spécifier de taille pour les tables. La taille est en fait définie en fonction de
l'espace total alloué à la base de données. Il est important, néanmoins, d'estimer l'espace
qu'occupera une table avec le temps.
 La structure des tables peut être modifiée en ligne.

Objets d'une Base de Données

Une base de données Oracle peut contenir de nombreuses structures de données. Chaque
structure doit être prédéfinie lors de la conception de la base de données pour pouvoir être créée
durant la phase de construction de la base.
 Table : stocke les données
 Vue : sous-groupes de données issues d'une ou de plusieurs tables
 Séquence : génère des valeurs de clés primaires
 Index : améliore les performances de certaines requêtes
 Synonyme : permet de renommer des objets

Structures des Tables Oracle

 Vous avez la possibilité de créer des tables à tout moment, y compris lorsque la base de données
est déjà en cours d'utilisation.
 Vous n'avez pas à spécifier de taille pour les tables. La taille est en fait définie en fonction de
l'espace total alloué à la base de données. Il est important, néanmoins, d'estimer l'espace
qu'occupera une table avec le temps.
 La structure des tables peut être modifiée en ligne.

L'Ordre CREATE TABLE

Créez des tables pour stocker des données en utilisant l'ordre SQL CREATE TABLE. Cet
ordre fait partie d'une série d'ordres appartenant au langage de définition des données (LDD) qui

33
sera étudié dans les prochains chapitres. Les ordres du LDD représentent un sous-ensemble des
ordres SQL utilisés pour créer, modifier ou supprimer des structures de données OracleS. Ils
agissent directement sur la base de données, et enregistrent des informations dans le dictionnaire de
données.
Pour créer une table, l'utilisateur doit disposer du privilège CREATE TABLE et d'un espace
de stockage dans laquelle il pourra créer des objets. L'administrateur de base de données utilise des
ordres du LCD (langage de contrôle des données) pour accorder des privilèges aux utilisateurs ;
Syntaxe :
CREATE TABLE [schema.]table
(column datatype [DEFAULT exp],...);

schema nom du propriétaire

table nom de la table
DEFAULT expr spécifie une valeur par défaut à utiliser en cas d'omission d'une
Valeur dans l'ordre (NSERT
column nom de la colonne
datatype type de données et longueur de la colonne

Interrogation du Dictionnaire de Données

Vous pouvez interroger les tables du dictionnaire de données pour afficher différents objets
de la base vous appartenant. Les tables du dictionnaire de données les plus fréquemment utilisées
sont les suivantes :
 USER_TABLES
 USER_OBJECTS
 USER_CATALOG

Remarque : CAT est un synonyme de USER_CATALOG. Vous pouvez l'utiliser à la place de

USER_CATALOG dans les ordres SQL.

Types de Données

Type de Données Description

VARCHAR2(size) Données caractères de longueur variable. Spécifie/;
obligatoirement une longueur (size) maximum. La longueur
minimum est 1, la longueur maximum est 4000.
CHAR(size) Données caractères de longueur fixe d'un nombre d'octets égal à
size. La longueur (size) minimum et par défaut est 1, la longueur
maximum est 2000.
NUMBER(p,s) Nombre de précision p et d'échelle s ; la précision est le nombre
total de chiffres allant de 1 à 38, et l'échelle est le nombre de
chiffres à droite de la virgule allant de -84 à 127.
DATE Valeurs de date et d'heure allant du 1er janvier 4712 av.J.C. au 31
décembre 9999 apr.J.C.
LONG Données caractères de longueur variable, jusqu'à 2 giga-octets,
CLOB Données caractères mono-octet, jusqu'à 4 giga-octets.
RAW(size) Données binaires, de longueur size. La longueur maximum est
2000.
LONGRAW Données binaires, de longue variable : jusqu'à 2 giga-octets.
BLOB Données binaires, jusqu'à 4 giga-octets.

34
BFILE Données binaires, stockées dans un fichier externe ; jusqu'à 4
giga- octets.

Création d'une Table avec des Lignes d'une Autre Table.

Il existe une deuxième façon de créer une table, qui consiste à utiliser la clause AS subquery.
Cette méthode permet à la fois de créer la table et d'y insérer des lignes ramenées par une
sous-interrogation.

Syntaxe :
CREATE TABLE table[(column,...)]
AS subquery;

table nom de la table

column nom de la colonne, valeur par défaut et contrainte d'intégrité
subquery ordre SELECT qui définit le groupe de lignes à insérer dans la nouvelle table

Conseils
 La table est créée avec les noms de colonnes spécifiés, puis remplie avec les lignes extraites au
moyen de l'ordre SELECT.
 La définition des colonnes ne peut contenir que le nom de la colonne et la valeur par défaut.
 Si des colonnes sont spécifiées, leur nombre doit être le même que celui de la sous-
interrogation SELECT.
 Si aucune colonne n'est spécifiée, elles seront du même nom que celles de la sous- interrogation.
Ordre ALTER TABLE
Après avoir créé vos tables, il peut arriver que vous deviez en modifier la structure pour
ajouter une colonne oubliée ou que vous décidiez de changer une définition de colonne. Cela est
possible grâce à l'ordre ALTER TABLE.
Vous pouvez ajouter des colonnes à une table en utilisant l'ordre ALTER TABLE avec la
clause ADD.

Syntaxe :
ALTER TABLE table
ADD (column datatype [DEFAULT expr][, column datatype] ...);

ALTER TABLE table

MODIFY (column datatype [DEFAULT expr][, column datatype] ...);

table nom de la table

column nom de la nouvelle colonne
datatype type de données et longueur de la nouvelle colonne
DEFAULT expr valeur par défaut de la nouvelle colonne

Vous pouvez modifier des colonnes existantes d'une table au moyen de l'ordre ALTER TABLE
avec la clause MODIFY.

Remarque : si une table contient déjà des lignes lorsque l'on ajoute une colonne, la nouvelle
colonne sera initialisée à NULL pour toutes les lignes sauf si l'on précise une valeur par défaut,
35
dans ce cas pour toutes les lignes de la table, la colonne sera initialisée avec la valeur par défaut.

Modification de Colonnes
Vous pouvez modifier la définition d'une colonne au moyen de l'ordre ALTER TABLE et de
la clause MODIFY. Les modifications effectuées peuvent être des modifications du type de
données, de taille et de la valeur par défaut.

Conseils
 Vous pouvez augmenter la largeur ou la précision d'une colonne numérique.
 Réduisez la largeur d'une colonne si celle-ci ne contient que des valeurs NULL ou si la table ne
contient aucune ligne.
 Modifiez le type de données si la colonne contient des valeurs NULL ou si la table est vide.
 Convertissez une colonne de type CHAR en type clé données VARCHAR2 ou inversement,
si la colonne contient des valeurs NULL, si vous ne réduises pas sa (aille ou si la table est
vide.
 La modification d'une valeur par défaut ne s'appliquera qu'aux insertions ultérieures.

Suppression de Tables
L'ordre DROP TABLE supprime la définition d'une table Oracle8. Lorsque vous supprimez
une table, la base de données perd toutes les données de la table ainsi que tous les index associés.
Syntaxe
DROP TABLE table;

où : table est le nom de la table

Conseils
 Toutes les données de la table sont supprimées.
 Les vues, synonymes ne sont pas supprimés mais ne sont plus utilisables.
 Toute transaction en instance est validée.
 Seul le créateur de la table ou un utilisateur ayant le privilège DROP ANY TABLE peut
supprimer une table.

Une fois exécuté, l'ordre DROP TABLE est irréversible. Oracle ne demande pas
confirmation lorsque vous lancez cet ordre. Si vous êtes le propriétaire de la table ou disposez
d'un niveau de privilège élevé, la table sera immédiatement supprimée. Tous les ordres du LDD
effectuent une validation qui rend la transaction permanente.

Modification du Nom d'un Objet

L'ordre RENAME est un ordre du LDD qui permet de renommer une table, une vue, une
séquence ou un synonyme.

Syntaxe
RENAME o.ld_name TO new_name;
où : old_name est l'ancien nom de la table, de la vue, de la séquence ou du synonyme
new_ name est le nouveau nom de la table, de la vue, de la séquence ou du synonyme
Vous devez être propriétaire de l'objet que vous renommez.
36
Vider une Table
TRUNCATE TABLE est un autre ordre LDD, qui permet de supprimer toutes les lignes
d'une table tout en libérant l'espace utilisé pour stocker cette table. L'ordre TRUNCATE
TABLE ne peut être annulé.

Syntaxe
TRUNCATE TABLE table;
où : table est le nom de la table
Vous devez être propriétaire de la table ou disposer du privilège système DELETE TABLE
pour tronquer une table.
L'ordre DELETE supprime aussi les lignes d'une table, mais il ne libère pas l'espace de
stockage

Les Contraintes
Oracle fait appel à des contraintes pour empêcher l'entrée de données incorrectes dans des
tables.
Vous pouvez utiliser des contraintes pour :
 Appliquer des règles au niveau d'une table chaque fois qu'une ligne est insérée, mise à jour ou
supprimée dans cette table. La contrainte doit être satisfaite pour que l'opération réussisse.
 Empêcher la suppression d'une table si il y a des dépendances avec d'autres tables.
 Fournir des règles pour des outils Oracle comme Developer 2000.

Contraintes d'Intégrité des Données

Contrainte Description
NOTNULL Spécifie que cette colonne ne doit pas contenir de valeur null
UNIQUE Spécifie une colonne ou une combinaison de colonnes dont les
valeurs doivent être uniques pour toutes les lignes de la table
PRIMARY KEY Identifie chaque ligne de la table de manière unique
FOREIGN KEY Etablit et contrôle une relation de clé étrangère entre la
colonne et une colonne de la table référencée
CHECK Spécifie une condition qui doit être vraie

Conventions Applicables aux Contraintes

Toutes les contraintes sont stockées dans le dictionnaire de données. Elles seront très faciles
à manipuler si vous leur donnez un nom parlant. Les noms de contraintes sont soumis aux
conventions de dénomination des objets standard. Si vous ne nommez pas une contrainte, Oracle8
génère un nom dont le format est SYS_Cn, où n est un entier permettant de créer un nom de
contrainte unique.
Il est possible de définir les contraintes au moment de la création de la table ou plus tard.
Vous pouvez retrouver les contraintes définies pour une table spécifique en consultant la
table USER_CONSTRAINTS du dictionnaire de données.
En général, on crée les contraintes en même temps que la table. Il est cependant possible
d'ajouter des contraintes ou d'en désactiver dans une table déjà créée.

37
La contrainte NOT NULL
La contrainte NOT NULL interdit la présence de valeurs NULL dans la colonne à laquelle
elle s'applique. Par défaut, les colonnes qui ne sont pas associées à la contrainte NOT NULL
peuvent contenir des valeurs NULL.

Exemple :
... deptno MUMBER (7,2)
CONSTRAINT emp_deptno_nn NOT NULL;

La Contrainte de Clé UNIQUE

Une contrainte d'intégrité de type clé UNIQUE exige que chaque valeur dans une colonne ou
dans un ensemble de colonnes (la clé) soit unique, c'est-à-dire qu'elle n'existe pas dans plusieurs
lignes pour la colonne ou l'ensemble de colonnes spécifiés. La colonne ou l'ensemble de colonne
indiqués dans la définition de la contrainte UNIQUE constituent la clé unique. Si la clé comprend
plusieurs colonnes, le groupe de colonnes est appelé clé unique composée. Les contraintes UNIQUE
autorisent la saisie de valeurs NULL, à moins que vous ne définissiez également des contraintes
NOT NULL sur les mêmes colonnes. En fait, lorsque des colonnes n'ont pas de contrainte NOT
NULL, un nombre quelconque de lignes peuvent contenir des valeurs NULL puisque celles-ci
n'équivalent à rien. Une valeur NULL dans une colonne (ou dans toutes les colonnes appartenant à
une clé unique composée) satisfait toujours une contrainte de clé UNIQUE.

Exemple :

...Constraint dept_deptno_pk UNIQUE(name)

Remarque : Oracle Server contrôle la contrainte UNIQUE en créant implicitement un index unique
sur la clé.

La Contrainte PRIMARY KEY

Une contrainte PRIMARY KEY crée une clé primaire pour la table. Une seule clé primaire
peut être créée par table. La contrainte PR1MARY KEY est une colonne ou un ensemble de
colonnes qui identifient de manière unique chaque ligne d'une table. Elle établit une règle d'unicité
de la colonne ou d'une combinaison de colonnes et garantit qu'aucune colonne faisant partie de la
clé primaire ne contient de valeur NULL.

Exemple :
... Constraint dept_deptno_pk PRIMARY KEY(deptno)
Remarque : un index UNIQUE est automatiquement créé pour une colonne PRIMARY KEY.

La Contrainte FOREIGN KEY

La contrainte FOREIGN KEY, ou contrainte d'intégrité référentielle, désigne une colonne ou
une combinaison de colonnes comme étant une clé étrangère et établit une relation avec une clé
primaire ou une clé unique de la même table ou d'une table différente. Dans l'exemple ci-dessus, la
colonne DEPTNO a été définie comme clé étrangère dans la table EMP (table dépendante ou
enfant) ; elle fait référence à la colonne DEPTNO de la table DEPT (table de référence ou parent).
Une valeur de clé étrangère doit obligatoirement correspondre à une valeur existante de la
table maître ou être NULL.
Les clés étrangères sont basées sur des valeurs de données et sont des pointeurs purement
logiques et non physiques.
38
Exemple :
... Constraint emp_deptno_fk FOREIGN KEY (deptno) REFERENCES dept(deptno)

La clé étrangère est définie dans la table détail et la table contenant la colonne référencée est
la table maître. Pour définir la clé étrangère, on utilise une combinaison des mots-clés suivants :
 FOREIGN KEY définit une colonne de la table détail dans une contrainte de niveau table.
 REFERENCES identifie la table et la colonne dans la table maître.
 ON DELETE CASCADE indique que si une ligne est supprimée de la table maître, les lignes
dépendantes de la table détail seront également supprimées.
En l'absence de l'option ON DELETE CASCADE, la ligne de la table maître ne peut pas être
supprimée si elle est référencée dans la table détail.

La contrainte CHECK

La contrainte CHECK définit une condition que chaque ligne doit obligatoirement satisfaire.
La condition peut utiliser les mêmes constructions que les conditions d'une requête, aux exceptions
près suivantes :
 Références aux pseudo-colonnes CURRVAL, NEXTVAL, LEVEL et ROWNUM
 Appels aux fonctions SYSDATE, UID, USER et USERENV
 Requêtes faisant référence à d'autres valeurs dans d'autres lignes
Plusieurs contraintes CHECK peuvent être définies sur la même colonne. Le nombre de
contraintes CHECK pouvant être associées à une colonne est illimité.
Vous pouvez ajouter des contraintes dans une table existante en utilisant l'ordre ALTER
TABLE avec la clause ADD.

Syntaxe :
ALTER TABLE table
ADD [CONSTRAINT constraint] type (column) :

table nom de la table

constraint nom de la contrainte
type type de contrainte
column nom de la colonne concernée par la contrainte
II est recommandé de préciser le nom de la contrainte, même si ce n'est pas obligatoire. A
défaut, le système générera lui-même des noms de contraintes.
Si vous souhaitez supprimer une contrainte, vous pouvez en retrouver le nom dans les vues
du dictionnaire de données USER_CONSTRAINTS et USER_CONS_COLUMNS. Utilisez ensuite
l'ordre ALTER TABLE avec la clause DROP. L'option CASCADE de la clause DROP provoque
également la suppression de toutes les contraintes associées.

Syntaxe
ALTER TABLE table DROP PRIMARY KEY | UNIQUE (column) \
CONSTRAINT constraint [CASCADE];
où : table représente le nom de la table
column représente le nom de la colonne concernée par la contrainte
constraint représente le nom de la contrainte
Lorsque vous supprimez une contrainte d'intégrité, elle n'est plus contrôlée par Oracle et
n'existe plus dans le dictionnaire de données.

Désactivation d'une Contrainte

39
 Vous pouvez désactiver une contrainte sans la supprimer (ce qui évite d'avoir à la recréer) en
utilisant l'ordre ALTER TABLE avec la clause DISABLE.

Syntaxe
ALTER TABLE table DISABLE CONSTRAINT constraint [CASCADE] ;
où : table représente le nom de la table
constraint représente le nom de la contrainte

Conseils
 Vous pouvez utiliser la clause DISABLE aussi bien dans l'ordre CREATE TABLE que
ALTER TABLE.
 La clause CASCADE désactive des contraintes d'intégrité dépendantes.

Activation d'une Contrainte

Vous pouvez activer une contrainte, sans la supprimer et la recréer, en utilisant l'ordre ALTER
TABLE avec la clause ENABLE.

Syntaxe
ALTER TABLE table ENABLE CONSTRAINT constraint;
où : table représente le nom de la table
constraint représente le nom de la contrainte

Conseils
 Si vous activez une contrainte, celle-ci s'applique à toutes les données de la table. Les
données de la table doivent donc toutes respecter la contrainte.
 Si vous activez une contrainte UNIQUE ou PR1MARY K.EY, un index unique est
automatiquement créé.
 Vous pouvez utiliser la clause ENABLE aussi bien dans l'ordre CREATE TABLE que
ALTER TABLE.
Vérification des Contraintes
Une fois que vous avez créé une table, vous pouvez en contrôler l'existence en exécutant la
commande DESCRIBE. Cependant, la seule contrainte que vous pouvez vérifier de cette manière
est NOT NULL. Si vous voulez afficher toutes les contraintes de votre table, interrogez la table
USER_CONSTRAINTS.

Remarque : les contraintes qui ne sont pas explicitement nommées par le propriétaire de la table se
voient attribuer un nom par le système. Dans la colonne décrivant le type de contrainte, la lettre C
signifie CHECK, P représente PR1MARY KEY, R intégrité référentielle et U UNIQUE. A noter
que la contrainte NULL est en réalité une contrainte CHECK.
Vous pouvez afficher le nom des colonnes associées à des contraintes en interrogeant la vue
USER_CONS_COLUMNS du dictionnaire de données. Cette vue est particulièrement utile pour les
contraintes ayant un nom attribué par le système.

Qu'est-ce qu'une Vue ?

Vous pouvez présenter des sous-ensembles ou des combinaisons logiques de données en
créant des vues sur des tables. Une vue est une table logique basée sur une table ou une autre vue.
Elle affiche des données qui ne lui sont pas propres, mais c'est comme une fenêtre par laquelle il est
possible de visualiser ou de modifier des données venant des tables. Les tables sur lesquelles une

40
vue est basée sont appelées tables de base. Une vue est stockée dans le dictionnaire de données,
comme un ordre SELECT.

Avantages d'une Vue

 Limite l'accès à la base de données en affichant une sélection de celle-ci.

 Permet aux utilisateurs de créer des requêtes simples pour extraire les résultats de requêtes
complexes. Par exemple, vous pouvez extraire des informations de plusieurs tables sans savoir
écrire un ordre de jointure.
 Garantit l'indépendance des données pour des utilisateurs et des programmes d'application
contextuels. Une vue peut être utilisée pour extraire les données de plusieurs tables.
 Permet à des groupes d'utilisateurs d'accéder aux données en fonction de leurs critères
particuliers.

Vues Simples et Vues Complexes

On distingue les vues simples et les vues complexes. La différence principale entre ces deux
types de vues est liée aux opérations LMD (insertion, mise à jour et suppression).
 Une vue simple :
 Est dérivée des données d'une seule table
 Ne contient ni fonctions ni groupes de données
 Permet d'exécuter des opérations du LMD
 Une vue complexe '.
 Est dérivée des données de plusieurs tables
 Contient des fonctions ou des groupes de données
 Ne permet pas toujours d'exécuter des opérations du LMD

Création d'une Vue

Vous pouvez créer une vue en imbriquant une sous-interrogation dans l'ordre CREATE
VIEW. Dans la syntaxe :
CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW view[(alias,...)]
AS subquery
[WITH CHECK OPTION[CONSRAINT constraint]]
[WITH READ ONLY]

OR REPLACE recrée la vue, si celle-ci existe déjà.

FORCE crée la vue. que les tables de base existent ou non.
NOFORCE ne crée la vue que s'il existe des tables de base (valeur par défaut).
view nom de la vue.
alias indique les noms des expressions sélectionnées par la requête de
la vue. Le nombe d'alias doit être égal au nombre d'expressions
sélectionnées par la vue.
subquery ordre SELECT complet. Vous pouvez utiliser des alias de
colonnes dans la liste SELECT.
WITH CHECK OPTION n'autorise l'insertion et la mise à jour que pour les
lignes auxquelles la vue peut accéder.

41
constraint nom attribué à la contrainte CHECK OPTION.
WITH READ ONLY garantit qu'aucune opération LMD ne peut être exécutée dans la vue.

Extraction des Données d'une Vue

Les données d'une vue s'extraient de la même façon que les données d'une table. Vous
pouvez afficher tout le contenu de la vue ou uniquement des lignes et des colonnes spécifiques.

Vues du Dictionnaire de Données

Une fois votre vue créée, vous pouvez interroger la table USER_VIEWS du dictionnaire de
données pour afficher le nom et la définition de la vue. Le texte de l'ordre SELECT qui constitue
votre vue est stocké dans une colonne de type LONG.

Accès aux Données par une Vue

Lorsque vous accédez à des données par le biais d'une vue. Oracle :
1. Extrait la définition de la vue à partir de la table USËR_VIEWS du dictionnaire de données.
2. Vérifie les privilèges d'accès de la table de base de la vue.
3. Convertit la requête de la vue en une opération équivalente dans la ou les tables de base sous-
jacentes. Autrement dit, les donnés sont extraites ou mises à jour dans la ou des tables de base.

Exécution des Ordres du LMD dans une Vue

Vous pouvez exécuter des opérations LMD sur les données d'une vue, sous réserve de
respecter certaines règles.
Vous ne pouvez pas supprimer une ligne d'une vue si elle contient l'un des éléments
suivants :
 Fonctions de groupe
 Clause GROUP BY
 Mot-clé DISTINCT
Vous ne pouvez pas modifier les données d'une vue qui contient une des conditions
mentionnées précédemment et un des éléments suivants :
 Colonnes définies par des expressions. Exemple : SALARY * 12
 Colonne ROWNUM
Vous ne pouvez pas ajouter de données dans une vue si elle contient un des éléments ci-
dessus et s'il existe dans la table de base des colonnes NOT NULL sans valeur par défaut qui ne
sont pas sélectionnées par la vue. Toutes les valeurs obligatoires doivent figurer dans la vue.
N'oubliez pas que vous ajoutez des valeurs directement dans la table sous-jacente par le biais d'une
vue.

Utilisation de la Clause WITH CHECK OPTION

II est possible d'exécuter des contrôles d'intégrité référentielle dans les vues. Vous pouvez
ainsi renforcer les contraintes au niveau de la base de données. Une vue permet de protéger
l'intégrité des données, mais dans un cadre très limité.
La clause WITH CHECK OPTION précise que les opérations d'insertion et de mise à jour
exécutées dans une vue n'autorisent pas la création de lignes que la vue ne peut pas sélectionner. Par
conséquent, elle permet de renforcer les contraintes d'intégrité et les contrôles de validation sur les
données à insérer ou à mettre à jour.
Si vous tentez d'exécuter des opérations LMD sur des lignes que la vue n'a pas sélectionnées,

42
un message d'erreur s'affiche avec le nom de la contrainte, si ce dernier a été précisé.

SQL> UPDATE empvu20

2 SET deptno =10
3 WHERE empno = 7788;

update empvu20
*
ERROR at line 1:

ORA-01402: view WITH CHECK OPTION where-clause violation

Remarque : Aucune ligne n'est mise à jour si le numéro du département est modifié (10), car la vue
ne pourra plus afficher cet employé. Avec la clause WITH CHECK OPTION, la vue ne peut
afficher que les employés du département 20 et n'autorise pas la modification du numéro de
département de ces employés au travers de la vue.

Refus des Ordres du LMD

L'option WITH READ ONLY vous permet de garantir qu'aucune opération LMD ne sera exécutée
dans votre vue. Dans l'exemple ci-dessus, la vue EMPVU10 est modifiée de telle sorte que toutes
les opérations LMD sont interdites dans cette vue. Toute tentative de suppression d'une ligne dans
la vue génère une erreur.

SQL> DELETE FROM empvulO

2 WHERE employee_number = 7782;
DELETE FROM empvu10
*
ERROR at line 1:
ORA-01752: Cannot delete from view without exactiy one keypreserved table

Suppression d'une Vue

L'ordre DROP VIEW permet de supprimer une vue. La définition de la vue est ainsi
supprimée de la base de données. La suppression d'une vue n'a aucun effet sur les tables sur
lesquelles la vue est basée. En revanche, les vues ou autres applications basées sur des vues
supprimées ne sont plus valides. Seul. le propriétaire ou un utilisateur ayant le privilège DROP
ANY VIEW peut supprimer une vue.
Dans la syntaxe :
DROP VIEW view
view représente le nom de la vue

Objets de la Base de Données

Un grand nombre d'applications exigent l'utilisation de numéros uniques pour les valeurs de
la clé primaire. Vous pouvez créer un code dans l'application pour répondre à cette exigence ou
générer des numéros uniques à l'aide d'une séquence.
Si vous souhaitez améliorer les performances de certaines requêtes, vous devez créer un
index. Les index permettent également de contrôler l'unicité d'une colonne ou d'un groupe de
colonnes.
43
 Vous pouvez attribuer d'autres noms aux objets en utilisant des synonymes.

Qu'est-ce qu'une Séquence ?

A l'aide d'un générateur de séquences, vous pouvez créer automatiquement des numéros de
séquence pour les lignes des tables. Une séquence est un objet de base de données créé par un
utilisateur et qui peut être partagé entre plusieurs utilisateurs.
En général, les séquences permettent de créer une valeur de clé primaire propre à chaque
ligne. La séquence est générée puis incrémentée (ou décrémentée) par une routine interne d'0racle8.
Vous pouvez ainsi gagner du temps, car le code applicatif utilisé pour écrire une routine de
génération de séquences est considérablement réduit.
Les numéros de séquence sont stockés et générés indépendamment des tables. Vous pouvez
donc utiliser la même séquence pour plusieurs tables.

Création d'une Séquence

Générez automatiquement des numéros séquentiels à l'aide de l'ordre CREATE SEQUENCE.
Dans la syntaxe :
séquence représente le nom du générateur de numéros séquentiels.
INCREMENT BY n définit l'intervalle entre les numéros de séquence, où n est un
entier. Si cette clause est omise, le pas d'incrémentation est 1.
START WITH n indique le premier numéro de séquence à générer. Si cette
clause est omise, la séquence commence par le numéro 1.
MAXVALUE n indique la valeur maximale que peut générer la séquence.
NOMAXVALUE définit la valeur maximale 107 pour une séquence
croissante, et la valeur -1 pour une séquence décroissante
(option par défaut).
MINVALUE n indique la valeur minimale de la séquence.
NOMINVALUE définit la valeur minimale 1 pour une séquence croissante,
et la valeur –(1026) pour une séquence décroissante (option
par défaut».
CYCLE | NOCYCLE précise si la séquence peut continuer à générer ou non des valeurs
après avoir atteint sa valeur maximale ou minimale. NOCYCLE est
l'option par défaut.
CACHE n NOCACHE indique le nombre de valeurs qu'Oracle préalloue et
conserve en mémoire. Par défaut. Oracle met 20 valeurs en
mémoire cache.
Vérification des Séquences
Une fois votre séquence créée, elle est documentée dans le dictionnaire de données. Comme
une séquence est un objet de la base de données, vous pouvez l'identifier dans la table
USER_OBJECTS du dictionnaire de données.
Vous pouvez également vérifier les paramètres de la séquence en effectuant une sélection à
partir de la table USER_SEQUENCES du dictionnaire de données.

Utilisation d'une Séquence

Une fois votre séquence créée, vous pouvez générer des numéros séquentiels pour vos tables.
Référencez les valeurs de votre séquence à l'aide des pseudocolonnes NEXTVAL et CURRVAL.

Pseudocolonnes NEXTVAL et CURRVAL

44
 La pseudocolonne NEXTVAL permet d'extraire des numéros successifs à partir de la
séquence indiquée. Vous devez désigner NEXTVAL avec le nom de la séquence. Lorsque vous
référencez séquence.NEXTVAL, un nouveau numéro de séquence est généré et CURRVAL reçoit le
numéro de séquence actuel.
La pseudocolonne CURRVAL fait référence au numéro de séquence que l'utilisateur vient de
générer. NEXTVAL doit générer un numéro de séquence dans la session actuelle de l'utilisateur
pour que CURRVAL puisse être référencée. Vous devez désigner CURRVAL avec le nom de la
séquence. Lorsque vous référencez séquence.CURRVAL, la dernière valeur retournée pour cette
session utilisateur s'affiche.

Règles d'Utilisation des Pseudocolonnes NEXTVAL et CURRVAL

Vous pouvez inclure NEXTVAL et CURRVAL dans :

 En projection d'un ordre SELECT qui ne fait pas partie d'une sous interrogation
 En projection d'une sous-interrogation dans un ordre INSERT
 La clause VALUES d'un ordre INSERT
 La clause SET d'un ordre UPDATE
Vous ne pouvez pas inclure NEXTVAL et CURRVAL dans :
 Dans l'ordre SELECT de création d'une vue
 Un ordre SELECT comprenant le mot-clé DISTINCT
 Un ordre SELECT avec les clauses GROUP BY, HAVING et ORDER BY
 Une sous-interrogation dans un ordre SELECT. DELETE ou UPDATE
 Une expression DEFAULT dans un ordre CREATE TABLE ou ALTER TABLE

Exemple:

SQL> SELECT dept_deptno . CURRVAL

2 FROM SYS . dual;

SQL> insert into emp …

2 VALUES (emp_empno . NEXTVAL, dept_deptno . CURRVAL, …

Mise en Mémoire Cache des Valeurs d'une Séquence

La mise en mémoire cache des séquences permet d'accéder plus rapidement à leurs valeurs.
Le cache est chargé dès la première référence à une séquence. Chaque requête demandant une
valeur de séquence suivante est extraite de la séquence en mémoire cache. Une fois le dernier
numéro utilisé, la requête suivante place d'autres numéros de la séquence en mémoire cache.

Attention aux Intervalles

Même si les générateurs de séquences émettent des numéros séquentiels sans «trous», ceux-
ci peuvent apparaître indépendamment d'une validation ou d'une annulation. Par conséquent, si vous
annulez un ordre contenant une séquence, le numéro de séquence est perdu. Une défaillance du
système peut également générer des interruptions dans une séquence. Si les valeurs de la séquence

45
sont mises en mémoire cache, elles seront perdues en cas de panne du système.
Comme les séquences ne sont pas directement liées aux tables, la même séquence peut être
utilisée pour plusieurs tables. Dans ce cas, chaque table peut contenir des intervalles entre les
numéros de séquence.

Modification d'une Séquence

Si vous atteignez la limite de la valeur maximale (MAXVALUE) de votre séquence, vous ne
pouvez plus affecter d'autres valeurs et vous recevez un message d'erreur indiquant que vous avez,
dépassé la valeur maximale. Pour continuer à utiliser votre séquence, vous pouvez la modifier à
l'aide de l'ordre ALTER SEQUENCE.

Syntaxe
ALTER SEQUENCE séquence [INCREMENT BY n]
[{MAXVALUE n | NOMAXVALUE}]
[{MINVALUE n | NOMINVALUE}]
[{CYCLE | NOCYCLE}]
[{CACHE n | NOCACHE}];
où ; séquence représente le nom du générateur de séquences

Règles
 Pour modifier une séquence, vous devez en être le propriétaire ou avoir le privilège ALTER.
 L'ordre ALTER SEQUENCE ne modifie que les numéros de séquence à venir.
 L'option START WITH ne peut pas être modifiée à l'aide de l'ordre ALTER SEQUENCE. Si
vous voulez que la séquence commence par un autre numéro, vous devez la supprimer puis la
recréer.
 Une validation est exécutée. Par exemple, il n'est pas possible d'imposer une nouvelle valeur
maximale (MAXVALUE) qui soit inférieure au numéro de séquence actuel.

Suppression d'une Séquence

Vous pouvez supprimer une séquence du dictionnaire de données à l'aide de l'ordre DROP
SEQUENCE. Pour cela, vous devez être le propriétaire de la séquence ou avoir le privilège DROP
ANY SEQUENCE.

Syntaxe
DROP SEQUENCE séquence;
où : séquence représente le nom du générateur de séquences

Qu'est-ce qu'un Index ?

Un index Oracle est un objet de schéma qui permet d'accélérer l'extraction de lignes par le
biais d'un pointeur. Il est possible de créer des index explicitement ou automatiquement. Si la
colonne ne comporte pas d'index, la table entière est balayée.
Un index offre un accès direct et rapide aux lignes d'une table. Il a pour but de réduire les E/S
sur disque grâce à un chemin d'accès indexé qui identifie rapidement l'emplacement des données.
Un index est automatiquement utilisé et mis à jour par Oracle. Lorsqu'un index est crée, l'utilisateur
n'est plus contraint d'intervenir directement.
Les index sont indépendants logiquement et physiquement des tables qu'ils indexent.
Autrement dit, vous pouvez à tout moment en créer ou en supprimer, car ils n'ont aucun effet sur les
tables de base ou sur d'autres index.

46
Remarque : Lorsque vous supprimez une table, les index correspondants sont également
supprimés.

Comment Créer un Index ?

Il est possible de créer deux types d'index. Le premier, de type unique, est créé
automatiquement par Oracle lorsque vous définissez une colonne de table avec une contrainte
PRIMARY KEY ou UNIQUE. Le nom de l'index est celui qui est attribué à la contrainte.
L'autre type d'index, non unique, est créé par l'utilisateur. Par exemple, vous pouvez créer un
index sur une colonne définie en FOREIGN KEY, de façon à réduire le temps d'extraction lors
d'une jointure

Création d'un Index

Créez un index sur une ou plusieurs colonnes à l'aide de l'ordre CREATE INDEX.
Dans la syntaxe :
CREATE INDEX index
ON TABLE (column[, column...]);
index représente le nom de l'index
table représente le nom de la table
column représente le nom de la colonne de la table à indexer

Visez l'Efficacité Plutôt que la Quantité

Un grand nombre d'index dans une table n'accélère pas forcément l'exécution des requêtes.
Chaque opération LMD validée dans une table comportant des index implique que les index doivent
être mis à jour. Plus vous associez d'index à une table, plus la mise à jour de tous les index par
Oracle sera longue après une opération LMD.

Quand Faut-il Créer un Index

 Lorsqu'une colonne est fréquemment utilisée dans la clause WHERE ou dans une condition de
jointure.
 Lorsqu'une colonne contient un large éventail de valeurs.
 Lorsqu'une colonne contient un grand nombre de valeurs NULL.
 Lorsque deux ou plusieurs colonnes sont fréquemment utilisées conjointement dans une clause
WHERE ou une condition de jointure.
 Lorsque la table est de grande taille et que la plupart des requêtes doivent extraire moins de 2 à
4 % de lignes.

Quand Ne Faut-il pas Créer d'Index

 Lorsque la table est de petite taille.
 Lorsque les colonnes ne sont pas souvent utilisées en tant que condition dans une requête.
 Lorsque la plupart des requêtes sont prévues pour extraire plus de 2 à 4 % de lignes.
 Lorsque la table est fréquemment mise à jour. Si une table comporte un ou plusieurs index, les
temps d'accès des ordres LMD à la table sont plus longs, car les index doivent être mis à jour.

Vérification des Index

Vérifiez l'existence de vos index à partir de la vue USER_INDEXES du dictionnaire de
données. Vous pouvez également vérifier les colonnes impliquées dans des index en interrogeant la
vue USER_IND_COLUMNS

Suppression d'un Index

Pour modifier un index, vous devez le supprimer puis le recréer. Pour supprimer une
définition d'index du dictionnaire de données, utiliser l'ordre DROP INDEX. Pour supprimer un

47
index, vous devez en être le propriétaire ou avoir le privilège DROP ANY INDEX.
Dans la syntaxe :
DROP INDEX index
index représente le nom de l'index.

Création d'un Synonyme pour un Objet

Pour vous référer à une table appartenant à un autre utilisateur, vous devez préfixer le nom de
la table par le nom de l'utilisateur qui l'a créée, en séparant les deux noms par un point. La création
d'un synonyme élimine la contrainte de désigner l'objet avec son schéma, et permet d'attribuer un
autre nom à une table, une vue, une séquence, une procédure ou d'autres objets. Cette méthode
s'avère particulièrement utile pour les noms d'objets longs, tels que des vues. Dans la syntaxe :
CREATE [PUBLIC] SYNONYM synonym
FOR object ;
PUBLIC crée un synonyme accessible à tous les utilisateurs
synonym représente le nom du synonyme à créer
object identifie l'objet pour lequel le synonyme est créé

Contrôle des Accès Utilisateur

Dans un environnement multi-utilisateur, l'accès et l'utilisation de la base de données doivent
être contrôlés à des fins de sécurité. Dans Oracle, vous pouvez :
 Contrôler l'accès à la base de données
 Autoriser l'accès à des objets spécifiques de la base de données
 Confirmer les privilèges accordés et reçus dans le dictionnaire de données Oracle
 Créer des synonymes pour les objets de la base de données
La sécurité de la base de données peut être classée en deux catégories : la sécurité du système
et la sécurité des données. La sécurité du système couvre l'accès à la base de données et son
utilisation au niveau du système (nom de l'utilisateur et mot de passe, espace disque alloué aux
utilisateurs et opérations système autorisées par l'utilisateur). La sécurité de la base de données
couvre l'accès aux objets de la base de données et leur utilisation, ainsi que les actions exécutées sur
ces objets par les utilisateurs.

Privilèges
Un privilège donne le droit d'exécuter certains ordres SQL. L'administrateur de base de
données est un utilisateur de haut niveau qui est habilité à autoriser les utilisateurs à accéder à la
base de données et à ses objets. Les utilisateurs doivent obtenir des privilèges système pour pouvoir
accéder à la base de données, et des privilèges objets pour pouvoir manipuler le contenu des objets
de la base de données. Ils peuvent aussi avoir reçu le droit d'accorder des privilèges supplémentaires
à d'autres utilisateurs ou à des rôles, qui sont des regroupements de privilèges.

Schéma
Un schéma est une collection d'objets, tels que des tables, des vues et des séquences. Un
schéma appartient à un utilisateur de la base de données et porte le même nom que ce dernier.

Privilèges Système
II existe plus de 100 privilèges système destinés aux utilisateurs et aux rôles. En général, ces
privilèges sont accordés par l'administrateur de base de données.

Privilèges Types de l'Administrateur de Base de Données

48
 Privilège système Opérations autorisées
CREATE USER Permet au bénéficiaire de créer d'autres utilisateurs
Oracle (privilège obligatoire pour le rôle d'administrateur
de base de données)
DROP USER Supprime un utilisateur

DROP ANY TABLE Supprime une table d'un schéma

BACKUP ANY TABLE Sauvegarde une table dans un schéma à l'aide de
l'utilitaire d'export

Privilèges Types Accordés à un Utilisateur

Après avoir créé un utilisateur, l'administrateur de base de données peut lui attribuer des
privilèges. Dans la syntaxe :
GRANT privilège [.privilège...]
TO user [,user ...] ;
privilège représente le privilège système à accorder
user représente le nom de l'utilisateur

Privilège Système Opérations autorisées

CREATE SESSION Connexion à la base de données
CREATE TABLE Création de tables dans le schéma de l'utilisateur
CREATE SEQUENCE Création d'une séquence dans le schéma de l'utilisateur
CREATE VIEW Création d'une vue dans le schéma de l'utilisateur
CREATE PROCEDURE Création d'une procédure, d'une fonction ou d'un package
stocké dans le schéma de l'utilisateur

Qu'est-ce qu'un Rôle ?

Un rôle est un groupe nommé de privilèges connexes qui peut être accordé à un utilisateur.
Cette méthode facilite l'octroi et le retrait des privilèges.
Un utilisateur peut avoir accès à plusieurs rôles, et le même rôle peut être attribué à plusieurs
utilisateurs. En général, les rôles sont créés pour une application de base de données.

Création et Attribution d'un Rôle

L'administrateur doit d'abord créer le rôle. Ensuite, il attribue des privilèges au rôle, puis le
rôle à des utilisateurs.
Syntaxe CREATE ROLE rôle;
où : rôle représente le nom du rôle à créer
Après avoir créé un rôle, l'administrateur de base de données attribue ce rôle à des
utilisateurs, puis des privilèges au rôle, à l'aide de l'ordre GRANT.

Privilèges Objet
Un privilège objet est un droit qui permet d'exécuter une action donnée sur une table, une
vue, une séquence, une procédure, une fonction ou un package spécifique. A chaque objet
correspond un ensemble de privilèges qui peut être accordé. Le tableau ci-dessus donne la liste des
privilèges associés à chaque objet. A noter que SELECT et ALTER sont les seuls privilèges qui

49
s'appliquent à une séquence. Les privilèges UPDATE, REFERENCES et INSERT peuvent être
limités, et ne s'appliquer qu'à une ou plusieurs colonnes. Il est possible
de limiter le privilège SELECT en créant une vue avec un sous-ensemble de colonnes et en
attribuant le privilège SELECT à la vue. Une autorisation associée à un synonyme peut être
convertie en une autorisation sur la table de base à laquelle le synonyme fait référence.

Octroi de Privilèges Objet

Différents privilèges objet sont disponibles pour les différents types d'objets de schéma. Un
utilisateur bénéficie automatiquement de tous les privilèges objet pour les objets contenus dans son
schéma. De plus, il peut accorder un privilège lié à un objet du schéma qui lui appartient à un autre
utilisateur ou à un rôle. Si l'octroi inclut le mot-clé GRANT OPTION, le bénéficiaire peut à son
tour accorder le privilège objet à d'autres utilisateurs ; sinon, il peut exploiter ce privilège mais ne
peut pas le transmettre. Dans la syntaxe :

GRANT {object_priv |(columns)]|ALL}

ON object
TO {user | role | PUBLIC}
[WITH GRANT OPTION];
object priv représente le privilège objet à accorder
culumns indique la colonne d'une table ou d'une vue à laquelle
des privilèges sont accordés
ALL représente tous les privilèges objet
ON object représente l'objet auquel des privilèges sont accordés
TO identifie l'utilisateur ou le rôle auquel le privilège est
accordé
PUBLIC accorde les privilèges objet à tous les utilisateurs
WITH GRANT OPTION autorise le bénéficiaire à accorder les privilèges objet
à d'autres utilisateurs et à des rôles.

Règles
 Pour accorder des privilèges sur un objet, il doit se trouver dans votre propre schéma ou les
privilèges objet doivent vous avoir été accordés avec le mot-clé WITH GRANT OPTION.
 Le propriétaire d'un objet peut accorder un privilège lié à cet objet à un autre utilisateur ou à un
rôle de la base de données.
 Le propriétaire d'un objet acquiert automatiquement tous les privilèges de l'objet.

Retrait des Privilèges Objet

Retirez les privilèges accordés à d'autres utilisateurs a l’aide de l'ordre REVOKE. Lorsque
vous recourez à cet ordre, les privilèges indiqués sont retirés aux utilisateurs que vous nommez ainsi
qu'à tous ceux auxquels ces privilèges ont pu être accordés grâce à l'option WITH GRANT
OPTION. Dans la syntaxe :

REVOKE {privilège [, privelege...]|ALL}

ON object
FROM {user[,user...] |role|PUBLIC}
[CASCADE CONSTRAINTSj;

CASCADE est obligatoire pour retirer toute contrainte d'intégrité référentielle

CONSTRAINTS portant sur l'objet grâce au privilège REFERENCES

Remarque : Si un privilège est accordé avec le mot-clé WITH GRANT OPTION à un utilisateur,
50
ce dernier peut également accorder ce privilège de façon à former une chaîne de bénéficiaires, mais
les octrois circulaires ne sont pas autorisés. Si le propriétaire retire un privilège à un utilisateur qui a
accordé le même privilège à d'autres utilisateurs, l'ordre REVOKE s'applique en cascade à tous les
utilisateurs ayant reçu ce privilège. Par exemple, si l'utilisateur A accorde à l'utilisateur B le
privilège SELECT dans une table en incluant le mot-clé WITH GRANT OPTION, l'utilisateur B
peut accorder à l'utilisateur C le même privilège avec le même mot-clé, et l'utilisateur C peut
également accorder ce privilège et ce mot-clé à l'utilisateur D. Si l'utilisateur A retire le privilège à
l'utilisateur B, le même privilège sera également retiré aux utilisateurs C et D.

51