Lagrede procedurer: Hvordan de fungerer, og hvordan man bruger dem godt

Enhver databasedrevet applikation når til sidst et punkt, hvor SQL-forespørgsler spredt ud over applikationskoden bliver et vedligeholdelsesproblem. Den samme komplekse join forekommer i tre forskellige tjenester. Forretningslogik, der hører hjemme i databaselaget, lækker ind i applikationen. Sikkerhedspolitikker, der burde håndhæves på datalaget, håndhæves i stedet inkonsekvent af applikationskode, der kan omgås. Lagrede procedurer adresserer denne type problemer ved at flytte genanvendelig, sikkerhedsfølsom og ydeevnekritisk SQL-logik ind i databasen, hvor den kan administreres, versioneres, sikres og optimeres uafhængigt af de applikationer, der kalder den.

En lagret procedure er et navngivet, prækompileret sæt af SQL-sætninger, der er gemt i databasen og udført som en enhed. Den accepterer parametre, indeholder logik og kan returnere resultater, outputparametre eller statuskoder. I modsætning til ad hoc-forespørgsler, der sendes af applikationskode, parses og kompileres en lagret procedure én gang, dens udførelsesplan caches og genbruges ved hvert efterfølgende kald, hvilket eliminerer den kompileringsoverhead, som gentagen dynamisk SQL medfører. Denne vejledning dækker, hvad lagrede procedurer er, hvornår de skal bruges, hvordan de håndhæver sikkerhed, hvordan de påvirker ydeevnen, og hvordan man administrerer dem, efterhånden som de vokser til afhængigheder, der spænder over en hel databaseejendom.

Hvad er en lagret procedure?

En lagret procedure er en prækompileret rutine, der er gemt i en relationsdatabase og kaldes ved navn med valgfrie parametre. Databasemotoren kompilerer den én gang, cacher udførelsesplanen og genbruger denne plan ved hvert efterfølgende kald, hvilket undgår den parse-kompilere-optimere-cyklus, som ad hoc SQL-forespørgsler kræver, hver gang de kører.

sql

-- SQL Server: basic stored procedure
CREATE PROCEDURE GetCustomerOrders
    @CustomerId INT,
    @StartDate  DATE,
    @EndDate    DATE
AS
BEGIN
    SET NOCOUNT ON;

    SELECT
        o.OrderId,
        o.OrderDate,
        o.TotalAmount,
        o.Status
    FROM Orders o
    WHERE o.CustomerId = @CustomerId
      AND o.OrderDate BETWEEN @StartDate AND @EndDate
    ORDER BY o.OrderDate DESC;
END;
GO

-- Calling the procedure
EXEC GetCustomerOrders
    @CustomerId = 12345,
    @StartDate  = '2026-01-01',
    @EndDate    = '2026-06-30';

Applikationen skriver aldrig SQL direkte. Den kalder GetCustomerOrders med parametre. Databasen håndterer resten.

Lagret procedure vs. visning vs. funktion

Tre databaseobjekter forveksles ofte. Tabellen nedenfor adskiller dem:

Object	Returpolitik	Accepterer parametre	Kan ændre data	Udførelsesplan cachelagret	bedst til
Lagret procedure	Resultatsæt, outputparametre, returkoder	Ja	Ja	Ja	Kompleks logik, DML-operationer, sikkerhedshåndhævelse
Se	Enkelt resultatsæt (som en tabel)	Ingen	Nej (normalt)	Delvis	Forenkling af SELECT-forespørgsler, sikkerhed på kolonneniveau
Skalarfunktion	Enkelt værdi	Ja	Ingen	Ingen	Beregninger genbrugt i SELECT-lister
Tabelværdifunktion	Resultatsæt	Ja	Ingen	Delvis	Parameteriserede visninger, sæt-returnerende beregninger

Vigtig forskel: Brug en visning, når du ønsker en genanvendelig SELECT-abstraktion. Brug en lagret procedure, når du har brug for parametre, betinget logik, dataændring eller sikkerhedshåndhævelse. Brug en funktion, når du har brug for en beregning, der returnerer en værdi eller tabel, og som skal komponeres med anden SQL.

De fire kernefordele

Ydeevne: Prækompilering og plancaching

Når SQL Server, PostgreSQL eller Oracle modtager et kald til en lagret procedure, kontrollerer den, om der findes en cachelagret udførelsesplan for den pågældende procedure. Hvis der findes en, udføres den med det samme. Hvis ikke, kompilerer den proceduren, genererer en udførelsesplan, cacher den og udfører den. Ved alle efterfølgende kald genbruges den cachelagrede plan.

Ad hoc SQL-forespørgsler, strengsammenkædede forespørgsler sendt fra applikationskode, kan rekompileres ved hvert kald, afhængigt af databasen og forespørgselsstrukturen. For højfrekvente forespørgsler, der kaldes tusindvis af gange i sekundet, er denne kompileringsoverhead betydelig.

sql

-- Demonstrating plan reuse in SQL Server
-- Check if a plan exists for a procedure
SELECT
    qs.execution_count,
    qs.total_elapsed_time / qs.execution_count AS avg_elapsed_ms,
    qs.total_logical_reads / qs.execution_count AS avg_logical_reads,
    SUBSTRING(qt.text, 1, 100) AS procedure_text
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
WHERE qt.text LIKE '%GetCustomerOrders%'
ORDER BY qs.execution_count DESC;

Reduceret netværkstrafik er den anden fordel med hensyn til ydeevne. I stedet for at sende en kompleks SQL-forespørgsel på 30 linjer fra applikationen til databasen ved hvert kald, sender applikationen et kort procedurekald. Netværksnyttelasten er minimal. Databasen udfører den tunge beregning på serversiden og returnerer kun resultatsættet.

Sikkerhed: Begrænsning af direkte adgang til tabeller

Det er den fordel, som SC-dataene specifikt søger efter, "hvordan man bruger lagrede procedurer til at begrænse direkte dataadgang og forbedre databasesikkerheden." Mekanismen er ligetil og effektiv.

Giv programbrugere tilladelse til at udføre lagrede procedurer. Nægte direkte adgang til de underliggende tabeller. Brugeren kan kalde proceduren, men kan ikke forespørge, indsætte, opdatere eller slette direkte fra tabellen.

sql

-- Create a role for application users
CREATE ROLE AppReadRole;

-- Grant execute on the procedure
GRANT EXECUTE ON GetCustomerOrders TO AppReadRole;

-- Deny direct table access
DENY SELECT ON Orders TO AppReadRole;
DENY SELECT ON Customers TO AppReadRole;

-- The user can now:
--   EXEC GetCustomerOrders @CustomerId=123, ... -> works
--   SELECT * FROM Orders WHERE ... -> ACCESS DENIED

Forebyggelse af SQL-injektion er den anden sikkerhedsfordel. Lagrede procedurer, der bruger parameteriserede input i stedet for dynamisk SQL-konstruktion, er i sagens natur beskyttet mod SQL-injektion. Parameterværdien behandles som en literal, ikke som SQL-kode.

sql

-- VULNERABLE: dynamic SQL built from user input
DECLARE @sql NVARCHAR(500);
SET @sql = 'SELECT * FROM Customers WHERE Name = ''' + @UserInput + '''';
EXEC(@sql);
-- An attacker can inject: '; DROP TABLE Customers; --

-- SAFE: parameterized stored procedure
CREATE PROCEDURE GetCustomerByName
    @CustomerName NVARCHAR(100)
AS
BEGIN
    SELECT CustomerId, Name, Email
    FROM Customers
    WHERE Name = @CustomerName;  -- @CustomerName is a literal, not SQL
END;

Pas på: En lagret procedure, der opbygger dynamisk SQL internt ved hjælp af EXEC() or sp_executesql Med strengsammenkædning af brugerinput er lige så sårbar som dynamisk SQL på applikationsniveau. Parameterisering skal udvides til enhver dynamisk SQL, der er konstrueret inde i proceduren.

Vedligeholdelse: Én ændring, alle applikationer opdateret

Når forretningslogik, skatteberegningsregler, rabatniveauer eller overholdelseskrav til datatransformationer ændres, centraliserer en lagret procedure denne logik ét sted. Hver applikation, der kalder proceduren, modtager automatisk den opdaterede funktionsmåde uden at kræve omimplementering.

sql

-- Before: discount logic duplicated in three application services
-- After: centralized in one stored procedure
CREATE PROCEDURE CalculateOrderTotal
    @OrderId INT,
    @DiscountedTotal DECIMAL(10,2) OUTPUT
AS
BEGIN
    DECLARE @Subtotal DECIMAL(10,2);
    DECLARE @CustomerTier NCHAR(1);

    SELECT @Subtotal     = SUM(li.Quantity * li.UnitPrice),
           @CustomerTier = c.Tier
    FROM OrderLineItems li
    JOIN Orders o ON o.OrderId = li.OrderId
    JOIN Customers c ON c.CustomerId = o.CustomerId
    WHERE li.OrderId = @OrderId
    GROUP BY c.Tier;

    -- Business rule: Gold customers get 15%, Silver get 8%, Standard get 0%
    SET @DiscountedTotal = @Subtotal * CASE @CustomerTier
        WHEN 'G' THEN 0.85
        WHEN 'S' THEN 0.92
        ELSE 1.00
    END;
END;

Skift rabatprocenterne ét sted. Alle tre applikationstjenester, der kalder CalculateOrderTotal straks afspejle de nye satser.

Indkapsling med outputparametre og fejlhåndtering

Lagrede procedurer returnerer flere værdier via outputparametre og kommunikerer behandlingsstatus via returkoder, hvilket muliggør mere omfattende interaktionsmønstre end en simpel SELECT.

sql

CREATE PROCEDURE InsertOrder
    @CustomerId  INT,
    @OrderDate   DATE,
    @NewOrderId  INT OUTPUT,
    @StatusCode  INT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    BEGIN TRY
        BEGIN TRANSACTION;

        INSERT INTO Orders (CustomerId, OrderDate, Status)
        VALUES (@CustomerId, @OrderDate, 'PENDING');

        SET @NewOrderId = SCOPE_IDENTITY();
        SET @StatusCode = 0;  -- success

        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        ROLLBACK TRANSACTION;
        SET @StatusCode = ERROR_NUMBER();
        SET @NewOrderId = -1;
    END CATCH;
END;

-- Usage
DECLARE @OrderId INT, @Status INT;
EXEC InsertOrder
    @CustomerId = 5001,
    @OrderDate  = '2026-07-15',
    @NewOrderId = @OrderId OUTPUT,
    @StatusCode = @Status OUTPUT;

IF @Status <> 0
    PRINT 'Insert failed with error: ' + CAST(@Status AS VARCHAR);
ELSE
    PRINT 'Created order: ' + CAST(@OrderId AS VARCHAR);

Performanceoptimering: Hvad eksekveringsplanen fortæller dig

Udførelsesplanen er databasemotorens registrering af, hvordan den valgte at udføre en forespørgsel, hvilke indekser den brugte, hvilken join-algoritme den valgte, og hvor mange rækker den estimerede ved hvert trin. For en lagret procedure, der kaldes tusindvis af gange om dagen, er udførelsesplanen det primære diagnosticeringsværktøj til ydeevneproblemer.

sql

-- Enable actual execution plan in SQL Server
-- Then run:
EXEC GetCustomerOrders
    @CustomerId = 12345,
    @StartDate  = '2026-01-01',
    @EndDate    = '2026-06-30';

-- Check for parameter sniffing issues
-- (cached plan was optimized for a different parameter distribution)
EXEC GetCustomerOrders
    @CustomerId = 99999,  -- rare customer with very few orders
    @StartDate  = '2026-01-01',
    @EndDate    = '2026-06-30'
WITH RECOMPILE;  -- forces fresh plan for this call

Parametersniffing er det mest almindelige ydeevneproblem for lagrede procedurer. SQL Server cacher den udførelsesplan, der genereres for det første sæt parametre, som proceduren kaldes med. Hvis efterfølgende kald bruger meget forskellige parameterværdier, en kunde med 50,000 ordrer vs. en kunde med 2 ordrer, kan den cachelagrede plan være meget suboptimal for disse værdier.

Afhjælpningsstrategier: OPTIMIZE FOR hint til at optimere for en repræsentativ parameterværdi; WITH RECOMPILE på procedureniveau for at generere en ny plan for hvert kald (dyrt, men effektivt når parameterfordelingerne varierer meget); lokal variabeltildeling i starten af ​​proceduren for at forhindre sniffing:

sql

CREATE PROCEDURE GetCustomerOrders
    @CustomerId INT,
    @StartDate  DATE,
    @EndDate    DATE
AS
BEGIN
    -- Local variable trick: prevents parameter sniffing
    DECLARE @LocalCustomerId INT = @CustomerId;
    DECLARE @LocalStart      DATE = @StartDate;
    DECLARE @LocalEnd        DATE = @EndDate;

    SELECT o.OrderId, o.OrderDate, o.TotalAmount
    FROM Orders o
    WHERE o.CustomerId = @LocalCustomerId
      AND o.OrderDate BETWEEN @LocalStart AND @LocalEnd;
END;

Bedste praksis: En fungerende tjekliste

I stedet for generelle principper er det disse fremgangsmåder, der gør lagrede procedurer vedligeholdbare i stor skala:

Navngivning og organisering

• Brug en ensartet navngivningskonvention: usp_ præfiks for brugerlagrede procedurer, sp_ reserveret til systemprocedurer
• Navngivningsprocedurer efter verbum + substantiv: GetCustomerOrders, InsertPaymentRecord, UpdateInventoryCount
• Gruppér relaterede procedurer i et skema: Sales.GetCustomerOrders, Inventory.UpdateStock

Kode struktur

• Start hver procedure med SET NOCOUNT ON at undertrykke rækketællingsmeddelelser, som klienter kan misfortolke
• Brug BEGIN TRY / BEGIN CATCH blokke med eksplicitte BEGIN TRANSACTION / COMMIT / ROLLBACK
• Undgå markører for sætoperationer, omskriv som sætbaseret SQL hvor det er muligt
• Brug ikke SELECT *, navngiv hver kolonne, som proceduren returnerer

Sikkerhed

• Giv udførelsestilladelser på procedurer; nægt direkte tabeladgang for applikationsroller
• Undgå dynamisk SQL bygget fra brugerinput i procedurer
• Brug sp_executesql med parameteriserede forespørgsler, hvis dynamisk SQL er uundgåelig

Ydeevne

• Tjek udførelsesplaner for tabelscanninger på store tabeller, tilføj indekser om nødvendigt
• Test med repræsentative parameterværdier for procedurer, der er modtagelige for sniffing
• Overvåg sys.dm_exec_procedure_stats til procedurer med høj udførelse eller lang varighed

Dokumentation

• Tilføj en headerkommentar til hver procedure: formål, parametre, returværdier, forfatter, sidst ændret
• Dokumentér forretningsregler kodet i procedurelogikken, ikke kun hvad SQL'en gør, men hvorfor

Håndtering af afhængigheder af lagrede procedurer

Lagrede procedurer eksisterer ikke isoleret. En procedure, der læser fra fem tabeller, kalder to andre procedurer og kaldes af et dusin applikationstjenester, er en komponent med komplekse afhængigheder i alle tre retninger: hvad den afhænger af, hvad der afhænger af den, og hvad den deler med andre procedurer.

Når en tabelkolonne ændrer type, skal alle procedurer, der refererer til den kolonne, testes. Når en procedures outputformat ændres, skal alle kaldere valideres. Når en procedure overvejes til ændring, bestemmer det fulde sæt af kaldere omfanget af ændringen og den nødvendige regressionstest.

Afhængighedstyper, der er vigtige:

• Objektafhængigheder: tabeller, visninger, funktioner og andre procedurer, som proceduren refererer til
• Opkaldsafhængigheder: programkode, andre lagrede procedurer og planlagte job, der kalder denne procedure
• Skemaafhængigheder: tabeller og kolonnedefinitioner, som procedurens parametertyper og SELECT-lister skal matche
• Transaktionsafhængigheder: procedurer, der deler transaktionsomfang med deres kaldere eller med hinanden

I en lille database med ti lagrede procedurer kan disse afhængigheder spores manuelt. I en databaseejendom med hundredvis af lagrede procedurer, hvilket er almindeligt i virksomhedsmiljøer, hvor lagrede procedurer indkapsler mange års forretningslogik, producerer manuel afhængighedssporing ufuldstændige kort og ændringsrelaterede hændelser.

Hvordan SMART TS XL Administrerer afhængigheder af lagrede procedurer på virksomhedsniveau

SMART TS XL's statisk kodeanalyse analyserer SQL-lagrede procedurer sammen med COBOL-programmer, Java-tjenester, Python-pipelines og andre komponenter, der interagerer med den samme database. Den samlede analyse producerer en tværsproget strukturel model: ikke kun SQL-til-SQL-afhængighederne i databasen, men hele kæden fra applikationskode gennem lagrede procedurer til underliggende tabeller og tilbage.

Funktionen til applikationsafhængighedskortlægning opbygger den komplette kaldende graf: hvilke COBOL-programmer bruger indlejret SQL, der læser fra tabeller, der ejes af lagrede procedurer, hvilke Java-tjenester kalder lagrede procedurer via JDBC, hvilke JCL-batchjob kalder databaseværktøjer, der kører lagrede procedurer. Når en lagret procedures signatur eller adfærd ændres, viser afhængighedskortet alle kaldere på tværs af alle sprog, det fulde omfang af, hvad der skal testes, før ændringen går i produktion.

konsekvensanalyse Funktionen gør dette afhængighedskort brugbart til ændringsplanlægning: foreslå en ændring til CalculateOrderTotal og modtage en opregnet liste over alle komponenter, der kalder den, alle tabeller den læser og skriver til, og alle downstream-procedurer den aktiverer. Dette konverterer spørgsmålet "hvad vil dette ødelægge?" fra en øvelse i stammeviden til en struktureret, evidensbaseret omfangsrapport.

virksomhedssøgning Funktionen gør den fulde afhængighedsmodel forespørgbar: find alle lagrede procedurer, der læser fra Orders, enhver opkalder af GetCustomerOrders, enhver procedure, der ændrer en specifik kolonne på få sekunder på tværs af en databasebesiddelse af enhver størrelse.

For hold, der udfører arvemodernisering programmer hvor lagrede procedurer koder årtiers forretningslogik, der skal bevares under migrering, SMART TS XLs analyse leverer den strukturelle dokumentation, der gør logikken udtrækkelig og migreringssekvensen planlæggelig.

Databaselaget, der fortjener sin plads

Lagrede procedurer er ikke en levn fra en tidligere databaseæra. De er det rette sted at placere logik, der hører hjemme i databasen: sikkerhedshåndhævelse, der skal være konsistent uanset hvilken applikation der tilgår dataene, ydeevnefølsomme forespørgsler, der drager fordel af cachelagrede udførelsesplaner, og forretningsregler, der skal opdateres én gang og udbredes overalt.

Fælden bruger ikke lagrede procedurer, den lader dem vokse til et udokumenteret, ukortlagt afhængighedsnetværk, som ingen fuldt ud forstår. En lagret procedure, der udfører en kritisk forretningsberegning, men ikke har dokumenterede kaldere, ingen headerkommentar, der forklarer dens formål, og ingen konsekvensanalyse før ændring, er en belastning, uanset hvor godt den er skrevet. At administrere afhængighedsgrafen er lige så vigtigt som at administrere selve SQL'en. Begge kræver systematisk analyse snarere end stamkundskab.