【大数据系列之MySQL】（三十四）：存储过程的介绍

create procedure 存储过程名（参数列表）
begin
  存储过程体（一组合法的SQL语句）
end
# 参数列表包含三部分
参数列表 参数名 参数类型
IN stuname varchar(20)

delimiter $
create procedure myp1()
begin
  insert into boys values(3, 'gom', 123456);
end $
call myp1()

create procedure myp2(IN beautyName varchar(20))
begin
  select bo.*
  from boys bo
  right join beauty b on bo.id = b.boyfriend_id
  where b.name = beautyName
end
call myp2('柳岩') $

create procedure myp3(IN beautyName varchar(20), OUT boyName varchar(20))
begin
  select bo.boyName INTO boyName
  from boys bo
  inner join beauty b on bo.id = b.boyfriend_id
  where b.name = beautyName
end
call myp3('小昭', @boyName) $

create procedure myp4(IN beautyName varchar(20), OUT boyName varchar(20), OUT userCP INT)
begin
  select bo.boyName, bo.userCP INTO boyName, userCP
  from boys bo
  inner join beauty b on bo.id = b.boyfriend_id
  where b.name = beautyName
end
call myp4('小昭', @boyName, @userCP) $

create procedure myp5(INOUT a INT, INOUT b INT)
begin
  set a=a*2;
  set b=b*2;
end

drop procedure 存储过程名

show create procedure myp2;

create function 函数名(参数列表) returns 返回类型
begin
  函数体
end
# 函数体中一定有return语句，否则会报错，要记得使用delimiter语句设置结束标记
# 返回公司的员工个数
create function myf1() returns int
begin
  declare c int default 0;
  select count(*) into c
  from employees;
  return c;
end $
myf1() $

drop function myf3;
show create function myf3;