Gespeicherte Prozeduren helfen bei der Datenbankoptimierung und der Verbesserung der Wiederverwendbarkeit. Anstatt dieselbe Abfrage ausführen zu müssen, können Sie die Aufgabe als gespeicherte Prozedur erstellen, die Sie bei Bedarf aufrufen. Am Ende dieses Beitrags erfahren Sie alles über gespeicherte Prozeduren.
Arbeiten mit gespeicherten Prozeduren in PostgreSQL
Als PostgreSQL-Benutzer haben Sie möglicherweise bemerkt, dass PostgreSQL-Funktionen keine Transaktionen ausführen. Es ist zwar möglich, eine Transaktion zu erstellen, es ist jedoch nicht möglich, sie festzuschreiben oder auf den vorherigen Status zurückzusetzen. Diese Einschränkungen werden jedoch mithilfe gespeicherter Prozeduren umgangen.
Hier ist die grundlegende Syntax zum Erstellen einer gespeicherten Prozedur in PostgreSQL:
PROZEDUR ERSTELLEN ODER ERSETZEN prozedurname(
Parameter[s] data_type
)
SPRACHE plpsql;
AS $$
ERKLÄREN
variables_if_any data_type
BEGINNEN
Logik
ENDE;
$$
Die wichtigsten Dinge, die Sie bei der angegebenen Syntax beachten sollten, sind der „Prozedurname“, den Namen, den Sie für die gespeicherte Prozedur verwenden, die Parameter, die Sie einbeziehen möchten, und ihre Datentypen sowie die Logik, bei der es sich hauptsächlich um die SQL-Anweisungen handelt.
Lassen Sie uns drei Beispiele geben, die Ihnen helfen zu verstehen, wie Sie die gespeicherten Prozeduren in PostgreSQL erstellen.
Beispiel 1: Eine gespeicherte Prozedur zur Berechnung des Quadrats einer Zahl
Für unser erstes Beispiel erstellen wir eine gespeicherte Prozedur, die die „RAISE NOTICE“-Anweisung verwendet, um die Ausgabe auf dem Terminal zu drucken. Die gespeicherte Prozedur nimmt den ganzzahligen Wert, den Sie ihr beim Aufruf geben, und berechnet ihr Quadrat.
So erstellen wir die gespeicherte Prozedur:
Wir nennen unseren Parameter „num1“ und es ist eine Ganzzahl. Für den Logikteil definieren wir, wie er das Quadrat von „num1“ erhält und es als Quadratvariable speichert. Wenn wir den Befehl ausführen, erhalten wir die Ausgabe „CREATE PROCEDURE“, die bestätigt, dass es uns gelungen ist, die gespeicherte Prozedur erfolgreich zu erstellen.
Die nächste Aufgabe besteht darin, die Prozedur aufzurufen und ihr das erwartete Argument zu geben.
CALL procedure_name(arguments);Sie erhalten die CALL-Ausgabe, die zeigt, dass die gespeicherte Prozedur ausgeführt wurde, und wir erhalten die erwartete Ausgabe, die in diesem Fall das Quadrat des von uns hinzugefügten Arguments ist.
Beispiel 2: Eine gespeicherte Prozedur zum Einfügen der Werte in einen Tabelleneintrag
Die folgenden beiden Beispiele zeigen, wie Sie eine gespeicherte Prozedur erstellen, die mit einer Datenbanktabelle arbeitet. Lassen Sie uns schnell die „Studenten“-Tabelle erstellen, mit der wir arbeiten werden.
Für dieses Beispiel erstellen wir eine gespeicherte Prozedur, die es einem Benutzer ermöglicht, die Werte in die neu erstellte Tabelle einzufügen. Beachten Sie, wie wir die Parameter angeben, die voraussichtlich als Argumente hinzugefügt werden, wenn wir die gespeicherte Prozedur aufrufen. Darüber hinaus definieren wir die Logik, die die hinzugefügten Argumente übernimmt und eine INSERT-SQL-Anweisung für die Tabelle „students“ ausführt.
Wir können die verfügbaren gespeicherten Prozeduren überprüfen, indem wir den folgenden Befehl ausführen:
\dfDie erste gespeicherte Prozedur, die wir aus der folgenden Ausgabe sehen können, ist „add_student“, die wir zuvor erstellt haben.
Rufen wir nun die gespeicherte Prozedur auf, um sie auszuführen. Das folgende Bild zeigt, wie wir eine leere Tabelle haben, aber wir haben die gespeicherte Prozedur aufgerufen, um den ersten Schüler hinzuzufügen:
Wenn wir die Werte in unserer Tabelle auflisten, beachten Sie, dass die Argumente, die wir mit dem Befehl „call procedure“ hinzugefügt haben, die Werte für unseren ersten Schüler in unserer Tabelle sind. So erstellen Sie eine gespeicherte Prozedur, um die Werte in eine Tabelle einzufügen.
Beachten Sie, dass beim Erstellen der gespeicherten Prozedur die von Ihnen angegebenen Parameter mit den Erwartungen in Ihrer Tabelle übereinstimmen müssen, um Fehler zu vermeiden. Außerdem muss der Datentyp übereinstimmen.
Beispiel 3: Eine gespeicherte Prozedur zum Aktualisieren eines Tabelleneintrags
Als Nächstes erstellen wir eine weitere gespeicherte Prozedur, die einen Tabelleneintrag aktualisiert. Wenn Sie die Werte in unserer Tabelle schnell aktualisieren möchten, können Sie wie folgt eine gespeicherte Aktualisierungsprozedur erstellen:
Geben Sie mit dem Schlüsselwort WHERE an, welche Spalte Sie aktualisieren möchten, und geben Sie mit dem Schlüsselwort SET den neuen Wert an. Anschließend müssen Sie das Schlüsselwort COMMIT hinzufügen, um die Änderungen beizubehalten.
Rufen wir die gespeicherte Update-Prozedur auf und fügen die erwarteten Argumente hinzu: „student_id“ und den neuen Kurs.
Wenn wir die Einträge in unserer Tabelle auflisten, können wir überprüfen, ob wir über den aktualisierten Kurs für den bestimmten Studenten verfügen, den wir angesprochen haben. So funktioniert eine gespeicherte Aktualisierungsprozedur.
Abschluss
Sie können jede gespeicherte Prozedur in PostgreSQL erstellen. Sie müssen lediglich die zu befolgende Syntax verstehen und dann Ihre Logik für die gespeicherte Prozedur definieren. Rufen Sie von dort aus die gespeicherte Prozedur auf und überprüfen Sie, ob sie wie erwartet ausgeführt wurde. In diesem Beitrag werden die gespeicherten Prozeduren in PostgreSQL erläutert und Beispiele für deren Erstellung bereitgestellt.