MySQL procédure stockée vs fonction, lequel utiliserais-je quand?

Question

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

Je me trompe probablement, mais il semble qu'une procédure stockée peut tout faire et davantage qu'une fonction stockée peut. Pourquoi / quand utiliserais-je une procédure vs une fonction?

Answer 1

La plus générale, la différence entre les procédures et les fonctions qu'ils sont appelés différemment et à des fins différentes:

1. Une procédure ne retourne pas de valeur. Au lieu de cela, elle est invoquée avec une instruction d'APPEL pour effectuer une opération telle que la modification d'une table ou de traitement de l'extrait des registres.
2. Une fonction est appelée dans une expression et renvoie une valeur unique directement à l'appelant à être utilisé dans l'expression.
3. Vous ne pouvez pas appeler une fonction avec une instruction d'APPEL, et vous ne pouvez pas appeler une procédure dans une expression.

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

1. Procédure de paramètres peuvent être définis comme entrée seule, sortie seule, ou les deux. Cela signifie qu'une procédure peut transmettre des valeurs à l'appelant en utilisant les paramètres de sortie. Ces valeurs peuvent être consultées dans les instructions qui suivent l'instruction d'APPEL. Les fonctions ont seulement des paramètres d'entrée. En conséquence, bien que les deux procédures et fonctions peuvent avoir des paramètres, des paramètres de la procédure de déclaration diffère de celle des fonctions.

2. Les fonctions renvoient une valeur, donc il doit y avoir une clause RETURNS dans une définition de fonction pour indiquer le type de données de la valeur de retour. Aussi, il doit y avoir au moins une instruction de RETOUR dans le corps de la fonction pour renvoyer une valeur à l'appelant. Les RETOURS et le RETOUR 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 à lui dans une expression. La fonction renvoie une valeur lors de l'évaluation de l'expression.

   • Une procédure est invoquée à l'aide d'une instruction d'APPEL, et ne peut transmettre des valeurs à l'aide des variables de sortie. Une fonction peut être appelée à partir de l'intérieur d'un énoncé, tout comme toute autre fonction (qui est, en invoquant le nom de la fonction), et peut retourner une valeur scalaire.

   • La spécification d'un paramètre IN, OUT ou INOUT est valable que pour une PROCÉDURE. Pour une FONCTION, les paramètres sont toujours considérés comme des paramètres.

   Si aucun mot-clé est donné devant un nom de paramètre, il est un paramètre par défaut. Paramètres pour les fonctions stockées ne sont pas précédés par IN, OUT ou INOUT. Tous les paramètres de la fonction sont traités comme des paramètres.

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

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 (pas de fonctions) est qu'une procédure peut générer un ensemble de résultats, ou même plusieurs ensembles de résultats, l'appelant processus de la même manière que le résultat d'une instruction SELECT. Cependant, le contenu de ces ensembles de résultats ne peuvent pas être utilisés directement dans l'expression.

Stockées routines sont associés à une base de données particulière, tout comme des tables ou des vues. Lorsque vous supprimez une base de données, toutes les routines stockées dans la base de données sont également supprimés.

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 avec le même nom dans une base de données.

Dans les procédures Stockées SQL dynamique peut être utilisé, mais pas dans des fonctions ou des déclencheurs.

SQL préparées (préparation, l'EXÉCUTION, DÉSALLOUER PRÉPARER) peuvent être utilisés dans des procédures stockées, mais pas stockées, des fonctions ou des déclencheurs. Ainsi, les fonctions stockées et les déclencheurs ne peuvent pas utiliser le SQL Dynamique (où vous construisez des déclarations comme des chaînes de caractères, puis les exécuter). (Dynamic SQL dans MySQL stockées routines)

Certaines plus intéressantes différences entre la FONCTION et la PROCÉDURE STOCKÉE:

1. Une procédure stockée est précompilé plan d'exécution où que les fonctions ne sont pas. Fonction Analysées et compilées lors de l'exécution. Stockées les procédures Stockées sous la forme d'un pseudo-code dans la base de données c'est à dire forme compilée.

2. (Je ne suis pas sûr pour ce point)
   Procédure stockée a la sécurité et réduit le réseau le trafic et l'on peut aussi appeler une procédure stockée dans aucune. de applications à la fois. référence

3. Les fonctions sont normalement utilisés pour les calculs où, comme les procédures sont normalement utilisés pour l'exécution de la logique métier.

4. Les fonctions Ne peuvent pas affecter l'état de la base de données (Déclaration, explicite ou implicite, commit ou rollback sont interdits dans la fonction) Alors que Les procédures stockées Peuvent affecter l'état de la base de données à l'aide de commettre etc.
   refrence: J. 1. Les Restrictions sur les Routines Stockées et les Déclencheurs

5. Les fonctions ne pouvez pas utiliser la CHASSE d' états alors que les procédures Stockées peuvent le faire.

6. Fonctions stockées ne peuvent pas être récursive, alors que les procédures Stockées peuvent être. Remarque: Récursive procédures stockées sont désactivés par défaut, mais peut être activé sur le serveur par le réglage de la max_sp_recursion_depth système de serveur de variable à une valeur différente de zéro. Voir la Section 5.2.3, "Variables Système", pour plus d'informations.

7. Au sein d'une fonction stockée ou d'un déclencheur, il n'est pas autorisé à modifier une table qui est déjà utilisé (pour la lecture ou l'écriture) par l'instruction qui a appelé la fonction ou le déclencheur. Bon Exemple: Comment mettre à Jour même table sur la suppression dans MYSQL?

Remarque: bien que certaines restrictions s'appliquent normalement à des fonctions stockées et les déclencheurs, mais pas à des procédures stockées, ces restrictions ne s'appliquent qu'aux procédures stockées, si elles sont appelées à partir d'une fonction stockée ou d'un déclencheur. Par exemple, bien que vous pouvez utiliser lors de la CHASSE dans une procédure stockée, une telle procédure stockée ne peut pas être appelée à partir d'une fonction stockée ou d'un déclencheur.

Answer 2

Vous ne pouvez pas mélanger dans les procédures stockées SQL ordinaire, alors qu’avec la fonction stockée, vous pouvez.

par exemple n’est pas valide si est une procédure, mais vous pouvez le faire si get_foo() est une fonction. Le prix est que les fonctions ont des limites plus qu’une procédure.

Answer 3

Une différence significative est que vous pouvez inclure une fonction dans vos requêtes SQL, mais les procédures stockées ne peut être invoquée avec le `` déclaration :

Exemple de l’UDF :

Exemple de procédure stockée :

Answer 4

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

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