Gestiunea datelor din tabele

Precizare. Pentru instrucţiunile care modifică date din tabele sunt utile tranzacţiile (vor fi descrise într-un curs următor).

INSERT

INSERT INTO [schema.]{nume_tabel | nume_view} [(lista_coloane)] VALUES(lista_valori)
INSERT INTO [schema.]{nume_tabel | nume_view} [(lista_coloane)] subinterogare

Observaţii:

lista_valori conţine câte o valoare pentru fiecare coloană din lista_coloane
Coloanele care lipsesc din lista de coloane primesc valoarea implicită sau valoarea null
Dacă lista_coloane lipseşte, atunci se consideră prezente toate coloanele, în ordinea de la definirea tabelului
Sirurile de caractere sunt delimitate de apostrof
Pot apare erori dacă restricţiile definite nu sunt respectate
In lista de valori se pot folosi expresii care se pot evalua la execuţia instrucţiunii. In expresii pot apare şi funcţii sistem.
Cu varianta VALUE se poate adăuga o singură înregistrare
Cu varianta a doua (cu o subinterogare) se pot adăuga mai multe înregistrări. Instrucţiunea select nu poate selecta înregistrări din tabelul curent în care se fac înserări.

Pentru fiecare înregistrare care se adaugă se caută spaţiu liber, în spaţiul tabel folosit, prin parcurgerea tuturor blocurilor deja alocate. Această parcurgere este necesară pentru o folosire optimă a spaţiului de memorie.
Pentru fiecare instrucţiune SQL se generează un plan de execuţie, care pentru instrucţiunea INSERT face parcurgerea amintită. Schimbarea acestui plan se poate face prin "hinturi" (se va vedea la optimizare). Pentru instrucţiunea INSERT un astfel de hint util, mai ales dacă se adaugă mai multe înregistrări, este APPEND, precizat sub forma:

INSERT /* +APPEND */ INTO ...

In acest caz se elimină parcurgerea amintită pentru căutarea de spaţiu liber şi înserarea se face începând de la ultimul bloc folosit la adăugarea precedentă.
Incepând cu versiunea Oracle 9i se pot face înserări multiple cu o singură instrucţiune, folosind o extensie a inserării cu varianta INTO ... SELECT ...:

INSERT [{ALL | FIRST}]
{[WHEN (conditie)] INTO ... VALUES (...) } ...
[[ELSE] INTO ... VALUES (...)]
instructiune_SELECT

Nu se pot combina opţiuni cu WHEN şi opţiuni fără WHEN
Dacă apare FIRST, atunci trebuie să apară şi WHEN
Pentru fiecare înregistrare curentă obţinută din instrucţiunea SELECT se execută înserările precizate de opţiunile INTO. In condiţiile din WHEN şi lista de valori din VALUES se pot folosi valorile din înregistrarea curentă.
Cu opţiunea ALL, valoare implicită, se evaluează fiecare opţiune WHEN condiţie (dacă lipseşte această clauză, atunci se consideră că valoarea condiţiei este true) cu condiţie true se efectuează inserarea descrisă. Se iau în considerare toate opţiunile WHEN.
Cu opţiunea FIRST se evaluează condiţiile din WHEN şi se execută înserarea pentru prima condiţie cu valoarea true, restul opţiunilor sunt abandonate.
Dacă toate opţiunile WHEN au condiţia diferită de true, atunci se execută înserarea din ELSE, dacă aceasta apare.

UPDATE

UPDATE [schema.]nume_tabel SET coloana=valoare [, coloana=valoare]... [WHERE conditie]
UPDATE nume_tabel SET (coloana [, coloana]...) = (subinterogare) [WHERE conditie]

Din tabelul precizat se modifică înregistrările pentru care condiţia din clauza WHERE are valoarea true. Dacă această clauză lipseşte, atunci se modifică tot tabelul.
Cu a doua variantă se poate modifica o listă de coloane prin rezultatul unei subinterogări ce are 0 sau 1 înregistrare. Dacă subinterogarea nu are înregistrări, atunci se atribuie valoarea null la fiecare coloană.
Observaţie. Pot apare erori dacă nu sunt respectate restricţiile definite. Intr-o astfel de situaţie nu se efectuează modificările cerute.

DELETE

DELETE [FROM] [schema.]nume_tabel [WHERE conditie_where]

Se elimină înregistrările din "nume_tabel" ce satisfac cu true condiţia din WHERE.

TRUNCATE

TRUNCATE TABLE [schema.]nume_tabel

Se elimină toate înregistrările din tabel.

MERGE

Instrucţiunea MERGE (numită şi UPSERT) se poate folosi în Oracle începând cu versiunea 9i. Sintaxa instrucţiunii este:

MERGE INTO {tabel_destinatie | view_destinatie}[alias-1]
USING {tabel_sursa | view_sursa | interogare} [alias-2]
ON (conditie)
WHEN MATCHED THEN 
  UPDATE SET coloana = expresie [, coloana = expresie] ... [clauza_WHERE]
  [DELETE clauza_WHERE]
WHEN NOT MATCHED THEN 
  INSERT [(coloana [, coloana] ... )] VALUES (expresie [, expresie] ... ) [clauza_WHERE]
  [DELETE clauza_WHERE]

In tabel_destinatie, sau view_destinatie (view modificabil), se efectuează unele modificări de actualizare sau înserare.
Se pleacă de la o sursă de înregistrări (tabel, view, instrucţiune select).
Pentru fiecare înregistrare din sursă se evaluează condiţia din ON. Dacă există cel puţin o înregistrare corespunzătoare în destinaţie pentru care această condiţie este adevărată, atunci se execută actualizările din destinaţie conform precizărilor din opţiunea "WHEN MATCHED THEN UPDATE" (în expresii se pot folosi valori din înregistrarea găsită în "sursa"), urmate de eventuale operaţii de ştergere în destinaţie.
Dacă această condiţie din ON nu este îndeplinită cu true pentru nicio înregistrare din destinaţie, atunci se efectuează operaţiile de înserare, în destinaţie, precizate în opţiunea "WHEN NOT MATCHED THEN INSERT", urmate de eventuale operaţii de ştergere în destinaţie.
Coloanele folosite în condiţia din ON nu se pot actualiza în instrucţiunea UPDATE.

Exemplu:
Presupunem tabelul următor:

create table persoane(cnp char(13), nume varchar2(30), prenume varchar2(30));
insert into persoane(cnp, nume, prenume) values('2580305123131','Ignat','Ana');
insert into persoane(cnp, nume, prenume) values('1580911244217','Matei','Ioan');
insert into persoane(cnp, nume, prenume) values('1591010120644','Matei','Vasile');
insert into persoane(cnp, nume, prenume) values('1630201126195','Irimia','Alin');
insert into persoane(cnp, nume, prenume) values('1660518125177','Barla','Bazil');
insert into persoane(cnp, nume, prenume) values('1661011120649','Bancescu','Gabriel');
insert into persoane(cnp, nume, prenume) values('2670518084754','Pop','Stefana');
insert into persoane(cnp, nume, prenume) values('2701007120700','Rus','Mihaiela');

Acest tabel se copiază în tabelul persoane_m, care se actualizează (se modifică valori pentru coloanele nume şi prenume, se adaugă noi înregistrări) într-o anumită aplicaţie (de exemplu pe un dispozitiv mobil), după care tabelul se copiază în baza de date.
Presupunem că după aceste operaţii conţinutul tabelului persoane_m din baza de date este următorul:

insert into persoane_m(cnp, nume, prenume) values('2580305123131','Popescu','Ana');
insert into persoane_m(cnp, nume, prenume) values('1580911244217','Matei','Ioan Aurel');
insert into persoane_m(cnp, nume, prenume) values('2670518084754','Popa','Stefana');
insert into persoane_m(cnp, nume, prenume) values('2701007120700','Rusu','Mihaiela');
insert into persoane_m(cnp, nume, prenume) values('1821115260049','Safta','Ioan');
insert into persoane_m(cnp, nume, prenume) values('2810527314008','Suteu','Valentina');
insert into persoane_m(cnp, nume, prenume) values('1820210125811','Bercea','Adrian');
insert into persoane_m(cnp, nume, prenume) values('1820609125477','Rad','Tiberiu');
insert into persoane_m(cnp, nume, prenume) values('2820726082565','Florean','Stefana');

Conţinutul primului tabel:

select * from persoane;

este:

CNP	NUME	PRENUME
2580305123131	Ignat	Ana
1580911244217	Matei	Ioan
1591010120644	Matei	Vasile
1630201126195	Irimia	Alin
1660518125177	Barla	Bazil
1661011120649	Bancescu	Gabriel
2670518084754	Pop	Stefana
2701007120700	Rus	Mihaiela

8 rows selected.

Datele din tabelul persoane_m se folosesc pentru actualizarea datelor din tabelul persoane:

Pentru înregistrările cu acelaşi cnp se iau valorile nume şi prenume din tabelul persoane_m
Inregistrările din persoane_m care nu se află (valoarea cnp nu este) în tabelul persoane se adaugă în acest tabel

Instrucţiunea de actualizare este:

MERGE INTO persoane a
     USING (SELECT cnp, nume, prenume from persoane_m) b
     ON (a.cnp = b.cnp)
     WHEN MATCHED THEN
         UPDATE SET a.nume = b.nume, a.prenume = b.prenume
     WHEN NOT MATCHED THEN
         INSERT (cnp, nume, prenume) VALUES (b.cnp, b.nume, b.prenume);

Conţinutul primului tabel după această instrucţiune:

select * from persoane;

este:

CNP	NUME	PRENUME
2580305123131	Popescu	Ana
1580911244217	Matei	Ioan Aurel
1591010120644	Matei	Vasile
1630201126195	Irimia	Alin
1660518125177	Barla	Bazil
1661011120649	Bancescu	Gabriel
2670518084754	Popa	Stefana
2701007120700	Rusu	Mihaiela
2820726082565	Florean	Stefana
1820210125811	Bercea	Adrian
2810527314008	Suteu	Valentina
1821115260049	Safta	Ioan
1820609125477	Rad	Tiberiu

13 rows selected.