Oracle et SQL
Oracle : Système de Gestion de Bases de Données (SGBD) relationnel (SGBDR) édité par
Oracle Corporation (http://www.oracle.com/). Basé sur SQL (Structured Query Language),
langage de définition, de manipulation et de contrôle de bases de données relationnelles
(standard ANSI depuis 1986).
La première version d’Oracle a été proposée en 1981, la Version qu’on utilise dans ce TP:
est la version Oracle 8i ou 10g.
1. SQL : (Structured Query Language)
SQL est à la fois un langage de :
• "LDD (Langage de Définition de Données) : création, modification et suppression des
définitions des tables
• "LMD (Langage de Manipulation de Données) : ajout, suppression, modification et
interrogation des données
• "LCD (Langage de Contrôle de Données) : gestion des protections d’accès
Chaque instruction est terminée par ( ;). Et pour insérer des Commentaires il faut les
précéder par : /* … */.
1.1. Définition des données
SQL propose aux utilisateurs un ensemble d’instructions lui permettant de créer des tables,
des indexes, des vues et d’associer à une définition d’une contrainte.
a. Création d’une table :
CREATE TABLE (Attribut1 TYPE, Attribut2 TYPE, …, contrainte_integrité1,
contrainte_integrité2, …);
Type des données :
• NUMBER(n) : Entier à n chiffres
• NUMBER(n, m) : Réel à n chiffres au total (virgule comprise), m après la virgule
• VARCHAR(n) : Chaîne de n caractères (entre ‘ ’)
• DATE : Date au format ‘JJ-MM-AAAA’
b. Définitions des contraintes d’intégrité
• Clé primaire : CONSTRAINT nom_contrainte PRIMARY KEY (attribut_clé [,attribut_clé2, …])
• Clé étrangère : CONSTRAINT nom_contrainte FOREIGN KEY (attribut_clé_ét) REFERENCES
table(attribut)
• Contrainte de domaine : CONSTRAINT nom_contrainte CHECK (condition)
c. Création d’index (accélération des accès)
CREATE [UNIQUE] INDEX nom_index ON nom_table (attribut [ASC|DESC], …);
Tel que : UNIQUE → pas de double
ASC/DESC → ordre croissant ou décroissant
1/4
d. Destructions :
• Pour supprimer une table : DROP TABLE nom_table;
• Pour supprimer un index : DROP INDEX nom_index;
e. Instruction des mises à jour :
• Ajout d’attributs : ALTER TABLE nom_table ADD (attribut TYPE, …);
• Modifications d’attributs : ALTER TABLE nom_table MODIFY (attribut TYPE, …);
• Suppression de contraintes : ALTER TABLE nom_table DROP CONSTRAINT
nom_contrainte;
• Ajout d’un tuple : INSERT INTO nom_table VALUES (val_att1, val_att2, …);
• Mise à jour d’un attribut : UPDATE nom_table SET attribut=valeur [WHERE condition];
• Ajout d’un tuple : INSERT INTO nom_table VALUES (val_att1, val_att2, …);
• Mise à jour d’un attribut : UPDATE nom_table SET attribut=valeur [WHERE condition];
• Suppression de tuples : DELETE FROM nom_table [WHERE condition];
1.2. Manipulation de données :
Après la création des tables, il est alors possible de récupérer les données stockées grâce à
des requêtes SQL :
SELECT [ALL|DISTINCT] attribut(s) FROM table(s) [WHERE condition]
[GROUP BY attribut(s) [HAVING condition]]
[ORDER BY attribut(s) [ASC|DESC]];
• Pour afficher tous les tuples d’une table : select * From table (s) ;
• Pour ordonner les tuples afficher : ORDER BY et ASC : ordre ascendant et DESC : ordre
descendant.
Il est possible de faire des Calculs grâce aux fonctions prédéfinies comme AVG, SUM, etc.
mais aussi en indiquant l’opération dans le bloc SELECT
ex. SELECT Attribut * x FROM Table;
• Projection: c’est une opération qui permet d’afficher un ensemble d’attribut d’une table:
ex. Table (att1, att2, att3, att4)
SELECT att2, att4 FROM Table;
• Restriction: c’est choisir un ensemble de tuples vérifiant la condition indiquée dans le
“Where” parmi les tuples d’une table.
ex. Table (att1, att2, att3, att4)
SELECT * FROM Table WHERE att3 = y;
Dans le “Where” on peut employer :
- Les opérateurs de comparaison (>,<, ≤, ≥, =) ;
- Vérifier l’appartenance d’un attribut à un intervalle (Where att BETWEEN val1 AND
val2) ;
- Indiquer qu’un attribut a une valeur précise : WHERE att IS val;
2/4
- Vérifier qu’un attribut possède comme valeur une chaîne de caractère se termine par un
ensemble de caractère précis (chaine) :WHERE att LIKE ‘%chaine’; commence par un
ensemble de caractère précis (chaine) WHERE att LIKE chaine%’ et contient une chaine
de caractère (chaine) : WHERE att LIKE‘%chaine%’ .
- Préciser qu’un attribut appartient à un ensemble (WHERE att IN (x, y , z) pour indiquer
que l’attribut « att » s’il a une valeur égale à x, y ou z le tuple sera sélectionné. Comme il
y a une Possibilité d’utiliser la négation pour tous ces prédicats : NOT BETWEEN,
NOT NULL, NOT LIKE, NOT IN.
• Fonctions d’agrégat : Elles opèrent sur un ensemble de valeurs.
– AVG(), VARIANCE(), STDDEV() : moyenne, variance et écart-type des valeurs ;
– SUM() : somme des valeurs ;
– MIN(), MAX() : valeur minimum, valeur maximum ;
– COUNT() : nombre de valeurs.
• Les Vues : Une Vue est une table virtuelle calculée à partir d’autres tables grâce à une
requête.
Définition d’une vue : CREATE VIEW nom_vue AS requête;
a. Intérêt des vues :
• Simplification de l’accès aux données en masquant les opérations de jointure.
• Sauvegarde indirecte de requêtes complexes.
• Présentation de mêmes données sous différentes formes adaptées aux différents usagers
particuliers.
• Support de l’indépendance logique.
• Renforcement de la sécurité des données par masquage des lignes et des colonnes sensibles aux
usagers non habilités
b. Problèmes de mise à jour, restrictions :
La mise à jour de données via une vue pose des problèmes et la plupart des systèmes
impose d’importantes restrictions.
• Le mot clé DISTINCT doit être absent.
• La clause FROM doit faire référence à une seule table.
• La clause SELECT doit faire référence directement aux attributs de la table concernée (pas
d’attribut dérivé).
• Les clauses GROUP BY et HAVING sont interdites.
3/4
1.3. Sécurité et autorisation
• Transmission de privilèges
GRANT privilège ON table|vue
TO user|PUBLIC [WITH GRANT OPTION];
Privilèges :
SELECT : lecture INSERT : insertion
UPDATE : mise à jour DELETE : suppression
ALL : tous les privilèges ALTER : destruction
INDEX : construction d’index
• Suppression de privilèges
REVOKE privilège ON table|vue FROM user|PUBLIC;
2. Catalogue du système
Contient sous forme relationnelle la définition de tous les objets créés par le système et les
usagers. Ces tables sont accessibles avec SQL (en mode consultation uniquement).
Quelques tables utiles gérées par Oracle
• USER_CATALOG (TABLE_NAME, TABLE_TYPE)
• USER_TAB_COLUMNS (TABLE_NAME, COLUMN_NAME, …)
• USER_IND_COLUMNS (INDEX_NAME, TABLE_NAME, COLUMN_NAME, …)
• ALL_TABLES (TABLE_NAME, OWNER, …)
4/4
