Transact-SQL (T-SQL) je proprietární rozšíření pro SQL od společností Microsoft a Sybase. Microsoft tento jazyk pužívá ve svých produktech Microsoft SQL Server . T-SQL poskytuje širokou škálu funkcionality pro programáory nebo databázové administrátory. Transact SQL umožňuje řízení SQL dotazů, podmíněné zpracování konstrukcemi IF, ELSE, CASE, nebo WHILE, využití zabudovaných funkcí MSSQL serveru, nebo také sekvenční zpracování pomocí kurzorů. Velká síla T-SQL je v uložených procedurách díky nimž mohou programátoři definovat dávky SQL a znovuvyužívat kód, definovat triggery, nebo například řešit část aplikační logiky už na databázovém serveru. Administrátoři moho pomocí systémových uložených procedur administrovat server, sytémové procedury totiž umožňují pomocí T-SQL nastavovat velkou škálu parametrů SQL serveru, vytvářet databáze, zálohovat, nastavovat práva atd…
Struktura IF, ELSE umožňuje vyhodnotit logický výraz a dle jeho výsledku vykonat dotaz.
IF Boolean_expression { sql_statement | statement_block } [ ELSE { sql_statement | statement_block } ]
DECLARE @Condition INT SET @Condition = 250 IF @Condition > 100 BEGIN SELECT TOP 100 * FROM TABLE END ELSE BEGIN SELECT TOP @Condition * FROM TABLE END
Funkce CASE umožňuje vracet hodnotu na základě vyhodnocení jednoho, nebo více výrazů.
CASE input_expression WHEN when_expression THEN result_expression [ ...n ] [ ELSE else_result_expression ] END
SELECT firstname, surname, CASE gender WHEN 'male' THEN 'M' WHEN 'female' THEN 'F' ELSE 'UNKNOWN' age, birthdate FROM peoples
RETURN umožňuje okamžitě opustit aktuální dotaz, dávku, nebo proceduru a vrátit se do nadřazeného bloku s návratovou hodnotou.
RETURN [ integer_expression ]
BEGIN SELECT firstname,surname FROM anytable IF (@@ROWCOUNT = 0) RETURN UPDATE ... ... END
Umožňuje v cyklu vykonávat příkaz nebo dávku příkazů.
WHILE Boolean_expression { sql_statement | statement_block | BREAK | CONTINUE }
DECLARE @nomrows INT WHILE(SELECT @numrows=COUNT(*) FROM anytable WHERE columnA>10) BEGIN IF @numrows=1 BREAK UPDATE anytable SET columnA=(columnA+1) END
Umožňuje provádět skoky v dávkách SQL.
GOTO label
... UPDATE anytable SET columnA=1 IF @@ROWCOUNT = 0 GOTO endblock UPDATE anytable SET columnB=2 ... ... endblock: RETURN
Kurzory umožňují sekvenčně zpracovávat řádky dotazu. Někdy je zapotřebí zpracovávat řádky jeden po druhém a ne celou sadu a právě k tomu kurzory slouží. Nicméně pokud to není nezbytně zapotřebí je lépe použít jiné konstrukce. Kurzory mohou totiž způsobit při svém běhu některé problémy, jedním z nich je velká paměťová náročnost, kurzory mohou způsobit i vyčerpání veškeré dostupné paměti, další problém nám může způsobit zamykání prostředků, ale asi největším jejich nedostatkem je rychlost, MSSQL server je založen na práci se sadami řádků a ne se sekvenčním zpracováním, proto je práce se sadami vždy rychlejší. Někdy však vaše aplikace použití kurzorů vyžaduje a vy jejich funkcionalitu velmi oceníte.
Kurzor se definuje SQL dotazem, který vrací sadu řádků. Řádky se pak z něj dají brát postupně po jednom, nebo po blocích a to v obou směrech podle definice kurzoru. Když skončíte práci s kurzorem je nutné ho explicitně uzavřít a dealokovat, aby jste uvolnili veškeré prostředky, které kurzor alokoval. Pokud tak neučiníte kurzor zůstane v paměti a bude jí blokovat. Problém může také nastat pokud by jste alokovali dva stejně pojmenované kurzory (i když nejsou ve stejném bloku!), je lépe tomu předcházet a v každé proceduře pojmenovávat kurzor dle jeho funkce jiným názvem.
DECLARE cursor_name [ INSENSITIVE ] [ SCROLL ] CURSOR FOR select_statement [ FOR { READ ONLY | UPDATE [ OF column_name [ ,...n ] ] } ] [;] Transact-SQL Extended Syntax DECLARE cursor_name CURSOR [ LOCAL | GLOBAL ] [ FORWARD_ONLY | SCROLL ] [ STATIC | KEYSET | DYNAMIC | FAST_FORWARD ] [ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ] [ TYPE_WARNING ] FOR select_statement [ FOR UPDATE [ OF column_name [ ,...n ] ] ] [;]
DECLARE cursorABC CURSOR FOR SELECT columnA,columnB,columnC FROM anytable
OPEN { { [ GLOBAL ] cursor_name } | cursor_variable_name }
OPEN cursorABC
FETCH [ [ NEXT | PRIOR | FIRST | LAST | ABSOLUTE { n | @nvar } | RELATIVE { n | @nvar } ] FROM ] { { [ GLOBAL ] cursor_name } | @cursor_variable_name } [ INTO @variable_name [ ,...n ] ]
FETCH NEXT FROM cursorABC INTO @A,@B,@C
CLOSE { { [ GLOBAL ] cursor_name } | cursor_variable_name } DEALLOCATE { { [ GLOBAL ] cursor_name } | @cursor_variable_name }
CLOSE cursorABC DEALLOCATE cursorABC
-- deklarace proměnných DECLARE @A INT DECLARE @B VARCHAR(10) DECLARE @C DECIMAL(10,2) -- deklarace kurzoru DECLARE cursorABC CURSOR FORWARD_ONLY FOR SELECT columnA,columnB,columnC FROM anytable -- otevření kurzoru OPEN cursorABC -- načte řádek do kurzoru FETCH NEXT FROM cursorABC INTO @A,@B,@C -- cyklus pro zpracování všech řádek WHILE @@FETCH_STATUS = 0 BEGIN -- zde můžeme zpracovávat řádky print @A print @B print @C -- načte další řádek FETCH NEXT FROM cursorABC INTO @A,@B,@C END -- uzavření kurzoru CLOSE cursorABC -- dealokace kurzoru DEALLOCATE cursorABC
Uložené procedura je seskupení příkazů T-SQL do jediného objektu uloženého na SQL serveru. Když se procedura vykonává poprvé po nastartovaní instance SQL serveru, uloží se do cache plán optimálního přístupu k datům, server pak tento plán využívá při každém dalším spuštění, čímž může urychlit vykonání dotazu. To je jedna a ne jediná z výhod uložených procedur. Další výhodou je znovupoužitelnost kódu, namísto aby jste všude kde je potřeba definovali jednoúčelový blok příkazů je lepší využít uloženou proceduru. Pokud by se tento kód měl vykonávat v nějaké z vašich aplikací jejich výhody se znásobí. Pokud by se třeba změnilo uložení dat v tabulkách, musíte vaši aplikaci popřípadě více aplikací na všech místech přepsat a v horším případě i překompilovat, ale pokud používáte uloženou proceduru, stačí ji změnit a pro aplikace se v podstatě nic nestalo. Použití procedur je také z pohledu db administrátora bezpečnější než přímý přístup do tabulek.
CREATE { PROC | PROCEDURE } [schema_name.] procedure_name [ ; NUMBER ] [ { @parameter [ type_schema_name. ] data_type } [ VARYING ] [ = DEFAULT ] [ OUT | OUTPUT ] [READONLY] ] [ ,...n ] [ WITH <procedure_option> [ ,...n ] ] [ FOR REPLICATION ] AS { <sql_statement> [;][ ...n ] | <method_specifier> } [;] <procedure_option> ::= [ ENCRYPTION ] [ RECOMPILE ] [ EXECUTE AS Clause ] <sql_statement> ::= { [ BEGIN ] statements [ END ] } <method_specifier> ::= EXTERNAL NAME assembly_name.class_name.method_name
CREATE PROCEDURE [dbo].[MyProcedure] @pid INT,@other INT,@sometext VARCHAR(100) AS IF ISNULL(@pid,0)=0 RETURN EXEC ExecuteAnotherProcedure @pid IF ISNULL(@other ,0)<>0 BEGIN DECLARE @a INT SELECT @a=a FROM anytable WHERE a=@pid AND b=@other UPDATE sometable SET a=@pid WHERE b=@other END
-- úprava procedury ALTER { PROC | PROCEDURE } [schema_name.] procedure_name [ ; NUMBER ] ... same AS CREATE -- odstranění procedury DROP { PROC | PROCEDURE } { [ schema_name. ] PROCEDURE } [ ,...n ]
CREATE PROCEDURE [dbo].[MyProcedure] WITH ENCRYPTION AS ...
CREATE FUNCTION [ schema_name. ] function_name ( [ { @parameter_name [ AS ][ type_schema_name. ] parameter_data_type [ = DEFAULT ] [ READONLY ] } [ ,...n ] ] ) RETURNS return_data_type [ WITH <function_option> [ ,...n ] ] [ AS ] BEGIN function_body RETURN scalar_expression END [ ; ]
CREATE FUNCTION dbo.udf_DayOfWeek(@dtDate DATETIME) RETURNS VARCHAR(10) AS BEGIN DECLARE @rtDayofWeek VARCHAR(10) SELECT @rtDayofWeek = CASE DATEPART(weekday,@dtDate) WHEN 1 THEN 'Monday' WHEN 2 THEN 'Tuesday' WHEN 3 THEN 'Wednesday' WHEN 4 THEN 'Thursday' WHEN 5 THEN 'Friday' WHEN 6 THEN 'Saturday' WHEN 7 THEN 'Sunday' END RETURN (@rtDayofWeek) END GO -- použití funkce SELECT udf_DayOfWeek(getdate())
CREATE FUNCTION [ schema_name. ] function_name ( [ { @parameter_name [ AS ] [ type_schema_name. ] parameter_data_type [ = DEFAULT ] [ READONLY ] } [ ,...n ] ] ) RETURNS TABLE [ WITH <function_option> [ ,...n ] ] [ AS ] RETURN [ ( ] select_stmt [ ) ] [ ; ]
CREATE FUNCTION dbo.GetByCity(@city VARCHAR(100)) RETURNS TABLE AS RETURN ( SELECT * FROM peoples WHERE city=@city ( GO -- použití funkce SELECT * FROM GetByCity('Prague')
CREATE FUNCTION [ schema_name. ] function_name ( [ { @parameter_name [ AS ] [ type_schema_name. ] parameter_data_type [ = DEFAULT ] [READONLY] } [ ,...n ] ] ) RETURNS @return_variable TABLE <table_type_definition> [ WITH <function_option> [ ,...n ] ] [ AS ] BEGIN function_body RETURN END [ ; ]
BEGIN TRY { sql_statement | statement_block } END TRY BEGIN CATCH [ { sql_statement | statement_block } ] END CATCH [ ; ]
BEGIN TRY INSERT anyTable (columnA,columnB,columnC) VALUES (1,2,3) END TRY BEGIN CATCH print 'Chyba při vykonávání příkazu!' END CATCH
sp_addmessage [ @msgnum = ] msg_id , [ @severity = ] severity , [ @msgtext = ] 'msg' [ , [ @lang = ] 'language' ] [ , [ @with_log = ] { 'TRUE' | 'FALSE' } ] [ , [ @REPLACE = ] 'replace' ] -- úprava vlastní vyjímky sp_altermessage [ @message_id = ] message_number , [ @parameter = ] 'write_to_log' , [ @parameter_value = ] 'value' -- odstraněni vyjímky sp_dropmessage [ @msgnum = ] message_number [ , [ @lang = ] 'language' ]
EXEC sp_addmessage 123456, 20, N'Došlo k mojí vyjímce!'
RAISERROR ( { msg_id | msg_str | @local_variable } { ,severity ,state } [ ,argument [ ,...n ] ] ) [ WITH OPTION [ ,...n ] ]
RAISERROR (123456, 20, 1, N'any.anytable' )