Schreiben von gespeicherten Prozeduren in Snowflake Scripting¶

Unter diesem Thema wird erklärt, wie Sie eine gespeicherte Prozedur in SQL unter Verwendung von Snowflake Scripting schreiben.

Unter diesem Thema:

Einführung
Verwenden von Argumenten, die an eine gespeicherte Prozedur übergeben werden
Zurückgeben von tabellarischen Daten
Aufrufen einer gespeicherten Prozedur aus einer anderen gespeicherten Prozedur
- Aufrufen einer gespeicherten Prozedur ohne Verwendung des zurückgegebenen Wertes
- Verwenden des vom Aufruf einer gespeicherten Prozedur zurückgegebenen Wertes

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 und über die klassische Weboberfläche.

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

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 oder die klassische Weboberfläche verwenden, benutzen Sie stattdessen dieses Beispiel (siehe Verwenden von Snowflake Scripting in SnowSQL und über die klassische Weboberfläche):

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

Das folgende Beispiel zeigt das Erstellen und Aufrufen 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

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;

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