Lagrade procedurer i SQL | En översikt

I den här bloggen kommer vi att diskutera lagrade procedurer i SQL som du kan använda för att spara en uppsättning kod och använda den upprepade gånger när du behöver den. Lagrade procedurer liknar vyer. Däremot kan du utföra såsom DROP, TRUNCATE, DELETE, etc. med lagrade procedurer som du inte kan utföra med vyer.

Lagrade procedurer är också förkompilerade så att de går snabbare än vyer. Det minimerar också mängden data som skickas till databasservern.

Innehållsförteckning

• Syntax för lagrade procedurer i SQL: Utan parametrar
• Syntax för lagrade procedurer i SQL: med parametrar
• Exempelscenario av lagrade procedurer i SSMS
• Andra exempelscenariot
• Tredje exempelscenariot
• Plats för sparade lagrade procedurer i SQL
• Skapa en lagrad procedur i SQL med parametrar
• Skapa en lagrad procedur med ett standardvärde
• Slutsats

Syntax för lagrade procedurer i SQL: Utan parametrar

För att skapa en lagrad procedur utan parametrar måste du börja med CREATE -funktionen. Lägg sedan till PROC- funktionen, skriv procedurens namn direkt efter och lägg till AS -funktionen.

Att skapa en lagrad procedur i liknar att skapa tabeller och vyer. Skillnaden är dock i processen att få data.

Om vi ​​till exempel vill hämta data från en vy använder vi " SELECT * FROM view_name ." Å andra sidan, för lagrade procedurer, använder vi EXEC som betyder "exekvera" och skriv sedan in den lagrade procedurens namn efter.

När vi kör en lagrad procedur kommer den också att ta fram informationen baserat på instruktionerna eller kommandona som du har lagt till.

Syntax för lagrade procedurer i SQL: med parametrar

Låt oss gå till . Processen liknar det tidigare exemplet som jag visade. Den enda skillnaden är att du måste inkludera parametrarna före AS- funktionen och även datatypen direkt efter.

Du kan också använda parametern du har deklarerat i skapandet av den lagrade proceduren i dina kommandon. Detta är för att du ska spara tid genom att ange flera värden. Till exempel, istället för att manuellt skriva customerID1 , customerID2 , och så vidare, använde vi @custid eller parametern för att undvika att ha en lång uppsättning kod.

Observera att när du använder eller inkluderar en parameter är det viktigt att använda @ -symbolen. Du kan också inkludera så många parametrar du vill, inte bara en. Så här utför vi lagrade procedurer med parametrar.

Som du märker använde vi också parametern med ett värde lika med 1 vid exekvering av den lagrade proceduren. Detta kommer endast att ge information om custid1 .

Exempelscenario av lagrade procedurer i SSMS

Låt oss ha fler exempel på lagrade procedurer i SQL. I första hand kommer jag att skapa ett exempel på en lagrad procedur genom att använda det markerade kommandot nedan.

För namnkonvention när vi skapar en lagrad procedur använder vi vanligtvis "usp" eller "sp" för att indikera att detta är en lagrad procedur. Om du undrar vad "usp" betyder, står det helt enkelt för användardefinierad lagrad procedur. 

Därefter kommer vi att skriva frågor för den lagrade proceduren som ska bearbetas när den exekveras.

Frågorna i föregående exempel tar helt enkelt bort tabellen med namnet dbo.stageOrders . Sedan kommer den att återskapa dbo.stageOrders med data från tabellen  Sales.SalesOrderHeader .

Låt oss skapa denna lagrade procedur genom att markera följande kod och klicka på knappen Execute .

Efter det bör du se ett meddelande som detta.

Sedan kommer vi nu att köra den lagrade proceduren genom att köra EXEC usp_TEST .

Efter exekvering av den lagrade proceduren usp_TEST bör dbo.stageOrders nu ha data från Sales.SalesOrderHeader . Låt oss kontrollera innehållet i dbo.stageOrders genom att köra kommandot nedan.

Som ett resultat är dessa data som vi skapade i dbo.stageOrders baserat på de kommandon vi har lagt till i vår lagrade procedur.

Andra exempelscenariot

Låt oss gå vidare till ett annat exempel. Den här gången vill jag inte släppa tabellen dbo.stageOrders och återskapa samma tabell med data som kommer från tabellen Sales.SalesOrderHeader .

Istället vill jag bara att data från Sales.SalesOrderHeader-tabellen ska visas när jag kör den lagrade proceduren " usp_TEST " .

För att utföra detta måste jag göra ändringar i den nyligen skapade lagrade proceduren genom att använda ALTER -satsen.

I det här exemplet använde vi ALTER -satsen som används för att ändra vår lagrade procedur. Du kan även tillämpa detta på vyer och tabeller. När vi kört koden i skärmdumpen ovan bör den ändra frågorna som ska behandlas av vår lagrade procedur.

Den här gången kommer det inte att återskapa någon tabell som vi gjorde tidigare. Om vi ​​kör " usp_TEST ," kommer det bara att hämta data från tabellen Sales.SalesOrderHeader .

Tredje exempelscenariot

Låt oss ta ett annat exempel. Den här gången kommer vi att återanvända tabellen dbo.stageOrders som vi hade i vårt första exempel på att skapa en lagrad procedur i SSMS . Vi börjar med det här exemplet genom att välja tabellen dbo.stageOrders. 

Resultaten vid val av all data från dbo.stageOrders bör vara så här.

Låt oss nu tömma tabellen dbo.stageOrders genom att köra usp_TEST . Vi kan göra det genom att lägga till följande kommando.

Efter att ha lagt till kommandot TRUNCATE TABLE måste vi uppdatera usp_TEST .

Efter det ska ett meddelande som säger att kommandon har slutförts visas. Sedan kör vi usp_TEST igen.

När usp_TEST körs kommer den att visa alla poster från Sales.SalesOrderHeader och även tömma tabellen  dbo.stageOrders .

För att kontrollera om tabellen dbo.stageOrders är tom måste vi välja den genom att använda följande kommando och köra.

När vi kör koden ovan kan vi se att tabellen dbo.stageOrders nu är tom. Detta beror på kommandot TRUNCATE TABLE som vi har använt för att uppdatera usp_TEST .

Plats för sparade lagrade procedurer i SQL

Om du vill se var den lagrade proceduren sparas, gå till panelen Objektutforskare på vänster sida och klicka på " + "-ikonen före databasen som du arbetar med.

Högerklicka sedan på Programmerbarhet och välj Uppdatera .

Expandera mappen eller gruppen Programmerbarhet genom att klicka på ikonen " + ". Expandera sedan gruppen lagrade procedurer genom att göra samma steg. Inuti gruppen Stored Procedures bör du se dbo.usp_TEST .

Om du vill kontrollera vilka kommandon eller frågor en specifik lagrad procedur i SQL utför kan du högerklicka på en lagrad procedur och följa stegen i skärmdumpen nedan.

Efter det kommer den att öppna den lagrade proceduren på en annan flik där du kan se kommandona som finns i den. Så här ser dbo.usp_TEST ut när den öppnas.

Som du kan se finns det några standardkommandon före CREATE -satsen. Du kan helt enkelt ta bort det om du vill. 

Nu vet du hur du kontrollerar vilka kommandon en lagrad procedur i SQL utför.

Skapa en lagrad procedur i SQL med parametrar

Därefter ska vi skapa en lagrad procedur med parametrar. Till exempel kommer vi att använda följande kod för att skapa en ny lagrad procedur.

I exempelkoden använde jag samma process för att skapa en lagrad procedur som heter usp_GetCustomer . Sedan lade jag till en parameter som är @CustomerID med en ingångstyp av INT .

Observera att när du väl har lagt till en parameter för att skapa en lagrad procedur bör du alltid ange en parameter när du ska köra ett kommando.

Låt oss se vad som händer om vi kör usp_GetCustomer utan att ange en parameter.

Vid exekvering av usp_GetCustomer utan en parameter kom ett felmeddelande. Så här skulle det se ut om vi kör usp_GetCustomer med en parameter.

Med en parameter tillhandahållen kan vi få ett korrekt resultat när vi kör vår lagrade procedur .

Skapa en lagrad procedur med ett standardvärde

Om du vill undvika att få ett felmeddelande när du kör en lagrad procedur med en parameter, kan du ställa in ett standardvärde som kommer att fungera som en standardparameter.

Till exempel kommer vi att skapa en lagrad procedur som heter usp_GetOrdersByYear .

Sedan ska jag lägga till en parameter @OrderYear med ingångstypen " INT " och ett standardvärde som är lika med 2011 .

Om vi ​​kör usp_GetOrdersByYear utan en parameter angiven kommer den att visa poster med år 2011 .

Å andra sidan, om vi kör usp_GetOrdersByYear med 2014 som en given parameter, bör den visa poster med år 2014 .

Så här använder du lagrade procedurer i dina dagliga datahanteringsuppgifter.

Slutsats

Med allt i beräkningen har du lärt dig vad en lagrad procedur i SQL är och dess syfte. Vi har också diskuterat ALTER -satsen som används för att göra ändringar eller uppdateringar i en nuvarande lagrad procedur.

Dessutom har du lärt dig att det finns olika metoder för att skapa lagrade procedurer i SQL och lärt dig hur du undviker att ta emot fel när du kör en lagrad procedur genom att ange ett standardvärde.

Viktigast av allt, du har lärt dig att använda lagrade procedurer för att lagra uppsättningar av kommandon för att undvika att köra långa uppsättningar kod upprepade gånger. Som en sista påminnelse, glöm inte att använda " @ "-symbolen när du anger en parameter.

Med vänliga hälsningar,

Hafiz