Laborator 10 - Crearea, definirea, modificarea și ștergerea obiectelor din baza de date
Crearea unei tabele
Comenzile pentru crearea și definirea de structuri tabelare sunt comenzi de definire a datelor (Data Definition Language - DDL) și permit crearea dar și relaționarea lor într-o bază de date.
Structura unei tabele este data de următoarele specificații de definire:
Sintaxa
CREATE TABLE [ schema.]TABLE_NAME [table_constraint]
COLUMN datatype [DEFAULT expr] [column_constraints]
[table_constraints] [TABLESPACE tablespace]
[storage parameters]
[ENABLE|DISABLE CLAUSE] [AS subquery]
Sintaxa completă
schema – este schema unde se creează tabela (specifică utilizatorul și baza de date)
tabel_name – este numele tabelei
column – este numele coloanei
datatype – reprezintă tipul coloanei
DEFAULT expr – specifică valoarea implicită a coloanei
column_constraint – definește constrângerile de integritate pe coloană
tablespace - specifică în ce tablespace al bazei de date se creează tabela
storage_parameters - definește parametrii de creare și pot fi:
PCTFREE - procentaj de spațiu rezervat pentru update
PCTUSED - procentaj minim folosit pentru un bloc de date
INITRANS - numărul inițial de tranzacții pentru fiecare bloc (1-255)
MAXTRANS - numărul maxim de tranzacții concurente (1-255)
CLUSTER - specifică dacă tabela face parte dintr-un cluster
ENABLE/DISABLE clause - activare / dezactivare de constrângeri
AS subquery - inserare de date dintr-o altă tabelă obținute printr-o interogare
Opțiunea DEFAULT
Este folosită pentru a da o valaore implicită unei coloane.
Previne apariția de valori NULL atunci când o linie este inserată fără o valoare din coloană.
Valorile implicite pot fi șiruri de caractere, numere, și funcții dar nu poate conține numele unei alte coloane.
Tipurile de date care pot fi asociate coloanelor unei tabele pot fi :
Câteva dintre cele mai uzuale tipuri de date sunt:
NUMBER - număr real de dimensiune variabilă (maxim 38 cifre)
NUMBER(n) - număr întreg de n cifre
NUMBER(n,m) - număr real de n cifre, dintre care m zecimale
CHAR(n) - șir de caractere de lungime fixă n (1-2000)
NCHAR(n) - analog cu CHAR, dar poate stoca șiruri de caractere Unicode
VARCHAR2(n) - șir de caractere de lungime variabilă n
NVARCHAR2(n) - analog cu VARCHAR dar poate stoca șiruri de caractere Unicode
LONG - șir de caractere de maxim 2 la puterea 31 octeți
LONG RAW - similar cu LONG dar conține date binare
ROWID - poate stoca identificatorul unei linii din tabelă
DATE - data calendaristică
TIMESTAMP(n) - extensie pentru tipul DATE care conține și fracțiuni de secundă pe n zecimale
Reguli pentru crearea unei tabele:
Userul trebuie să aibă drepturi de crearea a unei tabele.
Numele trebuie să fie unic în contul în care se creeaza și nu este case sentitive.
Numele trebuie să aibă maxim 30 de caractere continue, să înceapă cu o literă și să nu fie cuvânt rezervat Oracle.
O tabelă poate fi creată oricând dar nu poate fi alterată când este accesată de un alt user.
La creare nu este necesar să se specifice dimensiunea tabelei dar trebuie estimat ce spațiu va ocupa în tablespace.
Structura tabelelor poate fi modificată și ulterior (prin adăugarea sau ștergerea coloanelor, constrângerilor, indecși, etc.).
Dacă dimensiunea ințială este insuficientă i se alocă automat mai mult spațiu în limita tablespace-ului (care la rândul lui poate fi extins cu un nou data file).
Ex. 1. Să se creeze o tabelă pentru evindența studentilor.
Crearea unei tabele printr-o cerere select
Sunt două posibilități de crea o tabelă folosind o astfel de construcție:
Comanda CREATE TABLE nu conține descrierea structurii tabelare:
Se creează o tabelă cu o structură identică cu câmpurile specificate în cererea SELECT.
Tipurile coloanelor se păstrează.
Dacă cererea conține o expresie sau funcție trebuie să i se atribuie un alias valid.
Noua tabelă nu moștenește nicio constrângere de integritate de la vechile tabele, cu excepția NOT NULL.
Ex. 2. Să se creeze o tabelă care va conține veniturile angajațiilor din departamentul 20.
Comanda CREATE TABLE conține descrierea structurii tabelare:
Numărul coloanelor trebuie să coincidă cu numărul coloanelor din cererea SELECT.
Nu se va specifica tipul coloanelor.
Nu se pot specifica tipuri de coloane în comandă.
Se poate adăuga constrângerea NOT NULL și opțiunea DEFAULT în comandă.
Dacă cererea conține o expresie sau funcție nu trebuie să i se atribuie un alias deoarece definirea coloanei respective este impusă de comandă și nu de cerere.
Noua tabelă nu moștenește nicio constrângere de integritate de la vechile coloane.
Ex. 3. Să se creeze o tabelă cu o prima de 15% din venitul lunar pentru angajații din departamentul 30.
Constrângeri de integritate
Constrângerile de integritate sunt anumite reguli care trebuie respectate la nivel de tabelă sau în relațiile cu alte tabele.
Aceste reguli sunt verificate automat în cazul operațiilor de inserare, ștergere și modificare și, în cazul în care nu se validează, se generează o eroare și tranzacția nu se efectuează.
Constrângerile de integritate pot fi:
NOT NULL - înregistrările nu pot conține valori nule
UNIQUE - definește o cheie unică pe una sau mai multe coloane (nu pot fi mai multe înregistrări cu aceleași valori pe coloanele respective
PRIMARY KEY - definește o cheie primară la nivel de coloană sau tabelă (nu pot fi mai multe înregistrări cu aceeași cheie primară)
FOREIGN KEY - definește o cheie externă (tabela se relaționează cu altă tabelă pe o cheie unică sau cheie primară)
CHECK - forțează o condiție pe coloană
Caracteristici ale constrângerilor de integritate:
Fiecare constrângere va avea un nume dat de user sau generat de sistem.
Constrângerile pot fi activate sau dezactivate cu comanda ALTER TABLE.
Constrângerile pot fi adăugate sau șterse și după ce o tabela a fost creată.
Informațiile legate de constrângeri se păstrează în dicționarul de date.
Dacă o constrângere are nume dat de utilizator atunci este mai ușor să fie modificata.
Constrângerea NOT NULL
Se aplică la nivel de coloane și verifică dacă înregistrările au valorea null pe coloanele respective, forțând un cod de eroare care anulează tranzacția.
Cand se creează constrangeri pe o cheie primară se creează automat și o constrângere NOT NULL pe coloanele respective (o cheie primara nu trebuie să conțină valori nule pe coloanele care o definesc).
Sintaxa:
column_name datatype [CONSTRAINT constraint_name] NOT NULL
Constrângerea UNIQUE
Se folosește când vrem ca o coloană, sau perechi de coloane, să nu conțină valori duplicare.
Verificarea se face numai pentru înregistrările cu valori nenule deoarece constrângerea permite inserarea de valori nule în coloanele respective.
În mod automat se creează și un index pe coloanele define cheie unice, ceea ce duce la mărirea vitezei de integritate pe tabelă.
Dacă constrângerea se face pe o pereche de coloane atunci setul trebuie să fie unic.
Sintaxă la nivel de coloană:
column_name datatype [CONSTRAINT constraint_name] UNIQUE
Sintaxă la nivel de tabelă:
[, CONSTRAINT constraint_name] UNIQUE (col1[, col2, [...]])
Constrângere PRIMARY KEY
Se folosește pentru definirea cheii primare pe o coloană sau pereche de coloane.
O tabelă poate avea o singură constrângere de tip PRIMARY KEY și nu acceptă valori nule pentru colona sau perechea de coloane care o definesc.
Când se creează o cheie primară se creează în mod automat și o constângere de timp NOT NULL și UNIQUE.
Când se creează o cheie primară se creează în mod automat și un index pentru a scurta timpul de răspuns în cazul unei interogări.
Sintaxa la nivel de coloană:
column_name datatype [CONSTRAINT constraint_name] PRIMARY KEY
Sintaxă la nivel de tabelă:
[, CONSTRAINT constraint_name] PRIMARY KEY(col1[, col2, [...]])
Ex. 4. Să se creeze un nomenclator de funcții în care fiecare funcție să aibă un cod unic.
Ex. 5. Să se creeze o tabelă pentru a păstra date despre o persoană. Cheia primară să se facă pe seria CI, cod CI și CNP.
Constrângerea FOREIGN KEY
Sintaxa la nivel de coloană:
column_name datatype [CONSTRAINT constraint_name]
REFERENCES TABLE(COLUMN)
[ON DELETE CASCADE | DELETE SET NULL]
Sintaxă la nivel de tabelă:
[, CONSTRAINT constraint_name] FOREIGN KEY(col1[, col2, [...]])
REFERENCES TABLE(COLUMN)
[ON DELETE CASCADE | DELETE SET NULL]
Reguli pentru FOREIGN KEY
Inserarea unei linii într-o tabelă relațională (pe care am definit FOREIGN KEY) nu se poate face dacă valoarea pe care vrem să o inserăm pe coloana pe care avem foreign key nu există în tabela referită (în care am definit PRIMARY KEY sau UNIQUE) corespunzător coloanelor de relaționare. Se poate totuși insera valoarea null.
Ștergerea unei linii din tabela de referință nu se poate face atâta timp cât există linii relaționale pe linia respectivă în tabela relațională.
Regulile de mai sunt valabile și în cazul relaționării pe coloane.
Opțiunea ON DELETE CASCADE
Pentru a putea șterge în tabela de referință linii referite în alte tabele se folosește opțiunea ON DELETE CASCADE. În acest caz, când se șterge o linie în tabela de referință se vor șterge toate liniile din tabelele relaționate care sunt în relație cu linia respectivă.
În cazul unei tabele relaționată cu ea însăși, ștergerea unei linii care este referită duce la apariția de valori nule pe toate coloanele în liniile relaționate. Spre exemplu, în tabela angajati, când se șterge linia aferentă unui șef, toți angajații care au șeful respectiv vor primi valoare null pe coloana id_sef.
Folosind opțiunea ON DELETE SET NULL, coloanele de relație din tabela relaționată devin nule și nu sunt șterse liniile relaționate atunci când se șterge o linie din tabela de referință.
Ex. 6. Să se creeze tabela angajați și să se adauge constrângerile de integritate de tip FOREIGN KEY și PRIMARY KEY.
Constrângerea CHECK
Se folosește pentru a forța valorile unei coloane să verifice o condiție
Condiția poate să conțină și funcții, cu unele excepții (sysdate, user, etc.)
Sintaxa la nivel de coloană:
column_name datatype [CONSTRAINT constraint_name] CHECK (expr)
Sintaxă la nivel de tabelă:
[, CONSTRAINT constraint_name] CHECK (expr)
Ex. 7.Să se creeze tabela angajați astfel înât să se verifice dacă salariul este mai mare ca 0 , comisionul nu depășeste salariul și numele este scris doar cu litere mari.
Comanda ALTER TABLE
Comanda ALTER TABLE este folosită atunci când structura unei tabele trebuie modificată, sau unele proprietăți trebuie să fie șterse sau adăugate.
Sintaxa:
ALTER TABLE [schema.]TABLE_NAME
ADD {(COLUMN datatype) [DEFAULT EXP] | (column_constaints) } |
MODIFY {(COLUMN datatype) [DEFAULT EXP] | (column_constaints) } |
DROP drop_clause |
ENABLE enable_clause |
DISABLE disable_clause |
ALLOCATE EXTENT SIZE INTEGER [K, M]
DATAFILE ‘filename’
INSTANCE INTEGER
[storage_parameters]
Pentru a adăuga o nouă coloană se folosește comanda:
Comanda DROP TABLE
Comanda DROP TABLE este folosită pentru a șterge tabela din dictionarul bazei de date.
Sintaxa:
DROP TABLE TABLE_NAME
Comanda TRUNCATE
Comanda TRUNCATE este folosită pentru golirea unui tabel.
Această comandă șterge toate liniile dintr-o tabelă.
După ce am dat această comandă, comnda ROLLBACK nu funcționează.
Această comandă este mult mai rapidă decât comanda DELETE.
Sintaxa:
TRUNCATE TABLE [schema.]TABLE [{DROP|REUSE} STORAGE]
DROP STORAGE – eliberează spațiul rezultat din ștergerea liniilor.
REUSE STORAGE – păstrează spațiul rezultat din ștergerea liniilor alocate tabelei.
Comanda INSERT
Această comandă este folosită pentru inserarea de linii într-o tabelă.
Sintaxa:
INSERT INTO [shema.]{TABLE_NAME|view_name} [(col_1, col_2, ...)]
{VALUES(exp_1, expr_2, ...) | subquery}
table_name|view_name – numele tabelei sau view-ului în care se face inserarea
col_i – numele de coloană în care se face inserare
expr_i – valorile aferente coloanelor
subquery – este o subcerere care va întoarce valori ce vor fi utilizate la inserare
Comanda UPDATE
Comanda UPDATE este folosită pentru a actualiza datele din baza de date.
Sintaxa:
UPDATE [shema.]{TABLE_NAME|view_name}
SET
column_1 = {expr | subquery}, ... , column_n = {expr | subquery} |
{column_1, ... , column_n} = subquery
[WHERE conditions]
table_name|view_name – numele tabelei sau view-ului care se modifică
column_i – numele de coloană în care se modifică
expr – valorile aferente coloanelor
subquery – este o subcerere care va întoarce o linie și un număr de coloane egal cu numărul de coloane care va fi modificat
conditions – condiția care trebuie îndeplinită pentru modificarea datelor
Când se face actualizarea datelor într-o tabelă se verifică automat și constrângerile de integritate definite pe tabela respectivă, altfel se va genera un cod de eroare și tranzacția va eșua.
Situații în care pot apărea erori:
Noile valori sunt duplicare de cheie primară sau unică.
Actualizarea valorii cu o valoare nulă când coloana este NOT NULL.
Valorile noi nu respectă constrângerea CHECK.
Valorile noi nu respectă constrângerea FOREIGN KEY.
Valorile vechi erau referite de alte tabele printr-o constrângere FOREIGN KEY.
Subcererea returnează mai multe înregistrări.
Comanda DELETE
Comanda DELETE este folosită pentru ștergerea liniilor dintr-o tabelă sau view.
Sintaxa:
DELETE FROM [schema.] {TABLE_NAME|view_name}
WHERE conditions
Clauza WHERE permite folosirea de subcereri.
SEQUENCE
O secvență este un obiect al bazei de date care generează numere intregi unice.
O secvență este folosită pentru a genera valori pentru cheia primară.
Sintaxa:
CREATE SEQUENCE [schema. ]sequence_name
[[ INCREMENT BY INTEGER ]
| [START WITH ] INTEGER
| [ MAXVALUE INTEGER | NOMAXVALUE ]
| [ MINVALUE INTEGER | NOMINVALUE ]
| [ CYCLE | NOCYCLE ]
| [ CACHE INTEGER | NOCACHE } ]
| [ ORDER | NOORDER ] ]
sequence_name – numele secvenței
INCREMENT BY – specifică incrementul
START WITH – specifică valoare de start
MAXVALUE – specifică valoarea maximă a secvenței
NOMAXVALUE – specifică ca valoarea maximă este 10^27 pentru secvențe crescătoare și -1 pentru cele descrescătoare, această valaoare este implicită
MINVALUE – specifică valoare minimă a secvenței
NOMINVALUE – specifică ca valoarea minimă este 1 pentru secvențe crescătoare și -10^26 pentru cele descrescătoare, această valaoare este implicită
CYCLE – se folosește atunci când se dorește să se genereze valori chiar dacă s-a ajuns la valoarea minimă sau maximă ;
NOCYCLE – se folosește atunci când nu se dorește să se genereze valori atunci când se ajunge la valoarea minimă sau maximă;
CACHE – specifică câte valori se vor prealoca și se vor păstra în memorie ;
NOCACHE – specifică să nu se pre-aloce valori și să nu se păstreze în memorie;
ORDER – garantează că valorile se generează în ordine, folosit pentru RAC (Oracle Real Application Clusters);
NOORDER – nu garantează că valorile se generează în ordine.
Comanda ALTER SEQUENCE
Sintaxa:
ALTER SEQUENCE [ schema. ] sequence_name
{ INCREMENT BY INTEGER
| { MAXVALUE INTEGER | NOMAXVALUE }
| { MINVALUE INTEGER | NOMINVALUE }
| { CYCLE | NOCYCLE }
| { CACHE INTEGER | NOCACHE }
| { ORDER | NOORDER }
Comanda DROP SEQUENCE
DROP SEQUENCE sequence_name
INDEX
Un index este o structură care îmbunătățește viteza de acces la datele dintr-o bază de date.
Indecșii se creează automat în momentul în care se creează o cheie primară sau una unică.
Sintaxa:
CREATE
[ONLINE|OFFLINE]
[UNIQUE|FULLTEXT|SPATIAL] INDEX index_name
ON TABLE_NAME(column_name)
index_options
Ștergerea unui index:
DROP INDEX index_name
VIEW
O vedere este o tabelă logică care extrage date dintr-o tabelă propriu-zisă sau dintr-o alta vedere.
O vedere nu are date proprii, ci este ca o fereastră prin care datele din tabele pot fi actualizate sau vizualizate.
O vedere poate să conțină toate datele dintr-o tabelă (sau mai multe) sau anumite coloane.
Sintaxa:
CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW view_name
[ (alias [, alias]...) ]
AS subquery
[WITH CHECK OPTION [CONSTRAINT CONSTRAINT] ]
[WITH READ ONLY [CONSTRAINT CONSTRAINT] ];
Pentru a șterge o tabelă atunci se va folosi comanda DROP.
DROP VIEW view_name
Pe un VIEW care extrage date dintr-o singură tabelă se pot utiliza comenzile INSERT, UPDATE, DELETE.
Dacă VIEW-UL extrage informații din mai multe tebele atunci comenzile INSERT, UPDATE, DELETE nu se pot folosi.
Alte comenzi
ALTER DATABASE – pentru diverse operațiuni asupra bazei de date cum ar fi: deschiderea, inchiderea, montarea, crearea de noi fisiere de date, salvarea fisierelor de control, etc.
ALTER DATABASE {MOUNT | OPEN [RESETLOGS]}
ALTER SESSION – pentru schimbarea parametrilor unei sesiuni cum ar fi formatul de date, limba de afișare a mesajelor, setul de caractere utilizare, etc.
Simbarea formatului de afișare a datei calendaristice
ALTER SESSION SET NLS_DATE_FORMAT='dd-mm-yyyy';
ALTER SYSTEM – pentru schimbarea parametrilor de sistem cum ar fi: numărul maxim de useri, numărul maxim de sesiuni, activarea/dezactivarea unor parametri, închiderea unei sesiuni;
Schimbarea numărului maxim de sesiuni pentru instanța curentă la 60 și avertizarea la 50 de sesiuni (trebuie să aveți privilegiile necesare pentru a face modificări).
ALTER SYSTEM SET LICENSE_MAX_SESSIONS = 60 LICENSE_SESSIONS_WARNING = 50;
ALTER TABLESPACE – pentru modificarea parametrilor unui tablespace cum ar fi: adăugarea de noi fișiere de date, modificarea vechilor parametri, etc.
Extinderea unui tablespace cu un nou fișier de date:
ALTER TABLESPACE ADD DATAFILE 'E:\student\file.dat'
ALTER USER – pentru modificarea parametrilor de user cum ar fi: schimbarea parolei, alocarea de roluri, specificarea tablespace-ului implicit, cota alocată în tablespace, etc.
Atribuirea tuturor rolurilor pentru userul student mai puțin cel de administrator al bazei de date.
ALTER USER student DEFAULT ROLE ALL EXCEPT DBA
CONNECT – pentru conextarea la baza de date.
CONNECT sys AS SYSDBA
CREATE ROLE – pentru crearea rolurilor de acces la obiectele bazei de date.
CREATE ROLE admin IDENTIFY BY sef
CREATE TABLESPACE – pentru a crea un nou tablespace.
CREATE USER – pentru crearea unui user.
CREATE USER student
IDENTIFIED BY student
DEFAULT TABLESPACE student;
GRANT – pentru a grantifica privilegiile sau rolurile unor utilizatori.
GRANT {privilege|ROLE} [ON TABLE] TO username
REVOKE – pentru a revoca privilegiile sau rolurile unor utilizatori.
REVOKE {privilege|ROLE} [ON TABLE] FROM username