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

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

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

• Une procédure stockée peut, mais ne doit pas, renvoyer explicitement une valeur (par exemple, un indicateur d’erreur). Le but d’une procédure stockée est généralement d’effectuer des opérations administratives en exécutant des instructions SQL. Si une procédure ne renvoie pas explicitement une valeur, elle renvoie implicitement NULL.

  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

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

Si vous n’appelez pas la procédure stockée à partir d’un bloc Exécution de scripts Snowflake, vous ne pouvez pas utiliser la valeur renvoyée par une procédure stockée directement dans SQL (contrairement à la valeur renvoyée par une fonction). La syntaxe de la commande CALL ne fournit aucun emplacement pour stocker la valeur renvoyée ni de moyen de l’exploiter ou de transmettre la valeur à une autre opération. En d’autres termes, l’instruction suivante n’est pas une instruction SQL valide :

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

Si vous appelez une procédure stockée dans un bloc Exécution de scripts Snowflake, vous pouvez capturer la valeur retournée par la procédure stockée dans une variable Exécution de scripts Snowflake.

Vous pouvez également utiliser indirectement la valeur de retour d’une procédure stockée (en dehors d’un bloc Exécution de scripts Snowflake), comme décrit dans la liste suivante :

• 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 une procédure stockée qui renvoie des données tabulaires dans la clause FROM d’une instruction SELECT.

• 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 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 MyFunction_1(column_1) FROM table1;
  

  Copy

• 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()....

  Vous appelez une procédure stockée en tant qu’instruction indépendante, comme dans l’exemple suivant :

  CALL MyStoredProcedure_1(argument_1);
  

  Copy

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 SQL peut appeler plusieurs UDFs.

• Une seule instruction SQL ne peut appeler qu’une seule procédure stockée.

  De même, une procédure stockée, contrairement à une UDF, 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. Par exemple, voir 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 ne peuvent accéder à la base de données qu’avec des requêtes simples ; les procédures stockées peuvent exécuter des instructions DDL et DML.¶

• Dans une UDF, vous pouvez utiliser SQL pour exécuter des requêtes uniquement (et non des instructions DML ou DDL).

• 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