変数の操作¶

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

変数の宣言¶

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

データ型を明示的に指定。
変数の初期値の式を指定。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、 execute_stream または execute_string メソッドを Python Connector コードで使用している場合は、代わりにこの例を使用してください（SnowSQL、 Classic Console、Python Connectorで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

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

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

Snowflakeスクリプトが変数のデータ型を推測する方法
宣言の範囲を理解する

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

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

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

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

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

たとえば、変数を値 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;
  ...
```
Copy
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
```
Copy

宣言の範囲を理解する¶

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

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

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

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

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

<variable_name> := <expression> ;

Copy

条件:

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

例外ハンドラー（ブロックの EXCEPTION セクション）で変数を使うには、 DECLARE セクションで変数を宣言するか、ストアド・プロシージャの引数として渡す必要があります。 BEGIN ... END セクションでは宣言できません。詳細については、例外ハンドラーに変数を渡すをご参照ください。

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

さらに、 TO_QUERY 関数は、 SELECT ステートメントの FROM 句で直接 SQL 文字列を受け入れるための簡単な構文を提供します。TO_QUERY 関数とダイナミック SQL の比較については実行時に SQL を構築するをご参照ください。

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

Copy

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 と name を出力します。

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

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

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

次の例では、式に組み込みの 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

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

Copy

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

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

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

最も内側のブロック内では、 PV_NAME は、その最も内側のブロック（innermost block variable に設定されている）で宣言および設定された変数に解決されます。
中央のブロック内（および最も内側のブロックの外側）では、 PV_NAME は、中央のブロック（middle block variable に設定されている）で宣言および設定された変数に解決されます。
最も外側のブロック内（およびネストされたブロックの外側）では、 PV_NAME は、ストアドプロシージャに渡されたパラメーター（CALL ステートメントによって parameter に設定されている）に解決されます。

例は、このテーブルに依存しています。

CREATE OR REPLACE TABLE names (v VARCHAR);

Copy

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

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;

Copy

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

Copy

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

CALL duplicate_name('parameter');

Copy

テーブルの値を確認します。

SELECT *
    FROM names
    ORDER BY v;

Copy

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

出力は次のようになります。

最も内側のネストされたブロック（2つのレイヤーがネストされた）では、内側のブロックの変数 PV_NAME が使用されました。
中央のブロック（1つのレイヤーにネストされている）では、その中央のブロックの変数 PV_NAME が使用されました。
最も外側のブロックでは、パラメーターが使用されました。

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