Reklama
Wizyt
Dzisiaj: 298Wszystkich: 520575

T-SQL

Technikum » SBD » SQL » T-SQL


T-SQL (Transact-SQL) jest rozszerzeniem języka SQL stosowanym dla serwerów MS SQL Server. W jego skład oprócz standardowych poleceń języka SQL wchodzą instrukcje tworzenia pętli, instrukcje warunkowe, zmienne, wyzwalacze, funkcje i procedury.
W języku T-SQL ciąg poleceń bezpośrednio kierowany do serwera powinien kończyć się słowem kluczowym GO. Realizacja ciągu poleceń na poziomie edytora jest realizowana po wybraniu przycisku Execute lub wciśnięciu na klawiaturze F5.
Pojedyncza instrukcja może kończyć się średnikiem, ale brak średnika nie jest błędem. Instrukcje mogą być pisane w jednej linii. Jednak w celu większej czytelności kodu zaleca się umieszczanie każdej z nich w oddzielnej linii i stosowanie wcięć.

 

Zmienne


Zmienne deklarowane przez użytkownika są zmiennymi lokalnymi i istnieją tylko w obrębie skryptu. Muszą być poprzedzone znakiem @. Deklaracja zmiennych jest realizowana za pomocą instrukcji:

DECLARE @zmienna typ_danych



W jednej instrukcji można deklarować wiele zmiennych.

Przykład


W podanym przykładzie zostały zadeklarowane dwie zmienne lokalne @Imie i @Nazwisko.



Zmienne systemowe

Zmienne systemowe oznaczone są dwoma znakami @@.

Przykłady zmiennych systemowych:

  • @@ERROR — zwraca numer ostatniego błędu (0 — brak błędu);
  • @@FETCH_STATUS — ustala, czy kursor pobrał wiersz (0 — pobrał);
  • @@IDENTITY — zwraca ostatnio wygenerowaną wartość;
  • @@ROWCOUNT — zwraca liczbę wierszy, dla których została wykonana instrukcja SQL;
  • @@VERSION — zwraca informację o wersji SQL;
  • @@TRANCOUNT — zwraca liczbę otwartych transakcji.


Wartość do zmiennej można przypisać za pomocą instrukcji SELECT.

SELECT @zmienna=wyrażenie [FROM ...]


Przykład


Innym sposobem przypisania zmiennej wartości jest użycie instrukcji SET. Instrukcją SET można przypisać tylko jedną zmienną.


Przykład


Zmiennym można przypisywać wartości zwrócone przez zapytanie. Zapytanie takie musi jednak zwracać dokładnie jeden wiersz.


Przykład


Jeśli zapytanie zwróci więcej wierszy, to zmiennym zostaną przypisane wartości z ostatniego wiersza. Natomiast jeśli zapytanie nie zwróci żadnego wiersza, zmienne zachowają swoje dotychczasowe wartości.

Wartość przypisaną zmiennej można odczytać za pomocą instrukcji SELECT.


Przykład


Instrukcja INSERT INTO doda do tabeli Ksiazki nowy rekord i w polu tytuł umieści podaną wartość. Dodatkowo dla pola id_ksiazki, które nie może pozostać puste, zostanie wygenerowana wartość liczbowa. Zapytanie SELECT @@IDENTITY zwróci wartość wygenerowaną dla tego pola.

 

Instrukcja warunkowa


Instrukcja warunkowa używana jest do określania sposobu wykonania kodu. Ogólna postać instrukcji wygląda następująco:

 


Przykład


Wynikiem będzie sprawdzenie, czy klient o nazwisku Adam Nowak został zarejestrowany w bazie, a jeżeli nie został zarejestrowany — dodanie go do tabeli Klient.


Przykład



Funkcja systemowa object_Id (nazwa_obiektu) zwraca numer identyfikacyjny obiektu i została użyta do sprawdzenia, czy podany obiekt istnieje. Jeżeli tak jest, obiekt ten zostanie usunięty

 

Wyrażenie CASE


Wyrażenie CASE może być użyte wewnątrz niektórych poleceń języka SQL Należą do nich polecenia: SELECT, UPDATE, DELETE, SET oraz klauzule: IN, WHERE, ORDER BY, HAVING. Wyrażenie CASE sprawdza dla każdego wiersza wartość otrzymaną w zapytaniu i porównuje ją z wynikiem wyrażenia. W zależności od wyniku zwraca wartość zapisaną po słowie kluczowym THEN.



Przykład


Wyrażenie CASE sprawdza dla każdego wiersza tabeli Ksiazki wartość pola cena ksiazki i porównuje ją z wartościami 20 i 80. W zależności od wyniku porównania zwraca wartość zapisaną po słowie kluczowym THEN.

 

Wyrażenia tablicowe (CTE)


Wyrażenia tablicowe umożliwiają definiowanie wirtualnych tabel, do których można się odwoływać wielokrotnie za pomocą ich nazwy. W celu utworzenia wyrażenia tablicowego należy użyć polecenia WITH w postaci:


Po wykonaniu zapytania zostanie utworzona wirtualna tabela, z którą można pracować jak ze zwykłą tabelą.

Wyrażenia tablicowe mogą być stosowane:

  • do tworzenia zapytań cyklicznych,
  • jako odpowiedniki widoków, jeżeli nie ma potrzeby przechowywania definicji metadanych,
  • przy wielokrotnym odwoływaniu się do tabeli wynikowej w tym samym zapytaniu.

Zaletą stosowania wyrażeń tablicowych jest większa czytelność złożonych zapytań. Mogą one służyć do budowania prostych bloków logicznych, z których tworzone są bardziej złożone zapytania. Mogą być definiowane i używane w funkcjach i procedurach składowanych oraz w wyzwalaczach i widokach.

Wykonanie wyrażenia tablicowego następuje po podaniu polecenia SELECT:

 

Przykład


Najpierw została utworzona wirtualna tabela CTE_Klient, a następnie na podstawie tej tabeli w zapytaniu zostały wybrane dane do pokazania.
Jeżeli połączymy ze sobą wyniki dwóch wyrażeń tabelarycznych, uzyskamy możliwość wykonywania rekurencyjnych zapytań.

 

Procedury składowane


Procedury składowane to polecenie lub kilka poleceń, które są wykonywane jako całość w jednym bloku. Mogą mieć zadeklarowane zarówno parametry wejściowe, jak i wyjściowe. Mogą również zawierać polecenia kontrolowania kodu, takie jak IF oraz WHILE. Zaleca się, aby wszystkie powtarzalne czynności w bazie danych były definiowane przy użyciu procedur składowanych.

Zdefiniowana przez użytkownika procedura jest tworzona w bieżącej bazie danych. Wyjątkiem są procedury tymczasowe — one zawsze są tworzone w folderze tempdb.

Definiowanie procedury składowanej wygląda następująco:



Przykład

Utworzona procedura będzie pobierała parametr z ceną książek i zwracała listę tylko tych książek, których ceny są niższe niż cena podana jako parametr.


Pierwsza instrukcja tworzy procedurę o nazwie cena_ksiazki z parametrem wejściowym @cena_ks typu money.

Kolejne dwie instrukcje to instrukcje procedury. Pierwsza instrukcja to polecenie wydrukowania podanego tekstu połączonego z wartością parametru wejściowego, którego typ został zmieniony z money na varchar(10). Druga to zapytanie, w którym jako warunek klauzuli WHERE występuje parametr wejściowy, zwracające listę wszystkich książek, które kosztują mniej niż wartość parametru wejściowego.

Procedury składowane mogą być wykonywane w podany sposób:

EXECUTE nazwa procedury;

lub:

EXEC nazwa procedury;


Przykład

Aby uruchomić procedurę dla książek tańszych niż 90 zł, należy wpisać polecenie EXECUTE w podanej postaci:




Przykład
Mamy utworzyć procedurę, która po podaniu nazwiska i imienia klienta będzie zwra¬cała jego dane.


Procedura może zostać wykonana w następujący sposób:


lub:



Parametry do procedury można przekazywać anonimowo (bez określania, któremu parametrowi zostanie przypisana wartość). Jest to dobre rozwiązanie, gdy procedura ma jeden parametr wejściowy. Natomiast w przypadku, gdy tych parametrów jest więcej (jak w podanym przykładzie), lepiej stosować jawne przypisanie wartości do parametru.
Jeżeli procedura jest wywoływana jako pierwszy element w skrypcie, słowo kluczowe EXECUTE może zostać pominięte.

Użyta w procedurze składowanej opcja SET NOCOUNT ON zapobiega wyświetlaniu komunikatu pokazującego, na ilu wierszach działa instrukcja.


Przykład


Po podaniu tytułu książki procedura zwróci liczbę sprzedanych książek o podanym tytule. Procedura może zostać wielokrotnie użyta w analizie statystycznej dotyczącej sprzedaży poszczególnych tytułów.

 

Wbudowane procedury

Oprócz definiowania własnych procedur pracę z bazą danych może usprawnić stosowanie procedur wbudowanych.

  • sys.sp_addrolemember — ta procedura dodaje użytkownika do roli bazy danych. Parametry to @rolename i @membername.
  • sys.sp_adduser — ta procedura dodaje użytkownika do bazy danych. Parametry to: @loginame, @name_in_db, @grpname.
  • sys.sp_catalogs — ta procedura zwraca listę katalogów serwera. Parametr to @server_name.
  • sys.sp_columns — ta procedura zwraca szczegółowe informacje dotyczące kolumn wybranej tabeli. Parametry to: @table_name, @table_ovner, @table_gualifier, @column_name, @ODBCVer.
  • sys.sp_databases — ta procedura zwraca listę wszystkich baz danych dostępnych na serwerze wraz z informacją o ich rozmiarze.
  • sys.sp_help — ta procedura zwraca szczegółowe informacje (typ, precyzja itp.) wskazanego obiektu bazodanowego (na przykład tabeli).

 

Funkcje składowane


Funkcje składowane działają podobnie jak procedury składowane, ale sposób ich wykorzystania jest zupełnie inny. Jedną z cech takiej funkcji jest to, że należy zadeklarować w niej typ zwracanego wyniku. Instrukcja SELECT wykorzystywana w procedurze może zwracać pewną wartość, ale nie musi, natomiast w funkcji jest to wymagane. Dlatego zalecane jest, aby procedury były wykorzystywane do zapisywania, modyfikowania i usuwania rekordów, czyli działań, które nie zwracają wyniku, zaś funkcje do obliczeń typu: liczenie średniej, przeliczanie wartości itp.

Podstawowa postać funkcji to:

 

Podczas definiowania funkcji trzeba zdefiniować nagłówek (nazwa, lista parametrów, typ zwracanej wartości) i treść funkcji. Ostatnim poleceniem treści powinna być instrukcja RETURN, która określa wartości zwracane przez funkcję.


Przykład


Funkcja zmienia format daty z 2012-10-20 na bardziej przyjazny 20-10-2012.

Jeżeli w tabeli Klient zostanie umieszczone pole data urodzenia, to wywołanie funkcji może mieć postać:

 

Przykład tej funkcji pokazuje, jak duże możliwości daje stworzenie własnej biblioteki ze skryptami zawierającymi funkcje, które można wykorzystywać w wielu tworzonych projektach.



Funkcje wbudowane


Podobnie jak w przypadku procedur, użytkownik może skorzystać z istniejących wbudowanych funkcji. Najczęściej wykorzystywane funkcje to:

  • AVG — obliczanie wartości średniej;
  • SUM — sumowanie wartości;
  • COUNT — zliczanie liczby wystąpień;
  • GETDATE — zwracanie wartości bieżącej daty i czasu;
  • DAY — zwracanie dnia miesiąca jako liczby całkowitej;
  • YEAR — zwracanie roku jako liczby całkowitej;
  • MONTH — zwracanie miesiąca jako liczby całkowitej.

 

Wyzwalacze


Wyzwalacze (ang. triggers) to specjalny rodzaj procedur składowanych, które są automatycznie wykonywane, gdy na serwerze wystąpi określone zdarzenie. Tymi zdarzeniami są operacje przeprowadzane przez użytkownika. Dopiero po wykonaniu instrukcji uruchamiany jest wyzwalacz. Polecenia wykonywane w ramach wyzwalacza są traktowane jako transakcje rozpoczęte jawnie lub niejawnie przez użytkownika.

Głównym przeznaczeniem wyzwalaczy jest wymuszenie integralności danych. Tylko właściciel tabeli może utworzyć powiązany z nią wyzwalacz. Praw do tworzenia wy-zwalaczy nie można nikomu przekazać. Możliwe jest definiowanie dla tabeli dowolnej liczby powiązanych z nią wyzwalaczy. Nie można definiować wyzwalaczy tabel tymczasowych. Wyzwalacze nie zwracają żadnych danych. Są automatycznie uruchamiane w odpowiedzi na działania użytkownika.

Do tworzenia wyzwalaczy służy instrukcja CREATE TRIGGER.

W języku SQL występują trzy rodzaje wyzwalaczy:

  • wyzwalacze DML,
  • wyzwalacze DDL,
  • wyzwalacze logowania.



Wyzwalacze DML

Wyzwalacze DML są wywoływane, gdy zostanie wykonane jedno z poleceń języka DML, na przykład INSERT, UPDATE lub DELETE.

Takie wyzwalacze są użyteczne:

  • przy modyfikowaniu danych w powiązanych tabelach (jednak lepsze efekty uzyskamy, stosując w tej sytuacji kaskadowe więzy integralności);
  • przy egzekwowaniu warunków ograniczających zakres danych wprowadzanych do kolumny zdefiniowany w instrukcjach INSERT, UPDATE i DELETE (w przeciwieństwie do warunków ograniczających, definiowanych przy użyciu atrybutu CHECK, wyzwalacze DML mogą odwoływać się do kolumn w innych tabelach);
  • przy podejmowaniu działań na podstawie odczytu stanu tabeli przed modyfikacją i po modyfikacji.

Można wyróżnić następujące typy wyzwalaczy DML:

  • Wyzwalacze After — są uruchamiane po instrukcji generującej zdarzenie. Mogą być definiowane tylko dla tabel. Wyzwalacz zostanie uruchomiony tylko wtedy, gdy wszystkie operacje generujące zdarzenie zostały wykonane pomyślnie.
  • Wyzwalacze Instead of — są wykonywane zamiast instrukcji generującej zdarzenie. Są definiowane dla widoków opartych na tabelach i aktualizują zawartość tych widoków.
  • Wyzwalacze CLR — mogą być wyzwalaczami After lub Instead of Mogą być również wyzwalaczami DLL. Zamiast wykonywania transakcji dla procedury składowanej wykonują kod zewnętrzny utworzony w .NET Framework i przesłany do serwera SQL Server.


Postać instrukcji CREATE TRIGGER dla wyzwalaczy DML:


gdzie:

  • nazwa_tabeli /widoku — tabela lub widok, dla którego wykonywany jest wyzwalacz. Podanie nazwy tabeli lub wyzwalacza jest opcjonalne. Widok może wystąpić tylko w wyzwalaczu Instead of. Wyzwalaczy DML nie można tworzyć dla tabel tymczasowych.
  • FOR/AFTER — wyzwalacz zostanie uruchomiony tylko wtedy, gdy wszystkie operacje generujące zdarzenie zostały wykonane pomyślnie. Zdefiniowane kaskadowe aktualizowanie więzów integralności oraz warunki ograniczające również muszą dać pozytywne efekty.
  • INSTEAD OF — wyzwalacz jest wykonywany zamiast generującej go instrukcji. Tylko jeden wyzwalacz tego typu może zostać zdefiniowany dla tabeli lub widoku. Natomiast można zdefiniować widoki dla widoku i każdy z nich może posiadać swój wyzwalacz typu INSTEAD OF.
  • INSERT, UPDATE i DELETE — określają instrukcje modyfikacji danych, które mogą uruchomić wyzwalacz dla określonej tabeli lub dla określonego widoku. Co najmniej jedna z instrukcji musi zostać wybrana. W wyzwalaczach typu INSTEAD OF instrukcja DELETE nie jest dozwolona dla tabel, które mają zdefiniowaną opcję kaskadowego usuwania danych. Podobnie jak instrukcja INSERT, nie jest ona dozwolona w przypadku tabel, które mają zdefiniowaną opcję kaskadowego aktualizowania danych.


Przykład


Wyzwalacz wysyła do użytkownika komunikat, gdy ktoś próbuje dodać dane do tabeli Klient lub zmienić je.

RAISERROR( ... ) to funkcja generująca komunikat o błędzie.

 

Wyzwalacze DDL

Wyzwalacze DDL są uruchamiane w wyniku zdarzeń zachodzących po wykonaniu poleceń języka DDL Dotyczy to poleceń CREATE, ALTER i DROP. Często są wykorzystywane do sprawdzania stanu odpowiedzi wysyłanych przez procedury składowane do systemu. Mogą być wykorzystywane do zadań administracyjnych, takich jak audyt czy regulowanie operacji bazodanowych.

Są użyteczne, gdy trzeba:

  • uniemożliwić wprowadzenie zmian w schemacie bazy danych,
  • wykonać działania w bazie danych w odpowiedzi na zmiany w schemacie bazy,
  • zapamiętać zmiany w schemacie bazy danych.

Wyzwalacze DDL są uruchamiane tylko po poleceniach języka DDL. Nie mogą być używane wyzwalacze INSTEAD OF.


Postać instrukcji CREATE TRIGGER dla wyzwalaczy DDL:

gdzie:

  • DATABASE — działanie wyzwalacza dotyczy bieżącej bazy danych;
  • ALL SERVER — działanie wyzwalacza dotyczy bieżącego serwera;
  • nazwa zdarzenia — nazwa zdarzenia, po którym nastąpi wywołanie wyzwalacza.


Przykładowe zdarzenia wykorzystywane przez wyzwalacze DDL:

  • CREATE_TABLE,
  • ALTER_TABLE,
  • CREATE_SCHEMA,
  • GRANT_DATABASE,
  • CREATE_INDEX,
  • DROP_INDEX.

Słowa kluczowe zawsze są oddzielone znakiem podkreślenia _.


Przykład

Zdefiniowany wyzwalacz zadziała zawsze, gdy w bazie danych wystąpi zdarzenie DROP_TABLE lub ALTER_TABLE. Zdarzenia te są generowane instrukcjami DROP TABLE i ALTER TABLE.

 

Wyzwalacze logowania

Wyzwalacze logowania uruchamiają się w odpowiedzi na zdarzenia związane z logowaniem. Uruchamiane są po zakończeniu fazy uwierzytelnienia logowania, ale przed rozpoczęciem sesji użytkownika. Nie są uruchamiane, gdy logowanie nie powiedzie się.

Postać instrukcji CREATE TRIGGER dla wyzwalaczy logowania:




Przykład


W podanym przykładzie wyzwalacz logowania zabrania logowania do serwera SQL zainicjowanego przez login_test wtedy, gdy istnieją już trzy sesje logowania utworzone przez tego użytkownika.
Stosując ten mechanizm, można kontrolować aktywność logowania do serwera i ograniczać liczbę sesji dla określonego loginu.

 

Uprawnienia

Do tworzenia wyzwalaczy DML wymagane są uprawnienia ALTER dla tabeli lub widoku, dla którego tworzony jest wyzwalacz.

Do tworzenia wyzwalaczy DDL dla serwera (ON ALL SERVER) oraz wyzwalaczy logowania wymagane są uprawnienia CONTROL SERVER na serwerze.

Do tworzenia wyzwalaczy DDL dla bazy danych (ON DATABASE) wymagane są uprawnienia ALTER DATABASE.

 

 

Reklama