mysql中的存储过程
drop procedure if exists dcs;#增强健壮性 ,如果dcs
create procedure dcs(n int) #创建一个存储过程,名字叫dcs
begin
#存储过程的函数体,也可以理解为SQL语句的集合
declare i int(20) default(select count(*) from qq);
drop table if exists qq;
create table qq(id int(20) primary key auto_increment,score int(20));
insert into qq values(1,99),(2,65);
insert into qq values(3,23),(4,98);
insert into qq values(5,66),(6,77);
insert into qq values(7,88),(8,49);
#select *from qq;
#select *from qq where id=1;
#select *from qq where id=n;
/*if n=0 then
select count(*) from qq;
else
select max(score) from qq;
end if;*/
if n=0 then
select count(*) from qq;
else if n>0 && n<8 then
select max(score) from qq;
else if n>=8 then
select *from qq order by scroe desc;
else
select *from qq;
end if;
end if;
end if;
end #存储过程的结束
call dcs(5) #调用dsc()这个过程,注意创建跟调用不能同时进行,先执行创建在执行调用
|
|