Table of Contents

Techniky T-SQL

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…

Podmíněné zpracování

IF, ELSE

Struktura IF, ELSE umožňuje vyhodnotit logický výraz a dle jeho výsledku vykonat dotaz.

Syntax:
IF Boolean_expression { sql_statement | statement_block } 
        [ ELSE { sql_statement | statement_block } ] 
Příklad:
DECLARE @Condition INT
SET @Condition = 250
 
IF @Condition > 100
  BEGIN
    SELECT TOP 100 * FROM TABLE
  END
ELSE
  BEGIN
    SELECT TOP @Condition * FROM TABLE
  END

CASE

Funkce CASE umožňuje vracet hodnotu na základě vyhodnocení jednoho, nebo více výrazů.

Syntax:
CASE input_expression 
     WHEN when_expression THEN result_expression [ ...n ] 
     [ ELSE else_result_expression ] 
END
Příklad
SELECT firstname, surname,
CASE gender
  WHEN 'male' THEN 'M'
  WHEN 'female' THEN 'F'
ELSE 'UNKNOWN'
age, birthdate FROM peoples

Řízení toku

RETURN

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.

Syntax:
RETURN [ integer_expression ] 
Příklad:
BEGIN
  SELECT firstname,surname FROM anytable
  IF (@@ROWCOUNT = 0) RETURN
  UPDATE ...
  ...
END

WHILE

Umožňuje v cyklu vykonávat příkaz nebo dávku příkazů.

Syntax:
WHILE Boolean_expression 
     { sql_statement | statement_block | BREAK | CONTINUE } 
Příklad:
DECLARE @nomrows INT
WHILE(SELECT @numrows=COUNT(*) FROM anytable WHERE columnA>10)
BEGIN
  IF @numrows=1 BREAK
  UPDATE anytable SET columnA=(columnA+1)
END

GOTO

Umožňuje provádět skoky v dávkách SQL.

Syntax:
GOTO label
Příklad:
...
UPDATE anytable SET columnA=1
IF @@ROWCOUNT = 0 GOTO endblock
UPDATE anytable SET columnB=2
...
...
endblock:
RETURN

Kurzory

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.

Životní cyklus kurzoru

  1. deklarace kurzoru
  2. otevření kurzoru
  3. práce s řádky
  4. uzavření kurzoru
  5. dealokace kurzoru

Deklarace kurzoru

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 ] ] ]
[;]
Příklad:
DECLARE cursorABC CURSOR
FOR SELECT columnA,columnB,columnC FROM anytable

Otevření kurzoru

Syntax:
OPEN { { [ GLOBAL ] cursor_name } | cursor_variable_name }
Příklad:
OPEN cursorABC 

Práce s řádky

Syntax:
FETCH 
          [ [ NEXT | PRIOR | FIRST | LAST 
                    | ABSOLUTE { n | @nvar } 
                    | RELATIVE { n | @nvar } 
               ] 
               FROM 
          ] 
{ { [ GLOBAL ] cursor_name } | @cursor_variable_name } 
[ INTO @variable_name [ ,...n ] ] 
Příklad:
FETCH NEXT FROM cursorABC INTO @A,@B,@C

Zavření a dealokace kurzoru

Syntax:
CLOSE { { [ GLOBAL ] cursor_name } | cursor_variable_name }
 
DEALLOCATE { { [ GLOBAL ] cursor_name } | @cursor_variable_name }
Příklad:
CLOSE cursorABC 
DEALLOCATE cursorABC 

Komplexní příklad:

-- 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é procedury

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.

Vytvoření uložené procedury

Syntax:
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
Příklad:
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 a odstranění uložené procedury

Syntax:
-- úprava procedury
ALTER { PROC | PROCEDURE } [schema_name.] procedure_name [ ; NUMBER ] 
...
same AS CREATE
-- odstranění procedury
DROP { PROC | PROCEDURE } { [ schema_name. ] PROCEDURE } [ ,...n ]

Zašifrování uložené procedury

Syntax:
CREATE PROCEDURE [dbo].[MyProcedure]
WITH ENCRYPTION
AS
...

Uživatelsky definované funkce

Skalární uživatelsky definované funkce

Syntax:
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
[ ; ]
Příklad:
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())

Přímé tabulkové uživatelsky definované funkce

Syntax:
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 [ ) ]
[ ; ]
Příklad:
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')

Vícepříkazové uživatelsky definované funkce

Syntax:
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
[ ; ]
Příklad:
 

Odchytávání vyjímek

Funkce poskytující informace o chybách

Konstrukce TRY...CATCH

Syntax:
BEGIN TRY
     { sql_statement | statement_block }
END TRY
BEGIN CATCH
          [ { sql_statement | statement_block } ]
END CATCH
[ ; ]
Příklad:
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

Vytváření vlastních vyjímek

Syntax:
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' ]
Příklad:
EXEC sp_addmessage 123456, 20, N'Došlo k mojí vyjímce!'

Vyvolávání vlastních vyjímek

Syntax:
RAISERROR ( { msg_id | msg_str | @local_variable }
    { ,severity ,state }
    [ ,argument [ ,...n ] ] )
    [ WITH OPTION [ ,...n ] ]
Příklad:
RAISERROR (123456, 20, 1, N'any.anytable' )

Zdroje