#先创建一个表 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;
