mysql储存过程和函数、流程控制（if，case，while，loop，repeat）的运用

#先创建一个表
create table student (name varchar(10),gender enum(‘M’,‘F’),grade set(‘1’),english int(3),chinese int(3),math int(3));
insert into student values (‘zhao’,‘F’,‘1’,78,89,97),(‘qian’,‘M’,‘1’,72,98,94),(‘sun’,‘M’,‘1’,62,77,94),(‘nv’,‘F’,‘1’,54,77,91),(‘zhou’,‘F’,‘1’,87,77,71),(‘wu’,‘M’,‘1’,42,56,61),(‘zheng’,‘F’,‘1’,42,46,91),(‘wang’,‘F’,‘1’,42,96,100)
select * from student

#一个create procedure的例子：得出表格总人数
create procedure student_number(out number int)
begin
select count(*) into number from student;
end;
call student_number(@number)
select @number
#@是用户自己声明的用户变量，@@是系统变量。

#一个create procedure的例子：输入分数，得出是这一分数的人数
create procedure student_number1(in p_english int,out number1 int)
begin
select count(*) into number1 from student where english=p_english;
end;
call student_number1(78,@number1)
select @number1

#一个create procedure的例子:输入姓名，得出各科分数
create procedure student_score(in p_name varchar(10),out p_english int(3),out p_chinese int(3),out p_math int(3))
begin
select english,chinese,math into p_english,p_chinese,p_math from student where name=p_name;
select p_english,p_chinese,p_math;
end;
call student_score(‘zhao’,@p_english,@p_chinese,@p_math)#只用更改 ‘ ’ 内的姓名，即可得出他的分数。
#一个create procedure的例子:输入姓名，得出各科总分
create procedure student_score1(in p_name varchar(10),out scoretotal int(3))
begin
select english+chinese+math into scoretotal from student where name=p_name;
select scoretotal;
end;
call student_score1(‘qian’,@scoretotal)
#select english+chinese+math from student where name=‘wang’
#select sum(math) from student where gender=‘M’

#删除、显示储存过程
drop procedure student_score1
show procedure status
show create procedure student_score1

#变量声明和变量赋值
create procedure p1()
begin
declare p_score int(3) default 0;
set p_score=10;
select p_score;
end;
call p1()

#流程控制
##if if(条件,值1，值2) 应用在begin end中或外面
select if(8>=7,‘yes’,‘no’)
###将if放入 begin end中
create procedure p_2(in p_english int(3))
begin
select if(p_english>=60,‘pass’,‘no pass’);
end;
call p_2(70)
call p_2(55)

create procedure p_3(in p_name varchar(10))
begin
declare p_english int(3);
select english into p_english from student where name=p_name;
select if(p_english>=60,‘pass’,‘no pass’);
end;
call p_3(‘wang’)

##if 条件1 then 语句1；elseif 条件2 then 语句2；***；else 语句n 必须放在begin end中
#案例 student表中english成绩>=90，返回excellence；80-90之间，返回good；60-80之间，返回common；否则，返回no pass。
create procedure p_4(in p_name varchar(10),out result varchar(10))
begin
declare p_english int(3); #此处不设置一个声明变量是得不出结果的
select english into p_english from student where name=p_name;
if p_english>=90 then set result=‘excellent’;
elseif p_english>=80 and p_english<90 then set result=‘good’;
elseif p_english>=60 and p_english<80 then set result=‘common’;
else set result=‘no pass’;
end if;
select result;
end;
call p_4(‘zhao’,@result)
#把上面语句用function写出来
create function f_1(p_name varchar(10)) returns varchar(10)
begin
declare p_english int(3);
declare result varchar(10);
select english into p_english from student where name=p_name;
if p_english>=90 then set result=‘excellent’;
elseif p_english>=80 then set result=‘good’; #加不加and都是一样的
elseif p_english>=60 then set result=‘common’;
else set result=‘no pass’;
end if;
return result;
end;
select f_1(‘zhao’)

#实现这一案例：创建存储过程，如果工资<2000,则删除，如果5000>工资>2000,则涨工资1000，否则涨工资500
#1、首先建立一个工资表
create table employees(name varchar(255),gender enum(‘M’,‘F’),years int(3),workyears int(2),salary int(6));
insert into employees values (‘赵一’,‘F’,26,3,3500),(‘姚二’,‘M’,32,8,9000),(‘张三’,‘M’,30,8,5000),(‘李四’,‘F’,32,7,6000),(‘王五’,‘M’,28,2,4000),(‘贾六’,‘M’,35,12,10000),(‘刘七’,‘F’,24,1,1800),(‘王八’,‘M’,26,3,3200)
select * from employees;

create procedure p_salary(in p_salary int(6))
begin
if p_salary<2000 then delete from employees where salary=p_salary;
elseif p_salary>=2000 and p_salary<5000 then update employees set salary=salary+1000 where salary=p_salary;
else update employees set salary=salary+500 where salary=p_salary;
end if;
end;
call p_salary(3500)
select * from employees;

drop procedure salary

##case 应用在begin end 中或外面
#情况一: case;变量或表达式 when 值1 then 语句1;when 值2 then 语句2;…else 语句n;end case;
#情况二：case; when 条件1 then 语句1;when 条件2 then 语句2;else 语句n;end case:
create function f_3(f_name varchar(10)) returns varchar(10)
begin
declare result varchar(10);
declare f_english int(3);
select english into f_english from student where name=f_name;
case
when f_english>=90 then set result=‘excellence’;
when f_english>=80 and f_english<90 then set result=‘good’;
when f_english>=60 and f_english<80 then set result=‘common’;
else set result=‘no pass’;
end case;
return result;
end;
select f_3(‘nv’)
select * from student
drop function f_3

#循环语句 分类：while loop repeat 循环控制：iterate类似于 continue，结束本次循环，继续下一次；leave 类似于break，跳出，结束当前所在的循环。
#while 循环条件 do 循环体; end while;
#loop 循环体; end loop; 死循环（一直循环下去）
#repeat 循环体; until 结束循环的条件 end repeat;

#案例：批量插入，根据次数插入到admin表中多条记录
create table admin(usename int(10),password int(6));
alter table admin modify usename varchar(255);

create procedure p_6(in x int(10))
begin
declare i int(10) default 1;
while i<=x do insert into admin values (concat(‘rose’,i),666);
set i=i+1;
end while;
end;
call p_6(200);
select * from admin;

drop procedure p_6
delete from admin;

create procedure p_7()
begin
declare i int(2) default 1;
loop insert into admin values (concat(‘rose’,i),666);
set i=i+1;
end loop;
end;
call p_7();

create procedure p_8(in x int(10))
begin
declare i int(10) default 1;
repeat insert into admin values (concat(‘rose’,i),666);
set i=i+1;
until i=x end repeat; #x后面不要换行
end;
call p_8(100)
select * from admin;#99行
delete from admin
#案例：批量插入，根据次数插入到admin表中多条记录，但是去掉偶数行。
create procedure p_9(in x int(10))
begin
declare i int(10) default 0;
a:while i<=x do set i=i+1;
if mod(i,2)=0 then iterate a; #!= 为去掉奇数行
end if;
insert into admin values (concat(‘rose’,i),666);
end while a;
end;
call p_9(100);
select * from admin;
delete from admin;
drop procedure p_9

#案例：批量插入，根据次数插入到admin表中多条记录，运用leave，loop只插入100行。
create procedure p_10(in x int(10))
begin
declare i int(2) default 1;
a:loop insert into admin values (concat(‘rose’,i),666);
set i=i+1;
if i=x then leave a;
end if;
end loop;
end;
call p_10(100);
select * from admin;