Creare, modificare, stergere tabele

CREATE TABLE
ALTER TABLE
DROP TABLE
COMMENT

CREATE TABLE

Observaţie. Relativ la gestiunea tabelelor se vor face completări la cursurile unde se vor descrie următoarelor informaţii: baze de date orientate obiect, tipuri de date XML, gestiunea datelor LOB, optimizarea interogărilor.

In continuare se va face referire numai la tabele din baze de date relaţionale. O descriere completă a instrucţiunii CREATE TABLE se găseşte în documentaţie.
Sintaxa instrucţiunii este:

CREATE [GLOBAL TEMPORARY] TABLE [ schema.]tabel
    {(definitie_coloana [, definitie_coloana]... [, restrictie_tabel] ... ) 
     | AS (instructiune_SELECT)
    }
    [ON COMMIT { DELETE | PRESERVE } ROWS]
    [optiuni_de_memorare]

unde:
definitie_coloana este: nume_coloana tip_data [DEFAULT expr] [restrictie_coloana]

Pentru tip_data se va consulta fişierul Datatypes.htm.
Observaţie. Incepând cu versiunea Oracle 11g apar coloane virtuale (coloane calculate din valorile altor coloane).

Cu opţiunea DEFAULT se precizează valoarea implită în cazul în care la o înserare nu se atribuie valoare coloanei. Sunt unele restricţii privind modul de construire a expresiei din această opţiune: expresia să aibă o valoare de tipul coloanei, se pot folosi numai anumite funcţii SQL.

Restrictie_coloana poate fi de forma:

[CONSTRAINT nume_restrictie]
{NOT NULL 
  | UNIQUE 
  | PRIMARY KEY 
  | cheie_externa 
  | CHECK (conditie)
}
[stare_restrictie]

unde cheie_externa este de forma:

REFERENCES [schema.]{nume_tabel | nume_view } [(coloana)] [ON DELETE {CASCADE | SET NULL}]

Restricţia pentru o coloană (precizată mai sus) poate avea un nume şi este de tipul:

Valorile nu pot fi nule
Valorile sunt distincte (unice)
Valorile sunt distincte şi formează cheia primară a tabelului
Coloana este o cheie externă şi face referire la o coloană dintr-un tabel sau view. Se poate preciza modul de tratare a operaţiilor de ştergere.
Verifică o condiţie, care se poate evalua imediat. In "conditie" se poate face referire la mai multe coloane din tabel. O înregistrare (linie) din tabel va fi acceptată dacă valoarea condiţiei este TRUE sau NULL. In condiţie nu se pot folosi pseudocoloanele CURVAL, NEXTVAL, LEVEL, ROWNUM, precum şi funcţiile SYSDATE, USER, UID, USERENV, sau valori care se referă la alte înregistrări.

Observaţie. Pentru o coloană pot exista mai multe restricţii (de exemplu, una UNIQUE, mai multe CHECK, etc.).

Restricţia pentru tabel este definită astfel:

[CONSTRAINT nume_restrictie ]
{UNIQUE (coloana [, coloana ]...)
 | PRIMARY KEY (coloana [, coloana ]...)
 | FOREIGN KEY (coloana [, coloana ]...) cheie_externa_tabel
 | CHECK (conditie)
} 
[stare_restrictie]

unde cheie_externa_tabel este de forma:

REFERENCES [schema.]{nume_tabel | nume_view } [(coloana [,coloana]...)] [ON DELETE { CASCADE | SET NULL}]

Restricţiile unui tabel pot fi adăugate sau şterse, ele nu pot fi modificate (nu se poate schimba definiţia), dar ele pot fi activate/dezactivate. La definirea tabelului se poate specifica o stare în care se află restricţia. Lista stărilor este:

{[[NOT] DEFERRABLE ] 
 | [INITIALLY {IMMEDIATE | DEFERRED}] 
 | [ENABLE | DISABLE]
 | [VALIDATE | NOVALIDATE]
 | [RELY | NORELY] 
 | [using_index_clause] 
 | [exceptions_clause]
}

Vom preciza câteva dintre stările restricţiilor.

[NOT] DEFERRABLE (implicit este "NOT DEFERRABLE") precizează că restricţia se verifică la fiecare instrucţiune de modificare a datelor. Prin "DEFERRABLE" această verificare se face fie la fiecare instrucţiune de modificare a datelor, fie la sfârşitul fiecărei tranzacţii (situaţia se va deduce din opţiunea următoare).
Cu instrucţiunea "ALTER TABLE ... MODIFY nume_restricţie" nu se poate modifica starea DEFERRABLE/ NOT DEFERRABLE a unei restricţii (se poate schimba starea prin eliminarea cu opţiunea DROP şi crearea acesteia din nou)
Pentru INITIALLY valoarea implicită este "INITIALLY IMMEDIATE" şi precizează că implicit verificarea restricţiei se face după fiecare instrucţiune de modificare a datelor; iar prin "INITIALLY DEFERRED" se precizează că implicit verificarea restricţiei se face la sfârşitul fiecărei tranzacţii.
Starea INITIALLY IMMEDIATE/DEFERRED a unei liste de restricţii care au starea DEFERRABLE poate fi schimbată pentru tranzacţia curentă prin comanda:
```
SET CONSTRAINT[S]  {ALL | lista_restrictii}  stare
```
unde cu ALL se precizează toate restricţiile cu starea DEFERRABLE din tranzacţia curentă. Opţiunea "stare" poate fi IMMEDIATE sau DEFERRED.
Opţiunea ENABLE precizează activarea restricţiei, iar DISABLE dezactivarea ei (nu se verifică restricţia)
Cu VALIDATE se precizează că toate datele din tabel trebuie să verifice restricţia, iar NOVALIDATE precizează că pot exista date introduse anterior şi care nu verifică restricţia. Se foloseşte la modificarea tabelului (când pot exista date în el).

Dintre opţiunile de memorare amintim:

[ { PCTFREE integer
  | PCTUSED integer
  | TABLESPACE nume_tablespace
  | INITRANS integer
  | MAXTRANS integer
  | clauze_de_memorare
  }...
]

PCTFREE şi PCTUSED se referă la blocurile în care se memorează tabelul.
TABLESPACE precizează numele spaţiului tabel unde se memorează tabelul.
INITRANS şi MAXTRANS se referă la tranzacţii.

Clauzele de memorare pot fi:

STORAGE
({ INITIAL dimensiune
 | NEXT dimensiune
 | MINEXTENTS integer
 | MAXEXTENTS { integer | UNLIMITED }
 | max_dimensiune
 | PCTINCREASE integer
 | FREELISTS integer
 | FREELIST GROUPS integer
 | OPTIMAL [ dimensiune | NULL ]
 | BUFFER_POOL { KEEP | RECYCLE | DEFAULT }
 | ENCRYPT
 } ...
)

unde "size_clause" este de forma: integer [ K | M | G | T | P | E ]
Valorile acestor parametri se folosesc pentru determinarea extensiilor şi segmentelor în care se memorează tabelul. Semnificaţia lor depinde de tipul spaţiului tabel folosit.

Exemplu:

CREATE TABLE tabel 
  (.....) 
  STORAGE (INITIAL 100K NEXT 50K MINEXTENTS 1 MAXEXTENTS 50 PCTINCREASE 5);

Se alocă spaţiu conform valorilor din opţiunea STORAGE:

MINEXTENTS = 1 => se alocă un segment la creare.
INITIAL = 100K => dimensiunea primei extensii este 100 kilobytes.
Dacă tabelul nu încape în prima extensie, atunci se alocă a doua, de 50K, conform valorii NEXT = 50K.
Dacă tabelul nu încape în primele două extensii, atunci se aloca a treia extensie.
Deoarece PCTINCREASE = 5, dimensiunea fiecărei extensii noi va fi cu 5% mai mare decât precedenta.
Cu MAXEXTENTS = 50 se precizează că se pot aloca maximum 50 extensii pentru tabel.

Cu "GLOBAL TEMPORARY" în CREATE TABLE se declară un tabel pentru care se păstrează în dicţionar numai definiţia, datele se pierd la sfârşitul tranzacţiei sau la sfârşitul sesiunii de lucru. Astfel de tabele sunt utile pentru optimizarea interogărilor (se păstrează date temporare în ele, utile la operaţii de join).

Opţiunea ON COMMIT { DELETE | PRESERVE } ROWS se referă la tabele temporare şi precizează că datele vor fi şterse din tabel la sfârşitul tranzacţiei (ON COMMIT DELETE ROWS, care este şi valoarea implicită), sau vor fi păstrate până la sfârşitul sesiunii de lucru (ON COMMIT PRESERVE ROWS).
Observaţii privind tabelele temporare:

Nu pot fi partiţionate sau clusterizate
Nu se pot folosi la definirea de chei externe
Nu pot avea coloane de tipuri definite de utilizator
Nu pot avea opţiuni de memorare
Nu pot fi folosite la tranzacţii distribuite

Tabel secvenţial indexat

Acest tip de tabel este memorat în întregime ca un index (sub forma unui B-arbore) folosind cheia primară.
Sintaxa de creare este:

CREATE TABLE nume_tabel
(
  ... definire coloane
  CONSTRAINT Nume_restrictie PRIMARY KEY (lista_coloane)
)
ORGANIZATION INDEX;

Aceste tipuri de tabele sunt recomandate (pentru optimizare) la tabele relativ statice (cu puţine operaţii de adăugare/ştergere/actualizare) şi la care interogările se fac în principal după coloanele incluse în cheia primară.

ALTER TABLE

Instrucţiunea ALTER TABLE modifică proprietăţile unui tabel existent. In continuare se descriu câteva variante de utilizare:
Modificare restricţii:

ALTER TABLE [schema.]nume_tabel ADD { restrictie_coloana | restrictie_tabel } 
ALTER TABLE [schema.]nume_tabel MODIFY { CONSTRAINT nume_restrictie | PRIMARY KEY | UNIQUE (coloana) } stare_restrictie 
ALTER TABLE [schema.]nume_tabel RENAME CONSTRAINT nume_vechi TO nume_nou 
ALTER TABLE [schema.]nume_tabel DROP nume_restrictie

Modificare lista de coloane din tabel:

ALTER TABLE [schema.]nume_tabel ADD {(nume_coloana tip_data [DEFAULT expr] [restrictie])} 
ALTER TABLE [schema.]nume_tabel MODIFY (nume_coloana [tip_data] [DEFAULT expr] [restrictie] ) 
ALTER TABLE [schema.]nume_tabel DROP COLUMN nume_coloana 
ALTER TABLE [schema.]nume_tabel RENAME COLUMN nume_coloana_vechi TO nume_coloana_nou

DROP TABLE

Sintaxa instrucţiunii este:

DROP TABLE [schema.]nume_tabel [CASCADE CONSTRAINTS]

Cu opţiunea CASCADE CONSTRAINTS se cere eliminarea restricţiilor care folosesc cheia primară sau cheile unice din acest tabel.

COMMENT

La un tabel, view, sau coloană se poate adăuga un comentariu cu instrucţiunea:

COMMENT ON TABLE [schema.]{nume_tabel | nume_view} IS 'text' 
COMMENT ON COLUMN [schema.]{nume_tabel | nume_viewe}.coloana IS 'text'

Printr-un text de lungime zero se elimină un comentariu ataşat anterior.