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 SnowSQL, in der Classic Console und im Python-Konnektor.

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.

Beachten Sie Folgendes:

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 SnowSQL, die Classic Console oder die execute_stream- oder execute_string-Methode im Python-Konnektor-Code verwenden, benutzen Sie stattdessen das folgende Beispiel (siehe Verwenden von Snowflake Scripting in SnowSQL, in der Classic Console und im Python-Konnektor):

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. Weitere Details dazu finden Sie im nächsten Abschnitt.

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

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 (siehe Verwenden einer Variablen in einer SQL-Anweisung (Bindung)).

Die folgende gespeicherte Prozedur verwendet beispielsweise 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.

Verwenden eines Arguments als Objektbezeichner¶

Wenn Sie ein Argument verwenden müssen, um auf ein Objekt zu verweisen (z. B. einen Tabellennamen in der FROM-Klausel einer SELECT-Anweisung), können Sie mit dem Schlüsselwort IDENTIFIER anzeigen, dass das Argument einen Objektbezeichner repräsentiert. 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

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

Zurückgeben von tabellarischen Daten¶

Wenn Sie tabellarische Daten (z. B. Daten aus einem RESULTSET) von Ihrer gespeicherten Prozedur zurückgeben müssen, geben Sie in Ihrer CREATE PROCEDURE-Anweisung RETURNS TABLE (…) 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 nicht 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

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