Choisir d’écrire une procédure stockée ou une fonction définie par l’utilisateur¶

UDFs renvoient une valeur ; les procédures stockées n’en ont pas besoin¶

Les procédures stockées et les fonctions peuvent toutes deux renvoyer une valeur. Cependant :

• Le corps d’une procédure stockée peut, mais ne doit pas, renvoyer explicitement une valeur (par exemple, un indicateur d’erreur). C’est parce que le but d’une procédure stockée est généralement d’effectuer des opérations administratives en exécutant des instructions SQL.

  Notez que chaque instruction CREATE PROCEDURE doit inclure une clause RETURNS qui spécifie un type de retour, même si la procédure ne retourne rien explicitement. Si une procédure ne renvoie pas explicitement une valeur, elle renvoie implicitement NULL.

  Le code de l’exemple suivant déclare un type de retour pour la procédure avec une clause RETURNS mais une valeur n’est renvoyée qu’en cas d’erreur. En d’autres termes, tous les chemins de code ne renvoient pas une valeur.

  create or replace procedure do_stuff(input number)
  returns varchar
  language sql
  as
  $$
  declare
    error varchar default 'Bad input. Number must be less than 10.';
  
  begin
    if (input > 10) then
      return error;
    end if;
  
    -- Perform an operation that doesn't return a value.
  
  end;
  $$
  ;
  

  Copy

  Si une procédure ne renvoie pas explicitement une valeur, elle renvoie implicitement NULL.

• Une UDF renvoie toujours une valeur de manière explicite en spécifiant une expression. En effet, le but d’une UDF est de calculer et de renvoyer une valeur. Par exemple, le corps d’une UDF JavaScript doit contenir une instruction return qui renvoie une valeur.

Les valeurs de retour des UDF peuvent ne pas être directement utilisables en SQL ; les valeurs de retour des procédures stockées ne peuvent pas être¶

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 la plupart des langages de gestion) 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.

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. Par exemple, lorsque le gestionnaire de la procédure stockée est écrit en JavaScript, le JavaScript de la procédure stockée externe peut récupérer 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.

Les UDFs peuvent être appelées dans le contexte d’une autre instruction ; les procédures stockées sont appelées de manière indépendante¶

Une procédure stockée n’est pas évaluée à une valeur, et ne peut pas être utilisée dans tous les contextes dans lesquels une expression générale peut être utilisée. Par exemple, vous ne pouvez pas exclure SELECT my_stored_procedure()....

Une UDF évalue une valeur et peut être utilisée dans des contextes dans lesquels une expression générale peut être utilisée (par exemple, SELECT my_function() ...).

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

CALL MyStoredProcedure_1(argument_1);

SELECT MyFunction_1(column_1) FROM table1;

Pour plus de détails sur l’appel de fonctions et de procédures, voir ce qui suit :

• Appel d’une procédure stockée

• Appel d’une UDF

Plusieurs UDFs peuvent être appelées par une instruction unique ; une seule procédure stockée est appelée par une instruction unique¶

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 de détails sur l’appel de fonctions et de procédures, voir ce qui suit :

• Appel d’une procédure stockée

• Appel d’une UDF

Les UDFs peuvent accéder à la base de données ; les procédures stockées peuvent¶

Dans une procédure stockée, vous pouvez exécuter des opérations de base de données, telles que SELECT, UPDATE et CREATE :

• Par exemple, dans une procédure stockée JavaScript, vous pouvez utiliser l’API JavaScript pour effectuer ces opérations.

  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 de gestion 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.
    $$;
  

  Copy

• Dans une procédure stockée Écriture de scripts Snowflake, vous pouvez exécuter des instructions SQL.

  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 Exécution de scripts Snowflake dans la procédure stockée.

  CREATE PROCEDURE ...
    -- Call a stored procedure named my_procedure().
    CALL my_procedure(22);
    -- Execute a SQL statement that includes a call to a UDF.
    SELECT my_udf(column1) FROM table1;
  

  Copy

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