Kategorien:: DDL für benutzerdefinierte Funktionen, externe Funktionen und gespeicherte Prozeduren

CREATE PROCEDURE¶

Erstellt eine neue gespeicherte Prozedur.

Eine Prozedur kann in einer der folgenden Sprachen geschrieben werden:

Siehe auch:

ALTER PROCEDURE, DROP PROCEDURE, SHOW PROCEDURES, DESCRIBE PROCEDURE

Syntax¶

JavaScript

CREATE [ OR REPLACE ] PROCEDURE <name> ( [ <arg_name> <arg_data_type> ] [ , ... ] )
  RETURNS <result_data_type> [ NOT NULL ]
  LANGUAGE JAVASCRIPT
  [ { CALLED ON NULL INPUT | { RETURNS NULL ON NULL INPUT | STRICT } } ]
  [ VOLATILE | IMMUTABLE ] -- Note: VOLATILE and IMMUTABLE are deprecated.
  [ COMMENT = '<string_literal>' ]
  [ EXECUTE AS { CALLER | OWNER } ]
  AS '<procedure_definition>'

Wichtig

Bei JavaScript muss die Groß-/Kleinschreibung beachtet werden, bei SQL nicht. Wichtige Informationen zur Verwendung von Argumentnamen für gespeicherte Prozeduren im JavaScript-Code finden Sie unter Groß-/Kleinschreibung in JavaScript-Argumenten.

Snowflake Scripting

CREATE [ OR REPLACE ] PROCEDURE <name> ( [ <arg_name> <arg_data_type> ] [ , ... ] )
  RETURNS { <result_data_type> | TABLE ( [ <col_name> <col_data_type> [ , ... ] ] ) }
    [ NOT NULL ]
  LANGUAGE SQL
  [ { CALLED ON NULL INPUT | { RETURNS NULL ON NULL INPUT | STRICT } } ]
  [ VOLATILE | IMMUTABLE ] -- Note: VOLATILE and IMMUTABLE are deprecated.
  [ COMMENT = '<string_literal>' ]
  [ EXECUTE AS { CALLER | OWNER } ]
  AS <procedure_definition>

Bemerkung

Wenn Sie eine Snowflake Scripting-Prozedur in SnowSQL oder der klassischen Weboberfläche erstellen, müssen Sie Trennzeichen für Zeichenfolgenliterale (' oder $$) um die Prozedurdefinition setzen. Siehe Verwenden von Snowflake Scripting in SnowSQL und über die klassische Weboberfläche.

Snowpark (Scala und Java)

Für inline gespeicherte Prozeduren verwenden Sie die folgende Syntax:

CREATE [ OR REPLACE ] PROCEDURE <name> ( [ <arg_name> <arg_data_type> ] [ , ... ] )
  RETURNS <result_data_type> [ NOT NULL ]
  LANGUAGE { SCALA | JAVA }
  RUNTIME_VERSION = '<scala_or_java_runtime_version>'
  PACKAGES = ( '<fully_qualified_package_name_of_library_to_use>` )
  [ IMPORTS = ( '<stage_path_and_file_name_to_read>' [, '<stage_path_and_file_name_to_read>' ...] ) ]
  HANDLER = '<fully_qualified_method_name>'
  [ TARGET_PATH = '<stage_path_and_file_name_to_write>' ]
  [ { CALLED ON NULL INPUT | { RETURNS NULL ON NULL INPUT | STRICT } } ]
  [ VOLATILE | IMMUTABLE ] -- Note: VOLATILE and IMMUTABLE are deprecated.
  [ COMMENT = '<string_literal>' ]
  [ EXECUTE AS { CALLER | OWNER } ]
  AS '<procedure_definition>'

Für vorkompilierte gespeicherte Prozeduren in Scala und Java verwenden Sie die folgende Syntax:

CREATE [ OR REPLACE ] PROCEDURE <name> ( [ <arg_name> <arg_data_type> ] [ , ... ] )
  RETURNS <result_data_type> [ NOT NULL ]
  LANGUAGE { SCALA | JAVA }
  RUNTIME_VERSION = '<scala_or_java_runtime_version>'
  PACKAGES = ( '<fully_qualified_package_name_of_library_to_use>` )
  [ IMPORTS = ( '<stage_path_and_file_name_to_read>' [, '<stage_path_and_file_name_to_read>' ...] ) ]
  HANDLER = '<fully_qualified_method_name>'
  [ { CALLED ON NULL INPUT | { RETURNS NULL ON NULL INPUT | STRICT } } ]
  [ VOLATILE | IMMUTABLE ] -- Note: VOLATILE and IMMUTABLE are deprecated.
  [ COMMENT = '<string_literal>' ]
  [ EXECUTE AS { CALLER | OWNER } ]

Erforderliche Parameter¶

Alle Sprachen

Name ( [ Argumentname Argumentdatentyp ] [ , ... ] )

Gibt den Bezeichner (Name) und alle Eingabeargumente für die gespeicherte Prozedur an.

Für den Bezeichner:
- Der Bezeichner muss für das Schema, in dem die gespeicherte Prozedur erstellt wird, nicht eindeutig sein, da gespeicherte Prozeduren durch die Kombination aus Name und Argumenttypen identifiziert und aufgelöst werden.
- Der Bezeichner muss mit einem alphabetischen Zeichen beginnen und darf keine Leer- oder Sonderzeichen enthalten, es sei denn, die gesamte Bezeichnerzeichenfolge wird in doppelte Anführungszeichen gesetzt (z. B. „Mein Objekt“). Bei Bezeichnern, die in doppelte Anführungszeichen eingeschlossen sind, ist auch die Groß-/Kleinschreibung zu beachten. Siehe Anforderungen an Bezeichner.
Für die Eingabeargumente:
- Für Name_des_Arguments geben Sie den Namen des Eingabearguments an.
- Für Datentyp_des_Arguments ist der Snowflake-Datentyp zu verwenden, der der von Ihnen verwendeten Sprache entspricht.
  - Für gespeicherten JavaScript-Prozeduren siehe Zuordnen von SQL- und JavaScript-Datentypen.
  - Für Snowflake Scripting ein SQL-Datentyp.
  - Weitere Informationen zu gespeicherten Snowpark-Prozeduren (Scala und Java) finden Sie unter:
    - Anhang: Zuordnung von Scala-Datentypen zu Snowflake-Datentypen
    - Zuordnung von SQL-Java-Datentypen für Parameter und Rückgabetypen
    Bemerkung
    
    Für gespeicherte Snowpark-Prozeduren (Scala und Java) ist das Argument für das Snowpark-Session-Objekt wegzulassen.
    
    Das Argument Session ist kein formaler Parameter, den Sie in CREATE PROCEDURE oder CALL angeben. Wenn Sie Ihre gespeicherte Prozedur aufrufen, erstellt Snowflake automatisch ein Session-Objekt und übergibt es an die Scala-Funktion für Ihre gespeicherte Prozedur.

RETURNS Ergebnisdatentyp [ NOT NULL ]:

Gibt den von der gespeicherten Prozedur zurückgegebenen Ergebnistyp an.

Verwenden Sie für Ergebnisdatentyp den Snowflake-Datentyp, der dem Typ der von Ihnen verwendeten Sprache entspricht.
- Weitere Informationen zu gespeicherten JavaScript-Prozeduren finden Sie untern Zuordnen von SQL- und JavaScript-Datentypen.
- Für Snowflake Scripting ein SQL-Datentyp.
- Weitere Informationen zu gespeicherten Snowpark-Prozeduren (Scala und Java) finden Sie unter:
  - Anhang: Zuordnung von Scala-Datentypen zu Snowflake-Datentypen
  - Zuordnung von SQL-Java-Datentypen für Parameter und Rückgabetypen
  Bemerkung
  
  Gespeicherte Snowpark-Prozeduren (Scala und Java) müssen einen Rückgabewert haben.
Wenn Sie die Snowflake-Datentypen der Spalten in der zurückgegebenen Tabelle kennen, geben Sie für RETURNS TABLE ( [ Spaltenname Spaltendatentyp [ , ... ] ] ) die Spaltennamen und -typen an:
```
CREATE OR REPLACE PROCEDURE get_top_sales()
RETURNS TABLE (sales_date DATE, quantity NUMBER)
...
```
Andernfalls (z. B. wenn Sie die Spaltentypen während der Laufzeit bestimmen) können Sie die Spaltennamen und -typen weglassen:
```
CREATE OR REPLACE PROCEDURE get_top_sales()
RETURNS TABLE ()
```
Bemerkung

Derzeit können Sie in der RETURNS TABLE(...)-Klausel von CREATE PROCEDURE den Datentyp GEOGRAPHY nicht als Spaltentyp angeben.
```
CREATE OR REPLACE PROCEDURE test_return_geography_table_1()
RETURNS TABLE(g GEOGRAPHY)
...
```
Wenn Sie dies tun, führt der Aufruf der gespeicherten Prozedur zu einem Fehler:
```
CALL test_return_geography_table_1();
```
```
Stored procedure execution error: data type of returned table does not match expected returned table type
```
Um dieses Problem zu umgehen, können Sie die Spaltenargumente und -typen in RETURNS TABLE() weglassen.
```
CREATE OR REPLACE PROCEDURE test_return_geography_table_1()
RETURNS TABLE()
...
```
RETURNS TABLE(...) wird für gespeicherte Snowflake Scripting-Prozeduren nur dann unterstützt, wenn Ihre Prozedur eine Tabelle zurückgibt. Siehe RETURN.

In der Praxis kann der zurückgegebene Wert nicht verwendet werden, da der Aufruf nicht Teil eines Ausdrucks sein kann.

LANGUAGE Sprache

Gibt an, in welcher Sprache der Code der gespeicherten Prozedur geschrieben ist. Beachten Sie, dass dies für gespeicherte Prozeduren, die mit Snowflake Scripting geschrieben wurden, optional ist.

Derzeit werden folgende Werte für Sprache unterstützt:

JAVASCRIPT (siehe Schreiben von gespeicherten Prozeduren in JavaScript)
SQL (für Snowflake Scripting)
SCALA (für Snowpark (Scala))
JAVA (für Snowpark (Scala))

Standard: SQL.

AS Prozedurdefinition

Definiert den Code, der von der gespeicherten Prozedur ausgeführt wird. Die Definition kann aus jedem gültigen Code bestehen.

Beachten Sie Folgendes:

Bei vorkompilierten gespeicherten Prozeduren in Snowpark Scala und Java lassen Sie die AS-Klausel weg.

Verwenden Sie stattdessen die IMPORTS-Klausel, um den Speicherort der JAR-Datei anzugeben, die den Code der gespeicherten Prozedur enthält. Weitere Details dazu finden Sie unter:
- Schreiben von gespeicherten Prozeduren in Snowpark (Scala).
- Schreiben von gespeicherten Prozeduren in Snowpark (Java).
In folgenden Fällen müssen Sie Trennzeichen für Zeichenfolgenliterale (' oder $$) um die Prozedurdefinition setzen:
- Sie verwenden eine andere Sprache als Snowflake Scripting.
- Sie erstellen eine Snowflake Scripting-Prozedur in SnowSQL oder der klassischen Weboberfläche. Siehe Verwenden von Snowflake Scripting in SnowSQL und über die klassische Weboberfläche.
Wenn Sie Ihre gespeicherte Prozedur in JavaScript schreiben und eine Zeichenfolge Zeilenumbrüche enthält, können Sie um die Zeichenfolge herum Backquotes (auch „Backticks“ genannt) verwenden.

Im folgenden Beispiel einer gespeicherten JavaScript-Prozedur werden $$ und Backquotes verwendet, da der Text der gespeicherten Prozedur einfache und doppelte Anführungszeichen enthält:
CREATE OR REPLACE TABLE table1 ("column 1" VARCHAR);
CREATE or replace PROCEDURE proc3() RETURNS VARCHAR LANGUAGE javascript AS $$ var rs = snowflake.execute( { sqlText: `INSERT INTO table1 ("column 1") SELECT 'value 1' AS "column 1" ;` } ); return 'Done.'; $$;
Bei anderen Sprachen als Snowflake Scripting validiert Snowflake den Code nicht vollständig, wenn Sie den Befehl CREATE PROCEDURE ausführen.

Bei gespeicherten Snowpark (Scala)-Prozeduren werden beispielsweise die Anzahl und der Typ der Eingabeargumente überprüft, aber der Textteil der Funktion wird nicht validiert. Wenn die Zahl oder die Typen nicht übereinstimmen (z. B. wenn der Snowflake-Datentyp NUMBER verwendet wird, während das Argument ein nicht-numerischer Typ ist), führt die Ausführung des Befehls CREATE PROCEDURE zu einem Fehler.

Wenn der Code nicht gültig ist, wird der CREATE PROCEDURE-Befehl erfolgreich sein, und bei Aufruf der gespeicherten Prozedur werden Fehler zurückgegeben.

Weitere Informationen zu gespeicherten Prozeduren finden Sie unter Verwenden von gespeicherten Prozeduren.

Snowpark (Scala und Java)

RUNTIME_VERSION = 'Laufzeitversion_von_Scala_oder_Java'

Die zu verwendende Laufzeitversion von Scala oder Java. Derzeit werden nur folgende Versionen unterstützt:

2.12 (für Scala)
11 (für Java)

PACKAGES = ( 'vollqualifizierter_Paketname' )

Der vollqualifizierte Paketname der Snowpark-Bibliothek.

Geben Sie den vollqualifizierten Paketnamen der Snowpark-Bibliothek in folgendem Format an:

com.snowflake:snowpark:<version>

wobei Version die Versionsnummer oder latest für die neueste Version ist.

Bemerkung

Geben Sie eine Version an, die unterstützten Mindestversion entspricht oder höher ist:

Für Scala geben Sie die Version 1.1.0 oder höher an.

Snowflake unterstützt die Verwendung von Snowpark, Version 0.9.0 oder höher in einer gespeicherten Prozedur. Beachten Sie jedoch, dass diese Versionen Einschränkungen haben. So unterstützen Versionen vor 1.1.0 beispielsweise nicht die Verwendung von Transaktionen in einer gespeicherten Prozedur.

Für Java geben Sie die Version 1.3.0 oder höher an.

Für die Liste der unterstützten Pakete und Versionen fragen Sie die Ansicht INFORMATION_SCHEMA.PACKAGES nach Zeilen mit LANGUAGE = 'scala' bzw. LANGUAGE = 'java' ab. Beispiel:

select * from information_schema.packages where language = 'scala';

select * from information_schema.packages where language = 'java';

HANDLER = 'vollqualifizierter_Methodenname'

Der vollqualifizierte Name der Methode oder Funktion für die gespeicherte Prozedur. Dies geschieht typischerweise in der folgenden Form:

com.my_company.my_package.MyClass.myMethod

Wobei:

com.my_company.my_package

entspricht dem Paket, das das Objekt oder die Klasse enthält:

package com.my_company.my_package;

Optionale Parameter¶

Alle Sprachen

[ [ NOT ] NULL ]

Gibt an, ob die Funktion NULL-Werte zurückgeben kann oder nur NON-NULL-Werte zurückgeben darf.

Der Standardwert ist NULL (d. h. die gespeicherte Prozedur kann NULL zurückgeben).

CALLED ON NULL INPUT oder . RETURNS NULL ON NULL INPUT | STRICT

Gibt das Verhalten der gespeicherten Prozedur an, wenn sie mit Null-Eingaben aufgerufen wird. Im Gegensatz zu systemdefinierten Funktionen, die immer Null zurückgeben, wenn eine Eingabe Null ist, können gespeicherte Prozeduren Null-Eingaben verarbeiten und Nicht-Null-Werte zurückgeben, auch wenn eine Eingabe Null ist:

CALLED ON NULL INPUT ruft immer die gespeicherten Prozeduren mit Null-Eingaben auf. Es liegt an der Prozedur, mit solchen Werten angemessen umzugehen.
RETURNS NULL ON NULL INPUT (oder dessen Synonym STRICT) ruft die gespeicherte Prozedur nicht auf, wenn eine Eingabe Null ist, sodass die Anweisungen in der gespeicherten Prozedur nicht ausgeführt werden. Stattdessen wird immer ein Null-Wert zurückgegeben. Beachten Sie, dass die Prozedur bei Nicht-Null-Eingaben immer noch Null zurückgeben kann.

Standard: CALLED ON NULL INPUT

VOLATILE | IMMUTABLE

Veraltet

Achtung

Diese Schlüsselwörter sind für gespeicherte Prozeduren veraltet. Diese Schlüsselwörter sind nicht für die Verwendung mit gespeicherten Prozeduren gedacht. In einem zukünftigen Release werden diese Schlüsselwörter aus der Dokumentation entfernt.

COMMENT = 'Zeichenfolgenliteral'

Gibt einen Kommentar für die gespeicherte Prozedur an, der in der Spalte DESCRIPTION der Ausgabe SHOW PROCEDURES angezeigt wird.

Standard: stored procedure

EXECUTE AS CALLER oder . EXECUTE AS OWNER

Gibt an, ob eine gespeicherte Prozedur mit den Berechtigungen des Eigentümers (gespeicherte Prozedur mit „Eigentümerrechten“) oder mit den Berechtigungen des Aufrufers (gespeicherte Prozedur mit „Aufruferrechten“) ausgeführt werden.

Wenn Sie die CREATE PROCEDURE ... EXECUTE AS CALLER-Anweisung ausführen, wird die Prozedur künftig als Prozedur mit Aufruferrechten ausgeführt.
Wenn Sie CREATE PROCEDURE ... EXECUTE AS OWNER ausführen, wird die Prozedur als Prozedur mit Eigentümerrechten ausgeführt.

Standardmäßig wird die Prozedur als gespeicherte Prozedur mit Eigentümerrechten ausgeführt, wenn zum Zeitpunkt der Erstellung der Prozedur weder OWNER noch CALLER explizit angegeben wurden.

Gespeicherte Prozeduren mit Eigentümerrechten haben weniger Zugriffsrechte auf die Umgebung des Anrufers (z. B. Sitzungsvariablen des Anrufers). Snowflake wendet standardmäßig diese höhere Datenschutz- und Sicherheitsstufe an.

Weitere Informationen dazu finden Sie unter Erläuterungen zu gespeicherten Prozeduren mit Aufruferrechten und Eigentümerrechten.

Standard: OWNER

Snowpark (Scala und Java)

IMPORTS = ( 'stage_path_and_file_name_to_read' [, 'stage_path_and_file_name_to_read' ...] )

Der Speicherort (Stagingbereich), der Pfad und der Name der zu importierenden Datei(en). Sie müssen die IMPORTS-Klausel festlegen, um alle Dateien einzuschließen, von denen Ihre gespeicherte Prozedur abhängt:

Wenn Sie eine inline gespeicherte Prozedur schreiben, können Sie diese Klausel weglassen, es sei denn, Ihr Code hängt von Klassen ab, die außerhalb der gespeicherten Prozedur oder der Ressourcendateien definiert sind.
Wenn Sie eine vorkompilierte gespeicherte Prozedur schreiben, müssen Sie auch die JAR-Datei einschließen, die die Definition der gespeicherten Prozedur enthält.

Jede Datei in der IMPORTS-Klausel muss einen eindeutigen Namen haben, auch wenn sich die Dateien in verschiedenen Unterverzeichnissen oder verschiedenen Stagingbereichen befinden.

TARGET_PATH = 'stage_path_and_file_name_to_write'

Die TARGET_PATH-Klausel gibt den Speicherort an, in den Snowflake den kompilierten Code (JAR-Datei) nach der Kompilierung des in der Funktionsdefinition angegebenen Quellcodes schreiben soll.

Wenn diese Klausel weggelassen wird, kompiliert Snowflake den Quellcode jedes Mal neu, wenn der Code benötigt wird.

Bei dieser Klausel ist Folgendes zu beachten:

Sie können hier keine bestehende Datei angeben. Snowflake gibt einen Fehler zurück, wenn TARGET_PATH auf eine bestehende Datei verweist.
Wenn Sie sowohl die IMPORTS- als auch die TARGET_PATH-Klausel angeben, muss sich der Dateiname in der TARGET_PATH-Klausel von jedem Dateinamen in der IMPORTS-Klausel unterscheiden, auch wenn sich die Dateien in verschiedenen Unterverzeichnissen oder verschiedenen Stagingbereichen befinden.
Wenn Sie die gespeicherte Prozedur nicht mehr benötigen (z. B. wenn Sie die gespeicherte Prozedur löschen), müssen Sie diese JAR-Datei manuell entfernen.

Nutzungshinweise¶

Für alle gespeicherten Prozeduren gilt Folgendes:
- Gespeicherte Prozeduren unterstützen das Überladen. Zwei Prozeduren können denselben Namen haben, wenn sie eine unterschiedliche Anzahl von Parametern oder unterschiedliche Datentypen für ihre Parameter aufweisen.
- 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. Weitere Informationen zu gespeicherten Prozeduren und Transaktionen finden Sie unter Transaktionsmanagement.
- Metadaten:
  
  Achtung
  
  Kunden müssen sicherstellen, dass bei der Nutzung des Snowflake-Dienstes keine personenbezogenen Daten (außer für ein Objekt „Benutzer“), sensible Daten, exportkontrollierte Daten oder andere regulierte Daten als Metadaten eingegeben werden. Weitere Informationen dazu finden Sie unter Metadatenfelder in Snowflake.
Tipp

Wenn Ihr Unternehmen eine Mischung aus gespeicherten Prozeduren mit Aufruferrechten und mit Eigentümerrechten verwendet, möchten Sie vielleicht eine Namenskonvention für Ihre gespeicherten Prozeduren verwenden, um anzugeben, ob eine einzelne gespeicherte Prozedur eine gespeicherte Prozedur mit Aufruferrechten oder eine gespeicherte Prozedur mit Eigentümerrechten ist.
Für gespeicherte Prozeduren in JavaScript gilt Folgendes:
- Eine gespeicherte JavaScript-Prozedur kann nur einen einzigen Wert zurückgeben, z. B. eine Zeichenfolge (beispielsweise ein Erfolgs-/Fehler-Indikator) oder eine Zahl (z. B. ein Fehlercode). Wenn Sie umfangreichere Informationen zurückgeben müssen, können Sie einen VARCHAR-Wert verwenden, der durch ein Trennzeichen (z. B. ein Komma) getrennte Werte enthält, oder Sie verwenden einen Datentyp für semistrukturierte Daten (wie VARIANT).
Bei gespeicherten Snowpark (Scala)-Prozeduren sind die bekannten Einschränkungen zu beachten.
Bei gespeicherten Snowpark (Java)-Prozeduren sind die bekannten Einschränkungen zu beachten.

Beispiele¶

Dadurch wird eine einfache gespeicherte Prozedur erstellt, die einen fest codierten Wert zurückgibt. Dies ist unrealistisch, zeigt jedoch die grundlegende SQL-Syntax mit minimalem JavaScript-Code:

create or replace procedure sp_pi()
    returns float not null
    language javascript
    as
    $$
    return 3.1415926;
    $$
    ;

Dies zeigt ein realistischeres Beispiel, das einen Aufruf der JavaScript-API enthält. Eine umfangreichere Version dieser Prozedur könnte es einem Benutzer ermöglichen, Daten in eine Tabelle einzufügen, für die der Benutzer keine Berechtigungen zum direkten Einfügen hatte. JavaScript-Anweisungen konnten die Eingabeparameter nur überprüfen und SQL INSERT ausführen, wenn bestimmte Anforderungen erfüllt waren.

create or replace procedure stproc1(FLOAT_PARAM1 FLOAT)
    returns string
    language javascript
    strict
    execute as owner
    as
    $$
    var sql_command = 
     "INSERT INTO stproc_test_table1 (num_col1) VALUES (" + FLOAT_PARAM1 + ")";
    try {
        snowflake.execute (
            {sqlText: sql_command}
            );
        return "Succeeded.";   // Return a success/error indicator.
        }
    catch (err)  {
        return "Failed: " + err;   // Return a success/error indicator.
        }
    $$
    ;

Beispiele für gespeicherte Snowpark (Scala)-Prozeduren finden Sie unter Schreiben von gespeicherten Prozeduren in Snowpark (Scala).

Weitere Beispiele finden Sie unter Verwenden von gespeicherten Prozeduren.