死磕数据库系列（十五）：MySQL 存储过程、自定义函数、事务、流程控制的语法、创建和使用

2023-03-16 11:40:20

存储过程

语法：

创建存储过程

drop procedure if exists [存储过程名] ;

delimiter [结束标记]
create procedure [存储过程名]([参数1], [参数2] ...)
begin
    [存储过程体(一组合法的sql语句)]
end [结束标记]

delimiter ;

参数，其格式为 [in|out|inout] parameter_name type

in #表示输入参数；
out #表示输出参数；
inout #表示此参数既可以输入也可以输出；
param_name #表示参数名称；
type #表示参数的类型。

delimiter [结束标记] 本身与存储过程的语法无关，用于表示SQL语句的结束。

后一个命令 delimiter ; 将分隔符改回分号，用于告诉SQL引擎，遇到分号就可以提交了。

为什么要用 delimiter 定义结束标记？

因为 SQL引擎执行时，遇到分号（;）表示一个SQL语句的结束，会把这个SQL进行提交。

但是存储过程和自定义函数，功能比较复杂，里面有好多条的SQL语句，我们希望SQL引擎把这一个存储过程或函数当成一个整体提交（即原子性），而不是每段SQL语句遇到分号（;）就提交，这就无法保证完整性。

为了解决SQL执行引擎遇到分号提交的问题，就要使用 delimiter 定义一个新的结束标记，只不要是分号， SQL引擎就不会提交，这样保证存储过程和自定义函数的整体性。

所以，SQL 的 delimiter 的结束标记不能定义为分号（;），一般使用特殊字符，如$ 、//、/ 等。

begin...end 语句块。在 sql 语句，如果包含多条语句，我们需要把多条语句放到 begin...end 语句块中。begin...end 块可以嵌套，同一组的SQL 代码放到一个 begin...end 块，层层分开，大的好处是便于阅读。如果仅仅一条sql语句，则可以省略 begin end 。

begin...end 相当于 java 语言中的 { } 。如果 if 内只有一条SQL时，{ } 可以省略，同样，begin...end 也可以省略。

public int method(int param){
 int result=;
 if(param==1){    
  result=......
 }else if{param==2}{   
  result=......
 }else{   
  result=......
 }
 return result;
}

public int method(int param){
 int result=;  
 
 // 当 if 内只有一条SQL时，{ } 可以省略
 if(param==1)  
  result=...... 
 else if{param==2}
  result=......
 else
  result=......
 
 return result;
}

调用存储过程

call [存储过程名]([ proc_parameter [,proc_parameter ...]])

call [存储过程名]

说明：

当无参数时，可以省略括号，不写；
当有参数时，不可省略括号。

存储过程修改：修改存储过程，就是删除重建。

删除存储过程： drop procedure [if exists] sp_name

示例

drop table if exists `t_user`;
create table `t_user` (
  `id` int not null auto_increment,
  `name` varchar(20) not null,
  primary key (`id`)
) engine=innodb auto_increment=1 default charset=utf8;

无参数的存储过程

drop procedure if exists myproc1;
delimiter $
create procedure myproc1()
begin   ## 下面只有一条语句，begin end 可以省略
    insert into t_user values(null, 'Jas'),(null, 'Joy');
end $
delimiter ;

调用，并查看结果：

只有一个 in 参数的存储过程

drop procedure if exists myproc2;

delimiter $
create procedure myproc2(in userId int)
begin
    select name from t_user where id = userId;
end $

delimiter ;

调用，并查看结果：

包含 in 参数和 out 参数的存储过程

drop procedure if exists myproc3;

delimiter $
create procedure myproc3(in userId int, out username varchar(20))
begin
    select name into username     # 将查询到的用户名赋值给 username
    from t_user where id = userId;
end $
delimiter ;

调用，并查看结果：

创建存储过程 myproc3，包含一个in参数和一个out参数；
调用时，传入删除的id 和全局变量 @username ；
select @username 输出结果。

包含 inout 参数的存储过程

drop procedure if exists myproc4;

delimiter $
create procedure myproc4(inout a int)
begin
    set a = a * 2;
end $

delimiter ;

调用存储过程，并查询结果：

附：根据时间修改状态：

delimiter $$

use `exam9` $$  ## exam9 是数据库

drop procedure if exists `updateStatus` $$   ## 如果存在，就删除

create  procedure `updateStatus`()
begin
    update exam set `status`="已结束" where  `status` != "已结束" and  (now() - endtime)>  ;
    update exam  set `status`="正在答题"  where   `status` != "已结束" and ( now() - starttime)>= 
    and  (now() - endtime)<=;
    
    update haulinfo set bigstatus="已结束" where  bigstatus != "已结束" and  (curdate() - bigenddate)>; 
    update haulinfo set bigstatus="进行中" where (curdate() - bigenddate)<= and (curdate() - bigbegindate)>=;
    
    update exam set bigstatus=(select bigstatus from haulinfo where bigid=exam.bigid);
end $$

delimiter ;

区别：

存储过程的优点:

存储过程就是把经常使用的 sql语句或业务逻辑封装起来，预编译保存在数据库中，当需要的时候从数据库中直接调用，省去了编译的过程；
提高了运行速度；
同时降低网络数据传输量（不用传一堆sql代码快，而是传一个存储过程名字和几个参数）。

存储过程与函数的区别

一般来说，存储过程实现的功能要复杂一点，而函数的实现的功能针对性比较强。
存储过程可以有返回值也可以没有返回值，而自定义函数必须要返回值，且返回值有且只有一个。
存储过程一般是作为一个独立的部分来执行，而函数可以作为查询语句的一个部分来调用，因此它可以在查询语句中位于 from 关键字的后面。 SQL 语句中不可用存储过程，而可以使用函数。

二、自定义函数 udf（user-defined function ）

自定义函数就像是 abs() 、 concat() 内建函数一样去扩展 mysql 。

所以，udf 是对 mysql 功能的一个扩展。

自定义函数 udf

创建 udf

drop function if exists [函数名];

delimiter [结束标记]
create function [函数名]([参数1], [参数2] ...) returns [返回值类型]
begin 
    [方法体]
    return [返回值];
end [结束标记]

delimiter ;

参数，其格式为param_name type，如username varchar(20) 。

删除 udf： drop function [函数名]

3udf ： select [函数名](param_value, ...)

示例

t_user 表中的数据：

无参数的自定义函数

查询 t_user 中的数据行数，并返回。

drop function if exists myfun1;

delimiter $
create function myfun1() returns int
begin
    declare sum int default ;    # 定义局部变量 sum，默认值为 0
    
    select count(*) into sum    # 将查询的结果赋值给 sum
    from t_user;
    return sum;
end $

delimiter ;

调用结果：

有参数的自定义函数

drop function if exists myfun2;

delimiter $
create function myfun2(userId int) returns varchar(20)
begin
 set @username='';    # 定义系统会话变量
 select name into @username    # 将用户名赋值给 username
 from t_user
 where id = userId;
 return @username;
end $

delimiter ;

查看结果：

复合结构

语法格式

delimiter //
create function if exist deleteById(uid smallint unsigned) returns varchar(20) 
begin
    delete from t_order where id = uid;
    return (select count  (id) from son);
end //

delimiter ;

delimiter 修改默认的结束符

delimiter // 表示将默认的结束符由 ; 改为 // ，以后的sql语句都要以 // 作为结尾。

returns 声明返回值类型

returns varchar(20) 声明返回值是 20位长度的字符串。

returns int 声明返回值 int 。

reurn 定义返回值

reurn 语句 也包含在begin...end 中。

declare 定义局部变量

declare  var_name[,varname]...date_type [default value];

简单来说就是：

declare 变量1[,变量2,... ]变量类型 [default 默认值]

这些变量的作用范围是在begin…end程序中,而且定义局部变量语句必须在begin…end的行定义

示例：

delimiter //
create function addNum(x smallint unsigned, y smallint unsigned) returns smallint
begin
    declare a, b smallint unsigned default 10; ### 定义局部变量
    set  a = x, b = y;
    return a+b;
end //

delimiter ;

上边的代码只是把两个数相加,当然,没有必要这么写,只是说明局部变量的用法,还是要说明下:这些局部变量的作用范围是在 begin...end 程序中

变量

系统变量

全局变量（global）

作用域：针对于所有会话（连接）有效，但不能跨重启。重启后，配置失效。

# 查看所有全局变量
show global variables;

# 查看满足条件的部分系统变量
show global variables like '%char%';

# 查看指定的系统变量的值
select @@global.autocommit;

# 为某个系统变量赋值
set @@global.autocommit=;
set global autocommit=;

会话变量（默认 session ）

作用域：针对于当前会话（连接）有效
系统变量，如果不加 global 和 session ，则默认就是 session 。

# 查看所有会话变量
show session variables;  ## 等价于 show variables;

# 查看满足条件的部分会话变量
show session variables like '%char%';

# 查看指定的会话变量的值
select @@autocommit;
select @@session.tx_isolation;

# 为某个会话变量赋值
set @@session.tx_isolation='read-uncommitted';
set session tx_isolation='read-committed';

自定义变量

用户变量（全局的变量）

作用域：用户变量在当前连接（即当前会话）中都有效。

用户变量的特点：

不需要声明，直接使用，
前后数据类型可以不一样。

示例：

同一个用户变量，前后可以接收不同类型的赋值。

age 是 int
name 是varchar(20)

set @tmpVal = age;   ## 将age的值赋给 @tmpVal
set @tmpVal = name;  ## 将name的值赋给 @tmpVal

声明并初始化：

set @变量名=值;
set @变量名:=值;
select @变量名:=值;

赋值：

## 方式一：一般用于赋简单的值
set 变量名=值;
set 变量名:=值;
select 变量名:=值;

## 方式二：一般用于赋表 中的字段值
select 字段名或表达式 into 变量
from 表;

使用：

select @变量名;

局部变量

作用域：局变变量在 begin end 语句中有效，超过范围即失效。

声明：

declare 变量名 类型 【default 值】;
declare name varchar(20) ;
declare num int default ;

局部变量必须在 begin end 的行。

赋值：

# 方式一：一般用于赋简单的值
set 变量名=值;
set 变量名:=值;
select 变量名:=值;

# 方式二：一般用于赋表 中的字段值
select 字段名或表达式 into 变量
from 表;

使用：

select 变量名

用户变量与局部变量的区别：

事务和回滚点

事务

set autocommit=;    ## 1、取消自动提交
start transaction;   ## 2、开启事务

要执行的操作

commit;     ## 3、提交事务
rollback;   ## 4、回滚事务

存储过程和函数使用事务的格式：

begin  
 set autocommit=;    ## 取消自动提交
 start transaction;   ## 开启事务
  
要执行的操作
 
 commit;   ## 提交事务
end ;

保存点

在事务中，设置保存点，当回滚时，能回滚到这个保存点，但是保存点之前的执行不会回滚。

set autocommit=;    ## 取消自动提交
start transaction; 
..... ## SQL语句
savepoint aa; ## 设置保存点，aa 是自定义的名称，保持
..... ## SQL语句
rollback to aa ## 回滚到保存点。 但是保存点前面执行的SQL执行依然有效。

流程控制

存储过程和函数中可以使用流程控制语句来控制SQL 的执行。

mysql中可以使用 if 、case 、loop、leave、iterate 、repeat和 while 语句来进行流程控制。

每个流程中可能包含一个单独语句，或者是使用 begin...end 构造的复合语句，构造可以被嵌套。

if 函数

语法：

if(条件，值1，值2)

如果条件成立，则返回值1，否则返回值2

特点：可以用在任何位置。

if 条件判断

根据是否满足条件，将执行不同的语句。

特点：
与 if 函数的不同，这个if 条件只能用在 begin end 中！！

if 语法：

if  表达式1 then 语句1;
elseif 表达式2 then 语句2;
...
else 语句n;
end if;

参数说明：

特点：只能用在 begin end 中！！

注意： mysql还有一个if()函数，不同于这里描述的 if 语句。

if 示例：

if age>20 then 
 set @count1=@count1+1;  
elseif age=20 then 
 set @count2=@count2+1;  
else 
 set @count3=@count3+1;  
end if;

说明：

根据age与20的大小关系来执行不同的set语句。
如果age值大于20，那么将count 1的值加1；
如果age值等于20，那么将count 2的值加1；
其他情况将 count3 的值加 1 。
后，if 语句都需要使用 end if 来结束。

case when 条件判断

case when 也用来进行条件判断，其可以实现比 if 更复杂的条件判断。

case when 语法：

类似于 switch

case 表达式
 when 值1 then 结果1或语句1  ## 如果是语句，需要加分号
 when 值2 then 结果2或语句2  ## 如果是语句，需要加分号
 ...
 else 结果n或语句n          ## 如果是语句，需要加分号
end 【case】   ## 如果是放在begin end中，则结尾处需要加上case，如果放在select后面不需要

说明：

如果是在 begin end 中，则需要在每个语句后面加分号，case 结尾处加 case ；
如果放在 select 后面，则每个语句后面不需要加分号， case 结尾处不需要加 case 。

case when 语法2：

case 
 when 表达式1 then 结果1或语句1   ## 如果是语句，需要加分号 
 when 表达式2 then 结果2或语句2   ## 如果是语句，需要加分号
 ...
 else 结果n或语句n   ## 如果是语句，需要加分号
end 【case】           ## 如果是放在begin end中，则结尾处需要加上case，如果放在select后面不需要

说明：

语法1 的when 后面是值，
语法2 的when 后面是表达式，可以进行区间的判断，

这个两个语法大的区别。

示列：

loop 循环

loop 可以使某些特定的语句重复执行，实现一个简单的循环。

但是 loop 本身没有停止循环的语句，必须使用 leave、iterate 等才能停止循环。

loop 语法：

[begin_label:] loop 
 statement_list 
end loop [end_label]

参数说明：

1.begin_label 、end_label 分别表示循环开始和结束的标志，这两个标志必须相同，而且都可以省略；
2.statement_list 表示需要循环执行的语句。

loop 示例

add_num: loop  
 set @count=@count+1;  
end loop add_num ;

说明：

循环执行 count 加1的操作。
因为没有跳出循环的语句，这个循环成了一个死循环。
loop 循环都以 end loop 结束。

leave 跳出循环（break）

leave 用于跳出循环。leave 用于 loop、repeat、while ，中断并跳出循环。

leave 语法：

leave label

参数说明：

label 表示循环的标志。

leave 示例：

add_num: loop 
 set @count=@count+1; 
 if @count=100 then 
  leave add_num ; 
end loop add_num ;

循环执行 count 加1的操作。当 count 的值等于100时，则leave语句跳出循环。

iterate 跳出本次循环（continue）

iterate 也是跳出循环。但是，iterate 语句是跳出本次循环，然后直接进入下一次循环。

iterate 用于 loop、repeat、while 语句跳过本次循环。

iterate 语法：

iterate   label

参数说明：

label 表示循环的标志。

iterate 示例：

add_num: loop
    set @count=@count+1;
    if @count=100 then
     leave add_num ;
    else if mod(@count,3)= then
     iterate add_num;
    select  from employee ;
end loop add_num ;

说明：

循环执行 count 加1的操作。
当 count 值为100时结束循环。
如果 count 的值能够整除3，则跳出本次循环，不再执行下面的select语句。

leave 和 iterate 的区别

相同点：

leave 和 iterate 都用来跳出循环语句，但两者的功能是不一样的。

不同点：

leave 是跳出整个循环，然后执行循环后面的程序。
iterate 是跳出本次循环，然后进入下一次循环。

repeat （先）循环

repeat 是有条件控制的循环。当满足特定条件时，就会跳出循环语句。

repeat 语法：

repeat 
 statement_list 
 until search_condition    # until 后面没有分号（;），否则 报错
end repeat;

参数说明：

1.statement_list 表示循环的执行语句；
2.search_condition 表示结束循环的条件，满足该条件时循环结束。

repeat 示例：

repeat 
 set @count=@count+1; 
 until @count=100 
end repeat ;

循环执行count 加1的操作。

当 count 值为10 0时结束循环。

repeat循环都用end repeat结束。

while （先判断，再）循环

while 也是有条件控制的循环语句。但while 和 repeat 是不一样的。

while 是当满足条件时，执行循环内的语句。

while 语法：

while   search_condition do 
 statement_list 
end while ;

参数说明：

1.search_condition 表示循环执行的条件，满足该条件时循环执行；
2.statement_list 表示循环的执行语句。

while 示例：

while   @count<100 do 
 set @count=@count+1; 
end while ;

循环执行count 加1的操作。
如果 count 值小于100时执行循环；
如果 count 值等于100了，则跳出循环。
while 循环需要使用end while 来结束。

循环示例

loop 循环的示例

delimiter $$

drop procedure if exists `sp_testloop` $$

create  procedure `sp_testloop`(
 in p_number int, #要循环的次数 
 in p_startid int #循环的起始值
)
begin  
    declare v_val int default ;  
    set v_val=p_startid;

    loop_label:  loop   #循环开始
        set v_val=v_val+1; 
         
        if(v_val>p_number)then
            leave  loop_label;  # 终止循环
        end if;
    end loop; 
    select concat('testloop_',v_val) as tname;
end $$

delimiter ;

call sp_testloop(1000,);

while循环的示例

delimiter $$

drop procedure if exists `sp_test_while`$$

create  procedure `sp_test_while`(
 in p_number int, #要循环的次数
 in p_startid int #循环的起始值
)
begin  
 declare v_val int default ;  
 set v_val=p_startid;
 outer_label:  begin  #设置一个标记
  while v_val<=p_number do  
   set v_val=v_val+1;  
   if(v_val=100)then
    leave  outer_label;  #满足条件，终止循环，跳转到 end outer_label标记
   end if;
  end while;
  
  select '我是while外，outer_label内的SQL';## 这句SQL在outer_label代码块内，所以level后，这句SQL将不会执行；
 
 #只要是在outer_label代码块内 任意位置 Leave outer_label,那么Leave后的代码将不再执行
 end outer_label;
 select concat('test',v_val) as tname;
end$$

delimiter ;

call sp_test_while(1000,);

repeat 循环的示例

delimiter $$
drop procedure if exists `sp_test_repeat`$$

create  procedure `sp_test_repeat`(
 in p_number int, #要循环的次数
 in p_startid int #循环的起始值
)
begin  
 declare v_val int default ;  
 set v_val=p_startid;

 repeat  #repeat循环开始
  
  set v_val=v_val+1; 
  until v_val > p_number   #终止循环的条件，注意这里不能使用';'分号，否则报错     
    
 end repeat; #循环结束 
  
 select concat('test',v_val) as tname;
end$$

delimiter ;

call sp_test_repeat(1000,);

来源：https://xiaojin21cen.blog.csdn.net/
article/details/105596615

分享好友

分享这个小栈给你的朋友们，一起进步吧。

数据库专区

创建时间：2020-06-16 18:15:35

数据库是存放数据的仓库。它的存储空间很大，可以存放百万条、千万条、上亿条数据。但是数据库并不是随意地将数据进行存放，是有一定的规则的，否则查询的效率会很低。当今世界是一个充满着数据的互联网世界，充斥着大量的数据。即这个互联网世界就是数据世界。数据的来源有很多，比如出行记录、消费记录、浏览的网页、发送的消息等等。除了文本类型的数据，图像、音乐、声音都是数据。 [2]

展开

订阅须知

• 所有用户可根据关注领域订阅专区或所有专区

• 付费订阅：虚拟交易，一经交易不退款；若特殊情况，可3日内客服咨询

• 专区发布评论属默认订阅所评论专区（除付费小栈外）

技术专家

查看更多

小雨滴
专家
gaokeke123

专家

存储过程

语法：

为什么要用 delimiter 定义 结束标记？

示例

无参数的存储过程

只有一个 in 参数的存储过程

包含 in 参数和 out 参数的存储过程

包含 inout 参数的存储过程

附：根据 时间 修改 状态：

区别：

存储过程的优点:

存储过程 与 函数 的区别

二、自定义函数 udf（user-defined function ）

自定义函数 udf

创建 udf

示例

无参数的自定义函数

有参数的自定义函数

复合结构

语法格式

delimiter 修改默认的结束符

returns 声明返回值类型

reurn 定义 返回值

declare 定义局部变量

示例：

变量

系统变量

全局变量 （global）

会话变量（默认 session ）

自定义变量

用户变量 （全局的变量）

用户变量的特点：

示例：

声明并初始化：

赋值：

使用：

局部变量

声明：

赋值：

使用：

用户变量与 局部变量的区别：

事务和回滚点

事务

存储过程和函数使用事务的格式：

保存点

流程控制

if 函数

语法：

if 条件判断

if 语法：

参数说明：

case when 条件判断

case when 语法：

说明：

case when 语法2：

说明：

示列：

loop 循环

loop 语法：

参数说明：

loop 示例

说明：

leave 跳出循环（break）

leave 语法：

参数说明：

leave 示例：

iterate 跳出本次循环（continue）

iterate 语法：

参数说明：

iterate 示例：

leave 和 iterate 的区别

相同点：

不同点：

repeat （先）循环

repeat 语法：

参数说明：

repeat 示例：

while （先判断，再）循环

while 语法：

参数说明：

为什么要用 delimiter 定义结束标记？

附：根据时间修改状态：

存储过程与函数的区别

reurn 定义返回值

全局变量（global）

用户变量（全局的变量）

用户变量与局部变量的区别：