Schreiben von gespeicherten Prozeduren in Snowflake Scripting¶

Dieses Thema bietet eine Einführung in das Schreiben einer gespeicherten Prozedur in SQL mit Hilfe von Snowflake Scripting. Weitere Informationen über Snowflake Scripting finden Sie unter Snowflake Scripting-Entwicklerhandbuch.

Unter diesem Thema:

Einführung¶

So schreiben Sie eine gespeicherte Prozedur, die Snowflake Scripting verwendet:

Verwenden Sie den Befehl CREATE PROCEDURE oder WITH … CALL … mit LANGUAGE SQL.
Im Textteil der gespeicherten Prozedur (die AS-Klausel) verwenden Sie einen Snowflake Scripting-Block.

Bemerkung

Wenn Sie eine Snowflake Scripting-Prozedur in SnowSQL oder der klassischen Weboberfläche erstellen, müssen Sie um den Textteil der gespeicherten Prozedur Trennzeichen für Zeichenfolgenliterale (' oder $$) setzen.

Weitere Details dazu finden Sie unter Verwenden von Snowflake Scripting in Snowflake CLI, SnowSQL, Classic Console und im Python-Connector.

Snowflake begrenzt die maximale Größe des Quellcodes im Textteil einer gespeicherten Snowflake Scripting-Prozedur. Snowflake empfiehlt eine Begrenzung auf 100 KB. (Da der Code in komprimierter Form gespeichert ist, hängt das genaue Limit von der Komprimierbarkeit des Codes ab.)

Sie können während der Ausführung Ihres Handler-Codes Protokoll- und Ablaufverfolgungsdaten erfassen. Weitere Informationen dazu finden Sie unter Protokollierung, Ablaufverfolgung und Metriken.

Bemerkung

Für diese gespeicherten Prozeduren gelten dieselben Regeln hinsichtlich Aufruferrechte vs. Eigentümerrechte.
Für gespeicherte Prozeduren in Snowflake Scripting gelten die gleichen Hinweise und Richtlinien, die unter Verwenden von gespeicherten Prozeduren beschrieben sind.

Es folgt ein Beispiel für eine einfache gespeicherte Prozedur, die den Wert des übergebenen Arguments zurückgibt:

CREATE OR REPLACE PROCEDURE output_message(message VARCHAR)
RETURNS VARCHAR NOT NULL
LANGUAGE SQL
AS
BEGIN
  RETURN message;
END;

Copy

Hinweis: Wenn Sie Snowflake CLI, SnowSQL, die Classic Console, oder die Methode execute_stream oder execute_string in Python Connector Code verwenden, benutzen Sie stattdessen dieses Beispiel (siehe Verwenden von Snowflake Scripting in Snowflake CLI, SnowSQL, Classic Console und im Python-Connector):

CREATE OR REPLACE PROCEDURE output_message(message VARCHAR)
RETURNS VARCHAR NOT NULL
LANGUAGE SQL
AS
$$
BEGIN
  RETURN message;
END;
$$
;

Copy

Im Folgenden finden Sie ein Beispiel für das Aufrufen der gespeicherten Prozedur:

CALL output_message('Hello World');

Copy

Im Folgenden sehen Sie ein Beispiel für die Erstellung und den Aufruf einer anonymen gespeicherten Prozedur mit dem Befehl WITH … CALL …:

WITH anonymous_output_message AS PROCEDURE (message VARCHAR)
  RETURNS VARCHAR NOT NULL
  LANGUAGE SQL
  AS
  $$
  BEGIN
    RETURN message;
  END;
  $$
CALL anonymous_output_message('Hello World');

Copy

Beachten Sie, dass Sie um den Textteil (Body) einer anonymen gespeicherten Prozedur in Trennzeichen für Zeichenfolgenliterale (' oder $$) setzen müssen.

Verwenden von Argumenten, die an eine gespeicherte Prozedur übergeben werden¶

Wenn Sie Argumente an Ihre gespeicherte Prozedur übergeben, können Sie in jedem Snowflake Scripting-Ausdruck auf diese Argumente mit dem Namen verweisen. Gespeicherte Snowflake Scripting-Prozeduren unterstützen Eingabe- (IN) und Ausgabe-Argumente (OUT).

Wenn Sie in der Definition einer gespeicherten Snowflake Scripting-Prozedur ein Ausgabeargument angeben, kann die gespeicherte Prozedur den aktuellen Wert des Ausgabearguments an ein aufrufendes Programm zurückgeben, z. B. einen anonymen Block oder eine andere gespeicherte Prozedur. Die gespeicherte Prozedur nimmt einen Anfangswert für das Ausgabeargument, speichert den Wert in einer Variablen im Textkörper der Prozedur und führt optional Vorgänge aus, um den Wert der Variablen zu ändern, bevor der aktualisierte Wert an das aufrufende Programm zurückgegeben wird.

Beispielsweise können die Benutzer-ID eines Vertriebsmitarbeiters und ein Umsatzquartal an eine gespeicherte Prozedur namens emp_quarter_calling_sp_demo übergeben werden. Diese gespeicherte Prozedur ruft eine andere gespeicherte Prozedur namens sales_total_out_sp_demo auf. Die gespeicherte sales_total_out_sp_demo-Prozedur verfügt über ein Ausgabeargument, das Vorgänge ausführt, um den Gesamtumsatz des Vertriebsmitarbeiters für das Quartal an die aufrufende gespeicherte Prozedur emp_quarter_calling_sp_demo zurückzugeben. Ein Beispiel für dieses Szenario finden Sie unter Verwendung eines Ausgabearguments, um den Gesamtumsatz eines Mitarbeiters in einem Quartal zurückzugeben.

Wenn der Datentyp des übergebenen Werts und der Datentyp des Ausgabearguments nicht übereinstimmen, werden unterstützte Koersionen automatisch ausgeführt. Ein Beispiel dazu finden Sie unter Verwendung eines Ausgabearguments mit einem anderen Datentyp als der Eingabewert einer aufrufenden Prozedur. Informationen darüber, welche Koersionen Snowflake automatisch ausführen kann, finden Sie unter Datentypen, die umgewandelt werden können.

Die GET_DDL-Funktion und der SHOW PROCEDURES-Befehl zeigen den Typ (entweder IN oder OUT) der Argumente einer gespeicherten Prozedur in der Ausgabe an. Andere Befehle und Ansichten, die Metadaten über gespeicherte Prozeduren anzeigen, zeigen nicht den Typ der Argumente an, wie z. B. der DESCRIBE PROCEDURE-Befehl das Information Schema /sql-reference/info-schema/procedures`und die Account Usage-Ansicht :doc:`PROCEDURES.

Eine gespeicherte Prozedur kann nicht überladen werden, indem verschiedene Argumenttypen in der Signatur angegeben werden. Nehmen wir zum Beispiel an, eine gespeicherte Prozedur hat folgende Signatur:

CREATE PROCEDURE test_overloading(a IN NUMBER)

Copy

Die folgenden Befehle CREATE PROCEDURE schlagen fehl und die Fehlermeldung besagt, dass die Prozedur bereits existiert, da versucht wird, eine neue gespeicherte Prozedur zu erstellen, die sich vom vorherigen Beispiel nur durch den Argumenttyp unterscheidet:

CREATE PROCEDURE test_overloading(a OUT NUMBER)

Copy

Syntax¶

Verwenden Sie die folgende Syntax, um ein Argument in der Definition einer gespeicherten Snowflake Scripting-Prozedur anzugeben:

<arg_name> [ { IN | INPUT | OUT | OUTPUT } ] <arg_data_type>

Copy

Wobei:

arg_name

Der Name des Arguments. Der Name muss den Snowflake-Regeln für Bezeichner folgen (siehe unter Objektbezeichner).

{ IN | INPUT | OUT | OUTPUT }

Optionales Schlüsselwort, das angibt, ob das Argument ein Eingabeargument oder ein Ausgabeargument ist.

IN oder INPUT – Das Argument wird mit dem angegebenen Wert initialisiert, und dieser Wert wird einer Variablen einer gespeicherten Prozedur zugewiesen. Die Variable kann im Textteil der gespeicherten Prozedur geändert werden, aber ihr Endwert kann nicht an ein aufrufendes Programm übergeben werden.

IN und INPUT sind gleichbedeutend.
OUT oder OUTPUT – Das Argument wird mit dem angegebenen Wert initialisiert, und dieser Wert wird einer Variablen einer gespeicherten Prozedur zugewiesen. Die Variable kann im Textteil der gespeicherten Prozedur geändert werden, und ihr Endwert kann an ein aufrufendes Programm übergeben werden. Im Textteil einer gespeicherten Prozedur können Ausgabeargumenten nur mithilfe von Variablen Werte zugewiesen werden.

Ausgabeargumenten können auch nicht initialisierte Variablen übergeben werden. Wenn die zugehörige Variable nicht zugewiesen ist, gibt das Ausgabeargument NULL zurück.

OUT und OUTPUT sind gleichbedeutend.

Standard: IN

arg_data_type

SQL-Datentyp

Einschränkungen¶

Ausgabeargumente müssen in der Definition einer gespeicherten Prozedur angegeben werden.
Ausgabeargumente können nicht als optionale Argumente angegeben werden. Das heißt: Ausgabeargumente können nicht mit dem DEFAULT-Schlüsselwort angegeben werden.
Im Textteil einer gespeicherten Prozedur müssen Variablen verwendet werden, um den Ausgabeargumenten Werte zuzuweisen.
Dieselbe Variable kann nicht für mehrere Ausgabeargumente verwendet werden.
Sitzungsvariablen können nicht an Ausgabeargumente übergeben werden.
Benutzerdefinierte Funktionen (UDFs) unterstützen keine Ausgabeargumente.
Gespeicherte Prozeduren, die in anderen Sprachen als SQL geschrieben sind, unterstützen keine Ausgabeargumente.
Ausgabeargumente können nicht in asynchronen untergeordneten Jobs verwendet werden.
Gespeicherte Prozeduren sind auf 500 Argumente beschränkt, einschließlich Eingabe- und Ausgabeargumente.

Beispiele¶

Einfaches Beispiel für die Verwendung von Argumenten, die an eine gespeicherte Prozedur übergeben werden
Verwenden eines Arguments in einer SQL-Anweisung (Bindung)
Verwenden eines Arguments als Objektbezeichner
Verwenden eines Arguments beim Erstellen einer Zeichenfolge für eine SQL-Anweisung
Verwenden eines Ausgabearguments, um einen einzelnen Wert zurückzugeben
Verwenden von Ausgabeargumenten, um mehrere Werte für mehrere Aufrufe einer gespeicherten Prozedur zurückzugeben
Verwendung eines Ausgabearguments mit einem anderen Datentyp als der Eingabewert einer aufrufenden Prozedur
Verwendung eines Ausgabearguments, um den Gesamtumsatz eines Mitarbeiters in einem Quartal zurückzugeben

Einfaches Beispiel für die Verwendung von Argumenten, die an eine gespeicherte Prozedur übergeben werden¶

Die folgende gespeicherte Prozedur verwendet die Werte der Argumente aus IF- und RETURN-Anweisungen.

CREATE OR REPLACE PROCEDURE return_greater(number_1 INTEGER, number_2 INTEGER)
RETURNS INTEGER NOT NULL
LANGUAGE SQL
AS
BEGIN
  IF (number_1 > number_2) THEN
    RETURN number_1;
  ELSE
    RETURN number_2;
  END IF;
END;

Copy

CREATE OR REPLACE PROCEDURE return_greater(number_1 INTEGER, number_2 INTEGER)
RETURNS INTEGER NOT NULL
LANGUAGE SQL
AS
$$
BEGIN
  IF (number_1 > number_2) THEN
    RETURN number_1;
  ELSE
    RETURN number_2;
  END IF;
END;
$$
;

Copy

Im Folgenden finden Sie ein Beispiel für das Aufrufen der gespeicherten Prozedur:

CALL return_greater(2, 3);

Copy

Verwenden eines Arguments in einer SQL-Anweisung (Bindung)¶

Wenn Sie ein Argument in einer SQL-Anweisung verwenden müssen, setzen Sie wie bei Snowflake Scripting-Variablen einen Doppelpunkt (:) vor den Argumentnamen Weitere Informationen dazu finden Sie unter Verwenden einer Variablen in einer SQL-Anweisung (Bindung).

Die folgenden Abschnitte enthalten Beispiele für die Verwendung von Bindungsvariablen in gespeicherten Prozeduren:

Beispiel für die Verwendung einer Bindungsvariable in einer WHERE-Klausel
Beispiel für die Verwendung einer Bindungsvariablen, um den Wert einer Eigenschaft zu setzen
Beispiel für die Verwendung von Bindungsvariablen zum Setzen von Parametern in einem Befehl
Beispiele, die eine Bindungsvariable für ein Array verwenden

Beispiel für die Verwendung einer Bindungsvariable in einer WHERE-Klausel¶

Die folgende gespeicherte Prozedur verwendet das Argument id in der WHERE-Klausel einer SELECT-Anweisung. In der WHERE-Klausel wird das Argument als :id angegeben.

CREATE OR REPLACE PROCEDURE find_invoice_by_id(id VARCHAR)
RETURNS TABLE (id INTEGER, price NUMBER(12,2))
LANGUAGE SQL
AS
DECLARE
  res RESULTSET DEFAULT (SELECT * FROM invoices WHERE id = :id);
BEGIN
  RETURN TABLE(res);
END;

Copy

CREATE OR REPLACE PROCEDURE find_invoice_by_id(id VARCHAR)
RETURNS TABLE (id INTEGER, price NUMBER(12,2))
LANGUAGE SQL
AS
$$
DECLARE
  res RESULTSET DEFAULT (SELECT * FROM invoices WHERE id = :id);
BEGIN
  RETURN TABLE(res);
END;
$$
;

Copy

Im Folgenden finden Sie ein Beispiel für das Aufrufen der gespeicherten Prozedur:

CALL find_invoice_by_id('2');

Copy

Darüber hinaus bietet die TO_QUERY-Funktion eine einfache Syntax für die Übernahme einer SQL-Zeichenfolge direkt in die FROM-Klausel einer SELECT-Anweisung. Für einen Vergleich der TO_QUERY-Funktion mit der dynamischen SQL-Funktion siehe Erstellen von SQL zur Laufzeit.

Beispiel für die Verwendung einer Bindungsvariablen, um den Wert einer Eigenschaft zu setzen¶

Die folgende gespeicherte Prozedur verwendet das Argument comment, um einen Kommentar für eine Tabelle in einer CREATE TABLE-Anweisung hinzuzufügen. In der Anweisung wird das Argument als :comment angegeben.

CREATE OR REPLACE PROCEDURE test_bind_comment(comment VARCHAR)
RETURNS STRING
LANGUAGE SQL
AS
BEGIN
  CREATE OR REPLACE TABLE test_table_with_comment(a VARCHAR, n NUMBER) COMMENT = :comment;
END;

Copy

CREATE OR REPLACE PROCEDURE test_bind_comment(comment VARCHAR)
RETURNS STRING
LANGUAGE SQL
AS
$$
BEGIN
  CREATE OR REPLACE TABLE test_table_with_comment(a VARCHAR, n NUMBER) COMMENT = :comment;
END;
$$
;

Copy

Im Folgenden finden Sie ein Beispiel für das Aufrufen der gespeicherten Prozedur:

CALL test_bind_comment('My Test Table');

Copy

Zeigen Sie den Kommentar für die Tabelle an, indem Sie die Ansicht TABLES im INFORMATION_SCHEMA abfragen:

SELECT comment FROM information_schema.tables WHERE table_name='TEST_TABLE_WITH_COMMENT';

Copy

+---------------+
| COMMENT       |
|---------------|
| My Test Table |
+---------------+

Sie können den Kommentar auch anzeigen, indem Sie den Befehl SHOW TABLES ausführen.

Beispiel für die Verwendung von Bindungsvariablen zum Setzen von Parametern in einem Befehl¶

Angenommen, Sie haben einen Stagingbereich namens st mit CSV-Dateien:

CREATE OR REPLACE STAGE st;
PUT file://good_data.csv @st;
PUT file://errors_data.csv @st;

Copy

Sie möchten die Daten aus den CSV-Dateien in eine Tabelle mit dem Namen test_bind_stage_and_load laden:

CREATE OR REPLACE TABLE test_bind_stage_and_load (a VARCHAR, b VARCHAR, c VARCHAR);

Copy

Die folgende gespeicherte Prozedur verwendet die Parameter FROM, ON_ERROR und VALIDATION_MODE in einer COPY INTO <Tabelle>-Anweisung. In der Anweisung werden die Parameterwerte als :my_stage_name, :on_error, bzw. :valid_mode angegeben.

CREATE OR REPLACE PROCEDURE test_copy_files_validate(
  my_stage_name VARCHAR,
  on_error VARCHAR,
  valid_mode VARCHAR)
RETURNS STRING
LANGUAGE SQL
AS
BEGIN
  COPY INTO test_bind_stage_and_load
    FROM :my_stage_name
    ON_ERROR=:on_error
    FILE_FORMAT=(type='csv')
    VALIDATION_MODE=:valid_mode;
END;

Copy

CREATE OR REPLACE PROCEDURE test_copy_files_validate(
  my_stage_name VARCHAR,
  on_error VARCHAR,
  valid_mode VARCHAR)
RETURNS STRING
LANGUAGE SQL
AS
$$
BEGIN
  COPY INTO test_bind_stage_and_load
    FROM :my_stage_name
    ON_ERROR=:on_error
    FILE_FORMAT=(type='csv')
    VALIDATION_MODE=:valid_mode;
END;
$$
;

Copy

Im Folgenden finden Sie ein Beispiel für das Aufrufen der gespeicherten Prozedur:

CALL test_copy_files_validate('@st', 'skip_file', 'return_all_errors');

Copy

Beispiele, die eine Bindungsvariable für ein Array verwenden¶

Sie können eine Bindungsvariable, die ein Array <label-data_type_array> repräsentiert, mit dem Spread-Operator (**) in eine Liste von Einzelwerten expandieren. Weitere Informationen und Beispiele finden Sie unter Erweiterungsoperatoren.

Verwenden eines Arguments als Objektbezeichner¶

Wenn Sie ein Argument verwenden müssen, um auf ein Objekt zu verweisen (z. B. einen Tabellennamen in der Klausel FROM einer Anweisung SELECT), verwenden Sie das Schlüsselwort IDENTIFIER, um anzugeben, dass das Argument einen Objektbezeichner darstellt. Beispiel:

CREATE OR REPLACE PROCEDURE get_row_count(table_name VARCHAR)
RETURNS INTEGER NOT NULL
LANGUAGE SQL
AS
DECLARE
  row_count INTEGER DEFAULT 0;
  res RESULTSET DEFAULT (SELECT COUNT(*) AS COUNT FROM IDENTIFIER(:table_name));
  c1 CURSOR FOR res;
BEGIN
  FOR row_variable IN c1 DO
    row_count := row_variable.count;
  END FOR;
  RETURN row_count;
END;

Copy

CREATE OR REPLACE PROCEDURE get_row_count(table_name VARCHAR)
RETURNS INTEGER NOT NULL
LANGUAGE SQL
AS
$$
DECLARE
  row_count INTEGER DEFAULT 0;
  res RESULTSET DEFAULT (SELECT COUNT(*) AS COUNT FROM IDENTIFIER(:table_name));
  c1 CURSOR FOR res;
BEGIN
  FOR row_variable IN c1 DO
    row_count := row_variable.count;
  END FOR;
  RETURN row_count;
END;
$$
;

Copy

Im Folgenden finden Sie ein Beispiel für das Aufrufen der gespeicherten Prozedur:

CALL get_row_count('invoices');

Copy

Im folgenden Beispiel wird eine CREATE TABLE … AS SELECT (CTAS)-Anweisung in einer gespeicherten Prozedur auf der Grundlage der in den Argumenten angegebenen Tabellennamen ausgeführt.

CREATE OR REPLACE PROCEDURE ctas_sp(existing_table VARCHAR, new_table VARCHAR)
  RETURNS TEXT
  LANGUAGE SQL
AS
BEGIN
  CREATE OR REPLACE TABLE IDENTIFIER(:new_table) AS
    SELECT * FROM IDENTIFIER(:existing_table);
  RETURN 'Table created';
END;

Copy

CREATE OR REPLACE PROCEDURE ctas_sp(existing_table VARCHAR, new_table VARCHAR)
  RETURNS TEXT
  LANGUAGE SQL
AS
$$
BEGIN
  CREATE OR REPLACE TABLE IDENTIFIER(:new_table) AS
    SELECT * FROM IDENTIFIER(:existing_table);
  RETURN 'Table created';
END;
$$
;

Copy

Bevor Sie die Prozedur aufrufen, erstellen Sie eine einfache Tabelle und fügen Daten ein:

CREATE OR REPLACE TABLE test_table_for_ctas_sp (
  id NUMBER(2),
  v  VARCHAR(2))
AS SELECT
  column1,
  column2,
FROM
  VALUES
    (1, 'a'),
    (2, 'b'),
    (3, 'c');

Copy

Rufen Sie die gespeicherte Prozedur auf, um eine neue Tabelle zu erstellen, die auf dieser Tabelle basiert:

CALL ctas_sp('test_table_for_ctas_sp', 'test_table_for_ctas_sp_backup');

Copy

Verwenden eines Arguments beim Erstellen einer Zeichenfolge für eine SQL-Anweisung¶

Beachten Sie, dass Sie beim Erstellen einer SQL-Anweisung als Zeichenfolge (String), die an EXECUTE IMMEDIATE übergeben wird (siehe Zuweisen einer Abfrage zu einem deklarierten RESULTSET), dem Argument keinen Doppelpunkt voranstellen dürfen. Beispiel:

CREATE OR REPLACE PROCEDURE find_invoice_by_id_via_execute_immediate(id VARCHAR)
RETURNS TABLE (id INTEGER, price NUMBER(12,2))
LANGUAGE SQL
AS
DECLARE
  select_statement VARCHAR;
  res RESULTSET;
BEGIN
  select_statement := 'SELECT * FROM invoices WHERE id = ' || id;
  res := (EXECUTE IMMEDIATE :select_statement);
  RETURN TABLE(res);
END;

Copy

CREATE OR REPLACE PROCEDURE find_invoice_by_id_via_execute_immediate(id VARCHAR)
RETURNS TABLE (id INTEGER, price NUMBER(12,2))
LANGUAGE SQL
AS
$$
DECLARE
  select_statement VARCHAR;
  res RESULTSET;
BEGIN
  select_statement := 'SELECT * FROM invoices WHERE id = ' || id;
  res := (EXECUTE IMMEDIATE :select_statement);
  RETURN TABLE(res);
END;
$$
;

Copy

Verwenden eines Ausgabearguments, um einen einzelnen Wert zurückzugeben¶

Im folgenden Beispiel wird die gespeicherte Prozedur simple_out_sp_demo mit dem Ausgabeargument xout in seiner Definition erstellt. Die gespeicherte Prozedur setzt den Wert von xout auf 2.

CREATE OR REPLACE PROCEDURE simple_out_sp_demo(xout OUT NUMBER)
  RETURNS STRING
  LANGUAGE SQL
AS
BEGIN
  xout := 2;
  RETURN 'Done';
END;

Copy

CREATE OR REPLACE PROCEDURE simple_out_sp_demo(xout OUT NUMBER)
  RETURNS STRING
  LANGUAGE SQL
AS
$$
BEGIN
  xout := 2;
  RETURN 'Done';
END;
$$
;

Copy

Der folgende anonyme Block legt den Wert der x-Variablen auf 1 fest. Dann ruft er die gespeicherte Prozedur simple_out_sp_demo auf und gibt die Variable als Argument an.

BEGIN
  LET x := 1;
  CALL simple_out_sp_demo(:x);
  RETURN x;
END;

Copy

EXECUTE IMMEDIATE
$$
BEGIN
  LET x := 1;
  CALL simple_out_sp_demo(:x);
  RETURN x;
END;
$$
;

Copy

Die Ausgabe zeigt, dass die gespeicherte Prozedur simple_out_sp_demo einen Vorgang ausgeführt hat, um den Wert des Ausgabearguments auf 2 zu setzen, und diesen Wert dann an den anonymen Block zurückgegeben hat.

+-----------------+
| anonymous block |
|-----------------|
|               2 |
+-----------------+

Der folgende anonyme Block ruft die gespeicherte Prozedur simple_out_sp_demo auf und gibt einen Fehler zurück, da sie versucht, dem Ausgabeargument einen Wert zuzuweisen, indem sie einen Ausdruck statt einer Variablen verwendet.

BEGIN
  LET x := 1;
  CALL simple_out_sp_demo(:x + 2);
  RETURN x;
END;

Copy

EXECUTE IMMEDIATE
$$
BEGIN
  LET x := 1;
  CALL simple_out_sp_demo(:x + 2);
  RETURN x;
END;
$$
;

Copy

Verwenden von Ausgabeargumenten, um mehrere Werte für mehrere Aufrufe einer gespeicherten Prozedur zurückzugeben¶

Das folgende Beispiel veranschaulicht das folgende Verhalten in Bezug auf gespeicherte Prozeduren sowie Eingabe- und Ausgabeargumente:

Eine gespeicherte Prozedur kann mehrere Eingabe- und Ausgabeargumente in ihrer Definition haben.
Ein Programm kann eine gespeicherte Prozedur mit Ausgabeargumenten mehrfach aufrufen, und die Werte der Ausgabeargumente bleiben nach jedem Aufruf erhalten.
Eingabeargumente geben keine Werte an das aufrufende Programm zurück.

Erstellen Sie die gespeicherte Prozedur multiple_out_sp_demo mit mehreren Eingabe- und Ausgabeargumenten in der Definition. Die gespeicherte Prozedur führt dieselben Vorgänge mit den entsprechenden Eingabe- und Ausgabeargumenten aus. Die gespeicherte Prozedur fügt zum Beispiel 1 zum p1_in-Eingabeargument und zum p1_out-Ausgabeargument hinzu.

CREATE OR REPLACE PROCEDURE multiple_out_sp_demo(
    p1_in NUMBER,
    p1_out OUT NUMBER,
    p2_in VARCHAR(100),
    p2_out OUT VARCHAR(100),
    p3_in BOOLEAN,
    p3_out OUT BOOLEAN)
  RETURNS NUMBER
  LANGUAGE SQL
AS
BEGIN
  p1_in := p1_in + 1;
  p1_out := p1_out + 1;
  p2_in := p2_in || ' hi ';
  p2_out := p2_out || ' hi ';
  p3_in := (NOT p3_in);
  p3_out := (NOT p3_out);
  RETURN 1;
END;

Copy

CREATE OR REPLACE PROCEDURE multiple_out_sp_demo(
    p1_in NUMBER,
    p1_out OUT NUMBER,
    p2_in VARCHAR(100),
    p2_out OUT VARCHAR(100),
    p3_in BOOLEAN,
    p3_out OUT BOOLEAN)
  RETURNS NUMBER
  LANGUAGE SQL
AS
$$
BEGIN
  p1_in := p1_in + 1;
  p1_out := p1_out + 1;
  p2_in := p2_in || ' hi ';
  p2_out := p2_out || ' hi ';
  p3_in := (NOT p3_in);
  p3_out := (NOT p3_out);
  RETURN 1;
END;
$$
;

Copy

Der folgende anonyme Block weist den Variablen, die den Argumenten der gespeicherten Prozedur multiple_out_sp_demo entsprechen, Werte zu und ruft dann die gespeicherte Prozedur mehrfach auf. Der erste Aufruf verwendet die im anonymen Block angegebenen Variablenwerte, aber bei jedem nachfolgenden Aufruf werden die Werte verwendet, die von den Ausgabeargumenten in der gespeicherten Prozedur multiple_out_sp_demo zurückgegeben werden.

BEGIN
  LET x_in INT := 1;
  LET x_out INT := 1;
  LET y_in VARCHAR(100) := 'hello';
  LET y_out VARCHAR(100) := 'hello';
  LET z_in BOOLEAN := true;
  LET z_out BOOLEAN := true;

  CALL multiple_out_sp_demo(:x_in, :x_out, :y_in, :y_out, :z_in, :z_out);
  CALL multiple_out_sp_demo(:x_in, :x_out, :y_in, :y_out, :z_in, :z_out);
  CALL multiple_out_sp_demo(:x_in, :x_out, :y_in, :y_out, :z_in, :z_out);
  RETURN [x_in, x_out, y_in, y_out, z_in, z_out];
END;

Copy

EXECUTE IMMEDIATE
$$
BEGIN
  LET x_in INT := 1;
  LET x_out INT := 1;
  LET y_in VARCHAR(100) := 'hello';
  LET y_out VARCHAR(100) := 'hello';
  LET z_in BOOLEAN := true;
  LET z_out BOOLEAN := true;

  CALL multiple_out_sp_demo(:x_in, :x_out, :y_in, :y_out, :z_in, :z_out);
  CALL multiple_out_sp_demo(:x_in, :x_out, :y_in, :y_out, :z_in, :z_out);
  CALL multiple_out_sp_demo(:x_in, :x_out, :y_in, :y_out, :z_in, :z_out);
  RETURN [x_in, x_out, y_in, y_out, z_in, z_out];
END;
$$
;

Copy

+------------------------+
| anonymous block        |
|------------------------|
| [                      |
|   1,                   |
|   4,                   |
|   "hello",             |
|   "hello hi  hi  hi ", |
|   true,                |
|   false                |
| ]                      |
+------------------------+

Verwendung eines Ausgabearguments mit einem anderen Datentyp als der Eingabewert einer aufrufenden Prozedur¶

In einigen Anwendungsfällen kann es zu einer Diskrepanz zwischen dem Datentyp des Wertes, der an eine gespeicherte Prozedur übergeben wird, und dem Datentyp des Ausgabearguments der Prozedur kommen. In diesen Fällen werden automatisch unterstützte Koersionen ausgeführt.

Bemerkung

Obwohl Koersion in einigen Fällen unterstützt wird, wird dies nicht empfohlen.

Im folgenden Beispiel wird die automatische Konvertierung eines FLOAT-Wertes veranschaulicht, der an ein Ausgabeargument mit einem NUMBER-Datentyp übergeben wird. Der FLOAT-Wert wird automatisch in einen NUMBER-Wert umgewandelt und dann an den aufrufenden anonymen Block zurückgegeben.

Erstellen Sie die gespeicherte Prozedur sp_out_coercion, die ein Ausgabeargument vom Typ NUMBER nimmt:

CREATE OR REPLACE PROCEDURE sp_out_coercion(x OUT NUMBER)
  RETURNS STRING
  LANGUAGE SQL
AS
BEGIN
  x := x * 2;
  RETURN 'Done';
END;

Copy

CREATE OR REPLACE PROCEDURE sp_out_coercion(x OUT NUMBER)
  RETURNS STRING
  LANGUAGE SQL
AS
$$
BEGIN
  x := x * 2;
  RETURN 'Done';
END;
$$
;

Copy

Führen Sie einen anonymen Block aus, der einen FLOAT-Wert an die gespeicherte Prozedur sp_out_coercion übergibt:

BEGIN
  LET a FLOAT := 500.662;
  CALL sp_out_coercion(:a);
  RETURN a || ' (Type ' || SYSTEM$TYPEOF(a) || ')';
END;

Copy

EXECUTE IMMEDIATE
$$
BEGIN
  LET a FLOAT := 500.662;
  CALL sp_out_coercion(:a);
  RETURN a || ' (Type ' || SYSTEM$TYPEOF(a) || ')';
END;
$$
;

Copy

Die Ausgabe zeigt sowohl den zurückgegebenen Wert als auch den Datentyp des zurückgegebenen Wertes an, indem die SYSTEM$TYPEOF-Funktion aufgerufen wird. Beachten Sie, dass der Wert aus einem NUMBER-Wert zurück zu einem FLOAT-Wert erzwungen wird, nachdem er von der gespeicherten Prozedur zurückgegeben wurde:

+---------------------------+
| anonymous block           |
|---------------------------|
| 1002 (Type FLOAT[DOUBLE]) |
+---------------------------+

Verwendung eines Ausgabearguments, um den Gesamtumsatz eines Mitarbeiters in einem Quartal zurückzugeben¶

Dieses Beispiel verwendet die folgende quarterly_sales-Tabelle:

CREATE OR REPLACE TABLE quarterly_sales(
  empid INT,
  amount INT,
  quarter TEXT)
  AS SELECT * FROM VALUES
    (1, 10000, '2023_Q1'),
    (1, 400, '2023_Q1'),
    (2, 4500, '2023_Q1'),
    (2, 35000, '2023_Q1'),
    (1, 5000, '2023_Q2'),
    (1, 3000, '2023_Q2'),
    (2, 200, '2023_Q2'),
    (2, 90500, '2023_Q2'),
    (1, 6000, '2023_Q3'),
    (1, 5000, '2023_Q3'),
    (2, 2500, '2023_Q3'),
    (2, 9500, '2023_Q3'),
    (3, 2700, '2023_Q3'),
    (1, 8000, '2023_Q4'),
    (1, 10000, '2023_Q4'),
    (2, 800, '2023_Q4'),
    (2, 4500, '2023_Q4'),
    (3, 2700, '2023_Q4'),
    (3, 16000, '2023_Q4'),
    (3, 10200, '2023_Q4');

Copy

Erstellen Sie die gespeicherte Prozedur sales_total_out_sp_demo, die zwei Eingabeargumente für die Mitarbeiter-ID und das Quartal sowie ein Ausgabeargument nimmt, um die Umsatzsumme für den angegebenen Mitarbeiter und das Quartal zu berechnen.

CREATE OR REPLACE PROCEDURE sales_total_out_sp_demo(
    id INT,
    quarter VARCHAR(20),
    total_sales OUT NUMBER(38,0))
  RETURNS STRING
  LANGUAGE SQL
AS
$$
BEGIN
  SELECT SUM(amount) INTO total_sales FROM quarterly_sales
    WHERE empid = :id AND
          quarter = :quarter;
  RETURN 'Done';
END;
$$
;

Copy

Erstellen Sie die gespeicherte Prozedur emp_quarter_calling_sp_demo, die die gespeicherte Prozedur sales_total_out_sp_demo aufruft. Diese gespeicherte Prozedur nimmt auch zwei Eingabeargumente für die Mitarbeiter-ID und das Quartal.

CREATE OR REPLACE PROCEDURE emp_quarter_calling_sp_demo(
    id INT,
    quarter VARCHAR(20))
  RETURNS STRING
  LANGUAGE SQL
AS
BEGIN
  LET x NUMBER(38,0);
  CALL sales_total_out_sp_demo(:id, :quarter, :x);
  RETURN 'Total sales for employee ' || id || ' in quarter ' || quarter || ': ' || x;
END;

Copy

CREATE OR REPLACE PROCEDURE emp_quarter_calling_sp_demo(
    id INT,
    quarter VARCHAR(20))
  RETURNS STRING
  LANGUAGE SQL
AS
$$
BEGIN
  LET x NUMBER(38,0);
  CALL sales_total_out_sp_demo(:id, :quarter, :x);
  RETURN 'Total sales for employee ' || id || ' in quarter ' || quarter || ': ' || x;
END;
$$
;

Copy

Rufen Sie die emp_quarter_calling_sp_demo mit den Argumenten 2 (für die Mitarbeiter-ID) und '2023_Q4' (für das Quartal) auf.

CALL emp_quarter_calling_sp_demo(2, '2023_Q4');

Copy

+-----------------------------------------------------+
| emp_quarter_calling_sp_demo                         |
|-----------------------------------------------------|
| Total sales for employee 2 in quarter 2023_Q4: 5300 |
+-----------------------------------------------------+

Zurückgeben von tabellarischen Daten¶

Wenn Sie aus Ihrer gespeicherten Prozedur Tabellendaten zurückgeben müssen (z. B. Daten aus einem RESULTSET), geben Sie RETURNS TABLE(…) in Ihrer CREATE PROCEDURE Anweisung an.

Wenn Sie die Snowflake-Datentypen der Spalten in der zurückgegebenen Tabelle kennen, geben Sie die Spaltennamen und -typen in RETURNS TABLE() an.

CREATE OR REPLACE PROCEDURE get_top_sales()
RETURNS TABLE (sales_date DATE, quantity NUMBER)
...

Copy

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 ()
...

Copy

Bemerkung

Derzeit können Sie in der RETURNS TABLE(...)-Klausel den Datentyp GEOGRAPHY als Spaltentyp angeben. Dies gilt unabhängig davon, ob Sie eine gespeicherte oder eine anonyme Prozedur erstellen.

CREATE OR REPLACE PROCEDURE test_return_geography_table_1()
  RETURNS TABLE(g GEOGRAPHY)
  ...

Copy

WITH test_return_geography_table_1() AS PROCEDURE
  RETURNS TABLE(g GEOGRAPHY)
  ...
CALL test_return_geography_table_1();

Copy

Wenn Sie versuchen, GEOGRAPHY als Spaltentyp anzugeben, führt der Aufruf der gespeicherten Prozedur zu einem Fehler:

Stored procedure execution error: data type of returned table does not match expected returned table type

Copy

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()
  ...

Copy

WITH test_return_geography_table_1() AS PROCEDURE
  RETURNS TABLE()
  ...
CALL test_return_geography_table_1();

Copy

Wenn Sie die Daten in einem RESULTSET zurückgeben müssen, verwenden Sie in Ihrer RETURN-Anweisung TABLE().

Beispiel:

CREATE OR REPLACE PROCEDURE get_top_sales()
RETURNS TABLE (sales_date DATE, quantity NUMBER)
LANGUAGE SQL
AS
DECLARE
  res RESULTSET DEFAULT (SELECT sales_date, quantity FROM sales ORDER BY quantity DESC LIMIT 10);
BEGIN
  RETURN TABLE(res);
END;

Copy

CREATE OR REPLACE PROCEDURE get_top_sales()
RETURNS TABLE (sales_date DATE, quantity NUMBER)
LANGUAGE SQL
AS
$$
DECLARE
  res RESULTSET DEFAULT (SELECT sales_date, quantity FROM sales ORDER BY quantity DESC LIMIT 10);
BEGIN
  RETURN TABLE(res);
END;
$$
;

Copy

Im Folgenden finden Sie ein Beispiel für das Aufrufen der gespeicherten Prozedur:

CALL get_top_sales();

Copy

Aufrufen einer gespeicherten Prozedur aus einer anderen gespeicherten Prozedur¶

Wenn Sie in einer gespeicherten Prozedur eine andere gespeicherte Prozedur aufrufen müssen, verwenden Sie einen der folgenden Ansätze:

Aufrufen einer gespeicherten Prozedur ohne Verwendung des zurückgegebenen Wertes
Verwenden des vom Aufruf einer gespeicherten Prozedur zurückgegebenen Wertes
Übergabe von Werten für Ausgabeargumente von einer gespeicherten Prozedur an eine aufrufende gespeicherte Prozedur

Aufrufen einer gespeicherten Prozedur ohne Verwendung des zurückgegebenen Wertes¶

Verwenden Sie eine CALL-Anweisung, um die gespeicherte Prozedur aufzurufen (wie Sie es normalerweise tun würden).

Wenn Sie in der CALL-Anweisung Variablen oder Argumente als Eingabeargumente übergeben müssen, denken Sie daran, dem Variablennamen einen Doppelpunkt (:) voranzustellen. (siehe Verwenden einer Variablen in einer SQL-Anweisung (Bindung)).

Es folgt ein Beispiel für eine gespeicherte Prozedur, die eine andere gespeicherte Prozedur aufruft, aber nicht vom Rückgabewert abhängig ist.

Erstellen Sie zunächst eine Tabelle zur Verwendung im Beispiel:

-- Create a table for use in the example.
CREATE OR REPLACE TABLE int_table (value INTEGER);

Copy

Erstellen Sie dann die gespeicherte Prozedur, die Sie von einer anderen gespeicherten Prozedur aus aufrufen werden:

-- Create a stored procedure to be called from another stored procedure.
CREATE OR REPLACE PROCEDURE insert_value(value INTEGER)
RETURNS VARCHAR NOT NULL
LANGUAGE SQL
AS
BEGIN
  INSERT INTO int_table VALUES (:value);
  RETURN 'Rows inserted: ' || SQLROWCOUNT;
END;

Copy

-- Create a stored procedure to be called from another stored procedure.
CREATE OR REPLACE PROCEDURE insert_value(value INTEGER)
RETURNS VARCHAR NOT NULL
LANGUAGE SQL
AS
$$
BEGIN
  INSERT INTO int_table VALUES (:value);
  RETURN 'Rows inserted: ' || SQLROWCOUNT;
END;
$$
;

Copy

Erstellen Sie anschließend eine zweite gespeicherte Prozedur, die die erste gespeicherte Prozedur aufruft:

CREATE OR REPLACE PROCEDURE insert_two_values(value1 INTEGER, value2 INTEGER)
RETURNS VARCHAR NOT NULL
LANGUAGE SQL
AS
BEGIN
  CALL insert_value(:value1);
  CALL insert_value(:value2);
  RETURN 'Finished calling stored procedures';
END;

Copy

CREATE OR REPLACE PROCEDURE insert_two_values(value1 INTEGER, value2 INTEGER)
RETURNS VARCHAR NOT NULL
LANGUAGE SQL
AS
$$
BEGIN
  CALL insert_value(:value1);
  CALL insert_value(:value2);
  RETURN 'Finished calling stored procedures';
END;
$$
;

Copy

Rufen Sie schließlich die zweite gespeicherte Prozedur auf:

CALL insert_two_values(4, 5);

Copy

Verwenden des vom Aufruf einer gespeicherten Prozedur zurückgegebenen Wertes¶

Wenn Sie eine gespeicherte Prozedur aufrufen, die einen Skalarwert zurückgibt, und Sie auf diesen Wert zugreifen müssen, verwenden Sie die INTO :snowflake_scripting_variable-Klausel in der CALL-Anweisung, um den Wert in einer Snowflake Scripting-Variablen zu erfassen.

Im folgenden Beispiel wird die gespeicherte Prozedur get_row_count aufgerufen, die in Verwenden eines Arguments als Objektbezeichner definiert wurde.

CREATE OR REPLACE PROCEDURE count_greater_than(table_name VARCHAR, maximum_count INTEGER)
  RETURNS BOOLEAN NOT NULL
  LANGUAGE SQL
  AS
  DECLARE
    count1 NUMBER;
  BEGIN
    CALL get_row_count(:table_name) INTO :count1;
    IF (:count1 > maximum_count) THEN
      RETURN TRUE;
    ELSE
      RETURN FALSE;
    END IF;
  END;

Copy

CREATE OR REPLACE PROCEDURE count_greater_than(table_name VARCHAR, maximum_count INTEGER)
  RETURNS BOOLEAN NOT NULL
  LANGUAGE SQL
  AS
  $$
  DECLARE
    count1 NUMBER;
  BEGIN
    CALL get_row_count(:table_name) INTO :count1;
    IF (:count1 > maximum_count) THEN
      RETURN TRUE;
    ELSE
      RETURN FALSE;
    END IF;
  END;
  $$
  ;

Copy

Im Folgenden finden Sie ein Beispiel für das Aufrufen der gespeicherten Prozedur:

CALL count_greater_than('invoices', 3);

Copy

Wenn die gespeicherte Prozedur eine Tabelle zurückgibt, können Sie den Rückgabewert erfassen, indem Sie ein RESULTSET auf die Zeichenfolge setzen, die die CALL-Anweisung enthält. (siehe Zuweisen einer Abfrage zu einem deklarierten RESULTSET).

Um den Rückgabewert des Aufrufs abzurufen, können Sie ein CURSOR für das RESULTSET verwenden. Beispiel:

DECLARE
  res1 RESULTSET;
BEGIN
res1 := (CALL my_procedure());
LET c1 CURSOR FOR res1;
FOR row_variable IN c1 DO
  IF (row_variable.col1 > 0) THEN
    ...;
  ELSE
    ...;
  END IF;
END FOR;
...

Copy

Übergabe von Werten für Ausgabeargumente von einer gespeicherten Prozedur an eine aufrufende gespeicherte Prozedur¶

Wenn in der Definition einer gespeicherten Snowflake Scripting-Prozedur ein Ausgabeargument angegeben ist, kann die gespeicherte Prozedur den aktuellen Wert des Ausgabearguments an eine aufrufende gespeicherte Prozedur zurückgeben. Die gespeicherte Prozedur nimmt einen Anfangswert für das Ausgabeargument, speichert den Wert in einer Variablen im Textkörper der Prozedur und führt optional Vorgänge aus, um den Wert der Variablen zu ändern. Die gespeicherte Prozedur gibt dann den aktualisierten Wert an die aufrufende gespeicherte Prozedur zurück.

Ein Beispiel dazu finden Sie unter Verwendung eines Ausgabearguments, um den Gesamtumsatz eines Mitarbeiters in einem Quartal zurückzugeben.

Verwenden von geschachtelten gespeicherten Prozeduren¶

Eine geschachtelte gespeicherte Prozedur ist eine gespeicherte Prozedur, die im Bereich eines anonymen Blocks oder eines Blocks in einer anderen gespeicherten Prozedur (der übergeordneten gespeicherten Prozedur) definiert ist.

Sie deklarieren eine geschachtelte gespeicherte Prozedur im DECLARE-Abschnitt eines Blocks, der Teil einer CREATE PROCEDURE-Anweisung sein kann. Das folgende Beispiel zeigt eine Deklaration für eine geschachtelte gespeicherte Prozedur:

DECLARE
  <nested_stored_procedure_name> PROCEDURE (<arguments>)
     RETURNS <data_type>
     AS
     BEGIN
       <nested_procedure_procedure_statements>
     END;
BEGIN
  <statements>
END;

Copy

Weitere Informationen zur Deklarationssyntax einer geschachtelten gespeicherten Prozedur finden Sie unter Deklarationssyntax für verschachtelte gespeicherte Prozedur.

Eine geschachtelte gespeicherte Prozedur existiert nur im Bereich ihres -Blocks-, ,. Sie kann von jedem Abschnitt ihres Blocks aus aufgerufen werden (DECLARE, BEGIN … ENDund EXCEPTION). Ein einzelner Block kann mehrere geschachtelte gespeicherte Prozeduren enthalten, und eine geschachtelte gespeicherte Prozedur kann eine andere geschachtelte gespeicherte Prozedur im selben Block aufrufen. Eine geschachtelte Prozedur kann nicht von außerhalb ihres Blocks aufgerufen oder darauf zugegriffen werden.

Eine geschachtelte gespeicherte Prozedur wird in demselben Sicherheitskontext ausgeführt wie der Block, der sie definiert. Wenn eine geschachtelte gespeicherte Prozedur in einer übergeordneten gespeicherten Prozedur definiert ist, wird sie automatisch mit den gleichen Berechtigungen ausgeführt wie die übergeordnete gespeicherte Prozedur.

Bemerkung

Sowohl eine Deklaration für eine geschachtelte gespeicherte Prozedur als auch der CALL WITH-Befehl erstellt eine temporäre gespeicherte Prozedur mit eingeschränktem Bereich. Sie unterscheiden sich wie folgt:

Eine CALL WITH-Anweisung kann überall dort erscheinen, wo eine SQL-Anweisung erscheinen kann, auch innerhalb einer gespeicherten Prozedur, aber eine Deklaration für eine geschachtelte gespeicherte Prozedur muss sich in einem Snowflake Scripting-Block befinden.
Eine gespeicherte CALL WITH-Prozedur existiert nur im Bereich ihrer -Anweisung, aber eine geschachtelte gespeicherte Prozedur existiert im Bereich ihres Snowflake Scripting-Blocks.

Vorteile geschachtelter gespeicherter Prozeduren¶

Geschachtelte gespeicherte Prozeduren bieten die folgenden Vorteile:

Sie können die Sicherheit verbessern und vereinfachen, indem Sie die Logik innerhalb eines anonymen Blocks oder einer übergeordneten gespeicherten Prozedur kapseln, die den Zugriff darauf von außerhalb des Blocks oder der übergeordneten Prozedur verhindert.
Sie halten den Code modular, indem sie ihn logisch in kleinere Blöcke aufteilen, was die Wartung und das Debuggen erleichtert.
Sie verbessern die Wartungsfreundlichkeit, indem die Notwendigkeit von globalen Variablen oder zusätzlichen Argumenten reduziert wird, da eine geschachtelte gespeicherte Prozedur direkt auf die lokalen Variablen ihres Blocks zugreifen kann.

Nutzungshinweise für das Aufrufen geschachtelter gespeicherter Prozeduren¶

Die folgenden Nutzungshinweise gelten für das Aufrufen einer geschachtelten gespeicherten Prozedur:

Um Argumente an eine geschachtelte gespeicherte Prozedur zu übergeben, kann ein Block konstante Werte, Snowflake Scripting-Variablen, Bindungsvariablen, SQL-(Sitzungs)-Variablen verwenden.
Wenn der Datentyp des übergebenen Werts und der Datentyp eines Arguments nicht übereinstimmen, führt Snowflake automatisch unterstützte Koersionen aus. Informationen darüber, welche Koersionen Snowflake automatisch ausführen kann, finden Sie unter Datentypkonvertierung.

Nutzungshinweise für Variablen in einer geschachtelten gespeicherten Prozedur¶

Die folgenden Nutzungshinweise gelten für Variablen in einer geschachtelten gespeicherten Prozedur:

Eine geschachtelte gespeicherte Prozedur kann auf Variablen aus ihrem Block verweisen, die vor der Deklaration für die geschachtelte gespeicherte Prozedur im DECLARE-Abschnitt des Blocks deklariert wurden. Sie kann nicht auf Variablen verweisen, die danach im DECLARE-Abschnitt deklariert werden.
Eine geschachtelte gespeicherte Prozedur kann nicht auf Variablen zugreifen, die in einer LET-Anweisung im Abschnitt BEGIN … END eines Blocks deklariert sind.
Der Wert einer referenzierten Variablen spiegelt ihren Wert zum Zeitpunkt des Aufrufs der geschachtelten gespeicherten Prozedur wider.
Eine geschachtelte gespeicherte Prozedur kann den Wert einer referenzierten Variable ändern, und der geänderte Wert bleibt im Block und über mehrere Aufrufe derselben geschachtelten Prozedur hinweg in einer einzigen Ausführung ihres anonymen Blocks oder in einem einzigen Aufruf der übergeordneten gespeicherten Prozedur erhalten.
Der Wert einer Variablen, die vor dem Aufruf einer geschachtelten gespeicherten Prozedur deklariert wurde, kann als Argument an die geschachtelte gespeicherte Prozedur übergeben werden. Der Wert der Variable kann als Argument in einem Aufruf übergeben werden, auch wenn die Variable nach der Deklaration für die geschachtelte gespeicherte Prozedur oder in einer LET-Anweisung deklariert wurde.

Die folgende gespeicherte Prozedur deklariert beispielsweise mehrere Variablen:

CREATE OR REPLACE PROCEDURE outer_sp ()
RETURNS NUMBER
LANGUAGE SQL
AS
$$
DECLARE
  var_before_nested_proc NUMBER DEFAULT 1;
  test_nested_variables PROCEDURE(arg1 NUMBER)
    -- <nested_sp_logic>
  var_after_nested_proc NUMBER DEFAULT 2;
BEGIN
  LET var_let_before_call NUMBER DEFAULT 3;
  LET result := CALL nested_proc(:<var_name>);
  LET var_let_after_call NUMBER DEFAULT 3;
  RETURN result;
END;
$$;

Copy

In diesem Beispiel kann nur var_before_nested_proc in nested_sp_logic referenziert werden.

Beim Aufruf der geschachtelten gespeicherten Prozedur kann der Wert einer der folgenden Variablen als Argument in var_name an die geschachtelte gespeicherte Prozedur übergeben werden:

var_before_nested_proc
var_after_nested_proc
var_let_before_call

Der Wert von var_let_after_call kann nicht als Argument an die geschachtelte gespeicherte Prozedur übergeben werden.

Einschränkungen für geschachtelte gespeicherte Prozeduren¶

Die folgenden Einschränkungen gelten für das Definieren geschachtelter gespeicherter Prozeduren:

Sie können nicht innerhalb anderer geschachtelter gespeicherter Prozeduren oder innerhalb von Kontrollstrukturen definiert werden, wie z. B. FOR- oder WHILE-Schleifen
Jede geschachtelte gespeicherte Prozedur muss in ihrem Block einen eindeutigen Namen haben. Das heißt, geschachtelte gespeicherte Prozeduren können nicht überladen werden.
Sie unterstützen keine Ausgabeargumente (OUT).
Sie unterstützen keine optionalen Argumente mit Standardwerten.

Die folgenden Beschränkungen gelten für das Aufrufen geschachtelter gespeicherter Prozeduren:

Sie können nicht in einer EXECUTE IMMEDIATE-Anweisung aufgerufen werden.
Sie können nicht in asynchronen untergeordneten Jobs aufgerufen werden.
Sie unterstützen keine benannten Eingabeargumente (arg_name => arg). Argumente müssen über die Position angegeben werden. Weitere Informationen dazu finden Sie unter CALL.

Beispiele für geschachtelte gespeicherte Prozeduren¶

Die folgenden Beispiele verwenden geschachtelte gespeicherte Prozeduren:

Eine geschachtelte gespeicherte Prozedur definieren, die tabellarische Daten zurückgibt
Eine geschachtelte gespeicherte Prozedur definieren, die einen Skalarwert zurückgibt
Eine geschachtelte gespeicherte Prozedur in einem anonymen Block definieren
Eine geschachtelte gespeicherte Prozedur definieren, an die Argumente übergeben werden
Eine geschachtelte gespeicherte Prozedur definieren, die eine andere geschachtelte gespeicherte Prozedur aufruft

Eine geschachtelte gespeicherte Prozedur definieren, die tabellarische Daten zurückgibt¶

Das folgende Beispiel definiert eine geschachtelte gespeicherte Prozedur, die tabellarische Daten zurückgibt. Das Beispiel erstellt eine übergeordnete gespeicherte Prozedur namens nested_procedure_example_table mit einer geschachtelten gespeicherten Prozedur namens nested_return_table. Der Code enthält die folgende Logik:

Deklariert eine Variable namens res vom Typ RESULTSET.
Enthält die folgende Logik in der geschachtelten gespeicherten Prozedur:
- Deklariert eine Variable namens res2.
- Fügt Werte in eine Tabelle namens nested_table ein.
- Legt die res2-Variable zu den Ergebnissen einer SELECT für die Tabelle fest.
- Gibt die tabellarischen Daten im Resultset zurück.
Erstellt die Tabelle nested_table in der übergeordneten gespeicherten Prozedur.
Ruft die geschachtelte gespeicherte Prozedur nested_return_table auf und legt den Wert der res-Variablen zu den Ergebnissen des Aufrufs der geschachtelten gespeicherten Prozedur fest.
Gibt die tabellarischen Ergebnisse in der res-Variablen zurück.

CREATE OR REPLACE PROCEDURE nested_procedure_example_table()
RETURNS TABLE()
LANGUAGE SQL
AS
$$
DECLARE
  res RESULTSET;
  nested_return_table PROCEDURE()
    RETURNS TABLE()
    AS
    DECLARE
      res2 RESULTSET;
    BEGIN
      INSERT INTO nested_table VALUES(1);
      INSERT INTO nested_table VALUES(2);
      res2 := (SELECT * FROM nested_table);
      RETURN TABLE(res2);
    END;
BEGIN
  CREATE OR REPLACE TABLE nested_table(col1 INT);
  res := (CALL nested_return_table());
  RETURN TABLE(res);
END;
$$;

Copy

Rufen Sie die gespeicherte Prozedur auf:

CALL nested_procedure_example_table();

Copy

+------+
| COL1 |
|------|
|    1 |
|    2 |
+------+

Eine geschachtelte gespeicherte Prozedur definieren, die einen Skalarwert zurückgibt¶

Das folgende Beispiel definiert eine geschachtelte gespeicherte Prozedur, die einen Skalarwert zurückgibt. Das Beispiel erstellt eine übergeordnete gespeicherte Prozedur namens nested_procedure_example_scalar mit einer geschachtelten gespeicherten Prozedur namens simple_counter. Der Code enthält die folgende Logik:

Deklariert eine Variable namens counter vom Typ NUMBER und setzt den Wert dieser Variablen auf 0.
Gibt an, dass die geschachtelte gespeicherte Prozedur 1 zum aktuellen Wert der counter-Variablen hinzufügt.
Ruft die geschachtelte gespeicherte Prozedur dreimal in der übergeordneten gespeicherten Prozedur auf. Der Wert der counter-Variablen wird zwischen den Aufrufen der geschachtelten gespeicherten Prozedur übertragen.
Gibt den Wert der counter-Variablen zurück, der 3 ist.

CREATE OR REPLACE PROCEDURE nested_procedure_example_scalar()
RETURNS VARCHAR
LANGUAGE SQL
AS
$$
DECLARE
  counter NUMBER := 0;
  simple_counter PROCEDURE()
    RETURNS VARCHAR
    AS
    BEGIN
      counter := counter + 1;
      RETURN counter;
    END;
BEGIN
  CALL simple_counter();
  CALL simple_counter();
  CALL simple_counter();
  RETURN counter;
END;
$$;

Copy

Rufen Sie die gespeicherte Prozedur auf:

CALL nested_procedure_example_scalar();

Copy

+---------------------------------+
| NESTED_PROCEDURE_EXAMPLE_SCALAR |
|---------------------------------|
| 3                               |
+---------------------------------+

Eine geschachtelte gespeicherte Prozedur in einem anonymen Block definieren¶

Das folgende Beispiel ist dasselbe wie das Beispiel unter Eine geschachtelte gespeicherte Prozedur definieren, die einen Skalarwert zurückgibt, außer dass anstelle einer gespeicherten Prozedur eine geschachtelte gespeicherte Prozedur in einem anonymen Block definiert wird:

EXECUTE IMMEDIATE $$
DECLARE
  counter NUMBER := 0;
  simple_counter PROCEDURE()
    RETURNS VARCHAR
    AS
    BEGIN
      counter := counter + 1;
      RETURN counter;
    END;
BEGIN
  CALL simple_counter();
  CALL simple_counter();
  CALL simple_counter();
  RETURN counter;
END;
$$;

Copy

+-----------------+
| anonymous block |
|-----------------|
|               3 |
+-----------------+

Eine geschachtelte gespeicherte Prozedur definieren, an die Argumente übergeben werden¶

Das folgende Beispiel definiert eine geschachtelte gespeicherte Prozedur, an die Argumente übergeben werden. Im Beispiel fügt die geschachtelte gespeicherte Prozedur Werte in die folgende Tabelle ein:

CREATE OR REPLACE TABLE log_nested_values(col1 INT, col2 INT);

Copy

Das Beispiel erstellt eine übergeordnete gespeicherte Prozedur namens nested_procedure_example_arguments mit einer geschachtelten gespeicherten Prozedur namens log_and_multiply_numbers. Der geschachtelten gespeicherten Prozedur werden zwei Argumente vom Typ NUMBER übergeben. Der Code enthält die folgende Logik:

Deklariert Variablen a, b``und ``x vom Typ NUMBER.
Enthält eine geschachtelte gespeicherte Prozedur, die die folgenden Aktionen ausführt:
- Fügt die beiden Zahlenwerte, die von der übergeordneten gespeicherten Prozedur übergeben wurden, mithilfe von Bindungsvariablen in die log_nested_values-Tabelle ein.
- Legt den Wert der x-Variablen zum Ergebnis der Multiplikation der beiden Argumentwerte fest.
- Gibt den Wert von x an die übergeordnete gespeicherte Prozedur zurück.
Legt den Wert der a-Variablen auf 5 und der b-Variablen auf 10 fest.
Ruft die geschachtelte gespeicherte Prozedur auf.
Gibt den Wert der x-Variablen zurück, der in der geschachtelten gespeicherten Prozedur festgelegt wurde.

CREATE OR REPLACE PROCEDURE nested_procedure_example_arguments()
RETURNS NUMBER
LANGUAGE SQL
AS
$$
DECLARE
  a NUMBER;
  b NUMBER;
  x NUMBER;
  log_and_multiply_numbers PROCEDURE(num1 NUMBER, num2 NUMBER)
    RETURNS NUMBER
    AS
    BEGIN
      INSERT INTO log_nested_values VALUES(:num1, :num2);
      x := :num1 * :num2;
      RETURN x;
    END;
BEGIN
  a := 5;
  b := 10;
  CALL log_and_multiply_numbers(:a, :b);
  RETURN x;
END;
$$;

Copy

Rufen Sie die gespeicherte Prozedur auf:

CALL nested_procedure_example_arguments();

Copy

+------------------------------------+
| NESTED_PROCEDURE_EXAMPLE_ARGUMENTS |
|------------------------------------|
|                                 50 |
+------------------------------------+

Fragen Sie die log_nested_values-Tabelle ab, um zu bestätigen, dass die geschachtelte gespeicherte Prozedur die übergebenen Werte eingefügt hat:

SELECT * FROM log_nested_values;

Copy

+------+------+
| COL1 | COL2 |
|------+------|
|    5 |   10 |
+------+------+

Eine geschachtelte gespeicherte Prozedur definieren, die eine andere geschachtelte gespeicherte Prozedur aufruft¶

Im folgenden Beispiel wird eine geschachtelte gespeicherte Prozedur definiert, die eine andere geschachtelte gespeicherte Prozedur aufruft. Das Beispiel erstellt eine übergeordnete gespeicherte Prozedur namens nested_procedure_example_call_from_nested mit zwei geschachtelten gespeicherten Prozeduren namens counter_nested_proc und call_counter_nested_proc. Der Code enthält die folgende Logik:

Deklariert eine Variable namens counter vom Typ NUMBER und setzt den Wert dieser Variablen auf 0.
Enthält die geschachtelte gespeicherte Prozedur counter_nested_proc, die 10 zum Wert von counter hinzufügt.
Enthält die geschachtelte gespeicherte Prozedur call_counter_nested_proc, die 15 zum Wert von counter hinzufügt und auch counter_nested_proc aufruft (wodurch weitere 10 zum Wert von counter hinzugefügt werden).
Ruft beide geschachtelten gespeicherten Prozeduren in der übergeordneten gespeicherten Prozedur auf.
Gibt den Wert der counter-Variablen zurück, der 35 ist.

CREATE OR REPLACE PROCEDURE nested_procedure_example_call_from_nested()
RETURNS NUMBER
LANGUAGE SQL
AS
$$
DECLARE
  counter NUMBER := 0;
  counter_nested_proc PROCEDURE()
    RETURNS NUMBER
    AS
    DECLARE
      var1 NUMBER := 10;
    BEGIN
      counter := counter + var1;
    END;
  call_counter_nested_proc PROCEDURE()
    RETURNS NUMBER
    AS
    DECLARE
      var2 NUMBER := 15;
    BEGIN
      counter := counter + var2;
      CALL counter_nested_proc();
    END;
BEGIN
  counter := 0;
  CALL counter_nested_proc();
  CALL call_counter_nested_proc();
  RETURN counter;
END;
$$;

Copy

Rufen Sie die gespeicherte Prozedur auf:

CALL nested_procedure_example_call_from_nested();

Copy

+-------------------------------------------+
| NESTED_PROCEDURE_EXAMPLE_CALL_FROM_NESTED |
|-------------------------------------------|
|                                        35 |
+-------------------------------------------+

Verwenden und Setzen von SQL-Variablen in einer gespeicherten Prozedur¶

Gespeicherte Snowflake Scripting-Prozeduren werden standardmäßig mit Eigentümerrechten ausgeführt. Wenn eine gespeicherte Prozedur mit Eigentümerrechten ausgeführt wird, kann sie nicht auf SQL- (oder Sitzungs)-Variablen zugreifen.

Eine gespeicherte Prozedur mit Aufruferreichten kann jedoch die Sitzungsvariablen des Aufrufers lesen und sie in der Logik der gespeicherten Prozedur verwenden. Zum Beispiel kann eine gespeicherte Prozedur für die Aufruferrecht den Wert in einer SQL-Variablen in einer Abfrage verwenden. Um eine gespeicherte Prozedur zu erstellen, die mit den Aufruferrechten ausgeführt wird, geben Sie den Parameter EXECUTE AS CALLER in der CREATE PROCEDURE-Anweisung an.

Diese Beispiele veranschaulichen den entscheidenden Unterschied zwischen den Aufruferrechten und den Eigentümerrechten von gespeicherten Prozeduren. Sie versuchen, SQL-Variablen auf zwei Arten zu verwenden:

Legen Sie eine SQL Variable fest, bevor Sie die gespeicherte Prozedur aufrufen, und verwenden Sie dann die SQL-Variable innerhalb der gespeicherten Prozedur.
Legen Sie eine SQL-Variable innerhalb der gespeicherten Prozedur fest und verwenden Sie dann die SQL-Variable, nachdem Sie von der gespeicherten Prozedur zurückgekehrt sind.

Sowohl die Verwendung der SQL-Variablen als auch das Festlegen der SQL-Variablen funktionieren in der gespeicherten Prozedur mit Aufruferrechten ordnungsgemäß. Beide schlagen fehl, wenn eine gespeicherte Prozedur mit Eigentümerrechten verwendet wird, selbst wenn der Aufrufer der Eigentümer ist.

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

Verwenden einer SQL-Variablen in einer gespeicherten Prozedur¶

Dieses Beispiel verwendet eine SQL-Variable in einer gespeicherten Prozedur.

Setzen Sie zunächst eine SQL-Variable in einer Sitzung:

SET example_use_variable = 2;

Copy

Erstellen Sie eine einfache gespeicherte Prozedur, die mit den Aufruferrechten läuft und die folgende SQL-Variable verwendet:

CREATE OR REPLACE PROCEDURE use_sql_variable_proc()
RETURNS NUMBER
LANGUAGE SQL
EXECUTE AS CALLER
AS
DECLARE
  sess_var_x_2 NUMBER;
BEGIN
  sess_var_x_2 := 2 * $example_use_variable;
  RETURN sess_var_x_2;
END;

Copy

CREATE OR REPLACE PROCEDURE use_sql_variable_proc()
RETURNS NUMBER
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
DECLARE
  sess_var_x_2 NUMBER;
BEGIN
  sess_var_x_2 := 2 * $example_use_variable;
  RETURN sess_var_x_2;
END;
$$
;

Copy

Rufen Sie die gespeicherte Prozedur auf:

CALL use_sql_variable_proc();

Copy

+-----------------------+
| USE_SQL_VARIABLE_PROC |
|-----------------------|
|                     4 |
+-----------------------+

Setzen Sie die SQL-Variable auf einen anderen Wert:

SET example_use_variable = 9;

Copy

Rufen Sie die Prozedur erneut auf, um zu sehen, ob sich der zurückgegebene Wert geändert hat:

CALL use_sql_variable_proc();

Copy

+-----------------------+
| USE_SQL_VARIABLE_PROC |
|-----------------------|
|                    18 |
+-----------------------+

Setzen einer SQL-Variablen in einer gespeicherten Prozedur¶

Sie können eine SQL-Variable in einer gespeicherten Prozedur setzen, die mit den Aufruferrechten ausgeführt wird. Weitere Informationen, einschließlich Richtlinien für die Verwendung von SQL-Variablen in gespeicherten Prozeduren, finden Sie unter Gespeicherte Prozeduren mit Aufruferrechten.

Bemerkung

Obwohl Sie eine SQL-Variable in einer gespeicherten Prozedur festlegen und nach dem Ende der Prozedur gesetzt lassen können, wird dies von Snowflake nicht empfohlen.

Dieses Beispiel legt eine SQL-Variable in einer gespeicherten Prozedur fest.

Setzen Sie zunächst eine SQL-Variable in einer Sitzung:

SET example_set_variable = 55;

Copy

Bestätigen Sie den Wert der SQL-Variablen:

SHOW VARIABLES LIKE 'example_set_variable';

Copy

+----------------+-------------------------------+-------------------------------+----------------------+-------+-------+---------+
|     session_id | created_on                    | updated_on                    | name                 | value | type  | comment |
|----------------+-------------------------------+-------------------------------+----------------------+-------+-------+---------|
| 10363782631910 | 2024-11-27 08:18:32.007 -0800 | 2024-11-27 08:20:17.255 -0800 | EXAMPLE_SET_VARIABLE | 55    | fixed |         |
+----------------+-------------------------------+-------------------------------+----------------------+-------+-------+---------+

Die folgende gespeicherte Prozedur setzt beispielsweise die Variable SQL example_set_variable auf einen neuen Wert und gibt den neuen Wert zurück:

CREATE OR REPLACE PROCEDURE set_sql_variable_proc()
RETURNS NUMBER
LANGUAGE SQL
EXECUTE AS CALLER
AS
BEGIN
  SET example_set_variable = $example_set_variable - 3;
  RETURN $example_set_variable;
END;

Copy

CREATE OR REPLACE PROCEDURE set_sql_variable_proc()
RETURNS NUMBER
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
BEGIN
  SET example_set_variable = $example_set_variable - 3;
  RETURN $example_set_variable;
END;
$$
;

Copy

Rufen Sie die gespeicherte Prozedur auf:

CALL set_sql_variable_proc();

Copy

+-----------------------+
| SET_SQL_VARIABLE_PROC |
|-----------------------|
|                    52 |
+-----------------------+

Bestätigen Sie den neuen Wert der SQL-Variablen:

SHOW VARIABLES LIKE 'example_set_variable';

Copy

+----------------+-------------------------------+-------------------------------+----------------------+-------+-------+---------+
|     session_id | created_on                    | updated_on                    | name                 | value | type  | comment |
|----------------+-------------------------------+-------------------------------+----------------------+-------+-------+---------|
| 10363782631910 | 2024-11-27 08:18:32.007 -0800 | 2024-11-27 08:24:04.027 -0800 | EXAMPLE_SET_VARIABLE | 52    | fixed |         |
+----------------+-------------------------------+-------------------------------+----------------------+-------+-------+---------+