mysql存储过程

本篇博客 侧重点在 mysql存储过程的语法 和 简单应用，以 示例 为主

每行代码都有注释 适合快速学习

变量

注意：SQL查询 动态赋值时 SQL查询结果 只能有一个字段，一行数据

-- 定义 局部变量
declare id nvarchar(10);

-- 定义 局部变量 设置类型 和 默认值
declare id nvarchar(10) default 0;

-- 定义 全局变量 并 赋值（固定值）
set @a = 1;
-- 定义 全局变量 并 赋值（SQL查询，动态值）
set @a = (select GMV from shop WHERE brand_id = '4636' order by GMV desc limit 1);
-- 给 变量赋值（SQL查询，动态值）
select user_id into id from demo1 order by user_id desc limit 1;

存储过程示例

存储过程 基础

创建存储过程 统计店铺表 数据

-- 变更分隔符
delimiter $$
-- 如果存储过程存在 那么删除
DROP PROCEDURE IF EXISTS count_shop;
-- 创建新的 存储过程
CREATE PROCEDURE count_shop()
BEGIN
	-- 查询SQL语句
	SELECT COUNT(1) FROM shop;
END $$
delimiter ;
-- 调用 存储过程
CALL count_shop;

创建存储过程 创建表 并 插入数据

delimiter $$ -- 变更分隔符
DROP PROCEDURE IF EXISTS create_table_insert; -- 如果存储过程存在 那么删除

CREATE PROCEDURE create_table_insert() -- 创建新的 存储过程
BEGIN
	-- 创建表
	create table demo1(
		user_id int,
		user_name VARCHAR(50)
	);
	-- 插入数据
	INSERT INTO demo1 (user_id, user_name) VALUES(1,'张三'),(2,'李四'),(3,'王五');
END $$
delimiter $$
CALL create_table_insert;

创建局部变量 及 两种 赋值方式

delimiter $$
drop procedure if exists var;
create procedure var()
begin
	-- 创建 局部变量 并设置默认值为 0（作用域 仅限 begin里）
	declare id nvarchar(10) default 0;
	-- 给变量赋值
	set id = 1;
	-- 查询 表数据 赋值给 变量
	select user_id into id from demo1 order by user_id desc limit 1;
	-- 使用变量
	select * from demo1 WHERE user_id = id;
end $$
delimiter ;
call var;

存储过程 出入参

创建存储过程 传入id 传出姓名

出参 in 可以省略
入参 out 省略不报错，但如果省略 接收的变量 有值，那么就不会赋值
如果省略in和out 调用的时候给 参数 就是入参，写变量就是 出参

个人认为这里出参没啥软用，要用全局变量接收，跟在存储过程中定义全局变量一样

-- 查询所有 存储过程
show procedure status;

delimiter $$
drop procedure if exists test_out;
-- 出/入参 都写这里，调用的时候 接收/传递
create procedure test_out(uid int,out uname nvarchar(10))
begin
	set @uid=uid;
	-- 把查询的值 赋值给 出参
	select user_name into uname from demo1 where user_id = @uid;
end $$
delimiter ;
-- 传递 并接收参数，这里接收要用全局变量，不然接收不到存储过程里面的值
call test_out(3,@uname);
select @uname;

逻辑判断语法

if判断

case when 和 mysql 一样

delimiter $$
create procedure if_estimate(in input_value int)
begin
  if input_value > 10 then
    select '大于10';
  elseif input_value = 10 then
    select '等于10';
  else
    select '小于10';
  end if;
end$$
delimiter ;
call if_estimate(10);

while循环

delimiter $$
create procedure while_loop(in limit int)
begin
  declare counter int default 1;
  while counter <= limit do
    -- 在这里编写你的循环体代码
    select counter;
    set counter = counter + 1;
  end while;
end $$
delimiter ;
call while_loop(10);

while 模拟 for循环

delimiter $$
create procedure for_loop(in start_value int, in end_value int)
begin
  declare i int;
  set i = start_value;
  while i <= end_value do
    -- 在这里编写你的循环体代码
    select i;
    set i = i + 1;
  end while;
end $$
delimiter ;
call for_loop(1,10);

自定义函数

-- 创建函数
create function add_numbers (a int, b int)
-- 定义返回类型
returns int
begin
	-- 返回值
    return a + b;
end;
-- 调用函数
SELECT add_numbers(3, 4);

存储过程+自定义函数 实例

以下实例 实现了 所有偶数id 名称 转换为 虚拟名称（名称_VR）

用到了：存储过程、自定义函数、while循环 和 存储过程 if 判断
·
其中 在 存储过程 中 调用了 自定义函数，嵌套存储过程 也是一样的

-- 自定义函数：将名称 转换为虚拟名称
delimiter $$
-- 如果函数存在，那么删除
drop function if exists update_name;
-- 创建 自定义函数
create function update_name(vrname varchar(100))
-- 定义返回值类型
returns varchar(100)
deterministic
-- 定义代码块
BEGIN
		-- 为变量添加_VR后缀
		set vrname = concat(vrname,'_VR');
		-- 返回
		return vrname;
END $$
-- 测试函数
select update_name('a');



-- 存储过程：将id为偶数的 用户名称 转换为 虚拟的（_VR）
delimiter $$
drop procedure if exists two_vrname;
create procedure two_vrname()
begin
	-- 创建变量 循环开始
	declare i int default 0;
	-- 循环结束
	declare end_i int;
	declare uid int;
	-- 存储 返回的 虚拟名称
	declare uname varchar(100);
	-- 查询偶数有多少列，并获取最小id，赋值 给 变量
	select count(1),min(user_id) into end_i,uid from demo1 where user_id % 2 = 0 order by user_id limit 1;
	-- select i,end_i,uid; -- 调试
	-- 遍历 处理 偶数行
	WHILE i < end_i DO
		-- select i,end_i,uid; -- 调试
		-- 根据id 查 name 并 赋值
		select user_name into uname from demo1 where user_id = uid;
		-- 如果是虚拟的 那么不处理
		if right(unmae,3) = '_VR' then
			-- 调用update_name()函数，并修改
			update demo1 set user_name = update_name(uname) where user_id = uid;
		end if;
		-- 迭代 循环 变量
		set i = i + 1;
		-- 迭代 偶数 id
		set uid = uid + 2;
	END WHILE;
end $$
delimiter ;
call two_vrname();