DATABASE PROGRAMMING

1. DATABASE PROGRAMMING

2. Inserarea liniilor – Comanda INSERT Comanda INSERT permite inserarea unor linii într-un tabel. Este o comandă de tranzacţie, la fel ca şi Update, Delete şi Merge. Aceste comenzi nu au commit automat, se poate reveni cu o comandă de tip rollback.

3. Continuare - INSERT • Sintaxa comenzii INSERT este: INSERT INTO <nume tabel>(<coloana 1>, <coloana 2>, ....<coloana n>) VALUES (<valoare 1>, <valoare 2>, ....<valoare n>)

4. Continuare - INSERT • Denumirile coloanelor pot fi scrise explicit, sau pot lipsi (se recomandă numai în cazul în care utilizatorul este foarte sigur asupra structurii tabelului, dar trebuie să fie atent la valorile trecute în clauza VALUES şi la tipul acestora).

5. Fie tabelul d emai sus. Să adăugăm un nou angajat în tabel. Numele lui este David Pop, codul 20 iar salarul de 21000 $. El s-a angajat astăzila departamentul cu codul 90.

6. INSERT INTO employees VALUES( 20, ‘David’, ‘Pop’, 21000, SYSDATE, 90);

7. Structura tabelului Customers Următoarea comandă INSERT generează eroare. De ce? INSERT INTO customers (client_number, first_name, last_name, email) VALUES( 654,‘David’, ‘Pop’, ‘david@yahoo.com’);

8. Eroarea a fost generată de faptul că deşi există câmpul phone în structura tabelului, în comanda de inserare a unei înregistrări (INSERT) lipseşte valoarea aferentă acestui câmp. Din structura tabelului se observa cum coloana phone nu poate avea valori nule, deci valoarea acestui câmp trebuie inclusă în comanda INSERT.

9. Ca urmare, care va fi salariul lui David? INSERT INTO employees (ID, f_name, l_name, hire_date, dept_id) Values( 20, ‘David’, ‘Pop’, SYSDATE, 90);

10. Deoarece în clauza VALUES din comanda INSERT nu este precizată o valoare anume pentru coloana salary, rezultă că salariul va avea valoarea NULL.

11. Structura tabelului D_Packages Ce este greşit în următoarea comandă INSERT? INSERT INTO d_packages VALUES( 2, 56,1000);

12. Deoarece câmpul high_range permite întroducerea unei valori numerice de maxim 3 cifre, 1000 reprezintă o valoare prea mare pentru această coloană.

13. Structura tabelului Customers Dorim să-l adăugăm pe David în tabelul Customers. El nu are adresă de e-mail. Care este cauza erorii? INSERT INTO customers VALUES( 654,‘David’, ‘Pop’, 1234567891452);

14. Comanda INSERT este greşită deoarece, dacă nu există o valoare precizată pentru coloana e-mail, în clauza VALUES trebuie introdusă obligatoriu valoarea NULL.

15. Observaţie • Comanda INSERT se poate executa pe acelaşi tabel (folosind subquery), dar se va executa la infinit – se tot inserează valoarea returnată de subquery în ea însăşi.

16. Comanda UPDATE • Comanda UPDATE este folosită pentru a modifica o valoare într-un tabel. • Dacă în momentul creării unei coloane, aceasta nu are definită o valoare implicită, Oracle introduce valoarea NULL în coloana respectivă.

17. Sintaxa comenzii UPDATE UPDATE <tabel> SET <coloana> = <valoare sau subquery> WHERE <condiţie pt. linia selectată>

18. Observaţii - UPDATE • Valoarea cheii externe trebuie să se regăsească între valorile cheii primare; • Nu putem modifica cheia primară dacă acesteia îi corespunde o cheie externă; în acest caz modificăm sau ştergem cheia externă, după care putem modifica cheia primară.

19. Comanda DELETE • Se foloseşte pentru a şterge linii dintr-un tabel. • Are următoarea sintaxă: DELETE FROM <nume tabel> WHERE <condiţia pentru liniile selectate>

20. Observaţii - DELETE La ştergeri, trebuie să respectăm restricţiile de implementare: • R- Restrictive – nu se poate şterge “părintele” dacă are “copil” • C – on delete Cascade – dacă şterg “părintele” , se şterg şi “copiii” • N – on delete Null – dacă şterg “părintele” , cheia externă devine NULL • D – Default – dacă şterg “părintele” , “copiii” trec pe nivelul “părintelui”

21. Exemple DELETE 1. DELETE FROM copy_f_customers WHERE ID= 123; (şterge clienţii cu codul 123) 2. DELETE FROM copy_f_customers (şterge toate rândurile)

22. Comanda MERGE • În general se foloseşte la tabele de dimensiuni foarte mari. • Actualizează datele din tabelul sursă, care au corespondent în tabelul destinaţie ( UPDATE ) şi inserează în tabelul sursă datele noi ( INSERT ) .

23. Sintaxa comenzii MERGE MERGE INTO <tabel destinaţie> USING <tabel sursă sau subquery> ON <condiţie> WHEN MATCHED THEN UPDATE SET <coloana destinaţie 1> = <coloana sursă| expresie>, <coloana destinaţie 2> = <coloana sursă| expresie>, <coloana destinaţie n> = <coloana sursă| expresie> WHEN NOT MATCHED THEN INSERT (<coloană destinaţie 1 | expresie>, < coloană destinaţie 2 | expresie>, < coloană destinaţie n | expresie>) VALUES (<valoare 1>, <valoare 2>, <valoare n>)

24. TIPURI DE DATE SQL • Varchar2 – şir de caractere de lungime variabilă de max. 4000 caractere; se precizează obligatoriu lungimea şirului; Ex: varchar2(80) • Char – şir de caractere de lungime fixă până la 2000 de caractere; dacă utilizatorul furnizează mai puţine caractere, se adaugă blank- uri la stânga.

25. Number – număr real de până la 38 cifre • Ex: NUMBER(10,2) – partea întreagă formată din 8 cifre, iar partea zecimală din 2. Punctul zecimal nu se numără. • Date – dată calendaristică ( nu poate stoca fracţiuni de secundă ) • Ex: 21-Aug-2007 13:12:10 este corect • Ex: 21-Aug-2007 13:12:10.245 este incorect Formatul implicit al datei calendaristice este DD-MON-YY

26. Timestamp – este o extensie a tipului Date şi permite stocarea datelor până la ordinul nanosecundelor. • Ex: timestamp(3) alocă 3 cifre după secunde, adică măsoară în milisecunde • Timestamp with timezone – stochează diferenţa de fus orar • Timestamp with local timezone – este raportat la diferenţa de fus orar al BD.

27. Interval data types • Returnează timpul scurs între două date calendaristice • Interval year (precizie_an) to month – returnează intervalul de timp exprimat în ani şi luni; precizie_an se referă la numărul de cifre din reprezentarea anului şi are valoarea implicită 2.

28. Interval day (precizie_zile) to second (precizie fracţiuni de secundă) - returnează intervalul de timp exprimat în zile, ore, minute şi secunde, unde precizie_zile se referă la numărul maxim de cifre alocate zilei şi are valoarea implicită 2, iar precizie fracţiuni de secundă reprezintă numărul de cifre zecimale din reprezentarea secundei, având valoarea implicită 6.

29. LONG – se foloseşte în cazul datelor multimedia, caractere de dimesiune de până la 2 G; stocarea este in-line, folosirea acestui tip nu este recomandată. • RAW– se foloseşte pentru date binare, fişiere multimedia (jpg, mp3, wav) de dimensiune variabilă de max. 2000 bytes;

30. CLOB - Caracter Large Object –tip de caractere de dimensiune variabilă – max. 4 Giga • BLOB – Binary Large Object - tip multimedia de dimensiune variabilă – max. 4 Giga • BFILE –Binary File - şir binar de 4 Giga, se reţine doar directorul şi numele fişierului

31. Crearea unui tabel – comanda CREATE TABLE Comanda CREATE TABEL permite crearea unui nou tabel în schema proprie. Prin schema proprie înţelegem mulţimea tuturor obiectelor ce aparţin utilizatorului curent, numit şi proprietar. Comanda are sintaxa: CREATE TABLE <nume tabel> (<nume coloana 1 > <tip coloana 1 [DEFAULT <valoare sau expresie>]> [CONSTRAINT] [DISABLE] <contrângere>, <nume coloana n> <tip coloana n>) sau CREATE TABLE <nume tabel> AS SELECT <coloana 1>, <coloana 2>, <coloana n> FROM <tabel>

32. Reguli: • Atât numele tabelului cât şi numele coloanelor sunt identificatori. • Reamintim ca un identificator ORACLE - poate conţine doar litere, cifre şi caracterele _,$ şi #; - începe obligatoriu cu o literă; - nu poate depăşi 30 de caractere; - nu poate fi un cuvânt rezervat Oracle.

33. Proprietarul are toate drepturile asupra obiectelor pe care le-a creat. Pentru crearea unui tabel în altă schemă, se foloseşte comanda: CREATE ANY TABLE Tabelele utilizatorilor sunt înregistrate în dicţionarul bazei de date (catalogul BD). Acest dicţionar apare în momentul creării bazei de date, acesta conţine şi tabelele interne.

34. În dicţionarul BD se pot vedea toate tabelele existente, dacă folosim comanda : SELECT * FROM DICTIONARY DBA * Toate obiectele din BD ALL * Toate obiectele din schema proprie şi acele obiecte din alte scheme pt. care am privilegii de acces USER * Tables, views, queries, etc….

35. Coloanele unui tabel pot avea valori implicite (Default Value). Valorile implicite pot fi valori literale, expresii sau funcţii SQL (SYSDATE, USER), dar nu pot fi numele altor coloane, pseudocoloane (NEXTVAL sau CURRVAL). Valorile implicite trebuie să fie compatibile cu tipul datei din coloana respectivă.

36. O altă modalitate de a crea un tabel este: CREATE TABLE <nume tabel> AS SELECT <coloana 1>, <coloana 2>, <coloana n> FROM <tabel>

37. EXERCIŢIU: Scrieţi o comandă SQL care crează tabelul “products table”cu următoarea structură: • product_id column, • product_name, • product_description columns, • purchase_date – valoarea implicită a datei curente.

38. CREATE TABLE products (product_id NUMBER(2), product_name VARCHAR2(20), product_description VARCHAR2(100), purchase_date DATE DEFAULT SYSDATE);

39. Fie tabelul de mai sus, conţinând angajaţii unei întreprinderi. Creaţi din acest tabel un alt tabel, care va conţine numele, prenumele şi data angajării angajaţilor care au salariul mai mare de 1000 $.

40. CREATE TABLE copy_employees AS (SELECT first_name, last_name, hire_date FROM employees WHERE salary > 1000);

41. Întrebare: Dacă creăm un nou tabel prin copiere din alt tabel, ce nu se va transmite în noul tabel?

42. Răspuns: Restricţiile de integritate (constraints) din tabelul original nu vor fi preluate de noul tabel.

43. Modificarea structurii unui tabel Inserarea / Ştergerea coloanelor Pentru a insera, şterge sau modifica o coloană a unui tabel, se foloseşte comanda ALTER TABLE, care are următoarea sintaxă: ALTER TABLE <table> ADD (<numele noii coloane> <tipul datei>) DROP COLUMN <numele coloanei> MODIFY (<coloana 1> <tipul datei> [DEFAULT <valoare sau expresie>], <coloana n> <tipul datei>)

44. Modificarea unei coloane Pentru a modifica o coloană, se foloseşte comanda MODIFY care permite: • mărirea dimensiunii unei coloane de tip numeric sau şir de caractere; • micşorarea dimensiunii unei coloane, dacă aceasta conţine numai valori nule sau tabelul nu conţine înregistrări (linii); • modificarea tipului de date a unei coloane. (Atenţie! Se poate realiza doar dacă coloana conţine numai valori nule).

45. convertirea tipului char în varchar2 sau varchar2 în char. Această conversie se face doar dacă: - coloana respectivă conţine numai valori nule; - nu se modifică dimensiunea coloanei. O modificare a valorii implicite a unei coloane va avea efect doar asupra datelor nou introduse.

46. EXERCIŢIU Inseraţi coloana phone_numberîn tabelul employees.

47. REZOLVARE ALTER TABLE employees ADD (phone_number NUMBER(20));

48. EXERCIŢIU Scrieţi o comandă care modifică structura coloanei salary, astfel încât aceasta să poate primi valori formate din 6 cifre.

49. REZOLVARE ALTER TABLE employees MODIFY (salary number(6));

50. EXERCIŢIU Scrieţi o comandă care şterge coloana job_id din tabelul employees.