Maison > Article > base de données > Comment utiliser les procédures stockées et les fonctions stockées MYSQL
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.
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 ;
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 ;
调用存储过程:
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);
以下是一些常见的存储过程和存储函数的示例:
假设我们已经有一张名为employee
的员工表,现在需要创建一个存储过程,查询员工的姓名和工资,如果工资大于5000,则在结果中添加一个备注:“高收入”。
DELIMITER // CREATE PROCEDURE get_employee_info_with_note() BEGIN SELECT name, salary, IF(salary > 5000, '高收入', '') AS note FROM employee; END // DELIMITER ;
假设我们已经有一张名为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 ;
假设我们已经有一张名为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 ;
假设我们已经有一张名为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
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 : 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!