Vue d’ensemble des procédures stockées

Les procédures stockées sont plus ou moins similaires aux fonctions. Comme pour les fonctions, une procédure stockée est créée une fois et peut être exécutée plusieurs fois. Une procédure stockée est créée avec une commande CREATE PROCEDURE et est exécutée avec une commande CALL.

Une procédure stockée renvoie une valeur unique. Bien que vous puissiez exécuter des instructions SELECT à l’intérieur d’une procédure stockée, les résultats doivent être utilisés dans la procédure stockée ou être réduits à une valeur unique à renvoyer.

Les procédures stockées Snowflake utilisent JavaScript et, dans la plupart des cas, SQL :

  • JavaScript fournit les structures de contrôle (branchement et bouclage).

  • SQL est exécuté en appelant des fonctions dans une API JavaScript.

Dans ce chapitre :

Avantages des procédures stockées

Les procédures stockées permettent :

  • Logique procédurale (branchement et boucle), que SQL simple ne prend pas en charge.

  • Traitement des erreurs.

  • Création dynamique d’une instruction SQL et exécution.

  • Écriture de code qui s’exécute avec les privilèges du rôle propriétaire de la procédure plutôt qu’avec les privilèges du rôle qui exécute la procédure. Cela permet au propriétaire de la procédure stockée de déléguer le pouvoir d’effectuer des opérations spécifiées à des utilisateurs qui, autrement, ne pourraient pas le faire. Cependant, il existe des limitations à ces procédures stockées avec droits du propriétaire.

Une utilisation courante des procédures stockées consiste à automatiser une tâche nécessitant plusieurs instructions SQL et exécutée fréquemment. Par exemple, supposons que vous souhaitiez « nettoyer » une base de données en supprimant des données antérieures à une date spécifiée. Vous pouvez écrire plusieurs instructions DELETE, chacune d’elles supprimant les données d’une table spécifique. Vous pouvez placer toutes ces instructions dans une seule procédure stockée et transmettre un paramètre spécifiant la date limite. Ensuite, vous pouvez simplement appeler la procédure pour nettoyer la base de données. Lorsque votre base de données change, vous pouvez mettre à jour la procédure pour nettoyer les tables supplémentaires. Si plusieurs utilisateurs utilisent la commande de nettoyage, ils peuvent appeler une seule procédure plutôt que de se souvenir de chaque nom de table et de nettoyer chaque table individuellement.

Différences entre les procédures stockées et les UDFs

Les procédures stockées et les UDFs (fonctions définies par l’utilisateur) facilitent l’écriture de code modulaire. Cependant, il existe des différences importantes entre les UDFs et les procédures stockées.

Les procédures stockées sont appelées en tant qu’instructions indépendantes

Une procédure stockée est appelée en tant qu’instruction indépendante plutôt qu’en tant qu’élément d’une instruction. Le code ci-dessous illustre la différence entre appeler une procédure stockée et appeler une fonction :

CALL MyStoredProcedure1(argument_1);             -- Stored Procedure call

SELECT MyFunction1(column_1) FROM table1;        -- Function call

Le renvoi d’une valeur d’une procédure stockée est facultatif

Une procédure stockée est autorisée à renvoyer une valeur, mais n’est pas obligée de renvoyer une valeur.

Par contre, une fonction est obligée de renvoyer une valeur.

Les valeurs renvoyées par les procédures stockées ne sont pas directement utilisables dans SQL

La valeur renvoyée par une procédure stockée, contrairement à la valeur retournée par une fonction, ne peut pas être utilisée directement dans SQL.

Bien qu’une procédure stockée puisse renvoyer une valeur, la syntaxe de la commande CALL ne fournit aucun emplacement pour stocker la valeur renvoyée ni un moyen de l’exploiter ou de transmettre la valeur à une autre opération. SQL ne dispose d’aucun moyen de construire une instruction du type suivant :

y = stored_procedure1(x);                         -- Not allowed.

Cependant, il existe des moyens indirects d’utiliser la valeur de retour d’une procédure stockée :

  • Vous pouvez appeler la procédure stockée à l’intérieur d’une autre procédure stockée. Le JavaScript dans la procédure stockée externe peut extraire et stocker la sortie de la procédure stockée interne. N’oubliez pas, cependant, que la procédure stockée externe (et chaque procédure stockée interne) est toujours incapable de renvoyer plus d’une valeur à son appelant.

  • Vous pouvez appeler la procédure stockée, puis la fonction RESULT_SCAN et lui transmettre l’ID d’instruction généré pour la procédure stockée.

  • Vous pouvez stocker un jeu de résultats dans une table temporaire ou permanente, et utiliser cette table après un renvoi depuis l’appel de la procédure stockée.

  • Si le volume des données n’est pas trop volumineux, vous pouvez stocker plusieurs lignes et plusieurs colonnes dans une VARIANT (par exemple, sous la forme d’une valeur JSON) et renvoyer cette VARIANT.

Procédure stockée unique par instruction CALL

Une seule instruction exécutable ne peut appeler qu’une seule procédure stockée. En revanche, une seule instruction SQL peut appeler plusieurs fonctions.

De même, une procédure stockée, contrairement à une fonction, ne peut pas être appelée dans le cadre d’une expression.

Toutefois, dans une procédure stockée, la procédure stockée peut appeler une autre procédure stockée ou s’appeler elle-même de manière récursive. Un exemple de ceci est montré dans la section des exemples de code Exemples.

Pour plus d’informations sur l’appel d’une procédure stockée, voir Appel d’une procédure stockée.

Les procédures stockées peuvent accéder à la base de données et émettre des requêtes imbriquées via une API

Snowflake fournit une API JavaScript (sous la forme d’objets et de méthodes JavaScript). L’API permet aux procédures stockées d’exécuter des opérations de base de données telles que SELECT, UPDATE et CREATE.

L’exemple ci-dessous montre comment une procédure stockée peut créer et exécuter une instruction SQL qui appelle une autre procédure stockée. Le $$ indique le début et la fin du code JavaScript dans la procédure stockée.

create procedure ...
  $$
  // Create a Statement object that can call a stored procedure named
  // MY_PROCEDURE().
  var stmt1 = snowflake.createStatement( { sqlText: "call MY_PROCEDURE(22)" } );
  // Execute the SQL command; in other words, call MY_PROCEDURE(22).
  stmt1.execute();
  // Create a Statement object that executes a SQL command that includes
  // a call to a UDF.
  var stmt2 = snowflake.createStatement( { sqlText: "select MY_UDF(column1) from table1" } );
  // Execute the SQL statement and store the output (the "result set") in
  // a variable named "rs", which we can access later.
  var rs = stmt2.execute();
  // etc.
  $$;

Contrairement aux procédures stockées, les UDFs (y compris les UDFs JavaScript) n’ont pas accès à une API pouvant effectuer des opérations de base de données.

Choisir de créer une procédure stockée ou une UDF

En général, lorsque vous décidez de créer une procédure stockée ou une UDF :

Créer une procédure stockée
  • Si vous migrez une procédure stockée existante à partir d’une autre application/système.

  • Si vous devez effectuer des opérations sur la base de données :

    • Requêtes typiques et DML, telles que SELECT, UPDATE, etc.

    • Les tâches administratives, notamment DDL, telles que la suppression de tables temporaires, la suppression de données antérieures à N jours ou l’ajout d’utilisateurs.

Créer une UDF
  • Si vous migrez une UDF existante à partir d’une autre application/système.

  • Si vous avez besoin d’une fonction qui puisse être appelée dans le cadre d’une instruction SQL et qui doit renvoyer une valeur qui sera utilisée dans l’instruction.

  • Si votre sortie doit inclure une valeur pour chaque ligne d’entrée ou chaque groupe. Par exemple :

    select MyFunction(col1) from table1;
    
    select MyAggregateFunction(col1) from table1 group by col2;
    

Remarques relatives à la migration

De nombreux systèmes de base de données implémentent des procédures stockées uniquement en SQL. Pour migrer une procédure stockée SQL vers Snowflake, intégrez le SQL dans JavaScript, comme indiqué dans les exemples de la rubrique Travailler avec des procédures stockées .