Verwenden von gespeicherten Prozeduren¶

Gespeicherte Prozeduren dienen der Erstellung modularen Codes zur Abbildung komplexer Geschäftslogik durch Kombination mehrerer SQL-Anweisungen mit prozeduraler Logik.

Unter diesem Thema:

Bemerkung

Um eine anonyme Prozedur sowohl zu erstellen als auch aufzurufen, verwenden Sie CALL (mit anonymen Prozeduren). Das Erstellen und Aufrufen einer anonymen Prozedur erfordert keine Rolle mit CREATE PROCEDURE-Schemaberechtigungen.

Namenskonventionen für gespeicherte Prozeduren¶

Prozeduren müssen nach den von Snowflake vorgegebenen Konventionen benannt werden.

Weitere Informationen dazu finden Sie unter Benennen und Überladen von Prozeduren und UDFs.

Transaktionsmanagement¶

Gespeicherte Prozeduren sind nicht atomar. Wenn eine Anweisung in einer gespeicherten Prozedur fehlschlägt, werden die anderen Anweisungen in der gespeicherten Prozedur nicht unbedingt zurückgesetzt.

Sie können gespeicherte Prozeduren mit Transaktionen verwenden, um eine Gruppe von Anweisungen atomar zu machen. Weitere Details dazu finden Sie unter Gespeicherte Prozeduren und Transaktionen.

Allgemeine Hinweise¶

Symmetrischer Code¶

Wenn Sie mit Programmierung in Assemblersprache vertraut sind, ist die folgende Analogie möglicherweise hilfreich. In Assemblersprache erstellen und heben Funktionen ihre Umgebungen häufig symmetrisch auf. Beispiel:

-- Set up.
push a;
push b;
...
-- Clean up in the reverse order that you set up.
pop b;
pop a;

Copy

Möglicherweise möchten Sie folgenden Ansatz für Ihre gespeicherten Prozeduren verwenden:

Wenn eine gespeicherte Prozedur temporäre Änderungen an Ihrer Sitzung vornimmt, sollten diese Änderungen rückgängig gemacht werden, bevor die Rückgabe erfolgt.
Wenn eine gespeicherte Prozedur eine Ausnahmebehandlung oder Verzweigung oder eine andere Logik nutzt, die sich darauf auswirkt, welche Anweisungen ausgeführt werden, müssen Sie unabhängig davon, welche Verzweigungen Sie bei einem bestimmten Aufruf verwenden, alles bereinigen, was Sie erstellt haben.

Ihr Code könnte dann beispielsweise wie der folgende Pseudocode aussehen:

create procedure f() ...
    $$
    set x;
    set y;
    try  {
       set z;
       -- Do something interesting...
       ...
       unset z;
       }
    catch  {
       -- Give error message...
       ...
       unset z;
       }
    unset y;
    unset x;
    $$
    ;

Copy

Aufrufen einer gespeicherten Prozedur¶

Eine gespeicherte Prozedur wird mit einem SQL-Befehl aufgerufen. Weitere Informationen zum Aufrufen von gespeicherten Prozeduren finden Sie unter Aufrufen einer gespeicherten Prozedur.

Berechtigungen¶

Gespeicherte Prozeduren nutzen zwei Arten von Berechtigungen:

Berechtigungen direkt für die gespeicherte Prozedur selbst.
Berechtigungen für die Datenbankobjekte (z. B. Tabellen), auf die die gespeicherte Prozedur zugreift.

Berechtigungen für gespeicherte Prozeduren¶

Ähnlich wie bei anderen Datenbankobjekten (Tabellen, Ansichten, UDFs usw.) ist eine Rolle der Eigentümer einer gespeicherten Prozedur; dabei verfügt eine gespeicherte Prozedur über eine oder mehrere Berechtigungen, die anderen Rollen erteilt werden können.

Derzeit sind für gespeicherte Prozeduren die folgenden Berechtigungen gültig:

USAGE
OWNERSHIP

Damit eine Rolle eine gespeicherte Prozedur verwenden kann, muss die Rolle entweder Eigentümer sein oder über die Berechtigung USAGE für die gespeicherte Prozedur verfügen.

Berechtigungen für Datenbankobjekte, auf die gespeicherte Prozeduren zugreifen¶

Dieses Thema wird unter Understanding Caller’s Rights and Owner’s Rights Stored Procedures behandelt.

Hinweise zu gespeicherten Prozeduren¶

Obwohl gespeicherte Prozeduren Verschachtelung und Rekursion ermöglichen, beträgt die maximale Stapeltiefe von verschachtelten Aufrufen für benutzerdefinierte gespeicherte Prozeduren derzeit ungefähr 5 (einschließlich der gespeicherten Prozedur auf oberster Ebene) und kann geringer ausfallen, wenn einzelne gespeicherte Prozeduren in der Aufrufkette große Mengen an Ressourcen verbrauchen.
In seltenen Fällen kann es zu einem Deadlock kommen, wenn zu viele gespeicherte Prozeduren gleichzeitig aufgerufen werden.

Einschleusung von SQL-Befehlen¶

Gespeicherte Prozeduren können eine SQL-Anweisung dynamisch erstellen und ausführen. Dadurch werden jedoch Angriffe durch Einschleusung von SQL-Befehlen möglich, insbesondere wenn Sie die SQL-Anweisung mithilfe von Eingaben aus einer öffentlichen oder nicht vertrauenswürdigen Quelle erstellen.

Sie können das Risiko einer Einschleusung von SQL-Befehlen minimieren, indem Sie Parameter binden, anstatt Text zu verketten. Ein Beispiel für das Binden von Variablen finden Sie unter Binden von Variablen.

Falls Sie sich für eine Verkettung entscheiden, sollten Sie die Eingaben sorgfältig prüfen, wenn Sie SQL dynamisch mit Eingaben aus öffentlichen Quellen erstellen. Möglicherweise sollten Sie auch andere Vorsichtsmaßnahmen ergreifen, z. B. Abfragen mithilfe einer Rolle mit eingeschränkten Berechtigungen (z. B. Nur-Lese-Zugriff oder Zugriff auf bestimmte Tabellen oder Ansichten).

Weitere Informationen zu Angriffen durch Einschleusung von SQL finden Sie unter SQL-Injection (in Wikipedia).

Entwurfstipps für gespeicherte Prozeduren¶

Im Folgenden finden Sie einige Tipps zum Entwerfen einer gespeicherten Prozedur:

Welche Ressourcen, z. B. Tabellen, benötigt die gespeicherte Prozedur?
Welche Berechtigungen werden benötigt?

Überlegen Sie, auf welche Datenbankobjekte zugegriffen wird, von welchen Rollen Ihre gespeicherte Prozedur ausgeführt werden darf und welche Berechtigungen diese Rollen benötigen.

Wenn die Prozedur eine gespeicherte Prozedur mit Aufruferrechten sein soll, möchten Sie möglicherweise eine Rolle zum Ausführen dieser bestimmten Prozedur oder einer Gruppe verwandter Prozeduren erstellen. Anschließend können Sie dieser Rolle alle erforderlichen Berechtigungen erteilen und diese Rolle dann den entsprechenden Benutzern zuweisen.
Soll die gespeicherte Prozedur mit Aufruferrechten oder mit Eigentümerrechten ausgeführt werden? Weitere Informationen zu diesem Thema finden Sie unter Understanding Caller’s Rights and Owner’s Rights Stored Procedures.
Wie soll die Prozedur mit Fehlern umgehen, z. B. was soll die Prozedur tun, wenn eine erforderliche Tabelle fehlt oder ein Argument ungültig ist?
Soll die gespeicherte Prozedur ihre Aktivitäten oder Fehler protokollieren, indem sie beispielsweise in eine Protokolltabelle schreibt?
Erläuterungen darüber, wann eine gespeicherte Prozedur und wann eine UDF verwendet werden sollte, finden Sie unter Entscheidung über das Schreiben einer gespeicherten Prozedur oder einer benutzerdefinierten Funktion.

Dokumentieren gespeicherter Prozeduren¶

Gespeicherte Prozeduren werden normalerweise so geschrieben, dass sie wiederverwendet und häufig auch freigegeben werden können. Durch die Dokumentation gespeicherter Prozeduren können diese einfacher verwendet und verwaltet werden.

Im Folgenden finden Sie einige allgemeine Empfehlungen zur Dokumentation gespeicherter Prozeduren.

In der Regel gibt es mindestens zwei Benutzergruppen, die Informationen zu einer gespeicherten Prozedur benötigen:

Benutzer/Aufrufer
Programmierer/Autoren

Dokumentieren Sie für Benutzer (und Programmierer) Folgendes:

Name der gespeicherten Prozedur

Speicherort der gespeicherten Prozedur (Datenbank und Schema)

Zweck der gespeicherten Prozedur

Name, Datentyp und Bedeutung der einzelnen Eingabeparameter

Name, Datentyp und Bedeutung des Rückgabewerts. Wenn der Rückgabewert ein komplexer Typ ist, z. B. VARIANT, der Unterfelder enthält, dokumentieren Sie diese Unterfelder.

Wenn sich die gespeicherte Prozedur auf Daten aus ihrer Umgebung stützt, z. B. Sitzungsvariablen oder Sitzungsparameter, dokumentieren Sie die Namen, Zwecke und gültige Werte dieser Daten.

Zurückgegebene Fehler, ausgelöste Ausnahmen usw.

Rollen oder Berechtigungen, die zum Ausführen der Prozedur erforderlich sind. (Weitere Informationen zu diesem Thema finden Sie in den Erläuterungen zu Rollen unter Entwurfstipps für gespeicherte Prozeduren.)

Ob es sich bei der gespeicherten Prozedur um eine Prozedur mit Aufruferrechten oder mit Eigentümerrechten handelt.

Alle Voraussetzungen, z. B. Tabellen, die vorhanden sein müssen, bevor die Prozedur aufgerufen wird.

Alle Ausgaben (außer dem Rückgabewert), zum Beispiel neue Tabellen, die erstellt werden.

Alle „Nebenwirkungen“, z. B. Änderungen der Berechtigungen, Löschen alter Daten usw. Die meisten gespeicherten Prozeduren (im Gegensatz zu Funktionen) werden speziell wegen ihrer Nebeneffekte aufgerufen, weniger wegen ihrer Rückgabewerte. Stellen Sie daher sicher, dass Sie diese Auswirkungen dokumentieren.

Wenn nach dem Ausführen der gespeicherten Prozedur eine Bereinigung erforderlich ist, dokumentieren Sie diese Bereinigung.

Ob die Prozedur als Teil einer Transaktion mit mehreren Anweisungen aufgerufen werden kann (mit AUTOCOMMIT=FALSE), oder ob sie außerhalb einer Transaktion ausgeführt werden soll (mit AUTOCOMMIT=TRUE).

Ein Beispiel für einen Anruf und ein Beispiel für die Rückgabewerte.

Einschränkungen (falls zutreffend). Angenommen, die Prozedur liest eine Tabelle ein und gibt einen VARIANT-Wert zurück, der Informationen aus jeder Zeile der Tabelle enthält. Es ist möglich, dass der VARIANT-Wert größer wird als die maximale zulässige Größe von VARIANT. Daher müssen Sie dem Aufrufer möglicherweise eine Vorstellung von der maximalen Anzahl von Zeilen in der Tabelle geben, auf die die Prozedur zugreift.

Warnungen (falls zutreffend).

Tipps zur Problembehandlung

Für Programmierer:

Die Autoren

Erläuterungen, warum die Prozedur als Prozedur mit Aufruferrechten bzw. als Prozedur mit Eigentümerrechten erstellt wurde. Der Grund hierfür ist möglicherweise nicht offensichtlich.

Gespeicherte Prozeduren können verschachtelt sein, die Tiefe der Verschachtelung ist jedoch begrenzt. Wenn Ihre gespeicherte Prozedur andere gespeicherte Prozeduren aufruft und wahrscheinlich von anderen gespeicherten Prozeduren aufgerufen wird, sollten Sie die maximale bekannte Tiefe des Aufrufstapels Ihrer gespeicherten Prozedur angeben, damit die Aufrufer abschätzen können, ob ihre gespeicherte Prozedur die maximale Tiefe des Aufrufstapels überschreitet.

Tipps zum Debuggen

Speicherort und Format dieser Informationen bestimmen Sie. Sie können die Informationen beispielsweise im HTML-Format auf einer internen Website speichern. Überlegen Sie sich, wo Ihre Organisation ähnliche Informationen für andere Produkte oder ähnliche Informationen für andere Snowflake-Funktionen wie Ansichten, benutzerdefinierte Funktionen usw. speichert, bevor Sie entscheiden, wo die Speicherung erfolgen soll.

Weitere Tipps:

Fügen Sie Kommentare in den Quellcode ein, wie Sie es für jedes Stück Quellcode tun sollten.
- Denken Sie daran, dass Reverse Engineering aus Code schwierig ist. Beschreiben Sie nicht nur die Funktionsweise Ihres Algorithmus, sondern auch den Zweck dieses Algorithmus.
In gespeicherten Prozeduren ist ein optionaler COMMENT zulässig, der mit der Anweisung CREATE PROCEDURE oder ALTER PROCEDURE angegeben werden kann. Andere Personen können diesen Kommentar lesen, indem sie den Befehl SHOW PROCEDURES ausführen.
Wenn möglich, sollten Sie eine Masterkopie des CREATE PROCEDURE-Befehls jeder gespeicherten Prozedur in einem Quellcodeverwaltungssystem aufbewahren. Die Time Travel-Funktion von Snowflake gilt nicht für gespeicherte Prozeduren. Wenn Sie also nach älteren Versionen von gespeicherten Prozeduren suchen, müssen Sie dies außerhalb von Snowflake tun. Wenn kein Quellcodeverwaltungssystem verfügbar ist, können Sie ein solches teilweise simulieren, indem Sie die CREATE PROCEDURE-Befehle im VARCHAR-Feld einer Tabelle speichern und jede neue Version hinzufügen (ohne die älteren Versionen zu ersetzen).
Verwenden Sie eine Namenskonvention, um Informationen zu gespeicherten Prozeduren bereitzustellen. Beispielsweise kann ein Präfix oder Suffix im Namen angeben, ob die Prozedur eine gespeicherte Prozedur mit Aufruferrechten oder eine gespeicherte Prozedur mit Eigentümerrechten ist. (Sie können beispielsweise cr_ (für „Caller’s Rights“) als Präfix für die Aufruferrechte verwenden.)
Mit dem Befehl SHOW PROCEDURES können Sie die Datentypen und die Reihenfolge der Eingabeargumente sowie den Kommentar anzeigen. Beachten Sie jedoch, dass hier nur die Namen und Datentypen der Argumente angezeigt werden, eine Erklärung der Argumente erfolgt nicht.
Wenn Sie über die entsprechenden Berechtigungen verfügen, können Sie mit dem Befehl DESCRIBE PROCEDURE Folgendes anzeigen:
- Namen und Datentypen der Argumente
- Hauptteil der Prozedur und ob die Prozedur als Eigentümer oder Aufrufer ausgeführt wird
- Datentyp des Rückgabewerts
- Andere nützliche Informationen