本文共 4495 字，大约阅读时间需要 14 分钟。
最近开发中用到存储过程和函数，就顺便学习一下了。
1.什么是存储过程和函数？
存储过程和函数是事先经过编译并存储在数据库的一段SQL语句集合，调用存储过程和函数可以减少数据库和应用服务器之间的传输，对于提高数据处理的效率是有好处的。
2.存储过程和函数的区别
3.操作存储过程和函数的权限：
1）存储过程的语法
create procedure sp_name([proc_parameter[,...]])
[characteristic ...] routine_body
解释：
sp_name:存储过程的名字
proc_parameter:存储过程的参数,[ IN\OUT \ INOUT ] param_name（参数） type (类型）
存储过程的示例：
CREATE DEFINER=`root`@`::1` PROCEDURE `film_not_in_stock`(IN p_film_id INT, IN p_store_id INT, OUT p_film_count INT) READS SQL DATABEGIN SELECT inventory_id FROM inventory WHERE film_id = p_film_id AND store_id = p_store_id AND NOT inventory_in_stock(inventory_id); SELECT FOUND_ROWS() INTO p_film_count;END
2）函数的语法
create function sp_name ([func_parameter[,...]])
returns type
[characteristic...] routine_body
解释：
sp_name:函数的名字。
func_parameter:函数的参数类型，param_name,不需要加IN。
函数示例：
CREATE DEFINER=`root`@`::1` FUNCTION `inventory_in_stock`(p_inventory_id INT) RETURNS tinyint(1) READS SQL DATABEGIN DECLARE v_rentals INT; DECLARE v_out INT; #AN ITEM IS IN-STOCK IF THERE ARE EITHER NO ROWS IN THE rental TABLE #FOR THE ITEM OR ALL ROWS HAVE return_date POPULATED SELECT COUNT(*) INTO v_rentals FROM rental WHERE inventory_id = p_inventory_id; IF v_rentals = 0 THEN RETURN TRUE; END IF; SELECT COUNT(rental_id) INTO v_out FROM inventory LEFT JOIN rental USING(inventory_id) WHERE inventory.inventory_id = p_inventory_id AND rental.return_date IS NULL; IF v_out > 0 THEN RETURN FALSE; ELSE RETURN TRUE; END IF;END
存储过程和函数中characteristic特征值说明：
删除存储过程或者函数示例
删除过程film_in_stock
drop procedure film_in_stock;
存储过程和函数创建后，用户可能需要查看存储过程、函数的状态、定义等信息，便于了解存储过程或者函数的基本情况。
1.查看存储过程或者函数的状态
show procedure status like 'film_in_stock';
show procedure status like 'film_in_stock';+--------+---------------+-----------+----------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+| Db | Name | Type | Definer | Modified | Created | Security_type | Comment | character_set_client | collation_connection | Database Collation |+--------+---------------+-----------+----------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+| sakila | film_in_stock | PROCEDURE | root@::1 | 2018-09-26 21:19:23 | 2018-09-26 21:19:23 | DEFINER | | utf8mb4 | utf8mb4_0900_ai_ci | utf8mb4_0900_ai_ci |+--------+---------------+-----------+----------+---------------------+---------------------+---------------+---------+----------------------+-----------
2.查看过程过程或者函数的定义
show create procedure film_in_stock;
show create procedure film_in_stock;+---------------+-------------------------------------------------------------------------------------------+| Procedure | sql_mode | Create Procedure | character_set_client | collation_connection | Database Collation || film_in_stock | STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_ENGINE_SUBSTITUTION | CREATE DEFINER=`root`@`::1` PROCEDURE `film_in_stock`(IN p_film_id INT, IN p_store_id INT, OUT p_film_count INT) READS SQL DATABEGIN SELECT inventory_id FROM inventory WHERE film_id = p_film_id AND store_id = p_store_id AND inventory_in_stock(inventory_id); SELECT FOUND_ROWS() INTO p_film_count;END | utf8mb4 | utf8mb4_0900_ai_ci | utf8mb4_0900_ai_ci |
转载地址：http://ccfoi.baihongyu.com/