C11. Oracle / OR (1)

1. C11. Oracle / OR (1) Date Semistructurate, 2012-2013

2. C11. DS • Oracle – object-relational (1)

3. C11 - DS • Sistemele de BD relationale ofera suport pentru o colectie mica si (aproape) fixa de tipuri de date. In multe aplicatii, insa, trebuie gestionate date de complexitate mai mare. • Pentru a evita limitarile modelului relational pentru anumite aplicatii, s-au dezvoltat modele de date, printre care orientat-obiect, obiect-relational. In plus, XML este un limbaj prin care se permite prezentarea datelor intr-un mod „mai putin structurat” decat al celorlalte modele. • Conceptele de orientare-obiect au influentat suportul unor SGBD-uri pentru tipuri complexe, plus suport pentru orientare-obiect (clase, mostenire, suprascriere de metode, instantiere…).

4. C11 - DS • Sistemele de BD obiectuale s-au dezvoltat pe doua directii: • SGBD orientate-obiect (OODBMS): au fost propuse ca alternativa pentru SGBD relationale, pentru aplicatiile care lucreaza cu date de tipuri complexe; sunt prezente multe concepte din OO • SGBD obiect-relationale (ORDBMS): au fost propuse ca tentativa de a extinde BD relationale cu functionalitati necesare unei arii mai mari de aplicatii (inclusiv pentru date complexe), ca intermediar intre BD relationale si OO

5. C11 - DS • Modele de date O-R (object-relational) • Este o extindere a modelului relational prin includere de facilitati pentru orientare obiect si constructii care sa ofere suport unor tipuri de date noi / adaugate. • Se permite ca atributele tuplelor sa aiba un tip complex, inclusiv valori non-atomice precum relatiile imbricate. • Sunt pastrate fundamentele modelului relational, in particular, accesul declarativ la date, si este mentinuta compatibilitatea cu limbajele relationale existente.

6. C11 - DS • Modele de date O-R (object-relational). Oracle • Observatii: • Nu se respecta 1NF • Multimi / colectii / secvente / referinte la obiecte • Tabele imbricate (nested tables) • Se pastreaza fundamentul matematic al modelului relational: • Tabele unde unele coloane sunt de tip colectie / tabel • Tabele cu inregistrari de tip obiect (tabel de obiecte)

7. C11 - DS • Exemplu: biblioteca; fiecare carte are un titlu, o multime de autori, un editor si o multime de cuvinte cheie => relatia in non-1NF titlu set_autori editor (nume, filiala) set_cuv_cheie carte1 {A11, A12} (nume_E1, fil_E1) {Cuv1, Cuv2 } carte2 {A21, A22} (nume_E1, fil_E1) {Cuv3} • Versiunea 1NF (transformare directa, a.i. sa nu existe grupuri repetitive) • Exemplu: relatia biblioteca in 1NF (flat): titlu autor nume_editor filiala_editor cuv_cheie carte1 A11 nume_e1 fil_e1 cuv1 carte1 A12 nume_e1 fil_e1 cuv1 carte1 A11 nume_e1 fil_e1 cuv2 carte1 A12 nume_e1 fil_e1 cuv2 carte2 A21 nume_e1 fil_e1 cuv3 carte2 A22 nume_e1 fil_e1 cuv3 ...

8. C11 - DS • In 1NF flat: se elimina nevoia de a efectua join-uri, insa se pierde corespondenta 1-la-1 intre tuple si documente si prezinta redundanta a datelor. • Daca se presupune ca toate atributele relatiei depind de „titlu”, atunci procesul de normalizare (-> 3NF) ar avea ca rezultat obtinerea a trei relatii: • Autori (titlu, autor) • Cuvinte_cheie (titlu, cuv_cheie) • Carti (titlu, nume_editor, filiala_editor) • (eventual si Editori) • => Probleme: • multe interogari asupra acestor date presupun efectuare de operatii de join • reprezentarea prin relatii imbricate este mai naturala / potrivita aici

9. C11 - DS • Exemplu: Cursuri: 1 BD 2012 {Tambulea, Navroschi, Ban} {221, 222} 2 DS 2012 {Varga, Navroschi} {232, 531} 3 SO 2012 {Boian, Sterca} {221, 223, 224} Sau: 1 BD 2012 {(Tambulea, 221), (Tambulea, 222), (Navroschi, 221), (Ban, 222)} • Vezi normalizare (1NF flat – 1 tabel, 1NF – 2 sau 3 tabele) • Depinde ce se vrea!!!

10. C11 - DS • Oracle – caracteristici OR • tipuri de obiecte – ierarhii de tipuri – mostenire simpla • tipuri colectie (varrays si tabele imbricate) • tabele obiect (folosite in stocarea obiectelor, permitand consultarea atributelor acestora in mod relational) • functii tabel (au ca rezultat un set de inregistrari, care poate fi folosit in clauza FROM a unei interogari) • view-uri obiect (permit ca datele stocate in format relational sa fie vazute in „stil” OO) • metode (pot fi scrise in PL/SQL, Java sau C) • functii de agregare si functii de sortare definite de utilizator (utilizabile in interogari) • tipuri de date XML (stocare de documente XML)

11. C11 - DS • Tipuri de date • Tipuri si subtipuri de date scalare • numerice: number, real, float, integer, int, smallint, decimal, dec, numeric, natural, positive, binary_integer, ... • caracter: varchar2, varchar, string, long, raw, rowid, char, ... • boolean: boolean • data calendaristica: date • Valori binare: BLOB, CLOB, BFILE • Compuse: inregistrari, colectii • Cursor, referinta • Utilizator (TAD) • XMLType • Tipul unei variabile definita anterior: variabila%TYPE • Tipul unei coloane dintr-un tabel: [schema.]tabel.coloana%TYPE • Tipul unei inregistrari dintr-un tabel, dintr-un cursor: [schema.]tabel%ROWTYPEsau nume_cursor%ROWTYPE

12. C11 - DS • Expresii • Operanzi: constante, variabile, coloane din tabele sau cursoare, valori ale functiilor utilizator sau sistem • Operatori: aritmetici (+, -, *, /), concatenare (||), relationali (=, !=, <, <=, >, >=), logici (not, and, or)In expresii se pot folosi si operatorii: • IS NULL(expresie) - care are valoarea de true daca expresia argument este null, si false in caz contrar • expresie_sir_caractere LIKE sablon • expresie BETWEEN valmin AND valmax • expresie IN (lista_de_valori) • EXISTS • Conversii • Explicite: prin folosirea unor functii de conversie (ex: to_char(i)) • Implicite: la folosirea unor instructiuni de atribuire

13. C11 - DS • Structura bloc anonim: [declare <Constants> <Variables> <Cursors> <User defined exceptions>] begin <PL/SQL statements> end;

14. C11 - DS • Declaratie variabila: <variable_name> [constant] <data_type> [not null] [:= <expression>]; • Atribuire de valori variabilelor: <variable_name> := <expression>; • Sau select <column_list> into <variable_list> from <table> …;

15. C11 - DS Exemplu: declare counter integer := 0; begin counter := counter + 1; end; --------------------------------- declare cs int; ds varchar2(20); begin select cods, denumires into cs, ds from Sectii where cods = 1; end;

16. C11 - DS Exemplu: fie tabelul EMP declare employee_rec EMP%ROWTYPE; max_sal EMP.SALARY%TYPE; begin select EMPNO, ENAME, JOB, MGR, SALARY, HIREDATE, DEPTNO into employee_rec from EMP where EMPNO = 5698; select max(SAL) into max_sal from EMP; end;

17. C11 - DS • Structuri de control: if <condition> then <sequence of statements> [elsif ] <condition> then <sequence of statements> . . . [else] <sequence of statements> end if ; while <condition> loop <sequence of statements>; end loop for <index> in [reverse] <lower bound>..<upper bound> loop <sequence of statements> end loop Obs: o varianta de parcurgere a unui cursor – cu for...loop; alta varianta – vezi comenzile OPEN, FETCH, CLOSE for row_variable in cursor loop <sequence of statements> end loop

18. C11 - DS • Proceduri / functii: create [or replace] procedure <procedure name> [(<list of parameters>)] is <declarations> begin <sequence of statements> end [<procedure name>]; ------------------------------------------------------ create [or replace] function <function name> [(<list of parameters>)] return <data type> is <declarations> begin <sequence of statements> end [<function name>];

19. C11 - DS • Definire parametrii proceduri / functii: <parameter name> [IN | OUT | IN OUT] <data type> [{ := | DEFAULT} <expression>] • Executie proceduri: <proc_name>(<list of parameters>);

20. C11 - DS • Comentarii: -- comentariu pe o linie /* Comentariu pe mai multe linii*/

21. C11 - DS • Exemple: set serveroutput on; declare i int; begin dbms_output.enable; for i in 1..5 loop dbms_output.put_line(to_char(i)); end loop; dbms_output.put_line('----------------'); for i in reverse 1..5 loop dbms_output.put_line(to_char(i)); end loop; end;

22. C11 - DS set serveroutput on; declare cursor c is select id, nume from tc; varc c%ROWTYPE; begin dbms_output.enable; for varc in c loop dbms_output.put_line(varc.nume); end loop; -- sau open c; fetch c into varc; while c%FOUND loop dbms_output.put_line(varc.nume); fetch c into varc; end loop; close c; end;

23. C11 - DS set serveroutput on; declare i int; procedure tipar(i int) as begin dbms_output.enable; dbms_output.put_line('i: ' || to_char(i)); end; begin dbms_output.enable; tipar(10); end;

24. C11 - DS • Colectii • Colectia: multime ordonata de elemente de acelasi tip. • Tipuri colectii: • Vector: cu valori pentru indice intre 1 si o valoare maxima precizata la definire • Tabel indexat: cu valori posibile pentru indice intre -(2^31-1) si (2^31-1) • Tabel: cu valori pentru indice: 1, 2, .... Este asemanator cu un tabel memorat in baza de date unde dimensiunea superioara poate creste (nu e fixata); array variabil

25. C11 - DS • Scenariu general pentru utilizarea unei colectii: • Definirea unui tip de data colectie • Vector:TYPE nume IS {VARRAY | VARYING ARRAY} (dimensiune) OF tip_element [NOT NULL] • Tabel indexat:TYPE nume IS TABLE OF tip_element [NOT NULL] INDEX BY BINARY_INTEGER • Tabel:TYPE nume IS TABLE OF tip_element • Declararea unei variabile de un tip de data astfel definit • Pentru un vector sau tabel este necesara utilizarea unui constructor pentru initializare.Constructorul coincide cu numele colectiei, iar ca argumente se pot folosi valori ale componentelor:variabila:=nume_colectie([lista_de_valori)]; • Cu argumenteledate constructorului se completeaza primele pozitii din colectie. • Initializarea se poate face la declararea variabilei, sau mai tarziu printr-o initializare (instructiune de atribuire). • Pentru un tabel indexat se pot adauga valori prin atribuire, pe orice pozitie (fara ca pozitiile sa fie consecutive).

26. C11 - DS • Observatii – colectii: • Dimensiune: • Dimensiune fixa: vector • Dimensiune variabila: tabel indexat, tabel • Constructor: • Se poate folosi constructor: vector, tabel • Fara constructor (direct atribuiri): tabel indexat • Ocupare: • Pozitii continue: vector, tabel • Orice pozitie, posibil goluri: tabel indexat • Adaugare / stergere: • La / de la sfarsit: vector, tabel • Pe / de pe orice pozitie: tabel indexat • Posibilitate de a defini coloana de tip colectie: • Da: vector, tabel • Nu: tabel indexat • Stocare ca tabel imbricat: • Da: tabel • Nu: vector

27. C11 - DS • Elementele colectiei pot fi: • un scalar (char, number, ...) • tipul unei variabile definita anterior • tipul unei coloane dintr-un tabel existent • tipul liniilor unui tabel sau cursor • o inregistrare • un obiect • un tip colectie • Referire la un element din colectie: indexul si calificarea unei eventuale componente dintr-o inregistrare. • Exemplu:nume_colectie(index)[(index)...]nume_colectie(index).componenta

28. C11 - DS • Metode (proceduri, functii) pentru colectii: • count - numarul de elemente din colectie • exists(n) - returneazatrue daca pe pozitia n din colectie exista o valoare, si false in caz contrar • first, last - furnizeaza prima, respectiv ultima pozitie folosita in colectie. • prior(n), next(n) - dau indexul din fata, respectiv care urmeaza, celui precizat in argument. • extend - extinde o colectie vector sau tabel, care este deja initializata: • extend - adauga un element cu valoarea null (se poate efectua daca la declarare nu s-a folosit restrictia not null) • extend(n) - care adauga n elemente cu valoarea null • extend(n,i) care adauga n elemente cu valoarea elementului de pe pozitia i • trim - elimina elemente de la sfarsitul colectiilor de tip vector si tabel: • trim - elimina ultimul element • trim(n) - elimina ultimele n elemente din colectie • delete - sterge elemente dintr-o colectie tabel indexat: • delete - sterge toate elementele din colectie • delete(n) - sterge elementul de pe pozitia n • delete(m,n) - sterge toate elementele de la pozitia m la pozitia n

29. C11 - DS dbms_output.put_line('v:'); for i in 1..v.count loop dbms_output.put_line(to_char(v(i))); end loop; v.extend; for i in 1..v.count loop dbms_output.put_line(to_char(v(i))); end loop; v(4):=5; for i in 1..v.count loop dbms_output.put_line(to_char(v(i))); end loop; v.extend(2, 2); for i in 1..v.count loop dbms_output.put_line(to_char(v(i))); end loop; v.trim(3); for i in 1..v.count loop dbms_output.put_line(to_char(v(i))); end loop; end; set serveroutput on; declare i int; type tvector is varray (10) of i%type; v tvector; begin dbms_output.enable; v:=tvector(1, 2, 3); --VECTOR dbms_output.put_line('nr elemente:' || to_char(v.count)); if v.exists(3) then dbms_output.put_line('exista elem 3:' || to_char(v(3))); else dbms_output.put_line('nu exista elem 3:'); end if; if v.exists(4) then dbms_output.put_line('exista elem 4:' || to_char(v(4))); else dbms_output.put_line('nu exista elem 4:'); end if; dbms_output.put_line('v - first / last:'); dbms_output.put_line(to_char(v.first)); dbms_output.put_line(to_char(v.last)); dbms_output.put_line(to_char(v.next(2)));

30. C11 - DS ti(6):=6; dbms_output.put_line(to_char(ti.first)); dbms_output.put_line(to_char(ti.last)); if ti.exists(3) then dbms_output.put_line('exista elem 3:' || to_char(ti(3))); else dbms_output.put_line('nu exista elem 3:'); end if; dbms_output.put_line('--'); ti(7):=7; dbms_output.put_line('ti - first / last:'); dbms_output.put_line(to_char(ti.first)); dbms_output.put_line(to_char(ti.last)); ti.delete(6); dbms_output.put_line(to_char(ti.first)); dbms_output.put_line(to_char(ti.last)); ti.delete(3, 8); dbms_output.put_line(to_char(ti.first)); dbms_output.put_line(to_char(ti.last)); end; set serveroutput on; declare i int; type ttindex is table of smallint index by binary_integer; ti ttindex; begin dbms_output.enable; ti(2):=1; --TABLE INDEX dbms_output.put_line('nr elemente:' || to_char(ti.count)); if ti.exists(1) then dbms_output.put_line('exista elem 1:' || to_char(ti(1))); else dbms_output.put_line('nu exista elem 1:'); end if; if ti.exists(2) then dbms_output.put_line('exista elem 2:' || to_char(ti(2))); else dbms_output.put_line('nu exista elem 2:'); end if; dbms_output.put_line('ti - first / last:'); dbms_output.put_line(to_char(ti.first)); dbms_output.put_line(to_char(ti.last));

31. C11 - DS dbms_output.put_line('t:'); for i in 1..t.count loop dbms_output.put_line(to_char(t(i))); end loop; t.extend; for i in 1..t.count loop dbms_output.put_line(to_char(t(i))); end loop; t(3):=5; for i in 1..t.count loop dbms_output.put_line(to_char(t(i))); end loop; t.extend(2, 2); for i in 1..t.count loop dbms_output.put_line(to_char(t(i))); end loop; t.trim(1); for i in 1..t.count loop dbms_output.put_line(to_char(t(i))); end loop; end; set serveroutput on; declare type ttable is table of smallint; t ttable; begin dbms_output.enable; t:=ttable(1, 2, 3); --TABLE dbms_output.put_line('nr elemente:' || to_char(t.count)); if t.exists(1) then dbms_output.put_line('exista elem 1:' || to_char(t(1))); else dbms_output.put_line('nu exista elem 1:'); end if; if t.exists(3) then dbms_output.put_line('exista elem 3:' || to_char(t(3))); else dbms_output.put_line('nu exista elem 3:'); end if; dbms_output.put_line('t - first / last:'); dbms_output.put_line(to_char(t.first)); dbms_output.put_line(to_char(t.last)); dbms_output.put_line('next de 1: ' || to_char(t.next(1))); dbms_output.put_line('next de 2: ' || to_char(t.next(2)));

32. C11 - DS • Next 12 • Oracle (2)