Manipularea datelor - Limbajul de manipulare a datelor (DML)

Manipularea datelor

Scopul lectiei:

În aceasta lectie veti învata cum sa inserati înregistrari într-o tabela, sa reactualizati înregistrari existente în tabela si sa stergeti înregistrari existente dintr-o tabela. Veti mai învata si cum sa controlati tranzactii cu comenzile COMMIT, SAVEPOINT si ROLLBACK.

Limbajul de manipulare a datelor

Limbajul de manipulare a datelor (DML) este partea de baza a SQL. Când doriti sa adaugati, sa modificati sau sa stergeti date dintr-o baza de date, executati o comanda DML. O colectie de comenzi DML care formeaza o unitate logica de lucru se numeste tranzactie.

Considerati o baza de date din domeniul bancar. Când un client al bancii doreste sa transfere bani dintr-un depozit într-un cont curent, tranzactia ar putea consta în 3 operatii separate: scade suma din depozit, creste suma din contul curent, înregistreaza tranzactia în jurnalul de tranzactii. Serverul Oracle trebuie sa garanteze ca toate cele 3 comenzi SQL sunt executate în asa fel încât sa mentina echilibrul necesar între conturi. Când ceva opreste una dintre comenzile tranzactiilor sa se execute, atunci celelalte comenzi ale tranzactiilor trebuie sa fie anulate.

Adaugarea unei noi înregistrari într-un tabel:

Graficul de mai sus ilustreaza a 21221g64v daugarea unui nou departament în tabelul DEPT.

Prin intermediul comenzii INSERT se pot introduce noi înregistrari într-un tabel.

În descrierea sintaxei:

tabel este numele tabelului (bazei de date).

coloana este numele coloanei din tabelul respectiv.

valoare este valoarea corespunzatoare coloanei.

Nota Aceasta comanda folosind clauza VALUES adauga numai câte un rând odata la un tabel.

Deoarece se poate insera o noua înregistrare care contine valori pentru fiecare coloana, lista coloanelor nu este necesara în sintaxa INSERT. Totusi, daca aceasta nu este precizata, valorile trebuie enumerate conform ordinii coloanelor din tabel.

Pentru claritate, precizati lista coloanelor în sintaxa INSERT. Încadrati doar sirurile de caractere si datele între ghilimele simple; nu încadrati si valorile numerice.

Metode de inserare a valorilor NULL:

Asigurati-va ca pentru coloana vizata este permisa valoarea NULL, verificând câmpul Null? furnizat de comanda SQL*Plus DESCRIBE.

Serverul Oracle aplica automat toate constrângerile de integritate, de domeniu si tip pentru date. Orice coloana care nu este specificata în lista explicit va primi o valoare nula în noua înregistrare.

Inserarea de valori speciale folosind functii SQL:

Se pot folosi pseudocoloane pentru a introduce valori speciale în tabele.

Exemplul de mai sus înregistreaza informatia pentru angajatul Green în baza de date EMP. Pentru a introduce data si ora curenta în câmpul HIREDATE este folosita functia SYSDATE.

Se poate de asemeni folosi functia USER la introducerea de valori într-un tabel. Functia USER furnizaza numele utilizatorului curent.

Verificarea adaugarilor la tabel:

Inserarea de valori de tip data calendaristica:

Formatul DD-MON-YY este de obicei folosit pentru a insera o valoare de tip data calendaristica. Cu acest format, secolul este implicit cel curent. Deoarece data contine de asemenea informatii despre timp, ora implicita este 00:00:00.

Daca o data calendaristica necesita specificarea altui secol sau ora, trebuie folosita functia TO_DATE.

Exemplul din figura înregistreaza informatia despre angajatul Aromano în baza de date EMP. Câmpul HIREDATE primeste valoarea February 3, 1997.

Daca formatul RR este setat, secolul poate sa fie diferit de cel curent.

Inserarea de valori folosind variabile de substitutie:

Se poate scrie o comanda INSERT care sa permita utilizatorului sa adauge valori interactiv folosind variabilele de substitutie SQL*Plus.

Exemplul de mai sus înregistreaza informatia pentru un departament în tabelul DEPT. Numarul departamentului, numele si locatia sunt cerute interactiv utilizatorului.

Pentru valori de tip data calendaristica sau sir de caractere, ampersandul si numele variabilei sunt încadrate de ghilimele simple (apostroafe).

Crearea unui script pentru manipularea datelor:

Comanda împreuna cu variabilele de substitutie pot fi salvate într-un fisier si acesta poate fi executat. De fiecare data când se executa fisierul, sunt cerute valori noi pentru variabile. Prin intermediul comenzii SQL*Plus ACCEPT, mesajele afisate la cererea introducerii valorilor pot fi modificate.

Exemplul din figura înregistreaza informatia pentru un departament în tabelul DEPT. Utilizatorului îi sunt cerute numarul departamentului, numele si locatia acestuia, folosind mesajele de prompt stabilite în ACCEPT.

Parametrul de substitutie SQL*Plus nu trebuie precedat de & când este referit într-o comanda ACCEPT. Pentru a continua o comanda SQL*PLUS pe linia urmatoare se foloseste o linie. (-)

Copierea înregistrarilor dintr-un alt tabel:

Comanda INSERT poate fi folosita pentru a adauga înregistrari într-un tabel, valorile pentru câmpuri fiind derivate dintr-un tabel existent. Pentru aceasta se foloseste, în locul clauzei VALUES, o subinterogare.

Sintaxa este urmatoarea:

unde tabel este numele tabelului (bazei de date).

coloana este numele coloanei din tabelul în care se face inserarea.

subinterogare este subinterogarea care returneaza înregistrarile în tabel.

Numarul si tipul câmpurilor (coloanelor) din lista specificata în comanda INSERT trebuie sa corespunda numarului si tipului valorilor din subinterogare.

Modificarea datelor dintr-un tabel:

Figura de mai sus arata modificarea numarului departamentului pentru Clark din 10 în 20.

Actualizarea înregistrarilor:

Înregistrarile existente pot fi modificate folosind comanda UPDATE.

În sintaxa de mai sus:

tabel este numele tabelului (bazei de date).

coloana este numele coloanei din tabelul în care se face modificarea.

valoare este valoarea sau subinterogarea ce corespunde coloanei.

conditie identifica înregistrarile care trebuie modificate si este alcatuita din expresii, nume de coloane, constante, subinterogari si operatori de comparare.

Puteti avea confirmarea operatiei de modificare prin interogarea tabelului, afisând rândurile modificate.

Nota: În general, folositi cheia primara pentru a identifica o singura înregistrare. Folosirea altor coloane poate determina modificarea mai multor înregistrari. De exemplu, identificarea unei singure înregistrari în tabelul EMP prin nume poate fi periculoasa, deoarece pot exista mai multi angajati cu acelasi nume.

Comanda UPDATE modifica anumite înregistrari daca este specificata clauza WHERE. Exemplul urmator transfera angajatul cu numarul 7782 (Clark) la departamentul 20.

Daca omiteti clauza WHERE, vor fi modificate toate înregistrarile din baza de date.

Nota: Tabelul EMPLOYEE contine aceleasi date ca si EMP.

Modificarea înregistrarilor folosind subinterogari dupa mai multe câmpuri:

În clauza SET a unei comenzi UPDATE pot fi implementate subinterogari dupa mai multe câmpuri.

Sintaxa:

Actualizarea înregistrarilor folosind valori dintr-un alt tabel:

Puteti folosi subinterogari în comenzi UPDATE pentru a modifica înregistrari dintr-un tabel. Exemplul de mai sus arata cum se fac actualizari în tabelul EMPLOYEE pe baza valorilor din tabelul EMP. Este schimbat numarul de departament pentru toti angajatii cu aceeasi slujba ca a celui cu numarul 7788; noul lor numar de departament devenind egal cu al acestuia.

Violarea constrângerii de integritate:

Daca încercati sa atribuiti unui câmp o valoare care este legata de o constrângere de integritate, va rezulta o eroare.

În exemplul de mai sus, departamentul cu numarul 55 nu exista în tabelul parinte, DEPT, si astfel veti obtine eroarea parent key violation ORA-02291.

Nota: Constrângerile de integritate asigura ca datele adera la un set predefinit de reguli. O lectie urmatoare va dezvolta acest subiect mai pe larg.

Stergerea unei înregistrari dintr-un tabel

Figura de mai jos ilustreaza stergerea departamentului DEVELOPMENT din tabelul DEPT (presupunând ca nu exista constrângeri definite pe tabelul DEPT).

Stergerea înregistrarilor:

Puteti sterge înregistrari dintr-un tabel folosind DELETE.

În sintaxa:

tabel este numele tabelului (bazei de date).

conditie identifica înregistrarile care trebuie sterse si este alcatuita din expresii, nume de coloane, constante, subinterogari si operatori de comparare.

Puteti sterge anumite înregistrari prin specificarea clauzei WHERE în comanda DELETE. Exemplul de mai sus sterge departamentul DEVELOPMENT din tabelul DEPARTMENT. Va puteti asigura ca stergerea s-a facut corect, încercând afisarea rândurilor sterse printr-un SELECT.

Exemplu:

Stergeti toti cei angajati dupa data de 1 Ianuarie, 1997.

Daca ati omis clauza WHERE, toate rândurile din tabel vor fi sterse. Cel de-al doilea exemplu din figura sterge toate rândurile din tabelul DEPARTMENT, deoarece nu a fost specificata nici o clauza WHERE.

Nota: Tabelul DEPARTMENT contine aceleasi date ca si DEPT.

Stergerea înregistrarilor folosind valori dintr-un alt tabel:

Puteti folosi subinterogari pentru a sterge înregistrari dintr-un tabel, folosind informatiile din altul. Exemplul de mai sus sterge toti angajatii care sunt în departamentul 30. Subinterogarea cauta în tabelul DEPT numarul de departament pentru SALES, apoi furnizeaza numarul de departament interogarii principale, care sterge înregistrari din EMP pe baza acestuia.

Violarea constrângerii de integritate:

Daca încercati stergerea unei înregistrari care contine un câmp cu o valoare legata de o constrângere de integritate, veti obtine o eroare.

În exemplul de mai sus se încearca stergerea departamentului cu numarul 10 din tabelul DEPT, dar aceasta provoaca o eroare, deoarece numarul de departament este folosit ca si cheie externa în tabelul EMP. Daca înregistrarea parinte pe care încercati sa o stergeti are înregistrari fii, atunci veti primi un mesaj de eroare: child record found violation ORA - 02292.

Tranzactii în baze de date:

Serverul Oracle asigura consistenta datelor  pe baza tranzactiilor. Tranzactiile va ofera mai multa flexibilitate si control la modificari aduse datelor si asigura consistenta datelor în eventuala cadere a sistemului sau în cazul unei erori a procesului utilizator.

Tranzactiile constau din comenzi DML care se constituie într-o schimbare consistenta asupra datelor. De exemplu, un transfer de fonduri între doua conturi ar trebui sa includa debitul unui cont si creditul celuilalt cu o aceeasi suma de bani. Ambele actiuni ar trebui fie sa reuseasca împreuna, fie sa esueze împreuna. Operatia de creditare nu ar trebui sa poata fi efectuata fara ca cea de debit sa aiba succes.

Tipuri de tranzactii

Când începe si când se termina o tranzactie?

O tranzactie începe când este întalnita prima comanda SQL executabila si se termina la aparitia unuia dintre evenimentele:

O comanda COMMIT sau ROLLBACK

O comanda DDL, de exemplu CREATE

O comanda DCL

Utilizatorul iese din SQL*Plus

'Pica' sistemul.

Dupa ce se încheie o tranzactie, urmatoarea tranzactie va începe automat la prima comanda SQL executabila întâlnita.

O comanda DDL sau DCL este commited (modificarile facute sunt salvate) automat si de aceea încheie în mod implicit o tranzactie.

Comenzi pentru controlul explicit al tranzactiilor:

Puteti controla logica tranzactiilor folosind comenzile COMMIT, SAVEPOINT si ROLLBACK.

Nota: SAVEPOINT nu este ANSI standard SQL.

Nota: În SQL*Plus mai este disponibila si o a treia comanda. Comanda SQL*Plus AUTOCOMMIT poate fi setata ON sau OFF. Daca este setata pe ON, fiecare comanda DML individuala duce la salvarea modificarilor, imediat ce este executata. Nu se mai poate reveni la situatia dinainte (un rollback nu mai este posibil). Daca este setata pe OFF, COMMIT poate fi data explicit. De asemeni, COMMIT este executata odata cu o comanda DDL sau la iesirea din SQL*Plus.

Caderile sistemului: Când o tranzactie este întrerupta de o cadere a sistemului, întreaga tranzactie este automat pierduta (este 'rolled back'). Aceasta împiedica eroarea sa determine schimbari nedorite asupra datelor si reface starea bazelor din momentul ultimului COMMIT. (explicit sau implicit). Astfel, SQL*Plus pastreaza integritatea tabelelor (bazelor de date).

Salvarea modificarilor:

Fiecare modificare efectuata în timpul tranzactiei este temporara pâna când apare un 'commit' (pâna la 'salvarea' tranzactiei).

Starea datelor înainte de un COMMIT sau ROLLBACK:

Operatiile de manipulare a datelor afecteaza initial buffer-ul bazei de date; de aceea, starea initiala a datelor poate fi refacuta.

Utilizatorul curent poate urmari schimbarile facute prin interogarea tabelelor.

Alti utilizatori nu pot vedea modificarile facute de utilizatorul curent. Serverul Oracle instituie o consistenta la citire pentru a se asigura ca fiecare utilizator vede datele asa cum existau ele în momentul ultimei salvari.

Înregistrarile afectate sunt protejate (locked); alti utilizatori nu pot face modificari asupra lor.

Puteti salva toate modificarile prin folosirea comenzii COMMIT. Dupa executarea unui COMMIT:

Modificarile datelor sunt salvate în baza de date.

Starea anterioara a datelor nu mai poate fi refacuta.

Toti utilizatorii pot vedea rezultatele tranzactiei.

Protectia înregistrarilor modificate este înlaturata; aceste înregistrari pot fi modificate de alti utilizatori.

Toate savepoint-urile sunt sterse.

Exemplul de mai sus modifica tabelul EMP si seteaza numarul de departament pentru angajatul numarul 7782 (Clark) la 10. Apoi face ca modificarea sa devina permanenta prin comanda COMMIT.

Exemplu:

Creati un nou departament ADVERTISING cu cel putin un angajat. Salvati modificarile facute.

Stergerea modificarilor (refacerea starii initiale):

Puteti anula modificarile temporare folosind comanda ROLLBACK. Dupa un ROLLBACK:

Modificarile facute sunt pierdute.

Starea anterioara a datelor este refacuta.

Protectia asupra înregistrarilor implicate este ridicata.

Exemplu:

Încercând sa stergeti o înregistrare din tabelul TEST, puteti sterge accidental întreg tabelul. Puteti corecta greseala, iar apoi sa dati comenzile corecte si sa salvati modificarile.

Anularea modificarilor pâna la un savepoint:

Puteti crea un marcaj în cadrul tranzactiei curente folosind comanda SAVEPOINT. Astfel, tranzactia poate fi împartita în sectiuni mai mici. Puteti apoi anula modificarile temporare pâna la acel marcaj folosind comanda ROLLBACK TO SAVEPOINT.

Daca creati un al doilea savepoint cu acelasi nume ca unul anterior, savepoint-ul anterior este sters.

Rollback la nivel de comanda:

Se poate anula o parte din tranzactie printr-un rollback implicit daca este detectata o eroare la executia unei comenzi. Daca o singura comanda DML esueaza în timpul executiei unei tranzactii, efectul ei este anulat printr-un rollback la nivel de comanda, dar schimbarile facute de comenzile DML anterioare în tranzactie nu vor fi anulate. Ele pot fi salvate (commited) sau anulate (rolled back) în mod explicit de catre utilizator.

Oracle executa o comanda COMMIT implicita înainte si dupa orice comanda DDL.

Deci, chiar daca comanda voastra DDL nu se executa cu succes, nu puteti anula comenzile anterioare pentru ca serverul a executat un commit (a salvat modificarile).

Terminati-va tranzactiile explicit, prin executarea unei comenzi COMMIT sau ROLLBACK.

Consistenta la citire:

Utilizatorii bazei de date o acceseaza pe aceasta în doua moduri:

Operatii de citire (comanda SELECT);

Operatii de scriere (comenzile INSERT, UPDATE, DELETE).

Consistenta la citire este necesara pentru ca:

Cei care citesc/modifica datele sa aiba o vedere consistenta a datelor;

Cei care citesc datele sa nu vada datele care sunt în curs de modificare;

Cei care modifica datele sa aiba siguranta ca schimbarile în baza de date se fac în mod consistent;

Schimbarile facute de un utilizator sa nu intre în conflict sau sa afecteze schimbarile facute de un altul.

Scopul consistentei la citire este sa asigure ca fiecare utilizator vede datele în starea în care erau la ultima salvare, înainte sa înceapa o operatie DML.

Implementarea consistentei la citire:

Consistenta la citire este implementata în mod automat. Este pastrata o copie partiala a bazei de date în segmente rollback.

Când se realizeaza o operatie de insertie, actualizare sau stergere asupra bazei de date, serverul Oracle scrie o copie a datelor dinainte de modificare într-un segment rollback.

Toti utilizatorii, cu exceptia celui care a initiat modificarea, vad înca baza de date în starea de dinainte de începerea modificarii; ei vad datele din segmentul rollback.

Înainte ca schimbarile sa fie salvate în baza de date, numai utilizatorul care modifica datele vede baza de date modificata, toti ceilalti vazând datele din segmentul rollback. Aceasta garanteaza ca utilizatorii citesc consistent datele care nu sufera schimbari în chiar acel moment.

Când o comanda DML este salvata, schimbarea facuta în baza de date devine vizibila oricui executa o comanda SELECT. Spatiul ocupat de 'vechile' date din segmentul rollback este eliberat pentru a fi reutilizat.

Daca tranzactia este anulata, schimbarile sunt la rândul lor anulate.

Versiunea originala mai veche a datelor din segmentul rollback este scris înapoi în tabel.

Toti utilizatorii vad baza de date asa cum era înainte de a începe tranzactia.

Ce sunt lacatele?

Lacatele sunt mecanisme care previn interactiunea destructiva între tranzactii ce acceseaza aceeasi resursa: fie un obiect utilizator (de ex. tabele sau înregistrari), fie obiecte sistem care nu sunt vizibile utilizatorilor (de ex. structuri de date partajate si înregistrari "data dictionary").

Cum protejeaza Oracle datele

Protejarea unei baze de date Oracle este automatizata în întregime si nu necesita actiuni din partea utilizatorului. Implicit, protejarea are loc pentru toate comenzile SQL cu exceptia lui SELECT. Mecanismul implicit de protectie în Oracle foloseste în mod automat cel mai mic nivel aplicabil de restrictionare, astfel furnizând cel mai mare grad de concurenta existent, precum si integritate maxima a datelor. Oracle permite si protejarea datelor manual de catre utilizator.

Modurile de protectie

Oracle foloseste doua moduri de protectie într-o baza de date multiutilizator.

Rezumat:

Puteti manipula datele dintr-o baza de date utilizând comenzile: INSERT, UPDATE si DELETE.

Puteti controla modificarile datelor utilizând comenzile: COMMIT, SAVEPOINT si ROLLBACK.

Serverul Oracle garanteaza o vedere consistenta a datelor în orice moment.

Protejarea poate fi implicita sau explicita.

În aceste exercitii, veti adauga noi înregistrari la tabelul MY_EMPLOYEE, veti actualiza si sterge date din tabel si va veti controla tranzactiile.

Exercitii:

Inserarea de date în tabelul MY_EMPLOYEE.

Executati scriptul lab9_1.sql pentru a crea baza de date MY_EMPLOYEE, care va fi folosita pentru laborator.

Descrieti structura tabelului MY_EMPLOYEE pentru a identifica numele câmpurilor.

Adaugati prima înregistrare a tabelului din exemplul de mai jos. Nu folositi lista coloanelor în clauza INSERT.

Introduceti în tabelul MY_EMPLOYEE si al doilea rând de date din exemplul de mai sus. De data aceasta, specificati coloanele explicit în clauza INSERT.

Verificati adaugarea la tabel.

Creati un script numit loademp.sql pentru a insera înregistrari în tabelul MY_EMPLOYEE interactiv. Cereti utilizatorului numele mic(FIRST_NAME), numele de familie (LAST_NAME) si salariul fiecarui angajat. Concatenati prima litera a numelui mic si primele 7 caractere ale numelui de familie pentru a crea userid.

Inserati urmatoarele doua înregistrari în tabel, prin intermediul scriptului creat.

Verificati adaugarile.

Salvati modificarile.

Stergerea si actualizarea datelor din tabelul MY_EMPLOYEE.

Modificati numele de familie al angajatului cu ID = 3 în Drexler.

Modificati salariul la 1000 pentru toti cei cu salariul mai mic decat 900.

Verificati modificarile facute.

Stergeti-o pe Betty Dancs din tabelul MY_EMPLOYEE.

Verificati modificarile.

Salvati toate modificarile temporare.

Controlul tranzactiilor de date asupra tabelului MY_EMPLOYEE.

Inserati în tabel ultima înregistrare rulând scriptul creat la punctul 6.

Verificati adaugarea.

Marcati un savepoint în cadrul tranzactiei.

Stergeti toate înregistrarile din tabel.

Verificati ca tabelul este gol.

Anulati cea mai recenta operatie DELETE fara a anula si INSERT -ul anterior.

Verificati ca rândul nou este intact.

Salvati adaugarea, facând-o permanenta.