curseur de procédure stockée mysql

Explication détaillée des procédures stockées et des curseurs MySQL

Dans la base de données MySQL, une procédure stockée est un ensemble d'instructions SQL précompilées, qui peuvent être considérées comme un sous-programme dans la base de données. Les procédures stockées sont généralement utilisées pour gérer une logique métier complexe, simplifiant l'interaction entre les applications et les bases de données et améliorant l'efficacité et la sécurité du traitement des données. Un curseur est un mécanisme utilisé pour traiter les ensembles de résultats de requête dans des procédures stockées.

Cet article présentera en détail l'utilisation et les caractéristiques des procédures stockées et des curseurs MySQL.

1. Procédure stockée

1.1 Structure syntaxique de la procédure stockée

Voici la structure syntaxique de base de la procédure stockée MySQL :

CREATE PROCEDURE procedure_name [ (parameter_list) ] BEGIN
    -- 存储过程的逻辑实现
END;

Parmi eux,

• procedure_name : le nom de la procédure stockée
• parameter_list : la liste des paramètres de la procédure stockée peut contenir 0 ou plusieurs paramètres, chaque paramètre se compose du nom du paramètre et du type de paramètre entre
• BEGIN et END est la partie d'implémentation logique de la procédure stockée ;

Par exemple, définissez une procédure stockée simple pour interroger tous les enregistrements de la table étudiant (étudiant) et renvoyer :

CREATE PROCEDURE get_all_students()
BEGIN
    SELECT * FROM student;
END;

1.2 Passage des paramètres des procédures stockées

Les procédures stockées peuvent prédéfinir des paramètres, et ces paramètres peuvent être appelés lors de l'appel Réussi lors du stockage de la procédure. Les procédures stockées MySQL prennent en charge 3 méthodes de transmission de paramètres : IN, OUT et INOUT.

• IN : En tant que paramètre d'entrée, la valeur du paramètre est transmise lors de l'appel de la procédure stockée.
• OUT : En tant que paramètre de sortie, le résultat est calculé et renvoyé dans la procédure stockée.
• INOUT : C'est à la fois un paramètre d'entrée et un paramètre de sortie. Lors de l'appel d'une procédure stockée, vous transmettez la valeur du paramètre et recevez le résultat calculé de la procédure stockée.

Ce qui suit est un exemple de procédure stockée utilisant le passage de paramètres :

CREATE PROCEDURE add_two_numbers(IN num1 INT, IN num2 INT, OUT result INT)
BEGIN
    SET result = num1 + num2;
END;

Dans l'exemple ci-dessus, la procédure stockée add_two_numbers reçoit deux paramètres d'entrée num1 et num2 et attribue le résultat de leur ajout au résultat du paramètre de sortie.

Vous pouvez utiliser les instructions suivantes pour appeler cette procédure stockée :

CALL add_two_numbers(2, 3, @result);
SELECT @result; -- 输出 5

1.3 Contrôle de flux des procédures stockées

Les procédures stockées MySQL prennent en charge les instructions de contrôle de flux courantes, telles que IF, CASE, WHILE, LOOP et LEAVE, etc. Utilisez ces instructions pour implémenter un contrôle logique et un traitement métier complexes.

Ce qui suit est un exemple de procédure stockée utilisant les instructions IF et WHILE :

CREATE PROCEDURE calc_factorial(IN num INT, OUT result BIGINT)
BEGIN
    SET result = 1;
    WHILE num > 1 DO
        SET result = result * num;
        SET num = num - 1;
    END WHILE;
END;

Dans l'exemple ci-dessus, si le paramètre d'entrée num est supérieur à 1, une boucle WHILE est utilisée pour calculer la valeur factorielle de num et le résultat est stocké dans le paramètre de sortie result .

Vous pouvez utiliser l'instruction suivante pour appeler cette procédure stockée :

CALL calc_factorial(6, @result);
SELECT @result; -- 输出 720

2. Curseur

2.1 Concept de base du curseur

Dans les procédures stockées MySQL, le curseur est le mécanisme utilisé pour parcourir le résultat de la requête défini dans la procédure stockée . Le curseur suit la ligne actuelle dans le jeu de résultats de la requête et se déplace ligne par ligne selon les besoins pour lire les données de cette ligne.

Pour utiliser un curseur pour déclarer, ouvrir, fermer et faire fonctionner un curseur, vous devez utiliser les 4 commandes suivantes :

• DECLARE : déclarer le curseur, définir le nom du curseur, l'instruction de requête et le type de curseur, etc.
• OPEN : ouvrez le curseur et enregistrez le résultat de la requête dans le tampon pointé par le curseur.
• FETCH : obtenez la ligne actuelle pointée par le curseur et stockez la valeur de la ligne actuelle dans la variable correspondante.
• FERMER : Fermez le curseur et libérez la mémoire occupée par le curseur.

Ce qui suit est un exemple de procédure stockée utilisant des curseurs :

CREATE PROCEDURE get_all_students()
BEGIN
    DECLARE done INT DEFAULT FALSE; -- 定义游标是否结束的标志
    DECLARE s_id INT; -- 存储查询结果中的学生编号
    DECLARE s_name VARCHAR(255); -- 存储查询结果中的学生姓名
    DECLARE cursor_students CURSOR FOR SELECT id, name FROM student; -- 声明游标，查询表 student 中的所有数据
    -- 打开游标
    OPEN cursor_students;
    -- 遍历游标指向的结果集
    read_loop: LOOP
        -- 获取游标指向的当前行
        FETCH cursor_students INTO s_id, s_name;
        -- 如果游标到达结果集的末尾，则跳出循环
        IF done THEN
            LEAVE read_loop;
        END IF;
        -- 输出当前行的值
        SELECT s_id, s_name;
    END LOOP;
    -- 关闭游标
    CLOSE cursor_students;
END;

Dans l'exemple ci-dessus, un curseur nommé curseur_students est déclaré pour interroger toutes les données de la table student. Après avoir ouvert le curseur, utilisez le corps de la boucle read_loop pour parcourir l'ensemble de résultats de la requête, utilisez la commande FETCH pour obtenir la valeur de la ligne actuelle, stockez-la dans les variables s_id et s_name et affichez-la sur la console. Si le curseur atteint la fin du jeu de résultats, sortez de la boucle. Lorsque le curseur a fini d'utiliser, vous devez utiliser la commande CLOSE pour fermer le curseur.

2.2 Caractéristiques et scénarios d'application des curseurs

Bien que les curseurs offrent aux développeurs un moyen pratique d'interroger les ensembles de résultats, car ils nécessitent de la mémoire et des ressources supplémentaires, une attention particulière doit être accordée aux problèmes suivants lors de leur utilisation :

• Curseurs peut avoir un impact sur les performances, en particulier lorsque vous travaillez avec de grands ensembles de données. Il n'est pas recommandé d'utiliser des curseurs dans des environnements à grande échelle ou à forte concurrence, et d'autres méthodes, telles que les sous-requêtes et les opérations JOIN, doivent être prioritaires.
• Les curseurs ne peuvent être utilisés que dans des procédures stockées et ne peuvent pas être utilisés directement dans des instructions SQL.
• Les curseurs doivent être utilisés avec prudence, car si le curseur n'est pas fermé correctement, la base de données MySQL occupera beaucoup de ressources mémoire ou même plantera.

Normalement, les curseurs conviennent aux scénarios suivants :

• Situations dans lesquelles une logique de requête complexe doit être implémentée dans des procédures stockées.
• Situations dans lesquelles de grands ensembles de données doivent être traités par lots.
• Situations dans lesquelles l'ensemble de résultats de la requête doit être traité ligne par ligne.

3. Résumé

Cet article présente principalement l'utilisation et les caractéristiques des procédures stockées et des curseurs dans la base de données MySQL. Les procédures stockées peuvent améliorer l'efficacité et la sécurité des interactions entre les applications et les bases de données, et les curseurs peuvent facilement parcourir les ensembles de résultats de requêtes. Cependant, il convient de noter que vous devez être prudent lorsque vous utilisez des curseurs pour éviter les fuites de mémoire et les problèmes de performances.

Référence :

• MySQL :: Manuel de référence MySQL 8.0 :: 13.6.4.1 Déclaration du curseur DECLARE
• MySQL :: Manuel de référence MySQL 8.0 :: 13.6.4.2 Déclaration du curseur OPEN
• MySQL :: Manuel de référence MySQL 8.0 :: 13.6.4.3 Curseur FETCH Déclaration
• MySQL :: Manuel de référence MySQL 8.0 :: 13.6.4.4 Déclaration du curseur CLOSE

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!