mysql进阶篇之变量及存储过程
作者：YXN-sql 阅读量：215 发布日期：2021-11-18
变量
1、系统变量
是mysql服务器提供，不是用户自定义的，属于服务器层面。分为全变量global、会话变量session。
# 查看系统变量
show [session|global] variables; --查看所有的系统变量
show [session|global] variables like ‘...’; --可以通过like模糊匹配方式查找变量
select @@[session/global.]系统变量名； --查看指定变了的值，注意可选参数里面的.
# 设置系统变量
set [session|global] 系统变量名字 = 值; --不需要@，查询是才需要@
上面这种设置全局变量方法重启后就失效，不想失效就修改参数文件, 然后重启mysqld
vi /etc/my.cnf
[mysqld]
wait_timeout=10
例子：
show session variables like 'auto%'
select @@global.autocommit;
select @@session.autocommit;
set session auto_increment = 1 ;
2、用户变量
是用户根据需求自己定义的变量，用户变量不用提前声明，在用是时候直接用‘@变量名’使用就可以。其作用域为当前连接。
赋值
set @var_name = 值;
set @var_name := 值; --推荐使用这种冒号等于的方式定义
select @var_name := 值;
select 字段名 into @var_name from 表名;
查看变量
select @var_name ; --如果直接select 一个不存在的用户变量名会返回null而不会报错
例子：--赋值
set @myname := 'xiaoming';
set @myage := 18;
--使用
select @myname,@myage
3、局部变量
是根据需要定义在局部生效的变量，访问之前，需要declare声明。可用作存储过程内的局部变量和输入参数，局部变量的作用范围是在其内声明的begin...end模块。
声明：
declare 变量名 变量类型(长度) [default 默认值]
变量类型就是数据库字段类型：int、bigint、char、varchar、date、time
赋值：
set 变量名 = 值；
set 变量名 ：= 值；
select 字段名 into 变量名 from 表名；
ps：区分技巧：系统变量有2个@@，用户变量1个@，局部变量在存储过程或存储函数内部用declare声明。
存储过程
# 创建存储过程（函数）
# 语法形式
create procedure 存储过程名([[IN |OUT |INOUT ] 参数名 数据类形(长度)...])
begin
sql语句;
end;
# IN 输入参数：表示调用者向过程传入值（传入值可以是自变量或变量）
# OUT 输出参数：表示过程向调用者传出值(可以返回多个值)（传出值只能是变量）
# INOUT 输入输出参数：既表示调用者向过程传入值，又表示过程向调用者传出值（值只能是变量）
# 查看存储过程
select * from information_schema.routines where routine_schema = 'xxx'; --查看指定数据库的存储过程及状态信息
show create procedure 存储过程名字 ; --查看某个存储过程的定义sql语句
# 删除存储过程
drop procedure [if exists] 存储过程名字;
# 举例
# 带有输入参数的存储过程
create procedure a(in test1 varchar(15))
begin
select char_length(test1);
end;
# 调用存储过程
call a('上海');
# 带有输入参数与输出参数的存储过程
create procedure a1(in test1 varchar(15),out len1 int)
begin
select char_length(test1) into len1;
end;
call a1('乌鲁木齐',@len1);
select @len1;
实践
1、创建一个存储过程，输入一个分数后返回是分数的评判等级
create procedure p1(in score int, out result varchar(10))
begin
if score >= 85 then
set result := '优秀';
elseif score >= 60 then
set result := '及格';
eles
set result := '不及格';
end if;
end;
call p1(85,@result); --来一个用户自定义变量来接收out返回的值
select @result;
2、将传入的200分制的分数，进行换算，换成百分制，然后返回分数---> inout
create procedure p2(inout score duble)
begin
set score := score * 0.5;
end;
set @score = 78;
call p2(@score);
select @score;
3、while实现1到n的累加
create procedure p3(in n int)
begin
declare total int default 0;
while n>0 do
set total := total + n;
set n := n -1;
end while;
select total;
end;
4、repeat实现计算1到n的累加，n为传入参数值 --a.定义局部变量，记录累加之后的值； --b.每循环一次，就对n➖1，n为0则退出循环
create procedure p4(in n int)
begin
declare total int default 0;
repeat
set total := total + n;
set n := n-1;
until n<=0
end repeat;
select total;
end;
5、loop实现1到n的奇数的累加
create procedure p5(in n int)
begin
declare total int default 0;
sum:loop
if n<=0 then
leave sum;
end if;
if n%2 = 1 then
set n := n -1;
iterate sum;
end if;
set total := total + n;
set n := n -1;
end loop sum;
select total;
end;
6、case when使用
create procedure p6(in month int)
begin
declare result varchar(10);
case
when month >= 1 and month <= 3 then
set result := '第一季度';
when month >=4 and month <= 6 then
set result := '第二季度';
when month >= 7 and month <= 9 then
set result := '第三季度';
when month >=10 and month <= 12 then
set result := '第四季度';
else
set result := '非法参数';
end case;
select result;
end;
call p6(6)
7、游标的使用, 输入一个年龄，把tb_user表中小于这个年龄的用户的信息插入到另一个表tb_user_pro中
create procedure p7(int uage int)
begin
declare u_cursor cursor for select name,profession from tb_user where age <=uage;
declare uname varchar(10);
declare upro varchar(100);
declare exit handler for NOT FOUND close u_cuesor; --条件处理程序，当下面while循环时游标的数据为空时就不会报错了。
drop table id exists tb_user_pro;
create table if exists tb_user_pro(
id int primary key auto_increment,
name varchar(10),
profession varchar(100)
);
open u_cursor;
while true do
fetch u_cursor into uname,upro;
insert into tb_user_pro values(null,uname,upro);
end while;
close u_cursor;
end;
YXN-sql
2021-11-18