手続き型言語

Google 標準 SQL 手続き型言語を使用すると、1 つのクエリで複数のステートメントを複数ステートメント クエリとして実行できます。次のことをするために、複数のステートメント クエリを使用できます。

  • 共有状態の複数のステートメントを順番に実行します。
  • テーブルの作成や削除などの管理タスクを自動化します。
  • IFWHILE などのプログラミング構造を使用して、複雑なロジックを実装します。

このリファレンスには、Google 標準 SQL 手続き型言語の一部であるステートメントが含まれています。この手続き型言語を使用して複数ステートメント クエリを作成する方法の詳細については、複数ステートメント クエリを使用するをご覧ください。複数ステートメント クエリをストアド プロシージャに変換する方法を学習するには、ストアド プロシージャを操作するをご覧ください。

DECLARE

DECLARE variable_name[, ...] [variable_type] [DEFAULT expression];

variable_name は、有効な識別子である必要があります。また、variable_type は Google Standard SQL の任意のタイプです。

Description

指定された型の変数を宣言します。たとえば DEFAULT 句が指定された場合、変数は式の値で初期化されます。DEFAULT 句が指定されない場合、変数は値 NULL で初期化されます。

[variable_type] を省略する場合、DEFAULT 句を指定する必要があります。変数の型は、DEFAULT 句の式の型によって推定されます。

変数宣言は、他の手続き型ステートメントの前、または BEGIN ブロックの先頭に配置する必要があります。変数名は大文字と小文字を区別しません。

複数の変数名を 1 つの DECLARE ステートメント内に記述できます。ただし、variable_typeexpression は 1 つだけです。

現在のブロックまたは格納中のブロックですでに宣言されている変数と同じ名前の変数を宣言するとエラーになります。

たとえば DEFAULT 句が存在する場合、式の値は指定された型に強制型変換できる必要があります。同じブロックまたは格納中のブロック内ですでに宣言されているその他の変数を参照できます。

次の例では、変数 x を INT64 として値 NULL で初期化しています。

DECLARE x INT64;

次の例では、変数 d を DATE として現在の日付の値で初期化しています。

DECLARE d DATE DEFAULT CURRENT_DATE();

次の例では、xyzINT64 として値 0 で初期化しています。

DECLARE x, y, z INT64 DEFAULT 0;

次の例では、dataset1.products テーブルの任意の項目に対応する item という名前の変数を宣言しています。item の型はテーブル スキーマから推測されます。

DECLARE item DEFAULT (SELECT item FROM dataset1.products LIMIT 1);

SET

構文

SET variable_name = expression;
SET (variable_name[, ...]) = (expression[, ...]);

説明

変数が指定された式の値を持つように設定するか、または複数の式の結果に基づいて複数の変数を同時に設定します。

SET ステートメントは、複数ステートメント クエリ内の何処にでも表示できます。

次の例では、変数 x の値を 5 に設定しています。

SET x = 5;

次の例では、a の値を 4、b の値を 'foo'、c の値を false にそれぞれ設定しています。

SET (a, b, c) = (1 + 3, 'foo', false);

次の例では、クエリの結果を複数の変数に代入しています。まず、2 つの変数 target_wordcorpus_count を宣言します。次に、SELECT AS STRUCT クエリの結果を 2 つの変数に割り当てます。クエリの結果は、2 つのフィールドを持つ STRUCT を含む 1 つの行です。最初の要素は最初の変数に割り当てられ、2 番目の要素は 2 番目の変数に割り当てられます。

DECLARE target_word STRING DEFAULT 'methinks';
DECLARE corpus_count, word_count INT64;

SET (corpus_count, word_count) = (
  SELECT AS STRUCT COUNT(DISTINCT corpus), SUM(word_count)
  FROM `bigquery-public-data`.samples.shakespeare
  WHERE LOWER(word) = target_word
);

SELECT
  FORMAT('Found %d occurrences of "%s" across %d Shakespeare works',
         word_count, target_word, corpus_count) AS result;

このステートメント リストは、次の文字列を出力します。

Found 151 occurrences of "methinks" across 38 Shakespeare works

EXECUTE IMMEDIATE

構文

EXECUTE IMMEDIATE sql_expression [ INTO variable[, ...] ] [ USING identifier[, ...] ];

sql_expression:
  { "query_statement" | expression("query_statement") }

identifier:
  { variable | value } [ AS alias ]

説明

動的 SQL ステートメントをすぐに実行します。

  • sql_expression: クエリ ステートメント、クエリ ステートメントで使用できる式、単一の DDL ステートメント、または単一の DML ステートメントを表します。IF のような制御ステートメントは使用できません。
  • expression: 関数条件式式サブクエリのいずれかです。
  • query_statement: 実行する有効なスタンドアロン SQL ステートメントを表します。値を返す場合、INTO 句には同じ型の値を含める必要があります。USING 句にあるシステム変数と値の両方にアクセスできます。他のすべてのローカル変数とクエリ パラメータは、クエリ ステートメントに公開されません。
  • INTO 句: SQL 式の実行後、INTO 句を使用して結果を 1 つ以上の変数に格納できます。
  • USING 句: SQL 式の実行前、USING 句から 1 つ以上の識別子を SQL 式に渡すことができます。これらの識別子はクエリ パラメータと同様に機能し、クエリ ステートメントに値を公開します。識別子は変数または値です。

USING 句で参照される識別子の query_statement には、次のプレースホルダを含めることができます。

  • ?: このプレースホルダの値は、インデックスで USING 句の識別子にバインドされます。

    -- y = 1 * (3 + 2) = 5
    EXECUTE IMMEDIATE "SELECT ? * (? + 2)" INTO y USING 1, 3;
    
  • @identifier: このプレースホルダの値は、名前で USING 句の識別子にバインドされます。この構文はクエリ パラメータの構文と同じです。

    -- y = 1 * (3 + 2) = 5
    EXECUTE IMMEDIATE "SELECT @a * (@b + 2)" INTO y USING 1 as a, 3 as b;
    

EXECUTE IMMEDIATE ステートメントの動作に関するその他の注意事項は次のとおりです。

  • EXECUTE IMMEDIATE は、ネストされた要素として動的に実行されません。つまり、別の EXECUTE IMMEDIATE ステートメントに EXECUTE IMMEDIATE をネストすることはできません。
  • EXECUTE IMMEDIATE ステートメントが結果を返す場合、それらの結果はステートメント全体の結果になり、適切なシステム変数が更新されます。
  • 同じ変数を INTO 句と USING 句の両方に使用できます。
  • query_statement には、他のステートメント(BEGIN...END など)を含む単一の解析済みステートメントを含めることができます。
  • ゼロ行の値テーブルからも含めて、query_statement からゼロ行が返される場合、INTO 句のすべての変数は NULL に設定されます。
  • ゼロ行の値のテーブルからも含めて、1 つの行が query_statement から返される場合、値は変数名ではなく位置で割り当てられます。
  • INTO 句が存在する場合、query_statement から複数の行を返そうとするとエラーがスローされます。

この例では、書籍の表を作成してその表にデータを入力します。変数の参照、変数への値の保存、式の使用にはさまざまな方法があります。

-- create some variables
DECLARE book_name STRING DEFAULT 'Ulysses';
DECLARE book_year INT64 DEFAULT 1922;
DECLARE first_date INT64;

-- Create a temporary table called Books.
EXECUTE IMMEDIATE
  "CREATE TEMP TABLE Books (title STRING, publish_date INT64)";

-- Add a row for Hamlet (less secure)
EXECUTE IMMEDIATE
  "INSERT INTO Books (title, publish_date) VALUES('Hamlet', 1599)";

-- add a row for Ulysses, using the variables declared and the ? placeholder
EXECUTE IMMEDIATE
  "INSERT INTO Books (title, publish_date) VALUES(?, ?)"
  USING book_name, book_year;

-- add a row for Emma, using the identifier placeholder
EXECUTE IMMEDIATE
  "INSERT INTO Books (title, publish_date) VALUES(@name, @year)"
  USING 1815 as year, "Emma" as name;

-- add a row for Middlemarch, using an expression
EXECUTE IMMEDIATE
  CONCAT(
    "INSERT INTO Books (title, publish_date)", "VALUES('Middlemarch', 1871)"
  );

-- save the publish date of the first book, Hamlet, to a variable called
-- first_date
EXECUTE IMMEDIATE "SELECT publish_date FROM Books LIMIT 1" INTO first_date;

+------------------+------------------+
| title            | publish_date     |
+------------------+------------------+
| Hamlet           | 1599             |
| Ulysses          | 1922             |
| Emma             | 1815             |
| Middlemarch      | 1871             |
+------------------+------------------+

BEGIN...END

構文

BEGIN
  sql_statement_list
END;

説明

BEGIN は、宣言された変数が対応する END まで存在するステートメントのブロックを開始します。sql_statement_list は、それぞれがセミコロンで終わるゼロ個以上の SQL ステートメントのリストです。

変数宣言は、ブロックの先頭で、その他のタイプのステートメントよりも前に配置する必要があります。ブロック内で宣言された変数は、そのブロック内とネストされたブロック内でのみ参照できます。同じブロックまたは外側のブロックで宣言されている変数と同じ名前の変数を宣言するとエラーになります。

ブロックや条件文(BEGIN / ENDIF / ELSE / END IFWHILE / END WHILE など)の最大のネストレベルは 50 です。

BEGIN / END は、ネストされた要素として動的に実行されません。

このステートメントではラベルを使用できます。詳細については、ラベルをご覧ください。

次の例では、変数 x にデフォルト値 10 を宣言しています。次にブロックを開始し、変数 y に値 x(この場合は 10)が割り当てられ、この値を返します。さらに、END ステートメントはブロックを終了し、変数 y のスコープを終了します。最後に x の値を返します。

DECLARE x INT64 DEFAULT 10;
BEGIN
  DECLARE y INT64;
  SET y = x;
  SELECT y;
END;
SELECT x;

BEGIN...EXCEPTION...END

構文

BEGIN
  sql_statement_list
EXCEPTION WHEN ERROR THEN
  sql_statement_list
END;

説明

BEGIN...EXCEPTION はステートメントのブロックを実行します。ステートメントのいずれかにエラーが発生した場合、ブロックの残りの部分はスキップされ、EXCEPTION 句内のステートメントが実行されます。

EXCEPTION 句内では、次の EXCEPTION システム変数を使用することでエラーの詳細にアクセスできます。

名前 説明
@@error.formatted_stack_trace STRING @@error.stack_trace のコンテンツは人が読める文字列として表現されます。この値は表示を目的に使用され、予告なく変更されることがあります。プログラムによるエラーのスタック トレースへのアクセスには、代わりに @@error.stack_trace を使用してください。
@@error.message STRING 人が読めるエラー メッセージを指定します。
@@error.stack_trace 1 をご覧ください。 配列の各要素は、エラーの時点で実行しているステートメントまたはプロシージャ コールに対応し、現在実行中のスタック フレームが先頭に表示されます。各フィールドの意味の定義は、次のとおりです。
  • 行 / 列: スタック フレームの行番号と列番号を 1 から開始して指定します。フレームがプロシージャの本文内で発生した場合、line 1 column 1 はプロシージャの先頭にある BEGIN キーワードに対応します。
  • ロケーション: フレームがプロシージャ本文内にある場合、[project_name].[dataset_name].[procedure_name] 形式でプロシージャのフルネームを指定します。フレームがトップレベルの複数ステートメント クエリ内の場所を参照している場合、このフィールドは NULL になります。
  • ファイル名: 将来の使用のために予約されています。常に NULL です。
@@error.statement_text STRING エラーの原因となったステートメントのテキストを指定します。

1: @@error.stack_trace の型は ARRAY<STRUCT<line INT64, column INT64, filename STRING, location STRING>> です。

BigQuery はエラー メッセージをいつでも修正できるため、@@error.message の使用にあたっては、エラー メッセージが常に同じである、または一定のパターンであることを想定しないでください。エラー メッセージからテキストを抽出してエラーの場所情報を取得せず、代わりに @@error.stack_trace@@error.statement_text を使用してください。

例外ハンドラによってスローされる(そして、処理されない)例外を処理するには、個別の例外ハンドラを使用して外側のブロック内にあるブロックをラップする必要があります。

外側のブロックに別の例外ハンドラを使用する方法は次のとおりです。

BEGIN
  BEGIN
    ...
  EXCEPTION WHEN ERROR THEN
    SELECT 1/0;
  END;
EXCEPTION WHEN ERROR THEN
  -- The exception thrown from the inner exception handler lands here.
END;

BEGIN...EXCEPTION ブロックは、他の BEGIN ブロック同様に DECLARE ステートメントもサポートします。BEGIN ブロックで宣言されている変数は、BEGIN セクションでのみ有効で、ブロックの例外ハンドラでは使用できません。

このステートメントではラベルを使用できます。詳細については、ラベルをご覧ください。

CREATE OR REPLACE PROCEDURE dataset1.proc1() BEGIN
  SELECT 1/0;
END;

CREATE OR REPLACE PROCEDURE dataset1.proc2() BEGIN
  CALL dataset1.proc1();
END;

BEGIN
  CALL dataset1.proc2();
EXCEPTION WHEN ERROR THEN
  SELECT
    @@error.message,
    @@error.stack_trace,
    @@error.statement_text,
    @@error.formatted_stack_trace;
END;

この例では、ゼロ除算のエラーが発生した場合に複数のステートメント・クエリ全体が終了するのではなく、BigQuery が dataset1.proc1()dataset1.proc2() を停止し、例外ハンドラで SELECT ステートメントを実行します。例外ハンドラが実行されると、変数の値は次のようになります。

変数
@@error.message "Query error: division by zero: 1 / 0 at <project>.dataset1.proc1:2:3]"
@@error.stack_trace [
STRUCT(2 AS line, 3 AS column, NULL AS filename, "<project>.dataset1.proc1:2:3" AS location),
STRUCT(2 AS line, 3 AS column, NULL AS filename, "<project>.dataset1.proc2:2:3" AS location),
STRUCT(10 AS line, 3 AS column, NULL AS filename, NULL AS location),
]
@@error.statement_text "SELECT 1/0"
@@error.formatted_stack_trace "At <project>.dataset1.proc1[2:3]\nAt <project>.dataset1.proc2[2:3]\nAt [10:3]"

CASE

構文

CASE
  WHEN boolean_expression THEN sql_statement_list
  [...]
  [ELSE sql_statement_list]
END CASE;

Description

ブール式が true である THEN sql_statement_list を実行するか、条件が一致しない場合はオプションの ELSE sql_statement_list を実行します。

CASE には最大 50 個のネストレベルを設定できます。

CASE は、ネストされた要素として動的に実行されません。つまり、EXECUTE IMMEDIATE ステートメントに CASE をネストすることはできません。

この例では、products_a テーブルの target_product_ID に検索が行われています。ID が見つからない場合は、products_b テーブルでその ID が検索されます。ID が見つからない場合は、ELSE ブロックのステートメントが実行されます。

DECLARE target_product_id INT64 DEFAULT 103;
CASE
  WHEN
    EXISTS(SELECT 1 FROM dataset.products_a WHERE product_id = target_product_id)
    THEN SELECT 'found product in products_a table';
  WHEN
    EXISTS(SELECT 1 FROM dataset.products_b WHERE product_id = target_product_id)
    THEN SELECT 'found product in products_b table';
  ELSE
    SELECT 'did not find product';
END CASE;

CASE search_expression

構文

CASE search_expression
  WHEN expression THEN sql_statement_list
  [...]
  [ELSE sql_statement_list]
END CASE;

説明

検索式が WHEN 式と一致する最初の sql_statement_list を実行します。search_expression が評価されると、一致するかどうかが判明するまで各 WHEN 式が等しいかどうかテストされます。一致するものが見つからない場合、オプションの ELSE sql_statement_list が実行されます。

CASE には最大 50 個のネストレベルを設定できます。

CASE は、ネストされた要素として動的に実行されません。つまり、EXECUTE IMMEDIATE ステートメントに CASE をネストすることはできません。

次の例では、検索 ID として商品 ID を使用しています。ID が 1 の場合、'Product one' が返されます。ID が 2 の場合、'Product two' が返されます。ID がそれ以外の場合、Invalid product が返されます。

DECLARE product_id INT64 DEFAULT 1;
CASE product_id
  WHEN 1 THEN
    SELECT CONCAT('Product one');
  WHEN 2 THEN
    SELECT CONCAT('Product two');
  ELSE
    SELECT CONCAT('Invalid product');
END CASE;

IF

構文

IF condition THEN [sql_statement_list]
  [ELSEIF condition THEN sql_statement_list]
  [...]
  [ELSE sql_statement_list]
END IF;

説明

条件が true の場合は最初の sql_statement_list を、条件が一致しない場合はオプションの ELSE sql_statement_list を実行します。

ブロックや条件文(BEGIN / ENDIF / ELSE / END IFWHILE / END WHILE など)の最大のネストレベルは 50 です。

IF は、ネストされた要素として動的に実行されません。つまり、EXECUTE IMMEDIATE ステートメントに IF をネストすることはできません。

次の例では、INT64 変数 target_product_id のデフォルト値が 103 と宣言されています。この宣言によりテーブル dataset.productsproduct_id 列を持つ行を含み target_product_id の値と一致するかどうかがチェックされます。一致する場合は、プロダクトが見つかったという文字列と default_product_id の値が出力されます。一致しない場合は、プロダクトが見つからなかったという文字列と default_product_id の値が出力されます。

DECLARE target_product_id INT64 DEFAULT 103;
IF EXISTS(SELECT 1 FROM dataset.products
           WHERE product_id = target_product_id) THEN
  SELECT CONCAT('found product ', CAST(target_product_id AS STRING));
  ELSEIF EXISTS(SELECT 1 FROM dataset.more_products
           WHERE product_id = target_product_id) THEN
  SELECT CONCAT('found product from more_products table',
  CAST(target_product_id AS STRING));
ELSE
  SELECT CONCAT('did not find product ', CAST(target_product_id AS STRING));
END IF;

ラベル

構文

label_name: BEGIN
  block_statement_list
END [label_name];
label_name: LOOP
  loop_statement_list
END LOOP [label_name];
label_name: WHILE condition DO
  loop_statement_list
END WHILE [label_name];
label_name: FOR variable IN query DO
  loop_statement_list
END FOR [label_name];
label_name: REPEAT
  loop_statement_list
  UNTIL boolean_condition
END REPEAT [label_name];
block_statement_list:
  { statement | break_statement_with_label }[, ...]

loop_statement_list:
  { statement | break_continue_statement_with_label }[, ...]

break_statement_with_label:
  { BREAK | LEAVE } label_name;

break_continue_statement_with_label:
  { BREAK | LEAVE | CONTINUE | ITERATE } label_name;

説明

ラベルがある BREAK または CONTINUE ステートメントは、そのラベルに関連付けられたブロックまたはループの末尾へ無条件でジャンプします。ブロックまたはループとともにラベルを使用するには、そのラベルをブロックまたはループの先頭と、必要に応じて末尾に配置する必要があります。

  • ラベル名には任意の Google 標準 SQL 識別子を使用できます。これには、バッククォートを使用して予約文字やキーワードを含めることも含まれます。
  • マルチパート パス名は、引用符付きの識別子としてのみ使用できます。

    `foo.bar`: BEGIN ... END -- Works
    foo.bar: BEGIN ... END -- Does not work
    
  • ラベル名では大文字と小文字が区別されません。

  • 各ストアド プロシージャには、独立したラベル名ストアがあります。たとえば、プロシージャでは、呼び出し側のプロシージャで使用されているラベルを再定義できます。

  • ループまたはブロックで、外側のループまたはブロックで使用されているラベル名を繰り返すことはできません。

  • 手続き型ステートメントでは、ラベル名を重複しない部分で繰り返し使用できます。

  • 同じ名前のラベルと変数は使用できます。

  • BREAKLEAVECONTINUEITERATE ステートメントがラベルを指定する場合、常に最も内側のループを選択するのではなく、ループを終了するか、ループをラベル名と一致し続けます。

ブロックまたはループは、その内部でのみ参照できます。

label_1: BEGIN
  SELECT 1;
  BREAK label_1;
  SELECT 2; -- Unreached
END;
label_1: LOOP
  BREAK label_1;
END LOOP label_1;

WHILE x < 1 DO
  CONTINUE label_1; -- Error
END WHILE;

複数ステートメント クエリの重複しない部分では、ラベル名を繰り返すことができます。良い例:

label_1: BEGIN
  BREAK label_1;
END;

label_2: BEGIN
  BREAK label_2;
END;

label_1: BEGIN
  BREAK label_1;
END;

ループまたはブロックで、外側のループまたはブロックで使用されているラベル名を繰り返すことはできません。以下のエラーをスローします。

label_1: BEGIN
   label_1: BEGIN -- Error
     BREAK label_1;
   END;
END;

ラベルと変数には同じ名前を使用できます。以下は機能します。

label_1: BEGIN
   DECLARE label_1 INT64;
   BREAK label_1;
END;

ブロックまたはループを終了する END キーワードにラベル名を指定できますが、省略可能です。以下のどちらも機能します。

label_1: BEGIN
  BREAK label_1;
END label_1;
label_1: BEGIN
  BREAK label_1;
END;

ブロックまたはループの先頭にラベルがない場合、そのブロックまたはループの末尾にラベルを付けることはできません。以下のエラーをスローします。

BEGIN
  BREAK label_1;
END label_1;

この例では、BREAK ステートメントと CONTINUE ステートメントは、内部の WHILE x < 1 DO ループではなく、外部 label_1: LOOP をターゲットにしています。

label_1: LOOP
  WHILE x < 1 DO
    IF y < 1 THEN
      CONTINUE label_1;
    ELSE
      BREAK label_1;
  END WHILE;
END LOOP label_1

存在しないラベルを指定する BREAKLEAVECONTINUEITERATE ステートメントは、エラーをスローします。

WHILE x < 1 DO
  BREAK label_1; -- Error
END WHILE;

例外ハンドラ セクション内からブロックを終了できます。

label_1: BEGIN
  SELECT 1;
  EXCEPTION WHEN ERROR THEN
    BREAK label_1;
    SELECT 2; -- Unreached
END;

CONTINUE はブロックラベルでは使用できません。以下のエラーをスローします。

label_1: BEGIN
  SELECT 1;
  CONTINUE label_1; -- Error
  SELECT 2;
END;

ループ

LOOP

構文

LOOP
  sql_statement_list
END LOOP;

説明

BREAK または LEAVE ステートメントでループが終了するまで、sql_statement_list が実行されます。sql_statement_list は、それぞれがセミコロンで終わるゼロ個以上の SQL ステートメントのリストです。

LOOP は、ネストされた要素として動的に実行されません。つまり、EXECUTE IMMEDIATE ステートメントに LOOP をネストすることはできません。

このステートメントではラベルを使用できます。詳細については、ラベルをご覧ください。

次の例では、変数 x にデフォルト値 0 を宣言しています。次に、LOOP ステートメントを使用して、変数 x が 10 以上になるまで実行されるループを作成します。ループが終了すると、x の値が出力されます。

DECLARE x INT64 DEFAULT 0;
LOOP
  SET x = x + 1;
  IF x >= 10 THEN
    LEAVE;
  END IF;
END LOOP;
SELECT x;

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

+----+
| x  |
+----+
| 10 |
+----+

REPEAT

構文

REPEAT
  sql_statement_list
  UNTIL boolean_condition
END REPEAT;

説明

リストの末尾のブール条件が TRUE になるまで、ゼロ個以上の SQL ステートメントのリストを繰り返し実行します。ブール条件は式にする必要があります。BREAK または LEAVE ステートメントを使用して、このループを早期に終了できます。

REPEAT は、ネストされた要素として動的に実行されません。つまり、EXECUTE IMMEDIATE ステートメントに REPEAT をネストすることはできません。

このステートメントではラベルを使用できます。詳細については、ラベルをご覧ください。

次の例では、デフォルト値 0 を使用して変数 x を宣言しています。次に、REPEAT ステートメントを使用して、変数 x3 以上になるまで実行されるループを作成しています。

DECLARE x INT64 DEFAULT 0;

REPEAT
  SET x = x + 1;
  SELECT x;
  UNTIL x >= 3
END REPEAT;

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

+---+
| x |
+---+
| 1 |
+---+

+---+
| x |
+---+
| 2 |
+---+

+---+
| x |
+---+
| 3 |
+---+

WHILE

構文

WHILE boolean_expression DO
  sql_statement_list
END WHILE;

ブロックや条件文(BEGIN / ENDIF / ELSE / END IFWHILE / END WHILE など)の最大のネストレベルは 50 です。

説明

boolean_expression が true の場合は、sql_statement_list が実行されます。boolean_expression は、ループの反復処理ごとに評価されます。

WHILE は、ネストされた要素として動的に実行されません。つまり、EXECUTE IMMEDIATE ステートメントに WHILE をネストすることはできません。

このステートメントではラベルを使用できます。詳細については、ラベルをご覧ください。

BREAK

構文

BREAK;

説明

現在のループを終了します。

BREAK をループの外側で使用するとエラーが発生します。

このステートメントではラベルを使用できます。詳細については、ラベルをご覧ください。

次の例では、変数 headsheads_count が宣言されています。次に、ループを開始します。ループ内では、ランダムなブール値が heads に割り当てられています。次に、heads が true の場合に「Heads!」(コインの表)と出力され、heads_count が増分されます。true でない場合は「Tails!」(コインの裏)と出力され、ループが終了します。コインのフリップが表になった回数を表す文字列が出力されます。

DECLARE heads BOOL;
DECLARE heads_count INT64 DEFAULT 0;
LOOP
  SET heads = RAND() < 0.5;
  IF heads THEN
    SELECT 'Heads!';
    SET heads_count = heads_count + 1;
  ELSE
    SELECT 'Tails!';
    BREAK;
  END IF;
END LOOP;
SELECT CONCAT(CAST(heads_count AS STRING), ' heads in a row');

LEAVE

BREAK と同義。

CONTINUE

構文

CONTINUE;

説明

現在のループ内の次のステートメントをスキップし、ループの先頭に戻ります。

CONTINUE をループの外側で使用するとエラーが発生します。

このステートメントではラベルを使用できます。詳細については、ラベルをご覧ください。

次の例では、変数 headsheads_count が宣言されています。次に、ループを開始します。ループ内では、ランダムなブール値が heads に割り当てられています。次に、heads が true の場合に「Heads!」(コインの表)と出力され、heads_count が増分され、ループの最初に戻ります(その他のステートメントは飛ばされる)。true でない場合は「Tails!」(コインの裏)と出力され、ループが終了します。コインのフリップが表になった回数を表す文字列が出力されます。

DECLARE heads BOOL;
DECLARE heads_count INT64 DEFAULT 0;
LOOP
  SET heads = RAND() < 0.5;
  IF heads THEN
    SELECT 'Heads!';
    SET heads_count = heads_count + 1;
    CONTINUE;
  END IF;
  SELECT 'Tails!';
  BREAK;
END LOOP;
SELECT CONCAT(CAST(heads_count AS STRING), ' heads in a row');

ITERATE

CONTINUE と同義。

FOR...IN

構文

FOR loop_variable_name IN (table_expression)
DO
  sql_expression_list
END FOR;

説明

table_expression のすべての行をループして、loop_variable_name に行を割り当てます。各ループ内で、sql_expression_list の SQL ステートメントが loop_variable_name の現在の値を使用して実行されます。

table_expression の値は、ループの開始時に 1 回評価されます。それぞれの反復処理で、loop_variable_name の値は STRUCT であり、フィールドとしてテーブル式の最上位列を含んでいます。テーブル式に最上位の ORDER BY 句または UNNEST 配列演算子がない限り、loop_variable_name に値が割り当てられる順序は定義されません。

loop_variable_name のスコープはループ本文です。loop_variable_name の名前は、同じスコープ内の他の変数と競合できません。

このステートメントではラベルを使用できます。詳細については、ラベルをご覧ください。

FOR record IN
  (SELECT word, word_count
   FROM bigquery-public-data.samples.shakespeare
   LIMIT 5)
DO
  SELECT record.word, record.word_count;
END FOR;

トランザクション

BEGIN TRANSACTION

構文

BEGIN [TRANSACTION];

説明

トランザクションを開始します。

COMMIT TRANSACTION または ROLLBACK TRANSACTION ステートメントに達すると、トランザクションが終了します。これらのステートメントのいずれかに到達する前に実行が終了すると、自動ロールバックが発生します。

BigQuery のトランザクションの詳細については、マルチステートメント トランザクションをご覧ください。

次の例では、既存のテーブルから一時テーブルに行を選択し、これらの行を元のテーブルから削除して一時テーブルを別のテーブルに統合するトランザクションを実行します。

BEGIN TRANSACTION;

-- Create a temporary table of new arrivals from warehouse #1
CREATE TEMP TABLE tmp AS
SELECT * FROM mydataset.NewArrivals WHERE warehouse = 'warehouse #1';

-- Delete the matching records from the original table.
DELETE mydataset.NewArrivals WHERE warehouse = 'warehouse #1';

-- Merge the matching records into the Inventory table.
MERGE mydataset.Inventory AS I
USING tmp AS T
ON I.product = T.product
WHEN NOT MATCHED THEN
 INSERT(product, quantity, supply_constrained)
 VALUES(product, quantity, false)
WHEN MATCHED THEN
 UPDATE SET quantity = I.quantity + T.quantity;

DROP TABLE tmp;

COMMIT TRANSACTION;

COMMIT TRANSACTION

構文

COMMIT [TRANSACTION];

説明

オープン トランザクションを commit します。進行中のトランザクションがない場合、ステートメントは失敗します。

BigQuery のトランザクションの詳細については、マルチステートメント トランザクションをご覧ください。

BEGIN TRANSACTION;

-- SQL statements for the transaction go here.

COMMIT TRANSACTION;

ROLLBACK TRANSACTION

構文

ROLLBACK [TRANSACTION];

説明

オープン トランザクションをロールバックします。進行中のトランザクションがない場合、ステートメントは失敗します。

BigQuery のトランザクションの詳細については、マルチステートメント トランザクションをご覧ください。

次の例では、トランザクションでエラーが発生した場合にトランザクションをロールバックします。ロジックを説明するために、テーブルに行を挿入した後にゼロ除算エラーをトリガーします。これらのステートメントが実行されても、テーブルは影響を受けません。

BEGIN

  BEGIN TRANSACTION;
  INSERT INTO mydataset.NewArrivals
    VALUES ('top load washer', 100, 'warehouse #1');
  -- Trigger an error.
  SELECT 1/0;
  COMMIT TRANSACTION;

EXCEPTION WHEN ERROR THEN
  -- Roll back the transaction inside the exception handler.
  SELECT @@error.message;
  ROLLBACK TRANSACTION;
END;

RAISE

構文

RAISE [USING MESSAGE = message];

説明

USING MESSAGE = message が指定されると、必要に応じて特定のエラー メッセージを使用して、エラーを発生させます。

USING MESSAGE が指定されていない場合

RAISE ステートメントは EXCEPTION 句内でのみ使用する必要があります。RAISE ステートメントは検出された例外を再生成し、元のスタック トレースを保持します。

USING MESSAGE が指定されている場合

RAISE ステートメントが BEGIN...EXCEPTION ブロックの BEGIN セクションに含まれている場合、次のようになります。

  • ハンドラが呼び出されます。
  • @@error.message の値は、指定された message 文字列(messageNULL の場合は NULL になる可能性があります)と完全に一致します。

  • スタック トレースは、RAISE ステートメントに設定されます。

RAISE ステートメントが BEGIN...EXCEPTION ブロックの BEGIN セクションに含まれていない場合、RAISE ステートメントは指定したエラー メッセージを表示し、複数ステートメント クエリの実行を停止します。

RETURN

RETURN は複数ステートメント クエリの実行を停止します。

発信

構文

CALL procedure_name (procedure_argument[, …])

説明

引数リストとともにプロシージャを呼び出します。procedure_argument は、変数または式とすることが可能です。OUT 引数または INOUT 引数の場合、引数として渡される変数ごとに適切な Google 標準 SQL のタイプが指定される必要があります。

同じ変数を、プロシージャの引数リストに OUT 引数または INOUT 引数として複数回指定することはできません。

プロシージャ呼び出しの最大深度は 50 フレームです。

CALL は、ネストされた要素として動的に実行されません。つまり、EXECUTE IMMEDIATE ステートメントに CALL をネストすることはできません。

次の例では、変数 retCode を宣言しています。次に、引数 'someAccountId'retCode を渡し、データセット myDataset 内でプロシージャ updateSomeTables を呼び出します。最後に、retCode の値を返します。

DECLARE retCode INT64;
-- Procedure signature: (IN account_id STRING, OUT retCode INT64)
CALL myDataset.UpdateSomeTables('someAccountId', retCode);
SELECT retCode;