ZSBD PLSQL Wykad 3 Prowadzcy dr Pawe Drozda

ZSBD PL/SQL Wykład 3 Prowadzący: dr Paweł Drozda

Program wykładu Wprowadzenie do PLSQL 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

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

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

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

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

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

Przykład DECLARE name VARCHAR(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

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 VARCHAR 2(20): =‘Olsztyn’; ilosc CONSTANT NUMBER: =100; dr P. Drozda

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 name employees. last_name%TYPE; sal NUMBER(5, 2); addition sal%TYPE : =100; dr P. Drozda

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

Konwersja typów danych (1) Ukryta – gdy operacja łączy ze sobą różne typy danych Przykład DECLARE Numer number(5) : =200; Inny VARCHAR 2(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

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

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 name varchar(20): =‘Tomasz'; BEGIN DECLARE name varchar(20): =‘Michal'; BEGIN dbms_output. PUT_LINE(zew. name); END; end zew; dr P. Drozda

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

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

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 INTO FROM WHERE AVG(salary) srednia employees department_id = 50; Funkcje odnoszące się do jednego wiersza mogą być używane w PLSQL (LENGTH, CURRENT_DATE, MONTHS_BETWEEN) dr P. Drozda

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

Nazewnictwo zmiennych Co się stanie? DECLARE department_name VARCHAR 2(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

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

Przykład – polecenie SELECT DECLARE imie employees. 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

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

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

Instrukcje warunkowe CASE Składnia CASE pole WHEN warunek 1 THEN instrukcje 1 [WHEN warunek 2 THEN instrukcje 2] … [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

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

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

Pętla WHILE Składnia 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

Pętla FOR Składnia 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

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

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

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

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

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( definicjapole 1, definicjapole 2, …); dr P. Drozda

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

%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: nazwapola nazwatabeli%ROWTYPE; osoba employees%ROWTYPE; odwołanie do pola (np. zarobki) osoba. salary dr P. Drozda

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

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

Tworzenie tabel – INDEX BY TYPE nazwa IS TABLE OF typkolumny INDEX BY PLS_INTEGER, |BINARY_INTEGER|VARCHAR 2; 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

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

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

Tabele nested tables Składnia TYPE nazwa IS TABLE OF typ_danych; Nie mają z góry określonej wielkości Maksymalny rozmiar 2 GB 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

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

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

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

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

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

Przykład DECLARE CURSOR dane IS SELECT first_name, last_name FROM employees WHERE department = 50; imie employees. 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

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

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

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

FOR dla kursora Składnia FOR zmienna IN nazwakursora LOOP instrukcje; END LOOP; Otwarcie, FETCH i zamknięcie kursora przez pętle Zmienna – rekord sczytujący kolejne wiersze kursora dr P. Drozda

Przykład DECLARE CURSOR dane IS SELECT first_name, last_name FROM employees WHERE department_id = 50; BEGIN FOR wiersz IN dane LOOP DBMS_OUTPUT. PUT_LINE(wiersz. first_name|| ‘ ‘ || wiersz. last_name); END LOOP; DBMS_OUTPUT. PUT_LINE(dane%ROWCOUNT); END; dr P. Drozda

Atrybuty kursora %ISOPEN – zwraca prawdę gdy kursor otwarty %NOTFOUND – zwraca prawdę gdy zmienna nie zawiera danych %FOUND – zwraca prawdę gdy zmienna zawiera dane %ROWCOUNT – zwraca liczbę wierszy wyciągniętych poprzez kursor do momentu wywołania atrybutu dr P. Drozda

Przykład bez deklaracji kursora BEGIN FOR dane IN (SELECT last_name, first_name FROM employees) LOOP … END LOOP; END; dr P. Drozda

Kursor – parametry Do kursora mogą zostać dodane parametry Skłania: CURSOR nazwa(parametr typ, …) IS SELECT …; BEGIN OPEN nazwa(wartosc parametru); dr P. Drozda

Przykład DECLARE CURSOR ponumerze (depid NUMBER) IS SELECT last_name, first_name, salary FROM employees WHERE department_id=depid; BEGIN OPEN ponumerze(10); CLOSE ponumerze; OPEN ponumerze(50); … END; dr P. Drozda