Zvažuje sa situácia, keď uložené procedúry môžu znížiť výkon dotazu.
Pri kompilácii uložených procedúr v MS SQL Server 2000 sa uložené procedúry uložia do procedurálnej vyrovnávacej pamäte, čo môže zlepšiť výkon počas ich vykonávania odstránením potreby analýzy, optimalizácie a kompilácie kódu uloženej procedúry.
Na druhej strane existujú úskalia pri ukladaní skompilovaného kódu uloženej procedúry, ktoré môžu mať opačný efekt.
Faktom je, že pri kompilácii uloženej procedúry sa skompiluje plán vykonávania tých operátorov, ktoré tvoria kód procedúry, respektíve, ak je skompilovaná uložená procedúra uložená do vyrovnávacej pamäte, potom sa jej plán vykonávania uloží do vyrovnávacej pamäte, a preto uložená procedúra nebude byť optimalizované pre konkrétnu situáciu a parametre dopytu.
Urobí malý experiment, ktorý to demonštruje.
KROK 1... Vytvorenie databázy.
Vytvorme samostatnú databázu pre experiment.
CREATE DATABASE test_sp_perf
ON (NAME = "test_data", FILENAME = "c: \ temp \ test_data", SIZE = 1, MAXSIZE = 10, FILEGROWTH = 1 Mb)
LOG ON (NAME = "test_log", FILENAME = "c: \ temp \ test_log", SIZE = 1, MAXSIZE = 10, FILEGROWTH = 1 Mb)
KROK 2. Vytvorenie tabuľky.
CREATE TABLE sp_perf_test (column1 int, column2 char (5000))
KROK 3. Vyplnenie tabuľky testovacími čiarami. Do tabuľky sú zámerne pridané duplicitné riadky. 10 000 riadkov očíslovaných od 1 do 10 000 a 10 000 riadkov očíslovaných 50 000.
VYHLÁSIŤ @i int
SET @ i = 1
Kým<10000)
ZAČAŤ
INSERT INTO sp_perf_test (stĺpec1, stĺpec2) HODNOTY (@i, "Testovací reťazec #" + CAST (@i ako znak (8)))
INSERT INTO sp_perf_test (stĺpec1, stĺpec2) VALUES (50000, "Testovací reťazec #" + CAST (@i ako znak (8)))
SET @ i = @ i + 1
KONIEC
VYBERTE POČET (*) FROM sp_perf_test
Choď
KROK 4. Vytvorte index bez klastrov. Keďže plán vykonávania je uložený vo vyrovnávacej pamäti s procedúrou, index sa použije rovnako pre všetky volania.
VYTVORIŤ NEZAHRNUTÝ INDEX CL_perf_test ON sp_perf_test (stĺpec1)
Choď
5. KROK Vytvorenie uloženej procedúry. Procedúra jednoducho vykoná príkaz SELECT s podmienkou.
CREATE PROC proc1 (@param int)
AS
VYBERTE stĺpec1, stĺpec2 FROM sp_perf_test WHERE [e-mail chránený]
Choď
KROK 6. Spustenie uloženej procedúry. Pri spustení zraniteľnej procedúry sa špeciálne používa selektívny parameter. V dôsledku postupu dostaneme 1 riadok. Plán vykonávania naznačuje použitie indexu bez klastrov, pretože dotaz je selektívny a je to najlepší spôsob, ako získať riadok. Procedúra optimalizovaná na načítanie jedného riadku je uložená v procedurálnej cache.
EXEC proc1 1234
Choď
KROK 7. Spustenie uloženej procedúry s neselektívnym parametrom. Ako parameter sa používa hodnota 50 000. Riadky s takouto hodnotou prvého stĺpca cca 10 000 s použitím nezhlukovaného indexu a operácia vyhľadávania záložiek je neúčinná, ale keďže skompilovaný kód s plánom vykonávania je uložený v procedurálna vyrovnávacia pamäť, bude použitá. Ukazuje to plán vykonávania, ako aj skutočnosť, že operácia vyhľadávania záložiek bola vykonaná pre 9999 riadkov.
EXEC proc1 50 000
Choď
KROK 8. Načítavanie riadkov s prvým poľom rovným 50 000. Vykonaním samostatného dotazu sa dotaz optimalizuje a skompiluje so špecifickou hodnotou prvého stĺpca. Výsledkom je, že optimalizátor dotazov zistí, že pole je mnohokrát duplikované a rozhodne sa použiť operáciu skenovania tabuľky, čo je v tomto prípade oveľa efektívnejšie ako použitie nezhlukovaného indexu.
SELECT stĺpec1, stĺpec2 FROM sp_perf_test WHERE stĺpec1 = 50 000
Choď
Môžeme teda dospieť k záveru, že používanie uložených procedúr nemusí vždy zlepšiť výkon dotazu. Mali by ste byť veľmi opatrní pri uložených procedúrach, ktoré pracujú s výsledkami s premenlivým počtom riadkov a používajú rôzne plány vykonávania.
Skript môžete použiť na zopakovanie experimentu na čerstvom serveri MS SQL.
Typy uložených procedúr
SQL Server má niekoľko typov uložené procedúry.
- Systémové uložené procedúry sú určené na vykonávanie rôznych administratívnych úkonov. Takmer všetky akcie správy servera sa vykonávajú s ich pomocou. Môžeme povedať, že systém uložené procedúry sú rozhranie poskytujúce prácu so systémovými tabuľkami, ktoré sa v konečnom dôsledku scvrkáva na zmenu, pridávanie, mazanie a získavanie údajov zo systémových tabuliek používateľských aj systémových databáz. Systémové uložené procedúry majú predponu sp_, sú uložené v systémovej databáze a možno ich volať v kontexte akejkoľvek inej databázy.
- Vlastné uložené procedúry vykonávať určité akcie. Uložené procedúry- plnohodnotný databázový objekt. V dôsledku toho každý uložená procedúra sa nachádza v konkrétnej databáze, kde sa vykonáva.
- Dočasné uložené procedúry existujú len nejaký čas, po ktorom ich server automaticky zničí. Delia sa na lokálne a globálne. Miestne dočasné uložené procedúry možno volať len zo spojenia, v ktorom boli vytvorené. Keď vytvoríte takúto procedúru, musíte jej dať názov, ktorý začína jedným znakom #. Ako všetky dočasné predmety, uložené procedúry tohto typu sa automaticky vymažú, keď používateľ odpojí, reštartuje alebo zastaví server. Globálne dočasné uložené procedúry dostupné pre všetky serverové pripojenia, ktoré majú rovnaký postup. Ak ho chcete definovať, stačí mu dať názov začínajúci znakmi ##. Tieto procedúry sa vymažú, keď sa server reštartuje alebo zastaví, alebo keď sa zatvorí spojenie, v kontexte ktorého boli vytvorené.
Vytváranie, úprava a odstraňovanie uložených procedúr
Tvorba uložená procedúra zahŕňa riešenie nasledujúcich úloh:
- definovanie typu vytvoreného uložená procedúra: dočasné alebo zvykové. Okrem toho si môžete vytvoriť svoj vlastný systém uložená procedúra zadaním názvu s predponou sp_ a umiestnením do systémovej databázy. Tento postup bude dostupný v kontexte akejkoľvek databázy na lokálnom serveri;
- plánovanie prístupových práv. Pri tvorbe uložená procedúra treba mať na pamäti, že bude mať rovnaké prístupové práva k databázovým objektom ako používateľ, ktorý ho vytvoril;
- definícia parametre uloženej procedúry... Rovnako ako postupy, ktoré sa nachádzajú vo väčšine programovacích jazykov, uložené procedúry môže mať vstupné a výstupné parametre;
- vývoj kódu uložená procedúra... Kód procedúry môže obsahovať sekvenciu ľubovoľných príkazov SQL vrátane volaní ostatným. uložené procedúry.
Vytvorenie nového a úprava existujúceho uložená procedúra sa vykonáva pomocou nasledujúceho príkazu:
<определение_процедуры>:: = (CREATE | ALTER) procedure_name [; number] [(@ parameter_name datatype) [= default]] [, ... n] AS sql_operator [... n]
Pozrime sa na parametre tohto príkazu.
Pomocou predpôn sp_, #, ## možno vytvorenú procedúru definovať ako systémovú alebo dočasnú. Ako je zrejmé zo syntaxe príkazu, nie je dovolené zadať meno vlastníka, ktorý bude vlastniť vytvorenú procedúru, ako aj názov databázy, kde sa má nachádzať. Aby sa teda umiestnilo vytvorené uložená procedúra v konkrétnej databáze musíte spustiť príkaz CREATE PROCEDURE v kontexte tejto databázy. Pri manipulácii mimo tela uložená procedúra môžete použiť skrátené názvy pre objekty tej istej databázy, to znamená bez zadania názvu databázy. Keď potrebujete odkazovať na objekty nachádzajúce sa v iných databázach, je potrebné zadať názov databázy.
Číslo v názve je identifikačné číslo uložená procedúra, ktorý ho jednoznačne identifikuje v skupine postupov. Pre pohodlie riadenia procedúr, logicky rovnakého typu uložené procedúry môžu byť zoskupené tak, že im dáte rovnaký názov, ale rôzne identifikačné čísla.
Na prenos vstupných a výstupných údajov vo vytvorenom uložená procedúra možno použiť parametre, ktorých názvy, podobne ako názvy lokálnych premenných, musia začínať symbolom @. Jeden uložená procedúra je možné špecifikovať veľa parametrov oddelených čiarkami. Telo procedúry by nemalo používať lokálne premenné, ktorých názvy sú rovnaké ako názvy parametrov tejto procedúry.
Na určenie typu údajov, ktoré budú mať zodpovedajúce parameter uloženej procedúry, budú fungovať všetky typy údajov SQL, vrátane používateľsky definovaných. Dátový typ CURSOR je však možné použiť iba ako výstupný parameter uložená procedúra, t.j. špecifikovaním kľúčového slova OUTPUT.
Prítomnosť kľúčového slova OUTPUT znamená, že príslušný parameter je určený na vrátenie údajov z uložená procedúra... To však vôbec neznamená, že parameter nie je vhodný na odovzdávanie hodnôt uložená procedúra... Zadanie kľúčového slova OUTPUT dáva serveru pokyn na ukončenie uložená procedúra priraďte aktuálnu hodnotu parametra lokálnej premennej, ktorá bola zadaná ako hodnota parametra pri volaní procedúry. Všimnite si, že keď je zadané kľúčové slovo OUTPUT, hodnotu zodpovedajúceho parametra pri volaní procedúry možno nastaviť len pomocou lokálnej premennej. Nie je dovolené používať žiadne výrazy alebo konštanty, ktoré sú platné pre normálne parametre.
Kľúčové slovo VARYING sa používa v spojení s
Uložená procedúra (angl. uložená procedúra) je pomenovaný objekt databázového programu. V SQL Serveri existuje niekoľko typov uložených procedúr.
Systémové uložené procedúry (angl. uložená procedúra systému) dodávajú vývojári DBMS a používajú sa na vykonávanie akcií so systémovým katalógom alebo na získanie systémových informácií. Ich mená zvyčajne začínajú predponou „sp_“. Uložené procedúry všetkých typov sa vyvolávajú príkazom EXECUTE, ktorý možno skrátiť ako EXEC. Napríklad uložená procedúra sp_helplogins spustená bez parametrov generuje dve zostavy o názvoch účtov (angl. prihlásenia) a ich zodpovedajúcich používateľov v každej databáze (angl. používatelia).
EXEC sp_helplogins;
Aby ste získali predstavu o akciách vykonaných pomocou systémových uložených procedúr, v tabuľke. 10.6 uvádza niekoľko príkladov. V SQL Serveri je viac ako tisíc systémových uložených procedúr.
Tabuľka 10.6
Príklady uložených procedúr systému SQL Server
Užívateľ môže vytvárať uložené procedúry v užívateľských databázach a v databázach pre dočasné objekty. V druhom prípade bude uložená procedúra časový. Rovnako ako pri dočasných tabuľkách, názov dočasne uloženej procedúry musí začínať predponou "#", ak ide o lokálnu dočasne uloženú procedúru, alebo "##", ak ide o globálnu procedúru. Lokálna dočasná procedúra môže byť použitá len v rámci pripojenia, v ktorom bola vytvorená, globálna – a v rámci iných spojení.
Programovateľné objekty SQL Server je možné vytvárať pomocou nástrojov alebo zostáv Transact-SQL (angl. Assembly) v Common Language Runtime (CRL) rozhrania Microsoft .Net Framework. V tomto návode sa bude brať do úvahy iba prvá metóda.
Uložené procedúry sa vytvárajú pomocou príkazu CREATE PROCEDURE (môže byť skrátený na PROC), ktorého formát je uvedený nižšie:
VYTVORIŤ (PROCED I PROCEDURE) názov_proc [; číslo]
[(gparameter data_type)
[“Predvolené] |
[S
[NA REPLIKÁCIU]
AS ([BEGIN] sql_statement [;] [... n] [END])
Ak je uložená procedúra (alebo spúšťač, funkcia, pohľad) vytvorená s voľbou ENCRYPTION, jej kód sa skonvertuje tak, že text sa stane nečitateľným. Zároveň, ako je uvedené v, použitý algoritmus bol prevzatý zo starších verzií servera SQL Server a nemožno ho považovať za spoľahlivý ochranný algoritmus - existujú nástroje, ktoré vám umožňujú rýchlo vykonať spätnú konverziu.
Voľba PREKOMPILOVAŤ dáva systému pokyn, aby prekompiloval text pri každom volaní procedúry. Za normálnych okolností sa postup zostavený pri prvom spustení uloží do vyrovnávacej pamäte, čo môže zlepšiť výkon.
EXECUTE AS definuje bezpečnostný kontext, v ktorom by sa mala procedúra vykonať. Potom jedna z hodnôt f CALLER | SEBA | VLASTNÍK | "používateľské_meno"). CALLER je predvolená hodnota a znamená, že kód bude vykonaný v bezpečnostnom kontexte užívateľa volajúceho tento modul. V súlade s tým musí mať užívateľ oprávnenia nielen pre samotný naprogramovaný objekt, ale aj pre ostatné databázové objekty ním ovplyvnené. VYKONAŤ SAMO znamená použitie používateľského kontextu na vytvorenie alebo úpravu programovateľného objektu. OWNER označuje, že kód bude vykonaný v kontexte aktuálneho vlastníka postupu. Ak preň nie je definovaný vlastník, potom sa myslí vlastník schémy, do ktorej patrí. EXECUTE AS "user_name" vám umožňuje explicitne špecifikovať používateľské meno (v jednoduchých úvodzovkách).
Pre postup je možné špecifikovať parametre. Toto sú lokálne premenné používané na odovzdávanie hodnôt do procedúry. Ak je parameter deklarovaný kľúčovým slovom OUTPUT (alebo skrátene OUT), ide o výstupný parameter: hodnotu zadanú v procedúre po jej skončení môže použiť program, ktorý procedúru volal. Kľúčové slovo READONLY znamená, že hodnotu parametra nemožno v uloženej procedúre zmeniť.
Parametrom môžu byť priradené hodnoty, ale predvolené, ktoré sa použijú, ak pri volaní procedúry nie je explicitne špecifikovaná hodnota parametra. Uvažujme o príklade:
VYTVORIŤ PROC surma (@ a int, @b int = 0,
© result int OUTPUT) AS
SET @ výsledok = 0a + 0b
Vytvorili sme procedúru s tromi parametrami, pričom parameter @b má predvolenú hodnotu 0 a parameter @result je výstupným parametrom: prostredníctvom neho sa hodnota vracia volajúcemu programu. Vykonané akcie sú pomerne jednoduché - výstupný parameter dostane hodnotu súčtu dvoch vstupov.
Pri práci v SQL Server Management Studio možno vytvorenú uloženú procedúru nájsť v časti Programmable DB Objects (angl. Programovateľnosť) v podsekcii pre uložené procedúry (obrázok 10.2).
Pri volaní procedúry môžete ako vstupné parametre použiť premenné aj konštanty. Pozrime sa na dva príklady. V prvom sú vstupné parametre procedúry explicitne nastavené konštantami, pre výstupný parameter vo volaní je uvedené kľúčové slovo OUTPUT. V druhej možnosti sa ako prvý vstupný parameter použije hodnota premennej a pre druhý parameter sa pomocou kľúčového slova DEFAULT uvádza, že by sa mala použiť predvolená hodnota:
Ryža. 10.2.
DECLARE @ with int;
EXEC summa 10,5, @ c VÝSTUP;
TLAČ 0c; - Zobrazí sa 15
DECLARE Gi int = 5;
- pri volaní použiť predvolenú hodnotu
EXEC summa Gi, DEFAULT, 0с OUTPUT;
TLAČ 0c; - Zobrazí sa 5
Uvažujme teraz o príklade s analýzou návratového kódu, ktorým sa procedúra končí. Nech je potrebné vypočítať, koľko kníh v tabuľke Bookl vyšlo v danom rozmedzí rokov. Navyše, ak sa ukáže, že počiatočný rok je viac ako posledný rok, postup vráti „1“ a nepočíta sa, inak spočítame počet kníh a vrátime 0:
CREATE PROC dbo.rownum (0FirsYear int, GLastYear int, 0result int OUTPUT) AS
AK 0PrvýRok> 0MinulýRok NÁVRAT 1
SET @ výsledok = (VYBERTE POČET (*) Z dbo.Bookl
KDE MEDZI 0Prvým rokom A 0Minulým rokom);
Zvážte variant volania tejto procedúry, v ktorom je návratový kód uložený v celočíselnej premennej 0ret, po ktorej sa analyzuje jeho hodnota (v tomto prípade to bude 1). Funkcia CAST použitá v operátori PRINT sa používa na prevod hodnoty celočíselnej premennej Gres na typ reťazca:
DECLARE 0ret int, Gres int
EXEC Gret = rownum 2004, 2002, Gres OUT;
IF 0ret = l PRINT "Počiatočný rok je väčší ako konečný rok"
VYTLAČIŤ "Počet kníh" + OTVORENIE (Gres ako varchar (20))
Uložené procedúry dokážu nielen čítať dáta z tabuľky, ale aj upravovať dáta a dokonca vytvárať tabuľky a množstvo iných databázových objektov.
Z uloženej procedúry však nemôžete vytvárať schémy, funkcie, spúšťače, procedúry a zobrazenia.
Nasledujúci príklad ilustruje tieto schopnosti a rozsah pre dočasné objekty. Uložená procedúra nižšie kontroluje existenciu dočasnej tabuľky # thab2; ak táto tabuľka neexistuje, vytvorte ju. Potom sa hodnoty dvoch stĺpcov zadajú do tabuľky # Tab2 a obsah tabuľky sa zobrazí príkazom 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
Pred prvým volaním uloženej procedúry vytvoríme dočasnú tabuľku # Thab2, ktorá je v nej použitá. Venujte pozornosť operátorovi EXEC. V predchádzajúcich príkladoch boli parametre odovzdané do procedúry "podľa pozície", ale v tomto prípade sa používa iný formát na odovzdávanie parametrov - "podľa názvu", názov parametra a jeho hodnota sú výslovne uvedené:
CREATE TABLE dbo # Tab2 (id int, názov varchar (30));
EXEC My_Procl 0name = "lvan", 0id = 2;
SELECT * FROM dbo. # Tab2; –№2
Vo vyššie uvedenom príklade sa príkaz SELECT vykoná dvakrát: prvýkrát - v rámci procedúry, druhýkrát - z fragmentu volajúceho kódu (označeného komentárom "# 2").
Pred druhým volaním procedúry vymažeme dočasnú tabuľku # Tab2. Potom sa z uloženej procedúry vytvorí dočasná tabuľka s rovnakým názvom:
DROP TABLE dbo # Tab2;
EXEC My_Procl 0name = "Ivan", 0id = 2;
SELECT * FROM dbo. # Tab2; –№2
V tomto prípade iba príkaz SELECT v procedúre (s komentárom "Xa 1") zobrazí údaje. SELECT "# 2" bude mať za následok chybu, pretože dočasná tabuľka vytvorená v uloženej procedúre už bola vymazaná z tempdb v čase návratu procedúry.
Uloženú procedúru môžete zrušiť pomocou príkazu DROP PROCEDURE. Jeho formát je uvedený nižšie. Jeden operátor môže odstrániť niekoľko uložených procedúr tak, že ich uvedie oddelené čiarkami:
DROP (PROC I PROCEDURE) (postup) [
Napríklad odstránime predtým vytvorenú procedúru summa:
DROP PROC summa;
Môžete vykonať zmeny v existujúcom postupe (a v skutočnosti ho prepísať) pomocou príkazu ALTER PROCEDURE (prípustné
skratka PROC). S výnimkou kľúčového slova ALTER je formát príkazu podobný ako pri CREATE PROCEDURE. Zmeňme napríklad postup dbo. rownum nastavením možnosti spustenia v kontexte zabezpečenia vlastníka:
ALTER PROC dbo.rownum (SFirsYear int,
SLastYear int, Sresult int OUTPUT)
WITH EXECUTE AS Owner je inštalovateľná možnosť
AK 0PrvýRok> 0MinulýRok NÁVRAT 1 INAK ZAČNITE
SET 0výsledok = (VYBERTE POČET (*) Z dbo.Bookl
KDE MEDZI SFirsYear A SLastYear);
V niektorých prípadoch môže byť potrebné dynamicky vytvoriť príkaz a vykonať ho na databázovom serveri. Túto úlohu je možné vyriešiť aj pomocou operátora EXEC. Príklad nižšie načítava záznamy z tabuľky Bookl pod podmienkou, že atribút Year sa rovná hodnote nastavenej pomocou premennej:
DECLARE 0y int = 2000;
EXEC ("VYBERTE * Z dbo.Bookl WHERE =" [e-mail chránený]) ;
Vykonávanie dynamicky generovaných inštrukcií vytvára predpoklady na implementáciu počítačových útokov, ako je "SQL injection" (angl. SQL injekcia). Podstatou útoku je, že útočník vloží svoj vlastný SQL kód do dynamicky generovaného dotazu. Zvyčajne sa to stane, keď sa substituované parametre prevezmú z výsledkov používateľského vstupu.
Trochu zmeníme predchádzajúci príklad:
DECLARE 0y varchar (100);
SET 0y = "2OOO"; - dostali sme to od používateľa
Ak predpokladáme, že hodnotu reťazca priradenú v príkaze SET sme dostali od používateľa (nech akokoľvek, napríklad cez webovú aplikáciu), tak príklad ilustruje „bežné“ správanie nášho kódu.
DECLARE 0y varchar (100);
SET 0y = "2000; DELETE FROM dbo.Book2"; - injekcia
EXEC ("SELECT * FROM dbo.Book2 WHERE =" + 0y);
V takýchto prípadoch sa odporúča použiť vždy, keď je to možné, systémovú uloženú procedúru sp_executcsql, ktorá vám umožňuje kontrolovať typ parametrov, čo je jedna z prekážok SQL injection. Bez toho, aby sme podrobne zvážili jeho formát, analyzujme príklad podobný tomu, ktorý bol uvedený vyššie:
EXECUTE sp_executesql
N "SELECT * FROM dbo.Bookl WHERE = 0y",
Toto explicitne špecifikuje typ parametra použitého v dotaze a bude monitorovaný serverom SQL Server počas vykonávania. Písmeno "N" pred úvodzovkami znamená, že ide o doslovnú konštantu Unicode, ako to vyžaduje postup. Parameter môže byť priradená nielen konštantná hodnota, ale aj hodnota inej premennej.
Uložená procedúra je špeciálny typ dávky príkazov Transact-SQL vytvorených pomocou jazyka SQL a procedurálnych rozšírení. Hlavný rozdiel medzi balíkom a uloženou procedúrou je v tom, že táto je uložená ako databázový objekt. Inými slovami, uložené procedúry sa uchovávajú na strane servera, aby sa zlepšil výkon a opakovateľnosť.
Databázový stroj podporuje uložené procedúry a systémové procedúry. Uložené procedúry sa vytvárajú rovnakým spôsobom ako všetky ostatné databázové objekty, t.j. pomocou jazyka DDL. Systémové postupy sú poskytované databázovým strojom a možno ich použiť na prístup a úpravu informácií v systémovom katalógu.
Keď vytvoríte uloženú procedúru, môžete definovať voliteľný zoznam parametrov. Procedúra teda pri každom vyvolaní prijme vhodné argumenty. Uložené procedúry môžu vrátiť hodnotu obsahujúcu užívateľom definované informácie alebo v prípade chyby súvisiace chybové hlásenie.
Uložená procedúra je predkompilovaná pred uložením ako objekt do databázy. Predkompilovaná forma procedúry je uložená v databáze a používa sa pri každom jej volaní. Táto vlastnosť uložených procedúr poskytuje dôležitú výhodu v tom, že eliminuje (takmer vo všetkých prípadoch) opätovnú kompiláciu procedúry a poskytuje zodpovedajúce vylepšenia výkonu. Táto vlastnosť uložených procedúr má tiež pozitívny vplyv na množstvo dát vymieňaných medzi databázovým systémom a aplikáciami. Najmä volanie uloženej procedúry, ktorá má veľkosť niekoľko tisíc bajtov, môže vyžadovať menej ako 50 bajtov. Keď viacerí používatelia vykonávajú opakované úlohy pomocou uložených procedúr, kumulatívny efekt týchto úspor môže byť významný.
Uložené procedúry možno použiť aj na tieto účely:
na vytvorenie protokolu akcií s databázovými tabuľkami.
Použitie uložených procedúr poskytuje úroveň kontroly bezpečnosti, ktorá výrazne prevyšuje úroveň zabezpečenia poskytovanú použitím príkazov GRANT a REVOKE, ktoré používateľom udeľujú rôzne prístupové privilégiá. Je to možné, pretože autorizácia na vykonanie uloženej procedúry je nezávislá od autorizácie na úpravu objektov obsiahnutých v danej uloženej procedúre, ako je popísané v nasledujúcej časti.
Uložené procedúry, ktoré generujú protokoly pre zápisy a/alebo čítania v tabuľkách, poskytujú dodatočné zabezpečenie databázy. Pomocou týchto procedúr môže správca databázy sledovať zmeny vykonané v databáze používateľmi alebo aplikáciami.
Vytváranie a vykonávanie uložených procedúr
Uložené procedúry sa vytvárajú pomocou príkazu VYTVORIŤ POSTUP ktorý má nasledujúcu syntax:
CREATE PROC proc_name [((@ param1) type1 [VARYING] [= default1])] (,…) AS dávka | EXTERNAL NAME method_name Konvencie syntaxe
Parameter schema_name určuje názov schémy, ktorá je priradená ako vlastník vygenerovanej uloženej procedúry. Parameter proc_name určuje názov uloženej procedúry. Parameter @ param1 je parameter procedúry (formálny argument), ktorého typ údajov je špecifikovaný parametrom type1. Parametre procedúry sú lokálne v rámci procedúry, rovnako ako lokálne premenné sú lokálne v rámci balíka. Parametre procedúry sú hodnoty, ktoré volajúci odovzdá procedúre na použitie v nej. Parameter default1 definuje predvolenú hodnotu pre príslušný parameter procedúry. (Predvolená hodnota môže byť aj NULL.)
Možnosť OUTPUT označuje, že parameter procedúry je návratný parameter, ktorý možno použiť na vrátenie hodnoty z uloženej procedúry do volajúcej procedúry alebo systému.
Ako už bolo spomenuté, predkompilovaná forma procedúry je uložená v databáze a používa sa pri každom jej volaní. Ak je z nejakého dôvodu potrebné skompilovať uloženú procedúru pri každom jej volaní, pri deklarovaní procedúry použite možnosť S PREKOMPILOVANÍM... Použitie voľby WITH RECOMPILE neguje jednu z najdôležitejších výhod uložených procedúr: zlepšenie výkonu z jedinej kompilácie. Voľba WITH RECOMPILE by sa preto mala používať len pri častých zmenách databázových objektov používaných uloženou procedúrou.
klauzula EXECUTE AS definuje bezpečnostný kontext, v ktorom sa má uložená procedúra spustiť po jej zavolaní. Nastavením tohto kontextu môžete databázový stroj použiť na riadenie výberu používateľských účtov na kontrolu prístupových oprávnení k objektom, na ktoré odkazuje táto uložená procedúra.
V predvolenom nastavení môžu príkaz CREATE PROCEDURE použiť iba členovia s pevnou rolou servera sysadmin a pevnou databázovou rolou db_owner alebo db_ddladmin. Členovia týchto rolí však môžu prideliť toto právo iným používateľom pomocou inštrukcie GRANTOVAŤ POSTUP VYTVORENIA.
Nižšie uvedený príklad ukazuje, ako vytvoriť jednoduchú uloženú procedúru na prácu s tabuľkou projektu:
USE SampleDb; PREJDITE POSTUP VYTVORENIA Zvýšiť rozpočet (@percento INT = 5) AKO AKTUALIZOVAŤ SADU projektu Rozpočet = Rozpočet + Rozpočet * @ percent / 100;
Ako už bolo spomenuté, na oddelenie dvoch paketov použite GO pokyn... Príkaz CREATE PROCEDURE nie je možné kombinovať s inými príkazmi Transact-SQL v rovnakom balíku. Uložená procedúra Zvýšiť rozpočet zvyšuje rozpočty pre všetky projekty o konkrétny počet percent, ktorý je určený parametrom @percent. Procedúra tiež definuje predvolenú percentuálnu hodnotu (5), ktorá sa použije, ak tento argument nie je prítomný počas procedúry.
Uložené procedúry môžu pristupovať k tabuľkám, ktoré neexistujú. Táto vlastnosť vám umožňuje ladiť kód procedúry bez toho, aby ste najprv vytvorili príslušné tabuľky alebo sa dokonca pripojili k cieľovému serveru.
Na rozdiel od základných uložených procedúr, ktoré sú vždy uložené v aktuálnej databáze, je možné vytvárať dočasné uložené procedúry, ktoré sú vždy umiestnené v dočasnej systémovej databáze tempdb. Jedným z dôvodov na vytváranie dočasných uložených procedúr môže byť vyhnutie sa opakovanému vykonávaniu špecifickej skupiny príkazov pri pripájaní k databáze. Môžete vytvoriť lokálne alebo globálne dočasné procedúry. Na tento účel je názov lokálnej procedúry špecifikovaný jedným # symbolom (#proc_name) a názov globálnej procedúry je špecifikovaný dvojitým (## proc_name).
Lokálnu dočasne uloženú procedúru môže spustiť iba používateľ, ktorý ju vytvoril, a to len počas pripojenia k databáze, v ktorej bola vytvorená. Globálnu dočasnú procedúru môžu vykonávať všetci užívatelia, ale len dovtedy, kým sa neskončí posledné spojenie, v ktorom sa vykonáva (zvyčajne spojenie tvorcu procedúry).
Životný cyklus uloženej procedúry pozostáva z dvoch fáz: jej vytvorenia a jej vykonania. Každý postup sa vytvorí raz a vykoná sa opakovane. Uložená procedúra sa vykoná pomocou EXECUTE príkazy používateľ, ktorý vlastní procedúru alebo má povolenie VYKONAŤ na prístup k tejto procedúre. Príkaz EXECUTE má nasledujúcu syntax:
[] [@return_status =] (proc_name | @proc_name_var) ([[@ parameter1 =] hodnota | [@ parameter1 =] @variable] | DEFAULT) .. Konvencie syntaxe
Okrem parametra return_status majú všetky parametre príkazu EXECUTE rovnaký logický význam ako parametre s rovnakým názvom pre príkaz CREATE PROCEDURE. Parameter return_status definuje celočíselnú premennú, ktorá uchováva návratový stav procedúry. Hodnota môže byť priradená parametru buď pomocou konštanty (value) alebo lokálnej premennej (@variable). Poradie hodnôt pomenovaných parametrov nie je dôležité, ale hodnoty nepomenovaných parametrov musia byť zadané v poradí, v akom sú definované v príkaze CREATE PROCEDURE.
klauzula DEFAULT poskytuje predvolené hodnoty pre parameter procedúry, ktorý bol zadaný v definícii procedúry. Chyba nastane, keď procedúra očakáva hodnotu pre parameter, pre ktorý nebola definovaná žiadna predvolená hodnota a nie je zadaný žiadny parameter, alebo je zadané kľúčové slovo DEFAULT.
Keď je príkaz EXECUTE prvým príkazom v dávke, kľúčové slovo EXECUTE možno vynechať. Je však bezpečnejšie zahrnúť toto slovo do každého paketu. Použitie príkazu EXECUTE je znázornené v príklade nižšie:
USE SampleDb; VYKONAŤ Zvýšiť rozpočet 10;
Príkaz EXECUTE v tomto príklade spustí uloženú procedúru Zvýšenie rozpočtu, ktorá zvýši rozpočet pre všetky projekty o 10 %.
Príklad nižšie ukazuje, ako vytvoriť uloženú procedúru na spracovanie údajov v tabuľkách Employee a Works_on:
Procedúra ModifyEmpId v príklade ilustruje použitie uložených procedúr ako súčasť procesu referenčnej integrity (v tomto prípade medzi tabuľkami Employee a Works_on). Podobnú uloženú procedúru možno použiť v rámci definície spúšťača, ktorá skutočne vynucuje referenčnú integritu.
Nižšie uvedený príklad ukazuje použitie klauzuly OUTPUT v uloženej procedúre:
Túto uloženú procedúru možno vykonať pomocou nasledujúcich pokynov:
DECLARE @quantityDeleteEmployee INT; VYKONAŤ DeleteEmployee @ empId = 18316, @ [e-mail chránený] VÝKON; TLAČ N "Vymazaní zamestnanci:" + konvertovať (nvarchar (30), @quantityDeleteEmployee);
Tento postup spočíta počet projektov, na ktorých je zamestnanec s osobným číslom @empId zaneprázdnený, a výslednú hodnotu priradí parametru © counter. Po vymazaní všetkých riadkov pre dané osobné číslo z tabuliek Zamestnanec a Works_on sa vypočítaná hodnota priradí k premennej @quantityDeleteEmployee.
Hodnota parametra sa vráti do volajúcej procedúry len vtedy, ak je zadaná voľba OUTPUT. Vo vyššie uvedenom príklade postup DeleteEmployee odovzdá parameter @counter volajúcej procedúre, takže uložená procedúra vráti hodnotu systému. Preto musí byť parameter @counter špecifikovaný ako vo voľbe OUTPUT pri deklarovaní procedúry, tak aj v príkaze EXECUTE pri jej volaní.
WITH RESULTS SETS klauzula príkazu EXECUTE
V SQL Server 2012 sa zadáva príkaz EXECUTE S VÝSLEDKMI klauzula, prostredníctvom ktorého pri splnení určitých podmienok môžete zmeniť podobu sady výsledkov uloženej procedúry.
Nasledujúce dva príklady pomôžu vysvetliť túto vetu. Prvý príklad je úvodný príklad, ktorý ukazuje, ako môže vyzerať výsledok, keď sa vynechá klauzula WITH RESULTS SETS:
Procedúra EmployeesInDept je jednoduchý postup, ktorý zobrazuje osobné čísla a priezviská všetkých zamestnancov pracujúcich na konkrétnom oddelení. Číslo oddelenia je parametrom procedúry a musí byť špecifikované pri jej volaní. Vykonaním tohto postupu sa zobrazí tabuľka s dvoma stĺpcami, ktorých hlavičky sa zhodujú s názvami zodpovedajúcich stĺpcov databázovej tabuľky, t.j. ID a priezvisko. SQL Server 2012 používa novú klauzulu WITH RESULTS SETS na zmenu hlavičiek stĺpcov výsledkov (ako aj ich typu údajov). Aplikácia tohto návrhu je znázornená v príklade nižšie:
USE SampleDb; EXEC EmployeesInDept "d1" SO SADAMI VÝSLEDKOV ((INT NOT NULL, [Priezvisko] CHAR (20) NOT NULL));
Výsledok vykonania takto volanej uloženej procedúry bude nasledujúci:
Ako vidíte, spustenie uloženej procedúry pomocou klauzuly WITH RESULT SETS v príkaze EXECUTE vám umožňuje zmeniť názvy a typ údajov stĺpcov v sade výsledkov vrátených procedúrou. Táto nová funkcia teda poskytuje väčšiu flexibilitu pri vykonávaní uložených procedúr a umiestňovaní ich výsledkov do novej tabuľky.
Zmena štruktúry uložených procedúr
Databázový stroj tiež podporuje vyhlásenie ZMENIŤ POSTUP na úpravu štruktúry uložených procedúr. ALTER PROCEDURE sa zvyčajne používa na úpravu príkazov Transact-SQL v rámci procedúry. Všetky parametre príkazu ALTER PROCEDURE majú rovnaký význam ako parametre s rovnakým názvom pre príkaz CREATE PROCEDURE. Hlavným účelom použitia tohto príkazu je vyhnúť sa prepísaniu existujúcich práv uloženej procedúry.
Databázový stroj podporuje dátový typ CURSOR... Tento typ údajov sa používa na deklarovanie kurzorov v uložených procedúrach. Kurzor je programová konštrukcia používaná na ukladanie výsledkov dotazu (zvyčajne sady riadkov) a na umožnenie používateľom zobraziť tento výsledok riadok po riadku.
Ak chcete odstrániť jednu alebo skupinu uložených procedúr, použite vyhlásenie DROP PROCEDURE... Uloženú procedúru môže odstrániť iba vlastník alebo členovia pevných rolí db_owner a sysadmin.
uložené procedúry a CLR
SQL Server podporuje Common Language Runtime (CLR), ktorý vám umožňuje vyvíjať rôzne databázové objekty (uložené procedúry, UDF, spúšťače, UDF a UDD) pomocou jazyka C # a Visual Basic. CLR vám tiež umožňuje spúšťať tieto objekty pomocou bežného systému runtime.
CLR je povolená a zamietnutá opciou clr_enabled systémový postup sp_configure, ktorý sa spustí na vykonanie pokynom PREKONFIGURÁCIA... Nižšie uvedený príklad ukazuje, ako je možné aktivovať CLR pomocou systémovej procedúry sp_configure:
USE SampleDb; EXEC sp_configure "clr_enabled", 1 PREKONFIGURUJTE
Ak chcete vytvoriť, skompilovať a uložiť procedúru pomocou CLR, musíte vykonať nasledujúcu postupnosť krokov v uvedenom poradí:
Vytvorte uloženú procedúru v jazyku C # alebo Visual Basic a potom ju skompilujte pomocou vhodného kompilátora.
Pomocou návodu VYTVORIŤ MONTÁŽ, vytvorte zodpovedajúci spustiteľný súbor.
Vykonajte procedúru pomocou príkazu EXECUTE.
Obrázok nižšie zobrazuje vývojový diagram vyššie načrtnutých krokov. Nasleduje podrobnejší popis tohto procesu.
Najprv vytvorte požadovaný program vo vývojovom prostredí, ako je Visual Studio. Kompilujte hotový program do objektového kódu pomocou kompilátora C # alebo Visual Basic. Tento kód je uložený v súbore dynamickej knižnice (.dll), ktorý je zdrojom pre príkaz CREATE ASSEMBLY, ktorý generuje spustiteľný prechodný kód. Potom spustite príkaz CREATE PROCEDURE, aby ste uložili spustiteľný kód ako databázový objekt. Nakoniec spustite procedúru pomocou známeho príkazu EXECUTE.
Nižšie uvedený príklad ukazuje zdrojový kód pre uloženú procedúru v C #:
Používanie System.Data.SqlClient; pomocou Microsoft.SqlServer.Server; verejná čiastočná trieda StoredProcedures (public static int CountEmployees () (int rows; SqlConnection connection = new SqlConnection ("Context Connection = true"); connection.Open (); SqlCommand cmd = connection.CreateCommand (); cmd.CommandText = "select počítať (*) ako "Počet zamestnancov" "+" od Zamestnanca "; riadky = (int) cmd.ExecuteScalar (); pripojenie.Zatvoriť (); vrátiť riadky;))
Tento postup implementuje dotaz na počítanie počtu riadkov v tabuľke Zamestnanec. Direktívy using na začiatku programu špecifikujú menné priestory potrebné na spustenie programu. Použitie týchto direktív vám umožňuje špecifikovať názvy tried v zdrojovom kóde bez explicitného špecifikovania zodpovedajúcich menných priestorov. Ďalej je definovaná trieda StoredProcedures, pre ktorú Atribút SqlProcedure ktorý informuje kompilátor, že táto trieda je uložená procedúra. Metóda CountEmployees () je definovaná v kóde triedy. Pripojenie k databázovému systému je vytvorené prostredníctvom inštancie triedy SqlConnection... Na otvorenie pripojenia sa používa metóda Open () danej inštancie. A CreateCommand () metóda umožňuje prístup k inštancii triedy SqlCommnd ktorému sa odovzdá požadovaný SQL príkaz.
V nasledujúcom útržku kódu:
Cmd.CommandText = "vyberte počet (*) ako" Počet zamestnancov "" + "od zamestnanca";
používa príkaz SELECT na spočítanie počtu riadkov v tabuľke Zamestnanec a zobrazenie výsledku. Text príkazu je určený nastavením vlastnosti CommandText premennej cmd na inštanciu vrátenú metódou CreateCommand (). Ďalej sa volá Metóda ExecuteScalar (). inštancia SqlCommand. Táto metóda vráti skalárnu hodnotu, ktorá sa skonvertuje na typ údajov int a priradí sa k riadkom.
Teraz môžete tento kód skompilovať pomocou Visual Studia. Túto triedu som pridal do projektu s názvom CLRStoredProcedures, takže Visual Studio zostaví zostavu s rovnakým názvom s príponou * .dll. Príklad nižšie ukazuje ďalší krok pri vytváraní uloženej procedúry: vytvorenie spustiteľného kódu. Pred spustením kódu v tomto príklade musíte poznať umiestnenie skompilovaného súboru dll (zvyčajne sa nachádza v priečinku Debug projektu).
USE SampleDb; PREJDITE VYTVORIŤ MONTÁŽ CLRStoredProcedures Z "D: \ Projects \ CLRStoredProcedures \ bin \ Debug \ CLRStoredProcedures.dll" S POVOLANÍM_SET = BEZPEČNÉ
Príkaz CREATE ASSEMBLY berie ako vstup riadený kód a vytvára vhodný objekt, pre ktorý môžete vytvárať uložené procedúry CLR, UDF a spúšťače. Tento príkaz má nasledujúcu syntax:
CREATE ASSEMBLY Assembly_name [AUTHORIZATION owner_name] FROM (dll_file) Konvencie syntaxe
Assembly_name určuje názov zostavy. Voliteľná klauzula AUTHORIZATION určuje názov roly ako vlastníka tohto zhromaždenia. Klauzula FROM určuje cestu, kde sa nachádza zostava, ktorá sa má načítať.
S klauzulou PERMISSION_SET je veľmi dôležitá klauzula príkazu CREATE ASSEMBLY a musí byť vždy zahrnutá. Definuje množinu prístupových práv udelených kódu zostavy. Sada práv SAFE je najobmedzujúcejšia. Kód zostavy, ktorý má tieto práva, nemôže pristupovať k externým systémovým prostriedkom, ako sú súbory. Sada privilégií EXTERNAL_ACCESS umožňuje kódu zostavy pristupovať k špecifickým externým systémovým zdrojom, zatiaľ čo sada privilégií UNSAFE udeľuje neobmedzený prístup k prostriedkom vo vnútri aj mimo databázového systému.
Používateľ musí byť schopný vykonať príkaz CREATE ASSEMBLY, aby sa zachovali informácie o kóde zostavy. Zostavu vlastní používateľ (alebo rola), ktorý vykonáva tento príkaz. Z vlastníka zostavy môžete urobiť iného používateľa pomocou klauzuly AUTHORIZATION príkazu CREATE SCHEMA.
Databázový stroj tiež podporuje príkazy ALTER ASSEMBLY a DROP ASSEMBLY. Vyhlásenie ALTER MONTÁŽE používa sa na aktualizáciu zostavy na najnovšiu verziu. Toto vyhlásenie tiež pridáva alebo odstraňuje súbory spojené s príslušným zostavením. Vyhlásenie DROP MONTÁŽ odstráni zadanú zostavu a všetky súvisiace súbory z aktuálnej databázy.
Príklad nižšie ukazuje, ako vytvoriť uloženú procedúru na základe spravovaného kódu, ktorý ste implementovali skôr:
USE SampleDb; GO CREATE PROCEDURE CountEmployees AKO EXTERNÝ NÁZOV CLRStoredProcedures.StoredProcedures.CountEmployees
Príkaz CREATE PROCEDURE v príklade sa líši od rovnakého príkazu v príkladoch skôr tým, že obsahuje Parameter EXTERNÉHO NÁZOV... Tento parameter označuje, že kód generuje CLR. Názov v tejto vete má tri časti:
názov_zhromaždenia.názov_triedy.názov_metódy
Assembly_name Určuje názov zostavy.
class_name - označuje názov všeobecnej triedy;
názov_metódy - voliteľné, určuje názov metódy, ktorá je špecifikovaná v triede.
Vykonanie postupu CountEmployees je znázornené v príklade nižšie:
USE SampleDb; VYHLÁSIŤ @počet INT VYKONAŤ @počet = PočetZamestnanci VYTLAČIŤ @počet - Vrátenie 7
Príkaz PRINT vráti aktuálny počet riadkov v tabuľke Zamestnanec.
V Microsoft SQL Server implementovať a automatizovať svoje vlastné algoritmy ( výpočty), môžete použiť uložené procedúry, preto si dnes povieme, ako sa vytvárajú, upravujú a vymazávajú.
Najprv však trochu teórie, aby ste pochopili, čo sú uložené procedúry a na čo slúžia v T-SQL.
Poznámka! Pre začínajúcich programátorov odporúčam nasledujúce užitočné materiály na tému T-SQL:
- Pre podrobnejšie štúdium jazyka T-SQL odporúčam prečítať si aj knihu - The T-SQL Programmer's Way. Návod pre jazyk Transact-SQL.
Čo sú uložené procedúry v T-SQL?
Uložené procedúry Sú databázové objekty, ktoré obsahujú algoritmus vo forme množiny príkazov SQL. Inými slovami, môžeme povedať, že uložené procedúry sú programy v databáze. Uložené procedúry sa používajú na uloženie opätovne použiteľného kódu na serveri, napríklad ste napísali určitý algoritmus, sekvenčný výpočet alebo viackrokový príkaz SQL, a aby ste zakaždým nevykonávali všetky inštrukcie zahrnuté v tomto algoritme, môžete navrhnúť ako uloženú procedúru. Zároveň, keď vytvoríte procedúru SQL, server skompiluje kód a potom, keď spustíte túto procedúru SQL, server ju už nebude znova kompilovať.
Pre spustenie uloženej procedúry v SQL Serveri je potrebné pred jej názov napísať príkaz EXECUTE, prípadne je možný aj skrátený príkaz EXEC. Volanie uloženej procedúry v príkaze SELECT napríklad ako funkcie už nebude fungovať, t.j. procedúry prebiehajú oddelene.
V uložených procedúrach, na rozdiel od funkcií, už môžete vykonávať operácie úpravy údajov ako: UNSERT, UPDATE, DELETE. Taktiež v procedúrach môžete použiť SQL príkazy takmer akéhokoľvek typu, napríklad CREATE TABLE na vytváranie tabuliek alebo EXECUTE, t.j. volanie iných postupov. Výnimkou je niekoľko typov inštrukcií, ako napríklad: vytváranie alebo úprava funkcií, pohľadov, spúšťačov, vytváranie schém a niekoľko ďalších podobných inštrukcií, napríklad v uloženej procedúre nie je možné prepnúť aj kontext pripojenia k databáze (USE).
Uložená procedúra môže mať vstupné parametre a výstupné parametre, môže vrátiť tabuľkové údaje, nemôže vrátiť nič, iba vykonať inštrukcie v nej obsiahnuté.
Uložené procedúry sú veľmi užitočné, pomáhajú nám zautomatizovať alebo zjednodušiť mnohé operácie, napríklad neustále potrebujete generovať rôzne komplexné analytické zostavy pomocou kontingenčných tabuliek, t.j. operátor PIVOT. Na uľahčenie vytvárania dopytov s týmto operátorom ( ako viete, syntax PIVOT je dosť komplikovaná), Môžete si napísať postup, ktorý vám bude dynamicky generovať súhrnné zostavy, napríklad v materiáli „Dynamický PIVOT v T-SQL“ je uvedený príklad, ako je možné túto funkciu implementovať vo forme uloženej procedúry.
Príklady práce s uloženými procedúrami v Microsoft SQL Server
Počiatočné údaje pre príklady
Všetky nižšie uvedené príklady budú spustené v Microsoft SQL Server 2016 Express. Aby sme demonštrovali, ako fungujú uložené procedúry s reálnymi dátami, potrebujeme tieto dáta, poďme si ich vytvoriť. Vytvorte si napríklad testovaciu tabuľku a pridajte do nej niekoľko záznamov, povedzme, že ide o tabuľku obsahujúcu zoznam produktov s ich cenami.
Príkaz na vytvorenie tabuľky CREATE TABLE TestTable (INT IDENTITY (1,1) NOT NULL, INT NOT NULL, VARCHAR (100) NOT NULL, MONEY NULL) GO - Inštrukcia na pridanie údajov INSERT INTO TestTable (CategoryId, ProductName, Price) VALUES (1 , "Myš", 100), (1, "Klávesnica", 200), (2, "Telefón", 400) GO --Vyberte požiadavku SELECT * FROM TestTable
Dáta sú tam, teraz prejdime k vytváraniu uložených procedúr.
Vytvorenie uloženej procedúry T-SQL - príkaz CREATE PROCEDURE
Uložené procedúry sa vytvárajú pomocou príkazu VYTVORIŤ POSTUP, po tomto pokyne musíte napísať názov vašej procedúry, potom, ak je to potrebné, definovať vstupné a výstupné parametre v zátvorkách. Potom napíšete kľúčové slovo AS a otvoríte blok inštrukcií kľúčovým slovom BEGIN, zatvorte tento blok slovom END. Do tohto bloku napíšete všetky inštrukcie, ktoré implementujú váš algoritmus alebo nejaký sekvenčný výpočet, inými slovami, programujete v T-SQL.
Napíšme si napríklad uloženú procedúru, ktorá pridá nový záznam, t.j. nový produkt do našej testovacej tabuľky. K tomu si zadefinujeme tri vstupné parametre: @CategoryId - identifikátor kategórie produktu, @ProductName - názov produktu a @Price - cena produktu, tento parameter bude pre nás voliteľný, t.j. nebude možné prejsť do konania ( napríklad cenu zatiaľ nepoznáme), na to v jeho definícii nastavíme predvolenú hodnotu. Tieto parametre sú v tele procedúry, t.j. v bloku BEGIN ... END môžete použiť rovnakým spôsobom ako bežné premenné ( ako viete, premenné sú označené znakom @). Ak potrebujete zadať parametre výstupu, potom za názvom parametra zadajte kľúčové slovo OUTPUT ( alebo skrátene OUT).
V bloku BEGIN… END napíšeme príkaz na pridanie údajov, ako aj príkaz SELECT na konci procedúry, aby uložená procedúra vrátila tabuľkové údaje o produktoch v zadanej kategórii s prihliadnutím na nové , práve pridaný produkt. Aj v tejto uloženej procedúre som pridal spracovanie vstupného parametra, a to odstránenie nadbytočných medzier na začiatku a na konci textového riadku, aby sa predišlo situáciám, keď bolo omylom vložených niekoľko medzier.
Tu je kód pre tento postup ( Aj som sa k tomu vyjadril).
Vytvorte procedúru CREATE PROCEDURE TestProcedure (--Zadajte parametre @CategoryId INT, @ProductName VARCHAR (100), @Price MONEY = 0) AS BEGIN --Inštrukcie, ktoré implementujú váš algoritmus --Spracovanie prichádzajúcich parametrov --Odstráňte nadbytočné medzery na začiatku a na konci textového riadku SET @ProductName = LTRIM (RTRIM (@ProductName)); --Pridajte nový záznam INSERT INTO TestTable (CategoryId, ProductName, Price) VALUES (@CategoryId, @ProductName, @Price) - Vráťte údaje SELECT * FROM TestTable WHERE CategoryId = @CategoryId END GO
Spustenie uloženej procedúry T-SQL - príkaz EXECUTE
Uloženú procedúru môžete spustiť, ako som už poznamenal, pomocou príkazu EXECUTE alebo EXEC. Prichádzajúce parametre sa odovzdávajú procedúram jednoduchým ich vypísaním a zadaním príslušných hodnôt za názvom procedúry ( pre výstupné parametre musíte zadať aj príkaz OUTPUT). Názov parametrov je však možné vynechať, no v tomto prípade je potrebné dodržať postupnosť udávania hodnôt, t.j. špecifikujte hodnoty v poradí, v akom sú definované vstupné parametre ( to platí aj pre výstupné parametre).
Parametre, ktoré majú predvolené hodnoty, nie je potrebné špecifikovať, ide o takzvané voliteľné parametre.
Tu je niekoľko rôznych, ale ekvivalentných spôsobov spúšťania uložených procedúr, najmä nášho testovacieho postupu.
1. Vyvolajte procedúru bez zadania ceny EXECUTE TestProcedure @CategoryId = 1, @ProductName = "Test produkt 1" --2. Postup s označením ceny nazývame EXEC TestProcedure @CategoryId = 1, @ProductName = "Testovací produkt 2", @Price = 300 --3. Procedúru voláme bez uvedenia názvu parametrov EXEC TestProcedure 1, "Test Product 3", 400
Zmena uloženej procedúry na T-SQL - príkaz ALTER PROCEDURE
Pomocou pokynov môžete vykonať zmeny v algoritme postupu ZMENIŤ POSTUP... Inými slovami, ak chcete zmeniť existujúci postup, stačí napísať ALTER PROCEDURE namiesto CREATE PROCEDURE a podľa potreby zmeniť všetko ostatné.
Povedzme, že potrebujeme vykonať zmeny v našom testovacom postupe, povedzme parameter @Price, t.j. cenu, urobíme to povinné, preto odstránime predvolenú hodnotu a tiež si predstavte, že už nepotrebujeme získať výsledný súbor údajov, na to jednoducho odstránime príkaz SELECT z uloženej procedúry.
Zmeníme ALTER PROCEDURE TestProcedure (--Vstupné parametre @CategoryId INT, @ProductName VARCHAR (100), @Price MONEY) AS BEGIN --Inštrukcie, ktoré implementujú váš algoritmus --Spracovanie vstupných parametrov - Odstránenie nepotrebných medzier na začiatku a na koniec riadkov textu SET @ProductName = LTRIM (RTRIM (@ProductName)); --Pridať nový INSERT INTO TestTable (CategoryId, ProductName, Price) VALUES (@CategoryId, @ProductName, @Price) END GO record
Zrušenie uloženej procedúry T-SQL - príkaz DROP PROCEDURE
Ak je to potrebné, môžete uloženú procedúru vymazať, to sa vykonáva pomocou pokynov PROCEDÚRA ODPADU.
Napríklad vymažeme testovací postup, ktorý sme vytvorili.
PROCEDÚRA PAPNUTIA TestProcedúra
Pri odstraňovaní uložených procedúr je potrebné pamätať na to, že ak na procedúru odkazujú iné procedúry alebo príkazy SQL, po jej odstránení zlyhajú, pretože procedúra, na ktorú odkazovali, už neexistuje.
Mám všetko, dúfam, že materiál bol pre vás zatiaľ zaujímavý a užitočný!
