1. 什么是存储过程和存储函数
存储过程（stored procedure）是一组sql语句的集合，这些语句被存储在数据库中。通过封装业务逻辑，存储过程可以提高数据库执行效率和数据访问的安全性。
存储函数（Stored Function）是指在一个数据库中存储的一组执行SQL语句的集合，与存储过程的区别在于，存储函数有一个返回值。
2. 创建存储过程
CREATE PROCEDURE procedure_name([IN/OUT] parameter_name data_type)BEGIN SQL Statement;END;
假设我们已经有一张名为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_typeBEGIN 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 ;
9. 存储过程和存储函数的优点
代码可以重复使用，避免重复编写SQL语句；
在存储过程和存储函数中可以使用流程控制语句，处理复杂逻辑；
通过存储过程和存储函数可以对数据库操作进行封装，提高效率和安全性。
以上就是MYSQL存储过程和存储函数怎么使用的详细内容，更多请关注创想鸟其它相关文章！
版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。
如发现本站有涉嫌抄袭侵权/违法违规的内容， 请发送邮件至 chuangxiangniao@163.com 举报，一经查实，本站将立刻删除。
发布者：程序猿，转转请注明出处：https://www.chuangxiangniao.com/p/143643.html
微信扫一扫 
支付宝扫一扫 
