MySQL 存储过程&触发器&事务

杨小安

存储过程

概念

存储过程（Stored Procedure），是为了完成特定功能的SQL语句集。
优点

存储过程可以理解为shell脚本这类型的命令集输出工具，但是在底层，存储过程拥有更多的优点：

• 语言的灵活性跟功能性更强，在原有基础之上可以插入控制语句、循环语句等让SQL语句的功能更强，能够完成更复杂的运算跟判断。
  
• 封装性，存储过程被创建后，可以在被多次调用，同时可以进行修改，对程序源码不造成影响。
  
• 执行速度快，MySQL的语句在执行过程中会有一个[[MySQL语言的编译|转译过程]]，当数据库体量到达一定的级别的时候，对性能的影响很大，而使用存储过程的时候，执行过一次以后，产生的二进制代码就会被预留在缓存区，不需要再次编译。
  
• 减少网络流量，因为不需要进行转译了所以在日常使用中会减少对网路流量的使用。
  
• 提高数据库的完整性和安全性，基于上述的封装性，可以对用户的使用权限进行更好的限制，不需要给到更多的权限的同时也能够进行所需的查询，从而也保护了数据的完整性。
  

创建&调用 存储过程

调用语法

1. CALL 过程名称（参数1,参数2...）

复制代码

创建语法

1.  CREATE PROCEDURE <存储过程别名> ( [过程参数[,…] ] ) <过程体>

复制代码

过程参数的格式

1. [ IN | OUT | INOUT ] <参数名> <类型>

复制代码

示例

[[99-MySQL补充#MySQL分隔符|修改分隔符]]，再写存储过程

1. DELIMITER :
3. CREATE PROCEDURE SE()
4. BEGIN
5. SELECT * FROM learn.books;
6. END ：
8. DELIMITER ;
9. CALL SE();

复制代码

结束的时候在记得将分隔符修改回默认的。
MySQL变量

定义&调用变量

在MySQL界面中使用 SET 定义变量，用 SELECT 调用变量。

1. SET @a=1;
2. SELECT @a;

复制代码

此处定义的变量为临时变量，仅该次连接可用，当需要全局长期使用时，需要修改全局变量。
存储过程与变量

MySQL存储过程中使用 DECLARE 定义变量，变量仅作用于本次存储过程内，属于局部变量。同时需要对变量定义字段属性，调用时直接用声明了的变量名调用即可。
变量的传递

• IN：将变量传入存储过程
  
• OUT：将存储过程内的变量传出到MySQL变量中
  
• INOUT：变量进入存储过程又出来到MySQL变量中
  

示例IN

1. DELIMITER :
3. CREATE PROCEDURE getone(in id INT)
4. BEGIN
5. SELECT bname,bid FROM books WHERE bid=id;
6. END :
8. DELIMITER ;
10. CALL getone(1)；

复制代码

• 这里的变量是IN进行传递到存储过程之中，并定义了变量类型为整数。
  
• 这里将参数传入存储过程，所以在调用的时候需要给到传入参数。
  

示例OUT

1. DELIMITER :
3. CREATE PROCEDURE outone(OUT nu INT)
4. BEGIN
5. SET nu=1;
6. SELECT nu;
7. END:
9. DELIMITER ;
11. CALL outone(@num);
12. SELECT @num;

复制代码

这里将内部参数传递到了外部，并用num进行了接收。
示例INOUT

1. DELIMITER :
2. CREATE PROCEDURE inoutone(IN bookid INT,OUT bookname VARCHAR(255))
4. BEGIN
5.         SELECT bname into bookname FROM learn.books WHERE bid=bookid;
6. END:
8. DELIMITER ;
9. CALL inoutone(1,@a);

复制代码

INOUT的参数传递需要注意调用跟输入的对应关系
存储过程的循环

WHILE 循环

• 创建测试表　
  create table pwhile(id int);
  
• 在存储过程中while的结构为
  

WHILE DO ... END WHILE

• 示例
  

1. DELIMITER :
3. CREATE PROCEDURE pwhile()
4. BEGIN
5.         DECLARE i INT;
6.         SET i = 0;
7.        
8.         WHILE i < 10 DO
9.                 INSERT INTO test VALUES(i) ;
10.                 SET i = i + 1;
11.         END WHILE;
12.        
13.         SELECT * FROM test;
14. END :
16. DELIMITER ;
17. CALL pwhile();

复制代码

REPEAT循环

• 创建测试表
  `create table ptest(id int
  
• REPEAT循环结构为
  

REPEAT...UNTIL...END REPEAT

• 示例
  

1. DELIMITER :
3. CREATE PROCEDURE arepeat()
4. BEGIN
5.         DECLARE i INT;
6.         SET i = 0;
7.        
8.         REPEAT
9.                 INSERT INTO ptest VALUES(i) ;
10.                 SET i = i + 1;
11.         UNTIL i > 10 END REPEAT;
12.        
13.         SELECT * FROM ptest;
14. END :
16. DELIMITER ;
17. CALL arepeat();

复制代码

loop循环

• 创建测试表　
  cerate table ltest
  
• loop循环结构　
  

lp:loop ... if ... end if ... end loop

• 示例　
  

1. DELIMITER :
3. CREATE PROCEDURE tloop()
4. BEGIN
5.         DECLARE i INT;
6.         SET i = 0;
7.        
8. LP:LOOP
9.         INSERT INTO ltest VALUES(i) ;
10.         SET i = i + 1;
11.         IF i > 10 THEN
12.         LEAVE LP
13. END LOOP
15.         SELECT * FROM ltest;
16. END :
18. DELIMITER ;
19. CALL tloop();

复制代码

LP:LOOP 是对LOOP取了一个别名为LP

查询&删除 存储过程

指定库名称查询SELECT

1. SELECT name FROM mysql.proc WHERE db='[数据库名]' AND type='procedure';

复制代码

指定存储过程的名称查询SHOW

1. SHOW procedure STATUS LIKE '[存储过程名称]' \G

复制代码

删除存储过程DROP

1. DROP PROCEDURE IF EXISTS [存储过程名称];

复制代码

MySQL触发器 TRIGRRER

概念

触发器是一个特殊的存储过程，区别点在于存储过程需要使用CALL语句来调用，触发器的执行不需要，也不需要手动启动，只需要一个预定义事件就会被MySQL自动调用。
场景

主要用于保护数据，尤其是多表相互链接的时候，触发器能够让被链接的表之间保持一致性。
预定义事件

即是指触发触发器的场景，能够触发触发器的事件，有且只有 INSERT/UPDATE/DELETE 操作时才能触发。
触发器种类

根据预定义事件的分类，触发也分为以下三种：　

• INSERT 触发器
  
• UPDATE 触发器
  
• DELETE 触发器
  此三类触发器可指定在对应命令执行前或后激活触发器。
  

创建触发器

语法

1. CREATE TRIGGER <触发器名称>
2. <触发时机 AFTER|BEFORE>
3. <触发事件 UPDATE|DELETE|INSERT>
4. ON <需触发的表 路径> FOR EACH ROW <触发器主体>;

复制代码

补充点

• 同一个表中的不能有触发时机和触发事件相同的触发器
  
• 触发器关联的表要有永久性，不能将触发器与临时表、视图关联。
  
• FOR EACH ROW：行级触发
  
• 触发器主体：指的是触发器激活时执行的语句，要执行多个语句时，可以使用BEGIN END复合语句结构。
  

查看触发器

1. SHOW TRIGGERS;

复制代码

触发器中NEW和OLD

关于NEW和OLD，可以理解为一个特殊的表，定位了发生变化的数据类型。
在INSER中

NEW 用来表示将要（BEFORE）或已经（AFTER）插入的新数据；

在UPDATE中

NEW 用来表示将要（BEFORE）或已经（AFTER）插入的新数据；

在DELETE中

OLD 用来表示将要或已经被删除的原数据；

创建触发器

简单触发器

触发器的创建过程其实也是一个存储过程的创建

1. DELIMITER :
3. CREATE TRIGGER delcategory AFTER DELETE ON category FOR EACH ROW
4.        
5.         DELETE FROM books WHERE btypeid=3;
6.         :
8. DELIMITER ;

复制代码

NEW/OLD 的触发器

OLD 示例

当category表发生删除操作后，books表也会根据btypeid进行删除操作

1. DELIMITER :
3. CREATE TRIGGER cbook AFTER DELETE
4. ON category FOR EACH ROW
6.         BEGIN
7.         DELETE FROM books WHERE books.btypeid=old.btypeid;
8.         END :
10. DELIMITER ;

复制代码

old.btypeid 表示触发器所在表category中发生删除的btypeid

NEW 示例

阻止对students表的更新操作，但删除跟插入不受影响。

1. DELIMITER :
3. CREATE TRIGGER ubook BEFORE UPDATE
4. ON students FOR EACH ROW
6.         BEGIN
7.        
8.         IF old.name is NOT NULL THEN
9.                 SET new.name=old.name,new.id=old.id,new.age=old.age;
10.         END IF
11.        
12.         END :
13.        
14. DELIMITER ;

复制代码

IF old.name is NOT NULL 如果 old表中name字段不为空　　

• 此处new.name 是一个并不存在的值，而 old.name 是一个只读的数据存在于原数据中，即是将发生更新前的数据赋值给到了old.name
  

利用Pessimistic Lock的触发器

1. CREATE TRIGGER tr_MyTrigger ON tablename FOR UPDATE AS
3. UPDATE table1 SET column1 = data1
4. INNER JOIN deleted d ON table1.id = d.id
6. UPDATE table2 SET column2 = data2
7. INNER JOIN deleted d ON table2.id = d.id
9. UPDATE table3 SET column3 = data3
10. INNER JOIN deleted d ON table3.id = d.id
12. /* etc. */ GO

复制代码

说明

• FOR UPDATE AS UPDATE 是一种在MySQL数据库中实现悲观锁（Pessimistic Lock）的技术，它可以保证同一个记录多线程下同时访问时不会产生更新并发问题。
  
• /* etc. */ GO  是在SQL中常用的一个命令，它用于告诉SQL服务器要启动对SQL脚本的执行。
  

事务

概念

• MySQL数据库事务（datebase transaction）:MySQL事务是用来保证数据库数据一致性和完整性的一种机制。　
  
• 事务可以让用户将一系列的SQL语句保存在一个组中，并这些SQL语句作为一个单一的逻辑工作单元来执行。　
  
• MySQL事务由4个指令定义：BEGIN开始事务；COMMIT提交事务；ROLLBACK回滚事务；SAVEPOINT设置一个事务保存点。
  
• MySQL为每个会话维护了一个隐形的transaction id，以及一个当前正在执行的transaction的id以及一个超时时间，这些都将决定当前正在工作的事务是否需要提交或回滚。
  
• 仅支持INNODB和BDB两种存储引擎。
  

事务的特性ACID

原子性（Autmic）

指的是在事务操作的不可分割，仅有0和1，执行全部成功或者全部失败，没有部分成功部分失败。
一致性（Consistency）

指的是在事务开始的前后，整个数据库的一致性不受影响，数据完整性不受影响。
隔离性（Isolation）

指的是事务的执行是并行且独立的，在事务完成之后才会将结果进行发布，整个过程中彼此是不可见的，避免事务的一个混乱。
持久性（Durability）

指的是事务执行完成后，所存储的数据应该存储在数据库中，即使系统发送故障数据本身不受影响。
创建一个简单事务

创建一个提交的

1. SELECT bname FROM books WHERE bid=1 OR bid=2;
3. SET AUTOCOMMIT=0;
4. DELIMITER :
6. START TRANSACTION;
7.         UPDATE books SET bname="cc" WHERE bid=1;
8.         UPDATE books SET bname="dd" WHERE bid=2;
9.         COMMIT;
10. :
12. DELIMITER ;

复制代码

SET AUTOCOMMIT=0; 关闭自动提交事务
COMMIT; 提交事务

测试回滚

回滚命令：rollback
当引擎不符合时无法回滚

1. SHOW CREATE TABLE books\G
2. SELECT bname WHERE bid=1 or bid=2;
3. ROLLBACK;
4. SELECT bname WHERE bid=1 or bid=2;

复制代码

修改引擎后重新提交事务
引擎符合时

1. ALTER TABLE books ENGINE=INNODB;
2. SHOW CREATE TABLE books\G
3. SELECT bname WHERE bid=1 or bid=2;
5. DELIMITER :
7. START TRANSACTION;
8.         UPDATE books SET bname="cc" WHERE bid=1;
9.         UPDATE books SET bname="dd" WHERE bid=2;
10. :
11. DELIMITER ;
14. ROLLBACK;
15. SELECT bname WHERE bid=1 or bid=2;

复制代码

回滚成功　
　

来源:https://www.cnblogs.com/lhang/p/17400981.html
免责声明：由于采集信息均来自互联网，如果侵犯了您的权益，请联系我们【E-Mail:cb@itdo.tech】 我们会及时删除侵权内容，谢谢合作！