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 définit 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.

• 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 procédures stockées peuvent ne pas être directement utilisables dans SQL ; Les valeurs de retour des UDF sont¶

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 procédures stockées sont appelées indépendamment ; les UDFs peuvent être appelées dans le contexte d’une autre instruction¶

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;

Une seule procédure stockée est appelée par une instruction unique ; plusieurs UDFs peuvent être appelées 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 indiqué dans la section des exemples de procédures stockées.

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

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

• Dans une procédure stockée Guide du développeur Exécution 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;
  

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.