Definirea unui tabel

Definirea unui tabel

Exista doua tipuri de tabele : tabele de baza, care sunt memorate fizic in memorie si tabele view (vizualizari), care sunt virtuale si pentru care doar definitia este memorata.

Tipurile de date cele mai frecvent intalnite in SQL*PLUS sunt:

CHAR pentru sir de lungime fixa, daca nu se specifica implicit lungimea minima este 1 iar lungimea maxima este 2000);

VARCHAR2 pentru sir de caractere de lungime variabila (O lungime maxima poate fi specificata, implicit lungimea minima este 1 iar lungimea maxima este 4000);

NUMBER pentru numere in virgula fixa si mobila;

LONG pentru text de lungime variabila pana la 2 GB (gigabytes);

LONG RAW pentru date binare , nu apre in PL/SQL;

DATE pentru date calendaristice, intervalul pentru date este intre 4712 BC si 9999 AD.

BOOLEAN poate avea una din cele trei valori: TRUE, FALSE, NULL;

PLS_INTEGER pentru intregi cu semn cuprinsi intre 147483647 si

BLOB date binare, de lungime maxima 4GB;

BFILE date binare depozitate intr-un fisier extern, pina la 4 GB;

Crearea structurii unui tabel de baza se face prin comanda CREATE TABLE, care are urmatoarea sintaxa:

CREATE TABLE [schema.]table

(column datatype [DEFAULT expr], . );

unde:

schema : este numele utilizatorului care a creat tabela, este optional;

table : numele tabelei;

column : numele unei coloane;

datatype : tipul coloanei si lungimea coloanei (este un tip predefinit existent);

DEFAULT expr : este o optiune prin care se specifica o valoare implicita pentru o anumita coloana, in cazul in care nu se specifica nici o valoare pentru coloana respectiva, (previne introducerea unei valori NULL).

Exista cateva reguli la definirea tabelului cum ar fi:

Numele tabelei si numele coloanelor trebuie sa inceapa cu litera si poate avea lungimea maxima 30;

Numele poate contine numai A-Z, a-z, 0-9, _(underscore), $ si #;

Numele tabelului trebuie sa fie unic in cadrul bazei de date in care il creati;

Numele nu poate fi unul din cuvintele rezervate ale sistemului Oracle sau ale limbajului SQL;

Nu se face deosebirea intre litere mici si litere mari, (deci EMP este aceelasi nume cu emp).

Crearea unui tabel cu denumirea dept se va realiza astfel:

CREATE TABLE dept

(deptno NUMBER (2),

dname VARCHAR2(14),

loc VARCHAR2(13));

Putem crea o tabela copiind informatii dintr-o alta tabela, printr- o interogarea a tabelei respective, adica printr-un subquery, folosind urmatoarea sintaxa:

CREATE TABLE table

[(column (, column . )]

AS subquery;

unde:

table : numele tabelei;

column : numele unei coloane, se pot specifica valorile implicite si constrangerile de integritate;

subquery : este o instructiune SELECT care defineste setul de randuri ce pot fi inserate in noua tabela;

In cazul in care nu se specifica lista de coloane numele coloanelor din tabela vor fi acelasi cu numele coloanelor din subquery. De asemenea, se pot redenumi campurile din noul tabel. Daca se specifica lista de coloane, atunci numarul de coloane din tabela trebuie sa fie egal cu numarul de coloane din subquery.

Un exemplu, in care se va crea o tabela utilizand un subquery, se va scrie astfel:

CREATE TABLE dept30

AS

SELECT empno, ename, sal*12 ANNSAL, hiredate

FROM emp

WHERE deptno = 30;

In exemplu de mai sus s-a creat o tabela care contine detaliile tuturor angajatilor din departamentul 30. Putem verifica existenta tabelei de date noi create si definitia coloanelor tabelei utilizand comanda DESCRIBLE din SQL*PLUS.

Aceasta comanda poate fi folositoare cand trebuie sa creem un tabel similar cu unul existent, care contine date asemanatoare.

La crearea unei tabele pot fi specificate anumite constrangeri declarative prin care se exprima o conditie ce trebuie respectata de toate tuplurile unei relatii. Constrangerilor le pot fi asociate nume unice. Constrangerile declarative pot fi:

Constrangeri de domeniu, care definesc valorile luate de un atribut (NOT NULL, UNIQUE, CHECK);

Constrangeri de integritate a entitatii, care precizeaza cheia primara (PRIMARY KEY);

Constrangeri de integritate referentiala, care asigura coerenta intre cheile primare si cheile externe corespunzatoare (FOREIGN KEY)

Asertiuni, care specifica anumite constrangeri generale referitoare la atribute (ASSERTION).

In Oracle sunt valide urmatoarele constrangeri:

NOT NULL, specifica faptul ca, coloana respectiva nu poate contine valori NULL;

UNIQUE Key, valoarea coloanei sau valorile combinatiilor de coloane trebuie sa fie unica/unice pentru toate tuplurile din tabela.

PRIMARY KEY, defineste cheia primara, care identifica in mod unic fiecare tuplu (rand) din tabela;

FOREIGN KEY, defineste cheia externa, si stabileste o relatie intre coloana respectiva si o coloana a tabelei referite;

CHECK, specifica o conditie care trebuie sa fie indeplinita.

Toate constrangerile sunt stocate in dictionarul de date. Putem afisa constrangerile definite pentru o tabela specificata vizualizand tabela USER_CONSTRAINTS a dictionarului de date.

Daca nu este specificat un nume pentru o constrangere Oracle generteaza un nume unic cu formatul SYS_Cn, unde n este un intreg.

Definirea unei constangeri la crearea unei tabele se realizeaza astfel:

CREATE TABLE [schema.]table

(column datatype [DEFAULT expr],

[column_constraint],

. .

[table_constraint]);

unde:

schema : este numele utilizatorului care a creat tabela, este optional in cazul in care utlizatorul este si detinatorul tabelei ;

table : numele tabelei;

DEFAULT expr : specifica o valoare implicita, daca valoarea a fost omisa in instructiunea INSERT;

column : numele coloanei;

datatype : tipul coloanei si lungimea coloanei;

column_constraint : este o constrangere de integritate definita la nivel de coloana;

table_constraint : este o constrangere de integritate definita la nivel de tabela;

Definirea unei costrangeri la nivel de coloana se realizeaza folosind urmatoatrea sintaxa:

column [CONSTRAINT constraint_name] constaint_type

Definirea unei costrangeri la nivel de tabela se realizeaza astfel:

column, .

[CONSTRAINT constraint_name] constaint_type

(column, . ),

unde:

constraint_name : este numele constrangeri;

constraint_type : este tipul constrangeri;

O constrangere la nivel de coloana se refera la o singura coloana specificata si poate defini orice tip de constrangere, iar o constrangere la nivel de tabela se refera la una sau mai multe coloane si este definita separat de definirea coloanei in tabela, poate defini orice tip de constrangere exceptand NOT NULL.

Vom exemplifica definirea unor constrangeri prin cateva exemple.

a.        Definirea unei constrangeri de cheie primara (PRIMARY KEY) coloanei empno din tabela emp.

CREATE TABLE emp(

empno NUMBER (4),

ename VARCHAR(10),

. ..

deptno NUMBER(7,2) NOT NULL,

CONSTRAINT emp_empno_pk

PRIMARY KEY (empno));

b.       Definirea constrangeri NOT NULL la nivel de coloana pentru coloanele ename si deptno din tabela emp.

CREATE TABLE emp(

empno NUMBER (4),

ename VARCHAR(10) NOT NULL,

job VARCHAR2(9),

mgr NUMBER(4),

hiredate DATE,

sal NUMBER(7,2),

comm NUMBER(7,2),

deptno NUMBER(7,2) NOT NULL);

c.        Definirea constringeri de cheie unica pentru coloana dname si cheie primara pentru coloana deptno din tabela dept.

CREATE TABLE dept(

deptno NUMBER (2),

dname VARCHAR(12),

loc VARCHAR2(13),

CONSTRAINT dept_dname_uk UNIQUE(dname),

CONSTRAINT dept_deptno_pk PRIMARY KEY (deptno));

d.       Definirea unei constrangeri de cheie externa (FOREIGN KEY) pentru coloana deptno din tabela emp, care refera cheia primara deptno din tabela dept.

CREATE TABLE emp(

empno NUMBER (4),

ename VARCHAR(10) NOT NULL,

job VARCHAR2(9),

mgr NUMBER(4),

hiredate DATE,

sal NUMBER(7,2),

comm NUMBER(7,2),

deptno NUMBER(2) NOT NULL);

CONSTRAINT emp_deptno_fk FOREIGN KEY (deptno) REFERENCES dept (deptno) ON DELETE CASCADE );

O cheie externa este definita folosind urmatoarele cuvinte cheie :

FOREIGN KEY defineste coloana din tabela copil, in cadrul constrangeri la nivel de tabela;

REFERENCES identifica tabela si coloana din tabela parinte;

ON DELETE CASCADE indica faptul ca, atunci cand randurile din tabela parinte sunt sterse, vor fi sterse si rindurile dependente din tabela copil (este optionala). Daca nu se specifica aceasta optiune randurile din tabela parinte nu pot fi sterse daca este referinta la tabela copil.

e.        Definirea unei constrangeri care trebuie indeplinita de toate tuplurile din tabela. Coloana deptno din tabela dept trebuie sa aibe valori cuprinse intre 10 si 99, pentru toate randurile din tabela.

CREATE TABLE dept(

deptno NUMBER (2),

dname VARCHAR(12),

loc VARCHAR2(13),

CONSTRAINT dept_deptno_ck CHECK (DEPTNO BETWEEN 10 AND 99));

Modificarea structurii unui tabel se realizeaza cu ajutorul comenzii ALTER TABLE care permite adaugarea sau modificarea unei coloane, activarea, dezactivarea sau suprimarea unor constrangeri.

Comanda ALTER TABLE, in cazul adaugarii unei noi coloane, are sintaxa:

ALTER TABLE table

ADD (column datatype [DEFAULT expr]

[, column datatype] . )

Adaugarea unei noi coloane cu denumirea job la tabela dept30, se va realiza prin:

ALTER TABLE dept30

ADD (job VARCHAR2 (9));

Coloana nou creata a deveni ultima coloana in tabela.

Comanda ALTER TABLE, in cazul modificarii unei coloane, are sintaxa:

ALTER TABLE table

MODIFY (column datatype [DEFAULT expr]

[, column datatype] . )

Modificarea coloanei ename din tabela dept30 (se pot modifica tipul, lungimea si valorile implicite ale coloanei).

ALTER TABLE dept30

MODIFY (ename VARCHAR2 (15));

Comanda ALTER TABLE, in cazul adaugarii unei noi constangeri coloanei, are sintaxa:

ALTER TABLE table

ADD ([CONSTRAINT constraint] type (column);

Adaugarea unei constrangeri de cheie externa pentru coloana mgr din tabela emp, care refera coloana empno din tabela emp (deci nu putem introduce codul sefului mgr, pentru un angajat daca seful nu exista in tabela empno) .

ALTER TABLE emp

ADD CONSTRAINT emp_mgr_fk

FOREIGN KEY(mgr) REFERENCES emp(empno);

Comanda ALTER TABLE, in cazul dezactivarii unei constangeri, are sintaxa:

ALTER TABLE table

DISABLE CONSTRAINT constraint [CASCADE];

Clauza CASCADE dezactiveaza dependentele constrangeri de integritate.

Dezactivarea constrangeri emp_empno_pk este exemplificta prin secventa:

ALTER TABLE emp

DISABLE CONSTRAINT emp_mgr_fk CASCADE;

Comanda ALTER TABLE, in cazul activarii unei constangeri, are sintaxa:

ALTER TABLE table

ENABLE CONSTRAINT constraint ;

Activarea constrangeri emp_empno_pk este exemplificta prin secventa:

ALTER TABLE emp

ENABLE CONSTRAINT emp_mgr_fk;

Comanda ALTER TABLE, in cazul stergerii unei constangeri, are sintaxa:

ALTER TABLE table

DROP PRIMARY KEY | UNIQUE (column) |

CONSTRAINT constraint [CASCADE];

Stergerea constrangeri de cheie primara din tabela dept precum si stergerea constrangeri de cheie externa asociata din tabela emp.

ALTER TABLE dept

DROP PRIMARY KEY CASCADE;

Putem vizualita toate constrangerile pe o tabela interogand tabela USER_CONSTRAINTS.

Afisarea tuturor constrangerilor pe tabela emp, se realizeaza prin secventa:

SELECT constraint_name, constraint_type,

search_condition

FROM user_constraints

WHERE table_name = 'EMP';

Vizualizarea coloanelor asociate constangerilor din tabela emp se realizeaza interogand tabela USER_CONS_COLUMNS.

SELECT constraint_name, column_name,

FROM user_cons_columns

WHERE table_name = 'EMP';

Stergerea unei tabele se realizeaza cu ajutorul comenzii DROP TABLE care are sintaxa:

DROP TABLE table;

unde: table este numele unei tabele

Schimbarea numelui unei tabele, a unei vizualizari, sau a unei secvente se relizeaza cu comanda RENAME care are sintaxa:

RENAME old_name TO new_name;

unde:

old_name este numele vechi

new_name este numele nou

Stergerea unei tabele si eliberarea spatiului ocupat de aceea tabela se realizeaza cu comanda TRUNCATE, care are sintaxa:

TRUNCATE TABLE table;

unde: table este numele unei tabele

Comanda DELETE TABLE poate sterge randuri dintr-o tabela dar nu poate elibera spatiul ocupat de aceea tabela.

Se poate adauga comentarii unei tabele sau unei coloane prin utilizarea comenzi COMMENT care are sintaxa:

COMMENT ON TABLE table | COLUMN table.column

IS 'text';

unde: table numele tabelei

column este numele unei coloane dintr-o tabela

text este textul din comentariu

Comentariul este salvat in dictionarul de date si poate fi vizualizat in una din urmatoarele tabele ale dictionarului de date in coloana COMMENTS:

ALL_COL_COMMENTS

USER_COL_COMMENTS

ALL_TAB_COMMENTS

USER_TAB_COMMENTS

Putem sterge un comentariu dintr-o tabela prin setarea acestuia la un sir vid (' ').

Stergerea comentariului pentru tabela emp se va realiza prin urmatoarea secventa:

COMMENT ON TABLE emp IS ' ';