Zsbd PL/SQL część 2
description
Transcript of Zsbd PL/SQL część 2
![Page 1: Zsbd PL/SQL część 2](https://reader036.fdocument.pub/reader036/viewer/2022081520/56815685550346895dc43278/html5/thumbnails/1.jpg)
ZSBDPL/SQL CZĘŚĆ 2 Wykład 4Prowadzący: dr Paweł Drozda
![Page 2: Zsbd PL/SQL część 2](https://reader036.fdocument.pub/reader036/viewer/2022081520/56815685550346895dc43278/html5/thumbnails/2.jpg)
Program wykładu
dr P. Drozda
Wyjątki Procedury Funkcje
![Page 3: Zsbd PL/SQL część 2](https://reader036.fdocument.pub/reader036/viewer/2022081520/56815685550346895dc43278/html5/thumbnails/3.jpg)
Kontrola wyjątków
dr P. Drozda
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ą
![Page 4: Zsbd PL/SQL część 2](https://reader036.fdocument.pub/reader036/viewer/2022081520/56815685550346895dc43278/html5/thumbnails/4.jpg)
Przykład wprowadzający
dr P. Drozda
DECLAREname VARCHAR2(20);BEGIN
SELECT last_name INTO name FROM employees WHERE department_id = 50;
END;- zwraca błąd – więcej wierszy niż
oczekiwano
![Page 5: Zsbd PL/SQL część 2](https://reader036.fdocument.pub/reader036/viewer/2022081520/56815685550346895dc43278/html5/thumbnails/5.jpg)
Obsługa wyjątku przykład
dr P. Drozda
DECLAREname VARCHAR2(20);BEGIN
SELECT last_name INTO name FROM employees WHERE department_id = 50;dbms_output.put_line(name);
EXCEPTIONWHEN TOO_MANY_ROWS THEN
dbms_output.put_line(‘Zmień warunek WHERE tak aby zwracał 1 wiersz’);
END;
![Page 6: Zsbd PL/SQL część 2](https://reader036.fdocument.pub/reader036/viewer/2022081520/56815685550346895dc43278/html5/thumbnails/6.jpg)
Typy wyjątków
dr P. Drozda
Wyjątek Opis Sposób obsługiPredefiniowany błąd serwera Oracle
Jeden z wyjątków (około 20) które pojawiają się najczęściej w PL/SQL
Nie ma konieczności definiowania obsługi – oracle sam definiuje obsługę
Niepredefiniowany błąd serwera Oracle
Inny standardowy błąd Obsługa powinna być zdefiniowana
Błędy zdefiniowane przez użytkownika
Zdefiniowane przez użytkownika nienormalne zachowanie
Konieczna deklaracja w sekcji przechwytywania wyjątków oraz wywołanie za pomocą RAISE
![Page 7: Zsbd PL/SQL część 2](https://reader036.fdocument.pub/reader036/viewer/2022081520/56815685550346895dc43278/html5/thumbnails/7.jpg)
Tworzenie obsługi – sekcja EXCEPTION
dr P. Drozda
BEGIN …EXCEPTIONWHEN wyjatek1 [OR wyjatek2 …] THENinstrukcje;…WHEN wyjatek3 [OR wyjatek4…] THENinstrukcjeWHEN OTHERS THENinstrukcje;END;
![Page 8: Zsbd PL/SQL część 2](https://reader036.fdocument.pub/reader036/viewer/2022081520/56815685550346895dc43278/html5/thumbnails/8.jpg)
Przykład deklaracji obsługi wyjątków
dr P. Drozda
EXCEPTIONWHEN NO_DATA_FOUND THENdbms_output.put_line(‘poszukaj lepiej’);WHEN TOO_MANY_ROWS THENdbms_output.put_line(‘za dobrze szukasz’);WHEN OTHERS THENdbms_output.put_line(‘nie wiem co ale cos
nie dziala’);
![Page 9: Zsbd PL/SQL część 2](https://reader036.fdocument.pub/reader036/viewer/2022081520/56815685550346895dc43278/html5/thumbnails/9.jpg)
Predefiniowane wyjątki w Oracle
dr P. Drozda
Wyjątek Numer błędu
Opis
CASE_NOT_FOUND ORA-06592
Brak spełnienia jednego z warunków CASE
CURSOR_ALREADY_OPENED
ORA-06511
Kursor został już otwarty (przy próbie otwarcia)
DUP_VAL_ON_INDEX ORA-00001
Powtarzająca się wartość klucza głównego
INVALID_NUMBER ORA-01722
Zły typ danych przy konwersji
NO_DATA_FOUND ORA-01403
Brak wyników z SELECT
ROWTYPE_MISMATCH ORA-06504
Różne typy dla przypisywanych wartości
STORAGE_ERROR ORA-06500
Nie ma pamięci do operacji
TOO_MANY_ROWS ORA-01422
Zbyt wiele wierszy zwróconych z zapytania
VALUE_ERROR ORA-06502
Błąd związany z liczbami (np. konwersja)
ZERO_DIVIDE ORA-01476
Próba dzielenia przez 0
![Page 10: Zsbd PL/SQL część 2](https://reader036.fdocument.pub/reader036/viewer/2022081520/56815685550346895dc43278/html5/thumbnails/10.jpg)
Tworzenie nie predefiniowanych wyjątków dla błędów serwera
dr P. Drozda
Proces tworzenia obsługi
DECLARE
ASSOCIATE HANDLE
1. Deklaracja zmiennej typu EXCEPTION2. Przypisanie do zmiennej kodu błędu PRAGMA EXCEPTION_INIT(zmienna,kodbłędu)3. Zdefiniowanie obsługi błędu dla
zadeklarowanej zmiennej
Sekcja DECLARE Sekcja EXCEPTION
![Page 11: Zsbd PL/SQL część 2](https://reader036.fdocument.pub/reader036/viewer/2022081520/56815685550346895dc43278/html5/thumbnails/11.jpg)
Przykład – definicja obsługi błędu
dr P. Drozda
Definicja dla błędu przy wstawianiu NULLDECLARE wyjatek EXCEPTION;PRAGMA EXCEPTION_INIT(wyjatek, -01400);BEGIN INSERT INTO departments(department_id, department_name)
VALUES(270, NULL);EXCEPTIONWHEN wyjatek THENDBMS_OUTPUT.PUT_LINE('ten blad to:');DBMS_OUTPUT.PUT_LINE(SQLERRM);DBMS_OUTPUT.PUT_LINE(SQLCODE);END;
![Page 12: Zsbd PL/SQL część 2](https://reader036.fdocument.pub/reader036/viewer/2022081520/56815685550346895dc43278/html5/thumbnails/12.jpg)
Funkcja z wyłapywaniem wyjątków
dr P. Drozda
Jak gromadzić wyjątki które nie są zdefiniowane?
EXCEPTION…WHEN OTHERS THENblad_numer := SQLCODE;info_blad := SQLERRM;INSERT INTO bledy(uzytkownik, data, blad,
tresc) VALUES (USER, SYSDATE, blad_numer,info_blad);
end;
![Page 13: Zsbd PL/SQL część 2](https://reader036.fdocument.pub/reader036/viewer/2022081520/56815685550346895dc43278/html5/thumbnails/13.jpg)
Wyjątki użytkownika
dr P. Drozda
Proces tworzenia obsługi
DECLARE RAISE HANDLE
1. Deklaracja zmiennej typu EXCEPTION2. Wywołanie wyjątku w wyniku instrukcji RAISE3. Zdefiniowanie obsługi błędu dla
zadeklarowanej zmiennej
Sekcja DECLARE Sekcja EXCEPTIONSekcja główna
![Page 14: Zsbd PL/SQL część 2](https://reader036.fdocument.pub/reader036/viewer/2022081520/56815685550346895dc43278/html5/thumbnails/14.jpg)
Przykład wyjątku użytkownika
dr P. Drozda
DECLARE deptnum NUMBER:=300;name varchar2(20):=‘nie wstawi’;wyjatek EXCEPTION;
BEGINUPDATE departments
SET department_name = nameWHERE department_id = deptnum;
IF SQL%NOTFOUND THEN RAISE wyjatek;END IF;…
EXCEPTIONWHEN wyjatek THEN
dbms_output.put_line(‘nie ma takiego numeru’);END;
![Page 15: Zsbd PL/SQL część 2](https://reader036.fdocument.pub/reader036/viewer/2022081520/56815685550346895dc43278/html5/thumbnails/15.jpg)
Bloki nazwane
dr P. Drozda
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
![Page 16: Zsbd PL/SQL część 2](https://reader036.fdocument.pub/reader036/viewer/2022081520/56815685550346895dc43278/html5/thumbnails/16.jpg)
Nazwane a nienazwane bloki
dr P. Drozda
Nienazwane NazwaneKompilowane za każdym razem Kompilowane razBrak możliwości zapisania w bazie danych
Zapisywane w bazie danych
Brak możliwości używania przez inne aplikacje
Możliwość korzystania przez inne aplikacje
Nie zwracają wartości Funkcje zwracają wartość określonego typu, procedury mogą zwracać o lub więcej wartości
Nie można przekazywać parametrów do bloku
Można przekazywać parametry do bloku
![Page 17: Zsbd PL/SQL część 2](https://reader036.fdocument.pub/reader036/viewer/2022081520/56815685550346895dc43278/html5/thumbnails/17.jpg)
Procedury a funkcje
dr P. Drozda
Procedury FunkcjeWykonywane jako instrukcje PL/SQL
Wywoływane jako część jakiegoś wyrażenia
Nie zawierają RETURN w deklaracji
Muszą zawierać RETURN wraz z typem danych w deklaracji
Może przekazać na zewnątrz wartości parametrów (zastosowanie OUT, IN OUT)
Musi zwracać dokładnie jedną wartość
Może użyć RETURN Musi posiadać przynajmniej jeden RETURN w kodzie funkcji
![Page 18: Zsbd PL/SQL część 2](https://reader036.fdocument.pub/reader036/viewer/2022081520/56815685550346895dc43278/html5/thumbnails/18.jpg)
Procedury
dr P. Drozda
Składnia tworzenia:CREATE [OR REPLACE] PROCEDURE nazwa(parametr tryb
typ_danych [DEFAULT wartość], …)AS|IS
deklaracja zmiennych lokalnych;BEGINinstrukcje;…END;- tryb może przyjmować wartości :
IN – parametry wejściowe (ustawiane domyślnie)OUT – parametry wyjścioweIN OUT – parametry działające w obie strony
DEFAULT – w przypadku nie podania parametru, przyjmowana wartość
![Page 19: Zsbd PL/SQL część 2](https://reader036.fdocument.pub/reader036/viewer/2022081520/56815685550346895dc43278/html5/thumbnails/19.jpg)
Przykłady
dr P. Drozda
CREATE PROCEDURE nic ISBEGIN
NULL;END;
CREATE PROCEDURE nazwisko (ident IN NUMBER DEFAULT 111) IS
name employees.last_name%TYPE;BEGIN
SELECT last_name INTO name FROM employees WHERE employee_id = ident;dbms_output.put_line(name);
END;
![Page 20: Zsbd PL/SQL część 2](https://reader036.fdocument.pub/reader036/viewer/2022081520/56815685550346895dc43278/html5/thumbnails/20.jpg)
Wywołanie procedury
dr P. Drozda
W bloku PL/SQL jako nazwa procedury, wraz z parametrami
Z linii komend EXECUTE nazwa_procedury(parametry)
Przykłada) BEGIN
nic; END;
b) EXECUTE nazwisko(123)
![Page 21: Zsbd PL/SQL część 2](https://reader036.fdocument.pub/reader036/viewer/2022081520/56815685550346895dc43278/html5/thumbnails/21.jpg)
Przekazywanie parametrów IN, OUT
dr P. Drozda
CREATE PROCEDURE znajdz(id IN NUMBER, zarobki OUT NUMBER) IS
BEGINSELECT 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;
![Page 22: Zsbd PL/SQL część 2](https://reader036.fdocument.pub/reader036/viewer/2022081520/56815685550346895dc43278/html5/thumbnails/22.jpg)
Użycie parametru IN OUT
dr P. Drozda
CREATE PROCEDURE dodaj48 (telefon IN OUT varchar2) IS
BEGINtelefon := ‘+48’ || telefon;
END;
wywołanie proceduryDECLARE
tel varchar2(20):=‘997’;BEGIN
dodaj48(tel);dbms_output.put_line(tel);
END;
![Page 23: Zsbd PL/SQL część 2](https://reader036.fdocument.pub/reader036/viewer/2022081520/56815685550346895dc43278/html5/thumbnails/23.jpg)
Okno konsoli – zmienne
dr P. Drozda
Deklaracja VARIABLE nazwa typ
odwołanie w procedurze nazwaProcedury(:nazwaZmiennej)
wypisanie na ekran PRINT nazwaZmiennej
PrzykładVARIABLE zarobki NUMBERznajdz(134,:zarobki)PRINT zarobki
![Page 24: Zsbd PL/SQL część 2](https://reader036.fdocument.pub/reader036/viewer/2022081520/56815685550346895dc43278/html5/thumbnails/24.jpg)
Możliwości przekazywania parametrów
dr P. Drozda
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
![Page 25: Zsbd PL/SQL część 2](https://reader036.fdocument.pub/reader036/viewer/2022081520/56815685550346895dc43278/html5/thumbnails/25.jpg)
Przekazywanie parametrów cd.
dr P. Drozda
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’);
![Page 26: Zsbd PL/SQL część 2](https://reader036.fdocument.pub/reader036/viewer/2022081520/56815685550346895dc43278/html5/thumbnails/26.jpg)
Obsługa wyjątków w procedurach
dr P. Drozda
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
![Page 27: Zsbd PL/SQL część 2](https://reader036.fdocument.pub/reader036/viewer/2022081520/56815685550346895dc43278/html5/thumbnails/27.jpg)
Przykład
dr P. Drozda
CREATE OR REPLACEPROCEDURE wstaw (jid varchar2, title
varchar2)ISBEGININSERT INTO jobs(job_id, job_title)
VALUES(jid,title);EXCEPTIONWHEN OTHERS THENdbms_output.put_line('zly idik');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
![Page 28: Zsbd PL/SQL część 2](https://reader036.fdocument.pub/reader036/viewer/2022081520/56815685550346895dc43278/html5/thumbnails/28.jpg)
Funkcje
dr P. Drozda
SkładniaCREATE [OR REPLACE] FUNCTION nazwa
(parametr typ_danych, …) RETURN typ_danych ISzmienne lokalne;
BEGIN …RETURN wartość zgodna z typem;…
END;
![Page 29: Zsbd PL/SQL część 2](https://reader036.fdocument.pub/reader036/viewer/2022081520/56815685550346895dc43278/html5/thumbnails/29.jpg)
Przykład funkcji
dr P. Drozda
CREATE OR REPLACE FUNCTION maksik (a NUMBER, b NUMBER) RETURN NUMBER IS
BEGINIF a>b THEN RETURN a;ELSE return b;END IF;
END maksik;
![Page 30: Zsbd PL/SQL część 2](https://reader036.fdocument.pub/reader036/viewer/2022081520/56815685550346895dc43278/html5/thumbnails/30.jpg)
Przekazywanie parametrów
dr P. Drozda
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
![Page 31: Zsbd PL/SQL część 2](https://reader036.fdocument.pub/reader036/viewer/2022081520/56815685550346895dc43278/html5/thumbnails/31.jpg)
Jak wywołać funkcję
dr P. Drozda
Linia komend EXECUTE dbms_output.put_line(maksik(20,30)) VARIABLE wieksza NUMBER
EXECUTE :wieksza :=maksik(100,200) Blok anonimowy
DECLARE wieksza NUMBER;
BEGINwieksza:=maksik(30,50);
END; Instrukcja SQL
SELECT job_id, maksik(min_salary, max_salary) FROM jobs;
![Page 32: Zsbd PL/SQL część 2](https://reader036.fdocument.pub/reader036/viewer/2022081520/56815685550346895dc43278/html5/thumbnails/32.jpg)
Używanie funkcji w instrukcjach SQL
dr P. Drozda
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
![Page 33: Zsbd PL/SQL część 2](https://reader036.fdocument.pub/reader036/viewer/2022081520/56815685550346895dc43278/html5/thumbnails/33.jpg)
Ograniczenia na użycie w SQL
dr P. Drozda
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