ORACLE LE LANGAGE SQL ORACLE LE LANGAGE SQL

SOMMAIRE LE LANGAGE SQL

LE LANGAGE SQL .................................................................................................................. 2
1. Langage de Description des Données (L.D.D.).................................................................. 2
1.1 Types de données ....................................................................................................... 2 SQL (Structured Query language) est un langage de création et d’intérrogation
1.1.1. Caractères ........................................................................................................... 2 de bases de données. Il est utilisé par l’ensemble des Systèmes de Gestion de
1.1.2. Nombres ............................................................................................................. 3 Bases de Données Relationnelles tels que ORACLE, SQL Server,
1.1.3. Date .................................................................................................................... 3 SQLAnyWhere, INFORMIX, INGRES, MySQL, PostGres, Acces.
1.1.4. Binaires............................................................................................................... 3
1.1.5. Les gros objets.................................................................................................... 4
1.1.6. Gestion des gros objets....................................................................................... 4
Le langage SQL est composé de deux grandes parties :
• Initialisation des colonnes de type LOB ................................................................ 4
1.2 Schéma de la base de données.................................................................................... 5 • Le langage de description de données (L.D.D.) permet de décrire
1.3 Création de Table et de Vue ....................................................................................... 6 les fonctions d’administration d’une base de données, comme la
1.4 Modification du schéma d’une table .......................................................................... 8 création d’une table, d’une vue, la gestion des utilisateurs, la
1.5 Spécification des contraintes ...................................................................................... 9 spécification des contraintes d’intégrité et les outils d’optimisation.
1.6 Les performances ..................................................................................................... 14
1.6.1. Index................................................................................................................. 14 • Le langage de manipulation de données qui définit l’ensemble des
1.6.2. Clusters, Tables et Index .................................................................................. 14 opérations de manipulation de tables comme la sélection, la
[Link]. Création de tables en cluster..................................................................... 15 modification ou la suppression en fonction de critère donné.
[Link]. Modification et suppression de clusters ................................................... 16
1.6.3. Les séquences ................................................................................................... 17 1. Langage de Description des Données (L.D.D.)
2. Langage de Manipulation de Données (L.M.D.).............................................................. 18
2.1 Insertion de tuples .................................................................................................... 18
2.2 Suppression de tuples ............................................................................................... 19 Le langage manipule des données sous forme de tableaux appelés tables. Chaque
2.3 Modification de tuples.............................................................................................. 19 table contient une ou plusieurs colonnes et zéro ou plusieurs lignes appelées
2.4 Sélection de tuples.................................................................................................... 20 tuples. Les valeurs d’une colonne sont définies selon un type de données choisi
2.5 Notions de privilèges................................................................................................ 22 lors de la création de la table. Les types de données manipulés sont :
2.5.1. Privilèges objet ................................................................................................. 22 Char(longueur), Varchar(longueur), Numeric(Précision,échelle), Integer, Date,
2.5.2. Privilèges système ............................................................................................ 23
etc.
2.5.3. Attribution d’un privilège................................................................................. 24
2.5.4. Suppression d’un privilège............................................................................... 24
2.6 Notion de rôles ......................................................................................................... 25
2.6.1. Création et activation ....................................................................................... 25 1.1 Types de données
2.6.2. Modification et suppression ............................................................................. 26
2.7 Dictionnaire de données ........................................................................................... 26
2.8 Les transactions ........................................................................................................ 27 1.1.1. Caractères
3. Spécificités pour ORACLE .............................................................................................. 27
3.1 Expressions et fonctions........................................................................................... 28
3.1.1. Chaînes de caractères ....................................................................................... 28 • Le type CHAR (longueur) permet de stocker une chaîne de caractères de
3.1.2. Nombres ........................................................................................................... 28 longueur fixe. longueur indique le nombre de caractères qu’il est possible
3.1.3. Date .................................................................................................................. 29 de stocker dans le champ ; la valeur maximale est de 2 000 caractères, par
3.1.4. Fonctions de conversion et d’agrégat............................................................... 29 défaut la longueur est 1. L’insertion d’une chaîne plus longue que la
3.2 Traitement des structures arborescentes................................................................... 30 longueur spécifiée est refusée.
4. EXERCICES .................................................................................................................... 30
4.1 TP1 ........................................................................................................................... 30 • Le type VARCHAR2 (longueur) permet de stocker une chaîne de
4.2 TP2 ........................................................................................................................... 31 caractères de longueur variable. La longueur maximale est de 4 000
caractères. Une chaîne plus courte que la longueur spécifiée n’occupera
que l’emplacement correspondant à sa taille réelle. Le type VARCHAR
est un synonyme de VARCHAR2.

Professeur : ASSALE Adjé Louis 1/33 INP-HB Professeur : ASSALE Adjé Louis 2/33 INP-HB
ORACLE LE LANGAGE SQL ORACLE LE LANGAGE SQL

• Le type LONG permet de stocker des chaînes de caractères d’une 1.1.5. Les gros objets
longueur maximale de 2 GO. Son utilisation est soumise à certaines
restrictions : Les gros objets (LOB, Large Objects) sont utilisés pour gérer des données de
- pas de spécification de longueur maximale gros volume non structurées telles que les images, les sons, la vidéo et le texte.
- une colonne de ce type ne peut figurer ni dans une expression, ni dans un On a deux catégories de LOB :
prédicat • LOB interne
- une table ne peut comporter qu’une seule colonne de ce type - le type CLOB permet de stocker des objets de type chaîne de
caractères tels que des documents. La taille maximale d’une
Les constantes chaînes de caractères sont entre quotte : exemple ′ASSALE′ colonne est de 4 Go.
- le type BLOB permet de stocker des objets en représentation
binaire : graphiques, vidéo ou son. La taille maximale d’une
1.1.2. Nombres colonne est de 4 Go.
• LOB externe
Les données numériques comprennent les nombres entiers, les nombres le type BFILE permet de stocker une liste de pointeurs vers des
décimaux et les nombres en virgule flottante. fichiers de données binaires extérieurs à la base de données. La
Le type NUMBER [(précision [,échelle])] permet de les représenter. taille maximale du fichier externe est de 4 Go.
• précision : nombre entier de chiffres significatifs, de 1 à 38 (38 par
défaut) ; Il est possible de définir plusieurs colonnes de type LOB pour une table.
• échelle : nombre de chiffres à droite de la marque décimale ; prend une Lors de la création d’une table, il est possible d’attribuer aux colonnes de type
valeur comprise entre –84 et +127. LOB des paramètres de stockage différents de ceux de la table.
Les ordres SQL ne sont pas capables de gérer directement des colonnes de type
LOB, il faut utiliser le package DBMS_LOB.
1.1.3. Date

Le type DATE permet de stocker des informations comprenant une date et/ou 1.1.6. Gestion des gros objets
une heure. La représentation externe est définie par le paramètre
NLS_DATE_FORMAT du fichier de configuration du SGBDR. Exemple : le • Initialisation des colonnes de type LOB
format DD-MON-YY donne ′03-FEV-99′.
Avant toute affectation de valeur à une colonne de type LOB, il faut l’initialiser
à vide. On utilise les fonctions EMPTY_CLOB() et EMPTY_BLOB,
1.1.4. Binaires respectivement pour les types CLOB et BLOB. Par contre le type BFILE peut
être initialisé à NULL.
• Le type RAW (n) permet de stocker des données de type binaire de Soit la table Personne (N_pers number, Nom varchar2(20), Description Clob,
longueur fixe de valeur maximale de 2 000 octets Photo Blob, Emprunte Bfile)
sous SQL*PLUS, les données sont affichées sous forme hexadécimale.
Une insertion ou une modification d’une donnée se fait donc an spécifiant Insert into Personne (145, ‘ASSALE Yvann’, empty_clob(), empty_blob(), Null) ;
sa valeur hexadécimale sous forme de chaîne de caractères.
Exemple : insert into Test(Coln_Raw) values (‘1B5A’) ; insère dans la • Modification des colonnes de type LOB
table Test la séquence ESC Z dont la valeur hexadécimale est 1B5A
• Le type LONG RAW est identique au type LONG pour les données La commande UPDATE modifie les valeurs des colonnes de type LOB. Pour un
binaires. colonne de type BFILE, on utilise la fonction BFILENAME(‘nom répertoire’,
‘nom fichier’) pour préciser le chemin d’accès aux fichiers.

Professeur : ASSALE Adjé Louis 3/33 INP-HB Professeur : ASSALE Adjé Louis 4/33 INP-HB
ORACLE LE LANGAGE SQL ORACLE LE LANGAGE SQL

Par exemple : update personne set emprunte = Bfilename(‘c:\personnels’,

‘[Link]’) ; LIVRE
ETUDIANT EMPRUNT #Code_Liv
#Matricule Titre
• Package DBMS_LOB Nom Sortie Auteur
Prénoms Retour Genre
Les fonctions du package DBMS_LOB permettent de manipuler les données de Sexe Prix
type LOB dans un bloc PL/SQL.

Fonction Action
READ Permet de consulter une partie d’une colonne
SUBSTR Exécuter la fonction SUBSTR sur la colonne CLASSE
#Code_cl
INSTR Exécute la fonction INSTR sur la colonne Intitulé
GETLENGTH Donne la longueur (nombre d’octets) utilisée par la donnée Effectif
COMPARE Compare le contenu de deux colonnes
WRITE Ecrit une donnée dans une colonne à partir d’un octet donné
APPEND Ajoute des données à une colonne
ERASE Supprime tout ou partie des données d’une colonne Le schéma logique est le suivant :
TRIM Exécute la fonction RTRIM sur la colonne
COPY Copie tout ou partie d’une colonne dans une autre Etudiant (Matricule, Nom, Prénoms, Sexe, Code_cl)
Livre (Code_Liv, Titre, Auteur, Genre, Prix)
Classe (Code_cl, Intitulé, Effectif)
1.2 Schéma de la base de données Emprunt (Matricule, Code_Liv, Sortie, Retour)

Soit le Modèle Conceptuel de Données suivant : Les attributs soulignés font partir de la clé primaire, les attributs en italique sont
des clés étrangères.

Etudiant Livre
Matricule 0,N Emprunt 0,N Code_Liv 1.3 Création de Table et de Vue
Nom Sortie Titre
Prénoms Retour Auteur
Sexe Genre
Syntaxes :
Prix Create Table <nom de table>
1,1 (<attribut1><type attrribut1><Contraite1>,
∈ <attribut2><type attrribut2><Contraite2>,
1,N …
Classe
<attributN><type attrributN><ContraiteN>) ;
Code_cl
Intitulé Create View <nom de vue>
Effectif AS <requête de sélection>
<option de test> ;

Description :
Son équivalent selon le modèle Entity/Relationship de CASE est :
La contrainte introduit la notion d’intégrité de données (spécification de
contraintes).

Professeur : ASSALE Adjé Louis 5/33 INP-HB Professeur : ASSALE Adjé Louis 6/33 INP-HB
ORACLE LE LANGAGE SQL ORACLE LE LANGAGE SQL

Pour la requête de sélection voir Langage de manipulation chapitre • Les paramètres de gestion de l’espace sont :
sélection de tuples.
<Option de test> qui s’exprime par « with check option » est facultative. Paramètre Définition
Quand elle est spécifiée, elle impose les restrictions suivantes : PCTFREE Spécifie le pourcentage d’espace dans chaque bloc
• toute modification d’un tuple d’une vue est refusée si elle Oracle réservé pour les futures modifications de
entraîne une exclusion de la vue du tuple concerné. lignes. La valeur par défaut est 10 pour cent.
• Une insertion est refusée si le tuple introduit dans la vue ne PCTUSED Spécifie le pourcentage minimum d’espace utilisé que
satisfait pas au critère de sélection de celle-ci. Oracle maintient dans chaque de données. Un bloc
redevient candidat pour l’insertion de lignes quand son
Exemples : espace utilisé tombe en dessous de PCTUSED. La
Create table Etudiant (Matricule char(6) primary key, Nom char(20) Not valeur par défaut est 40 pour cent.
Null, Prénoms char(40)) ; INITRANS Indique le nombre d’entrées de transactions
initialement allouées dans l’entête du bloc.
Create view Roman MAXTRANS Indique le nombre maximum de transactions pouvant
AS Select Titre, Auteur, Genre From Livre Where Genre = ′roman′ ; accéder simultanément au bloc.
• Les paramètres de stockage sont :
Syntaxe de destruction de Table et Vue :
Drop Table <nom de table> ; Paramètre Description
Drop View <nom de vue> ; INITIAL Taille en octets du premier extent alloué à un
segment ; le défaut équivaut à cinq blocs de
• Création de table avec option de stockage données.
Oracle permet d’ajouter les options suivantes pour la gestion de l’espace de NEXT Taille en octets de l’extent suivant alloué à un
stockage : segment; le défaut équivaut à cinq blocs de
CREATE TABLE nom_table (…) données.
[PCTFREE pourcentage] [PCTUSED pourcentage] MAXETENTS Spécifie le nombre total d’extents. La valeur
[INITRANS nombre] [MAXTRANS nombre] minimale est 1. La valeur par défaut et la valeur
[TABLESPACE nom_tablespace] maximale dépendent de la taille du bloc de
[STORAGE ([INITIAL valeur] données.
[NEXT valeur] UNLIMITED spécifie que les extents doivent
[MINEXTENTS nombre] être automatiquement alloués au fur et à mesure
[MAXEXTENTS nombre] des besoins.
[PCTINCREASE pourcentage]) MINEXTENTS Nombre total d’extents à allouer lorsque le
] segment est créé ; la valeur par défaut est 1..
[CLUSTER nom_cluster] PCTINCREASE Pourcentage d’accroissement des extents suivant
[ENABLE | DISABLE contrainte] le dernier extent incrémental alloué ; la valeur par
[AS requête] ; défaut est 50%. NEXT = NEXT * (1 + nouveau
• Modification des paramètres d’allocation d’une table PCTINCREASE/100) dans le cas d’une
ALTER TABLE nom_table modification de PCTINCREASE.
[PCTFREE pourcentage] [PCTUSED pourcentage]
[INITRANS nombre] [MAXTRANS nombre]
[STORAGE paramètres_stockage] 1.4 Modification du schéma d’une table
[DROP contrainte]
Syntaxe :

Professeur : ASSALE Adjé Louis 7/33 INP-HB Professeur : ASSALE Adjé Louis 8/33 INP-HB
ORACLE LE LANGAGE SQL ORACLE LE LANGAGE SQL

Alter Table <nom de table> Add (<col1><type1><contrainte1>,…) ; - Etre une fonction type USER, SYSDATE, etc
Alter Table <nom de table> Modify (<col1><type1><contrainte1>,…) ; - Etre une séquence
Alter Table <nom de table> Drop (<col1>,…) ; (pour certains SGBD)
La valeur par défaut ne peut pas :
Description : - Utiliser les fonctions PL/SQL
Add, Modify et Drop permettent respectivement d’ajouter, de modifier et - Inclure une référence à d’autres colonnes.
de supprimer des colonnes d’une table.
Syntaxe et exemple
Exemple : Create Table Livre ( Code_Liv Char(6) Primary Key,…,
Alter Table Etudiant Add Sexe char(1) ; Prix number(9,0) Default 1000) ;

La contrainte NOT NULL

1.5 Spécification des contraintes
Oblige de donner une valeur à la colonne
Les contraintes qui régissent l’intégrité des données, se spécifient à travers la Syntaxe et exemple
définition de colonnes. On distingue 4 types d’intégrité : Create table Dept (Deptno number(2) Not Null, …);
• De domaine : contrainte DEFAULT
• D’entité : contraintes NOT NULL, UNIQUE, PRIMARY KEY La contrainte UNIQUE
• De référence : contrainte FOREIGN KEY
• D’application : contrainte CHECK • Évite d’avoir deux lignes avec la même valeur dans la même colonne.
• L’association des contraintes NOT NULL et UNIQUE est possible sur
Le nom d’une contrainte une colonne ou sur un ensemble de colonnes (clé composée)
• Un index est automatiquement crée avec comme nom celui de la
• Est crée lors de la création de la contrainte contrainte.
• Doit être unique pour le propriétaire • Peut être référencée par une clé étrangère (FOREIGN KEY).
• En cas de non-spécification lors de la création, un nom de la forme • Ne peut pas être créée sur une vue
SYS_Cnnn est généré automatiquement par Oracle
• Le nom est utilisé pour : Syntaxe et exemple
- Documenter le message d’erreur lors de la transgression de la Create Table emp (Nom_employe varchar(10) UNIQUE,…) ;
contrainte,
- Documenter le dictionnaire de données, La contrainte CHECK
- Faciliter l’activation, l’inhibition ou la suppression de la contrainte
• S’applique à toutes les lignes de la table
La syntaxe générale de spécification d’une contrainte est : constraint • Peut référencer une ou plusieurs colonnes
<nomcontrainte> type_contrainte …. • Peut comparer les colonnes entre elles
La contrainte DEFAULT • Peut comparer les colonnes à des constantes
• Ne peut pas contenir de sous requête.
Ce n’est pas tout à fait une contrainte. • Ne peut pas contenir de fonctions telles que SYSDATE, USER etc.
La valeur par défaut sur la colonne sera prise si elle n’est pas spécifiée au Syntaxe et exemple
moment d’un INSERT et uniquement au moment de la validation. Create table Etudiant (…, Sexe char(1) Check(Sexe in (‘M’,’F’))) ;
La valeur par défaut peut : La contrainte PRIMARY KEY
- Etre une constante appropriée au type de données

Professeur : ASSALE Adjé Louis 9/33 INP-HB Professeur : ASSALE Adjé Louis 10/33 INP-HB
ORACLE LE LANGAGE SQL ORACLE LE LANGAGE SQL

Create Table Emprunt(…,Constraint N_liv Foreign Key (Code_Liv)

• Permet d’obtenir l’unicité des lignes de la table References Livre(Code_Liv) On Delete Cascade, Constraint Mat
• Se comporte comme une contrainte UNIQUE et NOT NULL Foreign Key (Matricule) References Etudiant(Matricule) On Delete
combinées. Cascade);
• Peut concerner une ou plusieurs colonnes. Dans le cas de plusieurs Ou
colonnes, on définit la contrainte après la déclaration de tous les Create Table Emprunt(Code_Liv varchar(5) References
champs de la table. On peut donner un nom à la contrainte à l’aide du Livre(Code_Liv) On Delete Cascade, Matricule varchar(5) References
mot clé Constraint, sinon le système attribue un nom par défaut Etudiant On Delete Cascade, …);
• Limitée à une table
• Peut être référencée par une clé étrangère
• Création automatique d’un index L’option DELETE CASCADE
• Ne peut pas être créée sur une vue.
Si une ligne « maître » est supprimée, les lignes référencées par les clés
Syntaxe et exemple
étrangères sont supprimées automatiquement.
Create Table Emprunt( Matricule char(6) Not Null, Code_Liv Char(6)
Not Null, Sortie Date Not Null, Retour Date, Constraint emp Primary
Restrictions
Key (Matricule, Code_Liv, Sortie)) ;
Un enregistrement ne peut pas être supprimé s’il est référencé par une clé
Ici emp est le nom de la contrainte primary key, on peut ne pas spécifier un nom
étrangère.
pour la contrainte à l’aide de la syntaxe create table emprunt (Matricule….,
primary key (Matricule, Code_liv, Sortie) ; le système attribuera un nom par
défaut. La colonne référencée par une clé étrangère ne peut pas être mise à jour.

Gestion des contraintes

La contrainte FOREIGN KEY
Comme les colonnes, les contraintes peuvent être ajoutées, modifiées (à travers
• Permet d’établir une relation avec clé primaire ou unique définit
une colonne) et supprimées. De plus les contraintes peuvent être activées ou
précédemment ;
désactivées.
• Peut concerner une ou plusieurs colonnes ;
• Les valeurs peuvent être : Syntaxe et exemple
- Soit une valeur NULL ALTER TABLE <nom table> ADD ( Constraint <nom contrainte>
- Soit une valeur existante dans la table référencée. <Type contrainte> (<liste de colonnes>),…)
• Pas de nombre limité en clé étrangère.
• La cohérence entre la clé primaire et les clés étrangères est garantie ALTER TABLE <nom table> MODIFY
automatiquement ( <nom colonne> Constraint <nom contrainte> <Type contrainte>,…)
• Ne peut pas être créée sur une vue
• Ne peut pas référencer une table distante ALTER TABLE <nom table> DROP Constraint <nom
Syntaxe contrainte>/<Type contrainte> [CASCADE]
- [CONSTRAINT <nom_contrainte>] [FOREIGN KEY (<liste de
colonnes>)] REFERENCES <nom_table> [(<liste de ALTER TABLE <nom table> DISABLE Constraint <nom
colonnes>)] [ON DELETE CASCADE] contrainte>/<Type contrainte> [CASCADE]

Exemple : ALTER TABLE <nom table> ENABLE Constraint <nom

contrainte>/<Type contrainte> [EXCEPTION INTO <table rejets>]]

Professeur : ASSALE Adjé Louis 11/33 INP-HB Professeur : ASSALE Adjé Louis 12/33 INP-HB
ORACLE LE LANGAGE SQL ORACLE LE LANGAGE SQL

Vue Description
Quand une contrainte est transgressée, ORACLE insère l’adresse de chaque ALL_CONSTRAINTS Définition des contraintes sur les tables
ligne rejetée dans une table ayant la structure suivante : auxquelles l’utilisateur à accès
- le ROWID de la ligne rejetée, ALL_CONS_COLUMNS Définition des contraintes sur les colonnes des
- le nom du propriétaire de la table tables auxquelles l’utilisateur a accès.
- le nom de la table USER_CONSTRAINTS Définition des contraintes sur les tables de
- le nom de contrainte qui a rejeté la ligne l’utilisateur.
Exemple (pour SGBD Oracle) USER_CONS_COLUMNS Définition des contraintes sur les colonnes des
ALTER TABLE commandes ENABLE CONSTRAINT Paye tables de l’utilisateur.
EXCEPTIONS INTO rejets DBA_CONSTRAINTS Définition des contraintes sur toutes les tables
de la base.
Conclusion DBA_CONS_COLUMNS Définition des contraintes sur les colonnes de
toutes les tables de la base.
A l’aide des contraintes :

- L’intégrité et la cohérence de données sont garanties 1.6 Les performances

• Elles sont incluses dans les définitions des tables
• Elles sont appliquées avec les outils de développement (outils, CASE*,
SQL*forms, etc…) 1.6.1. Index
• Elles sont incontournables.
• Définitions stockées dans le dictionnaire de données Syntaxe :
Create <clause d’unicité> Index <nom index>
- La productivité de développement d’application est améliorée On <nom de table ou de vue>
• Pas de programmation spéciale (<col1> <ordre>, <col2> <ordre>,…) ;
• Spécification et maintenance faciles
• Codage réduit Description
• Une seule définition pour l’utilisation par plusieurs applications Les index sont généralement placés sur les colonnes fréquemment
utilisées par des requêtes de sélection. Leur création fournit au système un
• les cas de transgression sont signalés à l’utilisateur (le nom de la
moyen d’accès rapide aux informations de la base.
contrainte apparaît dans le message d’erreur)
La <clause d’unicité> UNIQUE est optionnelle, sa présence indique que
chaque valeur de l’index est unique.
- Les performances sont améliorées
Le <nom index> associe un nom à l’index créé. L’index peut être
• Réduction du trafic réseau
supprimé par la commande : Drop Index <nom index>
• Optimisation du contrôle d’intégrité (pas de contrôle exécuté si aucune <ordre> indique un tri décroissant (« DESC ») ou par défaut croissant
colonne n’est modifiée) (« ASC »).
- La gestion est simplifiée Exemple :
• Compte rendu de chaque transgression dans une table des rejets Create Index Ind_Etud
• Les contraintes peuvent être inhibées afin de réduire certains temps de On Etudiant (Nom, prénoms);
traitements.

- dans Oracle, les vues suivantes permettent de visualiser les contraintes : 1.6.2. Clusters, Tables et Index

Professeur : ASSALE Adjé Louis 13/33 INP-HB Professeur : ASSALE Adjé Louis 14/33 INP-HB
ORACLE LE LANGAGE SQL ORACLE LE LANGAGE SQL

Les clusters correspondent à un mode de rangement (facultatif) des lignes des l’expression comme clé de hash.
tables d’une base de données. On appelle cluster un ensemble de tables HASHKEYS n Création d’un hash cluster ; n est le nombre de
regroupées parce qu’elles partagent une ou plusieurs colonnes (clé primaire et clé de hash.
référence) et qu’elles sont souvent utilisées ensemble dans les opérations de
jointure. • La création des tables qui composent le cluster ; ici, on spécifie le
cluster utilisé et les colonnes qui entrent dans la composition de la
Les lignes de plusieurs tables relatives à une même clé de cluster sont stockées clé du cluster :
dans le même bloc. Ce mode de rangement favorise les requêtes qui font CREATE TABLE …
intervenir ces tables simultanément. …
CLUSTER nom_cluster (colonne [, colonne] …)
La clé de cluster est constituée d’une ou plusieurs colonnes que les tables en où colonne est le nom de colonne de la table participant à la
cluster ont en commun. définition de la clé de cluster.
On distingue deux types de clusters : • Et éventuellement la création d’un index de cluster et/ou de table :
• Indexé : dans ce cas, un index de cluster doit être crée sur la clé de CREATE [UNIQUE | BITMAP] INDEX nom_index
cluster avant toute opération sur les tables du cluster. ON {nom_table (col [ASC | DESC] [, col [ASC | DESC]] …) |
• A hashage : dans ce cas, les lignes des tables sont stockées et CLUSTER nom_cluster
accessibles par l’intermédiaire de la fonction de hash. [INITRANS valeur] [MAXTRANS valeur]
[TABLESPACE nom_tablespace]
[Link]. Création de tables en cluster [STORAGE param_stockage]
[PCTFREE valeur]
La création de tables en cluster nécessite deux étapes : [NOSORT]
• La création du cluster :
CREATE CLUSTER nom_cluster UNIQUE : interdit que deux lignes aient la même valeur (n’est plus
(colonne [, colonne] …) utilisé avec Oracle8).
[PCTUSED valeur] [PCTFREE valeur] BITMAP : indique que l’index doit être créé en structure bitmap.
[SIZE valeur]
[INITRANS valeur] [MAXTRANS valeur] [Link]. Modification et suppression de clusters
[TABLESPACE nom_tablespace]
[STORAGE param_stockage] • La modification s’effectue par :
[INDEX] ALTER CLUSTER nom_cluster
[ [HASH IS expression] [HASHKEYS n] ] ; [PCTUSED valeur] [PCTFREE valeur]
[SIZE valeur]
les paramètres sont : [INITRANS valeur] [MAXTRANS valeur]
[STORAGE param_stockage]
Paramètre Effet
colonne Nom et type de colonne composant la clé de • Et la suppression par :
cluster. DROP CLUSTER nom_cluster
SIZE Espace moyen nécessaire pour enregistrer toutes [INCLUDING TABLES [CASCADE CONSTRAINTS]] ;
les lignes relatives à une même valeur de clé de
cluster. INCLUDING TABLES permet de supprimer les tables associées, sinon il
INDEX Création d’un cluster avec un index crée sur la faudrait les supprimer avant de supprimer le cluster.
clé de cluster (option par défaut). CASCADE CONSTRAINTS permet de supprimer toutes les contraintes
HASH IS Désactive la fonction de hash interne et définit d’intégrité qui font référence à la clé primaire des tables en cluster.

Professeur : ASSALE Adjé Louis 15/33 INP-HB Professeur : ASSALE Adjé Louis 16/33 INP-HB
ORACLE LE LANGAGE SQL ORACLE LE LANGAGE SQL

ALTER SEQUENCE [Schéma.]nom_séquence

[INCREMENT BY {1 | valeur}]
1.6.3. Les séquences [START WITH valeur]
[{MAXVALUE valeur | NOMAXVALUE}]
Permettent de générer des valeurs séquentielles, utiles pour : [{MINVALUE valeur | NOMINVALUE}]
- générer des valeurs de clés primaires [{CYCLE | NOCYCLE}]
- coordonner les valeurs de clés dans plusieurs lignes ou tables [{CACHE valeur | 20} | NOCACHE] ;

Lorsqu’un attribut nécessite un ensemble de valeurs uniques dans un DROP SEQUENCE [Schéma.]nom_séquence
environnement multi-utilisateurs, il est conseillé de faire générer ces valeurs en
associant une séquence à l’attribut. • Utilisation

• Création d’une définition de séquence Utilisé dans un ordre SELECT, INSERT ou UPDATE en tant que
pseudocolonne, par :
CREATE SEQUENCE [Schéma.]nom_séquence - nom_sé[Link] : qui génère à chaque référence la
[INCREMENT BY {1 | valeur}] valeur suivante (ou la première valeur lors de la première
[START WITH valeur] référence)
[{MAXVALUE valeur | NOMAXVALUE}] - nom_sé[Link] : donne la valeur courante du
[{MINVALUE valeur | NOMINVALUE}] numéro de séquence à condition qu’un numéro ait été généré par
[{CYCLE | NOCYCLE}] un appel à NEXTVAL au cours de la même session.
[{CACHE valeur | 20} | NOCACHE] ;
Exemple :
Paramètre Signification Create sequence num_dépot
nom_séquence Nom du numéro de séquence enregistré dans le Start with 10000
dictionnaire des données Incrment by 1
INCREMENT Pas d’incrémentation du numéro de séquence. Peut être Nomaxvalue
positif ou négatif. Nocycle ;
START WITH Valeur de départ du numéro de séquence. Elle est par
défaut égale à MINVALUE pour une séquence Insert intop Dépôt (Num) Values (num_dé[Link]) ;
ascendante et à MAXVALUE pour une séquence
descendante 2. Langage de Manipulation de Données (L.M.D.)
CYCLE Lorsque le numéro de séquence atteint la valeur
MAXVALUE (respectivement MINVALUE), il repart à
MINVALUE (respectivement MAXVALUE) 2.1 Insertion de tuples
NOMAXVALUE et Limites haute et basse par défaut (1 et 10E27 -1) compte
NOMINVALUE tenu du sens ascendant ou descendant de la génération. Syntaxe :
NOCYCLE Pas de reprise après MAXVALUE ou MINVALUE Insert Into <nom table ou vue>
CACHE Demande une prégénération de numéros de séquence de (<liste de colonnes>)
telle sorte qu’il n’y ait pas d’attente lors d’une demande Values (valeur1,…, valeurN) ;
de valeur. Par défaut, 20 valeurs soont stockées en Ou
mémoire. Insert Into <nom table ou vue>
(<liste de colonnes>)
• Modification/Suppression <requête de sélection> ;

Professeur : ASSALE Adjé Louis 17/33 INP-HB Professeur : ASSALE Adjé Louis 18/33 INP-HB
ORACLE LE LANGAGE SQL ORACLE LE LANGAGE SQL

Ou
Description : Update <nom de table ou vue>
La <liste de colonnes> peut être omise ; dans ce cas, chaque colonne du SET (col1, col2, …) = (SELECT attribut1, attribut2, …)
tuple de la table désignée sera affectée. Si la <liste de colonnes> est précisée, [Where <critère de sélection>] ;
chaque colonne choisie doit être séparée de la suivante par une virgule.
La liste valeur doit contenir autant de valeurs et de colonnes concernées L’ordre SELECT doit ramener une seule ligne.
par l’insertion.
Description :
Exemple : Chaque expression peut contenir des fonctions et combiner des
Insert Into Livre opérateurs.
Values (′100′, ′Ah les Hommes′, ′Biton′, ′Nouvelle′,1500) ; N.B. : beaucoup de SGBDR n’autorise pas de modification aux travers
des vues.

2.2 Suppression de tuples Exemple :

Update Emprunt Set Retour = ‘02/03/01’
Syntaxe : Where Code_Liv = ‘14’ and Retour IS NULL ;
Delete From <nom table ou vue>
Where <critère de sélection> ;
2.4 Sélection de tuples
Description :
La clause Where est optionnelle, lorsqu’elle n’est pas présente, tous les Syntaxe:
tuples de la table ou de la vue sont détruits. Select <clause d’unicité>
<liste de colonnes>
Exemple : From <liste de tables>
Delete From Emprunt Where Retour is Not Null ; Where <critère de sélection>
Group By <liste de colonnes>
Suppression de toutes les lignes Having <critère de sélection>
TRUNCATE TABLE <nom_table> [DROP | REUSE STORAGE] Order By <critère d’ordre> ;

- DROP STORAGE: libération des blocs mémoires affectés à la Description:

table Chaque requête de sélection contient au minimum les clauses Select et
- REUSE STORAGE : conservation de l’espace mémoire alloué à From.
la table L’ordre d’apparition des mots clés (Select, From, …) doit être identique à
celui défini dans la syntaxe.
2.3 Modification de tuples La <clause d’unicité> définie au moyen du mot clé DISTINCT ou ALL
(option par défaut), permet d’afficher un seul exemplaire des tuples en double
Syntaxe: quand Distinct est choisi.
Update <nom de table ou vue> La <liste de colonnes> dans la clause Select composée d’une astérisque
Set <col1> = {<expression1> | (SELECT…)} (‘*’) indique que toutes les colonnes sont retenues. Le nom de la colonne peut
[, <col2> = {<expression2> | (SELECT…)},…] être préfixé du nom ou alias (synonyme) de la table ou vue dans laquelle elle se
[Where <critère de sélection>] ; trouve (expl [Link]). Le nom d’une colonne peut être une valeur calculée
(expl Min(prix)). La <liste de colonnes> de la clause Group By doit contenir
L’ordre SELECT doit ramener une seule valeur nécessairement des colonnes de la <liste de colonnes> de la clause Select.

Professeur : ASSALE Adjé Louis 19/33 INP-HB Professeur : ASSALE Adjé Louis 20/33 INP-HB
ORACLE LE LANGAGE SQL ORACLE LE LANGAGE SQL

Le <critère de sélection> est une expression booléenne vraie ou fausse. Select L.Code_Liv, Titre, Auteur
L’évaluation de ce critère conditionne le choix ou le rejet du tuple sur lequel il From Livre L, Emprunt E
s’applique. Le <critère de sélection> de la clause Having s’applique aux Where L.Code_Liv = E.Code_Liv And Retour Is Null ;′
partitions créées par Group By. De ce fait la clause Having ne peut exister sans
la clause Group By. L’utilisation de requête SQL dans la clause Where (sous-requête) est
La <liste des tables> est composée de noms de relations ou de vues autorisée à condition de mettre la sous-requête entre parenthèse. Les sous-
séparés par des virgules. Chaque nom peut être associé à un synonyme ou alias requêtes sont utilisées dans les cas suivants :
(expl « Etudiant E , Livre L»).S’il y a plus d’une table dans la <liste de tables> • Avec les opérateurs de comparaison :
penser à une jointure en définissant le critère de jointure dans la clause Where. Select * From Livre Where Prix>(Select AVG(Prix) From Livre) ;
Le <critère d’ordre> est constitué de noms de colonnes de la <liste de • Avec l’opérateur d’ensemble IN :
colonnes> séparés par des virgules, ou d’une liste de numéro séparés par des Select * From Livre Where Code_liv In (Select Code_liv
virgules. Chaque numéro correspond au rang d’une colonne dans la <liste de From Emprunt) ;
colonnes> de la clause Select. A coté de chaque nom de colonne, l’on peut • Avec les contrôles ANY ou ALL :
ajouter la mention « DESC » pour un tri dans un ordre décroissant. Par défaut, Select * From Livre Where Prix >Any (Select Prix
c’est la mention « ASC » ordre croissant. From Livre Where Genre = ‘Roman’) ;
• Avec test d’existence :
Exemples : Select * From Livre Where Exists (Select * From Emprunt
En plus de opérateurs arithmétiques et logiques (+, -, *, /, OR, AND, Where Livre.Code_Liv = Emprunt.Code_Liv);
NOT, =, >=, >, <, <=, <>), les opérateurs particuliers suivants sont utilisés dans • Avec une liste de colonnes dans le Where:
le <critère de sélection> : … Where (col1, col2, …) = (SELECT col1, col2, …)
• Opérateur d’intervalle BETWEEN :
Select * From Livre Where Prix Between 8000 and 10000 ;
• Opérateur d’ensemble IN: 2.5 Notions de privilèges
Select * From Livre Where Prix In (8000, 9000, 10000) ;
• Opérateur de test de valeur nulle IS NULL : Un privilège est le droit attribué à un utilisateur d’exécuter un ensemble donné
Select * From Emprunt Where Retour Is Not Null ; d’ordres SQL ou d’accéder à certains objets de la base de données.
• Opérateur de ressemblance LIKE : Un privilège peut être attribué soit directement à un utilisateur, soit à un rôle. Le
le caractère ′_′ remplace n’importe quel caractère et ′%′ n’importe quel rôle sera ensuite attribué à un ou plusieurs utilisateurs.
séquence de caractères. On distingue deux types de privilèges :
Select * From Livre Where Titre Like ′% les%′;
Select * From Livre Where titre Like ′_A%′;
2.5.1. Privilèges objet
Les fonctions de calcul COUNT(), SUM(), AVG(), MIN() et MAX() peuvent
être utilisées dans la clause Select et dans la clause Having. Elles s’utilisent Ils donnent le droit d’accès à une table, une vue, une séquence, une procédure,
généralement conjointement avec la clause Group By : une fonction ou un package.
Select Sum(Prix) From Livre Where Genre = ′Roman′ ;
Select Auteur, Count(*), Min(Prix), Max(Prix) From Livre Liste des privilèges objet :
Group By Auteur Having Count(*)>=2 ;
Objet Procédure
La spécification d’un critère de jointure est nécessaire dans la clause Where Table Vue séquence fonction
lorsqu’on a plus de 2 tables dans la <liste de tables>. Elle s’effectue par une privilège: package
comparaison d’attributs de différentes tables : ALTER Oui Oui
EXECUTE Oui

Professeur : ASSALE Adjé Louis 21/33 INP-HB Professeur : ASSALE Adjé Louis 22/33 INP-HB
ORACLE LE LANGAGE SQL ORACLE LE LANGAGE SQL

DELETE Oui Oui

INDEX Oui CREATE TRIGGER Création de déclencheurs dans son schema
INSERT Oui Oui Oui CREATE ANY TRIGGER Création de déclencheurs dans tous les schémas
REFERENCES [(col,…)] Oui ALTER ANY TRIGGER Activation, désactivation et compilation de triggers
SELECT [(col,…)] Oui Oui Oui DROP ANY TRIGGER Suppression de déclencheurs dans tous les schémas
UPDATE [(col,…)] Oui Oui
ALL Tous les droits
READ Droit d’accès en consultation 2.5.3. Attribution d’un privilège
NB : une séquence est une suite arithmétique de nombres
L’ordre GRANT sert à attribuer un privilège ou un rôle à un utilisateur ou à un
rôle.
2.5.2. Privilèges système
• Privilèges système ou rôles
Ils donnent le droit d’exécuter des actions sur un certain type d’objets. Il existe GRANT {privilège système | rôle} [, {privilège système | rôle}] …
plus de 80 privilèges système. TO {nom_utilisateur | rôle | PUBLIC} [, {nom_utilisateur | rôle |
PUBLIC}] …
Liste de quelques privilèges système : [WITH ADMIN OPTION]
• Privilèges objet
Privilège Opérations autorisées GRANT privilège objet [, privilège objet] …
CREATE SESSION Connexion à la base de données ON objet
ALTER SESSION Modification d’une session TO {nom_utilisateur | rôle | PUBLIC} [, {nom_utilisateur | rôle |
ALTER SYSTEM Modification des paramètres de session PUBLIC}] …
CREATE ANY USER Création d’utilisateur [WITH GRANT OPTION]
ALTER USER Modification des caractéristiques d’un utilisateur
DROP USER Suppression d’un utilisateur WITH ADMIN OPTION ou WITH GRANT OPTION autorise celui qui a reçu
CREATE TABLE Création de tables et d’index dans son schéma le privilège ou rôle à le transmettre à un autre utilisateur ou rôle.
CREATE ANY TABLE Création de tables dans tous les schémas PUBLIC permet d’affecter le privilège ou rôle à tous les utilisateurs.
ALTER ANY TABLE Modification des tables et vues de tous les schémas
DROP ANY TABLE Suppression de tables dans tous les schémas
2.5.4. Suppression d’un privilège
CREATE VIEW Création de vues dans son propre schéma
CREATE ANY VIEW Création de vues dans tous les schémas
DROP ANY VIEW Suppression de vues dans tous les schémas Un privilège ou un rôle peut être enlevé à un utilisateur ou à un rôle par l’ordre
- CREATE PROCEDURE - Création de procédures, fonctions et de packages REVOKE.
- CREATE ANY dans son propre schéma
PROCEDURE -- Création de procédures, fonctions et de packages • Privilèges système ou rôles
- ALTER ANY dans tous les schémas REVOKE {privilège système | rôle} [, {privilège système | rôle}] …
PROCEDURE - Modification de procédures, fonctions et de FROM {nom_utilisateur | rôle | PUBLIC} [, {nom_utilisateur | rôle |
- DROP ANY packages dans tous les schémas PUBLIC}] ;
PROCEDURE - Suppression de procédures, fonctions et de • Privilèges objet
- EXECUTE ANY packages dans tous les schémas REVOKE privilège objet [, privilège objet] …
PROCEDURE - Exécution de procédures, de fonctions et ON objet
références à des packages publics dans tous les FROM {nom_utilisateur | rôle | PUBLIC} [, {nom_utilisateur | rôle |
schéma PUBLIC}] …

Professeur : ASSALE Adjé Louis 23/33 INP-HB Professeur : ASSALE Adjé Louis 24/33 INP-HB
ORACLE LE LANGAGE SQL ORACLE LE LANGAGE SQL

[CASCADE CONSTRAINTS]
[FORCE] NOT IDENTFIED : Oracle ne vérifie pas les utilisateurs attachés à ce rôle
IDENTIFIED : Oracle vérifie les utilisateurs attachés à ce rôle
CASCADE CONSTRAINTS n’est utilisable qu’avec le privilège BY mot_de_passe : le mot de passe est obligatoire pour activer le rôle
REFERENCES et supprime les possibilités de contraintes référentielles EXTERNALLY : Oracle vérifie l’accès au rôle des utilisateurs se connectant au
accordées. travers du système d’exploitation.
FORCE n’est utilisable qu’avec le privilège EXECUTE pour les objets ayant
des dépendances sur des tables. Pour être effectif, un rôle doit être activé. La commande SET ROLE permet
d’activer ou de désactiver un rôle.

2.6 Notion de rôles SET ROLE {nom_rôle [IDENTIFIED BY mot_de_passe]

, nom_rôle [IDENTIFIED BY mot_de_passe] …}
C’est un ensemble de privilèges donnés à des utilisateurs ou à d’autres rôles. | ALL [EXCEPT rôle [, rôle] …]
• Ils peuvent être composés à la fois de privilèges système et de | NONE ;
privilèges objet Un rôle qui ne figure pas dans la liste est considéré comme désactivé.
• Ils ne sont pas la propriété d’un utilisateur
• Ils peuvent être activés ou inhibés pour chaque utilisateur
• Ils peuvent exiger des mots de passe à leur activation 2.6.2. Modification et suppression
• Leurs définitions sont stockées dans le dictionnaire de données
Cinq rôles sont prédéfinis dans Oracle8 pour assurer la compatibilité avec les La modification s’effectue par :
versions précédentes : ALTER ROLE nom_rôle [NOT IDENTIFIED]
• CONNECT : un utilisateur qui a le rôle CONNECT peut créer des [IDENTIFIED BY mot_de_passe] ;
tables, des vues, des séquences, des clusters, des synonymes et les liens La suppression par :
vers d’autres bases de données. DROP ROLE nom_rôle ;
Le rôle est alors retiré de tous les utilisateurs et de tous les autres rôles auxquels
• RESOURCE : l’utilisateur peut créer ses propres tables, index,
il avait été affecté.
séquences, clusters, procédures, fonctions et déclencheurs.
• DBA : l’utilisateur possède tous les privilèges système nécessaire à
l’administrateur de la base de données, et il a la possibilité de les 2.7 Dictionnaire de données
transmettre à d’autres utilisateurs.
• EXP_FULL_DATABASE : l’utilisateur possède les privilèges Les vues suivantes permettent de visualiser le contrôle des accès à la base de
nécessaires à l’exportation d’une base de données. Le rôle DBA données.
possède ce privilège.
• IMP_FULL_DATABASE : l’utilisateur possède les privilèges Vue Informations
nécessaires à l’importation d’une base de données. Le rôle DBA ALL_COL_PRIVS Liste des colonnes sur lesquelles l’utilisateur ou
possède ce privilège. PUBLIC a des privilèges.
ALL_TAB_PRIVS Liste des objets sur lesquelles l’utilisateur ou
2.6.1. Création et activation PUBLIC a des privilèges.
DBA_COL_PRIVS Liste des privilèges accordés sur des colonnes de
tables.
La syntaxe de création est :
DBA_ROLES Liste des rôles de la base de données.
CREATE ROLE nom_rôle
DBA_ROLE_PRIVS Listes des rôles attribués à des utilisateurs ou à des
[NOT IDENTIFIED]
rôles.
[IDENTIFIED { BY mot_de_passe | EXTERNALLY}] ;

Professeur : ASSALE Adjé Louis 25/33 INP-HB Professeur : ASSALE Adjé Louis 26/33 INP-HB
ORACLE LE LANGAGE SQL ORACLE LE LANGAGE SQL

DBA_SYS_PRIVS Listes des privilèges système attribués à des

utilisateurs ou à des rôles.
DBA_TAB_PRIVS Liste des privilèges sur les objets de la base. 3.1 Expressions et fonctions
USER_COL_PRIVS Liste des privilèges sur colonnes dont l’utilisateur
est soit le bénéficiaire, soit le créateur.
USER_ROLE_PRIVS Liste des rôles attribués à l’utilisateur. 3.1.1. Chaînes de caractères
USER_SYS_PRIVS Listes des privilèges système attribués à
l’utilisateur de façon directe. La concaténation est le seul opérateur utilisable pour définir une expression sur
USER_TAB_PRIVS Liste des privilèges sur objets dont l’utilisateur est une chaîne de caractères. Son symbole est « || ».
soit le bénéficiaire, soit le créateur. Les fonctions suivantes sont applicables sur des chaînes de caractères :
ALL_ALL_TABLES Donne des informations sur la structure des tables
et des vues accessibles à l’utilisateur. INITCAP (ch) : met en majuscule la 1ère lettre de chaque mot de la
ALL_CLUSTER Donne des informations sur la structure des chaîne
clusters accessibles à l’utilisateur INSTR (ch1, ch2 [, n] [, m]) renvoie la position de ch2 dans ch1 ou de
ALL_OBJET_TABLES Donne des informations sur la structure des tables la m ième occurrence de ch2 dans ch1 à partir du
accessibles à l’utilisateur caractère n; n et m sont optionnels et ont par
défaut la valeur 1
ALL_TAB_COLUMNS Donne la description des colonnes des tables, des
REPLACE (ch, ch_source, ch_rempl) remplace dans ch toutes les
vues et des clusters accessibles à l’utilisateur.
séquences de ch_source par la séquence ch_rempl
DBA_ALL_TABLES Donne des informations sur la structure des tables
LENGTH (ch) : longueur de ch
et des vues.
LOWER (ch) : mettre ch en minuscules
DBA_TAB_COLUMNS Donne la description des colonnes des tables, des
SUBSTR (ch, m, n) : extraire une sous-chaîne de ch commençant au m
vues et des clusters de la base.
ième caractère et de longueur n. Si n est omis,
DBA_TABLES Donne les caractéristiques des tables de la base.
prendre le reste de la chaîne.
USER_ALL_TABLES Donne des informations sur la structure des tables TRANSLATE (ch, origine, cible) : transforme dans ch toutes les
et des vues accessibles à l’utilisateur. occurrences de origine par cible
USER_TAB_COLUMNS Donne la description des colonnes des tables de UPPER (ch) : mettre ch en majuscules
l’utilisateur.
USER_TABLES Donne la description des tables de l’utilisateur. 3.1.2. Nombres

Les opérateurs addition (+), soustraction (-), multiplication (*) ou division (/)
2.8 Les transactions
permettent de définir une expression sur les nombres.
Les fonctions suivantes leurs sont applicables :
Syntaxe:
Commit Work ; ABS (n) la valeur absolue de n
Rollback Work ; CEIL (n) le plus petit auteur supérieur ou égal à n.
FLOOR (n) la partie entière de n.
Description : MOD (m, n) le reste de la division de m par n.
La transaction, ensemble de commandes, est une unité logique de POWER (m, n) n à la puissance n.
manipulation de données. La fin de la transaction spécifiée par « commit work » ROUND (n [, m]) n arrondi à un nombre à m décimal.
valide toutes les commandes, « rollback work » annule toutes les commandes. SIGN (n) -1 si n < 0
0 si n = 0
3. Spécificités pour ORACLE 1 si n > 0

Professeur : ASSALE Adjé Louis 27/33 INP-HB Professeur : ASSALE Adjé Louis 28/33 INP-HB
ORACLE LE LANGAGE SQL ORACLE LE LANGAGE SQL

SQRT (n) la racine carrée de n $ Le symbole monétaire de la langue utilisée précédera le premier
TRUNC (n [, m]) n tronqué à m décimal après le point décimal. Si m est chiffre significatif
< 0 la troncature se fait avant ce point décimal.
Le masque pour une date est de la forme : ′DD-MON-YY′ ′DD/MM/YY′
′[Link]′ ou une combinaison de caractères spécifiques.
3.1.3. Date
En plus des fonctions d’agrégat, AVG(), SUM(), MIN(), MAX(), COUNT(),
L’ajout d’une durée, exprimée en nombre de jours, à une date, s’effectue sous la Oracle offre : VARIANCE() pour calculer la variance et STDDEV pour calculer
forme : var_date +/- var_nombre. Le résultat est une date. l’écart type ou déviation standard.
L’obtention du nombre de jours séparant deux dates, sous la forme date2-date1 ;
le résultat est un nombre.
Les principales fonctions dates sont : 3.2 Traitement des structures arborescentes
ADD_MONTHS (Date, nombre) : retourne une date
MONTHS_BETWEEN (Date1, Date2) : retourne un nombre Il est possible de manipuler des structures de type liste ou arbre. Pour cela,
LAST_DAY (date) : renvoie la date du dernier jour du mois Oracle a introduit dans l’ordre SELECT après la clause WHERE la clause
correspondant à date. suivante : CONNECT BY condition [START WITH condition].
NEXT_DAY (date, nom_de_jour) : retourne la date du prochain jour de Il suffit d’introduire dans la table un attribut (clé du parent) représentant le lien
la semaine spécifié dans nom_de_jour. avec l’élément suivant ou précédent dans la liste ou dans l’arbre.
ROUND (date [, précision]) : arrondi la date selon la précision spécifiée
(sous forme de masque de date). La clause CONNECT BY condition définit le sens de la liaison. La condition
TRUNC (date [, précision]) : tronque la date à la précision spécifiée. s’exprime sous la forme :
SYSDATE : renvoie la date système Colonne1 = PRIOR colonne2
Ou PRIOR colonne1 = colonne2
Le mot clé PRIOR, associé à l’une ou l’autre colonnes, définit le sens de
3.1.4. Fonctions de conversion et d’agrégat parcours en indiquant la colonne utilisée comme critère de recherche à chaque
niveau.
Les principales fonctions de conversion sont : La condition peut contenir aussi un prédicat. Lorsque ce prédicat n’est pas
TO_CHAR ({date | nombre} [, masque]) : convertir une date ou un satisfait, le parcours de l’arbre s’arrête.
nombre en chaîne de caractères en fonction d’un masque.
TO_DATE (chaîne [, masque]) : convertir une chaîne en date en La clause START WITH condition permet d’indiquer la ligne à utiliser comme
fonction d’un masque. racine de l’arbre ou début de liste. En l’absence de cette clause, un arbre sera
TO_NUMBER (chaîne) : convertir une chaîne en nombre. construit à partir de chaque ligne satisfaisant le prédicat d’une clause WHERE
éventuelle.
Le masque pour un nombre est une chaîne de caractères combinant les
caractères suivants : Il est possible d’obtenir pour chaque ligne le niveau correspondant dans l’arbre
ou dans la liste. La première ligne sélectionnée est de niveau 1. Pour cela on
introduit la variable level dans la clause SELECT.
Caractère Effet
9 Représente un chiffre 4. EXERCICES
0 Représente un chiffre présent même si non significatif
. Point décimal apparent
V Définit la position de séparation entre partie entière et fractionnaire 4.1 TP1
, Une virgule apparaîtra à cet endroit

Professeur : ASSALE Adjé Louis 29/33 INP-HB Professeur : ASSALE Adjé Louis 30/33 INP-HB
ORACLE LE LANGAGE SQL ORACLE LE LANGAGE SQL

• Créer les utilisateurs, INGxx avec le mot de passe INGxx et INGyy avec • Créer le schéma suivant :
le mot de passe INGyy. Classe(Code_cl, Intitule, Effectif)
• attribuer les privilèges de connexion, de création et suppression de tables, Etudiant(Num, Nom, Sexe, Code_cl, Parrain)
vues, synonymes, procédures et déclencheurs à INGxx et les privilèges de Suit(Num, Mat, Note, date_ob)
connexion, modification de session, de création, suppression et Matiere(Mat, Libelle,)
modification de tables, vues synonymes, procédures et déclencheurs dans avec les contraintes de PRIMARY KEY pour toutes les clés primaires,
n’importe quel schéma à INGyy. FOREIGN KEY pour les clés étrangères (Code_cl dans Etudiant, Num
• Se connecter en tant que INGyy/INGyy dans Suit et Mat dans Suit), CHECK sur sexe pour valeur M ou F et
créer la table PERSONNE (Num, Nom, Fonction, Salaire) DEFAULT sur effectif pour valeur 0.
insérer quelques lignes (maximum 5 lignes avec une fonction au moins • Visualiser les informations sur les contraintes des différentes tables.
«Ingénieur »). • Tester les différentes contraintes :
• Se connecter en tant que system/manager et exécuter une commande - insérer les tuples (′BD′, ′Bases de Donnés′) et (′BD′, ′Génie Logiciel′)
SELECT sur la table PERSONNE de INGyy. dans la table Matiere. Que se passe-t-il ?
Tenter de donner à INGxx le privilège SELECT sur la table PERSONNE - désactiver la contrainte PRIMARY KEY de la table Matiere (si
de INGyy. Que se passe-t-il et pourquoi ? nécessaire les contraintes liées), insérer le deuxième tuple. Que constatez-
• Se connecter sous INGyy et donner à INGxx le privilège SELECT sur la vous ?
table PERSONNE. - activer la contrainte PRIMARY KEY de la table Matiere, que se passe-t-
• Vérifier à l’aide d’une vue du dictionnaire que le privilège a été il ?
correctement assigné. - rétablir la table
• Se connecter sous INGxx et vérifier que le privilège a été correctement - tester les contraintes CHECK et FOREIGN KEY.
attribué en utilisant une requête. • Insérer les informations suivantes :
• Se connecter sous INGyy et donner à INGxx la possibilité de mettre à jour
les colonnes Nom et Fonction de la table PERSONNE. Classe Matiere
• Se connecter sous INGxx et vérifier à l’aide de vues du dictionnaire de Code_cl Intitule Effectif Mat Libelle
données l’attribution des privilèges G1 Cracks BD Bases de Données
• Etant connecté sous INGxx, augmenter de 20% les salaires. Que se passe- G2 Soucieux IA Intelligence Artificielle
t-il et pourquoi ? changer la fonction Ingénieur en Technicien. Que se G3 Bosseurs GL Génie Logiciel
passe-t-il et pourquoi ? G4 Fainéants MM Multi-Média
• Etant connecté sous INGxx, créer une table ESSAI à deux colonnes dans G5 Cools SR Système et Réseaux
le schéma de INGyy. Que se passe-t-il et pourquoi ? MT Méthodologies
• Se connecter sous INGyy et créer la table ESSAI dans le schéma de
INGxx. Que se passe-t-il et pourquoi ?
Etudiant Suit
4.2 TP2 Num Nom Sexe Code_cl Parrain Num Mat Note Date_ob
• Se connecter sous system/manager, donner les privilèges système de 1 Assalé M G5 1 IA 80 12/01/98
création, modification et suppression de cluster à INGxx. 2 Adjé M G4 8 3 IA 75 06/08/99
• Se connecter sous INGxx/INGxx. 3 Koffi F G5 5 3 BD 80 23/05/00
• Créer un cluster indexé Mat_Etud avec Num comme clé de cluster. Placer 4 Konan M G2 2 2 BD 90 12/03/98
le cluster dans le tablespace USER_DATA et spécifier 2048 octets 5 Beugré F G1 2 2 SR 100 03/05/97
d’espace pour stocker les lignes apparentées. 6 Dadié M G1 1 5 GL 100 23/11/99
• Créer un Index i_clust_Etud pour ce cluster dans le tablespace USER1 7 Zié M G3 4 5 MM 50 26/10/98

Professeur : ASSALE Adjé Louis 31/33 INP-HB Professeur : ASSALE Adjé Louis 32/33 INP-HB
ORACLE LE LANGAGE SQL

8 Kouassi F G2 1 1 SR 80 15/02/00
9 Guidy F G4 6 9 MT 60 01/03/01
10 Bilé F G2 5 7 GL 45 13/07/98
11 Amon M G4 7 4 SR 90 19/06/97
12 Mel M G5 7 6 BD 60 13/04/99
6 GL 55 30/05/00
12 IA 95 14/08/01
10 BD 50 27/08/00
8 BD 65 31/07/97
11 SR 80 26/10/98
9 BD 50 18/06/99
7 SR 40 09/09/99
10 SR 35 07/07/97

• Répondre aux questions suivantes:

Les matières (mat) de la classe Cracks
Les étudiants (num, nom) qui ont une note de 80 dans chacune de leurs
matières
Les matières (mat) où tous les étudiants sont de la même classe
Les étudiants qui n'ont pas eu 100 comme note dans leurs matières
Les matières où il y a un seul étudiant
Nombre de matières suivis par classe
Nombre d’étudiants par matière
Nombre de classes par matière
Afficher l’arbre de parrainage en commençant par Kouassi

Professeur : ASSALE Adjé Louis 33/33 INP-HB