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 ;
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 ;
以上就是MYSQL存储过程和存储函数的学习文章及示例,希望对您有帮助。
在MYSQL中,存储过程和存储函数可以帮助我们封装业务逻辑,提高数据库执行效率,同时也可以提高数据访问的安全性。学会使用存储过程和存储函数可以帮助我们更好地管理和优化数据库。
到此这篇关于浅谈MYSQL存储过程和存储函数的文章就介绍到这了,更多相关MYSQL存储过程和存储函数内容请搜索以前的文章或继续浏览下面的相关文章希望大家以后多多支持!
相关文章: