ZSBDPL/SQL CZĘŚĆ 2 Wykład 4Prowadzący: dr Paweł Drozda

Program wykładu

dr P. Drozda

Wyjątki Procedury Funkcje

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ą

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

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;

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

Tworzenie obsługi – sekcja EXCEPTION

dr P. Drozda

BEGIN …EXCEPTIONWHEN wyjatek1 [OR wyjatek2 …] THENinstrukcje;…WHEN wyjatek3 [OR wyjatek4…] THENinstrukcjeWHEN OTHERS THENinstrukcje;END;

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’);

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

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

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;

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;

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

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;

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

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

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

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ść

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;

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)

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;

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;

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

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

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’);

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

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

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;

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;

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

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;

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

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