Zaawansowane programowanie w T SQL


Zaawansowane
programowanie w T-SQL
Dr inż. Izabela Rojek
Plan
Składnie T-SQL
Instrukcje sterujące
Kursory
Składnie specjalne
Obsługa błędów
Indeksy
Podsumowanie
Transact-SQL (T-SQL)
to implementacja języka SQL w systemie Microsoft SQL
Server.
Język ten nieco różni się od standardu, ale większość
podstawowych składni jest taka, jak w standardzie.
Język T-SQL stale ewoluuje.
Właściwie z języka zapytań stał się językiem
programowania baz danych.
Programiści tworzący oprogramowanie mogą się łatwo
nauczyć języka T-SQL dzięki istniejącym analogiom
z tradycyjnymi językami programowania strukturalnego.
Instrukcje sterujące
IF...ELSE daje możliwość warunkowego
wykonywania bloków kodu.
DECLARE @zmienna int
SET @zmienna = 100
IF @zmienna > 100
PRINT 'Zmienna jest większa niż 100
ELSE
BEGIN
PRINT 'Zmienna jest mniejsza niż 100'
SET @zmienna = 0 -- zerowanie zmiennej
END
Instrukcje sterujące
W T-SQL istnieje także składnia
CASE...END, która działa analogicznie do
składni IF, jednak działa w zapytaniach
typu SELECT na poziomie pojedynczego
wiersza
(umożliwia sprawdzanie wartości kolumny
w każdym rekordzie i wplatanie instrukcji
sterującej w zapytanie wyszukujące dane).
CASE...END
Powyższa składnia wybiera dane z tabel Person.Contact (nazwisko i
imię pracownika) oraz HumanResources.Employee (płeć - jeśli w
kolumnie Gender składnia CASE napotka wartość 'M', to zamienia ją w
zestawie wynikowym na 'male' - mężczyzna, jeśli napotka inną wartość,
wypisze 'female' - kobieta).
Instrukcje sterujące
Kolejna składnia sterująca to pętla WHILE.
Należy pamiętać, że pętla WHILE może być wykonywana w
nieskończoność, jeśli programista nie zapewni wyjścia z pętli.
Kursory
Kursor to zestawy rekordów umieszczane w
pamięci i przechowujące wyniki zapytań typu
SELECT.
Umożliwiają zaawansowane formatowanie
wyników wyszukiwania danych i przetwarzanie
rekordów jeden po drugim
(ale w ściśle określonej kolejności
determinowanej przez wynik zapytania, które
stanowi definicję kursora).
Kursory
Składnie specjalne
Nowoczesne języki SQL obfitują w specjalne
składnie, które znacznie rozszerzają
funkcjonalność.
Przykładem takiej składni może być PIVOT.
Składnia ta umożliwia stworzenie tabeli
wynikowej z zapytania SELECT, w której na
nagłówkach wierszy i kolumn znajdują się
wartości z tabel zródłowych.
Instrukcja PIVOT
Powyższa składnia wybiera z
tabeli
Purchasing.PurchaseOrderH
eader informacje: ile
zamówień na produkty
producentów identyfikowanych
przez kolumnę VendorID
(ograniczono VendorID do
wartości mniejszych od 6)
zrealizowali pracownicy
identyfikowani przez wartości
164 i 198 w kolumnie
EmployeeID.
Instrukcja PIVOT
Składnia PIVOT to odpowiednik kwerendy krzyżowej z
programu Microsoft Access.
Dokonuje ona zamiany - wartości z rekordów stają się
nagłówkami kolumn i wierszy, natomiast w wierszach
pojawiają się agregacje - w powyższym przykładzie
funkcja agregująca COUNT liczy ilość zrealizowanych
przez danego pracownika zamówień.
Istnieje też instrukcja UNPIVOT, która działa odwrotnie -
pozwala przejść od zagregowanych wartości do
pojedynczych rekordów (ale oczywiście wyniki nie będą
takie same, jak przed wykonaniem składni PIVOT).
Składnie
specjalne -
Powyższy wynik
obrazuje możliwości tej
składni umożliwiając
zbudowanie tabeli
wyświetlającej ilość
zamówień u wybranych
producentów
dokonanych przez
pięciu pracowników
(każdy pracownik o
określonym
EmployeeID).
Obsługa błędów
W trakcie działania kodu SQL mogą wydarzyć
się nieprzewidziane błędy.
Błędy te mogą wynikać z różnych przyczyn.
Mogą to być błędy wynikające z narzuconych w
bazie danych ograniczeń lub na przykład błędy
wynikające z prób wykonania niedozwolonych
operacji.
Wykrycie tych błędów i odpowiednia na nie
reakcja to zadanie dla programisty baz danych.
Gdzie wykrywać błędy?
Błędy wykrywamy najczęściej w:
transakcjach - po wykryciu błędu wycofujemy transakcję,
procedurach składowanych - wykrywamy błędy powstałe
głównie w wyniku niepoprawnych wartości parametrów
podanych przez użytkownika,
triggerach - podobnie jak w transakcjach, po napotkaniu
błędu wycofywana jest transakcja wywołująca trigger,
blokach kodu SQL - wszelkie rozbudowane bloki kodu
wymagają wykrywania błędów.
Metody wykrywania błędów
Jak można wykrywać błędy? Metod na to jest wiele.
Po pierwsze można zastosować składnie sterujące, np.
IF...ELSE, do sprawdzania wartości zmiennych jeszcze
przed wystąpieniem błędu.
Druga metoda to wykorzystanie istniejących w SZBD
funkcji wykrywających błędy.
W systemie Microsoft SQL Server taką funkcją jest
@@ERROR, która zwraca numer ostatnio napotkanego
błędu w bieżącej sesji.
Metody wykrywania błędów
Aktualnie istnieją także inne lepsze metody wykrywania i obsługi
błędów.
Chodzi o strukturalną obsługę wyjątków. Jako wyjątek
rozumiemy błąd, który wymaga obsługi.
Poniższy fragment kodu obrazuje przykładową obsługę
wyjątków przy pomocy składni TRY...CATCH (TRY - próbuj,
CATCH - przechwyć i obsłuż).
Obsługa błędów
Tworzenie tabeli archiwizującej informacje o błędach
Powyższy kod tworzy tabelę ErrorLog, która będzie zawierała
informacje o występujących błędach. W kolumnie ErrorNumber
zapisany zostanie numer błędu, w kolumnie ErrorMessage -
komunikat błędu, zaś w kolumnie ErrorDate - data i godzina
wystąpienia błędu (wartość domyślna - bieżąca data i czas - jest
generowana przy pomocy funkcji systemowej GETDATE).
Przechwytywanie błędów
Powyższy kod ilustruje działanie składni TRY...CATCH. W bloku
TRY umieszczamy składnie, które mogą spowodować wystąpienie
błędów, zaś blok CATCH zapisuje obsługę błędów (w tym
przypadku zapisanie informacji o błędzie do tabeli ErrorLog).
Informacje na temat błędu są uzyskiwane przy użyciu funkcji
ERROR_NUMBER (numer błędu) i ERROR_MESSAGE
(komunikat błędu).
Przeglądanie informacji o wystąpieniu błędów
Indeksy
Indeksy są specjalnym mechanizmem bazy
danych, który pozwala na przyspieszenie
wyszukiwania danych.
Jest to niewątpliwie bardzo ważny parametr
charakteryzujący bazę danych. Szczególnie w
bazach zawierających wiele tysięcy rekordów
mechanizm indeksów jest niezbędny do
zapewnienia rozsądnych czasów wyszukiwania
danych
Dostęp fizyczny do danych
Zrozumienie mechanizmu dostępu do danych
zapisanych w bazie danych jest bardzo istotny
dla zrozumienia zasad działania indeksów.

Jak wiadomo, dane w bazach danych w sposób
trwały są zapisywane na dyskach optycznych,
magnetycznych lub rodzinach nośników o
dostępie bezpośrednim takich jak macierze
RAID.
Dostęp fizyczny do danych
SZBD najczęściej nie zajmuje
się fizyczną obsługą dysków.
W procesie dostępu do danych
biorą udział: menedżer plików
i menedżer dysku.
Menedżer plików ma
odpowiednią wiedzę o
strukturze systemu plików i jest
odpowiedzialny za odszukanie
odpowiedniego pliku.
Menedżer dysku ma natomiast
niezbędną wiedzę na temat
fizycznej organizacji dysku i
jest odpowiedzialny za
odnalezienie odpowiedniej
strony danych.
Optymalizacja dostępu
Zasadniczy czas dostępu do danych bazy to czas
odczytu tych danych z dysków.
W celu optymalizacji tego dostępu stosuje się metodę
zwaną klastrowaniem.
Klastrowanie polega na dążeniu do utrzymania blisko
siebie na dysku rekordów, które są logicznie powiązane.
Taka organizacja danych znacznie przyspiesza dostęp
do danych. Aby odczytać dane powiązane (i w związku
z tym zapewne razem wykorzystywane i odczytywane),
głowica nie musi wykonywać dużych ruchów, a tym
samym maleją czasy wyszukiwania).
Klastrowanie
Rozróżniamy przy tym dwa rodzaje klastrowania:
klastrowanie wewnątrzplikowe,
klastrowanie międzyplikowe.
Klastrowanie wewnątrzplikowe polega na grupowaniu
rekordów obok siebie wewnątrz jednego pliku.
Klastrowanie międzyplikowe polega na umieszczaniu na
stronie obok siebie rekordów pochodzących z więcej niż
jednego pliku (tabeli).
Optymalizacja dostępu do danych sprowadza się w
zasadzie do odpowiedniego zarządzania stronami i
decydowania w jaki sposób dane mają być klastrowane.
Strony danych
Wszystkie dane z tabel relacyjnej bazy danych
są przechowywane na stronach danych (ang.
page) na dysku.
Na przykład w serwerze MS SQL strona danych
ma wielkość 8 KB. Strony tworzą większe
struktury zwane stertami (ang. heap).
Sterta jest kolekcją stron danych. W każdej
tabeli dane są przechowywane w kolekcji 8
kilobajowych stron.
8 ciągłych stron tworzy zakres (ang. extent).
Strony danych
Wiersze danych nie są na stronach przechowywane w
szczególnej kolejności, a strony danych nie są
połączone w listę.
Jeśli podczas wstawiania danych do strony, strona ulega
przepełnieniu, to wtedy następuje podział takiej strony.
Ponieważ jeden wiersz tabeli musi zmieścić się na
stronie danych, więc w serwerze MS SQL występuje
ograniczenie co do wielkości wiersza i wynosi ono 8060
bajtów (ponieważ na stronie zapisywane są jeszcze
metadane -informacje o obiekcie, którego dane
przechowuje strona itd., dane nie zajmują dokładnie
pełnych 8 KB).
Organizacja danych w serwerze MS SQL
Indeksy i ich zastosowanie
Zastanowimy się teraz nad problemem wyszukiwania danych
w tabeli. Na przykład załóżmy, że w tabeli Studenci chcemy
znalezć studenta o nazwisku Nowak.
Wiersze zapisane są w bazie w kolejności ich wpisywania i nie
są w szczególny sposób sortowane. Co robi wobec tego
system, kiedy wydajemy polecenie odnalezienia rekordu
zawierającego informacje o Nowaku, np.
Indeksy i ich zastosowanie
SELECT imie, nazwisko FROM Studenci
WHERE Nazwisko = 'Nowak'
System musi przeszukać całą tabelę (skanowanie
wszystkich stron danych zawierających dane z tabeli) i
przejrzeć wszystkie rekordy tej tabeli, aby mieć pewność,
że odnalazł rekordy zawierające nazwiska Nowak.
Operacja taka jest oczywiście czasochłonna.
Podobnie jest, gdy w książce poszukujemy jakiegoś hasła
(na przykład w podręczniku do baz danych szukamy
informacji o indeksach). Aby znalezć szukaną
informację, powinniśmy przeczytać całą książkę. Na
szczęście niektóre książki są wyposażone na końcu w
specjalne zestawienie haseł - czyli w indeks haseł.
Nasze postępowanie przebiega wówczas następująco:
Odszukujemy poszukiwane hasło w indeksie, które jest
uporządkowany alfabetycznie (co znacznie ułatwia nam
odnalezienie hasła).
Odczytujemy w indeksie numer strony na której hasło to
występuje w książce.
Otwieramy książkę na odpowiedniej stronie.
Przeglądamy stronę w poszukiwaniu naszego hasła.
Odczytujemy informacje związane z szukanym hasłem.
Idea działania indeksów w bazie danych jest dokładnie
taka sama.
Indeks określony na atrybucie A relacji jest
mechanizmem, który pozwala na efektywne
wyszukiwanie krotek o ustalonej wartości składowej
atrybutu A. Jak więc wyglądałby indeks dla tabeli
Studenci?
Indeks określany jest dla konkretnego pola. Mówimy
wówczas, że pole to jest polem indeksowanym. W
wypadku tabeli Studenci możemy jako pole indeksowane
wybrać pole nazwisko. Wówczas założenie indeksu na
tym polu będzie oznaczało założenie przez system
dodatkowej tabeli, w której umieszczone zostaną
nazwiska studentów z tabeli Studenci oraz dostarczy
przesłankę, gdzie należy szukać (na której stronie
danych) pełnej informacji o danym studencie.
Dodatkowo rekordy w tabeli indeksu zostaną
posortowane w kolejności alfabetycznej nazwisk.
Poszukiwanie naszego studenta Nowaka będzie
teraz przebiegać znacznie szybciej.
Działanie i rola indeksów polega głównie na
przyspieszeniu wyszukiwania rekordów w bazie
danych. Niestety obciążają one dodatkowo
system w czasie aktualizacji lub wstawiania
danych. SZBD musi bowiem oprócz
umieszczenia rekordu w bazie dokonać też
wpisu w tabeli indeksu oraz ponownie
posortować rekordy tabeli indeksu.
Zalety i wady stosowania indeksów
Niektóre z pól warto jest indeksować, inne
natomiast nie powinny być nigdy indeksowane.
Warto indeksować następujące pola:
klucze podstawowe i obce (często są automatycznie indeksowane),
pola, po których często następuje wyszukiwanie,
pola, do których dostęp następuje w ustalonej, uporządkowanej
kolejności.
Nie należy indeksować:
pól, do których rzadko odwołują się zapytania,
pól, które zawierają tylko kilka wartości unikatowych,
pól zawierających dane typu image, bit czy obiekt OLE.

Indeksy mogą być zakładane na jednym polu lub na większej liczbie
pól jednocześnie.
Dostęp do danych przy korzystaniu z indeksów
Dostęp do danych z wykorzystaniem indeksów nie zawsze jest
najlepszym rozwiązaniem. Czasem lepiej jest nie korzystać z
indeksów, ale bezpośrednio skanować tabelę. SZBD sprawdza,
czy dla danej tabeli istnieją indeksy, a następnie optymalizator
zapytań analizuje zapytanie i podejmuje decyzję, czy dostęp do
danych będzie prowadzony poprzez skanowanie tabeli, czy przy
wykorzystaniu indeksu.
Operacje na indeksach
Indeksy są tworzone najczęściej automatycznie dla kluczy
głównych oraz dla pól, dla którym ma być wymuszona
unikatowość.

Indeksy można też tworzyć dla innych pól korzystając z
polecenia języka SQL CREATE INDEX.

Poniżej podano przykład utworzenia indeksu grupowanego dla
tabeli memeber dla pola lastname.
Przykład utworzenia indeksu przy użyciu języka SQL
Operacje na indeksach
Indeksy mogą wymuszać unikatowość pól lub nie. Przykład
deklaracja indeksu wymuszającego unikatowość pola title_no
podano poniżej. Po założeniu takiego indeksu dodanie do tabeli
rekordu o istniejącej już wartości pola title_no nie będzie
możliwe.
Operacje na indeksach
Indeks może też być zakładany na kilku polach jednocześnie.
Poniższy przykład pokazuje sposób definiowania unikalnego
indeksu na polach isbn i copy_no jednocześnie.
Założenie takiego indeksu spowoduje, że będzie można
wprowadzić do tabeli rekord o istniejącej już wartości pola isbn
oraz rekord o istniejącej wartości pola copy_no. Nie będzie
natomiast możliwe wprowadzenie rekordu, w którym para
wartości isbn i copy_no będzie się powtarzać.
Indeksy
Indeksy stanowią ważny mechanizm przyspieszający
dostęp do danych zapisanych w bazie i wymuszający
unikatowość danych.
Z drugiej jednak strony obciążają dodatkowo system w
czasie wykonywania operacji aktualizacji i wstawiania
danych.
Dlatego też indeksy należy stosować z rozwagą
starannie planując, gdzie jakiego rodzaju indeksy
założyć.
Decyzje o zakładaniu indeksów wynikają często ze
specyfiki użytkowania bazy (np. kryteriów wyszukiwania)
i często są podejmowane w czasie pierwszego okresu
użytkowania bazy - okresu dostrajania systemu.


Wyszukiwarka