MySQL 存储过程事务示例 mysql存储过程开启事务

转载

mob6454cc743894 2024-08-07 13:22:48

文章标签 MySQL 存储过程事务示例 mysql 数据库存储过程异常处理 文章分类 MySQL 数据库

这篇文章，主要介绍MySQL之存储过程、异常处理、事务管理的使用。

一、MySQL存储过程

1.1、MySQL存储过程介绍

1.2、存储过程

（1）声明语句结束符

（2）创建存储过程

（3）存储过程开始和结束符号

（4）变量定义

（5）变量赋值

（6）调用存储过程

（7）参数

（8）存储过程注释

（9）查看存储过程状态

（10）执行动态字符串SQL

1.3、存储过程相关操作

（1）查看数据库所有存储过程

（2）查看存储过程详细内容

（3）修改存储过程

（4）删除存储过程

1.4、存储过程控制语句

（1）变量作用域

（2）条件语句

（3）循环语句

（4）while循环

（5）repeat循环

（6）loop循环

（7）iterate迭代循环

1.5、游标

（1）声明游标

（2）打开游标

（3）循环获取数据

（4）关闭游标

（5）声明NOT FOUND程序

（6）mysql游标工作流程

（7）完整游标操作案例

1.6、事务

（1）开启事务

（2）事务提交

（3）事务回滚

1.7、异常处理

（1）声明异常处理

（2）异常时候处理事务

一、MySQL存储过程

1.1、MySQL存储过程介绍

mysql5.0开始支持存储过程。
存储过程：完成特定功能的SQL数据集的集合，经过编译后创建在数据库中。

存储过程优点

可以封装并且隐藏复杂的业务逻辑。
可以接收输入参数，输出参数。
通过call来调用存储过程。
可以用于业务逻辑的数据校验。

存储过程缺点

不同数据库，存储过程语法不同，切换数据库后，需要重写存储过程。
存储过程性能与不同的数据库系统有关。

1.2、存储过程

（1）声明语句结束符

DELIMITER $$ 或者 DELIMITER // 作用：告诉数据库，存储过程中的分号（；）都使用$$、//来替换。

IN修饰，表示是输入参数
OUT修饰，表示输出参数
INOUT修饰，表示既是输入参数又是输出参数
存储过程中，SQL语句必须使用分号（；）结尾。
在定义过程时，使用 DELIMITER $$ 命令将语句的结束符号从分号 ; 临时改为两个 $$，使得过程体中使用的分号被直接传递到服务器，而不会被客户端（如mysql）解释。

DELIMITER $$
CREATE PROCEDURE sp_name ([IN/OUT/INOUT] proc_param1,[IN/OUT/INOUT] proc_param2...)
BEGIN
    SQL语句编写地方
END $$

（2）创建存储过程

CREATE PROCEDURE sp_name ([IN/OUT/INOUT] proc_param1,[IN/OUT/INOUT] proc_param2...)

（3）存储过程开始和结束符号

BEGIN......END可以多层嵌套使用，每个嵌套块及其中的每条语句，必须以分号结束，表示过程体结束的begin-end块(又叫做复合语句compound statement)，则不需要分号。

BEGIN
　　BEGIN
　　　　BEGIN
　　　　　　statements; 
　　　　END
　　END
END

（4）变量定义

局部变量，必须定义在每个存储过程最开始部分。
用户变量：使用@修饰（set @p = 1），用户变量，只能当前创建用户才能访问。
全局变量：使用@@修饰（set @@p = 2），全局变量，所有数据库用户都可以访问。

// 语法
DECLARE 变量名称 数据类型;
DECLARE 变量名称 数据类型 default 默认值;

// 案例
DECLARE p_param int default 0;

（5）变量赋值

赋值格式：SET 变量名 = 表达式值

SET @p_param = 1;    // 用户变量，使用@声明用户变量，两个@@声明全局变量
// 或者
SET p_param = 1;    // 局部变量
// 或者
SELECT field_name INTO p_param from ....;
// 同时赋值多个
SELECT field_name1,field_name2.... INTO p_param,p_param2... from ....;

（6）调用存储过程

call sp_name(参数);

（7）参数

IN 输入参数：表示调用者向过程传入值（传入值可以是字面量或变量）
OUT 输出参数：表示过程向调用者传出值(可以返回多个值)（传出值只能是变量）
INOUT 输入输出参数：既表示调用者向过程传入值，又表示过程向调用者传出值（值只能是变量）
如果存储过程没有参数，也必须在过程名后面写上小括号。
确保参数的名字不等于列的名字，否则在过程体中，参数名被当做列名来处理。

CREATE PROCEDURE 存储过程名([[IN |OUT |INOUT ] 参数名 数据类形...])

（8）存储过程注释

mysql存储过程，允许两种注释：

两个横杆：--，单行注释。
c语法风格注释：/**/ 多行注释。

-- 单行注释
/* 多行注释 */

（9）查看存储过程状态

SHOW PROCEDURE STATUS LIKE 存储过程名;
可以在最后添加 \G ，可以格式化查看。
在命令行界面查看

SHOW PROCEDURE STATUS LIKE '存储过程名称' \G;

（10）执行动态字符串SQL

动态字符串中，可以拼接参数、动态分组条件、动态字段等等。

-- 动态SQL字符串
SET V_SQL = CONCAT("SELECT * FROM test;");
-- 动态SQL字符串赋值给用户变量
SET @V_STR_SQL = V_SQL;
-- 预编译SQL字符串
PREPARE STMT FROM V_SQL;
-- 执行SQL语句
EXECUTE STMT;
-- 释放预处理语句
DEALLOCATE PREPARE STMT;

1.3、存储过程相关操作

（1）查看数据库所有存储过程

select name from mysql.proc where db='数据库名';
或者
select routine_name from information_schema.routines where routine_schema='数据库名';
或者
show procedure status where db='数据库名';

（2）查看存储过程详细内容

SHOW CREATE PROCEDURE 数据库.存储过程名;

（3）修改存储过程

ALTER PROCEDURE

（4）删除存储过程

DROP PROCEDURE IF EXISTS sp_name;

1.4、存储过程控制语句

（1）变量作用域

内部的变量在其作用域范围内享有更高的优先权，当执行到 end。变量时，内部变量消失，此时已经在其作用域外，变量不再可见了，应为在存储过程外再也不能找到这个申明的变量，但是你可以通过 out 参数或者将其值指派给会话变量来保存其值。

（2）条件语句

条件语句，有两种：

if....then....else...end if
case....when....then....else....end case

IF var = 0 THEN
    SQL语句
ELSE 
    SQL语句
END IF;
-- 第一种
CASE VAR
WHEN 0 THEN 
    SQL语句;
WHEN 1 THEN 
    SQL语句;
ELSE
    SQL语句;
END CASE;

-- 第二种
CASE 
WHEN VAR = 0 THEN 
    SQL语句;
WHEN VAR = 1 THEN 
    SQL语句;
ELSE
    SQL语句;
END CASE;

（3）循环语句

循环语句，四种循环：

while....end while
repeat.....end repeat
loop....end loop
iterate

（4）while循环

条件为TRUE时候，执行循环。

// 语法
while 条件 do
    循环语句
end while;

// 案例
set var=0;  
while var<6 do  
    insert into t values(var);  
    set var=var+1;  
end while;

（5）repeat循环

条件为FALSE时候，执行循环。

// 语法
repeat
    循环语句
util 条件
end repeat;

// 案例
set v=0;  
repeat  
    insert into t values(v);  
    set v = v+1;  
until v >= 5  
end repeat;

（6）loop循环

loop循环，不需要初始条件，也不需要结束条件。
loop循环，通过leave关键字离开循环。
leave关键字，类似于java中的break。

set v=0;  
LOOP_LABLE:loop  
    insert into t values(v);  
        set v=v+1;  
    if v >=5 then 
        leave LOOP_LABLE;    -- 离开循环
    end if;  
end loop;

（7）iterate迭代循环

ITERATE 通过引用复合语句的标号,来从新开始复合语句。

iterate关键字，类似于java中的continue。

set v=0;  
LOOP_LABLE:loop  
    if v=3 then   
        set v=v+1;  
        ITERATE LOOP_LABLE;  -- 继续循环
    end if;  
    insert into t values(v);  
    set v=v+1;  
    if v>=5 then 
        leave LOOP_LABLE;  -- 离开循环
    end if;  
end loop;

1.5、游标

（1）声明游标

DECLARE cursor_name游标名称 CURSOR FOR SELECT_statement查询语句;

（2）打开游标

OPEN cursor_name游标名称;

（3）循环获取数据

FETCH cursor_name游标名称 INTO 变量1,变量2,变量3....;

# FETCH每次向下获取一行数据记录
# 变量1,变量2,变量3....（对应select查询出来的字段，变量需要事先声明，注意：变量名称不能select字段名称相同，否则结果或被覆盖为null）

（4）关闭游标

CLOSE cursor_name游标名称;

（5）声明NOT FOUND程序

当游标遍历结束时候，FETCH不到行数据，就会触发NOT FOUND程序。

我们可以通过声明NOT FOUND，来判断是否遍历结束。

DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1;

# finished是自己声明的一个变量，用于判断是否游标获取结束

（6）mysql游标工作流程

|<-------- 非空循环 ---------|
DECLARE声明 --> OPEN开启游标 --> FETCH获取行数据 --> EMPTY判断循环是否行数据获取结束 --> CLOSE关闭游标

（7）完整游标操作案例

CREATE PROCEDURE prc_test_cursor()
begin
    -- 声明变量
    declare finished int default 0;
    declare id varchar(50) default '';
    declare userName varchar(50) default '';
    declare phoneNumber varchar(50) default '';

    -- 1、声明游标
    declare test_cursor cursor for (
        SELECT seq_no, user_name, phone_number FROM test limit 2
    );
    -- 2、声明NOT FOUND处理程序
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1; 

    -- 3、开启游标
    open test_cursor;
    -- 4、获取数据
    exit_loop: loop
        -- 获取行数据,将查询结果对应字段数据保存到临时变量中
        fetch test_cursor into id, userName, phoneNumber;
        -- 判断是否获取结束
        if finished = 1 then
            leave exit_loop;
        end if;
        -- TODO 进行各种操作
        select id, userName, phoneNumber;
    end loop exit_loop;
    -- 5、循环结束,关闭游标
    close test_cursor;
END

1.6、事务

（1）开启事务

-- 开启事务
start transaction;

（2）事务提交

-- 在commit之前，进行各种dml操作
COMMIT;

（3）事务回滚

-- 出现异常时候，可以执行rollback回滚，避免数据错乱问题
ROLLBACK;

1.7、异常处理

（1）声明异常处理

-- 声明异常处理：表示出现SQLEXCEPTION异常时候，继续执行，并且把t_error = 1变量设置为1
-- t_error是自己定义的变量，需要事先声明
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET t_error = 1;

（2）异常时候处理事务

-- 判断是否成功,成功则提交事务
if t_error = 1 then
    ROLLBACK;
else
    COMMIT;
end if;

到此，MySQL中的存储过程介绍完啦。

综上，这篇文章结束了，主要介绍MySQL之存储过程、异常处理、事务管理的使用。

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。

上一篇：Linux服务器整机复制镜像 linux服务器镜像备份

下一篇：python最短路径算法K短路 python最短路径算法完整代码

提问和评论都可以，用心的回复会被更多人看到评论

发布评论

相关文章

官方博客	全部文章	热门标签	班级博客
了解我们	网站地图	意见反馈

鸿蒙开发者社区	51CTO学堂
51CTO	软考资讯