fonction stockée de procédure stockée mysql

MySQL est un système de gestion de base de données relationnelle populaire qui prend en charge la création et l'utilisation d'objets de base de données tels que des procédures stockées et des fonctions stockées. Dans cet article, nous plongerons dans les concepts, les utilisations, la syntaxe et les exemples de procédures stockées et de fonctions stockées MySQL.

1. Que sont les procédures stockées et les fonctions stockées ?

Les procédures stockées et les fonctions stockées sont deux types d'objets de base de données dans MySQL. Ils peuvent tous être créés côté serveur MySQL et peuvent être appelés et exécutés par d'autres programmes clients. Ce qu'ils ont en commun, c'est qu'ils peuvent encapsuler une série d'instructions SQL et implémenter des opérations de base de données complexes ou une logique métier.

Une procédure stockée est une collection d'instructions SQL prédéfinies, similaires aux sous-programmes ou fonctions d'autres langages de programmation. Ils sont écrits sous la forme d'une seule instruction SQL et stockés sur le serveur et peuvent être appelés en cas de besoin. Les procédures stockées peuvent être utilisées pour mettre en œuvre certaines exigences métier spécifiques, telles que le traitement par lots de données, l'exécution de transactions, une requête rapide, etc. Les procédures stockées peuvent accepter des paramètres pendant l'exécution et peuvent également renvoyer des résultats après l'exécution.

Une fonction stockée est une procédure qui peut être appelée dans une requête SQL, qui accepte les paramètres d'entrée et renvoie une valeur unique. Semblables aux procédures stockées, les fonctions stockées sont également un ensemble d'instructions SQL. Les fonctions stockées sont généralement utilisées pour calculer, convertir ou manipuler des valeurs et renvoyer les résultats à l'appelant. Contrairement aux procédures stockées, les fonctions stockées ne peuvent renvoyer qu'une seule valeur et ne peuvent pas renvoyer un ensemble de résultats. Les fonctions stockées sont largement utilisées dans les requêtes et les rapports.

2. Avantages des procédures stockées et des fonctions stockées

1. Améliorer les performances de la base de données : les procédures stockées et les fonctions stockées peuvent être exécutées sur le serveur, réduisant ainsi la charge de transmission des données et la surcharge de communication réseau, améliorant ainsi les performances de la base de données.
2. Meilleure sécurité : les procédures stockées et les fonctions stockées peuvent s'appuyer sur le contrôle d'accès pour contrôler l'accès aux tables. Les codes SQL étant stockés sur le serveur de base de données, les utilisateurs ne peuvent pas y accéder directement et ne peuvent les utiliser qu'avec des autorisations spécifiques.
3. Code simplifié : les procédures stockées et les fonctions stockées peuvent réutiliser le code SQL, réduisant ainsi les requêtes SQL répétées et l'écriture de code logique, réduisant ainsi la quantité de code et le rendant plus maintenable.
4. Portabilité : les procédures stockées et les fonctions stockées peuvent être réutilisées dans différentes applications et sont portables sur différents systèmes d'exploitation et plates-formes de bases de données.

3. Créer et utiliser des procédures stockées

1. Créer des procédures stockées :

La syntaxe de création de procédures stockées est la suivante :

CREATE PROCEDURE procedure_name ([IN | OUT | INOUT] parameter datatype [, …])
BEGIN
SQL statements;
END;

• procedure_name : le nom de la procédure stockée
• parameter : le paramètre du procédure stockée, il peut s'agir de paramètres d'entrée IN (par défaut), de paramètres de sortie OUT et de paramètres INOUT (entrée et sortie simultanées).
• datatype : Le type de données du paramètre
• Relevés SQL : L'instruction du corps de la procédure stockée

Par exemple, la procédure stockée suivante transmettra le montant et l'identifiant client, et ajoutera le montant au solde du compte client :

CREATE PROCEDURE add_amount(IN amt INT, IN cust_id INT)
BEGIN
UPDATE customers SET balance = balance + amt WHERE id = cust_id;
END;

2. Appeler une procédure stockée :

La syntaxe pour appeler une procédure stockée est la suivante :

CALL procedure_name([parameter_value]);

Par exemple, le code suivant appellera la procédure stockée add_amount() qui vient d'être créée :

CALL add_amount(100, 1);

Le code ci-dessus augmentez le solde du client avec l'ID 1 de 100 dans la table des clients Yuan.

4. Créer et utiliser des fonctions stockées

1. Créer une fonction stockée :

La syntaxe pour créer une fonction stockée est la suivante :

CREATE FUNCTION function_name ([parameter datatype [, …]])
RETURNS datatype
BEGIN
SQL statements;
RETURN return_value;
END;

• function_name : le nom de la fonction stockée
• parameter : les paramètres de la fonction stockée
• datatype : le paramètre Type de données
• Instructions SQL : L'instruction corps de la fonction stockée
• return_value : La valeur de retour de la fonction stockée

Par exemple, l'exemple suivant crée une fonction stockée qui calcule un moyenne :

CREATE FUNCTION average (a INT, b INT)
RETURNS INT
BEGIN
DECLARE result INT;
SET result = (a + b) / 2;
RETURN result;
END;

2. Appelez la fonction stockée :

La syntaxe pour appeler une fonction stockée est similaire à l'appel d'une procédure stockée :

SELECT function_name([parameter_value]);

Par exemple, le code suivant appellera la fonction stockée moyenne() qui vient d'être créée :

SELECT average(10, 20);

Le code ci-dessus renverra 15, qui est le résultat de (10+20)/2.

5. Exemples de procédures stockées et de fonctions stockées

1. Exemples de procédures stockées

La procédure stockée suivante calculera automatiquement l'âge moyen de tous les clients et placera les résultats dans un autre tableau :

CREATE PROCEDURE calc_avg_age()
BEGIN
DECLARE avg_age FLOAT;
SELECT AVG(YEAR(CURDATE()) - YEAR(birth_date)) INTO avg_age FROM customers;
INSERT INTO statistics (name, value) VALUES ('avg_age', avg_age);
END;

2. Exemple de fonction stockée

La fonction stockée suivante renverra le nombre de jours entre deux dates :

CREATE FUNCTION days_between (date1 DATE, date2 DATE)
RETURNS INT
BEGIN
DECLARE num_days INT;
SET num_days = DATEDIFF(date2, date1);
RETURN num_days;
END;

Maintenant, je veux connaître le nombre de jours entre Noël et le Nouvel An 2022, nous pouvons utiliser le code suivant pour appeler la fonction stockée ci-dessus :

SELECT days_between('2022-12-25', '2023-01-01');

Le code ci-dessus renverra 7 qui est le nombre de jours entre deux dates.

6. Conclusion

Jusqu'à présent, nous avons compris les concepts, la syntaxe et les exemples de procédures stockées et de fonctions stockées MySQL. Ces objets peuvent nous aider à mieux organiser le code SQL et à améliorer les performances et la maintenabilité de la base de données. Dans les applications pratiques, les procédures stockées et les fonctions stockées sont largement utilisées dans les entrepôts de données, la business intelligence, les applications et d'autres domaines. Attention cependant à ne pas en abuser, car cela pourrait réduire la lisibilité et la maintenabilité de votre code.

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!