Utiliser des procédures stockées SQL

Une procédure stockée est un ensemble d'instructions pouvant être appelées à partir d'autres requêtes ou d'autres procédures stockées. Une procédure peut utiliser des arguments d'entrée et renvoyer des valeurs en sortie. Vous nommez et stockez une procédure dans un ensemble de données BigQuery. Une procédure stockée peut accéder aux données ou les modifier sur plusieurs ensembles de données par plusieurs utilisateurs. Elle peut également contenir une requête à plusieurs instructions.

Certaines procédures stockées sont intégrées à BigQuery et n'ont pas besoin d'être créées. Ces procédures sont appelées procédures système. Pour en savoir plus à ce sujet, consultez la documentation de référence sur les procédures système.

Les procédures stockées sont compatibles avec les instructions de langages procéduraux, qui vous permettent, par exemple, de définir des variables et de mettre en œuvre un flux de contrôle. Pour en savoir plus sur les instructions de langages procéduraux, consultez la documentation de référence sur les langages procéduraux.

Créer une procédure stockée

Pour créer une procédure, utilisez l'instruction CREATE PROCEDURE.

Dans l'exemple conceptuel suivant, procedure_name représente la procédure, et le corps de celle-ci figure entre les instructions BEGIN et END :

CREATE PROCEDURE dataset_name.procedure_name()
BEGIN
-- statements here
END

L'exemple suivant montre une procédure contenant une requête à plusieurs instructions. La requête à plusieurs instructions définit une variable, exécute une instruction INSERT et affiche le résultat sous forme de chaîne de texte mise en forme.

CREATE OR REPLACE PROCEDURE mydataset.create_customer()
BEGIN
  DECLARE id STRING;
  SET id = GENERATE_UUID();
  INSERT INTO mydataset.customers (customer_id)
    VALUES(id);
  SELECT FORMAT("Created customer %s", id);
END

Dans l'exemple précédent, le nom de la procédure est mydataset.create_customer et son corps s'affiche entre les instructions BEGIN et END.

Pour appeler la procédure, utilisez l'instruction CALL :

CALL mydataset.create_customer();

Transmettre une valeur avec un paramètre d'entrée

Une procédure peut contenir des paramètres d'entrée. Un paramètre d'entrée permet de fournir une valeur d'entrée pour une procédure, mais pas de valeur de sortie.

CREATE OR REPLACE PROCEDURE mydataset.create_customer(name STRING)
BEGIN
DECLARE id STRING;
SET id = GENERATE_UUID();
INSERT INTO mydataset.customers (customer_id, name)
  VALUES(id, name);
SELECT FORMAT("Created customer %s (%s)", id, name);
END

Transmettre une valeur avec un paramètre de sortie

Une procédure peut inclure des paramètres de sortie. Un paramètre de sortie renvoie une valeur de la procédure, mais ne permet pas de fournir une valeur d'entrée pour la procédure. Pour créer un paramètre de sortie, utilisez le mot clé OUT avant le nom du paramètre.

Par exemple, cette version de la procédure renvoie le nouveau numéro client via le paramètre id :

CREATE OR REPLACE PROCEDURE mydataset.create_customer(name STRING, OUT id STRING)
BEGIN
SET id = GENERATE_UUID();
INSERT INTO mydataset.customers (customer_id, name)
  VALUES(id, name);
SELECT FORMAT("Created customer %s (%s)", id, name);
END

Pour appeler cette procédure, vous devez utiliser une variable pour recevoir la valeur de sortie :

--- Create a new customer record.
DECLARE id STRING;
CALL mydataset.create_customer("alice",id);

--- Display the record.
SELECT * FROM mydataset.customers
WHERE customer_id = id;

Transmettre et extraire une valeur avec un paramètre d'entrée/sortie

Une procédure peut également inclure des paramètres d'entrée/sortie. Un paramètre d'entrée/sortie renvoie une valeur associée à la procédure et accepte également une valeur d'entrée. Pour créer un paramètre d'entrée/sortie, utilisez le mot clé INOUT avant le nom du paramètre. Pour en savoir plus, consultez la section Mode des arguments.

Autoriser les routines

Vous pouvez autoriser les procédures stockées en tant que routines. Les routines autorisées vous permettent de partager des résultats de requête avec des utilisateurs ou des groupes spécifiques, sans leur donner accès aux tables sous-jacentes qui ont généré les résultats. Par exemple, une routine autorisée peut calculer une agrégation sur des données ou rechercher une valeur de table et utiliser cette valeur dans un calcul.

Les routines autorisées peuvent créer, supprimer et manipuler des tables, ainsi qu'appeler d'autres procédures stockées sur la table sous-jacente.

Pour en savoir plus, consultez la section Routines autorisées.

Appeler une procédure stockée

Pour appeler une procédure stockée après sa création, utilisez l'instruction CALL. Par exemple, l'instruction suivante appelle la procédure stockée create_customer :

CALL mydataset.create_customer();

Remarque : Appeler une procédure stockée au lieu d'inclure les instructions SQL de la procédure directement dans votre requête entraîne une légère incidence sur les performances.

Appeler une procédure système

Pour appeler une procédure système intégrée, utilisez l'instruction CALL. Par exemple, l'instruction suivante appelle la procédure système BQ.REFRESH_MATERIALIZED_VIEW :

CALL BQ.REFRESH_MATERIALIZED_VIEW;