Čo sú uložené procedúry. klauzula CREATE PROCEDURE. Jednoduché ladenie SQL
Zahrňte do svojich postupov riadok - SET NOCOUNT ON:
Pri každom DML príkaze nám SQL server opatrne vracia správu s počtom spracovaných záznamov. Táto informácia môže byť pre nás užitočné pri ladení kódu, ale potom bude úplne zbytočné. Napísaním SET NOCOUNT ON túto funkciu deaktivujeme. Pre uložené procedúry obsahujúce viacero výrazov alebo slučiek môže táto akcia výrazne zvýšiť výkon, pretože sa výrazne zníži objem prevádzky.
Transakcia SQL
Použiť názov schémy s názvom objektu:
No myslím, že je to jasné. Táto operácia povie serveru, kde má hľadať predmety, a namiesto náhodného prehrabávania sa v košoch bude okamžite vedieť, kam má ísť a čo si má vziať. S veľkým množstvom databáz, tabuliek a uložených procedúr nám môže výrazne ušetriť čas a nervy.
Transakcia SQL
SELECT * FROM dbo.MyTable --Toto je dobrá vec -- Namiesto SELECT * FROM MyTable --Toto je zlá vec --Zavolajte procedúru EXEC dbo.MyProc --Znova dobre --Namiesto EXEC MyProc --Zle!
V názvoch uložených procedúr nepoužívajte predponu "sp_":
Ak názov našej procedúry začína na "sp_", SQL Server sa najprv pozrie do svojej hlavnej databázy. Faktom je, že táto predpona sa používa pre osobné interné uložené procedúry servera. Preto môže jeho použitie viesť k dodatočným nákladom a dokonca k nesprávnym výsledkom, ak sa v jeho databáze nájde postup s rovnakým názvom ako ten váš.
Použite IF EXISTS (SELECT 1) namiesto IF EXISTS (SELECT *):
Na kontrolu, či záznam existuje v inej tabuľke, použijeme príkaz IF EXISTS. Tento výraz vráti true, ak sa z vnútorného výrazu vráti aspoň jedna hodnota, nezáleží na "1", na všetkých stĺpcoch alebo tabuľke. Vrátené údaje sa v zásade žiadnym spôsobom nepoužívajú. Na kompresiu prevádzky počas prenosu údajov je teda logickejšie použiť „1“, ako je uvedené nižšie.
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 uložená procedúra systému) dodávajú vývojári DBMS a používajú sa na vykonávanie akcií v systémovom katalógu 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 spúšťajú pomocou príkazu EXECUTE, ktorý je možné skrátiť na EXEC. Napríklad uložená procedúra sp_helplogins, spustená bez parametrov, generuje dve zostavy názvov účtov (Angličtina) prihlásenia) a ich zodpovedajúcich používateľov v každej databáze (Angličtina) používatelia).
EXEC sp_helplogins;
Pre 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. 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é v systéme SQL procedúry 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 by bola 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 môžu byť vytvorené buď pomocou nástrojov Transact-SQL alebo pomocou zostáv (Angličtina) Assembly) v prostredí CRL (Common Language Runtime) prostredia Microsoft .Net Framework. V tomto návode sa bude brať do úvahy iba prvá metóda.
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
[ 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 je 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Ť určuje, že pri každom volaní procedúry systém prekompiluje text. V obvyklom prípade je postup zostavený pri prvom spustení uložený vo vyrovnávacej pamäti, čo umožňuje zvýšiť výkon.
EXECUTE AS definuje 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ľ povolenia nielen pre samotný programovateľný objekt, ale aj pre ostatné databázové objekty, 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 definovaný 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ýstup: hodnotu, ktorá mu bola pridelená v procedúre, môže po jej skončení použiť program, ktorý procedúru volal. 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. Zvážte 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, kde 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 sekcii programovateľné databázové objekty (Angličtina) Programovateľnosť) v časti pre uložené procedúry (obrázok 10.2).
Pri volaní procedúry možno ako vstupné parametre použiť premenné aj konštanty. Uvažujme o dvoch príkladoch. 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 ako druhý parameter pomocou kľúčové slovo DEFAULT určuje, že by sa mala použiť predvolená hodnota:
Ryža. 10.2.
DECLARE @with int;
EXEC summa 10,5,@c VÝSTUP;
PRINT0c; - Zobrazí sa 15
DECLARE Gi int = 5;
- pri volaní použiť predvolenú hodnotu
EXEC summa Gi,DEFAULT , 0s OUTPUT;
PRINT0c; - Zobrazí sa 5
Zvážte teraz príklad 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. V tomto prípade, ak je počiatočný rok väčší ako koncový rok, procedúra 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). používané v operátorovi Funkcia PRINT CAST sa používa na prevod 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 "+ CAST(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.
Schémy, funkcie, spúšťače, procedúry a zobrazenia však nemožno vytvoriť z uloženej procedúry.
Nasledujúci príklad ilustruje tieto možnosti a problémy súvisiace s rozsahom dočasných objektov. Nasledujúca uložená procedúra kontroluje existenciu dočasnej tabuľky #Tab2; ak táto tabuľka neexistuje, vytvorí 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 si vytvorte dočasnú tabuľku #Tab2, ktorá sa v nej používa. Venujte pozornosť operátorovi EXEC. V predchádzajúcich príkladoch boli parametre odovzdané procedúre "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 - vo vnútri 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. 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, keď sa procedúra vráti.
Uloženú procedúru môžete zrušiť pomocou príkazu DROP PROCEDURE. Jeho formát je uvedený nižšie. Pomocou jedného príkazu môžete odstrániť niekoľko uložených procedúr tak, že ich uvediete oddelené čiarkami:
ZAPNÚŤ (POSTUP PROC I) ( postup ) [
Napríklad odstránime predtým vytvorenú procedúru summa:
DROP PROC summa;
Môžete vykonať zmeny v existujúcej procedúre (v skutočnosti ju prepísať) pomocou príkazu ALTER PROCEDURE (povoliť
skratka PROC). S výnimkou kľúčového slova ALTER je formát príkazu takmer rovnaký ako formát CREATE PROCEDURE. Zmeňme napríklad postup dbo. rownum nastavením na vykonanie v kontexte zabezpečenia vlastníka:
ALTER PROC dbo.rownum(SFirsYear int,
SLastYear int, Sresult int OUTPUT)
S VYKONANÍM AKO vlastníka - možnosť nastavenia
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. Túto úlohu je možné vyriešiť aj pomocou operátora EXEC. Nasledujúci príklad vyberá záznamy z tabuľky Bookl na základe podmienky, že atribút Year sa rovná hodnote špecifikovanej premennou:
DECLARE 0y int = 2000;
EXEC("SELECT * FROM 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" (Angličtina) SQL injekcia). Podstatou útoku je, že páchateľ vloží svoj vlastný SQL kód do dynamicky generovaného dotazu. Zvyčajne sa to stane, keď sa z výsledkov používateľského vstupu prevezmú vložené parametre.
Trochu zmeníme predchádzajúci príklad:
DECLARE 0y varchar(100);
SET 0y="2OOO"; - 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 (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ždy, keď je to možné, sa v takýchto prípadoch odporúča použiť 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, analyzujeme 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.
1. Zahrňte do svojich postupov riadok - SET NOCOUNT ON: Pri každom DML príkaze nám SQL server opatrne vracia správu s počtom spracovaných záznamov. Tieto informácie sa nám môžu hodiť pri ladení kódu, ale potom budú úplne zbytočné. Napísaním SET NOCOUNT ON túto funkciu deaktivujeme. Pre uložené procedúry obsahujúce viacero výrazov alebo slučiek môže táto akcia výrazne zvýšiť výkon, pretože sa výrazne zníži objem prevádzky.
VYTVORIŤ PROC dbo.ProcName
AS
SET NOCOUNT ON;
--Kód postupu tu
VYBERTE stĺpec1 z dbo.TblTable1
--Prepnite SET NOCOUNT do počiatočného stavu
SET NOCOUNT OFF;
Ísť
2. Použite názov schémy s názvom objektu: No myslím, že je to jasné. Táto operácia povie serveru, kde má hľadať predmety, a namiesto náhodného prehrabávania sa v košoch bude okamžite vedieť, kam má ísť a čo si má vziať. S veľkým množstvom databáz, tabuliek a uložených procedúr nám môže výrazne ušetriť čas a nervy.
SELECT * FROM dbo.MyTable --Toto je dobrý spôsob, ako to urobiť
-- Namiesto
SELECT * FROM MyTable --To je zlá vec
--Výzva postupu
EXEC dbo.MyProc -- Opäť dobré
--Namiesto
EXEC MyProc – Zlý!
3. Nepoužívajte predponu "sp_" v názve vašich uložených procedúr: Ak názov našej procedúry začína na "sp_", SQL Server sa najprv pozrie do svojej hlavnej databázy. Faktom je, že táto predpona sa používa pre osobné interné uložené procedúry servera. Preto môže jeho použitie viesť k dodatočným nákladom a dokonca k nesprávnym výsledkom, ak sa v jeho databáze nájde postup s rovnakým názvom ako ten váš.
4. Použite IF EXISTS (SELECT 1) namiesto IF EXISTS (SELECT *): Na kontrolu, či záznam existuje v inej tabuľke, použijeme príkaz IF EXISTS. Tento výraz vráti hodnotu true, ak sa z vnútorného výrazu vráti aspoň jedna hodnota, nezáleží na „1“, na všetkých stĺpcoch alebo tabuľke. Vrátené údaje sa v zásade žiadnym spôsobom nepoužívajú. Na kompresiu prevádzky počas prenosu údajov je teda logickejšie použiť „1“, ako je uvedené nižšie:
AK EXISTUJE (VYBERTE 1 ZO syobjektov
WHERE name = "MyTable" AND type = "U")
5. Použite TRY-Catch na zachytenie chýb: Pred serverom 2005 bolo po každej požiadavke v procedúre zapísaných veľké množstvo kontrol chýb. Viac kódu vždy spotrebuje viac zdrojov a viac času. Od roku 2005 sa SQL Server javil ako správnejší a pohodlný spôsob riešenia tohto problému:
ZAČAŤ SKÚŠAŤ
--kód
KONIEC POKUS
ZAČAŤ CHYTÁVAŤ
-- kód zachytenia chyby
KONIEC ÚLOVKU
Záver
V podstate to je všetko, čo mám na dnes. Ešte raz opakujem, že tu sú len tie techniky, ktoré som osobne použil vo svojej praxi a môžem ručiť za ich účinnosť.
P.S.
Môj prvý príspevok, nesúďte striktne.
Uložená procedúra je špeciálny typ dávky 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 opakujúcich sa ú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.
Pri vytváraní uloženej procedúry môžete definovať voliteľný zoznam parametrov. Procedúra teda bude mať pri každom volaní príslušné argumenty. 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) rekompilácií procedúr a získania 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 s veľkosťou niekoľkých 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 denníka protokolov o akciách s databázovými tabuľkami.
Použitie uložených procedúr poskytuje úroveň kontroly bezpečnosti, ktorá výrazne presahuje bezpečnosť 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 uloženej procedúre, ako je popísané v nasledujúcej časti.
Poskytujú uložené procedúry, ktoré vytvárajú protokoly operácií zápisu a/alebo čítania tabuľky dodatočná príležitosť bezpečnosť databázy. Pomocou takýchto postupov môže administrátor 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 názov_metódy Konvencie syntaxe
Parameter schema_name určuje názov schémy, ktorý je priradený vlastníkom 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 určuje predvolenú hodnotu pre príslušný parameter procedúry. (Predvolená hodnota môže byť aj NULL.)
Možnosť OUTPUT určuje, že parameter procedúry je vratný 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í, používa sa deklarácia procedúry možnosť S PREKOMPILOVANÍM. 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, ak dochádza k častým zmenám databázových objektov používaných uloženou procedúrou.
EXECUTE AS ponuku definuje bezpečnostný kontext, v ktorom musí byť uložená procedúra vykonaná po jej zavolaní. Nastavením tohto kontextu môže databázový stroj riadiť výber 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ží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 inštrukcie 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 spomenuté, na oddelenie dvoch balíkov sa používa GO pokyn. 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. Postup tiež definuje predvolenú percentuálnu hodnotu (5), ktorá sa použije, ak tento argument nie je prítomný počas vykonávania postupu.
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 vytvorenia príslušných tabuliek a dokonca bez pripojenia 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 vytvárania dočasných uložených procedúr môže byť vyhnutie sa opakovanému vykonávaniu určitej 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 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 do ukončenia posledného pripojenia, na ktorom beží (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 EXECUTE príkazy užívateľ, ktorý vlastní procedúru alebo má právo VYKONAŤ prístup k procedúre. Príkaz EXECUTE má nasledujúcu syntax:
[] [@return_status =] (proc_name | @proc_name_var) ([[@parameter1 =] hodnota | [@parameter1=] @variable ] | DEFAULT).. Konvencie syntaxe
S výnimkou parametra return_status majú všetky parametre príkazu EXECUTE rovnakú boolovskú hodnotu ako parametre príkazu CREATE PROCEDURE s rovnakým názvom. Parameter return_status definuje 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 pomenovaných hodnôt parametrov nie je dôležité, ale nepomenované hodnoty 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. 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ť. Bezpečnejšie je však zahrnúť toto slovo do každého balenia. 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 %.
Nasledujúci príklad 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). Takáto uložená procedúra môže byť použitá vo vnútri definície spúšťača, ktorá v skutočnosti vynucuje referenčnú integritu.
Nasledujúci príklad ukazuje použitie klauzuly OUTPUT v uloženej procedúre:
Táto uložená procedúra môže byť vykonaná pomocou nasledujúcich príkazov:
DECLARE @quantityDeleteEmployee INT; VYKONAŤ DeleteEmployee @empId=18316, @ [e-mail chránený] VÝKON; PRINT N"Zamestnanci vymazaní: " + 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 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 do 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 je zadaný príkaz EXECUTE S VÝSLEDKMI klauzula A, ktoré za určitých podmienok môže zmeniť tvar 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 personálne čísla a priezviská všetkých zamestnancov pracujúcich na konkrétnom oddelení. Číslo oddelenia je parameter procedúry a musí byť špecifikovaný pri volaní procedúry. Vykonaním tejto procedúry sa vypíše 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 tohto ustanovenia 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 sady výsledkov vytvorený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. príkaz 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 príkazu CREATE PROCEDURE s rovnakým názvom. Primárnym účelom použitia tohto príkazu je vyhnúť sa prepísaniu existujúcich povolení 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 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 uloženej procedúry 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áty a užívateľom definované dátové typy) pomocou C# a Visual Basic. Spoločný jazykový modul runtime tiež umožňuje spúšťanie týchto objektov pomocou spoločného systému runtime.
Spoločný jazykový modul runtime sa zapína a vypína prostredníctvom možnosti clr_enabled systémový postup sp_configure, ktorý sa spustí na vykonanie inštrukciou PREKONFIGURÁCIA. Nasledujúci príklad ukazuje, ako môžete povoliť spoločný jazykový modul runtime pomocou systémovej procedúry sp_configure:
USE SampleDb; EXEC sp_configure "clr_enabled",1 RECONFIGURE
Vytvorenie, kompilácia a uloženie procedúry pomocou CLR vyžaduje 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.
Použitie inštrukcie VYTVORIŤ MONTÁŽ, vytvorte príslušný 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 viac Detailný popis tento proces.
Najprv vytvorte požadovaný program v nejakom vývojovom prostredí, napr vizuálne štúdio. 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 na uloženie vykonávaného kódu ako databázový objekt. Nakoniec spustite procedúru pomocou známeho príkazu EXECUTE.
Nasledujúci príklad ukazuje zdrojový kód uloženej procedúry 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 jeho vykonanie. 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. Vo vnútri kódu triedy je definovaná metóda CountEmployees(). Pripojenie k databázovému systému je vytvorené prostredníctvom inštancie triedy SqlConnection. Na otvorenie pripojenia sa používa metóda Open() tejto inštancie. ALE 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 celočíselný typ údajov int 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 kódu na spustenie. 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 vytvoriť uložené procedúry CLR (Common Language Runtime), 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 mala by byť vždy špecifikovaná. Definuje množinu prístupových práv udelených kódu zostavy. Sada práv 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 pristupovať k určitým externým systémovým zdrojom, zatiaľ čo sada práv UNSAFE poskytuje neobmedzený prístup k prostriedkom vo vnútri aj mimo databázového systému.
Na uloženie informácií o kóde zostavy musí byť používateľ schopný vydať príkaz CREATE ASSEMBLY. Zostava je vo vlastníctve používateľa (alebo roly), ktorý vykonáva inštrukciu. Vlastníka zostavy môžete zmeniť pomocou klauzuly AUTHORIZATION príkazu CREATE SCHEMA.
Databázový stroj podporuje aj príkazy ALTER ASSEMBLY a DROP ASSEMBLY. Vyhlásenie ALTER MONTÁŽE slúži na aktualizáciu zostavy Najnovšia verzia. Táto inštrukcia tiež pridáva alebo odstraňuje súbory spojené s príslušnou zostavou. DROP pokyn ZHROMAŽDENIE odstráni zadané zhromaždenie a všetky súvisiace súbory z aktuálnej databázy.
Nasledujúci príklad ukazuje, ako vytvoriť uloženú procedúru založenú na riadenom kóde implementovanom 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 predchádzajúcich príkladoch tým, že obsahuje Parameter EXTERNÉHO NÁZOV. Táto možnosť určuje, že kód generuje CLR. 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 - určuje názov zostavy;
class_name - určuje názov všeobecnej triedy;
názov_metódy - voliteľná časť, určuje názov metódy, ktorá je nastavená 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četZamestnanci VYTLAČIŤ @počet -- Vrátenie 7
Príkaz PRINT vráti aktuálny počet riadkov v tabuľke Zamestnanec.
uložená procedúra je možné len vtedy, ak sa vykonáva v kontexte databázy, kde sa postup nachádza.Typy uložených procedúr
SQL Server má niekoľko typov uložené procedúry.
- Systémové uložené procedúry 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, 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 vykonávať určité akcie. Uložené procedúry- kompletný 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 krátky č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 sú vytvorené. Pri vytváraní takejto procedúry musí dostať 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. Na jeho definovanie stačí dať mu 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 uložená procedúra: dočasné alebo vlastné. Okrem toho si môžete vytvoriť svoj vlastný systém uložená procedúra, pomenujte ho s predponou sp_ a umiestnite ho do systémovej databázy. Takýto postup bude dostupný v kontexte akejkoľvek databázy na lokálnom serveri;
- plánovanie prístupu. Pri tvorbe uložená procedúra majte 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 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 volania iných. 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 ) [=predvolene] ][,...n] AS sql_statement [...n]
Zvážte 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ému bude vytvorená procedúra patriť, ako aj názov databázy, kde má byť umiestnená. Teda s cieľom vyhovieť vytvorenému uložená procedúra v konkrétnej databáze musíte spustiť príkaz CREATE PROCEDURE v kontexte tejto databázy. Pri manipulácii z tela uložená procedúra Skrátené názvy možno použiť pre objekty v rovnakej databáze, t.j. bez zadania názvu databázy. Ak chcete odkazovať na objekty nachádzajúce sa 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 definuje 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.
Ak chcete odovzdať vstupné a výstupné údaje 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 Môžete zadať viacero možností oddelených čiarkami. Telo procedúry nesmie používať lokálne premenné, ktorých názvy sú rovnaké ako názvy parametrov procedúry.
Na určenie typu údajov, ktoré zodpovedajú parameter uloženej procedúry, je v poriadku akýkoľvek dátový typ SQL, vrátane užívateľom definovaných. Dátový typ CURSOR je však možné použiť len ako výstupný parameter uložená procedúra, t.j. s kľúčovým slovom VÝSTUP .
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á 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. Akékoľvek výrazy alebo konštanty povolené pre normálne parametre nie sú povolené.
Kľúčové slovo VARYING sa používa v spojení s