day12：存储过程和函数

学后端的菜妹

已于 2023-02-22 17:40:20 修改

阅读量164

点赞数

文章标签： sql 数据库 mysql

于 2023-02-20 12:46:53 首次发布

本文链接：https://blog.csdn.net/weixin_60566874/article/details/129122171

版权

存储过程和函数

存储过程

含义：一组预先编译好的sql语句的集合，理解成批处理语句

1、创建的语法

create procedure 存储过程名（参数列表）

begin

存储过程体（一组合法的sql语句）

end

注意：

参数列表包含三个部分（参数模式参数名参数类型）
如果存储过程体仅仅只有一句话，begin end 可以省略，存储过程体中的每句sql语句的结尾要求必须加分号，存储过程的结尾可以使用delimiter重新设置（delimiter 结束标记）

例如：in stuname varchar（20）

参数模式：

in：该参数可以作为输入，也就是该参数需要调用方传入值
out：该参数可以作为输出，也就是该参数可以作为返回值
inout：该参数既可以作为输入也可以作为输出，也就是该参数既需要传入值，又可以返回值

2、调用语法

call 存储过程名（实参列表）；

空参列表

案例：插入到admin表中5条记录
delimiter $
create producer 存储过程名()
begin
    insert into admin(username,sex) values('1','nan'),('2','nv'),...;
end $
#调用
call 存储过程名()$

调用带in模式参数的存储过程

案例：创建存储过程实现根据女神名查询对应男神信息
create producer 存储过程名(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 存储过程名('女神名')$

调用带out模式参数的存储过程

案例：创建存储过程实现根据女神名返回对应男神信息
create producer 存储过程名(in beautyname varchar(20),out boyname varchar(20))
begin
    select bo.boynamme into boyname
    from boys bo
    inner join beauty b
    on bo.id=b.boyfriend_id
    where b.name=beautyname
end $
#调用
set @bname$
call 存储过程名('女神名',@bname)$
select @bname$

案例：创建存储过程实现根据女神名返回对应男神名及魅力值
create producer 存储过程名(in beautyname varchar(20),out boyname varchar(20),out usercp int)
begin
    select bo.boynamme,bo.usercp into boyname,usercp
    from boys bo
    inner join beauty b
    on bo.id=b.boyfriend_id
    where b.name=beautyname
end $
#调用
call 存储过程名('女神名',@bname,@usercp)$
select @bname,@usercp$

调用带inout模式参数的存储过程

案例：传入a和b两个值，最终a和b都翻倍并返回
create producer 存储过程名(inout a int，inout b int)
begin
    set a=a*2;
    set b=b*2;
end$
#调用
set @m=10$;
set @n=20$;
call 存储过程名(@m,@n)$
select @m,@n$

存储过程的删除

语法：drop producer 存储过程名；

存储过程信息的查看

语法：show create producer 存储过程名；

函数

含义：一组预先编译好的sql语句的集合，理解成批处理语句

区别：存储过程可以有0个或多个返回值，适合做插入、批量更新；函数只有一个返回值，适合做处理数据返回一个结果

创建语法

create function 函数名(参数列表)  returns 返回类型
begin
    函数体
end

调用语法：select 函数名(参数列表)

注意：

参数列表包含两部分：参数名参数类型
函数体：肯定会有return语句，如果没有会报错
函数体只有一句话时可以省略begin end
使用delimiter语句设置结束标记 delimiter $

#1、无参有返回
create function 函数名() returns int
begin
    declare c int default 0;#定义变量
    select count(*) into c;#赋值
    from employees;
    return c;
end $
select 函数名()$

#2、有参有返回
根据员工名，返回他的工资
create function 函数名(empname varchar(20)) returns int
begin
    set @sal=0；#定义用户变量
    select salary into @sal #赋值
    from employees
    where last_name=empname；
    return @sal;
end $
select 函数名('员工名')$

查看函数

show create function 函数名；

删除函数

drop function 函数名；

流程控制结构

顺序结构：程序从上往下依次执行
分支结构：程序从两个或多个路径中选择一条去执行
循环结构：程序在满足一定条件的基础上，重复执行一段代码

1、分支结构

if函数

语法：if(表达式1，表达式2，表达式3)

执行顺序：表达式1成立，则if函数返回表达式2的值，否则返回表达式3的值

应用：任何地方

case结构

语法1：case 变量|表达式|字段

when 要判断的值 then 返回的值1或语句1；

when。。。

else 要返回的值n或语句n；

end case；

语法2：case

when 要判断的条件1 then 返回的值1或语句1；

when。。。

else 要返回的值n或语句n；

end case；

if结构

语法：if 条件1 then 语句1；

elseif 条件2 then 语句2；

。。。

elseif 条件n then 语句n；

end if；

2、循环结构

while
loop
repeat

循环控制：

iterate类似于continue，继续，结束本次循环，继续下一次循环

leave类似于break，跳出，结束当前循环

1、while

语法：

【标签：】while 循环条件 do

循环体；

end while 【标签：】

#案例：批量插入，根据次数插入到admin表中多条记录
create procedure 存储过程名(in insertcount int)
begin 
    declare i int defaule 1;
    while i<=insertcount do
      insert into admin(username,sex) values(concat('1',i),'nan');
      set i=i+1;
      end while;
end $
call 存储过程名(条数)$

#案例：批量插入，根据次数插入到admin表中多条记录,如果次数>20则停止
create procedure 存储过程名(in insertcount int)
begin 
    declare i int defaule 1;
    a:while i<=insertcount do
      insert into admin(username,sex) values(concat('1',i),'nan');
      if i>=20 then leave a;
      end if;
      set i=i+1;
      end while a;
end $
call 存储过程名(条数)$

2、loop

语法：

【标签：】loop

循环体；

end loop 【标签：】

3、repeat

语法：

【标签：】repeat

循环体；

until 结束循环的条件

end repeat【标签：】