SQL Stored Procedure: Automatisieren und Optimieren von Abfragen

SQL Stored Procedures sind Sätze von SQL-Anweisungen, die in einer Datenbank gespeichert und aufbewahrt werden. Sie können bei Bedarf ausgeführt werden, um Datenmanipulationen und -validierungen vorzunehmen, so dass für gängige Operationen kein wiederholter SQL-Code geschrieben werden muss. Stored Procedures sind bei der Datenbankverwaltung hilfreich, da sie die Effizienz und Wiederverwendbarkeit fördern. Außerdem unterstützen sie eine verbesserte Datenbanksicherheit und Wartungsfreundlichkeit. In diesem Artikel besprechen wir, wie man SQL Stored Procedures erstellt und ausführt, häufige Anwendungsfälle und Best Practices.

Für den Anfang empfehle ich dir die DataCamp-Kurse "Einführung in SQL" und "SQL lernen", in denen du die Grundlagen der Datenextraktion und -analyse mit SQL erlernst. Auch das SQL Basics Cheat Sheet, das du herunterladen kannst, ist ein hilfreiches Nachschlagewerk, da es alle gängigen SQL-Funktionen enthält.

Was ist eine Stored Procedure in SQL?

Eine gespeicherte Prozedur in SQL ist eine Sammlung von SQL-Anweisungen, die in der Datenbank gespeichert und aufbewahrt werden. Der Zweck einer SQL Stored Procedure ist es, eine Abfolge von Operationen in einer Datenbank durchzuführen, wie z.B. das Abfragen, Einfügen, Aktualisieren oder Löschen von Daten.

Im Gegensatz zu regulären SQL-Abfragen, die als separate Befehle ausgeführt werden, kapseln Stored Procedures eine Reihe von SQL-Anweisungen, so dass es einfach ist, den Code wiederzuverwenden, ohne SQL-Befehle wiederholt schreiben zu müssen.

Zu den Vorteilen von SQL Stored Procedures gehören folgende:

• Wiederverwendbarkeit des Codes: Sobald eine gespeicherte Prozedur erstellt ist, kann sie so oft wie nötig aufgerufen werden, wodurch Redundanzen im SQL-Code vermieden werden.
• Verbesserte Leistung: Stored Procedures werden oft schneller ausgeführt, weil sie vorkompiliert und auf dem Datenbankserver gespeichert sind, was die Netzwerklatenz und die Kompilierungszeit reduziert.
• Sicherheit: Gespeicherte Prozeduren können die Datensicherheit und die Kontrolle über den Zugriff auf sensible Daten verbessern, indem sie Benutzern die Erlaubnis erteilen, eine gespeicherte Prozedur ohne direkten Zugriff auf Tabellen auszuführen.

Grundlegende Syntax und Struktur

Die Syntax zum Erstellen einer Stored Procedure kann je nach Datenbanksystem (z. B. MySQL, SQL Server, Oracle) leicht variieren. Im Folgenden findest du ein allgemeines Beispiel mit der SQL Server-Syntax:

-- Create a stored procedure named ProcedureName
CREATE PROCEDURE ProcedureName 
    @Parameter1 INT, 
    @Parameter2 VARCHAR(50)  
AS
BEGIN
    -- SQL statements go here
    SELECT * FROM TableName WHERE Column1 = @Parameter1 AND Column2 = @Parameter2;
END;Bereitgestellt von

In der obigen Syntax;

• VERFAHREN ERSTELLEN: Dieser Befehl wird verwendet, um eine neue Stored Procedure zu definieren.

• Verfahrensname: Der Name, der der gespeicherten Prozedur gegeben wurde. Sie sollte innerhalb der Datenbank eindeutig sein.

• @Parameter1, @Parameter2: Parameter sind optional; sie ermöglichen es der Prozedur, Dateneingaben zu erhalten. Jeder Parameter wird mit einem @ Symbol und einem Datentyp (z.B. INT, VARCHAR(50)) definiert.

• ALS ANFANG...ENDE: Die SQL-Anweisungen innerhalb von BEGIN und END bilden den Körper der Prozedur, in dem die Hauptlogik ausgeführt wird. Die Prozedur ruft Datensätze aus einer Tabelle ab, die in diesem Beispiel auf bestimmten Bedingungen basieren.

Eingabe- und Ausgabeparameter

Mit den Eingabe- und Ausgabeparametern kannst du Werte an eine gespeicherte Prozedur übergeben und von ihr abrufen.

Wenn zum Beispiel @Parameter1 als Eingabeparameter definiert ist, kann ihm beim Aufruf der Prozedur ein beliebiger Wert zugewiesen werden, der die SQL-Logik oder die Ausgabe beeinflusst. Im folgenden Beispiel werden mit dem Parameter @UserID Daten für die angegebene UserID abgerufen.

-- Create a procedure to retrieve data for a specific user by UserID
CREATE PROCEDURE GetUserData 
 -- Input parameter: ID of the user to retrieve
    @UserID INT 
AS
BEGIN
    -- Select all columns from Users where UserID matches the input parameter
    SELECT * FROM Users WHERE UserID = @UserID;
END;Bereitgestellt von

Ausgabeparameter, die mit dem Schlüsselwort OUTPUT definiert werden, ermöglichen es einer gespeicherten Prozedur, einen Wert an die aufrufende Umgebung zurückzusenden. Wenn eine Prozedur zum Beispiel einen Rabatt berechnet, könnte sie einen Ausgabeparameter verwenden, um ihn an das aufrufende Programm zurückzugeben.

-- Create a procedure to calculate the discounted price
CREATE PROCEDURE CalculateDiscount 
    @Price DECIMAL(10, 2), 
    @DiscountRate DECIMAL(5, 2),  
    @FinalPrice DECIMAL(10, 2) OUTPUT -- Output: final price after discount
AS
BEGIN
    -- Calculate final price by applying the discount rate to the original price
    SET @FinalPrice = @Price * (1 - @DiscountRate);
END;Bereitgestellt von

Um diese Prozedur aufzurufen, würdest du verwenden;

-- Declare a variable to store the final price after discount
DECLARE @FinalPrice DECIMAL(10, 2);
-- Execute the CalculateDiscount procedure with a price of 100 and a 10% discount 
-- Store the output in the @FinalPrice variable
EXEC CalculateDiscount @Price = 100, @DiscountRate = 0.1, @FinalPrice = @FinalPrice OUTPUT;
-- Select and display the final discounted price
SELECT @FinalPrice AS FinalPrice;Bereitgestellt von

Stored Procedures in MySQL

Wie ich bereits erwähnt habe, kann die Ausführung einer gespeicherten Prozedur in SQL je nach Datenbanksystem und den verwendeten Tools auf unterschiedliche Weise erfolgen.

Stored Procedures in MySQL erstellen

Um eine Stored Procedure in MySQL zu erstellen, musst du den Namen der Prozedur, die Parameter und die SQL-Anweisungen definieren, die den Prozedurenkörper bilden. Im folgenden Beispiel wird eine Prozedur mit dem Namen GetEmployeeDetails erstellt, die EmployeeID als Eingabeparameter erhält und die Details für diesen speziellen Mitarbeiter abruft.

DELIMITER $
-- Create a procedure to retrieve details for a specific employee by EmployeeID
CREATE PROCEDURE GetEmployeeDetails(IN EmployeeID INT)  
BEGIN  
    -- Select all columns from Employees where EmployeeID matches the input parameter  
    SELECT * FROM Employees WHERE EmployeeID = EmployeeID;  
END$
DELIMITER ;Bereitgestellt von

Ausführen von Stored Procedures in MySQL

Die gängigste Art, eine gespeicherte Prozedur auszuführen, ist die Verwendung von SQL-Befehlen. In MySQL verwenden wir den Befehl CALL, um die Stored Procedure auszuführen.

CALL ProcedureName();Bereitgestellt von

Unter Verwendung der definierten GetEmployeeDetails Prozedur würde die Ausführungsabfrage wie folgt aussehen:

-- Execute the stored procedure to retrieve details for EmployeeID 101
CALL GetEmployeeDetails(101);Bereitgestellt von

Stored Procedures in SQL Server

SQL Server bietet eine spezielle Syntax und Befehle für das Erstellen, Ausführen und Verwalten von Stored Procedures. Dieser Ansatz macht es einfacher, effiziente und wiederverwendbare SQL-Routinen zu erstellen, die komplexe Aufgaben mit minimalen Wiederholungen bewältigen können.

Erstellen von gespeicherten Prozeduren in SQL Server

Beim Erstellen einer gespeicherten Prozedur in SQL Server werden der Name der Prozedur, die Parameter und die SQL-Anweisungen, die den Prozedurenkörper bilden, festgelegt. Im folgenden Beispiel wird eine Prozedur mit dem Namen GetEmployeeDetails erstellt, die @EmployeeID als Eingabeparameter erhält und die Details für diesen speziellen Mitarbeiter abruft.

-- Create a procedure to retrieve details for a specific employee by EmployeeID
CREATE PROCEDURE GetEmployeeDetails
    @EmployeeID INT          -- Input parameter: ID of the employee to retrieve
AS
BEGIN
    -- Select all columns from Employees where EmployeeID matches the input parameter
    SELECT * FROM Employees WHERE EmployeeID = @EmployeeID;
END;Bereitgestellt von

Ausführen von gespeicherten Prozeduren in SQL Server

In SQL Server ruft der Befehl EXEC oder EXECUTE eine gespeicherte Prozedur auf. Das folgende Beispiel zeigt, wie die gespeicherte Prozedur GetEmployeeDetails mit bestimmten Eingabeparametern ausgeführt werden kann. 

-- Execute the GetEmployeeDetails procedure with the EmployeeIDset to 102
EXEC GetEmployeeDetails @EmployeeID = 102;Bereitgestellt von

Du kannst die Ausgabeparameter auch ausführen, indem du die Variable im Befehl deklarierst. Im folgenden Beispiel wird @TotalSales als Variable deklariert, die die Ausgabe von CalculateTotalSales erhält. 

-- Declare a variable to store the total sales amount
DECLARE @TotalSales DECIMAL(10, 2);
-- Execute CalculateTotalSales for SalespersonID 5, store the result in @TotalSales
EXEC CalculateTotalSales @SalespersonID = 5, @TotalSales = @TotalSales OUTPUT;
-- Display the total sales amount
SELECT @TotalSales AS TotalSales;Bereitgestellt von

Ich empfehle, unseren Kurs Einführung in SQL Server zu besuchen, um die verschiedenen Funktionen von SQL Server für die Abfrage von Daten zu verstehen. Ziehe auch unseren Lernpfad für SQL Server-Entwickler in Betracht, der dich nicht nur mit dem Erstellen, Aktualisieren und Ausführen von gespeicherten Prozeduren vertraut macht, sondern dich auch mit Aggregatfunktionen, dem Verbinden, Einfügen und Löschen von Tabellen und vielem mehr vertraut macht.

Häufige Verwendungen für Stored Procedures

SQL Stored Procedures sind in Szenarien nützlich, in denen sich wiederholende komplexe Aufgaben erforderlich sind. Im Folgenden findest du praktische Anwendungen von Stored Procedures in der Datenverwaltung und im Geschäftsbetrieb. 

Datenvalidierung und Durchsetzung der Integrität

Stored Procedures können verwendet werden, um Daten vor der Aktualisierung oder Einfügung zu überprüfen. Im folgenden Beispiel prüft eine gespeicherte Prozedur, ob die E-Mail eines Kunden eindeutig ist, bevor sie einen neuen Datensatz in die Tabelle Customers einfügt, um die Datenkonsistenz zu gewährleisten. Dadurch wird die Validierungslogik in der Datenbank zentralisiert, was Redundanzen reduziert und eine einheitliche Durchsetzung in verschiedenen Anwendungen gewährleistet.

-- Create a procedure to add a new customer, checking for duplicate email
CREATE PROCEDURE AddCustomer
    @CustomerName VARCHAR(50), 
    @CustomerEmail VARCHAR(50)
AS
BEGIN
    -- Check if the email already exists in the Customers table
    IF EXISTS (SELECT 1 FROM Customers WHERE Email = @CustomerEmail)
        -- Throw an error if the email is already in use
        THROW 50000, 'Email already exists.', 1;
    ELSE
        -- Insert new customer details if email is unique
        INSERT INTO Customers (Name, Email) VALUES (@CustomerName, @CustomerEmail);
END;Bereitgestellt von

Automatisierte Datenverarbeitung und Berichterstattung

Du kannst auch Stored Procedures verwenden, um regelmäßige Berichte zu erstellen oder große Datensätze zu verarbeiten. Eine gespeicherte Prozedur könnte zum Beispiel die täglichen Verkaufsdaten einer E-Commerce-Plattform aggregieren und in einer Berichtstabelle speichern, damit die Teams leichter auf die Verkaufsdaten zugreifen können, ohne komplexe Abfragen durchführen zu müssen.

-- Create a procedure to generate a daily sales report
CREATE PROCEDURE GenerateDailySalesReport
AS
BEGIN
    -- Insert today's date and total sales into the SalesReport table
    INSERT INTO SalesReport (ReportDate, TotalSales)
    
    -- Select current date and sum of sales for today from Sales table
    SELECT CAST(GETDATE() AS DATE), SUM(SalesAmount) 
    FROM Sales 
    WHERE SaleDate = CAST(GETDATE() AS DATE);
END;Bereitgestellt von

Transaktionsmanagement

Durch die Verwendung von Stored Procedures kannst du sicherstellen, dass mehrere Vorgänge in einer einzigen Transaktion ausgeführt werden. In einem Banksystem kann eine gespeicherte Prozedur zum Beispiel sowohl Soll- als auch Haben-Aktionen bei einer Überweisung abwickeln und sicherstellen, dass beide Aktionen zusammen erfolgreich sind oder fehlschlagen.

-- Create a procedure to transfer funds between accounts
CREATE PROCEDURE TransferFunds
    @SenderAccount INT,
    @ReceiverAccount INT,
    @Amount DECIMAL(10, 2)
AS
BEGIN
    BEGIN TRANSACTION;  -- Start a transaction to ensure atomicity
    -- Deduct the specified amount from the sender's account balance
    UPDATE Accounts SET Balance = Balance - @Amount WHERE AccountID = @SenderAccount;
    -- Add the specified amount to the receiver's account balance
    UPDATE Accounts SET Balance = Balance + @Amount WHERE AccountID = @ReceiverAccount;
    -- Check for errors and rollback if any occurred; otherwise, commit the transaction
    IF @@ERROR <> 0
        ROLLBACK TRANSACTION;  -- Undo all changes if an error occurred
    ELSE
        COMMIT TRANSACTION;    -- Confirm changes if no errors
END;Bereitgestellt von

Zugangskontrolle und Datensicherheit

Du kannst SQL auch verwenden, um den Datenzugriff auf sensible Informationen zu kontrollieren. Eine gespeicherte Prozedur kann zum Beispiel den direkten Zugriff auf Tabellen einschränken, indem sie den Benutzern erlaubt, eine Prozedur aufzurufen, die nur relevante Felder, wie z. B. Kontostände, ohne Transaktionsdetails abruft.

-- Create a procedure to retrieve account balance, with authorization check
CREATE PROCEDURE GetAccountBalance
    @AccountID INT, 
    @UserID INT 
AS
BEGIN
    -- Check if the account exists and is owned by the specified user
    IF EXISTS (SELECT 1 FROM Accounts WHERE AccountID = @AccountID AND UserID = @UserID)
        -- If authorized, select and return the account balance
        SELECT Balance FROM Accounts WHERE AccountID = @AccountID;
    ELSE
        -- If unauthorized, throw an error
        THROW 50000, 'Unauthorized access.', 1;
END;Bereitgestellt von

Datenmigration und ETL-Prozesse

Stored Procedures werden auch zum Laden, Transformieren und Migrieren von Daten zwischen Systemen verwendet. Eine gespeicherte Prozedur kann die Datenextraktion aus einer Quelldatenbank automatisieren, sie nach Bedarf umwandeln und in eine Zieltabelle einfügen, was die Datenintegration für Berichte oder Analysen vereinfacht.

CREATE PROCEDURE ETLProcess
AS
BEGIN
    -- Extract
    INSERT INTO StagingTable
    SELECT * FROM SourceTable WHERE Condition;
    -- Transform
    UPDATE StagingTable SET ColumnX = TransformationLogic(ColumnX);
    -- Load
    INSERT INTO TargetTable
    SELECT * FROM StagingTable;
END;Bereitgestellt von

Best Practices für Stored Procedures

Das Schreiben effizienter und wartbarer Stored Procedures stellt sicher, dass deine Datenbank optimal funktioniert. Im Folgenden findest du Tipps zum Schreiben von Stored Procedures für deine SQL-Datenbanken.

• Verwende einheitliche Namenskonventionen: Damit gespeicherte Prozeduren leicht zu identifizieren und zu verstehen sind, solltest du ein einheitliches und beschreibendes Namensformat verwenden. Vermeide außerdem das Präfix sp_, das in SQL Server für Systemprozeduren reserviert ist, um mögliche Konflikte und Leistungsprobleme zu vermeiden.

• Implementiere die Fehlerbehandlung: Schließe SQL-Anweisungen in TRY...CATCH Blöcke ein, um Fehler abzufangen und zu behandeln und die Datenintegrität zu wahren. 

• Optimiere für Leistung: Minimiere die Verwendung von Cursorn, da sie langsam und ressourcenintensiv sein können. Versuche stattdessen, mengenbasierte Operationen zu verwenden, die im Allgemeinen effizienter sind. Indiziere außerdem die häufig verwendeten Spalten und vermeide komplexe Joins in großen Tabellen, um den Speicherbedarf zu reduzieren und die Effizienz zu steigern.

• Stored Procedures parametrisieren: Verwende Parameter anstelle von fest kodierten Werten, damit du dynamische Werte in deine Prozedur übergeben kannst, was sie flexibler und wiederverwendbar macht.

In unserem SQL-Kurs für Fortgeschrittene erfährst du mehr über die Verwendung von Aggregatfunktionen und Joins zum Filtern von Daten. Probiere auch unsere Lernpfade SQL Server Fundamentals und SQL Fundamentals aus, um deine Kenntnisse über das Verbinden von Tabellen und die Datenanalyse zu verbessern.

Fazit

SQL Stored Procedures verbessern die Wiederverwendbarkeit von Code und die Leistungsoptimierung im Datenbankmanagement. Stored Procedures erhöhen auch die Sicherheit der Datenbank durch kontrollierten Zugriff und die Gewährleistung der Datenintegrität. Als Datenexperte ermutige ich dich, das Erstellen und Ausführen von Stored Procedures zu üben, um die besten Praktiken der Datenbankverwaltung zu beherrschen.

Wenn du ein erfahrener Datenanalyst werden möchtest, schau dir unseren Lernpfad zum Associate Data Analyst in SQL an, um die notwendigen Fähigkeiten zu erlernen. Der Kurs Reporting in SQL ist auch geeignet, wenn du lernen willst, wie man professionelle Dashboards mit SQL erstellt. Schließlich empfehle ich dir, die SQL Associate-Zertifizierung zu erlangen, um zu zeigen, dass du SQL für die Datenanalyse beherrschst und dich von anderen Datenexperten abhebst.