Bazy danych

EWSIE

24.10.2010

Tworzenie i przeglądanie perspektyw

Aby móc ponownie skorzystać z tworzonych przez nas instrukcji SQL, szczególnie tych

bardzo długich i skomplikowanych, bez potrzeby ponownego ich wpisywania, tworzymy

perspektywy. Uważa się je za zapisane zapytania, które pozwalają na utworzenie obiektów bazy

danych działających podobnie do tabel, ale posiadających zawartość, która zmienia się dynamicznie

i dotyczy bezpośrednio tylko wybranych zgodnie z definicją wierszy. Perspektywy oferują dużą

elastyczność od tych dotyczących prostych zapytań do pojedynczej tabeli, aż do zapytań

skomplikowanych, dotyczących wielu tabel.

Tworzenie perspektyw

Polecenie CREATE VIEW pozwala na tworzenie widoków, czyli logicznych okien

zawierających widok na jedną lub więcej tabel. Widoki są obiektami podobnymi do formularzy,

gdyż nie przechowują danych, i do tabel, gdyż możliwe jest użycie tych samych poleceń SQL, które

można stosować do tabel (SELECT, UPDATE, DELETE, INSERT).

Dla wykonania określonych instrukcji SQL niezbędne jest spełnienie określonych wymagań.

Dotyczy to w szczególności polecenia SELECT, użytego dla tworzenia widoku i mogącego

zawierać klauzulę GROUP BY i DISTINCT.

Widoki są używane w celu zwiększenia poziomu bezpieczeństwa tablic poprzez ograniczenie

dostępu tylko do wybranych kolumn tabeli podstawowej oraz pozwalają ukryć przed

użytkownikiem złożoność danych.

Wadą widoków stosowanych w Postgresql jest niemożność dodawania i aktualizacji danych

poprzez te struktury. Ograniczenie to może sprawić nieco kłopotu programistom i użytkownikom

wykorzystującym tę cechę w innych systemach bazodanowych.

Składnia:

CREATE VIEW nazwa_prespektywy

AS zapytanie;

Gdzie zapytanie jest kompletnym zapytaniem SQL (w postaci instrukcji SELECT), które definiuje

zawartość perspektywy.

1

Bazy danych

EWSIE

24.10.2010

Przykład 1

Interesują nas informacje dotyczące liczby pracowników. Wyniki, które nas interesują, można

uzyskać na kilka sposobów. Najprościej jest zdefiniować następujące zapytanie:

SELECT count(*) FROM pracownik;

UWAGA: symbol gwiazdki (*) w powyższym zapytaniu oznacza, że interesują nas wszystkie

wiersze. Zapytanie oblicza całkowitą liczbę zwracanych wierszy, a tym samym liczbę wszystkich

pracowników.

Zwiększając złożoność tego zapytania, można dodać klauzulę JOIN, aby powiązać informacje

z tabeli pracownik z informacjami z tabeli zespol, w celu uzyskania nazwy zespolu, w którym

każdy z pracowników jest zatrudniony. Dodatkowo można dodać do zapytania klauzulę GROUP

BY, aby pogrupować pracowników według nazw zespołów.

UWAGA: dzięki grupowaniu według nazw zespołów funkcja COUNT() obliczy liczbę wierszy dla

zagregowanego wiersza (w tym przypadku dla unikalnej nazwy zespołu). Wreszcie można dodać

funkcję MIN( ) dla kolumny placa_pod w tabeli pracownik, aby uzyskać informację o najniższej

płacy podstawowej, którą zarabia pracownik w każdym z zespołów wraz z liczbą zatrudnionych w

nim pracowników.

SELECT count(*) as Liczba, min(placa_pod) as min, nazwa

FROM pracownik as p INNER JOIN zespol as z ON p.id_zesp = z.id_zesp

GOUP BY z.nazwa

ORDER BY Liczba DESC;

Częste wpisywanie powyższego już nie prostego zapytania może nastręczać kłopotów, dlatego

tworzymy perspektywę dla tego samego zapytania.

CREATE VIEW pracownik_zespol

AS SELECT count(*) as Liczba, min(placa_pod) as min, nazwa

FROM pracownik as p INNER JOIN zespol as z ON p.id_zesp = z.id_zesp

GOUP BY z.nazwa

ORDER BY Liczba DESC;

2

Bazy danych

EWSIE

24.10.2010

Zastosowanie perspektyw

Podstawową różnicą w działaniu perspektyw w stosunku do tabel jest fakt, że zamiast

wpisywania długiego zapytania wystarcza prosta instrukcja SELECT, np.

1. SELECT * FROM pracownik_zespol;

2. SELECT * FROM pracownik_zespol ORDER BY min DESC LIMIT 3;

Przykład 2 pokazuje ponadto, że wyniki perspektywy można posortować inaczej niż zrobiono to w

definicji perspektywy.

Każda próba użycia instrukcji DELETE lub UPDATE w odniesieniu do perspektywy powoduje

błąd, ponieważ perspektywa sama w sobie nie zawiera danych. Pomimo funkcjonalnego

podobieństwa do tabeli, perspektywa to jedynie „okno” do innego zbioru i sama w sobie nie jest

modyfikowalnym zbiorem danych.

Usuwanie perspektyw

Składnia:

DROP VIEW nazwa_perspektywy;

Usunięcie perspektywy nie ma wpływu na dane, które ta perspektywa wykorzystuje. Perspektywy

istnieją jedynie jako środek do obserwowania danych w innych tabelach i można je bezpiecznie

usunąć bez utraty danych (chociaż utracimy oczywiście zapytanie opisane przez perspektywę).

3