文章目录
说明背景创建存储过程使用、删除存储过程扩展: 在存储过程中使用循环使用WHILE循环使用LOOP循环使用REPEAT循环
说明
本文所使的MySQL版本为5.7.26
背景
我们现在有这样一个需求：往t_user_view表中随机插入n条记录，其中n为受控变量。t_user_view表的建表语句如下：
CREATE TABLE `t_user_view` (
`id` BIGINT ( 20 ) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '自增id',
`user_id` BIGINT ( 20 ) DEFAULT NULL COMMENT '用户id',
`viewed_user_id` BIGINT ( 20 ) DEFAULT NULL COMMENT '被查看用户id',
`viewed_user_sex` TINYINT ( 1 ) DEFAULT NULL COMMENT '被查看用户性别',
`viewed_user_age` INT ( 5 ) DEFAULT NULL COMMENT '被查看用户年龄',
`create_time` datetime ( 3 ) DEFAULT CURRENT_TIMESTAMP ( 3 ),
`update_time` datetime ( 3 ) DEFAULT CURRENT_TIMESTAMP ( 3 ) ,
PRIMARY KEY ( `id` ),
UNIQUE KEY `idx_user_viewed_user` ( `user_id`, `viewed_user_id` )
) ENGINE = INNODB DEFAULT CHARSET = utf8;
这是一个很常见的需求，尤其在我们分析SQL语句的执行计划时，我们经常会面临这种数据问题(毕竟MySQL是基于成本地优化，同一个SQL语句在不同的数据量级下，优化器最终使用的执行计划可能是不同的)。而这种问题通过存储过程实现起来非常简单 。
创建存储过程
创建存储过程的基本语法如下：
CREATE PROCEDURE procedure_name ([proc_parameter[,...]]) routine_body
routine_body通常是一个使用BEGIN...END框起来的代码块
BEGIN
statesment
END
proc_parameter的定义如下
proc_parameter:
[ IN | OUT | INOUT ] param_name type
其中： IN: 表示入参，如果参数没有指定是入参还是出参，则默认为入参。入参的值在存储过程内部可以获取到，但是外部无法感知到存储过程内部对参数的修改(可以理解为编程语言中的形参)。
# 默认为IN
CREATE PROCEDURE test_in_param(param_in INT)
BEGIN
SET param_in := 2;
END
SET @param_in = 1;
# 调用 test_in_param，打印参数
CALL test_in_param(@param_in);
# 最终输出结果为 1 而不是 2
SELECT @param_in;
OUT: 表示出参，存储过程无法像程序编程那样使用return来获取返回值，因此需要使用一个变量进行接收，默认值是NULL。与IN不同，外部可以感知到该值在存储过程内部的变化。
CREATE PROCEDURE test_out_param(OUT param_out INT)
BEGIN
SET param_out := 2;
END
SET @param_out = 1;
# 调用 test_in_param，打印参数
CALL test_out_param(@param_out);
# 最终输出结果为 2
SELECT @param_out;
INOUT: 既可以表示入参，也可以表示出参。同时具备IN和OUT的功能。 现在我们开始创建业务场景中的存储过程
# 重新定义结束符
DELIMITER $$
DROP PROCEDURE IF EXISTS insertion_user;
# 这里定义了两个入参，insert_times 表示插入数据的条数，
# max_user_id 表示user_id的最大值，主要是为了能让 user_id 均匀分布
CREATE PROCEDURE insertion_user(IN insert_times INT, IN max_user_id INT)
BEGIN
DECLARE i INT DEFAULT 1;
DECLARE user_id_seq INT DEFAULT 1;
# sex 只有0和1两种取值
DECLARE sex TINYINT DEFAULT 0;
WHILE i <= insert_times
DO
# 随机生成年龄
INSERT INTO t_user_view(user_id, viewed_user_id, viewed_user_sex, viewed_user_age) VALUES (user_id_seq, i, sex, FLOOR(RAND() * 40));
SET user_id_seq := user_id_seq + 1;
SET sex := sex + 1;
# 注意这里的THEN语句后面以及END IF语句后面一定要加一个分号
IF sex % 2 = 0 THEN SET sex := 0; END IF;
IF user_id_seq = max_user_id + 1 THEN SET user_id_seq := 1; END IF;
SET i := i + 1;
END WHILE;
END$$
# 换回原来的默认结束符
DELIMITER ;
CALL insertion_user_proc(1000, 10);
使用、删除存储过程
调用存储过程非常简单，直接使用CALL pro_name([pram...])， 如上例，注意参数与参数类型要对应。
CALL insertion_user_proc(1000, 10);
删除存储过程建议使用下列语法:
DROP PROCEDURE IF EXISTS pro_name;
扩展: 在存储过程中使用循环
有三种方式可以实现循环
使用WHILE循环
语法形式为：
WHILE condition
DO
statesment
END WHILE
使用LOOP循环
语法形式为
loop_name:LOOP
statesment
IF condition THEN LEAVE loop_name; END IF;
END LOOP;
使用LOOP循环需要手动调用LEAVE来打破循环，有点类似于编程语言中的while(true)与break的组合。 示例：使用LOOP实现n的阶乘
DELIMITER $$
CREATE PROCEDURE factorial_loop_proc(up_limit INT UNSIGNED)
BEGIN
DECLARE i INT DEFAULT 1;
DECLARE result INT DEFAULT 1;
factorial_loop:LOOP
SET result := result * i;
IF i >= up_limit THEN LEAVE factorial_loop; END IF;
SET i := i + 1;
END LOOP;
SELECT result;
END$$
DELIMITER ;
CALL factorial_loop_proc(10);
使用REPEAT循环
基本语法
REPEAT
statesment
UNTIL condition END REPEAT;
有点类似于编程语言的do...while 还是以上面的阶乘为例
DELIMITER $$
CREATE PROCEDURE factorial_repeat_proc(up_limit INT UNSIGNED)
BEGIN
DECLARE i INT DEFAULT 1;
DECLARE result INT DEFAULT 1;
REPEAT
SET result := result * i;
SET i := i + 1;
UNTIL i > up_limit END REPEAT;
SELECT result;
END$$
DELIMITER ;
CALL factorial_repeat_proc(10);