Erstellen gespeicherter Prozeduren
Gespeicherte Prozeduren sind eines der leistungsstärksten Tools in SQL Server zum Kapseln von Geschäftslogik und zur Verbesserung der Anwendungsleistung. Wenn Sie gespeicherte Prozeduren erstellen, erstellen Sie wiederverwendbare Codeblöcke, die auf dem Server ausgeführt werden, wodurch der Netzwerkdatenverkehr reduziert und die Datenzugriffslogik zentralisiert wird.
Gespeicherte Prozeduren verstehen
Eine gespeicherte Prozedur ist eine kompilierte Sammlung von T-SQL-Anweisungen, die SQL Server als einzelne Einheit speichert und ausführt. Im Gegensatz zu ungeplanten Abfragen, die Sie jedes Mal an den Server senden, werden gespeicherte Prozeduren vorkompiliert und optimiert, was bedeutet, dass sie bei nachfolgenden Ausführungen schneller ausgeführt werden.
Sie verwenden gespeicherte Prozeduren, um komplexe Geschäftslogik zu kapseln, Datenüberprüfungsregeln zu erzwingen und zu steuern, wie Anwendungen mit Ihrer Datenbank interagieren. Anstatt z. B. den direkten Tabellenzugriff zuzulassen, können Sie gespeicherte Prozeduren erstellen, die Eingaben überprüfen, Geschäftsregeln anwenden und Änderungen protokollieren, bevor Sie Daten ändern.
Die Leistungsvorteile stammen aus der Zwischenspeicherung von Abfrageplänen. Bei ungeplanten Abfragen muss SQL Server jede Abfrage jedes Mal analysieren und optimieren. Bei gespeicherten Prozeduren wird der Ausführungsplan nach der ersten Ausführung zwischengespeichert, wodurch der Aufwand für wiederholte Vorgänge reduziert wird.
Erstellen grundlegender gespeicherter Prozeduren
Das Erstellen einer gespeicherten Prozedur beginnt mit der CREATE PROCEDURE Anweisung, gefolgt von der T-SQL-Logik. Sie geben den Prozedurnamen mithilfe eines schemaqualifizierten Bezeichners an, wodurch Klarheit und Leistung verbessert werden.
CREATE PROCEDURE dbo.GetCustomerOrders
AS
BEGIN
SET NOCOUNT ON;
SELECT
OrderID,
CustomerID,
OrderDate,
TotalAmount
FROM dbo.Orders
ORDER BY OrderDate DESC;
END
Die SET NOCOUNT ON Anweisung verhindert, dass die Nachricht über die Anzahl der betroffenen Zeilen an den Client gesendet wird. Dadurch wird der Netzwerkdatenverkehr reduziert und die Leistung verbessert, insbesondere wenn die Prozedur mehrere Anweisungen ausführt.
Wenn Sie Prozeduren erstellen, definieren Sie den Prozedurtext mithilfe der BEGINEND Schlüsselwörter eindeutig. Dadurch wird der Code besser lesbar und verhindert Fehler beim späteren Hinzufügen oder Ändern von Logik.
Arbeiten mit Parametern
Parameter machen gespeicherte Prozeduren flexibel und wiederverwendbar. Sie definieren Eingabeparameter, um Werte aus der aufrufenden Anwendung zu akzeptieren, und Ausgabeparameter, um Werte zurück an den Aufrufer zurückzugeben.
Eingabeparameter verwenden das @-Symbol gefolgt von einem Parameternamen und Datentyp. Sie können Standardwerte bereitstellen, um Parameter optional zu machen:
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
Mit Ausgabeparametern können Sie Werte an die aufrufende Anwendung zurückgeben. Sie definieren sie mithilfe des Schlüsselworts 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
Wenn Sie eine Prozedur mit Ausgabeparametern aufrufen, müssen Sie eine Variable deklarieren, um den Wert zu empfangen und das OUTPUT Schlüsselwort in der EXECUTE Anweisung zu verwenden.
Fehlerbehandlung implementieren
Zu den robusten gespeicherten Prozeduren gehören die Fehlerbehandlung zum Verwalten unerwarteter Bedingungen und zum Verwalten der Datenintegrität. Sie implementieren die Fehlerbehandlung mithilfe von TRY...CATCH Blöcken, die ähnlich wie die Ausnahmebehandlung in anderen Programmiersprachen funktionieren.
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
Der TRY Block enthält die Hauptlogik, während der CATCH Block alle auftretenden Fehler behandelt. Sie können Systemfunktionen wie ERROR_MESSAGE(), ERROR_SEVERITY()und ERROR_STATE() zum Erfassen von Fehlerdetails und zum Übergeben an die aufrufende Anwendung verwenden.
Überprüfen Sie @@TRANCOUNT immer, bevor Sie Transaktionen im CATCH Block zurücksetzen. Dadurch werden Fehler verhindert, wenn die Transaktion bereits abgeschlossen oder nie gestartet wurde.
Anwenden von bewährten Methoden
Wenn Sie bewährte Methoden befolgen, wenn Sie gespeicherte Prozeduren erstellen, stellen Sie sicher, dass sie wartbar, sicher und leistungsfähig sind.
Verwenden Sie schema-qualifizierte Namen
Verwenden Sie schemaqualifizierte Namen für alle Objekte. Dadurch werden Mehrdeutigkeiten vermieden und die Leistung verbessert, indem der Aufwand bei der Schemaauflösung vermieden wird:
-- Good
SELECT * FROM dbo.Orders
-- Avoid
SELECT * FROM Orders
Implementieren der Parameterüberprüfung
Implementieren Sie die Parameterüberprüfung am Anfang Ihrer Prozedur. Schnell fehlschlagen, wenn Eingaben ungültig sind, anstatt fehlerhafte Daten zu verarbeiten.
IF @CustomerID IS NULL OR @CustomerID <= 0
BEGIN
RAISERROR('CustomerID must be a positive integer.', 16, 1);
RETURN -1;
END
Vermeiden von SELECT *
Vermeiden Sie SELECT * in Produktivcode. Führe Spalten explizit auf, um Probleme zu vermeiden, wenn sich Tabellenstrukturen ändern, und um die Abfrageleistung zu verbessern.
-- Good
SELECT OrderID, CustomerID, OrderDate FROM dbo.Orders
-- Avoid
SELECT * FROM dbo.Orders
Verwenden aussagekräftiger Namen
Verwenden Sie aussagekräftige Namen, die beschreiben, was das Verfahren tut. Fügen Sie ein Verb ein, das den Vorgang angibt (Get, Insert, Update, Delete, Calculate):
CREATE PROCEDURE dbo.GetActiveCustomersByRegion
CREATE PROCEDURE dbo.UpdateCustomerAddress
CREATE PROCEDURE dbo.DeleteExpiredOrders
Vermeiden des Präfixes sp_
Verwenden Sie das sp_ Präfix nicht für Ihre gespeicherten Prozeduren. SQL Server reserviert dieses Präfix für in der master Datenbank gespeicherte Systemprozeduren. Wenn Sie eine Prozedur mit sp_ benennen, durchsucht SQL Server zunächst master, bevor es die aktuelle Datenbank prüft, wodurch unnötiger Aufwand entsteht:
-- Good
CREATE PROCEDURE dbo.GetCustomerOrders
-- Avoid
CREATE PROCEDURE dbo.sp_GetCustomerOrders
Aufbauend auf diesen Praktiken können Sie gespeicherte Prozeduren erstellen, die Ihr Team verstehen und pflegen kann und denen Sie vertrauen können, dass sie in Produktionsumgebungen zuverlässig arbeiten.