SQL知识点五——存储过程和事务（超详细）

最新推荐文章于 2025-04-08 14:40:05 发布

超级蛋蛋怪无敌冲！！

最新推荐文章于 2025-04-08 14:40:05 发布

阅读量1.1k

点赞数 19

文章标签：数据库 mysql 1024程序员节

本文链接：https://blog.youkuaiyun.com/qq_62859013/article/details/143313254

版权

一、事务：

事务就是将一组SQL语句放在同一批次内去执行如果一个SQL语句出错，则该批次内的所有SQL都将被取消执行。

1.ACID 属性：

事务必须具备以下四个属性：

原子性（Atomicity：指事务是一个不可分割的最小工作单位，事务中的操作只有都发生和都不发生两种情况
一致性（Consistency）：数据的一致性
隔离性（Isolation）：事务与事务之间相互隔离，并发执行的各个事务之间不能互相干扰。
持久性（Durability）：永久写入硬盘，永久保存

2.事务语句：

SET AUTOCOMMIT：使用SET语句来改变自动提交模式

MySQL中默认是自动提交使用事务时应先关闭自动提交

SET AUTOCOMMIT  = 0;    # 关闭自动提交模式
SET AUTOCOMMIT  = 1;    # 开启自动提交模式

1. START TRANSACTION：开始一个事务,标记事务的起始点

2. COMMIT ：提交一个事务给数据库，这是一个事务控制语句，用于提交当前事务中所有已经执行的SQL语句，使这些更改永久保存到数据库中。

3. ROLLBACK：将事务回滚，数据回到本次事务的初始状态

4. SET AUTOCOMMIT = 1;/SET AUTOCOMMIT = 0;

开启MySQL数据库的自动提交/禁止当前会话的自动提交

在自动提交模式下，每条SQL语句都是一个独立的事务，无法回滚。默认自动提交
在手动提交模式下，可以更灵活地管理事务，确保多条SQL语句作为一个整体成功或失败。

关闭自动提交后，该位置作为一个事务的起点，执行COMMIT或者ROLLBACK语句后该事务结束

开启事务的步骤：

#步骤一：开启事务（可选）
start transaction;
#步骤二：编写事务中的sql语句（insert、update、delete）
#这里实现一下"李二给王五转账"的事务过程
update t_account set balance = 50 where vname = "李二";
update t_account set balance = 130 where vname = "王五";
#步骤三：结束事务
commit; #提交事务
# rollback; #回滚事务:就是事务不执行，回滚到事务执行前的状态

3.事务的并发：

同时运行的多个事务，如果没采用必要的隔离机制，就会发生以下各种问题

1.脏读：

对于两个事务T1,T2，T1读取了已经被T2更新但还没有被提交的字段之后，若T2回滚，T1读取的内容就是临时且无效的

2.不可重复读：

对于两个事务T1,T2，T1读取了一个字段，然后T2更新了该字段之后，T1在读取同一个字段，值就不同了

3.幻读：

有两个事务 T1 和 T2，它们并发执行。T1 在事务中两次读取某个范围的数据，而在两次读取之间，T2 对该范围的数据进行了插入或删除操作。结果，T1 第二次读取时会看到新的数据行，这些数据行在第一次读取时并不存在。或少了数据（删除）

4.事务的隔离级别：

为了避免上面的各种并发问题，用隔离并发运行各个事务，使他们相互不受影响

1. read uncommitted（读未提交数据）：允许事务读取另一个事务未提交的数据。（脏读、不可重复读和幻读的问题都会出现）。

2. read committed（读已提交数据）：只允许事务读取已经被其他事务提交的数据。（可以避免脏读，但不可重复读和幻读的问题仍然可能出现）

3.repeatable read（可重复读）：确保事务可以多次从一个字段中读取相同的值，在这个事务持续期间，禁止其他事务对这个字段进行更新(update)。（可以避免脏读和不可重复读，但幻读仍然存在）

4. serializable（串行化）：最高的隔离级别，确保事务的串行执行。所有并发问题都可避免，但性能十分低下

二、存储过程：

1.创建存储过程语句：

因为没有大括号，用begin end来代替

括号里面也可以传参数

create PROCEDURE showname()
BEGIN
 
  SQL语句xxxxxx;
  
END;

删除存储过程：

DROP PROCEDURE IF EXISTS 存储过程名字;

2.调用存储过程：

call showname();

3.存储过程传参

CREATE PROCEDURE showpwd00 ( grade int(10))
BEGIN

select StudentNo FROM student WHERE student.GradeId = grade;

END

  <!-- 调用 -->

  call showpwd00(4);

4.存储过程创建局部变量

declare创建局部变量i 设置类型和默认值

在查询语句中使用INTO赋值

create PROCEDURE showpwd()
BEGIN
DECLARE i int(10) DEFAULT(1001);
select LoginPwd INTO i FROM student WHERE StudentNo = 1000;
select i;
END;

5.存储过程的参数 IN、OUT和INOUT

IN 输入参数：表示调用者向过程传入值（传入值可以是字面量或变量），进入函数的时候不可改变，可以在内部改变
OUT 输出参数：传进去后值变成null，过程可以改变.
INOUT 输入输出参数：既表示调用者向过程传入值，又表示过程向调用者传出值（值只能是变量）

例子：

像这种传参，默认就是in

将要返回的值使用INTO给info，然后调用的时候通过@调用这个变量

sex是变量，传出值

CREATE PROCEDURE showpwd00 (in grade int(10),out info int(4))
BEGIN

select (StudentNo) into info FROM student WHERE student.GradeId = grade;
END

<!-- 调用 -->
call showpwd00(4,@info);

6. mysql存储过程控制语句

条件语句

1.IF-THEN-ELSE语句

都是以END IF结尾

if--then--end if语句

IF var=0 THEN
      ......
END IF ;

if--then--else--end if语句

IF var=0 THEN
      ....
ELSE
      .......
    END IF ;

2.CASE-WHEN-THEN-ELSE

和switch语句一样

END CASE结尾

CASE var
        WHEN 0 THEN
          ......
        WHEN 1 THEN
          .....
        ELSE
          .....
      END CASE ;

循环语句

1.WHILE-DO…END-WHILE

WHILE var<6 DO
        	.....
END WHILE ;

7. 转账存储过程中使用循环和事务

这是转账，给两条语句进行一个开启事务

设置变量err记录错误变量

DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET err=1;

使用if else当有错误时回滚

没有错误时提交

-- 创建存储过程 转账
CREATE PROCEDURE transform()
BEGIN
-- 声明错误变量
DECLARE err int DEFAULT 0;
-- 声明开启错误拦截 设置错误变量
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET err=1;
-- 开启
START TRANSACTION;
	UPDATE account1  SET cash = cash-100 WHERE id = 1;
	UPDATE account1  SET cash = cash+100 WHERE id = 2;

if err>0 THEN
ROLLBACK;
ELSE COMMIT;
END IF;
END;



-- 转账
CALL transform();