Utolsó frissítés: 14.08.2017

Az adatműveletek gyakran olyan utasítások halmazát jelentik, amelyeket egy bizonyos sorrendben kell végrehajtani. Például egy termékvásárlás hozzáadásakor adatokat kell bevinni a rendelések táblájába. Mielőtt azonban ezt megtenné, ellenőriznie kell, hogy a vásárolt termék raktáron van-e. Előfordulhat, hogy több további feltételt is ellenőriznie kell. Valójában egy termék megvásárlásának folyamata több műveletet is magában foglal, amelyeket egy bizonyos sorrendben kell végrehajtani. És ebben az esetben optimálisabb lenne ezeket a műveleteket egyetlen objektumba foglalni - tárolt eljárás(tárolt eljárás).

Azaz lényegében a tárolt eljárások egyetlen egységként végrehajtott utasítások halmaza. Így a tárolt eljárások lehetővé teszik az összetett műveletek egyszerűsítését és egyetlen objektumban való elhelyezését. A termék vásárlásának folyamata megváltozik, ennek megfelelően elegendő az eljárási kód megváltoztatása. Vagyis az eljárás a kódkezelést is leegyszerűsíti.

A tárolt eljárások azt is lehetővé teszik, hogy korlátozza a táblákban lévő adatokhoz való hozzáférést, és ezáltal csökkentse az adatokkal kapcsolatos szándékos vagy nem szándékos nem kívánt műveletek valószínűségét.

És egy másik fontos szempont a teljesítmény. A tárolt eljárások általában gyorsabban futnak, mint a hagyományos SQL utasítások. Ennek az az oka, hogy az eljáráskódot az első indításakor egyszer lefordítják, majd lefordított formában elmentik.

Tárolt eljárás létrehozásához használja a CREATE PROCEDURE vagy a CREATE PROC parancsot.

Tehát a tárolt eljárás három Főbb jellemzők: kód egyszerűsítése, biztonság és teljesítmény.

Tegyük fel például, hogy van egy táblázat az adatbázisban, amely a termékekre vonatkozó adatokat tárolja:

TÁBLÁZAT LÉTREHOZÁSA Termékek (ID INT IDENTITY PRIMARY KEY, Product Name NVARCHAR(30) NOT NULL, Gyártó NVARCHAR(20) NOT NULL, ProductCount INT ALAPÉRTELMEZETT 0, Price MONEY NOT NULL);

Hozzon létre egy tárolt eljárást az adatok lekéréséhez ebből a táblázatból:

USE productsdb; ELJÁRÁS LÉTREHOZÁSA ProductSummary AS SELECT Product Name AS Termék, Gyártó, Ár a termékektől

Mivel a CREATE PROCEDURE parancsot külön csomagban kell meghívni, az aktuális adatbázist beállító USE parancsot egy GO parancs követi az új csomag meghatározásához.

Az eljárás neve után ott kell lennie kulcsszó MINT.

Az eljárás törzsének a szkript többi részétől való elkülönítéséhez az eljárás kódját gyakran egy BEGIN...END blokkba helyezik:

USE productsdb; FOGYÁS ELJÁRÁS LÉTREHOZÁSA ProductSummary AS BEGIN SELECT Product Name AS Termék, Gyártó, Ár Termékek VÉGE;

Az eljárás hozzáadása után az SQL Server Management Studio adatbázis-csomópontjában láthatjuk az alcsomópontban Programozhatóság -> Tárolt eljárások:

Az eljárást pedig vizuális felületen keresztül is irányíthatjuk majd.

Az eljárás végrehajtása

Tárolt eljárás végrehajtásához hívja az EXEC vagy EXECUTE parancsot:

EXEC Product Summary

Eljárás eltávolítása

Eljárás eltávolításához használja a DROP PROCEDURE parancsot:

HAJTÁS ELJÁRÁS ProductSummary

Tárolva SQL eljárások egy végrehajtható programmodul, amely különféle objektumok formájában tárolható. Más szóval, ez egy objektum, amely SQL utasításokat tartalmaz. Ezek a tárolt eljárások végrehajthatók az alkalmazáskliensben, hogy megszerezzék jó teljesítmény. Ezenkívül az ilyen objektumokat gyakran más parancsfájlokból vagy akár más szakaszokból hívják meg.

Bevezetés

Sokan úgy vélik, hogy hasonlóak a különféle eljárásokhoz (illetve, kivéve az MS SQL-t). Talán ez igaz. Hasonló paraméterekkel rendelkeznek, és hasonló értékeket tudnak produkálni. Sőt, bizonyos esetekben összeérnek. Például kombinálják őket DDL és DML adatbázisokkal, valamint felhasználói funkciókkal (kódnéven UDF).

A valóságban az SQL tárolt eljárások rendelkeznek széleskörű előnyök, amelyek megkülönböztetik őket a hasonló folyamatoktól. Biztonság, programozási rugalmasság, termelékenység – mindez egyre több adatbázissal dolgozó felhasználót vonz. Az eljárások népszerűségének csúcspontja 2005-2010 között volt, amikor megjelent a Microsoft „SQL Server Management Studio” nevű programja. Segítségével az adatbázisokkal való munka sokkal könnyebbé, praktikusabbá és kényelmesebbé vált. Ez évről évre népszerűvé vált a programozók körében. Ma már teljesen ismerős program, amely az adatbázisokkal „kommunikáló” felhasználók számára egyenrangú az Excellel.

Egy eljárás meghívásakor azt azonnal maga a szerver dolgozza fel anélkül szükségtelen folyamatokés a felhasználói beavatkozás. Ezt követően bármilyen törlést, végrehajtást vagy módosítást végezhet. Mindezért a DDL operátor a felelős, aki egyedül végzi el a legösszetettebb műveleteket az objektumok feldolgozásához. Ráadásul mindez nagyon gyorsan megtörténik, és a szerver valójában nem töltődik be. Ez a sebesség és teljesítmény lehetővé teszi nagy mennyiségű információ nagyon gyors átvitelét a felhasználótól a szerverre és fordítva.

Ennek a technológiának az információkkal való munkavégzéshez való megvalósításához számos programozási nyelv létezik. Ide tartozik például az Oracle PL/SQL, az InterBase és Firebird rendszerek PSQL-je, valamint a klasszikus Microsoft Transact-SQL. Mindegyiket tárolt eljárások létrehozására és végrehajtására tervezték, ami lehetővé teszi a nagy adatbázis-feldolgozók számára, hogy saját algoritmusaikat használják. Erre azért is van szükség, hogy az ilyen információkat kezelők minden objektumot megvédhessenek a harmadik felek jogosulatlan hozzáférésétől, és ennek megfelelően bizonyos adatok létrehozásától, módosításától vagy törlésétől.

Termelékenység

Ezek az adatbázis-objektumok többféleképpen programozhatók. Ez lehetővé teszi a felhasználók számára, hogy kiválasszák a legmegfelelőbb módszert, így erőfeszítést és időt takarítanak meg. Ezenkívül az eljárás maga is feldolgozásra kerül, így elkerülhető a szerver és a felhasználó közötti kommunikációra fordított hatalmas idő. A modul átprogramozható és be is cserélhető a helyes irányt teljesen bármely pillanatban. Különösen érdemes figyelni az SQL tárolt eljárás elindításának sebességére: ez a folyamat gyorsabban megy végbe, mint a többi hasonló, ami kényelmessé és univerzálissá teszi.

Biztonság

Ez a fajta információfeldolgozás abban különbözik a hasonló folyamatoktól, hogy fokozott biztonságot garantál. Ezt az a tény biztosítja, hogy teljesen kizárható más felhasználók hozzáférése az eljárásokhoz. Ez lehetővé teszi az adminisztrátor számára, hogy önállóan végezzen műveleteket velük, anélkül, hogy félne az információk elfogásától vagy az adatbázishoz való jogosulatlan hozzáféréstől.

Adatátvitel

Az SQL tárolt eljárás és a kliens alkalmazás közötti kapcsolat a paraméterek és a visszatérési értékek használata. Ez utóbbinak nem kell átadnia az adatokat a tárolt eljárásnak, hanem ezeket az információkat (főleg a felhasználó kérésére) az SQL számára feldolgozzák. Miután a tárolt eljárás befejezte a munkáját, visszaküldi az adatcsomagokat (de ismételten opcionálisan) az őt hívó alkalmazásnak, különféle módszerek, melynek segítségével egy tárolt SQL-eljárás hívása és egy visszatérés is végrehajtható, pl.

Adatátvitel Output típusú paraméter használatával;

Adattovábbítás a return operátor segítségével;

Adattovábbítás a kiválasztási operátor segítségével.

Most nézzük meg, hogyan néz ki ez a folyamat belülről.

1. Hozzon létre egy EXEC tárolt eljárást SQL-ben

Eljárást létrehozhat az MS SQL-ben (Managment Studio). Az eljárás létrehozása után megjelenik az adatbázis programozható csomópontjában, amelyben a létrehozási eljárást az operátor végrehajtja. Az SQL tárolt eljárások végrehajtásához egy EXEC folyamatot használnak, amely magának az objektumnak a nevét tartalmazza.

Amikor létrehoz egy eljárást, először annak neve jelenik meg, majd egy vagy több hozzárendelt paraméter. A paraméterek opcionálisak lehetnek. A paraméter(ek), azaz az eljárás törzsének felírása után néhány szükséges műveletet el kell végezni.

A lényeg az, hogy egy testben lokális változók helyezkedhetnek el, és ezek a változók az eljárásokhoz képest is lokálisak. Más szóval, csak a Microsoft SQL Server eljárás törzsében tekinthetők meg. A tárolt eljárások ebben az esetben helyinek minősülnek.

Tehát egy eljárás létrehozásához szükségünk van az eljárás nevére és legalább egy paraméterre, mint az eljárás törzsére. Ne feledje, hogy ebben az esetben nagyszerű lehetőség egy eljárás létrehozása és végrehajtása az osztályozóban szereplő sémanévvel.

Az eljárás törzse bármilyen lehet, például tábla létrehozása, tábla egy vagy több sorának beszúrása, az adatbázis típusának és jellegének megállapítása stb. Az eljárási szerv azonban korlátozza bizonyos műveletek végrehajtását. Az alábbiakban felsorolunk néhány fontos korlátozást:

A szervezet nem hozhat létre más tárolt eljárást;

A test nem kelthet hamis benyomást a tárgyról;

A testnek nem szabad kiváltót létrehoznia.

2. Változó beállítása az eljárás törzsében

Lokális változókat állíthat be az eljárás törzséhez, és akkor azok kizárólag az eljárás törzsében lesznek. Célszerű a tárolt eljárástörzs elején változókat létrehozni. De az adott objektum törzsében bárhol beállíthat változókat.

Néha észreveheti, hogy egy sorban több változó van beállítva, és minden változó paramétert vessző választ el. Vegye figyelembe azt is, hogy a változó előtt @ előtag van. Az eljárás törzsében beállíthatja a változót, ahol csak akarja. Például a @NAME1 változó deklarálható az eljárástörzs végén. Egy deklarált változó értékének hozzárendeléséhez személyes adatok halmazát kell használni. Ellentétben azzal a helyzettel, amikor egynél több változót deklarálnak ugyanabban a sorban, ebben a helyzetben csak egy személyes adatkészlet kerül felhasználásra.

A felhasználók gyakran felteszik a kérdést: "Hogyan lehet több értéket hozzárendelni egy utasításhoz egy eljárás törzsében?" Jól. Érdekes kérdés, de sokkal könnyebb megtenni, mint gondolnád. Válasz: Olyan párok használata, mint például "Select Var = érték". Ezeket a párokat vesszővel elválasztva használhatja.

Számos példa mutat be embereket, akik egyszerű tárolt eljárást hoznak létre és hajtanak végre. Egy eljárás azonban képes olyan paramétereket elfogadni, hogy az őt hívó folyamat értékei közel állnak hozzá (de nem mindig). Ha egybeesnek, akkor megfelelő folyamatok indulnak el a testben. Például, ha létrehoz egy eljárást, amely elfogad egy várost és régiót a hívótól, és adatokat ad vissza arról, hogy hány szerző tartozik a megfelelő városhoz és régióhoz. Az eljárás lekérdezi az adatbázis szerzői tábláit, például a Pubokat a szerzők számának végrehajtásához. Például ezeknek az adatbázisoknak a beszerzéséhez a Google letölti az SQL-szkriptet az SQL2005 oldalról.

Az előző példában az eljárás két paramétert vesz igénybe, amelyeket angolul @State és @City néven nevezünk. Az adattípus megegyezik az alkalmazásban meghatározott típussal. Az eljárás törzsében @TotalAuthors belső változók találhatók, és ez a változó a szerzők számának megjelenítésére szolgál. Következik a lekérdezés kiválasztása szakasz, amely mindent kiszámít. Végül a számított érték kinyomtatásra kerül a kimeneti ablakban a print utasítás segítségével.

Hogyan hajtsunk végre egy tárolt eljárást SQL-ben

Az eljárás végrehajtásának két módja van. Az első mód paraméterek átadásával azt mutatja meg, hogy a vesszővel tagolt lista hogyan kerül végrehajtásra az eljárás neve után. Tegyük fel, hogy két értékünk van (mint az előző példában). Ezeket az értékeket a @State és @City eljárásparaméter-változók segítségével gyűjtjük össze. Ebben a paraméterátadási módszerben a sorrend fontos. Ezt a módszert ordinális argumentum átadásnak nevezzük. A második módszernél a paraméterek már közvetlenül hozzá vannak rendelve, és ebben az esetben a sorrend nem fontos. Ez a második módszer nevesített argumentumok átadásaként ismert.

Az eljárás kissé eltérhet a szokásostól. Minden ugyanaz, mint az előző példában, de csak itt vannak eltolva a paraméterek. Ez azt jelenti, hogy először a @City paraméter kerül tárolásra, a @State pedig az alapértelmezett érték mellé. Az alapértelmezett paraméter általában külön kiemelve van. Az SQL tárolt eljárások csak paraméterként kerülnek átadásra. Ebben az esetben, feltéve, hogy az "UT" paraméter helyettesíti az alapértelmezett "CA" értéket. A második végrehajtás során csak egy argumentumérték kerül átadásra a @City paraméterhez, és a @State paraméter az alapértelmezett "CA" értéket veszi fel. A tapasztalt programozók azt tanácsolják, hogy alapértelmezés szerint minden változónak a paraméterlista végén kell lennie. Ellenkező esetben a végrehajtás nem lehetséges, és akkor megnevezett argumentumok átadásával kell dolgozni, ami hosszabb és összetettebb.

4. SQL Server tárolt eljárások: Visszaküldési módszerek

Három fontos módja van az adatok küldésének egy úgynevezett tárolt eljárásban. Az alábbiakban felsoroljuk őket:

Egy tárolt eljárás értékét adja vissza;

Tárolt eljárási paraméter kimenet;

A tárolt eljárások egyikének kiválasztása.

4.1 Értékek visszaadása SQL tárolt eljárásokból

Ebben a technikában egy eljárás értéket rendel egy helyi változóhoz, és visszaadja azt. Egy eljárás közvetlenül is visszaadhat egy állandó értéket. A következő példában létrehoztunk egy eljárást, amely a szerzők teljes számát adja vissza. Ha összehasonlítja ezt az eljárást az előzőekkel, láthatja, hogy a nyomtatási érték megfordul.

Most nézzük meg, hogyan kell végrehajtani egy eljárást és kinyomtatni a visszatérési értékét. Az eljárás végrehajtásához egy változó beállítására és nyomtatásra van szükség, amely a teljes folyamat után megtörténik. Vegye figyelembe, hogy a print utasítás helyett használhat Select utasítást, például Select @RetValue vagy OutputValue utasítást.

4.2 SQL tárolt eljárási paraméterek kimenete

A válaszérték egyetlen változó visszaadására használható, amit az előző példában láttunk. Az Output paraméter használata lehetővé teszi, hogy egy eljárás egy vagy több változó értéket küldjön a hívónak. A kimeneti paramétert pontosan ez az „Output” kulcsszó jelöli ki az eljárás létrehozásakor. Ha egy paramétert kimeneti paraméterként adunk meg, akkor az eljárásobjektumnak értéket kell hozzárendelnie. Az SQL tárolt eljárások, amelyekre az alábbiakban példákat láthatunk, ebben az esetben összefoglaló információkkal térnek vissza.

Példánkban két kimeneti név lesz: @TotalAuthors és @TotalNoContract. A paraméterek listájában fel vannak tüntetve. Ezek a változók értékeket rendelnek hozzá az eljárás törzsén belül. Amikor kimeneti paramétereket használunk, a hívó az eljárás törzsében láthatja a beállított értéket.

Ezenkívül az előző forgatókönyvben két változót deklarálnak az MS SQL Server által a kimeneti paraméterben tárolt eljárások által tárolt értékek megtekintéséhez. Ezután az eljárás a „CA” paraméter normál értékének megadásával történik. A következő paraméterek kimeneti paraméterek, ezért a deklarált változók a megadott sorrendben kerülnek átadásra. Vegye figyelembe, hogy változók átadásakor a kimeneti kulcsszó is itt van beállítva. Az eljárás sikeres befejezése után a kimeneti paraméterek által visszaadott értékek megjelennek az üzenetablakban.

4.3 Az SQL tárolt eljárások egyikének kiválasztása

Ezt a technikát arra használják, hogy egy értékkészletet adattáblaként (RecordSet) küldjenek vissza a hívó tárolt eljáráshoz. Abban SQL példa Egy @AuthID paraméterekkel rendelkező tárolt eljárás lekérdezi a Szerzők táblát az adott @AuthId paraméterrel visszaadott rekordok szűrésével. A Select utasítás dönti el, hogy mit küldjön vissza a tárolt eljárás hívójának. A tárolt eljárás végrehajtásakor az AuthId visszaadásra kerül. Ez az eljárás itt mindig csak egy rekordot ad vissza, vagy egyet sem. Egy tárolt eljárás azonban nem korlátozza egynél több rekord visszaküldését. Nem ritka, hogy olyan példákat látunk, amikor az adatok kiválasztott paraméterek felhasználásával kerülnek visszaadásra, kiszámított változókkal, több összeg megadásával.

Végül

A tárolt eljárás meglehetősen komoly szoftver modul, visszaküldése vagy továbbítása, valamint a szükséges változók beállítása a kliens alkalmazásnak köszönhetően. Mivel a tárolt eljárás önmagában fut a szerveren, elkerülhető a hatalmas mennyiségű adatcsere a szerver és a kliens alkalmazás között (bizonyos számításoknál). Ez lehetővé teszi az SQL szerverek terhelésének csökkentését, ami természetesen a tulajdonosaik számára előnyös. Az egyik altípus a T SQL tárolt eljárások, de ezek tanulmányozása szükséges azok számára, akik lenyűgöző adatbázisokat készítenek. Nagy, sőt hatalmas számú árnyalat is hasznos lehet a tárolt eljárások tanulmányozásakor, de erre inkább azoknak van szükségük, akik a programozásba terveznek bekapcsolódni, akár szakmailag is.

Tárolt eljárás tárolt eljárás) egy elnevezett adatbázis-program objektum. Az SQL Server többféle tárolt eljárással rendelkezik.

Rendszerben tárolt eljárások rendszerben tárolt eljárás) a DBMS-fejlesztők biztosítják, és a rendszerkönyvtárral vagy a beszerzéssel kapcsolatos műveletek végrehajtására szolgálnak rendszer információ. Nevük általában "sp_" előtaggal kezdődik. Minden típusú tárolt eljárást az EXECUTE paranccsal futtathat, amely EXEC-re rövidíthető. Például az sp_helplogins tárolt eljárás, amely paraméterek nélkül fut, két jelentést készít a fióknevekről (Angol) bejelentkezések) és a megfelelő felhasználók az egyes adatbázisokban (Angol) felhasználók).

EXEC sp_helplogins;

A rendszerben tárolt eljárásokkal végrehajtott műveletekről a táblázat A 10.6 néhány példát mutat be. Az SQL Serverben összesen 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 az ideiglenes objektumok adatbázisában. 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 a "#" előtaggal kell kezdődnie, ha helyi ideiglenes tárolt eljárásról van szó, vagy a "##" előtaggal, ha globális. Helyi ideiglenes eljárás csak azon a kapcsolaton belül használható, amelyben létrejött, globális pedig más kapcsolatokon belül is.

Az SQL Server programozható objektumai Transact-SQL eszközök vagy összeállítások segítségével hozhatók létre (Angol) assembly) a Microsoft.Net Framework CRL (Common Language Runtime) környezetében. Ez az oktatóanyag csak az első módszerre vonatkozik.

Tárolt eljárások létrehozásához használja a CREATE PROCEDURE (rövidíthető PROC-ra) utasítást, amelynek formátumát az alábbiakban adjuk meg:

LÉTREHOZÁS (PROC I ELJÁRÁS) proc_name [ ; szám ]

[(gparameter data_type)

["alapértelmezett] |

[VAL VEL [ ,...n ] ]

[REPLIKÁLÁSHOZ]

AS ([ BEGIN ] sql_statement [;] [ ...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 átkerült innen korábbi verziók Az SQL Server 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 beállítás azt határozza meg, hogy a rendszer minden alkalommal újrafordítsa a szöveget, amikor az eljárást meghívják. Normál esetben az első futtatáskor összeállított eljárás a gyorsítótárban tárolódik, ami nagyobb teljesítményt tesz lehetővé.

Az EXECUTE AS azt a biztonsági környezetet határozza meg, 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 programozható objektumra, hanem más, általa érintett adatbázis-objektumokra is jogosultságokkal kell rendelkeznie. A VÉGREHAJTÁS MÁSKÉNT azt jelenti, hogy a felhasználó kontextusának felhasználását hozza létre vagy módosítja a programozható objektumot. A TULAJDONOS megadja, hogy a kód az eljárás jelenlegi tulajdonosának kontextusában kerül végrehajtásra. Ha nincs megadva tulajdonos, akkor a séma tulajdonosát feltételezzük, amelyhez tartozik. EXECUTE AS "user_name" lehetővé teszi a felhasználónév explicit megadását (egy idézőjelben).

Paraméterek adhatók meg egy eljáráshoz. Ezek olyan helyi változók, amelyekkel értékeket adnak át egy eljárásnak. Ha egy paramétert az OUTPUT (vagy röviden OUT) kulcsszóval deklarálunk, az egy kimeneti érték: az eljárásban annak befejezése után megadott értéket az eljárást meghívó program használhatja. 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 alapértelmezett értékek rendelhetők, amelyek akkor kerülnek felhasználásra, ha a paraméterérték nincs kifejezetten megadva az eljárás meghívásakor. Vegyünk egy példát:

PROC LÉTREHOZÁSA surma (@a int, @b int=0,

©eredmény int 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: visszaadja az értéket a hívó programnak. Az elvégzett műveletek meglehetősen egyszerűek - a kimeneti paraméter két bemeneti érték összegét kapja.

Az SQL Server Management Studio használatakor a létrehozott tárolt eljárás a programozható adatbázis-objektumok részben található (Angol) 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éterei kifejezetten konstansként vannak megadva, a hívás kimeneti paraméteréhez pedig az OUTPUT kulcsszó. A második opció egy változó értékét használja első bemeneti paraméterként, és meghatározza, hogy az alapértelmezett értéket kell használni a második paraméterhez a DEFAULT kulcsszó használatával:

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,DEFAULT , 0c 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. Tegyük fel, hogy ki kell számolnunk, hogy a Bookl táblázatban szereplő könyvek közül hány könyv jelent meg egy adott évtartományban. Ezenkívül, ha a kezdeti év nagyobb, 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 @eredmény= (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 (jelen esetben 1 lesz). A PRINT utasításban 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;

HA 0ret=l PRINT "A kezdő év nagyobb, mint a befejező é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ével kapcsolatos problémákat. A következő tárolt eljárás ellenőrzi a #TaL2 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 #TaL2 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 (id, name)VALUES (0id,0name)

SELECT * FROM dbo. #Tab2 –№1

A tárolt eljárás első meghívása előtt létrehozzuk a benne használt #TaL2 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, de ebben az esetben más 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:

TÁBLÁZAT LÉTREHOZÁSA dbo.#Tab2 (id int, név varchar(30));

EXEC My_Procl 0name="lvan", 0id=2;

SELECT * FROM dbo.#Tab2; –№2

A megadott példában SELECT utasítás kétszer fog működni: először – az eljáráson belül, másodszor – a hívó kódrészletből (a „No. 2” megjegyzéssel jelölve).

Az eljárás második hívása előtt töröljük a #TaL2 ideiglenes táblát. Ezután a tárolt eljárásból létrejön egy azonos nevű ideiglenes tábla:

DROP TABLE dbo.#Tab2;

EXEC My_Procl 0name="Ivan", 0id=2;

SELECT * FROM dbo.#Tab2; –№2

Ebben az esetben csak az eljáráson belül található SELECT utasítás ("Xa 1" megjegyzéssel) jelenít meg adatokat. A SELECT "No. 2" végrehajtása hibát eredményez, mivel a tárolt eljárásban létrehozott ideiglenes tábla az eljárás visszatérésekor már törlésre kerül a tempdb adatbázisból.

A DROP PROCEDURE utasítással eldobhat egy tárolt eljárást. Formátumát az alábbiakban mutatjuk be. Egy utasítással több tárolt eljárást is törölhet, vesszővel elválasztva:

LEADÁS (PROC I. ELJÁRÁS) ( eljárás ) [

Például töröljük a korábban létrehozott összegző eljárást:

DROP PROC summa;

Módosíthat egy meglévő eljárást (és valójában újradefiniálhatja) a használatával ALTER nyilatkozat ELJÁRÁS

PROC rövidítés). Az ALTER kulcsszó kivételével az utasítás formátuma lényegében megegyezik a CREATE PROCEDURE-éval. Például változtassuk meg a dbo eljárást. rownum, beállítva a tulajdonos biztonsági környezetében történő végrehajtásra:

ALTER PROC dbo.rownum (SFirsYear int,

SLastYear int, Result int OUTPUT)

VÉGREHAJTÁSSAL Tulajdonosként – telepíthető opció

HA 0FirsYear>0LastYear VISSZATÉRÉS 1 MÁS KEZDÉS

SET 0result= (SELECT COUNT(*) FROM dbo.Bookl

HOL AZ SFirsYear ÉS SLastYear KÖZÖTT);

Bizonyos esetekben szükség lehet egy parancs dinamikus generálására és végrehajtására az adatbázis-kiszolgálón. Ez a probléma az EXEC operátor használatával is megoldható. Az alábbi példa rekordokat kér le a Bookl táblából, ha az Year attribútum megegyezik a változó által megadott értékkel:

DECLARE 0у int = 2000;

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

A dinamikusan generált utasítások végrehajtása megteremti a számítógépes támadások, például az „SQL injekció” megvalósításának előfeltételeit. (Angol) 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 fordul elő, amikor a helyettesítendő paramétereket a felhasználói bevitel eredményeiből veszik.

Változtassuk meg kicsit az előző példát:

DECLARE 0у varchar(100);

SET 0у="2ООО"; – 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. webes alkalmazáson keresztül), akkor a példa kódunk „normális” viselkedését szemlélteti.

DECLARE 0у varchar(100);

SET 0у="2000; TÖRLÉS A dbo.Book2-ből"; - injekció

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

Ilyen esetekben ajánlatos 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 egyik akadálya annak, hogy SQL injekció. Anélkül, hogy részletesen megvizsgálnánk a formátumát, nézzünk egy példát a korábban bemutatotthoz:

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 vezérli azt a végrehajtás során. Az idézőjelek előtti "N" betű azt jelzi, hogy ez egy Unicode literális konstans, amint azt az eljárás megköveteli. Egy paraméterhez nemcsak állandó érték rendelhető, hanem egy másik változó értéke is.

tárolt eljárás csak akkor lehetséges, ha annak az adatbázisnak a keretében hajtják végre, ahol az eljárás található.

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

Az SQL Servernek többféle típusa van tárolt eljárások.

• Rendszer tárolt eljárások különféle adminisztratív tevékenységek elvégzésére tervezték. Szinte minden szerveradminisztrációs tevékenység az ő segítségükkel történik. Mondhatjuk, hogy rendszerszerű 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ázatainak megváltoztatásában, hozzáadásában, törlésében és lekérésében rejlik. Rendszer tárolt eljárások sp_ előtaggal rendelkeznek, a rendszer adatbázisban 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– teljes értékű adatbázis-objektum. Ennek eredményeként mindegyik tárolt eljárás egy adott adatbázisban található, ahol végrehajtódik.
• Ideiglenes tárolt eljárások csak egy ideig léteznek, utána 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 neki, 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ó megszakítja a kapcsolatot, vagy a szervert újraindítják vagy leállítják. Globális ideiglenes tárolt eljárások elérhetők minden olyan kiszolgálóról származó kapcsolathoz, amelyen ugyanaz az eljárás. Ennek meghatározásához adjon neki egy nevet, amely ## karakterekkel kezdődik. Ezek az eljárások a kiszolgáló újraindításakor vagy leállításakor törlődnek, vagy ha a létrejöttük környezetében lévő kapcsolat 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ő problémák 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, adjon neki egy nevet sp_ előtaggal, és helyezze el rendszer alapja adat. Ez az eljárás bármely helyi szerveradatbázisban elérhető lesz;
• hozzáférési jogok tervezése. Alkotás közben tárolt eljárás figyelembe kell venni, hogy ugyanolyan 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. A legtöbb programozási nyelvben alkalmazott eljárásokhoz hasonlóan, 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 a többi hívást is tárolt eljárások.

Új létrehozása és meglévő megváltoztatása tárolt eljárás a következő paranccsal történik:

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

Nézzük meg ennek a parancsnak a paramétereit.

Az sp_ , # , ## előtagok használatával a létrehozott eljárás rendszerké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 annak elhelyezkednie kell. Így annak érdekében, hogy a létrehozott tárolt eljárás egy adott adatbázisban ki kell adnia a CREATE PROCEDURE parancsot az adott adatbázis környezetében. Amikor elfordul a testtől tárolt eljárás a rövidített nevek használhatók ugyanannak az adatbázisnak az objektumaihoz, vagyis az adatbázisnév megadása nélkül. Ha más adatbázisokban található objektumokhoz kell hozzáférnie, az adatbázis nevének megadása kötelező.

A névben szereplő szám egy azonosító számot tárolt eljárás, amely egyedileg azonosítja az eljárások egy csoportjában. A könnyebb kezelhetőség érdekében az eljárások logikailag azonos típusúak 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 paraméterek használhatók, amelyek nevének a helyi változók nevéhez hasonlóan @ szimbólummal kell kezdődnie. Egy tárolt eljárás Több paramétert 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 típus megfelelő SQL adatok, 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 megadva az OUTPUT kulcsszót.

Az OUTPUT kulcsszó jelenléte azt jelenti, hogy a megfelelő paraméter adatokat küld vissza tárolt eljárás. Ez azonban 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 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 reguláris paraméterekhez engedélyezett kifejezések vagy állandók nem engedélyezettek.

A VARYING kulcsszó a következővel együtt használatos

A Microsoft SQL Serverben saját algoritmusai megvalósításához és automatizálásához ( számításokat) tárolt eljárásokat használhat, ezért ma arról lesz szó, hogyan jönnek létre, módosíthatók és törölhetők.

De először egy kis elmélet, hogy megértse, mik azok a tárolt eljárások, és miért van rájuk szükség a T-SQL-ben.

Jegyzet! Kezdő programozóknak ajánlom a következő hasznos anyagokat a T-SQL-ről:

• A T-SQL nyelv részletesebb tanulmányozásához javaslom a - The T-SQL Programmer's Path című könyv elolvasását is. Oktatóanyag a Transact-SQL nyelvről;
• Professzionális online kurzusok T-SQL-ről

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

Tárolt eljárások– ezek 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 arra szolgálnak, hogy újrafelhasználható kódot tároljanak a szerveren, például írt egy bizonyos algoritmust, szekvenciális számítást vagy többlépéses SQL utasítást, és annak érdekében, hogy ne hajtsa végre az ebben az algoritmusban található összes utasítást minden alkalommal, formázhatja 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 nem fordítja újra.

Egy tárolt eljárás SQL Serverben való futtatásához az EXECUTE parancsot a neve elé kell írni, és ezt a parancsot EXEC-re is rövidíthetjük. 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. eljárásokat külön indítják el.

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. Szinte bármilyen típusú SQL utasítást is használhat az eljárásokban, például CREATE TABLE táblák létrehozásához vagy EXECUTE, azaz. egyéb eljárások kihívása. Kivételt képeznek az utasítások többféle típusa, például: függvények, nézetek, triggerek létrehozása vagy módosítása, sémák létrehozása és számos más hasonló utasítás, például az adatbázis-kapcsolati környezetet (USE) sem lehet váltani egy tárolt eljárásban.

Egy tárolt eljárásnak lehetnek bemeneti és kimeneti paraméterei, visszaadhat táblázatos adatokat, vagy 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. Hogy könnyebb legyen a lekérdezések megfogalmazása ezzel az operátorral ( mint tudod, a PIVOT szintaxisa meglehetősen összetett), Í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” anyag példát ad 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

Példák forrásadatai

Az alábbi példák mindegyike a Microsoft SQL Server 2016 Express rendszerben fut. 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 árakkal.

Táblázat létrehozására vonatkozó utasítások CREATE TABLE TesztTable( 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 TesztTable(CategoryID, Product Name, Price) ÉRTÉKEK (1 , "Egér", 100), (1, "Billentyűzet", 200), (2, "Telefon", 400) GO --Válassza ki a lekérdezést SELECT * FROM TestTable

Az adatok megvannak, 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 - a CREATE PROCEDURE utasítás

A tárolt eljárások egy 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. Ezek után beírod az AS kulcsszót és megnyitod az utasításblokkot a BEGIN kulcsszóval, ezt a blokkot az END szóval zárod. 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 hozzáad új bejegyzés, azaz új termék tesztasztalunkhoz. Ehhez három bemeneti paramétert adunk meg: @CategoryId – termékkategória azonosító, @ProductName – terméknév és @Price – termék ára, ezt a paramétert Lesz egy opcionális, pl. nem szükséges átadni az eljárásnak ( például még nem tudjuk az árat), erre a célra egy alapértelmezett értéket adunk meg 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 normál változók ( Mint tudják, a változókat @ jellel jelöljük). Ha kimeneti paramétereket kell megadni, akkor a paraméter neve után tüntesse fel az OUTPUT ( vagy röviden OUT).

A BEGIN...END blokkban írunk egy utasítást az adatok hozzáadására, valamint egy SELECT utasítást az eljárás végén, 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 vegye figyelembe az új, most hozzáadott terméket. Ebben a tárolt eljárásban is hozzáadtam a bejövő paraméter feldolgozását, nevezetesen a törlést extra terek szövegsor elején és végén, hogy kiküszöbölje azokat a helyzeteket, amikor véletlenül több szóköz került beírásra.

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

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) AS BEGIN és a szövegsor végén SET @ProductName = LTRIM(RTRIM(@ProductName)); --Új rekord hozzáadása INSERT INTO TestTable(CategoryId, Product Name, Price) VALUES (@CategoryId, @ProductName, @Price) --Az adatok visszaadása 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 egyszerűen listázva és az eljárás neve után a megfelelő értékek megadásával kerülnek átadásra az eljárásoknak ( a kimeneti paramétereknél az OUTPUT parancsot is meg kell adni). Előfordulhat azonban, hogy a paraméterek nevei nem adhatók 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).

Az alapértelmezett értékkel rendelkező paramétereket nem kell megadni, 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. Hívja meg az árat jelző eljárást EXEC TestProcedure @CategoryId = 1, @ProductName = "Teszttermék 2", @Price = 300 --3. Hívja meg az eljárást a paraméterek nevének megadása nélkül 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 már létező eljárás megváltoztatásához csak az ALTER PROCEDURE-t 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ásunkat, mondjuk a @Price paramétert, pl. árat, 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 eljárást ALTER PROCEDURE TesztProcedure (--Bejövő paraméterek @CategoryId INT, @ProductName VARCHAR(100), @PÉNZár) AS BEGIN --Utasítások, amelyek megvalósítják az algoritmust --Bejövő paraméterek feldolgozása --Fölös szóközök eltávolítása az elején és a szövegsorok vége SET @ProductName = LTRIM(RTRIM(@ProductName)); -- Adjon hozzá egy új rekordot INSERT INTO TestTable(CategoryId, Product Name, Price) VALUES (@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, ezt az utasítások alapján teheti meg 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 hibával meghiúsulnak, mivel az általuk hivatkozott eljárás már nem létezik.

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