Ve standardním SQL server obdrží a vyhodnotí pouze jednotlivý SQL příkaz (SELECT, INSERT, UPDATE, …), jehož výsledek pak vrátí zpátky klientovi. Tj. jedná se o neprocedurální jazyk. V praxi to to znamená, že příkazy jazyka SQL se vykonávají sekvenčně, bez možnosti použití klasických programátorských konstrukcí, jako jsou například cykly, podmínky, využití procedur a funkcí a podobně.
Proto má téměř každá moderní databázová platforma implementované určité procedurální rozšíření jazyka SQL. Procedurální rozšíření nebyla dlouhou dobu standardizována (až do normy SQL 99), proto existuje velké množství procedurálních rozšíření (každý databázový systém má obvykle vlastní). Funkčnost jednotlivých procedurálních rozšíření bývá přibližně stejná, liší se však v syntaxi.
Procedurální rozšíření jazyka SQL na platformě Microsoft SQL Serveru má název Transact SQL, zkráceně T-SQL.
Při použití procedurálního rozšíření šetříme komunikační kanál. Místo velkého množství paketů se odesílá pouze jeden paket. Rovněž zvyšujeme rychlost provedení, protože odpadá zdržení vzniklé síťovou komunikací. Na klientovi snižujeme výpočetní náročnost, kterou naopak přenášíme na server, kde se nároky na systémové prostředky zvýší. Kód procedur může být navíc uložen na serveru, a může být ta využit všemi klientskými aplikacemi.
Povolené manipulace s daty jsou pomocí procedurálního rozšíření zapouzdřena do procedur (a funkcí). Uživatelské aplikace nemusejí mít přímo práva k celým tabulkám, ale pouze k těmto procedurám.
Zdrojový kód v Transact SQL je tvořen posloupností příkazů, které nemusí (ale mohou) být ukončeny středníkem. Několik příkazů uzavřených mezi klíčová slova BEGIN a END se považují za jeden (složený) příkaz. Příkazem Transact SQL je i libovolný SQL příkaz.
Za jeden příkaz se považuje i celá sekce DECLARE, která
slouží pro deklaraci proměnných.
Syntaxe deklarace proměnné:
DECLARE @jmeno_promene datovy_typ
Nabídka podporovaných typů je u jazyka T-SQL velmi bohatá. K dispozici je vždy několik datových typů různého rozsahu pro textové proměnné, číselné proměnné, datum a čas, jednotky peněžní měny, logické proměnné a binární proměnné.
Celá syntaktická definice příkazu declare:
DECLARE
{
{ @local_variable [AS] data_type | [ = value ] }
| { @cursor_variable_name CURSOR }
} [,...n]
| { @table_variable_name [AS] <table_type_definition> }
Syntaktická definice podmínky:
<table_type_definition> ::=
TABLE ( { <column_definition> | <table_constraint> } [ ,... ] )
<column_definition> ::=
column_name { scalar_data_type | AS computed_column_expression }
[ COLLATE collation_name ]
[ [ DEFAULT constant_expression ] | IDENTITY [ (seed ,increment ) ] ]
[ ROWGUIDCOL ]
[ <column_constraint> ]
<column_constraint> ::=
{ [ NULL | NOT NULL ]
| [ PRIMARY KEY | UNIQUE ]
| CHECK ( logical_expression )
| WITH ( <index_option > )
}
<table_constraint> ::=
{ { PRIMARY KEY | UNIQUE } ( column_name [ ,... ] )
| CHECK ( search_condition )
}
<index_option> ::= See CREATE TABLE for index option syntax.
Z definice příkazu DECLARE plyne, že s ním lze kromě deklarace lokálních proměnných deklarovat i proměnné typu kurzor, který je popsán dále, a proměnné typu tabulka.
Po deklaraci jsou všechny proměnné inicializovány na NULL, není-li definována jiná hodnota.
Příklad:
DECLARE
@podil FLOAT
SELECT
@podil = citatel/jmenovatel
FROM Zlomky
WHERE citatel=123
IF @podil
> 0
UPDATE Zlomky SET Vysledek = @podil WHERE citatel=123
V sekci DECLARE deklarujeme proměnnou @podíl typu reálné číslo. Dalším příkazem je příkaz SELECT, ve kterém do proměnné @podíl dosadíme hodnotu aritmetického výrazu spočteného hodnot sloupců tabulky Zlomky, ze které byl vybrán právě jeden řádek. Třetím příkazem je podmínka IF, které otestuje hodnotu proměnné @podíl. V případě splnění podmínky se vykoná příkaz UPDATE, který modifikuje hodnotu sloupce Výsledek v tabulce Zlomky pro vybraný řádek.
Hodnoty proměnným lze nastavovat dvěma způsoby. Prvním způsobem je samostatný příkaz SET, který se skládá z klíčového slova SET následovaného názvem proměnné, operátorem = a novou hodnotou proměnné (například SET @x = 5). Druhou možností jak nastavit hodnotu proměnné je vyžití příkazu SELECT, jak jsme viděli v příkladu. Pro jednoznačnost přiřazení hodnoty proměnné by příkaz SELECT by měl vracet právě jednu řádku.
Podmínka
IF Boolean_expression
{ sql_statement | statement_block }
[ ELSE
{ sql_statement | statement_block } ]
Cyklus
WHILE Boolean_expression
{ sql_statement | statement_block | BREAK | CONTINUE }
Příkaz GO není Transact SQL příkazem, ale odděluje od sebe jednotlivé bloky Transact SQL příkazů. Po uvedení příkazu GO je blok příkazu odeslán na server a tam vyhodnocen. V dalším bloku příkazů nelze využít proměnné deklarované v předchozím bloku, protože vykonáním předchozího bloku tyto proměnné zanikly.
Uložené procedury jsou uložené přímo v databázi spolu s ostatními objekty. V praxi to tedy znamená, že do databáze je možné umístit kromě údajů i část, případně i celou aplikační logiku na zpracování těchto údajů. To umožňuje nejen jednodušší distribuci a aplikace, ale přispívá to i ke spolehlivosti, protože aplikační logika je zabezpečena a zálohována stejně spolehlivě jako samotné údaje. Uložené procedury se ukládají do databáze v předkompilované podobě.
--Transact-SQL Stored Procedure 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 { [ BEGIN ] sql_statement [;] [ ...n ] [ END ] }
[;]
<procedure_option> ::=
[ ENCRYPTION ]
[ RECOMPILE ]
[ EXECUTE AS Clause ]
Procedura se volá příkazem EXEC následovaného jménem procedury a jejími parametry navzájem oddělenými čárkou. Parametry volané procedury nejsou uzavřeny v závorkách. Parametry lze předat buďto podle jejich pořadí uvedeném při definici procedury nebo pomocí jména parametru a k němu přiřazené hodnoty pomocí operátoru =.
Příklad:
CREATE PROCEDURE UpdEmpSal
@xEmpNo INTEGER,
@xSal INTEGER,
@xComm INTEGER = NULL
AS
UPDATE Emp SET
Sal = @xSal,
Comm = @xComm
WHERE EmpNo=@xEmpNo;
EXEC UpdEmpSal @xEmpNo = 1234, @xSal = 15500;
GO
EXEC UpdEmpSal 4321, 14000,
500;
GO
Funkce je speciálním případem uložené procedury, která však na rozdíl od procedury vrátí bloku, ve kterém byla zavolána, nějakou hodnotu. Tato hodnota se získá nebo vypočítá v těle funkce. Funkce může vracet buď skalární hodnotu nebo tabulku, která může být vrácena dvěma způsoby. Jedná se pak o jednoduché tabelární funkce a vícepříkazové tabelární funkce.
--Transact-SQL Scalar Function 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
[ ; ]
Jednoduché tabelární funkce vrací objekt typu table, který je vyvolá jedním
příkazem SELECT.
Pro jednoduché tabelární funkce bude návratová část vypadat následovně:
RETURNS TABLE
[ WITH <function_option> [ ,...n ] ]
[ AS ]
RETURN [ ( ] select_stmt [ ) ]
[ ; ]
Vícepříkazové tabelární funkce vrátí objekt typu table. Na rozdíl od
jednoduchých tabelárních funkcí mohou obsahovat více příkazů T-SQL a umožňují
tak sestavit komplexnější uživatelské funkce.
Syntax návratové části :
RETURNS @return_variable TABLE <table_type_definition>
[ WITH <function_option> [ ,...n ] ]
[ AS ]
BEGIN
function_body
RETURN
END
[ ; ]
Funkce se volá v příkazu SELECT. Před názvem funkce fce musí být uveden prefix dbo (příklad dbo.fce), pro odlišení od vestavěných funkcí. Volaná funkce musí mít parametry uzavřené v kulatých závorkách. Kulaté závorky nesmějí chybět ani u bezparametrické funkce. Parametry lze opět předávat podle pořadí nebo podle jména.
Příklad:
CREATE FUNCTION Polynom2 (
@x FLOAT,
@a FLOAT = 0,
@b FLOAT = 0,
@c FLOAT = 0
) RETURNS FLOAT
AS
BEGIN
RETURN (@a*@x+@b)*@x+@c;
END;
GO
SELECT Polynom2(1,
@b=2);
-- 2*x, x=1, tj. 2
SELECT Polynom2(2,
@b=3, @c=5);
-- 3*x+5, x=2, tj. 11
SELECT Polynom2(2,
1, 1, 1);
-- x2+x+1, x=2, tj. 7
Kurzory slouží ke zpracování víceřádkového výsledku SELECT příkazu. Kurzor si lze představit jako ukazatel na konkrétní řádek z výsledku dotazu, který můžeme v daný okamžik zpracovávat nezávisle na ostatních řádcích výsledku dotazu. Obvykle se kurzor používá k postupnému projití (od prvního do posledního) všech řádků výsledku dotazu.
Při práci s kurzory je nutno dodržet následující posloupnost kroků:
1.
Deklarace kurzoru
Deklarace se sestává ze dvou částí. Nejprve se deklaruje jméno kurzoru a
následně SQL dotaz.
Syntaxe příkazu pro deklaraci kurzoru je:
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 ] ] ]
[;]
2.
Otevření kurzoru
V této etapě se vykoná SQL dotaz zadaný v etapě
deklarace. Odteď jsou k dispozici údaje, které jsou výsledkem výběru SQL dotazu:
OPEN { { [ GLOBAL ] cursor_name } | cursor_variable_name }
3.
Výběr údajů prostřednictvím kurzoru
K výběru údajů slouží příkaz FETCH, který načítá údaj
z kurzoru a uloží ho do proměnných. Po každém výběru údajů se kurzor automaticky
přesune na následující záznam. Syntaxe:
FETCH
[ [ NEXT | PRIOR | FIRST | LAST
| ABSOLUTE { n | @nvar }
| RELATIVE { n | @nvar }
]
FROM
]
{ { [ GLOBAL ] cursor_name } | @cursor_variable_name }
[ INTO @variable_name [ ,...n ] ]
Pokud není kurzor definován pomocí klíčového slova SCROLL, tak lze použít pouze variantu NEXT, která je zároveň defaultní variantou příkazu FETCH.
4.
Zavření kurzoru
Zavře otevřený kurzor uvolněním současné sady výsledků
a zámků na řádcích, kde je kurzor umístěn. Zavření kurzoru nechá datové
struktury dostupné pro opětovné otevření, ale výběr a změna údajů není možná,
dokud nebude znovu otevřen. Zavření kurzoru může být provedeno jen nad otevřeným
kurzorem (tj. není možné uzavřít pouze deklarovaný nebo již uzavřený kurzor).
Syntax:
CLOSE { { [ GLOBAL ] cursor_name } | cursor_variable_name }
5.
Dealokace kurzoru
Odstraní referenci na kurzor. Pokud je odstraněna poslední reference na kurzor,
pak dojde k uvolnění datových struktur(systémových zdrojů), které kurzor
využíval. Syntax:
DEALLOCATE { { [ GLOBAL ] cursor_name } | @cursor_variable_name }
Příklad:
DECLARE
C CURSOR FOR
SELECT *
FROM EMP;
OPEN C;
FETCH NEXT FROM C;
WHILE @@FETCH_STATUS=0
BEGIN
…
FETCH NEXT FROM C;
END;
CLOSE C;
DEALLOCATE C;
@@FETCH_STATUS udává, zda příkaz fetch vrátil řádek.
Výjimky slouží k ošetření výjimečných stavů při vykonávání SQL příkazů. K výjimkám dochází velmi často a je nutné je korektně obsloužit. Výjimky mohou být generované serverem (například dělení nulou), nebo definovány a vyvolány programátorem. Pomocí mechanizmu výjimek nelze odchytávat kompilační chyby, varování a některé velmi závažné chyby.
Typy výjimek:
1. Určené k ošetření na serveru či v klientské aplikaci
• Standardní pojmenované výjimky
– Generované serverem, mají přidělené jméno
• Standardní nepojmenované výjimky
– Generované serverem, nemají přidělené jméno
2. Určené k ošetření na serveru
• Uživatelské vnitřní výjimky
– Definované uživatelem, mají jméno, nemají popis
3. Určené k ošetření v klientské aplikaci
• Uživatelské aplikační výjimky
– Definované uživatelem, nemají jméno, mají popis
Část zdrojového kódu, která může vyvolat výjimku, by měla být uzavřena v TRY bloku (BEGIN TRY, END TRY). Poté následuje CATCH blok (BEGIN CATCH, END CATCH), ve kterém se zjišťuje, jaká výjimka nastala a podle typu výjimky proběhne její ošetření.
Pro zjištění typu chyby je možno použít funkci ERROR_NUMBER, pro zjištění její závažnosti funkci ERROR_SEVERITY. Funkce ERROR_LINE určí číslo řádky, na které došlo k chybě. Funkce ERROR_MESSAGE vrátí text chybového hlášení.
Příklad:
BEGIN TRY
SELECT 1/0; -- zero divide …
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_STATE() AS ErrorState,
ERROR_PROCEDURE() AS ErrorProcedure,
ERROR_LINE() AS ErrorLine,
ERROR_MESSAGE() AS ErrorMessage;
END CATCH;
GO
Programové bloky napsané v jazyku T-SQL, například uložené procedury a spouště, hlásí chyby a výjimky pomocí funkce RAISERROR. Této funkci se jako parametr předává číslo chyby (větší než 50 000) nebo text chybového hlášení. Dalšími argumenty jsou závažnost chyby (vhodné volit mezi 11 až 18) a stav chyby (upřesnění jak chyba vznikla).
Syntax:
RAISERROR ( { msg_id | msg_str | @local_variable }
{ ,severity ,state }
[ ,argument [ ,...n ] ] )
[ WITH option [ ,...n ] ]
Jednotlivé zprávy lze předpřipravit pomocí sp_addmessage a poté se odkazovat jen pomocí msg_id. msg_str může obsahovat odkazy na parametry, podobně jako C-funkce printf, hodnoty předány v argumentech. Úroveň závažnosti(severity) od 1 do 15 nevytváří hlášení do systémového protokolu. Úroveň 16 zapíše varování a úrovně 17 až 25 zapíšou do systémového protokolu chybu. Úrovně od 1 do 18 může zadávat libovolný uživatel, vyšší úrovně jen administrátor.