Crearea procedurilor stocate
Procedurile stocate sunt unul dintre cele mai puternice instrumente din SQL Server pentru încapsularea logicii de business și îmbunătățirea performanței aplicațiilor. Când creezi proceduri stocate, construiești blocuri de cod reutilizabile care rulează pe server, reducând traficul de rețea și centralizând logica accesului la date.
Înțelege procedurile stocate
O procedură stocată este o colecție compilată de instrucțiuni T-SQL pe care SQL Server o stochează și execută ca o singură unitate. Spre deosebire de interogările neplanificate pe care le trimiți serverului de fiecare dată, procedurile stocate sunt precompilate și optimizate, ceea ce înseamnă că rulează mai rapid la execuțiile ulterioare.
Folosești proceduri stocate pentru a încapsula logica de business complexă, a impune reguli de validare a datelor și a controla modul în care aplicațiile interacționează cu baza ta de date. De exemplu, în loc să permiți acces direct la tabele, poți crea proceduri stocate care validează inputul, aplică reguli de business și înregistrează modificările înainte de a modifica datele.
Beneficiile de performanță vin din cache-ul planului de interogare. În cazul interogărilor neplanificate, SQL Server trebuie să analizeze și să optimizeze fiecare interogare de fiecare dată. În cazul procedurilor stocate, planul de execuție este stocat în cache după prima execuție, reducând overhead-ul pentru operațiuni repetate.
Creează proceduri de stocare de bază
Crearea unei proceduri stocate începe cu instrucțiunea CREATE PROCEDURE urmată de logica ta T-SQL. Specifici numele procedurii folosind un identificator calificat de schemă, ceea ce îmbunătățește claritatea și performanța.
CREATE PROCEDURE dbo.GetCustomerOrders
AS
BEGIN
SET NOCOUNT ON;
SELECT
OrderID,
CustomerID,
OrderDate,
TotalAmount
FROM dbo.Orders
ORDER BY OrderDate DESC;
END
Instrucțiunea SET NOCOUNT ON împiedică trimiterea mesajului despre numărul de rânduri afectate către client. Aceasta reduce traficul de rețea și îmbunătățește performanța, mai ales atunci când procedura execută mai multe instrucțiuni.
Când creezi proceduri, folosește BEGIN cuvintele-cheie și END pentru a defini clar corpul procedurii. Acest lucru face ca codul tău să fie mai ușor de citit și ajută la prevenirea erorilor la adăugarea sau modificarea logicii ulterioare.
Lucrul cu parametrii
Parametrii fac procedurile stocate flexibile și reutilizabile. Definești parametri de intrare pentru a accepta valorile aplicației care apelează și parametrii de output pentru a returna valorile către apelant.
Parametrii de intrare folosesc simbolul @ urmat de un nume de parametru și un tip de date. Poți oferi valori implicite pentru a face parametrii opționali:
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
Parametrii de ieșire îți permit să returnezi valori aplicației care apelează. Le definești folosind cuvântul-cheie OUTPUT :
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
Când apelezi o procedură cu parametri de ieșire, trebuie să declari o variabilă pentru a primi valoarea și să folosești OUTPUT cuvântul cheie din EXECUTE instrucțiuni.
Implementarea gestionării erorilor
Procedurile stocate robuste includ gestionarea erorilor pentru gestionarea condițiilor neașteptate și menținerea integrității datelor. Implementezi gestionarea erorilor folosind TRY...CATCH blocuri, care funcționează similar cu gestionarea excepțiilor în alte limbaje de programare.
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
Blocul TRY conține logica ta principală, în timp ce CATCH blocul gestionează orice erori care apare. Poți folosi funcții de sistem precum ERROR_MESSAGE(), ERROR_SEVERITY(), și ERROR_STATE() pentru a captura detalii de eroare și a le transmite aplicației apelante.
Verifică @@TRANCOUNT întotdeauna înainte să anulezi tranzacțiile din CATCH bloc. Acest lucru previne erorile dacă tranzacția a fost deja finalizată sau nu a fost niciodată începută.
Aplicarea celor mai bune practici
Urmarea celor mai bune practici consacrate atunci când creezi proceduri stocate asigură că acestea sunt întreținebile, sigure și performante.
Folosiți nume calificate de schemă
Folosiți nume calificate de schemă pentru toate obiectele. Aceasta elimină ambiguitatea și îmbunătățește performanța prin evitarea sarcinii de rezoluție a schemei:
-- Good
SELECT * FROM dbo.Orders
-- Avoid
SELECT * FROM Orders
Implementarea validării parametrilor
Implementează validarea parametrilor la începutul procedurii. Eșuează rapid când intrările sunt invalide, în loc să procesezi date greșite:
IF @CustomerID IS NULL OR @CustomerID <= 0
BEGIN
RAISERROR('CustomerID must be a positive integer.', 16, 1);
RETURN -1;
END
Evită SELECT *
Evită SELECT * în codul de producție. Listați explicit coloanele pentru a preveni problemele când structurile tabelelor se schimbă și pentru a îmbunătăți performanța interogării:
-- Good
SELECT OrderID, CustomerID, OrderDate FROM dbo.Orders
-- Avoid
SELECT * FROM dbo.Orders
Folosirea numelor cu sens
Folosește denumiri semnificative care să descrie ce face procedura. Include un verb care indică operațiunea (Get, Insert, Update, Delete, Calculate):
CREATE PROCEDURE dbo.GetActiveCustomersByRegion
CREATE PROCEDURE dbo.UpdateCustomerAddress
CREATE PROCEDURE dbo.DeleteExpiredOrders
Evită prefixul sp_
Nu folosi prefixul sp_ pentru procedurile tale stocate. SQL Server rezervă acest prefix pentru procedurile sistemului stocate în master baza de date. Când denumești o procedură cu sp_, SQL Server master caută mai întâi înainte de a verifica baza de date curentă, adăugând un overhead inutil:
-- Good
CREATE PROCEDURE dbo.GetCustomerOrders
-- Avoid
CREATE PROCEDURE dbo.sp_GetCustomerOrders
Construirea pe baza acestor practici te ajută să creezi proceduri stocate pe care echipa ta le poate înțelege, menține și avea încredere pentru a funcționa fiabil în medii de producție.