Mysql存储过程

什么是存储过程？

• 存储过程是数据库的一个对象，可以封装SQL语句集，完成复杂的业务逻辑，且可以入参出参。
• 创建时会预先编译保存，用户调用不需要再次编译

B站黑马教程链接

优点

• 生产环境下，可以直接修改存储过程，不用重启服务器
• 执行速度快，存储过程经过编译比单独一条一条执行要快
• 减少网络传输流量
• 方便优化

缺点

• 过程化编译，复杂业务处理的维护成本高
• 调试不便
• 不同数据库之间可以移植性差，不同数据库语法不一致！

准备

声明结束符：

• 开始：delimiter $$

• 结束：$$

• 创建存储过程

delimiter $$
-- 创建存储过程
create PROCEDURE hello_procedure()
begin
	select "1";
end $$

-- 运行存储过程
call hello_procedure() $$

• 定义局部变量（begin/end块有效）

delimiter $$
create PROCEDURE sp_var()
begin
-- declare 定义局部变量
	declare username varchar(32) default "小明";
	select username;
-- set 设置变量
	set username = "小黄";
	select username;
end$$

• 定义用户变量（当前会话有效）

-- 定义用户变量
set @username = '小白';

• 全局变量

• into定义变量

-- 定义结束符
delimiter $$
create procedure var_into()
begin
	select t_user.username into @username from t_user where id = 1;
end $$
-- 运行var_into()
call var_into() $$ 

select @username $$

• 入参出参

-- 语法
in | out |inout param_name type

• 入参 in

delimiter $$
-- 定义形参
create procedure sp_param01(in name varchar(32))
begin
	set @user_name = name;
end$$
call sp_param01('小明') $$
select @user_name $$

• 出参 out

delimiter $$
create procedure sp_param02(in name varchar(32),out password varchar(32))
begin
	set u.password into password from t_user u where u.name = name;
end$$
call sp_param02("admin",@password)$$

select @password $$

• inout 同时具备in 和out 的功能

• 流程控制（if else）

delimiter $$
create procedure sp_if()
begin
	if i > 0 
		then select '1';
	elseif i < 0 
		then select '-1';
	else
		select '0';
	end if:
		select 'end';
end $$

call sp_if()$$

• 流程控制（case when）

case years
	when 2001
		then
			业务逻辑;
	when 2002
		then
			业务逻辑;
	else:
		业务逻辑;
	end case:
		业务逻辑;
		

• 流程控制-循环(loop)
  至少写一个leave 否则会死循环

-- 定义一个变量控制循环次数
declare index int default 1;
-- loop_name 标记这个loop的名称
	loop_name:loop
		if index >10
			-- leave 退出loop_name 循环（break）
			-- iterate 继续循环（continue）
			then leave loop_name;
		end if;
		
		set index = index +1;	
-- 结束loop 带上loop的名称
	end loop loop_name;
	

• 循环（repeat）

repeat_name:repeat
	set index = index + 1;
	until index >=10
	end repeat repeat_name;

• 循环(while)

while index < 10 do
	set index = index +1
end while;

• 查询所有存储过程：

show PROCEDURE STATUS

• 查询存储过程语句

show create procedure sys.create_synonym_db

• 删除存储过程

drop PROCEDURE hello_procedure

• 异常处理(handler 句柄)

// continue 报错后继续执行 exit 报错后直接退出
declare continue handler for 1054 set outCode = -1;
//特别注意：
//在语法中，变量声明、游标声明、handler声明是必须按照先后顺序书写的，否则创建存储过程出错。