MySQL procédure stockée vs fonction, Que dois-je utiliser quand?

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 invoqué 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 une valeur unique directement à l'appelant d'être utilisé dans l'expression.
3. vous ne pouvez pas invoquer une fonction avec une instruction CALL, ni 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 paramètres de la procédure peuvent être définis comme des paramètres d'entrée seulement, de sortie seulement, 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'énoncé de L'appel. Les fonctions n'ont que des paramètres d'entrée. En conséquence, bien que les procédures et les fonctions puissent avoir des paramètres, la déclaration des paramètres de procédure diffère de celle des fonctions.

2. fonctions valeur de retour, il doit donc y avoir une clause de retour dans une définition de fonction pour indiquer le type de données de la valeur de retour. En outre, il doit y avoir au moins une déclaration de retour dans le corps de fonction pour renvoyer une valeur à l'appelant. Les retours et les retours n'apparaissent pas dans les définitions des procédures.

   • pour invoquer un procédé stocké, utilisez le CALL statement . Pour invoquer une fonction stockée, référez-vous à elle dans une expression. La fonction renvoie une valeur lors de l'évaluation de l'expression.

   • une procédure est invoquée en utilisant une instruction D'appel, et ne peut transmettre des valeurs de retour en utilisant des variables de sortie. Une fonction peut être appelée de à l'intérieur d'une instruction comme n'importe quelle autre fonction (c'est-à-dire en invoquant le nom de la fonction), et peut retourner une valeur scalaire.

   • spécifier un paramètre comme IN, OUT, ou INOUT n'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é n'est donné avant le nom d'un paramètre, c'est un paramètre IN par défaut. paramètres pour stocké les fonctions ne sont pas précédées par IN, OUT, ou INOUT. tous les paramètres de fonction sont traités comme dans les paramètres.

pour définir une procédure ou une fonction stockée, utiliser créer une procédure ou créer une fonction 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 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, que l'appelant traite le de la même manière que le résultat D'une instruction SELECT. Toutefois, le contenu de ces ensembles de résultats ne peut pas être utilisé directement dans l'expression.

routines stockées (se référant à la fois aux procédures stockées et aux fonctions stockées) sont associés à une base de données particulière, tout comme les tables ou les vues. lorsque vous supprimez une base de données, toutes les routines stockées dans la base de données sont également supprimées.

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, le SQL dynamique peut être utilisé, mais pas dans les fonctions ou les déclencheurs.

SQL prepared statements (PREPARE, EXECUTE, DEALLOCATE PREPARE) peut être utilisé dans les procédures stockées, mais pas les fonctions ou les déclencheurs stockés. Ainsi, les fonctions stockées et les déclencheurs ne peuvent pas utilisez le SQL dynamique (où vous construisez des instructions en tant que chaînes et les exécutez ensuite). (Dynamic SQL dans MySQL stockées routines)

quelques différences plus intéressantes entre la fonction et la procédure stockée:

1. ( ce point est copié d'un blogpost . ) La procédure stockée est un plan d'exécution précompilé où les fonctions ne sont pas. Fonction analysée et compilée à l'exécution. Procédures stockées, stockées sous forme de pseudo-code dans la base de données, c'est-à-dire sous forme compilée.

2. ( Je n'en suis pas sûr. )
   
   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ées pour les les procédures sont normalement utilisées pour exécuter la logique opérationnelle.

4. les fonctions ne peuvent pas affecter l'état de la base de données (Les énoncés qui font un commit ou un rollback explicite ou implicite sont rejetés en fonction) Alors Les procédures stockées peuvent affecter l'état de la base de données en utilisant commit, etc.
   
   réference: J. 1. Restrictions sur les Routines et les déclencheurs stockés

5. les fonctions ne peuvent pas utiliser les instructions FLUSH alors que les procédures stockées peuvent le faire.

6. les fonctions stockées ne peuvent pas être récursives alors que les procédures stockées peuvent l'être. Note: les procédures stockées récursives sont désactivées par défaut, mais peuvent être activées sur le serveur en mettant la variable système du serveur max_sp_recursion_depth à une valeur non nulle. Voir Section 5.2.3, "Variables système" , pour plus d'informations.

7. dans le cadre d'une fonction stockée ou d'un déclencheur, il n'est pas permis de modifier une table déjà utilisée (pour lire ou écrire) par l'énoncé qui a invoqué la fonction ou le déclencheur. Bon exemple: comment mettre à jour la même table sur la suppression dans MYSQL?

Note : bien que certaines restrictions ces restrictions s'appliquent normalement aux fonctions et déclencheurs stockés, mais non aux procédures stockées, si elles sont invoquées à partir d'une fonction ou d'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 stockée ou d'un déclencheur.

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

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 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 alors l'appliquer à chaque rangée, ou dans une clause où.

une procédure est exécutée en utilisant la requête D'appel.

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