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 anonymer Prozedur). 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.

Auswählen aus einer gespeicherten Prozedur

Sie rufen eine gespeicherte Prozedur in der FROM-Klausel einer SELECT-Anweisung auf. Weitere Informationen zur Auswahl aus einer gespeicherten Prozedur finden Sie unter Auswählen aus 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 Erläuterungen zu gespeicherten Prozeduren mit Aufruferrechten und Eigentümerrechten 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 16 (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.

Verwenden von gespeicherten Prozeduren in Snowsight

Sie können gespeicherte Prozeduren in SQL oder Snowsight nutzen.

Für jede gespeicherte Prozedur in Snowflake können Sie Data » Databases öffnen und nach der gespeicherten Prozedur suchen oder zu ihr browsen. Wählen Sie die gespeicherte Prozedur aus, um Details zu überprüfen und die Prozedur zu verwalten.

Sie müssen über die entsprechenden Berechtigungen verfügen, um auf die gespeicherte Prozedur in Snowsight zuzugreifen und diese verwalten zu können.

Details der gespeicherten Prozedur in Snowsight anzeigen

Nach dem Öffnen einer gespeicherten Prozedur in Snowsight können Sie Folgendes tun:

  • Ermitteln, wann die Prozedur erstellt wurde, und welche Kommentare zur Prozedur eingegeben wurden. Sie können den Mauszeiger über die Zeitangabe bewegen, um das genaue Datum und die genaue Uhrzeit anzuzeigen.

  • Überprüfen von zusätzlichen Details zu der gespeicherten Prozedur, einschließlich:

    • Argumente, die die gespeicherte Prozedur ggf. benötigt.

    • Datentyp des Ergebnisses der Prozedur.

    • Ob die Prozedur eine Aggregatfunktion ist.

    • Ob die Prozedur eine sichere Funktion ist.

    • Ob die Prozedur eine Tabellenfunktion ist.

    • Die Sprache, in der die gespeicherte Prozedur geschrieben ist. Beispiel: JavaScript.

  • Überprüfen der SQL-Definition der gespeicherten Prozedur im Bereich Procedure definition.

  • Überprüfen der Rollen mit Berechtigungen für die gespeicherte Prozedur im Bereich Privileges.

Gespeicherte Prozedur in Snowsight verwalten

Sie können die folgenden grundlegenden Verwaltungsaufgaben für eine gespeicherte Prozedur in Snowsight ausführen:

  • Um den Namen der gespeicherten Prozedur zu bearbeiten oder einen Kommentar hinzuzufügen, wählen Sie Weitere Optionen » Edit aus.

  • Um die gespeicherte Prozedur zu löschen, wählen Sie Weitere Optionen » Drop aus.

  • Um die Eigentümerschaft der gespeicherten Prozedur auf andere Rolle zu übertragen, wählen Sie Weitere Optionen » Transfer Ownership aus.

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 Erläuterungen zu gespeicherten Prozeduren mit Aufruferrechten und Eigentümerrechten.

  • 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

Sprache: Deutsch