Baze de Date - Limbajul SQL-Intero gări complexe, Vederi- Universitatea din Craiova,

Baze de Baze de DateDate

Anca IonAnca Ion2011-20122011-2012

Baze de Date-Limbajul SQL-Interogări complexe, Vederi-

Universitatea din Craiova,Facultatea de Automatica, Calculatoare si Electronica



Vederi• O vedere în SQL este creată utilizând comada CREATE

VIEW. Pentru a defini o vedere, trebuie să specificăm numele vederii v și interogarea <expresie interogare>, al cărei rezultat va fi salvat în vedere .

• create view v as <expresie interogare>

• Observație: exemplele din acest subcapitol folosesc baza de date Banca din subcapitolul 4.3.



Vederi

• Exemplu: Să considerăm vederea care constă din numele filialelor și numele clienților care au fie conturi, fie credite la aceeași filială.

create view clienti as(select Credit.numefiliala,Client.codclient from

Client,Credit,Imprumuta where ((Client.codclient=Imprumuta.codclient) and

(Credit.nrcredit=Imprumuta.nrcredit )))union(select Cont.numefiliala, Client.codclient from Client,Cont,

Depunewhere ((Client.codclient=Depune.codclient) and

(Cont.nrcont=Depune.nrcont )))



Vederi

• Numele atributelor unei vederi pot fi specificate explicit:

create view SumaTotalaCredite(numefiliala, totalcredite) as

select numefiliala, sum(suma)from Creditgroup by numefiliala



Vederi

• Vederile se mai numesc și tabele virtuale și pot apărea în orice loc în care apar și tabelele.

• Folosind vederea precedentă, putem afișa valoarea totală a creditelor de la filiala ING1.

select totalcreditefrom SumaTotalaCreditewhere numefiliala = 'ING1‘

• Dacă dorim să ștergem o vedere, se folosește comanda DROP VIEW:• drop view totalcredite



Modificarea bazei de date

• Comenzile de modificare a bazei de date INSERT, UPDATE, DELETE au fost descrise în capitolul 4.1.

• În acest subcapitol, se vor exemplifica forme mai complexe ale acestor comenzi.



Modificarea bazei de date - DELETE

•Exemple ale operației DELETE:1.Să se șteargă toate conturile de la filiala ING1.

delete from Contwhere numefiliala = 'ING1'

2.Să se șteargă toate creditele cu valori între 2500 și 3000delete from Creditwhere suma between 2500 and 3000

3.Să se șteargă toate conturile de le fiecare filială cu locația în Craiova.

delete from Contwhere numefiliala in (select numefilialafrom Filialawhere oras = 'Craiova')


Anca IonAnca Ion2011-20122011-2012•Comanda DELETE poate conține subinterogări.

•De exemplu, dacă dorim să ștergem conturile care au balanța sub media balantelor băncii, putem scrie:

delete from Contwhere balanta < (select avg (balanta)from Cont)

•Comanda DELETE testează mai întâi dacă fiecare tuplu din relația Cont îndeplinește condiția de a avea balanța mai mică decât media. Apoi toate tuplurile care îndeplinesc această condiție sunt șterse.

Modificarea bazei de date - DELETE


Anca IonAnca Ion2011-20122011-2012•Pentru a insera date într-o relație, se specifică tuplul sau o

interogare al cărei rezultat este o mulțime de tupluri.

•Exemplu:•Dacă dorim să oferim un cadou de 200 ron tuturor clientilor care au împrumuturi la filiala ING1. Numărul de credit va servi ca și număr de cont.

insert into Contselect nrcredit, numefiliala, 200from Creditwhere numefiliala = 'ING1'

Modificarea bazei de date - INSERT


Anca IonAnca Ion2011-20122011-2012•După ce în tabela Cont am adăugat câte un tuplu nou pentru

fiecare număr credit (care a servit ca și număr de cont)de la filiala ING1 și o balanță inițială de 200 ron, este nevoie să adaugăm tupluri în tabela Depune (codclient și nrcredit), făcând astfel legătura între clienți și conturile noi adăugate.

insert into Depuneselect Imprumuta.codclient,

Credit.nrcreditfrom Imprumuta, Creditwhere Imprumuta.nrcredit =

Credit.nrcredit and numefiliala = 'ING1'

Modificarea bazei de date - INSERT


Anca IonAnca Ion2011-20122011-2012•Ca și comenzile DELETE și INSERT, comanda UPDATE poate

conține o subinterogare.•De exemplu:•Să se adauge un procent de 5% pentru conturile a căror balanță este mai mare decât media:

update Contset balanta = balanta * 1.05where balanta >= (select avg (balanta)from Cont)

Modificarea bazei de date - UPDATE


Anca IonAnca Ion2011-20122011-2012•Să presupunem că dorim să adaugam un procent de 6%

pentru conturile cu balanța mai mare de 3000, în timp ce la celelalte conturi adăugăm 5%.•Vom scrie 2 comenzi UPDATE:

update Contset balanta = balanta * 1.06where balanta > 3000

update Contset balanta = balanta * 1.05where balanta <= 3000

•Ordinea în care se execută cele 2 comenzi este foarte importantă. Dacă schimbăm ordinea celor 2 UPDATE-uri, un cont cu o balanță sub 3000, după mărirea cu 5% poate ajunge cu balanța peste 3000. Astfel, acesta se va mări și în a 2-a comandă.



Anca IonAnca Ion2011-20122011-2012•SQL oferă o construcție CASE, care presupune execuția unor

operații de modificare într-o singură comandă UPDATE, eliminând problema ordinii de executare.

update Contset balanta = casewhen balanta <= 3000 then balanta * 1.05else balanta * 1.06end

•Forma generală a acestei construcții este:casewhen pred1 then rezultat1when pred2 then rezultat2. . .when predn then rezultatnelse rezultat0end



Anca IonAnca Ion2011-20122011-2012•Să considerăm următoarea vedere:

create view CrediteFiliale asselect numefiliala, nrcreditfrom Credit

•Operații de inserare, modificare și ștergere pe o vedere pot fi ambigue și dificile în cazul în care vederea se bazează pe mai multe relații. Astfel, multe SGBD-uri au impus următoarele constrângeri pentru a rezolva problemele modificărilor vederilor în cazul în care vederile se bazează pe mai multe relații:

•O modificare este permisă dacă vederea implică doar o relație și nu conține funcții de agregare.

•Exemplu:insert into CrediteFilialevalues ('ING5', 17)

Modificarea unei vederi


Anca IonAnca Ion2011-20122011-2012•Programarea bazelor de date cu JDBC

•JDBC (Java Database Connectivity) este o interfata standard SQL de acces la baze de date. •JDBC este constituita dintr-un set de clase si interfete scrise în Java, furnizând mecanisme standard pentru proiectanții aplicațiilor de baze de date. •JDBC 2.0 API include doua pachete: java.sql, cunoscut ca JDBC 2.0 core API si javax.sql, cunoscut ca JDBC Standard Extension.•JDBC 3.0 realizat in October 2001, introduce mai multe caracteristici aditionale, care includ extensii ale tipurilor de date, capabilitati aditionale pentru MetaData, etc.

Programarea bazelor de date



•Folosind JDBC este usor sa transmitem secvente SQL catre baze de date relationale. •Cu alte cuvinte, nu este necesar sa scriem un program pentru a accesa o baza de date Oracle, alt program pentru a accesa o baza de date Sybase si asa mai departe. •Este de ajuns sa scriem un singur program folosind API-ul JDBC si acesta va fi capabil sa trimita secvente SQL bazei de date dorite. •Bineînteles, scriind codul sursa în Java, ne este asigurata portabilitatea programului.

•Cu JDBC se realizeaza:•conectarea la o baza de date•transmiterea de secvente SQL•prelucrarea rezultatelelor primite de la SGBD



Anca IonAnca Ion2011-20122011-2012 Pașii pentru a lucra cu JDBC sunt:

A. Conectarea la o bază de date

Procesul de conectare la o baza de date implică două operații:1. încarcarea în memorie a unui driver corespunzator2. realizarea unei conexiuni propriu-zise

O conexiune (sesiune) la o baza de date reprezinta un context prin care sunt trimise secvente SQL si primite rezultate. Într-o aplicație pot exista mai multe conexiuni simultan la baze de date diferite sau la aceeași baza.

Clasele si interfetele responsabile cu realizarea unei conexiuni sunt: clasa DriverManager, ce se ocupa cu înregistrarea

driverelor ce vor fi folosite în aplicatie. interfata Driver, pe care trebuie sa o implementeze orice

clasa ce descrie un driver. clasa DriverPropertyInfo. interfata Connection, descrie obiectele ce modeleaza o

conexiune propriu-zisa cu baza de date.



Anca IonAnca Ion2011-20122011-2012B. Încărcarea în memorie a unui driver

•Primul lucru pe care trebuie să-l faca o aplicație în procesul de conectare la o baza de date este sa încarce în memorie clasa ce implementeaza driver-ul necesar comunicarii cu respectiva baza de date.

•Acest lucru poate fi realizat prin mai multe modalitati:1. DriverManager.registerDriver(new sun.jdbc.odbc.JdbcOdbcDriver());2. Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");3. System.setProperty("jdbc.drivers","sun.jdbc.odbc.JdbcOdbcDriver");4. java -Djdbc.drivers=sun.jdbc.odbc.JdbcOdbcDriver

•Clasele Driver (arhiva .jar) trebuie căutate și salvate pentru fiecare SGBD. În anexele acestui curs, există următoarele arhive pentru: -MySQL, PostgreSQL, MSSQL, Oracle.



Anca IonAnca Ion2011-20122011-2012C. Specificarea unei baze de date

•O dată ce un driver JDBC a fost încarcat în memorie, acesta poate fi folosit la stabilirea unei conexiuni cu o baza de date. Având în vedere faptul că pot exista mai multe drivere înregistrate în memorie, trebuie să avem posibilitea de a specifica pe lânga identificatorul bazei de date si driverul ce trebuie folosit.•Aceasta se realizeaza prin intermediul unei adrese specifice, numita JDBC URL, ce are urmatorul format:

jdbc:sub-protocol:identificator_baza_de_date•Câmpul sub-protocol denumeste tipul de driver ce trebuie folosit pentru realizarea conexiunii si poate fi odbc, oracle, sybase, db2 si asa mai departe. Identificatorul bazei de date este un indicator specific fiecarui driver care specifica baza de date cu care aplicatia doreste sa interactioneze. In functie de tipul driver-ului acest identificator poate include numele unei masini gazda, un numar de port, numele unui fisier sau al unui director, etc.

•jdbc:odbc:testdb•jdbc:oracle:thin:@machine_name:1521:nume_bazadedate

•jdbc:mysql://localhost:3306/nume_bazadedate•jdbc:postgresql://localhost:5432/nume_bazadedate

•La primirea unui JDBC URL, DriverManager-ul va parcurge lista driver-elor înregistrate în memorie, pâna când unul dintre ele va recunoaste URL-ul respectiv. Daca nu exista nici unul potrivit, atunci va fi lansata o exceptie de tipul SQLException, cu mesajul No suitable driver.



Anca IonAnca Ion2011-20122011-2012D.Realizarea unei conexiuni

•Metoda folosita pentru realizarea unei conexiuni este getConnection din clasa DriverManager si poate avea mai multe forme:Connection c = DriverManager.getConnection(url);Connection c = DriverManager.getConnection(url, username, password);Connection c = DriverManager.getConnection(url, dbproperies);

•O conexiune va fi folosita pentru:•crearea de secvențe SQL ce vor fi folosite pentru interogarea sau actualizarea bazei•aflarea unor informatii legate de baza de date (meta-date)

•Clasa Connection asigura suport pentru controlul tranzactiilor din memorie catre baza de date prin metodele commit, rollback, setAutoCommit .



Anca IonAnca Ion2011-20122011-2012E. Efectuarea de secvente SQL

•O dată facută conectarea cu DriverManager.getConection(), se poate folosi obiectul Connection rezultat pentru a se crea un obiect de tip Statement, cu ajutorul caruia putem trimite secvente SQL catre baza de date.

• Cele mai uzuale comenzi SQL sunt cele folosite pentru:•interogarea bazei de date (SELECT)•actualizarea bazei de date (INSERT, UPDATE, DELETE)

•Există 2 metode care pot fi aplicate pe un obiect de tip Connection pentru a crea un obiect de tip Statement. Acestea diferă în numărul de argument:

1.createStatement() returnează un obiect de tip Statement. 2.prepareStatement(Q), unde Q este o interogare SQL transmisă ca și String, returnează un obiect de tip PreparedStatement.



Anca IonAnca Ion2011-20122011-2012•Există 4 metode pentru a executa comenzi SQL.

1. executeQuery(Q) primește ca și argument o comandă SQL care trebuie să fie o interogare, și este aplicată unui obiect Statement. Această metodă returnează un obiect de tip ResultSet, care este o colecție de tupluri produse de interogarea Q.

2. executeQuery() este aplicată unui obiect PreparedStatement. Deoarece obiectul PreparedStatement are deja asociata o interogare, nu mai exista parametru transmis acestei metode. Această metodă returnează tot un obiect de tip ResultSet.

3. executeUpdate(U) primește o comandă SQL care nu este interogare, ci Update, Insert sau Delete și este aplicată unui obiect Statement. Efectul executării acestei comenzi se reflectă asupra bazei de date; metoda nu returnează nimic.

4. executeUpdate(), cu nici un argument, este aplicată pe un obiect PreparedStatement. În aceste caz, comanda SQL asociata cu PreparedStatement va fi excutată.



Anca IonAnca Ion2011-20122011-2012•Exemplu 1: Să presupunem că avem o conexiune c și dorim

să executăm interogarea:Select Nume from Student;

•O modalitate este de crea un obiect Statement și apoi să executăm interogarea direct. Rezultatul va fi plasat într-un obiect de tip ResultSet.

Statement s = c.createStatement();ResultSet r = s.executeQuery("SELECT Nume FROM Student");

•Cealaltă alternativă este de a pregăti o interogare și de a o executa mai târziu. Această modalitate este de preferat, atunci când interogarea se execută de mai multe ori.

PreparedStatement s = c. prepareStatement("SELECT Nume FROM Student");ResultSet r = s.executeQuery();

•Dacă interogarea executată nu are nici un parametru, cele 2 modalități sunt similare.



Anca IonAnca Ion2011-20122011-2012•Exemplu 2: Să presupunem că avem o conexiune c și dorim

să executăm operația de inserare:Insert into Student values ('Georgescu');

•Folosind prima variantă cu Statement:

Statement s = c.createStatement();s.executeUpdate("Insert into Student values ('Georgescu') ");

•sau folosind varianta cu PreparedStatement:

PreparedStatement s = c. prepareStatement("Insert into Student values ('Georgescu') ");s.executeUpdate();



Anca IonAnca Ion2011-20122011-2012•Exemplu 3: Să presupunem că dorim să executăm operația de

inserare folosind parametri:

Statement s = c.createStatement();String nume;s.executeUpdate("Insert into Student values (' "+nume+" ')");

Sau

PreparedStatement s=c.prepareStatement("Insert into Student values (?)");s.setString(1,nume);s.executeUpdate();



Anca IonAnca Ion2011-20122011-2012•Exemplu 4: Obținerea și prelucrarea rezultatelor

•Când executăm o interogare si obținem un rezultat de tip ResultSet, dorim să prelucrăm tuplurile din obiectul returnat. Astfel, clasa ResultSet oferă următoarele metode:

1.next()- când se aplică unui obiect ResultSet, un cursor implicit se poziționează la următorul tuplu (la primul tuplu atunci când se aplică prima dată); metoda returnează FALSE, dacă nu există nici un tuplu.

2.getString(i) sau getString(numeAtribut), getInt(i) sau getInt(numeAtribut), getFloat(i) sau getFloat(numeAtribut) și alte metode analoage pentru tipuri de date SQL –returnează valoarea atributului al i-lea (sau a atributului cu denumirea numeAtribut) din tuplul curent.while(r.next()){

String nume = r.getString(1);//sau : String nume = r.getString("Nume"); }

}




•Dati exemple de vederi si operatii de modificare complexe (ca in exemplele de la curs) pe o baza de date aleasa de voi.

Tema

Baze de Date - Limbajul SQL-Intero gări complexe, Vederi- Universitatea din Craiova,

Documents

Transcript of Baze de Date - Limbajul SQL-Intero gări complexe, Vederi- Universitatea din Craiova,