Plan du site  
français  English
pixel
pixel

Articles - Étudiants SUPINFO

Chapitre 07 - Créer les objets du schéma

Objets et types de données

Objets

Une base de données Oracle peut contenir de multiples structures de données. Chaque structure devrait être définie dans le design de la base de données pour qu’elle puisse être créée pendant la phase de construction de la base de données.

  • Table : Stocke les données

  • Vue : Sous-ensemble de données d’une ou plusieurs tables

  • Séquence : Génère des valeurs numériques

  • Index : Améliore les performances de certaines requêtes

  • Synonyme : Donne un nom alternatif aux objets

Structure des tables Oracle

  • Les tables peuvent êtres créées à n’importe quel moment, même lorsque des utilisateurs utilisent la base de données.

  • Vous n’avez pas besoin de spécifier la taille de la table. Cette est taille est limitée par l’espace alloué à la base de données. Il est tout de même important d’estimer combien de place prendra la table au cours du temps.

  • La structure d’une table peut être modifiée dynamiquement.

[Note]

Il existe d’autres objets dans une base de données, mais ils ne sont pas abordés dans ce cours.

Types de données

Lorsque vous identifiez une colonne pour une table, vous avez besoin de fournir un type de données pour la colonne. Il y a plusieurs types de données disponibles :

Type de données Description
VARCHAR2(size) Chaîne de caractères de longueur variable (Une taille maximale doit être spécifiée : la taille minimale est 1; la taille maximale est 4000).
CHAR[(size)] Chaîne de caractères de taille fixe (la taille minimale (par défaut) est 1; la taille maximal est 2000).
NUMBER[(p,s)] Nombre ayant une précision p et un pas s (La précision est le nombre total de chiffres décimaux, et le pas est le nombre de chiffres à droite du point de séparation des décimales ; la précision peut varier de 1 à 38, et le pas de -84 à 127.)
DATE Date et heure de la plus proche seconde du 1er Janvier 4712 av. JC au 31 Décembre 9999 apr. JC.
LONG Chaîne de caractère de longueur variable (jusqu’à 2 Go).
CLOB Chaîne de caractères (jusqu’à 4 Go).
RAW(size) Données binaires brutes de longueur size (une taille maximale peut être spécifiée, la limite est 2000).
LONG RAW Données binaires brutes de longueur variable (jusqu’à 2 Go).
BLOB Données binaires (jusqu’à 4 Go).
BFILE Données binaires stockées dans un fichier de données externe (jusqu’à 4 Go).
ROWID Un nombre encodé sur 64-bits représentant l’adresse unique d’une ligne dans une table.

Règles :

  • Une colonne LONG n’est pas copiée lorsqu’une table est créée en utilisant une sous-requête.

  • Une colonne LONG ne peut être incluse dans une clause GROUP BY ou ORDER BY.

  • Seulement une colonne de type LONG peut être utilisée par table.

  • Aucune contrainte ne peut être définie sur une colonne de type LONG.

  • Il est conseillé d’utiliser une colonne de type CLOB plutôt que LONG.

Autres types de données Datetime

TIMESTAMP Permet de stocker une date avec des fractions de seconde. Il y a plusieurs variations de ce type de données.
INTERVAL YEAR TO MONTH Permet de stocker un intervalle d’années et de mois. Il est utilisé pour représenter une différence entre deux datetime dont seulement le mois et l’année sont pris en compte.
INTERVAL DAY TO SECOND Permet de stocker un intervalle de jours, d’heures, de minutes et de secondes. Il est utilisé pour représenter une différence précise entre deux datetime.
[Note]

Ces types de données sont disponibles à partir de la version Oracle9i.

Type de données TIMESTAMP

Le type de données TIMESTAMP est une extension du type DATE. Il stocke l’année, le mois, le jour, l’heure, les minutes et les secondes du type de données DATE. Ce type de données est utilisé pour stocker les valeurs temporelles de manière précise.

TIMESTAMP[(fractional_seconds_precision)]

Le paramètre fractional_seconds_precision spécifie optionnellement le nombre de chiffres dans la partie fractionnelle du champ SECOND et peut varier de 0 à 9 (fractions de seconde). La valeur par défaut est 6.

Exemple :

CREATE TABLE new_employees
    (employee_id NUMBER,
     first_name VARCHAR2(15),
     last_name VARCHAR2(15),
     start_date TIMESTAMP(7),
     ...);

Supposons que deux lignes soient insérées dans la table NEW_EMPLOYEES. L’affichage montre les différences. (Un type de données DATE s’affiche par défaut sous la forme DD-MON-RR) :

SELECT start_date
FROM new_employees;

17-JUN-03 12.00.00.000000 AM
21-SEP-03 12.00.00.000000 AM

Type de données TIMESTAMP WITH TIME ZONE

TIMESTAMP WITH TIME ZONE est une variante du TIMESTAMP qui inclut le décalage horaire. Le décalage horaire est la différence (en heures et minutes) entre l’heure locale et UTC (Universal Time Coordinate, connue sous le nom de Greenwich Mean Time). Ce type de données est utilisé pour collecter et évaluer des informations temporelles en fonction des régions géographiques.

TIMESTAMP[(fractional_seconds_precision)]
WITH TIME ZONE

Par exemple,

TIMESTAMP '2003-04-15 8:00:00 -8:00'

est équivalent à

TIMESTAMP '2003-04-15 11:00:00 -5:00'

C’est à dire que 8:00 a.m. PST (Pacific Standard Time) est équivalente à 11:00 a.m. EST (Eastern Standard Time).

Cela peut également être spécifié comme ceci :

TIMESTAMP '2003-04-15 8:00:00 US/Pacific'

Type de données TIMESTAMP WITH LOCAL TIME ZONE

TIMESTAMP WITH LOCAL TIME ZONE est une autre variante du TIMESTAMP qui inclut le décalage horaire. Elle diffère du type TIMESTAMP WITH TIME ZONE dans le fait que les données stockées sont normalisées au fuseau horaire de la base de données, et que le décalage horaire n’est pas stocké comme une partie des données de colonne. Lorsque les utilisateurs récupèrent les données, elles sont retournées dans le fuseau horaire de la session. Le décalage horaire est la différence (en heures et minutes) entre l’heure locale et UTC.

TIMESTAMP[(fractional_seconds_precision)]
WITH LOCAL TIME ZONE

Contrairement au type de données TIMESTAMP WITH TIME ZONE, vous pouvez spécifier les colonnes du type TIMESTAMP WITH LOCAL TIME ZONE comme étant une clé primaire ou une clé unique :

CREATE TABLE time_example
(order_date TIMESTAMP WITH LOCAL TIME ZONE);

INSERT INTO time_example VALUES('15-JAN-04 09:34:28 AM');

SELECT *
FROM   time_example;

ORDER_DATE
----------------------------
15-JAN-04 09.34.28.000000 AM

Le type de données TIMESTAMP WITH LOCAL TIME ZONE est approprié pour des applications deux-tiers dans lesquelles vous souhaitez afficher la date et l’heure en utilisant le fuseau horaire du système de client.

Types de données Datetime

INTERVAL YEAR TO MONTH stocke une période du temps utilisant les champs YEAR et MONTH.

INTERVAL YEAR [(year_precision)] TO MONTH

Utilisez INTERVAL YEAR TO MONTH pour représenter la différence entre deux valeurs de datetime où les seules portions significatives sont l’année et le mois. Par exemple, vous devez utiliser cette valeur pour mettre un place une alerte se déclenchant 120 mois dans le futur ou pour vérifier si 6 mois sont écoulés depuis une table particulière.

Dans la syntaxe :

  • year_precision : est le nombre de chiffres du champ YEAR. La valeur par défaut de year_precision est 2.

Exemples :

  • INTERVAL '123-2' YEAR(3) TO MONTH : Indique un intervalle de 123 ans et 2 mois

  • INTERVAL '123' YEAR(3) : Indique un intervalle de 123 ans et 0 mois

  • INTERVAL '300' MONTH(3) : Indique un intervalle de 300 mois

  • INTERVAL '123' YEAR : Retourne une erreur car la précision par défaut est 2, or 123 a 3 chiffres

CREATE TABLE time_example2
       (loan_duration INTERVAL YEAR (3) TO MONTH);

INSERT INTO time_example2 (loan_duration)
VALUES (INTERVAL '120' MONTH(3));

SELECT TO_CHAR( sysdate+loan_duration, 'dd-mon-yyyy')
FROM time_example2;  --assuming today’s date is 26-Sep-2001

INTERVAL DAY TO SECOND stocke une période de temps en jours, en heures, en minutes et en secondes.

INTERVAL DAY [(day_precision)]
TO SECOND [(fractional_seconds_precision)]

Utilisez INTERVAL DAY TO SECOND pour représenter la différence précise entre deux valeurs datetime. Par exemple, vous pouvez utiliser cette valeur pour mettre en place une alerte se déclenchant 36 heures dans le futur ou pour enregistrer le temps entre le début et la fin d’une course. Pour représenter de longue durée, incluant plusieurs années, avec une grande précision, vous pouvez utiliser une valeur avec beaucoup de jours.

Explication de la syntaxe :

  • day_precision est le nombre de chiffres dans le champ DAY de datetime. Les valeurs acceptées sont comprises entre 0 et 9. La valeur par défaut est 2.

  • fractional_seconds_precision est le nombre de chiffres dans la partie des fractions des secondes (SECOND) de datetime. Les valeurs acceptées sont comprises entre 0 et 9. La valeur par défaut est 6.

Exemples :

  • INTERVAL '4 5:12:10.222' DAY TO SECOND(3) : Indique 4 jours, 5 heures, 12 minutes, 10 seconds et 222 fractions de seconde.

  • INTERVAL '180' DAY(3) : Indique 180 jours.

  • INTERVAL '4 5:12' DAY TO MINUTE : Indique 4 jours, 5 heures et 12 minutes.

  • INTERVAL '400 5' DAY(3) TO HOUR : Indique 400 jours et 5 heures.

  • INTERVAL '11:12:10.2222222' HOUR TO SECOND(7) : Indique 11 heures, 12 minutes et 10.2222222 secondes.

Exemple :

CREATE TABLE time_example3
(day_duration INTERVAL DAY (3) TO SECOND);

INSERT INTO time_example3 (day_duration)
VALUES (INTERVAL '180' DAY(3));

SELECT sysdate + day_duration "Half Year"
FROM time_example3;  --assuming today’s date is 26-Sep-2001

Gestion des tables

Règles de nommage

Vous nommez les tables et les colonnes d’une base de données en respectant les règles de nommage standards pour n’importe quel objet d’une base de données Oracle.

  • Les noms des tables et des colonnes doivent commencer par une lettre et avoir une longueur de 1 à 30 caractères.

  • Les noms doivent contenir uniquement les caractères A-Z, a-z, 0-9,_ (underscore), $ et # (caractère légaux, mais leur usage est déconseillé).

  • Deux objets appartenant au même utilisateur de la base de données Oracle, ne peuvent porter le même nom.

  • Les noms ne doivent pas être des mots réservés du serveur Oracle.

Directives de nommage :

Utilisez des noms explicites pour les tables et les autres objets de la base de données.

[Note]

Les noms ne sont pas sensibles à la casse. Par exemple, EMPLOYEES est équivalent à eMPloyees ou eMpLOYEES.

L’ordre CREATE TABLE

CREATE TABLE [schema.]table
(column datatype [DEFAULT expr][, ...]);

Vous pouvez créer des tables pour stocker des données en exécutant la commande SQL CREATE TABLE. Cette commande est une des commandes DDL faisant partie du sous-ensemble des commandes SQL utilisées pour créer, modifier ou supprimer les structures d’une base de données Oracle. Ces requêtes ont un effet immédiat sur la base de données et enregistrent également des informations dans le dictionnaire de données.

Pour créer une table, un utilisateur doit avoir le privilège CREATE TABLE et un espace de stockage dans lequel il pourra créer les objets. L’administrateur de base de données utilise des ordres DCL (Data Control Langage) pour accorder des privilèges aux utilisateurs (les commandes DCL sont expliquées dans un cours ultérieure).

Dans la syntaxe :

  • schema : le nom du propriétaire

  • table : le nom de la table

  • DEFAULT expr : définie une valeur par défaut dans le cas où aucune valeur n’est fournie lors d’une requête INSERT

  • column : le nom de la colonne

  • datatype : la longueur et le type de données de la colonne

Accéder aux tables d’un autre utilisateur

Un schéma est une collection d’objets. Les objets du schéma sont des structures logiques qui font directement référence aux données de la base de données. Les objets du schéma incluent les tables, les vues, les synonymes, les séquences, les procédures stockées, les index, les clusters et les liens de base de données.

Si une table n’appartient pas à l’utilisateur connecté, le nom du propriétaire doit précéder le nom de la table. Par exemple, s’il y a deux schémas USERA et USERB, et que les deux possèdent une table EMPLOYEES, alors si USERA veut accéder à la table appartenant à USERB, il doit préfixer le nom de la table EMPLOYEES par le nom du schéma.

SELECT *
FROM   userb.employees;

Si l’utilisateur USERB veut accéder à la table EMPLOYEES qui appartient à l’utilisateur USERA, il doit préfixer le nom de la table avec le nom du schéma :

SELECT *
FROM   usera.employees;

Option DEFAULT

Lorsque vous créez une table, vous pouvez spécifier une valeur par défaut pour une colonne donnée en utilisant l’option DEFAULT. Cette option empêche la colonne d’avoir des valeurs NULL, si aucune valeur n’est spécifiée pour la dite colonne lors de l’insertion la valeur par défaut est insérée. La valeur par défaut peut être une valeur littérale, une expression ou bien une fonction SQL (comme SYSDATE ou USER). Cette valeur ne peut être le nom d’une autre colonne ou pseudo-colonne (comme NEXTVAL ou CURRVAL). L’expression par défaut doit correspondre au type de données de la colonne.

CREATE TABLE hire_dates
   (id NUMBER(8),
    hire_date DATE DEFAULT SYSDATE);

Table created.
[Note]

CURRVAL et NEXTVAL sont expliquées plus loin.

Création des tables

CREATE TABLE dept
   (deptno NUMBER(2),
    dname VARCHAR2(14),
    loc VARCHAR2(13),
    create_date DATE DEFAULT SYSDATE);

Table created.
DESCRIBE dept

Cet exemple crée la table DEPT, avec 4 colonnes : DEPTNO, DNAME, LOC et CREATE_DATE. La colonne CREATE_DATE a une valeur par défaut. Si aucune valeur n’est fournie par la requête INSERT, la date système sera automatiquement insérée. Vous pouvez vérifier la création de la table en utilisant la commande DESCRIBE. Un ordre COMMIT implicite est effectué parce que la création d’une table est une opération DDL.

ALTER TABLE

Après avoir créé une table, vous pouvez avoir besoin de changer sa structure pour les raisons suivantes :

  • Vous avez oublié une colonne.

  • Votre définition de colonne a besoin d’être modifiée.

  • Vous devez supprimer des colonnes.

Vous pouvez faire cela en utilisant la requête ALTER TABLE.

DROP TABLE

La requête DROP TABLE supprime la définition d’une table Oracle. Quand vous supprimez une table, la base de données perd toutes les données de la table et tous les indexes qui lui sont associés.

Syntaxe :

DROP TABLE table

Dans la syntaxe :

  • table : est le nom de la table.

Règles :

  • Toutes les données sont supprimées de la table.

  • N’importe quel vue ou synonyme restent mais sont invalides.

  • N’importe quelle transaction est validé avec un COMMIT.

  • Seul le propriétaire de la table ou un utilisateur avec le privilège DROP ANY TABLE peut supprimer la table.

[Note]

La requête DROP TABLE, une fois exécutée est irréversible. Le serveur Oracle ne demande pas de confirmation lorsque vous exécutez la commande DROP TABLE. Si la table vous appartient ou si vous avez un privilège de haut niveau, alors la table est immédiatement enlevée. Tout comme les ordres DDL, DROP TABLE est validé la transaction par un COMMIT automatique.

DROP TABLE ... PURGE

Oracle Database 10g offre de nouvelles fonctionnalités intervenants à la suppression des tables. Quand vous supprimez une table, la base de données ne libère pas immédiatement l’espace occupé par la dernière. La base de données renomme la table et la place dans une corbeille. En cas de besoin, la table peut être restaurée avec la requête FLASHBACK TABLE. Si vous ne voulez pas placer la table dans la corbeille et donc libérer l’espace occupé immédiatement, effectuez une requête DROP TABLE avec la clause PURGE.

DROP TABLE dept80 PURGE;

Spécifiez PURGE seulement si vous voulez supprimer la table et libérer l’espace occupé en une étape. Si vous spécifiez PURGE, la base de données ne place pas la table et ses objets dépendants dans une corbeille. L’utilisation de cette clause revient à supprimer la table et à la vider de la corbeille. Cette clause offre aussi une sécurité accrue si vous ne voulez pas que des données sensibles soient visibles dans la corbeille.

[Note]

Vous ne pouvez pas annuler l’action d’une requête DROP TABLE … PURGE. Cette fonctionnalité n’est pas disponible dans les versions antérieures.

La requête FLASHBACK TABLE

Fonctionnalité de réparation libre-service

Oracle Database 10g fournit de nouvelle requête DDL FLASHBACK TABLE permettant de restaurer l’état d’une table à un moment antérieur en cas d’une modification ou d’une suppression indésirable. La requête FLASHBACK TABLE est un outil de réparation libre service qui restore les données d’une table et les attributs associés comme les index ou les vues. Cette opération est effectuée lorsque la base de données est ouverte en annulant un sous-ensemble de changements d’une table donnée. Comparé aux mécanismes de restauration traditionnels, cette fonctionnalité offre une facilité d’utilisation, une disponibilité accrue et une restauration rapide. La fonctionnalité flashback table ne concerne pas les corruptions provenant du disque dur défectueux.

Syntaxe :

FLASHBACK TABLE[schema.]table[,
[ schema.]table ]...
TO { TIMESTAMP | SCN | BEFORE DROP } expr
[ { ENABLE | DISABLE } TRIGGERS ];

Vous pouvez exécuter une opération flashback table sur une ou plusieurs tables, même si les tables se situent dans les schémas différents. Vous spécifiez un instant jusqu’auquel vous voulez restaurer en précisant un timestamp valide. Par défaut, tous les déclencheurs (triggers) sont désactivés pour les tables concernées. Vous pouvez outrepasser ce comportement par défaut en spécifiant la clause ENABLE TRIGGERS.

L’exemple suivant restaure la table EMP2 après qu’elle soit supprimée avec la requête DROP.

DROP TABLE emp2;
Table dropped
SELECT original_name, operation, droptime,
FROM recyclebin;
FLASHBACK TABLE emp2 TO BEFORE DROP;
Flashback complete

La corbeille est une table de dictionnaire de données qui contient les informations sur les objets supprimés. Les tables et les objets associés, comme les index, les contraintes et les tables imbriquées occupent de l’espace qui est souvent limité par un quota. Vous devez vider la corbeille lorsque vous atteignez le quota définit.

Chaque utilisateur peut être considéré comme un propriétaire de la corbeille car seuls les objets lui appartenant sont visibles dans la corbeille (RECYCLEBIN), à l’exception des utilisateurs ayant le privilège SYSDBA. Un utilisateur peut voir ses objets dans la corbeille en exécutant la requête suivante :

SELECT * FROM RECYCLEBIN;

Quand vous supprimez un utilisateur, tous les objets lui appartenant ne sont pas placés dans la corbeille et les objets présents dans la corbeille sont vidés. Vous pouvez vider la corbeille en effectuant la requête suivante :

PURGE RECYCLEBIN;

Créations avancées

Il est possible de créer une table en utilisant la clause AS avec une sous-requête ce qui permet de créer une table et d’insérer des lignes retournées par la sous-requête.

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

Dans la syntaxe :

  • table : est le nom de la table.

  • column : est le nom de la colonne, valeur par défaut, et contrainte d’intégrité

  • subquery : est la requête SELECT qui définit les lignes à insérer dans la nouvelle table

Règles :

  • La table est créée avec les noms de colonnes spécifiées, les lignes retrouvées par la requête SELECT sont insérées sans la table.

  • La définition de la colonne peut contenir seulement le nom de la colonne et la valeur par défaut.

  • Si les spécifications de colonnes sont données, le nombre de colonnes doit être égal au nombre de colonnes retournées par la sous-requête.

  • Si aucune spécification de colonne n’est donnée, les noms de colonnes de la table sont les mêmes que les noms de colonnes de la sous-requête.

  • Les types de données des colonnes et les contraintes NOT NULL sont passés à la nouvelle table. Les autres contraintes ne sont pas appliquées à la nouvelle table. Cependant, vous pouvez ajouter des contraintes dans la définition de colonne.

Dans l’exemple suivant on crée une table appelée DEPT80 qui contient des détails sur les employés travaillant dans le département 80. Remarquez que les données de la table DEPT80 proviennent de la table EMPLOYEES.

Vous pouvez vérifier l’existence de la table dans la base de donnée et vérifiez les définitions des colonnes en utilisant la commande iSQL*Plus DESCRIBE.

Soyez sur de donner un alias de colonne aux expressions dans la sous-requête. L’expression SALARY*12 a pour alias ANNSAL. Sans l’alias, l’erreur suivante est générée :

ERROR at line 3:
ORA-00998 : must name this expression with a column alias.
CREATE TABLE dept80
AS
 SELECT employee_id, last_name,
 salary*12 ANNSAL,
 hire_date
 FROM employees
 WHERE department_id = 80;
Table created.
DESCRIBE dept80

Les contraintes : Présentation

Inclure les contraintes

Le serveur Oracle utilise les contraintes pour empêcher les entrées invalides des données dans les tables. Vous pouvez utiliser les contraintes dans les cas suivants :

  • Mettre en place les règles pour les données dans la table lorsqu’une ligne est insérée, mise à jour ou supprimée. La contrainte doit être satisfaite pour que l’opération soit réussie.

  • Prévenir la suppression d’une table s’il y a des dépendances avec les autres tables.

  • Fournir des règles pour les outils Oracle, comme Oracle Developer.

Contraintes d’intégrité des données :

Contrainte Description
NOT NULL Spécifie que la colonne ne peut contenir de valeurs NULL
UNIQUE Spécifie une colonne ou une combinaison de colonnes dont les valeurs doivent être uniques pour chaque ligne de la table
PRIMARY KEY Identifie de façon unique chaque ligne de la table
FOREIGN KEY Établit une relation dite clé étrangère entre la colonne et une colonne de la table référencée
CHECK Spécifie une condition qui doit être validée

Toutes les contraintes sont stockées dans le dictionnaire de données. Les contraintes sont faciles à référencer si vous leur donnez un nom explicite. Les noms de contrainte doivent respecter les règles de nommage d’objet standards. Si vous ne nommez pas votre contrainte, le serveur Oracle génère un nom avec le format SYS_Cn, où le n représente un entier pour que la contrainte ait un nom unique. Les contraintes peuvent être définies lors de la création de la table ou bien après.

Définir une contrainte

Vous pouvez créer une contrainte aussi bien au niveau de la colonne qu’au niveau de la table.

CREATE TABLE [schema.]table
(column datatype [DEFAULT expr]
[column_constraint],
...
[table_constraint][,...]);

Les contraintes définies au niveau de la colonne sont incluses dans la définition de la colonne.

column [CONSTRAINT constraint_name] constraint_type,

Les contraintes définies au niveau de la table sont incluses à la fin de la définition de la table. Elles agissent sur une ou plusieurs colonnes. Les colonnes concernées sont incluses entre parenthèses.

column,...
[CONSTRAINT constraint_name] constraint_type
(column, ...),

Les contraintes NOT NULL doivent être définies au niveau de la colonne. Les contraintes qui s’appliquent à plus d’une colonne doivent être définies au niveau de la table.

Dans la syntaxe :

  • schema : est le même que le nom du propriétaire

  • table : est le nom de la table

  • DEFAULT expr : spécifie une valeur par défaut à utiliser si une valeur est omise dans la requête INSERT requête

  • column : est le nom de la colonne

  • datatype : est le type de données et la longueur de la colonne

  • column_constraint : est une contrainte d’intégrité faisant partie de la définition de colonne

  • table_constraint : est une contrainte d’intégrité incluse à la fin de la définition de table

Les contraintes sont généralement créées en même temps que la table. Les contraintes peuvent être ajoutées à la table après sa création et désactivées temporairement.

Les deux exemples créent une contrainte PRIMARY KEY sur la colonne EMPLOYEE_ID de la table EMPLOYEES :

La syntaxe au niveau de la colonne est utilisée pour créer la contrainte

CREATE TABLE employees(
employee_id NUMBER(6)
CONSTRAINT emp_emp_id_pk PRIMARY KEY,
first_name VARCHAR2(20),
...);

La syntaxe au niveau de la table est utilisée pour créer la contrainte

CREATE TABLE employees(
employee_id NUMBER(6),
first_name VARCHAR2(20),
...
job_id VARCHAR2(10) NOT NULL,
CONSTRAINT emp_emp_id_pk
PRIMARY KEY (EMPLOYEE_ID));

NOT NULL

La contrainte NOT NULL empêche l’insertion des valeurs NULL dans la colonne. Les colonnes sans la contrainte NOT NULL peuvent contenir des valeurs NULL par défaut. La contrainte NOT NULL doit être définie au niveau de la colonne.

UNIQUE

Une contrainte UNIQUE vérifie que chaque valeur dans la colonne ou dans le jeu de colonnes est unique. La colonne (ou le jeu de colonne) incluse dans la définition de la contrainte UNIQUE est appelée la clé unique (unique key). Si la contrainte UNIQUE comprend plus d’une colonne, le groupe de colonnes est appelé clé unique composé (composite unique key).

Les contraintes UNIQUE n’empêchent pas l’insertion des valeurs NULL sauf si vous définissez les contraintes NOT NULL sur les mêmes colonnes. N’importe quel nombre de lignes peut contenir des valeurs NULL dans une colonne avec la contrainte CHECK. En effet, une valeur NULL n’est pas considérée comme égale à quoi que ce soit. Une valeur NULL dans une colonne (ou dans toutes les colonnes de la clé unique composé) satisfait toujours la contrainte UNIQUE.

Les contraintes UNIQUE peuvent être définies au niveau de la colonne ou au niveau de la table. Une clé unique composée doit être créée au niveau de la définition de table.

Dans l’exemple suivant, une contrainte UNIQUE est appliquée à la colonne EMAIL de la table EMPLOYEES. Le nom de la contrainte est EMP_EMAIL_UK.

CREATE TABLE employees(
employee_id NUMBER(6),
last_name VARCHAR2(25) NOT NULL,
email VARCHAR2(25),
salary NUMBER(8,2),
commission_pct NUMBER(2,2),
hire_date DATE NOT NULL,
...
CONSTRAINT emp_email_uk UNIQUE(email));

Le serveur Oracle renforce la contrainte UNIQUE en créant implicitement un index unique sur la colonne ou les colonnes contenant la clé unique.

PRIMARY KEY

Une contrainte PRIMARY KEY crée une clé primaire pour la table. Une seule clé primaire peut être créée pour chaque table. La contrainte PRIMARY KEY est une colonne (ou un groupe de colonnes) qui identifie de façon unique chaque ligne de la table. Cette contrainte a les propriétés des contraintes NOT NULL et UNIQUE.

[Note]

Du fait que les valeurs de la contrainte clé primaire sont uniques, le serveur Oracle crée un index unique sur la ou les colonnes contenant la clé primaire.

FOREIGN KEY

La contrainte FOREIGN KEY (ou intégrité référentielle) désigne une colonne ou un ensemble de colonnes comme clé étrangère et établit une relation entre une clé primaire ou une clé unique dans la même table ou dans une table différente.

Dans l’exemple suivant, DEPARTMENT_ID a été défini comme une clé étrangère dans la table EMPLOYEES (table-enfant ou table dépendante) ; elle fait référence a la colonne DEPARTMENT_ID de la table DEPARTMENTS (table parente ou référencée)

Règles :

  • La valeur d’une clé étrangère doit correspondre à une valeur existante de la table parente ou doit être NULL.

  • Les clés étrangères sont fondées sur les valeurs de données, ce sont des pointeurs logiques plutôt que physiques.

Les contraintes FOREIGN KEY peuvent être définies au niveau de la table ou de la colonne. Une clé étrangère composite doit être déclarée au niveau de la définition de la table.

Dans l’exemple suivant, une contrainte FOREIGN KEY est créée sur la colonne DEPARTMENT_ID de la table EMPLOYEES, en utilisant la syntaxe du niveau de la table. Le nom de la contrainte est EMP_DEPTID_FK.

CREATE TABLE employees(
employee_id NUMBER(6),
last_name VARCHAR2(25) NOT NULL,
email VARCHAR2(25),
salary NUMBER(8,2),
commission_pct NUMBER(2,2),
hire_date DATE NOT NULL,
...
department_id NUMBER(4),
CONSTRAINT emp_dept_fk FOREIGN KEY (department_id)
REFERENCES departments(department_id),
CONSTRAINT emp_email_uk UNIQUE(email));

La clé étrangère peut également être définie au niveau de la colonne, si elle est basée sur une seule colonne. La syntaxe diffère vu que le mot-clé FOREIGN KEY n’apparaît pas :

CREATE TABLE employees
(...
department_id NUMBER(4) CONSTRAINT emp_deptid_fk
REFERENCES departments(department_id),
...
)

La clé étrangère est définie dans la table enfant, la table contenant la colonne référencée est la table parente. La clé étrangère est définie en utilisant une combinaison des mots clés suivants :

  • FOREIGN KEY est utilisé pour définir la contrainte au niveau de la table sur une colonne de la table enfant.

  • ON DELETE CASCADE indique que lorsqu’une ligne de la table parente est effacée, les lignes dépendantes dans la table fille sont aussi effacées.

  • ON DELETE SET NULL convertit les valeurs de la colonne clé étrangère en NULL quand une valeur parente est supprimée.

Le comportement par défaut est appelé règle restreinte (restrict rule) qui n’autorise pas la mise à jour ni la suppression des données référencées.

Sans les options ON DELETE CASCADE ou ON DELETE SET NULL, les lignes dans la table parente ne peuvent être effacée si elles sont référencées dans la table enfant.

CHECK

La contrainte CHECK défini une condition dont chaque ligne doit remplir une condition. La condition a la même structure que les conditions de requête avec les exceptions suivantes :

Une seule colonne peut avoir plusieurs contraintes CHECK. Il n’y a pas de limite de nombre de contraintes CHECK que vous pouvez définir sur une colonne.

Les contraintes CHECK peuvent être définies au niveau de la colonne ou au niveau de la table.

CREATE TABLE employees
(...
salary NUMBER(8,2) CONSTRAINT emp_salary_min
CHECK (salary > 0),
...

Exemples d’utilisation

L’exemple suivant contient la requête créant la table EMPLOYEES dans le schéma HR.

CREATE TABLE employees
( employee_id NUMBER(6)CONSTRAINT emp_employee_id PRIMARY KEY,
  first_name VARCHAR2(20),
  last_name VARCHAR2(25)CONSTRAINT emp_last_name_nn NOT NULL,
  email VARCHAR2(25)CONSTRAINT emp_email_nn NOT NULL CONSTRAINT emp_email_uk UNIQUE,
  phone_number VARCHAR2(20),
  hire_date DATE CONSTRAINT emp_hire_date_nn NOT NULL,
  job_id VARCHAR2(10) CONSTRAINT emp_job_nn NOT NULL,
  salary NUMBER(8,2) CONSTRAINT emp_salary_ck CHECK (salary>0),
  commission_pct NUMBER(2,2),
  manager_id NUMBER(6),
  department_id NUMBER(4),
  CONSTRAINT emp_dept_fk REFERENCES departments (department_id));

Si vous tentez de violer une règle d’une contrainte définie sur une colonne, une erreur est retournée.

Par exemple, si vous essayez de mettre à jour un enregistrement avec une valeur qui est liée à une contrainte d'intégrité, une erreur est retournée.

Dans l’exemple suivant, le département 55 n’existe pas dans la table parente DEPARTMENTS, donc vous recevez l’erreur ORA-02291 qui correspond à une violation de clé primaire.

UPDATE employees
SET department_id = 55
WHERE department_id = 110;
UPDATE employees
*
ERROR at line 1:
ORA-02291: integrity constraint (HR.EMP_DEPT_FK)
violated - parent key not found

Dans exemple ci-dessous, la suppression du département 60 de la table DEPARTMENTS échoue car le numéro de département est utilisé comme une clé étrangère dans la table EMPLOYEES. Si l’enregistrement que vous essayez de supprimer possède des enregistrements enfants, vous recevrez une erreur ORA-02292: child record found.

DELETE FROM departments
WHERE department_id = 60;
DELETE FROM departments
*
ERROR at line 1:
ORA-02292: integrity constraint (HR.EMP_DEPT_FK)
violated - child record found

La requête suivante fonctionne car il n’y a pas d’employé dans le département 70 :

DELETE FROM departments
WHERE  department_id = 70;
1 row deleted.

Vues

Qu’est ce qu’une vue ?

Vous pouvez présenter des sous-ensembles logiques ou des combinaisons de données en créant une vue. Une vue est une table logique basée sur une ou plusieurs tables. Une vue ne contient pas de données mais elle est comme une fenêtre à travers laquelle les données peuvent êtres vues ou changées. La table sur laquelle se base la vue est appelée table de base (base table). La vue est stockée comme une requête SELECT dans le dictionnaire de données.

Avantages des vues

  • Les vues restreignent l’accès aux données car elles peuvent afficher uniquement certaines colonnes d’une table.

  • Les vues peuvent êtres utilisées pour obtenir avec de simples requêtes des résultats qui nécessiteraient des requêtes plus complexes. Par exemple, une vue peut récupérer des informations de plusieurs tables sans que l’utilisateur n’ait à savoir faire une jointure.

  • Les vues fournissent l’indépendance des données pour les utilisateurs ad-hoc et les applications. Une vue peut être utilisée pour récupérer les données de nombreuses tables.

  • Les vues fournissent aux groupes d’utilisateurs un accès aux données particulières.

Vues simples et vues complexes

Il existe deux types de vues : simples et complexes. La différence est liée aux opérations DML (INSERT, UPDATE, DELETE).

  • Une vue simple est une vue qui :

    • récupère les données à partir d’une seule table.

    • ne contient pas de fonctions ou de groupes de données.

    • permet l’exécution des opérations DML.

  • Une vue complexe est une vue qui :

    • récupère les données à partir de plusieurs tables.

    • contient des fonctions ou des groupes de données.

    • ne permet pas toujours l’exécution des opérations DML.

Caractéristiques Vue simple Vue complexe
Nombre de tables Une Une ou plusieurs
Contient des fonctions Non Oui
Contient des groupes de données Non Oui
Autorise les opérations DML Oui Pas toujours

Création d’une vue

Vous pouvez créer une vue en plaçant une sous-requête dans l’ordre CREATE VIEW.

CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW view
[(alias[, alias]...)]
AS subquery
[WITH CHECK OPTION [CONSTRAINT constraint]]
[WITH READ ONLY [CONSTRAINT constraint]];

Dans la syntaxe :

Mot-clé Description
OR REPLACE recrée la vue si celle-ci existe déjà
FORCE crée la vue même si les tables de base n’existent pas
NOFORCE crée la vue seulement si les tables de base existent (par défaut)
view correspond au nom de la vue
alias spécifie le nom pour les expressions sélectionnées par la sous-requête (le nombre d’alias doit correspondre au nombre d’expressions sélectionnées)
subquery est une requête SELECT complète (vous pouvez utiliser les alias pour les colonnes de la liste SELECT)
WITH CHECK OPTION spécifie que seules ces lignes accessibles via la vue peuvent être insérées ou mises à jour
constraint correspond au nom assigné à la contrainte CHECK OPTION
WITH READ ONLY interdit toute opération DML sur la vue

Exemple :

L’exemple crée une vue qui contient l’identifiant d’employé, le prénom et le salaire pour chaque employé du département 80.

CREATE VIEW empvu80
AS SELECT employee_id, last_name, salary
   FROM employees
   WHERE department_id = 80;
View created.

Vous pouvez afficher la structure de la vue en utilisant la commande iSQL*Plus DESCRIBE.

DESCRIBE empvu80

Règles :

  • La sous-requête, qui définit une vue, peut avoir une syntaxe SELECT complexe incluant des jointures, des groupes et des sous-requêtes.

  • Si vous ne spécifiez pas un nom de contrainte pour une vue créée avec l’option WITH CHECK OPTION, le système lui assignera un nom par défaut dans le format SYS_Cn.

  • Vous pouvez utiliser l’option OR REPLACE pour changer la définition de la vue sans avoir à la supprimer et à la recréer ou à redistribuer les privilèges précédemment définis.

Les alias de colonne dans la sous-requête

Vous pouvez contrôler les noms des colonnes en incluant des alias dans la sous-requête.

Dans l’exemple, une vue est créée, elle contient l’identifiant d’employé (EMPLOYEE_ID) avec l’alias ID_NUMBER, le nom (LAST_NAME) avec l’alias NAME et le salaire annuel (SALARY) avec l’alias ANN_SALARY pour chaque employé du département 50.

CREATE VIEW salvu50
AS  SELECT employee_id ID_NUMBER, last_name NAME, salary*12 ANN_SALARY
    FROM employees
    WHERE department_id = 50;
View created.

Comme alternative, vous pouvez utiliser les alias entre l’ordre CREATE et la sous-requête SELECT. Le nombre d’alias doit être le même que le nombre d’expressions sélectionnées dans la sous-requête.

CREATE OR REPLACE VIEW salvu50 (ID_NUMBER, NAME, ANN_SALARY)
AS SELECT employee_id, last_name, salary*12
   FROM employees
   WHERE department_id = 50;

View created.

Récupérer les données à partir d’une vue

Vous pouvez récupérer les données d’une vue comme vous le feriez pour n’importe quelle table. Vous pouvez afficher la totalité des colonnes de la vue ou préciser les lignes et colonnes.

SELECT *
FROM salvu50;

Modifier une vue

Avec l’option OR REPLACE, une vue peut être créée même si une autre existe déjà avec le même nom, l’ancienne vue est remplacée par la nouvelle. Pour changer la définition de la vue sans avoir à la supprimer et à la recréer ou à redistribuer les privilèges précédemment définis.

[Note]

Quand vous définissez des alias dans la clause CREATE OR REPLACE VIEW, souvenez-vous que leur ordre doit correspondre à l’ordre des colonnes de la sous-requête.

CREATE OR REPLACE VIEW empvu80
(id_number, name, sal, department_id)
AS SELECT employee_id, first_name || ' ' || last_name, salary, department_id
   FROM employees
   WHERE department_id = 80;

View created.

Création d’une vue complexe

L’exemple suivant crée une vue complexe récupérant le nom de département, le salaire minimum, le salaire maximum et le salaire moyen pour chaque département. Notez que des noms des colonnes alternatifs ont été spécifiés dans la vue. Ceci est obligatoire si une colonne de la vue est dérivée d’une fonction ou d’une expression.

CREATE OR REPLACE VIEW dept_sum_vu (name, minsal, maxsal, avgsal)
AS SELECT d.department_name, MIN(e.salary), MAX(e.salary), AVG(e.salary)
   FROM employees e JOIN departments d
   ON (e.department_id = d.department_id)
   GROUP BY d.department_name;
View created.

Vous pouvez voir la structure d’une vue en utilisant la commande iSQL*PLUS DESCRIBE. Affichez le contenu d’une vue en utilisant une requête SELECT.

SELECT *
FROM dept_sum_vu;

Exécuter des opérations DML sur une vue

Vous pouvez exécuter les opérations DML sur les données à travers une vue si celle-ci respecte les règles suivantes.

Vous pouvez supprimer une ligne d’une vue sauf si elle contient :

  • des fonctions de groupe

  • une clause GROUP BY

  • le mot clé DISTINCT

  • une pseudo-colonne ROWNUM

Vous pouvez modifier des données à travers une vue sauf si elle contient l’une des conditions mentionnées ci-dessus ou qu’une de ses colonnes soit définie par une expression (par exemple, SALARY * 12).

Vous pouvez ajouter des données dans une vue sauf si celle-ci contient une des conditions listées précédemment. De plus, vous ne pouvez pas ajouter des données via la vue si une colonne NOT NULL (sans une valeur par défaut) de la table de base n’est pas référencée par la vue. Toutes les valeurs requises doivent être présentes dans la vue.

Souvenez-vous que vous ajoutez des valeurs directement dans la table de base à travers la vue.

Utilisation de la clause WITH CHECK OPTION

Il est possible d’exécuter des vérifications d’intégrité référentielle à travers les vues. Vous pouvez également créer des contraintes au niveau de la base de données. Les vues peuvent être utilisées pour protéger l’intégrité des données, mais leur utilisation est très limitée.

La clause WITH CHECK OPTION spécifie que les INSERT et les UPDATE exécutés à travers la vue ne peuvent créer de lignes que la vue ne peut pas sélectionner.

CREATE OR REPLACE VIEW empvu20
AS SELECT *
FROM employees
WHERE department_id = 20
WITH CHECK OPTION CONSTRAINT empvu20_ck;
View created.

Ceci renforce les contraintes d’intégrité et les vérifications des données pendant les insertions ou les mises à jour. S’il y a une tentative d’opération DML sur des lignes inaccessibles à travers la vue, une erreur est affichée (avec le nom de contrainte si celui-ci a été défini).

UPDATE empvu20
SET department_id = 10
WHERE employee_id = 201;

Génère l’erreur :

ERROR at line 1:
ORA-01402: view WITH CHECK OPTION where-clause violation
[Note]

Aucune ligne n’est mise à jour car si le DEPARTMENT_ID était changé à 10, la vue ne serait plus apte à voir cet employé. Avec la clause WITH CHECK OPTION, la vue ne peut voir que les employées du département 20. Le changement de DEPARTMENT_ID n’est donc pas autorisé pour les employées à travers la vue.

Interdiction les opérations DML

Vous pouvez interdire toute opération DML sur une vue en la créant avec l’option WITH READ ONLY.

Dans l’exemple suivant, la vue EMPVU10 est modifiée en sorte d’empêcher l’exécution de tout ordre DML.

CREATE OR REPLACE VIEW empvu10 
   (employee_number, employee_name, job_title)
AS  SELECT employee_id, last_name, job_id
    FROM employees
    WHERE department_id = 10
    WITH READ ONLY;
View created.

Toute tentative de suppression de ligne d’une vue avec la contrainte lecture-seule (WITH READ ONLY) retourne une erreur :

DELETE FROM empvu10
WHERE employee_number = 200;
DELETE FROM empvu10
       *
ERROR at line 1:
ORA-01752: cannot delete from view without exactly one
key-preserved table

Toute tentative d’insertion ou de modification de ligne d’une vue avec la contrainte lecture-seule (WITH READ ONLY) retourne une erreur :

01733: virtual column not allowed here.

Supprimer une vue

Vous devez utiliser la requête DROP VIEW pour effacer une vue. Cette requête efface la définition de la vue de la base de données. La suppression d’une vue n’affecte pas tables de base. Les vues ou les autres applications basées sur la vue effacée deviennent invalides.

Seul le propriétaire de la vue ou un utilisateur avec le privilège DROP ANY VIEW peut effacer une vue.

Syntaxe :

DROP VIEW view;

Dans la syntaxe :

  • view : est le nom de la vue

Dans l’exemple suivant la vue EMPVU80 est supprimée.

DROP VIEW empvu80;
View dropped.

Séquences

Qu’est-ce qu’une séquence?

Une séquence est un objet de la base de données créé par un utilisateur qui peut être partagé avec plusieurs utilisateurs pour générer des nombres entiers.

Vous pouvez définir une séquence pour générer une valeur unique ou pour recycler et utiliser de nouveau le même nombre.

L’usage typique des séquences est la création de valeurs pour les clés primaires, qui doivent être unique pour chaque ligne. La séquence est générée et incrémentée (ou décrémentée) par un sous-programme interne Oracle. C’est un objet qui fait gagner du temps car il réduit la quantité de code d’une application nécessaire pour écrire un générateur de séquence.

Les séquences de nombres sont stockées et générées indépendamment des tables. Cependant, une séquence peut être utilisée pour plusieurs tables.

Créer une séquence

Pour générer les nombres séquentiels utilisez l’ordre CREATE SEQUENCE.

Syntaxe :

CREATE SEQUENCE sequence
[INCREMENT BY n]
[START WITH n]
[{MAXVALUE n | NOMAXVALUE}]
[{MINVALUE n | NOMINVALUE}]
[{CYCLE | NOCYCLE}]
[{CACHE n | NOCACHE}];

Dans la syntaxe :

Mot-clé

Description

sequence

correspond au nom du générateur de séquence

INCREMENT BY n

spécifie l’intervalle entre les nombres de la séquence, où n est un entier (si cette clause est omise, la séquence s’incrémente de 1)

START WITH n

spécifie le premier nombre généré par la séquence, où n est un entier (si cette clause est omise, le premier nombre généré est 1)

MAXVALUE n

spécifie la valeur maximale que la séquence peut générer

NOMAXVALUE

spécifie une valeur maximale égale à 10^27 pour une séquence ascendante et à -1 pour une séquence descendante (c’est une option par défaut)

MINVALUE n

spécifie la valeur minimum de la séquence

NOMINVALUE

spécifie une valeur minimale égale à 1 pour une séquence ascendante et à -(10^26) pour une séquence descendante (c’est une option par défaut)

CYCLE | NOCYCLE

spécifie si la séquence continue à générer des valeurs après avoir atteint sa valeur maximale ou minimale (NOCYCLE est l’option par défaut).

CACHE n | NOCACHE

spécifie combien de valeurs le serveur Oracle doit pré-allouer et garder en mémoire (par défaut, le serveur Oracle met en cache 20 valeurs)

Dans l’exemple ci-dessous une séquence DEPT_DEPTID_SEQ est créée pour être utilisée avec la colonne DEPARTMENT_ID de la table DEPARTMENTS. La séquence démarre à 120, la mise en cache et le cycle sont désactivés.

CREATE SEQUENCE dept_deptid_seq
INCREMENT BY 10
START WITH 120
MAXVALUE 9999
NOCACHE
NOCYCLE;
Sequence created.

N’utilisez pas l’option CYCLE dans une séquence utilisée pour générer les valeurs d’une clé primaire.

[Note]

La séquence est complètement indépendante de la table. Généralement, une séquence est nommée en utilisant le nom abrégé de la table et de la colonne avec laquelle elle est utilisée. Tout de meme, une séquence peut être utilisée partout, indépendement de son nom.

Les pseudo-colonnes NEXTVAL et CURRVAL

Une fois la séquence crée, elle est utilisée pour générer des nombres séquentiels utilisés dans vos tables. Pour générer les valeurs utilisez les pseudo-colonnes NEXTVAL et CURRVAL.

La pseudo-colonne NEXTVAL est utilisée pour extraire les nombres successifs à partir de la séquence spécifiée. Quand vous faites appel à sequence.NEXTVAL, un nouveau nombre de la séquence est généré et le nombre courant de la séquence est placé dans CURRVAL.

La pseudo-colonne CURVALL est utilisée pour obtenir le dernier nombre généré par la séquence dans la session courante. NEXTVAL doit être utilisé pour générer un nombre dans la session de l’utilisateur courant avant que CURRVAL puisse être utilisé. Quand vous faites appel à sequence.CURVAL, la dernière valeur générée dans la session de l’utilisateur courant est retournée.

Règles d’utilisation de NEXTVAL et de CURRVAL

Vous pouvez utiliser NEXTVAL et CURRVAL dans les contextes suivants :

Dans la clause SELECT dans une requête SELECT ne faisant pas partie de la sous-requête

  • Dans la clause SELECT d’une sous-requête faisant partie de l’ordre INSERT

  • Dans la clause VALUES d’une requête INSERT

  • Dans la clause SET d’une requête UPDATE

Vous ne pouvez pas utiliser NEXTVAL et CURVAL dans les contextes suivants :

  • Dans la clause SELECT d’une vue

  • Dans une requête SELECT avec le mot-clé DISTINCT

  • Dans une requête SELECT avec les clauses GROUP BY, HAVING ou ORDER BY

  • Dans une sous-requête de la requête SELECT, DELETE ou UPDATE

  • Dans l’expression DEFAULT de la requête CREATE TABLE ou ALTER TABLE

Utilisation d’une séquence

L’exemple suivant insert un nouveau département dans la table DEPARTMENTS. Il utilise la séquence DEPT_DEPTID_SEQ pour générer un nouveau nombre.

INSERT INTO departments(department_id, department_name, location_id)
VALUES (dept_deptid_seq.NEXTVAL, 'Support', 2500);
1 row created.

Vous pouvez voir la valeur courante de la séquence:

SELECT dept_deptid_seq.CURRVAL
FROM dual;

Supposons que vous voulez ajouter un employé dans le département qui vient d’être créé. Pour cela il est nécessaire d’insérer une ligne dans la table EMPLOYEES, la séquence dept_deptid_seq peut être utilisée pour insérer le numéro du département.

INSERT INTO employees (employee_id, department_id, ...)
VALUES (employees_seq.NEXTVAL, dept_deptid_seq .CURRVAL, ...);
[Note]

Pour l’exemple précédant, la séquence employees_seq a été créée.

Mise en cache des valeurs de la séquence

Vous pouvez mettre en cache dans la mémoire des séquences pour avoir un accès plus rapide aux valeurs de la séquence. Le cache est rempli pendant le premier appel à la séquence. A chaque appel suivant la valeur est retournée du cache. Après que la dernière valeur mise en cache soit utilisée, l’appel suivant à la séquence provoque la mise en cache de nouvelles valeurs.

Trous dans les séquences

Même si la séquence Oracle génère les nombres sans trous, ceci est indépendant des COMMIT ou des ROLLBACK. En effet, si vous annulez (ROLLBACK) un ordre DML (INSERT, UPDATE, etc.) qui a utilisé une séquence, la valeur générée par la séquence est perdue.

Un autre événement, une défaillance du système, peut causer des trous dans les valeurs générées par des séquences. Si une séquence met en cache ses valeurs (option CACHE), les valeurs sont perdues suite à une défaillance du système.

Etant donné que les séquences ne sont pas liées directement aux tables, la même séquence peut être utilisée pour plusieurs tables. Si vous faites cela, chaque table contiendra des trous dans les valeurs générées par la séquence.

Modifier ou supprimer une séquence

Syntaxe :

ALTER SEQUENCE sequence
[INCREMENT BY n]
[{MAXVALUE n | NOMAXVALUE}]
[{MINVALUE n | NOMINVALUE}]
[{CYCLE | NOCYCLE}]
[{CACHE n | NOCACHE}];

Si vous atteignez la valeur limite de la séquence (MAXVALUE), aucune nouvelle valeur ne sera générée par la dernière et vous recevrez une erreur indiquant que la séquence dépasse la limite MAXVALUE.

Pour continuer à utiliser la séquence, vous pouvez la modifier en utilisant la requête ALTER SEQUENCE :

ALTER SEQUENCE dept_deptid_seq
 INCREMENT BY 20
 MAXVALUE 999999
 NOCACHE
 NOCYCLE;
Sequence altered.

Si vous voulez supprimer une séquence vous devez utilisez la requête DROP SEQUENCE :

Syntaxe :

DROP SEQUENCE sequence;

L’exemple suivant supprime la séquence DEPT_DEPTID_SEQ :

DROP SEQUENCE dept_deptid_seq;
Sequence dropped.

Règles :

  • Vous devez être le propriétaire de la séquence ou avoir le privilège ALTER sur la séquence pour pouvoir la modifier.

  • Vous devez être le propriétaire de la séquence ou avoir le privilège DROP ANY SEQUENCE pour la supprimer.

  • La requête ALTER SEQUENCE n’affecte que les futures valeurs générées sans altérer les valeurs déjà présentes dans les tables.

  • L’option START WITH n ne peut pas être changée avec ALTER SEQUENCE. Vous devez supprimer et recréer la séquence pour réinitialiser la valeur en question.

  • Quelques validations sont effectuées. Par exemple, une nouvelle valeur pour MAXVALUE qui est inférieure à la précédente ne peut pas être imposée :

ALTER SEQUENCE dept_deptid_seq
INCREMENT BY 20
MAXVALUE 90
NOCACHE
NOCYCLE;
ALTER SEQUENCE dept_deptid_seq
*
ERROR at line 1:
ORA-04009: MAXVALUE cannot be made to be less than the current value

Index

Les indexes sont les objets que vous créez pour amméliorer les performances de certainnes requêtes. Les indexes sont créés automatiquement pour toutes les clés primaires et uniques.

Un index est un objet de schéma qui peut accélérer la recherche des lignes en utilisant un pointeur. Les index peuvent être créés explicitement ou automatiquement. Si vous n’avez pas d’index sur une colonne, une analyse complète de la table est effectuée lors d’une recherche.

Un index fournit un accès direct et rapide aux lignes de la table. Son rôle est de réduire le nombre d’Entrées/Sorties (I/O) sur un disque en utilisant un chemin indexé pour retrouver les données rapidement. Un index est utilisé et maintenu automatiquement par le serveur Oracle. Après la création de l’index, aucune intervention particulière n’est requise de la part de l’utilisateur.

Les index sont indépendants de la table qu’ils indexent au niveau logique et physique. Cela signifie qu’ils peuvent être créés ou supprimés n’importe quand et n’ont aucun effet sur les tables de base ou sur les autres index.

[Note]

Lorsque vous supprimez une table, les index correspondant sont supprimés également.

Les type d'index

Deux types d’index peuvent êtres créés :

  • Index unique : Le serveur Oracle crée automatiquement cet index lorsque vous définissez une colonne avec la contrainte PRIMARY KEY ou la contrainte UNIQUE. Le nom de l’index est le nom donné à la contrainte.

  • Index non-unique : C’est un index que l’utilisateur peut créer. Par exemple, vous pouvez créer un index pour la colonne FOREIGN KEY d’une table pour améliorer les performances des requêtes utilisant les jointures.

[Note]

Vous pouvez créer manuellement un index unique, mais il est recommandé de créer une contrainte UNIQUE qui crée un index unique implicitement.

Création d’un index

Pour créer un index avec une ou plusieurs colonnes, utilisez la requête CREATE INDEX.

Syntaxe :

CREATE INDEX index
ON table (column[, column]...);

Dans la syntaxe :

index est le nom de l’index

table est le nom de la table

column le nom de la colonne de la table à indexer

Créez un index pour améliorer les performances des requêtes faisant une recherche sur la colonne LAST_NAME de la table EMPLOYEES.

CREATE INDEX emp_last_name_idx
ON employees(last_name);
Index created.

Le fait d’avoir un nombre important d’index sur une table peut dégrader significativement les performances des ordres DML. Chaque opération DML qui est validée (COMMIT) entraîne la mise à jour des index. Plus vous créez d’index sur une table, plus de traitements sont effectués par le serveur Oracle pour mettre à jour les indexes après une opération DML.

Quand créer un index

Vous devrez créer un index uniquement si :

  • la colonne contient une grande étendue de valeurs

  • la colonne contient un grand nombre de valeur NULL

  • une ou plusieurs colonnes sont fréquemment utilisées dans la clause WHERE ou dans les conditions de jointure

  • la taille de la table est importante et la plupart des requêtes retournent entre 2% et 4% des lignes

Rappelez-vous que si vous voulez renforcer l’unicité, vous devez définir une contrainte UNIQUE dans la définition de la table. Un index UNIQUE est alors créé automatiquement.

Supprimer un index

Vous ne pouvez pas modifier les index.

Pour modifier un index, vous devez le supprimer et le recréer. Pour supprimer un index vous devez utiliser la requête DROP INDEX. Pour supprimer un index, vous devez être le propriétaire de l’index ou avoir le privilège DROP ANY INDEX.

DROP INDEX index;

Dans la syntaxe index est le nom de l’index existant.

[Note]

Si vous supprimer une table, les index et les contraintes associés seront automatiquement supprimés mais les vues et les séquences ne le seront pas.

Synonymes

Définition

Les synonymes sont des objets de la base de données qui vous permettent de définir un autre nom pour la plupart des objets.

Création de synonyme pour un objet

Pour accéder à une table appartenant à un autre utilisateur, vous avez besoin de préfixer le nom de la table par le nom de l’utilisateur qui l’a créé suivi d’un point. La création d’un synonyme permet de ne pas préciser le schéma et fournit un nom alternatif à une table, une vue, une procédure, une séquence ou autres objets. Cette méthode est très utile avec les objets dont le nom est long tels que les vues.

Syntaxe :

CREATE [PUBLIC] SYNONYM synonym
FOR object;

Dans la syntaxe :

PUBLIC crée un synonyme accessible à tous les utilisateurs

synonym est le nom du synonyme

object est le nom de l’objet pour lequel le synonyme est créé

Règles :

  • L’objet référencé par le synonyme ne pas faire partie d’un package

  • Un synonyme privé ne peut pas porter le même nom qu’un objet déjà présent dans le schéma.

Dans l’exemple suivant le synonyme D_SUM est créé pour la vue DEPT_SUM_VU.

CREATE SYNONYM d_sum
FOR dept_sum_vu;
Synonym Created.

L’administrateur de la base de données peut créer un synonyme public accessible à tous les utilisateurs. Dans l’exemple ci-dessous un synonyme public DEPT est créé pour la table DEPARTMENTS de l’utilisateur Alice.

CREATE PUBLIC SYNONYM dept
FOR alice.departments;
Synonym created.

Désormais, pour accéder à la table DEPARTMENTS d’Alice il suffit d’interroger le synonyme DEPT comme suit :

SELECT *
FROM DEPT;

Supprimer un synonyme

Pour supprimer un synonyme, utilisez la requête DROP SYNONYM.

Syntaxe :

DROP SYNONYM synonym;

Dans la syntaxe synonym est le nom du synonyme existant.

Dans l’exemple suivant le synonyme D_SUM est supprimé.

DROP SYNONYM d_sum;
Synonym dropped.

Seuls les administrateurs de base de données peuvent supprimer les synonymes publics.

DROP PUBLIC SYNONYM synonym;
A propos de SUPINFO | Contacts & adresses | Enseigner à SUPINFO | Presse | Conditions d'utilisation & Copyright | Respect de la vie privée | Investir
Logo de la société Cisco, partenaire pédagogique de SUPINFO, la Grande École de l'informatique, du numérique et du management Logo de la société IBM, partenaire pédagogique de SUPINFO, la Grande École de l'informatique, du numérique et du management Logo de la société Sun-Oracle, partenaire pédagogique de SUPINFO, la Grande École de l'informatique, du numérique et du management Logo de la société Apple, partenaire pédagogique de SUPINFO, la Grande École de l'informatique, du numérique et du management Logo de la société Sybase, partenaire pédagogique de SUPINFO, la Grande École de l'informatique, du numérique et du management Logo de la société Novell, partenaire pédagogique de SUPINFO, la Grande École de l'informatique, du numérique et du management Logo de la société Intel, partenaire pédagogique de SUPINFO, la Grande École de l'informatique, du numérique et du management Logo de la société Accenture, partenaire pédagogique de SUPINFO, la Grande École de l'informatique, du numérique et du management Logo de la société SAP, partenaire pédagogique de SUPINFO, la Grande École de l'informatique, du numérique et du management Logo de la société Prometric, partenaire pédagogique de SUPINFO, la Grande École de l'informatique, du numérique et du management Logo du IT Academy Program par Microsoft, partenaire pédagogique de SUPINFO, la Grande École de l'informatique, du numérique et du management

SUPINFO International University
Ecole d'Informatique - IT School
École Supérieure d'Informatique de Paris, leader en France
La Grande Ecole de l'informatique, du numérique et du management
Fondée en 1965, reconnue par l'État. Titre Bac+5 certifié au niveau I.
SUPINFO International University is globally operated by EDUCINVEST Belgium - Avenue Louise, 534 - 1050 Brussels