| CATEGORII DOCUMENTE |
Dupa cum am precizat in prima parte a manualului, orice baza de date trebuie sa stabileasca regulile de integritate care sa garanteze ca datele introduse in baza de date sunt corecte si valide.
Aceasta inseamna ca daca exista o regula sau restrictie asupra unei entitati, atunci datele introduse in baza de date respecta aceste restrictii.
Regulile de integritate se definesc la crearea tabelelor folosind constrangerile. Constrangerile pot fi clasificate in:
- constrangeri de domeniu, care definesc valorile pe care le poate lua un atribut (NOT NULL, UNIQUE, CHECK)
- constrangeri de integritate a tabelei, precizand cheia primara a acesteia
- constrangeri de integritate referentiala, care asigura coerenta intre cheile primare (sau unice) si cheile straine corespunzatoare (FOREIGN KEY)
Pe de alta parte constrangerile se pot clasifica dupa nivelul la care sunt definite in:
- contrangeri la nivel de tabela care pot actiona asupra unei combinatii de coloane
- constrangeri la nivel de coloana.
Constrangerile NOT NULL se pot defini doar la nivel de coloana.
Constrangerile UNIQUE, PRIMARY KEY, FOREIGN KEY si CHECK pot fi definite atat la nivel de coloana cat si la nivel de tabela. Totusi daca aceste constrangeri implica mai multe coloane atunci trebuie sa fie definite obligatoriu la nivel de tabela.
Daca o restrictie se defineste la nivel de coloana se va folosi sintaxa:
nume_coloana tip_data tip_constr
sau
nume_coloana tip_data CONSTRAINT nume_constr tip_constr
La nivel de tabela folosim sintaxa
tip_constr
sau
CONSTRAINT nume_constr tip_constr
Se observa ca putem decide sa dam un nume explicit unei constrangeri, ceea ce usureaza referirea ulterioara la acea constrangere, sau putem sa nu definim un nume explicit, caz in care sistemul va genera un nume implicit. Daca se foloseste cuvantul CONSTRAINT, atunci obligatoriu acesta va fi urmat de numele dat explicit constrangerii.
Vom prezenta in continuare modul de definire al fiecareia dintre aceste constrangeri.
Dupa cum am vazut in capitolele anterioare, NULL este o valoare speciala. Necompletarea in tabela a unei celule conduce la completarea ei cu valoarea NULL, semnificand faptul ca celula respectiva are de fapt o valoare nedefinita.
Intr-un ERD, un atribut poate fi obligatoriu, lucru pe care il marcam cu o steluta in fata atributului respectiv. In baza de date aceasta conditie se traduce prin faptul ca valoarea coloanei respective trebuie obligatoriu completata, adica nu poate contine valoarea NULL. Pentru definirea acestui tip de restrictii folosim restrictia NOT NULL pentru coloana respectiva, fie la crearea tabelei fie mai tarziu la modificarea structurii acesteia.
La crearea tabelei, restrictia NOT NULL se precizeaza pentru fiecare coloana ce trebuie sa respecte aceasta restrictie, dupa precizarea tipului coloanei respective astfel:
CREATE TABLE angajati
( nume varchar2(30) NOT NULL,
prenume varchar2(30),
localitate varchar2(20) DEFAULT 'Iasi' NOT NULL
)
Se observa ca restrictia NOT NULL a putut fi folosita in combinatie cu clauza DEFAULT.
Cheia primara este o coloana sau o combinatie de coloane care identifica in mod unic liniile unei tabele. Coloanele care fac parte din cheia primara vor fi automat de tip NOT NULL fara ca acest lucru sa mai trebuiasca precizat explicit. Cand cheia primara este compusa dintr-o singura coloana, definirea acesteia se poate face la nivel de coloana ca in exemplul urmator:
CREATE TABLE angajati
( cnp number(13) PRIMARY KEY
nume varchar2(30),
)
sau daca dorim sa atribuim un nume constrangerii putem scrie
CREATE TABLE angajati
( cnp number(13) CONSTRAINT angajati_pk PRIMARY KEY
nume varchar2(30),
)
Definirea cheii primare la nivel de tabela se poate face si atunci cand cheia este compusa dintr-un singur camp, dar este obligatorie atunci cand este compusa din mai multe coloane.
De exemplu tabela carti are cheia primara compusa din combinatia coloanelor titlu, autor, data_aparitie. Comanda de creare a acestei tabele se poate scrie:
CREATE TABLE carti
( titlu VARCHAR2(30),
autor VARHAR2(30),
data_ap DATE,
format VARCHAR2(10),
nr_pag NUMBER(3),
CONSTRAINT carti_pk
PRIMARY KEY (titlu, autor, data_ap)
)
sau simplu
CREATE TABLE carti
( titlu VARCHAR2(30),
autor VARCHAR2(30),
data_ap DATE,
format VARCHAR2(10),
nr_pag NUMBER(3),
PRIMARY KEY (titlu, autor, data_ap)
)
Sintaxa generala de definire a cheii primare este deci
PRIMARY KEY (lista_coloane)
Similar se poate defini si restrictia UNIQUE care precizeaza ca valoare coloanei definita ca UNIQUE, sau combinatia valorilor coloanelor ce definesc restrictia UNIQUE trebuie sa fie unica pentru toate liniile din tabela. Cu alte cuvinte, intr-o coloana definita ca UNIQUE nu pot exista valori duplicate.
Atentie! Coloanele definite ca UNIQUE pot contine valori NULL, iar acestea pot fi oricate, adica valoare NULL este singura valoare ce poate fi duplicata intr-o coloana UNIQUE.
Exemple:
CREATE TABLE elevi
( nr_matr NUMBER(5) PRIMARY KEY,
cnp NUMBER(13) UNIQUE,
nume VARCHAR2(30),
prenume VARHAR2(30)
)
sau
CREATE TABLE elevi
( nr_matr NUMBER(5) PRIMARY KEY,
cnp NUMBER(13) CONSTRAINT cnp_uk UNIQUE,
nume VARCHAR2(30),
prenume VARHAR2(30)
)
sau
CREATE TABLE carti
( ISBN varchar2(20) PRIMARY KEY,
titlu VARCHAR2(30),
autor VARCHAR2(30),
data_ap DATE,
format VARCHAR2(10),
nr_pag NUMBER(3),
UNIQUE (titlu, autor, data_ap)
)
sau
CREATE TABLE carti
( ISBN varchar2(20) PRIMARY KEY,
titlu VARCHAR2(30),
autor VARCHAR2(30),
data_ap DATE,
format VARCHAR2(10),
nr_pag NUMBER(3),
CONSTRAINT carti_uk UNIQUE (titlu, autor, data_ap)
)
Restrictiile referentiale sunt categoria de restrictii care creeaza cele mai mari probleme in gestiunea bazelor de date.
Pentru exemplificarea modului de definire a chei straine vom relua un exemplu de ERD din capitolul I.3 si anume cel din figura II.6.1.
Crearea tabelei jucatori corespunzatoare entitatii JUCATOR din acest ERD se va scrie:
CREATE TABLE jucatori
( nr_legitimatie NUMBER(5) PRIMARY KEY,
cod_echipa NUMBER(3)
REFERENCES echipe(cod)
nume VARCHAR2(30) NOT NULL,
prenume VARCHAR2(30) NOT NULL,
datan DATE NOT NULL,
adresa VARCHAR2(60) NOT NULL,
telefon NUMBER(3),
email VARCHAR2(30)
)
sau
CREATE TABLE jucatori
( nr_legitimatie NUMBER(5) PRIMARY KEY,
cod_echipa NUMBER(3) CONSTRAINT ech_fk
REFERENCES echipe(cod),
nume VARCHAR2(30) NOT NULL,
prenume VARCHAR2(30) NOT NULL,
datan DATE NOT NULL,
adresa VARCHAR2(60) NOT NULL,
telefon NUMBER(3),
email VARCHAR2(30)
)
sau la nivel de tabela
CREATE TABLE jucatori
( nr_legitimatie NUMBER(5) PRIMARY KEY,
cod_echipa NUMBER(3),
nume VARCHAR2(30) NOT NULL,
prenume VARCHAR2(30) NOT NULL,
datan DATE NOT NULL,
adresa VARCHAR2(60) NOT NULL,
telefon NUMBER(3),
email VARCHAR2(30),
FOREIGN KEY (cod_echipa)
REFERENCES echipe(cod)
)
sau
CREATE TABLE jucatori
( nr_legitimatie NUMBER(5) PRIMARY KEY,
cod_echipa NUMBER(3),
nume VARCHAR2(30) NOT NULL,
prenume VARCHAR2(30) NOT NULL,
datan DATE NOT NULL,
adresa VARCHAR2(60) NOT NULL,
telefon NUMBER(3),
email VARCHAR2(30),
CONSTRAINT test_fk FOREIGN KEY (cod_echipa)
REFERENCES echipe(cod)
)
Sintaxa generala este asadar la nivel de tabela:
[CONSTRAINT nume_const] FOREIGN KEY (lista_coloane)
REFERENCES tabela_parinte(lista_coloane_referite)
iar la nivel de coloana
[CONSTRAINT nume_const]
REFERENCES tabela_parinte(lista_coloane_referite)
La definirea unei chei straine se poate utiliza o clauza suplimentara ON DELETE CASCADE care precizeaza ca la stergerea unei linii din tabela parinte se vor sterge automat din tabela copil acele linii care fac referire la linia ce se sterge din tabela parinte. De exemplu, prin folosirea acestei optiuni, la stergerea unei echipe se vor sterge automat toti jucatorii de la acea echipa.
Aceasta clauza se foloseste astfel:
CREATE TABLE jucatori
( nr_legitimatie NUMBER(5) PRIMARY KEY,
cod_echipa NUMBER(3) CONSTRAINT ech_fk
REFERENCES echipe(cod) ON DELETE CASCADE,
nume VARCHAR2(30) NOT NULL,
prenume VARCHAR2(30) NOT NULL,
datan DATE NOT NULL,
adresa VARCHAR2(60) NOT NULL,
telefon NUMBER(3),
email VARCHAR2(30)
)
O alta optiune este ON DELETE SET NULL care face ca la stergerea unui parinte, valorile cheii straine din liniile tabelei copil care fac referire la linia stearsa vor fi setate pe NULL. De exemplu la stergerea unei echipe, jucatorii acesteia vor deveni liberi de contract, deci codul echipei la care joaca va fi setat pe NULL:
CREATE TABLE jucatori
( nr_legitimatie NUMBER(5) PRIMARY KEY,
cod_echipa NUMBER(3) CONSTRAINT ech_fk
REFERENCES echipe(cod) ON DELETE SET NULL,
nume VARCHAR2(30) NOT NULL,
prenume VARCHAR2(30) NOT NULL,
datan DATE NOT NULL,
adresa VARCHAR2(60) NOT NULL,
telefon NUMBER(3),
email VARCHAR2(30)
)
Implicit, fara precizarea uneia din aceste doua optiuni, Oracle va interzice stergerea unei linii din tabela parinte atata timp cat mai exista macar o linie in tabela copil care face referire la ea.
Sa vedem acum cum cream tabela inscrieri corespunzatoare entitatii inscriere din figura II.6.2. Observam ca in cheia primara intra si coloanele ce fac parte din cheia straina.
CREATE TABLE inscriere (
id_student NUMBER(5) NOT NULL
REFERENCES studenti(id),
id_curs NUMBER(5) NOT NULL REFERENCES cursuri(id),
data_inscrierii DATE DEFAULT sysdate NOT NULL,
data_finalizarii DATE,
nota NUMBER (4,2),
PRIMARY KEY (id_student, id_curs, data_inscrierii)
)
Acest tip de constrangeri specifica o conditie ce trebuie sa fie indeplinita de datele introduse in coloana (sau coloanele) asupra careia actioneaza. O astfel de constrangere poate limita valorile care pot fi introduse in cadrul unei coloane.
Iata cateva exemple de reguli de validare pentru tabela elevi care pot fi implementate cu ajutorul constrangerilor de tip CHECK:
- numele si prenumele unui elev trebuie sa inceapa cu o majuscula restul literelor fiind litere mici
- nota unui elev nu poate fi mai mare de 10
- campul bursier poate avea doar valorile 'D' si NULL
- numarul de absente nemotivate va fi cel mult egal cu numarul total de absente
Crearea tabelei elevi in aceasta situatie se poate scrie astfel:
CREATE TABLE elevi
( nr_matr NUMBER(5) PRIMARY KEY,
cnp NUMBER(13) CONSTRAINT cnp_uk UNIQUE,
nume VARCHAR2(30) NOT NULL
CHECK nume=LTRIM(INITCAP(nume)),
prenume VARHAR2(30) NOT NULL
CHECK nume=LTRIM(INITCAP(nume))
bursier CHAR(1) CHECK bursier='D',
nota NUMBER(4,2)
CONSTRAINT nota_ck CHECK nota<=10
total_abs NUMBER(3),
abs_nemotiv NUMBER(3),
CHECK (abs_nemotiv<=total_abs)
)
Modificarea structurii unui tabel se realizeaza cu ajutorul comenzii ALTER TABLE, permitand adaugarea sau stergerea unei coloane, modificarea definitiei unei coloane, crearea unei noi constrangeri sau stergerea unor constrangeri existente.
Vom prezenta in continuare, pe scurt, fiecare dintre aceste operatii.
Se realizeaza folosind clauza ADD a comenzii ALTER TABLE. Sintaxa este similara cu cea a crearii unei coloane in cadrul comenzii CREATE TABLE. De exemplu comenda urmatoare adauga o colona nrgoluri la tabela jucatori:
ALTER TABLE jucatori
ADD nrgoluri NUMBER(4)
Coloana nou creata va deveni ultima coloana a tabelei. Daca tabela contine deja date, coloana adaugata va fi completata cu NULL in toate liniile existente. De aceea nu vom putea adauga o coloana cu restrictia NOT NULL la o tabela ce contine deja date.
Asadar o comanda de forma:
ALTER TABLE test ADD ex NUMBER(3) NOT NULL
sau
ALTER TABLE test ADD ex NUMBER(3) PRIMARY KEY
Sunt permise doar daca tabela nu contine deja date.
Insa comanda
ALTER TABLE test ADD ex NUMBER(3) UNIQUE
poate fi folosita in orice moment, deoarece dupa cum am precizat o coloana UNIQUE poate contine oricate valori NULL.
Se realizeaza folosind clauza DROP COLUMN a comenzii ALTER TABLE:
ALTER TABLE elevi DROP COLUMN bursier
Asa cum este si normal, stergerea unei coloane duce automat si la stergerea restrictiilor definite pentru aceasta si care nu implica si alte coloane.
De exemplu daca tabela elevi a fost creata cu ajutorul comenzii de la pagina 58, putem sterge fara probleme coloana nume:
ALTER TABLE elevi DROP COLUMN nume
chiar daca avem definita o restrictie de tip CHECK la nivelul acestei coloane. De asemenea putem sterge coloana nr_matr, chiar daca aceasta este cheia primara a tabelei:
insa se va genera o eroare daca incercam sa stergem coloana abs_nemotiv, din cauza restrictiei definita la nivel de tabela si care implica coloanele abs_nemotiv si total_abs.
O varianta ar fi sa stergem mai intai toate restrictiile in care apare coloana ce dorim sa o stergem, sau sa folosim clauza CASCADE CONSTRAINTS astfel:
Poate fi facuta cu clauza MODIFY ca in exemplul urmator:
ATLER TABLE elevi MODIFY prenume VARCHAR2(50)
Prin care am modificat tipul coloanei prenume de le VARCHAR2(30) la VARCHAR2(50), deoarece am descoperit la un moment dat ca exista elevi al caror prenume (compus) are mai mult de 30 de caractere.
Marirea numarului de caractere pentru o coloana de tip sir de caractere se poate face fara nici o problema, insa micsorarea acestei dimensiuni se poate face doar daca tabela este goala, sau coloana respectiva contine doar valori NULL.
Tot cu optiunea MODIFY se poate modifica, sau se poate stabili o valoare implicita, daca nu exista deja una astfel:
ALTER TABLE elevi MODIFY bursier CHAR(1)
DEFAULT 'D'
insa aceasta valoare implicita nu va afecta liniile deja existente in tabela, ci doar liniile ce vor fi introduSe in continuare.
Sintaxa comenzii pentru adaugarea unei constrangeri la nivel de tabela este:
ALTER TABLE nume_tabela
ADD CONSTRAINT nume_constr definitie_constr
sau
ALTER TABLE nume_tabela
ADD definitie_constr
De exemplu comanda urmatoare defineste cheia primara pentru o tabela fictiva
ALTER TABLE tabelaexemplu
ADD PRIMARY KEY (coloana1)
Aceasta comanda poate fi scrisa echivalent si
ALTER TABLE tabelaexemplu
ADD CONSTRAINT tabelaexemplu_pk PRIMARY KEY (coloana1)
Singura constrangere ce nu poate fi adaugata in acest fel este NOT NULL, care poate fi adaugata doar prin modificarea coloanei restective folosind MODIFY:
ALTER TABLE tabelaexemplu
MODIFY coloana2 VARCHAR2(20) NOT NULL
Stergerea unei constangeri se face folosind optiunea DROP CONSTRAINT astfel:
ALTER TABLE nume_tabela
DROP CONSTRAINT nume_constrangere
sau
ALTER TABLE nume_tabela DROP PRIMARY KEY
sau
ALTER TABLE nume_tabela
DROP UNIQUE(lista_coloane)
In unele situatii, este necesara o dezactivare temporara si apoi reactivarea unei constrangeri. Acest lucru se realizeaza astfel:
ALTER TABLE nume_tabela DISABLE/ENABLE
sau
ALTER TABLE nume_tabela DISABLE/ENABLE
sau
ALTER TABLE nume_tabela DISABLE/ENABLE
Clauza CASCADE precizeaza ca si constrangerile dependente sunt deasemenea afectate.
Pana in acest moment am exemplificat diverse comenzi pe tabele care am presupus ca exista deja in baza de date si sunt deja incarcate cu date. Insa atunci cand veti face propriile voastre aplicatii va trebui sa stiti sa introduceti singuri date in tabele, sa modificati unele dintre aceste date, sa stergeti la un moment dat o parte dintre ele etc.
Pentru a adauga linii intr-o tabela se utilizeaza comanda INSERT. Forma generala a acestei comenzi este urmatoarea:
INSERT INTO nume_tabela (lista_coloane)
VALUES (lista_valori);
unde nume_tabela este numele tabelei in care vom insera noua linie,
lista_coloane precizeaza exact coloanele pe care dorim sa le populam. Aceasta lista este optionala (ea poate lipsi).
lista_valori specifica valorile pe care le va lua, pe rand, coloanele din lista de coloane.
Lista de coloane si lista de valori trebuie sa aiba acelasi numar de elemente, si in plus coloanele si valorile din cele doua liste trebuie sa corespunda ca ordine si tip.
Valorile specificate in lista (sau cele implicite) intr-o comanda INSERT, trebuie sa satisfaca toate constrangerile aplicabile coloanelor respective (ca de exemplu PRIMARY KEY, CHECK, NOT NULL).
Daca la rularea unei comenzi INSERT este generata o eroare de sintaxa, sau a fost incalcata o constrangere, linia nu este adaugata la tabela ci se va genera un mesaj de eroare.
Atunci cand din lista de coloane este omisa o coloana, Oracle va completa valoarea acelei coloane cu NULL, cu exceptia situatiei cand a fost definita o valoare implicita pentru coloana respectiva. In acest caz, Oracle completeaza coloana cu valoarea implicita. Daca omiteti din lista de coloane o coloana care nu poate avea valoarea NULL (s-a definit o restrictie NOT NULL sau PRIMARY KEY), si nu este definita o valoare implicita pentru acea coloana, se va genera o eroare.
Pentru a exemplifica modul de functionare a comenzii INSERT vom crea tabela jucatori:
create table jucatori(
id NUMBER(5) PRIMARY KEY,
nume VARCHAR2(30) NOT NULL,
prenume VARCHAR2(30),
rating NUMBER(1) CHECK (rating between 1 and 5),
varsta NUMBER(2),
localitatea VARCHAR2(30) DEFAULT 'Timisoara',
email VARCHAR2(30) UNIQUE
)
O comanda completa de inserare a unei linii in aceasta tabela se poate scrie:
insert into jucatori (id, nume, prenume, rating, varsta,
localitatea, email)
values (18, 'Ionescu', NULL, 3, 30,
'Sibiu', 'user18@games.ro')
Fara a mai specifica coloanele putem scrie urmatoarea comanda, in care am tinut cont de ordinea coloanelor in tabela:
insert into jucatori values (11, 'Georgescu',
'Valeriu', 1, 18, 'Bucuresti', 'user11@games.ro')
Comanda urmatoare are ca efect completarea coloanelor id, nume, prenume cu valorile specificate in lista de valori iar coloanele rating, varsta, localitatea, email cu valorile implicite pentru aceste coloane, adica 'Timisoara' pentru localitate si respectiv NULL pentru rating, varsta, email:
insert into jucatori (id, nume, prenume)
values (22, 'Vasilescu', 'Anca')
Figura II.7.1
Desi campul email are definita o restrictie UNIQUE, putem insera inca o valoare NULL in aceasta coloana, doar valorile nenule trebuind sa fie unice. Observati in comanda urmatoare cum s-a precizat ca dorim setarea valorii implicite si a valorii NULL pentru campurile localitate, rating si email.
insert into jucatori (id, nume, prenume, rating, varsta,
localitatea, email)
values (37, 'Enescu', 'Monica', NULL, 26,
DEFAULT, NULL)
Figura II.7.2
Nu putem insa initializa coloanele id sau nume cu o valoare implicita, aceasta valoare implicita fiind in acest caz valoare NULL, care nu este permisa pentru cheia primara sau pentru o coloana avand restrictia NOT NULL:
Figura II.7.3.
Figura II.7.4.
Pentru completarea unui camp putem folosi o subinterogare ca in urmatorul exemplu:
insert into jucatori (id, nume, prenume)
values ((select max(id)+1 from jucatori),
'Plesca','Ovidiu')
Figura II.7.5.
In Oracle este permisa adaugarea mai multor linii simultan prin preluarea datelor din alte tabele, cu ajutorul unei subinterogari. Comanda urmatoare, de exemplu, preia toti angajatii din tabela employees care au job_id-ul egal cu 'IT_PROG' si ii insereaza in tabela jucatori:
insert into jucatori (id, nume)
select employee_id, last_name from employees
where job_id='IT_PROG'
Figura II.7.6.
Stergerea uneia sau mai multor linii dintr-o tabela se face utilizand comanda DELETE a carei sintaxa este:
DELETE FROM nume_tabela WHERE conditie
Liniile care se vor sterge sunt selectate folosind clauza WHERE:
DELETE FROM jucatori WHERE id>100
Stergerea liniilor se poate face si pe baza valorilor returnate de catre o subinterogare:
DELETE FROM jucatori WHERE id <
(SELECT id FROM jucatori WHERE nume='Ionescu')
Daca este omisa clauza WHERE, se vor sterge toate liniile din tabela, insa structura tabelei ramane (se sterge doar continutul tabelei, nu si tabela propriu-zisa). Deci comanda:
DELETE FROM jucatori
sterge toate liniile din tabela jucatori. Atentie! Aceste linii nu vor mai putea fi recuperate.
Modificarea uneia sau mai multor inregistrari (linii) dintr-o tabela se realizeaza cu comanda UPDATE care are sintaxa:
UPDATE nume_tabela
SET coloana1 = valoare1,
coloana2 = valoare2,
WHERE conditie
ca in urmatorul exemplu:
update jucatori
SET prenume='Emilian' WHERE id=18
care modifica (completeaza) prenumele jucatorului cu id-ul 18.
Modificarea valorilor unei linii se poate face pe baza valorilor returnate de catre o subinterogare. Astfel, daca dorim sa ii atribuim jucatorului cu id-ul 44 acelasi rating ca cel al jucatorului cu codul 18, iar varsta sa fie cu 5 mai mare decat varta jucatorului cu codul 43, vom scrie:
UPDATE jucatori
SET rating=(SELECT rating FROM jucatori WHERE id=18),
varsta=(SELECT varsta+5 FROM jucatori WHERE id=43)
WHERE id=44
Daca o subinterogare utilizata la actualizarea valorilor dintr-o coloana nu returneaza nici o valoare, atunci campul respectiv va fi initializat cu NULL:
UPDATE jucatori
SET rating = (SELECT rating FROM jucatori WHERE id=200)
WHERE id=44
Inaintea rularii acestei comenzii continutul tabelei jucatori era cea din figura II.7.7, iar dupa rularea sa continutul este cel din figura II.7.8. Se observa ca initial ratingul jucatorului 44 era 3, iar dupa rularea comenzii acesta a devenit NULL.
Figura II.7.7.
Figura II.7.7.
Interesant este ca o comanda de forma:
UPDATE jucatori
SET rating = (SELECT rating FROM jucatori WHERE id=18),
varsta = (SELECT varsta+5 FROM jucatori WHERE id=18)
WHERE id=44
se poate scrie si astfel:
UPDATE jucatori
SET (rating, varsta) =
(SELECT rating, varsta FROM jucatori WHERE id=18)
WHERE id=44
View-vederi
Uneori, din motive de securitate, ati dori sa nu permiteti anumitor utilizatori sa aiba acces nelimitat la o tabela, ci doar la datele ce se gasesc in anumite coloane ale acestei tabele.
De exemplu, intr-o firma, contabila firmei nu va avea acces la coloanele ce se refera la proiectele in care sunt implicati la momentul actual fiecare angajat al firmei, insa va avea cu siguranta acces la date privind salariul, tariful orar cu care este platit fiecare angajat, numarul de ore lucrate etc. Pe de alta parte, bibliotecara de la biblioteca firmei, nu va avea acces la datele privind salarizarea personalului ci doar la datele personale ale angajatilor (adresa, telefon, email etc).
Pentru a putea da acces partial la o tabela utilizatorilor vom folosi ceea ce numim vederi (sau views). O vedere este o tabela virtuala, pentru care nu sunt memorate date propriu-zise ci doar definitia vederii, care are rolul de filtrare a datelor.
Vederile sunt reprezentari logice ale tabelelor existente si functioneaza ca niste ferestre prin intermediul carora pot fi vizualizate si modificate datele din tabelele fizice (fig. II.8.1).
Figura II.8.1. Acces direct si indirect (printr-o vedere) la o tabela
Pe langa faptul ca ofera protectie marita a datelor, vederile mai au un mare avantaj: ele reduc in mod considerabil complexitatea interogarilor pe care utilizatorii trebuie sa le scrie. O vedere poate fi construita folosind operatii complexe de join, care raman 'ascunse' utlizatorului vederii respective, care va folosi interogari simple.
La crearea unei vederi se va folosi o subinterogare, oricat de complexa, insa aceasta NU poate folosi clauza ORDER BY.
Sintaxa generala de a comenzii pentru crearea unei vederi este:
CREATE OR REPLACE VIEW nume_nedere
AS subinterogare
Optiunea OR REPLACE poate lipsi, aceasta fiind utila atunci cand dorim sa modificam o vedere deja existenta.
De exemplu, urmatoarea comanda creeaza o vedere simpla pe baza tabelei employees:
CREATE OR REPLACE VIEW v1 AS
( SELECT first_name||' '||last_name as Nume,
salary
FROM employees WHERE department_id=20)
Dupa cum am precizat, o vedere se poate construi folosind mai multe tabele, ca in exemplul urmator:
CREATE OR REPLACE VIEW v2 AS
( SELECT a.nume ||' '|| a.prenume AS Angajat,
b.nume ||' '|| b.prenume AS Sef,
c.nume as Firma, d.nume as Job
FROM angajat a, angajat b
WHERE a.id_manager = b.id(+) and
a.idFirm=c.idFirm(+) and a.idJob=d.idJob(+)
)
Observatie. In subinterogarea care defineste o vedere, toate expresiile (nu si coloanele simple) trebuie sa aiba asociate un alias pentru a putea fi ulterior referite in interogari.
Cum putem interoga aceste vederi? Ele pot fi folosite ca orice tabela obisnuita, atat in interogari cat si in operatiile de actualizare (adaugare, modificare, stergere), asupra acestora din urma insa vom reveni in paragrafele urmatoare. Putem scrie de exemplu:
SELECT nume, salary FROM v1
WHERE nume like '%a%'
sau
SELECT angajat, sef, firma, job
FROM v2
O vedere poate fi sterasa cu comanda
DROP VIEW nume_vedere
Atentie! Stergerea unei vederi nu afecteaza in nici un fel datele din tabelele pe baza carora s-a creat vederea. Toate modificarile realizate asupra tabelelor prin intermediul vederii raman valabile si dupa stergerea acesteia.
In acest paragraf vom folosi pentru exemplificare tabelele jucatori si echipe create cu ajutorul urmatoarelor comenzi:
CREATE TABLE jucatori(
id NUMBER(5) PRIMARY KEY,
nume VARCHAR2(30) NOT NULL,
prenume VARCHAR2(30),
rating NUMBER(1) CHECK (rating BETWEEN 1 AND 5),
varsta NUMBER(2),
localitatea VARCHAR2(30) DEFAULT 'Timisoara',
email VARCHAR2(30) UNIQUE
)
Sa cream acum urmatoarele vederi:
CREATE OR REPLACE VIEW v1_JucatoriTm AS
( SELECT id, nume, varsta, localitatea FROM jucatori
WHERE localitatea = 'Timisoara' )
si
CREATE OR REPLACE VIEW v2_Jucatori AS
( SELECT nume, prenume FROM jucatori
WHERE rating IS NOT NULL)
Asadar am creat o vedere pentru toti jucatorii din Timisoara. Putem interoga simplu aceasta vedere:
SELECT * FROM v1_JucatoriTm
rezultatul fiind cel din tabelul urmator:
Tabelul II.8.1.
|
ID |
NUME |
VARSTA |
LOCALITATEA |
|
22 |
Vasilescu |
- |
Timisoara |
|
103 |
Hunold |
- |
Timisoara |
|
104 |
Ernst |
- |
Timisoara |
|
107 |
Lorentz |
- |
Timisoara |
|
37 |
Enescu |
26 |
Timisoara |
|
44 |
Plesca |
37 |
Timisoara |
iar comanda
SELECT * FROM v2_Jucatori
va afisa
Tabelul II.8.2.
|
NUME |
PRENUME |
|
Georgescu |
Valeriu |
|
Marin |
Adriana |
|
Ionescu |
Emilian |
Vom incerca acum, pe rand, sa vedem cum functioneaza fiecare operatie de actualizare a datelor.
O vedere poate fi creata folosind optiunea WITH READ OPTION, prin intermediul unei astfel de vederi neputandu-se efectua nici o operatie de actualizare. Aceste vederi sunt folosite doar pentru vizualizarea datelor:
CREATE OR REPLACE VIEW v4_JucatoriTm AS
( SELECT id, nume, varsta, localitatea
FROM jucatori
WHERE localitatea = 'Timisoara' )
WITH READ ONLY
Figura II.8.2.
Incercam sa inseram cate o inregistrare in tabela jucatori prin intermediul celor doua vederi create anterior:
insert into v1_JucatoriTm
values(210, 'Alexandrescu',41,'Iasi')
Comanda functioneaza perfect (fig. II.8.3), desi jucatorul nou inserat nu respecta domeniul vederii v1_JucatoriTm, adica desi putem vizualiza prin intermediul acestei vederi doar jucatorii din Timisoara, am reusit totusi sa inseram un jucator din alta localitate. Acest lucru ar putea crea probleme de securitate (am creat vederea tocmai pentru a restrictiona drepturile utilizatorilor).
Figura II.8.3.
Aceasta problema poate fi rezolvata prin folosirea optiunii WITH CHECK OPTION la crearea vederii. Vom crea o noua vedere v3_jucatoriTm folosind aceasta optiune:
CREATE OR REPLACE VIEW v3_JucatoriTm AS
( SELECT id, nume, varsta, localitatea FROM jucatori
WHERE localitatea = 'Timisoara' )
WITH CHECK OPTION
De aceasta data nu mai putem insera valori care sunt in afara domeniului vederii (fig. II.8.4).
Figura II.8.4.
Prin intermediul vederii v2_jucatori nu vom putea insera linii in tabela jucatori, deoarece prin intermediul vederii nu avem acces la campul id, care fiind cheie primara nu poate fi initializata cu valoarea implicita NULL (fig. II.8.5)
Figura II.8.5.
La stergerea unei inregistrari vom folosi comanda DELETE cu formatul deja cunoscut. Evident nu vom putea sterge din tabela decat liniile accesibile prin vederea respectiva. De aceea comanda:
DELETE FROM v1_jucatoriTm WHERE id=43
nu va genera nici o eroare, insa nu va sterge nici o linie intrucat jucatorul avand id-ul 43 este din Brasov, deci nu avem acces la el prin intermediul vederii v1_jucatoriTm.
Similar, nu vom putea folosi in clauza WHERE a comenzii DELETE coloane care nu sunt vizibile din vederea respectiva. De exemplu comanda
DELETE FROM v2_jucatori WHERE id=43
va genera o eroare, deoarece campul id este inaccesibil vederii (fig. II.8.6.)
Figura II.8.6.
Comenzile
delete from v2_jucatori where prenume='Emilian'
si
delete from jucatori where id=107
sunt perfect functionale.
Ca si in cazul celorlaltor operatii de actualizare vom putea modifica doar valorile liniilor si coloanelor care sunt vizibile din vederea respectiva:
update v1_jucatoriTm
set varsta=13
where id=103
Operatiile de actualizare a datelor prin intermediul vederilor NU pot fi realizate in urmatoarele conditii:
actualizarea datelor (stergere, modificare, inserare) nu se poate efectua daca subinterogarea cu care s-a creat vederea foloseste:
o functii de grup
o clauza GROUP BY
o clauza DISTINCT
o pseudocoloanele ROWNUM sau ROWID
nu se poate modifica un camp calculat al unei vederi:
De exemplu, daca s-a creat vederea
CREATE VIEW v5 AS
( SELECT id, nume, nvl(rating,0) rating
FROM jucatori)
vom putea actualiza campurile id si nume:
UPDATE v5
SET nume='Eminescu'
WHERE id=37
dar nu putem modifica valoarea din campul rating (fig. II.8.7.)
Figura II.8.7.
Nu se poate insera o linie intr-o tabela prin intermediul unei vederi decat daca toate coloanele NOT NULL ale tabelei sunt prezente in vedere.
Imaginati-va ca trebuie sa adaugati in baza de date a scolii, datele persoanele ale noilor elevi veniti in scoala voastra in clasa a IX-a. Fiecarui elev trebuie sa-i asociati un id unic in intreaga baza de date. Nu stiti insa exact care sunt id-urile elevilor deja existenti in baza de date, pentru a sti care sunt id-urile "libere". Cum rezolvati oare aceasta problema?
O varianta ar fi ca la inserarea unui nou elev sa determinati cel mai mare id existent in baza de date, si sa-i asociati elevului nou inserat un id cu o unitate mai mare decat cel mai mare id. Veti scrie o comanda de forma:
INSERT INTO elevi (id, nume, prenume, .)
VALUES ( SELECT max(id)+1 FROM elevi,
'Ionescu', 'Ioan', .)
O astfel de solutie poate genera probleme in cazul accesului concurent la baza de date, cand este posibil ca doi utilizatori diferiti sa incerce sa insereze doi elevi cu acelasi id.
Solutia este folosirea secventelor. Secventele sunt obiecte ale bazei de date cate genereaza automat, in mod secvential, liste de numere. Acestea sunt utile cand o tabela foloseste o cheie primara artificiala, ale carei valori dorim sa le generam automat.
Sintaxa pentru crearea unei secvente este urmatoarea:
CREATE SEQUENCE nume_secventa
START WITH n1
INCREMENT BY n2
MAXVALUE n3 | NOMAXVALUE
MINVALUE n4 | NOMINVALUE
CACHE n5 | NOCHACE
CYCLE | NOCYCLE
Sa explicam pe rand care este rolul fiecarei optiuni din aceasta comanda:
START WITH n1 - precizeaza de la ce valoare va incepe generarea valorilor. Aceasta optiune este utila atunci cand campul pentru care dorim sa generam valori folosind aceasta secventa contine deja valori. In acest caz, vom preciza in n1 o valoare mai mare decat toate valorile deja existente in coloana respectiva. Daca aceasta optiune nu este prezenta, se va incepe implicit de la valoarea 1.
INCREMENT BY n2 - precizeaza intervalul dintre doua numere din secventa. Poate fi un numar intreg pozitiv sau negativ, dar nu poate fi zero. Daca se precizeaza o valoare negativa, atunci valorile se vor genera in ordine descrescatoare, altfel se vor genera in ordine crescatoare. Daca omiteti aceasta optiune valoarea implicita a incrementului va fi 1.
MAXVALUE n3 si respectiv MINVALUE n4 - aceste clause specifica cea mai mare, respectiv cea mai mica valoare returnata de catre secventa. n3 si respectiv n4 trebuie sa fie numere intrege cu maxim 9 cifre.
NOMAXVALUE - valoarea maxima generata va fi 2147483647 pentru o secventa cu increment pozitiv, respectiv -1 pentru o secventa cu increment negativ.
NOMINVALUE - valoarea maxima generata va fi 1 pentru o secventa cu increment pozitiv, respectiv -2147483647 pentru o secventa cu increment negativ.
CACHE n5 - aceasta optiune este folosita din considerente de eficienta. Cu aceasta optiune se vor genera simultan n5 valori din secventa, si numai atunci cand acestea se vor epuiza se vor genera urmatoarele n5 valori. In acest fel se vor face mai putine modificari asupra bazei de date.
CYCLE | NOCYCLE - daca specificati optiunea CYCLE atunci cand secventa a ajuns la valoarea maxima (respectiv minima pentru o secventa cu increment negativ), secventa va reincepe sa genereze valori incepand cu MINVALUE (respectiv MAXVALUE pentru o secventa cu increment negativ). Evident, daca utilizati optiunea CYCLE nu exista nici o garantie privind unicitatea valorilor generate.
De exemplu, comanda:
CREATE SEQUENCE sec1
START WITH 1 INCREMENT BY 1
creeaza o secventa care va genera valori din 1 in 1, incepand cu 1, adica va genera in ordine valorile 1, 2, 3, etc.
Comanda
CREATE SEQUENCE sec2
START WITH 120 INCREMENT BY -3
creeaza o secventa care va genera valori descrescatoare din 3 in 3, incepand cu 120, adica va genera in ordine valorile 120, 117, 114, etc.
Stergerea unei secvente se face simplu cu comanda DROP SEQUENCE.
Sa vedem acum cum generam efectiv valorile din secventa. Vom folosi doua pseudocoloane speciale numite NEXTVAL si respectiv CURRVAL. NEXTVAL genereaza urmatoarea valoare din secventa, in timp ce CURVAL este folosita pentru a afla care a fost valoarea care tocmai a fost generata.
Pentru exemplificare, cream secventa
CREATE SEQUENCE sec3
START WITH 5 INCREMENT BY 3
si tabela
CREATE TABLE test(nr number(3))
si rulam de 3 ori comanda:
INSERT INTO test values(sec3.NEXTVAL)
In acest fel continutul tabelei este 5, 8, 11 (fig. II.9.1)
Figura II.9.1.
Daca rulam acum comanda
SELECT sec3.currval FROM dual
se va afisa valaoarea 11, adica exact ultima valoare generata de catre secventa.
Atentie! Pseudocoloanele NEXTVAL si CURRVAL nu pot fi folosite in urmatoarele contexte:
in clauza SELECT a unei vederi
intr-o comanda SELECT care foloseste optiunea DISTINCT
intr-o comanda SELECT care foloseste clauzele GROUP BY, HAVING, sau ORDER BY.
intr-o subinterogare din cadrul unei comenzi SELECT, DELETE sau UPDATE.
Intr-o optiune DEFAULT a comenzii CREATE TABLE sau ALTER TABLE.
Comanda ALTER SEQUENCE care permite modificarea unei secvente are sintaxa similara cu cea a comenzii CREATE SEQUENCE:
CREATE SEQUENCE nume_secventa
INCREMENT BY n2
MAXVALUE n3 | NOMAXVALUE
MINVALUE n4 | NOMINVALUE
CACHE n5 | NOCHACE
CYCLE | NOCYCLE
Modificarea unei secvente va afecta doar valorile ce se vor genera ulterior. La modificarea unei secvente trebuie sa se tina cont de cateva restrictii. De exemplu nu se poate stabili o valoare in clauza MAXVALUE care sa fie mai mica decat ultima valoare care a fost deja generata de catre secventa.
Sa experimentam putin optiunea de modificare a unei secvente. Sa rulam, pe rand, urmatoarele comenzi:
CREATE SEQUENCE sec4;
CREATE TABLE test1 (n NUMBER(2), v NUMBER(2));
INSERT INTO test1 values(1, sec4.NEXTVAL);
INSERT INTO test1 values(2, sec4.NEXTVAL);
INSERT INTO test1 values(3, sec4.NEXTVAL);
INSERT INTO test1 values(4, sec4.CURRVAL);
ALTER SEQUENCE sec4 INCREMENT BY -5 MINVALUE -200;
INSERT INTO test1 values(5, sec4.NEXTVAL);
INSERT INTO test1 values(6, sec4.NEXTVAL);
INSERT INTO test1 values(7, sec4.NEXTVAL);
Dupa aceste comenzi, continutul tabelei test va fi cel din tabelul urmator:
Tabelul II.9.1.
|
N |
V |
|
1 |
1 |
|
2 |
2 |
|
3 |
3 |
|
4 |
3 |
|
5 |
-2 |
|
6 |
-7 |
|
7 |
-12 |
Atentie! In Oracle Database Express Edition este posibil ca referirea la pseudocoloana CURRVAL sa nu functioneze in mod corespunzator.
|
Politica de confidentialitate | Termeni si conditii de utilizare |
Vizualizari: 2214
Importanta: 
Termeni si conditii de utilizare | Contact
© SCRIGROUP 2024 . All rights reserved
