Opgeslagen procedures in SQL | Een overzicht

In deze blog gaan we het hebben over opgeslagen procedures in SQL die u kunt gebruiken om een ​​set code op te slaan en deze herhaaldelijk te gebruiken wanneer u deze nodig heeft. Opgeslagen procedures zijn vergelijkbaar met weergaven. U kunt echter algemene tabelbewerkingen zoals DROP, TRUNCATE, DELETE, enz. uitvoeren met opgeslagen procedures die u niet kunt uitvoeren met weergaven.

Opgeslagen procedures zijn ook vooraf gecompileerd, zodat ze sneller werken dan weergaven. Het minimaliseert ook de hoeveelheid gegevens die naar de databaseserver wordt verzonden.

Inhoudsopgave

• Syntaxis voor opgeslagen procedures in SQL: zonder parameters
• Syntaxis voor opgeslagen procedures in SQL: met parameters
• Voorbeeldscenario van opgeslagen procedures in SSMS
• Tweede voorbeeldscenario
• Derde voorbeeldscenario
• Locatie van opgeslagen opgeslagen procedures in SQL
• Een opgeslagen procedure maken in SQL met parameters
• Een opgeslagen procedure maken met een standaardwaarde
• Conclusie

Syntaxis voor opgeslagen procedures in SQL: zonder parameters

Om een ​​opgeslagen procedure zonder parameters te maken, moet u beginnen met de functie CREATE . Voeg vervolgens de PROC- functie toe, typ direct daarna de procedurenaam en voeg de AS- functie toe.

Het maken van een opgeslagen procedure in is vergelijkbaar met het maken van tabellen en weergaven. Het verschil zit hem echter in het verkrijgen van gegevens.

Als we bijvoorbeeld gegevens uit een weergave willen halen, gebruiken we " SELECT * FROM view_name ". Aan de andere kant gebruiken we voor opgeslagen procedures de EXEC, wat "uitvoeren" betekent, en typen we vervolgens de naam van de opgeslagen procedure .

Zodra we een opgeslagen procedure uitvoeren , wordt de informatie ook weergegeven op basis van de instructies of opdrachten die u hebt toegevoegd.

Syntaxis voor opgeslagen procedures in SQL: met parameters

Laten we verder gaan met . Het proces is vergelijkbaar met het vorige voorbeeld dat ik heb gedemonstreerd. Het enige verschil is dat u de parameters vóór de AS- functie moet opnemen en ook het gegevenstype erna.

U kunt ook de parameter gebruiken die u hebt gedeclareerd bij het maken van de opgeslagen procedure in uw opdrachten. Dit is voor u om tijd te besparen door meerdere waarden in te voeren. In plaats van klantID1 , klantID2 enzovoort handmatig te typen, gebruikten we bijvoorbeeld @custid of de parameter om een ​​lange set code te voorkomen.

Houd er rekening mee dat wanneer u een parameter gebruikt of opneemt, het belangrijk is om het @ -symbool te gebruiken. U kunt ook zoveel parameters opnemen als u wilt, niet slechts één. Dit is hoe we opgeslagen procedures met parameters uitvoeren.

Zoals je kunt zien, hebben we ook de parameter gebruikt met een waarde gelijk aan 1 bij het uitvoeren van de opgeslagen procedure. Dit brengt alleen de informatie van custid1 .

Voorbeeldscenario van opgeslagen procedures in SSMS

Laten we meer voorbeelden geven van opgeslagen procedures in SQL. In eerste instantie ga ik een voorbeeld van een opgeslagen procedure maken met behulp van de gemarkeerde opdracht hieronder.

Voor naamgeving bij het maken van een opgeslagen procedure gebruiken we meestal "usp" of "sp" om aan te geven dat dit een opgeslagen procedure is. Als je je afvraagt ​​wat "usp" betekent: het staat simpelweg voor door de gebruiker gedefinieerde opgeslagen procedure. 

Vervolgens gaan we query's schrijven voor de opgeslagen procedure om te verwerken wanneer deze wordt uitgevoerd.

De query's in het vorige voorbeeld verwijderen gewoon de tabel met de naam dbo.stageOrders . Vervolgens wordt dbo.stageOrders opnieuw gemaakt met de gegevens uit de tabel Sales.SalesOrderHeader . 

Laten we deze opgeslagen procedure maken door de volgende code te markeren en op de knop Uitvoeren te klikken .

Daarna zou je zo'n bericht moeten zien.

Vervolgens voeren we nu de opgeslagen procedure uit door de EXEC usp_TEST uit te voeren .

Bij de uitvoering van de opgeslagen procedure usp_TEST zou de dbo.stageOrders nu de gegevens van Sales.SalesOrderHeader moeten hebben . Laten we de inhoud van dbo.stageOrders controleren door de onderstaande opdracht uit te voeren.

Als resultaat zijn dit de gegevens die we hebben gemaakt in dbo.stageOrders op basis van de opdrachten die we hebben toegevoegd aan onze opgeslagen procedure.

Tweede voorbeeldscenario

Laten we verder gaan met een ander voorbeeld. Deze keer wil ik de tabel dbo.stageOrders niet laten vallen en dezelfde tabel opnieuw maken met gegevens uit de tabel Sales.SalesOrderHeader .

In plaats daarvan wil ik gewoon dat de gegevens uit de tabel Sales.SalesOrderHeader worden weergegeven telkens wanneer ik de opgeslagen procedure " usp_TEST " uitvoer .

Om dit uit te voeren, moet ik wijzigingen aanbrengen in de recent gemaakte opgeslagen procedure met behulp van de ALTER- instructie.

In dit voorbeeld hebben we de ALTER- instructie gebruikt die wordt gebruikt om onze opgeslagen procedure te wijzigen. U kunt dit ook toepassen op weergaven en tabellen. Zodra we de code in de bovenstaande schermafbeelding hebben uitgevoerd, zou dit de vragen moeten wijzigen die moeten worden verwerkt door onze opgeslagen procedure.

Deze keer zal het geen enkele tabel opnieuw maken zoals we eerder deden. Als we " usp_TEST " uitvoeren , worden alleen de gegevens uit de tabel Sales.SalesOrderHeader opgehaald .

Derde voorbeeldscenario

Laten we nog een voorbeeld doen. Deze keer gebruiken we de tabel dbo.stageOrders die we hadden in ons eerste voorbeeld van het maken van een opgeslagen procedure in SSMS . We beginnen met dit voorbeeld door de tabel dbo.stageOrders te selecteren. 

De resultaten bij het selecteren van alle gegevens uit dbo.stageOrders zouden als volgt moeten zijn.

Laten we nu de tabel dbo.stageOrders legen door usp_TEST uit te voeren . We kunnen dat doen door het volgende commando toe te voegen.

Na het toevoegen van de opdracht TRUNCATE TABLE moeten we usp_TEST bijwerken .

Daarna zou een bericht moeten verschijnen waarin staat dat de opdrachten met succes zijn voltooid . Daarna voeren we usp_TEST opnieuw uit.

Bij het uitvoeren van usp_TEST , worden alle records van Sales.SalesOrderHeader weergegeven en wordt ook de tabel  dbo.stageOrders leeggemaakt.

Om te controleren of de tabel dbo.stageOrders leeg is, moeten we deze selecteren met behulp van de volgende opdracht en uitvoeren.

Bij het uitvoeren van de bovenstaande code kunnen we zien dat de tabel dbo.stageOrders nu leeg is. Dit komt door de opdracht TRUNCATE TABLE die we hebben gebruikt om usp_TEST bij te werken .

Locatie van opgeslagen opgeslagen procedures in SQL

Als u wilt zien waar de opgeslagen procedure in SQL wordt opgeslagen, gaat u naar het paneel Objectverkenner aan de linkerkant en klikt u op het pictogram " + " vóór de database waaraan u werkt.

Klik vervolgens met de rechtermuisknop op Programmeerbaarheid en selecteer Vernieuwen .

Vouw de map of groep Programmability uit door op het pictogram " + " te klikken. Vouw vervolgens de groep Opgeslagen procedures uit door dezelfde stap uit te voeren. Binnen de Stored Procedures-groep zou u de dbo.usp_TEST moeten zien .

Als u wilt controleren welke opdrachten of query's een specifieke opgeslagen procedure in SQL uitvoert, kunt u met de rechtermuisknop op een opgeslagen procedure klikken en de stappen in de onderstaande schermafbeelding volgen.

Daarna wordt de opgeslagen procedure geopend op een ander tabblad waar u de opdrachten erin kunt zien. Zo ziet dbo.usp_TEST eruit bij het openen ervan.

Zoals je kunt zien, zijn er enkele standaardcommando's vóór de CREATE- instructie. Je kunt dat gewoon verwijderen als je dat wilt. 

Nu weet u hoe u kunt controleren welke opdrachten een opgeslagen procedure in SQL uitvoert.

Een opgeslagen procedure maken in SQL met parameters

Vervolgens gaan we een opgeslagen procedure met parameters maken. We gaan bijvoorbeeld de volgende code gebruiken om een ​​nieuwe opgeslagen procedure te maken.

In de voorbeeldcode heb ik hetzelfde proces gebruikt voor het maken van een opgeslagen procedure met de naam usp_GetCustomer . Vervolgens heb ik een parameter toegevoegd die @CustomerID is met een invoertype van INT .

Houd er rekening mee dat als u eenmaal een parameter hebt toegevoegd bij het maken van een opgeslagen procedure, u altijd een parameter moet opgeven wanneer u een opdracht gaat uitvoeren.

Laten we eens kijken wat er gebeurt als we usp_GetCustomer uitvoeren zonder een parameter op te geven.

Bij het uitvoeren van usp_GetCustomer zonder parameter, verscheen er een foutmelding. Dit is hoe het eruit zou zien als we usp_GetCustomer uitvoeren met een parameter.

Met een opgegeven parameter kunnen we een goed resultaat krijgen bij het uitvoeren van onze opgeslagen procedure .

Een opgeslagen procedure maken met een standaardwaarde

Als u wilt voorkomen dat u een foutmelding krijgt bij het uitvoeren van een opgeslagen procedure met een parameter, kunt u een standaardwaarde instellen die als standaardparameter zal dienen.

We gaan bijvoorbeeld een opgeslagen procedure maken met de naam usp_GetOrdersByYear .

Dan ga ik een parameter toevoegen @OrderYear met invoertype " INT " en een standaardwaarde die gelijk is aan 2011 .

Als we usp_GetOrdersByYear uitvoeren zonder dat er een parameter is opgegeven, worden records met het jaar 2011 weergegeven .

Aan de andere kant, als we usp_GetOrdersByYear uitvoeren met 2014 als een gegeven parameter, zou het records moeten weergeven met het jaar 2014 .

Zo benut u opgeslagen procedures in uw dagelijkse datamanagementtaken.

HAVING Clausule in SQL Geaggregeerde functies
ISNULL SQL-functie In Case-statements
SQL-gegevensextractie met behulp van OFFSET en FETCH

Conclusie

Alles in aanmerking genomen, heb je geleerd wat een opgeslagen procedure in SQL is en wat het doel ervan is. We hebben ook de ALTER- instructie besproken die wordt gebruikt om wijzigingen of updates aan te brengen in een huidige opgeslagen procedure.

Daarnaast hebt u geleerd dat er verschillende methoden zijn om opgeslagen procedures in SQL te maken en hebt u geleerd hoe u kunt voorkomen dat u fouten ontvangt bij het uitvoeren van een opgeslagen procedure door een standaardwaarde op te geven.

Het belangrijkste is dat u hebt geleerd om opgeslagen procedures te gebruiken voor het opslaan van reeksen opdrachten om te voorkomen dat lange reeksen code herhaaldelijk moeten worden uitgevoerd. Als laatste herinnering, vergeet niet het " @ " -symbool te gebruiken bij het opgeven van een parameter.

Al het beste,

Hafiz