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'