IT评测·应用市场-qidao123.com

标题: MySQL 存储过程&触发器&事务 [打印本页]

作者: 知者何南    时间: 2023-5-15 12:52
标题: MySQL 存储过程&触发器&事务
存储过程

概念

存储过程(Stored Procedure),是为了完成特定功能的SQL语句集。
优点

存储过程可以理解为shell脚本这类型的命令集输出工具,但是在底层,存储过程拥有更多的优点:
创建&调用 存储过程

调用语法
  1. CALL 过程名称(参数1,参数2...)
复制代码
创建语法
  1.  CREATE PROCEDURE <存储过程别名> ( [过程参数[,…] ] ) <过程体>
复制代码
过程参数的格式
  1. [ IN | OUT | INOUT ] <参数名> <类型>
复制代码
示例

[[99-MySQL补充#MySQL分隔符|修改分隔符]],再写存储过程
  1. DELIMITER :
  2. CREATE PROCEDURE SE()
  3. BEGIN
  4. SELECT * FROM learn.books;
  5. END :
  6. DELIMITER ;
  7. CALL SE();
复制代码
结束的时候在记得将分隔符修改回默认的。
MySQL变量

定义&调用变量

在MySQL界面中使用 SET 定义变量,用 SELECT 调用变量。
  1. SET @a=1;
  2. SELECT @a;
复制代码
此处定义的变量为临时变量,仅该次连接可用,当需要全局长期使用时,需要修改全局变量
存储过程与变量

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

示例IN
  1. DELIMITER :
  2. CREATE PROCEDURE getone(in id INT)
  3. BEGIN
  4. SELECT bname,bid FROM books WHERE bid=id;
  5. END :
  6. DELIMITER ;
  7. CALL getone(1);
复制代码
示例OUT
  1. DELIMITER :
  2. CREATE PROCEDURE outone(OUT nu INT)
  3. BEGIN
  4. SET nu=1;
  5. SELECT nu;
  6. END:
  7. DELIMITER ;
  8. CALL outone(@num);
  9. SELECT @num;
复制代码
这里将内部参数传递到了外部,并用num进行了接收。
示例INOUT
  1. DELIMITER :
  2. CREATE PROCEDURE inoutone(IN bookid INT,OUT bookname VARCHAR(255))
  3. BEGIN
  4.         SELECT bname into bookname FROM learn.books WHERE bid=bookid;
  5. END:
  6. DELIMITER ;
  7. CALL inoutone(1,@a);
复制代码
INOUT的参数传递需要注意调用跟输入的对应关系
存储过程的循环

WHILE 循环

WHILE DO ... END WHILE

  1. DELIMITER :
  2. CREATE PROCEDURE pwhile()
  3. BEGIN
  4.         DECLARE i INT;
  5.         SET i = 0;
  6.        
  7.         WHILE i < 10 DO
  8.                 INSERT INTO test VALUES(i) ;
  9.                 SET i = i + 1;
  10.         END WHILE;
  11.        
  12.         SELECT * FROM test;
  13. END :
  14. DELIMITER ;
  15. CALL pwhile();
复制代码
REPEAT循环

REPEAT...UNTIL...END REPEAT

  1. DELIMITER :
  2. CREATE PROCEDURE arepeat()
  3. BEGIN
  4.         DECLARE i INT;
  5.         SET i = 0;
  6.        
  7.         REPEAT
  8.                 INSERT INTO ptest VALUES(i) ;
  9.                 SET i = i + 1;
  10.         UNTIL i > 10 END REPEAT;
  11.        
  12.         SELECT * FROM ptest;
  13. END :
  14. DELIMITER ;
  15. CALL arepeat();
复制代码
loop循环

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

  1. DELIMITER :
  2. CREATE PROCEDURE tloop()
  3. BEGIN
  4.         DECLARE i INT;
  5.         SET i = 0;
  6.        
  7. LP:LOOP
  8.         INSERT INTO ltest VALUES(i) ;
  9.         SET i = i + 1;
  10.         IF i > 10 THEN
  11.         LEAVE LP
  12. END LOOP
  13.         SELECT * FROM ltest;
  14. END :
  15. DELIMITER ;
  16. 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 操作时才能触发。
触发器种类

根据预定义事件的分类,触发也分为以下三种: 
创建触发器

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

查看触发器
  1. SHOW TRIGGERS;
复制代码
触发器中NEW和OLD

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

NEW 用来表示将要(BEFORE)或已经(AFTER)插入的新数据;

在UPDATE中

NEW 用来表示将要(BEFORE)或已经(AFTER)插入的新数据;

在DELETE中

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

创建触发器

简单触发器

触发器的创建过程其实也是一个存储过程的创建
  1. DELIMITER :
  2. CREATE TRIGGER delcategory AFTER DELETE ON category FOR EACH ROW
  3.        
  4.         DELETE FROM books WHERE btypeid=3;
  5.         :
  6. DELIMITER ;
复制代码

NEW/OLD 的触发器

OLD 示例

当category表发生删除操作后,books表也会根据btypeid进行删除操作
  1. DELIMITER :
  2. CREATE TRIGGER cbook AFTER DELETE
  3. ON category FOR EACH ROW
  4.         BEGIN
  5.         DELETE FROM books WHERE books.btypeid=old.btypeid;
  6.         END :
  7. DELIMITER ;
复制代码
old.btypeid 表示触发器所在表category中发生删除的btypeid
NEW 示例

阻止对students表的更新操作,但删除跟插入不受影响。
  1. DELIMITER :
  2. CREATE TRIGGER ubook BEFORE UPDATE
  3. ON students FOR EACH ROW
  4.         BEGIN
  5.        
  6.         IF old.name is NOT NULL THEN
  7.                 SET new.name=old.name,new.id=old.id,new.age=old.age;
  8.         END IF
  9.        
  10.         END :
  11.        
  12. DELIMITER ;
复制代码
IF old.name is NOT NULL 如果 old表中name字段不为空  

利用Pessimistic Lock的触发器
  1. CREATE TRIGGER tr_MyTrigger ON tablename FOR UPDATE AS
  2. UPDATE table1 SET column1 = data1
  3. INNER JOIN deleted d ON table1.id = d.id
  4. UPDATE table2 SET column2 = data2
  5. INNER JOIN deleted d ON table2.id = d.id
  6. UPDATE table3 SET column3 = data3
  7. INNER JOIN deleted d ON table3.id = d.id
  8. /* etc. */ GO
复制代码
说明

事务

概念

事务的特性ACID

原子性(Autmic)

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

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

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

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

创建一个提交的
  1. SELECT bname FROM books WHERE bid=1 OR bid=2;
  2. SET AUTOCOMMIT=0;
  3. DELIMITER :
  4. START TRANSACTION;
  5.         UPDATE books SET bname="cc" WHERE bid=1;
  6.         UPDATE books SET bname="dd" WHERE bid=2;
  7.         COMMIT;
  8. :
  9. 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;
  4. DELIMITER :
  5. START TRANSACTION;
  6.         UPDATE books SET bname="cc" WHERE bid=1;
  7.         UPDATE books SET bname="dd" WHERE bid=2;
  8. :
  9. DELIMITER ;
  10. ROLLBACK;
  11. SELECT bname WHERE bid=1 or bid=2;
复制代码
回滚成功 
 


免责声明:如果侵犯了您的权益,请联系站长,我们会及时删除侵权内容,谢谢合作!




欢迎光临 IT评测·应用市场-qidao123.com (https://dis.qidao123.com/) Powered by Discuz! X3.4