変数の操作

Snowflakeスクリプトでは、式、Snowflakeスクリプトステートメント、および SQL ステートメントで変数を使用できます。

このトピックの内容:

変数の宣言

変数を使用する前に、変数を宣言する必要があります。変数を宣言するときは、次のいずれかで変数の型を指定する必要があります。

変数を宣言するには、

  • ブロックの DECLARE セクション内で、次のいずれかを使用します。

    <variable_name> <type>;
    
    <variable_name> DEFAULT <expression> ;
    
    <variable_name> <type> DEFAULT <expression> ;
    
  • ブロックの BEGIN ... END セクション内(変数を使用する前)で、次のいずれかの方法により LET コマンドを使用します。

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

条件:

variable_name

変数の名前。名前は、 オブジェクト識別子 の名前付け規則に従う必要があります。

type

変数のデータ型。使用できるデータ型は、

DEFAULT expression または . := expression

expression の値を変数に割り当てます。

typeexpression の両方が指定されている場合、式は一致するデータ型に評価される必要があります。型が一致しない場合は、指定された type に値を キャスト できます。

次の例では、ブロックの DECLARE セクションと BEGIN ... END セクションで変数を宣言しています。

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;

注: SnowSQL または 従来のウェブインターフェイス を使用している場合は、代わりに次の例を使用します(SnowSQL でのSnowflakeスクリプトと従来のウェブインターフェイスの使用 を参照)。

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

次のセクションでは、変数のデータ型とスコープがどのように決定されるかについて説明します。

変数への値の割り当てについては、 宣言された変数への値の割り当て をご参照ください。

Snowflakeスクリプトが変数のデータ型を推測する方法

前述のように、データ型を明示的に指定せずに変数を宣言すると、Snowflakeスクリプトは、変数に割り当てた式からデータ型を推測します。

宣言からデータ型を省略する場合は、次の点に注意してください。

  • 式がさまざまなサイズのさまざまなデータ型に解決できる場合、Snowflakeは通常、柔軟性があり(例: NUMBER(3, 1) ではなく FLOAT)、ストレージ容量が大きい(例: VARCHAR(4) ではなく VARCHAR)型を選択します。

    たとえば、変数を値 12.3 に設定すると、Snowflakeは、次のような変数のデータ型の1つを選択できます。

    • NUMBER(3, 1)

    • NUMBER(38, 1)

    • FLOAT

    この例では、Snowflakeは FLOAT を選択します。

    変数に特定のデータ型(特に数値型またはタイムスタンプ型)が必要な場合は、初期値を指定する場合でも、データ型を明示的に指定することをSnowflakeはお勧めします。

  • Snowflakeが目的のデータ型を推測できない場合、Snowflakeは SQL コンパイルエラーを報告します。

    たとえば、次のコードは、データ型を明示的に指定せずに変数を宣言します。このコードは、変数をカーソル内の値に設定します。

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

    Snowflakeスクリプトのブロックがコンパイルされているとき(例: CREATE PROCEDURE コマンドが実行されるとき)、カーソルは開かれておらず、カーソル内の列のデータ型は不明です。その結果、Snowflakeは SQL コンパイルエラーを報告します。

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

宣言の範囲を理解する

Snowflakeスクリプトは、 レキシカルスコープ を使用します。値、結果セット、カーソル、または例外の変数がブロックの DECLARE セクションで宣言されている場合、宣言されたオブジェクトのスコープ(または可視性)は、そのブロックとそのブロックにネストされたブロックです。

ブロックが外側のブロックで宣言されたオブジェクトと同じ名前のオブジェクトを宣言する場合、内側のブロック(およびそのブロック内のすべてのブロック)内では、内側のブロックのオブジェクトのみがスコープ内にあります。オブジェクト名が参照されると、Snowflakeは、最初に現在のブロックから開始し、次に一致する名前のオブジェクトが見つかるまで、一度に1ブロックずつ外側に向かって、その名前のオブジェクトを探します。

たとえば、ストアドプロシージャ内で例外が宣言されている場合、例外のスコープはそのストアドプロシージャに限定されます。そのストアドプロシージャによって呼び出されたストアドプロシージャは、その例外を発生(または処理)できません。そのプロシージャを呼び出すストアドプロシージャは、その例外を処理(または発生)できません。

宣言された変数への値の割り当て

すでに宣言されている変数に値を割り当てるには、 := 演算子を使用します。

<variable_name> := <expression> ;

条件:

variable_name

変数の名前。名前は、 オブジェクト識別子 の名前付け規則に従う必要があります。

expression

式が評価され、結果の値が変数に割り当てられます。

式は、変数の型と一致するデータ型に評価される必要があります。式が型と一致しない場合は、変数の型に値を キャスト することができます。

式では、組み込みの SQL 関数や UDFs (ユーザー定義関数)などの関数を使用できます。

変数の使用

式や、Snowflakeスクリプト言語要素(RETURN など)で変数を使用できます。たとえば、次のコードでは、式で変数 revenuecost を、また RETURN ステートメントで変数 profit を使用しています。

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

SQL ステートメントでの変数の使用(バインド)

SQL ステートメントで変数を使用できます。(これは、変数のバインドと呼ばれることもあります。)変数名の前にコロンを付けます。例:

INSERT INTO my_table (x) VALUES (:my_variable)

オブジェクトの名前として変数を使用している場合(例: SELECT ステートメントの FROM 句にあるテーブルの名前)は、 IDENTIFIER キーワードを使用して、変数がオブジェクト識別子を表すことを示します。例:

SELECT COUNT(*) FROM IDENTIFIER(:table_name)

式や、 Snowflakeスクリプト言語要素RETURN など)で変数を使用している場合は、変数の前にコロンを付ける必要がないことに注意してください。

たとえば、次の場合はコロンプレフィックスは必要ありません。

  • RETURN で変数を使用している場合。この例では、変数 profit がSnowflakeスクリプト言語要素で使用されており、コロンプレフィックスは必要ありません。

    RETURN profit;
    
  • 実行する SQL ステートメントを含む文字列を作成している場合。この例では、変数 id_variable が式で使用されており、コロンプレフィックスは必要ありません。

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

SELECT ステートメントの結果に変数を設定する

Snowflake Scriptingブロックでは、 INTO 句を使用して、変数を SELECT 句で指定された式の値に設定できます。

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

この構文を使用する場合、

  • variable1 は、 expression1 の値に設定されます。

  • variable2 は、 expression2 の値に設定されます。

SELECT ステートメントは、単一の行を返す必要があることに注意してください。

次の例には、単一の行を返す SELECT ステートメントが含まれています。この例は、次のテーブルのデータに依存しています。

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

この例では、Snowflakeスクリプト変数 id および name をこれらの名前の列に返される値に設定します。

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

注: SnowSQL または 従来のウェブインターフェイス を使用している場合は、代わりに次の例を使用します(SnowSQL でのSnowflakeスクリプトと従来のウェブインターフェイスの使用 を参照)。

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

この例では、 SELECT ステートメントによって返された行から ID と名前を出力します。

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

変数の使用例

次の例は、変数を宣言し、変数に値または式を割り当て、変数のデータ型に値をキャストする方法を示しています。

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;

注: SnowSQL または 従来のウェブインターフェイス を使用している場合は、代わりに次の例を使用します(SnowSQL でのSnowflakeスクリプトと従来のウェブインターフェイスの使用 を参照)。

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

次の例では、式に組み込みの SQL 関数を使用しています。

my_variable := SQRT(variable_x);

次の宣言は、各変数に対する使用目的のデータ型の初期値を指定することによって、変数 profitcost、および revenue のデータ型を暗黙的に指定します。

この例では、 LET ステートメントを使用して、ブロックの DECLARE 部分の外側で cost 変数と revenue 変数を宣言する方法も示しています。

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;

注: SnowSQL または 従来のウェブインターフェイス を使用している場合は、代わりに次の例を使用します(SnowSQL でのSnowflakeスクリプトと従来のウェブインターフェイスの使用 を参照)。

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

次の例は、変数のスコープを示しています。この例には、2つの変数と、すべて同じ名前でスコープが異なるパラメーターが含まれています。

この例には、最も外側、中央、最も内側の3つのブロックが含まれています。

  • 最も内側のブロック内では、 PV_NAME は、その最も内側のブロック(innermost block variable に設定されている)で宣言および設定された変数に解決されます。

  • 中央のブロック内(および最も内側のブロックの外側)では、 PV_NAME は、中央のブロック(middle block variable に設定されている)で宣言および設定された変数に解決されます。

  • 最も外側のブロック内(およびネストされたブロックの外側)では、 PV_NAME は、ストアドプロシージャに渡されたパラメーター(CALL ステートメントによって parameter に設定されている)に解決されます。

たとえば、最も内側のブロックにある PV_NAME への文字列 innermost block variable の割り当ては、中央のブロックにある変数の値には影響しません。両方の変数の名前が同じであっても、最も内側のブロックの変数は中央のブロックの変数とは異なります。

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;

注: SnowSQL または 従来のウェブインターフェイス を使用している場合は、代わりに次の例を使用します(SnowSQL でのSnowflakeスクリプトと従来のウェブインターフェイスの使用 を参照)。

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

ストアドプロシージャを呼び出します。

CALL duplicate_name('parameter');

テーブルの値を確認します。これは次を示しています。

  • 最も内側のネストされたブロック(2つのレイヤーがネストされた)では、内側のブロックの変数 PV_NAME が使用されました。

  • 中央のブロック(1つのレイヤーにネストされている)では、その中央のブロックの変数 PV_NAME が使用されました。

  • 最も外側のブロックでは、パラメーターが使用されました。

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

カーソルを開くときに変数をバインドする例については、 カーソルを開く例 をご参照ください。

最上部に戻る