Mysql 存储过程

什么是存储过程？

存储过程是事先经过编译并存储在数据库的一段sql语句的集合

如何创建一个存储过程？

create procedure 存储过程名称([参数列表])
beginsql语句;
end#例
create procedure p1()
beginselect * from t_goods;select * from t_user;
end

如果是在命令行中创建存储过程，会遇见分号结束的问题，需要用delimiter来修改sql语句结束的标志
例 “delimiter #” 就是将#作为sql语句结束的标志

怎么使用存储过程？

call 存储过程名称([参数列表])
# 例
call p1()

怎么查看已经创建的存储过程？

# 查询指定数据库的存储过程及状态信息
select * from information_schema.routines where routine_schema='数据库名称' 
# 例
select * from information_schema.routines where routine_schema='seckill'# 查询某个存储过程的定义
show create procedure 存储过程名称
# 例
show create procedure p1

怎么删除一个存储过程？

drop procedure [if exists] 存储过程名称
# 例
drop procedure p1

变量

系统变量

系统变量是mysql服务器提供，分为全局变量(global)，会话变量(session)
如果没有指定session/global，默认为session
msyql重启后，设置的全局参数都会失效，要想不失效，可以在/etc/my.cnf中配置(linux)

查看系统变量

# 查看所有系统变量
show [session|global] variables
# 例
show global variables# 通过like模糊匹配查找系统变量
show [session|global] variables like '...'
# 例
show global variables like 'admin%'# 查看指定变量的值
select @@[session|global] 系统变量名
# 例 查autocommit的值
select @@autocommit
select @@session.autocommit

设置系统变量

set [session|global] 系统变量名=值
# 例
set session autocommit = 0set @@[session|global] 系统变量名=值
# 例
set @@session.autocommit = 1

用户变量

用户变量是用户自己定义的变量，用户变量不用提前声明，在用的时候直接使用"@变量名"使用就可以，其作用域为当前连接

变量赋值

# 四种方式
set @变量名 = 值, @变量名 = 值 ...
# 例
set @stu_name = 'zdy', @stu_age = 18set @变量名 := 值, @变量名 := 值 ...
# 例
set @stu_name := 'zzz', @stu_age := 20select @变量名:= 值, @变量名 := 值 ...
# 例
select @stu_name := 'zdz', @stu_age := 16select 字段名 from 表名 into @变量名
# 例 将cout(*)的值存入变量
select count(*) from t_user into @user_count

使用

select @变量名1, @变量名2....
# 例
select @stu_name, @stu_age

局部变量

局部成效，要通过declare成名之后才能使用，可作为存储过程内的局部变量和输入参数，局部变量的范围是在其内声明的begin…end模块

声明局部变量 （变量类型就是数据库字段类型 int、char、varchar…）

declare 变量名 变量类型[default ...]

变量赋值

set 变量名 = 值
set 变量名 := 值
select 字段名 from 表名 into 变量名 ...

整体案例

create procedure p3()
begin# 定义变量declare good_count int default 0;# 变量复制select count(*) from t_goods into good_count;# 查询变量值select good_count;
end

存储过程中使用IF条件判断

create procedure p4()
begindeclare score int default 67;declare result varchar(10);if score >= 80 thenset result := '优秀';elseif score >= 60 thenset result := '及格';elseset result := '不及格';end if;select result;
endcall p4()

存储过程参数

参数有三种类型
IN                      该类参数作为输入，也就是需要调用时传入值，也是默认类型
OUT                  该类参数作为输出，也就是该参数可以作为返回值
INOUT              该类参数既可以作为输入参数，也可以作为输出参数

使用

create procedure 存储过程名称([IN/OUT/INOUT 数名 参数类型])
beginsql语句;
end

案例

create procedure p5(in goods_count int, out result varchar(10))
beginif goods_count >= 1 thenset result := '有库存';elseset result := '库存不足';end if;
endcall p5(2, @result)
select @result

存储过程中使用case

语法一

# 如果case_value等于v1则执行statement1语句等于v2则执行statement2语句...否则执行statementn语句
case case_valuewhen v1 then statement1when v2 then statement2...else statementn
end case
# 例
create procedure p7(in month int, out result varchar(10))
begincase monthwhen 1 thenset result := '一月';when 2 thenset result := '二月';when 3 thenset result := '三月';elseset result := '非法参数';end case;
endcall p7(2, @result)
select @result

语法二

# 如果condition1满足执行statement1，如果condition2满足执行statement2...否则执行statementn语句
casewhen condition1 then statement1when condition2 then statement2...else statementn
end case
# 例
create procedure p6(in month int, out result varchar(10))
begincasewhen month >=1 && month <=3 thenset result := '第一季度';when month >=4 && month <=6 thenset result := '第二季度';when month >=7 && month <=9 thenset result := '第三季度';when month >=10 && month <=12 thenset result := '第四季度';elseset result := '非法参数';end case;
endcall p6(2, @result)
select @result

存储过程中使用while

语法(满足条件进入循环)

while 条件 dosql逻辑
end while
# 例 计算1-100的累加
create procedure p8(in n int)
begindeclare sum int default 0;while n > 0 doset sum := sum + n;set n := n - 1;end while;select sum;
endcall p8(100)

存储过程中使用repeat

语法(满足条件退出循环)

repeatsql逻辑until 条件
end repeat
# 例
create procedure p9(in n int)
begindeclare sum int default 0;repeatset sum := sum + n;set n := n - 1;until n < 0end repeat;select sum;
endcall p9(100)

存储过程中使用loop

语法
如果不在sql逻辑中增加退出循环的条件，可以用来实现简单的死循环，loop可以配合下面的两个语句使用
leave 退出循环，相当于Java的break
iterate 跳过当前循环，相当于Java的continue

[label:] loopsql逻辑
end loop [label]
# 例1 1-n的累加
create procedure p10(in n int)
begindeclare sum int default 0;sum:loopif n<=0 thenleave sum;end if;set sum := sum + n;set n := n - 1; end loop sum;select sum;
end
call p10(100)
# 例2 1-n的偶数的累加
create procedure p11(in n int)
begindeclare sum int default 0;count:loopif n<=0 thenleave count;end if;if n%2 = 1 thenset n := n - 1; iterate count;end if;set sum := sum + n;set n := n - 1; end loop count;select sum;
endcall p11(100)

游标类型

游标类型用于存储查询结果集，在存储过程和函数中可以使用游标对结果集进行循环的处理。游标的使用包括游标的声明、open、fetch、close

声明游标

declare 游标名 cursor for 查询语句

打开游标

open 游标名称

获取游标记录

fetch 游标名称 into 变量1,变量2...

关闭游标

close 游标名称

案例
根据传入的参数uage，来查询用户表tb_user中，所有的用户年龄小于等于uage的用户姓名(name)和专业(profession)，并将用户的姓名和专业插入到新创建的一张表(id,name,profession)中

create procedure p12(in uage int)
begin# 1. 声明游标存储结果集 声明时要先声明变量再声明游标declare uname varchar(100);declare upro varchar(100);declare u_cursor cursor for select name, peofession from tb_user where age <= uage;# 2. 准备：创建表结构create table if not exists tb_user_pro(id int primary key auto_increment,name varchar(100),profession varchar(100));# 3. 开启游标open u_cursor;while true do # 这里埋了一个坑 条件处理程序解决# 4. 将游标中的数据插入到新表中fetch u_cursor into uname, upro;insert into tb_user_pro values (null, uname, upro);end while;# 5. 关闭游标close u_cursor;
endcall p12(40)

条件处理程序

条件处理程序可以用来定义在流程控制结构执行过程中遇到问题时相应的处理过程，语法如下

declare handler_action hanler for condition_value...statement
handler:continue: # 继续执行当前程序exit: # 终止执行当前程序
condition_value:sqlstate sqlstate_value: # 状态码 如 02000sqlwarning: # 所有以01开头的sqlstate代码的简写not found: # 所有以02开头的sqlstate代码的简写sqlexception: # 所有没有被sqlwarning或not found捕获的sqlstate代码的简写

案例

create procedure p12(in uage int)
begin# 1. 声明游标存储结果集 声明时要先声明变量再声明游标declare uname varchar(100);declare upro varchar(100);declare u_cursor cursor for select name, peofession from tb_user where age <= uage;# 声明条件处理程序declare exit handler for SQLSTATE '02000' close u_cursor;# 2. 准备：创建表结构create table if not exists tb_user_pro(id int primary key auto_increment,name varchar(100),profession varchar(100));# 3. 开启游标open u_cursor;while true do# 4. 将游标中的数据插入到新表中fetch u_cursor into uname, upro;insert into tb_user_pro values (null, uname, upro);end while;# 5. 关闭游标close u_cursor;
endcall p12(40)

存储函数

存储函数是有返回值的存储过程，存储函数的参数只能是in类型，存储函数能实现的存储过程都能实现，语法如下

create function 存储函数名称([参数列表])
returns type [characteristic...]
beginsql语句;return ...;
end;characteristic:deterministic # 相同的输入参数总是产生相同的结果no sql # 不包含sql语句reads sql data # 包含读取数据的语句，但不包含写入数据的语句# 例 1-n的累加
create function fun1(n int)
returns int deterministic
begindeclare sum int default 0;while n>0 doset sum := sum + n;set n := n - 1;end while;return sum;
endselect fun1(100)