Lekce 14 - MS-SQL - Uložené procedury

V minulé lekci, MS-SQL - Transakce, jsme se věnovali transakcím. V dnešním MS-SQL tutoriálu se naučíme používat uložené procedury.

Úvod

V tomto článku se zaměříme na uložené procedury na MS-SQL. Procedury nejsou nic jiného, než uložené části kódu, který můžeme použít znovu a ušetříme čas při jejich vytváření. Pouze jednoduše zavoláme uloženou proceduru. Výhodou je, že v procedurách můžeme použít parametry a tím kód modifikovat.

Vytvoření procedury

Proceduru vytvoříme pomocí T-SQL.

Příklad: Chceme často volat příkaz, který vypíše seznam adres pro nějaké jméno:

SELECT * FROM Adresy WHERE Adresy.Name='Novak'

K vytvoření procedury použijeme příkaz CREATE

USE MojeDatabaze
GO
CREATE PROCEDURE dbo.VyberAdresy
AS
SELECT * FROM Adresy
WHERE Adresy.Name='Novak'
GO

Procedura se po vytvoření uloží do databáze do adresáře Programmability.

Spuštění procedury

Proceduru lze po vytvoření opakovaně spustit příkazem EXEC.

EXEC dbo.VyberAdresy
-- nebo
EXEC VyberAdresy
-- nebo jednoduše
VyberAdresy

Vytvoření procedury s parametrem

Někdy může být výhodné, když procedura obsahuje parametry, které můžeme při zavolání měnit. Například parametr pro zvolené jméno. Parametrů může být několik a oddělují se čárkou. Proceduru s parametrem vytvoříme takto.

CREATE PROCEDURE dbo.VyberAdresy @Jmeno nvarchar(30)
AS
SELECT * FROM Adresy
WHERE Adresy.Name[email protected]
GO

Volání procedury pak probíhá následovně

EXEC dbo.VyberAdresy @Jmeno='Novak'

Parametr s výchozí hodnotou

Je dobrým zvykem definovat výchozí hodnotu parametru, aby nedocházelo k chybám, pokud zavoláte proceduru bez parametru. Tato procedura pak proběhne bez chyb, jako kdyby byl parametr standardně zadán s hodnotou NULL.

CREATE PROCEDURE dbo.VyberAdresy @Jmeno nvarchar(30) = NULL
AS
SELECT * FROM Adresy
WHERE Adresy.Name[email protected]
GO

Procedura s výstupním parametrem

Nastavení výstupního parametru je prakticky stejné, jako nastavení vstupního, s tím rozdílem, že použijete direktivu OUTPUT nebo jenom OUT. Samozřejmě má smysl mít pouze jeden výstupní parametr.

CREATE PROCEDURE dbo.PocetAdres @Pocet int OUTPUT
AS
SELECT count(*) FROM Adresy
GO

Volání procedury potom vypadá takto

DECLARE @Vysledek
EXEC dbo.PocetAdres @[email protected] OUTPUT
-- nebo
EXEC dbo.PocetAdres @Vysledek OUTPUT

Použití TRY – CATCH v procedurách

Pokud neznáte Try-Catch, tak jde v podstatě o dva bloky kódu. První blok se provádí a dokončí, pokud nedojde k nějaké chybě. Pokud ano, přejde program na druhý blok kódu. Například, když potřebujeme zachytit a popsat nějakou chybu, ke které dojde v průběhu hlavního bloku programu. Používá se třeba při ladění kódu.

CREATE PROCEDURE dbo.TryCatch
AS
BEGIN TRY
-- hlavní blok kódu
SELECT 1/0 -- zde vznikne chyba při dělení nulou
END TRY
BEGIN CATCH
-- blok kódu, který se provede při chybě v hlavním programu
SELECT ERROR_NUMBER() AS ErrorNumber
     ,ERROR_MESSAGE() AS ErrorMessage;
END CATCH

A výsledek:

Komentáře v procedurách

V procedurách můžeme použít dva druhy komentářů. Komentář začíná znaky -- nebo je mezi /* a */

-- toto je řádkový komentář
/*
Toto je blokový komentář
*/

Druhy komentářů lze libovolně kombinovat.

Změna uložené procedury

Proceduru můžeme změnit příkazem ALTER

ALTER PROCEDURE dbo.VyberAdresy @Jmeno nvarchar(30) = NULL
AS
SELECT * FROM Adresy
WHERE Adresy.Name  LIKE @Jmeno + '%'

Smazání uložené procedury

Proceduru je možné smazat pomocí příkazu DROP

DROP PROCEDURE dbo.VyberAdresy

V příští lekci, MS-SQL - Datové typy podrobněji, se podíváme podrobněji na datové typy v MS-SQL databázi.