Wykład 6

Programowanie aplikacji baz danych 
po stronie serwera - obiekty programistyczne

 

Streszczenie

W tym wykładzie omówimy obiekty programistyczne przechowywane w bazie danych Oracle: procedury, funkcje, pakiety i wyzwalacze bazy danych oraz ich zastosowanie przy programowaniu aplikacji bazy danych po stronie serwera.
 


6.1 Procedury i funkcje

Procedury, funkcje i pakiety są to obiekty zapisywane w bazie danych tak jak inne obiekty. Jest także możliwość definiowania funkcji i procedur w blokach PL/SQL; ich użycie jest wtedy ograniczone do tego bloku. Raz zapisane w bazie danych procedury i funkcje mogą być używane w każdej aplikacji bazy danych, przez każdego użytkownika (zakładając posiadanie odpowiednich uprawnień tak samo jak w przypadku dowolnych innych obiektów bazy danych).

Typowe sytuacje ich użycia są następujące:

1. zdefiniowanie podstawowych funkcji używanych przez aplikacje, np. utworzenie zamówienia, usunięcie klienta;

2. przechowywanie w bazie danych zadań typu wsadowego, np. tworzenie tygodniowego zestawienia rachunków;

3. zapisanie w bazie danych transakcji, np. zebranie i przetworzenie informacji z odległych baz danych;

4. utworzenie ogólnego, dzielonego kodu przechowywanego na serwerze.

Definicja procedury ma następującą składnię:
 
CREATE [OR REPLACE] PROCEDURE nazwa (lista_parametrów_formalnych) {AS|IS}
blok PL/SQL bez słowa kluczowego DECLARE
 
Słowo REPLACE oznacza, że w sytuacji, gdy procedura o tej samej nazwie już istnieje, to nie należy traktować tego jak  błędu, a tylko zastąpić istniejący obiekt przez nowy. Jest to użyteczne przy testowaniu i uruchamianiu procedur, gdy wielokrotnie trzeba zmieniać ich treść.

Są trzy typy przekazywania parametrów:

  1. IN - wartość przychodzi z wywołującej jednostki programu i nie ulega zmianie; w procedurze lub funkcji, parametr typu IN nie może wystąpić po lewej stronie instrukcji przypisania,
  2. OUT – żadna wartość nie przychodzi z wywołującej jednostki programu; przy zwykłym zakończeniu (bez błędu) wartość parametru jest przekazywana do wywołującej jednostki programu,
  3. IN OUT (domyślny) – wartość przychodzi z wywołującej jednostki programu i przy zwykłym zakończeniu (bez błędu) wartość parametru jest przekazywana do wywołującej jednostki programu.

Specyfikując typ danych parametru formalnego nie podaje się jego rozmiaru, a więc dla parametru liczbowego używany jest typ NUMBER, dla typu napisowego zmiennej długości typ VARCHAR2. Można też używać specyfikacji typu danych odwołującego się do typu danych istniejącej kolumny:
 
tabela.kolumna%TYPE

 
Przykład

Wpłatę na konto można zapisać za pomocą następującej procedury:

CREATE OR REPLACE PROCEDURE Credit (acc_no IN NUMBER, amount IN NUMBER) AS
BEGIN
  UPDATE Accounts a SET a.Balance = a.Balance + amount
  WHERE a.Account_id = acc_no;
END;
/
W SQL wywołujemy procedurę w następujący sposób:
CALL Credit(10112, 1000);
W kodzie PL/SQL wywołujemy procedurę w zwykły sposób:
Credit(10112, 1000);
W SQL*Plus poprzedzamy nazwę procedury słowem kluczowym EXECUTE:
EXECUTE Credit(10112, 1000)
Sprawdzenie salda na koncie można zrealizować albo za pomocą procedury korzystając z parametru OUT albo za pomocą funkcji. Oto sposób korzystający z procedury:
CREATE OR REPLACE PROCEDURE Saldo (acc_no IN NUMBER, bal OUT NUMBER) AS
BEGIN
  SELECT a.Balance INTO bal FROM Accounts a
  WHERE a.Account_id = acc_no;
END;
/
Przy wywołaniu procedury Saldo drugim argumentem powinna być nazwa zmiennej liczbowej, na którą zostanie przypisany aktualny stan konta. Składnia definicji funkcji jest podobna do definicji procedury:
 
CREATE [OR REPLACE] FUNCTION nazwa (lista_parametrów_formalnych
RETURN typ {AS|IS}
blok PL/SQL bez słowa kluczowego DECLARE z instrukcją RETURN wyrażenie

 
Przykład

Oto przykład funkcji zwracającej stan konta.

CREATE OR REPLACE FUNCTION Saldo (acc_no IN NUMBER)
RETURN NUMBER IS
  value NUMBER;
BEGIN
  SELECT a.Balance INTO value FROM Accounts a
  WHERE a.Account_id = acc_no;
  RETURN value;
END;
/
W kodzie SQL lub PL/SQL używamy funkcji w zwykły sposób, np.
SELECT Saldo(a.Account_id) FROM Accounts a;
lub
x := Saldo(10112);
Natomiast w SQL*Plus możemy wywołać i wypisać wartość funkcji w następujący sposób:
VARIABLE p INTEGER
EXECUTE :p:= Saldo(10112)
PRINT p
Parametrom procedur i funkcji można przyporządkowywać wartości domyślne. Umieszcza się je wówczas na końcu listy parametrów formalnych.
CREATE OR REPLACE PROCEDURE Wpisz_studenta(
          imię IN Studenci.Imię%TYPE,
          nazwisko IN Studenci.Nazwisko%TYPE,
          rok IN Studenci.Rok%TYPE DEFAULT 1)
AS
  indeks Studenci.Indeks%TYPE;
BEGIN
  SELECT NVL(Max(s.Indeks)+1,1) INTO indeks
  FROM Studenci s;
  INSERT INTO Studenci
  VALUES(indeks,imię,nazwisko,rok);
END;
/
Gdy dodajemy studenta pierwszego roku, piszemy:
Wpisz_studenta('Jaś', 'Fasola');
Gdy student z innej uczelni przenosi się do nas od razu na rok 2, piszemy:
Wpisz_studenta('Jaś', 'Fasola ',2);
Gdy jest więcej parametrów o wartościach domyślnych, np. w procedurze Wpisz_studenta mógłby być jeszcze parametr data DATE DEFAULT Sysdate, wybór parametrów domyślnych, którym w chwili wywołania procedury przypisuje się wartości, dokonuje się przez wskazanie explicite nazwy parametru, np.
Wpisz_studenta('Jaś', 'Fasola', Data => '1-SEP-99');
Funkcji zapisanych w bazie danych można używać w instrukcjach SQL w taki sam sposób, jak funkcji standardowych – pod warunkiem, że nie zmieniają stanu bazy danych (nie mogą więc zawierać instrukcji INSERT, DELETE i UPDATE) ani nie mają parametrów wyjściowych. Nie powinny też korzystać ze zmiennych nielokalnych zadeklarowanych w pakietach. Wszystkie parametry muszą zostać wyspecyfikowane i nie wolno używać dla nich notacji typu Data => '1-SEP-99'.

Każdy podprogram może zgłosić błąd i przekazać go aplikacji, która go wywołała:
 
Raise_application_error(numer_błędu, tekst_komunikatu);

Zakres numerów od –20000 do –20999 jest zarezerwowany dla błędów definiowanych w procedurach i funkcjach.

Informację o parametrach procedury lub funkcji uzyskujemy od systemu za pomocą instrukcji DESCRIBE, np.

DESCRIBE Aktualizuj_zarobki
DESCRIBE Saldo
Do oglądania treści procedury zapisanej w bazie danych służy perspektywa słownika danych o nazwie User_source. Najwygodniej jest używać jej w następujący sposób:
COLUMN Line FORMAT 99999
COLUMN Text FORMAT A80
SET PAGESIZE 23
SET PAUSE ON
SELECT u.Line, u.Text
FROM User_Source u
WHERE u.Name = 'Nazwa_procedury';
Nazwę procedury (ewentualnie funkcji lub pakietu) należy wpisać dużymi literami! System wypisze tekst procedury podając numery linii, przy których użyciu łatwiej jest wyznaczyć miejsce wystąpienia błędu przy kompilacji procedury (to jest przy wykonywaniu instrukcji CREATE PROCEDURE). Na etapie testowania i usuwania błędów po każdej instrukcji CREATE PROCEDURE należy wykonywać lub ewentualnie umieszczać w skrypcie, jeśli kompilowane procedury znajdują się w skrypcie, następującą instrukcję SQL*Plus, która, gdy wystąpią błędy, wypisze je na ekran:
SHOW ERRORS
Przy testowaniu i wykrywaniu błędów semantycznych można używać procedury
DBMS_OUTPUT.Put_line(tekst)
Wyświetlany na ekranie tekst może zawierać w sobie aktualne wartości zmiennych, np.
DBMS_OUTPUT.Put_line('Pracownik: '||nazwisko||' Zarobki: ' ||TO_CHAR(zarobki));
Gdy zmieniają się obiekty, do których odwołuje się procedura lub funkcja, Oracle automatycznie dokonuje ponownej kompilacji, gdy ta procedura lub funkcja zostanie wywołana. Konieczność ponownej kompilacji można odczytać z perspektywy słownika danych User_objects:
SELECT u.Status 
FROM User_objects u
WHERE u.Object_name = 'OBLICZ_ZAROBKI';
Konieczność ponownej kompilacji wskazuje wartość Status = 'INVALID'. Aby samemu spowodować wykonanie ponownej kompilacji, używamy instrukcji:
ALTER PROCEDURE Oblicz_zarobki COMPILE;
Uprawnienia do użycia procedury (uprawnienie EXECUTE) nadaje się w standardowy sposób, np.
GRANT EXECUTE ON Oblicz_zarobki TO Moja_księgowa;

 


6.2 Pakiety

Ze względu na dużą liczbę procedur i funkcji, jakie zwykle powstają podczas tworzenia aplikacji, konieczne jest pogrupowanie ich w większe jednostki nazywane pakietami. W ramach pakietu możemy globalnie dla niego zdefiniować:

Każda sesja ma swoją własną kopię wewnętrznych struktur danych pakietu (np. zmiennych lokalnych). Zmienne i stałe zachowują swoje wartości na czas trwania sesji.

Pakiet składa się z dwóch części:

Składnia tworzenia specyfikacji pakietu jest następująca:
 
CREATE (OR REPLACE) PACKAGE nazwa_pakietu AS
       Deklaracje obiektów publicznych (w przypadku procedur i funkcji specyfikacja nagłówków)
END nazwa_pakietu;
 
Składnia tworzenia części implementacyjnej pakietu jest podobna:
 
CREATE (OR REPLACE) PACKAGE BODY nazwa_pakietu AS
   Definicje obiektów publicznych i prywatnych
END nazwa_pakietu;
 
Można też na koniec części implementacyjnej dołączyć kod inicjacyjny pakietu.
 
CREATE (OR REPLACE) PACKAGE BODY nazwa_pakietu AS
   Definicje obiektów publicznych i prywatnych
BEGIN
  Instrukcje inicjujące
END nazwa_pakietu;
 
Przykład

Rozważymy problem przyjmowania i zwalniania pracowników. Najpierw specyfikacja pakietu:

CREATE PACKAGE Obsługa_prac AS
  PROCEDURE Zatrudnij(
    numer_prac NUMBER,
    nazwisko VARCHAR2,
    zarobki NUMBER,
    numer_działu NUMBER);
  PROCEDURE Zwolnij(
    numer_prac NUMBER);
  ile_przyjętych NUMBER;
  ile_zwolnionych NUMBER;
END Obsługa_prac;
/
A następnie część implementacyjna tego pakietu:
CREATE PACKAGE BODY Obsługa_prac AS
  /* Procedura zatrudniania pracownika */
  PROCEDURE Zatrudnij(numer_prac NUMBER, nazwisko VARCHAR2,
    zarobki NUMBER, numer_działu NUMBER) IS
  BEGIN
    INSERT INTO Pracownicy VALUES (numer_prac, nazwisko, zarobki, numer_działu);
    COMMIT;
    ile_przyjętych := ile_przyjętych+1;
  END Zatrudnij;
  /* Procedura zwalniania pracownika */
  PROCEDURE Zwolnij(numer_prac NUMBER) IS
  BEGIN
    DELETE FROM Pracownicy WHERE Id_prac = numer_prac;
    COMMIT;
    ile_zwolnionych := ile_zwolnionych+1;
  END Zwolnij;
  BEGIN
     ile_przyjętych := 0;
     ile_zwolnionych := 0;
END Obsługa_prac;
/
Z zewnątrz dostęp do obiektów publicznych pakietu odbywa się przez podanie nazwy pakietu, kropki i nazwy obiektu, np.
Obsługa_prac.Zatrudnij(1000, 'Kowalski ', 2000, 12);
Zdefiniowany w specyfikacji pakietu globalny kursor może być współużywany przez wiele procedur. Rozważmy przykład:
CREATE OR REPLACE PACKAGE Pak IS
  CURSOR c1 IS SELECT Id_pracownika
               FROM Pracownicy
               ORDER BY Id_pracownika DESC;
  PROCEDURE Pierwsze_3;
  PROCEDURE Następne_3;
END Pak;
/
CREATE OR REPLACE PACKAGE BODY Pak IS
  v_id NUMBER;
  PROCEDURE Pierwsze_3 IS
  BEGIN
    OPEN c1;
    LOOP 
      FETCH c1 INTO v_id;
      DBMS_OUTPUT.PUT_LINE('Id :' || v_id);
      EXIT WHEN c1%ROWCOUNT >= 3;
    END LOOP;
  END Pierwsze_3;
  PROCEDURE Następne_3 IS
  BEGIN
     LOOP
        FETCH c1 INTO v_id;
        DBMS_OUTPUT.Put_line('Id :' || v_id);
        EXIT WHEN c1%ROWCOUNT >= 6;
     END LOOP;
     CLOSE c1;
   END Następne_3;
END Pak;
/
W podobny sposób można współdzielić inne obiekty, jak zmienne i rekordy PL/SQL. Zanotujmy, ze nie można przekazywać kursora przez parametr procedury ani nie można współdzielić zmiennych kursorowych. Można natomiast przekazywać zmienne kursorowe poprzez parametry procedur w pakiecie. Oto przykład w postaci skryptu ilustrujący przekazywanie zmiennej kursorowej poprzez parametry procedur w pakiecie.
VARIABLE krs REFCURSOR
CREATE OR REPLACE PACKAGE Emp_data AS
  TYPE Emp_cur_typ IS REF CURSOR RETURN emp%ROWTYPE;
  PROCEDURE Use_emp_cv(emp_cv IN OUT Emp_cur_typ);
  PROCEDURE Write(emp_cv IN OUT Emp_cur_typ);
END Emp_data;
/
CREATE OR REPLACE PACKAGE BODY Emp_data AS
  PROCEDURE Use_emp_cv(emp_cv IN OUT Emp_cur_typ)
  IS
  BEGIN
     OPEN emp_cv FOR SELECT * FROM emp;
  END use_emp_cv;
  PROCEDURE Write(emp_cv IN OUT Emp_cur_typ)
  IS rek emp_cv%ROWTYPE;
  BEGIN
     Use_emp_cv(emp_cv);
     DBMS_OUTPUT.Put_line('Pracownicy firmy:');
     LOOP
        FETCH emp_cv INTO rek;
        EXIT WHEN emp_cv%NOTFOUND;
        Dbms_output.Put_line(rek.ename);
     END LOOP;
     CLOSE emp_cv;
  END write;
END Emp_data;
/
execute Emp_data.Write(:krs)
Nazwy funkcji i procedur w pakiecie mogą być przeładowane, to znaczy tej samej nazwy można użyć dla różnych wersji tej samej procedury lub funkcji. Jest to wygodne w sytuacji, gdy różne, specjalne przypadki tej samej procedury lub funkcji możemy zapisać za pomocą zbioru procedur i funkcji używając tej samej nazwy, np. dodawanie z różną liczbą argumentów i różnymi typami danych. Wersje z tą samą nazwą muszą się różnić albo liczbą parametrów, albo nazwą i typem parametru. W przypadku powyższego pakietu można byłoby określić dwie wersje procedury Zwolnij:
PROCEDURE Zwolnij(Numer_prac NUMBER);
PROCEDURE Zwolnij(Nazwisko VARCHAR2);
Informacje o pakietach można odczytać w podobny sposób jak w przypadku funkcji i procedur posługując się perspektywą słownika danych User_source.

W przypadku funkcji deklarowanych w specyfikacji pakietu, które mają być używane w instrukcjach SQL, należy zamieszczać dyrektywę dla kompilatora określającą brak efektów obocznych w ich działaniu:
 
PRAGMA RESTRICT_REFERENCES(nazwa-funkcjiopcja, ...)

określa ewentualne współdziałanie funkcji z bazą danych i zmiennymi pakietu. Oto możliwe opcje:

1. WNDS - funkcja nie modyfikuje stanu bazy danych;

2. WNPS - funkcja nie modyfikuje wartości zmiennych pakietu;

3. RNDS - funkcja nie odczytuje zawartości bazy danych;

4. RNPS - funkcja nie odczytuje wartości zmiennych pakietu.

 
Na przykład dla funkcji obliczającej maksymalne zarobki pracownika w dziale:

FUNCTION Maks(Id_dz INTEGER) RETURN Real;
PRAGMA RESTRICT_REFERENCES (Maks, WNPS, WNDS, RNPS);
Procedury, funkcje i pakiety można usunąć z bazy danych za pomocą instrukcji:
 
DROP PROCEDURE procedura;

DROP FUNCTION funkcja;

DROP PACKAGE pakiet;

 
Zauważmy, że nie ma instrukcji DROP PACKAGE BODY! Instrukcja DROP PACKAGE usuwa jednocześnie zarówno specyfikację jak i część implementacyjną pakietu.

Oracle dostarcza pewnej liczby standardowych pakietów, takich jak używany już przez nas, DBMS_OUTPUT. Będzie jeszcze o niektórych z nich mowa za chwilę jak i w następnych wykładach.

Przy wykonywaniu bloku PL/SQL wiązania między identyfikatorami a obiektami bazodanowymi są statyczne – dokonywane w czasie kompilacji. Używając specjalnego standardowego pakietu DBMS_SQL można dokonywać wiązania w czasie wykonywania bloku. Oto przykład:

CREATE PROCEDURE Usuń_tab(nazwa_tabeli VARCHAR2) AS
  id_kursora INTEGER;
  wart INTEGER;
  polecenie VARCHAR2(250);
BEGIN
  polecenie := 'DROP TABLE '|| nazwa_tabeli;
  id_kursora := DBMS_SQL.Open_cursor;
  DBMS_SQL.Parse(id_kursora, polecenie, DBMS_SQL.v7);
  wart := DBMS_SQL.Execute(id_kursora);
  DBMS_SQL.Close_cursor(id_kursora);
END Usuń_tab;
/
W ten sposób można wykonywać też instrukcje definiujące dane jak CREATE TABLE.
 

6.3 Wyzwalacze bazy danych

Wyzwalacze bazy danych są procedurami:

Wyzwalacze bazy danych służą głównie do programowania więzów spójności i do programowania stałych czynności, które powinny być wykonywane w każdej aplikacji korzystającej z bazy danych.

Najczęściej używane są wyzwalacze tabelowe. Dla każdej tabeli można określić 12 typów wyzwalaczy (może być więcej niż jeden wyzwalacz jednego typu). Typ wyzwalacza zależy przede wszystkim od tego, czy dotyczy operacji na pojedynczym wierszu (wyzwalacz wierszowy) czy dotyczy wykonania całej instrukcji. Ponadto, zależy od tego, czy ma być wykonywany przed operacją (typ BEFORE) czy po (typ AFTER). Wreszcie, wyzwalacz może dotyczyć każdej z trzech instrukcji języka operowania danymi: INSERT, DELETE i UPDATE.

Wyzwalacze zostały wprowadzone do Standardu SQL:1999.
 

Wyzwalacze tabelowe

Oto składnia wyzwalacza tabelowego w Oracle:
 
CREATE [OR REPLACE] TRIGGER nazwa_wyzwalacza 
{BEFORE|AFTER} 
  specyfikacja_instrukcji
ON tabela
[FOR EACH ROW] 
blok_PL/SQL
 
gdzie specyfikacja_instrukcji jest ciągiem do trzech nazw instrukcji INSERT, DELETE i UPDATE połączonych spójnikiem OR. W przypadku UPDATE można dodatkowo podać nazwy kolumn, których aktualizacja, ma powodować odpalanie wyzwalacza – używając składni:
 
UPDATE OF kolumna,….

Kolejność odpalania wyzwalaczy jest następująca:
 

  1. wyzwalacz przed instrukcją,
  2. wyzwalacz przed pierwszym wierszem, na którym operuje instrukcja,
  3. wyzwalacz po pierwszym wierszu, na którym operuje instrukcja,
  4.  …
  5. wyzwalacz przed ostatnim wierszem,
  6. wyzwalacz po ostatnim wierszu,
  7. wyzwalacz po instrukcji.

Aby odróżnić w wyzwalaczach wierszowych stare i nowe wartości w wierszu tabeli, używamy następujących oznaczeń, odpowiednio na wiersz przed zmianą i na wiersz po zmianie:

1. :OLD – wiersz przed zmianą;

2. :NEW – wiersz po zmianie.
 

Przykład

IF :NEW.Zarobki < :OLD.Zarobki
Oto podstawowe sytuacje, w których używa się wyzwalaczy.

1. Do sprawdzania więzów spójności używamy wyzwalaczy typu BEFORE. W przypadku wystąpienia błędu, wykonanie odpowiedniej operacji zostaje anulowane.

Przykład
 
Utrzymywanie zarobków pracowników w przedziale wartości, w zależności od stanowiska.
 

CREATE TRIGGER Sprawdzenie_zarobków
BEFORE INSERT OR UPDATE OF Zarobki, Stanowisko
ON Pracownicy
FOR EACH ROW
DECLARE
  minimum NUMBER;
  maksimum NUMBER;
BEGIN
  /* Znalezienie zakresu zarobków na danym stanowisku */
  SELECT w.Min_zar, w.Mak_zar INTO minimum, maksimum
  FROM Widełki w
  WHERE w.Stanowisko = :NEW.Stanowisko;
  /* Jeśli zarobki spoza zakresu, generuj błąd */
  IF :NEW.Zarobki < Minimum OR :NEW.Zarobki > Maksimum THEN
    Raise_application_error(-20500,'Zarobki=' ||:NEW.Zarobki|| 'spoza zakresu');
  END IF;
END; /* Koniec wyzwalacza */
/
2. Do realizacji stałych czynności wymaganych po wykonaniu instrukcji (po zmianie wiersza w tabeli) używamy wyzwalacza typu AFTER. W wyzwalaczu mamy do dyspozycji informację o rodzaju instrukcji, która odpaliła dany wyzwalacz. Do tego celu służą systemowe zmienne logiczne: INSERTING, DELETING i UPDATING.

Przykład
 
Zajmiemy się aktualizacją budżetu działu. Zmiany, które mają wpływ na budżet działu, są związane ze zwalnianiem i zatrudnianiem pracowników, zmianą ich zatrudnienia w dziale i zmianą ich zarobków – są to wszystko zmiany w tabeli Pracownicy.
 

CREATE TRIGGER Budżet_działu
AFTER DELETE OR INSERT OR UPDATE OF Zarobki, Num_działu
ON Pracownicy
FOR EACH ROW
BEGIN 
  /* Zakładamy, że Zarobki, Num_działu nie są NULL */
  IF DELETING OR (UPDATING AND :OLD.Num_działu <> :NEW.Num_działu) THEN
    UPDATE Budżet
    SET Fund_płac = Fund_płac - :OLD.Zarobki
    WHERE Id_działu = :OLD.Num_działu;
  END IF;
  IF INSERTING OR (UPDATING AND :OLD.Num_działu <> :NEW.Num_działu) THEN
    UPDATE Budżet
    SET Fund_płac = Fund_płac + :NEW.Zarobki
    WHERE Id_działu = :NEW.Num_działu;
  END IF;
  IF UPDATING AND (:OLD.Num_działu = :NEW.Num_działu) AND (:OLD.Zarobki <> :NEW.Zarobki) THEN
    UPDATE Budżet
    SET Fund_płac=Fund_płac-:OLD.Zarobki+:NEW.Zarobki
    WHERE Id_działu = :OLD.Num_działu;
  END IF;
END;
/
3. Do wykonywania obliczeń, które trzeba zrealizować przed wstawieniem lub aktualizacją nowego wiersza (jak generowanie jednoznacznych identyfikatorów czy wyliczanie wartości dla pewnych pól), używamy wyzwalaczy typu BEFORE.

Przykład
 
Generowanie jednoznacznego identyfikatora, wstawianego do jednego z pól przy wykonywaniu instrukcji INSERT. 
 

CREATE TRIGGER GenerujIdPrac
BEFORE INSERT ON Pracownicy
FOR EACH ROW
BEGIN
  SELECT NVL(MAX(p.Id_prac)+1,1)
  INTO :NEW.Id_prac
  FROM Pracownicy p;
END;
/
Do wygenerowania kolejnego jednoznacznego identyfikatora pracownika, zamiast NVL(MAX(p.Id_prac)+1,1) można użyć sekwencji, np. Prac_seq.nextval. Mianowicie, należałoby zastąpić powyższą instrukcję SELECT instrukcją:
SELECT Prac_seq.nextval
INTO :NEW.Id_prac
FROM Dual;
Sięganie do wartości w tabeli wyzwalacza może być zabronione w pewnych przypadkach przez konkretny system zarządzania bazą danych!
 

4. Utrzymywanie reguł odmiennych od przyjętych w deklaratywnych więzach spójności referencyjnych jest realizowane za pomocą wyzwalaczy typu AFTER.

Przykład
 
Realizacja więzów spójności referencyjnej typu "ustaw NULL" dla UPDATE i DELETE.
 

CREATE TRIGGER Ustaw_null_dept
AFTER DELETE OR UPDATE OF Deptno ON Dept
FOR EACH ROW
BEGIN
  IF UPDATING AND :OLD.Deptno <> :NEW.Deptno OR DELETING THEN
    UPDATE Emp
    SET Emp.Deptno = NULL
    WHERE Emp.Deptno = :OLD.Deptno;
  END IF;
END;
Za pomocą wyzwalaczy można także zaprogramować dopasowany do sytuacji system zabezpieczeń przy wykonywaniu instrukcji – niedopuszczania do ich wykonania, gdy pewne warunki, jak np. aktualna godzina czy dzień tygodnia, nie są poprawne dla danej akcji. Można również zaprogramować system kontroli polegający na sporządzaniu śladu, co który użytkownik robił w bazie danych. Można również zaprogramować mechanizm propagowania wprowadzanych zmian do odległych baz danych.

Istnieje kilka naturalnych ograniczeń na to, co może być wykonane w ramach akcji wyzwalacza. Po pierwsze, w  wyzwalaczach nie wolno używać instrukcji COMMIT ani ROLLBACK.

Załóżmy, że mamy do czynienia z tabelą i wyzwalaczem określonym dla niej. Tabela zmieniana (ang. mutating) to albo tabela wyzwalacza albo tabela, w której klucz obcy odwołujący się do tabeli wyzwalacza został utworzony z opcją ON DELETE CASCADE lub ON DELETE SET NULL, czyli tabela potencjalnie zmieniana razem z tabelą wyzwalacza.

Reguła

W wyzwalaczu wierszowym nie wolno odczytywać ani zmieniać wartości w tabeli zmienianej z wyjątkiem wstawiania pojedynczego wiersza za pomocą instrukcji

INSERT INTO ... VALUES ...
A więc przy wykonywaniu instrukcji UPDATE na tabeli Emp, w wyzwalaczu związanym z tą tabelą nie można sięgać do żadnej wartości zapisanej w tej tabeli, np. aby obliczyć średnie zarobki wśród wszystkich pracowników i aby wpisać je jako nowe zarobki pracownika. Do wartości zapisanych w tabeli zmienianej można sięgać w wyzwalaczu określanym na poziomie instrukcji. Jeśli więc więzy spójności, które ma sprawdzać wyzwalacz, dotyczą całej tabeli, trzeba te operacje wykonywać w wyzwalaczu dla całej instrukcji – ewentualnie zapisując, w wyzwalaczu wierszowym, wymagane wartości do pomocniczych zmiennych, dla każdego zmienianego wiersza. Pomocnicze zmienne muszą być zadeklarowane w pakiecie – jest to jedyna droga przekazywania wartości między wyzwalaczami oprócz wykorzystania pomocniczej tabeli w bazie danych.
 

Wyzwalacze typu INSTEAD OF

Dla perspektywy mamy możliwość zdefiniowania specjalnego rodzaju wyzwalacza
 
CREATE [OR REPLACE] TRIGGER nazwa_wyzwalacza
INSTEAD OF specyfikacja_instrukcji
ON perspektywa
blok_PL/SQL
 
gdzie specyfikacja_instrukcji jest ciągiem do trzech nazw instrukcji INSERT, DELETE i UPDATE połączonych spójnikiem OR.

Wyzwalacz ten jest odpalany zamiast podanej w definicji instrukcji. Daje to możliwość pełnej realizacji postulatu, aby zmiany w bazie danych można było dokonywać z poziomu użytkowego – czyli perspektyw.

Przykład

Rozważmy perspektywę będącą złączeniem trzech tabel.

CREATE VIEW Manager_info AS
SELECT e.Ename, e.Empno, d.Dname, d.Deptno, p.Level, p.Projno
FROM Emp e, Dept d, Project p
WHERE e.Empno = d.Mgr_no
      AND d.Deptno = p.Resp_dept;
Następujący wyzwalacz pozwala wstawiać wiersze poprzez tę perspektywę, co w efekcie powoduje wstawienie informacji do trzech tabel. Wyzwalacz ten jest odpalany i zastępuje podaną w definicji instrukcję – w tym przypadku INSERT.
CREATE TRIGGER Manager_info_insert
INSTEAD OF INSERT ON Manager_info
FOR EACH ROW
DECLARE
  p NUMBER;
BEGIN 
  SELECT Count(*) INTO p FROM Emp 
  WHERE Emp.empno = :NEW.Empno;
  IF p=0 THEN
    INSERT INTO Emp VALUES(:NEW.Empno, :NEW.Ename);
  ELSE
    UPDATE Emp SET Emp.Ename = :NEW.Ename
    WHERE Emp.Empno = :NEW.Empno;
  END IF;
  SELECT Count(*) INTO p FROM Dept 
  WHERE Dept.Deptno = :NEW.Deptno;
  IF p=0 THEN
     INSERT INTO Dept VALUES(:NEW.Deptno, :NEW.Dname);
  ELSE

 
Wyzwalacze systemowe

Wyzwalacze systemowe mają składnię:
 
CREATE [OR REPLACE] TRIGGER nazwa_wyzwalacza
[BEFORE|AFTER|INSTEAD OF][zdarzenie_bazodanowe|zdarzenie_DDL
ON [DATABASE|SCHEMA] 
blok_PL/SQL
 
Przy czym:

  1. zdarzenie_bazodanowe to: SERVERERROR, LOGON, LOGOFF, STARTUP lub SHUTDOWN,
  2. zdarzenie_DDL to nazwa instrukcji DDL a więc na przykład: CREATE, ALTER, DROP, GRANT, REVOKE. 

Zdarzenia można łączyć ze sobą w jednym wyzwalaczu systemowym za pomocą słowa kluczowego OR.
 

Przykład
 
Rozważmy wyzwalacz, który rejestruje w dzienniku wszystkie pojawiające się błędy serwera bazy danych. Dla błędu nr 1017 oznaczającego "niepoprawne logowanie" jest przewidziana specjalna obsługa.
 

CREATE TRIGGER Log_errors AFTER SERVERERROR ON DATABASE 
BEGIN
  IF (IS_SERVERERROR (1017)) THEN
      <wykonaj specjalną obsługę w tym przypadku>
  ELSE
     <zapisz w dzienniku informację o błędzie>
  END IF;
END;
Przykład
 
Rozważmy wyzwalacz, który odnotowuje fakt wykonywania na koncie użytkownika instrukcji tworzenia nowego obiektu w bazie danych.
 
CREATE TRIGGER Audit_db_object AFTER CREATE ON SCHEMA
<zapisz w dzienniku informację o utworzeniu obiektu>
Szczególne znaczenie ma wyzwalacz dla zdarzenia logowania się użytkownika. Umożliwia on sprawdzenie, skąd loguje się użytkownik a także umożliwia utworzenie dla danego użytkownika odpowiedniego kontekstu w postaci zbioru wartości atrybutów – do użycia przez aplikację. Oracle wprowadza nawet w tym celu specjalny rodzaj obiektu w bazie danych o nazwie kontekst, z którego procedurę wywołuje wyzwalacz przy logowaniu się użytkownika.

Operacje na wyzwalaczach

Wyzwalacz bazy danych może być włączany i wyłączany za pomocą instrukcji:
 
ALTER TRIGGER wyzwalacz {ENABLE|DISABLE};

Wyzwalacz może zostać usunięty za pomocą instrukcji:
 
DROP TRIGGER wyzwalacz;

Informacje o wyzwalaczach znajdują się w perspektywie słownika danych User_triggers. Aby je obejrzeć, piszemy (nazwę wyzwalacza dużymi literami):
 
SELECT u.Trigger_type, u.Table_name, u.Triggering_event
FROM User_triggers u
WHERE u.Trigger_name = 'WYZWALACZ';
 
lub
 
SELECT u.Trigger_body
FROM User_triggers u
WHERE u.Trigger_name = 'WYZWALACZ';

 


Podsumowanie

W wykładach 4, 5 i 6 zostały przedstawione podstawowe konstrukcje wchodzące w skład aplikacji po stronie serwera:

1. deklaratywne więzy spójności;

2. język proceduralny będący rozszerzeniem języka SQL;

3. procedury, funkcje, pakiety;

4. wyzwalacze bazy danych.

Z tego tylko punkt 1 należy do Standardu SQL’92. Natomiast pozostałe elementy 2-4 należą do kolejnej wersji Standardu SQL:1999.
 


Słownik pojęć

procedura – obiekt bazy danych złożony z nagłówka i bloku kodu PL/SQL. Jest możliwe wykonanie tego bloku kodu przez serwer bazy danych korzystając z instrukcji SQL CALL.

funkcja – obiekt bazy danych złożony z nagłówka i bloku PL/SQL. Jest możliwe wykonanie tego bloku kodu przez serwer bazy danych z przekazaniem wartości wywołania w miejsce, gdzie funkcja została użyta.

pakiet – kolekcja zmiennych, stałych, kursorów, wyjątków, funkcji i procedur traktowana jako całość. Pakiet składa się z dwóch części: specyfikacji i implementacji.

wyzwalacz bazy danych – obiekt bazy danych w postaci nagłówka i bloku PL/SQL, który jest przypisywany określonemu obiektowi i zdarzeniu zachodzącemu dla tego obiektu lub zdarzeniu zachodzącemu w bazie danych (jak np. logowanie się użytkownika). Kod jest wykonywany przy każdym wystąpieniu tego zdarzenia.

wyzwalacz tabelowy - wyzwalacz wiązany z tabelą i ze zdarzeniem wykonywania instrukcji INSERT, DELETE lub  UPDATE.

wyzwalacz INSTEAD OF - wyzwalacz wiązany z perspektywą i ze zdarzeniem wykonywania instrukcji INSERT, DELETE lub  UPDATE.

wyzwalacz systemowy - wyzwalacz wiązany ze zdarzeniem zachodzącym w bazie danych.
 


Zadania

Problem 1

W hotelu jest potrzebna baza danych. Mają się w niej znaleźć informacje o gościach, pokojach podzielonych na kategorie (zaproponuj je), rezerwacjach (zamówionych – na rodzaj pokoju i przydzielonych – na konkretny pokój). Opierając się na tych informacjach powinno dać się:
 
  • dokonywać rezerwacji przypisując wolny pokój zgodny z oczekiwaniami gościa – jeśli nie można spełnić oczekiwań gościa zapisując zamówienie rezerwacji do rozstrzygnięcia przez upoważnionych pracowników;
  • sprawdzać ile razy dana osoba była gościem w ostatnich dwóch latach;
  • o godz. 23 sprawdzać, którzy goście przybyli do hotelu zgodnie z rezerwacją;
  • zwalniać wolny pokój i wypisywać rachunek.

1. Zaprojektuj schemat bazy danych w trzeciej postaci normalnej (np. zob. wykład 5 z RBD).

2. Zaprogramuj jako procedurę PL/SQL następujące zadanie:
 
Dokonaj rezerwacji pokoju dla VIP (bardzo ważnego gościa) na jedną noc. Jeśli nie ma wolnego pokoju, spełniającego oczekiwania gościa, wśród osób, które dokonały rezerwacji na pokój spełniający specyfikację VIP, wybierz jedną z nich – nie mającą statusu VIP i zamień jej rezerwację na rezerwację dla VIP. Skasowaną rezerwację na pokój zamień na zamówioną rezerwację. 
 

3. Przygotuj pakiet funkcji i procedur realizujących następujące zadania:
 
· wpisz nowego gościa;
· wypisz wszystkie pokoje, które nie są jeszcze zarezerwowane na okres czasu od daty d1 do daty d2;
· dokonaj rezerwacji zgodnie z życzeniami gościa (gdy nie ma wolnego pokoju, wpisz do zamówień);
· zapisz, że gość zgłosił się do hotelu;
· usuń rezerwację dokonaną przez gościa;
· przygotuj rachunek dla gościa (przyznając mu rabat, jeśli jest częstym gościem);
· wyznacz osoby, które dokonały rezerwacji, a jeszcze nie zgłosiły się danego dnia.
· dokonaj rezerwacji pokoju dla VIP na okres czasu od daty d1 do daty d2
 

4. Napisz procedurę, która:
 
· dla każdego gościa hotelowego: oblicza ile razy i przez jaki okres czasu przebywal w hotelu i wstawia te informacje do pustej tabeli:
        Podsum(Id_goscia, Imie, Nazwisko, Ile_razy, Jak_dlugo)
· usuwa wszystkie rezerwacje dotyczące pobytów starszych niż sprzed pięciu lat

 

Problem 2

W zakładzie fryzjerskim jest potrzebna baza danych. Mają się w niej znaleźć informacje o klientach, usługach, rezerwacjach, pracownikach i ich dyżurach. Opierając się na tych informacjach będzie można:
 
 
1. dokonywać rezerwacji klienta na zamawiane usługi do konkretnego fryzjera;
2. sprawdzać ile razy dana osoba była klientem w ostatnich dwóch latach, ewentualnie przyznając jej rabat;
3. wypisywać rachunek za wykonane usługi;
4. planować obsadę pracowniczą na następny tydzień;
5. znajdować zastępstwo w przypadku choroby;
6. obliczać należną pracownikowi pensję (opierając się na wykonanych usługach).

 
1. Zaprojektuj schemat bazy danych w trzeciej postaci normalnej.

2. Zaprogramuj jako procedurę PL/SQL następujące zadanie:
 
Jeden z fryzjerów jest chory. Dla każdej przypisanej do niego usługi należy znaleźć zastępcę – w pierwszej kolejności biorąc pod uwagę fryzjerów, którzy tego dnia mają wyznaczony dyżur.

Zadania na wyzwalacze

1. W hotelowej bazie danych (Problem 1) zastąp deklaratywne więzy  spójności referencyjnej przez więzy definiowane za pomocą wyzwalaczy.

2. Dla hotelowej bazy danych (Problem 1) napisz wyzwalacze, które dla każdej rezerwacji sprawdzają:
a. czy data początku pobytu jest wcześniejsza niż data końca pobytu;
b. aktualizują wartości ile_razy i jak_dlugo w tabeli Podsum.
Uwzględnij wszystkie trzy operacje INSERT, DELETE i UPDATE!

3. We fryzjerskiej bazie danych (Problem 2) zastąp deklaratywne więzy  spójności referencyjnej przez więzy definiowane za pomocą wyzwalaczy.
 



Strona przygotowana przez Lecha Banachowskiego. Ostatnia aktualizacja - 01/24/05.