Informatyka, Bazy danych II

Kierunek Informatyka

Laboratorium 7 - Wyjątki w PL/SQL

Zagadnienia:

• obsługa wyjątków predefiniowanych

• tworzenie własnych wyjątków

• przechwytywanie własnych wyjątków;

Rodzaje wyjątków w PL/SQL:

1. Definiowane wyjątki systemowe - wywoływane w przypadku wystąpienia błędów przetwarzania RDBMS lub błędów języka PL/SQL

EXCEPTION

WHEN CURSOR_ALREADY_OPEN

THEN CLOSE my_cursor;

END;

2. Definiowane wyjątki użytkownika - wywoływane w przypadku wystąpienia błędów aplikacji użytkownika, definiowane w części deklaracyjnej i wywoływane explicite w części ciała bloku PL/SQL

PROCEDURE calc_annual_sales

(company_id_in IN company.company_id%TYPE)

IS

invalid_company_id EXCEPTION;

no_sales_for_company EXCEPTION;

negative_balance EXCEPTION;

duplicate_company BOOLEAN;

BEGIN

... body of executable statements ...

EXCEPTION

WHEN invalid_company_id THEN ...

WHEN no_sales_for_company THEN ...

END;

3. Niedefiniowane wyjątki systemowe - tylko niewielka część błędów ma swoje zdefiniowane wyjątki systemowe, reszta ma swoje numery błędów i może być obsługiwana zgodnie ze składnią PRAGMA EXCEPTION_INIT

PROCEDURE delete_company (company_id_in IN NUMBER)

IS

/* Deklaracja wyjatku. */

still_have_employees EXCEPTION;

/* Przyporzadkowanie wyjatku do numeru bledu */

PRAGMA EXCEPTION_INIT (still_have_employees, −2292);

BEGIN

DELETE FROM company

WHERE company_id = company_id_in;

EXCEPTION

/* Wyjatek wywolany w zwiazku z zaleznoscia tabeli od innych */

WHEN still_have_employees

THEN

DBMS_OUTPUT.PUT_LINE (' Please delete employees for company first.');

END;

Można także wykorzystać kody błędów do informacji o wystąpieniu wyjątku: SQLERRM, SQLCODE - użycie klauzuli WHEN OTHERS w części EXCEPTION.

PROCEDURE delete_company (company_id_in IN NUMBER)

IS

BEGIN

DELETE FROM company

WHERE company_id = company_id_in;

EXCEPTION

WHEN OTHERS

THEN

/* Blok PL/SQL w czesci EXCEPTION */

DECLARE

error_code NUMBER := SQLCODE;

error_msg VARCHAR2 (300) := SQLERRM;

BEGIN

IF error_code = −2292

THEN

/* Tabela podrzedna zawiera powiazania. Usun powiazania! */

DELETE FROM employee

WHERE company_id = company_id_in;

/* Teraz usun wlasciwe dane! */

DELETE FROM company

WHERE company_id = company_id_in;

ELSIF error_code = −2291

THEN

/* ID nie zostal znaleziony */

DBMS_OUTPUT.PUTLINE

(' Invalid company ID: '||TO_CHAR (company_id_in));

ELSE

/* Uzycie WHEN OTHERS wewnatrz WHEN OTHERS! */

DBMS_OUTPUT.PUTLINE

(' Error deleting company, error: '||error_msg);

END IF;

END;

END delete_company;

4. Nienazwane wyjątki użytkownika - wywoływane za pomocą polecenia RAISE_APPLICATION_ERROR, ze zdefiniowaniem kodu błędu i wiadomości o błędzie, wykorzystywane do obsługi błędów komunikacji klient - serwer.

RAISE_APPLICATION_ERROR(error_number, message[, {TRUE | FALSE}]);

error_number z zakresu -20000 .. -20999

message - maks. do 2048 bajtów tekstu

TRUE/FALSE - jeśli false (domyślnie), zastępuje poprzednie wiadomości o błędach w podprogramie

Uwaga!

W przypadku wystąpienia tego wyjątku, zmiany globalne na obiektach bazy danych nie będą wycofane, wymaga to więc użycia explicite polecenia ROLLBACK.

Wyjątki w PL/SQL - facts & tips

1. Sekcja EXCEPTION w bloku PL/SQL obsługuje tylko wyjątki wywołane w tym bloku.

2. Wyjątek wywołany w części deklaracyjnej bloku PL/SQL jest obsługiwany w części EXCEPTION bloku wyższego, jeśli istnieje ta część.

3. Wyjątki wywołane w sekcji EXCEPTION są obsługiwane w części EXCEPTION bloku wyższego, jeśli istnieje ta część.

4. Jeśli chcemy przechwycić wszelkie wyjątki, należy korzystać z klauzuli WHEN_OTHERS.

5. W wypadku wystąpienia wyjątku, obsługa programu przechodzi do części EXCEPTION. Nie ma możliwości powrotu do części wykonywalnej tego bloku, po wywołaniu wyjątku.

6. Po przechwyceniu wyjątku, w następnej kolejności wykonywana jest sekcja BODY wyższego bloku PL/SQL, o ile istnieje ta część.

7. W celu przechwycenia specyficznych wyjątków, należy je definiować i nazywać.

Ponadto

Ponowne definiowanie wyjątków predefiniowanych

EXCEPTION

WHEN invalid_number OR STANDARD.INVALID_NUMBER

THEN ...

Jawne wywołanie wyjątków predefiniowanych

Podobnie jak wywołuje się wyjątek definiowany przez użytkownika, można również jawnie wywołać wyjątek predefiniowany, który z reguły wywołuje sam system ORACLE.

...

IF acct_type NOT IN (1, 2, 3) THEN

RAISE INVALID_NUMBER;

...

Wyjątki wywoływane w części deklaracyjnej

PROCEDURE delete_company (company_id_in IN NUMBER)

IS

zmienna varchar2(2) := 'ZA DUZO TEKSTU W TEJ ZMIENNEJ';

BEGIN

...

EXCEPTION

WHEN VALUE_ERROR THEN

END;

Wyjątki wywoływane w części EXCEPTION

PROCEDURE delete_company (company_id_in IN NUMBER)

IS

zmienna varchar2(2) := 'ZA DUZO TEKSTU W TEJ ZMIENNEJ';

BEGIN

...

BEGIN

...

EXCEPTION

WHEN VALUE_ERROR THEN RAISE NO_DATA_FOUND;

WHEN NO_DATA_FOUND THEN ...

END;

...

EXCEPTION

WHEN NO_DATA_FOUND THEN ...

END;

Wywołanie wyjątku w części wyjątku powoduje przerwanie wykonywania części EXCEPTION w danym bloku PL/SQL, przejście do części EXCEPTION bloku wyższego i obsługę wywołanego wyjątku.

Przypomnienie - systemowe wyjątki predefiniowane:

DUP_VAL_ON_INDEX: wywoływany, gdy na skutek wykonania polecenia insert lub update różne rekordy relacji posiadają tę samą wartość atrybutu zadeklarowanego jako unikalny;

INVALID_CURSOR: wywoływany w wyniku wykonania operacji na kursorze, która jest niepoprawna w pewnym kontekście, np. zamknięcie nieotwartego kursora;

INVALID_NUMBER: wywoływany, gdy nie jest możliwa konwersja łańcucha znaków do postaci liczby, ponieważ łańcuch zawiera niedopuszczalne wartości;

LOGIN_DENIED: wywoływany w przypadku podania niepoprawnych parametrów w poleceniu rozpoczęcia sesji - login, np. nazwy użytkownika lub hasła;

NO_DATA_FOUND: wywoływany, gdy polecenie select nie wyznacza żadnej krotki;

NOT_LOGGED_ON: wywoływany w wyniku próby wykonania funkcji Oracle przez użytkownika nie dołączonego do systemu;

PROGRAM_ERROR: wywoływany w wyniku błędnego działania programu PL/SQL;

STORAGE_ERROR: wywoływany w wyniku błędu pamięci lub braku miejsca w pamięci;

TIMEOUT_ON_RESOURCE: wywoływany po upłynięciu czasu oczekiwania na przydzielenie zasobu;

TOO_MANY_ROWS: wywoływany, gdy jest wymagana dokładnie jedna krotka, a polecenie select wyznacza większą liczbę krotek;

VALUE_ERROR: wywoływany w przypadku błędu konwersji typów danych (np. próby wpisania daty do numerycznego pola krotki) lub, gdy aktualna długość danej przekracza wcześniej zadeklarowaną długość (np. próba wpisania do pola krotki o długości 3 znaków danej o długości 5 znaków);

ZERO_DIVIDE: wywoływany w wyniku próby dzielenia przez 0;

OTHERS: wywoływany w dla wszystkich wyjątków, które nie zostały wyspecyfikowane w bloku exception; definiowany jako ostatni w bloku exception.

Kody błędów w PL/SQL:

ACCESS_INTO_NULL ORA-06530 -6530

CASE_NOT_FOUND ORA-06592 -6592

COLLECTION_IS_NULL ORA-06531 -6531

CURSOR_ALREADY_OPEN ORA-06511 -6511

DUP_VAL_ON_INDEX ORA-00001 -1

INVALID_CURSOR ORA-01001 -1001

INVALID_NUMBER ORA-01722 -1722

LOGIN_DENIED ORA-01017 -1017

NO_DATA_FOUND ORA-01403 +100

NOT_LOGGED_ON ORA-01012 -1012

PROGRAM_ERROR ORA-06501 -6501

ROWTYPE_MISMATCH ORA-06504 -6504

SELF_IS_NULL ORA-30625 -30625

STORAGE_ERROR ORA-06500 -6500

SUBSCRIPT_BEYOND_COUNT ORA-06533 -6533

SUBSCRIPT_OUTSIDE_LIMIT ORA-06532 -6532

SYS_INVALID_ROWID ORA-01410 -1410

TIMEOUT_ON_RESOURCE ORA-00051 -51

TOO_MANY_ROWS ORA-01422 -1422

VALUE_ERROR ORA-06502 -6502

ZERO_DIVIDE ORA-01476 -1476

Zadania do samodzielnego wykonania:

Zad. 1.

Wykorzystując funkcję NVL napisz blok PL/SQL, który będzie wypisywał stosunek płacy podstawowej do premii dla danego pracownika. Wyniki posortuj wg nazwiska. Obsłuż wyjątek predefiniowany dla dzielenia przez zero.

1. Andrzej Barczak Wskaźnik 5,529

2. Mieczysław Foryś Wskaźnik 6,518

Dzielenie przez zero dla wiersza 3

Zad. 2.

Zmodyfikuj poprzednie zadanie uruchamiając obsługę ZERO_DIVIDE dla każdego wiersza:

1. Andrzej Barczak Wskaźnik 5,529

2. Mieczysław Foryś Wskaźnik 6,518

Dzielenie przez zero dla wiersza 3

Dzielenie przez zero dla wiersza 4

5. Antoni Jówko Wskaźnik 3,387

Dzielenie przez zero dla wiersza 6

7. Bogusław Leszczyński Wskaźnik 7,948

8. Piotr Matusak Wskaźnik 7,238

Dzielenie przez zero dla wiersza 9

...

Zad. 3.

Napisz procedurę PL/SQL, która wypisze informacje o danym pracowniku (imię, nazwisko, płaca podstawowa, nazwa jednostki). Parametrem procedury jest ID pracownika. Obsłuż wszelkie możliwe wyjątki.

Zad. 4.

Zmodyfikuj procedurę z zadania 3 tak, by dla pracowników nie mających premii został zdefiniowany i obsłużony wyjątek nowy (raise_application_error).

Zad. 5.

Utwórz procedurę wypisującą liczbę osób pracujących w jednostkach AP o ID < niż liczba podana jako parametr procedury. Zadeklaruj własny wyjątek no_data_found i wywołaj go dla liczby osób < 2. Obsługa wyjątku razem z obsługą dla NO_DATA_FOUND.

Akademia Podlaska - liczba osób: 6

Wydział Nauk Ścisłych - liczba osób: 4

Wydział Humanistyczny - liczba osób: <= 2!!!

Zad. 6.

Zmodyfikuj poprzednią procedurę dodając jej obsługę błędów dla nieprawidłowej liczby wprowadzanej jako parametr (jawne wywołanie i obsługa INVALID_NUMBER). Zakładamy, że poprawną liczbą jest liczba tylko z zakresu id_jedn.

Zad.7.

Utwórz procedurę PL/SQL zwracającą sumę zarobków w danej jednostce AP. Uwaga! W przypadku braku pracowników suma wynosi 0. Obsłuż wszelkie możliwe wyjątki.

Po skończonej pracy usuń wszystkie tabele z przestrzeni Oracle.

Laboratorium 8 - Obsługa DDL w PL/SQL. Obsługa pakietów PL/SQL

• Wykonanie instrukcji DDL przy zastosowaniu EXECUTE IMMEDIATE

• Tworzenie i usuwanie własnych pakietów

• Ustawianie praw dostępu do pakietów;

---