変数の操作¶

変数の宣言¶

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

• データ型を明示的に指定。

• 変数の初期値の式を指定。Snowflakeスクリプトは、式を使用して変数のデータ型を判別します。 Snowflakeスクリプトが変数のデータ型を推測する方法 をご参照ください。

変数を宣言するには、

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

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

  Copy

• ブロックの BEGIN ... END セクション内（変数を使用する前）で、次のいずれかの方法により LET コマンドを使用します。

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

  Copy

条件:

variable_name

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

type

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

• SQL データ型。

• CURSOR

• RESULTSET

• EXCEPTION

DEFAULT expression または . := expression

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

type と expression の両方が指定されている場合、式は一致するデータ型に評価される必要があります。型が一致しない場合は、指定された 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;

Copy

注: SnowSQL または Classic Console を使用している場合は、代わりに次の例を使用します（SnowSQL および Classic Console での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;
$$
;

Copy

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

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

• 宣言の範囲を理解する

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

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

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

<variable_name> := <expression> ;

条件:

variable_name

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

expression

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

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

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

変数の使用¶

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

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

Copy

SQL ステートメントでの変数の使用（バインド）¶

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

INSERT INTO my_table (x) VALUES (:my_variable)

Copy

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

SELECT COUNT(*) FROM IDENTIFIER(:table_name)

Copy

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

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

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

  RETURN profit;
  

  Copy

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

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

  Copy

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

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

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

Copy

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

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

Copy

この例では、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;

Copy

注: SnowSQL または Classic Console を使用している場合は、代わりに次の例を使用します（SnowSQL および Classic Console での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;
$$
;

Copy

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

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

Copy

ストアドプロシージャの戻り値を変数に設定する方法¶

ストアドプロシージャ呼び出しからの戻り値の使用 をご参照ください。

変数の使用例¶

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

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;

Copy

注: SnowSQL または Classic Console を使用している場合は、代わりに次の例を使用します（SnowSQL および Classic Console での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;
$$
;

Copy

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

my_variable := SQRT(variable_x);

Copy

次の宣言は、各変数に対する使用目的のデータ型の初期値を指定することによって、変数 profit、 cost、および 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;

Copy

注: SnowSQL または Classic Console を使用している場合は、代わりに次の例を使用します（SnowSQL および Classic Console でのSnowflakeスクリプトの使用 を参照）。

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

Copy

次の例は、変数のスコープを示しています。この例には、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;

Copy

注: SnowSQL または Classic Console を使用している場合は、代わりに次の例を使用します（SnowSQL および Classic Console での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;
$$
;

Copy

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

CALL duplicate_name('parameter');

Copy

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

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

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

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

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

  Copy

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