Verwenden von Variablen¶

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. Dazu gibt es folgende Möglichkeiten:

• Explizite Angabe des Datentyps

• Angabe eines Ausdrucks für den Anfangswert der Variablen. Snowflake Scripting verwendet den Ausdruck, um den Datentyp der Variablen zu bestimmen. Siehe Wie Snowflake Scripting den Datentyp einer Variablen ableitet.

Zum Deklarieren einer Variable haben Sie folgende Möglichkeiten:

• Innerhalb des Abschnitts DECLARE des Blocks mit einer der folgenden Optionen:

  <variable_name> <type> ;
  
  <variable_name> DEFAULT <expression> ;
  
  <variable_name> <type> DEFAULT <expression> ;
  

  Copy

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

  Copy

Wobei:

variable_name

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

type

Der Datentyp der Variablen. Der Datentyp kann einer der folgenden sein:

• Ein SQL-Datentyp

• CURSOR

• RESULTSET

• EXCEPTION

DEFAULT expression oder . := expression

Weist der Variablen den Wert von expression (Ausdruck) zu.

Wenn sowohl type als auch expression angegeben sind, muss der Ausdruck den passenden Datentyp ergeben. Wenn die Typen nicht übereinstimmen, können Sie den Wert in den angegebenen Typ type 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, 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):

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

+-----------------+
| anonymous block |
|-----------------|
|           10.00 |
+-----------------+

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

• Wie Snowflake Scripting den Datentyp einer Variablen ableitet

• Erläuterungen zur Sichtbarkeit von Deklarationen

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

Zuweisen eines Wertes zu einer deklarierten Variablen¶

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

<variable_name> := <expression> ;

Wobei:

variable_name

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

expression

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 Variablen in Ausdrücken und mit Elementen der Snowflake Scripting-Sprache (wie RETURN) verwenden. Im folgenden Codebeispiel werden die Variablen revenue und cost in einem Ausdruck und die Variable profit in einer RETURN-Anweisung verwendet:

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

Um eine Variable in einem Handler für Ausnahmen (dem Abschnitt EXCEPTION eines Blocks) zu verwenden, muss die Variable im Abschnitt DECLARE deklariert oder als Argument an eine gespeicherte Prozedur übergeben werden. Sie kann nicht im Abschnitt BEGIN … END deklariert werden. Weitere Informationen dazu finden Sie unter Übergabe von Variablen an einen Ausnahme-Handler.

Verwenden einer Variablen in einer SQL-Anweisung (Bindung)¶

Sie können eine Variable in einer SQL-Anweisung verwenden, was manchmal auch als Binden einer Variablen bezeichnet wird. Stellen Sie dazu 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)

Wenn Sie eine Variable in einem Ausdruck oder mit einem Snowflake Scripting-Sprachelement (z. B. RETURN) verwenden, müssen Sie der Variable keinen Doppelpunkt voranstellen.

In folgenden Fällen benötigen Sie beispielsweise keinen Doppelpunkt:

• Sie verwenden die Variable mit RETURN. In diesem Beispiel wird die Variable profit mit einem Snowflake Scripting-Sprachelement verwendet und benötigt keinen Doppelpunkt als Präfix.

  RETURN profit;
  

  Copy

• Sie erstellen eine Zeichenfolge, die eine SQL-Anweisung enthält, die ausgeführt werden soll. In diesem Beispiel wird die Variable id_variable in einem Ausdruck verwendet und benötigt keinen Doppelpunkt als Präfix.

  LET select_statement := 'SELECT * FROM invoices WHERE id = ' || id_variable;
  

  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.

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 expression1 gesetzt.

• variable2 wird auf den Wert von expression2 gesetzt.

Die SELECT-Anweisung muss eine einzelne Zeile zurückgeben.

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, 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):

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 id und name der Zeile aus, die von der SELECT-Anweisung zurückgegeben wurde.

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

Setzen einer Variablen auf den Rückgabewert einer gespeicherten Prozedur¶

Siehe Verwenden des vom Aufruf einer gespeicherten Prozedur zurückgegebenen Wertes.

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, 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):

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

+-----------------+
| anonymous block |
|-----------------|
| 168, 2020-09-30 |
+-----------------+

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, 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):

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

+-----------------+
| anonymous block |
|-----------------|
|              10 |
+-----------------+

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

Das Beispiel stützt sich auf diese Tabelle:

CREATE OR REPLACE TABLE names (v VARCHAR);

In diesem Beispiel hat 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 OR REPLACE 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, 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 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:

SELECT *
    FROM names
    ORDER BY v;

+--------------------------+
| V                        |
|--------------------------|
| innermost block variable |
| middle block variable    |
| parameter                |
+--------------------------+

Die Ausgabe 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.

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