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

Polecenie WHILE

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

AS
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'