MySQL基础知识：存储过程 – Stored Procedure

大家好，又见面了，我是你们的朋友全栈君。

MySQL存储过程（Stored Procedure）主要的知识点：

• 分隔符（delimiter）
• 变量（variable）
• 参数（parameters）

分隔符（DELIMITER）

MySQL通过delimiter来区分不同的SQL语句(SQL Statement)，默认的分隔符是 ;；

对于procedure，会有多条SQL Statement，且MySQL的每个statement都需要以分隔符结束；

如果我们想把一个procedure作为一条statement，那么我们就不能用默认的分隔符;，否则MySQL Server就不会把procedure里面的多条Statement认作一条statement。

因此，需要临时换一下分隔符delimiter，以使得 procedure 作为一条statement。

变量（Variable）

有三种变量：

• Stored Procedure的局部变量：作用域在代码块内(begin和end之间)，用declare定义。
• Session级别的session变量 (session variable) 即是 用户自定义变量（User-Defined Variables）：作用域在当前连接的session；变量名以@开始。
• 系统变量 (Server System Variables)：全局或会话级。

注意： 一般情况我们在存储过程中都应该使用局部变量。

参数

三种参数：

• IN
• OUT
• INOUT

示例：

数据库的dump脚本：Github

use mysql_practice;

drop procedure if exists sp_get_customer_basic_info;

-- #1: 吧默认的分隔符从 ';' 改为 '$$'; 
-- 注: $$ 后面没有 ';' 
DELIMITER $$

create procedure sp_get_customer_basic_info
(
	IN limitCount INT,
    OUT totalCount INT
)
BEGIN

declare total_count int default 0; -- 定义局部变量：scope 在BEGIN和END之间

set @session_count = 0; -- 定义session级别的变量。

SELECT 
	cus.no,
	cus.first_name,
	cus.last_name,
	pr.ext_name as province_name,
	cr.ext_name as city_name,
	ar.ext_name as area_name,
	ca.address_detail
FROM mysql_practice.customer cus
join customer_address ca
on ca.customer_id = cus.id
join region ar -- area
on ca.area_id = ar.id and ar.deep = 2
join region cr -- city
on ar.pid = cr.id
join region pr -- province
on cr.pid = pr.id
LIMIT limitCount; -- proceduer里面的sql语句还是用';'分割。

select count(1) into totalCount from mysql_practice.customer; -- 赋值OUT参数

END $$ -- $$表示整个proceduer作为一个statement的结束


DELIMITER ;  -- 改回默认的分隔符： ';'.

执行procedure:

set @totalCount = 0;
call sp_get_customer_basic_info(10, @totalCount );
select @totalCount;

参考资料

1. SHOW VARIABLES Statement
2. How to Declare Variables in MySQL
3. CREATE PROCEDURE and CREATE FUNCTION Statements

MySQL基础知识：存储过程 – Stored Procedure