mysql存储过程（2）

一、if语句

（1）if单分支
格式“：
if 条件 THEN
sql1 如果满足则
ELSE
sql2 否则运行
end if; 结束if语句

案例：
delimiter //
drop PROCEDURE if EXISTS cc2 ;
create PROCEDURE cc2(in x int )

BEGIN
if x>2 THEN
select * from emp ;
ELSE
select * from dept ;
end if;
END
//
call cc2(5)

（2）if多分支
格式：
if 条件1 THEN
slq1
ELSE if 条件2 then
slq2
ELSE if 条件3 then
sql3
ELSE
sql4
end if;
END IF;
end if;

案例
delimiter //
drop PROCEDURE if EXISTS cc2 ;
create PROCEDURE cc2(in x int )
BEGIn
if x=2 THEN
select * from emp ;
ELSE if x=10 then
select * from dept ;
ELSE if x=100 then
select * from student ;
ELSE
select * from sc ;
end if;
END IF;
end if;
END
//

call cc2(100)

作业：

建表语句：
create table student2(
id int primary key ,
name char(20),
sex char(10),
age int(3),
mobile char(20),
class char(10),
english int(10),
chinese int(10),
math int(10)
)engine=innodb default charset=utf8;

insert into student2 values
(1,'小红','女',23,'13813828824','1719',77,88,98),
(2,'小明','男',23,'13713713711','1720',56,66,55),
(3,'小李','男',23,'15915913911','1719',78,64,87),
(4,'小张','男',23,'15915913912','1720',77,76,77),
(5,'小白','女',24,'15915913913','1719',90,89,98),
(6,'小陈','女',19,'15915913914','1719',84,100,81),
(7,'小钱','女',20,'15915913915',null,45,99,93);

根据student学生表去写
1.当传入的参数（大于0）小于等于表里面数据的条数时，则根据分组显示班级的总成绩
2.当传入的参数大于表里面数据的条数时，则统计表里面的数据有多少条
3.当传入其他，则查询表里面的所有数据
案例：

delimiter //
drop PROCEDURE if EXISTS cc2 ;
create PROCEDURE cc2(in x int )
BEGIN
DECLARE i int DEFAULT (select count() from student2);
if x<=i and x>0 THEN
select sum(english+chinese+math) from student2 group by class ;
ELSE IF x>i THEN
select count() from student2;
ELSE
select * from student2;
end if;
end if;
END
//

call cc2(-1)

delimiter//
drop procedure if exists cc2;
create procedure cc2(in x int)
BEGIN
if 0<x and x<(select count() from student2) THEN
select class,sum(english+chinese+math) from student2 group by class;
else if x>(select count() from student2) THEN
select count(*) from student2;
else
select * from student2;
end IF;
end IF;
end //

call cc2(-1)