Wykład ten omawia konstrukcje Transact-SQL, których nie stosujemy zwykle na co dzień. Wiele z nich jest jednak w stanie znacznie ułatwić nam pracę i uprościć kod oraz poradzić sobie z nietypowymi sytuacjami. Większość przedstawionych tutaj konstrukcji jest specyficzna dla języka Transact-SQL i nie zadziała w jego odpowiednikach lub ma tam zupełnie inną składnię.
Klauzula CASE umożliwia zróżnicowanie wyświetlania danych na liście SELECT zapytania, w zależności od warunku. Niejednokrotnie może to znacznie uprościć zapytania, które bez CASE wymagałyby na przykład pisania kilku wersji zapytań dla podzbiorów danych i łączenia ich przez UNION. CASE występuje w dwóch wersjach.
Wersja pierwsza wypisze podaną wartość w zależności od wartości podanych przez programistę bezpośrednio, np.:
SELECT ename,
CASE job
WHEN 'SALESMAN' THEN 'Sprzedawca'
WHEN 'PRESIDENT' THEN 'Szef'
ELSE 'Pozostali'
END
FROM emp
W powyższym przykładzie, gdy w kolumnie "Job" znajdzie się wartość "SALESMAN" wypisany zostanie tekst "Sprzedawca". Gdy znajdzie się tam wartość "PRESIDENT" zostanie wypisany napis "Szef". Dla każdej innej wartości pojawi się napis "Pozostali".
Możemy podać dowolnie wiele linii zawierających składnię WHEN ... THEN ...
Druga, bardziej zaawansowana wersja CASE umożliwia wypisanie różnych danych w zależności od warunku logicznego, a nie wartości występujących w tabeli bezpośrednio, np.:
SELECT ename,
CASE
WHEN job='SALESMAN' AND sal>=1000 THEN 'Bogaty sprzedawca'
WHEN job='SALESMAN' AND sal<1000 THEN 'Biedny sprzedawca'
WHEN job='PRESIDENT' THEN 'Szef'
ELSE 'Pozostali'
END
FROM emp
Jak widzimy, w tym wariancie nie podajemy nazwy kolumny po słowie CASE, za to po każdym WHEN musi znaleźć się warunek logiczny. Gdy jest spełniony, wypisywana jest wartość podana po słowie THEN. Gdy spełnionych jest kilka warunków, zadziała pierwszy. Warunek logiczny występujący po WHEN może używać tych samych operatorów i funkcji, co klauzula WHERE instrukcji SELECT, którą poznaliśmy na początku tego kursu. Możemy również użyć tam podzapytania.
Konstrukcja ta kopiuje wynik działania instrukcji SELECT do nowej tabeli, np.:
SELECT empno, ename, sal INTO nowy_emp FROM emp
Instrukcja ta spowoduje utworzenie nowej tabeli o nazwie "nowy_emp" i schemacie określonym przez zwrócone kolumny oraz skopiowanie wierszy do tej tabeli.
Usuwa wszystkie rekordy z tabeli, nie zapisując do dziennika transakcji usuwanych danych. Nie da się więc wycofać tej instrukcji przez ROLLBACK, lecz będzie działać szybciej niż DELETE. Jeśli w tabeli była ustawiona właściwość IDENTITY (automatycznie numerowanie klucza głównego lub innej kolumny), to zostanie ona wyzerowania i numerowanie zacznie się od początku.
TRUNCATE TABLE emp
Instrukcja UPDATE może pobierać dane z tabeli innej niż modyfikowana w celu dokonania aktualizacji lub sprawdzenia warunków. Pozwala to dokonać modyfikacji danych w tabeli na podstawie innej tabeli. Gdybyśmy chcieli wykonać tego typu modyfikację bez skorelowanego UPDATE, musielibyśmy użyć kursor, aby dla każdego wiersza jednej tabeli wykonywać modyfikacje w drugiej. Byłaby to z pewnością konstrukcja dużo bardziej złożona i mniej wydajna.
Aby wykorzystać tę konstrukcję, musimy do standardowej instrukcji UPDATE dodać klauzulę FROM między SET, a WHERE. Aby instrukcja była logicznie poprawna zwykle musimy dodać warunek złączenia tak, aby skojarzyć wiersze tabeli modyfikowanej z wierszami tabeli podanej po FROM.
UPDATE nowy_emp SET sal = emp.sal FROM emp WHERE emp.empno = nowy_emp.empno
W przykładzie tym pensje pracowników w tabeli "nowy_emp" zostaną nadpisane na takie, jakie istnieją w tabeli "emp".
Funkcje są obiektem programistycznym przechowywanym w bazie danych podobnie do procedur składowanych. Zaletą funkcji jest możliwość użycia ich bezpośrednio wewnątrz instrukcji SQL w odróżnieniu od procedur składowanych, które muszą być wywołane poleceniem EXECUTE.
Funkcja musi zwracać wartość określonego po słowie RETURNS typu i zawierać polecenie RETURN, które tą wartość zwróci. Poza tym w jej kodzie możemy używać wszelkich konstrukcji, których nauczyliśmy się dotychczas.
Poniższy przykład pokazuje funkcję, która zwróci średnią pensję w podanym dziale:
CREATE FUNCTION srednia_w_dziale (@deptno INT) RETURNS NUMERIC(8,2) AS BEGIN DECLARE @srednia NUMERIC(8,2) SELECT @srednia = AVG(sal) FROM emp WHERE deptno = @deptno RETURN @srednia END
Aby użyć tej funkcji, wystarczy napisać na przykład:
SELECT dbo.srednia_w_dziale(30)
Zauważmy, że nazwa funkcji musi być poprzedzona nazwą schematu.
Możemy użyć funkcji wewnątrz dowolnej instrukcji SQL. W poniższym przykładzie zwrócone zostaną te rekordy pracowników, których pensja jest wyższa od średniej:
SELECT * FROM emp WHERE sal > dbo.srednia_w_dziale(20)
Funkcje pozwalają więc znacznie uprościć kod. Jak się łatwo domyślić, powyższa instrukcja SQL bez funkcji wymagałaby użycia podzapytania. W praktyce warto stosować funkcje wtedy, gdy pewne obliczenia wykonywane są wielokrotnie, w różnych miejscach aplikacji.
Funkcje tabelaryczne różnią się od przedstawionych w poprzednim punkcie funkcji tym, że zwracają one nie pojedynczą wartość, lecz tabelę wartości. Można powiedzieć, że są one odpowiednikiem perspektyw (view), lecz dodatkowo oferują możliwość definiowania parametrów.
W tego typu funkcjach po słowie return nie podajemy typu danych, lecz słowo TABLE. Na końcu kodu powinna znaleźć się instrukcja SELECT, której wynik będzie tworzył tabelę zwracaną przez funkcję.
W poniższym przykładzie funkcja tabelaryczna została użyta, aby zwrócić pracowników określonego działu:
CREATE FUNCTION pracownicy_dzialu (@deptno INT) RETURNS TABLE AS RETURN ( SELECT * FROM emp WHERE deptno = @deptno )
Tego typu funkcji możemy użyć w zapytaniach tak, jak zwykłych tabel, np.:
SELECT * FROM pracownicy_dzialu(20)
Wadą funkcji tabelarycznych z poprzedniego punktu było to, że mogliśmy w ich definicji umieścić tylko jedną instrukcję SELECT zwracającą wynik. Struktura wynikowej tabeli była tworzona automatycznie, na podstawie tej instrukcji. Złożone funkcje tabelaryczne pozwalają nam określić strukturę zwracanej tabeli z wynikami, a więc jej kolumny i typy danych.
Po słowie RETURNS musimy podać zmienną tabelaryczną z określoną strukturą. Następnie w kodzie musimy wypełnić tą zmienną, a na końcu umieścić słowo RETURN. Poniższy przykład zwróci dwie kolumny tabeli "emp" dla pracowników określonego działu.
CREATE FUNCTION pracownicy_dzialu (@deptno INT) RETURNS @tabelka TABLE ( empno INT, ename VARCHAR(10) ) AS BEGIN INSERT INTO @tabelka (empno, ename) SELECT empno, ename FROM emp WHERE deptno = @deptno RETURN END
Bardzo często zdarza się, że przy pisaniu złożonej procedury lub skryptu SQL musimy wybrać pewne dane aby użyć ich w dalszych przekształceniach. Takie dane możemy zapisać do tabeli tymczasowej. Będziemy postępować w ten sposób wtedy, gdy musimy wykonać wiele różnych przekształceń danych, które nie dają się wykonać przy pomocy pojedynczych instrukcji SQL. Technika ta może też poprawić wydajność wtedy, gdy raz przeliczony zestaw danych będzie używany wielokrotnie w dalszych przekształceniach.
Tego typu tabele tworzymy w taki sam sposób jak każdą inną tabelę, przy pomocy instrukcji CREATE TABLE. Nazwa tabeli tymczasowej musi zaczynać się od znaku # (lokalna tabela tymczasowa) lub ## (globalna tabela tymczasowa). Dostępne są tutaj wszystkie elementy składni, których nauczyliśmy się, gdy tworzyliśmy tradycyjne tabele, a więc więzy spójności, czy nawet indeksy.
Lokalne tabele tymczasowe są widoczne tylko dla danego połączenia i są automatycznie usuwane po jego zakończeniu.
CREATE TABLE #tabela_tymczasowa (...)
Globalne tabele tymczasowe są widoczne dla każdego połączenia z bazą danych. Są usuwane automatycznie, gdy ostatni użytkownik korzystający z niej zakończy swoje połączenie.
CREATE TABLE ##globalna_tabela_tymczasowa (...)
Tabele tymczasowe są przechowywane w bazie systemowej Tempdb. Nie zauważymy więc ich w interfejsie Management Studio w swojej bazie danych.
Bardzo podobne zastosowanie do tabel tymczasowych mają zmienne tabelaryczne. Różnica jest taka, że są przechowywane głównie w pamięci RAM, a dostęp do nich odbywa się z pominięciem mechanizmów silnika bazy danych takich jak blokady czy zarządzanie transakcjami. Mogą więc działać szybciej, jednak wykorzystują dużą ilość RAM. Nie jest możliwe zakładanie indeksów. Zmienne tabelaryczne możemy traktować jako odpowiednik tablic znanych z języków programowania ogólnego przeznaczenia.
Zmienne tabelaryczne deklarujemy podobnie do zmiennych:
DECLARE @tabela TABLE (Id INT, Nazwisko VARCHAR(50))
Odwołujemy się do nich przy pomocy standardowych instrukcji SQL np.:
INSERT INTO @tabela (Id, Nazwisko) VALUES (1, 'Kowalski') SELECT * FROM @tabela
Tworząc aplikację bazodanową opartą o procedury składowane, może zajść potrzeba wywołania istniejącej procedury z poziomu innej procedury. Jeśli chcielibyśmy przechwycić wynik działania takiej zagnieżdżonej procedury, zrobimy to łatwo w przypadku, gdy zwraca ona dane przez parametr wyjściowy lub instrukcję RETURN. Nie możemy jednak w bezpośredni sposób lub przy pomocy kursora odwołać się do wyniku zwróconego przez resultset. Możemy obejść ten problem wstawiając wynik działania procedury do uprzednio stworzonej tabeli tymczasowej np.:
INSERT INTO #tabela_tymczasowa (...) EXEC procedura
Przy domyślnej deklaracji kursora, po jego rekordach możemy przesuwać się instrukcją FETCH NEXT ... jedynie naprzód. Możemy jednak zadeklarować kursor, który pozwala na dowolne przemieszanie się po rekordach:
DECLARE kursor SCROLL CURSOR FOR SELECT * FROM emp
Po takiej deklaracji będziemy mogli pobierać dowolny wiersz:
FETCH NEXT – następny wierszFETCH PRIOR – poprzedni wierszFETCH FIRST – pierwszy wierszFETCH LAST – ostatni wierszFETCH ABSOLUTE(n) – wiersz o numerze nFETCH RELATIVE(n) – wiersz o n rekordów dalej (lub wcześniej jeżeli n<0)Dotychczas zajmowaliśmy się wyzwalaczami uruchamianymi przez instrukcje DML (INSERT, UPDATE, DELETE). Istnieje możliwość zdefiniowania wyzwalacza na poziomie bazy danych lub serwera, uruchamianego przez instrukcje DDL (CREATE, ALTER, DROP). Pozwolą one kontrolować tworzenie i modyfikowanie obiektów obrębie bazy danych i całego serwera, jak również zautomatyzować czynności związane z operacjami DDL. W tego typu wyzwalaczach nie możemy odwoływać się do tabel inserted i deleted.
CREATE TRIGGER nazwa ON [DATABASE | ALL SERVER] FOR instrukcje AS BEGIN ... END
Gdy stworzymy wyzwalacz z klauzulą ON DATABASE będzie on dotyczył tylko bieżącej bazy danych. ALL SERVER określi, że jest to wyzwalacz na poziomie serwera. W zależności od tego inne mogą być instrukcje, które podajemy po słowie FOR.
Instrukcje to między innymi:
CREATE_TABLE – (na poziomie bazy danych)ALTER_TABLE – (na poziomie bazy danych)CREATE_PROCEDURE – (na poziomie bazy danych)CREATE_DATABASE – (na poziomie serwera)CREATE_LOGIN – (na poziomie serwera)Dotychczas tworzyliśmy wyzwalacze uruchamiane po wykonaniu instrukcji (AFTER) lub przed (BEFORE w Oracle). Możemy również stworzyć wyzwalacz wykonujący się zamiast instrukcji, która go uruchomiła, np.:
CREATE TRIGGER nazwa ON emp INSTEAD OF DELETE AS BEGIN ... END
Jednym z zastosowań tego typu wyzwalaczy może być oprogramowanie złożonych perspektyw, które w przeciwnym wypadku byłyby tylko do odczytu.
W MS SQL istnieje możliwość zdefiniowania własnego typu danych, na podstawie istniejącego typu. Jest to w zasadzie jedynie alias ułatwiający programowanie i administrację. Przykład:
CREATE TYPE pesel FROM VARCHAR(11) NOT NULL
Typ tworzony jest w określonej bazie danych. Gdy go utworzymy, będzie możliwe użycie go zarówno przy tworzeniu tabel, jak też w deklaracjach zmiennych.