mysql存储过程,函数,变量与流程控制

文章链接:https://codemouse.online/archives/354846877687

存储过程

存储过程和自定义函数是事先经过编译并存储在数据库中的一段SQL语句集合
存储过程参数可以使用 in out inout 函数只能使用in
函数必须要有返回值 存储过程没有返回值

• 无参存储过程

--查找平均花费高的两个客户

create procedure getMaxAvgCost()
begin
    select customer,avg(price)
    from orders
    group by customer
    order by avg(price) desc
    limit 2;
end;

--调用存储过程
call getMaxAvgCost();
--修改
alter procedure getMaxAvgCost();
--查看存储过程信息
show create procedure getMaxAvgCost;
--删除存储过程
drop procedure getMaxAvgCost;

• 带参存储过程

--获取指定日期最大单笔订单

create procedure getMaxByDate(in odate date)
begin
    select customer,max(price)
    from  orders
    where orderdate=odate;
end;
使用:call getmaxbydate('2018-9-21');

--使用存储过程插入数据

create procedure insertTbOrders(
in oid int,in oprice int,in odate date,
in ocus varchar(22))
begin
insert into orders values(oid,oprice,odate,ocus);
select * from orders where id=oid;
end;

call insertTbOrders(8,400,'2018-9-28','danny');
call insertTbOrders(7,200,'2018-9-28','danny');

--删除
create procedure deleteOrders(in oid int)
begin
delete from orders where id=oid;
end;

call deleteOrders(7);

变量

数据在内存中存储可以变化的值或者一个名字
局部变量
声明用declare,可以用default来指定默认值

create procedure getsum(in a int, in b int)
begin
declare c int default 0;
set c=a+b;
select c as '和';
end;

call getsum(21,34);

--用户变量
作用于当前整个连接
set @x=10;
set @y=24;
select @sum:=(@x+@y) as sum;

--会话变量
作用域限于当前客户端连接
show session variables;

--全局变量
作用域为整个服务,但不能跨重启(一个@是用户变量,两个@是全局)
select @@version;

自定义函数(可以省略in)

返回值写返回类型

create function getGrade(mark int)
returns varchar(20)
begin
return(
    case floor(mark/10)
    when 5 then '不及格'
    when 6 then '继续努力'
    when 7 then '成绩良好'
    else '成绩优秀'
    end
);
end;

select getGrade(100);

流程控制

• begin.... end程序块语句
  将多条sql语句封装起来构成程序块

create procedure test1()
begin
declare x int;
declare y date;
begin
  set x=100;
  set y='2018-9-28';
  select x,y;
end;
end;

call test1;

• if判断语句
  用于条件判断,if语句要用end if结束

create procedure test2( in x int)
begin
if x>100 then
  select 'perfect';
elseif x=100 then
  select 'great';
else select 'good';
end if;
end;

• case分支语句
  计算条件列表并返回多个可能结果表达式

create procedure test3(in score int)
begin
case score
when 1 then
  select 'A';
when 2 then
  select 'B';
when 3 then
  select 'C';
else select 'D';
end case;
end;

case test3(2);

• while循环语句
  设置重复执行sql语句或语句块的条件,只要指定的条件为真,就重复执行

create procedure test4(out sum int)
begin
declare i int default 1;
declare s int default 0;
while i<=10 do
  set s=s+i;
  set i=i+1;
end while;
set sum=s;
end;

call test4(@s);
select @s;

• loop循环语句
  没有循环条件 通过leave退出循环(leave相当于break)

create procedure test5()
begin
declare i int default 0;
s:loop
  set i=i+1;
  if i=11 then
   leave s;
  end if;
end loop;
select i;
end;

• repeat循环语句
  先执行一次循环体,在判断循环条件,为真退出循环,否则继续执行循环

create procedure test6()
begin
declare i int default 0;
repeat
  set i=i+1;
until i>11
end repeat;
select i;
end;