Uložené procedúry v SQL | Prehľad

V tomto blogu budeme diskutovať o uložených procedúrach v SQL, ktoré môžete použiť na uloženie sady kódu a opakovane ju použiť, kedykoľvek ju budete potrebovať. Uložené procedúry sú podobné pohľadom. S uloženými procedúrami však môžete vykonávať napríklad DROP, TRUNCATE, DELETE atď., ktoré nemôžete vykonávať pomocou pohľadov.

Uložené procedúry sú tiež predkompilované, takže bežia rýchlejšie ako zobrazenia. Minimalizuje tiež množstvo údajov odosielaných na databázový server.

Obsah

• Syntax pre uložené procedúry v SQL: Bez parametrov
• Syntax pre uložené procedúry v SQL: S parametrami
• Vzorový scenár uložených procedúr v SSMS
• Druhý vzorový scenár
• Tretí vzorový scenár
• Umiestnenie uložených procedúr v SQL
• Vytvorenie uloženej procedúry v SQL s parametrami
• Vytvorenie uloženej procedúry s predvolenou hodnotou
• Záver

Syntax pre uložené procedúry v SQL: Bez parametrov

Ak chcete vytvoriť uloženú procedúru bez parametrov, musíte začať s funkciou CREATE . Potom pridajte funkciu PROC , zadajte názov procedúry hneď za ňou a pridajte funkciu AS .

Vytvorenie uloženej procedúry v je podobné vytváraniu tabuliek a zobrazení. Rozdiel je však v procese získavania údajov.

Napríklad, ak chceme získať údaje zo zobrazenia, použijeme „ SELECT * FROM view_name . Na druhej strane, pre uložené procedúry používame EXEC, čo znamená „vykonať“, a potom zadajte názov uloženej procedúry .

Keď spustíme uloženú procedúru , zobrazí sa aj informácia na základe pokynov alebo príkazov, ktoré ste pridali.

Syntax pre uložené procedúry v SQL: S parametrami

Presuňme sa na . Proces je podobný predchádzajúcemu príkladu, ktorý som demonštroval. Jediný rozdiel je, že musíte zahrnúť parametre pred funkciou AS a tiež typ údajov hneď za ňou.

Môžete tiež použiť parameter, ktorý ste deklarovali pri vytváraní uloženej procedúry vo vašich príkazoch. Je to pre vás, aby ste ušetrili čas zadávaním viacerých hodnôt. Napríklad namiesto manuálneho zadávania customerID1 , customerID2 atď. sme použili @custid alebo parameter, aby sme sa vyhli dlhému súboru kódu.

Vezmite prosím na vedomie, že pri používaní alebo zahrnutí parametra je dôležité použiť symbol @ . Môžete tiež zahrnúť toľko parametrov, koľko chcete, nielen jeden. Takto vykonávame uložené procedúry s parametrami.

Ako si môžete všimnúť, parameter s hodnotou rovnajúcou sa 1 sme použili aj pri vykonávaní uloženej procedúry. To prinesie iba informácie custid1 .

Vzorový scenár uložených procedúr v SSMS

Uveďme si viac príkladov uložených procedúr v SQL. V prvom prípade vytvorím vzorovú uloženú procedúru pomocou zvýrazneného príkazu nižšie.

Na pomenovanie konvencie pri vytváraní uloženej procedúry zvyčajne používame „usp“ alebo „sp“ na označenie, že ide o uloženú procedúru. Ak vás zaujíma, čo znamená „usp“, znamená to jednoducho uloženú procedúru definovanú používateľom. 

Ďalej budeme písať dotazy pre uloženú procedúru, ktorá sa má spracovať po jej spustení.

Dotazy v predchádzajúcom príklade jednoducho odstránia tabuľku s názvom dbo.stageOrders . Potom znova vytvorí dbo.stageOrders s údajmi z tabuľky Sales.SalesOrderHeader . 

Vytvorme túto uloženú procedúru zvýraznením nasledujúceho kódu a kliknutím na tlačidlo Vykonať .

Potom by ste mali vidieť správu ako je táto.

Potom teraz spustíme uloženú procedúru spustením EXEC usp_TEST .

Po vykonaní uloženej procedúry usp_TEST by mal mať dbo.stageOrders teraz údaje z Sales.SalesOrderHeader . Skontrolujeme obsah dbo.stageOrders spustením príkazu nižšie.

Výsledkom sú údaje, ktoré sme vytvorili v dbo.stageOrders na základe príkazov, ktoré sme pridali do našej uloženej procedúry.

Druhý vzorový scenár

Prejdime na ďalší príklad. Tentoraz nechcem zrušiť tabuľku dbo.stageOrders a znova vytvoriť rovnakú tabuľku s údajmi pochádzajúcimi z tabuľky Sales.SalesOrderHeader .

Namiesto toho chcem, aby sa údaje z tabuľky Sales.SalesOrderHeader zobrazili vždy, keď spustím uloženú procedúru „ usp_TEST “ .

Aby som to mohol vykonať, musím vykonať zmeny v nedávno vytvorenej uloženej procedúre pomocou príkazu ALTER .

V tomto príklade sme použili príkaz ALTER , ktorý sa používa na úpravu našej uloženej procedúry. Môžete to použiť aj na zobrazenia a tabuľky. Po spustení kódu na snímke obrazovky vyššie by sa mali zmeniť dotazy, ktoré sa majú spracovať našou uloženou procedúrou.

Tentoraz nevytvorí žiadny stôl ako predtým. Ak vykonáme „ usp_TEST “, prinesie len údaje z tabuľky Sales.SalesOrderHeader .

Tretí vzorový scenár

Urobme ďalší príklad. Tentoraz znovu použijeme tabuľku dbo.stageOrders, ktorú sme mali v našom prvom príklade vytvorenia uloženej procedúry v SSMS . Začneme týmto príkladom výberom tabuľky dbo.stageOrders. 

Výsledky po výbere všetkých údajov z dbo.stageOrders by mali byť takéto.

Teraz vyprázdnime tabuľku dbo.stageOrders vykonaním usp_TEST . Môžeme to urobiť pridaním nasledujúceho príkazu.

Po pridaní príkazu TRUNCATE TABLE musíme aktualizovať usp_TEST .

Potom by sa malo zobraziť hlásenie Príkazy sú úspešne dokončené . Potom znova vykonáme usp_TEST .

Po vykonaní usp_TEST zobrazí všetky záznamy z Sales.SalesOrderHeader a tiež vyprázdni tabuľku dbo.stageOrders . 

Ak chcete skontrolovať, či je tabuľka dbo.stageOrders prázdna, musíme ju vybrať pomocou nasledujúceho príkazu a spustiť.

Po spustení vyššie uvedeného kódu vidíme, že tabuľka dbo.stageOrders je teraz prázdna. Je to spôsobené príkazom TRUNCATE TABLE , ktorý sme použili na aktualizáciu usp_TEST .

Umiestnenie uložených procedúr v SQL

Ak chcete vidieť, kde sa uložená procedúra ukladá, prejdite na panel Prieskumník objektov na ľavej strane a kliknite na ikonu „ + “ pred databázou, na ktorej pracujete.

Potom kliknite pravým tlačidlom myši na Programovateľnosť a vyberte možnosť Obnoviť .

Rozbaľte priečinok alebo skupinu Programmability kliknutím na ikonu „ + “. Potom rozbaľte skupinu Uložené procedúry vykonaním rovnakého kroku. V skupine Uložené procedúry by ste mali vidieť súbor dbo.usp_TEST .

Ak chcete skontrolovať, aké príkazy alebo dotazy vykonáva konkrétna uložená procedúra v SQL, môžete kliknúť pravým tlačidlom myši na uloženú procedúru a postupovať podľa krokov na obrázku nižšie.

Potom sa uložená procedúra otvorí na inej karte, kde môžete vidieť príkazy, ktoré sú v nej uvedené. Takto vyzerá dbo.usp_TEST po jeho otvorení.

Ako vidíte, pred príkazom CREATE je niekoľko predvolených príkazov . Ak chcete, môžete to jednoducho odstrániť. 

Teraz viete, ako skontrolovať, aké príkazy vykonáva uložená procedúra v SQL .

Vytvorenie uloženej procedúry v SQL s parametrami

Ďalej vytvoríme uloženú procedúru s parametrami. Napríklad na vytvorenie novej uloženej procedúry použijeme nasledujúci kód.

V príklade kódu som použil rovnaký proces vytvárania uloženej procedúry s názvom usp_GetCustomer . Potom som pridal parameter, ktorý je @CustomerID s typom vstupu INT .

Berte na vedomie, že po pridaní parametra pri vytváraní uloženej procedúry by ste mali vždy zadať parameter vždy, keď sa chystáte vykonať príkaz.

Pozrime sa, čo sa stane, ak vykonáme usp_GetCustomer bez zadania parametra.

Po vykonaní usp_GetCustomer bez parametra sa objavilo chybové hlásenie. Takto by to vyzeralo, keby sme vykonali usp_GetCustomer s parametrom.

S poskytnutým parametrom sme schopní získať správny výsledok pri vykonávaní našej uloženej procedúry .

Vytvorenie uloženej procedúry s predvolenou hodnotou

Ak sa chcete vyhnúť chybovému hláseniu pri vykonávaní uloženej procedúry s parametrom, môžete nastaviť predvolenú hodnotu, ktorá bude slúžiť ako predvolený parameter.

Napríklad vytvoríme uloženú procedúru s názvom usp_GetOrdersByYear .

Potom pridám parameter @OrderYear s typom vstupu „ INT “ a predvolenou hodnotou, ktorá sa rovná roku 2011 .

Ak vykonáme usp_GetOrdersByYear bez zadaného parametra, zobrazia sa záznamy s rokom 2011 .

Na druhej strane, ak vykonáme usp_GetOrdersByYear s rokom 2014 ako daný parameter, mal by zobraziť záznamy s rokom 2014 .

Takto využívate uložené procedúry vo svojich každodenných úlohách správy údajov.

Záver

Keď vezmete do úvahy všetko, naučili ste sa, čo je uložená procedúra v SQL a jej účel. Diskutovali sme aj o príkaze ALTER , ktorý sa používa na vykonanie zmien alebo aktualizácií v súčasnej uloženej procedúre.

Okrem toho ste sa dozvedeli, že existujú rôzne metódy na vytváranie uložených procedúr v SQL a naučili ste sa, ako sa vyhnúť chybám pri vykonávaní uloženej procedúry poskytnutím predvolenej hodnoty.

Najdôležitejšie je, že ste sa naučili používať uložené procedúry na ukladanie sád príkazov, aby ste sa vyhli opakovanému spúšťaniu dlhých sád kódu. Ako poslednú pripomienku nezabudnite pri zadávaní parametra použiť symbol „ @ “.

Všetko najlepšie,

Hafiz