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

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

Beachten Sie Folgendes:

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;

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

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

CALL output_message('Hello World');

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

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;

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

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

CALL return_greater(2, 3);

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;

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

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

CALL find_invoice_by_id('2');

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;

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

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

CALL get_row_count('invoices');

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;

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

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

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

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;

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

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

CALL get_top_sales();

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

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;

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

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;

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

Rufen Sie schließlich die zweite gespeicherte Prozedur auf:

CALL insert_two_values(4, 5);

Verwenden des vom Aufruf einer gespeicherten Prozedur zurückgegebenen Wertes

Wenn Sie den Rückgabewert der gespeicherten Prozedur, die Sie aufrufen, weiterverwenden müssen, legen Sie für RESULTSET als Zeichenfolge (String) fest, 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. Beachten Sie, dass beim Zugriff auf das Ergebnis des Aufrufs der Name der Spalte, die das Ergebnis enthält, der Name der von Ihnen aufgerufenen gespeicherten Prozedur ist.

Wenn Sie zum Beispiel die gespeicherte Prozedur get_row_count aufgerufen haben, können Sie den Wert aus row_variable.get_row_count abrufen.

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
  res1 RESULTSET;
BEGIN
  res1 := (CALL get_row_count(:table_name));
  LET c1 CURSOR FOR res1;
  FOR row_variable in c1 DO
    IF (row_variable.get_row_count > maximum_count) THEN
      RETURN TRUE;
    ELSE
      RETURN FALSE;
    END IF;
 END FOR;
END;

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 count_greater_than(table_name VARCHAR, maximum_count INTEGER)
RETURNS BOOLEAN NOT NULL
LANGUAGE SQL
AS
$$
DECLARE
  res1 RESULTSET;
BEGIN
  res1 := (CALL get_row_count(:table_name));
  LET c1 CURSOR FOR res1;
  FOR row_variable in c1 DO
    IF (row_variable.get_row_count > maximum_count) THEN
      RETURN TRUE;
    ELSE
      RETURN FALSE;
    END IF;
 END FOR;
END;
$$
;

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

CALL count_greater_than('invoices', 3);
Zurück zum Anfang