Uložené procedúry v T-SQL - vytváranie, úprava, mazanie. Vytvorenie uložených procedúr na serveri Microsoft SQL Server Sql vytvorí uloženú procedúru

Uložená procedúra je špeciálny typ balíka príkazov Transact-SQL vytvorený pomocou jazyka SQL a procedurálnych rozšírení. Hlavný rozdiel medzi balíkom a uloženou procedúrou je v tom, že tá je uložená ako databázový objekt. Inými slovami, uložené procedúry sú uložené na strane servera, aby sa zlepšil výkon a konzistencia opakovateľných úloh.

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. Týmto spôsobom bude procedúra akceptovať príslušné argumenty pri každom vyvolaní. Uložené procedúry môžu vrátiť hodnotu obsahujúcu užívateľom definované informácie alebo v prípade chyby príslušné chybové hlásenie.

Uložená procedúra je predkompilovaná predtým, ako je uložená ako objekt v databáze. 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 eliminácie (takmer vo všetkých prípadoch) opakovaných kompilácií procedúr a dosiahnutia zodpovedajúceho zlepš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ť dosť 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žívanie uložených procedúr poskytuje úroveň kontroly bezpečnosti, ktorá výrazne presahuje bezpečnosť poskytovanú pomocou 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 uloženej procedúre, ako je popísané v nasledujúcej časti.

Uložené procedúry, ktoré vytvárajú protokoly operácií zápisu a/alebo čítania tabuliek, poskytujú ďalšiu možnosť zabezpečenia databázy. Pomocou takýchto procedúr môže administrátor databázy monitorovať zmeny vykonané v databáze užívateľmi alebo aplikačnými programami.

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 názov_metódy Konvencie syntaxe

Parameter schema_name určuje názov schémy, ktorý je priradený vlastníkom vytvorenej 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 určený 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 určuje 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ávratový parameter a možno ho 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 z nejakého dôvodu musí byť uložená procedúra skompilovaná pri každom jej volaní, pri deklarovaní procedúry použite S možnosťou PREKOMPILOVAŤ. Použitie možnosti WITH RECOMPILE neguje jednu z najdôležitejších výhod uložených procedúr: zlepšenie výkonu vďaka jedinej kompilácii. Voľba WITH RECOMPILE by sa preto mala používať len vtedy, keď sa databázové objekty používané uloženou procedúrou často upravujú.

klauzula EXECUTE AS definuje bezpečnostný kontext, v ktorom sa má uložená procedúra spustiť po jej zavolaní. Nastavením tohto kontextu môže databázový stroj riadiť výber používateľských účtov na overenie prístupových oprávnení k objektom, na ktoré odkazuje uložená procedúra.

V predvolenom nastavení môžu príkaz CREATE PROCEDURE používať 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 pomocou príkazu prideliť toto právo iným používateľom 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 (@percent INT=5) AKO AKTUALIZOVAŤ NASTAVENIE projektu Rozpočet = Rozpočet + Rozpočet * @percento/100;

Ako už bolo uvedené, na oddelenie dvoch paketov použite Pokyny GO. Príkaz CREATE PROCEDURE nie je možné kombinovať s inými príkazmi Transact-SQL v rovnakej dávke. Uložená procedúra Zvýšiť rozpočet zvyšuje rozpočty pre všetky projekty o určité percento, určené parametrom @percent. Procedúra tiež definuje predvolenú percentuálnu hodnotu (5), ktorá sa použije, ak tento argument nie je prítomný pri spustení 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 predchádzajúceho vytvárania príslušných tabuliek alebo dokonca pripojenia k cieľovému serveru.

Na rozdiel od primárnych 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 uložené 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ť vyhýbanie 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 zadaný jedným znakom # (#názov_proc) a názov globálnej procedúry je zadaný dvojitým znakom (##názov_proc.).

Lokálnu dočasne uloženú procedúru môže spustiť iba používateľ, ktorý ju vytvoril, a to iba 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 do ukončenia posledného spojenia, na 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á procedúra je vytvorená raz a vykonaná mnohokrát. Uložená procedúra sa vykoná pomocou VYKONAŤ pokyny užívateľ, ktorý je vlastníkom procedúry alebo má privilégium EXECUTE na prístup k tejto procedúre. Príkaz EXECUTE má nasledujúcu syntax:

[] [@return_status =] (názov_procu | @názov_proc_var) (hodnota [[@parameter1 =] | [@parameter1=] @premenná ] | PREDCHOZIE).. Konvencie syntaxe

S výnimkou parametra return_status majú všetky parametre príkazu EXECUTE rovnaký logický význam ako rovnaké parametre príkazu CREATE PROCEDURE. Parameter return_status určuje celočíselnú premennú, ktorá ukladá 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ť poskytnuté v poradí, v akom sú definované v príkaze CREATE PROCEDURE.

klauzula DEFAULT poskytuje predvolenú hodnotu pre parameter procedúry, ktorý bol zadaný v definícii procedúry. Keď procedúra očakáva hodnotu pre parameter, pre ktorý nebola definovaná žiadna predvolená hodnota a parameter chýba alebo je zadané kľúčové slovo DEFAULT, dôjde k chybe.

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ýšenie rozpočtu 10;

Príkaz EXECUTE v tomto príklade spustí uloženú procedúru Zvýšenie rozpočtu, ktorá zvýši rozpočet všetkých projektov o 10 %.

Nižšie uvedený príklad ukazuje, ako vytvoriť uloženú procedúru na spracovanie údajov v tabuľkách Employee a Works_on:

Príklad postupu ModifyEmpId ilustruje použitie uložených procedúr ako súčasť procesu udržiavania referenčnej integrity (v tomto prípade medzi tabuľkami Employee a Works_on). Podobnú uloženú procedúru možno použiť vo vnútri definície spúšťača, ktorá v skutočnosti poskytuje referenčnú integritu.

Nasledujúci 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; EXECUTE DeleteEmployee @empId=18316, @counter=@quantityDeleteEmployee OUTPUT; PRINT N"Vymazaní zamestnanci: " + convert(nvarchar(30), @quantityDeleteEmployee);

Tento postup spočíta počet projektov, na ktorých pracuje zamestnanec s osobným číslom @empId a výslednú hodnotu priradí parametru ©counter. Po odstránení 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, preto 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 pre príkaz EXECUTE zadáte S VÝSLEDKMI klauzula, prostredníctvom ktorého môžete pri splnení určitých podmienok zmeniť formu 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 parameter procedúry a musí byť špecifikovaný pri jeho volaní. Vykonaním tohto postupu sa vytvorí tabuľka s dvoma stĺpcami, ktorých hlavičky sa zhodujú s názvami zodpovedajúcich stĺpcov v databázovej tabuľke, t.j. ID a priezvisko. Ak chcete zmeniť hlavičky stĺpcov výsledkov (ako aj ich typ údajov), SQL Server 2012 používa novú klauzulu WITH RESULTS SETS. Aplikácia tejto vety 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 môžete vidieť, spustenie uloženej procedúry pomocou klauzuly WITH RESULT SETS v príkaze EXECUTE vám umožňuje zmeniť názvy a dátové typy stĺpcov v sade výsledkov vytvorenej 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 inštrukciu ZMENIŤ POSTUP na úpravu štruktúry uložených procedúr. Príkaz ALTER PROCEDURE sa zvyčajne používa na zmenu príkazov Transact-SQL v rámci procedúry. Všetky parametre príkazu ALTER PROCEDURE majú rovnaký význam ako rovnaké parametre príkazu 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 Typ údajov 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 umožňuje používateľom zobraziť tento výsledok riadok po riadku.

Ak chcete odstrániť jednu alebo skupinu uložených procedúr, použite Návod na POSTUP PÁDANIA. 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 spoločný jazykový modul runtime

SQL Server podporuje Common Language Runtime (CLR), ktorý vám umožňuje vyvíjať rôzne databázové objekty (uložené procedúry, užívateľom definované funkcie, spúšťače, užívateľom definované agregácie a vlastné dátové typy) pomocou C# a Visual Basic. CLR vám tiež umožňuje spúšťať tieto objekty pomocou spoločného runtime systému.

Spoločný jazykový modul runtime sa zapína a vypína pomocou tejto možnosti clr_enabled systémový postup sp_configure, ktorý sa spúšťa na vykonanie pokynom PREKONFIGURÁCIA. Nasledujúci príklad ukazuje, ako môžete použiť systémový postup sp_configure na povolenie CLR:

USE SampleDb; EXEC sp_configure "clr_enabled",1 RECONFIGURE

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 pokynov VYTVORIŤ MONTÁŽ, vytvorte zodpovedajúci spustiteľný súbor.

Vykonajte procedúru pomocou príkazu EXECUTE.

Na obrázku nižšie je grafická schéma vyššie uvedený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ý slúži ako zdroj pre príkaz CREATE ASSEMBLY, ktorý vytvára prechodný spustiteľný kód. Ďalej zadajte príkaz CREATE PROCEDURE, aby ste uložili vykonávací 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 count(*) ako "Počet zamestnancov" " + "od zamestnanca"; rows = (int)cmd.ExecuteScalar(); connection.Close(); return rows; ) )

Tento postup implementuje dotaz na počítanie počtu riadkov v tabuľke Zamestnanec. Pomocou direktív na začiatku programu špecifikujte 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 sa vytvorí prostredníctvom inštancie triedy SqlConnection. Na otvorenie pripojenia sa používa metóda Open() tejto 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 je to tzv Metóda ExecuteScalar(). Inštancia SqlCommand. Táto metóda vráti skalárnu hodnotu, ktorá sa skonvertuje na celočíselný typ údajov a priradí sa k premennej riadkov.

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 zodpovedajúci objekt, na ktorom môžete vytvárať uložené procedúry CLR, užívateľom definované funkcie a spúšťače. Táto inštrukcia má nasledujúcu syntax:

CREATE ASSEMBLY Assembly_name [ AUTORIZÁCIA vlastník_názov ] FROM (dll_file) Konvencie syntaxe

Parameter 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 špecifikovaná. Definuje množinu oprávnení udelených kódu zostavy. Sada povolení SAFE je najviac obmedzujúca. Kód zostavy, ktorý má tieto práva, nemôže pristupovať k externým systémovým prostriedkom, ako sú súbory. Sada práv EXTERNAL_ACCESS umožňuje kódu zostavy prístup k určitým externým systémovým zdrojom, zatiaľ čo sada práv UNSAFE umožňuje neobmedzený prístup k prostriedkom vo vnútri aj mimo databázového systému.

Ak chcete uložiť informácie o kóde zostavy, používateľ musí mať možnosť vydať príkaz CREATE ASSEMBLY. Vlastníkom zostavy je používateľ (alebo rola) vykonávajúci inštrukciu. Môžete urobiť iného používateľa vlastníkom zostavy pomocou klauzuly AUTHORIZATION príkazu CREATE SCHEMA.

Databázový stroj podporuje aj príkazy ALTER ASSEMBLY a DROP ASSEMBLY. Vyhlásenie ALTER ASSEMBLY slúži na aktualizáciu zostavy na najnovšiu verziu. Táto inštrukcia tiež pridáva alebo odstraňuje súbory spojené s príslušnou zostavou. Návod na MONTÁŽ PADOM Odstráni zadanú zostavu a všetky s ňou súvisiace súbory z aktuálnej databázy.

Nižšie uvedený príklad 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

Inštrukcia CREATE PROCEDURE v príklade sa líši od tej istej inštrukcie v predchádzajúcich príkladoch tým, že obsahuje Parameter EXTERNÉHO NÁZOV. Táto možnosť určuje, že kód je generovaný modulom Common Language Runtime. Názov v tejto vete sa skladá z troch častí:

názov_zhromaždenia.názov_triedy.názov_metódy

názov_zhromaždenia - označuje názov zostavy;

class_name - označuje názov všeobecnej triedy;

názov_metódy - voliteľná časť, určuje názov metódy, ktorá je definovaná vo vnútri triedy.

Vykonanie postupu CountEmployees je znázornené v príklade nižšie:

USE SampleDb; VYHLÁSIŤ @počet INT VYKONAŤ @počet = PočetZamestnancov VYTLAČIŤ @počet -- Vrátiť 7

Príkaz PRINT vráti aktuálny počet riadkov v tabuľke Zamestnanec.

Pri práci so serverom SQL Server môžu používatelia vytvárať vlastné procedúry, ktoré implementujú určité akcie. Uložené procedúry sú plnohodnotné databázové objekty, a preto je každá z nich uložená v konkrétnej databáze. Priame volanie uloženej procedúry je možné len vtedy, ak sa tak deje v kontexte databázy, kde sa procedúra nachádza.

Typy uložených procedúr

SQL Server má niekoľko typov uložených procedúr.

Systémové uložené procedúry sú určené na vykonávanie rôznych administratívnych akcií. Takmer všetky činnosti správy servera sa vykonávajú s ich pomocou. Môžeme povedať, že systémové uložené procedúry sú rozhraním, ktoré poskytuje prácu so systémovými tabuľkami, čo v konečnom dôsledku spočíva v zmene, pridávaní, odstraňovaní a získavaní ú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 implementujú určité akcie. Uložené procedúry sú plnohodnotným databázovým objektom. V dôsledku toho sa každá uložená procedúra nachádza v konkrétnej databáze, kde sa vykonáva.

Dočasne uložené procedúry existujú len krátky čas, po ktorom ich server automaticky zničí. Delia sa na lokálne a globálne. Lokálne dočasne uložené procedúry možno volať len z pripojenia, v ktorom sú vytvorené. Pri vytváraní takejto procedúry jej musíte dať názov začínajúci jedným znakom #. Rovnako ako všetky dočasné objekty, uložené procedúry tohto typu sa automaticky vymažú, keď sa užívateľ odpojí alebo sa server reštartuje alebo zastaví. Globálne dočasne uložené procedúry sú dostupné pre každé pripojenie zo servera, ktorý má rovnakú procedúru. 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í pripojenie v kontexte, v ktorom boli vytvorené.

Spúšťače

Spúšťače sú typom uloženej procedúry. Vykonajú sa, keď sa na tabuľke spustí operátor jazyka manipulácie s údajmi (DML). Spúšťače sa používajú na kontrolu integrity údajov a tiež na vrátenie transakcií.

Spúšť je zostavená SQL procedúra, ktorej vykonanie je podmienené výskytom určitých udalostí v rámci relačnej databázy. Použitie spúšťačov je z veľkej časti veľmi pohodlné pre používateľov databázy. Napriek tomu ich použitie často zahŕňa dodatočné náklady na zdroje pre I/O operácie. Keď je možné dosiahnuť rovnaké výsledky (s oveľa menšou réžiou) pomocou uložených procedúr alebo aplikačných programov, použitie spúšťačov nie je praktické.

Spúšťače je špeciálny nástroj SQL servera, ktorý sa používa na udržiavanie integrity údajov v databáze. Obmedzenia integrity, pravidlá a predvolené hodnoty nemusia vždy dosiahnuť požadovanú úroveň funkčnosti. Často je potrebné implementovať komplexné algoritmy overovania údajov, aby sa zabezpečila ich spoľahlivosť a reálnosť. Okrem toho niekedy potrebujete sledovať zmeny v hodnotách tabuľky, aby bolo možné súvisiace údaje podľa potreby upraviť. Spúšťače si možno predstaviť ako druh filtrov, ktoré nadobudnú účinnosť po dokončení všetkých operácií v súlade s pravidlami, štandardnými hodnotami atď.

Spúšť je špeciálny typ uloženej procedúry, ktorú automaticky spúšťa server pri pokuse o zmenu údajov v tabuľkách, ku ktorým sú priradené spúšťače. Každý Spúšť je viazaná na konkrétny stôl. Všetky úpravy údajov, ktoré vykoná, sa považujú za jednu transakciu. Ak sa zistí chyba alebo porušenie integrity údajov, transakcia sa vráti späť. Zmeny sú preto zakázané. Všetky zmeny, ktoré už vykonal spúšťač, sa tiež vrátia späť.

Vytvára spúšťač iba vlastník databázy. Toto obmedzenie vám umožňuje vyhnúť sa náhodným zmenám štruktúry tabuliek, spôsobov pripojenia iných objektov k nim atď.

Spúšť Je to veľmi užitočný a zároveň nebezpečný prostriedok. Ak je teda logika jeho fungovania nesprávna, môžete ľahko zničiť celú databázu, takže spúšťače musia byť ladené veľmi opatrne.

Na rozdiel od bežného podprogramu, spúšťač sa vykoná implicitne vždy, keď nastane spúšťacia udalosť, a nemá žiadne argumenty. Jeho aktivácia sa niekedy nazýva spustenie spúšte. Pomocou spúšťačov sa dosahujú tieto ciele:

Overenie správnosti zadaných údajov a presadzovanie komplexných obmedzení integrity údajov, ktoré je ťažké, ak nie nemožné, udržiavať pomocou obmedzení integrity nastavených v tabuľke;

vydávanie upozornení, ktoré vám pripomenie vykonať určité akcie pri aktualizácii tabuľky implementovanej určitým spôsobom;

zhromažďovanie informácií o audite zaznamenávaním informácií o vykonaných zmenách a tých, ktorí ich vykonali;

podpora replikácie.

Základný formát príkazu CREATE TRIGGER je uvedený nižšie:

<Определение_триггера>::=

CREATE TRIGGER názov spúšťača

PRED | PO<триггерное_событие>

ON<имя_таблицы>

<список_старых_или_новых_псевдонимов>]

<тело_триггера>

Spúšťacie udalosti pozostávajú z vkladania, odstraňovania a aktualizácie riadkov v tabuľke. V druhom prípade môžete zadať špecifické názvy stĺpcov tabuľky pre spúšťaciu udalosť. Načasovanie spúšťača sa určuje pomocou kľúčových slov BEFORE ( Spúšť prebieha pred vykonaním udalostí s ním spojených) alebo AFTER (po ich vykonaní).

Akcie vykonávané spúšťačom sú špecifikované pre každý riadok (PRE KAŽDÝ RIADOK), na ktorý sa vzťahuje udalosť, alebo len raz pre každú udalosť (PRE KAŽDÝ VÝKAZ).

Nesprávne napísané spúšťače môžu viesť k vážnym problémom, ako sú mŕtve zámky. Spúšťače môžu blokovať veľa zdrojov na dlhé časové obdobia, takže osobitnú pozornosť je potrebné venovať minimalizácii konfliktov prístupu.

Spúšť možno vytvoriť iba v aktuálnej databáze, ale je možné pristupovať k iným databázam v rámci spúšťača, vrátane tých, ktoré sa nachádzajú na vzdialenom serveri.

uložená procedúra je možné len vtedy, ak sa vykonáva v kontexte databázy, v ktorej sa postup nachádza.

Typy uložených procedúr

SQL Server má niekoľko typov uložené procedúry.

systém uložené procedúry určené na vykonávanie rôznych administratívnych úkonov. Takmer všetky činnosti správy servera sa vykonávajú s ich pomocou. Dá sa povedať, že systémové uložené procedúry sú rozhranie, ktoré poskytuje prácu so systémovými tabuľkami, čo v konečnom dôsledku spočíva v zmene, pridávaní, odstraňovaní a získavaní údajov zo systémových tabuliek používateľských aj systémových databáz. systém 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 špecifickej databáze, kde sa vykonáva.
Dočasné uložené procedúry existujú iba chvíľu, potom 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é. Pri vytváraní takejto procedúry jej musíte 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ď sa používateľ odpojí alebo sa server reštartuje alebo zastaví. Globálne dočasné uložené procedúry sú dostupné pre všetky pripojenia zo servera, ktorý má 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í pripojenie v kontexte, v ktorom boli vytvorené.

Vytvárajte, upravujte a odstraňovajte uložené procedúry

Tvorba uložená procedúra zahŕňa riešenie nasledujúcich problémov:

určenie typu vytvoreného uložená procedúra: dočasné alebo vlastné. Okrem toho si môžete vytvoriť svoj vlastný systém uložená procedúra, pričom mu dáte názov s predponou sp_ a umiestnite ho do systémovej databázy. Tento postup bude dostupný v kontexte akejkoľvek databázy lokálneho servera;
plánovanie prístupových práv. Pri tvorbe uložená procedúra treba brať do úvahy, ž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. Podobne ako postupy zahrnuté 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í iných uložené procedúry.

Vytvorenie nového a zmena existujúceho uložená procedúra vykonáte pomocou nasledujúceho príkazu:

<определение_процедуры>::= (CREATE | ALTER ) procedure_name [;number] [(@parameter_name data_type ) [=predvolene] ][,...n] AS sql_operator [...n]

Pozrime sa na parametre tohto príkazu.

Pomocou predpôn sp_ , # , ## môže byť vytvorená procedúra definovaná 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 špecifickej databáze musíte zadať príkaz CREATE PROCEDURE v kontexte tejto databázy. Pri odbočovaní od tela uložená procedúra skrátené názvy možno použiť pre objekty tej istej databázy, t.j. bez zadania názvu databázy. Keď potrebujete získať prístup k objektom umiestneným v iných databázach, zadanie názvu databázy je povinné.

Číslo v názve je identifikačné číslo uložená procedúra, ktorý ho jednoznačne identifikuje v skupine postupov. Pre uľahčenie riadenia sú postupy 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 môžu byť použité parametre, ktorých názvy, podobne ako názvy lokálnych premenných, musia začínať symbolom @. Jeden uložená procedúra Môžete zadať viacero parametrov oddelených čiarkami. Telo procedúry by nemalo používať lokálne premenné, ktorých názvy sa zhodujú s názvami parametrov tejto procedúry.

Na určenie typu údajov, ktoré zodpovedajú parameter uloženej procedúry, vhodné sú akékoľvek dátové typy SQL, vrátane užívateľsky definovaných. Dátový typ CURSOR je však možné použiť len ako výstupný parameter uložená procedúra, t.j. zadaní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 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á pri volaní procedúry ako hodnota parametra. Všimnite si, že pri zadávaní kľúčového slova OUTPUT je možné hodnotu zodpovedajúceho parametra pri volaní procedúry nastaviť len pomocou lokálnej premennej. Použitie akýchkoľvek výrazov alebo konštánt, ktoré sú povolené pre regulárne parametre, nie je povolené.

Kľúčové slovo VARYING sa používa v spojení s

Uložená procedúra uložená procedúra) je pomenovaný objekt databázového programu. SQL Server má niekoľko typov uložených procedúr.

Systémové uložené procedúry systémové uložené procedúry) dodávajú vývojári DBMS a používajú sa na vykonávanie akcií so systémovým adresárom alebo získavanie systémových informácií. Ich mená zvyčajne začínajú predponou "sp_". Všetky typy uložených procedúr spustíte pomocou príkazu EXECUTE, ktorý možno skrátiť na EXEC. Napríklad uložená procedúra sp_helplogins, spustená bez parametrov, vytvára dve správy o názvoch účtov (Angličtina) prihlásenia) a zodpovedajúcich používateľov v každej databáze (Angličtina) používatelia).

EXEC sp_helplogins;

Aby ste získali predstavu o akciách vykonávaných pomocou systémových uložených procedúr, tabuľka 10.6 uvádza niekoľko príkladov. Celkovo je v SQL Serveri 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áze 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álnu dočasnú procedúru je možné použiť len v rámci spojenia, v ktorom bola vytvorená, globálnu možno použiť aj v rámci iných spojení.

Programovateľné objekty SQL Server je možné vytvárať pomocou nástrojov alebo zostáv Transact-SQL (Angličtina) Assembly) v prostredí CRL (Common Language Runtime) prostredia Microsoft.Net Framework. Tento tutoriál sa bude zaoberať iba prvou metódou.

Na vytvorenie uložených procedúr použite príkaz CREATE PROCEDURE (môže byť skrátený na PROC), ktorého formát je uvedený nižšie:

VYTVORIŤ (POSTUP PROC I) názov_proc [ ; číslo]

[(gparameter data_type)

[“predvolené] |

[S [ ,...n ] ]

[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 transformuje tak, že text sa stane nečitateľným. Zároveň, ako je uvedené v, použitý algoritmus bol prenesený 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Ť určuje, že systém prekompiluje text pri každom volaní procedúry. V normálnom prípade je postup zostavený pri prvom spustení uložený vo vyrovnávacej pamäti, čo umožňuje zvýšený výkon.

EXECUTE AS určuje bezpečnostný kontext, v ktorom sa má procedúra vykonať. Ďalej 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ť používateľ oprávnenia nielen k samotnému programovateľnému objektu, ale aj k iným databázovým objektom, ktoré sú ním ovplyvnené. VYKONAŤ SAMO znamená použitie kontextu používateľa, ktorý vytvára alebo upravuje programovateľný objekt. OWNER určuje, že kód bude vykonaný v kontexte aktuálneho vlastníka procedúry. Ak preň nie je určený vlastník, predpokladá sa 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é zadať parametre. Toto sú lokálne premenné používané na odovzdávanie hodnôt do procedúry. Ak je parameter deklarovaný s kľúčovým slovom OUTPUT (alebo skrátene OUT), ide o výstupnú hodnotu: hodnotu, ktorá mu bola pridelená v procedúre po jej dokončení, môže použiť program, ktorý procedúru vyvolal. Kľúčové slovo READONLY znamená, že hodnotu parametra nemožno v uloženej procedúre zmeniť.

Parametrom je možné priradiť predvolené hodnoty, ktoré sa použijú, ak pri volaní procedúry nie je explicitne špecifikovaná hodnota parametra. Pozrime sa na príklad:

CREATE PROC surma (@a int, @b int=0,

©result int OUTPUT) AS

SET @výsledok=0a+0b

Vytvorili sme procedúru s tromi parametrami a parameter @b má predvolenú hodnotu =0 a parameter @result je výstupný parameter: vracia hodnotu volajúcemu programu. Vykonané akcie sú pomerne jednoduché - výstupný parameter dostane hodnotu súčtu dvoch vstupných.

Pri práci v SQL Server Management Studio možno vytvorenú uloženú procedúru nájsť v sekcii programovateľné databázové objekty (Angličtina) Programovateľnosť) v podsekcii pre uložené procedúry (obr. 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 špecifikované ako konštanty a pre výstupný parameter vo volaní je zadané kľúčové slovo OUTPUT. Druhá možnosť používa hodnotu premennej ako prvý vstupný parameter a určuje, že pre druhý parameter by sa mala použiť predvolená hodnota pomocou kľúčového slova DEFAULT:

Ryža. 10.2.

DECLARE @с 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 , 0c VÝSTUP;

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čí. Predpokladajme, že musíme vypočítať, koľko kníh v tabuľke Bookl je vydaných v danom rozmedzí rokov. Navyše, ak je počiatočný rok väčší 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);

Uvažujme 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 príkaze PRINT sa používa na konverziu hodnoty celočíselnej premennej Gres na typ reťazca:

VYHLÁSIŤ 0ret int, Gres int

EXEC Gret = rownum 2004, 2002, Gres OUT;

IF 0ret=l PRINT "Počiatočný rok je väčší ako koncový rok"

VYTLAČIŤ "Počet kníh" + ODOSIELAŤ (Gres ako varchar(20))

Uložené procedúry dokážu nielen čítať údaje z tabuľky, ale aj upravovať údaje a dokonca vytvárať tabuľky a množstvo ďalší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 problémy súvisiace s rozsahom dočasných objektov. Nasledujúca uložená procedúra kontroluje existenciu dočasnej tabuľky #TaL2; ak táto tabuľka neexistuje, vytvorí ju. Potom sa hodnoty dvoch stĺpcov zadajú do tabuľky #TaL2 a obsah tabuľky sa zobrazí pomocou príkazu 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 #TaL2, ktorá sa v nej používa. Venujte pozornosť operátorovi EXEC. V predchádzajúcich príkladoch sa parametre odovzdávali do procedúry „podľa pozície“, ale v tomto prípade sa používa iný formát odovzdávania parametrov – „podľa názvu“, názov parametra a jeho hodnota sú explicitne 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 bude príkaz SELECT spracovaný 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 #TaL2. 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 umiestnený vo vnútri procedúry (s komentárom „Xa 1“) zobrazí údaje. Vykonanie SELECT "č. 2" bude mať za následok chybu, pretože dočasná tabuľka vytvorená v uloženej procedúre už bude vymazaná z databázy 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. Jedným príkazom môžete odstrániť niekoľko uložených procedúr, pričom ich uvediete oddelené čiarkami:

ZAPNÚŤ (POSTUP PROC I) ( postup ) [

Napríklad vymažeme predtým vytvorenú procedúru summa:

DROP PROC summa;

Môžete vykonať zmeny v existujúcej procedúre (a v skutočnosti ju predefinovať) pomocou príkazu ALTER PROCEDURE (povolené

skratka PROC). S výnimkou kľúčového slova ALTER je formát príkazu v podstate rovnaký ako pri CREATE PROCEDURE. Zmeňme napríklad postup dbo. rownum a nastavte ho tak, aby sa spustil v kontexte zabezpečenia vlastníka:

ALTER PROC dbo.rownum (SFirsYear int,

SLastYear int, Sresult int OUTPUT)

S EXECUTE AS Owner – inštalovateľná možnosť

AK 0PrvýRok>0MinulýRok NÁVRAT 1 ELŠIE 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 vygenerovať príkaz a vykonať ho na databázovom serveri. Tento problém je možné vyriešiť aj pomocou operátora EXEC. Nižšie uvedený príklad načíta záznamy z tabuľky Bookl, ak sa atribút Year rovná hodnote špecifikovanej premennou:

DECLARE 0у int = 2000;

EXEC ("SELECT * FROM dbo.Bookl WHERE = "+@y) ;

Vykonávanie dynamicky generovaných inštrukcií vytvára predpoklady pre implementáciu počítačových útokov, ako je „SQL injection“ (Angličtina) SQL injekcia). Podstatou útoku je, že útočník vloží svoj vlastný SQL kód do dynamicky generovaného dotazu. K tomu zvyčajne dochádza, keď sa nahradzované parametre preberajú z výsledkov používateľského vstupu.

Trochu zmeníme predchádzajúci príklad:

DECLARE 0у varchar(100);

SET 0у="2ООО"; – toto sme dostali od používateľa

Ak predpokladáme, že sme od používateľa dostali hodnotu reťazca priradenú v príkaze SET (bez ohľadu na to, ako napríklad cez webovú aplikáciu), potom príklad ilustruje „normálne“ správanie nášho kódu.

DECLARE 0у varchar(100);

SET 0у="2000; DELETE FROM dbo.Book2"; - injekcia

EXEC("SELECT * FROM dbo.Book2 WHERE ="+0y);

V takýchto prípadoch sa odporúča, ak je to možné, použiť systémovú uloženú procedúru sp_executcsql, ktorá umožňuje ovládať typ parametrov, čo je jedna z bariér SQL injekcií. Bez toho, aby sme podrobne zvážili jeho formát, pozrime sa na 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 SQL Server ho bude kontrolovať 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.

Definuje sa pojem uložených procedúr. Poskytuje príklady vytvárania, úpravy a používania uložených procedúr s parametrami. Uvedená je definícia vstupných a výstupných parametrov. Poskytnuté sú príklady vytvárania a volania uložených procedúr.

Koncept uloženej procedúry

Uložené procedúry sú skupiny vzájomne prepojených SQL príkazov, ktorých použitie uľahčuje a spružňuje prácu programátora, od r uložená procedúra je často oveľa jednoduchšia ako postupnosť jednotlivých príkazov SQL. Uložené procedúry sú množinou príkazov, ktoré pozostávajú z jedného alebo viacerých príkazov alebo funkcií SQL a sú uložené v kompilovanej forme v databáze. Vykonanie v databáze uložené procedúry Namiesto jednotlivých príkazov SQL má používateľ nasledujúce výhody:

potrebné operátory sú už obsiahnuté v databáze;
všetci prešli javiskom parsovanie a sú v spustiteľnom formáte; predtým vykonanie uloženej procedúry SQL Server preň vygeneruje plán vykonávania, vykoná jeho optimalizáciu a kompiláciu;
uložené procedúry podpora modulárne programovanie, pretože vám umožňujú rozdeliť veľké úlohy na samostatné, menšie a ľahšie spravovateľné časti;
uložené procedúry môže spôsobiť iným uložené procedúry a funkcie;
uložené procedúry možno volať z iných typov aplikačných programov;
zvyčajne, uložené procedúry vykonávať rýchlejšie ako postupnosť jednotlivých príkazov;
uložené procedúry jednoduchšie použitie: môžu pozostávať z desiatok alebo stoviek príkazov, ale na ich spustenie stačí zadať názov požadovaného príkazu uložená procedúra. To vám umožňuje znížiť veľkosť požiadavky odoslanej z klienta na server, a tým aj zaťaženie siete.

Ukladanie procedúr na rovnaké miesto, kde sa vykonávajú, znižuje množstvo dát prenášaných cez sieť a zlepšuje celkový výkon systému. Aplikácia uložené procedúry zjednodušuje údržbu softvérových systémov a vykonávanie zmien v nich. Zvyčajne sú všetky obmedzenia integrity vo forme pravidiel a algoritmov spracovania údajov implementované na databázovom serveri a sú dostupné koncovej aplikácii ako súbor. uložené procedúry, ktoré predstavujú rozhranie na spracovanie údajov. Na zabezpečenie integrity údajov, ako aj z bezpečnostných dôvodov, aplikácia zvyčajne nedostáva priamy prístup k údajom - všetka práca s ňou prebieha volaním určitých uložené procedúry.

Tento prístup veľmi zjednodušuje úpravu algoritmov spracovania údajov, ktoré sú okamžite dostupné pre všetkých používateľov siete, a poskytuje možnosť rozšíriť systém bez vykonania zmien v samotnej aplikácii: stačí zmeniť uložená procedúra na databázovom serveri. Vývojár nemusí prekompilovať aplikáciu, vytvárať jej kópie ani inštruovať používateľov, aby pracovali s novou verziou. Používatelia si možno ani neuvedomujú, že v systéme boli vykonané zmeny.

Uložené procedúry existujú nezávisle od tabuliek alebo iných databázových objektov. Volá ich klientsky program, iný uložená procedúra alebo spúšťač. Vývojár môže spravovať prístupové práva k uložená procedúra, ktorým sa povolí alebo zakáže jeho vykonanie. Zmeniť kód uložená procedúra povolené iba jej vlastníkom alebo členom pevnej databázy. V prípade potreby môžete previesť jeho vlastníctvo z jedného používateľa na druhého.

Uložené procedúry v prostredí MS SQL Server

Pri práci so serverom SQL Server môžu používatelia vytvárať vlastné procedúry, ktoré implementujú určité akcie. Uložené procedúry sú plnohodnotné databázové objekty, a preto je každý z nich uložený v konkrétnej databáze. Priamy hovor uložená procedúra je možné len vtedy, ak sa vykonáva v kontexte databázy, v ktorej sa postup nachádza.

Typy uložených procedúr

SQL Server má niekoľko typov uložené procedúry.

systém uložené procedúry určené na vykonávanie rôznych administratívnych úkonov. Takmer všetky činnosti správy servera sa vykonávajú s ich pomocou. Dá sa povedať, že systémové uložené procedúry sú rozhranie, ktoré poskytuje prácu so systémovými tabuľkami, čo v konečnom dôsledku spočíva v zmene, pridávaní, odstraňovaní a získavaní údajov zo systémových tabuliek používateľských aj systémových databáz. systém 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 špecifickej databáze, kde sa vykonáva.
Dočasné uložené procedúry existujú iba chvíľu, potom 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é. Pri vytváraní takejto procedúry jej musíte 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ď sa používateľ odpojí alebo sa server reštartuje alebo zastaví. Globálne dočasné uložené procedúry sú dostupné pre všetky pripojenia zo servera, ktorý má 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í pripojenie v kontexte, v ktorom boli vytvorené.

Vytvárajte, upravujte a odstraňovajte uložené procedúry

Tvorba uložená procedúra zahŕňa riešenie nasledujúcich problémov:

určenie typu vytvoreného uložená procedúra: dočasné alebo vlastné. Okrem toho si môžete vytvoriť svoj vlastný systém uložená procedúra, pričom mu dáte názov s predponou sp_ a umiestnite ho do systémovej databázy. Tento postup bude dostupný v kontexte akejkoľvek databázy lokálneho servera;
plánovanie prístupových práv. Pri tvorbe uložená procedúra treba brať do úvahy, ž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. Podobne ako postupy zahrnuté 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í iných uložené procedúry.

Vytvorenie nového a zmena existujúceho uložená procedúra vykonáte pomocou nasledujúceho príkazu:

<определение_процедуры>::= (CREATE | ALTER ) PROC názov_procedúry [;číslo] [(@názov_parametra typ_údajov ) [=predvolené] ][,...n] AS sql_operator [...n]

Pozrime sa na parametre tohto príkazu.

Kľúčové slovo VARYING sa používa v spojení s parametrom OUTPUT, ktorý je typu CURSOR. To určuje výstupný parameter bude sada výsledkov.

Kľúčové slovo DEFAULT predstavuje zodpovedajúcu hodnotu predvolený parameter. Pri volaní procedúry teda nemusíte explicitne špecifikovať hodnotu zodpovedajúceho parametra.

Keďže server ukladá do vyrovnávacej pamäte plán vykonávania dotazu a skompilovaný kód, pri ďalšom volaní procedúry sa použijú pripravené hodnoty. V niektorých prípadoch je však stále potrebné prekompilovať kód procedúry. Zadanie kľúčového slova RECOMPILE inštruuje systém, aby vytvoril plán vykonávania uložená procedúra zakaždým, keď zavolá.

Parameter FOR REPLICATION sa vyžaduje pri replikácii údajov a povolení vytvoreného uložená procedúra ako článok na publikovanie.

Kľúčové slovo ENCRYPTION dáva serveru pokyn na zašifrovanie kódu uložená procedúra, ktorý môže poskytnúť ochranu pred použitím proprietárnych algoritmov, ktoré implementujú prácu uložená procedúra.

Kľúčové slovo AS je umiestnené na začiatku samotného tela uložená procedúra, t.j. súbor SQL príkazov, pomocou ktorých sa bude realizovať tá či oná akcia. V tele procedúry je možné použiť takmer všetky SQL príkazy, deklarovať transakcie, nastavovať zámky a volať ďalšie. uložené procedúry. Odísť z uložená procedúra možno vykonať pomocou príkazu RETURN.

Odstránenie uloženej procedúry vykonávané príkazom:

ZAKÁZAŤ PROCEDURE (názov_procedúry) [,...n]

Vykonanie uloženej procedúry

Pre vykonať uloženú procedúru Použitý príkaz je:

[[ EXEC [ UTE] názov_procedúry [;číslo] [[@názov_parametra=](hodnota | @názov_premennej) |][,...n]

Ak hovor uložená procedúra nie je jediným príkazom v balíku, je potrebná prítomnosť príkazu EXECUTE. Okrem toho je tento príkaz potrebný na volanie procedúry z tela inej procedúry alebo spúšťača.

Použitie kľúčového slova OUTPUT pri volaní procedúry je povolené len pre parametre, ktoré boli deklarované kedy vytvorenie postupu s kľúčovým slovom OUTPUT.

Keď je pri volaní procedúry pre parameter zadané kľúčové slovo DEFAULT, použije sa predvolená hodnota. Prirodzene, špecifikované slovo DEFAULT je povolené len pre tie parametre, pre ktoré je definované predvolená hodnota.

Syntax príkazu EXECUTE ukazuje, že názvy parametrov možno pri volaní procedúry vynechať. V tomto prípade však musí používateľ zadať hodnoty parametrov v rovnakom poradí, v akom boli kedy uvedené vytvorenie postupu. Priradiť k parametru predvolená hodnota, pri výpise ho nemôžete len tak preskočiť. Ak chcete vynechať parametre, pre ktoré je definovaný predvolená hodnota, stačí pri volaní explicitne špecifikovať názvy parametrov uložená procedúra. Navyše týmto spôsobom môžete uviesť parametre a ich hodnoty v ľubovoľnom poradí.

Všimnite si, že pri volaní procedúry sú zadané buď názvy parametrov s hodnotami, alebo len hodnoty bez názvu parametra. Ich kombinovanie nie je povolené.

Príklad 12.1. Postup bez parametrov. Vypracujte postup na získanie mien a nákladov na tovar zakúpený Ivanovom.

CREATE PROC my_proc1 AS SELECT Product.Name, Product.Price*Transaction.Množstvo AS Cost, Customer.Last Name FROM Customer INNER JOIN (Product INNER JOIN Transaction ON Product.ProductCode=Transaction.ProductCode) ON Customer.CustomerCode=Transaction.Customer Zákazník .Priezvisko='Ivanov' Príklad 12.1. Postup na získanie mien a hodnôt tovaru zakúpeného Ivanovom.