Création d'autres objets de schéma

Outre les tables, les autres objets de schéma essentiels sont la vue, les séquences, les index et les synonymes. Une vue est une table logique ou virtuelle. Les synonymes sont simplement des noms d'alias pour les objets de base de données.Les synonymes simplifient également l'écriture de requêtes et fournissent un élément de sécurité du système en masquant le nom réel d'un objet de base de données.Les séquences sont des objets de base de données spéciaux qui prennent en charge la génération automatique de valeurs entières, et sont souvent utilisées pour générer des valeurs de clé primaire pour les tables. Les index sont créés sur des colonnes de table pour faciliter la récupération rapide des informations des tables.

Vues

Une vue de base de données est une table logique ou virtuelle basée sur une requête.Les vues sont interrogées comme des tables.Cela signifie que de votre point de vue en tant que développeur ou du point de vue de l'utilisateur d'un système de base de données, une vue ressemble à une table. comme un objet est stocké dans le dictionnaire de données d'une base de données; cependant, une vue ne stocke aucune donnée elle-même.Une base de données stocke également le plan d'exécution pour créer une vue - cela signifie que les données peuvent être récupérées rapidement grâce à l'utilisation d'une vue même si les données réelles présentées par une requête SELECT d'une vue ne sont pas stockées Dans le cadre d'une vue. Au contraire, les données sont "rassemblées" chaque fois qu'une vue est interrogée à partir des tables de base de données pour lesquelles une vue est définie - on les appelle des tables de base.

La syntaxe générale est donnée ci-dessous.

CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW [ViewName]
[(Column Alias Name...)] 
AS [Query]
[WITH [CHECK OPTION] [READ ONLY] [CONSTRAINT]];

De la syntaxe,

L'option FORCE permet de créer une vue même si une table de base à laquelle la vue fait référence n'existe pas déjà. Cette option permet de créer une vue avant la création proprement dite des tables de base et des données associées.

L'option NOFORCE est l'opposé de FORCE et permet à un utilisateur système de créer une vue s'il dispose des privilèges requis pour créer une vue et si les tables à partir desquelles la vue est créée existent déjà. Ceci est l'option par défaut.

L'option WITH READ ONLY permet de créer une vue en lecture seule. Vous ne pouvez pas utiliser les commandes DELETE, INSERT ou UPDATE pour modifier les données d'une vue en lecture seule.

La clause WITH CHECK OPTION permet la mise à jour des lignes qui peuvent être sélectionnées via la vue, ainsi que de spécifier des contraintes sur les valeurs.La clause CONSTRAINT fonctionne en conjonction avec la clause WITH CHECK OPTION pour permettre à un administrateur de base de données d'attribuer un nom unique Si un administrateur de base de données omet la clause CONSTRAINT, Oracle attribuera automatiquement à la contrainte un nom généré par le système qui ne sera pas très significatif.

Types de vues

Une vue simple est créée au-dessus d'une seule table.Il s'agit d'une simple requête SELECT sans fonctions ni clause de groupe, mais juste une sélection de colonnes de la table sans aucune transformation.Si un DML est effectué sur la vue, il est immédiatement reflété dans la table de base.

Une vue complexe est créée sur plusieurs tables à l'aide de jointures. Elle peut contenir des fonctions SQL, Grouper par fonctions. Mais comme la vue est sur plusieurs données et la sélection de colonnes n'est pas non plus simple, elle ne permet pas d'opération DML dessus.

Illustration

Simple View: La vue simple ci-dessous sélectionne le nom de l'employé, l'identifiant du service et le salaire pour les employés avec l'ID JOB comme DEV.

CREATE OR REPLACE VIEW v_emp_dev
AS
SELECT first_name, department_id, salary
FROM employees
WHERE job_id = 'DEV';

Complex view: L'exemple ci-dessous montre le nom du service, le salaire moyen tiré dans le service et le nombre d'employés qui y travaillent.

CREATE OR REPLACE VIEW EMP_VU
AS
SELECT department_name, AVG (salary) avg_sal, COUNT (first_name) count
FROM employees E, departments D
WHERE E.department_id = D.department_id
GROUP BY department_name;

DESCRIBE [view name]décrit la structure de la vue. Les colonnes sont répertoriées dans le même ordre que dans la définition de vue.

Opérations DML sur une vue

Les opérations DML peuvent être facilement exécutées sur des vues simples. Comme indiqué précédemment, les opérations d'insertion, de mise à jour et de suppression se produisent en fait sur la table de base.

Lorsque vous exécutez une instruction UPDATE, DELETE ou INSERT DML sur une vue, vous manipulez en fait les lignes de données de la ou des tables de base sur lesquelles la vue est définie. Il existe des restrictions sur l'utilisation des instructions UPDATE, DELETE et INSERT. Tout d'abord, pour utiliser l'instruction UPDATE, DELETE ou INSERT avec une vue, la vue doit pouvoir être mise à jour.Une vue peut être mise à jour si la clause SELECT ne spécifie aucune fonction d'agrégation dans la liste SELECT. De plus, la vue ne peut pas ont été créés à l'aide d'une ou de plusieurs clauses GROUP BY, DISTINCT ou UNION. Il est permis d'utiliser des fonctions d'agrégation dans une sous-requête SELECT dans une clause FROM. En outre, la vue ne peut pas avoir de colonnes dérivées dans la liste SELECT. Ensuite, si une vue est créée à la suite d'une opération JOIN (une vue de jointure), les instructions UPDATE et INSERT peuvent uniquement modifier ou insérer des lignes dans l'une des tables de base à la fois. Vous ne pouvez pas modifier les lignes de deux tables ou plus avec une seule instruction de langage de manipulation de données (DML). Enfin, une instruction DELETE ne peut s'exécuter sur une vue que si une table est référencée dans une clause FROM. Cela signifie simplement que vous ne pouvez pas supprimer des lignes d'une table qui n'a pas été spécifiée.

Clause WITH CHECK OPTION

WITH CHECK OPTION est une clause facultative qui spécifie le niveau de vérification à effectuer lors de l'insertion ou de la mise à jour de données via une vue.Si une vue est créée à l'aide de la clause WITH CHECK OPTION, chaque ligne qui est insérée ou mise à jour dans la table de base via la vue doit être conforme à la définition de la vue. Notez que l'option ne peut pas être spécifiée si la vue est créée en lecture seule.

Par exemple, une vue V_EMP_DEV est créée pour les employés qui sont des développeurs (JOB_ID = DEV).

CREATE OR REPLACE VIEW v_emp_dev
AS
SELECT first_name, department_id, salary, 
FROM employees
WHERE job_id = 'DEV'
WITH CHECK OPTION empvu_dev;

Un utilisateur tente de mettre à jour le salaire d'un employé des ressources humaines via la vue, mais rencontre une exception. C'est parce que la vue a été créée AVEC CHECK OPTION.

UPDATE v_emp_dev
SET salary = salary+500
WHERE JOB_ID = 'HR';
ORA-01402: view WITH CHECK OPTION where-clause violation

S'il s'agissait d'une vue simple, l'instruction UPDATE n'aurait soulevé aucune exception.

Abandonner la vue

Un administrateur de base de données (DBA) ou un propriétaire de vue peut supprimer une vue avec l'instruction DROP VIEW. Si une vue a des contraintes définies, vous devez spécifier la clause CASCADE CONSTRAINTS lors de la suppression d'une vue; sinon, l'instruction DROP VIEW échoue. Si une autre vue ou un autre objet de base de données tel qu'un synonyme ou une vue matérialisée (ces deux objets sont traités plus loin dans ce chapitre) fait référence à une vue supprimée, Oracle ne supprime pas ces objets de base de données; Oracle les marque plutôt comme non valides. Vous pouvez supprimer ces objets non valides ou les redéfinir afin de les rendre à nouveau valides.

La commande DROP VIEW ci-dessous supprime la vue EMP_VU de la base de données.

DROP VIEW EMP_VU;

Séquences

Oracle offre la possibilité de générer des séquences de nombres uniques pour ce type d'utilisation, et elles sont appelées séquences.En règle générale, les séquences sont utilisées pour générer des valeurs entières séquentielles uniques qui sont utilisées comme valeurs de clé primaire dans les tables de base de données.Une séquence de nombres peut être généré dans l'ordre croissant ou décroissant.Notez qu'un nombre une fois généré par séquence ne peut pas être annulé.

Syntaxe

CREATE SEQUENCE <sequence name>
[INCREMENT BY < number >]
[START WITH < start value number>]
[MAXVALUE < MAXIMUM VLAUE NUMBER>]
[NOMAXVALUE]
[MINVALUE < minimum value number>]
[CYCLE | NOCYCLE]
[CACHE < number of sequence value to cache> | NOCACHE]
[ORDER | NOORDER];

De la syntaxe,

L'instruction CREATE SEQUENCE doit spécifier un nom de séquence unique. C'est la seule clause requise dans la déclaration. Si vous ne spécifiez aucune des autres clauses, tous les numéros de séquence générés suivront les paramètres par défaut d'Oracle.

La clause INCREMENT BY détermine comment une séquence s'incrémente à mesure que chaque nombre est généré. L'incrément par défaut est un; cependant, si vous avez une bonne raison pour qu'une séquence saute des nombres, vous pouvez spécifier un incrément différent. Un incrément numérique positif génère des numéros de séquence ascendants avec un intervalle égal à l'intervalle sélectionné. Un incrément numérique négatif génère des numéros de séquence décroissants.

La clause START WITH spécifie la valeur numérique de départ de la séquence - le numéro de départ par défaut est un. De plus, vous devez spécifier une valeur de départ si vous avez déjà des lignes avec des données dans la colonne qui vont désormais stocker les valeurs de séquence.

La clause MAXVALUE spécifie la valeur maximale à laquelle une séquence peut être incrémentée. En l'absence de MAXVALUE, la valeur maximale autorisée qui peut être générée pour une séquence est assez grande, 10 à la 27e puissance - 1. La valeur par défaut est NOMAXVALUE.

La clause MINVALUE spécifie la valeur minimale d'une séquence pour une séquence de décrémentation (une qui génère des nombres dans l'ordre décroissant). La valeur par défaut est NOMINVALUE.

La clause CYCLE spécifie que les valeurs de séquence peuvent être réutilisées si la séquence atteint la valeur MAXVALUE spécifiée. Si la séquence tourne, les nombres sont générés en recommençant à la valeur START WITH.

La clause CACHE peut améliorer les performances du système en permettant à Oracle de générer un lot spécifié de numéros séquencés à stocker dans la mémoire cache.

Si vous spécifiez CACHE sans spécifier de nombre, la taille du cache par défaut est de 20 numéros de séquence. Vous pouvez éventuellement spécifier NOCACHE pour empêcher le cache des numéros de séquence.

La clause ORDER spécifie que les numéros de séquence sont attribués dans l'ordre chronologique exact dans lequel ils sont demandés.

NEXTVAL et CURRVAL

Les valeurs de séquence sont générées par l'utilisation de deux pseudo colonnes nommées currval et nextval.Une pseudo-colonne se comporte comme une colonne de table, mais les pseudo colonnes ne sont pas réellement stockées dans une table.La première fois que vous sélectionnez la pseudo-colonne nextval, la valeur initiale dans Les sélections suivantes de la pseudo-colonne nextval provoquent l'incrémentation de la séquence comme spécifié dans la clause INCREMENT BY et renvoient la valeur de séquence nouvellement générée.La pseudo-colonne currval renvoie la valeur actuelle de la séquence, qui est la valeur renvoyée par la dernière référence à nextval.

Dans une session, NEXTVAL, et non CURRVAL, doit être la première action sur la séquence. En effet, dans une session, lorsque NEXTVAL génère le premier numéro de la session à partir de la séquence, Oracle conserve la valeur actuelle dans CURRVAL.

Syntaxe:

Sequence.NEXTVAL
Sequence.CURRVAL

Points à noter -

CURRVAL et NEXTVAL ne peuvent être utilisés que dans l'Outer SQL d'une instruction select.
CURRVAL et NEXTVAL peuvent être utilisés dans l'instruction INSERT pour remplacer une clé primaire de colonne.Il peut être utilisé à la fois comme clause de sous-requête et également dans la clause VALUES.
CURRVAL et NEXTVAL peuvent être utilisés pour mettre à jour les valeurs dans les tableaux.
CURRVAL et NEXTVAL ne peuvent pas être dans la liste de sélection VIEW, avec le mot clé DISTINCT, avec les clauses GROUP BY, HAVING ou ORDER BY et l'expression DEFAULT dans une instruction CREATE TABLE ou ALTER TABLE.

Modifier la séquence

Le propriétaire de la séquence peut modifier une séquence pour modifier les attributs tels que les clauses INCREMENT BY value, MINVALUE, MAXVALUE, CYCLE ou CACHE uniquement. Notez que les changements effectués seront reflétés dans les prochains chiffres.

Syntaxe:

ALTER SEQUENCE [sequence name]
INCREMENT BY n
MAXVALUE n
NOCACHE
NOCYCLE

Abandonner la séquence

La commande DROP SEQUENCE supprime les séquences qui doivent être recréées ou qui ne sont plus nécessaires.

DROP SEQUENCE [sequence name]

Index

Les index sont les objets de base de données utilisés pour régler les performances de la requête SELECT.Il existe différents types d'index, notamment ceux utilisés pour appliquer des contraintes de clé primaire, des index uniques, des index non uniques et des index concaténés, entre autres. Les requêtes nécessiteraient qu'Oracle analyse toutes les lignes d'une table afin de renvoyer les lignes requises pour la table de résultats.Un index est créé sur les colonnes de la table, qui stocke ensuite toutes les valeurs de la colonne sous le segment d'index.À la différence de la séquence, les index sont table spécifiques.Ils sont automatiquement supprimés une fois la table supprimée.

Les index peuvent être créés automatiquement ou manuellement. Lorsque vous spécifiez une contrainte PRIMARY KEY ou UNIQUE, Oracle crée automatiquement un index unique pour prendre en charge la récupération rapide des données pour la table spécifiée.

L'utilisateur peut également créer des index manuellement pour optimiser les performances de la requête.Les index créés manuellement peuvent être uniques ou non uniques.Les index non uniques peuvent être des index basés sur B-Tree, Bitmap ou Function.Par défaut, Oracle crée des index B-Tree sur les colonnes .Voici la syntaxe

Syntaxe

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

Notez que UNIQUE et BITMAP ne doivent être spécifiés que pour les index uniques et bitmap. Par défaut, Oracle crée des index B-Tree pour les index normaux.

Un index composite (également appelé index concaténé) est un index créé sur plusieurs colonnes d'une table. Les colonnes d'un index composite peuvent apparaître dans n'importe quel ordre et ne doivent pas nécessairement être des colonnes adjacentes dans le tableau. Les index composites améliorent la vitesse de récupération des lignes pour les requêtes dans lesquelles la clause WHERE fait référence à la totalité ou à la partie de tête des colonnes de l'index composite. Un index peut contenir un maximum de 32 colonnes.

Par exemple, un utilisateur crée l'index IDX_EMP sur la colonne HIRE_DATE de la table EMPLOYEES. L'utilisation de l'index réduira les E / S du disque en parcourant le parcours indexé et trouve les données qui sont filtrées sur la colonne HIRE_DATE.

CREATE INDEX IDX_EMP ON employees(hire_date);

Suppression de l'index

Les index ne peuvent pas être modifiés mais peuvent être modifiés à des fins d'analyse, de reconstruction ou de calcul de statistiques.Si la définition de l'index doit être modifiée, elle doit être supprimée et recréée.La syntaxe de la commande DROP INDEX est simple.

DROP INDEX index_name;

Synonymes

Un synonyme est un alias, c'est-à-dire une forme de raccourci utilisé pour simplifier la tâche de référencement d'un objet de base de données.Le concept est analogue à l'utilisation de surnoms pour les amis et connaissances.Référencer un objet appartenant à un autre utilisateur nécessite que le nom de schéma soit être préfixé avec lui. À l'aide d'un synonyme, vous réduisez l'effort de référencement de l'objet avec le nom du schéma. De cette façon, synonyme fournit la transparence de l'emplacement car le nom du synonyme masque le nom réel de l'objet et son propriétaire.

Il existe deux catégories de synonymes, publics et privés. Un synonyme public peut être utilisé pour permettre un accès facile à un objet pour tous les utilisateurs du système. En fait, l'individu qui crée un synonyme public ne possède pas le synonyme, il appartiendra plutôt au groupe d'utilisateurs PUBLIC qui existe dans Oracle, tandis que les synonymes privés, en revanche, appartiennent à l'utilisateur système qui les crée et y résident. schéma de l'utilisateur.

Syntaxe

CREATE [PUBLIC] SYNONYM [synonym name] 
FOR OBJECT;

Un utilisateur système peut accorder le privilège d'utiliser des synonymes privés qu'il possède à d'autres utilisateurs système.Pour créer des synonymes, vous devez disposer du privilège CREATE SYNONYM.En outre, vous devez disposer du privilège CREATE PUBLIC SYNONYM afin de créer des synonymes publics .Si un synonyme est déclaré public, le nom du synonyme ne peut pas déjà être utilisé comme synonyme public.Tenter de créer un synonyme public qui existe déjà entraînera l'échec de la commande CREATE PUBLIC SYNONYM, et Oracle renverra l'ORA-00955: nom est déjà utilisé par un message d'erreur d'objet existant.

Illustration

Considérez que deux utilisateurs U1 et U2.U1 ont accès à la table EMPLOYEES. Donc, pour permettre l'accès sur la table EMPLOYEES à U2 également, un synonyme peut être créé dans le schéma U2. L'accès doit être accordé par U1 à U2.

CONN U2/U2
SQL> CREATE SYNONYM EMP_SYN	FOR U1.employees;

CONN U1/U1
SQL> GRANT ALL ON EMP_SYN TO U2;

CONN U2/U2
SQL> SELECT * FROM EMP_SYN;

Supprimer un synonyme

Un utilisateur peut supprimer le synonyme qui lui appartient. Pour supprimer un synonyme public, vous devez disposer du privilège DROP PUBLIC SYNONYM.

DROP SYNONYM EMP_SYN;