Accueil > Informatique > Base de Données > Le modèle Relationnel et le langage SQL

Le modèle Relationnel et le langage SQL

mardi 26 août 2008, par frederic

Introduction

La puissance du modèle relationnel offre aux utilisateurs la possibilité d’analyser les données par l’intermédiaire de requêtes interactives et d’états sans recourir à l’assistance de programmeurs.
A la fin des années quatre-vingt, les avancées constantes, à la fois dans le domaine des technologies matérielles et dans celui des systèmes relationnels, ont renforcé l’intégration de la base de données relationnelle dans les systèmes de traitement transactionnel.

Les bases de données relationnelle actuelles disposent d’un certain nombre de fonctionnalités très utiles que l’article original de E. F. Codd ne mentionnait pas. Toutefois, au moment de la rédaction de cet exposé, aucune base de données du marché ne comportait l’intégralité des règles du système relationnel de Codd.

Aujourd’hui, la base de données relationnelle se trouve au cœur des systèmes d’information de nombreuses organisations, publiques ou privées, grandes ou petites. Il existe de nombreux éditeurs de systèmes de gestion de bases de données relationnels (SGBDR), parmi lesquels Oracle, Sybase, IBM, Informix, Microsoft, et Computer Associates. De ce groupe, Oracle a émergé comme leader. Son moteur de SGBDR a été implémenté sur plus de plates-formes que toute autre base de données. C’est la raison pour laquelle un grand nombre de concepteurs de logiciels l’ont choisi comme plate-forme de prédilection.

Un SGBDR (système de gestion de base de données relationnel) est un logiciel qui permet de gérer une base de données relationnelle. Un tel système accepte l’utilisation d’instructions déclaratives pour décrire les règles régissant les données de la base. Cette fonctionnalité s’appelle intégrité déclarative. Le SGBDR est le composant central d’une architecture client-serveur qui sera abordée dans ce chapitre.

SQL (Structured Query Language) n’est pas un langage procédural, comme C ou COBOL qui décrivent précisément les procédures d’accès et de manipulation des données. A la différence de ces langages, SQL se contente d’indiquer ce qui doit être fait. Le système de gestion de base de données se charge ensuite de déterminer comment exécuter la requête. SQL existe en tant que standard industriel ANSI (American National Standards Institute) et ISO (International Standards Organization).

L’implémentation SQL d’Oracle respecte le niveau 2 des standards ANSI X3.135-1989 et ISO 9075-1989 avec une prise en charge totale de la fonction d’amélioration de l’intégrité, Comme d’autres fournisseurs de bases de données, Oracle fournit de nombreuses extensions au SQL ANSI.

En 1970, l’ACM, un respectable journal informatique américain, publiait un article intitulé "Un modèle de données relationnel pour les grandes banques de données partagées". L’auteur de cet article était le Dr E. F. Codd, un membre du laboratoire de recherche IBM de San José. Le sujet jetait les fondements mathématiques et théoriques du concept de la base de données relationnelle.

Il est difficile de faire état d’un autre article ayant eu autant d’impact auprès des constructeurs, des informaticiens et des utilisateurs.

Une base de données relationnelle est un système d’information présentant les données sous forme de lignes regroupées au sein de tables, chaque table étant composée d’une ou de plusieurs colonnes.

Dans son article, Codd décrivait les éléments d’une base de données relationnelle : relations, attributs, domaines, et les opérateurs relationnels. Il présentait un système de stockage de données pourvu de trois caractéristiques qui faisaient jusque-là terriblement défaut :

– Indépendance logique des données.
Cette caractéristique tant attendue signifiait que les changements apportés à un attribut (une colonne), par exemple, une augmentation ou diminution de sa taille, ne devaient avoir aucune incidence sur les autres attributs de la même relation (une table).
Cette indépendance logique des données semblait très intéressante aux yeux des organisations spécialisées dans le traitement de données, car elle permettait de réduire de façon conséquente le coût de la maintenance logicielle.

– Intégrité référentielle et intégrité des données.
A la différence des autres systèmes, une base de données relationnelle libérait l’application de la charge d’assurer les contraintes d’intégrité. Codd décrivait deux autres caractéristiques propres à la base de données relationnelle, l’intégrité référentielle et l’intégrité des données.

– Requête spécifique.

Cette caractéristique devait permettre à l’utilisateur d’indiquer à la base quelles données
extraire sans avoir à spécifier de quelle manière la tâche devait s’effectuer.

Il est important de bien comprendre les limitations des systèmes de bases de données de l’époque. L’utilisateur moyen ne pouvait récupérer des données selon des critères déterminés que par l’intermédiaire de programmes conçus spécialement à cet effet.

Pour une opération aussi élémentaire qu’agrandir la largeur d’un champ existant, de sorte qu’il puisse accepter plus de chiffres d’un numéro de téléphone par exemple, les programmeurs chargés de la maintenance devaient replancher sur d’innombrables programmes, aboutissant à un simple ajustement d’offset (le décalage) entre deux champs. Même si beaucoup de ces programmes pouvaient ne pas contenir de relation directe avec le numéro de téléphone en question, il fallait tenir compte de l’effet de vague que produisait le changement de taille du champ correspondant.

Un certain temps s’est écoulé avant qu’un produit commercialisé n’intègre réellement certaines des caractéristiques ainsi définies. Au début des années quatre-vingt, la base de données relationnelle constitua le fondement sur lequel se sont appuyés les systèmes d’aide à la décision.

Le Modèle physique des Travaux Pratiques

1.1 Génération des fichiers de stockage et des utilisateurs

create tablespace TP_IDX datafile 'C:\ORACLE\ORADATA\ A4\TP_IDX.DBF' size 40M reuse;
create tablespace TP_USR datafile 'C:\ ORACLE\ORADATA\ A4\TP_USR.DBF' size 80M reuse;
/
create user A4 identified by A4;
grant connect to A4;
grant resource to A4;

1.2 Génération des séquences

PROMPT Creating Sequence 'NUM_FOURNISSEUR'
CREATE SEQUENCE NUM_FOURNISSEUR
 NOMAXVALUE
 NOMINVALUE
 NOCYCLE
 NOCACHE
/
PROMPT Creating Sequence 'NUM_CLIENT'
CREATE SEQUENCE NUM_CLIENT
 NOMAXVALUE
 NOMINVALUE
 NOCYCLE
 NOCACHE
/
PROMPT Creating Sequence 'NUM_PRODUIT'
CREATE SEQUENCE NUM_PRODUIT
 NOMAXVALUE
 NOMINVALUE
 NOCYCLE
 NOCACHE
/
PROMPT Creating Sequence 'NUM_COMMANDE'
CREATE SEQUENCE NUM_COMMANDE
 NOMAXVALUE
 NOMINVALUE
 NOCYCLE
 NOCACHE
/
PROMPT Creating Sequence 'NUM_LIGNE_COMMANDE'
CREATE SEQUENCE NUM_LIGNE_COMMANDE
 NOMAXVALUE
 NOMINVALUE
 NOCYCLE
 NOCACHE
/

1.3 Génération des tables

PROMPT Creating Table 'TYPE_FOURNISSEUR'
CREATE TABLE TYPE_FOURNISSEUR
 (TYPE_FOURNISSEUR VARCHAR2(1) NOT NULL
 ,LIBELLE_TYPE_FOURNISSEUR VARCHAR2(30) NOT NULL
 )
 STORAGE
 (
 INITIAL 50K
 NEXT 50K
 PCTINCREASE 0
 MINEXTENTS 1
 MAXEXTENTS 100
 )
 TABLESPACE TP_USR
/

PROMPT Creating Table 'TYPE_COMMANDE'
CREATE TABLE TYPE_COMMANDE
 (TYPE_COMMANDE VARCHAR2(1) NOT NULL
 ,LIBELLE_TYPE_COMMANDE VARCHAR2(30) NOT NULL
 )
 STORAGE
 (
 INITIAL 50K
 NEXT 50K
 PCTINCREASE 0
 MINEXTENTS 1
 MAXEXTENTS 100
 )
 TABLESPACE TP_USR
/

PROMPT Creating Table 'FOURNISSEUR'
CREATE TABLE FOURNISSEUR
 (NUM_FOURNISSEUR NUMBER(7,0) NOT NULL
 ,NOM_FOURNISSEUR VARCHAR2(40) NOT NULL
 ,TYPE_FOURNISSEUR VARCHAR2(1)
 ,ADRESSE_1_FOURNISSEUR VARCHAR2(40) NOT NULL
 ,ADRESSE_2_FOURNISSEUR VARCHAR2(40)
 ,CP_FOURNISSEUR VARCHAR2(5) NOT NULL
 ,VILLE_FOURNISSEUR VARCHAR2(40) NOT NULL
 ,DATE_CREATION DATE NOT NULL
 )
 STORAGE
 (
 INITIAL 50K
 NEXT 50K
 PCTINCREASE 0
 MINEXTENTS 1
 MAXEXTENTS 100
 )
 TABLESPACE TP_USR
/

PROMPT Creating Table 'LIGNE_COMMANDE'
CREATE TABLE LIGNE_COMMANDE
 (NUM_LIGNE_COMMANDE NUMBER(7,0) NOT NULL
 ,NUM_COMMANDE NUMBER(7,0) NOT NULL
 ,NUM_PRODUIT NUMBER(7,0) NOT NULL
 ,QTE NUMBER(4) NOT NULL
 ,PU_HT NUMBER(6,2) NOT NULL
 ,REMISE NUMBER(3) NOT NULL
 )
 STORAGE
 (
 INITIAL 50K
 NEXT 50K
 PCTINCREASE 0
 MINEXTENTS 1
 MAXEXTENTS 100
 )
 TABLESPACE TP_USR
/

PROMPT Creating Table 'STATUS_COMMANDE'
CREATE TABLE STATUS_COMMANDE
 (STATUS_COMMANDE VARCHAR2(1) NOT NULL
 ,LIBELLE_STATUS_COMMANDE VARCHAR2(30) NOT NULL
 )
 STORAGE
 (
 INITIAL 50K
 NEXT 50K
 PCTINCREASE 0
 MINEXTENTS 1
 MAXEXTENTS 100
 )
 TABLESPACE TP_USR
/

PROMPT Creating Table 'CLIENT'
CREATE TABLE CLIENT
 (NUM_CLIENT NUMBER(7,0) NOT NULL
 ,NOM_CLIENT VARCHAR2(40) NOT NULL
 ,TYPE_CLIENT VARCHAR2(1)
 ,ADRESSE_1_CLIENT VARCHAR2(40) NOT NULL
 ,ADRESSE_2_CLIENT VARCHAR2(40)
 ,CP_CLIENT VARCHAR2(5) NOT NULL
 ,VILLE_CLIENT VARCHAR2(40) NOT NULL
 ,DATE_CREATION DATE NOT NULL
 )
 STORAGE
 (
 INITIAL 50K
 NEXT 50K
 PCTINCREASE 0
 MINEXTENTS 1
 MAXEXTENTS 100
 )
 TABLESPACE TP_USR
/
PROMPT Creating Table 'PRODUIT'
CREATE TABLE PRODUIT
 (NUM_PRODUIT NUMBER(7,0) NOT NULL
 ,NUM_FOURNISSEUR NUMBER(7) NOT NULL
 ,LIBELLE_PRODUIT VARCHAR2(30) NOT NULL
 ,QTE_STOCK NUMBER(7) NOT NULL
 ,QTE_LIMITE NUMBER(7,0) NOT NULL
 )
 STORAGE
 (
 INITIAL 50K
 NEXT 50K
 PCTINCREASE 0
 MINEXTENTS 1
 MAXEXTENTS 100
 )
 TABLESPACE TP_USR
/

PROMPT Creating Table 'ENTETE_COMMANDE'
CREATE TABLE ENTETE_COMMANDE
 (NUM_COMMANDE NUMBER(7,0) NOT NULL
 ,DATE_COMMANDE DATE NOT NULL
 ,NUM_CLIENT NUMBER(7,0) NOT NULL
 ,TYPE_COMMANDE VARCHAR2(1) NOT NULL
 ,STATUS_COMMANDE VARCHAR2(1) NOT NULL
 )
 STORAGE
 (
 INITIAL 50K
 NEXT 50K
 PCTINCREASE 0
 MINEXTENTS 1
 MAXEXTENTS 100
 )
 TABLESPACE TP_USR
/

PROMPT Creating Table 'TYPE_CLIENT'
CREATE TABLE TYPE_CLIENT
 (TYPE_CLIENT VARCHAR2(1) NOT NULL
 ,LIBELLE_TYPE_CLIENT VARCHAR2(30) NOT NULL
 )
 STORAGE
 (
 INITIAL 50K
 NEXT 50K
 PCTINCREASE 0
 MINEXTENTS 1
 MAXEXTENTS 100
 )
 TABLESPACE TP_USR
/

1.4 Génération des clés primaires

PROMPT Creating Primary Key on 'TYPE_FOURNISSEUR'
ALTER TABLE TYPE_FOURNISSEUR
 ADD CONSTRAINT PK_TYPE_FOURNISSEUR PRIMARY KEY 
  (TYPE_FOURNISSEUR)
 USING INDEX 
 STORAGE
 (
 INITIAL 20K
 NEXT 20K
 PCTINCREASE 0
 MINEXTENTS 1
 MAXEXTENTS 100
 )
 TABLESPACE TP_IDX
/

PROMPT Creating Primary Key on 'TYPE_COMMANDE'
ALTER TABLE TYPE_COMMANDE
 ADD CONSTRAINT PK_TYPE_COMMANDE PRIMARY KEY 
  (TYPE_COMMANDE)
 USING INDEX 
 STORAGE
 (
 INITIAL 20K
 NEXT 20K
 PCTINCREASE 0
 MINEXTENTS 1
 MAXEXTENTS 100
 )
 TABLESPACE TP_IDX
/

PROMPT Creating Primary Key on 'FOURNISSEUR'
ALTER TABLE FOURNISSEUR
 ADD CONSTRAINT PK_FOURNISSEUR PRIMARY KEY 
  (NUM_FOURNISSEUR)
 USING INDEX 
 STORAGE
 (
 INITIAL 20K
 NEXT 20K
 PCTINCREASE 0
 MINEXTENTS 1
 MAXEXTENTS 100
 )
 TABLESPACE TP_IDX
/

PROMPT Creating Primary Key on 'LIGNE_COMMANDE'
ALTER TABLE LIGNE_COMMANDE
 ADD CONSTRAINT PK_LIGNE_COMMANDE PRIMARY KEY 
  (NUM_LIGNE_COMMANDE)
 USING INDEX 
 STORAGE
 (
 INITIAL 20K
 NEXT 20K
 PCTINCREASE 0
 MINEXTENTS 1
 MAXEXTENTS 100
 )
 TABLESPACE TP_IDX
/

PROMPT Creating Primary Key on 'STATUS_COMMANDE'
ALTER TABLE STATUS_COMMANDE
 ADD CONSTRAINT PK_STATUS_COMMANDE PRIMARY KEY 
  (STATUS_COMMANDE)
 USING INDEX 
 STORAGE
 (
 INITIAL 20K
 NEXT 20K
 PCTINCREASE 0
 MINEXTENTS 1
 MAXEXTENTS 100
 )
 TABLESPACE TP_IDX
/

PROMPT Creating Primary Key on 'CLIENT'
ALTER TABLE CLIENT
 ADD CONSTRAINT PK_CLIENT PRIMARY KEY 
  (NUM_CLIENT)
 USING INDEX 
 STORAGE
 (
 INITIAL 20K
 NEXT 20K
 PCTINCREASE 0
 MINEXTENTS 1
 MAXEXTENTS 100
 )
 TABLESPACE TP_IDX
/
PROMPT Creating Primary Key on 'PRODUIT'
ALTER TABLE PRODUIT
 ADD CONSTRAINT PK_PRODUIT PRIMARY KEY 
  (NUM_PRODUIT)
 USING INDEX 
 STORAGE
 (
 INITIAL 20K
 NEXT 20K
 PCTINCREASE 0
 MINEXTENTS 1
 MAXEXTENTS 100
 )
 TABLESPACE TP_IDX
/

PROMPT Creating Primary Key on 'ENTETE_COMMANDE'
ALTER TABLE ENTETE_COMMANDE
 ADD CONSTRAINT PK_ENTETE_COMMANDE PRIMARY KEY 
  (NUM_COMMANDE)
 USING INDEX 
 STORAGE
 (
 INITIAL 20K
 NEXT 20K
 PCTINCREASE 0
 MINEXTENTS 1
 MAXEXTENTS 100
 )
 TABLESPACE TP_IDX
/

PROMPT Creating Primary Key on 'TYPE_CLIENT'
ALTER TABLE TYPE_CLIENT
 ADD CONSTRAINT PK_TYPE_CLIENT PRIMARY KEY 
  (TYPE_CLIENT)
 USING INDEX 
 STORAGE
 (
 INITIAL 20K
 NEXT 20K
 PCTINCREASE 0
 MINEXTENTS 1
 MAXEXTENTS 100
 )
 TABLESPACE TP_IDX
/

1.5 Génération des clés étrangères

PROMPT Creating Foreign Keys on 'FOURNISSEUR'
ALTER TABLE FOURNISSEUR ADD CONSTRAINT
 FK_FOURNISSEUR_1 FOREIGN KEY 
  (TYPE_FOURNISSEUR) REFERENCES TYPE_FOURNISSEUR
  (TYPE_FOURNISSEUR)
/

PROMPT Creating Foreign Keys on 'LIGNE_COMMANDE'
ALTER TABLE LIGNE_COMMANDE ADD CONSTRAINT
 FK_LIGNE_COMMANDE_1 FOREIGN KEY 
  (NUM_COMMANDE) REFERENCES ENTETE_COMMANDE
  (NUM_COMMANDE) ADD CONSTRAINT
 FK_LIGNE_COMMANDE_2 FOREIGN KEY 
  (NUM_PRODUIT) REFERENCES PRODUIT
  (NUM_PRODUIT)
/

PROMPT Creating Foreign Keys on 'CLIENT'
ALTER TABLE CLIENT ADD CONSTRAINT
 FK_TYPE_CLIENT_1 FOREIGN KEY 
  (TYPE_CLIENT) REFERENCES TYPE_CLIENT
  (TYPE_CLIENT)
/

PROMPT Creating Foreign Keys on 'PRODUIT'
ALTER TABLE PRODUIT ADD CONSTRAINT
 FK_PRODUIT_1 FOREIGN KEY 
  (NUM_FOURNISSEUR) REFERENCES FOURNISSEUR
  (NUM_FOURNISSEUR)
/

PROMPT Creating Foreign Keys on 'ENTETE_COMMANDE'
ALTER TABLE ENTETE_COMMANDE ADD CONSTRAINT
 FK_ENTETE_COMMANDE_3 FOREIGN KEY 
  (TYPE_COMMANDE) REFERENCES TYPE_COMMANDE
  (TYPE_COMMANDE) ADD CONSTRAINT
 FK_ENTETE_COMMANDE_1 FOREIGN KEY 
  (NUM_CLIENT) REFERENCES CLIENT
  (NUM_CLIENT) ADD CONSTRAINT
 FK_ENTETE_COMMANDE_2 FOREIGN KEY 
  (STATUS_COMMANDE) REFERENCES STATUS_COMMANDE
  (STATUS_COMMANDE)
/

1.6 Insertion des données

— Alimentation des tables de paramétrage

Insert into TYPE_FOURNISSEUR values ('A','Artisan');
Insert into TYPE_FOURNISSEUR values ('I','Industriel');
Insert into TYPE_FOURNISSEUR values ('C','Centrale d''achat');

Insert into TYPE_CLIENT values ('N','National');
Insert into TYPE_CLIENT values ('R','Régional');
Insert into TYPE_CLIENT values ('I','International');

Insert into TYPE_COMMANDE values ('C','Courrier');
Insert into TYPE_COMMANDE values ('T','Téléphone');
Insert into TYPE_COMMANDE values ('M','Minitel');
Insert into TYPE_COMMANDE values ('I','Internet');

Insert into STATUS_COMMANDE values ('E','En cours');
Insert into STATUS_COMMANDE values ('V','Validée');
Insert into STATUS_COMMANDE values ('F','Facturée');

— Création de la commande 1

Insert into CLIENT values (NUM_CLIENT.NEXTVAL,'DURANT','N','Rue des affaires',null,'59650','VILLEUNEUVE D''ASCQ',sysdate);
Insert into FOURNISSEUR values (NUM_FOURNISSEUR.NEXTVAL,'DUPONT','C','Rue de la vente',null,'59290','WASQUEHAL',sysdate);

Insert into ENTETE_COMMANDE values (NUM_COMMANDE.NEXTVAL,sysdate,NUM_CLIENT.CURRVAL,'C','E');
Insert into PRODUIT values (NUM_PRODUIT.NEXTVAL,NUM_FOURNISSEUR.CURRVAL,'Pantalon Levis 501 Taille S',25,10);

Insert into LIGNE_COMMANDE values (NUM_LIGNE_COMMANDE.NEXTVAL,NUM_COMMANDE.CURRVAL,NUM_PRODUIT.CURRVAL,1,350,0);

Insert into PRODUIT values (NUM_PRODUIT.NEXTVAL,NUM_FOURNISSEUR.CURRVAL,'Pantalon Levis 501 Taille L',25,10);
Insert into PRODUIT values (NUM_PRODUIT.NEXTVAL,NUM_FOURNISSEUR.CURRVAL,'Pantalon Levis 501 Taille XL',25,10);
Insert into PRODUIT values (NUM_PRODUIT.NEXTVAL,NUM_FOURNISSEUR.CURRVAL,'Pantalon Levis 501 Taille XXL',25,10);

Insert into LIGNE_COMMANDE values (NUM_LIGNE_COMMANDE.NEXTVAL,NUM_COMMANDE.CURRVAL,NUM_PRODUIT.CURRVAL,1,400,0);

— Création de la commande 2

Insert into CLIENT values (NUM_CLIENT.NEXTVAL,'MIRZA','N','Rue des achats',null,'59000','LILLE',sysdate);
Insert into FOURNISSEUR values (NUM_FOURNISSEUR.NEXTVAL,'FELIX','C','Rue des magasins',null,'59290','WASQUEHAL',sysdate);

Insert into ENTETE_COMMANDE values (NUM_COMMANDE.NEXTVAL,sysdate,NUM_CLIENT.CURRVAL,'C','E');
Insert into PRODUIT values (NUM_PRODUIT.NEXTVAL,NUM_FOURNISSEUR.CURRVAL,'Veste Levis 501 Taille S',25,10);

Insert into LIGNE_COMMANDE values (NUM_LIGNE_COMMANDE.NEXTVAL,NUM_COMMANDE.CURRVAL,NUM_PRODUIT.CURRVAL,1,400,0);

Insert into PRODUIT values (NUM_PRODUIT.NEXTVAL,NUM_FOURNISSEUR.CURRVAL,'Veste Levis 501 Taille L',25,10);
Insert into PRODUIT values (NUM_PRODUIT.NEXTVAL,NUM_FOURNISSEUR.CURRVAL,'Veste Levis 501 Taille XL',25,10);
Insert into PRODUIT values (NUM_PRODUIT.NEXTVAL,NUM_FOURNISSEUR.CURRVAL,'Veste Levis 501 Taille XXL',25,10);

Insert into LIGNE_COMMANDE values (NUM_LIGNE_COMMANDE.NEXTVAL,NUM_COMMANDE.CURRVAL,NUM_PRODUIT.CURRVAL,1,500,0);

— Création de la commande 3

Insert into CLIENT values (NUM_CLIENT.NEXTVAL,'BARNABE',null,'Rue du sac',null,'59000','LILLE',sysdate);
Insert into FOURNISSEUR values (NUM_FOURNISSEUR.NEXTVAL,'HONORE','C','Rue du molinel',null,'5900','LILLE',sysdate);

Insert into ENTETE_COMMANDE values (NUM_COMMANDE.NEXTVAL,sysdate,NUM_CLIENT.CURRVAL,'C','E');
Insert into PRODUIT values (NUM_PRODUIT.NEXTVAL,NUM_FOURNISSEUR.CURRVAL,'SAC Levis 501',14,10);
Insert into LIGNE_COMMANDE values (NUM_LIGNE_COMMANDE.NEXTVAL,NUM_COMMANDE.CURRVAL,NUM_PRODUIT.CURRVAL,4,400,0);

2 Le Langage de Manipulation des données (LMD)

2.1 La consultation des données et les différents opérateurs

La consultation des données se fait par le verbe SELECT qui signifie sélectionner ou extraire des données. La consultation des données ne nécessite aucun COMMIT ou ROLLBACK.

SELECT [ALL | DISTINCT] liste_de_sélection
FROM liste_de_tables
[WHERE condition]
[ [START WITH condition] CONNECT BY condition ]
[ GROUP BY liste_d'expression [HAVING condition] ]
[ (UNION | UNION ALL | INTERSECT | MINUS) commande SELECT]
[ ORDER BY (expr | position) [ASC | DESC]
[, (expr | position) [ASC | DESC ]]...]
[FOR UPDATE [OF [ [schéma.](table | vue).]colonne
[, [[schéma.](table | vue).]colonne]...] [NOWAIT]

Les clauses SELECT et FROM sont obligatoires et permettent de spécifier respectivement la liste des colonnes à extraire et la liste de tables à partir desquelles les données seront extraites. Toutes les autres clauses sont optionnelles.

Exemple 1 : Sélection de l’ensemble des clients :
SELECT * FROM CLIENT;

Exemple 2 : Sélection du numéro et du nom des clients :
SELECT NUM_CLIENT Numéro, NOM_CLIENT Nom FROM CLIENT;

La clause WHERE a pour but d’introduire des conditions à la requête grâce à des opérateurs. Il existe deux classes d’opérateurs :

Les opérateurs Unaires : Opérateur Opérande
Les opérateurs Binaires : Opérande1 Opérateur Opérande2

+, - Positif, Négatif
*, / Multiplication, Division
+, -, || Addition, Soustraction, Concatenation

=, !=, <, >, <=, >=, IS NULL, LIKE, BETWEEN, IN Comparaison

NOT Négation logique
AND Conjonction logique
OR Disjonction logique

UNION / UNION ALL Union
INTERSECT Intersection
MINUS Différence
(+) Externalisation d’une jointure

Exemple 3 : Sélection de l’ensemble des clients dont le nom commence par ‘BA’ :
SELECT * FROM CLIENT WHERE NOM_CLIENT LIKE ‘BA%’;

Exemple 4 : Sélection de l’ensemble des clients n’ayant pas de deuxième ligne d’adresse :
SELECT * FROM CLIENT WHERE ADRESSE_2_CLIENT is null;

Exemple 5 : Sélection de l’ensemble des clients ayant été créés entre le 01/01/1999 et le 31/01/1999 :

SELECT * FROM CLIENT WHERE DATE_CREATION BETWEEN to_date(‘01/01/1999’,’DD/MM/YYYY’) and to_date(‘31/01/1999’,’DD/MM/YYYY’);

2.2 Les fonctions numériques

ABS Retourne la valeur absolue d’un nombre.

SELECT ABS(-15) "Absolute" FROM DUAL;

  Absolute
----------
        15

ACOS Retourne le cosine arc d’un nombre.

SELECT ACOS(.3)"Arc_Cosine" FROM DUAL;

Arc_Cosine
----------
1.26610367

ASIN Retourne le sinus arc d’un nombre.

SELECT ASIN(.3) "Arc_Sine" FROM DUAL;

Arc_Sine
----------
.304692654

ATAN Retourne la tangente arc d’un nombre.

SELECT ATAN(.3) "Arc_Tangent" FROM DUAL;

Arc_Tangent
-----------
 .291456794

ATAN2 Retourne la tangente arc de n et m.

 
SELECT ATAN2(.3, .2) "Arc_Tangent2" FROM DUAL;
 
Arc_Tangent2
------------
  .982793723

CEIL Retourne le plus petit supérieur ou égal d’un nombre.

SELECT CEIL(15.7) "Ceiling" FROM DUAL;

   Ceiling
----------
        16

COS Retourne le cosinus d’un nombre.

SELECT COS(180 * 3.14159265359/180)
"Cosine of 180 degrees" FROM DUAL;

Cosine of 180 degrees
---------------------
                   -1

COSH Retourne le cosinus hyperbolique d’un nombre. 

SELECT COSH(0) "Hyperbolic cosine of 0" FROM DUAL;
 
Hyperbolic cosine of 0
----------------------
                     1

EXP Retourne l’exponentiel d’un nombre.

 
SELECT EXP(4) "e to the 4th power" FROM DUAL;

e to the 4th power
------------------
          54.59815

FLOOR Retourne la valeur entière d’un nombre.

SELECT FLOOR(15.7) "Floor" FROM DUAL;

     Floor
----------
        15

LN Retourne le logarithme néperién de n.

SELECT LN(95) "Natural log of 95" FROM DUAL;

Natural log of 95
-----------------
       4.55387689

LOG Retourne le logarithme à base de m, de n.

SELECT LOG(10,100) "Log base 10 of 100" FROM DUAL;

Log base 10 of 100
------------------
                 2

MOD Retourne le reste de la division entière de m par n.

SELECT MOD(11,4) "Modulus" FROM DUAL;

   Modulus
----------
         3

POWER Retorne la valeur de m à la puissnce n.

SELECT POWER(3,2) "Raised" FROM DUAL;

    Raised
----------
         9

ROUND Retourne la valeur de n arrondi à m positions à droite du point décimal.

SELECT ROUND(15.193,1) "Round" FROM DUAL;

     Round
----------
      15.2

SELECT ROUND(15.193,-1) "Round" FROM DUAL;

     Round
----------
        20

SIGN Retourne le signe d’un nombre (Si n<0, la fonction retourne -1 ; Si n=0, la fonction retourne 0 ; Si n>0, la fonction retourne 1).

SELECT SIGN(-15) "Sign" FROM DUAL;

      Sign
----------
        -1

SIN Retourne le sinus d’un nombre.

SELECT SIN(30 * 3.14159265359/180)
 "Sine of 30 degrees" FROM DUAL;

Sine of 30 degrees
------------------
                .5

SINH Retourne le sinus hyperbolique d’un nombre.

SELECT SINH(1) "Hyperbolic sine of 1" FROM DUAL;

Hyperbolic sine of 1
--------------------
          1.17520119

SQRT Retourne la racine carré d’un nombre.

SELECT SQRT(26) "Square root" FROM DUAL;

Square root
-----------
5.09901951

TAN Retourne la tangente d’un nombre.

SELECT TAN(135 * 3.14159265359/180)
"Tangent of 135 degrees"  FROM DUAL;

Tangent of 135 degrees
----------------------
                   - 1

TANH Retourne la tangente hyperbolique d’un nombre.

SELECT TANH(.5) "Hyperbolic tangent of .5" FROM DUAL;

Hyperbolic tangent of .5
------------------------
              .462117157

TRUNC Retourne la valeur n tronquée à m positions décimale.

SELECT TRUNC(15.79,1) "Truncate" FROM DUAL;

  Truncate
----------
      15.7

SELECT TRUNC(15.79,-1) "Truncate" FROM DUAL;

  Truncate
----------
        10

2.3 Les fonctions de chaînes de caractères.

a) Les fonctions de chaînes de caractères retournant une chaîne de caractères.

CHR Retourne le caractère ayant l’équivalence binaire de n.

SELECT CHR(67)||CHR(65)||CHR(84) "Dog"
  FROM DUAL;

Dog
---
CAT

SELECT CHR(16705 USING NCHAR_CS) FROM DUAL;
 
C
-
A

CONCAT Retourne la concatenation de 2 chaînes de caractères.

SELECT CONCAT( CONCAT(ename, ' is a '), job) "Job"
FROM emp
WHERE empno = 7900;

Job
-----------------
JAMES is a CLERK

INITCAP Met en majuscule la première lettre de chaque mot de la chaîne.

SELECT INITCAP('the soap') "Capitals" FROM DUAL;

Capitals
--------
The Soap

LOWER Retourne la chaîne de caractère en minuscule.

SELECT LOWER('MR. SCOTT MCMILLAN') "Lowercase" FROM DUAL;

Lowercase
------------------
mr. scott mcmillan

LPAD Retourne la chaîne ch1 complétée à gauche avec la chaîne ch2 à la longueur n.

SELECT LPAD('Page 1',15,'*.') "LPAD example" FROM DUAL;

LPAD example
---------------
*.*.*.*.*Page 1

LTRIM Enlève les caractères de gauche de la chaîne ch1 qui se trouve dans l’ensemble ch2 jusqu’à la rencontre du premier caractère non compris dans ens.

SELECT LTRIM('xyxXxyLAST WORD','xy') "LTRIM example" FROM DUAL;

LTRIM example
-------------
XxyLAST WORD

NLS_INITCAP Fonctionne de la même façon que INITCAP, à l’exception de l’introduction des paramètres NLS qui tiennent compte de la langue utilisée.

SELECT NLS_INITCAP('ijsland', 'NLS_SORT = XDutch') "Capitalized" FROM DUAL;

Capitalized
-------
IJsland

NLS_LOWER Identique à LOWER mais avec les paramètres NLS.

SELECT NLS_LOWER ('CITTA''', 'NLS_SORT = XGerman') "Lowercase" FROM DUAL;

Lower
-----
cittá

NLS_UPPER Identique à UPPER mais avec les paramètres NLS.

SELECT NLS_UPPER ('groß', 'NLS_SORT = XGerman') "Uppercase" FROM DUAL;

Upper
-----
GROSS

REPLACE Remplace dans la chaîne char chaque occurrence ch1 par ch2.

SELECT REPLACE('JACK and JUE','J','BL') "Changes" FROM DUAL;

Changes
--------------
BLACK and BLUE

RPAD Retourne la chaîne ch1 complétée à droite avec la séquence ch2 à la longueur n.

SELECT RPAD('MORRISON',12,'ab') "RPAD example" FROM DUAL;

RPAD example
------------
MORRISONabab

RTRIM Enlève les caractères de droite de la chaîne ch1 qui se trouve dans l’ensemble ch2 jusqu’à la rencontre du dernier caractère non compris dans ens.

SELECT RTRIM('BROWNINGyxXxy','xy') "RTRIM e.g." FROM DUAL;
 
RTRIM e.g
-----------
BROWNINGyxX

SOUNDEX Retourne une chaîne de caractères contenant la représentation phonétique de char.

SELECT ename FROM emp WHERE SOUNDEX(ename) = SOUNDEX('SMYTHE');
 
ENAME
----------
SMITH

SUBSTR Retourne une portion de n caractères de la chaîne char commençant à la position m.

SELECT SUBSTR('ABCDEFG',3.1,4) "Subs" FROM DUAL;
 
Subs
----
CDEF

SELECT SUBSTR('ABCDEFG',-5,4) "Subs" FROM DUAL;

Subs
----
CDEF

SUBSTRB Retourne une portion de n octets de la chaîne char commençant à la position m.

SELECT SUBSTRB('ABCDEFG',5,4.2) "Substring with bytes" FROM DUAL;

Substring with bytes
--------------------
CD

TRANSLATE Retourne la chaîne char après remplacement de toutes les occurrences source par cible.

SELECT TRANSLATE('2KRW229', '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ', 
'9999999999XXXXXXXXXXXXXXXXXXXXXXXXXX') "License" FROM DUAL;
 
License
--------
9XXX999 

SELECT TRANSLATE('2KRW229', '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ', 
'0123456789') "Translate example" FROM DUAL;
 
Translate example
-----------------
2229

UPPER Retourne la chaîne char avec toutes les lettres en majuscule.

 
SELECT UPPER('Large') "Uppercase" FROM DUAL;

Upper
-----
LARGE

b) Les fonctions de chaînes de caractères retournant un nombre.

ASCII Retourne la valeur ASCII en décimal du premier caractère de la chaîne.

SELECT ASCII('Q') FROM DUAL;
 
ASCII('Q')
----------
        81

INSTR retourne la position de char2 dans char1 avec n caractères et m occurrences.

SELECT INSTR('CORPORATE FLOOR','OR', 3, 2) "Instring" FROM DUAL;
 
  Instring
----------
        14

SELECT INSTR('CORPORATE FLOOR','OR', -3, 2)
"Reversed Instring"
     FROM DUAL;
 
Reversed Instring
-----------------
                2

INSTRB retourne la position de char2 dans char1 avec n octets et m occurrences.

SELECT INSTRB('CORPORATE FLOOR','OR',5,2) "Instring in bytes" FROM DUAL;

Instring in bytes
-----------------
               27

LENGTH Retourne lla longueur de la chaîne char en caractères.

SELECT LENGTH(’CANDIDE’) "Length in characters" FROM DUAL ;

 
Length in characters
--------------------
                   7

LENGTHB Retourne lla longueur de la chaîne char en octets.

SELECT LENGTHB ('CANDIDE') "Length in bytes" FROM DUAL;
 
Length in bytes
---------------
             14

NLSSORT Retourne la valeur de char dans un autre séquence de tri.

SELECT ename FROM emp WHERE NLSSORT (ename, 'NLS_SORT = German')
> NLSSORT ('S', 'NLS_SORT = German') ORDER BY ename;
 
ENAME
----------
SCOTT
SMITH
TURNER
WARD

IV.4.4 Les fonctions de date

ADD_MONTHS Ajout n mois à la date D. Le résultat est une date.

 
SELECT TO_CHAR(ADD_MONTHS(hiredate,1),'DD-MON-YYYY') "Next month"
FROM emp WHERE ename = 'SMITH';

Next Month
-----------
17-JAN-1981

LAST_DAY Retourne la date du dernier jour du mois de la date d.

SELECT SYSDATE, LAST_DAY(SYSDATE) "Last", LAST_DAY(SYSDATE) - SYSDATE "Days Left"
FROM DUAL;
 
SYSDATE   Last       Days Left
--------- --------- ----------
23-OCT-97 31-OCT-97          8


SELECT TO_CHAR(ADD_MONTHS(LAST_DAY(hiredate),5),'DD-MON-YYYY')"Five months"
FROM emp WHERE ename = 'MARTIN';
 
Five months
-----------
28-FEB-1982

MONTHS_BETWEEN Retourne le nombre de mois entre les dates d1 et d2.

SELECT MONTHS_BETWEEN (TO_DATE('02-02-1995','MM-DD-YYYY'), 
TO_DATE('01-01-1995','MM-DD-YYYY') ) "Months" FROM DUAL;
 
    Months
----------
1.03225806

NEXT_DAY Retourne la date du premier jour de semaine nommé par char.

SELECT NEXT_DAY('15-MAR-92','TUESDAY') "NEXT DAY" FROM DUAL;
 
NEXT DAY
---------
17-MAR-92

ROUND Retourne une date arrondie selon le format spécifié (DD ;MM,YY).

SELECT ROUND (TO_DATE ('27-OCT-92'),'YEAR') "New Year" FROM DUAL;
 
New Year
---------
01-JAN-93

SYSDATE Retourne la date et l’heure courante du système.

SELECT TO_CHAR(SYSDATE, 'MM-DD-YYYY HH24:MI:SS')"NOW" FROM DUAL;

NOW
-------------------
10-29-1993 20:27:11

TRUNC Retourne la date d tronquée selon le format spécifié.

SELECT TRUNC(TO_DATE('27-OCT-92','DD-MON-YY'), 'YEAR') "New Year" FROM DUAL;
 
New Year
---------
01-JAN-92

IV.4.5 Les fonctions de conversion

CHARTOROWID Convertit une valeur de type CHAR or VARCHAR2 à une valeur de type ROWID.

SELECT ename FROM emp WHERE ROWID = CHARTOROWID('AAAAfZAABAAACp8AAO');
 
ENAME
------
LEWIS

CONVERT Convertit une chaîne de caractères d’un ensemble de caractères à un autre

SELECT CONVERT('Groß', 'US7ASCII', 'WE8HP') "Conversion" FROM DUAL;

Conversion
----------
Gross

HEXTORAW Convertit une valeur héxadécimale en une valeur binaire.

INSERT INTO graphics (raw_column) SELECT HEXTORAW('7D') FROM DUAL;

RAWTOHEX Convertit une valeur binaire en une valeur héxadécimale.

SELECT RAWTOHEX(raw_column) "Graphics" FROM graphics;

Graphics
--------
7D

ROWIDTOCHAR Convertit une valeur de type ROWID en une valeur de type CHAR.

SELECT ROWID FROM offices WHERE ROWIDTOCHAR(ROWID) LIKE '%Br1AAB%';

ROWID
------------------
AAAAZ6AABAAABr1AAB

TO_CHAR Convertit une donnée de type DATE à une valeur de type VARCHAR2 selon le format spécifié.

SELECT TO_CHAR(HIREDATE, 'Month DD, YYYY') "New date format" FROM emp
WHERE ename = 'BLAKE';
 
New date format
------------------
May       01, 1981

TO_CHAR Convertit une donnée de type NUMBER à une valeur de type VARCHAR2 selon le format spécifié.

SELECT TO_CHAR(-10000,'L99G999D99MI') "Amount" FROM DUAL;

Amount
--------------
  $10,000.00-

SELECT TO_CHAR(-10000,'L99G999D99MI','NLS_NUMERIC_CHARACTERS = '',.''
NLS_CURRENCY = ''AusDollars'' ') "Amount" FROM DUAL;

Amount
-------------------
AusDollars10.000,00-

TO_DATE Convertit une chaîne de caractères à une valeur de type DATE selon le format spécifié.

INSERT INTO bonus (bonus_date) SELECT TO_DATE('January 15, 1989, 11:00 A.M.','Month dd, YYYY, HH:MI A.M.', 'NLS_DATE_LANGUAGE = American')
FROM DUAL;

TO_NUMBER Convertit une valeur de type CHAR ou VARCHAR2 à un nombre selon le format spécifié.

UPDATE emp SET sal = sal + TO_NUMBER('100.00', '9G999D99')
WHERE ename = 'BLAKE';

SELECT TO_NUMBER('-AusDollars100','L9G999D99',
'NLS_NUMERIC_CHARACTERS = '',.''NLS_CURRENCY = ''AusDollars'' ') "Amount" FROM DUAL;

    Amount
----------
      -100

IV.4.6 Les autres fonctions

DUMP Retourne le code du type de données, sa longueur et sa représentation interne.

SELECT DUMP('abc', 1016) FROM DUAL;

DUMP('ABC',1016)                          
------------------------------------------ 
Type=96 Len=3 CharacterSet=WE8DEC: 61,62,63 


SELECT DUMP(ename, 8, 3, 2) "OCTAL" FROM emp WHERE ename = 'SCOTT';

OCTAL
----------------------------
Type=1 Len=5: 117,124 


SELECT DUMP(ename, 10, 3, 2) "ASCII" FROM emp WHERE ename = 'SCOTT';

ASCII
----------------------------
Type=1 Len=5: 79,84

GREATEST Retourne la plus grande valeur dans la liste.

SELECT GREATEST ('HARRY', 'HARRIOT', 'HAROLD') "Great" FROM DUAL;
 
Great
-----
HARRY

LEAST Retourne la plus petite valeur de la liste.

SELECT LEAST('HARRY','HARRIOT','HAROLD') "LEAST" FROM DUAL;
 
LEAST
------
HAROLD

NVL Retourne Exp2 si Exp1 est nulle. Si non retourne Exp1.

SELECT ename, NVL(TO_CHAR(COMM), 'NOT APPLICABLE')
"COMMISSION" FROM emp WHERE deptno = 30;
 
ENAME      COMMISSION
---------- -------------------------------------
ALLEN      300
WARD       500
BLAKE      NOT APPLICABLE
TURNER     0
JAMES      NOT APPLICABLE

UID Retoure un entier qui identifie l’utilisateur courant.
USER Retourne le nom de l’utilisateur courant.

SELECT USER, UID FROM DUAL;
 
USER                                  UID
------------------------------ ----------
SCOTT                                  19

USERENV Retourne les informations sur la session courante.
– ’ISDBA’
– ’LANGUAGE’
– ’TERMINAL’
– ’SESSIONID’
– ’ENTRYID’
– ’LANG’
– ’INSTANCE’

SELECT USERENV('LANGUAGE') "Language" FROM DUAL;

Language
-----------------------------------
AMERICAN_AMERICA.WE8DEC
<code>
______________________________________________________________________________
VSIZE Retourne le nombre d’octets de la représentation interne de exp. 
<code>
SELECT ename, VSIZE (ename) "BYTES" FROM emp WHERE deptno = 10;
 
ENAME           BYTES
---------- ----------
CLARK               5
KING                4
MILLER              6

DECODE Permet de comparer une colonne ou une expression à une condition de recherche explicitée et de renvoyer un résultat qui dépend de cette comparaison.

Expression DECODE(expr,recherche,résultat[,recherche,résultat]…[,défaut])

DECODE(QTE_STOCK,0,’Stock épuisé’,10,’Stock limite’,’Stock OK’)

Une expression DECODE ne peut pas contenir plus de 255 composantes.

ROWID Permet de retourner un type interne à Oracle identifiant une ligne unique de données (permettant un accès rapide aux données).

RONUM Permet de retourner un numéro courant de l’enregistrement par rapport à la sélection.

SELECT ROWID, ROWNUM, NUM_CLIENT, SUBSTR(NOM_CLIENT,1,8) NOM FROM CLIENT;

ROWID                  ROWNUM NUM_CLIENT NOM
------------------ ---------- ---------- --------
AAABM8AABAAADnCAAA          1          1 DURANT
AAABM8AABAAADnCAAB          2          2 MIRZA
AAABM8AABAAADnCAAC          3          3 BARNABE

IV.4.7 Les fonctions d’agrégat

Plusieurs fonctions d’agrégat accepte deux types d’options :

– DISTINCT Cette option oblige la fonction d’agrégat à ne considérer que les valeurs distinctes.
– ALL Cette option oblige à la fonction à considérer toutes les valeurs y compris celles qui sont dupliquées.

AVG Retourne la moyenne des valeurs de n.

AVG(DISTINCT n)

SELECT AVG(sal) "Average" FROM emp;

   Average
----------
2077.21429

COUNT Retourne le nombre d’enregistrements d’une requête.

COUNT({* | [DISTINCT|ALL] expr})

Si vous spécifiez expr, la fonction retourne le nombre d’enregistrements ou expr n’est pas null. Si vous spécifiez l’asterisque (*), cette fonction retourne l’ensemble des enregistrements y compris dupliqués ou nulls.

SELECT COUNT(*) "Total" FROM emp;
 
   Total
--------
      18

SELECT COUNT(job) "Count" FROM emp;
 
   Count
--------
      14

SELECT COUNT(DISTINCT job) "Jobs" FROM emp;
 
     Jobs
---------
        5

MAX Retourne le maximum des valeurs de expr.

MAX([DISTINCT|ALL] expr)

SELECT MAX(sal) "Maximum" FROM emp;
 
   Maximum
----------
      5000

MIN Retourne le minimum des valeurs de expr.

MIN([DISTINCT|ALL] expr)

SELECT MIN(hiredate) "Earliest" FROM emp;
 
Earliest
---------
17-DEC-80

STDDEV Retourne l’écart type de la colonne.

STDDEV([DISTINCT|ALL] x)

SELECT STDDEV(sal) "Deviation" FROM emp;
 
 Deviation
----------
1182.50322

SUM Retourne la somme des valeurs de la requête.

SUM([DISTINCT|ALL] n)

SELECT SUM(sal) "Total" FROM emp;
 
     Total
----------
     29081

VARIANCE Retourne la variance de la colonne.

VARIANCE([DISTINCT|ALL]x)

SELECT VARIANCE(sal) "Variance" FROM emp;
 
Variance
----------
1389313.87

IV.4.8 La requête sélection et les conditions de jointure

a)L’équijointure

Une équijointure est une jointure dont la condition est une comparaison d’égalité de deux colonnes appartenant à deux tables différentes.

Exemple : Lister les clients ayant effectués une commande entre le 01/01/1999 et aujourd’hui.

SELECT DISTINCT C.NUM_CLIENT, C.NOM_CLIENT, C.VILLE_CLIENT, E.DATE_COMMANDE
FROM CLIENT C, ENTETE_COMMANDE E
WHERE C.NUM_CLIENT=E.NUM_CLIENT
AND E.DATE_COMMANDE BETWEEN TO_DATE(‘01/01/1999’,’DD/MM/YYYY’) AND SYSDATE;

b)La thétajointure

Une thétajointure est une jointure dont la condition est une comparaison de deux colonnes utilisant un opérateur autre que l’égalité.

c)La jointure multiple

Une jointure multiple est une jointure qui met en relation plusieurs colonnes de plusieurs tables.

Exemple : Lister les produits qui ont été commandés entre le 01/01/1999 et aujourd’hui.

SELECT P.NUM_PRODUIT, P.LIBELLE_PRODUIT
FROM PRODUIT P, ENTETE_COMMANDE E, LIGNE_COMMANDE L
WHERE P.NUM_PRODUIT = L.NUM_PRODUIT
AND E.NUM_COMMANDE = L.NUM_COMMANDE
AND E.DATE_COMMANDE BETWEEN TO_DATE(‘01/01/1999’,’DD/MM/YYYY’) AND SYSDATE;

d)L’autojointure

Une autojointure est une jointure d’une table avec elle-même.

Exemple : Lister tous les couples de produits dont la quantité en stock du premier est égale à deux fois celle du second.

SELECT X.NUM_PRODUIT, X.QTE_STOCK, Y.NUM_PRODUIT, Y.QTE_STOCK
FROM PRODUIT X, PRODUIT Y
WHERE X.QTE_STOCK = 2 * Y.QTE_STOCK;

e)La jointure externe

Une jointure externe est une jointure qui favorise une table par rapport à une autre. Ainsi les lignes de la table dominante seront affichées même si la condition n’est pas respectée.

Exemple : Lister les clients dont le code postal est égal à 59000.

SELECT C.NUM_CLIENT, C.NOM_CLIENT, C.CP_CLIENT, T.LIBELLE_TYPE_CLIENT
FROM TYPE_CLIENT T, CLIENT C
WHERE C.TYPE_CLIENT = T.TYPE_CLIENT
AND C.CP_CLIENT = '59000';

NUM_CLIENT NOM_CLIENT                               CP_CL
---------- ---------------------------------------- -----
LIBELLE_TYPE_CLIENT
------------------------------
         2 MIRZA                                    59000

National

Le client BARNABE ne possède pas de TYPE de CLIENT. Afin de pouvoir l’obtenir dans une sélection, il est important d’externaliser la requête par rapport à la table TYPE_CLIENT.

SELECT C.NUM_CLIENT, C.NOM_CLIENT, C.CP_CLIENT, T.LIBELLE_TYPE_CLIENT
FROM TYPE_CLIENT T, CLIENT C
WHERE C.TYPE_CLIENT = T.TYPE_CLIENT(+)
AND C.CP_CLIENT = '59000';

NUM_CLIENT NOM_CLIENT                               CP_CL
---------- ---------------------------------------- -----
LIBELLE_TYPE_CLIENT
------------------------------
         2 MIRZA                                    59000
National

         3 BARNABE                                  59000

L’exemple ci-dessus est une jointure externe droite.

IV.4.9 La requête sélection et les conditions de sous-requêtes

SQL permet de comparer une expression ou une colonne au résultat d’une autre requête SELECT. Cette condition est dite condition de sous-requête et les deux requêtes sont dites requêtes imbriquées. La sous-requête peut faire appel dans sa condition à une autre sous-requête, etc

Une condition de sous-requête peut être formulée selon l’une des possibilités suivantes :

WHEREexp opérateur_de_comparaison (ALL | ANY | SOME)(requête_SELECT)
WHERE exp [NOT] IN (requête_SELECT)
WHERE [NOT] EXISTS (requête_SELECT)

Les sous-requêtes situées après les mots clés IN, ALL, ANY et SOME doivent avoir le même nombre de colonnes que celui spécifié dans exp. L’évaluation de ces sous-requêtes peut renvoyer plusieurs valeurs qui seront interprétées comme suit :

ALL : la condition est vraie si la comparaison est vraie pour chacune des valeurs retoumées. Si l’expression de condition est de type numérique et si l’opérateur de comparaison est ’>’, la sous-requête sera équivalente à l’extraction de la valeur maximale car, si la condition est vraie pour le maximum, elle l’est aussi pour toutes les autres valeurs.

ANY : la condition est vraie si la comparaison est vraie pour au moins une des valeurs retournées. Si l’expression de condition est de type numérique et si l’opérateur de comparaison est ’<’, la sous-requête sera équivalente à l’extraction de la valeur minimale car, si la condition est vraie pour le minimum, elle l’est aussi pour toutes les autres valeurs.

SOME : représente le synonyme de ANY.

Les mots clés ALL, ANY et SOME sont inutiles si le résultat de la sous-requête renvoie une seule valeur.

IN : la condition est vraie si la comparaison est vraie pour une des valeurs retoumées par la sous-requête.

EXISTS : il est différent des autres opérateurs. Il renvoie le booléen vrai ou faux selon le résultat de la sous-requête. Si l’évaluation de la sous-requête donne lieu à une ou plusieurs ligne(s), la valeur retoumée est vraie. Cette valeur sera fausse dans le cas contraire.

Exemple : Lister les produits ayant une quantité en stock supérieure à la moyenne des quantités en stock de l’ensemble des produits.

SELECT P.NUM_PRODUIT, P.LIBELLE_PRODUIT, P.QTE_STOCK
FROM PRODUIT P
WHERE P.QTE_STOCK > (SELECT AVG(PM.QTE_STOCK) FROM PRODUIT PM);

NUM_PRODUIT LIBELLE_PRODUIT                 QTE_STOCK
----------- ------------------------------ ----------
          1 Pantalon Levis 501 Taille S            25
          2 Pantalon Levis 501 Taille L            25
          3 Pantalon Levis 501 Taille XL           25
          4 Pantalon Levis 501 Taille XXL          25
          5 Veste Levis 501 Taille S               25
          6 Veste Levis 501 Taille L               25
          7 Veste Levis 501 Taille XL              25
          8 Veste Levis 501 Taille XXL             25

Une sous-requête est définie comme une instruction SELECT apparaissant dans d’autres instructions du LMD, à savoir SELECT, UPDATE, DELETE, ou INSERT.

Dans une instruction SELECT, une sous-requête fait partie d’une condition dans la clause WHERE. Il existe certaines particularités importantes liées à l’utilisation d’une sous-requête :

La sous-requête doit apparaître entre parenthèses.

Le nombre de lignes retournées par la sous-requête doit correspondre au nombre de valeurs que la fonction ou l’opérateur attend. Dans l’exemple précédent, l’opérateur > attend une seule valeur pour sa comparaison, et la fonction AVG (qui est une fonction de type groupe) retoume une seule valeur.

Le nombre de colonnes retournées par la sous-requête doit correspondre au nombre de colonnes que la fonction ou l’opérateur attend.

La clause ORDER BY n’est pas utilisée au sein d’une sous-requête.

La création d’une nouvelle table avec l’instruction SELECT

Analysons une forme d’instruction CREATE TABLE qui utilise une requête pour spécifier la structure
d’une table à créer. Cette instruction peut se révéler pratique lors du développement et des tests d’une application.

Si vous souhaitez effectuer des tests sur le contenu d’une table, ajouter, supprimer, ou modifier des lignes, il serait judicieux de réaliser une copie de la table en question. Imaginez la situation suivante : la table contient une liste de clients sur les dix demières années, ce qui représente un nombre important de lignes, environ 10 000. Vous souhaitez utiliser un sous-ensemble de ces lignes, en particulier, celles qui concernent les clients demeurant à WASQUEHAL.

Exemple : Création d’une table à partir d’un sous-ensemble de lignes d’une autre table

CREATE TABLE CLIENT_WASQUEHAL
AS
SELECT * 
FROM CLIENT
WHERE CP_CLIENT = '59290';

Examinons maintenant la syntaxe :

CREATE TABLE nom_nouvelle_table
AS
instruction_select

Les variables sont définies ainsi :

nom_nouvelle_table est le nom de la table à créer.
instruction_select est une instruction SELECT valide.

De plus, ces deux instructions peuvent servir un autre objectif. Si vous souhaitez créer une table possédant la même structure qu’une table existante, avec les mêmes définitions de colonnes, mais vidée de ses données, vous pouvez recourir à la requête suivante :

CREATE TABLE nom_nouvelle_table_vide
AS
SELECT *
FROM nom_table_existante
WHERE 1 = 2;

Vous avez probablement remarqué que 1 n’est jamais égal à 2. C’est la raison pour laquelle aucune ligne de la table existante n’est copiée dans la nouvelle table. Cette dernière possède les mêmes définitions de colonnes, mais aucune donnée. Vous pourriez recourir à n’importe quelle expression fausse pour obtenir ce résultat.

Une nouvelle fonctionnalité de l’instruction SELECT permet à une sous-requête d’être utilisée à la place d’une table ou d’une vue dans une clause FROM.

Exemple : Lister les produits qui ont été commandés entre le 01/01/1999 et aujourd’hui.

SELECT P.NUM_PRODUIT, P.LIBELLE_PRODUIT
FROM PRODUIT P, ENTETE_COMMANDE E, LIGNE_COMMANDE L
WHERE P.NUM_PRODUIT = L.NUM_PRODUIT
AND E.NUM_COMMANDE = L.NUM_COMMANDE
AND E.DATE_COMMANDE BETWEEN TO_DATE(‘01/01/1999’,’DD/MM/YYYY’) AND SYSDATE;

Ou

SELECT P.NUM_PRODUIT, P.LIBELLE_PRODUIT
FROM
PRODUIT P,
(SELECT NUM_COMMANDE FROM ENTETE_COMMANDE WHERE DATE_COMMANDE BETWEEN TO_DATE(‘01/01/1999’,’DD/MM/YYYY’) AND SYSDATE) E,
LIGNE_COMMANDE L
WHERE P.NUM_PRODUIT = L.NUM_PRODUIT
AND E.NUM_COMMANDE = L.NUM_COMMANDE

IV.4.10 La requête sélection et le groupement des données

SQL permet de grouper des données ayant des valeurs communes. Cette possibilité est explicitée par la clause GROUP BY.

Les expressions de la clause GROUP BY peuvent contenir n’importe quelle colonne appartenant à la liste de tables de la clause FROM.

Exemple 1 : Lister le nombre de lignes par commande.

SELECT L.NUM_COMMANDE, COUNT(*)
FROM LIGNE_COMMANDE L
GROUP BY L.NUM_COMMANDE;

Exemple 2 : Lister le montant total de chaque commande.

SELECT L.NUM_COMMANDE, SUM(L.QTE*L.PU_HT)
FROM LIGNE_COMMANDE L
GROUP BY L.NUM_COMMANDE;

La condition de la clause WHERE ne pouvant en aucun cas inclure des fonctions d’agrégat, SQL offre la clause HAVING qui permet un restriction portant sur un groupe de lignes.

Exemple 3 : Lister les numéros de commande ayant un montant total supérieur à 1000 francs.

SELECT L.NUM_COMMANDE, SUM(L.QTE*L.PU_HT) MONTANT_TOTAL
FROM LIGNE_COMMANDE L
GROUP BY L.NUM_COMMANDE
HAVING SUM(L.QTE*L.PU_HT) > 1000;

IV.4.11 La requête sélection et le tri du résultat

SQL permet de réaliser des opérations de tri sur les données extraites avec une requête d’interrogation. On peut ainsi trier jusqu’à huit colonnes dans une même requête. C’est la clause ORDER BY qui réalise le tri.

SQL offre deux options de tri en ascendant avec l’option ASC (ascending) prise par défaut ou en descendant avec l’option DESC (descending) spécifiée explicitement.

Exemple 1 : Lister tous les clients par ordre de CP et de nom.

SELECT *
FROM CLIENT
ORDER BY CP_CLIENT, NOM_CLIENT;

Exemple 2 : Lister toutes les commandes par ordre décroisssant des numéros de commandes et de lignes.

SELECT NUM_COMMANDE, NUM_LIGNE_COMMANDE
FROM LIGNE_COMMANDE
ORDER BY 1,2 DESC;

IV.4.12 La requête sélection et les opérateurs ensemblistes

SQL est bâti sur les principes de l’algèbre relationnelle et la théorie des ensembles. L’union, l’intersection et la différence des ensembles sont incluses dans le langage de manipulation de données par les verbes UNION, INTERSECT et MINUS.

a)L’union

L’union permet la fusion de données provenant de plusieurs requêtes.

Requête SELECT
UNION [ALL]
Requête SELECT

La condition essentielle de formulation de requêtes est la compatibilité des listes de sélection dans les clauses SELECT du point de vue du type de données et de l’ordre des colonnes. L’opérateur UNION supprime toutes les données redondantes par défaut, sauf si l’option ALL est explicitée.

Exemple : Lister l’ensemble des clients et des fournisseurs dont le code postal est égal à 59000.

SELECT C.NUM_CLIENT NUMERO,
       C.NOM_CLIENT NOM,
       C.CP_CLIENT  CP
FROM CLIENT C
WHERE C.CP_CLIENT = '59000'
UNION ALL
SELECT F.NUM_FOURNISSEUR NUMERO,
       F.NOM_FOURNISSEUR NOM,
       F.CP_FOURNISSEUR  CP
FROM FOURNISSEUR F
WHERE F.CP_FOURNISSEUR = '59000'

    NUMERO NOM                                      CP
---------- ---------------------------------------- -----
         2 MIRZA                                    59000
         3 BARNABE                                  59000

b)L’intersection

L’intersection permet d’obtenir comme résultat les lignes appartenant à la fois à deux requêtes.

Requête SELECT
INTERSECT
Requête SELECT

c)La différence

La différence permet d’obtenir comme résultat les lignes de la première requête qui ne sont pas comprises dans la seconde requête.

Requête SELECT
MINUS
Requête SELECT

IV.4.13 L’insertion des données

L’insertion de nouvelles données dans la base de données se fait selon deux types de requête : La première permet d’insérer des données en provenance du monde extérieur et la seconde permet une insertion insertables. L’insertion des données nécessite un COMMIT ou un ROLLBACK en fin de transaction.

a)Insertion de données externes

INSERT INTO [schéma.](table | vue)[@dblink]
[(liste_de_colonnes)]
VALUES (liste_de_valeur)

Exemple : Insertion d’un nouveau client.

INSERT INTO CLIENT VALUES (NUM_CLIENT.NEXTVAL,
   'BARNABE',
   null,
   'Rue du sac',
   null,
   '59000',
   'LILLE',
   sysdate);

b)Insertion de données internes

INSERT INTO nom_de_table [liste_de_sélection)]
Requête_SELECT

Exemple : Alimentation en données d’une table temporaire TMP_CLIENT par les clients ayant un code postal égal à 59000.

INSET INTO TMP_CLIENT
SELECT NUM_CLIENT,
       NOM_CLIENT,
       TYPE_CLIENT,
       ADRESSE_1_CLIENT,
       ADRESSE_2_CLIENT,
       CP_CLIENT,
       VILLE_CLIENT,
       DATE_CREATION
FROM CLIENT
WHERE CP_CLIENT=’59000’;

IV.4.14 La modification des données

SQL permet de modifier les données existantes par la commande UPDATE. La modification des données nécessite un COMMIT ou un ROLLBACK en fin de transaction. Il existe trois types de syntaxe :

a)Première syntaxe

UPDATE [schéma.](table | vue)[@dblink] [alias]
SET nom_de_colonne = exp,…
[WHERE condition]

Exemple 1 : Augmentation générale des prix unitaires des lignes de commande de 0,5%

UPDATE LIGNE_COMMANDE SET PU_HT=PU_HT*1.005;

Exemple 2 : Ajout des mots ‘Fin série’ au libellé des produits et mise à jour de la quantité limite à 0 pour tous les produits ayant une quantité en stock inférieure à 5

UPDATE PRODUIT 
SET LIBELLE_PRODUIT = LIBELLE_PRODUIT||’ Fin série’,
QTE_LIMITE = 0
WHERE QTE_STOCK < 5;

b)Seconde syntaxe

UPDATE [schéma.](table | vue)[@dblink] [alias]
SET (liste_de_colonnes) = (requête SELECT)
[WHERE condition]

Exemple : Modification de l’adresse (1 et 2), de la ville et du code postal du client numéro 3 par ceux du fournisseur numéro 2

UPDATE CLIENT
SET (ADRESSE_1_CLIENT, ADRESSE_2_CLIENT, VILLE_CLIENT, CP_CLIENT) = 
      (SELECT ADRESSE_1_FOURNISSEUR,
              ADRESSE_2_FOURNISSEUR,
              VILLE_FOURNISSEUR,
              CP_FOURNISSEUR 
       FROM FOURNISSEUR
       WHERE NUM_FOURNISSEUR=2)
WHERE NUM_CLIENT = 3;

c)Troisième syntaxe (Combinaison de la première et de la seconde)

UPDATE [schéma.](table | vue)[@dblink] [alias]
SET (liste_de_colonnes) = (requête SELECT), nom_de_colonne = exp,…
[WHERE condition]

IV.4.15 La suppression des données

SQL permet de supprimer des enregistrements par la commande DELETE. La suppression des données nécessite un COMMIT ou un ROLLBACK en fin de transaction.

DELETE FROM [schéma.](table | vue)[@dblink] [alias]
[WHERE condition]

Exemple 1 : Suppression de l’ensemble des lignes de commande de la base de données.

DELETE FROM LIGNE_COMMANDE;

Exemple 2 : Suppression de l’ensemble des lignes de commande de la base de données concernant le produit numéro 4.

DELETE FROM LIGNE_COMMANDE
WHERE NUM_PRODUIT = 4;

IV.4.16 Le plan d’éxecution d’une requête

Oracle permet d’analyser le plan d’éxecution d’une requête.

a) La table Oracle utilisée afin de stocker le plan d’éxecution d’une requête

create table PLAN_TABLE (statement_id 	varchar2(30),
 timestamp    	date,
	 remarks      	varchar2(80),
	 operation    	varchar2(30),
	 options       	varchar2(30),
	 object_node  	varchar2(128),
	 object_owner 	varchar2(30),
	 object_name  	varchar2(30),
	 object_instance  numeric,
	 object_type      varchar2(30),
	 optimizer        varchar2(255),
	 search_columns   numeric,
	 id		      numeric,
	 parent_id	      numeric,
	 position	      numeric,
	 cost		      numeric,
	 cardinality	numeric,
	 bytes		numeric,
	 other_tag        varchar2(255),
	 other		long);

b) Le script SQL utilisé afin d’obtenir le plan d’éxecution d’une requête

-- RAZ de la table PLAN_TABLE
set verify off
DELETE FROM PLAN_TABLE WHERE STATEMENT_ID='P_EXEC1';
COMMIT;

-- Analyse de la requête
EXPLAIN PLAN
	SET STATEMENT_ID = 'P_EXEC1' FOR
        SELECT NOM_CLIENT
          FROM CLIENT
         WHERE NOM_CLIENT LIKE &NOM||'%';

-- Lecture de la table PLAN_TABLE
COL OPERATION FORMAT A30
COL OPTIONS FORMAT A20
COL OBJECT_NAME FORMAT A20

SELECT LPAD(' ',2*LEVEL)||OPERATION OPERATION ,OPTIONS, OBJECT_NAME
	FROM PLAN_TABLE WHERE STATEMENT_ID='P_EXEC1'
	CONNECT BY PRIOR ID = PARENT_ID
           AND STATEMENT_ID = 'P_EXEC1'
 	START WITH ID = 1
           AND STATEMENT_ID = 'P_EXEC1'
	ORDER BY ID;

c) Quelques exemples d’analyse du plan d’exécution d’une requête

Accès par rapport à la clé primaire

EXPLAIN PLAN
   SET STATEMENT_ID = 'P_EXEC1' FOR
          SELECT NOM_CLIENT
            FROM CLIENT
           WHERE NUM_CLIENT=1

OPERATION                      OPTIONS              OBJECT_NAME
------------------------------ -------------------- --------------------
  TABLE ACCESS                 BY INDEX ROWID       CLIENT
    INDEX                      UNIQUE SCAN          PK_CLIENT

Accès par rapport à un champ non indéxé

EXPLAIN PLAN
   SET STATEMENT_ID = 'P_EXEC1' FOR
          SELECT NOM_CLIENT
            FROM CLIENT
           WHERE NOM_CLIENT LIKE 'BAR%'

OPERATION                      OPTIONS              OBJECT_NAME
------------------------------ -------------------- --------------------
  TABLE ACCESS                 FULL                 CLIENT

Accès par rapport au ROWID de l’enregistrement

EXPLAIN PLAN
   SET STATEMENT_ID = 'P_EXEC1' FOR
          SELECT NOM_CLIENT
            FROM CLIENT
           WHERE ROWID='AAABM8AABAAADnCAAA'

OPERATION                      OPTIONS              OBJECT_NAME
------------------------------ -------------------- --------------------
  TABLE ACCESS                 BY USER ROWID        CLIENT

d) La comparaison du plan d’éxecution d’une requête écrite de deux manières différentes (Requête / Sous-Requête)

EXPLAIN PLAN
SET STATEMENT_ID = 'P_EXEC1' FOR
 SELECT P.NUM_PRODUIT, P.LIBELLE_PRODUIT
 FROM PRODUIT P, ENTETE_COMMANDE E, LIGNE_COMMANDE L
 WHERE P.NUM_PRODUIT = L.NUM_PRODUIT
 AND E.NUM_COMMANDE = L.NUM_COMMANDE
 AND E.DATE_COMMANDE BETWEEN TO_DATE('01/01/1999','DD/MM/YYYY') AND SYSDATE

OPERATION                      OPTIONS              OBJECT_NAME
------------------------------ -------------------- --------------------
  NESTED LOOPS
    NESTED LOOPS
      TABLE ACCESS             FULL                 LIGNE_COMMANDE
      TABLE ACCESS             BY INDEX ROWID       ENTETE_COMMANDE
        INDEX                  UNIQUE SCAN          PK_ENTETE_COMMANDE
    TABLE ACCESS               BY INDEX ROWID       PRODUIT
      INDEX                    UNIQUE SCAN          PK_PRODUIT




EXPLAIN PLAN
 SET STATEMENT_ID = 'P_EXEC1' FOR
  SELECT P.NUM_PRODUIT, P.LIBELLE_PRODUIT
  FROM
  PRODUIT P,
  (SELECT NUM_COMMANDE FROM ENTETE_COMMANDE
   WHERE DATE_COMMANDE BETWEEN
   TO_DATE('01/01/1999','DD/MM/YYYY') AND SYSDATE
  ) E, 
 LIGNE_COMMANDE L
 WHERE P.NUM_PRODUIT = L.NUM_PRODUIT
 AND E.NUM_COMMANDE = L.NUM_COMMANDE

OPERATION                      OPTIONS              OBJECT_NAME
------------------------------ -------------------- --------------------
  NESTED LOOPS
    NESTED LOOPS
      TABLE ACCESS             FULL                 LIGNE_COMMANDE
      TABLE ACCESS             BY INDEX ROWID       ENTETE_COMMANDE
        INDEX                  UNIQUE SCAN          PK_ENTETE_COMMANDE
    TABLE ACCESS               BY INDEX ROWID       PRODUIT
      INDEX                    UNIQUE SCAN          PK_PRODUIT

e) La comparaison du plan d’éxecution d’une requête écrite de deux manières différentes (NOT IN / MINUS)

EXPLAIN PLAN
SET STATEMENT_ID = 'P_EXEC1' FOR
 SELECT P.NUM_PRODUIT, P.LIBELLE_PRODUIT
 FROM PRODUIT P 
 WHERE P.NUM_PRODUIT not in 
 (SELECT DISTINCT L.NUM_PRODUIT
  FROM ENTETE_COMMANDE E, LIGNE_COMMANDE L
  WHERE E.NUM_COMMANDE = L.NUM_COMMANDE
  AND E.DATE_COMMANDE BETWEEN
  TO_DATE('01/01/1999','DD/MM/YYYY') AND SYSDATE
 )


OPERATION                      OPTIONS              OBJECT_NAME
------------------------------ -------------------- --------------------
  FILTER
    TABLE ACCESS               FULL                 PRODUIT
    NESTED LOOPS
      TABLE ACCESS             FULL                 LIGNE_COMMANDE
      TABLE ACCESS             BY INDEX ROWID       ENTETE_COMMANDE
        INDEX                  UNIQUE SCAN          PK_ENTETE_COMMANDE


EXPLAIN PLAN
SET STATEMENT_ID = 'P_EXEC1' FOR
 SELECT P.NUM_PRODUIT, P.LIBELLE_PRODUIT
 FROM PRODUIT P 
 MINUS
 SELECT DISTINCT P.NUM_PRODUIT, P.LIBELLE_PRODUIT
 FROM PRODUIT P,ENTETE_COMMANDE E, LIGNE_COMMANDE L
 WHERE E.NUM_COMMANDE = L.NUM_COMMANDE
 AND P.NUM_PRODUIT = L.NUM_PRODUIT
 AND E.DATE_COMMANDE BETWEEN
 TO_DATE('01/01/1999','DD/MM/YYYY') AND SYSDATE



OPERATION                      OPTIONS              OBJECT_NAME
------------------------------ -------------------- --------------------
  MINUS
    SORT                       UNIQUE
      TABLE ACCESS             FULL                 PRODUIT
    SORT                       UNIQUE
      NESTED LOOPS
        NESTED LOOPS
          TABLE ACCESS         FULL                 LIGNE_COMMANDE
          TABLE ACCESS         BY INDEX ROWID       ENTETE_COMMANDE
            INDEX              UNIQUE SCAN          PK_ENTETE_COMMANDE
        TABLE ACCESS           BY INDEX ROWID       PRODUIT
          INDEX                UNIQUE SCAN          PK_PRODUIT

f) Un exemple d’optimisation du plan d’éxecution d’une requête

EXPLAIN PLAN
SET STATEMENT_ID = 'P_EXEC1' FOR
 SELECT P.NUM_PRODUIT, P.LIBELLE_PRODUIT
 FROM PRODUIT P, ENTETE_COMMANDE E, LIGNE_COMMANDE L
 WHERE P.NUM_PRODUIT = L.NUM_PRODUIT
 AND E.NUM_COMMANDE = L.NUM_COMMANDE
 AND E.DATE_COMMANDE BETWEEN TO_DATE('01/01/1999','DD/MM/YYYY') AND SYSDATE

OPERATION                      OPTIONS              OBJECT_NAME
------------------------------ -------------------- --------------------
NESTED LOOPS
NESTED LOOPS
TABLE ACCESS             FULL                         LIGNE_COMMANDE
TABLE ACCESS             BY INDEX ROWID       ENTETE_COMMANDE
INDEX                          UNIQUE SCAN           PK_ENTETE_COMMANDE
TABLE ACCESS             BY INDEX ROWID       PRODUIT
INDEX                          UNIQUE SCAN           PK_PRODUIT

Création d’un index sur la champ DATE_COMMANDE de la table ENTETE_COMMANDE

create index ENTETE_COMMANDE_IDX1
 ON ENTETE_COMMANDE (DATE_COMMANDE) TABLESPACE TP_IDX;

Création d’un index sur le champ NUM_COMMANDE de la table LIGNE_COMMANDE (Champ de type clé étrangère de la table ENTETE_COMMANDE)

create index LIGNE_COMMANDE_IDX1
 ON LIGNE_COMMANDE (NUM_COMMANDE) TABLESPACE TP_IDX;



EXPLAIN PLAN
SET STATEMENT_ID = 'P_EXEC1' FOR
 SELECT P.NUM_PRODUIT, P.LIBELLE_PRODUIT
 FROM ENTETE_COMMANDE E, PRODUIT P, LIGNE_COMMANDE L
 WHERE P.NUM_PRODUIT = L.NUM_PRODUIT
 AND E.NUM_COMMANDE = L.NUM_COMMANDE
 AND E.DATE_COMMANDE BETWEEN TO_DATE('01/01/1999','DD/MM/YYYY') AND SYSDATE

OPERATION                      OPTIONS              OBJECT_NAME
------------------------------ -------------------- --------------------
NESTED LOOPS
NESTED LOOPS
TABLE ACCESS               BY INDEX ROWID      ENTETE_COMMANDE
INDEX                            RANGE SCAN           ENTETE_COMMANDE_IDX1
TABLE ACCESS               BY INDEX ROWID      LIGNE_COMMANDE
INDEX                            RANGE SCAN           LIGNE_COMMANDE_IDX1
TABLE ACCESS               BY INDEX ROWID      PRODUIT
INDEX                            UNIQUE SCAN          PK_PRODUIT

IV.4.17 Les nouvelles caractéristiques d’Oracle 8 Server

Le 24 juin 1997, Oracle Corporation a officiellement annoncé la naissance de la dernière version du SGBDR d’Oracle : Oracle8. Cette version est un des composants principaux de l’architecture NCA (Network Computing Architecture) d’Oracle. Oracle Corporation met l’accent sur plusieurs nouvelles caractéristiques d’Oracle8 Server, telles que :

Amélioration de l’évolutivité. La taille maximale d’une base de données Oracle a été augmentée pour prendre en charge des centaines de téraoctets, en fonction du système d’exploitation qui l’héberge.

Amélioration de l’administration de la sécurité. Par exemple, Oracle8 Server inclut à présent la gestion de mots de passe afin de limiter leur durée de vie, et appliquer certains critères plus complexes dans leur composition, comme une longueur minimale. Un compte peut par ailleurs être verrouillé après un nombre donné de tentatives infructueuses.

Amélioration des performances au moyen du partitionnement. Une table ou un index peuvent être divisés en portions plus petites, appelées partitions, définies selon les valeurs d’une ou plusieurs colonnes. Une partition de table peut être gérée individuellement afin que les opérations dans une partition n’affectent pas la disponibilité des données dans d’autres partitions.
De plus, les opérations du langage LMD, INSERT, UPDATE ou DELETE sur des tables partitionnées peuvent être traitées en parallèle. En d’autres termes, Oracle8 Server peut assigner une partie du traitement nécessaire à une instruction du LMD à plusieurs processus qui peuvent, à leur tour, être alloués à plusieurs processeurs par le système d’exploitation du serveur. On aboutit ainsi à une exécution plus rapide des opérations parallèles du LMD.

Amélioration des fonctionnalités de réplication de base de données. Les performances et la capacité de gestion de la réplication ont été améliorées de façon significative.

Capacité à gérer un nombre plus important d’utilisateurs concurrents. En groupant les connexions, le serveur Oracle8 peut traiter un plus grand nombre d’utilisateurs concurrents, jusqu’à 3000, selon le système d’exploitation et les ressources matérielles dont dispose le serveur.

Nouveaux types et amélioration des types existants. Certains types déjà existants ont été améliorés et de nouveaux types ont été introduits.

Amélioration de l’instruction SELECT. Une nouvelle fonctionnalité de l’instruction SELECT permet à une sous-requête d’être utilisée à la place d’une table ou d’une vue dans une clause FROM.

Fonctionnalites objet-relationnelles via l’option d’objets Oracle. Une nouvelle option du SGBDR intègre la définition et l’utilisation de nouveaux types de données.

Oracle Corporation a déclaré que l’outil de migration d’Oracle8 peut migrer une base de donnée Oracle7 (version 7.1, 7.2 ou 7.3) vers Oracle8. De plus, une application qui fonctionne actuellement avec une base de données Oracle7 pourra opérer correctement avec une base Oracle8.