ITA-101 Bazy Danych
Włodzimierz Dąbrowski, Przemysław Kowalczuk, Konrad Markowski
Moduł 8 Wersja 1.0
Programowanie zaawansowane w T-SQL
Spis treści
Programowanie zaawansowane w T-SQL ... 1
Informacje o module ... 2
Przygotowanie teoretyczne ... 3
Przykładowy problem ... 3
Podstawy teoretyczne... 3
Przykładowe rozwiązanie ... 6
Porady praktyczne ... 6
Uwagi dla studenta ... 7
Dodatkowe źródła informacji... 7
Laboratorium podstawowe ... 8
Problem 1 (czas realizacji 30 minut) ... 8
Problem 2 (czas realizacji 15 minut) ... 10
Laboratorium rozszerzone ... 12
Zadanie 1 (czas realizacji 90 min) ... 12
Informacje o module
Opis modułu
Programowanie w języku zapytań to ważna umiejętność. Powinni ją opanować zarówno programiści, jak i administratorzy. Różne dialekty języka SQL oferują różne składnie, jednak reguły, jakimi powinien kierować się tworzący kod, są te same niezależnie od systemu zarządzania bazami danych. W module tym znajdziesz informację na temat zaawansowanego programowania w T-SQL. Dowiesz się w jaki sposób korzystać z instrukcji sterujących, kursorów oraz jak funkcjonuje obsługa błędów.
Cel modułu
Celem modułu jest zapoznanie czytelnika z zaawansowanymi elementami języka T-SQL, takimi jak instrukcje sterujące, kursory, obsługa błędów itp.
Uzyskane kompetencje
Po zrealizowaniu modułu będziesz:
• potrafił użyć instrukcji sterujących w języku T-SQL
• rozumiał sposób działania i funkcjonowania kursorów
• wiedział, w jaki sposób działa obsługa błędów Wymagania wstępne
Przed przystąpieniem do pracy z tym modułem powinieneś:
• potrafić stworzyć bazę danych wraz z jej podstawowymi obiektami (patrz: moduł 3)
• potrafić napisać podstawowe instrukcje w języku T-SQL DCL i T-SQL DDL (patrz: moduł 5)
• potrafić pisać zapytania w języku T-SQL DML (patrz: moduł 6)
• rozumieć podstawową składnię języka T-SQL Mapa zależności modułu
Zgodnie z mapą zależności przedstawioną na rys. 1, przed przystąpieniem do realizacji tego modułu należy zapoznać się z materiałem zawartym w modułach 3, 5 i 6.
Rys. 1 Mapa zależności modułu
Przygotowanie teoretyczne
Przykładowy problem
Podczas wytwarzania bazy danych wiąże ze sobą grupę osób, do której należą projektant bazy danych, administrator systemu zarządzania bazą danych, administrator bazy danych oraz programista bazy danych. Bardzo ważne jest, żeby wszyscy biorący udział w wytwarzaniu systemu bazodanowego współdziałali ze sobą na różnych etapach. Najwięcej wspólnego z programowaniem w T-SQL ma programista bazy danych. Od niego w dużej mierze zależy, czy wytworzone funkcje, procedury składowane, wyzwalacze i inne elementy będą działały we właściwy sposób. To on jest odpowiedzialny za przygotowanie odpowiednich elementów programowych, które następnie będzie wykorzystywał projektant aplikacji i programista aplikacji dostępowej. Również od niego w pewnym stopniu zależy bezpieczeństwo danych gromadzonych w bazie. W związku z tym powinien on dysponować odpowiednią wiedzą i posiadać pewne umiejętności z zakresu zaawansowanego programowania w języku T-SQL. Znajomość tego języka oraz dobre nawyki wynikające z praktyki programowania są bardzo ważne. Podstawowymi składowymi języka T-SQL są instrukcje sterujące, kursory i obsługa błędów.
Podstawy teoretyczne
Język T-SQL stale ewoluuje. Z języka zapytań stał się właściwie językiem programowania baz danych. Programiści tworzący oprogramowanie mogą łatwo nauczyć się T-SQL dzięki istniejącym analogiom do tradycyjnych języków programowania proceduralnego. Jednymi z częściej używanych konstrukcji są instrukcje sterujące.
Instrukcje sterujące
Instrukcja sterująca IF…ELSE daje możliwość warunkowego wykonywania bloków kodu. W implementacji T-SQL wygląda ona jak poniżej:
IF warunek_logiczny
{ wyrazenie_sql | wyrazenie_blokowe } ELSE
{ wyrazenie_sql | wyrazenie_blokowe }
Należy zwrócić uwagę, że jeśli blok po słowie IF lub ELSE składa się z wielu linii kodu, musisz użyć składni BEGIN…END. Wówczas:
IF warunek_logiczny BEGIN
{ wyrazenie_sql | wyrazenie_blokowe } END
ELSE BEGIN
{ wyrazenie_sql | wyrazenie_blokowe } END
Poniższy kod przedstawia przykładowe użycie instrukcji sterującej IF…ELSE:
IF @zmienna > 100
PRINT 'Zmienna jest większa niż 100' ELSE
BEGIN
PRINT 'Zmienna jest mniejsza niż 100' SET @zmienna = 0 -- zerowanie zmiennej END
Kolejna instrukcja sterująca to pętla WHILE. W T-SQL wygląda ona następująco:
WHILE wyrazenie_logiczne
{ wyrazenie_sql | wyrazenie_blokowe }
[ BREAK ]
{ wyrazenie_sql | wyrazenie_blokowe } [ CONTINUE ]
{ wyrazenie_sql | wyrazenie_blokowe }
Wyrażenie logiczne powinno zwracać wartość TRUE lub FALSE. Podobnie jak w przypadku instrukcji sterującej IF…ELSE, jeśli wyrażenie SQL składa się z wielu linii kodu, musisz użyć właśnie składni BEGIN…END. W instrukcji WHILE występują dwie istotne instrukcje:
• BREAK – powoduje wyjście z pętli WHILE
• CONTINUE – powoduje rozpoczęcie kolejnej interacji pętli WHILE Poniższy kod przedstawia przykładowe użycie instrukcji sterującej WHILE:
DECLARE @zmienna int SET @zmienna = 0
WHILE @zmienna < 10 BEGIN
PRINT 'Iteracja nr ' + CAST(@zmienna AS varchar(2)) SET @zmienna = @zmienna + 1
END
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 obiekt, który umożliwia poruszanie się po wynikach zapytania rekord po rekordzie.
Umożliwia on przetwarzanie rekordów jeden po drugim, co daje możliwość zaawansowanego formatowania wyników wyszukiwania danych (ale w ściśle określonej kolejności, determinowanej przez wynik zapytania użytego w definicji kursora).
Deklaracja kursora w języku T-SQL wygląda następująco:
DECLARE nazwa_kursora CURSOR [ { LOCAL | GLOBAL } ] [ { FORWARD_ONLY | SCROLL } ]
[ { STATIC | KEYSET | DYNAMIC | FAST_FORWARD } ] [ { READ_ONLY | SCROLL_LOCKS | OPTIMISTIC } ] [ TYPE_WARNING ]
FOR wyrazenie_select
[ FOR UPDATE [ OF nazwa_kolumny [,…n] ] ] [;]
Poniższy kod przedstawia przykładowe użycie kursora: DECLARE Employee_Cursor CURSOR FOR SELECT LastName, FirstName
FROM Northwind.dbo.Employees WHERE LastName like 'B%' OPEN Employee_Cursor
FETCH NEXT FROM Employee_Cursor WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM Employee_Cursor END
CLOSE Employee_Cursor DEALLOCATE Employee_Cursor
Składnia specjalna
Nowoczesne dialekty języka SQL obfitują w specjalne elementy składniowe, które znacznie rozszerzają jego funkcjonalność. Przykładem takiej składni może być PIVOT. Składnia ta umożliwia stworzenie tabeli wynikowej z zapytania SELECT, w której w nagłówkach wierszy i kolumn znajdują się wartości z tabel źródłowych.
USE AdventureWorks GO
SELECT VendorID, [164] AS Emp1, [198] AS Emp2, [223] AS Emp3, [231] AS Emp4, [233] AS Emp5
FROM
(SELECT PurchaseOrderID, EmployeeID, VendorID FROM Purchasing.PurchaseOrderHeader) p
PIVOT (
COUNT (PurchaseOrderID) FOR EmployeeID IN
( [164], [198], [223], [231], [233] ) ) AS pvt
ORDER BY VendorID;
Zapytanie zamieszczony powyżej może przykładowo wygenerować następujący wynik:
VendorID Emp1 Emp2 Emp3 Emp4 Emp5
1 4 3 5 4 4
2 4 1 5 5 5
3 4 3 5 4 4
4 4 2 5 5 4
5 5 1 5 5 5
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 wykonywania kodu SQL mogą wystąpić nieprzewidziane błędy, wynikające z różnych przyczyn. Mogą to być błędy powstałe wskutek narzuconych w bazie danych ograniczeń lub na przykład błędy spowodowane próbami wykonania niedozwolonych operacji. Wykrycie tych błędów i odpowiednia reakcja na nie to zadanie dla programisty baz danych.
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
• wyzwalaczach – podobnie jak w transakcjach, po napotkaniu błędu wycofywana jest transakcja wywołująca wyzwalacz
• blokach kodu SQL – wszelkie rozbudowane bloki kodu wymagają wykrywania błędów
Metod pozwalających na wykrycie błędów jest wiele. Można zastosować instrukcje sterujące, np.
IF…ELSE, do sprawdzania wartości zmiennych, by w ten sposób zapobiegać wystąpieniu błędów.
Drugą metodą jest wykorzystanie istniejących w systemie zarządzania bazami danych funkcji pozwających wykrywać błędy. W systemie Microsoft SQL Server taką funkcją jest @@ERROR, która zwraca numer ostatnio napotkanego błędu w bieżącej sesji.
Aktualnie istnieją także inne, przez wielu uznawane za lepsze metody wykrywania i obsługi błędów.
Chodzi o strukturalną obsługę wyjątków. Przez wyjątek rozumiemy błąd, który wymaga obsługi.
Poniżej pokazano składnię TRY…CATCH (ang. try – próbuj, catch – przechwyć):
BEGIN TRY
{ wyrazenie_sql | wyrazenie_blokowe } END TRY
BEGIN CATCH
{ wyrazenie_sql | wyrazenie_blokowe } END CATCH
Jeśli wyrażenie SQL składa się z wielu linii kodu, podobnie jak w przypadku instrukcji sterujących IF…ELSE i WHILE musisz użyć składni BEGIN…END.
Przykładowe rozwiązanie
Przykładowe użycie instrukcji sterującej
Rozważmy przykład, w którym średnia cena produktu jest mniejsza od 100 zł. Jeżeli maksymalna cena jest większa od 300 zł, wówczas w pętli WHILE podwajamy ją. Pętla ta wykonuje się do momentu, gdy maksymalna cena jest większa od 300 zł. Po spełnieniu warunku wychodzimy z pętli i wyświetlamy komunikat dla użytkownika.
USE AdventureWorks;
GO
WHILE (SELECT AVG(ListPrice) FROM Production.Product) < 100 BEGIN
UPDATE Production.Product
SET ListPrice = ListPrice * 2
SELECT MAX(ListPrice) FROM Production.Product
IF (SELECT MAX(ListPrice) FROM Production.Product) > 300 BREAK
END
PRINT 'Wyświetlony komunikat po wyjściu z pętli';
Przykładowe użycie TRY…CATCH
Poniższy fragment kodu obrazuje przykładową obsługę wyjątków przy pomocy składni TRY…CATCH.
W celu zilustrowania obsługi błędów, wewnątrz bloku TRY dokonujemy dzielenia przez zero, które jest niewykonalne. Wówczas wywoływany jest wyjątek, który jest przechwytywany w bloku CATCH.
Po przechwyceniu wyjątku wyświetlany jest odpowiedni komunikat zdefiniowany przez programistę.
BEGIN TRY
-- generujemy błąd SELECT 1/0;
END TRY
BEGIN CATCH
-- obsługujemy błąd
RAISERROR('Nie dzielimy przez zero',16,1) END CATCH
Porady praktyczne
• Funkcja wykrywania błędów @@ERROR zwraca numer ostatniego błędu lub 0, jeśli wykonanie polecenia T-SQL zakończyło się powodzeniem. Na poniższym przykładzie pokazano użycie funkcji @@ERROR do sprawdzenia naruszenia ograniczenia, do którego może dojść w sytuacji modyfikacji danych:
UPDATE EmployeePayHistory SET PayFrequency = 4 WHERE EmployeeID = 1 IF @@ERROR = 547
PRINT 'naruszone ograniczenie.' GO
• Informacje na temat błędu mogą być uzyskiwane przy użyciu funkcji ERROR_NUMBER (numer błędu) i ERROR_MESSAGE (komunikat błędu).
• SQL Server udostępnia trzy rodzaje programowalnych obiektów: funkcje, procedury składowane i wyzwalacze. W celu poprawnego pisania elementów programowalnych należy znać zaawansowane elementy języka T-SQL. Zamiast wykonywania pojedynczego polecenia lub komendy, obiekty te umożliwiają tworzenie bogatej logiki programistycznej, która zawiera pętle, kontrolę przepływu, podejmowanie decyzji i rozwidlenia.
• Zdolność do uruchamiania transakcji i przeznaczenie ich do pozostawienia lub wycofania nie wystarczy do efektywnego radzenia sobie z problemem. W tym przypadku niezbędnym składnikiem jest zdolność do programowanego wykrywania i obsłużenia błędów.
• Wewnątrz bloku CATCH możemy ustalić co spowodowało błąd i przekazać informacje na jego temat używając systemu obsługi błędów języka T-SQL. Najczęściej używanymi funkcjami tego rodzaju są ERROR_NUMBER i ERROR_MESSAGE. Zwracają one numer i opis błędu.
• Używając funkcji ERROR_NUMBER i ERROR_MESSAGE w bloku instrukcji CATCH możemy zdecydować, czy potrzeba użyć ROLLBACK do cofnięcia transakcji.
Uwagi dla studenta
Jesteś przygotowany do realizacji laboratorium jeśli:
• rozumiesz, co oznacza instrukcja sterująca
• rozumiesz zasadę działania kursorów
• umiesz zdefiniować prostą pętlę
• umiesz podać przykład obsługi błędów
Pamiętaj o zapoznaniu się z uwagami i poradami zawartymi w tym module. Upewnij się, że rozumiesz omawiane w nich zagadnienia. Jeśli masz trudności ze zrozumieniem tematu zawartego w uwagach, przeczytaj ponownie informacje z tego rozdziału i zajrzyj do notatek z wykładów.
Dodatkowe źródła informacji
1. Kalen Delaney, Microsoft SQL Server 2005: Rozwiązania praktyczne krok po kroku, Microsoft Press, 2006
W książce autor w przystępny i zrozumiały sposób przedstawia podstawowe mechanizmy zaawansowanego programowania w języku T-SQL. Książka polecana szczególnie dla osób początkujących.
2. Edward Whalen, Microsoft SQL Server 2005 Administrator’s Companion, Microsoft Press, 2006 W książce autor pokazał zaawansowane mechanizmy programowania w języku T-SQL, które mogą zostać wykorzystane do programowania w bazach danych takich obiektów jak funkcje, procedury składowane, wyzwalacze i transakcje. Pozycja szczególnie polecana dla osób chcących poszerzyć swoją wiedzę na temat programowania baz danych.
3. Deren Bieniek, Randy Dyess, Mike Hotek, Javier Loria, Adam Machanic, Antonio Soto, Adolfo Wiernik, SQL Server 2005. Implementacja i obsługa, APN Promise, 2006
W książce tej, podobnie jak w powyższej pozycji, przedstawiono elementy zaawansowanego programowania w języku T-SQL. Pokazano praktyczne zastosowanie zaawansowanych mechanizmów T-SQL. Książka polecana dla osób początkujących, jak również chcących poszerzyć swoją wiedzę.
Laboratorium podstawowe
Problem 1 (czas realizacji 30 minut)
Jesteś administratorem w firmie National Insurance. Właśnie dowiedziałeś się od swojego szefa, że firma planuje rozszerzenie na skalę uczelnianą systemu prac dyplomowych, którym zarządzała dotąd na Twoim wydziale. Zadanie jakie przed Tobą stoi to stworzenie zaawansowanego mechanizmu wybierania danych z twojej bazy danych oraz przedstawienie tych danych w odpowiedniej formie.
Zadanie Tok postępowania
1. Nawiąż połączenie z SQL Server 2008
• Uruchom maszynę wirtualną BD2008.
— Jako nazwę użytkownika podaj Administrator.
— Jako hasło podaj P@ssw0rd.
Jeśli nie masz zdefiniowanej maszyny wirtualnej w Microsoft Virtual PC, dodaj nową maszynę używając wirtualnego dysku twardego z pliku D:\VirtualPC\Dydaktyka\BD2008.vhd.
• Kliknij Start. Z grupy programów Microsoft SQL Server 2008 uruchom SQL Server Management Studio.
• W oknie logowania kliknij Connect.
2. Przetestuj instrukcję sterującą CASE
• Z menu głównego wybierz File -> Open -> File.
• Odszukaj plik C:\Labs\Lab06\Case.sql.
• Zaznacz i uruchom kod ze skryptu Case.sql. Wynik działania pokazano na rys. 2.
USE PraceDyplomowe GO
SELECT
C.Nazwisko, C.Imie, CASE
WHEN E.ID_Ocena = '2' THEN 'trzy' ELSE 'inna'
END AS Gender FROM Ocena E INNER JOIN Osoba C
ON E.ID_Ocena = C.ID_Osoby GO
Rys. 2 Wynik działania skryptu z użyciem instrukcji CASE
3. Zastosuj stronicowanie danych
• Z menu głównego wybierz File -> Open -> File.
• Odszukaj pliku C:\Labs\Lab06\Stronicowanie.sql
• Zaznacz i uruchom kod ze skryptu Stronicowanie.sql. Wynik działania pokazano na rys. 3.
USE PraceDyplomowe GO
SELECT T.Nazwisko FROM (
SELECT Nazwisko,
ROW_NUMBER() OVER(ORDER BY ID_Stopnian) AS Pozytywna FROM Osoba
) AS T
WHERE T.Pozytywna BETWEEN 2 AND 6 GO
Rys. 3 Wynik stronicowania danych
Powyższe zapytanie stanowi rozwiązanie problemu stronicowania danych. W tym wypadku wynikiem jest lista osób z tabeli Osoby, przy czym wyświetlone zostają nazwiska osób, które z pracy otrzymały ocenę pomiędzy 3 a 5, biorąc pod uwagę sortowanie według kolumny ID_Stopnian. Zapytanie zadziała tak samo, nawet gdy numeracja w tej kolumnie nie będzie ciągła. Wykorzystuje ono podzapytanie, w którym dla każdego wiersza generowana jest liczba porządkowa za pomocą funkcji ROW_NUMBER (funkcja ta musi przed generowaniem wartości określić kryterium sortowania, stąd klauzula ORDER BY).
Problem 2 (czas realizacji 15 minut)
Kolejne zadanie związane z rozbudową systemu prac dyplomowych jakie przed Tobą stoi to oprogramowanie obsługi błędów (przechwytywanie i obsługa) z użyciem zaawansowanych mechanizmów, jakimi dysponuje SQL Server.
Zadanie Tok postępowania
1. Nawiąż połączenie z SQL Server 2008
• Uruchom maszynę wirtualną BD2008.
— Jako nazwę użytkownika podaj Administrator.
— Jako hasło podaj P@ssw0rd.
Jeśli nie masz zdefiniowanej maszyny wirtualnej w Microsoft Virtual PC, dodaj nową maszynę używając wirtualnego dysku twardego z pliku D:\VirtualPC\Dydaktyka\BD2008.vhd.
• Kliknij Start. Z grupy programów Microsoft SQL Server 2008 uruchom SQL Server Management Studio.
• W oknie logowania kliknij Connect.
2. Utwórz tabelę archiwizacji błędów
• Z menu głównego wybierz File -> Open -> File.
• Odszukaj pliku C:\Labs\Lab09\Errors.sql
• Zaznacz i uruchom kod ze skryptu Errors.sql, który tworzy tabele, w której będą zapisywane informacje o błędach
-- (1) Ustawiamy się na baze danych PraceDyplomowe USE PraceDyplomowe
GO
-- (2) Tworzymy tabele w ktorej bedzie przechowywana informacja o -- bledach
CREATE TABLE TabelaBledow (
ErrorID int IDENTITY(1,1) PRIMARY KEY, ErrorNumber int NOT NULL,
ErrorMessage nvarchar(200) NOT NULL, ErrorDate datetime DEFAULT GETDATE() )
GO
Powyższy kod tworzy tabelę TabelaBledow, 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).
3. Przechwytywa nie błędów
• Zaznacz i uruchom (F5) poniższy fragment kodu:
-- (3) Implementacja obslugi przechwytywania bledow BEGIN TRY
SELECT 1/0 END TRY BEGIN CATCH
INSERT TabelaBledow(ErrorNumber, ErrorMessage) SELECT ERROR_NUMBER(), ERROR_MESSAGE()
END CATCH GO
4. Przeglądanie informacji o wystąpieniu błędu
• Zaznacz i uruchom (F5) poniższy fragment kodu:
-- (4) Wyswietlenie zawartosci tabeli bledow SELECT * FROM TabelaBledow
GO
Wynik działania pokazano na rys. 4.
Rys. 4 Informacja o błędzie
Laboratorium rozszerzone
Zadanie 1 (czas realizacji 90 min)
Jesteś administratorem w firmie National Insurance. Właśnie dowiedziałeś się od swojego szefa, że firma zarządzająca bazą AdventureWorks planuje rozszerzenie i modernizacje systemu w celu spełnienia pewnych standardów. W związku z modernizacją systemu bazodanowego najprawdopodobniej ulegną zmianie pewne metody wybierania danych. Wydaje się zasadne stworzenie bardziej wyrafinowanych metod wybierania danych w oparciu o zaawansowane konstrukcje języka T-SQL. Prawdopodobnie również w związku z wejściem na wyższy poziom programowania w T-SQL będziesz musiał wprowadzić strukturalną obsługę błędów.
Zadanie jakie przed Toba stoi to:
1. Podjęcie decyzji, które zapytania w bazie danych pozostaną bez zmian, które zostaną zmodyfikowane i zastąpione czymś bardziej złożonym wykorzystując zaawansowane możliwości języka T-SQL, a które będą musiały zostać całkowicie usunięte.
2. Zaplanowanie struktury obsługi błędów dla zapytań istniejących w systemie bazodanowym.
