Zsbd PL/SQL

1. ZsbdPL/SQL Wykład 3 Prowadzący: dr Paweł Drozda

2. Program wykładu • Wprowadzenie do PL\SQL • Zmienne • Typy danych • Bloki, podbloki • Funkcje SQL w PL/SQL • Instrukcje warunkowe, pętle • Złożone typy danych • Rekordy • Tabele • Kursory dr P. Drozda

3. Co to jest PL/SQL i po co? • Rozszerzenie SQL-a do języka proceduralnego • Standardowy język Oracle dostępu do danych w relacyjnych bazach • Ułatwia wykonywanie powtarzających się operacji jak również operacji opartych o określone warunki np. jeśli chcemy dać podwyżkę zależną od działu w którym pracownik jest zatrudniony dr P. Drozda

4. Podstawowe elementy PL/SQL • Kod zorganizowany w wykonywalne bloki • Zawiera elementy języka proceduralnego: • Zmienne, stałe typy danych • Instrukcje warunkowe, pętle • Bloki danych używanie wiele razy – procedury, funkcje, pakiety • Obsługa wyjątków dr P. Drozda

5. Zalety PL/SQL • Integracja elementów proceduralnych z SQL-em • Zwiększona wydajność • Możliwość podziału kodu na moduły • Integracja z narzędziami Oracle • Obsługa wyjątków dr P. Drozda

6. Struktura bloku • DECLARE (opcja)– deklaracja zmiennych • BEGIN (konieczne) – zawiera instrukcje SQL i PL/SQL • EXCEPTION (opcja) – działania gdy pojawią się błędy • END; (konieczne) – kończy blok DECLARE … BEGIN … EXCEPTION … END; dr P. Drozda

7. Typy bloków Anonimowy Procedura Funkcja [DECLARE] BEGIN Instrukcje SQL i PL/SQL [EXCEPTION] END; PROCEDURE name IS BEGIN Instrukcje SQL i PL/SQL [EXCEPTION] END; FUNCTION name RETURN datatype IS BEGIN Instrukcje SQL i PL/SQL [EXCEPTION] END; dr P. Drozda

8. Przykład DECLARE nameVARCHAR(30); BEGIN SELECT last_name INTO name FROM employees WHERE employee_id = 111; DBMS_OUTPUT.PUT_LINE(‘Nazwisko dla numeru 111 ’ || name); END; dr P. Drozda

9. Zmienne w PL/SQL • Zaczynają się od litery • Mogą zawierać cyfry oraz $,_ i # • Maksymalnie 30 znaków • Niedozwolone słowa kluczowe • Przykład deklaracji: DECLARE data DATE; numer NUMBER NOT NULL :=10; miasto VARCHAR2(20):=‘Olsztyn’; ilosc CONSTANT NUMBER:=100; dr P. Drozda

10. Deklaracja zmiennych z %TYPE • Jeśli zmienna odnosi się do zmiennej wcześniej zadeklarowanej lub do kolumny z tabeli • Zmienna z atrybutem %TYPE przyjmuje typ danych odnośnika • Ułatwienie uniknięcia błędnego typu danych • Gdy zmienia się typ kolumny – zmiana typu zmiennej • Przykład DECLARE nameemployees.last_name%TYPE; sal NUMBER(5,2); additionsal%TYPE :=100; dr P. Drozda

11. Typy danych – obiekty • CLOB – duże ilości tekstu • BLOB – duże obiekty binarne. Dane nie są interpretowane w bazie danych • BFILE – pliki zawierające dane binarne. Nie są składowane w bazie – w bazie tylko wskazane miejsce, gdzie dany plik się znajduje • NCLOB – zawiera obiekty opisujące znaki w różnych językach dr P. Drozda

12. Konwersja typów danych (1) • Ukryta – gdy operacja łączy ze sobą różne typy danych Przykład DECLARE Numer number(5) :=200; Inny VARCHAR2(10) :=‘200’; Razem Numer%TYPE; BEGIN Razem := Numer + Inny; End; • Powyższa konwersja stosowana pomiędzy: • Znakami i datami • Numerami i znakami dr P. Drozda

13. Konwersja typów danych (2) • Jawna – stosowane wbudowane funkcje • TO_CHAR – przekształca do znaków • TO_DATE – przekształca do daty • TO_NUMBER – przekształca do liczby • TO_TIMESTAMP – zmienia ciąg znaków do typu timestamp dr P. Drozda

14. Podbloki – widoczność zmiennych • Gdy zmienne mają tę samą nazwę – widoczna zmienna z bloku wewnętrznego • Dostęp do zmiennej zewnętrznej – nadanie etykiety blokowi zewnętrznemu • Przykład: begin <<zew>> DECLARE namevarchar(20):=‘Tomasz'; BEGIN DECLARE namevarchar(20):=‘Michal'; BEGIN dbms_output.PUT_LINE(zew.name); END; END; end zew; dr P. Drozda

15. Przykład begin <<zew>> DECLARE zarobki NUMBER(7,2):=4000; dodatek NUMBER(7,2) := zarobki*0.2; info VARCHAR2(40):=‘dostaje’; BEGIN DECLARE zarobki NUMBER(7,2):=40000; dodatek NUMBER(7,2) := 0; wszystko NUMBER(7,2):=zarobki+dodatek; BEGIN (*)info := ‘Dyrektor nie’|| info; zew.dodatek:=zarobki*0.3; END; (**) info:= ‘Administrator ’|| info; END; end zew; • Pytania: • Wartość info z (*) • Wartość wszystko z (**) • Dodatek z (*) • Zew.dodatek z (*) • Dodatek z (**) • Info z (**) dr P. Drozda

16. Operatory • Logiczne • AND, OR, NOT • Arytmetyczne • +,-,*,/ • Łączenia • || • Problem NULL • Porównanie zawierające NULL zwraca zawsze NULL • Użycie NOT do NULL zwraca NULL • Gdy w warunku występuje NULL – przypisane operacje nie są wykonywane dr P. Drozda

17. Funkcje w PL/SQL • Brak możliwości użycia w poleceniach PL/SQL (DECODE, AVG, SUM, MIN, MAX, etc) salary = AVG(employees.salary); - niedozwolone • Mogą być użyte tylko w instrukcjach SQL osadzonych w PL/SQL SELECT AVG(salary) INTO srednia FROM employees WHERE department_id = 50; • Funkcje odnoszące się do jednego wiersza mogą być używane w PL\SQL (LENGTH, CURRENT_DATE, MONTHS_BETWEEN) dr P. Drozda

18. Konwencja kodowania • SQL, PL/SQL, Typy zmiennych – wielkie litery • Zmienne, stałe, nazwy kolumn, nazwy tabel – małe litery • Stosowanie wcięć dla czytelności kodu • Przykład: DECLARE dept_no NUMBER(4); location_id NUMBER(4); BEGIN SELECT department_id, location_id INTO dept_no , location_id FROM departments WHERE department_name = ‘Sales’; END; dr P. Drozda

19. Nazewnictwo zmiennych • Co się stanie? DECLARE department_name VARCHAR2(20):=‘SALES’; BEGIN DELETE FROM departments WHERE department_name = department_name; END; • Należy unikać nazywania zmiennych tak samo jak nazwy kolumn – problemy w klauzuli WHERE • Po SELECT oraz INTO – nie ma problemu dr P. Drozda

20. Polecenie SELECT w PL/SQL • Składnia SELECT kolumny INTO zmienne FROM tabela [WHERE warunki]; • Cechy: • Słowo INTO jest obowiązkowe • Liczba i typy kolumn zgadzają się z liczbą i typami zmiennych • Otrzymane wyniki zapytania zapisywane są w zmiennych • Zapytanie musi zwrócić dokładnie jeden wiersz dr P. Drozda

21. Przykład – polecenie SELECT DECLARE imieemployees.first_name%TYPE; nazwisko employees.last_name%TYPE; BEGIN SELECT first_name, last_name INTO imie, nazwisko FROM employees WHERE employee_id = 111; END; dr P. Drozda

22. DML w PL/SQL • Polecenia DML odpowiadają dokładnie poleceniom SQL (INSERT, UPDATE, DELETE, MERGE) • Przykład MERGE INTO zatrudnienie z USING archiwum a ON (z.id_prac = a.ident) WHEN MATCHED THEN SET z.imie = a.imie; z.nazwisko = a.nazwisko; WHEN NOT MATCHED THEN INSERT VALUES (a.ident,a.imie, a.nazwisko); dr P. Drozda

23. Instrukcje warunkowe – IF • Składnia IF (podobnie jak wszędzie) IF warunek THEN instrukcja; [ELSEIF warunek THEN instrukcja;] [ELSE instrukcja;] END IF; Przykład: DECLARE sal NUMBER(4); premia NUMBER(4); BEGIN IF sal > 5000 THEN premia = sal*0.1; ELSEIF sal > 3000 THEN premia= sal*0.2; ELSE premia = sal*0.3; END IF; END; dr P. Drozda

24. Instrukcje warunkowe CASE • Składnia CASE pole WHEN warunek1 THEN instrukcje1 [WHEN warunek2 THEN instrukcje2] … [ELSE instrukcje] END; Przykład ocena_slownie = CASE ocena WHEN 5 THEN ‘bardzo dobry’ WHEN 4 THEN ‘dobry’ WHEN 3 THEN ‘dostateczny’ WHEN 2 THEN ‘niedostateczny’ WHEN 1,0 THEN ‘osioł’ ELSE ‘nie ma takiej oceny’ END; dr P. Drozda

25. Inny przykład CASE • CASE WHEN ocena = 5 THEN ‘ bardzo dobry’ … WHEN ocena IN (1,0) THEN ‘osioł’ ELSE ‘nie ma takiej oceny’ END; dr P. Drozda

26. Pętla LOOP • Wykonuje się do póki warunek nie jest spełniony • Składnia pętli LOOP instrukcje; … EXIT WHEN warunek; instrukcje; END LOOP; • Przykład DECLARE suma NUMBER(4):=0; i NUMBER(3):=1; LOOP suma:=suma+i; EXIT WHEN suma >100; i:=i+1; END LOOP; dr P. Drozda

27. Pętla WHILE • Składnia WHILE WHILE warunek LOOP instrukcje; END LOOP; • Przykład: DECLARE x NUMBER(4):=20; BEGIN WHILE x>0 LOOP x:=x-1; DBMS_OUTPUT.PUT_LINE(x); END LOOP; END; dr P. Drozda

28. Pętla FOR • Składnia FOR FOR licznik IN dolnagr..gornagr [REVERSE] LOOP Instrukcje; END LOOP; • Licznik nie musi być deklarowany • Jeśli REVERSE licznik przechodzi od górnej granicy do dolnej • Granice FOR nie powinny być NULL dr P. Drozda

29. FOR przykład DECLARE zarobki NUMBER(7):=2000; BEGIN FOR i IN 1..10 LOOP zarobki :=zarobki + i*1000; INSERT INTO EMP VALUES (ident.nextval, ‘kozak’,zarobki); END LOOP; END; dr P. Drozda

30. Kontrola wykonywania pętli • CONTINUE • wymusza porzucenie obecnej iteracji w pętli i rozpoczęcie nowej • Może zwiększyć wydajność bloku • EXIT • wymusza opuszczenie obecnej pętli i przejście do kolejnej instrukcji • Konieczne w podstawowej pętli LOOP dr P. Drozda

31. CONTINUE, EXIT - przykład DECLARE i NUMBER(4):=0; suma NUMBER(10):=0; BEGIN WHILE TRUE LOOP i:=i+1; IF MOD(i,2)=0 THEN CONTINUE; END IF; suma:=suma + i; IF i=10 THEN EXIT; END IF; END LOOP; END; dr P. Drozda

32. Złożone typy danych • Rekordy • %ROWTYPE • Tabele INDEX BY • Tabele nested tables • Tabele VARRAY dr P. Drozda

33. Rekordy • Zawierają dane logicznie ze sobą powiązane np. wiersz bądź część wiersza tabeli • Mogą zawierać pola o różnych typach danych • Zapisują tylko jeden wiersz (np. z tabeli) • Muszą mieć co najmniej jedno pole • Składnia rekordu: TYPE nazwa IS RECORD( definicjapole1, definicjapole2,…); dr P. Drozda

34. Przykład definicji rekordu TYPE pracownik IS RECORD( imie VARCHAR(20), id NUMBER(5), nazwisko VARCHAR(20), zarobki employees.salary%TYPE, miejsce departments%ROWTYPE); wystapienie pracownik; odwołanie do pola np. wystapienie.id – odwołanie do identyfikatora dr P. Drozda

35. %ROWTYPE • Odwołuje się do wszystkich kolumn jednej tabeli • Przed %ROWTYPE należy dodać nazwę tabeli do której ma odwoływać się zmienna • Nazwy kolumn oraz typy danych w poszczególnych kolumnach przekazane do zmiennej • Przy zmianie typów danych i nazw w tabeli – zmiana też w zmiennej z %ROWTYPE – nie trzeba pilnować typów • Deklaracja: nazwapolanazwatabeli%ROWTYPE; osoba employees%ROWTYPE; odwołanie do pola (np. zarobki) osoba.salary dr P. Drozda

36. Przykład wykorzystania %ROWTYPE Tabela emp(id,name, salary,fire_date) DECLARE zmiana emp%ROWTYPE; BEGIN SELECT * INTO zmiana FROM emp WHERE id=111; zmiana.fire_date = CURRENT_DATE; UPDATE emp SET ROW zmiana WHERE id=111; END; dr P. Drozda

37. Tabele INDEX BY • Składają się z dwóch pól – identyfikatora (liczba lub ciąg znaków) oraz wartości • Gromadzą dane o tym samym typie – np. imiona z tabeli employees • Mają nieograniczoną wielkość tzn. ogranicza je zakres wartości identyfikatora • Odpowiadają tabelom z „normalnych” języków programowania dr P. Drozda

38. Tworzenie tabel – INDEX BY TYPE nazwa IS TABLE OF typkolumny INDEX BY PLS_INTEGER,|BINARY_INTEGER|VARCHAR2; • Przykład DECLARE TYPE tabelaimiona IS TABLE OF employees.first_name%TYPE INDEX BY PLS_INTEGER; imiona tabelaimiona; BEGIN imiona(1):=‘Zenek’; END; dr P. Drozda

39. Funkcje odwołujące się do tabel INDEX BY • EXISTS – sprawdza czy istnieje dana komórka w tabeli np. IF imiona(1).EXISTS THEN INSERT INTO …; • COUNT – zwraca liczbę elementów z tabeli dbms_output.put_line(imiona.COUNT); • FIRST, LAST – zwracają pierwszą/ostatnią wartość z tabeli; gdy tabela pusta – zwranany jest NULL • PRIOR(n), NEXT(n) – zwracają poprzedni/kolejny indeks z tabeli – porównując do n • DELETE – usuwa elementy z tabeli • bez argumentu – usuwa wszystko • z argumentem n – usuwa n-ty element • z argumentami m,n – usuwa wszystkie elementy od m do n dr P. Drozda

40. Przykład INDEX BY DECLARE TYPE typpracownik IS TABLE OF employees%ROWTYPE INDEX BY PLS_INTEGER; pracownik typpracownik; BEGIN FOR i IN 100..105 LOOP SELECT * INTO pracownik(i) FROM employees WHERE employee_id=i; END LOOP; FOR i IN pracownik.FIRST..pracownik.LAST LOOP DBMS_OUTPUT.PUT_LINE(pracownik(i).last_name, pracownik(i).first_name); END LOOP; END; dr P. Drozda

41. Tabele nested tables • Składnia TYPE nazwa IS TABLE OF typ_danych; • Nie mają z góry określonej wielkości • Maksymalny rozmiar 2GB • Odwołania do poszczególnych wartości – tak jak w tabelach INDEX BY (w kluczach nie może być ujemnych wartości) • Wiersze nie są poustawiane w jakimś porządku – przeszukiwanie rekord po rekordzie (INDEX BY – mają klucz indeksowany) dr P. Drozda

42. Przykład – nested tables DECLARE TYPE typ_lokacji IS TABLE OF locations.city%TYPE; miasta typ_lokacji; BEGIN miasta:=typ_lokacji(‘Gniewkowo’, ’Solec’, ’Pszczólki’, ’Książki’); FOR i IN 1..miasta.count() LOOP dbms_output(miasta(i)); END LOOP; END; dr P. Drozda

43. Tabele VARRAY • Ustalona w deklaracji wielkość tabeli • Składnia tworzenia tabeli VARRAY • TYPE nazwa IS VARRAY(n) OF typDanych; • Maksymalny rozmiar 2GB dr P. Drozda

44. Kursory • Definiowane do poleceń SELECT, które zwracają wiele wierszy do przechowywania danych • Można przetwarzać wiersz po wierszu • Zasada działania: EMPTY? YES DECLARE OPEN FETCH CLOSE NO dr P. Drozda

45. Składnia deklaracji kursora • CURSOR nazwa IS zapytanie SQL (SELECT); • Przykład: DECLARE CURSOR dept_30 IS SELECT last_name, first_name, salary FROM employees WHERE department_id = 30; dr P. Drozda

46. Otwieranie kursora – OPEN • Aby była możliwość dostępu do danych w kursorze należy użyć polecenia – OPEN nazwa; • Polecenie alokuje dynamicznie pamięć dla danych z otwieranego kursora • Odczytywanie są odpowiednie typy i nazwy kolumn dla kursora • Wiersze nie są przekazywane do zmiennych kursora – dopiero w momencie uruchomienia polecenia FETCH dane są czytane dr P. Drozda

47. Przykład DECLARE CURSOR dane IS SELECT first_name, last_name FROM employees WHERE department = 50; imieemployees.first_name%TYPE; nazwisko employees.last_name%TYPE; BEGIN OPEN dane; FETCH dane INTO imie, nazwisko; DBMS_OUTPUT.PUT_LINE(imie || ‘ ’|| nazwisko); END; dr P. Drozda

48. FETCH, CLOSE • FETCH • pobiera z kursora jeden wiersz danych i przesuwa się do następnego wiersza • Po INTO liczba zmiennych i typy muszą odpowiadać liczbie i typom kolumn pobieranych z kursora • Kolejność występowania zmiennych musi odpowiadać kolejności kolumnom zdefiniowanych w kursorze • FETCH nazwa INTO zmienne; • CLOSE • Zamyka dostęp do kursora • Zwalnia pamięć zajmowaną przez kursor • Dobra praktyka zawsze zamykać, gdy niepotrzebny • CLOSE nazwa; dr P. Drozda

49. Użycie rekordów w kursorach • Przy deklaracji zmiennej – typ ustawiany jako nazwakursora%ROWTYPE; • Przy poleceniu FETCH wiersz wpisywany do zadeklarowanego rekordu Przykład: DECLARE CURSOR dane …; wiersz dane%ROWTYPE; BEGIN OPEN dane; FETCH dane INTO wiersz; … END; dr P. Drozda

50. Przykład – pełna wersja DECLARE CURSOR dane IS SELECT first_name, last_name FROM employees WHERE department_id = 50; wiersz dane%ROWTYPE; BEGIN OPEN dane; LOOP FETCH dane INTO wiersz; EXIT WHEN dane%NOTFOUND; DBMS_OUTPUT.PUT_LINE(wiersz.first_name, ‘ ‘ , wiersz.last_name); END LOOP; dane%ROWCOUNT; CLOSE dane; END; dr P. Drozda