PROGRAMOWANIE W JĘZYKU TRANSACT-SQL (T-SQL).
Kolejny wykład koncentruje się na własnościach programowania języka
Transact-SQL (T-SQL). Na początek zostaną omówione pliki wsadowe i
skrypty a następnie elementy języka kontroli przepływu, takie jak bloki
IF...ELSE i polecenia WHILE.
PLIKI WSADOWE
Plik
wsadowy
jest
zbiorem
poleceń
T-SQL
(zadań
wsadowych)
interpretowanych przez SQL Server jako całość. Polecenia są zgrupowane
razem a polecenie GO oznacza koniec pliku wsadowego. Następujący przykład
pokazuje zadanie wsadowe.
USE WYPAUT;
BEGIN TRAN;
CREATE TABLE STANOWISKA (stanowisko char(15), wykaz_obowiazkow
char(50));
INSERT INTO stanowiska (stanowisko) SELECT DISTINCT stanowisko
FROM pracownicy;
SELECT DISTINCT stanowisko FROM pracownicy ;
COMMIT TRAN;
GO
JeŜeli wystąpi błąd w składni w dowolnym miejscu zadania (polecenia)
wsadowego, całe zadanie jest usuwane (wyjątki !!!)
Niektóre polecenia mogą być uŜywane w pliku wsadowym, podczas gdy dla
innych nie jest to moŜliwe. Następujące polecenia CREATE mogą być
połączone w pojedynczym pliku wsadowym:
CREATE DATABASE
CREATE TABLE
CREATE INDEX
Następujące polecenia nie mogą być łączone z innymi w tym samym pliku
wsadowym:
CREATE TRIGGER
CREATE PROCEDURE
CREATE VIEW
Korzystając z zadań wsadowych naleŜy mieć na uwadze jeszcze kilka innych kwestii. Zadanie wsadowe jest poddawane : przetwarzaniu, analizie
składniowej, optymalizowane, kompilowane a następnie wykonywane.
Skrypty
Skrypt jest zbiorem złoŜonym z jednego lub więcej zadań wsadowych, które są
zapisywane jako grupa. Skrypty są wykonywane na ogół jako część pewnej
jednostki pracy, która ma zostać zrealizowana, jak np.: ładowanie lub
konserwacja danych.
USE wyp_aut
BEGIN TRAN
SELECT * INTO wypozyczenia_01 FROM wyp WHERE data_odd<'2002-01-
01';
DELETE FROM wyp WHERE data_odd<'2002-01-01';
COMMIT TRAN;
GO
BEGIN TRAN
SELECT * INTO klienci_01 FROM klienci WHERE nr_klienta not in
(SELECT nr_klienta from wyp where data_wyp>'2002-01-01');
DELETE FROM klienci where nr_klienta not in (SELECT nr_klienta from
wypozyczenia where data_wyp>'2002-01-01');
COMMIT TRAN;
GO
ELEMENTY JĘZYKA KONTROLI PRZEPŁYWU
JeŜeli zadanie wsadowe z wieloma poleceniami jest wysyłane do SQL Servera
do wykonania, polecenia są wykonywane w kolejności, w jakiej zostały podane.
Język T-SQL dostarcza kilku poleceń, które pozwalają na zmianę kolejności
wykonywania poleceń w pliku wsadowym. Są one szczególnie przydatne w
transakcjach,
procedurach
wyzwalanych,
funkcjach
i
procedurach
składowanych.
Blok BEGIN...END
Niektóre z poleceń kontroli przepływu jakie zostaną omówione będą wymagały
pojedynczego polecenia jako części swojej składni. Wszędzie gdzie oczekiwane
jest pojedyncze polecenie, moŜna uŜywać tego pojedynczego polecenia. JeŜeli
zajdzie potrzeba, aby wiele poleceń było wykonywanych razem, naleŜy
zamknąć je pomiędzy słowami kluczowymi BEGIN i END. Konstrukcja ta
zostanie wykorzystana w kolejnych przykładach.
Polecenie PRINT
Do tego momentu, jedynym sposobem zwrócenia informacji z SQL Servera do
programu klienta było korzystanie z polecenia SELECT. SQL Server
obsługuje równieŜ polecenie PRINT, ale jest ono ograniczone:
PRINT {' dowolny tekstt' | @ zmienna_lokalna | @@ zmienna_globalna}
Wszystko, co moŜna drukować to łańcuch ASCII (stała łańcuchowa) lub
zmienna typu znakowego (stała lub o zmiennej długości).
Blok IF...ELSE
Blok IF..ELSE pozwala aby polecenie było wykonywane warunkowo. Po
słowie IF występuje wyraŜenie, które musi być warunkiem typu prawda lub
fałsz. JeŜeli wyraŜenie jest prawdziwe, to polecenie jest wykonywane.
Opcjonalne słowo kluczowe ELSE wprowadza polecenie alternatywne, które
moŜe być wykonywane gdy wyraŜenie występujące po IF jest fałszywe.
IF warunek
WyraŜenie1
ELSE
WyraŜenie2
IF (SELECT AVG(pensja) from pracownicy)<1600
PRINT ‘średnia płaca poniŜej 1600zł’
ELSE
PRINT ‘średnia płaca powyŜej 1600zł’
WyraŜenia CASE
Programiści często wymagają moŜliwości stosowania wyraŜenia warunkowego
w innym poleceniu. WyraŜenie CASE pozwala aby wyraŜenia T-SQL były
upraszczane dla wartości warunkowych. WyraŜenie to pozwala zwracać róŜne
wartości w zaleŜności od wartości kontrolnej lub warunku.
Składnia prostego polecenia CASE wygląda następująco:
CASE expression
WHEN warunek1 THEN wyraŜenie1
[[WHEN warunek2 THEN wyraŜenie2[ [...]]
[ELSE wyraŜenie_n]
END
Proste wyraŜenie CASE porównuje wartość początkową z kaŜdą z wartości na
liście i zwraca w wyniku skojarzone z tą wartością wyraŜenie. JeŜeli Ŝadne z
wyraŜeń nie zostanie dopasowane, zwracane jest wyraŜenie występujące po
słowie kluczowym ELSE.
SELECT nazwisko, imie, (CASE
WHEN pensja < 1500 THEN 'mała'
WHEN pensja BETWEEN 1500 and 2000 then 'średnia'
ELSE 'duŜa'
END) as jaka_pensja
From pracownicy
Język T-SQL posiada konstrukcję WHILE, która pozwala na wykonywanie
powtarzane, aŜ do momentu, gdy jest spełniony odpowiedni warunek.
UŜywanie tej konstrukcji jest ograniczone w wielu przypadkach, poniewaŜ ze
swojej natury, SQL Server działa na zbiorach wierszy. Przykładowo, nie ma
potrzeby uŜywania polecenia WHILE, aby przejrzeć wszystkie wiersze w
tabeli;
polecenie SELECT pozwala przeglądnąć wszystkie wiersze,
sprawdzając dla kaŜdego wiersza kryteria zawarte w poleceniu WHERE.
W niektórych sytuacjach potrzebna jest moŜliwość powtórzenia akcji. MoŜna
powtarzać działanie wewnątrz transakcji (nie polecane, poniewaŜ transakcje
powinny być małe i szybkie), w procedurze składowanej lub w funkcji SQL.
Konstrukcja WHILE jest powtarzana, do czasu, aŜ podany warunek będzie
prawdziwy. Podobnie jak w przypadku IF...ELSE, jeŜeli warunek zawiera
polecenie SELECT, polecenie to w całości musi być ujęte w nawiasy zwykłe.
Następujący przykład wielokrotnie sprawdza średnią pensję wszystkich
pracowników. Dopóki średnia płaca jest mniejsza niŜ 1800, płaca pracownika
jest zwiększana o 5%. Powtarzanie zostaje przerwane gdy średnia płaca jest
większa lub równa 1800.
WHILE (SELECT AVG(pensja) FROM pracownicy) < 1800
BEGIN
UPDATE pracownicy SET pensja = pensja * 1.05
PRINT 'pensja pracowników została podniesiona o 5%'
END
ZMIENNE LOKALNE I GLOBALNE
Język Transact-SQL pozwala na uŜycie w instrukcjach programowych
lokalnych i globalnych zmiennych.
Zmienne są elementami języka, którym przypisuje się wartości. W języku
Transact-SQL moŜna uŜywać zmiennych lokalnych i globalnych.
Zmienne lokalne (definiowane przez uŜytkownika)
Zmienną lokalną definiuje się instrukcją DECLARE, wartość początkową
przypisuje się jej instrukcją SET, a następnie wykorzystuje się ją
w instrukcjach pakietu lub procedury, w której została zadeklarowana.
Zmienną lokalną oznacza się symbolem @, poprzedzającym jej nazwę.
DECLARE {@nazwa_zmiennej typ_danych } [,...n]
SET @nazwa_zmiennej_lokalnej = wyraŜenie
PoniŜszy przykład tworzy zmienną lokalną @rok, przypisuje jej wartość, a następnie pobiera dane z bazy danych, wybierając rekordy zawierające
wartość zmiennej lokalnej.
USE wyp_aut
DECLARE @rok decimal(6,2)
SET @rok =2002
SELECT * FROM wypozyczenia
WHERE Year(data_wyp)=@rok
GO
PoniŜszy przykład tworzy zmienne lokalne @PODWYZKA_PROCENT
DECIMAL(10,6), @LICZNIK DECIMAL(5,2), przypisuje im wartości,
a następnie dokonuje aktualizacji danych wykorzystując do ich przetwarzania
obydwie zmienne. (przeanalizować poniŜszy przykład)
USE WYP
SELECT * INTO #ANALIZA FROM PRACOWNICY
DECLARE
@PODWYZKA_PROCENT
DECIMAL(10,6),
@LICZNIK
DECIMAL(5,2)
SET @PODWYZKA_PROCENT=0
SET @LICZNIK=0
WHILE (SELECT AVG(PENSJA) FROM #ANALIZA) < 2000 AND
@PODWYZKA_PROCENT<0.3
BEGIN
SET
@PODWYZKA_PROCENT=(1+@PODWYZKA_PROCENT)*0.05+@POD
WYZKA_PROCENT
SET @LICZNIK=@LICZNIK+1
UPDATE #ANALIZA SET PENSJA = PENSJA * (1+0.0500)
PRINT @LICZNIK
PRINT @PODWYZKA_PROCENT
END
SELECT @LICZNIK ILOSC_PODWYZEK ,@PODWYZKA_PROCENT
LACZNA_WIELKOSC_PODWYZKI,AVG(PENSJA)SREDNIA_PENSJA
FROM #ANALIZA
DROP TABLE #ANALIZA
GO
Zmienne globalne (dostarczane przez system)
Zmienne globalne są predefiniowane i obsługiwane przez SQL Server.
UŜytkownicy nie mogą nadać lub zmienić bezpośrednio wartości zmiennej
globalnej. Wiele zmiennych globalnych informuje o aktywności systemu, która
miała miejsce od ostatniego uruchomienia serwera SQL; inne dostarczają
informacji na temat połączeń Zmienną lokalną oznacza się symbolem @@, poprzedzającym jej nazwę.
Zmienne globalne uŜyteczne są przy pobieraniu bieŜących informacji na temat
stanu serwera SQL.
PROCEDURY SKŁADOWANE
Procedura składowana jest nazwanym, prekompilowanym zestawem
instrukcji SQL, przechowywanym na serwerze, zapewniającym szybszą
i wydajniejszą realizację. Procedury składowane mogą być uruchamiane przez
pojedyncze wywołanie z aplikacji klienta, dopuszczają zmienne deklarowane
przez uŜytkownika, wykonanie warunkowe i inne uŜyteczne właściwości
programistyczne. Na serwerze SQL występują procedury składowane róŜnych
typów: systemowe, lokalne, tymczasowe i zdalne.
Systemowe procedury składowane są procedurami składowanymi
dostarczanymi
razem
z serwerem SQL jako ułatwienie w pobieraniu
informacji
z tabel
systemowych.
Obsługują
one
takŜe
zadania
administratorskie w bazie, które powodują modyfikacje tabel systemowych
bez bezpośredniego odwoływania się do nich. Mogą być uruchamiane w kaŜdej
bazie.
Lokalne procedury składowane tworzone są w konkretnych bazach
uŜytkownika.
Tymczasowe procedury składowane mogą być lokalne lub globalne—
jako lokalne tymczasowe procedury w ramach sesji uŜytkownika lub jako
globalne tymczasowe procedury w ramach sesji wszystkich uŜytkowników.
Zdalne procedury składowane są procedurami wywoływanymi ze
zdalnego serwera lub przez klienta podłączonego do innego serwera.
Procedury składowane są podobne do procedur w innych językach progra-
mowania i mogą: akceptować parametry, zawierać programowe instrukcje do
realizacji zadań w bazie (np. uruchamianie innych procedur), zwracać wartość
stanu do procedury wywołującej lub wsadu, aby sygnalizować udane lub
nieudane wykonanie (i przyczynę błędu) oraz zwracać wiele wartości w formie
parametrów do procedury wywołującej lub wsadu.
Procedury składowane posiadają wiele zalet. Mogą one:
Realizować logikę aplikacji dla aplikacji klienckich, zapewniając
spójność danych i manipulacji nimi.
Dostarczać logikę przetwarzania. Logika przetwarzania i załoŜenia
zawarte w procedurach składowanych mogą być zmieniane w jednym miejscu.
Wszystkie aplikacje mogą uŜywać tych samych procedur składowanych, aby
zapewnić spójność modyfikacji danych.
Dostarczać mechanizmy bezpieczeństwa. UŜytkownik moŜe mieć prawo
do uruchamiania procedury składowanej nawet jeśli nie ma praw do
uŜywanych przez procedurę tabel i widoków.
Automatycznie uruchamiać się przy starcie, jeśli systemowa procedura
składowana sp_procoption ma ustawioną opcje true. Procedury systemowe
uruchamiane są przy starcie po odbudowaniu wszystkich baz danych.
Zwiększać wydajność, gdyŜ są prekompilowane, a po pierwszym
uruchomieniu pozostają w pamięci dla dalszych wywołań.
Redukować obciąŜenie sieci. Operacje mogą być wykonywane przez
przesłanie pojedynczych instrukcji, zamiast wysyłania setek linii kodu języka
Transact-SQL.
Procedury składowane redukują ilość Ŝądań wysyłanych przez klienta do
serwera.
Instrukcja CREATE PROCEDURE nie moŜe być uŜyta w jednym wsadzie
z innymi instrukcjami SQL.
Definicja CREATE PROCEDURE moŜe zawierać dowolną ilość i typ
instrukcji SQL z wyjątkiem następujących: CREATE VIEW, CREATE
TRIGGER, CREATE DEFAULT, CREATE PROCEDURE i CREATE
RULE. Inne obiekty bazy danych mogą być w procedurze tworzone.
Procedury składowane mogą odwoływać się do istniejących tabel, widoków
i procedur składowanych, a takŜe tabel tymczasowych.
PoniŜszy kod tworzy procedurę składowaną, która wyświetla wypoŜyczenia
przechowywane w bazie wypoŜyczenia dokonane w 2003 roku.
CREATE PROC wyp2003
AS
SELECT *
FROM wyp
WHERE data_wyp BETWEEN ‘01/01/2003 and ‘12/31/2003’
Uruchamianie procedur składowanych
Procedury składowane uruchamia się przy uŜyciu instrukcji EXECUTE
razem z nazwą procedury i parametrami
PoniŜsza instrukcja uruchamia procedurę składowaną wyp2003, która
wymienia wypoŜyczenia przechowywane w bazie wypozyczenia
EXEC wyp2003
GO
PoniŜszy kod tworzy procedurę składowaną, która podnosi pensję pracownika
zgodnie z danymi zawartymi w zmiennych @dzial, @z_wyskosc
CREATE PROCEDURE podwyzka
@z_dzial varchar(20), @z_wysokosc integer
UPDATE pracownicy SET
pracownicy.pensja=pracownicy.pensja+@z_wysokosc
WHERE pracownicy.dzial=@z_dzial;
PoniŜsze instrukcje uruchamiają procedurę składowaną podwyzka
USE wypaut
EXEC podwyzka 'TECHNICZNY' ,20
GO
USE wypaut
EXEC podwyzka @z_dzial= 'TECHNICZNY' , @z_wysokosc =20
GO
FUNKCJE ZDEFINIOWANE PRZEZ UśYTKOWNIKA
WaŜną własnością SQL Servera 2008 jest moŜliwość definiowania przez
uŜytkownika własnych funkcji ( User-Defined-Functions) których wartością
moŜe być skalar (tekst, data) jak i tabela. Definiowane funkcje przez
uŜytkownika mogą zaleŜeć od wielu parametrów (zmiennych). Klauzula
RETURNS w definiowanych funkcjach decyduje czy zwracana jest
pojedyncza wartość czy teŜ tabela.
MoŜna wyróŜnić dwa typy definicji funkcji UDF której wartością jest tabela:
• zwracana tabela jest wynikiem pojedynczego polecenia Select;
wtedy nazwy kolumn i ich typy są zdeterminowane przez polecenie
Select (funkcja typu inline).
• definicja funkcji zawiera : określenie struktury tabeli wynikowej
oraz wstawienie do niej określonych rekordów. Określenie tego typu
funkcji daje większą swobodę w jej definiowaniu (funkcje typu
multistatement)
Definicje funkcji typu multistatemen mogą zawierać następujące
polecenia:
• przypisania
• polecenia kontroli przepływu
• operacje na kursorach
• polecenia deklarowania zmiennych
• polecenia INSERT, UPDATE, DELETE
• polecenia SET/SELECT, uŜywane do przypisania wartości do
zmiennych lokalnych
Najprostsze w budowaniu są funkcje skalarne. Podstawowa składnia w
tym przypadku jest następująca:
CREATE FUNCTION nazwa_uŜytkownika.nazwa funkcji
(par1_nzwa typ1, par2_nazwa typ2)
RETURNS wart_nazwa typ
Treść definiująca zwracaną wartość
RETURN wart_nazwa
Przykład- całka z gęstości rozkładu normalnego.
CREATE FUNCTION CALKA_N (
@K_LEWY DECIMAL(16,10) ,@K_PRAWY DECIMAL(16,10))
RETURNS DECIMAL(16,10)
AS
BEGIN
DECLARE @WYNIK DECIMAL(16,10)
DECLARE @PUNKT DECIMAL(16,10)
SET @PUNKT=@K_LEWY
SET @WYNIK=0.0
WHILE @PUNKT<@K_PRAWY
BEGIN
SET @WYNIK=@WYNIK+1/(SQRT(2*PI()))*(((@K_PRAWY-
@K_LEWY)*0.00005*(EXP(-POWER(@PUNKT,2)/2)
+4*EXP(-POWER(@PUNKT+0.00005*(@K_PRAWY-
@K_LEWY),2)/2)+EXP(-POWER(@PUNKT+0.0001*(@K_PRAWY-
@K_LEWY),2)/2))/3))
SET @PUNKT=@PUNKT+0.0001*(@K_PRAWY-@K_LEWY)
END
RETURN @WYNIK
END
SELECT DBO.CALKA_N(0,0.1),DBO.CALKA_N(0,1),DBO.CALKA_N(0,10)
MoŜna wyróŜnić dwa rodzaje funkcji tabelowych (zwracających tabelę)
Funkcję tabelową o wartości inline
Funkcję tabelową o wartości multistatement
Składnia funkcji pierwszego typ (inline) przedstawia się następująco :
CREATE FUNCTION nazwa_funkcj (zmienne wraz typem)
RETURNS TABLE AS RETURN ( pełna składnia polecenia Select)
(w trakcie definiowania tego typu funkcji nie jest definiowana nazwa
zwracanej tabeli, wszystkie kolumny (nawet wyliczeniowe) w poleceniu select
muszą być nazwane)
Przykład funkcji zwracającej tabelę dwukolumnową, zawierającą ilość
wypoŜyczeń samochodów w poszczególnych kolorach powyŜej wskazanej ilości
w argumencie funkcji.
CREATE FUNCTION DBO.GRUPOWANIE_KOLORY (@ZAKRES INT)
RETURNS TABLE AS RETURN (SELECT COUNT(KOLOR) ILOSC ,
KOLOR
FROM WYP , SAMOCHODY
WHERE WYP.NR_SAMOCHODU=SAMOCHODY.NR_SAMOCHODU
GROUP BY KOLOR
HAVING COUNT(KOLOR)>@ZAKRES)
Po utworzeniu funkcji moŜe ona być wywoływana w dowolnym poleceniu
SELECT czy teŜ procedurze
Przykład:
SELECT * FROM DBO.GRUPOWANIE_KOLORY(1)
Składnia funkcji drugiego typu (multistatement) przedstawia się następująco :
CREATE FUNCTION nazwa_funkcji (zmienne wraz typem) RETURNS
TABLE ( opcjonalnie_nazwa_tabeli) (pola_wraz_z_typami_danych)
AS
BEGIN
Deklaracje pomocniczych zmiennych
Polecenia typu SELECT, INSERT, UPDATE przy pomocy których
wydobywane są dane i wstawiane do docelowej tabeli
RETURN
END
Przykład funkcji dokonującej wyznaczenia wielkości minimalnego i
maksymalnego czasu trwania wypoŜyczenia dla zadanego roku.
CREATE FUNCTION DBO.MAX_MIN (@ROK INT)
RETURNS @LICZNOSC_POZIOMU TABLE (POZIOM CHAR(8),
WIELKOSC INT)
AS
BEGIN
DECLARE @MINIMUM DECIMAL(16,4)
DECLARE @MAKSIMUM DECIMAL(16,4)
SELECT
@MINIMUM=MIN(datediff(dd,data_wyp,data_odd)+1),@MAKSIMUM=MA
X(datediff(dd,data_wyp,data_odd)+1) FROM wyp WHERE
year(data_wyp)=@rok
INSERT INTO
@LICZNOSC_GRUP
VALUES ('MAKSIMUM',
@MAKSIMUM)
INSERT INTO
@LICZNOSC_POZIOMU
VALUES ('MINIMUM', @MINIMUM)
RETURN
END
SELECT * FROM MAX_MIN(2)
PROCEDURY WYZWALANE
Procedura wyzwalana jest specjalnym typem procedury składowanej,
wywoływanej automatycznie przy próbie modyfikacji danych chronionych.
Procedury wyzwalane pomagają zachować integralność danych przez ochronę
nieautoryzowanymi lub niespójnymi zmianami danych. Procedury wyzwalane
nie mają parametrów i nie mogą być wywoływane bezpośrednio. Wszystkie
procedury wyzwalane (INSERT, UPDATE, DELETE) domyślnie uruchamiają
się po wykonaniu modyfikacji danych. Zwane są one procedurami
wyzwalanymi AFTER.
Procedury wyzwalane uŜywają tabel wirtualnych Inserted, Deleted, o takiej
samej strukturze jak struktura tabeli aktualizowanej. JeŜeli dodawany jest
nowy wiersz do tabeli aktualizowanej, wpis rejestrowany jest w tabeli bazowej
jak i w tabeli Inserted. Gdy dane są usuwane, usunięty rekord przechowywany
jest
w
tabeli
Deleted.
Uaktualnienie
wiąŜe
się
z
umieszczeniem
modyfikowanego rekordu w postaci oryginalnej w tabeli Deleted oraz tegoŜ
rekordu w wersji modyfikowanej w tabeli Inserted jak i w tabeli bazowej.
Procedurę wyzwalaną tworzy się za pomocą polecenia CREATE TRIGGER.
CREATE TRIGGER nazwa_triggera
ON nazwa_tabeli
FOR AFTER (INSERT, UPDATE, DELETE)
AS polecenia_SQLa
Przykład procedury wyzwalanej, sprawdzającej czy wypoŜyczane auto jest aktualnie na stanie (podobnie w przypadku wypoŜyczania ksiąŜek – baza
danych biblioteka)
CREATE TRIGGER JUZ_WYPOZYCZONY
ON WYP
FOR INSERT
AS
IF(SELECT COUNT(*) FROM WYPOZYCZENIA, INSERTED WHERE
WYP.NR_SAMOCHODU=INSERTED.NR_SAMOCHODU AND
WYP.DATA_ODD IS NULL)<=1
COMMIT TRANSACTION
ELSE
BEGIN
PRINT 'SAMOCHÓD JESZCZE NIE ODDANY'
ROLLBACK TRANSACTION
END
Próba dodania rekordu poprzez wykonanie polecenia SQL:
INSERT INTO wyp
VALUES (INSERT INTO wyp VALUES (15, 7, 'Jan 1 2007 12:00AM', 'Jan 6
2007 12:00AM', 4, 6, 44, 62))
Kończy się komunikatem 'SAMOCHÓD JESZCZE NIE ODDANY'