plik

POLITECHNIKA ŚWIĘTOKRZYSKA

ATEDRA

YSTEMÓW

TEROWANIA

ARZĄDZANIA

Instrukcja Laboratoryjna 9

Bazy danych – „Wyjątki, kursory, wyzwalacze

Instrukcje opracował:
mgr inż. Łukasz Zawarczyński

Kielce 2010

1. Program laboratorium:

9. Kursory, wyzwalacze, wyjątki w BD.

2. Cel ćwiczenia:

Celem ćwiczenia jest napisanie podprogramów do zadanych projektów BD. Zadania zostaną

podane na zajęciach dla indywidualnego projektu BD.

3. Wyjątki:

Pisanie własnych podprogramów w języku PL/SQL wymaga od programisty stosowania pewnych
algorytmów zabezpieczeń, wykonania danej operacji (transakcji) na istniejącej bazie danych. W

przypadku, gdy nie zostają naruszone wcześniej zadeklarowane więzy spójności, prawie każda
operacja SQL zostanie wykonana (z małymi wyjątkami tj.: savepoint, segmenty wycofania rollback

itp.). Co się dzieje w przypadku gdy aktualizujemy wpis bazy, lub chcemy dodać nową wartość dla
danego atrybutu. Procedura (czy funkcja) powinna zawierać mechanizm sprawdzania wpisów BD.

Jedną z metod sprawdzania (wywołania) jest funkcja:

raise_application_error(numer_błędu, 'Tekst błędu');

Funkcje daje możliwość:

•

Zatrzymania programu.

•

Wycofania wszelakich zmian dokonanych przez transakcję, w ramach której jest

wykonywany dany podprogram.

•

Wyświetlenia numeru błędu oraz komunikatu, które podaje się jako parametry

PERZYKŁAD:

Napisać procedurę, która usunie studenta o zadanym (jako parametr) numerze albumu i sprawdzi
wpis w bazie danych:

create or replace procedure student_p (album in dane.nr_albumu%type) IS

gdy_nie_ma exception;
begin

delete from dane where nr_albumu=album;
if sql%notfound then raise gdy_nie_ma;

end if;
exception

when gdy_nie_ma then
raise_application_error (12345, 'Nie ma studenta o takim numerze albumu !!!');

end student_p

4. Kursory:

W systemie Oracle dane dotyczące wykonywania poszczególnych transakcji, są przechowywane w

specjalnych obszarach pamięci SQL. Poprzez określenie obszaru, przez wskazanie nazwy (określenie
kursora) mamy możliwość dostępu do pewnych informacji w nim zawartych. W systemie Oracle

istnieją dwa typy kursorów: jawne i niejawne (zapytania składające się z jednego wiersza, Oracle
niejawnie tworzy kursor dla instrukcji DML i DDL).

Aby przetworzyć wyniki zapytań złożonych, zawierających wiele wierszy instrukcji, kursor musi

zostać jawnie zadeklarowany.

Algorytm konfiguracji kursorów jawnych:

•

zadeklarowanie kursora

CURSOR nazwa IS zapytanie_select

•

otwarcie kursora

•

pobranie danych z kursora

•

zamknięcie kursora

Powyższe operacje
OPEN, FETCH, CLOSE lub FOR LOOP

Najprostsza deklaracja kursora polega na podaniu nazwy w sekcji deklaracyjnej oraz

przyporządkowanie np.: instrukcji SELECT.

CURSOR nazwa_kursora[(parametr[,parametr] ...) IS
instrukcja_select;
parametr::=nazwa_parametru typ_danych[{:= | DEFAULT)wyrażenie]

PRZYKŁAD:

CURSOR nr (album NUMBER) IS SELECT * FROM dane WHERE nr_albumu=album;

PRZYKŁAD:

Utworzyć blok PL/SQL, który dokona podwyżki 10% dla pracownika o nazwisku Kowalski:

DECLARE

rekord pracownik%ROWTYPE
CURSOR pracownik_kursor (nazwisko_prac) IS

SELECT pensja FROM pracownik WHERE nazwisko=nazwisko_prac

FOR UPDATE OF pensja;

BEGIN

FOR rekord IN pracownik_kursor(rekord)

LOOP

UPDATE pracownik SET pensja=rekord.pensja*1.1 WHERE CURRENT OF
pracownik_kursor;

END LOOP;

COMMIT;

END;

Po otwarciu kursora, wczytany zbiór wyników posiada atrybut „tylko do odczytu”, wskaźnik kursora

wskazuje na pierwszy wiersz tabeli, otwarcie wcześniej otwartego kursora wygeneruje wyjątek
CURSOR_ALREADY_OPEN, a zbiór wyników generowany jest tylko i wyłącznie w momencie

otwarcia, a nie deklaracji.

Pobieranie danych z kursora odbywa się poprzez przepisanie danych bezpośrednio do zmiennych:

FETCH nazwa_kursora INTO zm1, zm2, ..., zmn;

lub do zmiennej rekordowej:

rekord nazwa_kursora%ROWTYPE

lub:

rekord nazwa_tabeli%ROWTYPE

oraz:

FETCH nazwa_kursora INTO rekord

Atrybuty kursorów:

%FOUND – wartością atrybutu jest TRUE, jeśli ostatnie wywołanie jest FETCH dla kursora
zakończyło się sprawdzeniem wiersza, albo FALSE jeśli nie. Po otwarciu kursora, przed pierwszym

wywołaniem FETCH, wartością atrybutu jest NULL.

%ISOPEN – wartością atrybutu jest TRUE, jeśli kursor jest otwarty, albo FALSE jeśli jest on

zamknięty.

%NOTFOUND – wartością atrybutu jest FALSE, jeśli ostatnie wywołanie FETCH dla kursora
zakończyło się sprawdzeniem wiersza, albo TRUE jeśli nie. Po otwarciu wartość NULL.

%ROWCOUNT – wartością atrybutu jest ilość wierszy odebranych przez FETCH.

Wielokrotne pobieranie danych z kursora:

LOOP
FETCH csr_name ...
EXIT WHEN csr_name%NOTFOUND
... -- obsługa danych
END LOOP

lub:

FETCH csr_name ...
WHILE ( csr_name%FOUND) LOOP
... -- obsługa danych
FETCH csr_name ...

END LOOP

oraz:

FOR idx IN csr_name LOOP
... -- obsługa danych
END LOOP

FOR idx IN ( SELECT ... FROM ... ) LOOP
... -- obsługa danych
END LOOP

PRZYKŁAD:

Napisać procedurę, która wyświetli ilość pracowników wraz z listą danych:

Pracownicy (id_prac, nazwisko, etat, id_szefa, data_z, pensja, id_zesp);
Zespoly (id_zesp, nazwa, ... );

PROCEDURE lista (zespol INTEGER) IS

numer_prac pracownicy.id_prac%TYPE;
nazwisko pracownicy.nazwisko%TYPE;

CURSOR c IS SELECT id_prac, nazwisko FROM pracownicy WHERE id_zesp=zespol;
BEGIN

OPEN c

LOOP

FETCH c INTO numer_prac, nazwisko;
IF c%NOTFOUND THEN EXIT;

END IF;

dbms_output.put_line(numer_prac || ' ' || nazwisko);

END LOOP;

dbms_output.put_line('Wierszy: '||c%ROWCOUNT);

CLOSE c;

END;

5. Wyzwalacze BD:

Wyzwalacze bazy danych służą głównie do oprogramowania niestandardowych więzów spójności.
Można również określić pewną grupę operacji (czynności), które będą wykorzystywane w każdej

aplikacji, korzystającej z systemu BD. Wyzwalacz może dotyczyć każdej instrukcji operowania
danymi tj.: INSERT, DELETE i UPDATE.

CREATE [OR REPLACE] TRIGGER nazwa_wyzwalacza
BEFORE | AFTER
[INSERT lub (OR) UPDATE lub (OR) DELETE]
ON nazwa_tabeli
[FOR EACH ROW]
blok_PL/SQL

Kolejność wywoływania wyzwalaczy:
a) wyzwalacz przed instrukcją

b) wyzwalacz przed pierwszym wierszem, na którym operuje instrukcja
c) wyzwalacz po pierwszym wierszu, na którym operuje instrukcja

d) ...

e) wyzwalacz przed ostatnim wierszem

f) wyzwalacz po ostatnim wierszu;
g) wyzwalacz po instrukcji

W celu odróżnienia starych od nowych wartości w danym wierszu, używa się oznaczeń przed, NEW

i po zmianie, OLD. Aby sprawdzić wyjątek, należy użyć wyzwalacza BEFORE – gdy wystąpi błąd
wykonanie operacji zostanie anulowane. Do realizacji stałych operacji, po wykonaniu danej

instrukcji używa się wyzwalacza AFTER. Wyzwalacz ten daje możliwość podglądu instrukcji, która
„odpaliła” dany wyzwalacz. Do tego celu wykorzystuje się zmienne logiczne: INSERTING, DELETING

I UPDATING.

PRZYKŁAD:
Napisać wyzwalacz, który będzie kontrolował wpisy, dotyczące zarobków dla danej grupy

pracowników:

CREATE OR REPLACE TRIGGER sprawdz
BEFORE INSERT OR UPDATE OF pensja ON pracownicy FOR EACH ROW

DECLARE

min_kw DECIMAL(5,2):=1000;
max_kw DECIMAL(5,2):=15000;

pensja DECIMAL(5,2);

BEGIN

SELECT pensja INTO pensja FROM pracownicy WHERE dzial=”mieszalnia”;

IF (:new.pensja > max_kw) AND (:new.pensja < min_kw) THEN
raise_application_error(-20500,'Kwota z poza zakresu danej grupy !!!');

ENDIF

END;

ALTER TRIGGER nazwa {ENABLE | DISABLE}; – włączenie wyzwalacza

DROP TRIGGER nazwa; – usunięcie wyzwalacza

Informacje o wyzwalaczach są przechowywane w perspektywie User_triggers. Listę można
sprawdzić tak jak atrybuty tablicy, przy czym nazwy wyzwalaczy podaje się dużymi literami.

SELECT Trigger_type, Table_name, Triggering_event FROM
User_triggers WHERE Trigger_name='nazwa_wyzwalacza';

lub:

SELECT Trigger_body FROM User_triggers WHERE
Trigger_name='nazwa_wyzwalacza';

6. Zadania do wykonania:

Napisać podprogramy modyfikujące indywidualny projekt bazy danych. Zadania zostaną

podane na zajęciach przez prowadzącego.