Comment utiliser les procédures stockées et les fonctions stockées MYSQL

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

• Une procédure stockée (Stored Procedure) est un ensemble d'instructions SQL stockées dans la base de données. En encapsulant la logique métier, les procédures stockées peuvent améliorer l'efficacité de l'exécution des bases de données et la sécurité de l'accès aux données.

• La fonction stockée fait référence à un ensemble d'instructions SQL exécutées stockées dans une base de données. La différence avec les procédures stockées est que les fonctions stockées ont une valeur de retour.

2. Créez une procédure stockée

CREATE PROCEDURE procedure_name([IN/OUT] parameter_name data_type)
BEGIN
    SQL Statement;
END;

Supposons que nous ayons déjà une table d'employés nommée employee, et que nous devions maintenant créer une procédure stockée qui peut interroger le nom et le salaire de l'employé en fonction du Numéro de travail de l'employé：employee的员工表，现在需要创建一个存储过程，可以根据员工的工号查询员工的姓名和工资：

DELIMITER //
CREATE PROCEDURE get_employee_info_by_id(IN emp_id INT)
BEGIN
    SELECT name, salary FROM employee WHERE id = emp_id;
END //
DELIMITER ;

3. 创建存储函数

CREATE FUNCTION function_name([IN/OUT] parameter_name data_type) RETURNS data_type
BEGIN
    DECLARE variable_name data_type;
    SQL Statement;
    RETURN variable_name;
END;

假设我们已经有一张名为product的商品表，现在需要创建一个存储函数，可以根据商品的编号查询商品的单价：

DELIMITER //
CREATE FUNCTION get_product_price_by_id(IN product_id INT) RETURNS DECIMAL(10,2)
BEGIN
    DECLARE price DECIMAL(10,2);
    SELECT unit_price INTO price FROM product WHERE id = product_id;
    RETURN price;
END //
DELIMITER ;

4. 存储过程和存储函数的使用

• 调用存储过程：

CALL procedure_name([parameter_name]);

• 调用存储函数：

SELECT function_name([parameter_name]);

使用上面创建的get_employee_info_by_id存储过程可以这样调用：

CALL get_employee_info_by_id(1);

使用上面创建的get_product_price_by_id存储函数可以这样调用：

SELECT get_product_price_by_id(1001);

以下是一些常见的存储过程和存储函数的示例：

5. 带有if语句的存储过程

假设我们已经有一张名为employee的员工表，现在需要创建一个存储过程，查询员工的姓名和工资，如果工资大于5000，则在结果中添加一个备注：“高收入”。

DELIMITER //
CREATE PROCEDURE get_employee_info_with_note()
BEGIN
    SELECT name, salary, IF(salary > 5000, '高收入', '') AS note FROM employee;
END //
DELIMITER ;

6. 带有循环语句的存储过程

假设我们已经有一张名为product的商品表，现在需要创建一个存储过程，把商品的单价全部乘以1.1。

DELIMITER //
CREATE PROCEDURE update_all_product_price()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE pid INT;
    DECLARE price DECIMAL(10,2);
    DECLARE cur CURSOR FOR SELECT id, unit_price FROM product;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    OPEN cur;
    read_loop: LOOP
        FETCH cur INTO pid, price;
        IF done THEN
            LEAVE read_loop;
        END IF;
        UPDATE product SET unit_price = price * 1.1 WHERE id = pid;
    END LOOP;
    CLOSE cur;
END //
DELIMITER ;

7. 带有事务的存储过程

假设我们已经有一张名为order的订单表和一张名为order_item的订单详情表，现在需要创建一个存储过程，向这两张表中插入一条记录。

DELIMITER //
CREATE PROCEDURE insert_order(IN order_id INT, IN item_name VARCHAR(50), IN item_price DECIMAL(10,2), IN item_quantity INT)
BEGIN
    START TRANSACTION;
    INSERT INTO `order`(id) VALUES(order_id);
    SET @last_order_id = LAST_INSERT_ID();
    INSERT INTO order_item(order_id, item_name, item_price, item_quantity) VALUES(@last_order_id, item_name, item_price, item_quantity);
    COMMIT;
END //
DELIMITER ;

8. 带有游标的存储函数

假设我们已经有一张名为product

DELIMITER //
CREATE FUNCTION get_max_product_price() RETURNS DECIMAL(10,2)
BEGIN
    DECLARE max_price DECIMAL(10,2);
    DECLARE cur CURSOR FOR SELECT unit_price FROM product;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET max_price = 0;
    OPEN cur;
    FETCH cur INTO max_price;
    read_loop: LOOP
        FETCH cur INTO max_price;
        IF max_price IS NULL THEN
            LEAVE read_loop;
        END IF;
        IF max_price > @max_price THEN 
            SET @max_price = max_price;
        END IF;
    END LOOP;
    CLOSE cur;
    RETURN max_price;
END //
DELIMITER ;

3. Créer une fonction de stockage

rrreee

Supposons que nous ayons déjà une table de produits nommée product, et maintenant nous devons créer une fonction de stockage qui peut interroger le prix unitaire de le produit en fonction du numéro de produit :

rrreee
• 4. Utilisation de procédures stockées et de fonctions stockées

• Appel de procédures stockées :

rrreee

• Appel de fonctions stockées :

🎜rrreee🎜Utilisation du get_employee_info_by _id La procédure stockée créée ci-dessus peut ressembler à ceci Appel : 🎜rrreee🎜En utilisant la fonction stockée get_product_price_by_id créée ci-dessus, elle peut être appelée comme ceci : 🎜rrreee🎜Voici quelques exemples de procédures stockées courantes et stockées fonctions : 🎜🎜5. Procédures stockées avec des instructions if 🎜🎜Supposons que nous ayons déjà une table d'employés nommée employee. Nous devons maintenant créer une procédure stockée pour demander le nom et le salaire de l'employé. supérieur à 5000, ajoutez une note au résultat : « Revenu élevé ». 🎜rrreee🎜6. Procédure stockée avec instruction de boucle🎜🎜Supposons que nous ayons déjà une table de produits nommée product, et que nous devons maintenant créer une procédure stockée pour multiplier les prix unitaires de tous les produits par 1,1. 🎜rrreee🎜7. Procédure stockée avec transactions🎜🎜Supposons que nous ayons déjà une table de commande nommée order et une table de détails de commande nommée order_item. pour insérer un enregistrement dans ces deux tables. 🎜rrreee🎜8. Fonction de stockage avec curseur🎜🎜Supposons que nous ayons déjà une table de produits nommée product, et que nous devons maintenant créer une fonction stockée pour interroger le prix unitaire maximum dans la table des produits. 🎜rrreee🎜9. Avantages des procédures stockées et des fonctions stockées🎜🎜🎜🎜Le code peut être réutilisé pour éviter l'écriture répétée d'instructions SQL ;🎜🎜🎜🎜Les instructions de contrôle de flux peuvent être utilisées dans les procédures stockées et les fonctions stockées pour gérer une logique complexe ;🎜 🎜 🎜🎜Les opérations de base de données peuvent être encapsulées via des procédures stockées et des fonctions stockées pour améliorer l'efficacité et la sécurité. 🎜🎜🎜

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!