Bazy Danych, laboratorium, 2010.11.29

Plan zajęć:

  • ćwiczenia dotyczące procedur i funkcji,
  • dodanie wyzwalaczy kontrolujących poprawność danych do bazy biblioteki. przypomnienie składni PL/SQL

Notatki:

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.

Ć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 > 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 > :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 > 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');
Tomasz Waleń
Tomasz Waleń
Assistant Professor