Obsługa biura rachunkowego
Sprawozdanie nr VII
Widoki, procedury, triggery
Widoki
Widoki to inaczej nazwane select'y. Mają one za zadanie ułatwić wykonywanie najczęstszych zapytań w bazie danych. Aby przeglądanie mojej bazy danych było wygodniejsze i szybsze postanowiłem stworzyć widoki odpowiedzialne za:
wyświetlenie wszystkich klientów wraz z okresami, w których byli zatrudnieni oraz informacjami o firmie, której byli pracownikami:
CREATE VIEW inf_klient AS
SELECT k.id_k AS identyfikator_klienta,imie, nazwisko, NIP, data_ur, wojewodztwo, k.miasto, k.ulica, k.nr_domu, k.kod_pocztowy, data_przyjecia, data_zwolnienia, nazwa AS nazwa_f, f.miasto AS miasto_f, f.ulica AS ulica_f,f.nr_domu AS nr_domu_f, f.kod_pocztowy AS kod_pocztowy_f
FROM dba.klienci AS k KEY JOIN dba.okresy_zatrud AS o KEY JOIN dba.firmy AS f;
wyświetlenie wszystkich dostarczonych przez klientów dokumentów wraz z danymi od kogo i przez kogo zostały przyjęte:
CREATE VIEW inf_dok_dost AS
SELECT k.id_k AS identyfikator_klienta,k.imie, k.nazwisko, NIP, nazwa AS nazwa_dokumentu, opis AS opis_dokumentu, data AS data_dostarczenia, p.id_p AS identyfikator_przyjmujacego
FROM dba.klienci AS k KEY JOIN dba.dokumenty_dost AS d KEY JOIN dba.pracownicy AS p;
wyświetlenie wszystkich wystawionych dokumentów wraz z informacjami dla kogo i przez kogo były wystawione:
CREATE VIEW inf_dok_wyst AS
SELECT DISTINCT d.id_dok_w,k.id_k AS identyfikator_klienta,k.imie, k.nazwisko, NIP, nazwa AS nazwa_dokumentu, opis AS opis_dokumentu, d.data AS data_wystawienia, p.id_p AS identyfikator_wydajacego
FROM dba.klienci AS k KEY JOIN dba.dane KEY JOIN dba.dokumenty_wyst AS d KEY JOIN dba.pracownicy AS p KEY JOIN dba.rodzaj_dok;
wyświetlenie jakiego typu są wystawione dokumenty wraz z informacjami kogo dotyczą:
CREATE VIEW rodzaj_dok AS
SELECT k.id_k,id_dok_w AS identyfikator_dokumentu, nazwa, opis, d.data
FROM dba.klienci KEY JOIN dba.dane KEY JOIN dba.dokumenty_wyst AS d KEY JOIN dba.PITy;
Procedury
Do mojej bazy dodałem również zestaw procedur, które powinny umożliwić szybkie odnalezienie pożądanych danych. Oto ich zestawienie:
procedura wyświetlająca informacje o konkretnym kliencie ( zakładam, że znalezienie konkretnego klienta będzie opierało się na znajomości jego identyfikatora).
CREATE PROCEDURE inf_klient( IN id_kl INTEGER)
BEGIN
SELECT imie, nazwisko, NIP, data_ur, wojewodztwo, k.miasto, k.ulica, k.nr_domu, k.kod_pocztowy, data_przyjecia, data_zwolnienia, nazwa AS nazwa_f, f.miasto AS miasto_f, f.ulica AS ulica_f,f.nr_domu AS nr_domu_f, f.kod_pocztowy AS kod_pocztowy_f
FROM dba.klienci AS k KEY JOIN dba.okresy_zatrud AS o KEY JOIN dba.firmy AS f
WHERE k.id_k=id_kl;
END;
Procedura wyświetlająca informacje o dokumencie dostarczonym do firmy przez klienta, wyszukiwanym (od kogo pochodzi, kiedy został dostarczony i przez kogo został odebrany)
CREATE PROCEDURE inf_dok_dost (IN id_dokum INTEGER)
BEGIN
SELECT k.id_k AS identyfikator_klienta,k.imie, k.nazwisko, NIP, nazwa AS nazwa_dokumentu, opis AS opis_dokumentu, data AS data_dostarczenia, p.id_p AS identyfikator_przyjmujacego
FROM dba.klienci AS k KEY JOIN dba.dokumenty_dost AS d KEY JOIN dba.pracownicy AS p
WHERE d.id_dok = id_dokum;
END;
Procedura wyświetlająca dane o konkretnym dokumencie wystawionym (dla kogo został wystawniony, rodzaj dokumentu, date wystawienia, kto wystawił dokument)
CREATE PROCEDURE inf_dok_wyst (IN id_dokum INTEGER)
BEGIN
SELECT DISTINCT d.id_dok_w,k.id_k AS identyfikator_klienta,k.imie, k.nazwisko, NIP, nazwa AS nazwa_dokumentu, opis AS opis_dokumentu, d.data AS data_wystawienia, p.id_p AS identyfikator_wydajacego
FROM dba.klienci AS k KEY JOIN dba.dane KEY JOIN dba.dokumenty_wyst AS d KEY JOIN dba.pracownicy AS p KEY JOIN dba.rodzaj_dok
WHERE d.id_dok_w= id_dokum;
END;
4. Procedura wyświetlająca dane klienta wpisane do konkretnego formularza.
CREATE PROCEDURE dane_klienta (IN id_kl INTEGER, IN dokum_wykon INTEGER)
BEGIN
SELECT k.id_k, nazwa, nr_kom, pp.opis, wartosc
FROM dba.klienci AS k KEY JOIN dba.dane KEY JOIN dba.dokumenty_wyst AS d KEY JOIN dba.PITy KEY JOIN dba.pola_pitow AS pp
WHERE k.id_k=id_kl AND d.id_dok_w=dokum_wykon;
END;
Triggery
Triggery służą do sprawdzenia poprawności wpisywanych danych. Dla mnie najważniejsze było sprawdzenie czy, aby przez pomyłkę nie dopisano do bazy klienta, który już się w niej znajduje. Przy tym trzeba zaznaczyć, że jedna fizyczna osoba w bazie danych może być wpisana kilkukrotnie, bo zmieniła np. Adres zamieszkania. W takiej sytuacji nie można zmienić danych w bazie, ponieważ wprowadziłoby to zamęt i mogło doprowadzić do upadku firmy.
CREATE TRIGGER spr_klient BEFORE UPDATE, INSERT
ORDER 2
ON dba.klienci
REFERENCING NEW AS n_kl
FOR EACH ROW
BEGIN
DECLARE istnieje_klient EXCEPTION FOR sqlstate value '99900';
IF EXISTS(
SELECT * FROM dba.klienci
WHERE imie=n_kl.imie AND nazwisko=n_kl.nazwisko AND NIP=n_kl.NIP AND data_ur=n_kl.data_ur AND miasto=n_kl.miasto AND ulica=n_kl.ulica AND nr_domu=n_kl.nr_domu
)
THEN
SIGNAL istnieje_klient;
END IF;
END;
Trigger uruchamiany jest automatycznie przy próbie dodania lub edycji wiersza w tabeli klienci. Jeśli napotka on wiersz z takimi samymi jak wprowadzane dane to zwróci błąd i uniemożliwi wprowadzenie zmian.
Przydzielamy prawa dostępu do stworzonych procedur dla odpowiednich kont:
GRANT EXECUTE
ON dba.inf_klient
TO gl_ksiegowy
GRANT EXECUTE
ON dba.inf_dok_dost
TO gl_ksiegowy
GRANT EXECUTE
ON dba.inf_dok_wyst
TO gl_ksiegowy
GRANT EXECUTE
ON dba.dane_klienta
TO gl_ksiegowy
GRANT EXECUTE
ON dba.inf_klient
TO ksiegowi
GRANT EXECUTE
ON dba.dane_klienta
TO ksiegowi
GRANT EXECUTE
ON dba.inf_dok_wyst
TO ksiegowi
GRANT EXECUTE
ON dba.inf_dok_dost
TO ksiegowi
Księgowi oraz główny księgowy posiadają prawa SELECT do wszystkich tabel w bazie, dlatego swobodnie możemy nadać im prawa do procedur ułatwiających im przeglądanie tych danych. Informatycy nie mają uprawnień do przeglądania zawartości bazy danych, więc tak samo jak dla konta Właściciel nie przydzielamy im praw do wykonywania procedur.