Mysql【存储过程和存储函数，触发器，事务】

一、存储过程和存储函数

概念： 

存储过程和函数是 事先经过编译并存储在数据库中的一段 SQL 语句的集合

好处：

• 存储过程和函数可以重复使用，减轻开发人员的工作量。类似于java中方法可以多次调用

• 减少网络流量，存储过程和函数位于服务器上，调用的时候只需要传递名称和参数即可

• 减少数据在数据库和应用服务器之间的传输，可以提高数据处理的效率

• 将一些业务逻辑在数据库层面来实现，可以减少代码层面的业务处

存储过程和存储函数的区别：【存储过程和函数效果基本相同】

• 函数必须有返回值

• 存储过程没有返回值

---

1. 存储过程的创建和调用

创建： 

-- 修改结束分隔符为$
DELIMITER $

-- 标准语法
CREATE PROCEDURE 存储过程名称(参数...)   【小括号为参数列表，与java方法类似】
BEGIN
	sql语句;        ；表是结束，但存储过程中可以有多条sql语句，
                    所以开头中先把结束符改为$作为这个方法的结束标记，使用完后在改回；
END$

-- 修改结束分隔符为分号
DELIMITER ;            

调用存储过程：（相当于调用方法）

-- 标准语法
CALL 存储过程名称(实际参数);

-- 调用stu_group存储过程
CALL stu_group();

 

2.存储过程的查看和删除

查询某个数据库中创建的全部存储过程（方法）

 3.存储过程语法 3.1变量

 3.2 if语句

 语法：

-- 标准语法
IF 判断条件1 THEN 执行的sql语句1;
[ELSEIF 判断条件2 THEN 执行的sql语句2;]
...
[ELSE 执行的sql语句n;]
END IF;

 3.3 参数传递和返回值

语法：

DELIMITER $

-- 标准语法
CREATE PROCEDURE 存储过程名称([IN|OUT|INOUT] 参数名 数据类型)
BEGIN
	执行的sql语句;
END$

DELIMITER ;

案例： 

 

info作为输出参数，加上@表示先将变量传入存储过程中，进行赋值，赋值完毕后在输出此参数

@info就能查出所保存的数据了 

 @变量名:  这种变量要在变量名称前面加上“@”符号，叫做用户会话变量，代表整个会话过程他都是有作用的，这个类似于全局变量一样。

@@变量名: 这种在变量前加上 "@@" 符号, 叫做系统变量 

3.4 While循环

 语法：

-- 标准语法
初始化语句;
WHILE 条件判断语句 DO
	循环体语句;
	条件控制语句;
END WHILE;

存储过程总结

• 存储过程是 事先经过编译并存储在数据库中的一段 SQL 语句的集合。可以在数据库层面做一些业务处理

• 说白了存储过程其实就是将sql语句封装为方法，然后可以调用方法执行sql语句而已

• 存储过程的好处

  • 安全

  • 高效

  • 复用性强

4.存储函数 

• 存储函数和存储过程是非常相似的。存储函数可以做的事情，存储过程也可以做到！

• 存储函数有返回值，存储过程没有返回值(参数的out其实也相当于是返回数据了)

语法：（创建）

DELIMITER $

-- 标准语法
CREATE FUNCTION 函数名称([参数 数据类型])
RETURNS 返回值类型
BEGIN
	执行的sql语句;
	RETURN 结果;
END$

DELIMITER ;

 

---

二、触发器

1.触发器的概念

• 触发器是与表有关的数据库对象，可以在 insert/update/delete 之前或之后，触发并执行触发器中定义的SQL语句。触发器的这种特性可以协助应用在数据库端确保数据的完整性 、日志记录 、数据校验等操作 。

• 使用别名 NEW 和 OLD 来引用触发器中发生变化的记录内容，这与其他的数据库是相似的。现在触发器还只支持行级触发，不支持语句级触发。

触发器类型	OLD的含义	NEW的含义
INSERT 型触发器	无 (因为插入前状态无数据)	NEW 表示将要或者已经新增的数据
UPDATE 型触发器	OLD 表示修改之前的数据	NEW 表示将要或已经修改后的数据
DELETE 型触发器	OLD 表示将要或者已经删除的数据	无 (因为删除后状态无数据)

 

---

三、事务 1.事务的概念

• 一条或多条 SQL 语句组成一个执行单元，其特点是这个单元要么同时成功要么同时失败，

• 单元中的每条 SQL 语句都相互依赖，形成一个整体，

• 如果某条 SQL 语句执行失败或者出现错误，那么整个单元就会回滚，撤回到事务最初的状态

• 如果单元中所有的 SQL 语句都执行成功，则事务就顺利执行。

2.事务的数据准备

-- 创建db10数据库
CREATE DATAbase db10;

-- 使用db10数据库
USE db10;

-- 创建账户表
CREATE TABLE account(
	id INT PRIMARY KEY AUTO_INCREMENT,	-- 账户id
	NAME VARCHAr(20),			-- 账户名称
	money DOUBLE				-- 账户余额
);
-- 添加数据
INSERT INTO account VALUES (NULL,'张三',1000),(NULL,'李四',1000);

3.未添加事务演示

-- 张三给李四转账500元
-- 1.张三账户-500
UPDATE account SET money=money-500 WHERe NAME='张三';
-- 2.李四账户+500
出错了...        【执行后第一条语句成功，在此处报错，第三条无法执行。因此出现问题】
UPDATE account SET money=money+500 WHERe NAME='李四';

-- 该场景下，这两条sql语句要么同时成功，要么同时失败。否则就会出错。因此就需要被事务所管理！

4.添加事务

操作事务的三个步骤

1. 开启事务：记录回滚点，并通知服务器，将要执行一组操作，要么同时成功、要么同时失败

2. 执行sql语句：执行具体的一条或多条sql语句

3. 结束事务(提交|回滚)

   • 提交：没出现问题，数据进行更新

   • 回滚：出现问题，数据恢复到开启事务时的状态

 操作事务演示：

-- 开启事务
START TRANSACTION;

-- 张三给李四转账500元
-- 1.张三账户-500
UPDATE account SET money=money-500 WHERe NAME='张三';
-- 2.李四账户+500
-- 出错了...
UPDATE account SET money=money+500 WHERe NAME='李四';

-- 回滚事务(出现问题) 
ROLLBACK;

-- 提交事务(没出现问题)
COMMIT;

先执行开启事务，再去执行语句，如果有错就去执行回滚语句，如果没错就选中提交事务语句执行

---

5.事务的提交方式

• 事务的提交方式：

  • 自动提交(MySQL默认为自动提交)【比如平常编写的sql语句】

  • 手动提交

查看事物的提交方式： 

-- 标准语法
SELECT @@AUTOCOMMIT;  -- 【1代表自动提交    0代表手动提交】

修改提交方式 ： 

-- 标准语法
SET @@AUTOCOMMIT=数字;

-- 修改为手动提交
SET @@AUTOCOMMIT=0;

-- 查看提交方式
SELECT @@AUTOCOMMIT;

---

6.事务的四大特征(ACID)【面试】

• 原子性(atomicity)

  • 原子性是指事务包含的所有操作要么全部成功，要么全部失败回滚，

  • 因此事务的操作如果成功就必须要完全应用到数据库，如果操作失败则不能对数据库有任何影响

• 一致性(consistency)

  • 一致性是指事务必须使数据库从一个一致性状态变换到另一个一致性状态，也就是说一个事务执行之前和执行之后都必须处于一致性状态

  • 拿转账来说，假设张三和李四两者的钱加起来一共是2000，那么不管A和B之间如何转账，转几次账，事务结束后两个用户的钱相加起来应该还得是2000，这就是事务的一致性

• 隔离性(isolcation)

  • 隔离性是当多个用户并发访问数据库时，比如操作同一张表时，数据库为每一个用户开启的事务，不能被其他事务的操作所干扰，多个并发事务之间要相互隔离

• 持久性(durability)

  • 持久性是指一个事务一旦被提交了，那么对数据库中的数据的改变就是永久性的，即便是在数据库系统遇到故障的情况下也不会丢失提交事务的操作

7.事务的隔离级别

隔离级别的概念

• 多个客户端操作时 ,各个客户端的事务之间应该是隔离的，相互独立的 , 不受影响的。

• 而如果多个事务操作同一批数据时，则需要设置不同的隔离级别 , 否则就会产生问题 。

• 我们先来了解一下四种隔离级别的名称 , 再来看看可能出现的问题

• 可能引发的问题

问题	现象
脏读	是指在一个事务处理过程中读取了另一个未提交的事务中的数据 , 导致两次查询结果不一致
不可重复读	是指在一个事务处理过程中读取了另一个事务中修改并已提交的数据, 导致两次查询结果不一致
幻读	select 某记录是否存在，不存在，准备插入此记录，但执行 insert 时发现此记录已存在，无法插入。或不存在执行delete删除，却发现删除成功

---

查询数据库隔离级别  ：

-- 标准语法
SELECT @@TX_ISOLATION;

修改数据库隔离级别：

-- 标准语法
SET GLOBAL TRANSACTION ISOLATION LEVEL 级别字符串;

-- 修改数据库隔离级别为read uncommitted
SET GLOBAL TRANSACTION ISOLATION LEVEL read uncommitted;

-- 查看隔离级别
SELECT @@TX_ISOLATION;   -- 修改隔离级别后需要关闭软件重新打开软件才能更新出来

9.隔离级别总结别.

10.事务的总结

• 一条或多条 SQL 语句组成一个执行单元，其特点是这个单元要么同时成功要么同时失败。例如转账操作

• 开启事务：start transaction;

• 回滚事务：rollback;

• 提交事务：commit;

• 事务四大特征

  • 原子性

  • 持久性

  • 隔离性

  • 一致性

• 事务的隔离级别

  • read uncommitted(读未提交)

  • read committed (读已提交)

  • repeatable read (可重复读)

  • serializable (串行化)