Compléments BD

FORMATION D’INGÉNIEURS EN INFORMATIQUE FIIFO
UNIVERSITÉ PARIS SUD
IUT d’ORSAY et FACULTÉ des SCIENCES d’ORSAY
Spécialisation Bases de données
G.Ausseresse
COMPLÉMENTS
de BASES de DONNÉES
ORACLE
Page
I.
1.
2.
II.
DICTIONNAIRE DE DONNEES ............................................................................................... 2
Cas général ............................................................................................................................... 2
Oracle ....................................................................................................................................... 3
PRIVILEGES ET ROLES ........................................................................................................... 5
1. Privilèges objets ....................................................................................................................... 5
2. Privilèges système .................................................................................................................... 5
3. Rôles......................................................................................................................................... 6
III. VUES ........................................................................................................................................... 9
1. Définition ................................................................................................................................. 9
2. Syntaxe ..................................................................................................................................... 9
3. Intérêt ..................................................................................................................................... 10
4. Mise à jour SQL92 ................................................................................................................. 11
5. Mise à jour Oracle .................................................................................................................. 13
IV. DIVERS ..................................................................................................................................... 14
1. Synonymes ............................................................................................................................. 14
2. Pseudo-colonnes..................................................................................................................... 15
3. Table DUAL .......................................................................................................................... 15
4. Séquences ............................................................................................................................... 15
ANNEXE ........................................................................................................................................... 17
Base exemple ETUDES ................................................................................................................. 17
V.
VOCABULAIRE ORACLE ...................................................................................................... 18
VI. REFERENCES........................................................................................................................... 19
FIIFO
769802668
édité le 16/04/2017 22:45:00
I.
DICTIONNAIRE DE DONNEES
1. Cas général
Définition d’une base de données :
La norme SQL92 ne définit pas formellement la notion de base de données. La norme définit par
contre :
- des concepts : environnement-SQL, catalogue, schéma, tables, vues, colonnes, domaines.
- une structure hiérarchique de ces concepts : un environnement-SQL contient zéro, un ou
plusieurs catalogues ; un catalogue contient un ou plusieurs schémas et un schéma est contenu
dans un seul catalogue ; un schéma contient zéro, une ou plusieurs tables et chaque table
appartient à un seul schéma, etc.
Un schéma est un ensemble logique de domaines, tables, contraintes, vues et privilèges. C’est la
notion théorique la plus proche de celle de base de données usuelle (par exemple, Ingres). Chaque
constructeur de SGBD peut en avoir une implémentation physique qui lui est propre.
Chaque catalogue contient un schéma particulier, dit schéma d’information, regroupant des tables
qui définissent tous les objets se trouvant dans les schémas du catalogue. Certains parlent de
dictionnaire ou de méta-base de la base de données. La norme précise un certains nombre de
tables : table des schémas (c.-à-d. décrivant les schémas), table des tables, tables des colonnes,
tables des droits, etc. Mais les utilisateurs ne peuvent accéder à toutes les données de la méta-base
(penser aux mots de passe). Ce qui est alors souvent appelé tables systèmes est en fait un ensemble
de vues sur les tables de la méta-base. Les utilisateurs n’ont accès qu’à ces vues qu’en lecture (pour
des raisons de sécurité évidentes, ils ne peuvent les modifier). Par exemple, le SGBD Oracle définit
plus d’une centaine de vues sur les tables de la méta-base.
Noms des objets d’une base
Avant SQL92, la notion de schéma était couplée avec celle de nom-utilisateur, ce qui garantissait
l’unicité des noms de schémas. En SQL92, un utilisateur pouvant créer plusieurs schémas, ceux-ci,
en cas d’ambiguïté, sont désignés en fonction du catalogue qui les contient. Le nom complet d’une
table est alors :
nom_catalogue.nom_schéma.nom_table
(penser à la définition hiérarchique des fichiers UNIX)
Le schéma entité-association donné en annexe modélise les objets des bases de données SQL92.
Ces objets sont souvent identifiés relativement à d’autres.
A propos de la norme SQL
La norme ne définit pas tout. Parmi les éléments non définis, elle distingue :
- ceux définis par l’implémentation (149 éléments) : l’implémentation doit en définir la syntaxe,
les valeurs, le comportement,…, suivant les cas. Par exemple, la précision du type de donnée
entier doit être définie par l’implémentation.
769802668
Page 2
sur 19
-
ceux dépendants de l’implémentation (75 éléments) : l’implémentation n’a pas besoin de les
préciser. Par exemple, la représentation physique d’un type de donnée, lequel peut varier au
cours des versions successives du SGBD.
2. Oracle
Dictionnaire de données et tables système Oracle :
Le dictionnaire de données d’une base de données se compose de tables de base et de vues (tables
système) sur ces tables. Ces diverses tables sont la propriété de l’utilisateur Oracle SYS et se
trouvent dans l’espace de tables (« tablespace ») SYSTEM de cette BD. Il est important pour
l’intégrité de la BD qu’aucun utilisateur ne puisse modifier ces tables1.
Dans les tables de base, une partie de l’information est cryptée. Seul Oracle peut lire et écrire dans
ces tables. Par contre, les vues sont lisibles par les utilisateurs (s’ils possèdent les droits adéquats)
en utilisant le langage SQL (dès que la base est ouverte).
Que contiennent ces tables ?
 La définition de tous les objets de la BD (tables, vues, index, clusters, synonymes, triggers,
procédures,…).
 Combien d’espace est alloué et combien d’espace est utilisé par ces objets.
 Des informations sur les contraintes d’intégrité.
 Les valeurs par défaut des colonnes.
 Des informations sur les utilisateurs, leurs droits et leurs rôles.
 Des informations d’audit : qui a accédé/mis à jour des objets.
 …
A quoi servent ces tables ?
 Oracle y accèdent pour trouver des informations sur les utilisateurs, les objets des schémas et les
structures de stockages.
 Oracle les modifie chaque fois qu’un ordre du DDL (langage de définition de données) est
exécuté.
 Chaque utilisateur de la BD peut y lire des informations sur la BD.
Des synonymes existent pour plusieurs vues couramment utilisées. Il est recommandé aux
programmeurs de les utiliser car ils sont moins susceptibles de changement d’une version à l’autre
du SGBD.
Exemples :
TABS est le synonyme de USER_TABLES, COLS de USER_TAB_COLUMNS,
CAT de USER_CATALOG, DICT de DICTIONNARY…
Les tables système, qui n’ont pas une appellation définie dans la norme SQL2, portent dans le cas
d’Oracle un nom préfixé suivant leur contenu. Dans plusieurs cas, il existe 3 vues contenant des
informations similaires et qui se distinguent par leur préfixe. Leurs colonnes sont alors les mêmes
sauf les cas signalés ci-après en remarque.
A l’exception d’une table d’audit, SYS.AUD§, qui peut devenir trop volumineuse et dont l’administrateur peut enlever
des lignes sans préjudice pour le fonctionnement de la base.
1
769802668
Page 3
sur 19
PREFIXE
USER_
ALL_
DBA_
Vue utilisateur
Ce qui se trouve dans le
schéma utilisateur
Vue utilisateur étendue
Tout ce à quoi peut
accéder l’utilisateur
Vue pour administrateur BD
Tout ce qui concerne
Nécessite le droit SELECT tous les utilisateurs
ANY TABLE pour y accéder
REMARQUE
Pas de colonne OWNER, car sa
valeur est le nom de l’utilisateur
Sur-ensemble de la vue USER_
associée
Pour des raisons de sécurité,
- certaines des colonnes de ces
vues ne figurent pas dans les
autres vues
- elles ne possèdent pas de
synonymes
EXEMPLES
Un utilisateur veut connaître les objets de son schéma :
SELECT OBJECT_NAME, OBJECT_TYPE FROM USER_OBJECTS
Remarque : Pour bien comprendre la particularité d’Oracle, voici l’équivalent avec Ingres de cette
requête. Si l’utilisateur Ingres veut savoir quelles sont ses tables, vues et index :
SELECT table_name, table_type
FROM iitables
WHERE table_owner = USER
Un utilisateur veut connaître tous les objets auxquels il a accès :
SELECT OWNER, OBJECT_NAME, OBJECT_TYPE FROM ALL_OBJECTS
Un administrateur veut connaître tous les objets de la BD :
SELECT OWNER, OBJECT_NAME, OBJECT_TYPE FROM SYS.DBA_OBJECTS
Contraintes portant sur une table :
Les contraintes associées à une table sont décrites dans la table USER_CONSTRAINTS qui
contient les colonnes suivantes
CONSTRAINT_NAME : nom de la contrainte
CONSTRAINT_TYPE : type de contrainte C pour CHECK, P pour primary key, R pour clé étrangère (intégrité
référentielle)
SEARCH_CONDITION : énoncé de la contrainte
Oracle maintient aussi des tables virtuelles qui reflètent l’activité de la BD et sont appelées
« dynamic performance tables ». Ces tables sont préfixés par V_$. Seuls les DBA peuvent les
utiliser, créer des vues dessus et autoriser des utilisateurs à interroger ces vues.
Des vues de synonymes publics préfixés par V$ existent :
V$DATAFILE : fichiers de la BD
V$FIXED_TABLES : « dynamic performance » tables et vues
769802668
Page 4
sur 19
II.
PRIVILEGES ET ROLES
Dans Oracle, les possibilités en matière de sécurité des données sont plus développées que dans la
norme SQL2 et les privilèges sont accordés à trois niveaux :
1. Privilèges objets
2. Privilèges système
3. Rôles
1. Privilèges objets
Il permettent de réaliser des opérations particulières sur des objets (tables, vues, séquences,
procédures stockées, synonymes de table, …)
ALTER DELETE EXECUTE INDEX INSERT REFERENCES SELECT UPDATE
*
*
*
*
*
*
*
TABLE
*
*
*
*
VUE
*
*
SEQUENCE
*
PROCEDURE
Ils ne peuvent être accordés que par le propriétaire de l’objet ou par qui a reçu le droit d’accorder.
GRANT liste_privilèges | ALL
ON objet
TO liste_utilisateurs | rôle | PUBLIC
[WITH GRANT OPTION] ;
ALL=en une seule commande tous les privilèges possibles sur l’objet
PUBLIC=tous les utilisateurs
permet de transmettre le privilège
Suppression du privilège:
REVOKE liste_privilèges | ALL
ON objet FROM liste_utilisateurs | rôle | PUBLIC
[CASCADE CONSTRAINT][FORCE] ;
La suppression d’un privilège a des effets, dits en CASCADE, si l’utilisateur avait utilisé ce
privilège pour l’accorder à d’autres ou créer des objets ou des contraintes d’intégrité référentielles.
De plus, deux options sont possibles :
CASCADE CONSTRAINT :
option pour supprimer toutes les contraintes de références
définies grâce au privilège REFERENCES par le révoqué.
FORCE :
option pour révoquer un privilège EXECUTE sur un objet
défini par le révoqué.
2. Privilèges système
Ils permettent aux utilisateurs d’une base de réaliser diverses opérations ou catégories d’opérations,
sans qu’elles portent sur un objet précis, mais sur un type de commande. Par exemple : créer une
table, se connecter à la base, etc. Il en existe presque une centaine dans Oracle 8i.
769802668
Page 5
sur 19
Exemples : CREATE [ANY] CLUSTER|INDEX|PROCEDURE|SEQUENCE|TABLE|TRIGGER|TYPE|VIEW
Le paramêtre ANY signifie que le privilège est valable dans tous les schémas de la base.
 Accorder un privilège système :
GRANT liste_privilèges_système
TO liste_utilisateurs | rôle | PUBLIC
PUBLIC = tous les utilisateurs
[WITH ADMIN OPTION] ;
3. Rôles
Cette notion n’existe pas dans SQL2 et est propre à ORACLE
3.1) Définition
Un rôle est un ensemble de privilèges objet, de privilèges système et de rôles.
3.2) Intérêt des rôles
Ils permettent de gérer au mieux les droits des différents utilisateurs tout en consolidant la sécurité.
Une hiérarchie de rôles aide à gérer plus facilement le sécurité des données d’une base.
On distingue des rôles :
 au niveau application contenant les privilèges nécessaires à l’exécution d’une application.
 au niveau utilisateur contenant privilèges et rôles qui sont nécessaires à un type d’utilisateur.
Exemple : Cas où différents utilisateurs doivent pouvoir accéder à deux applications :
gestion_stages et gestion_enseignants.
1.
Créer un rôle appelé gest_sta_ens
2.
Donner les rôles des applications gestion_stages et gestion_enseignants au rôle
gest_sta_ens
3.
accorder le rôle gest_sta_ens aux utilisateurs concernés.
util1
util2
util4
util3
Rôle
Gest_sta_ens
Rôle
Gestion_stage
s
Privilèges de
l ’application
Gestion_stages
769802668
util5
Rôles
Utilisateur
s
Rôle
Gestion_enseignants
Privilèges de
l ’application
Gestion_enseignants
Rôles
Application
s
Privilèges
Application
s
Page 6
sur 19
3.3) Mode d’emploi des rôles
1. Le créateur (qui possède le privilège CREATE ROLE) doit donner un nom au rôle :
CREATE ROLE nom_rôle
([NOT IDENTIFIED | IDENTIFIED [BY password | EXTERNALLY | GLOBALLY]) ;
NOT IDENTIFIED est l’option par défaut : aucun mot de passe n’est demandé pour activer le rôle.
IDENTIFIED signifie que l’utilisateur doit être autorisé par une méthode spécifique avant que le
rôle soit activé.
BY password : le rôle est protégé par un mot de passe qui sera demandé chaque fois qu’il sera
activé.
EXTERNALLY : l’autorisation est donné par un service externe (système d’exploitation par
exemple)
GLOBALLY : l’autorisation est donnée par le Oracle Security Service ou lors du login (annuaire
LDAP).
2. définir les privilèges du rôle : GRANT…ON…TO nom_rôle ;
3. accorder le rôle aux utilisateurs : GRANT nom_rôle TO liste_utilisateurs ;
4. chaque utilisateur doit activer le rôle reçu avant de l’utiliser :
SET ROLE liste_rôle | ALL [EXCEPT nom_rôle];
Les rôles ne figurant pas dans la liste liste_rôle sont désactivés.
ALL les active tous excepté ceux figurant après EXCEPT.
5. Pour désactiver les rôles : SET ROLE liste_rôle NONE ;
Le créateur d’un rôle peut le supprimer ainsi que tous les privilèges accordés aux utilisateurs qui le
possèdent par : DROP ROLE nom_rôle ;
Rôles prédéfinis :
Trois rôles sont définis pour compatibilité avec les versions d’Oracle précédentes, mais Oracle
recommande à l’administrateur d’une base de définir ses propres rôles. Il s’agit de :
CONNECT : permet à l’utilisateur qui le possède de se connecter et de créer certains objets (voir
ci-dessous).
RESSOURCE : permet à l’utilisateur qui le possède de créer des procédures, des triggers, des
types, …(voir ci-dessous).
DBA : nécessaire à l’administration de la base, comporte plusieurs rôles et de nombreux privilèges.
Des rôles permettent d’utiliser les outils EXPORT, IMPORT.
EXP_FULL_DATABASE : rôle pour l’exportation des données d’une base entière.
IMP_FULL_DATABASE : rôle pour l’importation des données d’une base entière.
Des rôles sont définis pour accéder aux vues et packages du dictionnaire des données exporté :
DELETE_CATALOG_ROLE
EXECUTE_CATALOG_ROLE
SELECT_CATALOG_ROLE
Le tableau ci-dessous définit les privilèges de certains rôles.
DES2000 est un rôle qui contient tous les privilèges nécessaires à un utilisateur de
DESIGNER_2000.
769802668
Page 7
sur 19
PRIVILEGE
COMMENTAIRE
Ce rôle permet de
ALTER SESSION
CREATE CLUSTER
CREATE DATABASE LINK
CREATE SEQUENCE
CREATE SESSION
se connecter
CREATE SYNONYM
CREATE TABLE
CREATE VIEW
CREATE PROCEDURE
CREATE ROLE
CREATE SNAPSHOT
CREATE TRIGGER
CREATE TYPE
DROP ANY SYNONYM
UNLIMITED TABLESPACE
SELECT ANY TABLE
accéder aux tables DBA_…
DES2000
*
*
*
*
*
ANY
*
*
*
*
*
*
ROLE
CONNECT
*
*
*
*
*
*
*
*
RESSOURCE
*
*
*
*
*
*
*
*
Tables système permettant de connaître les privilèges et rôles
Des vues sur des tables du dictionnaire des données de la BD (appelées tables système) sont
accessibles par SQL et permettent de connaître les privilèges et rôles existants.
NOM de la table
user_tab_privs
user_sys_privs
user_role_privs
all_tab_privs
dba_roles
dba_sys_privs
session_privs
session_roles
769802668
Contenu
Droits sur des objets que l’utilisateur possède, ou qu’il a reçu le droit d’accorder.
Privilèges système accordés à l’utilisateur courant.
Rôles accordés à l’utilisateur courant
Droits sur des objets pour lesquels l’utilisateur est celui qui a accordé les droits (GRANTOR),
celui qui les a reçu (GRANTEE), le propriétaire (GRANTOR) ou un rôle activé, ou droits
reçus par PUBLIC
Tous les rôles de la base.
Privilèges système accordés aux utilisateurs et aux rôles.
Privilèges système dont dispose l’utilisateur connecté
Rôles activés par l’utilisateur connecté.
Page 8
sur 19
III. VUES
1. Définition
Une vue est une table dérivée de tables permanentes, non stockée physiquement dans la base de
données comme une table ordinaire. Une vue est une table virtuelle dynamique et en quelque sorte
une fenêtre sur la base.
La manière de stocker et d’utiliser une vue dépend du SGBD. Par exemple, dans Ingres, seule la
définition de la vue est stockée dans les tables système de la base. Chaque fois qu’un ordre SQL fait
appel a une vue, SQL remplace la vue par sa définition, obtenant un ordre SQL ne contenant plus
que des tables. Ingres optimise alors le chemin d’accès aux tables.
Rien ne permet à un utilisateur de faire la différence entre une table et une vue, sauf en ce qui
concerne les commandes de mise à jour (voir paragraphes 4 et 5).
2. Syntaxe
Syntaxe création
CREATE [OR REPLACE] VIEW nom_vue [(liste-colonnes)]
AS SELECT …
FROM liste-tables-vues
tables ou vues qu’on appelle tables de base de la vue
[WHERE condition]
[WITH [CASCADE | LOCAL] CHECK OPTION]
concerne les mises à jour voir IV
Syntaxe suppression
DROP VIEW nom_vue
Droits nécessaires pour créer une vue
Le créateur de la vue doit avoir le privilège CREATE VIEW (ou CREATE ANY VIEW pour créer
une vue dans un autre schéma que le sien). Il doit avoir reçu directement (sans passer par un rôle)
les droits SELECT, INSERT, DELETE, UPDATE sur chacune des tables de base de la vue. Ces
droits doivent avoir été acquis avec l’option WITH GRANT OPTION pour que le créateur de la vue
puisse accorder des droits dessus à d’autres utilisateurs
Exemple 1. Vue permettant d’avoir la note finale de chaque étudiant dans chaque cours avec le
nom du professeur.
CREATE VIEW BULLETIN (NUM, CODE, ENS, NOTE, BONUS, TOTAL)
AS SELECT NUM, C.CODE, ENS, NOTE, BONUS, NOTE + BONUS
FROM RESUBIS, COURS C
WHERE RESUBIS.CODE = C.CODE
L’ordre SELECT * FROM BULLETIN donne alors
NUM
2140
769802668
CODE
ALGO3
ENS
Korth
NOTE
12
BONUS
2
TOTAL
14
Page 9
sur 19
1128
1128
2140
3213
3512
ALGO3
BD
BD
ALGO3
ALGO1
Korth
Korth
Korth
Korth
Ullman
15
9
11
15
7
1
0
3
2
1
16
9
14
17
8
3. Intérêt
 Augmenter l’indépendance logique
Cela permet de faire évoluer le schéma de la base de données sans avoir à modifier les programmes
d’application. Nous allons illustrer ceci sur un exemple :
Exemple 2. Supposons que l’on dispose au départ de la base de schéma
ETUDIANT = (NUM, NOM, ADR)
INSCRIT0 = (NUM, CODE, ENS, DATE)
RESUBIS0 = (NUM, CODE, ENS, BONUS, NOTE)
Et de la vue qui permet d’éditer les résultats, définie par
CREATE VIEW BULLETIN (NUM, CODE, ENS, NOTE, BONUS, TOTAL)
AS SELECT NUM, CODE, ENS, NOTE, BONUS, NOTE + BONUS
FROM RESUBIS0
Puis on décide, du fait qu’un cours n’est assuré que par un seul professeur, de modifier les relations
pour “normaliser” la base en introduisant la relation COURS :
COURS = (CODE, ENS)
INSCRIT = (NUM, CODE, DATE)
RESU
= (NUM, CODE, NOTE)
Il suffit alors de changer la définition de la vue ( voir l’exemple 1) en gardant les mêmes noms de
colonnes pour ne pas avoir à modifier les programmes d’application.

Préserver la confidentialité
Exemple 3. Chaque étudiant a le droit de consulter sur ordinateur les notes, mais pas de savoir qui
les a obtenues lorsqu’elles sont < 10.
CREATE VIEW BELLESNOTES (NOM, NUM, CODE, NOTE)
AS SELECT E.NOM, R.NUM, R.CODE, R.NOTE
FROM RESU R, ETUDIANT E
WHERE R.NUM = E.NUM AND R.NOTE >=10
CREATE VIEW SALESNOTES (NOM, NUM, CODE, NOTE)
AS SELECT E.NOM, R.NUM, R.CODE
FROM RESU R, ETUDIANT E
WHERE R.NUM = E.NUM AND R.NOTE <10
769802668
Page 10
sur 19
CREATE VIEW VOIRNOTES (NOM, NUM, CODE, NOTE)
AS SELECT * FROM BELLESNOTES
UNION
SELECT * FROM SALESNOTES
Il faudra ensuite donner le droit de lire la vue VOIRNOTES.
GRANT SELECT ON VOIRNOTES TO PUBLIC
Exemple 4. Chaque étudiant n’a le droit de consulter sur ordinateur que les notes qui le concernent.
On crée une vue ne donnant que les notes de l’utilisateur connecté et on accorde à tous les
utilisateurs le droit de consulter la vue.
CREATE VIEW MESNOTES
AS SELECT E.NOM, RESU.NUM, RESU.CODE, RESU.NOTE
FROM RESU, ETUDIANT E
WHERE RESU.NUM = E.NUM AND E.NOM = USER
(la variable USER sera remplacée par le nom d’utilisateur Oracle)
GRANT SELECT ON MESNOTES TO PUBLIC

Simplifier le travail de l’utilisateur
Une vue peut éviter d’avoir à répéter certaines opérations, comme écrire des jointures (par exemple,
dans le cas où une jointure est nécessaire pour connaître un libellé correspondant à un code), faire
des calculs (voir exemple II.1), …
Exemple 5. Pour connaître le nom de l’étudiant avec ses notes sans avoir à écrire la jointure des
tables RESU et ETUDIANT.
CREATE VIEW RESU_EN_CLAIR
AS SELECT E.NOM, R.NUM, R.CODE, R.NOTE
FROM RESU R, ETUDIANT E
WHERE R.NUM = E.NUM
Laquelle la modification est demandée, est vérifiée.
4. Mise à jour SQL92
Toutes les vues ne permettent pas des mise à jour. Le principe est assez simple. On ne peut faire une
mise à jour au travers d’une vue que si une ligne de la vue ne correspond qu’à une unique ligne
d’une autre table de base, qui, elle, sera physiquement mise à jour.
Exemple 6. vue non modifiable
CREATE VIEW TOTALNOTES (NUM, TOTAL_ETUDIANT)
AS SELECT NUM, SUM (NOTE)
FROM RESU
GROUP BY NUM
769802668
Page 11
sur 19
Ceci n’est pas une vue permettant une mise à jour : on ne peut répercuter sur RESU une
modification de SUM (NOTE).
De là découlent des règles (un peu plus restrictive que de bon sens) données par le norme SQL (92
comme 89) pour définir une vue dite modifiable, c’est à dire qui permet d’exécuter des ordres
UPDATE, DELETE, INSERT. Une vue est modifiable si sa définition vérifie les propriétés
suivantes.
 Pas de DISTINCT
 La clause FROM n’est suivie que d’un seul nom, qui est celui soit d’une table, soit d’une
vue modifiable (appelée TAB dans ce qui suit).
 La liste derrière SELECT ne comporte que des colonnes de TAB et une seule fois chaque.
 La clause WHERE peut contenir un bloc SFW imbriqué, mais celui-ci ne doit pas faire
référence à TAB
 Pas de clause GROUP BY ou HAVING
Exemple 7. vues modifiables.
CREATE VIEW BASSESNOTES (NUM, CODE, NOTE)
AS SELECT NUM, CODE, NOTE
FROM RESU
WHERE NOTE < 10
CREATE VIEW NOTES_ANC (NUM, CODE, NOTE)
AS SELECT NUM, CODE, NOTE
FROM RESU
WHERE NUM IN (SELECT NUM FROM INSCRIT)
Option WITH[CASCADE | LOCAL] CHECK OPTION
Cette option permet d’empêcher l’utilisateur des mises à jour non conformes à la définition de la
vue.
Exemple 8. modification contraire à la définition de la vue.
Supposons que la vue BASSESNOTES de l’exemple IV.2 ait été définie.
Alors l’ordre
UPDATE BASSESNOTES
SET NOTE = NOTE + 2
Pose problème en transformant le n-uplet (1128, BD, 9) en (1128, BD, 11) avec une note > 10
contredisant la définition de la vue.
Si la vue a été définie avec l’option WITH CHECK OPTION, un tel cas ne se produira pas. Sans
l’option WITH CHECK OPTION, la table RESU sera mise à jour.
CASCADE est l’option par défaut. Elle permet de répercuter les modifications sur les tables
dépendant de la vue (puisqu’une vue peut être définie à partir d’une autre vue, elle-même définie à
partir de …). Dans le cas de LOCAL, seule la clause WHERE de la vue sur laquelle la modification
est demandée, est vérifiée.
769802668
Page 12
sur 19
5. Mise à jour Oracle
Dans le cas d’Oracle, le principe reste le même : il faut que la mise à jour de la vue corresponde à la
mise à jour d’une seule table.
Mais Oracle permet d’avoir une vue définie sur une jointure (plusieurs tables derrière la clause
FROM). Il définit la notion de table « key-preserved » dans une jointure : table dont la clé primaire
est aussi une clé (au sens UNIQUE, NOT NULL) de la jointure.
Pour que la mise à jour sur une vue définie par jointure soit possible, il faut au moins (voir la
documentation Oracle pour tous les cas) que, suivant les cas d’ordre donnés ci-dessous, les
conditions suivantes soient vérifiées, en plus de la condition « une seule table concernée par la mise
à jour ».



UPDATE : toutes les colonnes modifiées doivent appartenir à une table « key-preserved ».
DELETE : la jointure ne doit contenir qu’une seule table « key-preserved » (la même table peut
figurer plusieurs fois dans la jointure sauf si la vue comporte la clause CHECK OPTION).
INSERT : toutes les colonnes concernées par l’insertion appartiennent à une table « keypreserved » et la vue ne comporte pas de CHECK OPTION.
Pour savoir quelles colonnes d’une vue obtenue par jointure sont modifiables, il faut consulter la
table système USER_UPDATABLE_COLUMNS (COLUMN_NAME, UPD : YES/NO)
769802668
Page 13
sur 19
IV. DIVERS
1. Synonymes
1.1. Définition et intérêt
Oracle permet de renommer les objets d’un schéma en créant des noms additionnels appelés
synonymes. Ils sont utiles pour :
 Faciliter l’écriture de requêtes sans avoir besoin de spécifier la localisation et les propriétaires
des objets.
 Aider à la mise au point des applications : pendant la phase de test, utiliser un synonyme
pointant sur une table de test et, après la mise au point, faire pointer le synonyme sur la vraie
table de l’application.
Objets concernés : table, vue, séquence, fonction, procédure, package, snapshot, synonyme.
Il existe deux types de synonymes, privé et public, leur création étant soumise à des conditions :
 Synonymes privé : l’utilisateur peut créer des synonymes privés dans son schéma ou, s’il
possède le privilège CREATE ANY SYNONYM, dans un autre schéma. Le nom du synonyme
privé doit être différent du nom des autres objets du même schéma.
 Synonyme public : accessible par tout utilisateur. Quand un conflit de nom existe entre un
synonyme public et privé, Oracle donne la priorité à l’objet local.
1.2. Syntaxe
CREATE [PUBLIC] SYNONYM nom_synonyme
FOR nom_objet
DROP [PUBLIC] SYNONYM nom_synonyme
1.3. Droits nécessaires
Pour créer un synonyme, il faut avoir les privilèges système :
CREATE SYNONYM : pour créer un synonyme privé dans son propre schéma.
CREATE ANY SYNONYM : pour créer un synonyme privé dans un autre schéma.
CREATE PUBLIC SYNONYM : pour créer un synonyme public.
Les droits sur un synonyme sont les mêmes que ceux sur l’objet sur les quels ils pointe. Donner un
droit sur un synonyme est équivalent à donner un droit sur cet objet. Inversement, donner un droit
sur l’objet accorde le même droit sur tous ses synonymes. L’utilisateur qui a reçu un tel droit peut
utiliser aussi bien le nom du synonyme que celui de l’objet dans ses requêtes autorisées.
1.4.Renommage
Il ne faut pas confondre la définition d’un synonyme avec le fait de renommer un objet (table, vue,
séquence, synonyme privé, mais pas les colonnes des tables).
769802668
Page 14
sur 19
RENAME ancien_objet TO nouveau
Dans le cas de renommage d’un objet, les contraintes, index et privilèges existant dessus sont
transférés automatiquement, alors que les vues, procédures, synonymes qui se réfèrent à l’ancien
objet deviennent invalides.
2. Pseudo-colonnes
Une pseudocolonne se comporte comme une colonne de table, mais n’est pas stockée dans une
table. On peut la lire, mais pas mettre à jour, ni détruire ses valeurs.
Exemples :
CURRVAL and NEXTVAL(voir ci-dessous).
LEVEL : utile pour les requêtes hiérarchiques.
ROWID : contient l’adresse de chaque ligne de la table (ne peut servir de clé primaire car mise à
jour)
ROWNUM : nombre qui indique l’ordre d’une ligne retournée par un requête (permet par exemple
de limiter le nombre de lignes retournées).
3. Table DUAL
3.1. Définition
C’est une table :
 qui est créée automatiquement avec le dictionnaire.
 qui est possédée par l’utilisateur SYS, mais accessible à tous.
 qui ne contient qu’une colonne DUMMY de type VARCHAR2(1) et une valeur X.
3.2. Utilisation
On peut obtenir la valeur d’une pseudocolonne (exemple : date système), d’une constante ou d’une
expression à partir de n’importe quelle table :
SELECT SYSDATE FROM USER_TABLES
renvoie autant de fois SYSDATE que la table USER_TABLES contient de
lignes !
SELECT UNIQUE SYSDATE FROM USER_TABLES donne une seule fois la date système mais accède à une table qui
est volummineuse, au contraire de la table DUAL.
SELECT SYSDATE FROM DUAL
4. Séquences
4.1 Définition et intérêt
Lorsque la création de nouvelles entités dans la même table est assurée par plusieurs utilisateurs
avec création d’un identifiant numérique, des problèmes de concurrences d’accès pour connaître la
valeur de l’identifiant peuvent se poser. Pour résoudre ce problème, Oracle a implémenté des objets
769802668
Page 15
sur 19
appelés séquences qui génèrent automatiquement des valeurs séquentielles uniques, différentes pour
chaque utilisateur. Ces valeurs servent souvent de clé primaire ou de clé secondaire.
4.2 Syntaxe
CREATE SEQUENCE [schema.]nom_sequence
liste_paramètres
Exemple :
CREATE SEQUENCE ma_suite
INCREMENT by 10
Pour utiliser les valeurs de la séquence, on dispose des pseudocolonnes :
CURRVAL valeur courante de la séquence
NEXTVAL incrémente de 1 et donne la valeur suivante
SELECT ma_suite.currval from dual
INSERT INTO etudiant
VALUES (ma_suite.nextval, ‘LEWIS’, ‘CLERK’, SYSDATE) ;
769802668
Page 16
sur 19
ANNEXE
Base exemple ETUDES
ETUDIANT = (NUM, NOM, ADR)
COURS = (CODE, ENS)
INSCRIT = (NUM,CODE, DATE)
RESU = (NUM, CODE, NOTE)
PROF = (ENS, ADR)
RESUBIS = (NUM, CODE, NOTE, BONUS)
La relation INSCRIT sert à mémoriser les inscriptions antérieures des étudiants. La relation RESU
donne les notes des étudiants aux cours qu’ils suivent actuellement.
La projection de RESUBIS sur les attributs NUM, CODE, NOTE est égale à la relation RESU et
BONUS représente le bonus attribué pour un cours.
ETUDIANT
NUM
2140
1128
3213
3512
INSCRIT
NUM
2140
2140
1128
3213
3213
RESUBIS
NUM
2140
1128
1128
2140
3213
3512
769802668
NOM
Dupond
Durand
Dubois
Martin
CODE
BD
ALGO1
ALGO1
BD
ALGO3
CODE
ALGO3
ALGO3
BD
BD
ALGO3
ALGO1
COURS
CODE
BD
ALGO1
ALGO3
ADR
Paris
Orsay
Massy
Orsay
RESU
NUM
2140
1128
1128
2140
3213
3512
DATE
93
94
94
93
94
NOTE
12
15
9
11
15
7
BONUS
2
1
0
3
2
1
ENS
Korth
Ullman
Korth
CODE
ALGO3
ALGO3
BD
BD
ALGO3
ALGO1
NOTE
12
15
9
11
15
7
PROF
ENS
Korth
Ullman
Delmal
ADR
Paris
Orsay
Liège
Page 17
sur 19
V.
VOCABULAIRE ORACLE
Un cluster est un regroupement physique de plusieurs tables autour d’une ou plusieurs colonnes
afin d’augmenter les performances (par exemple en cas d’opérations de jointures).
Database link : objet de la base locale qui permet l’accès à une base distante (ORACLE ou non) ou
de monter une seconde base en lecture seulement.
Fonction : ensemble nommé de commandes PL/SQL qui retourne une valeur à l’appelant,
contrairement à une procédure.
Instance Oracle : ensemble associé à une base de donnée Oracle et formé d’un espace mémoire
appelé System Global Area (SGA) alloué par Oracle et de processus Oracle.
Procédure : ensemble nommé de commandes PL/SQL stockées dans la base.
Schéma : ensemble d’objets accessibles à un utilisateur (tables, vues, index, séquences, synonymes,
packages,…) ; à chaque utilisateur est associé un schéma. A chaque schéma correspond un espace
logique de stockage dans un ou plusieurs tablespaces de la base de données.
Session : connexion spécifique d’un utilisateur à une instance Oracle via un processus utilisateur.
Chaque fois qu’un utilisateur fait tourner un programme applicatif (par exemple un programme
ProC) ou un outil Oracle (tel SQL*Plus), Oracle crée un processus utilisateur pour faire tourner
l’application.
Par exemple, si un utilisateur se connecte à SQL*Plus, il indique username/password et une session
est établie pour cet utilisateur. Elle dure du début de la connexion à la déconnexion. Mais il peut
exister simultanément plusieurs sessions pour le même username/password.
Séquence : suite de valeurs générés et gérée automatiquement par Oracle.
Snapshot (cliché) : une table qui contient le résultat d’une requête définie sur une ou plusieurs
tables ou vues localisées dans une base distante. Notion qui n’est utilisée que dans un contexte de
base de données répartie.
Cette notion permet de maintenir des copies des données de la base distante sur un nœud local (en
lecture seulement).
Tablespace : une allocation d’espace disque dans la base pour contenir des objets.
Trigger (déclencheur) : procédure stockée dans la base et associé à un événement pouvant
intervenir sur une table. Cette procédure s’exécute quand une commande SQL de mise à jour
(DELETE, INSERT, UPDATE) affecte la table associée au déclencheur.
769802668
Page 18
sur 19
VI. REFERENCES
Documentation ORACLE
Oracle8 SQL reference
/oracle/server.804/a58225
Oracle8 Application Developper’s Guide
/oracle/server.804/a58241.htm
PL/SQL User’s Guide and Reference
/oracle/server.804/a58236
Pro*C/C++ Precompiler Programmer’s Guide
Pour l’inclusion de PL/SQL dans un programme :
/oracle/server.804/a58233/plsql.htm
Pour connaître les options de précompilation :
/oracle/server.804/a58233/precomp.htm#2111
OUVRAGES PUBLIES
SQL2, Application à ORACLE, ACCESS et RDB. P. Delmal
De Boeck Université, 1998, 2ème édition.
Oracle 7, A Abdellatif, M. Limame, A. Zeroual,
Eyrolles, 1998, 7ème tirage.
Oracle 8, Utilisation et administration. Guide de Formation. Roger Chapuis,
Editions Dunes & laser, 1998
Oracle 8, Développement de bases de données, David Lockman,
Simon & Schuster Macmillan, 1997
769802668
Page 19
sur 19