什么是存储过程?
- 存储过程是数据库的一个对象,可以封装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声明是必须按照先后顺序书写的,否则创建存储过程出错。