Creazione di altri oggetti dello schema

Oltre alle tabelle, altri oggetti essenziali dello schema sono viste, sequenze, indici e sinonimi. Una vista è una tabella logica o virtuale. I sinonimi sono semplicemente nomi alias per oggetti di database, inoltre semplificano la scrittura di query e forniscono un elemento di sicurezza del sistema camuffando il nome effettivo di un oggetto di database.Le sequenze sono oggetti di database speciali che supportano la generazione automatica di valori interi e sono spesso utilizzati per generare valori di chiave primaria per le tabelle. Gli indici vengono creati sulle colonne della tabella per facilitare il recupero rapido delle informazioni dalle tabelle.

Visualizzazioni

Una visualizzazione di database è una tabella logica o virtuale basata su una query. Le visualizzazioni vengono interrogate proprio come le tabelle. Ciò significa che dal punto di vista di uno sviluppatore o da quello di un utente del sistema di database, una visualizzazione ha l'aspetto di una tabella. come un oggetto viene memorizzato nel dizionario dei dati di un database; tuttavia, una vista non memorizza i dati in sé.Un database memorizza anche il piano di esecuzione per la creazione di una vista: ciò significa che i dati possono essere recuperati rapidamente attraverso l'uso di una vista anche se i dati effettivi presentati da una query SELECT di una vista non sono memorizzati come parte di una vista. Piuttosto, i dati vengono "raccolti insieme" ogni volta che una vista viene interrogata dalle tabelle del database per le quali è definita una vista, queste sono chiamate tabelle di base.

La sintassi generale è fornita di seguito.

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

Dalla sintassi,

L'opzione FORCE consente di creare una vista anche se una tabella di base a cui la vista fa riferimento non esiste già. Questa opzione viene utilizzata per creare una vista prima dell'effettiva creazione delle tabelle di base e dei dati di accompagnamento.

L'opzione NOFORCE è l'opposto di FORCE e consente a un utente del sistema di creare una vista se dispone dei privilegi necessari per creare una vista e se le tabelle da cui è stata creata la vista esistono già. Questa è l'opzione predefinita.

L'opzione WITH READ ONLY consente la creazione di una visualizzazione di sola lettura. Non è possibile utilizzare i comandi DELETE, INSERT o UPDATE per modificare i dati per una visualizzazione di sola lettura.

La clausola WITH CHECK OPTION consente l'aggiornamento delle righe che possono essere selezionate tramite la vista, oltre a specificare vincoli sui valori. La clausola CONSTRAINT funziona insieme alla clausola WITH CHECK OPTION per consentire a un amministratore del database di assegnare un nome univoco all'OPZIONE DI CONTROLLO. Se un amministratore di database omette la clausola CONSTRAINT, Oracle assegnerà automaticamente al vincolo un nome generato dal sistema che non sarà molto significativo.

Tipi di visualizzazioni

Una vista semplice viene creata sopra una sola tabella, è una semplice query SELECT senza funzioni o clausola di gruppo, ma solo una selezione di colonne dalla tabella senza alcuna trasformazione.Se viene eseguita una DML sulla vista, si riflette immediatamente nella tabella di base.

Una vista complessa viene creata su più tabelle utilizzando i join. Può contenere funzioni SQL, Raggruppa per funzioni, ma poiché la vista è su più dati e anche la selezione delle colonne non è semplice, non consente operazioni DML su di essa.

Illustrazione

Simple View: La visualizzazione semplice di seguito seleziona il nome del dipendente, l'ID reparto e lo stipendio per i dipendenti con ID LAVORO come DEV.

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

Complex view: L'esempio seguente mostra il nome del dipartimento, lo stipendio medio prelevato nel dipartimento e il conteggio dei dipendenti che vi lavorano.

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]descrive la struttura della vista. Le colonne sono elencate nella stessa sequenza della definizione della vista.

Operazioni DML su una vista

Le operazioni DML possono essere facilmente esercitate su viste semplici. Come affermato in precedenza, le operazioni di inserimento, aggiornamento ed eliminazione avvengono effettivamente sulla tabella di base.

Quando si esegue un'istruzione UPDATE, DELETE o INSERT DML su una vista, si stanno effettivamente manipolando le righe di dati per la tabella o le tabelle di base su cui è definita la vista. Esistono limitazioni sull'uso delle istruzioni UPDATE, DELETE e INSERT con le viste.In primo luogo, per utilizzare l'istruzione UPDATE, DELETE o INSERT con una vista, la vista deve essere aggiornabile.Una vista è aggiornabile se la clausola SELECT non specifica alcuna funzione aggregata nell'elenco SELECT.Inoltre, la vista non potrebbe sono state create mediante l'uso di una o più clausole GROUP BY, DISTINCT o UNION. È consentito utilizzare funzioni aggregate in una sottoquery SELECT in una clausola FROM. Inoltre, la vista non può avere colonne derivate nell'elenco SELECT. Successivamente, se una vista viene creata come risultato di un'operazione JOIN (una vista di join), le istruzioni UPDATE e INSERT possono solo modificare o inserire righe in una delle tabelle di base alla volta. Non è possibile modificare righe da due o più tabelle con una singola istruzione DML (Data Manipulation Language). Infine, un'istruzione DELETE può essere eseguita solo su una vista se si fa riferimento a una tabella in una clausola FROM. Ciò significa semplicemente che non è possibile eliminare righe da una tabella che non è stata specificata.

Clausola WITH CHECK OPTION

WITH CHECK OPTION è una clausola opzionale che specifica il livello di controllo da eseguire quando si inseriscono o si aggiornano i dati attraverso una vista.Se una vista viene creata utilizzando la clausola WITH CHECK OPTION, ogni riga che viene inserita o aggiornata nella tabella di base tramite la vista deve essere conforme alla definizione della vista. Notare che l'opzione non può essere specificata se la visualizzazione è stata creata in sola lettura.

Ad esempio, viene creata una vista V_EMP_DEV per i dipendenti che sono sviluppatori (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 utente tenta di aggiornare lo stipendio di un dipendente delle risorse umane tramite la visualizzazione, ma incontra un'eccezione. È perché la vista è stata creata CON CHECK OPTION.

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

Se fosse stata una visualizzazione semplice, l'istruzione UPDATE non avrebbe sollevato alcuna eccezione.

Far cadere la vista

Un amministratore di database (DBA) o il proprietario di una vista può eliminare una vista con l'istruzione DROP VIEW. Se una vista ha vincoli definiti, è necessario specificare la clausola CASCADE CONSTRAINTS quando si rilascia una vista; in caso contrario, l'istruzione DROP VIEW non viene elaborata. Se un'altra vista o un altro oggetto di database come un sinonimo o una vista materializzata (entrambi questi oggetti sono discussi più avanti in questo capitolo) fa riferimento a una vista rilasciata, Oracle non elimina questi oggetti di database; piuttosto, Oracle li contrassegna come non validi. Puoi eliminare questi oggetti non validi o ridefinirli per renderli nuovamente validi.

Il comando DROP VIEW di seguito elimina la vista EMP_VU dal database.

DROP VIEW EMP_VU;

Sequenze

Oracle offre la capacità di generare sequenze di numeri univoci per questo tipo di utilizzo e vengono chiamate sequenze. In genere, le sequenze vengono utilizzate per generare valori interi sequenziali univoci che vengono utilizzati come valori di chiave primaria nelle tabelle del database. essere generato in ordine crescente o decrescente. Si noti che un numero una volta generato dalla sequenza non può essere ripristinato.

Sintassi

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];

Dalla sintassi,

L'istruzione CREATE SEQUENCE deve specificare un nome di sequenza univoco. Questa è l'unica clausola richiesta nella dichiarazione. Se non si specifica nessuna delle altre clausole, tutti i numeri di sequenza generati seguiranno le impostazioni predefinite di Oracle.

La clausola INCREMENT BY determina come una sequenza viene incrementata man mano che viene generato ogni numero. L'incremento predefinito è uno; tuttavia, se si ha una buona ragione per cui una sequenza ignora i numeri, è possibile specificare un incremento diverso. Un incremento numerico positivo genera numeri di sequenza crescenti con un intervallo uguale all'intervallo selezionato. Un incremento numerico negativo genera numeri di sequenza decrescenti.

La clausola START WITH specifica il valore numerico iniziale per la sequenza: il numero iniziale predefinito è uno. Inoltre, è necessario specificare un valore iniziale se si dispone già di alcune righe con dati nella colonna che ora memorizzeranno i valori della sequenza.

La clausola MAXVALUE specifica il valore massimo a cui una sequenza può essere incrementata. In assenza di MAXVALUE, il valore massimo consentito che può essere generato per una sequenza è abbastanza grande, da 10 alla 27a potenza - 1. Il valore predefinito è NOMAXVALUE.

La clausola MINVALUE specifica il valore minimo di una sequenza per una sequenza decrescente (quella che genera numeri in ordine decrescente). L'impostazione predefinita è NOMINVALUE.

La clausola CYCLE specifica che i valori della sequenza possono essere riutilizzati se la sequenza raggiunge il MAXVALUE specificato. Se la sequenza scorre, i numeri vengono generati ricominciando dal valore INIZIO CON.

La clausola CACHE può migliorare le prestazioni del sistema consentendo a Oracle di generare un batch specificato di numeri in sequenza da archiviare nella memoria cache.

Se si specifica CACHE senza specificare un numero, la dimensione della cache predefinita è 20 numeri di sequenza. Facoltativamente, è possibile specificare NOCACHE per impedire la cache dei numeri di sequenza.

La clausola ORDER specifica che i numeri di sequenza vengono allocati nell'esatto ordine cronologico in cui sono richiesti.

NEXTVAL e CURRVAL

I valori di sequenza vengono generati tramite l'uso di due pseudo colonne denominate currval e nextval. Una pseudo colonna si comporta come una colonna di tabella, ma le pseudo colonne non vengono effettivamente memorizzate in una tabella. La prima volta che si seleziona la pseudo colonna nextval, il valore iniziale in la sequenza viene restituita.Selezioni successive della pseudo colonna nextval fanno sì che la sequenza aumenti come specificato nella clausola INCREMENT BY e restituisca il valore della sequenza appena generato.La pseudo colonna currval restituisce il valore corrente della sequenza, che è il valore restituito da l'ultimo riferimento a nextval.

In una sessione, NEXTVAL e non CURRVAL deve essere la prima azione sulla sequenza. Questo perché in una sessione, quando NEXTVAL genera il primo numero della sessione dalla sequenza, Oracle mantiene il valore corrente nel CURRVAL.

Sintassi:

Sequence.NEXTVAL
Sequence.CURRVAL

Punti da notare -

• CURRVAL e NEXTVAL possono essere utilizzati solo nell'SQL esterno di un'istruzione select.

• CURRVAL e NEXTVAL possono essere utilizzati nell'istruzione INSERT per sostituire la chiave primaria di una colonna e possono essere utilizzati sia come clausola di sottoquery che nella clausola VALUES.

• CURRVAL e NEXTVAL possono essere utilizzati per aggiornare i valori nelle tabelle.

• CURRVAL e NEXTVAL non possono essere nell'elenco di selezione VIEW, con la parola chiave DISTINCT, con le clausole GROUP BY, HAVING o ORDER BY e l'espressione DEFAULT in un'istruzione CREATE TABLE o ALTER TABLE.

Modifica della sequenza

Il proprietario della sequenza può modificare una sequenza per alterare solo attributi come INCREMENT BY value, MINVALUE, MAXVALUE, CYCLE o CACHE. Tieni presente che le modifiche apportate si rifletteranno nei prossimi numeri.

Sintassi:

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

Eliminare la sequenza

Il comando DROP SEQUENCE elimina le sequenze che devono essere ricreate o che non sono più necessarie.

DROP SEQUENCE [sequence name]

Indici

Gli indici sono gli oggetti di database utilizzati per ottimizzare le prestazioni della query SELECT. Esistono diversi tipi di indici, inclusi quelli utilizzati per applicare vincoli di chiave primaria, indici univoci, indici non univoci e indici concatenati, tra gli altri. le query richiederebbero a Oracle di eseguire la scansione di tutte le righe in una tabella per restituire le righe richieste per la tabella dei risultati. Viene creato un indice sulle colonne della tabella, che quindi memorizza tutti i valori della colonna sotto il segmento dell'indice. A differenza della sequenza, gli indici sono tabella vengono automaticamente eliminati una volta che la tabella è stata eliminata.

Gli indici possono essere creati automaticamente o manualmente. Quando si specifica un vincolo PRIMARY KEY o UNIQUE, Oracle creerà automaticamente un indice univoco per supportare il recupero rapido dei dati per la tabella specificata.

In alternativa, l'utente può creare indici manualmente per ottimizzare le prestazioni della query.Gli indici creati manualmente possono essere univoci o non univoci.Indici non univoci possono essere B-Tree, Bitmap o Indice basato su funzione.Per impostazione predefinita, Oracle crea indici B-Tree su colonne Ecco la sintassi

Sintassi

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

Notare che UNIQUE e BITMAP devono essere specificati solo per indici univoci e bitmap. Per impostazione predefinita, Oracle crea indici B-Tree per indici normali.

Un indice composto (chiamato anche indice concatenato) è un indice creato su più colonne di una tabella. Le colonne in un indice composto possono apparire in qualsiasi ordine e non è necessario che siano colonne adiacenti nella tabella. Gli indici compositi migliorano la velocità di recupero delle righe per le query in cui la clausola WHERE fa riferimento a tutte o alla parte iniziale delle colonne nell'indice composto. Un indice può contenere un massimo di 32 colonne.

Ad esempio, un utente crea l'indice IDX_EMP sulla colonna HIRE_DATE della tabella EMPLOYEES. L'utilizzo dell'indice ridurrà l'I / O del disco attraversando la scansione del percorso indicizzato e trova i dati filtrati nella colonna HIRE_DATE.

CREATE INDEX IDX_EMP ON employees(hire_date);

Far cadere l'indice

Gli indici non possono essere modificati ma possono essere alterati per scopi di analisi, ricostruzione o calcolo delle statistiche. Se la definizione dell'indice deve essere modificata, deve essere eliminata e ricreata. La sintassi del comando DROP INDEX è semplice.

DROP INDEX index_name;

Sinonimi

Un sinonimo è un alias, cioè una forma di abbreviazione utilizzata per semplificare il compito di fare riferimento a un oggetto di database.Il concetto è analogo all'uso di soprannomi per amici e conoscenti.Il riferimento a un oggetto di proprietà di un altro utente richiede il nome dello schema per essere preceduto da esso. Con l'aiuto di un sinonimo, si riduce lo sforzo di fare riferimento all'oggetto insieme al nome dello schema. In questo modo, il sinonimo fornisce la trasparenza della posizione perché il nome del sinonimo nasconde il nome dell'oggetto effettivo e il suo proprietario.

Esistono due categorie di sinonimi, pubblico e privato. Un sinonimo pubblico può essere utilizzato per consentire un facile accesso a un oggetto a tutti gli utenti del sistema. Infatti, l'individuo che crea un sinonimo pubblico non possiede il sinonimo, piuttosto, apparterrà al gruppo di utenti PUBLIC che esiste all'interno di Oracle.I sinonimi privati, invece, appartengono all'utente di sistema che li crea e risiede in quello schema dell'utente.

Sintassi

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

Un utente di sistema può concedere il privilegio di utilizzare sinonimi privati ​​di sua proprietà ad altri utenti di sistema.Per creare sinonimi, è necessario disporre del privilegio CREATE SYNONYM. Inoltre, è necessario disporre del privilegio CREATE SYNONYM PUBLIC per creare sinonimi pubblici Se un sinonimo viene dichiarato pubblico, il nome del sinonimo non può essere già utilizzato come sinonimo pubblico.Tentando di creare un sinonimo pubblico già esistente, il comando CREATE PUBLIC SYNONYM avrà esito negativo e Oracle restituirà ORA-00955: nome è già utilizzato da un messaggio di errore oggetto esistente.

Illustrazione

Considera due utenti U1 e U2.U1 ha accesso alla tabella EMPLOYEES. Quindi, per abilitare l'accesso sulla tabella EMPLOYEES anche agli U2, è possibile creare un sinonimo nello schema U2. L'accesso deve essere concesso da U1 a 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;

Eliminare un sinonimo

Un utente può eliminare il sinonimo di cui è proprietario. Per eliminare un sinonimo pubblico, è necessario disporre del privilegio DROP PUBLIC SYNONYM.

DROP SYNONYM EMP_SYN;