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:
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;
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');
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');
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¶
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 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)
...
WITH test_return_geography_table_1() AS PROCEDURE
RETURNS TABLE(g GEOGRAPHY)
...
CALL test_return_geography_table_1();
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
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()
...
WITH test_return_geography_table_1() AS PROCEDURE
RETURNS TABLE()
...
CALL test_return_geography_table_1();
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;
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 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);
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 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;
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
count1 NUMBER;
BEGIN
CALL get_row_count(:table_name) INTO :count1;
IF (:count1 > maximum_count) THEN
RETURN TRUE;
ELSE
RETURN FALSE;
END IF;
END;
$$
;
Im Folgenden finden Sie ein Beispiel für das Aufrufen der gespeicherten Prozedur:
CALL count_greater_than('invoices', 3);
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;
...