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:
- notatki z ćwiczeń dt. PL/SQL'a: część 1, część 2,
- ważniak.mimuw.edu.pl, ćwiczenia dt. PL/SQL'a: część 1 część 2 część 3
- notatki dt. triggerów
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');