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

Tento výukový obsah pomáhají rozvíjet následující firmy, které dost možná hledají právě tebe!

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]edek 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.