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.