14. diel - MS-SQL - Uložené procedúry

V minulej lekcii, MS-SQL - Transakcia , sme sa venovali transakciám.

V dnešnom MS-SQL tutoriálu sa naučíme používať uložené procedúry.

Úvod

V tomto článku sa zameriame na uložené procedúry na MS-SQL. Procedúry nie sú nič iné, než uložené časti kódu, ktorý môžeme použiť znovu a ušetríme čas pri ich vytváraní. Iba jednoducho zavoláme uloženú procedúru. Výhodou je, že v procedúrach môžeme použiť parametre a tým kód modifikovať.

Vytvorenie procedúry

Procedúru vytvoríme pomocou T-SQL.

Príklad: Chceme často volať príkaz, ktorý vypíše zoznam adries pre nejaké meno:

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

K vytvoreniu procedúry použijeme príkaz CREATE

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

Procedúra sa po vytvorení uložia do databázy do adresára Programmability.

Spustenie procedúry

Procedúru možno po vytvorení opakovane spustiť príkazom EXEC.

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

Vytvorenie procedúry s parametrom

Niekedy môže byť výhodné, keď procedúra obsahuje parametre, ktoré môžeme pri zavolaní meniť. Napríklad parameter pre zvolené meno. Parametrov môže byť niekoľko a oddeľujú sa čiarkou. Procedúru s parametrom vytvoríme takto.

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

Volanie procedúry potom prebieha nasledovne

EXEC dbo.VyberAdresy @Jmeno='Novak'

Parameter s východiskovou hodnotou

Je dobrým zvykom definovať predvolenú hodnotu parametra, aby nedochádzalo k chybám, ak zavoláte procedúru bez parametra. Táto procedúra potom prebehne bez chýb, ako keby bol parameter štandardne zadaný s hodnotou NULL.

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

Procedúra s výstupným parametrom

Nastavenie výstupného parametra je prakticky rovnaké, ako nastavenie vstupného, s tým rozdielom, že použijete direktívu OUTPUT alebo len OUT. Samozrejme má zmysel mať iba jeden výstupný parameter.

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

Volanie procedúry potom vyzerá takto

DECLARE @Vysledek
EXEC dbo.PocetAdres @Pocet=@Vysledek OUTPUT
-- nebo
EXEC dbo.PocetAdres @Vysledek OUTPUT

Použitie TRY - CATCH v procedúrach

Ak nepoznáte Try-Catch, tak ide v podstate o dva bloky kódu. Prvý blok sa vykonáva a dokončí, pokiaľ nedôjde k nejakej chybe. Ak áno, prejde program na druhý blok kódu. Napríklad, keď potrebujeme zachytiť a popísať nejakú chybu, ku ktorej dôjde v priebehu hlavného bloku programu. Používa sa napríklad pri ladení 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ýsledok:

Komentáre v procedúrach

V procedúrach môžeme použiť dva druhy komentárov. Komentár začína znaky - alebo je medzi / * a * /

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

Druhy komentárov možno ľubovoľne kombinovať.

Zmena uložené procedúry

Procedúru môžeme zmeniť príkazom ALTER

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

Zmazanie uložené procedúry

Procedúru je možné zmazať pomocou príkazu DROP

DROP PROCEDURE dbo.VyberAdresy

V budúcej lekcii, MS-SQL - Dátové typy podrobnejšie , sa pozrieme podrobnejšie na dátové typy v MS-SQL databáze.