Zsbd PL/SQL część 2

1. ZsbdPL/SQL część 2 Wykład 4 Prowadzący: dr Paweł Drozda

2. Program wykładu • Wyjątki • Procedury • Funkcje dr P. Drozda

3. Kontrola wyjątków • Wyjątki – Błędy pojawiające się podczas uruchomienia bloku • W momencie wystąpienia wyjątku blok kończy działanie – wyjątek pozawala na wykonanie ostatnich działań przed końcem wykonania bloku • Większość języków programowania zapewnia kontrolę wyjątków • Dwie opcje wywołania wyjątku: • predefiniowane w Oracle (np. NO_DATA_FOUND) • wywoływane przez użytkownika za pomocą RAISE nazwa, gdzie nazwa zdefiniowana jako wyjątek z dodaną obsługą dr P. Drozda

4. Przykład wprowadzający DECLARE name VARCHAR2(20); BEGIN SELECT last_name INTO name FROM employees WHERE department_id = 50; END; - zwraca błąd – więcej wierszy niż oczekiwano dr P. Drozda

5. Obsługa wyjątku przykład DECLARE name VARCHAR2(20); BEGIN SELECT last_name INTO name FROM employees WHERE department_id = 50; dbms_output.put_line(name); EXCEPTION WHEN TOO_MANY_ROWS THEN dbms_output.put_line(‘Zmień warunek WHERE tak aby zwracał 1 wiersz’); END; dr P. Drozda

6. Typy wyjątków dr P. Drozda

7. Tworzenie obsługi – sekcja EXCEPTION BEGIN … EXCEPTION WHEN wyjatek1 [OR wyjatek2 …] THEN instrukcje; … WHEN wyjatek3 [OR wyjatek4…] THEN instrukcje WHEN OTHERS THEN instrukcje; END; dr P. Drozda

8. Przykład deklaracji obsługi wyjątków EXCEPTION WHEN NO_DATA_FOUND THEN dbms_output.put_line(‘poszukaj lepiej’); WHEN TOO_MANY_ROWS THEN dbms_output.put_line(‘za dobrze szukasz’); WHEN OTHERS THEN dbms_output.put_line(‘nie wiem co ale cos nie dziala’); dr P. Drozda

9. Predefiniowane wyjątki w Oracle dr P. Drozda

10. Tworzenie nie predefiniowanych wyjątków dla błędów serwera • Proces tworzenia obsługi Sekcja DECLARE Sekcja EXCEPTION DECLARE ASSOCIATE HANDLE • Deklaracja zmiennej typu EXCEPTION • Przypisanie do zmiennej kodu błędu • PRAGMA EXCEPTION_INIT(zmienna,kodbłędu) • 3. Zdefiniowanie obsługi błędu dla zadeklarowanej zmiennej dr P. Drozda

11. Przykład – definicja obsługi błędu • Definicja dla błędu przy wstawianiu NULL DECLARE wyjatek EXCEPTION; PRAGMA EXCEPTION_INIT(wyjatek, -01400); BEGIN INSERT INTO departments(department_id, department_name) VALUES(270, NULL); EXCEPTION WHEN wyjatek THEN DBMS_OUTPUT.PUT_LINE('ten blad to:'); DBMS_OUTPUT.PUT_LINE(SQLERRM); DBMS_OUTPUT.PUT_LINE(SQLCODE); END; dr P. Drozda

12. Funkcja z wyłapywaniem wyjątków • Jak gromadzić wyjątki które nie są zdefiniowane? EXCEPTION … WHEN OTHERS THEN blad_numer := SQLCODE; info_blad := SQLERRM; INSERT INTO bledy(uzytkownik, data, blad, tresc) VALUES (USER, SYSDATE, blad_numer,info_blad); end; dr P. Drozda

13. Wyjątki użytkownika • Proces tworzenia obsługi Sekcja główna Sekcja EXCEPTION Sekcja DECLARE DECLARE RAISE HANDLE • Deklaracja zmiennej typu EXCEPTION • Wywołanie wyjątku w wyniku instrukcji RAISE • 3. Zdefiniowanie obsługi błędu dla zadeklarowanej zmiennej dr P. Drozda

14. Przykład wyjątku użytkownika DECLARE deptnum NUMBER:=300; name varchar2(20):=‘nie wstawi’; wyjatek EXCEPTION; BEGIN UPDATE departments SET department_name = name WHERE department_id = deptnum; IF SQL%NOTFOUND THEN RAISE wyjatek; END IF; … EXCEPTION WHEN wyjatek THEN dbms_output.put_line(‘nie ma takiego numeru’); END; dr P. Drozda

15. Bloki nazwane • Dwa typy: • Procedury • Funkcje • Możliwość przechowywania w systemie baz danych • Możliwość wielokrotnego wykorzystywania w wielu miejscach i przez różnych użytkowników • Odwołanie przez nazwę (najczęściej z parametrami) • Wyodrębnienie mniejszych zadań tworzących logiczną całość • Łatwiejsze wychwycenie błędów, łatwiejsze zmiany dr P. Drozda

16. Nazwane a nienazwane bloki dr P. Drozda

17. Procedury a funkcje dr P. Drozda

18. Procedury • Składnia tworzenia: CREATE [OR REPLACE] PROCEDURE nazwa(parametr tryb typ_danych [DEFAULT wartość], …) AS|IS deklaracja zmiennych lokalnych; BEGIN instrukcje; … END; • tryb może przyjmować wartości : IN – parametry wejściowe (ustawiane domyślnie) OUT – parametry wyjściowe IN OUT – parametry działające w obie strony DEFAULT – w przypadku nie podania parametru, przyjmowana wartość dr P. Drozda

19. Przykłady CREATE PROCEDURE nic IS BEGIN NULL; END; CREATE PROCEDURE nazwisko (ident IN NUMBER DEFAULT 111) IS nameemployees.last_name%TYPE; BEGIN SELECT last_name INTO name FROM employees WHERE employee_id = ident; dbms_output.put_line(name); END; dr P. Drozda

20. Wywołanie procedury • W bloku PL/SQL • jako nazwa procedury, wraz z parametrami • Z linii komend • EXECUTE nazwa_procedury(parametry) • Przykład a) BEGIN nic; END; b) EXECUTE nazwisko(123) dr P. Drozda

21. Przekazywanie parametrów IN, OUT CREATE PROCEDURE znajdz(id IN NUMBER, zarobki OUT NUMBER) IS BEGIN SELECT salary INTO zarobki FROM employees WHERE employee_id=id; END; CREATE PROCEDURE wyswietl IS zarobki employees.salary%TYPE; BEGIN znajdz(123, zarobki); dbms_output.put_line(zarobki); znajdz(201, zarobki); dbms_output.put_line(zarobki); END; dr P. Drozda

22. Użycie parametru IN OUT CREATE PROCEDURE dodaj48 (telefon IN OUT varchar2) IS BEGIN telefon := ‘+48’ || telefon; END; wywołanie procedury DECLARE tel varchar2(20):=‘997’; BEGIN dodaj48(tel); dbms_output.put_line(tel); END; dr P. Drozda

23. Okno konsoli – zmienne • Deklaracja • VARIABLE nazwa typ • odwołanie w procedurze • nazwaProcedury(:nazwaZmiennej) • wypisanie na ekran • PRINT nazwaZmiennej • Przykład VARIABLE zarobki NUMBER znajdz(134,:zarobki) PRINT zarobki dr P. Drozda

24. Możliwości przekazywania parametrów • W ustalonym porządku – normalnie • nazwisko(111) • Poprzez nazwy • nazwisko(ident=>111) • Mieszane • dodaj_job(‘IT_DBA’, ’admin’,min_salary => 6000, max_salary => 20000) • UWAGA do mieszanych • parametry porządkowe muszą poprzedzać parametry podawane poprzez nazwy – inaczej błąd dr P. Drozda

25. Przekazywanie parametrów cd. • Stosowanie wartości DEFAULT • procedura może być wywołana bez parametrów przyjmowane wartości domyślne • gdy nie ma wartości domyślnych odpowiednie parametry muszą być podane • Przykład dodaj_job; dodaj_job(‘IT_DBA’,’superadmin’); dodaj_job(job_title=>’superhiperadmin’); dr P. Drozda

26. Obsługa wyjątków w procedurach • Deklaracja tak jak w blokach anonimowych • Gdy wyjątek obsługiwany – tylko wywołanie procedury zwracające wyjątek nie zakończy się sukcesem – pozostałe tak • Gdy wyjątek nieobsługiwany – w momencie pojawienia się wyjątku cały program kończy działanie – nic po wyjątku nie jest wykonywane dr P. Drozda

27. Przykład CREATE OR REPLACE PROCEDURE wstaw (jid varchar2, title varchar2) IS BEGIN INSERT INTO jobs(job_id, job_title) VALUES(jid,title); EXCEPTION WHEN OTHERS THEN dbms_output.put_line('zlyidik'); END; BEGIN wstaw(‘it_dba’, ‘superadmin’); wstaw(‘it_dba’,’superhiperadmin’); END; Dzięki tej części pierwszy wstaw zostanie wykonany. jeśli by nie było obsługi wyjątków – nic by się nie dodało dr P. Drozda

28. Funkcje • Składnia CREATE [OR REPLACE] FUNCTION nazwa (parametr typ_danych, …) RETURN typ_danych IS zmienne lokalne; BEGIN … RETURN wartość zgodna z typem; … END; dr P. Drozda

29. Przykład funkcji CREATE OR REPLACE FUNCTION maksik (a NUMBER, b NUMBER) RETURN NUMBER IS BEGIN IF a>b THEN RETURN a; ELSE return b; END IF; END maksik; dr P. Drozda

30. Przekazywanie parametrów • w określonym porządku, poprzez odwołanie do nazwy, mieszane – tak jak przy procedurach • możliwość wykorzystania wartości domyślnej DEFAULT • Ograniczenia na parametry mieszane – tak jak w procedurach dr P. Drozda

31. Jak wywołać funkcję • Linia komend • EXECUTE dbms_output.put_line(maksik(20,30)) • VARIABLE wieksza NUMBER EXECUTE :wieksza:=maksik(100,200) • Blok anonimowy • DECLARE wieksza NUMBER; BEGIN wieksza:=maksik(30,50); END; • Instrukcja SQL • SELECT job_id, maksik(min_salary, max_salary) FROM jobs; dr P. Drozda

32. Używanie funkcji w instrukcjach SQL • Mogą być stosowane jak jednowierszowe funkcje wbudowane • na liście kolumn w SELECT • W warunkach WHERE i HAVING • W grupowaniu i porządkowaniu (GROUP BY, ORDER BY) • W INSERT w miejscu VALUES • W UPDATE po SET dr P. Drozda

33. Ograniczenia na użycie w SQL • Zawierają tylko parametry w trybie IN • parametry są zwykłych typów SQL • zwracana wartość musi być typu SQL – nie PL/SQL • Funkcja musi być przechowywana w bazie • Użytkownik wykonujący musi mieć przywilej EXECUTE FUNCTION • Nie mogą zawierać COMMIT, ROLLBACK • Przy SELECT nie mogą zawierać DML • Przy UPDATE i DELETE nie mogą wykonywać instrukcji DML na tej samej tabeli dr P. Drozda