Tárolt eljárások SQL-ben | Áttekintés

Ebben a blogban azokról az SQL-ben tárolt eljárásokról fogunk beszélni, amelyek segítségével elmenthet egy kódkészletet, és ismételten használhatja, amikor csak szüksége van rá. A tárolt eljárások hasonlóak a nézetekhez. A tárolt eljárásokkal azonban végrehajthat például DROP, TRUNCATE, DELETE stb., amelyeket nem tud végrehajtani nézetekkel.

A tárolt eljárások szintén előre le vannak fordítva, így gyorsabban futnak, mint a nézetek. Ezenkívül minimalizálja az adatbázis-kiszolgálónak küldött adatok mennyiségét.

Tartalomjegyzék

• Tárolt eljárások szintaxisa SQL-ben: Paraméterek nélkül
• Tárolt eljárások szintaxisa SQL-ben: Paraméterekkel
• Az SSMS-ben tárolt eljárások mintaforgatókönyve
• Második példa forgatókönyv
• Harmadik példa forgatókönyv
• A mentett tárolt eljárások helye az SQL-ben
• Tárolt eljárás létrehozása SQL-ben paraméterekkel
• Tárolt eljárás létrehozása alapértelmezett értékkel
• Következtetés

Tárolt eljárások szintaxisa SQL-ben: Paraméterek nélkül

Paraméterek nélküli tárolt eljárás létrehozásához a CREATE funkcióval kell kezdenie . Ezután adja hozzá a PROC függvényt, közvetlenül utána írja be az eljárás nevét, és adja hozzá az AS függvényt.

A tárolt eljárás létrehozása hasonló a táblák és nézetek létrehozásához. A különbség azonban az adatgyűjtés folyamatában van.

Például, ha adatokat szeretnénk lekérni egy nézetből, akkor a „ SELECT * FROM view_name ” paramétert használjuk. Másrészt a tárolt eljárásokhoz az EXEC-et használjuk, ami „végrehajtást” jelent, majd utána írjuk be a tárolt eljárás nevét .

Miután végrehajtottunk egy tárolt eljárást , az az Ön által hozzáadott utasítások vagy parancsok alapján is megjeleníti az információkat.

Tárolt eljárások szintaxisa SQL-ben: Paraméterekkel

Térjünk át ide. A folyamat hasonló az előző példámhoz, amelyet bemutattam. Az egyetlen különbség az, hogy az AS függvény előtt meg kell adni a paramétereket, és közvetlenül utána az adattípust is.

A parancsokban a tárolt eljárás létrehozása során deklarált paramétert is használhatja. Ezzel időt takaríthat meg több érték megadásával. Például a customerID1 , customerID2 stb. manuális beírása helyett a @custid vagy a paramétert használtuk, hogy elkerüljük a hosszú kódkészletet.

Kérjük, vegye figyelembe, hogy paraméter használatakor vagy belefoglalásakor fontos a @ szimbólum használata. Tetszőleges számú paramétert is megadhat, nem csak egyet. Így hajtjuk végre a tárolt eljárásokat paraméterekkel.

Mint látható, az 1 -es értékű paramétert is használtuk a tárolt eljárás végrehajtása során. Ez csak a custid1 információit jeleníti meg .

Az SSMS-ben tárolt eljárások mintaforgatókönyve

Nézzünk még példákat az SQL-ben tárolt eljárásokra. Először egy minta tárolt eljárást fogok létrehozni az alább kiemelt paranccsal.

A tárolt eljárás létrehozásának elnevezési konvenciójához általában az „usp” vagy az „sp” kifejezést használjuk annak jelzésére, hogy ez egy tárolt eljárás. Ha kíváncsi, mit jelent az „usp”, egyszerűen a felhasználó által meghatározott tárolt eljárást jelenti. 

Ezután lekérdezéseket fogunk írni a tárolt eljáráshoz, amelyet a végrehajtáskor feldolgozni kell.

Az előző példában szereplő lekérdezések egyszerűen eltávolítják a dbo.stageOrders nevű táblát . Ezután újra létrehozza a dbo.stageOrders fájlt a Sales.SalesOrderHeader tábla adataival . 

Hozzuk létre ezt a tárolt eljárást a következő kód kiemelésével és az Execute gombra kattintva.

Ezt követően egy ehhez hasonló üzenetet kell látnia.

Ezután végrehajtjuk a tárolt eljárást az EXEC usp_TEST futtatásával .

Az usp_TEST tárolt eljárás végrehajtása után a dbo.stageOrdersnek rendelkeznie kell a Sales.SalesOrderHeader adataival . Ellenőrizzük a dbo.stageOrders tartalmát az alábbi parancs futtatásával.

Ennek eredményeként ezek azok az adatok, amelyeket a dbo.stageOrders -ben hoztunk létre a tárolt eljárásunkhoz hozzáadott parancsok alapján.

Második példa forgatókönyv

Térjünk át egy másik példára. Ezúttal nem szeretném eldobni a dbo.stageOrders táblát, és újra létrehozni ugyanazt a táblát a Sales.SalesOrderHeader táblából származó adatokkal .

Ehelyett csak azt szeretném, hogy a Sales.SalesOrderHeader tábla adatai megjelenjenek, amikor végrehajtom az „ usp_TEST ” tárolt eljárást .

Ennek végrehajtásához módosítanom kell a nemrégiben létrehozott tárolt eljárást az ALTER utasítás használatával.

Ebben a példában az ALTER utasítást használtuk, amely a tárolt eljárásunk módosítására szolgál. Ezt nézetekre és táblázatokra is alkalmazhatja. Miután futtattuk a fenti képernyőképen látható kódot, módosítania kell a tárolt eljárásunk által feldolgozandó lekérdezéseket.

Ezúttal nem fog újra létrehozni egyetlen táblázatot sem, mint korábban. Ha végrehajtjuk az „ usp_TEST ” parancsot, akkor az csak a Sales.SalesOrderHeader táblából hozza az adatokat .

Harmadik példa forgatókönyv

Vegyünk egy másik példát. Ezúttal újra felhasználjuk a dbo.stageOrders táblát, amely az SSMS- ben tárolt eljárás létrehozásának első példájában volt . Ezzel a példával kezdjük a dbo.stageOrders tábla kiválasztásával. 

A dbo.stageOrders összes adatának kiválasztásakor az eredményeknek ilyeneknek kell lenniük.

Most ürítsük ki a dbo.stageOrders táblát az usp_TEST végrehajtásával . Ezt a következő parancs hozzáadásával tehetjük meg.

A TRUNCATE TABLE parancs hozzáadása után frissítenünk kell az usp_TEST fájlt .

Ezt követően egy üzenetnek kell megjelennie , amely szerint a parancsok sikeresen befejeződtek . Ezután újra végrehajtjuk az usp_TEST parancsot .

Az usp_TEST végrehajtásakor megjelenik a Sales.SalesOrderHeader összes rekordja , és kiüríti a dbo.stageOrders táblát is. 

Annak ellenőrzéséhez, hogy a dbo.stageOrders tábla üres-e, ki kell jelölnünk a következő paranccsal, és végre kell hajtanunk.

A fenti kód futtatásakor azt látjuk, hogy a dbo.stageOrders tábla most üres. Ez a TRUNCATE TABLE parancsnak köszönhető, amelyet az usp_TEST frissítésére használtunk .

A mentett tárolt eljárások helye az SQL-ben

Ha látni szeretné, hogy a tárolt eljárás hova kerül mentésre, lépjen az Object Explorer panelre a bal oldalon, és kattintson a „ + ” ikonra az adatbázis előtt, amelyen dolgozik.

Ezután kattintson a jobb gombbal a Programozhatóság elemre , és válassza a Frissítés lehetőséget .

A „ + ” ikonra kattintva bontsa ki a Programozhatóság mappát vagy csoportot . Ezután bontsa ki a Tárolt eljárások csoportot ugyanazzal a lépéssel. A Tárolt eljárások csoportban látnia kell a dbo.usp_TEST fájlt .

Ha szeretné ellenőrizni, hogy az SQL-ben egy adott tárolt eljárás milyen parancsokat vagy lekérdezéseket hajt végre, kattintson a jobb gombbal egy tárolt eljárásra, és kövesse az alábbi képernyőképen látható lépéseket.

Ezt követően megnyitja a tárolt eljárást egy másik lapon, ahol láthatja a benne található parancsokat. Így néz ki a dbo.usp_TEST megnyitásakor.

Amint láthatja, van néhány alapértelmezett parancs a CREATE utasítás előtt. Egyszerűen törölheti, ha akarja. 

Most már tudja, hogyan ellenőrizheti, hogy az SQL-ben tárolt eljárás milyen parancsokat hajt végre.

Tárolt eljárás létrehozása SQL-ben paraméterekkel

Ezután létrehozunk egy tárolt eljárást paraméterekkel. Például a következő kódot fogjuk használni egy új tárolt eljárás létrehozásához.

A példakódban ugyanezt a folyamatot használtam az usp_GetCustomer nevű tárolt eljárás létrehozására . Ezután hozzáadtam egy @CustomerID paramétert INT bemeneti típussal .

Vegye figyelembe, hogy miután hozzáadott egy paramétert egy tárolt eljárás létrehozásához, mindig meg kell adnia egy paramétert, amikor parancsot akar végrehajtani.

Nézzük meg, mi történik, ha az usp_GetCustomer parancsot paraméter megadása nélkül hajtjuk végre.

Az usp_GetCustomer paraméter nélküli futtatásakor hibaüzenet jelent meg. Így nézne ki, ha egy paraméterrel végrehajtjuk az usp_GetCustomer parancsot .

Egy megadott paraméterrel megfelelő eredményt tudunk elérni a tárolt eljárásunk végrehajtása során .

Tárolt eljárás létrehozása alapértelmezett értékkel

Ha el szeretné kerülni, hogy hibaüzenetet kapjon egy tárolt eljárás paraméterrel végrehajtásakor, beállíthat egy alapértelmezett értéket, amely alapértelmezett paraméterként fog szolgálni.

Például létrehozunk egy usp_GetOrdersByYear nevű tárolt eljárást .

Ezután hozzáadok egy @OrderYear paramétert „ INT ” bemeneti típussal és egy alapértelmezett értékkel, amely megegyezik a 2011-gyel .

Ha az usp_GetOrdersByYear parancsot paraméter megadása nélkül hajtjuk végre , akkor a 2011-es évszámú rekordokat jeleníti meg .

Másrészt, ha az usp_GetOrdersByYear parancsot 2014 paraméterrel hajtjuk végre, akkor a 2014-es évszámmal kell rekordokat megjelenítenie .

Így hasznosítja a tárolt eljárásokat a napi adatkezelési feladatai során.

Következtetés

Mindent figyelembe véve megtanulta, mi az SQL-ben tárolt eljárás, és mi a célja. Megbeszéltük az ALTER utasítást is, amelyet a jelenlegi tárolt eljárás módosítására vagy frissítésére használnak.

Ezenkívül megtanulta, hogy különböző módszerek léteznek a tárolt eljárások létrehozására SQL-ben, és megtanulta, hogyan kerülheti el a hibákat a tárolt eljárások végrehajtásakor egy alapértelmezett érték megadásával.

A legfontosabb, hogy megtanulta használni a tárolt eljárásokat a parancskészletek tárolására, hogy elkerülje a hosszú kódkészletek ismételt futtatását. Utolsó emlékeztetőként ne felejtse el használni a " @ " szimbólumot a paraméter megadásakor.

Minden jót,

Hafiz