SQL-Gespeicherte Prozedur: Abfragen automatisieren und optimieren

Tutorials
SQL

SQL gespeicherte Prozeduren sind Sets von SQL-Anweisungen, die in einer Datenbank gespeichert sind. Sie können auf Abruf ausgeführt werden, um Datenmanipulationen und Validierungsaufgaben durchzuführen, was die Notwendigkeit verringert, wiederholten SQL-Code für häufige Operationen zu schreiben. Gespeicherte Prozeduren sind hilfreich bei der Datenbankverwaltung, da sie Effizienz und Wiederverwendbarkeit fördern. Darüber hinaus unterstützen sie eine verbesserte Datenbanksicherheit und -wartbarkeit. In diesem Artikel werden wir besprechen, wie man SQL gespeicherte Prozeduren erstellt und ausführt, häufige Anwendungsfälle und bewährte Praktiken.

Wenn wir anfangen, empfehle ich dringend, die Kurse Einführung in SQL und SQL lernen von DataCamp zu belegen, um das grundlegende Wissen über das Extrahieren und Analysieren von Daten mit SQL zu erlernen. Außerdem ist das SQL Basics Cheat Sheet, das Sie herunterladen können, eine hilfreiche Referenz, da es alle gängigsten SQL-Funktionen enthält.

Was ist eine gespeicherte Prozedur in SQL?

Eine gespeicherte Prozedur in SQL ist eine Sammlung von SQL-Anweisungen, die innerhalb der Datenbank gespeichert ist. Der Zweck der SQL gespeicherten Prozedur besteht darin, eine Abfolge von Operationen auf einer Datenbank auszuführen, wie zum Beispiel Abfragen, Einfügen, Aktualisieren oder Löschen von Daten.

Im Gegensatz zu regulären SQL-Abfragen, die als separate Befehle ausgeführt werden, kapseln gespeicherte Prozeduren eine Reihe von SQL-Anweisungen, was es einfach macht, den Code wiederzuverwenden, ohne SQL-Befehle wiederholt schreiben zu müssen.

Die Vorteile von SQL gespeicherten Prozeduren sind wie folgt:

  • Code-Wiederverwendbarkeit: Sobald eine gespeicherte Prozedur erstellt ist, kann sie so oft aufgerufen werden, wie nötig, wodurch Redundanzen im SQL-Code vermieden werden.
  • Verbesserte Leistung: Gespeicherte Prozeduren werden oft schneller ausgeführt, da sie vorab kompiliert und auf dem Datenbankserver gespeichert sind, was die Netzwerklatenz und Kompilierungszeit reduziert.
  • Sicherheit: Gespeicherte Prozeduren können die Datensicherheit verbessern und die Kontrolle über den Zugriff auf sensible Daten erhöhen, indem Benutzern die Berechtigung erteilt wird, eine gespeicherte Prozedur auszuführen, ohne direkten Zugriff auf die Tabellen zu haben.

Grundlegende Syntax und Struktur

Die Syntax zum Erstellen einer gespeicherten Prozedur kann je nach Datenbanksystem (z. B. MySQL, SQL Server, Oracle) leicht variieren. Im Folgenden finden Sie ein allgemeines Beispiel unter Verwendung der SQL Server-Syntax:

-- Erstelle eine gespeicherte Prozedur mit dem Namen ProcedureName
CREATE PROCEDURE ProcedureName 
    @Parameter1 INT, 
    @Parameter2 VARCHAR(50)  
AS
BEGIN
    -- SQL-Anweisungen kommen hierhin
    SELECT * FROM TableName WHERE Column1 = @Parameter1 AND Column2 = @Parameter2;
END;

In der obigen Syntax;

  • ERSTELLEN VON PROCEDURE: Dieser Befehl wird verwendet, um eine neue gespeicherte Prozedur zu definieren.

  • ProcedureName: Der Name, der der gespeicherten Prozedur gegeben wird. Er sollte innerhalb der Datenbank eindeutig sein.

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

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

Eingabe- und Ausgabeparameter

Die Eingabe- und Ausgabeparameter ermöglichen es Ihnen, Werte an eine gespeicherte Prozedur zu übergeben und von ihr zu empfangen.

Zum Beispiel, wenn @Parameter1 als Eingabeparameter definiert ist, kann ihm bei Aufruf der Prozedur jeder Wert zugewiesen werden, was die SQL-Logik oder das Ergebnis beeinflusst. Im folgenden Beispiel ruft der Parameter @UserID spezifische Daten für die angegebene UserID ab.

-- Erstelle eine Prozedur, um Daten für einen bestimmten Benutzer nach UserID abzurufen
CREATE PROCEDURE GetUserData 
 -- Eingabeparameter: ID des abzurufenden Benutzers
    @UserID INT 
AS
BEGIN
    -- Wähle alle Spalten aus Users, wo UserID mit dem Eingabeparameter übereinstimmt
    SELECT * FROM Users WHERE UserID = @UserID;
END;

Ausgabeparameter, die durch das Schlüsselwort OUTPUT definiert sind, ermöglichen es einer gespeicherten Prozedur, einen Wert an die aufrufende Umgebung zurückzugeben. Zum Beispiel, wenn eine Prozedur einen Rabatt berechnet, könnte sie einen Ausgabeparameter verwenden, um ihn an das aufrufende Programm zurückzugeben.

-- Erstelle eine Prozedur zur Berechnung des rabattierten Preises
CREATE PROCEDURE CalculateDiscount 
    @Price DECIMAL(10, 2), 
    @DiscountRate DECIMAL(5, 2),  
    @FinalPrice DECIMAL(10, 2) OUTPUT -- Ausgabe: Endpreis nach Rabatt
AS
BEGIN
    -- Berechne den Endpreis, indem der Rabatt auf den ursprünglichen Preis angewendet wird
    SET @FinalPrice = @Price * (1 - @DiscountRate);
END;

Um dieses Verfahren aufzurufen, würden Sie verwenden:

-- Deklarieren Sie eine Variable, um den endgültigen Preis nach dem Rabatt zu speichern
DECLARE @FinalPrice DECIMAL(10, 2);
-- Führen Sie das CalculateDiscount-Verfahren mit einem Preis von 100 und einem Rabatt von 10% aus
-- Speichern Sie das Ergebnis in der Variablen @FinalPrice
EXEC CalculateDiscount @Price = 100, @DiscountRate = 0.1, @FinalPrice = @FinalPrice OUTPUT;
-- Wählen Sie den endgültigen rabattierten Preis aus und zeigen Sie ihn an
SELECT @FinalPrice AS FinalPrice;

Gespeicherte Prozeduren in MySQL

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

Erstellen von gespeicherten Prozeduren in MySQL

Das Erstellen einer gespeicherten Prozedur in MySQL umfasst die Definition des Namens der Prozedur, der Parameter und der SQL-Anweisungen, die ihren Körper bilden. Das folgende Beispiel erstellt eine Prozedur namens GetEmployeeDetails, die EmployeeID als Eingabeparameter erhält und Details für diesen spezifischen Mitarbeiter abruft.

DELIMITER $
-- Erstellen Sie eine Prozedur, um Details für einen bestimmten Mitarbeiter anhand der EmployeeID abzurufen
CREATE PROCEDURE GetEmployeeDetails(IN EmployeeID INT)  
BEGIN  
    -- Wählen Sie alle Spalten aus Employees aus, bei denen die EmployeeID mit dem Eingabeparameter übereinstimmt
    SELECT * FROM Employees WHERE EmployeeID = EmployeeID;  
END$
DELIMITER ;

Ausführen von gespeicherten Prozeduren in MySQL

Der gebräuchlichste Weg, eine gespeicherte Prozedur auszuführen, ist die Verwendung von SQL-Befehlen. In MySQL verwenden wir den CALL-Befehl, um die gespeicherte Prozedur auszuführen.

CALL ProcedureName();

Mit der definierten GetEmployeeDetails-Prozedur würde die Ausführungsabfrage folgendermaßen aussehen:

-- Führen Sie die gespeicherte Prozedur aus, um Details für EmployeeID 101 abzurufen
CALL GetEmployeeDetails(101);

Gespeicherte Prozeduren in SQL Server

SQL Server bietet spezifische Syntax und Befehle zum Erstellen, Ausführen und Verwalten von gespeicherten Prozeduren. Dieser Ansatz erleichtert den Aufbau effizienter und wiederverwendbarer SQL-Routinen, die komplexe Aufgaben mit minimaler Wiederholung bewältigen können.

Erstellen von gespeicherten Prozeduren in SQL Server

Das Erstellen einer gespeicherten Prozedur in SQL Server umfasst die Definition des Namens der Prozedur, der Parameter und der SQL-Anweisungen, die den Körper ausmachen. Das folgende Beispiel erstellt eine Prozedur namens GetEmployeeDetails, die @EmployeeID als Eingabeparameter verwendet und Details für diesen spezifischen Mitarbeiter abruft.

-- Erstellen Sie eine Prozedur, um Details für einen bestimmten Mitarbeiter anhand der EmployeeID abzurufen
CREATE PROCEDURE GetEmployeeDetails
    @EmployeeID INT          -- Eingabeparameter: ID des abzurufenden Mitarbeiters
AS
BEGIN
    -- Wählen Sie alle Spalten aus Employees aus, bei denen die EmployeeID mit dem Eingabeparameter übereinstimmt
    SELECT * FROM Employees WHERE EmployeeID = @EmployeeID;
END;

Das Ausführen gespeicherter Prozeduren in SQL Server

In SQL Server ruft der Befehl EXEC oder EXECUTE eine gespeicherte Prozedur auf. Das folgende Beispiel zeigt, wie man die gespeicherte Prozedur GetEmployeeDetails mit spezifischen Eingabeparametern ausführt.

-- Führen Sie die GetEmployeeDetails-Prozedur aus, wobei die EmployeeID auf 102 gesetzt ist
EXEC GetEmployeeDetails @EmployeeID = 102;

Sie können auch die Ausgabeparameter ausführen, indem Sie die Variable im Befehl deklarieren. Im folgenden Beispiel wird @TotalSales als Variable deklariert, um die Ausgabe von CalculateTotalSales zu empfangen.

-- Deklarieren Sie eine Variable, um den Gesamtverkaufsbetrag zu speichern
DECLARE @TotalSales DECIMAL(10, 2);
-- Führen Sie CalculateTotalSales für SalespersonID 5 aus und speichern Sie das Ergebnis in @TotalSales
EXEC CalculateTotalSales @SalespersonID = 5, @TotalSales = @TotalSales OUTPUT;
-- Anzeigen des Gesamtverkaufsbetrags
SELECT @TotalSales AS TotalSales;

Ich empfehle, unseren Einführung in SQL Server Kurs zu belegen, um die verschiedenen Funktionen von SQL Server zum Abfragen von Daten zu verstehen. Ziehen Sie auch unseren vollständigen SQL Server Developer Karrierepfad in Betracht, der Sie nicht nur mit den Fähigkeiten ausstattet, gespeicherte Prozeduren zu erstellen, zu aktualisieren und auszuführen, sondern Ihnen auch bei Aggregatfunktionen, dem Verknüpfen, Einfügen und Löschen von Tabellen und vielem mehr hilft.

Häufige Anwendungen für gespeicherte Prozeduren

SQL gespeicherte Prozeduren sind nützlich in Szenarien, in denen wiederholte komplexe Aufgaben erforderlich sind. Die folgenden sind praktische Anwendungen von gespeicherten Prozeduren im Datenmanagement und in Geschäftsabläufen.

Datenvalidierung und Durchsetzung der Integrität

Gespeicherte Prozeduren können verwendet werden, um Daten vor Updates oder Einfügungen zu validieren. Im folgenden Beispiel überprüft eine gespeicherte Prozedur, ob die E-Mail eines Kunden einzigartig ist, bevor ein neuer Datensatz in die Tabelle Customers eingefügt wird, um die Datenkonsistenz sicherzustellen. Dies zentralisiert die Validierungslogik innerhalb der Datenbank, reduziert Redundanz und stellt eine einheitliche Durchsetzung über verschiedene Anwendungen hinweg sicher.

-- Erstelle eine Prozedur, um einen neuen Kunden hinzuzufügen, und überprüfe auf doppelte E-Mail-Adressen
CREATE PROCEDURE AddCustomer
    @CustomerName VARCHAR(50), 
    @CustomerEmail VARCHAR(50)
AS
BEGIN
    -- Überprüfe, ob die E-Mail bereits in der Kundentabelle existiert
    IF EXISTS (SELECT 1 FROM Customers WHERE Email = @CustomerEmail)
        -- Wirf einen Fehler, wenn die E-Mail bereits in Gebrauch ist
        THROW 50000, 'Email already exists.', 1;
    ELSE
        -- Füge die Details des neuen Kunden hinzu, wenn die E-Mail einzigartig ist
        INSERT INTO Customers (Name, Email) VALUES (@CustomerName, @CustomerEmail);
END;

Automatisierte Datenverarbeitung und Berichterstattung

Sie können auch gespeicherte Prozeduren verwenden, um regelmäßige Berichte zu erstellen oder große Datensätze zu verarbeiten. Zum Beispiel könnte eine gespeicherte Prozedur tägliche Verkaufsdaten von einer E-Commerce-Plattform aggregieren und in einer Berichtstabelle speichern, was es den Teams erleichtert, Verkaufsanalysen zuzugreifen, ohne komplexe Abfragen ausführen zu müssen.

-- Erstellen Sie eine Prozedur zur Generierung eines täglichen Verkaufsberichts
CREATE PROCEDURE GenerateDailySalesReport
AS
BEGIN
    -- Fügen Sie das heutige Datum und die Gesamteinnahmen in die SalesReport-Tabelle ein
    INSERT INTO SalesReport (ReportDate, TotalSales)
    
    -- Wählen Sie das aktuelle Datum und die Summe der Verkäufe für heute aus der Verkaufs-Tabelle aus
    SELECT CAST(GETDATE() AS DATE), SUM(SalesAmount) 
    FROM Sales 
    WHERE SaleDate = CAST(GETDATE() AS DATE);
END;

Transaktionsmanagement

Durch die Verwendung von gespeicherten Prozeduren können Sie sicherstellen, dass mehrere Operationen als eine einzige Transaktion ausgeführt werden. Zum Beispiel kann in einem Banksystem eine gespeicherte Prozedur sowohl Debit- als auch Kreditaktionen bei einem Geldtransfer verwalten und sicherstellen, dass beide Aktionen zusammen erfolgreich sind oder zusammen fehlschlagen.

-- Erstellen Sie ein Verfahren zur Übertragung von Geldern zwischen Konten
CREATE PROCEDURE TransferFunds
    @SenderAccount INT,
    @ReceiverAccount INT,
    @Amount DECIMAL(10, 2)
AS
BEGIN
    BEGIN TRANSACTION;  -- Starten Sie eine Transaktion, um Atomizität sicherzustellen
    -- Ziehen Sie den angegebenen Betrag vom Kontostand des Absenders ab
    UPDATE Accounts SET Balance = Balance - @Amount WHERE AccountID = @SenderAccount;
    -- Fügen Sie den angegebenen Betrag dem Kontostand des Empfängers hinzu
    UPDATE Accounts SET Balance = Balance + @Amount WHERE AccountID = @ReceiverAccount;
    -- Überprüfen Sie auf Fehler und machen Sie bei Auftreten eines Fehlers eine Rückabwicklung; andernfalls bestätigen Sie die Transaktion
    IF @@ERROR <> 0
        ROLLBACK TRANSACTION;  -- Machen Sie alle Änderungen rückgängig, wenn ein Fehler aufgetreten ist
    ELSE
        COMMIT TRANSACTION;    -- Bestätigen Sie die Änderungen, wenn keine Fehler aufgetreten sind
END;

Zugriffskontrolle und Datensicherheit

Sie können auch SQL-Prozeduren verwenden, um den Datenzugriff auf sensible Informationen zu steuern. Zum Beispiel kann eine gespeicherte Prozedur den direkten Tabellenzugriff einschränken, indem sie es Benutzern erlaubt, eine Prozedur aufzurufen, die nur relevante Felder wie Kontostände abruft, ohne Transaktionsdetails.

-- Erstellen Sie eine Prozedur zum Abrufen des Kontostands mit Berechtigungsprüfung
CREATE PROCEDURE GetAccountBalance
    @AccountID INT, 
    @UserID INT 
AS
BEGIN
    -- Überprüfen Sie, ob das Konto existiert und dem angegebenen Benutzer gehört
    IF EXISTS (SELECT 1 FROM Accounts WHERE AccountID = @AccountID AND UserID = @UserID)
        -- Wenn autorisiert, wählen Sie den Kontostand aus und geben Sie ihn zurück
        SELECT Balance FROM Accounts WHERE AccountID = @AccountID;
    ELSE
        -- Wenn nicht autorisiert, werfen Sie einen Fehler
        THROW 50000, 'Unauthorized access.', 1;
END;

Datenmigration und ETL-Prozesse

Gespeicherte Prozeduren werden auch verwendet, um Daten zwischen Systemen zu laden, zu transformieren und zu migrieren. Eine gespeicherte Prozedur kann die Datenaus extraction aus einer Quelldatenbank automatisieren, sie nach Bedarf transformieren und in eine Zieltabelle einfügen, was die Datenintegration für Berichterstattung oder Analyse vereinfacht.

CREATE PROCEDURE ETLProcess
AS
BEGIN
    -- Extrahieren
    INSERT INTO StagingTable
    SELECT * FROM SourceTable WHERE Condition;
    -- Transformieren
    UPDATE StagingTable SET ColumnX = TransformationLogic(ColumnX);
    -- Laden
    INSERT INTO TargetTable
    SELECT * FROM StagingTable;
END;

Best Practices für gespeicherte Prozeduren

Effiziente und wartbare gespeicherte Prozeduren zu schreiben, sorgt dafür, dass Ihre Datenbank optimal funktioniert. Die folgenden Tipps helfen Ihnen beim Schreiben von gespeicherten Prozeduren für Ihre SQL-Datenbanken.

  • Verwenden Sie konsistente Namenskonventionen: Verwenden Sie ein konsistentes und beschreibendes Namensformat, um gespeicherte Prozeduren leicht identifizierbar und verständlich zu machen. Vermeiden Sie auch das Präfix sp_ in SQL Server, das für Systemprozeduren reserviert ist, um mögliche Konflikte und Leistungsprobleme zu vermeiden.

  • Fehlerbehandlung implementieren: SQL-Anweisungen in TRY...CATCH-Blöcke einfügen, um Fehler zu erfassen und zu behandeln sowie die Datenintegrität zu wahren. 

  • Für die Leistung optimieren: Minimieren Sie die Verwendung von Cursors, da diese langsam und ressourcenintensiv sein können. Versuchen Sie stattdessen, setbasierte Operationen zu verwenden, die in der Regel effizienter sind. Indizieren Sie auch häufig verwendete Spalten und vermeiden Sie komplexe Joins in großen Tabellen, um den Speicheraufwand zu reduzieren und die Effizienz zu verbessern.

  • Parameterisieren von gespeicherten Prozeduren: Verwenden Sie Parameter anstelle von fest codierten Werten, um dynamische Werte in Ihre Prozedur zu übergeben, was sie flexibler und wiederverwendbar macht.

Werfen Sie einen Blick auf unseren Kurs Intermediate SQL, um mehr über die Verwendung von Aggregatfunktionen und Joins zur Filterung von Daten zu erfahren. Probieren Sie auch unsere Skill-Tracks SQL Server Fundamentals und SQL Fundamentals aus, um Ihre Fähigkeiten bei Tabellenverknüpfungen und Datenanalyse zu verbessern.

Schlussfolgerung

SQL gespeicherte Prozeduren verbessern die Code-Wiederverwendbarkeit und die Leistungsoptimierung im Datenbankmanagement. Gespeicherte Prozeduren erhöhen auch die Datenbanksicherheit durch kontrollierten Zugriff und die Gewährleistung der Datenintegrität. Als Datenpraktiker ermutige ich Sie, das Erstellen und Ausführen von gespeicherten Prozeduren zu üben, um die besten Praktiken im Datenbankmanagement zu beherrschen.

Wenn Sie daran interessiert sind, ein kompetenter Datenanalyst zu werden, schauen Sie sich unseren Associate Data Analyst in SQL Karrierepfad an, um die notwendigen Fähigkeiten zu erlernen. Der Reporting in SQL Kurs ist ebenfalls geeignet, wenn Sie lernen möchten, wie man professionelle Dashboards mit SQL erstellt. Schließlich empfehle ich, die SQL Associate Certification zu erwerben, um Ihr Können im Umgang mit SQL für die Datenanalyse zu demonstrieren und sich von anderen Datenprofis abzuheben.

Source:
https://www.datacamp.com/tutorial/sql-stored-procedure