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.
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.