Lagrede procedurer i SQL | Et overblik

I denne blog vil vi diskutere lagrede procedurer i SQL, som du kan bruge til at gemme et sæt kode og bruge det gentagne gange, når du har brug for det. Lagrede procedurer ligner visninger. Du kan dog udføre som DROP, TRUNCATE, DELETE osv. med lagrede procedurer, som du ikke kan udføre med visninger.

Lagrede procedurer er også præ-kompileret, så det kører hurtigere end visninger. Det minimerer også mængden af ​​data, der sendes til databaseserveren.

Indholdsfortegnelse

• Syntaks for lagrede procedurer i SQL: Uden parametre
• Syntaks for lagrede procedurer i SQL: Med parametre
• Eksempelscenarie af lagrede procedurer i SSMS
• Andet eksempelscenarie
• Tredje eksempelscenarie
• Placering af gemte lagrede procedurer i SQL
• Oprettelse af en lagret procedure i SQL med parametre
• Oprettelse af en lagret procedure med en standardværdi
• Konklusion

Syntaks for lagrede procedurer i SQL: Uden parametre

For at oprette en lagret procedure uden parametre, skal du starte med CREATE- funktionen. Tilføj derefter PROC- funktionen, skriv procedurenavnet lige efter, og tilføj AS -funktionen.

Oprettelse af en lagret procedure i svarer til at oprette tabeller og visninger. Forskellen er dog i processen med at få data.

For eksempel, hvis vi ønsker at hente data fra en visning, bruger vi " SELECT * FROM view_name ." På den anden side, til lagrede procedurer, bruger vi EXEC, som betyder "udfør", og skriv derefter navnet på den lagrede procedure efter.

Når vi udfører en lagret procedure , vil den også hente oplysningerne frem baseret på de instruktioner eller kommandoer, du har tilføjet.

Syntaks for lagrede procedurer i SQL: Med parametre

Lad os flytte til . Processen ligner det tidligere eksempel, som jeg demonstrerede. Den eneste forskel er, at du skal inkludere parametrene før AS- funktionen og også datatypen lige efter.

Du kan også bruge den parameter du har erklæret i oprettelsen af ​​den lagrede procedure i dine kommandoer. Dette er for at du kan spare tid ved at indtaste flere værdier. For eksempel brugte vi @custid eller parameteren i stedet for manuelt at skrive customerID1 , customerID2 , og så videre for at undgå at have et langt sæt kode.

Bemærk venligst, at når du bruger eller inkluderer en parameter, er det vigtigt at bruge @ -symbolet. Du kan også inkludere så mange parametre, du vil, ikke kun én. Sådan udfører vi lagrede procedurer med parametre.

Som du kan bemærke, brugte vi også parameteren med en værdi lig med 1 til at udføre den lagrede procedure. Dette vil kun bringe oplysningerne fra custid1 .

Eksempelscenarie af lagrede procedurer i SSMS

Lad os få flere eksempler på lagrede procedurer i SQL. I første omgang vil jeg oprette et eksempel på en lagret procedure ved at bruge den fremhævede kommando nedenfor.

Til navngivningskonvention ved oprettelse af en lagret procedure, bruger vi normalt "usp" eller "sp" for at angive, at dette er en lagret procedure. Hvis du undrer dig over, hvad "usp" betyder, står det blot for brugerdefineret lagret procedure. 

Dernæst skal vi skrive forespørgsler til den lagrede procedure, der skal behandles, når den udføres.

Forespørgslerne i det foregående eksempel vil simpelthen fjerne tabellen med navnet dbo.stageOrders . Derefter vil den genskabe dbo.stageOrders med dataene fra tabellen Sales.SalesOrderHeader . 

Lad os oprette denne lagrede procedure ved at fremhæve følgende kode og klikke på knappen Udfør .

Derefter skulle du se en besked som denne.

Så vil vi nu udføre den lagrede procedure ved at køre EXEC usp_TEST .

Efter udførelse af den lagrede procedure usp_TEST skulle dbo.stageOrders nu have dataene fra Sales.SalesOrderHeader . Lad os tjekke indholdet af dbo.stageOrders ved at køre kommandoen nedenfor.

Som følge heraf er disse data, som vi oprettede i dbo.stageOrders baseret på de kommandoer, vi har tilføjet til vores lagrede procedure.

Andet eksempelscenarie

Lad os gå videre til et andet eksempel. Denne gang ønsker jeg ikke at droppe tabellen dbo.stageOrders og genskabe den samme tabel med data, der kommer fra tabellen Sales.SalesOrderHeader .

I stedet vil jeg bare have, at dataene fra Sales.SalesOrderHeader-tabellen skal vises, hver gang jeg udfører den lagrede procedure " usp_TEST " .

For at udføre dette skal jeg foretage ændringer i den nyligt oprettede lagrede procedure ved at bruge ALTER -sætningen.

I dette eksempel brugte vi ALTER- sætningen, som bruges til at ændre vores lagrede procedure. Du kan også anvende dette på visninger og tabeller. Når vi har kørt koden i skærmbilledet ovenfor, skulle den ændre de forespørgsler, der skal behandles af vores lagrede procedure.

Denne gang vil den ikke genskabe nogen tabel, som vi gjorde tidligere. Hvis vi udfører " usp_TEST ," vil det blot bringe dataene fra tabellen Sales.SalesOrderHeader .

Tredje eksempelscenarie

Lad os tage et andet eksempel. Denne gang genbruger vi tabellen dbo.stageOrders, som vi havde i vores første eksempel på oprettelse af en lagret procedure i SSMS . Vi starter med dette eksempel ved at vælge tabellen dbo.stageOrders. 

Resultaterne ved valg af alle data fra dbo.stageOrders skulle være sådan her.

Lad os nu tømme tabellen dbo.stageOrders ved at udføre usp_TEST . Det kan vi gøre ved at tilføje følgende kommando.

Efter at have tilføjet kommandoen TRUNCATE TABLE , skal vi opdatere usp_TEST .

Derefter skal der vises en meddelelse, der siger , at kommandoer er gennemført med succes . Så udfører vi usp_TEST igen.

Ved udførelse af usp_TEST vil den vise alle posterne fra Sales.SalesOrderHeader og også tømme tabellen dbo.stageOrders . 

For at kontrollere, om tabellen dbo.stageOrders er tom, skal vi vælge den ved at bruge følgende kommando og udføre.

Når vi kører koden ovenfor, kan vi se, at dbo.stageOrders-tabellen nu er tom. Dette skyldes kommandoen TRUNCATE TABLE , som vi har brugt til at opdatere usp_TEST .

Placering af gemte lagrede procedurer i SQL

Hvis du vil se, hvor den lagrede procedure bliver gemt, skal du gå til Objekt Explorer- panelet i venstre side og klikke på " + "-ikonet før den database, du arbejder på.

Højreklik derefter på Programmerbarhed og vælg Opdater .

Udvid mappen eller gruppen Programmerbarhed ved at klikke på " + "-ikonet. Udvid derefter gruppen Stored Procedures ved at udføre det samme trin. Inde i gruppen Stored Procedures bør du se dbo.usp_TEST .

Hvis du vil kontrollere, hvilke kommandoer eller forespørgsler en specifik lagret procedure i SQL udfører, kan du højreklikke på en lagret procedure og følge trinene i skærmbilledet nedenfor.

Derefter åbner den den lagrede procedure i en anden fane, hvor du kan se kommandoerne i den. Sådan ser dbo.usp_TEST ud, når den åbnes.

Som du kan se, er der nogle standardkommandoer før CREATE -sætningen. Du kan simpelthen slette det, hvis du vil. 

Nu ved du, hvordan du kontrollerer, hvilke kommandoer en lagret procedure i SQL udfører.

Oprettelse af en lagret procedure i SQL med parametre

Dernæst skal vi oprette en lagret procedure med parametre. For eksempel vil vi bruge følgende kode til at oprette en ny lagret procedure.

I eksempelkoden brugte jeg den samme proces til at oprette en lagret procedure ved navn usp_GetCustomer . Derefter tilføjede jeg en parameter, som er @CustomerID med inputtypen INT .

Bemærk, at når du først har tilføjet en parameter i oprettelsen af ​​en lagret procedure, skal du altid angive en parameter, når du skal udføre en kommando.

Lad os se, hvad der vil ske, hvis vi udfører usp_GetCustomer uden at angive en parameter.

Ved udførelse af usp_GetCustomer uden en parameter, kom der en fejlmeddelelse. Sådan ville det se ud, hvis vi udfører usp_GetCustomer med en parameter.

Med en parameter angivet, er vi i stand til at få et korrekt resultat i at udføre vores lagrede procedure .

Oprettelse af en lagret procedure med en standardværdi

Hvis du vil undgå at modtage en fejlmeddelelse ved udførelse af en lagret procedure med en parameter, kan du indstille en standardværdi, der vil fungere som en standardparameter.

For eksempel vil vi oprette en lagret procedure ved navn usp_GetOrdersByYear .

Så vil jeg tilføje en parameter @OrderYear med inputtypen " INT " og en standardværdi, der er lig med 2011 .

Hvis vi udfører usp_GetOrdersByYear uden en parameter angivet, vil den vise poster med år 2011 .

På den anden side, hvis vi udfører usp_GetOrdersByYear med 2014 som en given parameter, skulle den vise poster med år 2014 .

Sådan udnytter du lagrede procedurer i dine daglige datahåndteringsopgaver.

Konklusion

Tager du alt i betragtning, har du lært, hvad en lagret procedure i SQL er og dens formål. Vi har også diskuteret ALTER- sætningen, der bruges til at foretage ændringer eller opdateringer i en nuværende lagret procedure.

Derudover har du lært, at der er forskellige metoder til at oprette lagrede procedurer i SQL og lært, hvordan du undgår at modtage fejl, når du udfører en lagret procedure ved at angive en standardværdi.

Vigtigst er det, at du har lært at bruge lagrede procedurer til at gemme sæt af kommandoer for at undgå at køre lange sæt kode gentagne gange. Som en sidste påmindelse, glem ikke at bruge " @ " symbolet, når du angiver en parameter.

Alt det bedste,

Hafiz