-SQL tárolt eljárások

A tárolt eljárás - egy speciális típusú-SQL nyilatkozatok csomag segítségével létrehozott SQL és eljárási nyelv kiterjesztése. A fő különbség a csomagot, és a tárolt eljárás abban áll, hogy az utóbbi egy adatbázisban tárolják objektumot. Más szóval, a tárolt eljárások tárolja a szerver oldalon, hogy javítsa a teljesítményt és konzisztens teljesítmény az ismétlődő feladatok.

A Database Engine támogatja a tárolt eljárások és a rendszer eljárásokat. A tárolt eljárások jönnek létre, ugyanúgy, mint az összes többi adatbázis-objektumok, azaz A DDL nyelvet. A szisztémás kezelések állnak rendelkezésre, és a Database Engine lehet hozzáféréshez használt adatokat a rendszer katalógus és annak módosításait.

Amikor létrehoz egy tárolt eljárást, akkor meg egy opcionális paraméter listát. Így az eljárás fogja tenni a megfelelő érvek minden alkalommal hívják. A tárolt eljárások vissza értéket, amely a felhasználó által megadott adatokat, vagy hiba esetén, a megfelelő hibaüzenetet.

A tárolt eljárás korábban összeállított előtt tárolják, mint egy tárgy az adatbázisban. Előre összeállított formában az eljárást az adatbázisban tárolt és használják minden egyes alkalommal hívják. Ez a tulajdonság a tárolt eljárások az fontos előny az eliminációs (szinte minden esetben) újrafordítja eljárásokat és a megfelelő teljesítmény javítására. Ez a tulajdonság a tárolt eljárások is pozitív hatással van az adatok mennyisége résztvevő közötti cseréjét az adatbázis-rendszer és az alkalmazások. Különösen, hogy hívja a tárolt eljárás néhány ezer bájt vehet kevesebb, mint 50 bájt. Ha több felhasználó ismétlődő feladatok és a tárolt eljárások, a kumulatív hatás a megtakarítás is elég jelentős.

A tárolt eljárások is alkalmazhatók a következő célokra:

hogy hozzon létre egy folyóirat napló az intézkedések az adatbázis táblákat.

Tárolt eljárásokat létrehozni naplók rögzítésére és / vagy olvasási táblázatok, további kapacitás biztosítása, hogy biztosítsa az adatbázisban. Ilyen eljárásokat használva az adatbázis-kezelő követheti módosítások az adatbázis felhasználók vagy alkalmazások.

Létrehozása és a végrehajtó tárolt eljárások

A tárolt eljárások jönnek létre a CREATE ELJÁRÁS nyilatkozatot. amely a következő szintaxist:

Sémanév paraméter határozza meg a nevét, a séma, amely által kijelölt tulajdonos, hogy hozzon létre egy tárolt eljárást. Proc_name paraméter határozza meg a nevét, a tárolt eljárás. @ Param1 paraméter egy olyan paraméter, az eljárás (hivatalos érvelés), amelynek adattípus határozza meg paraméter Type1. A paraméterek helyi kezeléseket az eljárás, ahogyan a lokális változók lokalizált a csomagon belül. eljárás paraméterei - az értékek, amelyeket átadott a hívó eljárás használni. Default1 paraméter határozza meg az alapértelmezett érték a megfelelő feldolgozási paraméter. (Az alapértelmezett érték is lehet NULL).

Opció kimenet azt jelzi, hogy az eljárás paraméter visszatért, és ez lehet, hogy visszatérjen az érték a tárolt eljárás hívó eljárás vagy rendszer.

Hajtják végre az záradék határozza meg a biztonsági környezetet, amelyben a tárolt eljárás után a hívás végre kell hajtani. Kérdezi az összefüggésben segítségével Database Engine segítségével szabályozhatja, hogy mely felhasználói fiókok érvényesíteni engedélyeinek hivatkozott objektumok ezen tárolt eljárást.

Alapértelmezésben, járjon CREATE ELJÁRÁS Csak tagjai a rendszergazda rögzített kiszolgáló szerepét, és a rögzített adatbázis szerepe db_owner vagy db_ddladmin. De a tagjai ezeket a szerepeket lehet rendelni ezt a jogot, hogy a többi felhasználó a GRANT CREATE ELJÁRÁS nyilatkozatot.

Az alábbi példa bemutatja, hogyan lehet létrehozni egy egyszerű tárolt eljárások dolgozó Projekt tábla:

Amint azt korábban említettük, hogy elválassza a két csomag használt GO utasításokat. Instruction CREATE ELJÁRÁS nem vonható össze más-SQL nyilatkozatok egy tételben. A tárolt eljárás IncreaseBudget növeli a költségvetés valamennyi projekt egy bizonyos számú százalék által meghatározott paraméter @percent. Az eljárás szintén meghatározható az alapértelmezett érték által az érdeklődés (5), amelyet használni, ha az eljárás során, ez az érv hiányzik.

A tárolt eljárások utalhat létező táblából. Ez a funkció lehetővé teszi, hogy a hibakeresés a perrendtartás létrehozása nélkül első vonatkozó táblázatokat, sőt anélkül, hogy csatlakozik a terminál szerver.

Ezzel szemben a fő tárolt eljárások, amelyek mindig tárolja az aktuális adatbázis, akkor létre ideiglenes tárolt eljárásokat, amelyek mindig bekerül az átmeneti rendszer tempdb adatbázisban. Ennek egyik oka az, hogy az ideiglenes tárolt eljárások lehetnek a vágy, hogy elkerüljék ismételt végrehajtása egy bizonyos használati csoport, ha csatlakozik egy adatbázisba. Hozhat létre helyi vagy globális ideiglenes eljárásokat. Ehhez a neve a helyi eljárás van megadva egy karakter # (#proc_name), és a nevét a Global Action - dupla (## proc_name).

Helyi ideiglenes tárolt eljárás csak akkor végezhető a felhasználó, aki létrehozta, és csak egy kapcsolatot az adatbázis, amelyben létrejött. Global ideiglenes eljárás az összes felhasználó számára, de csak addig, amíg az utóbbi befejezése vegyület, amelyben azt végzik (általában a kapcsolat eljárást, az alkotó).

Az életciklus a tárolt eljárás két szakaszból áll, a teremtés és annak végrehajtását. Mindkét eljárás jön létre, csak egyszer végre ismételten. A tárolt eljárás végre a felhasználó EXECUTE utasítást, amely birtokolja az eljárást, vagy joga van hozzáférni a végre ezt a folyamatot. EXECUTE kijelentés szintaxis a következő:

Kivéve return_status paraméter, az összes paraméter EXECUTE utasítással azonos logikai értékű, mint az azonos paraméter CREATE ELJÁRÁS nyilatkozatot. Return_status paraméter határozza meg egy egész típusú változó, amely tárolja a visszatérési állapota az eljárást. Az érték paraméter segítségével állítható be vagy állandó (érték), vagy egy lokális változó (@variable). Az, hogy a nevesített paraméterek nem fontos, de az értékek nem nevezett paramétert meg kell adni abban a sorrendben, amelyben definiálva vannak a CREATE ELJÁRÁS nyilatkozatot.

DEFAULT kikötés alapértelmezett értéket biztosít az eljárás olyan paraméter, amit megadott definíciója az eljárást. Ha az eljárás vár a paraméter értékét, amelyre egy alapértelmezett értéket nem határozták meg, és nincs lehetőség, vagy az alapértelmezett kulcsszó megadva, akkor hiba történt.

Amikor a EXECUTE utasítás az első utasítás csomagot, akkor a kulcsszó Execute elhagyható. Mindazonáltal, ez biztonságosabb lesz, hogy tartalmazza ezt a szót minden egyes csomagot. Használata EXECUTE utasítás látható az alábbi példában:

EXECUTE kijelentés ebben a példában végrehajtja a tárolt eljárást IncreaseBudget, amely növeli a költségvetés 10% -át a projekt.

A következő példa bemutatja, hogyan lehet létrehozni egy tárolt eljárás feldolgozni az adatokat táblázatok és Alkalmazott Works_on:

ModifyEmpId eljárást példában használatát szemlélteti a tárolt eljárások, mint a folyamat részeként, hogy biztosítsa a hivatkozási integritást (ebben az esetben a munkavállaló és a Works_on táblázatok). Az ilyen tárolt eljárás lehet meghatározásához használt a ravaszt, ami valójában nyújt hivatkozási integritást.

Az alábbi példa bemutatja a használatát a tárolt eljárást kínál OUTPUT:

Ez a tárolt eljárás lehet futtatni, hogy végre az alábbi utasításokat:

Javaslatot Eredményhalmazokat EXECUTE utasítással

A következő két példa segít megmagyarázni a javaslatot. Az első példa egy bevezető példa, hogy bemutatja, hogyan az eredmény jelenhet meg a javaslatot elhagytuk az eredményeket állítja:

A kimenet a tárolt eljárás által okozott ilyen módszer a következő:

Amint látható, futás egy tárolt eljárás segítségével WITH eredmény határozza meg javaslatokat EXECUTE utasítás lehetővé teszi, hogy módosítsa a nevét és adatait típusú oszlop az eredmény meg, ezt az eljárást kiadjuk. Így ez az új funkció nagyobb rugalmasságot biztosít a végrehajtás a tárolt eljárások és üzembe az eredményeket egy új táblát.

Változások a szerkezet a tárolt eljárások

Component Database Engine támogatja ALTER ELJÁRÁS beszámoló szerkezetének módosítására tárolt eljárásokat. ALTER ELJÁRÁS utasítást általában használják, hogy módosítsa a Transact-SQL eljárás keretein belül. Minden ALTER ELJÁRÁS nyilatkozat paraméterek jelentése ugyanaz, mint az azonos paraméter CREATE ELJÁRÁS nyilatkozatot. Az elsődleges cél a kézikönyv, hogy elkerüljük felülírja a már meglévő jogait a tárolt eljárás.

Eltávolításához egy vagy egy csoport tárolt eljárások Az utasítás által használt DROP ELJÁRÁS. Törölje a tárolt eljárás csak a tulajdonos, illetve tagjai az előre meghatározott szerepek db_owner és rendszergazda.

Tárolt eljárások és a CLR

SQL Server támogatja a közös nyelvi futtatókörnyezet CLR (Common Language Runtime), amely lehetővé teszi, hogy a fejlesztés különböző adatbázis-objektumok (tárolt eljárások, felhasználó által definiált függvények, triggerek, a felhasználó által definiált összesítő függvények és a felhasználó által definiált adattípusok) a nyelv C # és Visual Basic. A CLR is lehetővé teszi ezeket az objektumokat egy közös futásidejű rendszer.

A CLR megengedett és tiltott lehetőségek clr_enabled rendszer sp_configure. ami lesz végre utasítást újra a beállítást. A következő példa azt mutatja, hogy a rendszer hogyan használhatja az eljárás sp_configure használatának engedélyezése CLR környezet:

Létrehozásához, összeállításához és mentéséhez eljárásnak a segítségével a CLR elvégzéséhez szükséges a következő lépéseket ebben a sorrendben:

Hozzon létre egy tárolt eljárás a C # vagy Visual Basic, majd fordítsd le a megfelelő fordítót.

A CREATE KÖZGYŰLÉS nyilatkozatot. hozza létre a megfelelő végrehajtható fájlt.

Mentse az eljárás, mint a szerver objektum a CREATE ELJÁRÁS nyilatkozatot.

Kövesse az eljárást a EXECUTE utasítással.

Az alábbi ábra egy olyan folyamatábra látható a lépések előzőekben bemutatott. Az alábbi egy részletesebb leírást ebben a folyamatban.

Először hozzon létre a kívánt program minden fejlesztési környezet, mint a Visual Studio. Fordítsuk le a kész programot tárgykód a C # vagy Visual Basic fordító. Ez a kód van tárolva dynamic link library fájl (.dll), amely arra szolgál, mint a forrás a CREATE KÖZGYŰLÉS utasítás létrehoz egy köztes futtatható kódot. Ezután végezze el a CREATE ELJÁRÁS nyilatkozatot, hogy mentse a végrehajtható kód az adatbázis formájában objektumot. Végül indítsuk el az eljárást a végrehajtás, vagyis ismeri EXECUTE utasítással.

A következő példa azt mutatja, a forráskód a tárolt eljárás nyelvén C #:

Ebben az eljárásban a kérelmet végre számolni a sorok számát a Munkavállaló asztalra. Használatával irányelvek elején meghatározott program névterek végrehajtásához szükséges. Ezeknek a használata az irányelvek lehetővé teszi, hogy adja meg a forráskódot osztály nevét nem adta meg egyértelműen a megfelelő névteret. További StoredProcedures meghatározni osztályt, amely az attribútum érvényes SqlProcedure. amely tájékoztatja a fordító, hogy ez az osztály egy tárolt eljárást. Belül CountEmployees () metódus által meghatározott osztály kódját. Egy vegyületet, amelynek az adatbázis rendszer által létrehozott SqlConnection példány osztályban. Hogy egy kapcsolatot, nyílt () módszer a példány. A CreateCommand () metódus lehetővé teszi a hozzáférést egy példányt SqlCommnd osztályban. amely közvetíti az SQL-parancsot.

Az alábbi kódrészlet:

SELECT utasítást arra használjuk, hogy számolja meg a sorok számát a munkavállalói asztal és megjelenítve az eredményt. A parancs szöveget rendelünk hozzá a CommandText ingatlan a változó cmd fokon által visszaadott CreateCommand (). A továbbiakban ExecuteScalar () eljárás egy példányát SqlCommand. Ez a módszer visszaadja a skaláris érték, amely átalakul egy egész adattípus int, és rendelt sorok.

Most lehet lefordítani a kódot a Visual Studio környezetben. Tettem hozzá ezt az osztályt, hogy a projekt neve CLRStoredProcedures, így a Visual Studio lefordítja a szerelvény az azonos nevű kiterjesztéssel * .dll. A következő példa szemlélteti a következő lépés létre egy tárolt eljárás: létre végrehajtható kódot. Mielőtt futtatja a kódot ebben a példában azt kell tudni, hogy a helyét a lefordított dll-fájlt (általában található a Debug mappában a projekt).

CREATE KÖZGYŰLÉS utasításnak input a kezelt kódot, és létrehozza a megfelelő objektumot, amely létrehozhat egy CLR tárolt eljárásokat, felhasználó által megadott függvények és triggerek. Az utasítás szintaxisa:

A assembly_name paraméter határozza meg a nevét, a szerelvény. Egy adott esetben engedély kikötés nevét adja meg a szerepet, mint tulajdonos a szerelvény. A FROM záradék meghatározza az útvonalat, ahol a szerelvény van betöltve.

WITH PERMISSION_SET javaslat egy nagyon fontos javaslatot CREATE KÖZGYŰLÉS nyilatkozatot, és mindig fel kell tüntetni. Ez határozza meg egy sor hozzáférési jogok, amelyeket a kódot szerelvény. SAFE a jogok a legszigorúbb. assembly kód ezekkel a jogokkal, nem tud hozzáférni a külső rendszer erőforrásait, például fájlokat. Állítsa EXTERNAL_ACCESS jogok teszi a kód szerelvény hozzáférni bizonyos külső erőforrásokat, így egy sor jogi UNSAFE korlátlan hozzáférést biztosít forrásokat, belül és kívül egyaránt az adatbázis-rendszer.

Hogy mentse információt a kódot szerelvény, a felhasználónak kell, hogy képes legyen elvégezni a CREATE KÖZGYŰLÉS nyilatkozatot. összeszerelés tulajdonosa a felhasználó (vagy szerep), végrehajtó ezeket az utasításokat. A tulajdonos a szerelvény lehet egy másik felhasználó a CREATE SCHEMA ajánlat utasításokat az engedélyt.

A Database Engine támogatja a kézi és az ALTER KÖZGYŰLÉS DROP KÖZGYŰLÉS. ALTER KÖZGYŰLÉS nyilatkozat frissítheti az egységet a legújabb verzióra. Ez az állítás hozzáadása vagy eltávolítása társított fájlok a megfelelő egységet. Utasítás DROP KÖZGYŰLÉS eltávolítja a megadott szerelési és az összes hozzá kapcsolódó fájlokat az aktuális adatbázist.

A következő példa bemutatja, hogyan lehet létrehozni egy tárolt eljárás alapján a kezelt kódot korábban végrehajtott:

CREATE ELJÁRÁS nyilatkozatot a példa abban különbözik az ugyanazon utasítást a fenti példák, hogy tartalmaz egy paramétert külső neve. Ez a paraméter határozza meg, hogy a kód által létrehozott CLR. Nevezze el ezt a javaslatot, három részből áll:

assembly_name - nevét jelöli meg az egységet;

class_name - jelzi az általános osztály nevét;

method_name - választható része, meghatározza az eljárás nevét, amelyet az határoz meg az osztályban.

Végrehajtása CountEmployees bemutatott eljárással az alábbi példában:

PRINT utasítás visszaadja a jelenlegi sorok számát a Munkavállaló asztalra.