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

2023-03-16 00:00:00 语句 变量 循环 存储过程 分号


存储过程

语法:

创建存储过程

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(20not 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 intout 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 intreturns 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 unsignedreturns varchar(20
begin
    delete from t_order where id = uid;
    return (select count  (idfrom 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 unsignedreturns 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 )

作用域:针对于当前会话(连接)有效
系统变量,如果不加 globalsession ,则默认就是 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



相关文章