Egy olyan helyzetet veszünk figyelembe, amikor a tárolt eljárások ronthatják a lekérdezés teljesítményét.
Az MS SQL Server 2000 rendszerben tárolt eljárások összeállításakor a tárolt eljárások a procedurális gyorsítótárba kerülnek, ami javíthatja a teljesítményt végrehajtásuk során azáltal, hogy nincs szükség a tárolt eljáráskód elemzésére, optimalizálására és fordítására.
Másrészt vannak buktatók egy tárolt eljárás lefordított kódjának tárolásában, amelyek ellenkező hatást válthatnak ki.
A tény az, hogy egy tárolt eljárás összeállításakor az eljáráskódot alkotó operátorok végrehajtási terve összeállításra kerül, illetve ha a lefordított tárolt eljárás gyorsítótárazásra kerül, akkor annak végrehajtási terve is gyorsítótárazásra kerül, ezért a tárolt eljárás nem fog egy adott helyzetre és lekérdezési paraméterekre optimalizálható.
Egy kis kísérletet fog végezni ennek bemutatására.
1. LÉPÉS... Adatbázis létrehozása.
Hozzunk létre egy külön adatbázist a kísérlethez.
ADATBÁZIS LÉTREHOZÁSA test_sp_perf
BE (NÉV = "teszt_adatok", FÁJLNÉV = "c: \ temp \ teszt_adatok", MÉRET = 1, MAXSIZE = 10, FÁJLGYÁRTÁS = 1 Mb)
BEJELENTKEZÉS (NÉV = "teszt_napló", FÁJLNÉV = "c: \ temp \ teszt_napló", MÉRET = 1, MAXSIZE = 10, FÁJLGYÁRTÁS = 1 Mb)
2. LÉPÉS. Táblázat készítése.
TÁBLÁZAT LÉTREHOZÁSA sp_perf_test (oszlop1 int, oszlop2 karakter (5000))
3. LÉPÉS. A táblázat kitöltése tesztsorokkal. Az ismétlődő sorok szándékosan kerülnek hozzáadásra a táblázathoz. 10 000 sor 1-től 10 000-ig számozott, 10 000 sor pedig 50 000-ig.
NYILATKOZAT @i int
SET @ i = 1
Miközben én<10000)
KEZDŐDIK
INSERT INTO sp_perf_test (1. oszlop, 2. oszlop) ÉRTÉKEK (@i, "Tesztkarakterlánc #" + CAST (@i karakterként (8)))
INSERT INTO sp_perf_test (1. oszlop, 2. oszlop) ÉRTÉKEK (50000, "Tesztkarakterlánc #" + CAST (@i karakterként (8)))
BEÁLLÍTÁS @ i = @ i + 1
VÉGE
SZÁM (*) KIVÁLASZTÁSA AZ sp_perf_testből
Megy
4. LÉPÉS. Hozzon létre egy nem fürtözött indexet. Mivel a végrehajtási terv az eljárással gyorsítótárazva van, az index minden hívásnál ugyanazt fogja használni.
NEM CLUSTERED INDEX LÉTREHOZÁSA CL_perf_test ON sp_perf_test (1. oszlop)
Megy
5. LÉPÉS. Tárolt eljárás létrehozása. Az eljárás egyszerűen végrehajt egy SELECT utasítást egy feltétellel.
PROC proc1 LÉTREHOZÁSA (@param int)
MINT
SELECT oszlop1, oszlop 2 FROM sp_perf_test WHERE [e-mail védett]
Megy
6. LÉPÉS. Tárolt eljárás futtatása. Sebezhető eljárás indításakor speciálisan egy szelektív paramétert használnak. Az eljárás eredményeként 1 sort kapunk. A végrehajtási terv nem fürtözött index használatát jelzi, mert a lekérdezés szelektív, és ez a legjobb módja egy sor lekérésének. Egy sor lekérésére optimalizált eljárás az eljárási gyorsítótárban van tárolva.
EXEC proc1 1234
Megy
7. LÉPÉS. Tárolt eljárás futtatása nem szelektív paraméterrel. Paraméterként az 50 000-es értéket használjuk.. Azok a sorok, amelyeknek az első oszlopának ilyen értéke 10 000 körül van, a nem fürtözött index és a könyvjelző keresési művelet nem hatékony, de mivel a lefordított kód a végrehajtási tervvel a procedurális gyorsítótár, azt használni fogják. A végrehajtási terv ezt mutatja, valamint az is, hogy a könyvjelzőkeresési műveletet 9999 sorra hajtották végre.
EXEC proc1 50 000
Megy
8. LÉPÉS. 50 000-es első mezővel rendelkező sorok lekérése. Egy külön lekérdezés végrehajtása optimalizálja és összeállítja a lekérdezést az első oszlop adott értékével. Ennek eredményeként a lekérdezésoptimalizáló észleli, hogy a mező sokszor megkettőződik, és a táblaellenőrzési művelet mellett dönt, ami ebben az esetben sokkal hatékonyabb, mint egy nem fürtözött index használata.
SELECT oszlop1, oszlop2 FROM sp_perf_test WHERE oszlop1 = 50000
Megy
Ebből arra következtethetünk, hogy a tárolt eljárások használata nem mindig javítja a lekérdezés teljesítményét. Nagyon óvatosnak kell lennie azokkal a tárolt eljárásokkal, amelyek változó sorszámú eredményekkel dolgoznak, és különböző végrehajtási terveket használnak.
A szkript segítségével megismételheti a kísérletet egy friss MS SQL szerveren.
Tárolt eljárástípusok
Az SQL Serverben többféle típus létezik tárolt eljárások.
- Szisztémás tárolt eljárások különféle adminisztratív műveletek elvégzésére szolgálnak. Szinte minden szerveradminisztrációs művelet az ő segítségükkel történik. Mondhatjuk, hogy a rendszer tárolt eljárások egy olyan felület, amely rendszertáblázatokkal való munkát biztosít, ami végső soron a felhasználói és rendszeradatbázisok rendszertábláinak megváltoztatásában, hozzáadásában, törlésében és lekérésében rejlik. Szisztémás tárolt eljárások sp_ előtaggal vannak ellátva, a rendszer adatbázisában tárolódnak, és bármely más adatbázis kontextusában meghívhatók.
- Egyedi tárolt eljárások bizonyos műveleteket végrehajtani. Tárolt eljárások- egy teljes értékű adatbázis-objektum. Ennek eredményeként mindegyik tárolt eljárás egy adott adatbázisban található, ahol lefut.
- Ideiglenes tárolt eljárások csak egy ideig léteznek, majd a szerver automatikusan megsemmisíti őket. Helyi és globális csoportokra oszthatók. Helyi ideiglenes tárolt eljárások csak abból a kapcsolatból hívhatók meg, amelyben létrejöttek. Amikor létrehoz egy ilyen eljárást, olyan nevet kell adnia, amely egyetlen # karakterrel kezdődik. Mint minden ideiglenes objektum, tárolt eljárások Az ilyen típusú adatok automatikusan törlődnek, amikor a felhasználó leválasztja, újraindítja vagy leállítja a szervert. Globális ideiglenes tárolt eljárások elérhető minden olyan szerverkapcsolathoz, amelyen ugyanaz az eljárás. Ennek meghatározásához csak egy nevet kell adnia, amely ## szimbólumokkal kezdődik. Ezek az eljárások a kiszolgáló újraindításakor vagy leállításakor törlődnek, vagy ha a kapcsolat, amelyben létrejöttek, bezárul.
Tárolt eljárások létrehozása, módosítása és törlése
Teremtés tárolt eljárás a következő feladatok megoldását foglalja magában:
- a létrehozott típusának meghatározása tárolt eljárás: ideiglenes vagy egyedi. Ezenkívül létrehozhatja saját rendszerét tárolt eljárásúgy, hogy adunk neki egy nevet sp_ előtaggal, és elhelyezzük a rendszer adatbázisában. Ez az eljárás elérhető lesz a helyi szerver bármely adatbázisában;
- hozzáférési jogok tervezése. Alkotás közben tárolt eljárás szem előtt kell tartani, hogy ugyanazokkal a hozzáférési jogokkal rendelkezik az adatbázis-objektumokhoz, mint az azt létrehozó felhasználó;
- meghatározás tárolt eljárási paraméterek... Mint a legtöbb programozási nyelvben megtalálható eljárások, tárolt eljárások bemeneti és kimeneti paraméterei lehetnek;
- kód fejlesztés tárolt eljárás... Az eljáráskód bármilyen SQL-parancs sorozatát tartalmazhatja, beleértve a másoknak szóló hívásokat is. tárolt eljárások.
Új létrehozása és egy meglévő módosítása tárolt eljárás a következő paranccsal történik:
<определение_процедуры>:: = (CREATE | ALTER) eljárásnév [; szám] [(@ paraméter_neve adattípus) [= alapértelmezett]] [, ... n] AS sql_operator [... n]
Tekintsük ennek a parancsnak a paramétereit.
Az sp_, #, ## előtagok használatával a létrehozott eljárás rendszeresként vagy ideiglenesként definiálható. Amint a parancs szintaxisából látható, nem szabad megadni a tulajdonos nevét, aki a létrehozott eljárás tulajdonosa lesz, valamint annak az adatbázisnak a nevét, ahol az elhelyezkednie kell. Így annak érdekében, hogy a létrehozott tárolt eljárás egy adott adatbázison a CREATE PROCEDURE parancsot az adott adatbázis környezetében kell futtatnia. Testen kívüli kezeléskor tárolt eljárás Az azonos adatbázis objektumaihoz rövidített neveket is használhatunk, vagyis az adatbázis nevének megadása nélkül. Ha más adatbázisokban található objektumokra kell hivatkozni, meg kell adni az adatbázis nevét.
A névben szereplő szám az azonosító szám tárolt eljárás, amely egyedileg azonosítja az eljárások egy csoportjában. Az eljárások kezelésének kényelme érdekében logikailag azonos típusú tárolt eljárások azonos névvel, de eltérő azonosító számmal csoportosíthatók.
Bemeneti és kimeneti adatok átviteléhez a létrehozottban tárolt eljárás olyan paraméterek használhatók, amelyek nevének, akárcsak a helyi változók nevének, @ szimbólummal kell kezdődnie. Egy tárolt eljárás sok paraméter megadható, vesszővel elválasztva. Az eljárás törzse nem használhat olyan helyi változókat, amelyek neve megegyezik az eljárás paramétereinek nevével.
Annak meghatározása, hogy milyen típusú adatok lesznek a megfelelő tárolt eljárási paraméter, minden SQL adattípus, beleértve a felhasználó által meghatározottakat is, működni fog. A CURSOR adattípus azonban csak úgy használható, mint kimeneti paraméter tárolt eljárás, azaz az OUTPUT kulcsszó megadásával.
Az OUTPUT kulcsszó jelenléte azt jelenti, hogy a megfelelő paraméter adatokat küld vissza tárolt eljárás... Ez azonban egyáltalán nem jelenti azt, hogy a paraméter nem alkalmas az értékek átadására tárolt eljárás... Az OUTPUT kulcsszó megadása kilépésre utasítja a szervert tárolt eljárás rendelje hozzá a paraméter aktuális értékét ahhoz a helyi változóhoz, amelyet az eljárás hívásakor paraméterértékként adtunk meg. Vegye figyelembe, hogy az OUTPUT kulcsszó megadásakor a megfelelő paraméter értéke eljárás hívásakor csak helyi változó segítségével állítható be. Nem használhat olyan kifejezéseket vagy állandókat, amelyek normál paraméterekre érvényesek.
A VARYING kulcsszó a következővel együtt használatos:
Tárolt eljárás (angol. tárolt eljárás) egy elnevezett adatbázis-program objektum. Az SQL Serverben többféle tárolt eljárás létezik.
Rendszerben tárolt eljárások (angol. rendszerben tárolt eljárás) a DBMS-fejlesztők biztosítják, és a rendszerkatalógussal végzett műveletek végrehajtására vagy a rendszerinformációk megszerzésére használják. Nevük általában "sp_" előtaggal kezdődik. Minden típusú tárolt eljárást az EXECUTE paranccsal hívunk meg, amely rövidítve EXEC. Például az sp_helplogins tárolt eljárás, amely paraméterek nélkül fut, két jelentést generál a fióknevekről (eng. bejelentkezési adatok) és a hozzájuk tartozó felhasználók az egyes adatbázisokban (eng. felhasználók).
EXEC sp_helplogins;
Képet adni a rendszerben tárolt eljárások segítségével végrehajtott műveletekről táblázatban. A 10.6 néhány példát mutat be. Az SQL Serverben több mint ezer rendszerben tárolt eljárás található.
10.6. táblázat
SQL Server rendszerben tárolt eljárások példái
A felhasználó tárolt eljárásokat hozhat létre felhasználói adatbázisokban és ideiglenes objektumok adatbázisaiban. Ez utóbbi esetben a tárolt eljárás lesz időbeli. Az ideiglenes táblákhoz hasonlóan az ideiglenesen tárolt eljárás nevének "#" előtaggal kell kezdődnie, ha helyi ideiglenes tárolt eljárásról van szó, vagy "##" előtaggal, ha globális. A helyi ideiglenes eljárás csak azon a kapcsolaton belül használható, amelyben létrejött, a globális - és egyéb kapcsolatokon belül.
Az SQL Server programozható objektumai Transact-SQL eszközök vagy összeállítások segítségével hozhatók létre (eng. assembly) a Microsoft .Net Framework Common Language Runtime (CRL) programjában. Ebben az oktatóanyagban csak az első módszert vesszük figyelembe.
A tárolt eljárások a CREATE PROCEDURE utasítással jönnek létre (rövidíthető PROC), amelynek formátuma az alábbiakban látható:
LÉTREHOZÁS (PROC I ELJÁRÁS) proc_name [; szám]
[(gparameter data_type)
["Alapértelmezett] |
[VAL VEL
[REPLIKÁLÁSHOZ]
AS ([BEGIN] sql_utasítás [;] [... n] [END])
Ha egy tárolt eljárást (vagy triggert, függvényt, nézetet) a TITKOSÍTÁS opcióval hozunk létre, akkor annak kódja úgy alakul át, hogy a szöveg olvashatatlanná válik. Ugyanakkor, amint azt a cikkben megjegyeztük, a használt algoritmus az SQL Server korábbi verzióiból származik, és nem tekinthető megbízható védelmi algoritmusnak – vannak olyan segédprogramok, amelyek lehetővé teszik a fordított átalakítás gyors végrehajtását.
A RECOMPILE opció arra utasítja a rendszert, hogy az eljárás minden egyes meghívásakor fordítsa újra a szöveget. Általában az első indításkor összeállított eljárás a gyorsítótárba kerül, ami javíthatja a teljesítményt.
Az EXECUTE AS meghatározza azt a biztonsági környezetet, amelyben az eljárást végre kell hajtani. Ezután az egyik f CALLER | érték ÖNMAGA | TULAJDONOS | "felhasználónév"). A CALLER az alapértelmezett, és azt jelenti, hogy a kód a modult hívó felhasználó biztonsági környezetében kerül végrehajtásra. Ennek megfelelően a felhasználónak nem csak magára a programozott objektumra, hanem az általa érintett egyéb adatbázis-objektumokra is jogosultságokkal kell rendelkeznie. A VÉGREHAJTÁS MINT SELF azt jelenti, hogy felhasználói kontextusban hozunk létre vagy módosítunk egy programozható objektumot. A TULAJDONOS azt jelzi, hogy a kód az eljárás jelenlegi tulajdonosának kontextusában kerül végrehajtásra. Ha nincs hozzá definiálva tulajdonos, akkor annak a sémának a tulajdonosát kell érteni, amelyhez tartozik. EXECUTE AS "user_name" lehetővé teszi a felhasználónév explicit megadását (egy idézőjelben).
Az eljáráshoz paramétereket lehet megadni. Ezek olyan helyi változók, amelyek értéket adnak át az eljárásnak. Ha egy paramétert az OUTPUT kulcsszóval (vagy röviden OUT) deklarálunk, akkor az kimeneti paraméter: az eljárásban megadott értéket a befejezése után használhatja az eljárást meghívó program. A READONLY kulcsszó azt jelenti, hogy a paraméter értéke nem módosítható a tárolt eljáráson belül.
A paraméterekhez hozzárendelhetők értékek, de alapértelmezés szerint, ami akkor kerül felhasználásra, ha a paraméterérték nincs kifejezetten megadva az eljárás meghívásakor. Nézzünk egy példát:
PROC surma (@ a int, @b int = 0,
© eredmény in OUTPUT) AS
SET @ eredmény = 0a + 0b
Létrehoztunk egy három paraméteres eljárást, és a @b paraméter alapértelmezett értéke 0, a @result paraméter pedig egy kimeneti paraméter: ezen keresztül az érték visszakerül a hívó programba. A végrehajtott műveletek meglehetősen egyszerűek - a kimeneti paraméter a két bemenet összegének értékét kapja.
Az SQL Server Management Studio használatakor a létrehozott tárolt eljárás a Programozható DB objektumok részben található (eng. Programozhatóság) a tárolt eljárások alfejezetében (10.2. ábra).
Eljárások meghívásakor változókat és konstansokat is használhatunk bemeneti paraméterként. Nézzünk két példát. Az elsőben az eljárás bemeneti paramétereit kifejezetten konstansok állítják be, a hívás kimeneti paraméteréhez az OUTPUT kulcsszót adjuk meg. A második opcióban a változó értéke az első bemeneti paraméter, a második paraméternél pedig a DEFAULT kulcsszó használatával jelzi, hogy az alapértelmezett értéket kell használni:
Rizs. 10.2.
DECLARE @ int;
EXEC summa 10.5, @ c OUTPUT;
NYOMTATÁS 0c; - 15 jelenik meg
DECLARE Gi int = 5;
- híváskor használja az alapértelmezett értéket
EXEC summa Gi, ALAPÉRTELMEZETT, 0с OUTPUT;
NYOMTATÁS 0c; - 5 jelenik meg
Nézzünk most egy példát a visszatérési kód elemzésével, amellyel az eljárás véget ér. Legyen szükség arra, hogy kiszámoljuk, hány könyv jelent meg a Bookl táblázatban egy adott évtartományban. Ezenkívül, ha a kezdeti év többnek bizonyult, mint az utolsó év, az eljárás „1”-et ad vissza, és nem számít, ellenkező esetben a könyvek számát számoljuk, és 0-t adunk vissza:
PROC LÉTREHOZÁSA dbo.rownum (0FirsYear int, GLastYear int, 0result int OUTPUT) AS
HA 0FirsYear> 0LastYear VISSZATÉRÉS 1
SET @ result = (SELECT COUNT (*) FROM dbo.Bookl
HOL 0EGYESÉV ÉS 0Tavaly KÖZÖTT);
Tekintsük ennek az eljárásnak egy olyan változatát, amelyben a visszatérési kódot a 0ret egész változóban tároljuk, majd elemzi az értékét (ebben az esetben 1 lesz). A PRINT operátorban használt CAST függvény a Gres egész változó értékét karakterlánc típusúvá alakítja:
DECLARE 0ret int, Gres int
EXEC Gret = rownum 2004, 2002, Gres OUT;
IF 0ret = l PRINT "A kezdő év nagyobb, mint a záró év"
PRINT "Könyvek száma" + CAST (Gres mint varchar (20))
A tárolt eljárások nem csak adatokat tudnak kiolvasni egy táblából, hanem módosíthatják az adatokat, sőt táblákat és számos egyéb adatbázis-objektumot is létrehozhatnak.
Tárolt eljárásból azonban nem hozható létre sémák, függvények, triggerek, eljárások és nézetek.
A következő példa bemutatja ezeket a képességeket és az ideiglenes objektumok hatókörét. Az alábbi tárolt eljárás ellenőrzi a # thab2 ideiglenes tábla meglétét; ha ez a tábla nem létezik, akkor létrehozza. Ezt követően két oszlop értéke bekerül a # Tab2 táblába, és a táblázat tartalma a SELECT utasítással jelenik meg:
PROC LÉTREHOZÁSA My_Procl (@id int, @name varchar (30))
HA AZ OBJECT_ID ("tempdb.dbo. # Tab21) NULL
INSERT INTO dbo. # Tab2 (azonosító, név) ÉRTÉKEK (0id, 0name)
SELECT * FROM dbo. # Tab2 –№1
A tárolt eljárás első hívása előtt elkészítjük a benne használt # Thab2 ideiglenes táblát. Ügyeljen az EXEC operátorra. Az előző példákban a paramétereket „pozíció szerint” adtuk át az eljárásnak, és ebben az esetben egy másik formátumot használunk a paraméterek átadására - „név szerint”, a paraméter neve és értéke kifejezetten fel van tüntetve:
CREATE TABLE dbo. # Tab2 (id int, name varchar (30));
EXEC My_Procl 0name = "lvan", 0id = 2;
SELECT * FROM dbo # Tab2; –№2
A fenti példában a SELECT utasítás kétszer kerül végrehajtásra: először - az eljáráson belül, másodszor - a hívó kódrészletből ("# 2" megjegyzéssel).
Az eljárás második hívása előtt töröljük a # Tab2 ideiglenes táblát. Ezután a tárolt eljárásból létrejön az azonos nevű ideiglenes tábla:
DROP TABLE dbo # Tab2;
EXEC My_Procl 0name = "Iván", 0id = 2;
SELECT * FROM dbo # Tab2; –№2
Ebben az esetben csak az eljáráson belüli SELECT utasítás ("Xa 1" megjegyzéssel) jeleníti meg az adatokat. A SELECT "# 2" hibát eredményez, mert a tárolt eljárásban létrehozott ideiglenes tábla már törölve van a tempdb-ből, amikor az eljárás visszatér.
A DROP PROCEDURE utasítással eldobhat egy tárolt eljárást. Formátuma alább látható. Egy operátor több tárolt eljárást is törölhet, ha azokat vesszővel elválasztva felsorolja:
LEADÁS (PROC I. ELJÁRÁS) (eljárás) [
Például távolítsuk el a korábban létrehozott eljárás összegét:
DROP PROC summa;
Módosíthat egy meglévő eljárást (és valójában felülírhatja) az ALTER PROCEDURE utasítással (megengedhető
a PROC rövidítés). Az ALTER kulcsszó kivételével az utasítás formátuma hasonló a CREATE PROCEDURE-éhoz. Például változtassuk meg a dbo eljárást. rownum úgy, hogy végrehajtási opciót állít be a tulajdonos biztonsági környezetében:
ALTER PROC dbo.rownum (SFirsYear int,
SLastYear int, Result int OUTPUT)
WITH EXECUTE AS Owner egy telepíthető opció
HA 0FirsYear> 0LastYear VISSZATÉRÉS 1 MÁS KEZDÉS
SET 0result = (SZÁM VÁLASZTÁSA (*) FROM dbo.Bookl
HOL AZ SFirsYear ÉS SLastYear KÖZÖTT);
Bizonyos esetekben szükség lehet egy parancs dinamikus kialakítására és végrehajtására az adatbázis-kiszolgálón. Ez a feladat az EXEC operátor segítségével is megoldható. Az alábbi példa lekéri a rekordokat a Bookl táblából azzal a feltétellel, hogy az év attribútum egyenlő a változóval beállított értékkel:
DECLARE 0y int = 2000;
EXEC ("SELECT * FROM dbo.Bookl WHERE = [e-mail védett]) ;
A dinamikusan generált utasítások végrehajtása előfeltételeket teremt a számítógépes támadások, például az "SQL injekció" végrehajtásához. (eng. SQL injekció). A támadás lényege, hogy a támadó saját SQL kódját fecskendezi be egy dinamikusan generált lekérdezésbe. Ez általában akkor történik, amikor a helyettesített paramétereket a felhasználói bevitel eredményeiből veszik.
Változtassunk egy kicsit az előző példán:
DECLARE 0y varchar (100);
SET 0y = "2OOO"; - ezt kaptuk a felhasználótól
Ha feltételezzük, hogy a SET utasításban hozzárendelt string értéket megkaptuk a felhasználótól (mindegy, hogy pl. webalkalmazáson keresztül), akkor a példa kódunk "szokásos" viselkedését szemlélteti.
DECLARE 0y varchar (100);
SET 0y = "2000; TÖRLÉS A dbo.Book2-ből"; - injekció
EXEC ("SELECT * FROM dbo.Book2 WHERE = " + 0y);
Ilyen esetekben javasolt lehetőség szerint az sp_executcsql rendszerben tárolt eljárást használni, amely lehetővé teszi a paraméterek típusának szabályozását, ami az SQL injekció egyik gátja. Anélkül, hogy részletesen megvizsgálnánk a formátumát, elemezzünk egy, a korábban bemutatotthoz hasonló példát:
EXECUTE sp_executesql
N "SELECT * FROM dbo.Bookl WHERE = 0y",
Ez kifejezetten meghatározza a lekérdezésben használt paraméter típusát, és az SQL Server felügyeli a végrehajtás során. Az idézőjelek előtti "N" betű azt jelzi, hogy az eljárás által megkövetelt Unicode literális állandóról van szó. Egy paraméterhez nem csak állandó érték rendelhető, hanem egy másik változó értéke is.
Tárolt eljárás a Transact-SQL utasítások kötegének egy speciális típusa, amelyet az SQL nyelv és az eljárási kiterjesztések használatával hoztak létre. A fő különbség a csomag és a tárolt eljárás között az, hogy az utóbbi adatbázis-objektumként kerül tárolásra. Más szavakkal, a tárolt eljárások a szerver oldalon megmaradnak a teljesítmény és az ismételhetőség javítása érdekében.
Az adatbázismotor támogatja a tárolt eljárásokat és a rendszereljárásokat. A tárolt eljárások ugyanúgy jönnek létre, mint az összes többi adatbázis-objektum, azaz. a DDL nyelv használatával. Rendszereljárások az adatbázismotor biztosítja, és a rendszerkatalógusban lévő információk eléréséhez és módosításához használhatók.
Amikor létrehoz egy tárolt eljárást, megadhat egy opcionális paraméterlistát. Így az eljárás minden meghívásakor elfogadja a megfelelő argumentumokat. A tárolt eljárások visszaadhatnak egy felhasználó által definiált információkat tartalmazó értéket, vagy hiba esetén egy kapcsolódó hibaüzenetet.
A tárolt eljárást előre lefordítják, mielőtt objektumként tárolnák az adatbázisban. Az eljárás előre lefordított formája az adatbázisban tárolódik, és minden meghívásakor használatos. A tárolt eljárásoknak ez a tulajdonsága fontos előnyt jelent, mivel (majdnem minden esetben) kiküszöböli az eljárás újrafordítását, és ennek megfelelően javítja a teljesítményt. A tárolt eljárások ezen tulajdonsága pozitív hatással van az adatbázisrendszer és az alkalmazások közötti adatcsere mennyiségére is. Különösen egy több ezer bájt méretű tárolt eljárás hívása igényelhet 50 bájtnál kevesebbet. Ha több felhasználó hajt végre ismétlődő feladatokat tárolt eljárásokkal, ezeknek a megtakarításoknak a kumulatív hatása jelentős lehet.
A tárolt eljárások a következő célokra is felhasználhatók:
műveletnapló létrehozásához adatbázistáblákkal.
A tárolt eljárások használata olyan szintű biztonsági ellenőrzést biztosít, amely jelentősen meghaladja a felhasználók számára eltérő hozzáférési jogosultságokat biztosító GRANT és REVOKE utasítások által biztosított biztonsági szintet. Ez azért lehetséges, mert a tárolt eljárás végrehajtásának engedélyezése független az adott tárolt eljárásban lévő objektumok módosítására való jogosultságtól, a következő részben leírtak szerint.
A tárolt eljárások, amelyek naplókat generálnak a táblákon történő írásokhoz és/vagy olvasáshoz, további biztonságot nyújtanak az adatbázis számára. Ezekkel az eljárásokkal az adatbázis-adminisztrátor nyomon követheti a felhasználók vagy alkalmazások által az adatbázisban végrehajtott módosításokat.
Tárolt eljárások létrehozása és végrehajtása
A tárolt eljárások az utasítás segítségével jönnek létre ELJÁRÁS LÉTREHOZÁSA amely a következő szintaxissal rendelkezik:
CREATE PROC proc_name [((@ param1) type1 [VARYING] [= default1])] (,…) AS köteg | EXTERNAL NAME metódus_neve Szintaxis konvenciók
A séma_neve paraméter a generált tárolt eljárás tulajdonosaként hozzárendelt séma nevét adja meg. A proc_name paraméter a tárolt eljárás nevét adja meg. A @ param1 paraméter egy eljárásparaméter (formális argumentum), amelynek adattípusát a type1 paraméter határozza meg. Az eljárás paraméterei lokálisak az eljáráson belül, ahogy a helyi változók is helyiek egy csomagon belül. Az eljárási paraméterek olyan értékek, amelyeket a hívó átad az eljárásnak, hogy felhasználja. A default1 paraméter határozza meg a megfelelő eljárási paraméter alapértelmezett értékét. (Az alapértelmezett érték NULL is lehet.)
OUTPUT opció azt jelzi, hogy az eljárásparaméter egy visszaadható paraméter, amely felhasználható egy tárolt eljárás értékének visszaadására a hívó eljárásnak vagy rendszernek.
Mint korábban említettük, az eljárás előre lefordított formája az adatbázisban tárolódik, és minden meghívásakor használatos. Ha valamilyen okból a tárolt eljárást minden meghívásakor le kell fordítani, az eljárás deklarálásakor használja a opció WITH RECOMPILE... A WITH RECOMPILE opció használata tagadja a tárolt eljárások egyik legfontosabb előnyét: az egyetlen fordításból származó teljesítménynövekedést. Ezért a WITH RECOMPILE beállítást csak akkor szabad használni, ha gyakran módosítja a tárolt eljárás által használt adatbázis-objektumokat.
EXECUTE AS záradék meghatározza azt a biztonsági környezetet, amelyben a tárolt eljárásnak a meghívása után végre kell hajtania. Ennek a kontextusnak a beállításával az adatbázismotor segítségével vezérelheti a felhasználói fiókok kiválasztását a tárolt eljárás által hivatkozott objektumok hozzáférési engedélyeinek ellenőrzéséhez.
Alapértelmezés szerint csak a sysadmin rögzített kiszolgálói szerepkör és a db_owner vagy a db_ddladmin rögzített adatbázisszerepkör tagjai használhatják a CREATE PROCEDURE utasítást. De ezeknek a szerepeknek a tagjai ezt a jogot más felhasználókhoz is hozzárendelhetik az utasítás alapján A TÁMOGATÁS LÉTREHOZÁSI ELJÁRÁSA.
Az alábbi példa bemutatja, hogyan hozhat létre egy egyszerű tárolt eljárást a Project táblával való együttműködéshez:
SampleDb HASZNÁLATA; ELJÁRÁS LÉTREHOZÁSA Költségkeret növelése (@százalék INT = 5) FRISSÍTÉSKÉNT Projekt SET Költségvetés = Költségvetés + Költségvetés * @ százalék / 100;
Ahogy korábban említettük, két csomag szétválasztásához használja a GO utasítás... A CREATE PROCEDURE utasítás nem kombinálható más Transact-SQL utasításokkal ugyanabban a csomagban. A IncreaseBudget tárolt eljárás megnöveli az összes projekt költségvetését a @percent paraméter által meghatározott százalékos számmal. Az eljárás egy alapértelmezett százalékértéket (5) is meghatároz, amelyet akkor használ, ha ez az argumentum nincs jelen az eljárás során.
A tárolt eljárások hozzáférhetnek a nem létező táblákhoz. Ez a tulajdonság lehetővé teszi az eljáráskód hibakeresését anélkül, hogy először létrehozná a megfelelő táblákat, vagy csatlakozna a célkiszolgálóhoz.
Az alapvető tárolt eljárásoktól eltérően, amelyek mindig az aktuális adatbázisban vannak tárolva, lehetőség van ideiglenes tárolt eljárások létrehozására, amelyek mindig a tempdb ideiglenes rendszeradatbázisba kerülnek. Az ideiglenesen tárolt eljárások létrehozásának egyik oka lehet, hogy az adatbázishoz való kapcsolódás során elkerüljük az utasítások egy csoportjának ismétlődő végrehajtását. Létrehozhat helyi vagy globális ideiglenes eljárásokat. Ehhez a helyi eljárás neve egyetlen # szimbólummal (#proc_name), a globális eljárás neve pedig kettőssel (## proc_name) van megadva.
A helyi ideiglenesen tárolt eljárást csak az azt létrehozó felhasználó hajthatja végre, és csak az adatbázishoz való kapcsolódás során, amelyben létrehozták. A globális ideiglenes eljárást minden felhasználó végrehajthatja, de csak addig, amíg az utolsó végrehajtási kapcsolat be nem fejeződik (általában az eljárás létrehozójának kapcsolata).
Egy tárolt eljárás életciklusa két szakaszból áll: létrehozásából és végrehajtásából. Minden eljárás egyszer jön létre, és ismételten végrehajtásra kerül. A tárolt eljárást a EXCUTE utasításokat olyan felhasználó, aki egy eljárás tulajdonosa vagy VÉGREHAJTÁSI engedéllyel rendelkezik az eljárás eléréséhez. Az EXECUTE utasítás szintaxisa a következő:
[] [@return_status =] (proc_name | @proc_name_var) ([[@ paraméter1 =] érték | [@ paraméter1 =] @változó] | ALAPÉRTELMEZETT) .. Szintaxis konvenciók
A return_status paraméter kivételével az EXECUTE utasítás minden paramétere ugyanazzal a logikai jelentéssel rendelkezik, mint a CREATE PROCEDURE utasítás azonos nevű paraméterei. A return_status paraméter egy egész változót határoz meg, amely az eljárás visszatérési állapotát tárolja. Egy érték hozzárendelhető egy paraméterhez konstans (érték) vagy helyi változó (@változó) használatával. A megnevezett paraméterek értékeinek sorrendje nem fontos, de a névtelen paraméterek értékeit a CREATE PROCEDURE utasításban meghatározott sorrendben kell megadni.
DEFAULT záradék alapértelmezett értékeket biztosít az eljárás definíciójában megadott eljárásparaméterekhez. Hiba történik, ha egy eljárás értéket vár egy olyan paraméterhez, amelyhez nincs alapértelmezett érték, és nincs megadva paraméter, vagy a DEFAULT kulcsszó van megadva.
Ha az EXECUTE utasítás az első utasítás egy kötegben, az EXECUTE kulcsszó elhagyható. Biztonságosabb azonban minden csomagban szerepeltetni ezt a szót. Az EXECUTE utasítás használatát az alábbi példa mutatja be:
SampleDb HASZNÁLATA; VÉGREHAJTÁSA Költségkeret növelése 10;
Az EXECUTE utasítás ebben a példában végrehajtja az IncreaseBudget tárolt eljárást, amely az összes projekt költségvetését 10%-kal növeli.
Az alábbi példa bemutatja, hogyan hozhat létre tárolt eljárást az Employee és Works_on táblákban lévő adatok feldolgozásához:
A példában szereplő ModifyEmpId eljárás a tárolt eljárások használatát mutatja be a hivatkozási integritási folyamat részeként (ebben az esetben az Employee és Works_on táblák között). Hasonló tárolt eljárás használható egy trigger-definíción belül, amely ténylegesen érvényesíti a hivatkozási integritást.
Az alábbi példa az OUTPUT záradék használatát mutatja be egy tárolt eljárásban:
Ez a tárolt eljárás a következő utasítások segítségével hajtható végre:
DECLARE @quantityDeleteEmployee INT; VÉGREHAJTÁS DeleteEmployee @ empId = 18316, @ [e-mail védett] KIMENET; PRINT N "Törölt alkalmazottak:" + konvertálás (nvarchar (30), @quantityDeleteEmployee);
Ez az eljárás megszámolja azoknak a projekteknek a számát, amelyekben az @empId személyi számmal rendelkező alkalmazott elfoglalt, és a kapott értéket hozzárendeli a © számláló paraméterhez. Miután egy adott személyi számhoz tartozó összes sort törölte az Employee és Works_on táblákból, a számított értéket a rendszer a @quantityDeleteEmployee változóhoz rendeli.
A paraméterérték csak akkor kerül vissza a hívó eljárásba, ha az OUTPUT opció meg van adva. A fenti példában a DeleteEmployee eljárás átadja a @counter paramétert a hívó eljárásnak, így a tárolt eljárás értéket ad vissza a rendszernek. Ezért a @counter paramétert mind az OUTPUT opcióban meg kell adni egy eljárás deklarálásakor, mind az EXECUTE utasításban annak meghívásakor.
WITH RESULTS SETS az EXECUTE utasítás záradéka
Az SQL Server 2012 rendszerben az EXECUTE utasítás kerül megadásra WITH RESULTS SETS záradék, amelyen keresztül bizonyos feltételek teljesülése esetén módosíthatja a tárolt eljárás eredményhalmazának formáját.
A következő két példa segít ennek a mondatnak a magyarázatában. Az első példa egy bevezető példa, amely megmutatja, hogyan nézhet ki az eredmény, ha a WITH RESULTS SETS záradékot kihagyjuk:
Az EmployeesInDept eljárás egy egyszerű eljárás, amely megjeleníti az adott részlegen dolgozó összes alkalmazott személyi számát és vezetéknevét. Az osztályszám az eljárás paramétere, és hívásakor meg kell adni. Ezt az eljárást végrehajtva egy két oszlopos tábla jelenik meg, amelyek fejlécei egybeesnek az adatbázistábla megfelelő oszlopainak nevével, azaz. Azonosító és vezetéknév. Az SQL Server 2012 az új WITH RESULTS SETS záradékot használja az eredményoszlop fejléceinek (valamint azok adattípusának) módosításához. Ennek a javaslatnak az alkalmazását az alábbi példa mutatja be:
SampleDb HASZNÁLATA; EXEC EmployeesInDept "d1" EREDMÉNY HASZNÁLATÁVAL ((INT NEM NULL, [Vezetéknév] CHAR (20) NOT NULL));
Az így meghívott tárolt eljárás végrehajtásának eredménye a következő lesz:
Amint látható, a tárolt eljárás futtatása az EXECUTE utasítás WITH RESULT SETS záradékával lehetővé teszi az eljárás által visszaadott eredményhalmaz oszlopainak nevének és adattípusának megváltoztatását. Így ez az új funkció nagyobb rugalmasságot biztosít a tárolt eljárások végrehajtásában és eredményeik új táblában való elhelyezésében.
A tárolt eljárások szerkezetének megváltoztatása
Az adatbázismotor is alátámasztja ezt az állítást ELJÁRÁS MÓDOSÍTÁSA a tárolt eljárások szerkezetének módosítására. Az ALTER PROCEDURE általában a Transact-SQL utasítások eljáráson belüli módosítására szolgál. Az ALTER PROCEDURE utasítás összes paraméterének jelentése megegyezik a CREATE PROCEDURE utasítás azonos nevű paramétereivel. Az utasítás használatának fő célja a meglévő tárolt eljárásjogok felülbírálásának elkerülése.
Az adatbázismotor támogatja adattípus CURSOR... Ez az adattípus a kurzorok deklarálására szolgál a tárolt eljárásokban. Kurzor egy programozási konstrukció, amely egy lekérdezés eredményeinek (általában sorok halmazának) tárolására szolgál, és lehetővé teszi a felhasználók számára, hogy az eredményt soronként jelenítsék meg.
A tárolt eljárások egy vagy csoportjának törléséhez használja a ELJÁRÁS nyilatkozat... Csak a db_owner és a sysadmin rögzített szerepkörök tulajdonosa vagy tagjai törölhetnek egy tárolt eljárást.
Tárolt eljárások és a CLR
Az SQL Server támogatja a Common Language Runtime (CLR) szabványt, amely lehetővé teszi különböző adatbázis-objektumok (tárolt eljárások, UDF-ek, triggerek, UDF-ek és UDD-k) fejlesztését C # és Visual Basic használatával. A CLR lehetővé teszi ezen objektumok végrehajtását is a közös futásidejű rendszer használatával.
Az opció engedélyezi és letiltja a CLR-t clr_enabled rendszer eljárása sp_configure, amelynek végrehajtását az utasítás indítja el ÚJRAKONFIGURÁLÁS... Az alábbi példa bemutatja, hogyan engedélyezhető a CLR az sp_configure rendszereljárással:
SampleDb HASZNÁLATA; EXEC sp_configure "clr_enabled", 1 RECONFIGURE
Egy eljárás CLR használatával történő létrehozásához, összeállításához és mentéséhez a következő lépések sorrendjét kell végrehajtania:
Hozzon létre egy tárolt eljárást C # vagy Visual Basic nyelven, majd fordítsa le a megfelelő fordító segítségével.
Az utasítás felhasználásával ÖSSZESZERELÉS LÉTREHOZÁSA, hozza létre a megfelelő végrehajtható fájlt.
Hajtsa végre az eljárást az EXECUTE utasítás használatával.
Az alábbi ábra a korábban vázolt lépések folyamatábráját mutatja be. Az alábbiakban ennek a folyamatnak a részletesebb leírása található.
Először hozza létre a szükséges programot egy fejlesztői környezetben, például a Visual Studioban. Fordítsa le a kész programot objektumkódra a C # vagy a Visual Basic fordító segítségével. Ez a kód egy dinamikus hivatkozási könyvtár (.dll) fájlba kerül mentésre, amely a CREATE ASSEMBLY utasítás forrása, amely végrehajtható köztes kódot hoz létre. Ezután futtassa a CREATE PROCEDURE utasítást a végrehajtható kód adatbázis-objektumként való mentéséhez. Végül futtassa az eljárást az ismerős EXECUTE utasítással.
Az alábbi példa egy C #-ban tárolt eljárás forráskódját mutatja be:
A System.Data.SqlClient használata; Microsoft.SqlServer.Server használatával; nyilvános részleges osztály StoredProcedures (nyilvános statikus int CountEmployees () (int sorok; SqlConnection kapcsolat = új SqlConnection ("Context Connection = true"); connection.Open (); SqlCommand cmd = kapcsolat.CreateCommand (); cmd.CommandText = "kiválasztás count (*) mint "Alkalmazottak száma" "+" in Employee "; sorok = (int) cmd.ExecuteScalar (); connect.Close (); sorok visszaadása;))
Ez az eljárás egy lekérdezést valósít meg az Employee tábla sorainak megszámlálására. A program elején található use direktívák meghatározzák a program végrehajtásához szükséges névtereket. Ezen direktívák használata lehetővé teszi osztálynevek megadását a forráskódban anélkül, hogy kifejezetten megadná a megfelelő névtereket. Ezután meghatározzuk a StoredProcedures osztályt, amelyhez a SqlProcedure attribútum amely tájékoztatja a fordítót arról, hogy ez az osztály egy tárolt eljárás. A CountEmployees () metódus az osztálykódon belül van definiálva. Az adatbázis-rendszerrel való kapcsolat az osztály egy példányán keresztül jön létre SqlConnection... A kapcsolat megnyitásához az adott példány Megnyitás () metódusát kell használni. A CreateCommand () metódus lehetővé teszi az osztály egy példányának elérését SqlCommnd amelyhez a szükséges SQL-parancsot továbbítják.
A következő kódrészletben:
Cmd.CommandText = "a szám kiválasztása (*) mint" Alkalmazottak száma "" + "Alkalmazotttól";
egy SELECT utasítást használ az Employee tábla sorainak megszámlálására és az eredmény megjelenítésére. A parancsszöveg úgy adható meg, hogy a cmd változó CommandText tulajdonságát a CreateCommand () metódus által visszaadott példányra állítja be. A következőt hívják ExecuteScalar () metódus egy SqlCommand példány. Ez a metódus egy skaláris értéket ad vissza, amelyet a rendszer int adattípusra konvertál, és hozzárendel a sorokhoz.
Most már lefordíthatja ezt a kódot a Visual Studio segítségével. Ezt az osztályt hozzáadtam a CLRStoredProcedures nevű projekthez, így a Visual Studio az azonos nevű összeállítást * .dll kiterjesztéssel fogja lefordítani. Az alábbi példa a tárolt eljárás létrehozásának következő lépését mutatja be: a végrehajtható kód létrehozását. A példában szereplő kód futtatása előtt ismernie kell a lefordított dll fájl helyét (általában a projekt Debug mappájában található).
SampleDb HASZNÁLATA; GO CREATE ASSEMBLY CLRStoredProcedures A "D: \ Projects \ CLRStoredProcedures \ bin \ Debug \ CLRStoredProcedures.dll" AZ PERMISSION_SET = BIZTONSÁGOS BEÁLLÍTÁSÁRA
A CREATE ASSEMBLY utasítás kezelt kódot használ bemenetként, és létrehoz egy megfelelő objektumot, amelyhez CLR-ben tárolt eljárásokat, UDF-eket és triggereket hozhat létre. Ennek az utasításnak a szintaxisa a következő:
CREATE ASSEMBLY assembly_name [AUTHORIZATION, tulajdonos_neve] FROM (dll_fájl) Szintaxis konvenciók
Az összeállítás_neve az összeállítás nevét adja meg. Az opcionális AUTHORIZATION záradék megadja a szerepkör nevét az összeállítás tulajdonosaként. A FROM záradék megadja azt az elérési utat, ahol a betöltendő összeállítás található.
PERMISSION_SET záradékkal a CREATE ASSEMBLY utasítás nagyon fontos kitétele, és mindig szerepelnie kell. Meghatározza az összeállítási kódhoz biztosított hozzáférési jogok készletét. A BIZTONSÁGOS jogkészlet a legkorlátozóbb. Az ezekkel a jogokkal rendelkező összeállítási kódok nem férhetnek hozzá külső rendszererőforrásokhoz, például fájlokhoz. Az EXTERNAL_ACCESS jogosultságkészlet lehetővé teszi az összeállítás kódjának, hogy hozzáférjen bizonyos külső rendszererőforrásokhoz, míg az UNSAFE jogosultságkészlet korlátlan hozzáférést biztosít az erőforrásokhoz, az adatbázisrendszeren belül és kívül egyaránt.
A felhasználónak képesnek kell lennie a CREATE ASSEMBLY utasítás végrehajtására, hogy megőrizze az összeállítási kóddal kapcsolatos információkat. Az összeállítás annak a felhasználónak (vagy szerepkörnek) a tulajdonosa, aki végrehajtja ezt az utasítást. Az összeállítás tulajdonosát másik felhasználóvá teheti a CREATE SCHEMA utasítás AUTHORIZATION záradékának használatával.
Az adatbázismotor támogatja az ALTER ASSEMBLY és a DROP ASSEMBLY utasításokat is. ALTER ASEMBLY Nyilatkozat az összeállítás legújabb verzióra való frissítésére szolgál. Ez az utasítás a megfelelő összeállításhoz társított fájlokat is hozzáadja vagy eltávolítja. DROP ASEMBLY nyilatkozat eltávolítja a megadott összeállítást és az összes kapcsolódó fájlt az aktuális adatbázisból.
Az alábbi példa bemutatja, hogyan hozhat létre tárolt eljárást a korábban implementált felügyelt kód alapján:
SampleDb HASZNÁLATA; GO ELJÁRÁS LÉTREHOZÁSA CountEmployees MINT KÜLSŐ NÉV CLRStoredProcedures.StoredProcedures.CountEmployees
A példa CREATE PROCEDURE utasítása abban különbözik a korábbi példákban szereplő utasítástól, hogy tartalmaz EXTERNAL NAME paraméter... Ez a paraméter azt jelzi, hogy a kódot a CLR generálja. Ebben a mondatban a név három részből áll:
összeállítás_neve.osztály_neve.módszerneve
összeállítás_neve Megadja az összeállítás nevét.
osztály_neve - az általános osztály nevét jelzi;
módszer_neve - nem kötelező, az osztályon belül megadott metódus nevét adja meg.
A CountEmployees eljárás végrehajtása az alábbi példában látható:
SampleDb HASZNÁLATA; DECLARE @count INT EXECUTE @count = Alkalmazottak száma PRINT @count - Returns 7
A PRINT utasítás az Employee tábla sorainak aktuális számát adja vissza.
A Microsoft SQL Serverben saját algoritmusainak megvalósításához és automatizálásához ( számításokat), használhatja a tárolt eljárásokat, ezért ma ezek létrehozásáról, módosításáról és törléséről lesz szó.
De először egy kis elmélet, hogy megértse, mik azok a tárolt eljárások, és mire valók a T-SQL-ben.
Jegyzet! Kezdő programozóknak ajánlom a következő hasznos anyagokat a T-SQL témában:
- A T-SQL nyelv részletesebb tanulmányozásához ajánlom a The T-SQL Programmer's Way című könyv elolvasását is. Oktatóanyag a Transact-SQL nyelvhez.
Mik azok a tárolt eljárások a T-SQL-ben?
Tárolt eljárások Olyan adatbázis-objektumok, amelyek egy algoritmust tartalmaznak SQL utasításkészlet formájában. Más szóval azt mondhatjuk, hogy a tárolt eljárások egy adatbázison belüli programok. A tárolt eljárások az újrafelhasználható kódok szerverre mentésére szolgálnak, például írt egy algoritmust, szekvenciális számítást vagy többlépcsős SQL utasítást, és annak érdekében, hogy az algoritmusban szereplő összes utasítást ne hajtsa végre minden alkalommal, megtervezheti azt mint tárolt eljárás. Ugyanakkor, amikor létrehoz egy SQL-eljárást, a szerver lefordítja a kódot, majd minden alkalommal, amikor ezt az SQL-eljárást futtatja, a szerver többé nem fordítja újra.
Egy tárolt eljárás SQL Serverben való futtatásához a neve elé kell írni az EXECUTE parancsot, vagy lehetséges a rövidített EXEC parancs is. Egy SELECT utasításban tárolt eljárás meghívása például függvényként már nem fog működni, pl. az eljárások külön futnak.
A tárolt eljárásokban a függvényekkel ellentétben már végezhetünk adatmódosítási műveleteket, mint például: UNSERT, UPDATE, DELETE. Ezenkívül az eljárásokban szinte bármilyen típusú SQL utasítást használhat, például a CREATE TABLE táblák létrehozásához vagy az EXECUTE, azaz az EXECUTE, azaz. egyéb eljárások kihívása. Kivételt képeznek az utasítások többféle típusa, mint például: függvények, nézetek, triggerek létrehozása vagy módosítása, sémák létrehozása és néhány más hasonló utasítás, például az adatbázis-kapcsolati kontextus (USE) váltása sem lehetséges egy tárolt eljárásban.
Egy tárolt eljárásnak lehetnek bemeneti és kimeneti paraméterei, táblázatos adatokat adhat vissza, semmit nem ad vissza, csak a benne foglalt utasításokat hajtja végre.
Nagyon hasznosak a tárolt eljárások, amelyek segítségével számos műveletet automatizálhatunk vagy egyszerűsíthetünk, például folyamatosan kell különféle összetett analitikai jelentéseket készíteni pivot táblák segítségével, pl. a PIVOT operátor. A lekérdezések létrehozásának megkönnyítése érdekében ezzel az operátorral ( mint tudod, a PIVOT szintaxisa meglehetősen bonyolult), Írhat olyan eljárást, amely dinamikusan generál összefoglaló jelentéseket az Ön számára, például a "Dinamikus PIVOT T-SQL-ben" anyagban bemutatunk egy példát arra, hogyan lehet ezt a szolgáltatást tárolt eljárás formájában megvalósítani.
Példák a Microsoft SQL Server tárolt eljárásainak használatára
Kiindulási adatok példákhoz
Az alábbi példák mindegyike a Microsoft SQL Server 2016 Express rendszerben kerül végrehajtásra. Ahhoz, hogy bemutathassuk, hogyan működnek a tárolt eljárások valós adatokkal, szükségünk van ezekre az adatokra, hozzuk létre. Például hozzunk létre egy teszttáblát, és adjunk hozzá több rekordot, tegyük fel, hogy ez egy táblázat, amely a termékek listáját tartalmazza az árakkal.
Táblázat létrehozására vonatkozó utasítás CREATE TABLE TestTable (INT IDENTITY (1,1) NOT NULL, INT NOT NULL, VARCHAR (100) NOT NULL, MONEY NULL) GO - Utasítás az adatok hozzáadásához INSERT INTO TestTable (CategoryId, Product Name, Price) VALUES (1 , "Egér", 100), (1, "Billentyűzet", 200), (2, "Telefon", 400) GO -- Kérelem kiválasztása SELECT * FROM TestTable
Az adatok megvannak, most térjünk át a tárolt eljárások létrehozására.
Tárolt T-SQL-eljárás létrehozása – ELJÁRÁS LÉTREHOZÁSA
A tárolt eljárások az utasítás segítségével jönnek létre ELJÁRÁS LÉTREHOZÁSA, ezen utasítás után meg kell írni az eljárásod nevét, majd ha szükséges, zárójelben megadni a bemeneti és kimeneti paramétereket. Ezután írja be az AS kulcsszót, és a BEGIN kulcsszóval nyitja meg az utasításblokkot, zárja be ezt a blokkot az END szóval. Ebbe a blokkba beírod az összes utasítást, ami megvalósítja az algoritmusodat vagy valamilyen szekvenciális számítást, vagyis T-SQL-ben programozsz.
Például írjunk egy tárolt eljárást, amely új rekordot ad hozzá, pl. új termék a teszttáblázatunkra. Ehhez három bejövő paramétert fogunk definiálni: @CategoryId - termékkategória azonosító, @ProductName - terméknév és @Price - termék ára, ez a paraméter nem kötelező, pl. nem lehet átadni az eljárásnak ( például még nem tudjuk az árat), ehhez a definíciójában beállítjuk az alapértelmezett értéket. Ezek a paraméterek az eljárás törzsében vannak, azaz. a BEGIN ... END blokkban ugyanúgy használható, mint a közönséges változókban ( mint tudod, a változókat @ jelöli). Ha meg kell adnia a kimeneti paramétereket, akkor a paraméter neve után adja meg az OUTPUT ( vagy rövidítve OUT).
A BEGIN… END blokkban írunk egy utasítást az adatok hozzáadására, valamint az eljárás végén egy SELECT utasítást, hogy a tárolt eljárás táblázatos adatokat adjon vissza a megadott kategóriába tartozó termékekről, figyelembe véve az új , most hozzáadott termék. Ebben a tárolt eljárásban is hozzáadtam a beviteli paraméter feldolgozását, nevezetesen a felesleges szóközök eltávolítását a szövegsor elején és végén, hogy elkerüljük azokat a helyzeteket, amikor véletlenül több szóköz került beszúrásra.
Itt van az eljárás kódja ( Hozzászóltam is).
Eljárás létrehozása ELJÁRÁS LÉTREHOZÁSA Teszteljárás (--Beviteli paraméterek @CategoryId INT, @ProductName VARCHAR (100), @PÉNZár = 0) KEZDÉSÉN - Az algoritmust megvalósító utasítások - A bejövő paraméterek kezelése - A felesleges szóközök törlése az elején és a szövegsor végén SET @ProductName = LTRIM (RTRIM (@ProductName)); -- Adjon hozzá egy új INSERT INTO teszttáblázatot (CategoryId, Product Name, Price) VALUES (@CategoryId, @ProductName, @Price) rekord - Az adatok visszaadása SELECT * FROM TestTable WHERE CategoryId = @CategoryId END GO
T-SQL tárolt eljárás futtatása – VÉGREHAJTÁS Parancs
A tárolt eljárást elindíthatja, amint azt már megjegyeztem, az EXECUTE vagy EXEC paranccsal. A bejövő paramétereket egyszerűen felsoroljuk, és az eljárás neve után megadjuk a megfelelő értékeket ( a kimeneti paramétereknél meg kell adni az OUTPUT parancsot is). A paraméterek neve azonban elhagyható, de ebben az esetben az értékek megadásának sorrendjét kell követni, pl. adja meg az értékeket a bemeneti paraméterek meghatározásának sorrendjében ( ez vonatkozik a kimeneti paraméterekre is).
Az alapértelmezett értékkel rendelkező paramétereket nem kell megadni, ezek úgynevezett opcionális paraméterek.
Íme néhány eltérő, de egyenértékű mód a tárolt eljárások futtatására, különösen a teszteljárásunkra.
1. Hívja meg az eljárást az ár megadása nélkül EXECUTE TestProcedure @CategoryId = 1, @ProductName = "1. teszttermék" --2. Az eljárást az árjelzéssel EXEC TestProcedure @CategoryId = 1, @ProductName = "2. teszttermék", @Price = 300 --3. Az eljárást a paraméterek nevének megadása nélkül hívjuk meg EXEC TestProcedure 1, "Test Product 3", 400
Tárolt eljárás módosítása T-SQL - ALTER PROCEDURE utasításra
Az utasítások segítségével módosíthatja az eljárás algoritmusát ELJÁRÁS MÓDOSÍTÁSA... Más szóval, egy meglévő eljárás megváltoztatásához csak az ALTER PROCEDURE parancsot kell írnia a CREATE PROCEDURE helyett, és minden mást szükség szerint módosítania kell.
Tegyük fel, hogy módosítani kell a teszteljárásunkon, mondjuk a @Price paramétert, pl. árat, akkor kötelezővé tesszük, ehhez eltávolítjuk az alapértelmezett értéket, és azt is képzeljük el, hogy már nem kell beszereznünk a kapott adatkészletet, ehhez egyszerűen eltávolítjuk a SELECT utasítást a tárolt eljárásból.
Módosítjuk az ALTER PROCEDURE Teszteljárást (--Bejövő paraméterek @CategoryId INT, @ProductName VARCHAR (100), @Price MONEY) AS BEGIN --Utasítások, amelyek megvalósítják az algoritmust --A bemeneti paraméterek kezelése - A felesleges szóközök eltávolítása az elején és a a szövegsorok vége SET @ProductName = LTRIM (RTRIM (@ProductName)); -- Adjon hozzá egy új INSERT INTO teszttáblázatot (CategoryId, Product Name, Price) VALUES (@CategoryId, @ProductName, @Price) END GO rekord
Tárolt T-SQL-eljárás eldobása – ELJÁRÁS DROP PROCEDURE utasítás
Ha szükséges, törölheti a tárolt eljárást, ez az utasítás segítségével történik LEADÁSI ELJÁRÁS.
Például töröljük az általunk létrehozott teszteljárást.
DROP ELJÁRÁS Teszteljárás
A tárolt eljárások törlésekor érdemes megjegyezni, hogy ha az eljárásra más eljárások vagy SQL utasítások hivatkoznak, akkor azok a törlés után meghiúsulnak, mert az általuk hivatkozott eljárás már nem létezik.
Mindenem megvan, remélem az anyag érdekes és hasznos volt számodra, eddig!
