Tárolt eljárások ms sql szerver környezetben. Tárolt eljárások a T-SQL-ben – Létrehozás, módosítás, törlés

1. Vegye bele az eljárásokba a - SET NOCOUNT ON sort: Az SQL szerver minden DML utasításnál gondosan visszaküld egy üzenetet, amely tartalmazza a feldolgozott rekordok számát. Ez az információ hasznos lehet számunkra a kód hibakeresése során, de utána teljesen használhatatlan lesz. A SET NOCOUNT ON írásával letiltjuk ezt a funkciót. A több kifejezést vagy\and ciklust tartalmazó tárolt eljárások esetében ez a művelet jelentős teljesítménynövekedést adhat, mivel a forgalom mennyisége jelentősen csökken.

PROC LÉTREHOZÁSA dbo.ProcName
MINT
SET NOCOUNT ON;
-- Az eljárás kódja itt
SELECT oszlop1. A dbo.TblTable1
-- Kapcsolja a SET NOCOUNT beállítást kezdeti állapotba
SET NO COUNT OFF;
MEGY

2. Használja a séma nevét az objektumnévvel: Nos, szerintem egyértelmű. Ez a művelet megmondja a szervernek, hogy hol keresse az objektumokat, és ahelyett, hogy véletlenszerűen turkálna a tárolókban, azonnal tudni fogja, hová kell mennie és mit kell vinnie. A nagyszámú adatbázissal, táblázattal és tárolt eljárással jelentősen megspórolhatja az időnket és az idegeinket.

SELECT * FROM dbo.MyTable --Ez egy jó módja ennek
-- Ahelyett
SELECT * FROM MyTable --Ez rossz dolog
-- Eljárás felhívás
EXEC dbo.MyProc -- Ismét jó
--Ahelyett
EXEC MyProc -- Rossz!

3. Ne használja az „sp_” előtagot a tárolt eljárások nevében: Ha az eljárás neve "sp_"-val kezdődik, akkor az SQL Server először a fő adatbázisában fog keresni. A helyzet az, hogy ezt az előtagot a szerver személyes belső tárolt eljárásaihoz használják. Ezért használata többletköltségekhez, sőt hibás eredményekhez vezethet, ha az Önével azonos nevű eljárás található az adatbázisában.

4. Használja az IF LÉTEZ (SELECT 1) lehetőséget az IF EXISTS (SELECT *) helyett: Annak ellenőrzésére, hogy egy rekord létezik-e egy másik táblában, az IF EXISTS utasítást használjuk. Ez a kifejezés igazat ad vissza, ha legalább egy értéket ad vissza a belső kifejezés, nem számít "1", minden oszlop vagy egy táblázat. A visszaküldött adatok elvileg semmilyen módon nem kerülnek felhasználásra. Így az adatátvitel során a forgalom tömörítéséhez logikusabb az „1” használata, az alábbiak szerint:

HA LÉTEZIK (VÁLASSZON 1-ET A sysobjects közül
WHERE név = "MyTable" ÉS típus = "U")

5. Használja a TRY-Catch-et a hibák észleléséhez: A 2005-ös szerver előtt minden kérés után rengeteg hibaellenőrzést írtak az eljárásban. A több kód mindig több erőforrást és több időt emészt fel. Az SQL Server 2005 óta a probléma megoldására egy korrektebb és kényelmesebb módszer jelent meg:

KEZDJ MEG A PRÓBÁLÁST
--kód
VÉGE A PRÓBÁZAT VÉGE
KEZDJE EL A FOGÁST
-- hibafogó kód
VÉGE FOGÁS

Következtetés
Lényegében ennyi van mára. Még egyszer megismétlem, hogy itt csak azokat a technikákat soroljuk fel, amelyeket személyesen alkalmaztam a gyakorlatom során, és ezek hatékonyságát garantálhatom.

P.S.
Első hozzászólásom, ne ítélj szigorúan.

A Microsoft SQL Serverben saját algoritmusaik 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 javaslom a The Way of the T-SQL Programmer című könyvet is. Transact-SQL oktatóanyag.

Mik azok a tárolt eljárások a T-SQL-ben?

Tárolt eljárások- Ezek olyan adatbázis-objektumok, amelyekbe az algoritmus SQL utasításkészlet formájában van beágyazva. 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 szerveren való tárolására szolgálnak, például algoritmust, szekvenciális számítást vagy többlépcsős SQL utasítást írt, és annak érdekében, hogy az algoritmusban szereplő összes utasítást ne hajtsa végre minden alkalommal, elrendezheti tárolt eljárásként. Ebben az esetben, amikor létrehoz egy SQL-eljárást, a kiszolgáló lefordítja a kódot, majd minden alkalommal, amikor ezt az eljárást futtatja, az SQL-kiszolgáló többé nem fordítja le újra.

Egy tárolt eljárás futtatásához az SQL Serverben az EXECUTE parancsot a neve elé kell írni, de lehetséges az EXEC parancs gyorsírása is. Tárolt eljárást hívjunk meg például SELECT utasításban, mivel egy függvény 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 lehetséges olyan adatmódosítási műveletek végrehajtása, mint: UNSERT, UPDATE, DELETE. Ezenkívül az eljárásokban szinte bármilyen típusú SQL utasítás 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, visszaadhat táblázatos adatokat, nem ad vissza semmit, 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. PIVOT operátor. A lekérdezések létrehozásának egyszerűsítése érdekében ezzel az operátorral ( mint tudod, a PIVOT szintaxis meglehetősen bonyolult), írhat egy 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 ennek a funkciónak tárolt eljárás formájában való megvalósítására.

Példák a Microsoft SQL Server tárolt eljárásainak kezelésére

Kiindulási adatok példákhoz

Az alábbi példák végrehajtása a Microsoft SQL Server 2016 Express rendszerben történik. 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ázatot, és adjunk hozzá néhány rekordot, tegyük fel, hogy ez lesz egy táblázat, amely a termékek listáját tartalmazza az árával.

Táblalétrehozási utasítás CREATE TABLE TesztTable( INT IDENTITY(1,1) NOT NULL, INT NOT NULL, VARCHAR(100) NOT NULL, MONEY NULL) GO -- Adatutasítás hozzáadása INSERT INTO TesztTable(Kategóriaazonosító, Terméknév, Ár) ÉRTÉKEK (1 , "Egér", 100), (1, "Billentyűzet", 200), (2, "Telefon", 400) GO -- SELECT * FROM TestTable lekérdezés

Adatok vannak, most térjünk át a tárolt eljárások létrehozására.

Tárolt eljárás létrehozása T-SQL-ben - CREATE PROCEDURE utasítás

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ás nevét, majd ha szükséges, zárójelben megadni a bemeneti és kimeneti paramétereket. Ezután beírja az AS kulcsszót, és megnyit egy utasításblokkot a BEGIN kulcsszóval, ezt a blokkot az END szóval zárja be. 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 elem a teszttáblázatunkon. Ehhez három bemeneti paramétert fogunk definiálni: @CategoryId - termékkategória azonosító, @ProductName - terméknév és @Price - termék ára, ez a paraméter számunkra opcionális lesz, pl. nem vihető át az eljárásba ( például az árát még nem tudjuk), ehhez beállítjuk az alapértelmezett értéket a definíciójában. Ezek a paraméterek az eljárás törzsében vannak, azaz. a BEGIN…END blokkban ugyanúgy használhatók, mint a közönséges változók ( mint tudod, a változókat @ jellel jelöljük). Ha kimeneti paramétereket kell megadni, akkor a paraméter neve után adja meg az OUTPUT kulcsszót ( 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 bejövő paraméter feldolgozását, nevezetesen a felesleges szóközök eltávolítását a karakterlánc elejéről és végéről, hogy elkerüljük azokat a helyzeteket, amikor véletlenül több szóköz került beírásra.

Itt található az eljárás kódja Én is hozzászóltam).

Eljárás létrehozása CREATE PROCEDURE TestProcedure (--Bejövő paraméterek @CategoryId INT, @ProductName VARCHAR(100), @PÉNZár = 0) AS BEGIN a szöveg végén SET @ProductName = LTRIM(RTRIM(@Terméknév)); --Új bejegyzés hozzáadása INSERT INTO TestTable(CategoryId, Product Name, Price) VALUES (@CategoryId, @ProductName, @Price) --Return data SELECT * FROM TestTable WHERE CategoryId = @CategoryId END GO

Tárolt eljárás futtatása T-SQL-ben – EXECUTE parancs

Futtathat egy tárolt eljárást, ahogy már megjegyeztem, az EXECUTE vagy EXEC paranccsal. A bejövő paraméterek az eljárásoknak úgy kerülnek átadásra, hogy egyszerűen felsorolják őket, és megadják a megfelelő értékeket az eljárás neve után ( a kimeneti paramétereknél az OUTPUT parancsot is meg kell adni). Előfordulhat azonban, hogy a paraméterek neve nem adható meg, 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).

Előfordulhat, hogy az alapértelmezett értékkel rendelkező paraméterek nem adhatók meg, ezek az úgynevezett opcionális paraméterek.

Íme néhány különböző, de egyenértékű módszer 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. Meghívjuk az eljárást a megadott árral EXEC TestProcedure @CategoryId = 1, @ProductName = "Teszttermék 2", @Price = 300 --3. Az eljárást az EXEC paraméterek nevének megadása nélkül hívjuk meg TestProcedure 1, "Test item 3", 400

Tárolt eljárás módosítása T-SQL-re – ALTER PROCEDURE utasítás

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 már létező eljárás megváltoztatásához csak az ELJÁRÁS LÉTREHOZÁSA helyett az ALTER PROCEDURE parancsot kell írnia, és minden mást szükség szerint módosítania kell.

Tegyük fel, hogy módosítani kell a teszteljárásunkat, mondjuk a @Price paramétert, pl. ár, 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ítsa az eljárást ALTER PROCEDURE TestProcedure (--bejövő paraméterek @CategoryId INT, @ProductName VARCHAR(100), @PÉNZár) AS BEGIN szövegsorok közül SET @ProductName = LTRIM(RTRIM(@ProductName)); --Új rekord hozzáadása INSERT INTO TesztTable(Kategóriaazonosító, Terméknév, Ár) ÉRTÉKEK (@Kategóriaazonosító, @Terméknév, @Ár) END GO

Tárolt eljárás törlése a T-SQL - DROP PROCEDURE utasításban

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 törlése után hibával meghiúsulnak, mivel az általuk hivatkozott eljárás már nem létezik.

Mindenem megvan, remélem érdekes és hasznos volt számodra az anyag, viszlát!

Meg van határozva a tárolt eljárások fogalma. Példákat adunk paraméterekkel tárolt eljárások létrehozására, módosítására és használatára. Adott a bemeneti és kimeneti paraméterek meghatározása. Példák a tárolt eljárások létrehozására és meghívására.

A tárolt eljárás fogalma

Tárolt eljárások egymással összefüggő SQL utasítások csoportjai, amelyek használata megkönnyíti és rugalmasabbá teszi a programozó munkáját, mert tárolt eljárás gyakran sokkal egyszerűbb, mint az egyes SQL utasítások sorozata. A tárolt eljárások olyan parancsok halmaza, amelyek egy vagy több SQL utasításból vagy függvényből állnak, és az adatbázisban lefordított formában tárolódnak. Végrehajtás az adatbázisban tárolt eljárások Az egyedi SQL utasítások helyett a következő előnyöket nyújtja a felhasználónak:

• a szükséges operátorok már benne vannak az adatbázisban;
• mindannyian átjutottak a színpadon elemzéseés végrehajtható formátumban vannak; előtt tárolt eljárás végrehajtása Az SQL Server végrehajtási tervet készít hozzá, optimalizálja és lefordítja;
• tárolt eljárások támogatás moduláris programozás, mivel lehetővé teszik a nagy feladatok független, kisebb és könnyen kezelhető részekre bontását;
• tárolt eljárások okozhat másoknak tárolt eljárásokés funkciók;
• tárolt eljárások más típusú alkalmazási programokból is hívható;
• általában, tárolt eljárások gyorsabban hajtódnak végre, mint az egyes utasítások sorozata;
• tárolt eljárások könnyebben használható: több tíz és száz parancsból állhatnak, de futtatásukhoz elég csak a kívánt nevet megadni tárolt eljárás. Ez lehetővé teszi a klienstől a szerver felé küldött kérés méretének csökkentését, és ezáltal a hálózat terhelésének csökkentését.

Az eljárások ugyanazon a helyen történő tárolása, ahol végrehajtásuk történik, csökkenti a hálózaton keresztül továbbított adatok mennyiségét, és javítja a rendszer általános teljesítményét. Alkalmazás tárolt eljárások leegyszerűsíti a szoftverrendszerek karbantartását és változtatásait. Általában az összes integritási megszorítás szabályok és adatfeldolgozási algoritmusok formájában az adatbázis-kiszolgálón valósul meg, és készletként elérhetők a végalkalmazás számára. tárolt eljárások, amelyek az adatfeldolgozó felületet képviselik. Az adatok sértetlenségének biztosítása, valamint biztonsági okokból az alkalmazás általában nem fér hozzá közvetlenül az adatokhoz - minden munka az adatokkal egy vagy másik felhívással történik. tárolt eljárások.

Ez a megközelítés nagyon egyszerűvé teszi az adatfeldolgozó algoritmusok módosítását, amelyek azonnal elérhetővé válnak minden hálózati felhasználó számára, és lehetővé teszi a rendszer bővítését anélkül, hogy magát az alkalmazást módosítaná: elég megváltoztatni tárolt eljárás az adatbázis-kiszolgálón. A fejlesztőnek nem kell újrafordítania az alkalmazást, másolatokat készíteni róla, és nem kell tájékoztatnia a felhasználókat az új verzió használatának szükségességéről. Előfordulhat, hogy a felhasználók nem is tudnak arról, hogy változások történtek a rendszerben.

Tárolt eljárások tábláktól vagy bármely más adatbázis-objektumtól függetlenül léteznek. A kliensprogram hívja meg őket, egy másik tárolt eljárás vagy kiváltó. A fejlesztő kezelheti a hozzáférési jogokat tárolt eljárás végrehajtásának engedélyezése vagy tiltása. Változtassa meg a kódot tárolt eljárás csak a tulajdonosa vagy a rögzített adatbázis-szerepkör tagja engedélyezi. Ha szükséges, átruházhatja a tulajdonjogát egyik felhasználóról a másikra.

Tárolt eljárások MS SQL Server környezetben

Az SQL Serverrel végzett munka során a felhasználók saját eljárásokat hozhatnak létre, amelyek bizonyos műveleteket hajtanak végre. Tárolt eljárások teljes értékű adatbázis-objektumok, ezért mindegyik egy adott adatbázisban van tárolva. Közvetlen hívás tárolt eljárás csak akkor lehetséges, ha annak az adatbázisnak a környezetében hajtják végre, ahol az eljárás található.

A tárolt eljárások típusai

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 tevékenységek elvégzésére tervezték. Szinte az összes szerveradminisztrációs művelet az ő segítségükkel történik. Azt mondhatjuk, hogy a rendszer tárolt eljárások olyan interfész, amely rendszertáblázatokkal való munkát biztosít, ami végső soron a felhasználói és rendszeradatbázisok rendszertáblázatainak megváltoztatásán, hozzáadásán, törlésén és az adatok visszakeresésén múlik. 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 hajtson végre bizonyos intézkedéseket. Tárolt eljárások- egy teljes 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 rövid ideig léteznek, ezután 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. Egy ilyen eljárás létrehozásakor olyan nevet kell adni, amely egyetlen # karakterrel kezdődik. Mint minden ideiglenes objektum, tárolt eljárások Ezek a típusok 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. Meghatározásához elegendő egy ## karakterekkel kezdődő nevet adni neki. Ezek az eljárások a kiszolgáló újraindításakor vagy leállításakor törlődnek, vagy ha a kapcsolat, amelynek környezetében 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:

• 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, adjon neki egy nevet az sp_ előtaggal, és helyezze el a rendszer adatbázisában. Ez az eljárás elérhető lesz a helyi szerver bármely adatbázisában;
• hozzáférés tervezése. Alkotás közben tárolt eljárás ne feledje, 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 lehetnek bemeneti és kimeneti paraméterei ;
• kód fejlesztés tárolt eljárás. Az eljáráskód bármilyen SQL-parancs sorozatát tartalmazhatja, beleértve mások meghívását is. tárolt eljárások.

Új létrehozása és meglévő módosítása tárolt eljárás a következő paranccsal történik:

<определение_процедуры>::= (CREATE | ALTER ) PROC eljárásnév [;szám] [(@paraméter_neve adattípus ) [=alapértelmezett] ][,...n] AS sql_utasítás [...n]

Tekintsük ennek a parancsnak a paramétereit.

Az sp_ , # , ## előtagok használatával a létrehozott eljárás rendszerként vagy ideiglenes eljárásként definiálható. Ahogy a parancs szintaxisából is látszik, nem szabad megadni a tulajdonos nevét, akihez a létrehozott eljárás fog tartozni, valamint annak az adatbázisnak a nevét, ahová el kell helyezni. Így a teremtett befogadása érdekében tárolt eljárás egy adott adatbázisban a CREATE PROCEDURE parancsot az adott adatbázis környezetében kell futtatnia. Ha a testből kezelik tárolt eljárás Rövidített nevek használhatók az azonos adatbázisban lévő objektumokhoz, azaz az adatbázisnév megadása nélkül. Ha más adatbázisokban található objektumokra szeretne hivatkozni, az adatbázis nevének megadása kötelező.

A névben szereplő szám az azonosító szám tárolt eljárás, amely egyedileg határozza meg egy eljárások csoportjában. Az eljárások kezelésének kényelme érdekében logikailag ugyanaz a típus tárolt eljárások azonos névvel, de eltérő azonosító számmal csoportosíthatók.

Bemeneti és kimeneti adatok átadása a létrehozottban tárolt eljárás 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 Több lehetőséget is 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.

A megfelelő adattípus meghatározásához tárolt eljárási paraméter, bármilyen SQL adattípus megfelelő, beleértve a felhasználó által meghatározottakat is. A CURSOR adattípus azonban csak mint kimeneti paraméter tárolt eljárás, azaz az OUTPUT kulcsszó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 arra utasítja a kiszolgálót, hogy lépjen ki 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 meghívásakor a paraméter értékeként adtunk meg. Vegye figyelembe, hogy az OUTPUT kulcsszó megadásakor az eljárás hívásakor a megfelelő paraméter értéke csak helyi változó segítségével állítható be. A normál paraméterekhez engedélyezett kifejezések vagy állandók nem megengedettek.

A VARYING kulcsszó az OUTPUT paraméterrel együtt használatos, amely CURSOR típusú. Azt határozza meg kimeneti paraméter lesz a kapott halmaz.

A DEFAULT kulcsszó az az érték, amelyet a megfelelő kulcsszó alapértelmezett beállítás. Így egy eljárás meghívásakor nem lehet kifejezetten megadni a megfelelő paraméter értékét.

Mivel a szerver gyorsítótárazza a lekérdezés végrehajtási tervet és a lefordított kódot, az eljárás legközelebbi meghívásakor a már előkészített értékek kerülnek felhasználásra. Bizonyos esetekben azonban továbbra is szükséges az eljáráskód újrafordítása. A RECOMPILE kulcsszó megadása végrehajtási terv létrehozására utasítja a rendszert tárolt eljárás minden alkalommal, amikor hívják.

A FOR REPLICATION paraméter szükséges az adatok replikálásához és a létrehozott adatokhoz tárolt eljárás cikkként egy kiadványban.

Az ENCRYPTION kulcsszó a kód titkosítására utasítja a szervert tárolt eljárás, amely védelmet nyújthat a művet megvalósító szerzői jogi algoritmusok használata ellen tárolt eljárás.

Az AS kulcsszó a tényleges törzs elejére kerül tárolt eljárás, azaz SQL parancsok halmaza, amelyek segítségével ez vagy az a művelet végrehajtásra kerül. Szinte az összes SQL parancs használható az eljárás törzsében, tranzakciók deklarálhatók, zárak állíthatók be, és egyebek meghívhatók. tárolt eljárások. kilépés tárolt eljárás a RETURN paranccsal tehető meg.

Tárolt eljárás törlése parancs hajtja végre:

ELJÁRÁS LEADÁSA (eljárás_neve) [,...n]

Tárolt eljárás végrehajtása

Mert tárolt eljárás végrehajtása a parancsot használják:

[[ EXEC [ UTE] eljárásnév [;szám] [[@paraméter_neve=](érték | @változónév) |][,...n]

Ha a hívás tárolt eljárás nem az egyetlen parancs a csomagban, akkor az EXECUTE parancs megléte kötelező. Ezenkívül ez a parancs szükséges egy eljárás meghívásához egy másik eljárás vagy trigger törzséből.

Az OUTPUT kulcsszó használata eljárás hívásakor csak olyan paramétereknél megengedett, amelyek mikor lettek deklarálva eljárás létrehozása az OUTPUT kulcsszóval.

Ha egy eljáráshíváshoz a DEFAULT kulcsszó van megadva, a DEFAULT kulcsszó kerül felhasználásra. alapértelmezett érték. Természetesen a megadott DEFAULT szó csak azokhoz a paraméterekhez engedélyezett, amelyekhez definiálva van alapértelmezett érték.

Az EXECUTE parancs szintaxisából látható, hogy a paraméternevek elhagyhatók egy eljárás hívásakor. Ebben az esetben azonban a felhasználónak meg kell adnia a paraméterek értékeit abban a sorrendben, amelyben azok mikor szerepeltek eljárás létrehozása. Paraméter hozzárendelése alapértelmezett érték, egyszerűen kihagyja, ha a felsorolás nem lehetséges. Ha ki kell hagyni azokat a paramétereket, amelyekre a alapértelmezett érték, híváskor elég kifejezetten megadni a paraméterneveket tárolt eljárás. Sőt, ily módon a paramétereket és azok értékeit bármilyen sorrendben felsorolhatja.

Vegye figyelembe, hogy egy eljárás meghívásakor vagy értékekkel rendelkező paraméternevek, vagy csak paraméternév nélküli értékek kerülnek megadásra. Kombinációjuk nem megengedett.

12.1. példa. Paraméterek nélküli eljárás. Dolgozzon ki eljárást az Ivanov által vásárolt áruk nevének és árának megszerzésére.

PROC LÉTREHOZÁSA my_proc1 AS KIVÁLASZTÁS Cikknév, Cikk.Ár*Kereskedés.Mennyiség AS Költség, Vevő.Vezetéknév AZ Ügyfél BELSŐ CSATLAKOZTATÁSÁRÓL (Tétel BELSŐ JOIN Tranzakció ON Item.ItemId=Trade.ItemId) ON Customer.CustomerCode=Trade.CHERE Ügyfél .Lastname='Ivanov' 12.1. példa. Eljárás az Ivanov által vásárolt áruk nevének és árának megszerzésére.

Mert eljárásra való felhívás parancsok használhatók:

EXEC my_proc1 vagy my_proc1

Az eljárás egy adathalmazt ad vissza.

Példa 12.2. Paraméterek nélküli eljárás. Hozzon létre egy eljárást az első osztályú cikk árának 10%-os csökkentésére.

Mert eljárásra való felhívás parancsok használhatók:

EXEC my_proc2 vagy my_proc2

Az eljárás nem ad vissza semmilyen adatot.

12.3. példa. Eljárás bemeneti paraméterrel. Hozzon létre egy eljárást az adott vásárló által vásárolt cikkek nevének és árának lekéréséhez.

PROC LÉTREHOZÁSA my_proc3 @k VARCHAR(20) AS SELECT Cikknév, Cikk.Ár*Kereskedés.Mennyiség AS Költség, Vevő.Vezetéknév FROM Ügyfél BELSŐ CSATLAKOZTATÁSÁRÓL (Tétel BELSŐ CSATLAKOZÁSA Kereskedelem ON Item.ItemID=Trade.ItemID) ON Client.CustomerID =Deal.CustomerCode WHERE Customer.LastName=@k 12.3. példa. Eljárás az adott vásárló által vásárolt cikkek nevének és árának lekérésére.

Mert eljárásra való felhívás parancsok használhatók:

EXEC my_proc3 "Ivanov" vagy my_proc3 @k="Ivanov"

12.4. példa.. Készítsen eljárást egy adott típusú termék árának csökkentésére a megadott %-nak megfelelően.

Mert eljárásra való felhívás parancsok használhatók:

EXEC my_proc4 "Waffle",0.05 vagy EXEC my_proc4 @t="Ofa", @p=0.05

12.5. példa. Eljárás bemeneti paraméterekkelés alapértelmezett értékeket. Készítsen eljárást egy adott típusú termék árának csökkentésére a megadott %-nak megfelelően.

PROC LÉTREHOZÁSA my_proc5 @t VARCHAR(20)='Candy', @p FLOAT=0.1 AS UPDATE Elem SET Ár=Ár*(1-@p) WHERE Típus=@t 12.5. példa. Eljárás bemeneti paraméterekkel és alapértelmezett értékekkel. Készítsen eljárást egy adott típusú termék árának csökkentésére a megadott %-nak megfelelően.

Mert eljárásra való felhívás parancsok használhatók:

EXEC my_proc5 "Waffle",0.05 vagy EXEC my_proc5 @t="Otya", @p=0.05 vagy EXEC my_proc5 @p=0.05

Ebben az esetben az édességek ára csökken (a típus értékét az eljárás hívásakor nem adjuk meg, és alapértelmezés szerint veszik).

Az utóbbi esetben mindkét paraméter (mind a típus, mind a százalék) nincs megadva az eljárás hívásakor, alapértelmezés szerint ezek értékeit veszik fel.

12.6. példa. Eljárás bemeneti és kimeneti paraméterekkel. Hozzon létre egy eljárást az adott hónapban eladott áruk összköltségének meghatározásához.

PROC LÉTREHOZÁSA my_proc6 @m INT, @s FLOAT OUTPUT AS SELECT @s=Összeg(Tétel.Ár*Kereskedés.Mennyiség) FROM Cikk BELSŐ JOIN Kereskedés ON Item.ItemID=Trade.ItemID CSOPORT BY Month(Trade.Date) HAVING Month( Deal.Date)=@m 12.6. példa. Eljárás bemeneti és kimeneti paraméterekkel. Hozzon létre egy eljárást az adott hónapban eladott áruk összköltségének meghatározásához.

Mert eljárásra való felhívás parancsok használhatók:

DECLARE @st FLOAT EXEC my_proc6 1,@st OUTPUT SELECT @st

Ez a parancsblokk lehetővé teszi a januárban eladott áruk költségének meghatározását ( bemeneti paraméter a hónap 1-re van állítva).

Hozzon létre egy eljárást annak meghatározására, hogy egy adott alkalmazott dolgozik a cég által vásárolt áruk teljes mennyiségén.

Először is kidolgozunk egy eljárást annak a cégnek a meghatározására, ahol a munkavállaló dolgozik.

12.7. példa. Használat beágyazott eljárások. Hozzon létre egy eljárást annak meghatározására, hogy egy adott alkalmazott dolgozik a cég által vásárolt áruk teljes mennyiségén.

Ezután létrehozunk egy eljárást, amely megszámolja a számunkra érdekelt cég által vásárolt áruk teljes mennyiségét.

PROC LÉTREHOZÁSA my_proc8 @fam VARCHAR(20), @kol INT OUTPUT AS DECLARE @firm VARCHAR(20) EXEC my_proc7 @fam,@firm OUTPUT SELECT @kol=Sum(Trade.Quantity) FROM Client INNER JOIN Trade ON ClientColient. Deal.ClientCode GROUP BY Client.Company HAVING Client.Company=@firm 12.7. példa. Hozzon létre egy eljárást annak meghatározására, hogy egy adott alkalmazott dolgozik a cég által vásárolt áruk teljes mennyiségén.

Az eljárást a következő paranccsal hívják meg:

DECLARE @k INT EXEC my_proc8 'Ivanov',@k OUTPUT SELECT @k

Tárolt eljárás egy speciális típusú Transact-SQL utasításköteg, amelyet az SQL nyelv és az eljárási kiterjesztések segítségével 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ásokat a szerver oldalon tárolják, hogy javítsák az ismétlődő feladatok teljesítményét és konzisztenciáját.

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.

Tárolt eljárás létrehozásakor megadhat egy opcionális paraméterlistát. Így az eljárás minden meghívásakor a megfelelő argumentumokat veszi fel. A tárolt eljárások visszaadhatnak egy felhasználó által definiált információkat tartalmazó értéket, vagy hiba esetén egy megfelelő hibaüzenetet.

A tárolt eljárást a rendszer előre lefordítja, mielőtt objektumként tárolná 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 azt a fontos előnyt jelenti, hogy (majdnem minden esetben) kiküszöböli az eljárások újrafordításait, és ennek megfelelő teljesítményjavulást ér el. 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ájtos 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:

naplók létrehozásához az adatbázistáblákkal végzett műveletekről.

A tárolt eljárások használata olyan szintű biztonsági ellenőrzést biztosít, amely jóval túlmutat a felhasználók számára eltérő hozzáférési jogosultságokat biztosító GRANT és REVOKE utasítások által nyújtott biztonságon. Ez azért lehetséges, mert a tárolt eljárás végrehajtására vonatkozó jogosultság független a tárolt eljárásban található objektumok módosítására vonatkozó jogosultságtól, a következő részben leírtak szerint.

A naplóírási és/vagy táblák olvasási tárolt eljárások további biztonságot nyújtanak az adatbázis számára. Az ilyen eljárások használatával az adatbázis-adminisztrátor nyomon követheti a felhasználók vagy alkalmazások által az adatbázison 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, amelynek szintaxisa a következő:

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 adja meg a generált tárolt eljárás tulajdonosa által hozzárendelt sémanevet. 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 egy 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 a megfelelő eljárási paraméter alapértelmezett értékét adja meg. (Az alapértelmezett érték NULL is lehet.)

OUTPUT opció meghatározza, hogy egy eljárásparaméter visszaadható, és 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 oknál fogva egy tárolt eljárást minden meghívásakor le kell fordítani, az eljárás deklarációja opció WITH RECOMPILE. A WITH RECOMPILE opció használata megtagadja a tárolt eljárások egyik legfontosabb előnyét: az egyetlen fordításnak köszönhető teljesítménynövekedést. Ezért a WITH RECOMPILE beállítást csak akkor szabad használni, ha a tárolt eljárás által használt adatbázis-objektumok gyakran változnak.

EXCUTE AS ajánlat meghatározza azt a biztonsági környezetet, amelyben a tárolt eljárást a meghívása után végre kell hajtani. Ennek a kontextusnak a beállításával az adatbázismotor szabályozhatja 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ázis szerepkör tagjai használhatják a CREATE PROCEDURE utasítást. E szerepkörök tagjai azonban 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:

HASZNÁLATA SampleDb; ELJÁRÁS LÉTREHOZÁSA Költségkeret növelése (@százalék INT=5) FRISSÍTÉSKÉNT Projektbeállítás Költségvetés = Költségvetés + Költségvetés * @százalék/100;

Mint korábban említettük, két csomag elkülönítésére szolgál GO utasítás. A CREATE PROCEDURE utasítás nem kombinálható más Transact-SQL utasításokkal ugyanabban a kötegben. A IncreaseBudget tárolt eljárás megnöveli az összes projekt költségvetését egy bizonyos százalékkal, amelyet a @percent paraméter határoz meg. Az eljárás egy alapértelmezett százalékértéket (5) is meghatároz, amely akkor kerül alkalmazásra, ha ez az argumentum nincs jelen az eljárás végrehajtása 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, és anélkül, hogy 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 # karakterrel (#proc_name), a globális eljárás neve pedig kettős karakterrel (##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. Egy globális ideiglenes eljárást minden felhasználó végrehajthat, de csak addig, amíg az utolsó kapcsolat, amelyen fut (általában az eljárás létrehozójának kapcsolata), meg nem szűnik.

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ást egyszer hoznak létre és többször hajtanak végre. A tárolt eljárást a EXCUTE utasításokat az eljárás tulajdonosa, vagy az eljáráshoz való hozzáférés VÉGREHAJTÁSI jogával rendelkező felhasználó. Az EXECUTE utasítás szintaxisa a következő:

[] [@return_status =] (proc_name | @proc_name_var) ([[@parameter1 =] érték | [@parameter1=] @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 értékkel rendelkezik, mint az azonos nevű CREATE PROCEDURE utasítás 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éterértékek sorrendje nem fontos, de a névtelen paraméterértékeket 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. Ha egy eljárás értéket vár egy olyan paraméterhez, amelyhez nincs alapértelmezett érték megadva, és a paraméter hiányzik, vagy a DEFAULT kulcsszó van megadva, hiba történik.

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:

HASZNÁLATA SampleDb; 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.

A következő 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). Egy ilyen tárolt eljárás egy trigger definíción belül használható, amely ténylegesen érvényesíti a hivatkozási integritást.

A következő 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ásokkal hajtható végre:

DECLARE @quantityDeleteEmployee INT; VÉGREHAJTÁS DeleteEmployee @empId=18316, @counter=@quantityDeleteEmployee OUTPUT; PRINT N"Törölt alkalmazottak: " + convert(nvarchar(30), @quantityDeleteEmployee);

Ez az eljárás megszámolja azon projektek számát, amelyeken egy @empId személyi számmal rendelkező alkalmazott dolgozik, és a kapott értéket hozzárendeli a ©counter paraméterhez. Miután törölte egy adott személyi szám összes sorát 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 visszaadja az értéket a rendszernek. Ezért a @counter paramétert meg kell adni mind az OUTPUT opcióban az eljárás deklarálásakor, mind az EXECUTE utasításban a meghívásakor.

WITH RESULTS SETS egy EXECUTE utasítás záradéka

Az SQL Server 2012 rendszerben az EXECUTE utasítás kerül begépelésre WITH RESULTS SETS záradék A, amely bizonyos feltételek mellett megváltoztathatja a tárolt eljárás eredményhalmazának alakjá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 egy eljárási paraméter, és az eljárás hívásakor meg kell adni. Ennek az eljárásnak a végrehajtása egy két oszlopos táblát ad ki, amelyek fejlécei megegyeznek az adatbázistábla megfelelő oszlopainak nevével, azaz. azonosító és vezetéknév. Az eredmény oszlopfejléceinek (valamint az adattípusuknak) módosításához az SQL Server 2012 az új WITH RESULTS SETS záradékot használja. E záradék alkalmazását az alábbi példa mutatja be:

HASZNÁLATA SampleDb; EXEC EmployeesInDept "d1" EREDMÉNYHASZNÁLATOKKAL (( INT NOT 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ó, egy tárolt eljárás futtatása a WITH RESULT SETS záradékkal egy EXECUTE utasításban lehetővé teszi az eljárás által előállított eredményhalmaz oszlopok 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 utasítást általában a Transact-SQL utasítások eljáráson belüli módosítására használják. Az ALTER PROCEDURE utasítás összes paraméterének jelentése megegyezik az azonos nevű CREATE PROCEDURE utasítás paramétereivel. Az utasítás használatának elsődleges célja a meglévő tárolt eljárásjogosultságok felülbírálásának elkerülése.

Az adatbázismotor támogatja CURSOR adattípus. 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 tárolására szolgál (általában sorok halmaza), és lehetővé teszi a felhasználók számára, hogy soronként jelenítsék meg az eredményeket.

Egy vagy több tárolt eljárás eltávolításához használja a DROP PROCEDURE nyilatkozatot. Csak a tárolt eljárás tulajdonosa vagy a db_owner és a sysadmin rögzített szerepkör tagjai törölhetnek egy tárolt eljárást.

Tárolt eljárások és a közös nyelvi futtatókörnyezet

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, felhasználó által definiált függvények, triggerek, felhasználó által definiált aggregátumok és felhasználó által definiált adattípusok) fejlesztését C# és Visual Basic használatával. A közös nyelvű futási környezet lehetővé teszi ezen objektumok végrehajtását is a közös futási rendszer használatával.

A közös nyelv futtatókörnyezete az opcióval engedélyezhető és letiltható clr_enabled rendszer eljárása sp_configure, amelyet az utasítás indít végrehajtásra ÚJRAKONFIGURÁLÁS. A következő példa bemutatja, hogyan engedélyezheti a közös nyelv futtatókörnyezetét az sp_configure rendszereljárással:

HASZNÁLATA SampleDb; EXEC sp_configure "clr_enabled",1 RECONFIGURE

Egy eljárás CLR segítségével történő létrehozásához, fordításához és mentéséhez a következő lépések sorrendje szükséges a felsorolt ​​sorrendben:

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 használata ÖSSZESZERELÉS LÉTREHOZÁSA, hozza létre a megfelelő végrehajtható fájlt.

Végezzen el egy eljárást az EXECUTE utasítás használatával.

Az alábbi ábrán a korábban vázolt lépések grafikus diagramja látható. Az alábbiakban ennek a folyamatnak a részletesebb leírása található.

Először hozza létre a kívánt 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ájlban van tárolva, amely a CREATE ASSEMBLY utasítás forrásaként szolgál, amely közbenső végrehajtható kódot hoz létre. Ezután adjon ki egy CREATE PROCEDURE utasítást a végrehajtott kód adatbázis-objektumként történő mentéséhez. Végül futtassa az eljárást az ismerős EXECUTE utasítással.

Az alábbi példa a tárolt eljárás forráskódját mutatja be C#-ban:

A System.Data.SqlClient használata; Microsoft.SqlServer.Server használatával; nyilvános részleges osztály StoredProcedures ( public static int CountEmployees() ( int sorok; SqlConnection kapcsolat = new SqlConnection("Context Connection=true"); connection.Open(); SqlCommand cmd = kapcsolat.CreateCommand(); cmd.CommandText = "select count(*) mint "Alkalmazottak száma" " + "Alkalmazotttól"; rows = (int)cmd.ExecuteScalar(); connection.Close(); return rows; ) )

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 lévő direktívák használatával adja meg a 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, hogy ez az osztály egy tárolt eljárás. Az osztálykódon belül a CountEmployees() metódus 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 a példány Open() metódusát kell használni. A CreateCommand() metódus lehetővé teszi egy osztály példányának elérését SqlCommnd, amelyhez a kívánt SQL-parancs kerül átadásra.

A következő kódrészletben:

Cmd.CommandText = "Select count(*) mint "Alkalmazottak száma" " + "az 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. A következőt hívják ExecuteScalar() metódus az SqlCommand példánya. Ez a metódus egy skaláris értéket ad vissza, amelyet int egész adattípusra konvertál, és hozzárendel a sorváltozóhoz.

Most már lefordíthatja ezt a kódot a Visual Studio segítségével. Ezt az osztályt CLRStoredProcedures néven adtam a projekthez, így a Visual Studio az azonos nevű összeállítást *.dll kiterjesztéssel fogja lefordítani. Az alábbi példa egy tárolt eljárás létrehozásának következő lépését mutatja be: a futtatandó kód létrehozását. A példában szereplő kód végrehajtása előtt ismernie kell a lefordított .dll fájl helyét (általában a projekt Debug mappájában található).

HASZNÁLATA SampleDb; LÉPJEN EL AZ ASSEMBLY CLRStoredProcedures LÉTREHOZÁSÁRA A "D:\Projects\CLRStoredProcedures\bin\Debug\CLRStoredProcedures.dll" menüből AZ PERMISSION_SET = BIZTONSÁGOS.

A CREATE ASSEMBLY utasítás kezelt kódot vesz bemenetként, és létrehoz egy megfelelő objektumot, amelyhez közös nyelvű futásidejű (CLR) tárolt eljárásokat, felhasználó által definiált függvényeket és triggereket hozhat létre. Ennek az utasításnak a szintaxisa a következő:

CREATE ASSEMBLY összeállításnév [ ENGEDÉLYEZÉS tulajdonosi_neve ] FROM (dll_fájl) Szintaxis konvenciók

Az assembly_name paraméter határozza meg az összeállítás nevét. Az opcionális AUTHORIZATION záradék egy szerepkör nevét határozza meg 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 meg kell adni. Meghatározza az összeállítási kódhoz biztosított hozzáférési jogok készletét. A SAFE jogok halmaza a leginkább korlátozó. 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ási kód hozzáférését 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ázis-rendszeren belül és kívül egyaránt.

Az összeállítási kód információinak tárolásához a felhasználónak képesnek kell lennie egy CREATE ASSEMBLY utasítás kiadására. Az összeállítás az utasítást végrehajtó felhasználó (vagy szerepkör) tulajdona. Az összeállítás tulajdonosát a CREATE SCHEMA utasítás AUTHORIZATION záradékával módosíthatja.

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.

A következő példa bemutatja, hogyan hozhat létre tárolt eljárást a korábban implementált felügyelt kód alapján:

HASZNÁLATA SampleDb; 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 az opció azt határozza meg, 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 - az összeállítás nevét adja meg;

osztály_neve - az általános osztály nevét adja meg;

módszer_neve - opcionális rész, az osztályon belül beállított metódus nevét adja meg.

A CountEmployees eljárás végrehajtása az alábbi példában látható:

HASZNÁLATA SampleDb; DECLARE @count INT EXECUTE @count = Munkavállalók száma NYOMTATÁS @count -- Returns 7

A PRINT utasítás az Employee tábla sorainak aktuális számát adja vissza.

Eljárási nyilatkozat

ELJÁRÁS LÉTREHOZÁSA [({IN|OUT|INOUT} [,…])]
[DINAMIKUS EREDMÉNY BEÁLLÍTÁSA ]
KEZDŐDIK [ATOM]

VÉGE

Kulcsszavak
. IN (Input) – bemeneti paraméter
. OUT (Output) – kimeneti paraméter
. INOUT - bemenet és kimenet, valamint egy mező (nincs paraméter)
. DINAMIKUS EREDMÉNY BEÁLLÍTÁS azt jelzi, hogy az eljárás meg tudja nyitni a megadott számú kurzort, amely nyitva marad az eljárás visszatérése után

Megjegyzések
A tárolt eljárásokban nem ajánlott sok paramétert (elsősorban nagy számokat és karakterláncokat) használni a hálózat és a verem torlódása miatt. A gyakorlatban a Transact-SQL, PL/SQL és Informix meglévő dialektusai jelentős eltéréseket mutatnak a szabványtól, mind a paraméterek deklarálásában és használatában, mind a változó deklarációjában, mind a szubrutinhívásban. A Microsoft a következő közelítés használatát javasolja a tárolt eljárások gyorsítótár méretének becsléséhez:
=(maximum egyidejű felhasználó)*(a végrehajtási terv legnagyobb mérete)*1,25. A végrehajtási terv méretének laponkénti meghatározása a következő paranccsal végezhető el: DBCC MEMUSAGE.

Eljárás felhívás

Sok létező DBMS-ben a tárolt eljárásokat a következő utasítással hívják meg:

ELJÁRÁS VÉGREHAJTÁSA [(][)]

jegyzet: A tárolt eljárások hívhatók alkalmazáson belülről, másik tárolt eljárásból vagy interaktívan.

Példa az eljárási nyilatkozatra

ELJÁRÁS LÉTREHOZÁSA Proc1 AS //eljárás deklarálása
Cur1 CURSOR DECLARE FOR SELECT SNname, City FROM SalesPeople WHERE Értékelés >200 //kurzor deklarálása
OPEN Cur1 //kurzor megnyitása
FETCH NEXT FROM Cur1 //adatok olvasása a kurzorból
WHILE @@Fetch_Status=0
KEZDŐDIK
HOZZÁ EL A KÖVETKEZŐT Cur1-ről
VÉGE
CLOSE Cur1 //kurzor bezárása
FELVONÁSA Cur1
EXECUTE Proc1 //futtassa az eljárást

Polimorfizmus
Ugyanabban a sémában két azonos nevű szubrutin hozható létre, ha a két szubrutin paraméterei kellően eltérnek egymástól ahhoz, hogy megkülönböztethetők legyenek. Annak érdekében, hogy meg lehessen különböztetni két, azonos nevű rutint ugyanabban a sémában, mindegyik egy alternatív és egyedi nevet kap (specifikus név). Egy ilyen név kifejezetten megadható az alprogram definiálásakor. Ha több azonos nevű szubrutint hívunk, a kívánt szubrutin meghatározása több lépésben történik:
. Kezdetben minden megadott nevű eljárás definiálva van, ha pedig nincs, akkor minden adott nevű függvény.
. A további elemzéshez csak azok a rutinok maradnak meg, amelyekhez az adott felhasználó végrehajtási jogosultsággal (EXECUTE) rendelkezik.
. Számukra azok kerülnek kiválasztásra, amelyek paramétereinek száma megfelel a hívási argumentumok számának. A paraméterek megadott adattípusait és azok pozícióit ellenőrizzük.
. Ha egynél több szubrutin maradt, akkor a rövidebb minősítő nevű szubrutin kerül kiválasztásra.
A gyakorlatban az Oracle-ben a polimorfizmus támogatott a csak csomagban deklarált függvényeknél, a DB@ - különböző sémákban, a túlterhelés pedig tilos a Sybase-ben és az MS SQL Serverben.

Az eljárások eltávolítása és megváltoztatása
A következő utasítás az eljárás törlésére szolgál:

Egy eljárás módosításához használja a következő utasítást:

ELJÁRÁS MÓDOSÍTÁSA [([{IN|OUT|INOUT}])]
KEZDŐDIK [ATOM]

VÉGE

Az eljárások végrehajtására vonatkozó jogosultságok

GRANT EXECUTE ON NAK NEK |NYILVÁNOS [TÁMOGATÁSI OPCIÓVAL]

Rendszereljárások
Sok DBMS (beleértve az SQL Servert is) rendelkezik bizonyos beépített rendszerben tárolt eljárásokkal, amelyeket saját céljaira használhat.