Was sind gespeicherte Prozeduren?

SQL (Structured Query Language) ist eine Standardsprache zur Verwaltung und zum Betrieb relationaler Datenbanken. Eine seiner leistungsstarken und häufig verwendeten Funktionen ist die gespeicherte Prozedur. Eine gespeicherte Prozedur ist eine Reihe von SQL-Anweisungen, die vorkompiliert und in der Datenbank gespeichert werden und Eingabeparameter akzeptieren, Operationen ausführen und Ergebnisse zurückgeben können. Lassen Sie uns untersuchen, was eine gespeicherte Prozedur ist und wie man eine erstellt.

Einführung in gespeicherte Prozeduren

Gespeicherte Prozeduren mögen wie ein komplexer Begriff klingen, aber sie sind die Grundlage einer effizienten Datenbankverwaltung. Beginnen wir mit seiner Definition.

Was ist eine gespeicherte Prozedur?

Eine gespeicherte Prozedur ist eine Reihe von SQL-Anweisungen, die vordefiniert und auf dem Datenbankserver gespeichert sind. Wenn Sie diese Vorgänge ausführen müssen, können Sie sie ausführen, indem Sie den Namen der gespeicherten Prozedur aufrufen, anstatt mehrere separate Abfragebefehle zu senden.

Hier ist ein vereinfachtes Beispiel, das zeigt, wie eine einfache gespeicherte Prozedur in SQL Server erstellt wird:

CREATE PROCEDURE procedure_name
AS
BEGIN
   -- SQL statements
END

Hier sind einige der Schlüsselkomponenten einer gespeicherten Prozedur:

• Eingabeparameter: Dies sind Werte, die von außen an eine gespeicherte Prozedur übergeben werden, um das Verhalten der gespeicherten Prozedur anzupassen. Eingabeparameter ermöglichen einer gespeicherten Prozedur, unterschiedliche Aktionen basierend auf unterschiedlichen Bedingungen auszuführen.
• Ausgabeparameter: Ähnlich wie Eingabeparameter sind auch Ausgabeparameter Teil einer gespeicherten Prozedur, ihre Rolle besteht jedoch darin, Werte an den Aufrufer zurückzugeben, anstatt Werte zu empfangen.
• Lokale Variablen: Dies sind Variablen, die innerhalb einer gespeicherten Prozedur deklariert werden und zum Speichern von Zwischenergebnissen oder berechneten Werten während der Ausführung verwendet werden. Lokale Variablen sind nur im Kontext einer gespeicherten Prozedur sichtbar und können während ihrer Lebensdauer mehrfach zugewiesen werden.
• SQL-Anweisungen: Diese bilden die Kernlogik einer gespeicherten Prozedur, einschließlich, aber nicht beschränkt auf das Abfragen, Einfügen, Aktualisieren und Löschen von Daten.

Diese Komponenten arbeiten zusammen, um gespeicherte Prozeduren zu einer wiederverwendbaren und effizienten Möglichkeit zur Durchführung von Datenbankoperationen zu machen. Durch die Kapselung allgemeiner Datenbankaufgaben in gespeicherten Prozeduren können Sie die Anwendungsentwicklung vereinfachen und gleichzeitig die Leistung und Sicherheit verbessern.

Wie gespeicherte Prozeduren funktionieren

Gespeicherte Prozeduren werden innerhalb des Datenbankservers ausgeführt, was bedeutet, dass sie Vorgänge effizienter und schneller abschließen können, als wenn mehrere Abfragen nacheinander vom Client gesendet würden. Darüber hinaus kann die Verwendung gespeicherter Prozeduren den Netzwerkverkehr erheblich reduzieren, da nur die endgültige Ergebnismenge vom Server zum Client übertragen werden muss und nicht die Ergebnisse jeder einzelnen Abfrage hin und her. Auf diese Weise wird nicht nur die Geschwindigkeit der Datenverarbeitung verbessert, sondern auch die Nutzung der Netzwerkbandbreite reduziert.

Rolle im Datenbankmanagement

Gespeicherte Prozeduren spielen eine zentrale Rolle bei der Datenbankverwaltung, da sie die Geschäftslogik zentral auf dem Datenbankserver speichern. Dadurch wird sichergestellt, dass kritische Vorgänge stets konsistent, sicher und effizient ausgeführt werden. Konkret helfen gespeicherte Prozeduren:

• Aufrechterhaltung der Datenintegrität: Indem sie sicherstellen, dass alle Datenvorgänge vorgegebenen Regeln und Einschränkungen folgen, tragen gespeicherte Prozeduren zur Aufrechterhaltung der Datenintegrität und -konsistenz bei.
• Durchsetzung der Geschäftslogik: Durch die Kapselung komplexer Geschäftsregeln in gespeicherten Prozeduren wird sichergestellt, dass diese Regeln strikt durchgesetzt werden und nicht durch Änderungen im Client-Code beeinträchtigt werden.
• Vereinfachung der Datenbankinteraktion: Durch die Bereitstellung einer Schnittstelle, die komplexe Vorgänge kapselt, reduzieren gespeicherte Prozeduren die Komplexität der Interaktion zwischen Anwendung und Datenbank und erleichtern so die Entwicklung und Wartung.

Vorteile der Verwendung gespeicherter Prozeduren

Die Verwendung gespeicherter Prozeduren bietet mehrere wesentliche Vorteile:

1. Verbesserte Leistung:

• Vorkompilierte gespeicherte Prozeduren werden schneller ausgeführt.
• Verbesserte Reaktionsgeschwindigkeit und effizientere Nutzung von Serverressourcen.

1. Wiederverwendbarkeit und Wartbarkeit:

• Gespeicherte Prozeduren können mehrmals aufgerufen werden, um die Codeduplizierung zu reduzieren.
• Aktualisierungen gespeicherter Prozeduren werden an allen Orten wirksam, an denen sie verwendet werden, wodurch Konsistenz gewährleistet und Fehler reduziert werden.

1. Datensicherheit:

• Kontrollieren Sie den Datenbankzugriff und beschränken Sie die Möglichkeit, Tabellen direkt zu bedienen.
• Stellen Sie durch gespeicherte Prozeduren eine Sicherheitsschicht bereit, um unbefugten Zugriff und böswillige Angriffe zu verhindern.

Allgemeine Befehle, die mit gespeicherten Prozeduren verwendet werden

Sehen wir uns nun nützliche Befehle an, die mit gespeicherten Prozeduren gekoppelt sind.

VERFAHREN ERSTELLEN

Wie bereits erwähnt, wird dieser Befehl verwendet, um eine neue gespeicherte Prozedur in der Datenbank zu definieren. Hier ist ein Beispiel für eine gespeicherte Prozedur, die diese Funktion verwendet:

Angenommen, wir haben eine Tabelle namens „Mitarbeiter“ mit den folgenden Spalten:

• Mitarbeiter-ID
• Vorname
• Nachname
• Abteilungs-ID
• Gehalt

Wir möchten eine gespeicherte Prozedur erstellen, um alle Mitarbeiter einer bestimmten Abteilung abzurufen.

CREATE PROCEDURE procedure_name
AS
BEGIN
   -- SQL statements
END

EXEC

Mit diesem Befehl wird eine gespeicherte Prozedur ausgeführt. Es kann auch zur Übergabe von Eingabe- und Ausgabeparametern verwendet werden. Für unser vorheriges Beispiel würde der Befehl „EXEC“ so aussehen:

CREATE PROCEDURE GetEmployeesByDepartment
   @DepartmentID INT
AS
BEGIN
   SELECT EmployeeID, FirstName, LastName, DepartmentID, Salary
   FROM Employees
   WHERE DepartmentID = @DepartmentID;
END;

VERFAHREN ÄNDERN

Mit diesem Befehl können Sie eine vorhandene gespeicherte Prozedur ändern, ohne sie löschen und neu erstellen zu müssen. Wenn wir mit dem vorherigen Beispiel fortfahren und die gespeicherte Prozedur mit dem Namen „GetEmployeesByDepartment“ ändern möchten, um einen zusätzlichen Gehaltsfilter hinzuzufügen, möchten wir Informationen über Mitarbeiter in einer bestimmten Abteilung abrufen, deren Gehalt einen bestimmten Betrag übersteigt.

Hier ist ein Beispiel:

EXEC GetEmployeesByDepartment @DepartmentID = 1;

DROP-VERFAHREN

Wenn eine gespeicherte Prozedur nicht mehr benötigt wird, können Sie sie mit dem Befehl DROP PROCEDURE aus der Datenbank entfernen.

ALTER PROCEDURE GetEmployeesByDepartment
   @DepartmentID INT,
   @MinSalary DECIMAL(10, 2)
AS
BEGIN
   SELECT EmployeeID, FirstName, LastName, DepartmentID, Salary
   FROM Employees
   WHERE DepartmentID = @DepartmentID AND Salary > @MinSalary;
END;

So erstellen und verwenden Sie gespeicherte Prozeduren

Wir werden uns mit der Erstellung und Verwendung gespeicherter Prozeduren in drei Bereichen befassen:

• MySQL
• SQL Server
• Orakel

MySQL

Das Erstellen gespeicherter Prozeduren in MySQL ist ziemlich einfach. Sie definieren die Prozedur, geben Parameter an und schreiben SQL-Code mit der Anweisung „CREATE PROCEDURE“.

Sie können dies tun:

Schritt 1: Erstellen Sie eine Mitarbeitertabelle

Erstellen wir zunächst eine Beispiel-Mitarbeitertabelle, die wir mit den Daten füllen, die wir verwenden werden.

DROP PROCEDURE GetEmployeesByDepartment

Schritt 2: Beispieldaten einfügen

Fügen Sie einige Beispieldaten in die Tabelle „Mitarbeiter“ ein.

CREATE TABLE Employees (
   EmployeeID INT PRIMARY KEY AUTO_INCREMENT,
   FirstName VARCHAR(50),
   LastName VARCHAR(50),
   DepartmentID INT,
   Salary DECIMAL(10, 2)
);

Schritt 3: Erstellen Sie eine gespeicherte Prozedur

Lassen Sie uns eine gespeicherte Prozedur erstellen, um Mitarbeiter basierend auf ihrer Abteilung abzurufen.

INSERT INTO Employees (FirstName, LastName, DepartmentID, Salary)
VALUES
('John', 'Doe', 1, 60000),
('Jane', 'Smith', 2, 65000),
('Sam', 'Brown', 1, 62000),
('Sue', 'Green', 3, 67000);

Schritt 4: Rufen Sie die gespeicherte Prozedur auf

Um die gespeicherte Prozedur aufzurufen und die Mitarbeiter einer bestimmten Abteilung abzurufen, verwenden Sie die CALL-Anweisung.

CREATE PROCEDURE GetEmployeesByDepartment(IN depID INT)
BEGIN
   SELECT EmployeeID, FirstName, LastName, DepartmentID, Salary
   FROM Employees
   WHERE DepartmentID = depID;
END;

SQL-Server

In SQL Server ist die Erstellung und Ausführung gespeicherter Prozeduren etwas anders, aber nicht drastisch geändert. Hier ist ein Beispiel:

Schritt 1: Erstellen Sie die Mitarbeitertabelle

Erstellen wir zunächst eine Beispieltabelle „Mitarbeiter“.

CALL GetEmployeesByDepartment(1);

Schritt 2: Beispieldaten einfügen

Als nächstes fügen wir einige Beispieldaten in die Tabelle „Mitarbeiter“ ein.

CREATE PROCEDURE procedure_name
AS
BEGIN
   -- SQL statements
END

Schritt 3: Erstellen Sie eine gespeicherte Prozedur

Lassen Sie uns eine gespeicherte Prozedur erstellen, um Mitarbeiter basierend auf ihrer Abteilung abzurufen.

CREATE PROCEDURE GetEmployeesByDepartment
   @DepartmentID INT
AS
BEGIN
   SELECT EmployeeID, FirstName, LastName, DepartmentID, Salary
   FROM Employees
   WHERE DepartmentID = @DepartmentID;
END;

Schritt 4: Führen Sie die gespeicherte Prozedur aus

Um die gespeicherte Prozedur auszuführen und die Mitarbeiter einer bestimmten Abteilung abzurufen, verwenden Sie die EXEC-Anweisung.

EXEC GetEmployeesByDepartment @DepartmentID = 1;

Orakel

Oracle unterstützt auch gespeicherte Prozeduren. Hier finden Sie eine Schritt-für-Schritt-Anleitung zur Implementierung in Oracle mithilfe von SQL.

Schritt 1: Erstellen Sie eine Mitarbeitertabelle

Erstellen wir zunächst eine Beispieltabelle „Mitarbeiter“.

ALTER PROCEDURE GetEmployeesByDepartment
   @DepartmentID INT,
   @MinSalary DECIMAL(10, 2)
AS
BEGIN
   SELECT EmployeeID, FirstName, LastName, DepartmentID, Salary
   FROM Employees
   WHERE DepartmentID = @DepartmentID AND Salary > @MinSalary;
END;

Schritt 2: Beispieldaten einfügen

Als nächstes fügen wir einige Beispieldaten in die Mitarbeitertabelle ein, um einen Datensatz zu erstellen.

DROP PROCEDURE GetEmployeesByDepartment

Schritt 3: Erstellen Sie eine gespeicherte Prozedur

Lassen Sie uns eine gespeicherte Prozedur erstellen, um Mitarbeiter basierend auf ihrer Abteilung abzurufen.

CREATE TABLE Employees (
   EmployeeID INT PRIMARY KEY AUTO_INCREMENT,
   FirstName VARCHAR(50),
   LastName VARCHAR(50),
   DepartmentID INT,
   Salary DECIMAL(10, 2)
);

Entwerfen gespeicherter Prozeduren: Best Practices

Nachdem wir diese praktische Einführung abgeschlossen haben, werfen wir einen Blick auf einige Best Practices für den Entwurf gespeicherter Prozeduren.

Verwendung parametrisierter Abfragen

Parametrierte Abfragen in gespeicherten Prozeduren tragen dazu bei, SQL-Injection-Angriffe zu verhindern. Verwenden Sie immer Parameter, anstatt Benutzereingaben direkt in SQL-Anweisungen zu verketten.

Verwenden Sie beispielsweise nicht Folgendes:

INSERT INTO Employees (FirstName, LastName, DepartmentID, Salary)
VALUES
('John', 'Doe', 1, 60000),
('Jane', 'Smith', 2, 65000),
('Sam', 'Brown', 1, 62000),
('Sue', 'Green', 3, 67000);

Verwenden Sie dies:

CREATE PROCEDURE GetEmployeesByDepartment(IN depID INT)
BEGIN
   SELECT EmployeeID, FirstName, LastName, DepartmentID, Salary
   FROM Employees
   WHERE DepartmentID = depID;
END;

Beschränken Sie den Zugriff auf zugrunde liegende Tabellen

Wie bereits erwähnt, können gespeicherte Prozeduren als Sicherheitsschicht fungieren, indem sie den direkten Zugriff auf zugrunde liegende Tabellen einschränken. Dies verringert das Risiko, dass sensible Daten offengelegt werden.

Optimieren Sie SQL-Code

Um sicherzustellen, dass gespeicherte Prozeduren effizient ausgeführt werden, sollten sie hinsichtlich der Leistung optimiert werden. Dies bedeutet, unnötige Berechnungen zu reduzieren und Indizes sinnvoll zu nutzen. Sie können die Abfrageeffizienz verbessern, indem Sie den Abfrageausführungsplan analysieren, um Leistungsengpässe zu identifizieren und zu beheben.

Sie sollten beispielsweise vermeiden, „SELECT *“ zum Abrufen aller Felder in einer Tabelle zu verwenden, da dies die übertragene Datenmenge erhöht und die Effizienz verringert. Stattdessen sollten Sie nur die Felder auswählen, die Sie benötigen, um den Umfang des Datenabrufs einzugrenzen und die Leistung zu verbessern.

Dokumentieren Sie Ihre gespeicherten Prozeduren

Die Dokumentation von Code gilt auch für das Schreiben gespeicherter Prozeduren. Dies ist wichtig, damit andere Entwickler die Rolle und Funktion jedes Verfahrens verstehen. Es fördert außerdem konsistente Namenskonventionen und Codierungsstile.

Dieser Prozess kann durch das Hinzufügen von Kommentaren zu gespeicherten Prozeduren oder das Führen einer separaten Dokumentation erreicht werden. Zum Beispiel:

CREATE PROCEDURE procedure_name
AS
BEGIN
   -- SQL statements
END

Behalten Sie die Versionskontrolle bei

Die Versionskontrolle ist für die Verwaltung und Nachverfolgung von Änderungen an gespeicherten Prozeduren von entscheidender Bedeutung. Es ist hilfreich, ein Repository zu verwalten, das den vollständigen Verlauf der Änderungen an gespeicherten Prozedurskripten und deren Dokumentation enthält. Dies macht es nicht nur einfacher, den Überblick über alle Änderungen zu behalten, sondern gewährleistet auch die Konsistenz über verschiedene Bereitstellungsumgebungen hinweg.

Letzte Gedanken

Gespeicherte Prozeduren sind ein effizientes und sicheres Mittel zur Datenbankverwaltung. Sie bieten eine Reihe von Vorteilen, die, wenn sie mit den richtigen Best Practices eingesetzt werden, die Effizienz und Effektivität der Datenanalyse innerhalb einer Organisation erheblich steigern können.

---

Gemeinschaft

Gehen Sie zur Chat2DB-Website
? Treten Sie der Chat2DB-Community bei
? Folgen Sie uns auf X
? Finden Sie uns auf Discord

Das obige ist der detaillierte Inhalt vonWas sind gespeicherte Prozeduren?. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!