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

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

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

In diesem 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

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

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 setzt eine SQL-Variable in einer gespeicherten Prozedur.

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 |         |
+----------------+-------------------------------+-------------------------------+----------------------+-------+-------+---------+