一:存储过程:

存储过程是事先经过编译并且存储在数据库中的一段SQL语句的集合，调用存储过程可以简化应用开发人员的很多工作，减少数据库和应用服务器之间的传输，对于提交数据处理的效率是有好处的 

在思想上来说就是在数据库层面，将SQL代码语言层面进行代码的封装和复用

好处:

1)封装，复用:可以把某一条业务数据封装在存储过程中，需要用到的时候直接进行调用即可

2)可以进行接收参数，也可以进行返回数据，在存储过程中，可以传递参数，还可以进行接收返回值

3)减少网络交互，效率提升，如果说涉及到多条SQL，每执行一次都是一次网络传输，如果说封装在存储过程，我们只需要进行网络交互一次就可以了

语法:

创建存储过程:

创建存储过程:create procedure pl()
设置结束分隔符: delimiter $$
beginselect * from student order by id desc;
end;
执行存储过程:call pl();
删除存储过程:drop procedure if exists p1;

查看存储过程:show create procedure p1;
第二种方式:
select * from information_schema.ROUTINES where rount_schema="数据库的名字"l

最后我们需要重新进行指定一下结束符:delimiter ;就可以了

MYSQL中的变量: 

全局变量:全局变量针对于所有的会话

会话变量:会话变量针对于单个会话，在另一个会话窗口就不生效了

在MYSQL中变量一共分成三种，一种是系统变量，一种是用户自定义变量，一种是局部变量

1)系统变量:

1)是由MYSQL进行提供的，不是用户所定义的，属于服务器层面，分为全局变量和会话变量

如何查看系统变量呢？

show variables:查看所有的系统变量

show session variables:查看所有的会话变量

show session/global variables like "xxx%"这是可以通过like进行模糊匹配的方式来进行查询变量

2)查询变量

select @@[session|global] 系统变量名字

3)比如说我想要查询事务自动提交进行操作的指令:show session variables like "auto%";

select @@autocommit

select @@session.autocommit//查看会话级别的自动提交是否开启

3)设置系统变量:

set [session/global] 系统变量名=值

set @@[session/global]+系统变量名=值

set session autocommit=0;

//开关关闭，只要不报错那么就执行成功，这种关闭只是对当前会话设置，不会影响到其他会话

set session autocommit=0;//关闭自动提交
insert into student values(1,"hhhh");
commit;//手动进行提交事务
//set session autocommit=1//事务自动提交

要想设置全局的事务自动提交:set global autocommit=1;数据库重新启动，还是默认事务提交，也就是设置的全局参数会失效

2)用户定义变量:是用户根据自己需要进行定义的变量，用户变量不需要进行提前声明，在用的时候直接还是使用@变量名就可以，其作用域为当前连接

赋值:set @变量名=自定义的值，可以使用=或者是：=

set @myname="admin";
set @mygender:=10；
set @myage:=10,@myhobby:=90;

赋值语句还可以使用select:select @变量名字=XXX

select @mycolor:="生命在于运动";

查看用户自定义变量:

select @myname,@mygender;

我们还可以把某个SQL查询指定的结果赋值给某一个变量:

select count(*) into @Mycount from user;//表示会把从User表中查询出来的结果赋值给Mycount变量

如果说在用户指定自定义变量的时候没有进行初始化操作，那么默认值就是空

3)局部变量:局部变量是根据需要进行定义的在全局内部生效的变量，在我们进行访问之前需要先使用declare来进行声明，可用作存储过程的局部变量和输出参数，局部变量作用的范围是begin到end块

声明方法:
set 变量名=值;
set 变量名:=值;
select 字段名 into 变量名 from 表名

声明全局变量:
create procedure p2()
begindeclare count int default 0;//第一个定义方式select count(*) into count from student;//第二种定义方式select count;//定义第三种方式
end;
call p2();

查看这个变量:select+这个变量

练习:根据定义的分数变量，判断当前分数对应的分数等级:

score>85代表等级为优秀

score>=60并且score<85表示等级为及格

score<60表示为不及格

1)if用于做条件判断:具体的语法是:

if 条件1 then+SQL语句
else if 条件2+SQL语句
else 条件2+SQL语句

根据自己定义的分数变量，来实现这个题:

else if结构可以有多个，可以有，也可以没有:

create procedure p3()begindeclare score int default 58;declare result varchar(10);if score > 85 then set result:="优秀";elseif score>=70 then set result:="良好";else  set result:="不及格";end if;select result;end;

虽然上面的需求我们已经解决了，但是也存在着一些问题，咱们是否可以将score分数动态的传递进来呢？计算出来的分数作为返回值？

参数的类型主要分成下面三种:

1)in 表示该类参数作为输入，也就是调用的时候需要进行传入的值；

2)out表示该类参数作为输出，也就是说该参数作为返回值；

3)inout:表示及可以作为输入参数，也可以作为输出参数；

将上面的案例改成:根据传入的参数score判断当前的分数等级:

create procedure p4(in score int,out result varchar(50))
beginif score>85 then set result:="优秀";elseif score>70 then set result:="及格";else set result:="不及格";
end if;
end;
进行系统调用:
call p4(18,@result);
select @result;

我们还是应该注意:elseif要写在一起，else后面不可以有then

案例:

我们现在将传入的200分制的分数进行换算，换算成百分制，然后进行返回:

create procedure p5(inout score)
begin
set score :=score*0.5;
end;set @score=198;
call p5(@score);select @score

2)case:

语法结构:

casewhen 条件and条件 then SQL语句
else SQL语句
end case;

现在我们根据一个案例，进行判断月份所属的季节:

create procedure p6(in month int)
begindeclare result varchar(60);casewhen 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:="第三季度";else set result:="非法参数";end case;
select concat("您输入的月份是:",month,"，所属的季度是",result);
end;
-----------------------------------------------------------------+
| concat("您输入的月份是:",month,"，所属的季度是",result)               |
+-----------------------------------------------------------------------+
| 您输入的月份是:10，所属的季度是非法参数                               |
+-----------------------------------------------------------------------+

3)while语句:

while循环是有条件的循环控制语句，满足条件之后，才会进行执行里面的SQL语句:

while 条件 do

             SQL语句

end while;

下面我们来进行编写一个具体的案例:

定义局部变量，记录累加后的值

每进行循环一次，就会对n进行减1，如果说最后n减到0，那么就会退出循环

create procedure p7(in n int)
begindeclare total int default 0;
while n>0 doset total:=total+1;set n:=n-1;end while;select total;
end;
call p7(10);

4)repeat:它是有条件的循环控制语句，当满足until声明的条件的时候，则退出循环，也就是说我们会先执行一次逻辑，然后会进行判断逻辑是否满足，如果满足那么会直接退出，如果不满足，那么会直接继续执行循环；

语法:

repeatSQL逻辑;until 条件
end repeat;

我们还是进行累加从1到n的值:

create procedure p8(in n int)
begindeclare total int default 0;repeatset total:=total+n;set n:=n-1;until n<=0
end repeat;
select total;
end;
call p8(10);

5)loop循环:我们可以直接通过loop实现简单的循环，如果我们没有在SQL逻辑中增加退出循环的条件，可以用来实现简单的死循环，Loop可以用来配合两条语句来进行使用:

leavel+循环体的名字(loop):表示退出这个循环体

5.1)计算从1到n累加的所有值:

create procedure p9(in n int)
begindeclare total int default 0;sum:loop              #前面表示的是这个loop循环体的名字,方便与后面进行操作if(n<0) then leave sum    end if      #写上判断条件
set total:=total+n;
set n:=n-1;end loop sum;//表示循环体的范围select total;
end;call(9);

5.2)计算从1到n所有偶数的累加的值，n未传入的参数值:

create procedure p10(in n int)
begin
declare total int default 0;
sum:loop
if n<=0 then leave sum; end if;
if n%2=0 then set total:=total+n; set n:=n-1; 
else  set n:=n-1; end if;
end loop sum;
select total;
end;

create procedure p10(in n int)
begin
declare total int default 0;
sum:loop
if n<=0 then leave sum; end if;if n%2=1 then iterate sum; set n:=n-1;end if;
set total:=total+n;
set n:=n-1;
end loop sum;
select total;
end;

还有一种思路就是说

1)定义局部变量，记录累加之后的值

2)每循环一次，我们就对n进行减1，如果n减到0，我们就直接退出循环

3)每当我们进行累加的数据是奇数，那么直接进入到下一次循环:

现在我们假设这样一种场景:

我们想要在存储过程里面，查询出所有的记录放到一个变量里面，但是这个变量是属于int类型的，所以我们从本质上无法将数据库的所有记录放到一个变量里面

之前的方法是这样的:
create procedure p11()
begindelcare stu_count int default 0;select * into stu_count from student;select stu_count;
end;
call pll();

此时我们是用自己定义的局部变量来进行存储所有数据就不行了

我们这个时候就需要进行使用到游标了:

游标:可以简单认为是一个集合

定义:游标是用来存储查询结果集的数据类型，在存储过程中和函数可以使用游标对结果集进行循环的处理

现在我们有一个业务场景:

我们要根据传入的参数id，来进行查询User表，所有用户年龄小于等于id的用户姓名和密码，并将用户姓名和密码插入到新创建的一张新表里面

create procedure p11(in id int)
begin
//声明变量
declare username varchar(50);
declare password varchar(50);
//1.声明游标,查询储存结果集
declare u_user cursor  for select username,password from User where id

但是此时程序会出现报错:

020001 1329 No data -zero rows fetched ,selected,processed因为我们此时没有进行指定循环的退出条件，所以我们无法进行退出循环，此时我们就需要使用条件处理程序

declare exit handler for sqlstate= '02000' close u_user;
//自动检查状态码并退出游标
//这是声明条件处理程序,当我们的SQL语句抛出的状态码以02开头的时候,我们可以先关闭游标在进行退出
declare exit handler  for not found close u_user;

并且变量的声明一定要在游标之后:

SQLSTATE:表示状态码

SQL WARNING:所有以01开头的SQLSTATE开头的缩写

NOT FOUND:所有以02为开头的SQLSTATE开头的缩写

SQLEXCEPTION:上述两种都没有；​​​​​​​