Zaawansowane programowanie w T

124
Bazy danych
Zaawansowane programowanie w TSQL
Bazy danych
125
Przegląd zagadnień
Skladnie T-SQL
Obsluga bledów
Podsumowanie
Laboratorium
Znajomość języka SQL, jakim posługuje się SZBD, jest bardzo waŜne,
jeśli chcesz wydajnie pracować z bazami danych. Przed przystąpieniem do tego
wykładu powinieneś znać podstawowe składnie SQL, takie jak SELECT czy
INSERT. Nie będziemy poświęcać im tu miejsca, a jedynie wplatać je w nieco
bardziej zaawansowane składnie.
126
Bazy danych
Składnie T-SQL
Instrukcje sterujace
Kursory
Skladnie specjalne
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.
Bazy danych
127
Instrukcje sterujące
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.
Jedymi z częściej uŜywanych składni są 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.
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
Zwróć uwagę, Ŝe rolę klamrowych nawiasów w T-SQL pełni blok
BEGIN...END. Jeśli blok po słowie IF lub ELSE składa się z wielu linii kodu,
musisz uŜyć właśnie składni BEGIN...END.
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).
Kolejna składnia sterująca to pętla WHILE. W T-SQL wygląda to następująco.
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 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
128
Bazy danych
w ściśle określonej kolejności determinowanej przez wynik zapytania, które
stanowi definicję 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ł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 ź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;
-- Przykładowy wynik
VendorID
Emp4
1
4
2
5
3
4
4
4
5
5
Emp1
Emp5
4
Emp2
Emp3
3
5
4
4
1
5
5
4
3
5
4
4
2
5
5
5
1
5
5
Bazy danych
129
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).
130
Bazy danych
Obsługa błędów
Gdzie wykrywac bledy?
Metody wykrywania bledó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.
Bazy danych
131
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 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ąpnieniem błędu. Druga metoda to wykorzystanie
istniejących w SZBD funkcje wykrywające błędy. W systemie Microsoft SQL
Server taką funkcją jest @@ERROR, która zwraca numer błędu napotkanego w
ostatnio napotkanego błędu w bieŜącej sesji.
Aktualnie istnieją takŜe inne - moŜna by rzec 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Ŝ).
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;
132
Bazy danych
Podsumowanie
Skladnie T-SQL
Obsluga bledów
Programowanie w języku zapytań to waŜna umiejętność. Powinni ją
opanować zarówno programiści, jak i administratorzy. RóŜne języki SQL
oferują róŜne składnie. Jednak reguły, jakimi powinien kierować się tworzący
kod, są te same nizaleŜnie od SZBD. Bardzo często opanowanie w
zaawansowanym stopniu składni jednego języka pozwala w przyszłości na
łatwe opanowanie innego.
Bazy danych
133
Laboratorium
KaŜdy producent SZBD w swoich systemach oferuje składnie, które
rozszerzają standard ANSI SQL. W tym ćwiczeniu poznasz trzy składnie, które
słuŜą do zaawansowanego wybierania danych i formatowania wyników zapytań
SELECT.
Obsługa błędów jest waŜnym aspektem programowania w kaŜdym języku takŜe w języku T-SQL. System Microsoft SQL Server 2005 oferuje strukturalną
obsługę wyjątków (błędów). W tym ćwiczeniu zobaczysz w działaniu składnię
TRY...CATCH, która słuŜy do przechwytywania i obsługi błędów.
134
Bazy danych
Krok 1 - Instrukcja sterująca CASE
►
►
►
►
►
►
►
Zaloguj się do maszyny wirtualnej ZBD jako uŜytkownik
Administrator z hasłem P@ssw0rd.
Kliknij Start. Z grupy programów Microsoft SQL Server 2005
uruchom SQL Server Management Studio.
W oknie logowania kliknij Connect.
Kliknij w menu głównym programu Management Studio na File.
Kliknij Open - File.
Odszukaj plik C:\Labs\Lab05\Queries.sql i kliknij Open.
Zaznacz kod, który wykonuje zapytanie SELECT z uŜyciem składni
CASE (patrz kod poniŜej).
USE AdventureWorks
GO
SELECT
C.LastName,
C.FirstName,
CASE
WHEN E.Gender = 'M' THEN 'male'
ELSE 'female'
END AS Gender
FROM HumanResources.Employee E
INNER JOIN Person.Contact C
ON E.ContactID = C.ContactID
►
Wciśnij F5, aby uruchomić zaznaczony fragment kodu.
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).
Składnia CASE jest instrukcją sterującą działającą na poziomie
pojedynczego wiersza w zapytaniach SELECT. MoŜna jej uŜyć do
zaawansowanego formatowania wyników zapytań SELECT.
Krok 2 - Instrukcja PIVOT
►
Zaznacz kod, który wykonuje zapytanie SELECT z opcją PIVOT
(patrz kod poniŜej).
Bazy danych
135
SELECT
VendorID
, [164] AS Emp1
, [198] AS Emp2
FROM
(SELECT
PurchaseOrderID,
EmployeeID,
VendorID
FROM Purchasing.PurchaseOrderHeader) p
PIVOT
(
COUNT (PurchaseOrderID)
FOR EmployeeID IN
( [164], [198])
) AS pvt
WHERE VendorID < 6
ORDER BY VendorID
►
Wciśnij F5, aby uruchomić zaznaczony fragment kodu.
PowyŜsza składnia wybiera z tabeli Purchasing.PurchaseOrderHeader
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.
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ń.
W Books Online znajdziesz takŜe opis składni 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).
Krok 3 - Stronicowane danych z uŜyciem funkcji rankingu
►
Zaznacz kod, który wykonuje zapytanie SELECT z uŜyciem funkcji
rankingu.
SELECT T.Name
FROM (
SELECT
Name,
ROW_NUMBER()
OVER(ORDER
DepartmentID) AS Number
FROM HumanResources.Department
) AS T
WHERE T.Number BETWEEN 3 AND 6
►
Wciśnij F5, aby uruchomić zaznaczony fragment kodu.
BY
136
Bazy danych
PowyŜsze zapytanie stanowi rozwiązanie problemu stronicowania danych.
W tym wypadku wynikiem jest lista nazw działów (departamentów) z tabeli
HumanResources.Department, przy czym wyświetlone zostają działy od
trzeciego do szóstego (cztery działy - trzeci, czwarty, piąty i szósty) biorąc pod
uwagę sortowanie według kolumny DepartmentID.
Składnia zadziała tak samo nawet, gdy numeracja w tej kolumnie nie będzie
ciągła. Składnia wykorzystuje 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 składnia ORDER BY).
Funkcje rankingu (nazywane tak ze względu na zastosowanie)
w systemie Microsoft SQL Server 2005: ROW_NUMBER, RANK,
DENSE_RANK, NTILE. Ich opis znajdziesz w Books Online.
Bazy danych
137
Obsługa błędów
Krok 1 - Tworzenie tabeli archiwizującej informacje o błędach
►
►
►
►
►
►
►
Zaloguj się do maszyny wirtualnej ZBD jako uŜytkownik
Administrator z hasłem P@ssw0rd.
Kliknij Start. Z grupy programów Microsoft SQL Server 2005
uruchom SQL Server Management Studio.
W oknie logowania kliknij Connect.
Kliknij w menu głównym programu Management Studio na File.
Kliknij Open - File.
Odszukaj plik C:\Labs\Lab05\Errors.sql i kliknij Open.
Zaznacz kod, który tworzy tabelę, w której będą zapisywane
informacje o błędach (patrz kod poniŜej).
USE AdventureWorks
GO
CREATE TABLE ErrorLog
(
ErrorID int IDENTITY(1,1) PRIMARY KEY,
ErrorNumber int NOT NULL,
ErrorMessage nvarchar(200) NOT NULL,
ErrorDate datetime DEFAULT GETDATE()
)
GO
►
Wciśnij F5, aby uruchomić zaznaczony fragment kodu.
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).
Krok 2 - Przechwytywanie błędów
►
Zaznacz kod, który implementuje obsługę błędów (patrz kod poniŜej).
BEGIN TRY
SELECT 1/0
END TRY
BEGIN CATCH
INSERT ErrorLog(ErrorNumber, ErrorMessage)
SELECT ERROR_NUMBER(), ERROR_MESSAGE()
END CATCH
►
Wciśnij F5, aby uruchomić zaznaczony fragment kodu.
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ś
138
Bazy danych
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).
Microsoft SQL Server 2005 dysponuje pokaźną listą funkcji
systemowych do uzyskiwania informacji na temat występujących
błędów. Poszukaj w Books Online opisów funkcji: ERROR_LINE,
ERROR_SEVERITY i ERROR_PROCEDURE.
Krok 3 - Przeglądanie informacji o wystąpieniu błędów
►
Zaznacz kod, który wyświetla zawartość tabeli ErrorLog (patrz kod
poniŜej).
SELECT * FROM ErrorLog
►
Wciśnij F5, aby uruchomić zaznaczony fragment kodu.
Przykładowy wynik działania powyŜszego zapytania:
ErrorID ErrorNumber ErrorMessage
ErrorDate
----------- ----------- --------------------------------- ----------------------1
8134
Divide by zero error encountered. 2006-07-02 11:03:56.427
Zapytanie zwraca zawartość tabeli ErrorLog - czyli informacje o dotychczas
zapisanych błędach.