Laboratorium 6: kursory, wyzwalacze, procedury

Przydatne informacje:

sprawdzanie błędów kompilacji: show errors procedure nazwaProcedury (bez średnika na końcu)
informacje o zdefiniowanych procedurach/funkcjach/wyzwalaczach są przechowywane w tabelach USER_PROCEDURES, USER_TRIGGERS.

Tabele

Deklaracje

DECLARE
  TYPE t_tab IS TABLE OF VARCHAR(20) INDEX BY BINARY_INTEGER;
  tab t_tab;

Operacje na tabelach:

tab(i)           --i-ty element
tab.EXISTS(i)    --czy i-ty jest określony
tab.COUNT        --liczba elementów
tab.FIRST        --pierwszy element tabeli
tab.LAST         --ostatni element tabeli
tab.PRIOR(i)     --poprzednik i-tego
tab.NEXT(i)      --nastepnik i-tego
tab.DELETE       --usuń zawartość tabeli
tab.DELETE(i)    --usuń i-ty element
tab.DELETE(i,j)  --usuń elementy od i do j

Kursory służące do zmiany danych

DECLARE
  CURSOR nazwaKursora
      IS zapytanieSQL
  FOR UPDATE OF lista_kolumn;

Różni się od zwykłego kursora zakładaniem blokady na modyfikowane pola.

Przykład:

DECLARE
  CURSOR c_ksiazki IS SELECT * FROM ksiazki FOR UPDATE OF tytul;
BEGIN
  FOR k IN c_ksiazki LOOP
    dbms_output.put_line('ksiazka '||k.nr_ew);
    UPDATE ksiazki SET tytul=tytul||'_a' WHERE nr_ew=k.nr_ew;
  END LOOP;
END;
/

Procedury

PROCEDURE nazwa [(parametr 1[, parametr 2, ...])] IS
   deklaracje zmiennych
BEGIN
  treść procedury
[EXCEPTION
   obsauga wyjątków]
END [nazwa];

Denicja parametry ma następującą postać:

nazwa parametru [IN | OUT [NOCOPY] | IN OUT [NOCOPY]] typ
    [{:= | DEFAULT} wyrażenie]

IN - przekazanie parametru przez wartość,
OUT - parametr musi być zmienn¡, zachowuje się jak niezainicjalizowana
zmienna (pocz¡tkowo przechowuje pust¡ wartość),
IN OUT - parametr musi być zmienn¡, zachowuje si¦ jak zainicjalizowana
zmienna,
NOCOPY - przekazywanie parametrów przez referencje.

Komunikaty o błędach kompilacji można zobaczyć wykonuj¡c polecenie:

SHOW errors procedure nazwa;

Aby wywołać procedurę z poziomu sqlplusa, należy wykonać polecenie:

execute nazwaProcedury(wartość1,wartość2);
execute nazwaProcedury(nazwa1 => wartość1,nazwa2 => wartość2);
execute nazwaProcedury; /* jeśli procedura nie ma parametrów */

W niektórych przypadkach można najpierw zadeklarować funkcję, a dopiero
później podać jej treść, np.

PROCEDURE nazwa ( .. definicja parametrów .. );

Zgłaszanie błędów, działanie programów można przerwać za pomoc¡ funkcji
raise_application_error, np.

RAISE_APPLICATION_ERROR(kod,komunikat);
RAISE_APPLICATION_ERROR(-20000,'bardzo ważny błąd');

Kody od -20999..-20000 są zarezerwowane dla użytkowników.

Funkcje

FUNCTION nazwa [(parametr 1,[ parametr 2, ...])] RETURN typ IS
   [deklaracje zmiennych]
BEGIN
   treść
[EXCEPTION
   obsługa wyjątków]
END [nazwa];

W treści funkcji należy użyć polecenia RETURN, które zwraca wynik i kończy
działanie funkcji (dokładnie tak jak w C/C++).

Komunikaty o błędach kompilacji można zobaczyć wykonując polecenie:

SHOW errors FUNCTION nazwa;

Informacje o procedurach i funkcjach można otrzymać za pomoc¡ poleceń:

DESCRIBE PROCEDURE nazwa
DESCRIBE FUNCTION nazwa

Pakiety

CREATE PACKAGE nazwa AS
   PROCEDURE procedura1 (...);
   PROCEDURE precedura2;
   ...
END emp_actions;
 
CREATE PACKAGE BODY nazwa AS
   PROCEDURE procedura1 (...) IS
   BEGIN
   END procedura1;
 
   PROCEDURE procedura2 IS
   BEGIN
   END procedura2;
   ...
END nazwa;

Wyzwalacze

Wyzwalacze to fragmenty programów wykonywane w przypadku zajęcia określonych operacji na bazie danych. Wyzwalacze s¡ wykonywane automatycznie przez bazie danych.

Do czego mogą służyć wyzwalacze:

kontroli poprawno±ci danych,
logowania zmian na bazie,
sprawdzania uprawnień do wykonywania poszczególnych operacji.

CREATE [OR REPLACE] TRIGGER nazwa
    {BEFORE|AFTER} {INSERT|DELETE|UPDATE} ON nazwa tabeli
    [REFERENCING [NEW AS nazwa_nowego_wiersza] [OLD AS nazwa_starego_wiersza]]
    [FOR EACH ROW [WHEN (warunek)]]
    treść wyzwalacza

Wyzwalacze mogą być uruchamiane:

jednokrotnie dla każdego wiersza (klauzula FOR EACH ROW),
jednokrotnie dla każdego polecenia,

Kolejność wykonywania wyzwalaczy:

przed instrukcj¡,
przed pierwszym operowanym wierszem
po pierwszym wierszu ...
przed ostatnim wierszem
po ostatnim wierszu
po instrukcji

W wyzwalaczach nie wolno używać operacji związanych z transakcjami (COM-MIT, ROLLBACK).
Po zdefiniowaniu wyzwalacza, baza może stwierdzić, że kompilacja się nie powiodła. Komunikaty o błędach można zobaczyć wykonuj¡c polecenie:

show errors trigger nazwaWyzwalacza;

Inne polecenia dotycz¡ce wyzwalaczy:

select trigger_name from user_triggers - lista zdeniowanych wyzwalaczy,
select trigger_type, triggering_event, table_name, referencing_names,
trigger_body from user_triggers where trigger_name = 'bazwa' - dokaadne informacje konkretnym wyzwalaczu,
drop trigger nazwaWyzwalacza - usuwanie,
alter trigger nazwaWyzwalacza {disable|enable} - wyłączenie/włączenie wyzwalacza.

Odwołania do wartości zmienianych wierszy:

:OLD.nazwa_pola - wartości przed zmian¡/usunięciem,
:NEW.nazwa_pola - wartości po zmianie/wstawieniu,

Można definiować jeden wyzwalacz dla wielu zdarzeń, np.

CREATE OR REPLACE TRIGGER TestowyWyzwalacz
    BEFOR INSERT OR UPDATE OR DELETE ON tabela
    FOR EACH ROW
BEGIN
  IF INSERTING THEN
    ...
  ELSIF DELETING THEN
    ...
  ELSIF UPDATING THEN
    ...
  END IF;
END;

SQL - różności

Widoki:

CREATE VIEW nazwa AS zapytanie_sql;

Sekwencje:

CREATE SEQUENCE nazwa
[   INCREMENT BY wartość ]
[   START WITH wartość ]
;
SELECT nazwa_sekwencji.NEXTVAL FROM dual; /* zwraca wartość + zwiększa licznik */
SELECT nazwa_sekwencji.CURRVAL FROM dual; /* zwraca wartość + bez zwiększania licznika */

Ćwiczenie. Napisz procedurę wypisującą ostatnie 10 wypożyczeń książek.

Rozwiązanie

CREATE OR REPLACE PROCEDURE PokazWypozyczenia IS
  CURSOR c_w IS
  SELECT w.*,k.tytul,c.imie,c.nazwisko 
    FROM wypozyczenia w LEFT JOIN czytelnicy c ON (w.pesel=c.pesel)
     LEFT JOIN ksiazki k ON (w.nr_ew=k.nr_ew)
    ORDER BY w.data_wypozyczenia DESC,w.pesel ASC,w.nr_ew ASC;
BEGIN
  FOR tmp IN c_w LOOP
    EXIT WHEN c_w%ROWCOUNT = 11;
    dbms_output.put_line('data: '||TO_CHAR(tmp.data_wypozyczenia,'YYYY-MM-DD HH24:MI')||
  ' nr_ew: '||tmp.nr_ew||' ('||tmp.tytul||') pesel: '||TRIM(tmp.pesel)||
  ' ('||tmp.imie||' '||tmp.nazwisko||')');
  END LOOP;   
END;
/
-- aby uruchomić procedurę napisz: execute PokazWypozyczenia

Ćwiczenie. Napisz funkcję, która otrzymuje jako parametr numer ewidencyjny książki i zwraca ile razy była ona wypożyczona.

Rozwiązanie

CREATE OR REPLACE FUNCTION LiczbaWypozyczen (p_nr_ew IN NUMBER) 
  RETURN NUMBER IS
  ile INT;
BEGIN
  SELECT COUNT(*) INTO ile FROM wypozyczenia WHERE nr_ew=p_nr_ew; 
  RETURN ile;
END;
/
-- aby uruchomić procedurę napisz: select nr_ew,LiczbaWypozyczen(nr_ew) FROM ksiazki

Ćwiczenie. Sprawdź jakie procedury i funkcje są zdefiniowane w twojej bazie danych.

Rozwiązanie

SELECT OBJECT_NAME,PROCEDURE_NAME FROM USER_PROCEDURES;

Ćwiczenie. Usuń wybraną procedurę lub funkcję (np. procedurę PokazWypozyczenia).

Rozwiązanie

DROP PROCEDURE POKAZWYPOZYCZENIA;
DROP FUNCTION LiczbaWypozyczen;

Ćwiczenie. Dodaj wyzwalacz do tabeli wypożyczenia, który będzie blokował wypożyczanie książek, które jeszcze nie zostały zwrócone.

Rozwiązanie

CREATE OR REPLACE TRIGGER sprawdzWypozyczenia 
BEFORE INSERT ON wypozyczenia 
FOR EACH ROW
DECLARE
  czy_wypozyczona INT;
BEGIN
  SELECT COUNT(*) INTO czy_wypozyczona FROM wypozyczenia WHERE nr_ew=:NEW.nr_ew AND data_zwrotu IS NULL;
  IF czy_wypozyczona &gt; 0 THEN
      raise_application_error(-20000,'Książka jest jeszcze wypożyczona');
  END IF;
END;
/
DELETE FROM wypozyczenia WHERE nr_ew=2;
INSERT INTO wypozyczenia VALUES (2,'001','01-OCT-10',NULL); 
INSERT INTO wypozyczenia VALUES (2,'001','20-OCT-10','19-OCT-10');

Ćwiczenie. Usuń wyzwalacz SprawdzWypozyczenia z tabeli wypożyczenia.

Rozwiązanie

DROP TRIGGER SprawdzWypozyczenia;

Ćwiczenie. Dodaj wyzwalacz do tabeli wypożyczenia, który będzie uniemożliwiał wprowadzanie wypożyczeń z datą zwrotu mniejszą niż data wypożyczenia.

Rozwiązanie

CREATE OR REPLACE TRIGGER sprawdzWypozyczenia 
BEFORE INSERT OR UPDATE ON wypozyczenia 
FOR EACH ROW
BEGIN
    IF :NEW.data_wypozyczenia IS NOT NULL AND :NEW.data_zwrotu IS NOT NULL THEN
      IF :NEW.data_wypozyczenia &gt; :NEW.data_zwrotu THEN
        raise_application_error(-20000,'Data zwrotu musi być większa niż data wypożyczenia');
      END IF;
    END IF;
END;
/
INSERT INTO wypozyczenia VALUES (1,'001','01-OCT-10','02-OCT-10'); 
INSERT INTO wypozyczenia VALUES (1,'001','20-OCT-10','19-OCT-10');

Ćwiczenie. Dodaj wyzwalacz do tabeli wypożyczenia, który będzie uniemożliwiał wypożyczanie więcej niż 10 książek jednocześnie dla jednej osoby.

Rozwiązanie

CREATE OR REPLACE TRIGGER sprawdzWypozyczenia 
BEFORE INSERT ON wypozyczenia  
FOR EACH ROW
DECLARE
  ile INT;
BEGIN
  SELECT COUNT(*) INTO ile FROM wypozyczenia WHERE pesel=:NEW.pesel AND data_zwrotu IS NULL;
  IF :NEW.data_zwrotu IS NULL THEN
    ile:=ile+1;
  END IF;
  IF ile &gt; 10 THEN 
    raise_application_error(-20001,'Przekroczono limit wypożyczeń na jedną osobę');
  END IF;
END;
/

Ćwiczenie. Dodaj wyzwalacz do tabeli książki, który będzie automatycznie nadawał kolejne numery ewidencyjne.

Rozwiązanie (działa poprawnie tylko gdy dodajemy pojedyncze wiersze):

CREATE OR REPLACE TRIGGER dodajKsiazke
BEFORE INSERT ON ksiazki
FOR EACH ROW
BEGIN
  IF :NEW.nr_ew IS NULL THEN
    SELECT NVL(MAX(nr_ew),0)+1 INTO :NEW.nr_ew FROM ksiazki;
  END IF;
END;
/
INSERT INTO ksiazki (autor,tytul) VALUES ('aaa1','ttt1');
INSERT INTO ksiazki (autor,tytul) VALUES ('aaa2','ttt2');