Utilizzo degli stati DDL

Utilizzo degli stati DDL per creare e gestire le tabelle

Uno schema è la collezione di più oggetti del database, che sono conosciuti come oggetti schema.Questi oggetti hanno accesso diretto dal loro proprietario schema.La tabella sottostante elenca gli oggetti schema.

Tabella – per immagazzinare dati
Visualizzazione – per proiettare dati in un formato desiderato da una o più tabelle
Sequenza – per generare valori numerici
Index – per migliorare le prestazioni delle query sulle tabelle
Sinonimo – nome alternativo di un oggetto

Uno dei primi passi nella creazione di un database è creare le tabelle che memorizzeranno i dati di un’organizzazione.La progettazione del database implica l’identificazione dei requisiti degli utenti del sistema per vari sistemi organizzativi come l’inserimento degli ordini, la gestione dell’inventario e la contabilità. Indipendentemente dalle dimensioni e dalla complessità del database, ogni database è composto da tabelle.

Creazione della tabella

Per creare una tabella nel database, un DBA deve avere certe informazioni in mano – il nome della tabella, il nome della colonna, i tipi di dati della colonna e le dimensioni della colonna. Tutte queste informazioni possono essere modificate in seguito usando i comandi DDL.

Convenzioni sui nomi delle tabelle –

Il nome che scegli per una tabella deve seguire queste regole standard:
Il nome deve iniziare con una lettera A-Z o a-z
Può contenere numeri e sottolineature
Può essere in MAIUSCOLO o minuscolo
Può avere una lunghezza massima di 30 caratteri
Non può usare lo stesso nome di un altro oggetto esistente nel tuo schema
Non deve essere una parola riservata SQL

Seguendo le linee guida di cui sopra, EMP85′ può essere un nome di tabella valido.Ma 85EMP non lo è. Allo stesso modo, UPDATE non può essere scelto come nome di tabella poiché è una parola chiave riservata SQL.

Dichiarazione CREATE TABLE

La CREATE TABLE è un’istruzione DDL che viene usata per creare tabelle nel database.La tabella viene creata non appena lo script CREATE TABLE viene eseguito ed è pronta a contenere i dati in seguito.L’utente deve avere il privilegio di sistema CREATE TABLE per creare la tabella nel proprio schema.Ma per creare una tabella nello schema di qualsiasi utente, l’utente deve avere lo schema CREATE ANY TABLE.

Qui c’è la sintassi di un’istruzione di base CREATE TABLE.Ci possono essere molte clausole aggiuntive per fornire esplicitamente le specifiche di archiviazione o i valori di segmento.

CREATE TABLE table ( { column datatype ... | table_constraint} ... | table_constraint} ]...)

Nella sintassi di cui sopra, DEFAULT specifica il valore di default che può essere usato durante l’istruzione INSERT se la colonna viene ignorata. Non può contenere riferimenti ad altre colonne della tabella o pseudo colonne (CURRVAL, NEXTVAL, LEVEL e ROWNUM) eccetto SYSDATE e USER, o costanti di data che non sono completamente specificate.

I vincoli sono le regole definite opzionalmente a livello di colonna o di tabella (trattate più avanti in questo capitolo).Queste regole sono controllate durante qualsiasi azione sui dati (Insert, update) sulla tabella e sollevano un errore per interrompere l’azione in caso di violazione.

Per esempio, l’istruzione CREATE TABLE qui sotto crea una tabella EMP_TEST. Notate le specifiche della colonna, il tipo di dati e la precisione.

CREATE TABLE SCOTT.EMP_TEST(EMPID NUMBER,ENAME VARCHAR2(100),DEPARTMENT_ID NUMBER,SALARY NUMBER,JOB_ID VARCHAR2(3),HIREDATE DATE,COMM NUMBER);

Un utente può fare riferimento alle tabelle dallo schema di un altro utente anteponendo il nome utente o lo schema al nome della tabella. Per esempio, un utente GUEST desidera interrogare il nome e lo stipendio del dipendente dalla tabella EMP_TEST che è di proprietà di SCOTT. Egli può eseguire la seguente query –

SELECT ENAME, SALARY,FROM GUEST.EMP_TEST;

Una colonna può contenere un valore predefinito durante la creazione della tabella e aiuta a limitare i valori NULL che entrano nella colonna. Il valore predefinito può essere dedotto da un letterale, un’espressione o una funzione SQL che deve restituire un tipo di dati compatibile con la colonna. Nella seguente dichiarazione CREATE TABLE, notate che la colonna LOCATION_ID ha il valore predefinito 100.

CREATE TABLE SCOTT.DEPARTMENT(DEPARTMENT_ID NUMBER, DNAME VARCHAR2 (100), LOCATION_ID NUMBER DEFAULT 100);

CTAS – Creare una tabella usando una subquery

Una tabella può essere creata da una tabella esistente nel database usando l’opzione subquery, che copia la struttura della tabella e i dati dalla tabella. I dati possono anche essere copiati in base alle condizioni e le definizioni dei tipi di dati delle colonne, compresi i vincoli NOT NULL imposti esplicitamente, vengono copiati nella nuova tabella.

Il seguente script CTAS crea una nuova tabella EMP_BACKUP. I dati dei dipendenti del dipartimento 20 vengono copiati nella nuova tabella.

CREATE TABLE EMP_BACKUPASSELECT * FROM EMP_TESTWHERE department_id=20;

Tipi di dati

I tipi di dati sono usati per specificare il comportamento di base di una colonna nella tabella.Su una base più ampia, il comportamento della colonna può appartenere a una famiglia di numeri, caratteri o date.Ci sono molti altri sottotipi che appartengono a queste famiglie.

Tipo di dati NUMBER

Il tipo di dati NUMBER comprende valori numerici interi, a virgola fissa e a virgola mobile. Le prime versioni di Oracle definivano diversi tipi di dati per ciascuno di questi diversi tipi di numeri, ma ora il tipo di dati NUMBER serve tutti questi scopi.Scegliete il tipo di dato NUMBER quando una colonna deve memorizzare dati numerici che possono essere usati in calcoli matematici.Occasionalmente, il tipo di dato NUMBER è usato per memorizzare numeri di identificazione dove quei numeri sono generati dal DBMS come numeri sequenziali.

NUMBER (p, s), dove p è la precisione fino a 38 cifre e s è la scala (numero di cifre a destra del punto decimale).La scala può variare da -84 a 127.

NUMBER (p), è un numero a virgola fissa con una scala di zero e una precisione di p.

FLOAT ,dove p è la precisione binaria che può variare da 1 a 126. Se p non è specificato, il valore predefinito è il 126 binario.

Tipo di dati DATE

Per ogni tipo di dati DATE, secolo, anno, mese, giorno, ora, minuto, secondo sono memorizzati nel database. Ogni sistema di database ha un formato di data predefinito che è definito dal parametro di inizializzazione NLS_DATE_FORMAT. Questo parametro è solitamente impostato su DD-MON-YY.Se non si specifica un’ora, l’ora predefinita è 12:00:00 a.m.

Tipo di dati carattere

Oracle supporta tre tipi di dati carattere predefiniti tra cui CHAR, VARCHAR, VARCHAR2, e LONG.VARCHAR e VARCHAR2 sono in realtà sinonimi, e Oracle raccomanda di usare VARCHAR2 invece di VARCHAR.Per esempio, un numero di sicurezza sociale (SSN) negli Stati Uniti è assegnato ad ogni cittadino ed è sempre di 9 caratteri (anche se un SSN è strettamente composto da cifre, le cifre sono trattate come caratteri), e verrebbe specificato come CHAR(9). Usate il tipo di dati VARCHAR2 per memorizzare dati alfanumerici di lunghezza variabile: per esempio, il nome di un cliente o l’indirizzo variano considerevolmente in termini di numero di caratteri da memorizzare, la dimensione massima di una colonna VARCHAR2 è di 4.000 caratteri.

Tipo di dati LOB

Oracle fornisce diversi tipi di dati LOB, inclusi CLOB (character large object) e BLOB (binary large object).Le colonne di questi tipi di dati possono memorizzare dati non strutturati tra cui testo, immagini, video e dati spaziali.Il tipo di dati CLOB può memorizzare fino a otto terabyte di dati di carattere usando il set di caratteri del database CHAR. Il tipo di dati BLOB è usato per memorizzare grandi oggetti binari non strutturati come quelli associati a dati di immagini e video dove i dati sono semplicemente un flusso di valori “bit”.Un tipo di dati BLOB può memorizzare fino a otto terabyte di dati binari.Il tipo di dati NCLOB può memorizzare grandi oggetti di carattere in set di caratteri nazionali multibyte fino a 8TB a 128TB.Il valore del tipo di dati BFILE funziona come un localizzatore di file o puntatore a file sul file system del server. La dimensione massima del file supportata è da 8TB a 128TB.

Constraints

Constraints sono l’insieme di regole definite nelle tabelle Oracle per assicurare l’integrità dei dati.Queste regole sono applicate per ogni colonna o set di colonne.Ogni volta che la tabella partecipa all’azione dei dati, queste regole sono validate e sollevano un’eccezione in caso di violazione. I tipi di vincolo disponibili sono NOT NULL, Primary Key, Unique, Check, e Foreign Key.

La sintassi seguente può essere usata per imporre un vincolo a livello di colonna.

Sintassi:

column constraint_type

Tutti i vincoli tranne NOT NULL, possono essere definiti anche a livello di tabella. I vincoli composti possono essere specificati solo a livello di tabella.

Costrizione NOT NULL

Una costrizione NOT NULL significa che una riga di dati deve avere un valore per la colonna specificata come NOT NULL.Se una colonna è specificata come NOT NULL, l’RDBMS Oracle non permetterà di memorizzare righe nella tabella dei dipendenti che violano questa costrizione.Può essere definita solo a livello di colonna, e non a livello di tabella.

Sintassi:

COLUMN

Vincolo UNICO

A volte è necessario imporre l’unicità per un valore di colonna che non è una colonna chiave primaria.Il vincolo UNICO può essere usato per imporre questa regola e Oracle rifiuterà qualsiasi riga che viola il vincolo unico.Il vincolo unico assicura che i valori della colonna siano distinti, senza duplicati.

Sintassi:

Livello colonna:

COLUMN

Livello tabella: CONSTRAINT UNIQUE (nome colonna)

Nota: Oracle crea internamente un indice unico per prevenire la duplicazione dei valori della colonna. gli indici saranno discussi più tardi in PL/SQL.

CREATE TABLE TEST( ... , NAME VARCHAR2(20) CONSTRAINT TEST_NAME_UK UNIQUE, ... );

In caso di chiave unica composita, deve essere definita a livello di tabella come segue.

CREATE TABLE TEST( ... , NAME VARCHAR2(20), STD VARCHAR2(20) , CONSTRAINT TEST_NAME_UK UNIQUE (NAME, STD) );

Chiave primaria

Ogni tabella deve normalmente contenere una colonna o un insieme di colonne che identifica in modo univoco le righe di dati che sono memorizzati nella tabella.Questa colonna o insieme di colonne è chiamata chiave primaria.La maggior parte delle tabelle ha una singola colonna come chiave primaria.Le colonne chiave primaria sono limitate contro i NULL e i valori duplicati.

Punti da notare –

Una tabella può avere solo una chiave primaria.
Colonne multiple possono essere raggruppate sotto una chiave primaria composta.
Oracle crea internamente un indice unico per prevenire la duplicazione dei valori delle colonne.Gli indici saranno discussi più tardi in PL/SQL.

Sintassi:

Livello colonna:

COLUMN

Livello tabella:

CONSTRAINT PRIMARY KEY

L’esempio seguente mostra come usare il vincolo PRIMARY KEY a livello di colonna.

CREATE TABLE TEST( ID NUMBER CONSTRAINT TEST_PK PRIMARY KEY, ... );

L’esempio seguente mostra come definire la chiave primaria composita usando il vincolo PRIMARY KEY a livello di tabella.

CREATE TABLE TEST ( ..., CONSTRAINT TEST_PK PRIMARY KEY (ID) );

Chiave straniera

Quando due tabelle condividono la relazione padre-figlio basata su una colonna specifica, la colonna di unione nella tabella figlia è conosciuta come chiave straniera.Questa proprietà della colonna corrispondente nella tabella genitore è conosciuta come integrità referenziale. I valori della colonna Foreign Key nella tabella figlia possono essere nulli o devono essere i valori esistenti della tabella genitore. Si prega di notare che solo le colonne chiave primaria della tabella di riferimento sono idonee a far rispettare l’integrità referenziale.

Se una chiave esterna è definita sulla colonna nella tabella figlio, Oracle non permette la cancellazione della riga padre, se contiene righe figlio, ma se l’opzione ON DELETE CASCADE è data al momento della definizione della chiave esterna, Oracle cancella tutte le righe figlio mentre la riga padre viene cancellata.Allo stesso modo, ON DELETE SET NULL indica che quando una riga della tabella padre viene cancellata, i valori della chiave esterna sono impostati su null.

Sintassi:

Livello colonna:

COLUMN

Livello tabella:

CONSTRAINT

L’esempio seguente mostra come usare il vincolo FOREIGN KEY a livello di colonna.

CREATE TABLE TEST(ccode varchar2(5) CONSTRAINT TEST_FK REFERENCES PARENT_TEST(ccode), ...);

Uso della clausola ON DELETE CASCADE

CREATE TABLE TEST(ccode varchar2(5) CONSTRAINT TEST_FK REFERENCES PARENT_TEST (ccode) ON DELETE CASCADE, ...);

Check constraint

A volte i valori dei dati memorizzati in una colonna specifica devono rientrare in un intervallo di valori accettabile.Un CHECK constraint richiede che la condizione di controllo specificata sia vera o sconosciuta per ogni riga memorizzata nella tabella.Check constraint permette di imporre una regola condizionale su una colonna, che deve essere validata prima che i dati siano inseriti nella colonna. La condizione non deve contenere una subquery o pseudo colonna CURRVAL NEXTVAL, LEVEL, ROWNUM, o SYSDATE.

Oracle permette che una singola colonna abbia più di un vincolo CHECK. Infatti, non c’è un limite pratico al numero di vincoli CHECK che possono essere definiti per una colonna.

Sintassi:

Livello colonna:

COLUMN CONSTRAINT

Livello tabella:

CONSTRAINT CHECK (condition)

Il seguente esempio mostra come usare il vincolo CHECK a livello colonna.

CREATE TABLE TEST( ..., GRADE char (1) CONSTRAINT TEST_CHK CHECK (upper (GRADE) in ('A','B','C')), ...);

Il seguente esempio mostra come usare il vincolo CHECK a livello tabella.

CREATE TABLE TEST( ..., CONSTRAINT TEST_CHK CHECK (stdate < = enddate),);

Dichiarazione ALTER TABLE

Un DBA può apportare modifiche alla struttura della tabella o alle definizioni delle colonne dopo che la tabella è stata creata nel database.Il comando DDL ALTER TABLE è usato per eseguire tali azioni.Il comando Alter fornisce molteplici utilità esclusive per gli oggetti dello schema.La dichiarazione ALTER TABLE è usata per aggiungere, eliminare, rinominare e modificare una colonna in una tabella.

La seguente dichiarazione ALTER TABLE rinomina la tabella EMP in EMP_NEW.

ALTER TABLE EMP RENAME TO EMP_NEW;

La seguente istruzione ALTER TABLE aggiunge una nuova colonna TESTCOL alla tabella EMP_NEW

ALTER TABLE EMP_NEW ADD (TESTCOL VARCHAR2 (100))

La seguente istruzione ALTER TABLE rinomina la colonna TESTCOL in TESTNEW.

ALTER TABLE EMP_NEW RENAME COLUMN TESTCOL TO TESTNEW

La seguente dichiarazione ALTER TABLE elimina la colonna TESTNEW dalla tabella EMP_NEW

ALTER TABLE EMP_NEW DROP COLUMN TESTNEW;

La seguente dichiarazione ALTER TABLE aggiunge la chiave primaria alla colonna EMPLOYEE_ID.

ALTER TABLE EMP_NEW ADD PRIMARY KEY (EMPLOYEE_ID)

La seguente dichiarazione ALTER TABLE elimina la chiave primaria.

ALTER TABLE EMP_NEW DROP PRIMARY KEY;

La seguente dichiarazione ALTER TABLE cambia la modalità della tabella in sola lettura.

ALTER TABLE EMP_NEW READ ONLY;

Tabelle di sola lettura

Le tabelle di sola lettura sono un miglioramento di Oracle 11g e permettono di usare le tabelle per scopi di sola lettura. Nelle versioni precedenti di Oracle, le tabelle venivano rese di sola lettura concedendo il privilegio di SELECT agli altri utenti, ma il proprietario aveva ancora il privilegio di lettura e scrittura, ma ora, se una tabella è impostata in sola lettura, anche il proprietario non ha accesso alla manipolazione dei dati.

Sintassi:

ALTER TALE READ ONLY

ALTER TALE READ WRITE

Illustrazione

SQL>CREATE TABLE ORATEST (id NUMBER)SQL>INSERT INTO ORATEST VALUES (1);SQL>ALTER TABLE ORATEST READ ONLY;SQL> INSERT INTO ORATEST VALUES (2);INSERT INTO ORATEST VALUES (2) *ERROR at line 1:ORA-12081: update operation not allowed on table "TEST"."ORATEST"SQL> UPDATE ORATEST SET id = 2;UPDATE ORATEST SET id = 2 *ERROR at line 1:ORA-12081: update operation not allowed on table "TEST"."ORATEST"SQL> DELETE FROM ORATEST;DELETE FROM ORATEST *ERROR at line 1:ORA-12081: update operation not allowed on table "TEST"."ORATEST"SQL> TRUNCATE TABLE ORATEST;TRUNCATE TABLE ORATEST *ERROR at line 1:ORA-12081: update operation not allowed on table "TEST"."ORATEST"SQL> ALTER TABLE ORATEST ADD (description VARCHAR2 (50));ALTER TABLE ORATEST ADD (description VARCHAR2 (50))*ERROR at line 1:ORA-12081: update operation not allowed on table "TEST"."ORATEST"SQL> ALTER TABLE ORATEST READ WRITE;Table altered.SQL> DELETE FROM ORATEST;1 row deleted.

Dichiarazione DROP TABLE

La dichiarazione DROP TABLE è usata per rimuovere una tabella dal database. La tabella eliminata e i suoi dati non sono più disponibili per la selezione. La tabella eliminata può essere recuperata usando l’utilità FLASHBACK, se disponibile nel recyclebin. L’eliminazione di una tabella elimina l’indice e i trigger associati ad essa.

Sintassi:

DROP TABLE

L’istruzione seguente farà cadere la tabella e la metterà nel recyclebin.

DROP TABLE emp_new;

L’istruzione seguente farà cadere la tabella e la tirerà fuori anche dal recyclebin.

DROP TABLE emp_new PURGE;

Previsioni