Lagrede prosedyrer i SQL | Et overblikk

I denne bloggen skal vi diskutere lagrede prosedyrer i SQL som du kan bruke til å lagre et sett med kode og bruke det gjentatte ganger når du trenger det. Lagrede prosedyrer ligner på visninger. Imidlertid kan du utføre som DROP, TRUNCATE, DELETE, etc. med lagrede prosedyrer som du ikke kan utføre med visninger.

Lagrede prosedyrer er også forhåndskompilert slik at de kjører raskere enn visninger. Det minimerer også mengden data som sendes til databaseserveren.

Innholdsfortegnelse

• Syntaks for lagrede prosedyrer i SQL: uten parametere
• Syntaks for lagrede prosedyrer i SQL: Med parametere
• Eksempelscenario av lagrede prosedyrer i SSMS
• Andre eksempelscenario
• Tredje eksempelscenario
• Plassering av lagrede prosedyrer i SQL
• Opprette en lagret prosedyre i SQL med parametere
• Opprette en lagret prosedyre med en standardverdi
• Konklusjon

Syntaks for lagrede prosedyrer i SQL: uten parametere

For å lage en lagret prosedyre uten parametere, må du starte med CREATE -funksjonen. Legg deretter til PROC- funksjonen, skriv inn prosedyrenavnet rett etter, og legg til AS- funksjonen.

Å lage en lagret prosedyre i ligner på å lage tabeller og visninger. Forskjellen er imidlertid i prosessen med å få data.

For eksempel, hvis vi ønsker å hente data fra en visning, bruker vi " SELECT * FROM view_name ." På den annen side, for lagrede prosedyrer, bruker vi EXEC som betyr "utfør", og skriver deretter inn navnet på den lagrede prosedyren etter.

Når vi utfører en lagret prosedyre , vil den også hente informasjonen basert på instruksjonene eller kommandoene du har lagt til.

Syntaks for lagrede prosedyrer i SQL: Med parametere

La oss gå til . Prosessen ligner på det forrige eksemplet som jeg demonstrerte. Den eneste forskjellen er at du må inkludere parametrene før AS- funksjonen og også datatypen rett etter.

Du kan også bruke parameteren du har deklarert i opprettelsen av den lagrede prosedyren i kommandoene dine. Dette er for at du skal spare tid ved å legge inn flere verdier. For eksempel, i stedet for manuelt å skrive kundeID1 , kundeID2 og så videre, brukte vi @custid eller parameteren for å unngå å ha et langt sett med kode.

Vær oppmerksom på at når du bruker eller inkluderer en parameter, er det viktig å bruke @ -symbolet. Du kan også inkludere så mange parametere du vil, ikke bare én. Dette er hvordan vi utfører lagrede prosedyrer med parametere.

Som du kan legge merke til, brukte vi også parameteren med en verdi lik 1 for å utføre den lagrede prosedyren. Dette vil bare bringe informasjonen til custid1 .

Eksempelscenario av lagrede prosedyrer i SSMS

La oss ha flere eksempler på lagrede prosedyrer i SQL. I første omgang skal jeg lage et eksempel på en lagret prosedyre ved å bruke den uthevede kommandoen nedenfor.

For navnekonvensjon ved å lage en lagret prosedyre bruker vi vanligvis "usp" eller "sp" for å indikere at dette er en lagret prosedyre. Hvis du lurer på hva "usp" betyr, står det ganske enkelt for brukerdefinert lagret prosedyre. 

Deretter skal vi skrive spørringer for den lagrede prosedyren som skal behandles når den er utført.

Spørringene i forrige eksempel vil ganske enkelt fjerne tabellen med navnet dbo.stageOrders . Deretter vil den gjenskape dbo.stageOrders med dataene fra Sales.SalesOrderHeader -tabellen. 

La oss lage denne lagrede prosedyren ved å markere følgende kode og klikke på Utfør- knappen.

Etter det skal du se en melding som denne.

Deretter vil vi nå utføre den lagrede prosedyren ved å kjøre EXEC usp_TEST .

Etter utførelse av den lagrede prosedyren usp_TEST , skal dbo.stageOrders nå ha dataene fra Sales.SalesOrderHeader . La oss sjekke innholdet i dbo.stageOrders ved å kjøre kommandoen nedenfor.

Som et resultat er dette dataene vi opprettet i dbo.stageOrders basert på kommandoene vi har lagt til i vår lagrede prosedyre.

Andre eksempelscenario

La oss gå videre til et annet eksempel. Denne gangen vil jeg ikke droppe tabellen dbo.stageOrders og gjenskape den samme tabellen med data som kommer fra Sales.SalesOrderHeader- tabellen.

I stedet vil jeg bare at dataene fra Sales.SalesOrderHeader-tabellen skal vises hver gang jeg utfører den lagrede prosedyren " usp_TEST " .

For å utføre dette, må jeg gjøre endringer i den nylig opprettede lagrede prosedyren ved å bruke ALTER- setningen.

I dette eksemplet brukte vi ALTER -setningen som brukes til å endre vår lagrede prosedyre. Du kan også bruke dette på visninger og tabeller. Når vi kjører koden i skjermbildet ovenfor, bør den endre spørringene som skal behandles av vår lagrede prosedyre.

Denne gangen vil den ikke gjenskape noen tabell som vi gjorde tidligere. Hvis vi kjører " usp_TEST ," vil det bare hente dataene fra Sales.SalesOrderHeader- tabellen.

Tredje eksempelscenario

La oss ta et annet eksempel. Denne gangen vil vi gjenbruke tabellen dbo.stageOrders som vi hadde i vårt første eksempel på å lage en lagret prosedyre i SSMS . Vi starter med dette eksemplet ved å velge tabellen dbo.stageOrders. 

Resultatene ved valg av alle data fra dbo.stageOrders skal være slik.

La oss nå tømme tabellen dbo.stageOrders ved å kjøre usp_TEST . Vi kan gjøre det ved å legge til følgende kommando.

Etter å ha lagt til TRUNCATE TABLE- kommandoen, må vi oppdatere usp_TEST .

Etter det skal en melding som sier at kommandoer er fullført, vises. Så kjører vi usp_TEST igjen.

Ved utføring av usp_TEST vil den vise alle postene fra Sales.SalesOrderHeader og også tømme tabellen dbo.stageOrders . 

For å sjekke om tabellen dbo.stageOrders er tom, må vi velge den ved å bruke følgende kommando og kjøre.

Når vi kjører koden ovenfor, kan vi se at dbo.stageOrders-tabellen nå er tom. Dette skyldes TRUNCATE TABLE- kommandoen som vi har brukt til å oppdatere usp_TEST .

Plassering av lagrede prosedyrer i SQL

Hvis du vil se hvor den lagrede prosedyren lagres, gå til Objektutforsker- panelet på venstre side og klikk på " + "-ikonet før databasen du jobber med.

Høyreklikk deretter på Programmerbarhet og velg Oppdater .

Utvid Programmerbarhet- mappen eller -gruppen ved å klikke på " + "-ikonet. Utvid deretter gruppen Lagrede prosedyrer ved å gjøre det samme trinnet. Inne i Lagrede prosedyrer-gruppen bør du se dbo.usp_TEST .

Hvis du vil sjekke hvilke kommandoer eller spørringer en spesifikk lagret prosedyre i SQL utfører, kan du høyreklikke på en lagret prosedyre og følge trinnene i skjermbildet nedenfor.

Etter det vil den åpne den lagrede prosedyren i en annen fane der du kan se kommandoene som er gitt i den. Slik ser dbo.usp_TEST ut når du åpner den.

Som du kan se, er det noen standardkommandoer før CREATE -setningen. Du kan ganske enkelt slette det hvis du vil. 

Nå vet du hvordan du sjekker hvilke kommandoer en lagret prosedyre i SQL utfører.

Opprette en lagret prosedyre i SQL med parametere

Deretter skal vi lage en lagret prosedyre med parametere. For eksempel skal vi bruke følgende kode for å lage en ny lagret prosedyre.

I eksempelkoden brukte jeg den samme prosessen med å lage en lagret prosedyre kalt usp_GetCustomer . Så la jeg til en parameter som er @CustomerID med en inngangstype INT .

Vær oppmerksom på at når du har lagt til en parameter i å lage en lagret prosedyre, bør du alltid oppgi en parameter når du skal utføre en kommando.

La oss se hva som vil skje hvis vi kjører usp_GetCustomer uten å oppgi en parameter.

Ved kjøring av usp_GetCustomer uten en parameter, kom det opp en feilmelding. Slik ville det sett ut hvis vi kjører usp_GetCustomer med en parameter.

Med en parameter oppgitt, er vi i stand til å få et riktig resultat når vi utfører vår lagrede prosedyre .

Opprette en lagret prosedyre med en standardverdi

Hvis du vil unngå å motta en feilmelding når du utfører en lagret prosedyre med en parameter, kan du angi en standardverdi som vil fungere som en standardparameter.

For eksempel skal vi lage en lagret prosedyre kalt usp_GetOrdersByYear .

Deretter skal jeg legge til en parameter @OrderYear med inndatatypen “ INT ” og en standardverdi som er lik 2011 .

Hvis vi kjører usp_GetOrdersByYear uten en parameter oppgitt, vil den vise poster med år 2011 .

På den annen side, hvis vi kjører usp_GetOrdersByYear med 2014 som en gitt parameter, skal den vise poster med år 2014 .

Slik bruker du lagrede prosedyrer i dine daglige databehandlingsoppgaver.

Konklusjon

Når du tar alt i betraktning, har du lært hva en lagret prosedyre i SQL er og dens formål. Vi har også diskutert ALTER -setningen som brukes til å gjøre endringer eller oppdateringer i en nåværende lagret prosedyre.

I tillegg har du lært at det er forskjellige metoder for å lage lagrede prosedyrer i SQL og lært hvordan du unngår å motta feil når du utfører en lagret prosedyre ved å angi en standardverdi.

Det viktigste er at du har lært å bruke lagrede prosedyrer for å lagre sett med kommandoer for å unngå å kjøre lange sett med kode gjentatte ganger. Som en siste påminnelse, ikke glem å bruke " @ "-symbolet når du oppgir en parameter.

Beste ønsker,

Hafiz