Salvestatud protseduurid: kuidas need toimivad ja kuidas neid hästi kasutada

Iga andmebaasipõhine rakendus jõuab lõpuks punkti, kus rakenduskoodis laiali pillutatud SQL-päringud muutuvad hooldusprobleemiks. Sama keeruline liitmine esineb kolmes erinevas teenuses. Andmebaasikihile kuuluv äriloogika lekib rakendusse. Turvapoliitikad, mida tuleks jõustada andmekihil, jõustatakse hoopis rakenduse koodis ebajärjekindlalt, millest saab mööda minna. Salvestatud protseduurid lahendavad selle probleemi, teisaldades korduvkasutatava, turvatundliku ja jõudluskriitilise SQL-loogika andmebaasi, kus seda saab hallata, versioonida, turvata ja optimeerida sõltumatult rakendustest, mis seda kutsuvad.

Salvestatud protseduur on nimetatud, eelkompileeritud SQL-lausete kogum, mis salvestatakse andmebaasi ja käivitatakse ühikuna. See aktsepteerib parameetreid, sisaldab loogikat ja saab tagastada tulemusi, väljundparameetreid või olekukoode. Erinevalt rakenduskoodi saadetud ad hoc päringutest parsitakse ja kompileeritakse salvestatud protseduur üks kord, selle täitmisplaan salvestatakse vahemällu ja seda kasutatakse uuesti iga järgneva päringu korral, välistades kompileerimise lisakulud, mida korduv dünaamiline SQL tekitab. See juhend käsitleb, mis on salvestatud protseduurid, millal neid kasutada, kuidas need turvalisust tagavad, kuidas need mõjutavad jõudlust ja kuidas neid hallata, kui need kasvavad sõltuvusteks, mis hõlmavad kogu andmebaasi.

Mis on salvestatud protseduur?

Salvestatud protseduur on eelkompileeritud rutiin, mis salvestatakse relatsioonandmebaasis ja mida kutsutakse nimepidi valikuliste parameetritega. Andmebaasimootor kompileerib selle üks kord, salvestab täitmisplaani vahemällu ja kasutab seda plaani iga järgneva kutsumise korral uuesti, vältides parsimise-kompileerimise-optimeerimise tsüklit, mida ad hoc SQL-päringud iga käivitamise korral nõuavad.

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';

Rakendus ei kirjuta kunagi otse SQL-i. See kutsub esile GetCustomerOrders parameetritega. Andmebaas teeb ülejäänu.

Salvestatud protseduur vs. vaade vs. funktsioon

Kolm andmebaasiobjekti aetakse sageli segi. Allolev tabel eristab neid:

objekt	Tagastamine	Aktsepteerib parameetreid	Saab andmeid muuta	Täitmisplaan vahemällu salvestatud	Parim
Salvestatud protseduur	Tulemuste komplektid, väljundparameetrid, tagastuskoodid	Jah	Jah	Jah	Kompleksne loogika, DML-operatsioonid, turvalisuse jõustamine
vaade	Üksik tulemuste komplekt (nagu tabel)	Ei	Ei (tavaliselt)	Osaline	SELECT-päringute lihtsustamine, veerutaseme turvalisus
Skalaarfunktsioon	Üksikväärtus	Jah	Ei	Ei	SELECT-loendites taaskasutatud arvutused
Tabelipõhine funktsioon	Tulemuste komplekt	Jah	Ei	Osaline	Parameetrilised vaated, hulga tagastamise arvutused

Peamine erinevus: Kasutage vaadet, kui soovite korduvkasutatavat SELECT-abstraktsiooni. Kasutage salvestatud protseduuri, kui vajate parameetreid, tingimuslikku loogikat, andmete muutmist või turvalisuse jõustamist. Kasutage funktsiooni, kui vajate arvutust, mis tagastab väärtuse või tabeli ja peab koosnema teistest SQL-käskudest.

Neli peamist eelist

Jõudlus: eelkompileerimine ja plaani vahemällu salvestamine

Kui SQL Server, PostgreSQL või Oracle saab salvestatud protseduuri kutse, kontrollib see, kas selle protseduuri jaoks on olemas vahemällu salvestatud täitmisplaan. Kui on, siis käivitatakse see kohe. Kui ei, siis kompileeritakse protseduur, genereeritakse täitmisplaan, salvestatakse see vahemällu ja käivitatakse. Kõigi järgnevate kutsetega kasutatakse vahemällu salvestatud plaani uuesti.

Rakenduskoodist saadetud ad hoc SQL-päringud ehk stringidest liitpäringud võidakse iga päringu korral uuesti kompileerida, olenevalt andmebaasist ja päringu struktuurist. Tuhandeid kordi sekundis kutsutavate suure sagedusega päringute puhul on see kompileerimise lisakoormus märkimisväärne.

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;

Vähendatud võrguliiklus on teine ​​jõudluse eelis. Selle asemel, et saata rakendusest iga kutse korral andmebaasile keerukas 30-realine SQL-päring, saadab rakendus lühikese protseduurikutse. Võrgu koormus on minimaalne. Andmebaas teeb serveripoolse raske arvutuse ja tagastab ainult tulemuste komplekti.

Turvalisus: Otsese tabeli juurdepääsu piiramine

See on eelis, mida SC andmed konkreetselt otsivad – „kuidas kasutada salvestatud protseduure otsese andmetele juurdepääsu piiramiseks ja andmebaasi turvalisuse parandamiseks“. Mehhanism on lihtne ja võimas.

Anna rakenduse kasutajatele salvestatud protseduuride käivitamise õigus. Keela otsejuurdepääs alustabelitele. Kasutaja saab protseduuri kutsuda, kuid ei saa tabelisse otse päringuid teha, seda sisestada, värskendada ega kustutada.

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

SQL-süstimise ennetamine on teine ​​turvaeelis. Salvestatud protseduurid, mis kasutavad dünaamilise SQL-konstruktsiooni asemel parameetrilisi sisendeid, on SQL-süstimise eest loomupäraselt kaitstud. Parameetri väärtust käsitletakse literaalina, mitte SQL-koodina.

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;

Vaata ette: Salvestatud protseduur, mis loob sisemiselt dünaamilise SQL-i, kasutades EXEC() or sp_executesql Kasutaja sisendite stringide liitmine on sama haavatav kui rakendustaseme dünaamiline SQL. Parameetriseerimine peab laienema igale protseduuri sees loodud dünaamilisele SQL-ile.

Hooldatavus: üks muudatus, kõik rakendused on uuendatud

Kui äriloogika muutub, näiteks maksude arvutamise reeglid, allahindlustasemed või vastavusnõuete täitmiseks vajalikud andmete teisendused, koondab salvestatud protseduur selle loogika ühte kohta. Iga protseduuri kutsuv rakendus saab uuendatud käitumise automaatselt, ilma et oleks vaja uuesti juurutada.

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;

Muutke allahindluse protsente ühes kohas. Kõik kolm rakendusteenust, mis helistavad CalculateOrderTotal koheselt uusi hindu kajastama.

Kapseldamine väljundparameetrite ja veakäsitlusega

Salvestatud protseduurid tagastavad väljundparameetrite kaudu mitu väärtust ja edastavad töötlemise olekut tagastuskoodide kaudu, võimaldades rikkamaid interaktsioonimustreid kui lihtne 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);

Toimivuse optimeerimine: mida täitmisplaan teile ütleb

Täitmisplaan on andmebaasimootori salvestus selle kohta, kuidas ta otsustas päringut täita, milliseid indekseid ta kasutas, millise liitumisalgoritmi ta valis ja mitu rida ta igal sammul hindas. Salvestatud protseduuri puhul, mida kutsutakse tuhandeid kordi päevas, on täitmisplaan peamine diagnostikavahend jõudlusprobleemide tuvastamiseks.

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

Parameetrite nuusutamine on kõige levinum salvestatud protseduuri jõudlusprobleem. SQL Server vahemällu salvestab protseduuri kutsumise esimese parameetrite komplekti jaoks loodud täitmisplaani. Kui järgnevad kõned kasutavad väga erinevaid parameetrite väärtusi, näiteks klient 50 000 tellimusega vs klient 2 tellimusega, võib vahemällu salvestatud plaan nende väärtuste jaoks olla väga mitteoptimaalne.

Leevendusstrateegiad: OPTIMIZE FOR vihje parameetri esindusliku väärtuse optimeerimiseks; WITH RECOMPILE protseduuri tasandil, et genereerida iga kõne jaoks uus plaan (kulukas, aga efektiivne, kui parameetrite jaotused on väga erinevad); protseduuri alguses lokaalsete muutujate määramine nuhkimise vältimiseks:

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;

Parimad tavad: toimiv kontrollnimekiri

Üldpõhimõtete asemel on salvestatud protseduuride ulatuslikult hooldatavaks muutmiseks tavad järgmised:

Nimetamine ja korraldus

• Kasutage järjepidevat nimetamiskonventsiooni: usp_ kasutaja salvestatud protseduuride eesliide, sp_ reserveeritud süsteemiprotseduuridele
• Nimeta protseduurid tegusõna + nimisõna abil: GetCustomerOrders, InsertPaymentRecord, UpdateInventoryCount
• Skeemil grupeeritud seotud protseduurid: Sales.GetCustomerOrders, Inventory.UpdateStock

Koodi struktuur

• Alustage iga protseduuri sellega SET NOCOUNT ON et vältida ridade arvu teateid, mida kliendid võivad valesti tõlgendada
• Kasutama BEGIN TRY / BEGIN CATCH plokid selgesõnalise BEGIN TRANSACTION / COMMIT / ROLLBACK
• Väldi kursoreid hulgaoperatsioonide puhul, kirjuta võimaluse korral ümber hulgapõhiseks SQL-iks
• Ära kasuta SELECT *, nimeta iga veerg, mille protseduur tagastab

TURVALISUS

• Andke protseduuridele täitmisõigused; keelake rakendusrollidele otsene juurdepääs tabelile
• Väldi protseduuride sees kasutaja sisendist loodud dünaamilist SQL-i
• Kasutama sp_executesql parameetriliste päringutega, kui dünaamiline SQL on vältimatu

jõudlus

• Kontrollige suurte tabelite skaneerimise täitmisplaane, lisage vajadusel indeksid
• Nuusutamise suhtes tundlike protseduuride puhul testitakse representatiivsete parameetrite väärtustega
• Jälgida sys.dm_exec_procedure_stats suure täitmismahuga või pika kestusega protseduuride jaoks

dokumentatsioon

• Lisa igale protseduurile päisekommentaar: eesmärk, parameetrid, tagastusväärtused, autor, viimane muutmine
• Dokumenteerige protseduuriloogikasse kodeeritud ärireeglid, mitte ainult seda, mida SQL teeb, vaid ka seda, miks

Salvestatud protseduuride sõltuvuste haldamine

Salvestatud protseduurid ei eksisteeri isoleeritult. Protseduur, mis loeb viiest tabelist, kutsub esile kaks muud protseduuri ja mida kutsub esile tosin rakendusteenust, on komponent, millel on keerulised sõltuvused kõigis kolmes suunas: millest see sõltub, mis sellest sõltub ja mida see teiste protseduuridega jagab.

Kui tabeli veeru tüüp muutub, tuleb testida iga sellele veerule viitavat protseduuri. Kui protseduuri väljundvorming muutub, tuleb valideerida iga kutsuja. Kui protseduuri kaalutakse muutmiseks, määrab muudatuse ulatuse ja vajaliku regressioontestimise kogu kutsujate komplekt.

Olulised sõltuvustüübid:

• Objektide sõltuvused: tabelid, vaated, funktsioonid ja muud protseduurid, millele protseduur viitab
• Helistaja sõltuvused: rakenduse kood, muud salvestatud protseduurid ja ajastatud tööd, mis seda protseduuri kutsuvad
• Skeemi sõltuvused: tabelid ja veerudefinitsioonid, millele protseduuri parameetritüübid ja SELECT-loendid peavad vastama
• Tehingute sõltuvused: protseduurid, mis jagavad tehingu ulatust oma kutsujatega või üksteisega

Väikeses andmebaasis, kus on kümme salvestatud protseduuri, saab neid sõltuvusi käsitsi jälgida. Andmebaasis, kus on sadu salvestatud protseduure, mis on tavaline ettevõttekeskkondades, kus salvestatud protseduurid hõlmavad aastaid äriloogikat, tekitab käsitsi sõltuvuste jälgimine mittetäielikke kaarte ja muudatustega seotud intsidente.

Kuidas SMART TS XL Haldab salvestatud protseduuride sõltuvusi ettevõtte tasandil

SMART TS XL'S staatilise koodi analüüs parsib SQL-i salvestatud protseduure koos COBOL-programmide, Java-teenuste, Pythoni torujuhtmete ja muude sama andmebaasiga suhtlevate komponentidega. Ühendatud analüüs loob keelteülese struktuurimudeli: mitte ainult SQL-i ja SQL-i vahelised sõltuvused andmebaasis, vaid kogu ahela rakenduskoodist salvestatud protseduuride kaudu alustabeliteni ja tagasi.

. rakenduse sõltuvuste kaardistamine võimekus loob täieliku helistaja graafiku: millised COBOL-programmid kasutavad manustatud SQL-i, mis loeb salvestatud protseduuridele kuuluvatest tabelitest, millised Java-teenused kutsuvad salvestatud protseduure JDBC kaudu, millised JCL-i pakk-tööd käivitavad andmebaasi utiliite, mis käitavad salvestatud protseduure. Kui salvestatud protseduuri signatuur või käitumine muutub, näitab sõltuvuskaart iga helistajat igas keeles – kogu ulatust sellest, mida tuleb enne muudatuse tootmisse minekut testida.

. mõju analüüs võimekus muudab selle sõltuvuskaardi muudatuste planeerimisel rakendatavaks: tehke ettepanek muuta CalculateOrderTotal ja saada nummerdatud loendi igast komponendist, mis seda kutsub, igast tabelist, mida see loeb ja kirjutab, ning igast allavoolu protseduurist, mida see käivitab. See teisendab küsimuse „mida see rikub?“ hõimuteadmiste harjutusest struktureeritud, tõenduspõhiseks ulatusaruandeks.

. ettevõtte otsing võimekus muudab kogu sõltuvusmudeli päringuliseks: leia iga salvestatud protseduur, mis loeb Orders, iga helistaja GetCustomerOrders, iga protseduur, mis muudab konkreetset veergu sekunditega mis tahes suurusega andmebaasis.

Meeskondadele, kes juhivad pärand moderniseerimine programmid, kus salvestatud protseduurid kodeerivad aastakümnete pikkust äriloogikat, mida tuleb migreerimise ajal säilitada, SMART TS XLanalüüs annab struktuurilise dokumentatsiooni, mis muudab loogika ekstraheeritavaks ja migratsioonijärjestuse planeeritavaks.

Andmebaasi kiht, mis teenib oma koha

Salvestatud protseduurid ei ole varasema andmebaasiajastu relikt. Need on õige koht andmebaasi kuuluva loogika paigutamiseks: turvalisuse jõustamine, mis peab olema järjepidev olenemata sellest, milline rakendus andmetele juurde pääseb, jõudlustundlikud päringud, mis saavad kasu vahemällu salvestatud täitmisplaanidest, ja ärireeglid, mis peaksid üks kord värskendatama ja kõikjale levima.

Lõks ei seisne salvestatud protseduuride kasutamises, vaid nende kasvamises dokumenteerimata ja kaardistamata sõltuvusvõrgustikuks, millest keegi täielikult aru ei saa. Salvestatud protseduur, mis teeb kriitilise äriarvutuse, kuid millel puuduvad dokumenteeritud kutsujad, eesmärki selgitav päisekommentaar ja enne muutmist puudub mõjuanalüüs, on probleem olenemata sellest, kui hästi see on kirjutatud. Sõltuvusgraafiku haldamine on sama oluline kui SQL-i enda haldamine. Mõlemad nõuavad pigem süstemaatilist analüüsi kui hõimualaseid teadmisi.