Procédure stockée MySQL vs fonction, qui utiliserais-je quand?

Je regarde les procédures stockées et la fonction MySQL. Quelle est la vraie différence?

Ils semblent être similaires, mais une fonction a plus de limitations.

Je me trompe probablement, mais il semble qu’une procédure stockée puisse tout faire et qu’une fonction stockée peut l’être. Pourquoi / quand utiliser une procédure ou une fonction?

    Vous ne pouvez pas mélanger des procédures stockées avec du SQL ordinaire, tout en conservant la fonction stockée.

    Par exemple, SELECT get_foo(myColumn) FROM mytable n’est pas valide si get_foo() est une procédure, mais vous pouvez le faire si get_foo() est une fonction. Le prix est que les fonctions ont plus de limitations qu’une procédure.

    La différence la plus générale entre les procédures et les fonctions est qu’elles sont invoquées différemment et à des fins différentes:

    1. Une procédure ne renvoie pas de valeur. Au lieu de cela, il est appelé avec une instruction CALL pour effectuer une opération telle que la modification d’une table ou le traitement des enregistrements récupérés.
    2. Une fonction est appelée dans une expression et renvoie directement une valeur unique à l’appelant à utiliser dans l’expression.
    3. Vous ne pouvez pas appeler une fonction avec une instruction CALL, ni appeler une procédure dans une expression.

    La syntaxe pour la création de routine diffère quelque peu pour les procédures et les fonctions:

    1. Les parameters de procédure peuvent être définis comme entrée uniquement, sortie uniquement ou les deux. Cela signifie qu’une procédure peut renvoyer des valeurs à l’appelant en utilisant des parameters de sortie. Ces valeurs sont accessibles dans les instructions qui suivent l’instruction CALL. Les fonctions n’ont que des parameters d’entrée. Par conséquent, bien que les procédures et les fonctions puissent avoir des parameters, la déclaration de paramètre de procédure diffère de celle des fonctions.
    2. Les fonctions renvoient une valeur, il doit donc y avoir une clause RETURNS dans une définition de fonction pour indiquer le type de données de la valeur renvoyée. De plus, le corps de la fonction doit comporter au moins une instruction RETURN pour renvoyer une valeur à l’appelant. RETURNS et RETURN n’apparaissent pas dans les définitions de procédure.

      • Pour appeler une procédure stockée, utilisez l’ CALL statement . Pour appeler une fonction stockée, reportez-vous à celle-ci dans une expression. La fonction renvoie une valeur lors de l’évaluation de l’expression.

      • Une procédure est appelée à l’aide d’une instruction CALL et ne peut que renvoyer des valeurs à l’aide de variables de sortie. Une fonction peut être appelée depuis une instruction comme toute autre fonction (c’est-à-dire en invoquant le nom de la fonction) et peut renvoyer une valeur scalaire.

      • La spécification d’un paramètre comme IN, OUT ou INOUT n’est valide que pour une procédure. Pour une FUNCTION, les parameters sont toujours considérés comme des parameters IN.

      Si aucun mot-clé n’est donné avant un nom de paramètre, il s’agit d’un paramètre IN par défaut. Les parameters des fonctions stockées ne sont pas précédés de IN, OUT ou INOUT. Tous les parameters de fonction sont traités comme parameters IN.

    Pour définir une procédure stockée ou une fonction, utilisez respectivement CREATE PROCEDURE ou CREATE FUNCTION:

     CREATE PROCEDURE proc_name ([parameters]) [characteristics] routine_body CREATE FUNCTION func_name ([parameters]) RETURNS data_type // diffrent [characteristics] routine_body 

    Une extension MySQL pour une procédure stockée (et non des fonctions) est qu’une procédure peut générer un ensemble de résultats, voire plusieurs jeux de résultats, que l’appelant traite de la même manière que le résultat d’une instruction SELECT. Cependant, le contenu de ces jeux de résultats ne peut pas être utilisé directement dans expression.

    Les routines stockées (faisant référence aux procédures stockées et aux fonctions stockées) sont associées à une firebase database particulière, tout comme les tables ou les vues. Lorsque vous déposez une firebase database, toutes les routines stockées dans la firebase database sont également supprimées.

    Les procédures stockées et les fonctions ne partagent pas le même espace de noms. Il est possible d’avoir une procédure et une fonction du même nom dans une firebase database.

    Dans les procédures stockées, le SQL dynamic peut être utilisé mais pas dans les fonctions ou les déclencheurs.

    Les instructions SQL préparées (PREPARE, EXECUTE, DEALLOCATE PREPARE) peuvent être utilisées dans des procédures stockées, mais pas dans des fonctions ou des déclencheurs stockés. Ainsi, les fonctions stockées et les déclencheurs ne peuvent pas utiliser le SQL dynamic (où vous construisez des instructions sous forme de chaînes, puis les exécutez). (SQL dynamic dans les routines stockées MySQL)

    Quelques différences plus intéressantes entre les procédures FONCTION et STOCKÉ:

    1. ( Ce point est copié depuis un article de blog . ) La procédure stockée est un plan d’exécution précompilé où les fonctions ne le sont pas. Fonction analysée et compilée à l’exécution. Procédures stockées, stockées sous forme de pseudo-code dans la firebase database, c’est-à-dire sous forme compilée.

    2. ( Je ne suis pas sûr de ce point. )
      La procédure stockée a la sécurité et réduit le trafic réseau et nous pouvons également appeler la procédure stockée dans n’importe quel no. des applications à la fois. référence

    3. Les fonctions sont normalement utilisées pour les calculs où les procédures sont normalement utilisées pour exécuter une logique métier.

    4. Fonctions Impossible d’affecter l’état de la firebase database (les instructions qui effectuent une validation ou une annulation explicite ou implicite sont interdites dans la fonction), alors que les procédures stockées peuvent affecter l’état de la firebase database à l’aide de la validation, etc.
      Fréquence: J.1. Ressortingctions sur les routines et les déclencheurs stockés

    5. Les fonctions ne peuvent pas utiliser les instructions FLUSH , contrairement aux procédures stockées.

    6. Les fonctions stockées ne peuvent pas être récursives, alors que les procédures stockées peuvent l’être. Remarque: Les procédures stockées récursives sont désactivées par défaut, mais elles peuvent être activées sur le serveur en définissant la variable système max_sp_recursion_depth du serveur sur une valeur différente de zéro. Voir Section 5.2.3, «Variables système» , pour plus d’informations.

    7. Dans une fonction ou un déclencheur stocké, il n’est pas permis de modifier une table déjà utilisée (pour la lecture ou l’écriture) par l’instruction qui a appelé la fonction ou le déclencheur. Bon exemple: Comment mettre à jour la même table lors de la suppression dans MYSQL?

    Remarque : bien que certaines ressortingctions s’appliquent normalement aux fonctions stockées et aux déclencheurs mais pas aux procédures stockées, ces ressortingctions s’appliquent aux procédures stockées si elles sont appelées depuis une fonction ou un déclencheur stocké. Par exemple, bien que vous puissiez utiliser FLUSH dans une procédure stockée, une telle procédure stockée ne peut pas être appelée à partir d’une fonction ou d’un déclencheur stocké.

    Une différence importante est que vous pouvez inclure une fonction dans vos requêtes SQL, mais les procédures stockées ne peuvent être appelées qu’avec l’instruction CALL :

    Exemple UDF:

     CREATE FUNCTION hello (s CHAR(20)) RETURNS CHAR(50) DETERMINISTIC RETURN CONCAT('Hello, ',s,'!'); Query OK, 0 rows affected (0.00 sec) CREATE TABLE names (id int, name varchar(20)); INSERT INTO names VALUES (1, 'Bob'); INSERT INTO names VALUES (2, 'John'); INSERT INTO names VALUES (3, 'Paul'); SELECT hello(name) FROM names; +--------------+ | hello(name) | +--------------+ | Hello, Bob! | | Hello, John! | | Hello, Paul! | +--------------+ 3 rows in set (0.00 sec) 

    Exemple de Sproc:

     delimiter // CREATE PROCEDURE simpleproc (IN s CHAR(100)) BEGIN SELECT CONCAT('Hello, ', s, '!'); END// Query OK, 0 rows affected (0.00 sec) delimiter ; CALL simpleproc('World'); +---------------------------+ | CONCAT('Hello, ', s, '!') | +---------------------------+ | Hello, World! | +---------------------------+ 1 row in set (0.00 sec) 

    Une fonction stockée peut être utilisée dans une requête. Vous pouvez ensuite l’appliquer à chaque ligne ou dans une clause WHERE.

    Une procédure est exécutée à l’aide de la requête CALL.

    Procédure stockée peut être appelée récursivement, mais la fonction stockée ne peut pas