Opprette lagrede prosedyrer
Lagrede prosedyrer er et av de kraftigste verktøyene i SQL Server for å kapsle inn forretningslogikk og forbedre applikasjonsytelsen. Når du lager lagrede prosedyrer, bygger du gjenbrukbare kodeblokker som kjører på serveren, noe som reduserer nettverkstrafikken og sentraliserer datatilgangslogikken.
Forstå lagrede prosedyrer
En lagret prosedyre er en kompilert samling av T-SQL-setninger som SQL Server lagrer og kjører som én enhet. I motsetning til uplanlagte spørringer som du sender til serveren hver gang, er lagrede prosedyrer forhåndskompilert og optimalisert, noe som betyr at de kjører raskere ved påfølgende kjøringer.
Du bruker lagrede prosedyrer for å kapsle inn kompleks forretningslogikk, håndheve datavalideringsregler og kontrollere hvordan applikasjoner samhandler med databasen din. For eksempel, i stedet for å tillate direkte tabelltilgang, kan du lage lagrede prosedyrer som validerer input, anvender forretningsregler og logger endringer før data endres.
Ytelsesfordelene kommer fra caching av spørringsplaner. Ved uplanlagte forespørsler må SQL Server analysere og optimalisere hver spørring hver gang. Med lagrede prosedyrer caches utførelsesplanen etter første kjøring, noe som reduserer overhead for gjentatte operasjoner.
Lag grunnleggende lagrede prosedyrer
Å lage en lagret prosedyre starter med setningen CREATE PROCEDURE etterfulgt av din T-SQL-logikk. Du spesifiserer prosedyrens navn ved hjelp av en skjema-kvalifisert identifikator, noe som forbedrer klarhet og ytelse.
CREATE PROCEDURE dbo.GetCustomerOrders
AS
BEGIN
SET NOCOUNT ON;
SELECT
OrderID,
CustomerID,
OrderDate,
TotalAmount
FROM dbo.Orders
ORDER BY OrderDate DESC;
END
Setningen SET NOCOUNT ON forhindrer at meldingen om antall berørte rader sendes til klienten. Dette reduserer nettverkstrafikken og forbedrer ytelsen, spesielt når prosedyren utfører flere setninger.
Når du lager prosedyrer, bruk og END nøkkelordene BEGIN for å tydelig definere prosedyrekroppen. Dette gjør koden din mer lesbar og hjelper til med å forhindre feil når du legger til eller endrer logikk senere.
Arbeide med parametere
Parametere gjør lagrede prosedyrer fleksible og gjenbrukbare. Du definerer inndataparametere for å akseptere verdier fra den kallende applikasjonen, og utdataparametere for å returnere verdier tilbake til kalleren.
Inndataparametere bruker @-symbolet etterfulgt av et parameternavn og datatype. Du kan oppgi standardverdier for å gjøre parametere valgfrie:
CREATE PROCEDURE dbo.GetCustomerOrdersByDate
@CustomerID int,
@StartDate datetime = NULL,
@EndDate datetime = NULL
AS
BEGIN
SET NOCOUNT ON;
SELECT
OrderID,
OrderDate,
TotalAmount
FROM dbo.Orders
WHERE CustomerID = @CustomerID
AND (@StartDate IS NULL OR OrderDate >= @StartDate)
AND (@EndDate IS NULL OR OrderDate <= @EndDate)
ORDER BY OrderDate DESC;
END
Utgangsparametrene lar deg returnere verdier til den kallende applikasjonen. Du definerer dem med OUTPUT nøkkelordet:
CREATE PROCEDURE dbo.CalculateOrderTotal
@OrderID int,
@TotalAmount decimal(10,2) OUTPUT
AS
BEGIN
SET NOCOUNT ON;
SELECT @TotalAmount = SUM(Quantity * UnitPrice)
FROM dbo.OrderDetails
WHERE OrderID = @OrderID;
RETURN 0;
END
Når du kaller en prosedyre med utgangsparametere, må du erklære en variabel for å motta verdien og bruke OUTPUT nøkkelordet i setningen EXECUTE .
Implementer feilhåndtering
Robuste lagrede prosedyrer inkluderer feilhåndtering for å håndtere uventede forhold og opprettholde dataintegritet. Du implementerer feilhåndtering ved hjelp av TRY...CATCH blokker, som fungerer på lignende måte som unntakshåndtering i andre programmeringsspråk.
CREATE PROCEDURE dbo.InsertCustomerOrder
@CustomerID int,
@OrderDate datetime,
@TotalAmount decimal(10,2)
AS
BEGIN
SET NOCOUNT ON;
BEGIN TRY
BEGIN TRANSACTION;
-- Validate customer exists
IF NOT EXISTS (SELECT 1 FROM dbo.Customers WHERE CustomerID = @CustomerID)
BEGIN
RAISERROR('Customer does not exist.', 16, 1);
END
-- Insert order
INSERT INTO dbo.Orders (CustomerID, OrderDate, TotalAmount)
VALUES (@CustomerID, @OrderDate, @TotalAmount);
COMMIT TRANSACTION;
RETURN 0;
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION;
DECLARE @ErrorMessage nvarchar(4000) = ERROR_MESSAGE();
DECLARE @ErrorSeverity int = ERROR_SEVERITY();
DECLARE @ErrorState int = ERROR_STATE();
RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState);
RETURN -1;
END CATCH
END
Blokken TRY inneholder hovedlogikken din, mens blokken håndterer CATCH eventuelle feil som oppstår. Du kan bruke systemfunksjoner som ERROR_MESSAGE(), ERROR_SEVERITY(), og ERROR_STATE() for å fange feildetaljer og sende dem til den kallende applikasjonen.
Sjekk @@TRANCOUNT alltid før du ruller tilbake transaksjoner i blokken CATCH . Dette forhindrer feil hvis transaksjonen allerede er fullført eller aldri er startet.
Bruk anbefalte fremgangsmåter
Å følge etablerte beste praksiser når du lager lagrede prosedyrer, sikrer at de er vedlikeholdbare, sikre og effektive.
Bruk skjema-kvalifiserte navn
Bruk skjema-kvalifiserte navn for alle objekter. Dette eliminerer tvetydighet og forbedrer ytelsen ved å unngå overhead for skjemaoppløsning:
-- Good
SELECT * FROM dbo.Orders
-- Avoid
SELECT * FROM Orders
Implementer parametervalidering
Implementer parametervalidering i starten av prosedyren. Feiler raskt når input er ugyldige i stedet for å behandle dårlige data:
IF @CustomerID IS NULL OR @CustomerID <= 0
BEGIN
RAISERROR('CustomerID must be a positive integer.', 16, 1);
RETURN -1;
END
Unngå SELECT *
Unngå SELECT * kode i produksjonen. List eksplisitt kolonner for å forhindre problemer når tabellstrukturer endres og for å forbedre spørringsytelsen:
-- Good
SELECT OrderID, CustomerID, OrderDate FROM dbo.Orders
-- Avoid
SELECT * FROM dbo.Orders
Bruk meningsfulle navn
Bruk meningsfulle navn som beskriver hva prosedyren gjør. Inkluder et verb som angir operasjonen (Hent, Sett inn, Oppdater, Slett, Beregn):
CREATE PROCEDURE dbo.GetActiveCustomersByRegion
CREATE PROCEDURE dbo.UpdateCustomerAddress
CREATE PROCEDURE dbo.DeleteExpiredOrders
Unngå prefikset sp_
Ikke bruk prefikset sp_ for lagrede prosedyrer. SQL Server reserverer dette prefikset for systemprosedyrer lagret i databasen master . Når du navngir en prosedyre med sp_, søker master SQL Server først før den sjekker den nåværende databasen, noe som legger til unødvendig overhead:
-- Good
CREATE PROCEDURE dbo.GetCustomerOrders
-- Avoid
CREATE PROCEDURE dbo.sp_GetCustomerOrders
Å bygge videre på disse praksisene hjelper deg å lage lagrede prosedyrer som teamet ditt kan forstå, vedlikeholde og stole på for å fungere pålitelig i produksjonsmiljøer.