Verwenden von Variablen

In Snowflake Scripting können Sie Variablen in Ausdrücken und SQL-Anweisungen verwenden.

Unter diesem Thema:

Deklarieren einer Variablen

Bevor Sie eine Variable verwenden können, müssen Sie die Variable deklarieren. Wenn Sie eine Variable deklarieren, müssen Sie den Typ der Variablen angeben. Es gibt zwei Möglichkeiten:

Sie können eine Variable wie folgt deklarieren:

  • Innerhalb des Abschnitts DECLARE des Blocks mit einer der folgenden Möglichkeiten:

    <variable_name> <type>;
    
    <variable_name> DEFAULT <expression> ;
    
    <variable_name> <type> DEFAULT <expression> ;
    
  • Innerhalb des Abschnitts BEGIN … END des Blocks (bevor Sie die Variable verwenden), indem Sie den Befehl LET auf eine der folgenden Arten verwenden:

    LET <variable_name> <type> { DEFAULT | := } <expression> ;
    
    LET <variable_name> { DEFAULT | := } <expression> ;
    

Wobei:

Variablenname

Der Name der Variablen. Der Name muss den Snowflake-Regeln für Bezeichner folgen (siehe unter Objektbezeichner).

Typ

Der Datentyp der Variablen. Folgende Typen sind möglich:

DEFAULT Ausdruck oder . := Ausdruck

Weist der Variablen den Wert von Ausdruck zu.

Wenn sowohl Typ als auch Ausdruck angegeben sind, muss der Ausdruck den passenden Datentyp ergeben. Wenn die Typen nicht übereinstimmen, können Sie den Wert in den angegebenen Typ umwandeln.

Im folgenden Beispiel werden Variablen im Abschnitt DECLARE und im Abschnitt BEGIN ... END des Blocks deklariert:

DECLARE
    profit number(38, 2) DEFAULT 0.0;
BEGIN
    LET cost number(38, 2) := 100.0;
    LET revenue number(38, 2) DEFAULT 110.0;

    profit := revenue - cost;
    RETURN profit;
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):

EXECUTE IMMEDIATE $$
    DECLARE
        profit number(38, 2) DEFAULT 0.0;
    BEGIN
        LET cost number(38, 2) := 100.0;
        LET revenue number(38, 2) DEFAULT 110.0;

        profit := revenue - cost;
        RETURN profit;
    END;
$$
;

In den nächsten Abschnitten wird erläutert, wie der Datentyp und der Geltungsbereich einer Variablen bestimmt werden:

Weitere Informationen zum Zuweisen eines Wertes zu einer Variablen finden Sie unter Zuweisen eines Wertes zu einer deklarierten Variable.

Wie Snowflake Scripting den Datentyp einer Variablen ableitet

Wenn Sie eine Variable deklarieren, ohne den Datentyp explizit anzugeben, leitet Snowflake Scripting den Datentyp aus dem Ausdruck ab, den Sie der Variablen zuweisen.

Wenn Sie sich dafür entscheiden, den Datentyp in der Deklaration wegzulassen, beachten Sie Folgendes:

  • Wenn der Ausdruck in verschiedene Datentypen unterschiedlicher Größe aufgelöst werden kann, wählt Snowflake normalerweise den Typ, der einerseits flexibel ist (z. B. FLOAT statt NUMBER(3, 1)) und anderseits eine hohe Speicherkapazität hat (z. B. VARCHAR statt VARCHAR(4)).

    Wenn Sie z. B. eine Variable auf den Wert 12.3 setzen, kann Snowflake einen von mehreren Datentypen für die Variable auswählen, einschließlich:

    • NUMBER(3, 1)

    • NUMBER(38, 1)

    • FLOAT

    In diesem Beispiel wählt Snowflake den Datentyp FLOAT.

    Wenn Sie für eine Variable einen bestimmten Datentyp benötigen (insbesondere einen numerischen Typ oder einen Zeitstempeltyp), empfiehlt Snowflake, den Datentyp explizit anzugeben, auch wenn Sie einen Anfangswert bereitstellen.

  • Wenn Snowflake nicht in der Lage ist, den beabsichtigten Datentyp zu ermitteln, meldet Snowflake einen SQL-Kompilierungsfehler.

    Im folgenden Codebeispiel wird eine Variable deklariert, ohne dass der Datentyp explizit angegeben wird. Der Code setzt die Variable auf den Wert in einem Cursor.

    ...
    for current_row in cursor_1 do:
        let price := current_row.price_column;
        ...
    

    Wenn der Snowflake Scripting-Block kompiliert wird (z. B. bei Ausführung des Befehls CREATE PROCEDURE), ist der Cursor noch nicht geöffnet, und der Datentyp der Spalte im Cursor ist unbekannt. Infolgedessen meldet Snowflake einen SQL-Kompilierungsfehler:

    092228 (P0000): SQL compilation error:
        error line 7 at position 4 variable 'PRICE' cannot have its type inferred from initializer
    

Erläuterungen zur Sichtbarkeit von Deklarationen

Snowflake Scripting verwendet lexikalische (statische) Sichtbarkeitsbereiche. Wenn im Abschnitt DECLARE eines Blocks eine Variable für einen Wert, ein Resultset, einen Cursor oder eine Ausnahme deklariert wird, erstreckt sich der Sichtbarkeitsbereich des deklarierten Objekts auf diesen Block und alle in diesem Block verschachtelten Blöcke.

Wenn ein Block ein Objekt mit demselben Namen deklariert wie ein in einem äußeren Block deklariertes Objekt, dann ist innerhalb des inneren Blocks (und aller Blöcke innerhalb dieses Blocks) nur das Objekt des inneren Blocks sichtbar. Wenn ein Objektname referenziert wird, sucht Snowflake nach dem Objekt mit diesem Namen, indem es zuerst im aktuellen Block beginnt und dann blockweise nach außen wandert, bis ein Objekt mit dem übereinstimmenden Namen gefunden wird.

Wenn zum Beispiel eine Ausnahme innerhalb einer gespeicherten Prozedur deklariert wird, ist der Sichtbarkeitsbereich der Ausnahme auf diese gespeicherte Prozedur beschränkt. Gespeicherte Prozeduren, die von dieser gespeicherten Prozedur aufgerufen werden, können diese Ausnahme nicht auslösen (oder verarbeiten). Gespeicherte Prozeduren, die diese Prozedur aufrufen, können diese Ausnahme nicht verarbeiten (oder auslösen).

Zuweisen eines Wertes zu einer deklarierten Variable

Um einer bereits deklarierten Variablen einen Wert zuzuweisen, verwenden Sie den Operator :=:

<variable_name> := <expression> ;

Wobei:

Variablenname

Der Name der Variablen. Der Name muss den Snowflake-Regeln für Bezeichner folgen (siehe unter Objektbezeichner).

Ausdruck

Der Ausdruck wird ausgewertet, und der Ergebniswert wird der Variablen zugewiesen.

Der Ausdruck muss einen Datentyp ergeben, der mit dem Typ der Variablen übereinstimmt. Wenn der Ausdruck nicht mit dem Typ übereinstimmt, können Sie den Wert in den Typ der Variablen umwandeln.

In dem Ausdruck können Sie Funktionen verwenden, einschließlich integrierter SQL-Funktionen und UDFs (benutzerdefinierte Funktionen).

Verwenden einer Variablen

Sie können eine Variable in einem Ausdruck verwenden. Im folgenden Beispiel werden im Code die Variablen revenue und cost in einem Ausdruck verwendet:

DECLARE
    profit NUMBER(38, 2);
    revenue NUMBER(38, 2);
    cost NUMBER(38, 2);
BEGIN
    ...
    profit := revenue - cost;
    ...

Verwenden einer Variablen in einer SQL-Anweisung (Bindung)

Sie können eine Variable in einer SQL-Anweisung verwenden. (Dies wird manchmal auch als Bindung einer Variablen bezeichnet.) Stellen Sie dem Variablennamen einen Doppelpunkt voran. Beispiel:

INSERT INTO my_table (x) VALUES (:my_variable)

Wenn Sie die Variable als Namen eines Objekts verwenden (z. B. den Namen einer Tabelle in der FROM-Klausel einer SELECT-Anweisung), können Sie mit dem Schlüsselwort IDENTIFIER anzeigen, dass die Variable einen Objektbezeichner repräsentiert. Beispiel:

SELECT COUNT(*) FROM IDENTIFIER(:table_name)

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), der Variablen keinen Doppelpunkt voranstellen dürfen. Beispiel:

LET select_statement := 'SELECT * FROM invoices WHERE id = ' || id;
res := (EXECUTE IMMEDIATE :select_statement);

Setzen von Variablen auf die Ergebnisse einer SELECT-Anweisung

In einem Snowflake Scripting-Block können Sie mit der INTO-Klausel Variablen auf die Werte von Ausdrücken setzen, die in einer SELECT-Klausel angegeben sind:

SELECT <expression1>, <expression2>, ... INTO :<variable1>, :<variable2>, ... FROM ... WHERE ...;

Wenn Sie diese Syntax verwenden:

  • Variable1 wird auf den Wert von Ausdruck1 gesetzt.

  • Variable2 wird auf den Wert von Ausdruck2 gesetzt.

Beachten Sie, dass die SELECT-Anweisung eine einzelne Zeile zurückgeben muss.

Im folgenden Beispiel wird eine SELECT-Anweisung verwendet, die eine einzelne Zeile zurückgibt. Das Beispiel stützt sich auf Daten aus dieser Tabelle:

CREATE OR REPLACE TABLE some_data (id INTEGER, name VARCHAR);
INSERT INTO some_data (id, name) VALUES
  (1, 'a'),
  (2, 'b');

Im Beispiel werden die Snowflake Scripting-Variablen id und name auf die Werte gesetzt, die für die Spalten mit diesen Namen zurückgegeben werden.

DECLARE
  id INTEGER;
  name VARCHAR;
BEGIN
  SELECT id, name INTO :id, :name FROM some_data WHERE id = 1;
  RETURN :id || ' ' || :name;
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):

EXECUTE IMMEDIATE $$
DECLARE
  id INTEGER;
  name VARCHAR;
BEGIN
  SELECT id, name INTO :id, :name FROM some_data WHERE id = 1;
  RETURN :id || ' ' || :name;
END;
$$
;

Das Beispiel gibt die ID und den Namen der Zeile aus, die von der SELECT-Anweisung zurückgegeben wurde.

+-----------------+
| anonymous block |
|-----------------|
| 1 a             |
+-----------------+

Beispiele für die Verwendung von Variablen

Im folgenden Beispiel wird gezeigt, wie eine Variable deklariert wird, wie einer Variablen ein Wert oder Ausdruck zugewiesen wird und wie ein Wert in den Datentyp einer Variablen umwandelt wird:

DECLARE
  w INTEGER;
  x INTEGER DEFAULT 0;
  dt DATE;
  result_string VARCHAR;
BEGIN
  w := 1;                     -- Assign a value.
  w := 24 * 7;                -- Assign the result of an expression.
  dt := '2020-09-30'::DATE;   -- Explicit cast.
  dt := '2020-09-30';         -- Implicit cast.
  result_string := w::VARCHAR || ', ' || dt::VARCHAR;
  RETURN result_string;
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):

EXECUTE IMMEDIATE $$
DECLARE
    w INTEGER;
    x INTEGER DEFAULT 0;
    dt DATE;
    result_string VARCHAR;
BEGIN
    w := 1;                     -- Assign a value.
    w := 24 * 7;                -- Assign the result of an expression.
    dt := '2020-09-30'::DATE;   -- Explicit cast.
    dt := '2020-09-30';         -- Implicit cast.
    result_string := w::VARCHAR || ', ' || dt::VARCHAR;
    RETURN result_string;
END;
$$
;

Im folgenden Beispiel wird in dem Ausdruck eine integriert SQL-Funktion verwendet:

my_variable := SQRT(variable_x);

Mit der folgenden Deklaration werden die Datentypen der Variablen profit, cost und revenue implizit festgelegt, indem für jede Variable ein Anfangswert des vorgesehenen Datentyps angegeben wird.

Das Beispiel zeigt auch die Verwendung der LET-Anweisung zum Deklarieren der Variablen cost und revenue außerhalb des DECLARE-Teils des Blocks:

DECLARE
  profit number(38, 2) DEFAULT 0.0;
BEGIN
  LET cost number(38, 2) := 100.0;
  LET revenue number(38, 2) DEFAULT 110.0;

  profit := revenue - cost;
  RETURN profit;
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):

EXECUTE IMMEDIATE $$
DECLARE
    profit DEFAULT 0.0;
BEGIN
    LET cost := 100.0;
    LET revenue DEFAULT 110.0;
    profit := revenue - cost;
    RETURN profit;
END;
$$
;

Das folgende Beispiel veranschaulicht den Sichtbarkeitsbereich einer Variablen. Dieses Beispiel enthält zwei Variablen und einen Parameter, die alle denselben Namen, aber unterschiedliche Sichtbarkeitsbereiche haben.

Das Beispiel enthält drei Blöcke: den äußersten, den mittleren und den innersten Block.

  • Innerhalb des innersten Blocks löst sich PV_NAME in die Variable auf, die in diesem innersten Block deklariert und gesetzt wurde (die auf innermost block variable gesetzt ist).

  • Innerhalb des mittleren Blocks (und außerhalb des innersten Blocks) löst sich PV_NAME in die im mittleren Block deklarierte und gesetzte Variable auf (die auf middle block variable gesetzt ist).

  • Innerhalb des äußersten Blocks (und außerhalb der verschachtelten Blöcke) wird PV_NAME in den Parameter aufgelöst, der an die gespeicherte Prozedur übergeben wird (der durch die CALL-Anweisung auf parameter gesetzt wird).

So hat beispielsweise die Zuweisung der Zeichenfolge innermost block variable an PV_NAME im innersten Block keine Auswirkungen auf den Wert der Variablen im mittleren Block. Die Variable im innersten Block ist eine andere als die Variable im mittleren Block, auch wenn beide Variablen denselben Namen haben.

CREATE PROCEDURE duplicate_name(pv_name VARCHAR)
RETURNS VARCHAR
LANGUAGE SQL
AS
BEGIN
  DECLARE
    PV_NAME VARCHAR;
  BEGIN
    PV_NAME := 'middle block variable';
    DECLARE
      PV_NAME VARCHAR;
    BEGIN
      PV_NAME := 'innermost block variable';
      INSERT INTO names (v) VALUES (:PV_NAME);
    END;
    -- Because the innermost and middle blocks have separate variables
    -- named "pv_name", the INSERT below inserts the value
    -- 'middle block variable'.
    INSERT INTO names (v) VALUES (:PV_NAME);
  END;
  -- This inserts the value of the input parameter.
  INSERT INTO names (v) VALUES (:PV_NAME);
  RETURN 'Completed.';
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 PROCEDURE duplicate_name(pv_name VARCHAR)
RETURNS VARCHAR
LANGUAGE SQL
AS
$$
    BEGIN
        DECLARE
            PV_NAME VARCHAR;
        BEGIN
            PV_NAME := 'middle block variable';
            DECLARE
                PV_NAME VARCHAR;
            BEGIN
                PV_NAME := 'innermost block variable';
                INSERT INTO names (v) VALUES (:PV_NAME);
            END;
            -- Because the innermost and middle blocks have separate variables
            -- named "pv_name", the INSERT below inserts the value
            -- 'middle block variable'.
            INSERT INTO names (v) VALUES (:PV_NAME);
        END;
        -- This inserts the value of the input parameter.
        INSERT INTO names (v) VALUES (:PV_NAME);
        RETURN 'Completed.';
    END;
$$
;

Rufen Sie die gespeicherte Prozedur auf:

CALL duplicate_name('parameter');

Prüfen Sie die Werte in der Tabelle: Dies zeigt Folgendes:

  • Im innersten verschachtelten Block (der zwei Ebenen verschachtelt war) wurde die Variable PV_NAME des inneren Blocks verwendet.

  • Im mittleren Block (der eine Ebene verschachtelt war) wurde die Variable PV_NAME dieses mittleren Blocks verwendet.

  • Im äußersten Block wurde der Parameter verwendet.

    SELECT *
        FROM names
        ORDER BY v;
    +--------------------------+
    | V                        |
    |--------------------------|
    | innermost block variable |
    | middle block variable    |
    | parameter                |
    +--------------------------+
    

Ein Beispiel für die Bindung einer Variablen beim Öffnen eines Cursors finden Sie in den Beispielen für das Öffnen eines Cursors.

Zurück zum Anfang