Langage procédural

Le langage procédural SQL standard de Google vous permet d'exécuter plusieurs instructions en une seule requête en tant que requête à plusieurs instructions. Vous pouvez utiliser une requête à plusieurs instructions pour:

• exécuter plusieurs requêtes à la suite, avec un état partagé ;
• Automatiser les tâches de gestion telles que la création ou la suppression de tables.
• mettre en œuvre une logique complexe à l'aide de boucles de programmation telles que IF et WHILE.

Ce document de référence contient les instructions faisant partie du langage procédural SQL standard de Google. Pour en savoir plus sur l'utilisation de ce langage procédural pour écrire des requêtes multi-instructions, consultez la page Utiliser des requêtes multi-instructions. Pour savoir comment convertir des requêtes multi-instructions en procédures stockées, consultez la page Utiliser des procédures stockées.

DECLARE

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

variable_name doit être un identifiant valide, et variable_type désigne n'importe quel type de langage SQL standard de Google.

Description

Déclare une variable du type spécifié. Si la clause DEFAULT est spécifiée, la variable est initialisée avec la valeur de l'expression. Si aucune clause DEFAULT n'est présente, la variable est initialisée avec la valeur NULL.

Si [variable_type] est omis, vous devez spécifier une clause DEFAULT. Le type de la variable sera déduit du type d'expression spécifié dans la clause DEFAULT.

Les déclarations de variables doivent apparaître avant d'autres instructions procédurales ou au début d'un bloc BEGIN. Les noms de variables ne sont pas sensibles à la casse.

Une seule instruction DECLARE peut contenir plusieurs noms de variables, mais un seul variable_type et une seule expression.

Vous ne pouvez pas déclarer une variable portant le même nom qu'une variable déclarée précédemment dans le bloc actuel ou dans un bloc conteneur.

Si la clause DEFAULT est présente, la valeur de l'expression doit pouvoir être convertie par coercition dans le type spécifié. L'expression peut référencer d'autres variables déclarées précédemment dans le même bloc ou dans un bloc conteneur.

Exemples

L'exemple suivant initialise la variable x en tant que INT64 avec la valeur NULL.

DECLARE x INT64;

L'exemple suivant initialise la variable d en tant que DATE avec la valeur de la date actuelle.

DECLARE d DATE DEFAULT CURRENT_DATE();

L'exemple suivant initialise les variables x, y et z en tant que INT64 avec la valeur 0.

DECLARE x, y, z INT64 DEFAULT 0;

L'exemple suivant déclare une variable nommée item correspondant à un élément arbitraire de la table dataset1.products. Le type de item est déduit du schéma de la table.

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

SET

Syntaxe

SET variable_name = expression;

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

Description

Définit une variable comme ayant la valeur de l'expression fournie, ou définit plusieurs variables en même temps en fonction du résultat de plusieurs expressions.

L'instruction SET peut apparaître n'importe où dans une requête multi-instruction.

Exemples

L'exemple suivant définit la variable x comme ayant la valeur 5.

SET x = 5;

L'exemple suivant définit la variable a comme ayant la valeur 4, b comme ayant la valeur "foo", et la variable c comme ayant la valeur false.

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

L'exemple suivant attribue le résultat d'une requête à plusieurs variables. Tout d'abord, il déclare deux variables, target_word et corpus_count. Ensuite, il attribue les résultats d'une requête SELECT AS STRUCT aux deux variables. Le résultat de la requête est une seule ligne contenant un type STRUCT avec deux champs. Le premier élément est attribué à la première variable, et le second élément à la seconde variable.

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;

Cette liste d'instructions génère la chaîne suivante :

Found 151 occurrences of "methinks" across 38 Shakespeare works

EXECUTE IMMEDIATE

Syntaxe

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

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

identifier:
  { variable | value } [ AS alias ]

Description

Exécute une instruction SQL dynamique à la volée.

• sql_expression : Représente une instruction de requête, une expression que vous pouvez utiliser sur une instruction de requête, une instruction LDD, ou une seule instruction LMD. Il ne peut pas s'agir d'une instruction de contrôle telle que IF.
• expression : peut être une fonction, une expression conditionnelle ou une sous-requête d'expression.
• query_statement : représente une instruction SQL autonome valide à exécuter. Si cela renvoie une valeur, la clause INTO doit contenir des valeurs du même type. Vous pouvez accéder aux variables système et aux valeurs présentes dans la clause USING. Toutes les autres variables locales et tous les paramètres de requête ne sont pas exposés à l'instruction de requête.
• Clause INTO : après l'exécution de l'expression SQL, vous pouvez stocker les résultats dans une ou plusieurs variables à l'aide de la clause INTO.
• Clause USING : avant d'exécuter votre expression SQL, vous pouvez transmettre un ou plusieurs identifiants de la clause USING dans l'expression SQL. Ces identifiants fonctionnent de la même manière que les paramètres de requête, en exposant des valeurs à l'instruction de requête. Un identifiant peut être une variable ou une valeur.

Vous pouvez inclure ces espaces réservés dans query_statement pour les identifiants référencés dans la clause USING :

• ? : la valeur de cet espace réservé est liée à un identifiant dans la clause USING par index.

  -- y = 1 * (3 + 2) = 5
  EXECUTE IMMEDIATE "SELECT ? * (? + 2)" INTO y USING 1, 3;
  

• @identifier : la valeur de cet espace réservé est liée à un identifiant dans la clause USING par son nom. Cette syntaxe est identique à la syntaxe du paramètre de requête.

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

Voici quelques remarques supplémentaires sur le comportement de l'instruction EXECUTE IMMEDIATE :

• EXECUTE IMMEDIATE ne peut pas être exécuté de manière dynamique en tant qu'élément imbriqué. Cela signifie que EXECUTE IMMEDIATE ne peut pas être imbriqué dans une autre instruction EXECUTE IMMEDIATE.
• Si une instruction EXECUTE IMMEDIATE renvoie des résultats, ceux-ci deviennent le résultat de l'instruction complète et toutes les variables système appropriées sont mises à jour.
• La même variable peut apparaître dans les clauses INTO et USING.
• query_statement peut contenir une seule instruction analysée contenant d'autres instructions (par exemple, BEGIN… END).
• Si aucune ligne n'est renvoyée à partir de query_statement, y compris à partir des tables de valeurs à zéro ligne, toutes les variables de la clause INTO sont définies sur NULL.
• Si une ligne est renvoyée à partir de query_statement, y compris à partir de tables de valeurs à zéro ligne, les valeurs sont attribuées par position, et non par nom de variable.
• Si une clause INTO est présente, une erreur est générée si vous essayez de renvoyer plusieurs lignes de query_statement.

Exemples

Dans cet exemple, nous créons une table de livres et y ajoutons des données. Notez les différentes façons de référencer des variables, d'enregistrer des valeurs dans des variables et d'utiliser des expressions.

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

Syntaxe

BEGIN
  sql_statement_list
END;

Description

BEGIN démarre un bloc d'instructions où les variables déclarées n'existent que jusqu'à l'instruction END correspondante. sql_statement_list correspond à une liste de zéro, une ou plusieurs instructions SQL se terminant par un point-virgule.

Les déclarations de variables doivent apparaître au début du bloc, avant tout autre type d'instructions. Les variables déclarées dans un bloc ne peuvent être référencées que dans ce bloc et dans les blocs imbriqués. Vous ne pouvez pas déclarer une variable portant le même nom qu'une variable déclarée dans le même bloc ou dans un bloc externe.

Le niveau d'imbrication maximal est de 50 pour les blocs et les instructions conditionnelles telles que BEGIN/END, IF/ELSE/END IF et WHILE/END WHILE.

BEGIN/END ne peut pas être exécuté dynamiquement en tant qu'élément imbriqué.

Vous pouvez utiliser un libellé avec cette instruction. Pour en savoir plus, consultez la section Libellés.

Exemples

L'exemple suivant déclare une variable x avec une valeur par défaut de 10, puis déclenche un bloc dans lequel la valeur x (10) est attribuée à la variable y, puis renvoie cette valeur. Ensuite, l'instruction END met fin au bloc, ce qui met fin au champ d'application de la variable y ; enfin, la valeur x est renvoyée.

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

BEGIN...EXCEPTION...END

Syntaxe

BEGIN
  sql_statement_list
EXCEPTION WHEN ERROR THEN
  sql_statement_list
END;

Description

BEGIN...EXCEPTION exécute un bloc d'instructions. Si l'une des instructions rencontre une erreur, le reste du bloc est ignoré et les instructions de la clause EXCEPTION sont exécutées.

Dans la clause EXCEPTION, vous pouvez accéder aux détails de l'erreur à l'aide des variables système EXCEPTION suivantes :

Nom	Type	Description
@@error.formatted_stack_trace	STRING	Contenu de @@error.stack_trace exprimé sous forme de chaîne lisible. Cette valeur est destinée à être affichée et peut être modifiée sans préavis. L'accès automatique à la trace de la pile d'une erreur doit utiliser @@error.stack_trace à la place.
@@error.message	STRING	Indique un message d'erreur lisible par l'utilisateur.
@@error.stack_trace	Voir 1.	Chaque élément du tableau correspond à un appel d'instruction ou de procédure exécuté au moment de l'erreur, la trace de la pile en cours d'exécution apparaissant en premier. La signification de chaque champ est définie comme suit : line/column : spécifie la ligne et le numéro de colonne de la trace de la pile, en commençant par 1. Si la trace se trouve dans un corps de procédure, alors line 1 column 1 correspond au mot clé BEGIN au début du corps de la procédure. location : si la trace se trouve dans un corps de procédure, ce champ spécifie le nom complet de la procédure, au format [project_name].[dataset_name].[procedure_name]. Si la trace fait référence à un emplacement dans une requête multi-instruction de premier niveau, ce champ est NULL. filename : réservé pour une utilisation ultérieure. Toujours NULL.
@@error.statement_text	STRING	Spécifie le texte de l'instruction à l'origine de l'erreur.

¹ Le type de @@error.stack_trace est ARRAY<STRUCT<line INT64, column INT64, filename STRING, location STRING>>.

BigQuery se réservant le droit de réviser les messages d'erreur à tout moment, les utilisateurs de @@error.message ne doivent pas s'appuyer sur des messages d'erreur identiques ou suivant un schéma particulier. Pour obtenir des informations sur l'emplacement des erreurs, évitez d'extraire du texte à partir du message d'erreur. Utilisez plutôt @@error.stack_trace et @@error.statement_text.

Pour gérer les exceptions qui sont générées (mais non traitées) par un gestionnaire d'exceptions, vous devez encapsuler le bloc dans un bloc externe avec un gestionnaire d'exceptions distinct.

L'exemple suivant montre comment utiliser un bloc externe avec un gestionnaire d'exceptions distinct :

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;

Les blocs BEGIN...EXCEPTION acceptent également les instructions DECLARE, comme tous les autres blocs BEGIN. Les variables déclarées dans un bloc BEGIN ne sont valides que dans la section BEGIN, mais ne peuvent pas être utilisées dans le gestionnaire d'exceptions de ce bloc.

Vous pouvez utiliser un libellé avec cette instruction. Pour en savoir plus, consultez la section Libellés.

Exemples

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;

Dans cet exemple, lorsque l'erreur de division par zéro se produit, au lieu d'arrêter l'ensemble de la requête multi-instruction, BigQuery arrête dataset1.proc1() et dataset1.proc2(), puis exécute l'instruction SELECT dans le gestionnaire d'exceptions. Lorsque le gestionnaire d'exceptions s'exécute, les variables ont les valeurs suivantes :

Variable	Valeur
@@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

Syntaxe

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

Description

Exécute THEN sql_statement_list lorsque l'expression booléenne est vraie, ou l'instruction facultative ELSE sql_statement_list si la condition n'est pas satisfaite.

CASE peut comporter jusqu'à 50 niveaux d'imbrication.

CASE ne peut pas être exécuté de manière dynamique en tant qu'élément imbriqué. Cela signifie que CASE ne peut pas être imbriqué dans une instruction EXECUTE IMMEDIATE.

Exemples

Dans cet exemple, une recherche est effectuée pour l'élément target_product_ID dans la table products_a. Si l'ID est introuvable, une recherche est effectuée pour cet ID dans la table products_b. Si l'ID est introuvable, l'instruction du bloc ELSE est exécutée.

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

Syntaxe

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

Description

Exécute la première expression sql_statement_list dont l'expression de recherche correspond à une expression WHEN. La search_expression est évaluée une fois, puis testée sur chaque expression WHEN pour vérifier l'égalité jusqu'à ce qu'une correspondance soit trouvée. Si aucune correspondance n'est trouvée, l'instruction facultative ELSE sql_statement_list est exécutée.

CASE peut comporter jusqu'à 50 niveaux d'imbrication.

CASE ne peut pas être exécuté de manière dynamique en tant qu'élément imbriqué. Cela signifie que CASE ne peut pas être imbriqué dans une instruction EXECUTE IMMEDIATE.

Exemples

L'exemple suivant utilise l'ID du produit en tant qu'expression de recherche. Si l'ID est 1, la valeur 'Product one' est renvoyée. Si l'ID est 2, la valeur 'Product two' est renvoyée. Si l'ID est différent, Invalid product est renvoyé.

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

Syntaxe

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

Description

Exécute le premier bloc sql_statement_list où la condition est "true", ou le bloc ELSE sql_statement_list facultatif si aucune condition ne correspond.

Le niveau d'imbrication maximal est de 50 pour les blocs et les instructions conditionnelles telles que BEGIN/END, IF/ELSE/END IF et WHILE/END WHILE.

IF ne peut pas être exécuté de manière dynamique en tant qu'élément imbriqué. Cela signifie que IF ne peut pas être imbriqué dans une instruction EXECUTE IMMEDIATE.

Exemples

L'exemple suivant déclare une variable INT64 target_product_id avec une valeur par défaut de 103. Ensuite, il vérifie si la table dataset.products contient une ligne où la colonne product_id correspond à la valeur de target_product_id. Si c'est le cas, il génère une chaîne indiquant que le produit a été trouvé, ainsi que la valeur de default_product_id. Si ce n'est pas le cas, il génère une chaîne indiquant que le produit n'a pas été trouvé, ainsi que la valeur de 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;

Libellés

Syntaxe

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;

Description

Une instruction BREAK ou CONTINUE avec un libellé fournit un saut inconditionnel à la fin du bloc ou de la boucle associée à ce libellé. Pour utiliser un libellé avec un bloc ou une boucle, celui-ci doit apparaître au début du bloc ou de la boucle, et éventuellement à la fin.

• Un nom de libellé peut être constitué de n'importe quel identifiant SQL standard Google, pouvant comporter des accents graves afin d'inclure des caractères ou des mots clés réservés.

• Vous pouvez utiliser des noms de chemins en plusieurs parties, mais uniquement en tant qu'identifiants entre guillemets.

  `foo.bar`: BEGIN ... END -- Works
  foo.bar: BEGIN ... END -- Does not work
  

• Les noms de libellés ne sont pas sensibles à la casse.

• Chaque procédure stockée possède un magasin indépendant de noms de libellés. Par exemple, une procédure peut redéfinir un libellé déjà utilisé dans une procédure appelante.

• Une boucle ou un bloc ne peut pas répéter un nom de libellé utilisé dans une boucle ou un bloc englobant.

• Les noms de libellés répétés sont autorisés dans les parties d'instructions procédurales qui ne se chevauchent pas.

• Un libellé et une variable peuvent porter le même nom.

• Lorsque l'instruction BREAK, LEAVE, CONTINUE ou ITERATE spécifie un libellé, elle quitte ou poursuit la boucle correspondant au nom du libellé plutôt que de toujours sélectionner la boucle la plus imbriquée.

Exemples

Vous ne pouvez référencer un bloc ou une boucle qu'au sein de celui-ci.

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;

Les noms de libellés répétés sont autorisés dans les parties de la requête multi-instruction qui ne se chevauchent pas. La proposition ci-dessous fonctionne :

label_1: BEGIN
  BREAK label_1;
END;

label_2: BEGIN
  BREAK label_2;
END;

label_1: BEGIN
  BREAK label_1;
END;

Une boucle ou un bloc ne peut pas répéter un nom de libellé utilisé dans une boucle ou un bloc englobant. Cela génère une erreur :

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

Un libellé et une variable peuvent porter le même nom. La proposition ci-dessous fonctionne :

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

Le mot clé END qui termine un bloc ou une boucle peut spécifier un nom de libellé, mais ce paramètre est facultatif. Ces deux méthodes fonctionnent :

label_1: BEGIN
  BREAK label_1;
END label_1;

label_1: BEGIN
  BREAK label_1;
END;

Vous ne pouvez pas spécifier de libellé à la fin d'un bloc ou d'une boucle s'il n'y a pas de libellé au début du bloc ou de la boucle. Cela génère une erreur :

BEGIN
  BREAK label_1;
END label_1;

Dans cet exemple, les instructions BREAK et CONTINUE ciblent la label_1: LOOP externe, plutôt que la boucle WHILE x < 1 DO interne :

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

Une instruction BREAK, LEAVE, CONTINUE ou ITERATE qui spécifie un libellé qui n'existe pas génère une erreur :

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

Vous pouvez quitter un bloc depuis la section du gestionnaire d'exceptions :

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

CONTINUE ne peut pas être utilisé avec un libellé de bloc. Cela génère une erreur :

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

Boucles

LOOP

Syntaxe

LOOP
  sql_statement_list
END LOOP;

Description

Exécute sql_statement_list jusqu'à ce qu'une instruction BREAK ou LEAVE quitte la boucle. sql_statement_list correspond à une liste de zéro, une ou plusieurs instructions SQL se terminant par un point-virgule.

LOOP ne peut pas être exécuté de manière dynamique en tant qu'élément imbriqué. Cela signifie que LOOP ne peut pas être imbriqué dans une instruction EXECUTE IMMEDIATE.

Vous pouvez utiliser un libellé avec cette instruction. Pour en savoir plus, consultez la section Libellés.

Exemples

L'exemple suivant déclare une variable x avec une valeur par défaut de 0. Il utilise ensuite l'instruction LOOP pour créer une boucle qui s'exécute jusqu'à ce que la variable x soit supérieure ou égale à 10. Après la sortie de la boucle, l'exemple génère la valeur de x.

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

Cet exemple génère ce qui suit :

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

REPEAT

Syntaxe

REPEAT
  sql_statement_list
  UNTIL boolean_condition
END REPEAT;

Description

Exécute de manière répétée une liste de zéro instructions SQL ou plus jusqu'à ce que la condition booléenne située à la fin de la liste corresponde à TRUE. La condition booléenne doit être une expression. Vous pouvez quitter cette boucle plus tôt avec l'instruction BREAK ou LEAVE.

REPEAT ne peut pas être exécuté de manière dynamique en tant qu'élément imbriqué. Cela signifie que REPEAT ne peut pas être imbriqué dans une instruction EXECUTE IMMEDIATE.

Vous pouvez utiliser un libellé avec cette instruction. Pour en savoir plus, consultez la section Libellés.

Exemples

L'exemple suivant déclare une variable x avec la valeur par défaut 0. Ensuite, il utilise l'instruction REPEAT pour créer une boucle qui s'exécute jusqu'à ce que la variable x soit supérieure ou égale à 3.

DECLARE x INT64 DEFAULT 0;

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

Cet exemple génère ce qui suit :

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

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

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

WHILE

Syntaxe

WHILE boolean_expression DO
  sql_statement_list
END WHILE;

Le niveau d'imbrication maximal est de 50 pour les blocs et les instructions conditionnelles telles que BEGIN/END, IF/ELSE/END IF et WHILE/END WHILE.

Description

Si boolean_expression a la valeur "true", exécute sql_statement_list. boolean_expression est évalué pour chaque itération de la boucle.

WHILE ne peut pas être exécuté de manière dynamique en tant qu'élément imbriqué. Cela signifie que WHILE ne peut pas être imbriqué dans une instruction EXECUTE IMMEDIATE.

Vous pouvez utiliser un libellé avec cette instruction. Pour en savoir plus, consultez la section Libellés.

BREAK

Syntaxe

BREAK;

Description

Quitte la boucle actuelle.

Vous ne pouvez pas utiliser BREAK en dehors d'une boucle.

Vous pouvez utiliser un libellé avec cette instruction. Pour en savoir plus, consultez la section Libellés.

Exemples

L'exemple suivant déclare deux variables, heads et heads_count. Ensuite, il démarre une boucle qui attribue une valeur booléenne aléatoire à heads, puis il vérifie si heads a la valeur "true". Si c'est le cas, il affiche "Heads!" (face) et incrémente heads_count. Si ce n'est pas le cas, il affiche "Tails!" (pile) et quitte la boucle. Enfin, il génère une chaîne indiquant le nombre de fois où le tirage à pile ou face a donné le résultat "heads".

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

Synonyme de BREAK.

CONTINUER

Syntaxe

CONTINUE;

Description

Ignore toutes les instructions suivantes dans la boucle actuelle et revient au début de la boucle.

Vous ne pouvez pas utiliser CONTINUE en dehors d'une boucle.

Vous pouvez utiliser un libellé avec cette instruction. Pour en savoir plus, consultez la section Libellés.

Exemples

L'exemple suivant déclare deux variables, heads et heads_count. Ensuite, il démarre une boucle qui attribue une valeur booléenne aléatoire à heads, puis il vérifie si heads a la valeur "true". Si c'est le cas, il affiche "Heads!" (face), incrémente heads_count et redémarre la boucle en ignorant les instructions restantes. Si ce n'est pas le cas, il affiche "Tails!" (pile) et quitte la boucle. Enfin, il génère une chaîne indiquant le nombre de fois où le tirage à pile ou face a donné le résultat "heads".

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

Synonyme de CONTINUE.

FOR...IN

Syntaxe

FOR loop_variable_name IN (table_expression)
DO
  sql_expression_list
END FOR;

Description

Boucle sur chaque ligne de table_expression et l'attribue à loop_variable_name. Dans chaque boucle, les instructions SQL de sql_expression_list sont exécutées à l'aide de la valeur actuelle de loop_variable_name.

La valeur de table_expression est évaluée une fois au début de la boucle. À chaque itération, la valeur de loop_variable_name est une STRUCT contenant les colonnes de premier niveau de l'expression de table en tant que champs. L'ordre dans lequel les valeurs sont attribuées à loop_variable_name n'est pas défini, sauf si l'expression de table comporte une clause ORDER BY de premier niveau ou un opérateur de tableau UNNEST.

Le champ d'application de loop_variable_name est le corps de la boucle. Le nom de loop_variable_name ne peut pas entrer en conflit avec d'autres variables du même champ d'application.

Vous pouvez utiliser un libellé avec cette instruction. Pour en savoir plus, consultez la section Libellés.

Exemple

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

Transactions

Bêta

Ce produit ou cette fonctionnalité est couvert par les Conditions des offres pré-DG des conditions d'utilisation de Google Cloud Platform. Les fonctionnalités et produits pré-DG sont susceptibles de présenter une compatibilité limitée, et les modifications apportées à ces fonctionnalités ou produits peuvent ne pas être compatibles avec d'autres versions pré-DG. Pour en savoir plus, consultez les descriptions des étapes de lancement.

BEGIN TRANSACTION

Syntaxe

BEGIN [TRANSACTION];

Description

Lance une transaction.

La transaction se termine lorsqu'une instruction COMMIT TRANSACTION ou ROLLBACK TRANSACTION est atteinte. Si l'exécution se termine avant d'atteindre l'une de ces instructions, un rollback automatique est effectué.

Pour en savoir plus sur les transactions dans BigQuery, consultez la section Transactions contenant plusieurs instructions.

Exemple

L'exemple suivant effectue une transaction qui sélectionne les lignes d'une table existante dans une table temporaire, supprime ces lignes de la table d'origine, puis fusionne la table temporaire dans une autre table.

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

Syntaxe

COMMIT [TRANSACTION];

Description

Effectue un commit d'une transaction ouverte. Si aucune transaction ouverte n'est en cours, l'instruction échoue.

Pour en savoir plus sur les transactions dans BigQuery, consultez la section Transactions contenant plusieurs instructions.

Exemple

BEGIN TRANSACTION;

-- SQL statements for the transaction go here.

COMMIT TRANSACTION;

ROLLBACK TRANSACTION

Syntaxe

ROLLBACK [TRANSACTION];

Description

Effectue un rollback d'une transaction ouverte. S'il n'existe aucune transaction ouverte en cours, l'instruction échoue.

Pour en savoir plus sur les transactions dans BigQuery, consultez la section Transactions contenant plusieurs instructions.

Exemple

L'exemple suivant effectue le rollback d'une transaction si une erreur se produit pendant celle-ci. Pour illustrer la logique, l'exemple déclenche une erreur de division par zéro après l'insertion d'une ligne dans une table. Une fois ces instructions exécutées, la table n'est pas affectée.

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

Syntaxe

RAISE [USING MESSAGE = message];

Description

Génère une erreur en utilisant éventuellement le message d'erreur spécifié lorsque USING MESSAGE = message est fourni.

Lorsque USING MESSAGE n'est pas fourni

L'instruction RAISE ne doit être utilisée que dans une clause EXCEPTION. L'instruction RAISE génère à nouveau l'exception qui a été interceptée et conserve la trace de la pile d'origine.

Lorsque USING MESSAGE est fourni

Si l'instruction RAISE est contenue dans la section BEGIN d'un bloc BEGIN...EXCEPTION :

• Le gestionnaire est appelé.

• La valeur de @@error.message correspond exactement à la chaîne message fournie (pouvant être NULL si message est NULL).

• La trace de la pile sera définie sur l'instruction RAISE.

Si l'instruction RAISE n'est pas contenue dans la section BEGIN d'un bloc BEGIN...EXCEPTION, l'instruction RAISE arrête l'exécution de la requête multi-instruction en spécifiant le message d'erreur.

RETURN

RETURN arrête l'exécution de la requête multi-instruction.

CALL

Syntaxe

CALL procedure_name (procedure_argument[, …])

Description

Appelle une procédure avec une liste d'arguments. procedure_argument peut être une variable ou une expression. Pour les arguments OUT ou INOUT, une variable transmise en tant qu'argument doit présenter le type approprié de langage SQL standard de Google.

La même variable peut ne pas apparaître plusieurs fois sous la forme d'un argument OUT ou INOUT dans la liste d'arguments de la procédure.

La profondeur maximale des appels de procédure est de 50 frames.

CALL ne peut pas être exécuté de manière dynamique en tant qu'élément imbriqué. Cela signifie que CALL ne peut pas être imbriqué dans une instruction EXECUTE IMMEDIATE.

Exemples

L'exemple suivant déclare une variable retCode. Ensuite, il appelle la procédure updateSomeTables dans l'ensemble de données myDataset, en transmettant les arguments 'someAccountId' et retCode. Enfin, il renvoie la valeur de retCode.

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