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:

Erstellen einer gespeicherten Prozedur

DDL für gespeicherte Prozeduren

Gespeicherte Prozeduren sind First-Class-Datenbankobjekte. Die folgenden DDL-Befehle gelten für gespeicherte Prozeduren:

Darüber hinaus bietet Snowflake folgenden Befehl zum Ausführen gespeicherter Prozeduren:

Namenskonventionen für gespeicherte Prozeduren

Gespeicherte Prozeduren sind Datenbankobjekte, d. h. sie werden in einer festgelegten Datenbank und einem festgelegten Schema erstellt. Sie verfügen über einen vollqualifizierten Namen, der durch ihren Namespace in Form von Datenbank.Schema.Prozedurname definiert wird. Beispiel:

CALL temporary_db_qualified_names_test.temporary_schema_1.stproc_pi();

Beim Aufrufen ohne den vollqualifizierten Namen werden gespeicherte Prozeduren für die aktuell in der Sitzung verwendete Datenbank und das aktuell verwendete Schema aufgelöst.

Überladen von Namen

Snowflake unterstützt das Überladen von Namen für gespeicherte Prozeduren. Verschiedene in demselben Schema gespeicherte Prozeduren können den gleichen Namen haben, sofern sich ihre Signaturen unterscheiden, entweder durch die Anzahl der Argumente oder die Argumenttypen. Wenn eine überladene gespeicherte Prozedur aufgerufen wird, überprüft Snowflake die Argumente und ruft die korrekt gespeicherte Prozedur auf.

Ein Beispiel finden Sie unter Überladen der Namen gespeicherter Prozeduren.

Seien Sie vorsichtig, wenn Sie die Überladung verwenden. Durch die Kombination aus automatischer Typkonvertierung und Überladung können kleine Benutzerfehler schnell zu unerwarteten Ergebnissen führen. Ein Beispiel finden Sie unter Überladen der Namen gespeicherter Prozeduren.

Mögliche Konflikte mit systemdefinierten Funktionen und benutzerdefinierten Funktionen

Gespeicherte Prozeduren und benutzerdefinierte Funktionen können denselben Namen tragen, wenn die Anzahl ihrer Argumente unterschiedlich ist oder wenn die Argumente unterschiedliche Datentypen haben.

Allerdings erlaubt Snowflake keine Erstellung von gespeicherten Prozeduren, die den gleichen Namen tragen wie eine systemdefinierte Funktion.

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;

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;
    $$
    ;

Aufrufen einer gespeicherten Prozedur

Verwenden Sie zum Ausführen einer gespeicherten Prozedur eine CALL-Anweisung. Beispiel:

call stproc1(5.14::FLOAT);

Jedes Argument für eine gespeicherte Prozedur kann ein allgemeiner Ausdruck sein:

CALL stproc1(2 * 5.14::FLOAT);

Ein Argument kann eine Unterabfrage sein:

CALL stproc1(SELECT COUNT(*) FROM stproc_test_table1);

Sie können nur eine gespeicherte Prozedur pro CALL-Anweisung aufrufen. Zum Beispiel schlägt die folgende Aussagen fehl:

call proc1(1), proc2(2);                          -- Not allowed

Außerdem können Sie den CALL einer gespeicherten Prozedur nicht als Teil eines Ausdrucks verwenden. Beispielsweise schlagen alle folgenden Anweisungen fehl:

call proc1(1) + proc1(2);                         -- Not allowed
call proc1(1) + 1;                                -- Not allowed
call proc1(proc2(x));                             -- Not allowed
select * from (call proc1(1));                    -- Not allowed

Innerhalb einer gespeicherten Prozedur kann die gespeicherte Prozedur jedoch eine andere gespeicherte Prozedur aufrufen oder sich selbst rekursiv aufrufen.

Vorsicht

Verschachtelte Aufrufe können die maximal zulässige Stapeltiefe überschreiten. Seien Sie daher vorsichtig, wenn Sie Aufrufe verschachteln, insbesondere bei Gebrauch der Rekursion.

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 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 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 Erstellen einer gespeicherten Prozedur oder einer UDF.

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

Zurück zum Anfang