Utilizzo di istruzioni DDL

Utilizzo di istruzioni DDL per creare e gestire tabelle

Uno schema è la raccolta di più oggetti di database, noti come oggetti schema. Questi oggetti hanno accesso diretto dal loro schema proprietario. La tabella seguente elenca gli oggetti schema.

Tabella - per memorizzare i dati
Visualizza - per proiettare i dati nel formato desiderato da una o più tabelle
Sequenza - per generare valori numerici
Indice: per migliorare le prestazioni delle query sulle tabelle
Sinonimo: nome alternativo di un oggetto

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

Creazione del tavolo

Per creare una tabella nel database, un DBA deve avere determinate informazioni in mano: il nome della tabella, il nome della colonna, i tipi di dati delle colonne e le dimensioni delle colonne. Tutte queste informazioni possono essere modificate successivamente utilizzando i comandi DDL.

Convenzioni di denominazione delle tabelle -

Il nome che scegli per una tabella deve seguire queste regole standard:
Il nome deve iniziare con una lettera AZ o az
Può contenere numeri e trattini bassi
Può essere in MAIUSCOLO o in minuscolo
Può contenere fino a 30 caratteri
Impossibile utilizzare lo stesso nome di un altro oggetto esistente nello 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 a SQL.

Istruzione CREATE TABLE

CREATE TABLE è un'istruzione DDL che viene utilizzata per creare tabelle nel database. La tabella viene creata non appena viene eseguito lo script CREATE TABLE ed è pronta a contenere i dati in avanti. L'utente deve disporre del 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.

Di seguito è riportata la sintassi di un'istruzione CREATE TABLE di base. Potrebbero essere presenti molte clausole aggiuntive per fornire esplicitamente le specifiche di archiviazione oi valori del segmento.

CREATE TABLE [schema.]table 
          ( { column datatype [DEFAULT expr] [column_constraint] ... 
            | table_constraint} 
         [, { column datatype [DEFAULT expr] [column_constraint] ... 
            | table_constraint} ]...) 
         [AS subquery]

Nella sintassi precedente, DEFAULT specifica il valore predefinito che può essere utilizzato 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 vengono verificate durante qualsiasi azione sui dati (inserimento, aggiornamento) sulla tabella e generano un errore per interrompere l'azione in caso di violazione.

Ad esempio, l'istruzione CREATE TABLE di seguito crea una tabella EMP_TEST. Notare 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 al nome utente o allo schema il nome della tabella. Ad esempio, un utente GUEST desidera interrogare il nome e lo stipendio del dipendente dalla tabella EMP_TEST che è di proprietà di SCOTT. Può emettere la query seguente:

SELECT  ENAME, SALARY,
FROM 	GUEST.EMP_TEST;

Una colonna può contenere un valore predefinito durante il tempo di creazione della tabella e aiuta a limitare i valori NULL che entrano nella colonna. Il valore predefinito può essere dedotto da una funzione letterale, espressione o SQL che deve restituire un tipo di dati compatibile alla colonna. Nell'istruzione CREATE TABLE di seguito, nota 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: crea una tabella utilizzando una sottoquery

È possibile creare una tabella da una tabella esistente nel database utilizzando un'opzione di sottoquery, che copia la struttura della tabella così come i dati dalla tabella. I dati possono anche essere copiati in base alle condizioni. Le definizioni del tipo di dati della colonna, inclusi i vincoli NOT NULL esplicitamente imposti, vengono copiati nella nuova tabella.

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

CREATE TABLE EMP_BACKUP
AS
SELECT * FROM EMP_TEST
WHERE department_id=20;

Tipi di dati

I tipi di dati vengono utilizzati 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 numerico

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 a tutti questi scopi.Scegli il tipo di dati NUMBER quando una colonna deve memorizzare dati numerici che possono essere utilizzati nei calcoli matematici. Occasionalmente, il tipo di dati NUMBER viene utilizzato per memorizzare i numeri di identificazione in cui tali numeri sono generati dal DBMS come numeri sequenziali.

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

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

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

Tipo di dati data

Per ogni tipo di dati DATE, nel database vengono memorizzati secolo, anno, mese, giorno, ora, minuti e secondi. Ogni sistema di database ha un formato di data predefinito definito dal parametro di inizializzazione NLS_DATE_FORMAT. Questo parametro è solitamente impostato su GG-LUN-AA. Se non si specifica un'ora, l'ora predefinita è 12:00:00 am

Tipo di dati carattere

Oracle supporta tre tipi di dati di carattere predefiniti inclusi CHAR, VARCHAR, VARCHAR2 e LONG.VARCHAR e VARCHAR2 sono effettivamente sinonimi e Oracle consiglia di utilizzare VARCHAR2 invece di VARCHAR.Utilizzare il tipo di dati CHAR quando la colonna memorizzerà i valori dei caratteri a lunghezza fissa. Ad esempio, un numero di previdenza sociale (SSN) negli Stati Uniti è assegnato a ogni cittadino e ha sempre una dimensione di 9 caratteri (anche se un SSN è strettamente composto da cifre, le cifre sono trattate come caratteri) e sarebbe specificato come CHAR (9). Utilizzare il tipo di dati VARCHAR2 per memorizzare dati alfanumerici di lunghezza variabile. Ad esempio, il nome o l'indirizzo di un cliente varierà notevolmente 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 diversi, tra cui CLOB (oggetto grande carattere) e BLOB (oggetto grande binario). Le colonne di questi tipi di dati possono memorizzare dati non strutturati inclusi testo, immagini, video e dati spaziali. Il tipo di dati CLOB può memorizzare fino a otto terabyte di dati di caratteri utilizzando il set di caratteri del database CHAR. Il tipo di dati BLOB viene utilizzato per memorizzare oggetti binari di grandi dimensioni non strutturati come quelli associati a dati di immagini e video in cui 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 oggetti di grandi dimensioni con caratteri nazionali multibyte impostati da 8 TB a 128 TB. Il valore del tipo di dati BFILE funziona come un localizzatore di file o un puntatore a un file sul file system del server. La dimensione massima del file supportata è compresa tra 8 TB e 128 TB.

Vincoli

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

La sintassi seguente può essere utilizzata per imporre vincoli a livello di colonna.

Sintassi:

column [data type] [CONSTRAINT constraint_name] constraint_type

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

Vincolo NOT NULL

Un vincolo 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, Oracle RDBMS non consentirà l'archiviazione delle righe nella tabella dei dipendenti che violano questo vincolo. può essere definito solo a livello di colonna e non a livello di tabella.

Sintassi:

COLUMN [data type] [NOT NULL]

Vincolo UNICO

A volte è necessario imporre l'unicità per un valore di colonna che non è una colonna di chiave primaria.Il vincolo UNIQUE può essere utilizzato per applicare questa regola e Oracle rifiuterà tutte le righe che violano il vincolo univoco.Il vincolo unico garantisce che i valori di colonna siano distinti , senza duplicati.

Sintassi:

Column Level:

COLUMN [data type] [CONSTRAINT <name>] [UNIQUE]

Table Level: CONSTRAINT [nome vincolo] UNIQUE (nome colonna)

Nota: Oracle crea internamente un indice univoco per evitare la duplicazione nei valori delle colonne. Gli indici saranno discussi più avanti in PL / SQL.

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

In caso di chiave univoca composta, deve essere definita a livello di tabella come di seguito.

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 identifichi in modo univoco le righe di dati archiviate nella tabella. Questa colonna o insieme di colonne è indicata come chiave primaria. La maggior parte delle tabelle ha una singola colonna come chiave primaria. le colonne chiave sono limitate a valori NULL e duplicati.

Punti da notare -

Una tabella può avere solo una chiave primaria.
È possibile raggruppare più colonne sotto una chiave primaria composta.
Oracle crea internamente un indice univoco per evitare la duplicazione nei valori delle colonne. Gli indici saranno discussi più avanti in PL / SQL.

Sintassi:

Column level:

COLUMN [data type] [CONSTRAINT <constraint name> PRIMARY KEY]

Table level:

CONSTRAINT [constraint name] PRIMARY KEY [column (s)]

L'esempio seguente mostra come utilizzare il vincolo PRIMARY KEY a livello di colonna.

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

Il seguente esempio mostra come definire la chiave primaria composita utilizzando il vincolo PRIMARY KEY a livello di tabella.

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

Chiave esterna

Quando due tabelle condividono la relazione padre figlio in base a una colonna specifica, la colonna di unione nella tabella figlio è nota come chiave esterna.Questa proprietà della colonna corrispondente nella tabella padre è nota come integrità referenziale. essere null o devono essere i valori esistenti della tabella padre. Si noti che solo le colonne della chiave primaria della tabella di riferimento sono idonee per applicare l'integrità referenziale.

Se una chiave esterna è definita sulla colonna nella tabella figlia, Oracle non consente l'eliminazione della riga padre, se contiene righe figlio.Tuttavia, se viene fornita l'opzione ON DELETE CASCADE al momento della definizione della chiave esterna, Oracle elimina tutte le righe secondarie durante l'eliminazione della riga padre. Allo stesso modo, ON DELETE SET NULL indica che quando una riga nella tabella padre viene eliminata, i valori della chiave esterna sono impostati su null.

Sintassi:

Column Level:

COLUMN [data type] [CONSTRAINT] [constraint name] [REFERENCES] [table name (column name)]

Table level:

CONSTRAINT [constraint name] [FOREIGN KEY (foreign key column name) REFERENCES] [referenced table name (referenced column name)]

L'esempio seguente mostra come utilizzare il vincolo FOREIGN KEY a livello di colonna.

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

Usage of ON DELETE CASCADE clause

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

Verifica vincolo

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

Oracle consente a una singola colonna di avere più di un vincolo CHECK. In effetti, non esiste un limite pratico al numero di vincoli CHECK che possono essere definiti per una colonna.

Sintassi:

Column level:

COLUMN [data type] CONSTRAINT [name] [CHECK (condition)]

Table level:

CONSTRAINT [name] CHECK (condition)

L'esempio seguente mostra come utilizzare il vincolo CHECK a livello di colonna.

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

L'esempio seguente mostra come utilizzare il vincolo CHECK a livello di tabella.

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

Istruzione 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 viene utilizzato per eseguire tali azioni.Il comando ALTER fornisce più utilità esclusive per gli oggetti dello schema.Viene utilizzata l'istruzione ALTER TABLE per aggiungere, eliminare, rinominare e modificare una colonna in una tabella.

La seguente istruzione 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

L'istruzione ALTER TABLE di seguito elimina la colonna TESTNEW dalla tabella EMP_NEW

ALTER TABLE EMP_NEW DROP COLUMN TESTNEW;

L'istruzione ALTER TABLE di seguito aggiunge la chiave primaria nella colonna EMPLOYEE_ID.

ALTER TABLE EMP_NEW ADD PRIMARY KEY (EMPLOYEE_ID)

L'istruzione ALTER TABLE di seguito rilascia la chiave primaria.

ALTER TABLE EMP_NEW DROP PRIMARY KEY;

L'istruzione ALTER TABLE seguente cambia la modalità tabella in sola lettura.

ALTER TABLE EMP_NEW READ ONLY;

Tabelle di sola lettura

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

Sintassi:

ALTER TALE [TABLE NAME] READ ONLY

ALTER TALE [TABLE NAME] 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

L'istruzione DROP TABLE viene utilizzata per rimuovere una tabella dal database. La tabella eliminata ei relativi dati non rimangono più disponibili per la selezione. La tabella eliminata può essere ripristinata utilizzando l'utilità FLASHBACK, se disponibile in recyclebin. L'eliminazione di una tabella elimina l'indice e i trigger ad essa associati.

Sintassi:

DROP TABLE [TABLE NAME] [PURGE]

L'istruzione seguente lascerà cadere il tavolo e lo inserirà nel cestino.

DROP TABLE emp_new;

L'istruzione seguente lascerà cadere il tavolo e lo scaricherà anche dal cestino.

DROP TABLE emp_new PURGE;

↰ Previous page Next page ↱

Utilizzo di istruzioni DDL