Zvažuje se situace, kdy uložené procedury mohou snížit výkon dotazu.
Při kompilaci uložených procedur v MS SQL Server 2000 jsou uložené procedury umístěny do mezipaměti procedur, což může zlepšit výkon během jejich provádění eliminací potřeby analýzy, optimalizace a kompilace kódu uložené procedury.
Na druhou stranu existují úskalí při ukládání zkompilovaného kódu uložené procedury, která mohou mít opačný efekt.
Faktem je, že při kompilaci uložené procedury se zkompiluje prováděcí plán těch operátorů, které tvoří kód procedury, respektive, pokud je zkompilovaná uložená procedura uložena do mezipaměti, pak se její prováděcí plán uloží do mezipaměti, a proto uložená procedura nebude být optimalizován pro konkrétní situaci a parametry dotazu.
Udělá malý experiment, který to demonstruje.
KROK 1... Vytvoření databáze.
Vytvořme samostatnou databázi pro experiment.
CREATE DATABASE test_sp_perf
ON (NAME = "test_data", FILENAME = "c: \ temp \ test_data", SIZE = 1, MAXSIZE = 10, FILEGROWTH = 1 Mb)
PŘIHLÁSIT SE (NAME = "test_log", FILENAME = "c: \ temp \ test_log", SIZE = 1, MAXSIZE = 10, FILEGROWTH = 1 Mb)
KROK 2. Vytvoření tabulky.
CREATE TABLE sp_perf_test (column1 int, column2 char (5000))
KROK 3. Vyplnění tabulky testovacími čarami. Do tabulky jsou záměrně přidány duplicitní řádky. 10 000 řádků očíslovaných od 1 do 10 000 a 10 000 řádků očíslovaných 50 000.
DECLARE @i int
SET @ i = 1
Zatímco já<10000)
ZAČÍT
INSERT INTO sp_perf_test (sloupec1, sloupec2) HODNOTY (@i, "Testovací řetězec #" + CAST (@i jako znak (8)))
INSERT INTO sp_perf_test (sloupec1, sloupec2) VALUES (50000, "Testovací řetězec #" + CAST (@i jako znak (8)))
SET @ i = @ i + 1
KONEC
VYBERTE POČET (*) FROM sp_perf_test
Jít
KROK 4. Vytvořte index bez klastrů. Vzhledem k tomu, že plán provádění je uložen do mezipaměti s procedurou, bude index použit stejný pro všechna volání.
VYTVOŘIT NENCLUSTEROVANÝ INDEX CL_perf_test ON sp_perf_test (sloupec1)
Jít
KROK 5. Vytvoření uložené procedury. Procedura jednoduše provede příkaz SELECT s podmínkou.
VYTVOŘIT PROC proc1 (@param int)
TAK JAKO
SELECT sloupec1, sloupec2 FROM sp_perf_test WHERE [e-mail chráněný]
Jít
KROK 6. Spuštění uložené procedury. Při spouštění zranitelné procedury se speciálně používá selektivní parametr. V důsledku postupu získáme 1 řádek. Prováděcí plán označuje použití indexu bez klastrů, protože dotaz je selektivní a je nejlepším způsobem, jak načíst řádek. Procedura optimalizovaná pro načtení jednoho řádku je uložena v mezipaměti procedur.
EXEC proc1 1234
Jít
KROK 7. Spuštění uložené procedury s neselektivním parametrem. Jako parametr je použita hodnota 50 000. Řádky s takovou hodnotou prvního sloupce cca 10 000, s použitím neshlukovaného indexu a operace vyhledávání záložek je neúčinná, ale protože zkompilovaný kód s plánem provádění je uložen v procedurální cache, bude použita. Prováděcí plán to ukazuje, stejně jako skutečnost, že operace vyhledávání záložek byla provedena pro 9999 řádků.
EXEC proc1 50 000
Jít
KROK 8. Načtení řádků s prvním polem rovným 50 000. Provedení samostatného dotazu optimalizuje a zkompiluje dotaz se specifickou hodnotou prvního sloupce. V důsledku toho optimalizátor dotazů zjistí, že pole je mnohokrát duplikováno, a rozhodne se použít operaci skenování tabulky, což je v tomto případě mnohem efektivnější než použití indexu bez klastrů.
SELECT sloupec1, sloupec2 FROM sp_perf_test WHERE sloupec1 = 50000
Jít
Můžeme tedy dojít k závěru, že použití uložených procedur nemusí vždy zlepšit výkon dotazů. Měli byste být velmi opatrní ohledně uložených procedur, které pracují s výsledky s proměnným počtem řádků a používají různé plány provádění.
Skript můžete použít k opakování experimentu na čerstvém MS SQL serveru.
Typy uložených procedur
SQL Server má několik typů uložené procedury.
- Systémový uložené procedury jsou určeny k provádění různých administrativních akcí. Téměř všechny akce správy serveru se provádějí s jejich pomocí. Můžeme říci, že systém uložené procedury jsou rozhraní poskytující práci se systémovými tabulkami, které se v konečném důsledku scvrkává na změnu, přidávání, mazání a načítání dat ze systémových tabulek uživatelských i systémových databází. Systémový uložené procedury mají předponu sp_, jsou uloženy v systémové databázi a lze je volat v kontextu jakékoli jiné databáze.
- Zvyk uložené procedury provádět určité akce. Uložené procedury- plnohodnotný databázový objekt. V důsledku toho každý uložené procedury se nachází v konkrétní databázi, kde se spouští.
- Dočasný uložené procedury existují pouze po určitou dobu, poté jsou serverem automaticky zničeny. Dělí se na lokální a globální. Místní dočasné uložené procedury lze volat pouze ze spojení, ve kterém byly vytvořeny. Když vytvoříte takový postup, musíte mu dát název, který začíná jedním znakem #. Jako všechny dočasné předměty, uložené procedury tohoto typu jsou automaticky odstraněny, když uživatel odpojí, restartuje nebo zastaví server. Globální dočasné uložené procedury dostupné pro všechna připojení k serveru, která mají stejný postup. Chcete-li ji definovat, stačí ji pojmenovat začínající znaky ##. Tyto procedury jsou odstraněny, když je server restartován nebo zastaven, nebo když je zavřeno připojení, v jehož kontextu byly vytvořeny.
Vytváření, úpravy a odstraňování uložených procedur
Stvoření uložené procedury zahrnuje řešení následujících úkolů:
- definování typu vytvořeného uložené procedury: dočasné nebo vlastní. Navíc si můžete vytvořit svůj vlastní systém uložené procedury zadáním názvu s předponou sp_ a umístěním do systémové databáze. Tento postup bude dostupný v kontextu jakékoli databáze na lokálním serveru;
- plánování přístupových práv. Při tvorbě uložené procedury je třeba mít na paměti, že bude mít stejná přístupová práva k databázovým objektům jako uživatel, který jej vytvořil;
- definice parametry uložené procedury... Stejně jako postupy nalezené ve většině programovacích jazyků, uložené procedury může mít vstupní a výstupní parametry;
- vývoj kódu uložené procedury... Kód procedury může obsahovat sekvenci libovolných příkazů SQL, včetně volání ostatních. uložené procedury.
Vytvoření nového a úprava stávajícího uložené procedury se provádí pomocí následujícího příkazu:
<определение_процедуры>:: = (CREATE | ALTER) název_procedury [; číslo] [(@ název_parametru datový typ) [= výchozí]] [, ... n] AS sql_operátor [... n]
Podívejme se na parametry tohoto příkazu.
Pomocí předpon sp_, #, ## lze vytvořenou proceduru definovat jako systémovou nebo dočasnou. Jak je patrné ze syntaxe příkazu, není dovoleno uvádět jméno vlastníka, který bude vlastnit vytvořenou proceduru, ani název databáze, kde se má nacházet. Tedy za účelem umístění vytvořeného uložené procedury v konkrétní databázi musíte spustit příkaz CREATE PROCEDURE v kontextu této databáze. Při manipulaci mimo tělo uložené procedury můžete použít zkrácené názvy pro objekty stejné databáze, to znamená bez zadání názvu databáze. Pokud potřebujete odkazovat na objekty umístěné v jiných databázích, je vyžadováno zadání názvu databáze.
Číslo v názvu je identifikační číslo uložené procedury, který jej jednoznačně identifikuje ve skupině procedur. Pro pohodlí řízení procedur, logicky stejného typu uložené procedury lze seskupit tak, že jim dáte stejný název, ale různá identifikační čísla.
K přenosu vstupních a výstupních dat ve vytvořeném uložené procedury lze použít parametry, jejichž názvy, stejně jako názvy lokálních proměnných, musí začínat symbolem @. Jeden uložené procedury lze zadat mnoho parametrů oddělených čárkami. Tělo procedury by nemělo používat lokální proměnné, jejichž názvy jsou stejné jako názvy parametrů této procedury.
Chcete-li určit typ dat, která budou mít odpovídající parametr uložené procedury, budou fungovat jakékoli datové typy SQL, včetně uživatelsky definovaných. Datový typ CURSOR však lze použít pouze jako výstupní parametr uložené procedury, tj. zadáním klíčového slova OUTPUT.
Přítomnost klíčového slova OUTPUT znamená, že odpovídající parametr má vracet data uložené procedury... To však vůbec neznamená, že parametr není vhodný pro předávání hodnot uložené procedury... Zadáním klíčového slova OUTPUT dáváte serveru pokyn k ukončení uložené procedury přiřaďte aktuální hodnotu parametru lokální proměnné, která byla zadána jako hodnota parametru při volání procedury. Všimněte si, že když je zadáno klíčové slovo OUTPUT, hodnotu odpovídajícího parametru při volání procedury lze nastavit pouze pomocí lokální proměnné. Nesmíte používat žádné výrazy nebo konstanty, které jsou platné pro normální parametry.
Klíčové slovo VARYING se používá ve spojení s
Uložená procedura (angl. uložená procedura) je pojmenovaný objekt databázového programu. SQL Server má několik typů uložených procedur.
Systémové uložené procedury (angl. uložená procedura systému) jsou dodávány vývojáři DBMS a používají se k provádění akcí se systémovým katalogem nebo získávání systémových informací. Jejich jména obvykle začínají předponou „sp_“. Uložené procedury všech typů se vyvolávají pomocí příkazu EXECUTE, který lze zkrátit jako EXEC. Například sp_helplogins uložená procedura spuštěná bez parametrů generuje dvě sestavy s názvy účtů (angl. logins) a jejich odpovídající uživatelé v každé databázi (angl. uživatelé).
EXEC sp_helplogins;
Pro představu o akcích prováděných pomocí systémových uložených procedur v tabulce. 10.6 uvádí několik příkladů. SQL Server obsahuje více než tisíc systémových uložených procedur.
Tabulka 10.6
Příklady uložených procedur systému SQL Server
Uživatel může vytvářet uložené procedury v uživatelských databázích a v databázích pro dočasné objekty. V druhém případě bude uložená procedura temporální. Stejně jako u dočasných tabulek musí název dočasně uložené procedury začínat předponou "#", pokud se jedná o místní dočasně uloženou proceduru, nebo "##", pokud se jedná o globální proceduru. Lokální dočasný postup lze použít pouze v rámci připojení, ve kterém byl vytvořen, globální - a v rámci jiných připojení.
Programovatelné objekty SQL Server lze vytvořit pomocí nástrojů Transact-SQL nebo sestavení (angl. Assembly) v Common Language Runtime (CRL) rozhraní Microsoft .Net Framework. V tomto tutoriálu bude zvažována pouze první metoda.
Uložené procedury se vytvářejí pomocí příkazu CREATE PROCEDURE (může být zkrácen na PROC), jehož formát je uveden níže:
VYTVOŘIT (PROCED I PROCEDURE) název_proc [; číslo]
[(gparameter data_type)
[“Výchozí] |
[S
[PRO REPLIKACI]
AS ([BEGIN] sql_statement [;] [... n] [END])
Pokud je uložená procedura (nebo spouštěč, funkce, pohled) vytvořena pomocí možnosti ŠIFROVÁNÍ, její kód se převede tak, že se text stane nečitelným. Současně, jak je uvedeno v, použitý algoritmus byl přenesen z dřívějších verzí SQL Server a nelze jej považovat za spolehlivý ochranný algoritmus - existují nástroje, které vám umožňují rychle provést zpětnou konverzi.
Možnost PŘEKOMPILOVAT dává systému pokyn k opětovnému zkompilování textu při každém volání procedury. Obvykle se postup zkompilovaný při prvním spuštění uloží do mezipaměti, což může zlepšit výkon.
EXECUTE AS definuje kontext zabezpečení, ve kterém by měla být procedura provedena. Pak jedna z hodnot f CALLER | SEBE | VLASTNÍK | "uživatelské_jméno"). CALLER je výchozí nastavení a znamená, že kód bude spuštěn v kontextu zabezpečení uživatele volajícího tento modul. V souladu s tím musí mít uživatel oprávnění nejen pro samotný naprogramovaný objekt, ale také pro další databázové objekty, které jsou jím ovlivněny. EXECUTE AS SELF znamená použití uživatelského kontextu k vytvoření nebo úpravě programovatelného objektu. OWNER označuje, že kód bude spuštěn v kontextu aktuálního vlastníka procedury. Pokud pro něj není definován žádný vlastník, pak se myslí vlastník schématu, ke kterému patří. EXECUTE AS "user_name" umožňuje explicitně specifikovat uživatelské jméno (v jednoduchých uvozovkách).
Pro proceduru lze zadat parametry. Jedná se o lokální proměnné používané k předávání hodnot do procedury. Pokud je parametr deklarován klíčovým slovem OUTPUT (nebo zkráceně OUT), jedná se o výstupní parametr: hodnotu zadanou v proceduře po jejím skončení může použít program, který proceduru volal. Klíčové slovo READONLY znamená, že hodnotu parametru nelze v uložené proceduře změnit.
Parametrům lze přiřadit hodnoty, ale výchozí, které se použijí, pokud není při volání procedury explicitně zadána hodnota parametru. Podívejme se na příklad:
VYTVOŘIT PROC surma (@ a int, @b int = 0,
© result int OUTPUT) AS
SET @ výsledek = 0a + 0b
Vytvořili jsme proceduru se třemi parametry a parametr @b má výchozí hodnotu 0 a parametr @result je výstupní parametr: jeho prostřednictvím se hodnota vrací volajícímu programu. Prováděné akce jsou poměrně jednoduché - výstupní parametr obdrží hodnotu součtu dvou vstupů.
Při práci v SQL Server Management Studio lze vytvořenou uloženou proceduru nalézt v části Programmable DB Objects (angl. Programovatelnost) v podsekci pro uložené procedury (obrázek 10.2).
Při volání procedury můžete jako vstupní parametry použít proměnné i konstanty. Podívejme se na dva příklady. V prvním jsou vstupní parametry procedury explicitně nastaveny konstantami, pro výstupní parametr ve volání je uvedeno klíčové slovo OUTPUT. Ve druhé možnosti je hodnota proměnné použita jako první vstupní parametr a pro druhý parametr je pomocí klíčového slova DEFAULT uvedeno, že by měla být použita výchozí hodnota:
Rýže. 10.2.
DECLARE @ with int;
EXEC suma 10,5, @ c VÝSTUP;
TISK 0c; - Zobrazí se 15
DECLARE Gi int = 5;
- při volání použít výchozí hodnotu
EXEC summa Gi, DEFAULT, 0с OUTPUT;
TISK 0c; - Zobrazí se 5
Podívejme se nyní na příklad s analýzou návratového kódu, kterým procedura končí. Nechť je třeba spočítat, kolik knih v tabulce Bookl vyšlo v daném rozmezí let. Navíc, pokud se ukázalo, že počáteční rok je více než poslední rok, procedura vrátí „1“ a nepočítá se, jinak spočítáme počet knih a vrátíme 0:
CREATE PROC dbo.rownum (0FirsYear int, GLastYear int, 0result int OUTPUT) AS
POKUD 0FirsYear> 0LastYear NÁVRAT 1
SET @ výsledek = (VYBRAT POČET (*) Z dbo.Bookl
KDE MEZI 0Prvním rokem A 0minulým rokem);
Zvažte variantu volání této procedury, ve které je návratový kód uložen v celočíselné proměnné 0ret, načež je analyzována její hodnota (v tomto případě to bude 1). Funkce CAST použitá v operátoru PRINT se používá k převodu hodnoty celočíselné proměnné Gres na typ řetězce:
DECLARE 0ret int, Gres int
EXEC Gret = rownum 2004, 2002, Gres OUT;
IF 0ret = l PRINT "Počáteční rok je větší než koncový rok"
TISK "Počet knih" + ODLÍCENÍ (Gres jako varchar (20))
Uložené procedury mohou nejen číst data z tabulky, ale také data upravovat a dokonce vytvářet tabulky a řadu dalších databázových objektů.
Z uložené procedury však nelze vytvářet schémata, funkce, spouštěče, procedury a pohledy.
Následující příklad ilustruje obě tyto možnosti a rozsah pro dočasné objekty. Uložená procedura níže kontroluje existenci dočasné tabulky # thab2; pokud tato tabulka neexistuje, pak ji vytvoří. Poté se do tabulky # Tab2 zadají hodnoty dvou sloupců a obsah tabulky se zobrazí příkazem SELECT:
CREATE PROC My_Procl (@id int, @name varchar (30))
IF OBJECT_ID ("tempdb.dbo. # Tab21) JE NULL
INSERT INTO dbo. # Tab2 (id, name) VALUES (0id, 0name)
VYBERTE * Z dbo. # Tab2 –№1
Před prvním voláním uložené procedury vytvoříme dočasnou tabulku # Thab2 v ní použitou. Věnujte pozornost operátorovi EXEC. V předchozích příkladech byly parametry předávány do procedury "podle pozice" a v tomto případě je použit jiný formát pro předávání parametrů - "podle jména", název parametru a jeho hodnota jsou výslovně uvedeny:
CREATE TABLE dbo # Tab2 (id int, název varchar (30));
EXEC My_Procl 0name = "lvan", 0id = 2;
SELECT * FROM dbo. # Tab2; –№2
Ve výše uvedeném příkladu bude příkaz SELECT proveden dvakrát: poprvé - uvnitř procedury, podruhé - z fragmentu volajícího kódu (označeného komentářem "# 2").
Před druhým voláním procedury smažeme dočasnou tabulku # Tab2. Poté se z uložené procedury vytvoří dočasná tabulka se stejným názvem:
DROP TABLE dbo. # Tab2;
EXEC My_Procl 0name = "Ivan", 0id = 2;
SELECT * FROM dbo. # Tab2; –№2
V tomto případě zobrazí data pouze příkaz SELECT uvnitř procedury (s komentářem „Xa 1“). SELECT "# 2" bude mít za následek chybu, protože dočasná tabulka vytvořená v uložené proceduře již byla odstraněna z databáze tempdb v době návratu procedury.
Uloženou proceduru můžete zrušit pomocí příkazu DROP PROCEDURE. Jeho formát je uveden níže. Jeden operátor může odstranit několik uložených procedur tak, že je uvede oddělené čárkami:
DROP (PROC I PROCEDURE) (postup) [
Odeberme například dříve vytvořenou proceduru summa:
DROP PROC summa;
Můžete provést změny existující procedury (a ve skutečnosti ji přepsat) pomocí příkazu ALTER PROCEDURE (přípustné
zkratka PROC). S výjimkou klíčového slova ALTER je formát příkazu podobný jako u příkazu CREATE PROCEDURE. Změňme například postup dbo. rownum nastavením možnosti spuštění v kontextu zabezpečení vlastníka:
ALTER PROC dbo.rownum (SFirsYear int,
SLastYear int, Sresult int OUTPUT)
WITH EXECUTE AS Owner je instalovatelná možnost
POKUD 0FirsYear> 0LastYear NÁVRAT 1 ELSE ZAČNĚTE
SET 0výsledek = (VYBRAT POČET (*) Z dbo.Bookl
KDE MEZI SFirsYear A SLastYear);
V některých případech může být nutné dynamicky vytvořit příkaz a provést jej na databázovém serveru. Tuto úlohu lze také vyřešit pomocí operátoru EXEC. Níže uvedený příklad načte záznamy z tabulky Bookl pod podmínkou, že atribut Year je roven hodnotě nastavené pomocí proměnné:
DECLARE 0y int = 2000;
EXEC ("VYBRAT * Z dbo.Bookl WHERE =" [e-mail chráněný]) ;
Provádění dynamicky generovaných instrukcí vytváří předpoklady pro realizaci počítačových útoků, jako je „SQL injection“ (angl. SQL injekce). Podstatou útoku je, že útočník vloží svůj vlastní SQL kód do dynamicky generovaného dotazu. K tomu obvykle dochází, když jsou nahrazené parametry převzaty z výsledků uživatelského vstupu.
Změňme trochu předchozí příklad:
DECLARE 0y varchar (100);
SET 0y = "2OOO"; - máme to od uživatele
Pokud předpokládáme, že jsme od uživatele obdrželi hodnotu řetězce přiřazenou v příkazu SET (ať už jakkoli, například prostřednictvím webové aplikace), pak příklad ilustruje „běžné“ chování našeho kódu.
DECLARE 0y varchar (100);
SET 0y = "2000; DELETE FROM dbo.Book2"; - injekce
EXEC ("SELECT * FROM dbo.Book2 WHERE =" + 0y);
V takových případech se doporučuje použít, kdykoli je to možné, systémovou uloženou proceduru sp_executcsql, která umožňuje řídit typ parametrů, což je jedna z bariér SQL injection. Aniž bychom podrobně zvažovali jeho formát, pojďme analyzovat příklad podobný tomu, který byl uveden dříve:
EXECUTE sp_executesql
N "SELECT * FROM dbo.Bookl WHERE = 0y",
To explicitně určuje typ parametru použitého v dotazu a bude monitorován serverem SQL Server během provádění. Písmeno "N" před uvozovkami označuje, že se jedná o doslovnou konstantu Unicode, jak to vyžaduje procedura. Parametru lze přiřadit nejen konstantní hodnotu, ale i hodnotu jiné proměnné.
Uložené procedury je speciální typ dávky příkazů Transact-SQL vytvořených pomocí jazyka SQL a procedurálních rozšíření. Hlavní rozdíl mezi balíčkem a uloženou procedurou je v tom, že ta je uložena jako databázový objekt. Jinými slovy, uložené procedury jsou zachovány na straně serveru, aby se zlepšil výkon a opakovatelnost.
Database Engine podporuje uložené procedury a systémové procedury. Uložené procedury se vytvářejí stejně jako všechny ostatní databázové objekty, tzn. pomocí jazyka DDL. Systémové postupy jsou poskytovány Database Engine a lze je použít k přístupu a úpravě informací v systémovém katalogu.
Když vytváříte uloženou proceduru, můžete definovat volitelný seznam parametrů. Procedura tedy při každém zavolání přijme příslušné argumenty. Uložené procedury mohou vrátit hodnotu obsahující uživatelem definované informace nebo v případě chyby související chybovou zprávu.
Uložená procedura je předkompilována před uložením jako objekt do databáze. Předkompilovaná forma procedury je uložena v databázi a použita při každém jejím volání. Tato vlastnost uložených procedur poskytuje důležitou výhodu v tom, že eliminuje (téměř ve všech případech) rekompilaci procedury a poskytuje odpovídající zlepšení výkonu. Tato vlastnost uložených procedur má také pozitivní vliv na množství dat vyměňovaných mezi databázovým systémem a aplikacemi. Zejména volání uložené procedury, která má velikost několik tisíc bajtů, může vyžadovat méně než 50 bajtů. Když více uživatelů provádí opakované úkoly pomocí uložených procedur, může být kumulativní efekt těchto úspor významný.
Uložené procedury lze také použít pro následující účely:
k vytvoření protokolu akcí s databázovými tabulkami.
Použití uložených procedur poskytuje úroveň kontroly zabezpečení, která výrazně převyšuje úroveň zabezpečení poskytovanou pomocí příkazů GRANT a REVOKE, které uživatelům udělují různá přístupová oprávnění. To je možné, protože oprávnění ke spuštění uložené procedury je nezávislé na oprávnění upravovat objekty obsažené v dané uložené proceduře, jak je popsáno v další části.
Uložené procedury, které generují protokoly pro zápisy a/nebo čtení v tabulkách, poskytují další zabezpečení databáze. Pomocí těchto postupů může správce databáze sledovat změny provedené v databázi uživateli nebo aplikacemi.
Vytváření a spouštění uložených procedur
Uložené procedury se vytvářejí pomocí příkazu VYTVOŘIT POSTUP který má následující syntaxi:
VYTVOŘIT PROC název_procu [((@ param1) typ1 [ROZMĚRNÉ] [= výchozí1])] (,…) JAKO dávka | EXTERNÍ JMÉNO název_metody Konvence syntaxe
Parametr schema_name určuje název schématu, které je přiřazeno jako vlastník vygenerované uložené procedury. Parametr proc_name určuje název uložené procedury. Parametr @ param1 je parametr procedury (formální argument), jehož datový typ je určen parametrem type1. Parametry procedury jsou lokální v rámci procedury, stejně jako lokální proměnné jsou lokální v rámci balíčku. Parametry procedury jsou hodnoty, které volající předá proceduře pro použití v ní. Parametr default1 definuje výchozí hodnotu pro odpovídající parametr procedury. (Výchozí hodnota může být také NULL.)
Možnost OUTPUT označuje, že parametr procedury je vratný parametr, který lze použít k vrácení hodnoty z uložené procedury do volající procedury nebo systému.
Jak již bylo zmíněno dříve, předkompilovaná forma procedury je uložena v databázi a použita při každém jejím volání. Pokud z nějakého důvodu musí být uložená procedura zkompilována pokaždé, když je volána, při deklaraci procedury použijte možnost S PŘEKOMPILOVÁNÍM... Použití možnosti WITH RECOMPILE neguje jednu z nejdůležitějších výhod uložených procedur: zlepšení výkonu z jediné kompilace. Možnost WITH RECOMPILE by se proto měla používat pouze při častých změnách databázových objektů používaných uloženou procedurou.
klauzule EXECUTE AS definuje kontext zabezpečení, ve kterém by se měla uložená procedura spustit po jejím zavolání. Nastavením tohoto kontextu můžete pomocí Database Engine řídit výběr uživatelských účtů pro kontrolu přístupových oprávnění k objektům, na které odkazuje tato uložená procedura.
Ve výchozím nastavení mohou příkaz CREATE PROCEDURE používat pouze členové s pevnou rolí serveru sysadmin a pevnou databázovou rolí db_owner nebo db_ddladmin. Ale členové těchto rolí mohou toto právo přiřadit jiným uživatelům pomocí instrukce UDĚLEJTE POSTUP VYTVOŘENÍ POSTUPU.
Níže uvedený příklad ukazuje, jak vytvořit jednoduchou uloženou proceduru pro práci s tabulkou projektu:
POUŽÍVEJTE SampleDb; PŘEJDĚTE NA POSTUP VYTVOŘENÍ Zvýšení rozpočtu (@procento INT = 5) JAKO AKTUALIZACE NASTAVENÍ projektu Rozpočet = Rozpočet + Rozpočet * @ procent / 100;
Jak již bylo zmíněno dříve, k oddělení dvou paketů použijte GO instrukce... Příkaz CREATE PROCEDURE nelze kombinovat s jinými příkazy Transact-SQL ve stejném balíčku. Uložená procedura ZvýšitBudget zvyšuje rozpočty pro všechny projekty o určitý počet procent, zadaný parametrem @percent. Procedura také definuje výchozí procentuální hodnotu (5), která se použije, pokud tento argument není během procedury přítomen.
Uložené procedury mohou přistupovat k tabulkám, které neexistují. Tato vlastnost vám umožňuje ladit kód procedury, aniž byste museli nejprve vytvářet příslušné tabulky nebo se dokonce připojovat k cílovému serveru.
Na rozdíl od základních uložených procedur, které jsou vždy uloženy v aktuální databázi, je možné vytvořit dočasné uložené procedury, které jsou vždy umístěny v dočasné systémové databázi tempdb. Jedním z důvodů pro vytváření dočasných uložených procedur může být zamezení opakovanému provádění určité skupiny příkazů při připojování k databázi. Můžete vytvořit místní nebo globální dočasné procedury. Za tímto účelem je název lokální procedury zadán jedním symbolem # (#jméno_proc) a název globální procedury je zadán dvojitým (## název_proc).
Lokální dočasně uloženou proceduru může spustit pouze uživatel, který ji vytvořil, a pouze během připojení k databázi, ve které byla vytvořena. Globální dočasnou proceduru mohou provádět všichni uživatelé, ale pouze do té doby, než skončí poslední spojení, ve kterém se provádí (obvykle spojení tvůrce procedury).
Životní cyklus uložené procedury se skládá ze dvou fází: její vytvoření a její provedení. Každá procedura je vytvořena jednou a prováděna opakovaně. Uložená procedura je provedena pomocí EXECUTE příkazy uživatel, který vlastní proceduru nebo má oprávnění EXECUTE pro přístup k této proceduře. Příkaz EXECUTE má následující syntaxi:
[] [@return_status =] (proc_name | @proc_name_var) ([[@ parameter1 =] hodnota | [@ parameter1 =] @variable] | VÝCHOZÍ) .. Syntaktické konvence
Kromě parametru return_status mají všechny parametry příkazu EXECUTE stejný logický význam jako stejnojmenné parametry příkazu CREATE PROCEDURE. Parametr return_status definuje celočíselnou proměnnou, která ukládá návratový stav procedury. Hodnotu lze parametru přiřadit buď pomocí konstanty (hodnota) nebo lokální proměnné (@proměnná). Pořadí hodnot pojmenovaných parametrů není důležité, ale hodnoty nepojmenovaných parametrů musí být zadány v pořadí, v jakém jsou definovány v příkazu CREATE PROCEDURE.
klauzule DEFAULT poskytuje výchozí hodnoty pro parametr procedury, který byl zadán v definici procedury. K chybě dojde, když procedura očekává hodnotu parametru, pro který nebyla definována žádná výchozí hodnota a není zadán žádný parametr, nebo je zadáno klíčové slovo DEFAULT.
Pokud je příkaz EXECUTE prvním příkazem v dávce, lze klíčové slovo EXECUTE vynechat. Je však bezpečnější zahrnout toto slovo do každého paketu. Použití příkazu EXECUTE je znázorněno v příkladu níže:
POUŽÍVEJTE SampleDb; EXECUTE Zvýšit rozpočet 10;
Příkaz EXECUTE v tomto příkladu provede uloženou proceduru Zvýšit rozpočet, která zvýší rozpočet pro všechny projekty o 10 %.
Níže uvedený příklad ukazuje, jak vytvořit uloženou proceduru pro zpracování dat v tabulkách Employee a Works_on:
Procedura ModifyEmpId v příkladu ilustruje použití uložených procedur jako součást procesu referenční integrity (v tomto případě mezi tabulkami Employee a Works_on). Podobnou uloženou proceduru lze použít v rámci definice spouštěče, která ve skutečnosti vynucuje referenční integritu.
Níže uvedený příklad ukazuje použití klauzule OUTPUT v uložené proceduře:
Tuto uloženou proceduru lze provést pomocí následujících pokynů:
DECLARE @quantityDeleteEmployee INT; EXECUTE DeleteEmployee @ empId = 18316, @ [e-mail chráněný] VÝSTUP; TISKNOUT N "Smazaní zaměstnanci:" + převést (nvarchar (30), @quantityDeleteEmployee);
Tento postup spočítá počet projektů, na kterých je pracovník s osobním číslem @empId zaneprázdněn, a výslednou hodnotu přiřadí parametru © counter. Po smazání všech řádků pro dané osobní číslo z tabulek Zaměstnanec a Práce_on se vypočítaná hodnota přiřadí proměnné @quantityDeleteEmployee.
Hodnota parametru je vrácena do volající procedury pouze v případě, že je zadána volba OUTPUT. Ve výše uvedeném příkladu předává procedura DeleteEmployee parametr @counter volající proceduře, takže uložená procedura vrací hodnotu systému. Proto musí být parametr @counter uveden jak ve volbě OUTPUT při deklaraci procedury, tak v příkazu EXECUTE při jejím volání.
WITH RESULTS SETS klauzule příkazu EXECUTE
V SQL Server 2012 se zadává příkaz EXECUTE S klauzule RESULTS SETS, jehož prostřednictvím můžete při splnění určitých podmínek měnit podobu výsledné sady uložené procedury.
Následující dva příklady pomohou vysvětlit tuto větu. První příklad je úvodní příklad, který ukazuje, jak by mohl vypadat výsledek, když je vynechána klauzule WITH RESULTS SETS:
Procedura EmployeesInDept je jednoduchý postup, který zobrazuje personální čísla a příjmení všech zaměstnanců pracujících v konkrétním oddělení. Číslo oddělení je parametrem procedury a musí být specifikováno při jejím volání. Provedením tohoto postupu se zobrazí tabulka se dvěma sloupci, jejichž záhlaví se shodují s názvy odpovídajících sloupců databázové tabulky, tzn. ID a příjmení. SQL Server 2012 používá novou klauzuli WITH RESULTS SETS ke změně záhlaví sloupců výsledků (a také jejich datového typu). Použití tohoto návrhu je znázorněno na příkladu níže:
POUŽÍVEJTE SampleDb; EXEC EmployeesInDept "d1" SE SADAMI VÝSLEDKŮ ((INT NOT NULL, [Last Name] CHAR (20) NOT NULL));
Výsledek spuštění uložené procedury nazvané tímto způsobem bude následující:
Jak vidíte, spuštění uložené procedury pomocí klauzule WITH RESULT SETS v příkazu EXECUTE umožňuje změnit názvy a datový typ sloupců ve výsledné sadě vrácené procedurou. Tato nová funkce tedy poskytuje větší flexibilitu při provádění uložených procedur a umístění jejich výsledků do nové tabulky.
Změna struktury uložených procedur
Databázový stroj také podporuje prohlášení ZMĚNIT POSTUP upravit strukturu uložených procedur. ALTER PROCEDURE se obvykle používá k úpravě příkazů Transact-SQL v rámci procedury. Všechny parametry příkazu ALTER PROCEDURE mají stejný význam jako stejnojmenné parametry příkazu CREATE PROCEDURE. Hlavním účelem použití tohoto příkazu je vyhnout se přepsání existujících práv uložených procedur.
Databázový stroj podporuje datový typ CURSOR... Tento datový typ se používá k deklaraci kurzorů v uložených procedurách. Kurzor je programovací konstrukce používaná k ukládání výsledků dotazu (obvykle sady řádků) ak umožnění uživatelům zobrazovat tento výsledek řádek po řádku.
Chcete-li odstranit jednu nebo skupinu uložených procedur, použijte příkaz DROP PROCEDURE... Uloženou proceduru může odstranit pouze vlastník nebo členové pevných rolí db_owner a sysadmin.
Uložené procedury a CLR
SQL Server podporuje Common Language Runtime (CLR), který umožňuje vyvíjet různé databázové objekty (uložené procedury, UDF, triggery, UDF a UDD) pomocí C # a Visual Basic. CLR také umožňuje spouštět tyto objekty pomocí běžného runtime systému.
CLR je povoleno a zakázáno opcí clr_enabled systémový postup sp_configure, který je spuštěn k provedení instrukce PŘENASTAVIT... Níže uvedený příklad ukazuje, jak lze aktivovat CLR pomocí systémové procedury sp_configure:
POUŽÍVEJTE SampleDb; EXEC sp_configure "clr_enabled", 1 PŘEKONFIGURUJTE
Chcete-li vytvořit, zkompilovat a uložit proceduru pomocí CLR, musíte provést následující posloupnost kroků v uvedeném pořadí:
Vytvořte uloženou proceduru v jazyce C # nebo Visual Basic a poté ji zkompilujte pomocí příslušného kompilátoru.
Pomocí instrukce VYTVOŘIT SESTAVU, vytvořte odpovídající spustitelný soubor.
Proveďte proceduru pomocí příkazu EXECUTE.
Obrázek níže ukazuje vývojový diagram dříve nastíněných kroků. Následuje podrobnější popis tohoto procesu.
Nejprve vytvořte požadovaný program ve vývojovém prostředí, jako je Visual Studio. Zkompilujte hotový program do objektového kódu pomocí kompilátoru C # nebo Visual Basic. Tento kód je uložen v souboru dynamicky propojované knihovny (.dll), který je zdrojem pro příkaz CREATE ASSEMBLY, který generuje spustitelný přechodný kód. Dále spusťte příkaz CREATE PROCEDURE a uložte spustitelný kód jako databázový objekt. Nakonec spusťte proceduru pomocí známého příkazu EXECUTE.
Níže uvedený příklad ukazuje zdrojový kód pro uloženou proceduru v C #:
Použití System.Data.SqlClient; pomocí Microsoft.SqlServer.Server; veřejná částečná třída StoredProcedures (public static int CountEmployees () (int rows; připojení SqlConnection = nové SqlConnection ("Context Connection = true"); connection.Open (); SqlCommand cmd = connection.CreateCommand (); cmd.CommandText = "select počítat (*) jako "Počet zaměstnanců" "+" od zaměstnance "; řádky = (int) cmd.ExecuteScalar (); připojení.Zavřít (); vrátit řádky;))
Tento postup implementuje dotaz pro počítání počtu řádků v tabulce Zaměstnanec. Direktivy using na začátku programu určují jmenné prostory potřebné ke spuštění programu. Použití těchto direktiv vám umožňuje specifikovat názvy tříd ve zdrojovém kódu, aniž byste explicitně specifikovali odpovídající jmenné prostory. Dále je definována třída StoredProcedures, pro kterou Atribut SqlProcedure který informuje kompilátor, že tato třída je uložená procedura. Metoda CountEmployees () je definována uvnitř kódu třídy. Připojení k databázovému systému je navázáno prostřednictvím instance třídy SqlConnection... K otevření připojení se použije metoda Open () této instance. A CreateCommand () metoda umožňuje přístup k instanci třídy SqlCommnd kterému je předán požadovaný SQL příkaz.
V následujícím fragmentu kódu:
Cmd.CommandText = "vyberte počet (*) jako" Počet zaměstnanců "" + "od zaměstnance";
používá příkaz SELECT k počítání počtu řádků v tabulce Zaměstnanec a zobrazení výsledku. Text příkazu je určen nastavením vlastnosti CommandText proměnné cmd na instanci vrácenou metodou CreateCommand (). Další se nazývá Metoda ExecuteScalar (). instance SqlCommand. Tato metoda vrací skalární hodnotu, která je převedena na datový typ int a přiřazena k řádkům.
Nyní můžete tento kód zkompilovat pomocí sady Visual Studio. Tuto třídu jsem přidal do projektu s názvem CLRStoredProcedures, takže Visual Studio zkompiluje sestavení stejného jména s příponou * .dll. Níže uvedený příklad ukazuje další krok při vytváření uložené procedury: vytvoření spustitelného kódu. Před spuštěním kódu v tomto příkladu musíte znát umístění zkompilovaného souboru dll (obvykle se nachází ve složce Debug projektu).
POUŽÍVEJTE SampleDb; PŘEJDĚTE VYTVOŘIT SESTAVU CLRStoredProcedures Z "D: \ Projects \ CLRStoredProcedures \ bin \ Debug \ CLRStoredProcedures.dll" S POVOLENOU_SET = BEZPEČNÉ
Příkaz CREATE ASSEMBLY převezme spravovaný kód jako vstup a vytvoří vhodný objekt, pro který můžete vytvořit uložené procedury CLR, uživatelské funkce a spouštěče. Tento příkaz má následující syntaxi:
CREATE ASSEMBLY Assembly_name [AUTHORIZATION owner_name] FROM (dll_file) Konvence syntaxe
Název_sestavení určuje název sestavení. Volitelná klauzule AUTHORIZATION určuje název role jako vlastníka tohoto sestavení. Klauzule FROM určuje cestu, kde se nachází sestava, která se má načíst.
S klauzulí PERMISSION_SET je velmi důležitá klauzule příkazu CREATE ASSEMBLY a musí být vždy zahrnuta. Definuje sadu přístupových práv udělených kódu sestavení. Sada práv SAFE je nejvíce omezující. Kód sestavení, který má tato práva, nemůže přistupovat k externím systémovým prostředkům, jako jsou soubory. Sada oprávnění EXTERNAL_ACCESS umožňuje kódu sestavení přistupovat ke konkrétním externím systémovým zdrojům, zatímco sada oprávnění UNSAFE uděluje neomezený přístup ke zdrojům uvnitř i vně databázového systému.
Uživatel musí být schopen provést příkaz CREATE ASSEMBLY, aby se zachovaly informace o kódu sestavení. Sestavení je vlastněno uživatelem (nebo rolí), který provádí tento příkaz. Z vlastníka sestavení můžete udělat jiného uživatele pomocí klauzule AUTHORIZATION příkazu CREATE SCHEMA.
Databázový stroj také podporuje příkazy ALTER ASSEMBLY a DROP ASSEMBLY. Prohlášení ALTER MONTÁŽ slouží k aktualizaci sestavy na nejnovější verzi. Tento příkaz také přidá nebo odebere soubory spojené s odpovídající sestavou. Prohlášení DROP MONTÁŽ odebere zadané sestavení a všechny související soubory z aktuální databáze.
Níže uvedený příklad ukazuje, jak vytvořit uloženou proceduru na základě spravovaného kódu, který jste implementovali dříve:
POUŽÍVEJTE SampleDb; GO CREATE PROCEDURE CountEmployees JAKO EXTERNÍ NÁZEV CLRStoredProcedures.StoredProcedures.CountEmployees
Příkaz CREATE PROCEDURE v příkladu se liší od stejného příkazu v příkladech výše tím, že obsahuje Parametr EXTERNAL NAME... Tento parametr označuje, že kód generuje CLR. Název v této větě má tři části:
název_sestavení.název_třídy.název_metody
název_sestavení Určuje název sestavení.
class_name - označuje jméno obecné třídy;
název_metody - volitelný, udává název metody, který je zadán uvnitř třídy.
Provedení postupu CountEmployees je znázorněno v příkladu níže:
POUŽÍVEJTE SampleDb; DECLARE @count INT EXECUTE @count = CountEmployees TISK @count - Návrat 7
Příkaz PRINT vrátí aktuální počet řádků v tabulce Zaměstnanec.
V Microsoft SQL Server implementovat a automatizovat vlastní algoritmy ( výpočty), můžete používat uložené procedury, takže si dnes povíme, jak se vytvářejí, upravují a mažou.
Nejprve ale trochu teorie, abyste pochopili, co jsou uložené procedury a k čemu v T-SQL slouží.
Poznámka! Pro začínající programátory doporučuji následující užitečné materiály na téma T-SQL:
- Pro podrobnější studium jazyka T-SQL doporučuji přečíst i knihu - Cesta programátora T-SQL. Výukový program pro jazyk Transact-SQL.
Co jsou uložené procedury v T-SQL?
Uložené procedury Jsou databázové objekty, které obsahují algoritmus ve formě sady příkazů SQL. Jinými slovy, můžeme říci, že uložené procedury jsou programy v databázi. Uložené procedury se používají k uložení opakovaně použitelného kódu na serveru, například jste napsali určitý algoritmus, sekvenční výpočet nebo vícekrokový SQL příkaz, a abyste pokaždé neprováděli všechny instrukce zahrnuté v tomto algoritmu, můžete navrhnout jako uloženou proceduru. Současně, když vytvoříte proceduru SQL, server zkompiluje kód a poté při každém spuštění této procedury SQL ji server již nebude znovu kompilovat.
Pro spuštění uložené procedury v SQL Serveru je nutné před její název napsat příkaz EXECUTE, případně je možný i zkrácený příkaz EXEC. Volání uložené procedury v příkazu SELECT např. jako funkce již nebude fungovat, tzn. procedury běží samostatně.
V uložených procedurách, na rozdíl od funkcí, již můžete provádět operace úpravy dat, jako jsou: UNSERT, UPDATE, DELETE. Také v procedurách můžete použít SQL příkazy téměř jakéhokoli typu, například CREATE TABLE k vytváření tabulek nebo EXECUTE, tzn. volání dalších procedur. Výjimkou je několik typů instrukcí, jako jsou: vytváření nebo úprava funkcí, pohledů, triggerů, vytváření schémat a několik dalších podobných instrukcí, například také není možné přepnout kontext připojení k databázi (USE) v uložené proceduře.
Uložená procedura může mít vstupní parametry a výstupní parametry, může vracet tabulková data, nemůže vracet nic, pouze provádět instrukce v ní obsažené.
Uložené procedury jsou velmi užitečné, pomáhají nám zautomatizovat nebo zjednodušit mnoho operací, například neustále potřebujete generovat různé komplexní analytické sestavy pomocí kontingenčních tabulek, tzn. operátor PIVOT. Pro snazší vytváření dotazů s tímto operátorem ( jak víte, syntaxe PIVOT je poměrně komplikovaná), Můžete si napsat proceduru, která vám bude dynamicky generovat souhrnné sestavy, např. v materiálu „Dynamický PIVOT v T-SQL“ je uveden příklad, jak lze tuto funkci implementovat ve formě uložené procedury.
Příklady práce s uloženými procedurami v Microsoft SQL Server
Počáteční údaje pro příklady
Všechny příklady níže budou provedeny v Microsoft SQL Server 2016 Express. Abychom předvedli, jak fungují uložené procedury s reálnými daty, potřebujeme tato data, pojďme si je vytvořit. Vytvoříme si například testovací tabulku a přidáme do ní několik záznamů, řekněme, že je to tabulka obsahující seznam produktů s jejich cenami.
Příkaz k vytvoření tabulky CREATE TABLE TestTable (INT IDENTITY (1,1) NOT NULL, INT NOT NULL, VARCHAR (100) NOT NULL, MONEY NULL) GO - Instrukce pro přidání dat INSERT INTO TestTable (CategoryId, ProductName, Price) VALUES (1 , "Myš", 100), (1, "Klávesnice", 200), (2, "Telefon", 400) GO --Vyberte požadavek SELECT * FROM TestTable
Data tam jsou, nyní přejdeme k vytváření uložených procedur.
Vytvoření uložené procedury T-SQL - příkaz CREATE PROCEDURE
Uložené procedury se vytvářejí pomocí příkazu VYTVOŘIT POSTUP, po této instrukci musíte napsat název vaší procedury a poté případně definovat vstupní a výstupní parametry v závorkách. Poté napíšete klíčové slovo AS a otevřete blok instrukcí klíčovým slovem BEGIN, tento blok zavřete slovem END. Uvnitř tohoto bloku píšete všechny instrukce, které implementují váš algoritmus nebo nějaký druh sekvenčního výpočtu, jinými slovy programujete v T-SQL.
Napišme si například uloženou proceduru, která přidá nový záznam, tzn. nový produkt do naší testovací tabulky. K tomu si nadefinujeme tři příchozí parametry: @CategoryId - identifikátor kategorie produktu, @ProductName - název produktu a @Price - cena produktu, tento parametr pro nás bude volitelný, tzn. nebude možné jej předat do řízení ( například cenu zatím neznáme), pro to v jeho definici nastavíme výchozí hodnotu. Tyto parametry jsou v těle procedury, tzn. v bloku BEGIN ... END můžete použít stejným způsobem jako běžné proměnné ( jak víte, proměnné jsou označeny @). Pokud potřebujete zadat výstupní parametry, pak za názvem parametru zadejte klíčové slovo OUTPUT ( nebo zkráceně OUT).
V bloku BEGIN… END napíšeme příkaz pro přidání dat a také příkaz SELECT na konci procedury, aby uložená procedura vracela tabulková data o produktech v zadané kategorii s přihlédnutím k nové , právě přidán produkt. Také v této uložené proceduře jsem přidal zpracování vstupního parametru, konkrétně odstranění nadbytečných mezer na začátku a na konci textového řádku, aby se předešlo situacím, kdy bylo omylem vloženo několik mezer.
Zde je kód pro tento postup ( Také jsem se k tomu vyjádřil).
Vytvořte proceduru CREATE PROCEDURE TestProcedure (--Zadejte parametry @CategoryId INT, @ProductName VARCHAR (100), @Price MONEY = 0) AS BEGIN --Instrukce, které implementují váš algoritmus --Zacházení s příchozími parametry --Smazání nadbytečných mezer na začátku a na konci textového řádku SET @ProductName = LTRIM (RTRIM (@ProductName)); --Přidat nový záznam INSERT INTO TestTable (CategoryId, ProductName, Price) VALUES (@CategoryId, @ProductName, @Price) - Vraťte data SELECT * FROM TestTable WHERE CategoryId = @CategoryId END GO
Spuštění uložené procedury T-SQL - příkaz EXECUTE
Uloženou proceduru můžete spustit, jak jsem již poznamenal, pomocí příkazu EXECUTE nebo EXEC. Příchozí parametry jsou předávány procedurám tak, že je jednoduše vypíšete a za název procedury uvedete příslušné hodnoty ( pro výstupní parametry musíte také zadat příkaz OUTPUT). Název parametrů však lze vynechat, ale v tomto případě je nutné dodržet posloupnost upřesňování hodnot, tzn. zadejte hodnoty v pořadí, ve kterém jsou definovány vstupní parametry ( to platí i pro výstupní parametry).
Parametry, které mají výchozí hodnoty, není nutné uvádět, jedná se o takzvané volitelné parametry.
Zde jsou některé různé, ale ekvivalentní způsoby spouštění uložených procedur, zejména naše testovací procedura.
1. Vyvolejte proceduru bez zadání ceny PROVEĎTE Testovací proceduru @CategoryId = 1, @ProductName = "Test produkt 1" --2. Postup s označením ceny nazýváme EXEC TestProcedure @CategoryId = 1, @ProductName = "Test produkt 2", @Price = 300 --3. Procedura voláme bez uvedení názvu parametrů EXEC TestProcedure 1, "Test Product 3", 400
Změna uložené procedury na T-SQL - příkaz ALTER PROCEDURE
Pomocí pokynů můžete provést změny v algoritmu postupu ZMĚNIT POSTUP... Jinými slovy, pro změnu existující procedury stačí napsat ALTER PROCEDURE místo CREATE PROCEDURE a vše ostatní podle potřeby změnit.
Řekněme, že potřebujeme provést změny v našem testovacím postupu, řekněme parametr @Price, tj. cenu, uděláme to povinné, proto odstraníme výchozí hodnotu a také si představte, že již nepotřebujeme získat výslednou datovou sadu, k tomu jednoduše odstraníme příkaz SELECT z uložené procedury.
Změníme ALTER PROCEDURE TestProcedure (--Příchozí parametry @CategoryId INT, @ProductName VARCHAR (100), @Price MONEY) AS BEGIN --Pokyny, které implementují váš algoritmus --Zacházení se vstupními parametry – Odstranění nadbytečných mezer na začátku a na konec textových řádků SET @ProductName = LTRIM (RTRIM (@ProductName)); --Přidat nový INSERT INTO TestTable (CategoryId, ProductName, Price) VALUES (@CategoryId, @ProductName, @Price) END GO záznam
Vypuštění uložené procedury T-SQL – příkaz DROP PROCEDURE
V případě potřeby můžete uloženou proceduru smazat, to se provádí pomocí instrukce PROCEDURA DOP.
Například smažeme testovací proceduru, kterou jsme vytvořili.
DROP PROCEDURE TestProcedure
Při odstraňování uložených procedur mějte na paměti, že pokud na proceduru odkazují jiné procedury nebo příkazy SQL, po jejím odstranění se nezdaří, protože procedura, na kterou odkazují, již neexistuje.
Mám vše, doufám, že materiál byl pro vás zatím zajímavý a užitečný!
