8. Modificarea datelor  

In aceast capitol veti invata cum sa inserati linii (date) in tabele, sa stergeti liniile existente din tabele. De asemenea veti invata sa controlati tranzactiile utilizand comenzile COMMIT, SAVEPOINT, si ROLLBACK.

Limbajul de manipulare al datelor (DML)

Limbajul de manipulare al datelor este nucleul limbajului SQL. Cand doriti sa adaugati, sa actualizati, sau sa stergeti date din baza de date, executati comenzi DML(Data Manipulation Language). O colectie de comenzi DMl care formeaza o unitate logica reprezinta o tranzactie.

In capitolele anterioare am discutat despre limbajul DDL (Data Definition Language) cu ajutorul caruia putem sa crem sau sa distrugem diferite obiecte ale bazei de date.I acest capitol ne ocupam de limbajul de manipulare al datelor (DML) care ne permite sa aduagam, sa modificam, sau sa distrugem datele din baza de date. Oracle 9i introduge o noua functie MERGE, pe langa cele existente : INSERT, UPDATE, DELETE . Sa consideram baza de date a Facultatiilor.

Adaugare o noua inregistrare

Sintaxa este :

INSERT INTO tabela [ ( coloana [ , coloana . . . ])]
VALUES ( valoare [, valoare . . . ]);  

tabela – numele tabelei

coloana – numele coloanei din tabela

valoare – valoarea corespunzatoare coloanei

Nota : Se poate adauga o singura linie o data.


Exemplu

Introduce un nou oras in tabela DEPT.

Deoarece se poate insera o noua linie ce contine valori pentru fiecare coloana, lista coloanelor nu mai este necesara in clauza INSERT . Totusi daca nu utilizam lista de coloane, valorile trebuie sa fie listate in ordinea coloanelor din tabela, iar o valoare trebuie utilizata pentru fiecare coloana. Pentru o utilizare mai usoara putem utiliza comanda DESCRIBE DEPT, care ne afiseaza campurile tabelei in ordinea lor, precum si tipul fiecarui camp.

Inerarea linilor ce contin valori NULL

Inserarea liniilor ce contin valori NULL se poate face prin doua metode:

•  metoda implicita : Omiterea unui camp din lista campurilor existente in tabela respectiva.

INSERT INTO DEPT (DEPTNO,DNAME,LOC)
VALUES (50,NULL,'Bucuresti');

•  metoda explicita : Specificarea cuvantului NULL in clauza VALUES .

INSERT INTO DEPT (DEPTNO,DNAME,LOC)
VALUES (50,NULL,'Bucuresti');

Inserarea unor valori speciale  

Functia SYSDATE inregistreaza data curenta si ora.

INSERT INTO EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) VALUES (7658, 'IONESCU', 'ANALIST', 7566, SYSDATE, 1000, NULL, 20);

Putem utiliza diferite functii pentru a insera valori speciale in tabela noastra.

Exemplu

Insereaza in tabela EMP datele personale, precum si data cand acestea au fost introduse, prin utilizarea comenzii SYSDATE, care reprezinta data sistemului.

Se pot salva comenzi cu substitutie de variabile pentru a executa comenzi in fisier. Exemplu de mai sus inregistreaza informatii in tabela de EMP. Rulati scriptul si veti fi informat pentru a substitui variabilele &nume, si & prenume. Valorile pe care le introduceti in formular vor fi introduse in tabela.

Copierea informatiilor dintr-o alta tabela

Se scrie comanda INSERT cu ajutorul uniu subquery. Nu se utilizeaza clauza VALUES. Potriviti numarul de campuuri din caluza INSERT cu cel din subquery. Se poate folosi clauza INSERT pentru a aduaga linii intr-o tabela unde valorile sunt dintr-o alta tabela .In loc de caluza VALUES, folosim un subquery.

Sintaxa

INSERT INTO tabela [ coloana ( , coloana ) ] subquery ;

tabela – numele tabelei

coloana – numele campului din tabela

subquery – subquery-ul care returneaza campurile din cealalta tabela

Numarul de coloane si tipurile de date din lista campurilor trebuie din caluza INSERT trebuie sa se potriveasca cu valorile si tipurile de date din subquery. Pentru a creea o copie a linilor unei tabele, vom folosi SELECT * in subquery.

Exemplu

Se introduc datele din tabela ORASE intr-o alta tabela numita COPIE_ORASE.

Actualizare datelor dintr-o tabela

• Schimbarea liniilor existente folosind clauza UPDATE
  
  
  UPDATE tabela
  SET coloana = valoare [, coloana = valoare, . . . ]
  [WHERE CONDITIE ];

In sintaxa :

tabela - numele tabelei
coloana - numele coloanei in care vor fi introduse datele
valoare - valoarea corespunzatoare din subquery
conditie - identificarea campurilor care vor fi actualizate

Nota : In general se foloseste cheia primara pentru a identifica un tuplu. Utilizarea altei coloane poate duce la actualizarea maimultor tupluri.
De exemplu in relatia persoane putem avea de doua sau mai multe ori acelasi nume.

Stergerea tuplurilor dintr-o tabela

Se pot sterge tupluri dintr-o tabela utilizand clauza DELETE.

DELETE [FROM] tabela
[WHERE             conditie];

In sintaxa

tabela - numele tabelei;
conditie - identifica tuplurile care trebuie sterse si este compusa din nume de campurri, expresii, constante, subquery-uri, si operatori de comparatie.

Sterge anumite tupluri dintr-o tabela specificand clauza WHERE in declaratia functiei DELETE.
In exemplul urmator sterge angajatii care lucreaza in deparatamenul 10 din tabela EMP. Se poate confirma operatia de stergere prin afisarea tuplurilor sterse cu ajutorul declaratiei lui SELECT.

Daca se omite clauza WHERE toate campurile din tabela vor fi sterse. Al doilea exemplu sterge toate tuplurile din tabela COPY_EMP deoarece nu a fost specificata clauza WHERE.

Se pot sterge si mai multe

sistemului.

TRANZACTII

Procesarea unei Tranzactii

O tranzactie este o operatie asupra unei baze de date care implica una sau mai multe modificari in una sau mai multe tabele.

Exista doua clase de tranzactii. Tranzactii DML care contin un numar oarecare de blocuri DML si pe care ORACLE le trateaza ca o singura entitate sau o singura unitate logica de lucru, si tranzactii DDL care contin un singur bloc DDL.

Nu pot exista situatii "jumatate de drum" in timpul executiei unei tranzactii, asa incat unele modificari specificate in tranzactie sa fie aplicate bazei de date si altele nu. Pentru fiecare tranzactie ori toate modificarile sunt aplicate bazei de date, ori nici una din modificari nu este indeplinita ( sunt toate abandonate - discarded ).

O tranzactie incepe cand prima comanda executabila DML sau DDL este intalnita si se termina in una din urmatoarele situatii :

• Intalneste COMMIT/ROLLBACK
• Comanda DDL se termina
• Anumite erori (DEADLOCK)
• EXIT - iesire din SQL*Plus
• Eroare sistem

Un bloc DDL este executat automat si de aceea implicit incheie o tranzactie.

Dupa incheierea unei tranzactii, urmatorul bloc executabil SQL va lansa automat urmatoarea tranzactie.

Tipurile de tranzactii

Tip	Descriere
Limbajul de manipulare al datelor (DML)	Este constituit din mai multe cereri DML pe care serverul ORACLE le trateaza ca pe o singura entitate sau o unitate logica
Limbajul de definire al datelor (DDL)	Este format dintr-o singura cerere DDL
Limbajul de control al datelor (DCL)	Este format dintr-o singura cerere DCL

Permanentizarea Modificarilor

Pentru ca modificarile sa ramana permanente, ele trebiue executate asupra bazei de date. comanda COMMIT realizeaza permanentizarea modificari- lor; ROLLBACK permite sa abandonam sau sa anulam modificarile. Modifiacrea, sau modificarile, executate asupra bazei de date intre 2 comenzi COMMIT reprezinta o tranzactie. Pana cand tranzactia nu este executata, nici una din modificari nu este vizibila utilizatorilor.

Inlaturarea Modificarilor Nedorite

Modificarile neexecutate pot fi abandonate prin comanda ROLLBACK. ROLLBACK va atribui datelor valorile care acestea le aveau imediat dupa executarea ultimului COMMIT prin anularea tuturor modificarilor facute dupa ultimul COMMIT.

Erorile de Sistem

Cand o tranzactie este intrerupta de o eroare serioasa, de exemplu o eroare de sistem, intreaga tranzactie este anulata. Aceasta previne erorile datorate modificarilor nedorite asupra datelor, si realizeaza intoarcerea tabelelor la starile de dupa ultimul COMMIT. In acest fel SQL*Plus protejea- za integritatea tabelelor.

Anularea automata este cauzata cel mai des de catre o eroare de sistem, ca de exemplu o resetare a sistemului sau o cadere de tensiune. Erorile de tastare a comenzilor, ca de exemplu tastarea gresita a unor nume de coloane sau incercarile de a realiza operatii neautorizate asupra tabelelor altor utilizatori, nu intrerup tranzactia si nu realizeaza anu- larea automata. Aceasta se datoreaza faptului ca aceste erori sunt detectate in cursul compilarii (de catre PARSER) ( cand un bloc SQL este scanat si verificat), si nu in timpul executiei.

O tranzactie noua este lansata urmand unui COMMIT sau ROLLBACK - adica cand primul bloc executabil DML sau DDL este intalnit.

Semnificatia Tranzactiilor

ORACLE asigura consistenta datelor bazata pe tranzactii. Tranzacti ile dau utilizatorului mai multa flexibilitate si control la lucrul asupra datelor, si asigura consistenta datelor in cazul unei erori a procesului utilizator sau a unei erori de sistem.

Tranzactiile ar trebui sa contina doar acele comenzi DML care realizeaza o singura modificare asupra datelor. De exemplu un transfer de fonduri (sa spunem 1000$) intre 2 conturi ar trebui sa implice un debit al unui cont de 1000$ si un credit al altui cont de 1000$. Ambele actiuni ar trebui sa se incheie cu succes sau sa dea eroare impreuna. Creditul nu ar trebui executat fara debit.

Controlul Tranzactiilor cu Instructiuni SQL

Urmatoarele instructiuni SQL sunt utilizate cand apar executii (commit) sau refaceri (rollback) :

• COMMIT[WORK]
• SAVEPOINT nume_savepoint
• ROLLBACK[WORK] to [SAVEPOINT] nume_savepoint

De notat ca COMMIT si ROLLBACK sunt instructiuni (blocuri) SQL.

Cele 3 blocuri SQL utilizate pentru controlul tranzactiilor sunt explicate mai jos:

COMMIT[WORK]

Sintaxa : COMMIT[WORK];

• Permanentizeaza schimbarile in tranzactia curenta
• Sterge toate punctele de salvare (savepoint) din tranzactie
• Termina tranzactia
• Elibereaza toate blocarile (Lock) tranzactiei
• Cuvantul cheie WORK este optional
• Utilizatorul trebuie sa expliciteze sfarsitul tranzactiei in programul aplicatie utilizand COMMIT (sau ROLLBACK). Daca nu se executa explicit tranzactia si programul se termina anormal, ultima tranzactie executata va fi anulata.
• Executii implicite (commit) apar in urmatoarele situatii :
  • inainte de o comanda DDL
  • dupa o comanda DDL
  • la inchiderea mormala a unei baze de date

Blocurile DDL cauzeaza mereu executii (commit) in timpul executiei lor. Daca introduceti un bloc DDL dupa cateva blocuri DML, blocul DDL cauzeaza aparitia unui commit inaintea propriei executii, incheind tranzactia curenta. Astfel daca blocul DDL este executat pana la capat, este si inregistrat.

SAVEPOINT

Sintaxa : SAVEPOINT nume_savepoint Exemplu : SAVEPOINT terminare_actualizari

• Poate fi utilizat pentru a imparti o tranzactie in bucati mai mici
• Punctele de salvare (savepoints) permit utilizatorului sa retina toata munca sa la orice moment din timp, cu optiunea de a inregistra mai tarziu totul sau a anula totul sau o parte din ea. Astfel, pentru o tran- zactie lunga, se pot salva parti din ea, pe masura executiei, la sfarsit inregistrandu-se sau refacandu-se continutul initial. La aparitia unei erori nu trebuie executat din nou fiecare bloc.
• La crearea unui nou punct de salvare cu acelasi nume ca al unuia dinainte, primul punct este sters.
• Numarul maxim de puncte de salvare pentru un proces utilizator este implicit 5. Aceasta limita poate fi schimbata. ROLLBACK[WORK] to [SAVEPOINT] nume_punct_salvare
• Instructiunea ROLLBACK este utilizata pentru a reface un lucru.
• Cuvantul cheie "work" este optional. Intoarcerea la un punct de salvare este de asemenea optionala.
• Daca se utilizeaza ROLLBACK fara clauza TO SAVEPOINT, atunci :
  • se termina tranzactia
  • se anuleaza modificarile din tranzactia curenta
  • sterge toate punctele de salvare din tranzactie
  • elibereaza blocarile tranzactiei

Intoarcerea la Nivel de Bloc

O parte a unei tranzactii poate fi anulata. Daca un singur bloc DML da eroare, ORACLE 9i va intoarce inapoi doar acel bloc. Aceasta facilitate este cunoscuta ca STATEMENT LEVEL ROLLBACK. Intoarcerea la nivel de bloc inseamna daca un singur segment DML da eroare la executia unei tranzactii, efectul lui este anulat, dar schimbarile realizate de precedentul bloc DML in tranzactie nu vor fi anulate si pot fi inscrise (COMMIT) sau intoarse (ROLLBACK) explicit de catre utilizator.

Daca blocul este unul de tip DDL, inscrierea (commit) care precede imediat acest bloc nu este anulata (schimbarile au fost facute deja permanen- te). ORACLE realizeaza intoarcerea la nivel de bloc prin crearea unui punct de salvare implicit inainte de executarea fiecarei comenzi DML. Utilizatorul nu poate referi caest punct de salvare in mod direct.

Astfel, daca va intoarceti la un punct de salvare, atunci:

• anulati o parte din tranzactie
• se retine punctul de salvare pentru intoarcere, dar se pierd toate celelalte punct create dupa punctul de salvare numit.
• se elibereaza toate blocarile de tabele si linii.

Intoarceri Implicite

Intoarcerile implicite apar cand se intalnesc terminari anormale ale executiei (de exemplu cand se intrerupe un proces utilizator). Intoarcerile implicite la nivel de bloc apar la eroarea de executie a unui bloc.
Este recomandat ca tranzactiile sa se termine explicit utilizand COMMIT[WORK] ori ROLLBACK[WORK].

Comenziile COMMIT sau ROLLBACK pot fi date manual sau automat prin utilizarea optiunii AUTOCOMMIT a comenzii SET. Optiunea AUTOCOMMIT controleaza cand schimbarile intr-o baza de date sunt facute permanente.

Exista doua setari :


SET AUTO[COMMIT] ON COMMIT este utilizat automat la fiecare INSERT, UPDATE sau DELETE
SET AUTO[COMMIT] OFF COMMIT poate fi utilizata de utilizator explicit. De asemenea, COMMIT se executa cand apare comanda, cand se executa comenzile DROP, ALTER sau CREATE, sau la iesirea din SQL*Plus. ROLLBACK poate fi executat explicit de catre utilizator pentru refacerea bazei de date.

In acest exemplu am sters inregistrarea cu numele SMITH din tabela EMP, si am inserat o noua linie in tabela DEPT. Schimbarile au fost facute permanente de caluza COMMIT.

Aici am sters toate inregistrarile din tabela COPIE_EMP, ca apoi sa renuntam la schimbarile facute cu ajutorul clauzei ROLLBACK.

Consistenta la Citire

Utilizatorii bazelor de date fac doua tipuri de accesuri asupra bazelor de date:

• Operatii de citire ( SELECT )
• Operatii de scriere ( INSERT, UPDATE, DELETE )

Cititorului si scriitorului unei baze de date trebuie sa i se garanteze o vedere consistenta asupra bazei de date. Cititorii nu trebuie sa vizulaizeze o data care este in curs de modificare. Si scriitorii trebuie sa fie siguri ca schimbarile intr-o baza de date sunt facute intr-un mod consistent : schimbarile facute de un scriitor sa nu distruga sau sa intre in confilct cu schimbarile pe care le face un alt scriitor.

Scopul consistentei la citire este acela de a asigura faptul ca fiecare utilizator vede data ca fiind cea de la ultimul COMMIT, inainte ca o operatie DML sa inceapa.

Consistenta la citire este implementata prin tinerea unor copii partiale ale bazei de date in segmente de intoarcere (ROLLBACK).

Cand de executa operatii de scriere intr-o baza de date, ORACLE va face o copie a datelor onainte de schimbare si o va scrie intr-un segment de intoarcere.

Toti cititorii, exceptandu-i pe cei care au facut schimbarile, inca mai vad baza de date care exista inainte ca schimbarile sa fie facute - ei vad segmentul de intoarcere de fapt.

Oricum, inainte ca schimbarile sa fie facute permanente in baza de date, doar utilizatorul care modifica datele poate sa vada baza de date cu alteratiile incorporate. Toti ceilalti vad baza nemodificata ( fereastra din segmentul de intoarcere ). Aceasta garanteaza citirea unor date consistente care nu fac subiectul unor modificari in curs.

Cand un bloc DML se executa, schimbarile facute in baza de date devin vizibile oricarui utilizator care executa SELECT. Modificarile sunt facute 'universale' si acum toti utilizatorii vad baza de date cu modificarile incorporate.

Spatiul ocupat de catre 'vechile' date in segmentul de intoarcere este eliberate pentru a fi reutilizat.

Daca tranzactia este anulata (ROLLBACK), atunci toate schimbarile sunt 'anulate' :

• Versiunea veche ('originala') a bazei de date aflata in segmentul de intoarcere este scrisa inapoi ('recuperata') in baza de date.
• Toti utilizatorii vad baza de date existenta inainte de inceperea tranzactiei.

  Tranzactii de Citire

  Implicit, modelul consistent al ORACLE RDBMS garanteaza ca rezultatul executiei unui bloc este consistenet. Oricum, in anumite situatii se poate dori chestionarea unor blocuri multiple asupra datelor din mai multe tabele si se doreste asigurarea ca datele sunt consistente. Adica, re- zultatele in orice tabela sunt consistente in timp in raport cu rezultatele din orice alta tabela.

  Linia SQL : SET TRANSACTION READ ONLY este utilizata pentru a incepe o tranzactie de citire exclusiv.

  Consistenta la citire pe care READ ONLY o furnizeaza este implementa- ta in acelasi fel cu consistenta la nivel de bloc - utilizand segmente de intoatcere. Fiecare bloc vede implicit o fereastra consistenta a datelor la momentul inceperii blocului. Aceasta facilitate este foarte folositoare pentru rapoarte care ruleaza mai multe chestionari asupra mai multor tabele in timp ce alti utilizatori actualizeaza aceleasi tabele.
  

  Nota :

  • SET TRANSACTION READ ONLY este utilizata pentru a incepe o tran- zactie doar de citire.
  • Doar cereri ( blocuri SELECT ) sunt permise. Comenzile DML nu sunt permise. SELECT FOR UPDATE va genera o eroare.
  • O instructiune COMMIT, ROLLBACK, sau un bloc DDL va termina tran- zactia de citire ( de retinut ca blocurile DDL genereaza implicit suprascrieri - COMMIT ). Cu blocuri DDL, nu este data nici o indi- catie referitoare la faptul ca tranzactia se termina implicit.
  • In timpul tranzactiei de citire, toate cererile se refera la aceeasi copie a bazei de date ( schimbarile sunt efectuate inainte ca tranzactia de citire sa inceapa).
  • Alti utilizatori pot continua sa citeasca sau sa modifice datele.