MySQL 存储过程&触发器&事务
存储过程概念
存储过程(Stored Procedure),是为了完成特定功能的SQL语句集。
优点
存储过程可以理解为shell脚本这类型的命令集输出工具,但是在底层,存储过程拥有更多的优点:
[*]语言的灵活性跟功能性更强,在原有基础之上可以插入控制语句、循环语句等让SQL语句的功能更强,能够完成更复杂的运算跟判断。
[*]封装性,存储过程被创建后,可以在被多次调用,同时可以进行修改,对程序源码不造成影响。
[*]执行速度快,MySQL的语句在执行过程中会有一个[],当数据库体量到达一定的级别的时候,对性能的影响很大,而使用存储过程的时候,执行过一次以后,产生的二进制代码就会被预留在缓存区,不需要再次编译。
[*]减少网络流量,因为不需要进行转译了所以在日常使用中会减少对网路流量的使用。
[*]提高数据库的完整性和安全性,基于上述的封装性,可以对用户的使用权限进行更好的限制,不需要给到更多的权限的同时也能够进行所需的查询,从而也保护了数据的完整性。
创建&调用 存储过程
调用语法
CALL 过程名称(参数1,参数2...)创建语法
CREATE PROCEDURE <存储过程别名> ( [过程参数[,…] ] ) <过程体>过程参数的格式
[ IN | OUT | INOUT ] <参数名> <类型>示例
[],再写存储过程
DELIMITER :
CREATE PROCEDURE SE()
BEGIN
SELECT * FROM learn.books;
END :
DELIMITER ;
CALL SE();结束的时候在记得将分隔符修改回默认的。
MySQL变量
定义&调用变量
在MySQL界面中使用 SET 定义变量,用 SELECT 调用变量。
SET @a=1;
SELECT @a;此处定义的变量为临时变量,仅该次连接可用,当需要全局长期使用时,需要修改全局变量。
存储过程与变量
MySQL存储过程中使用 DECLARE 定义变量,变量仅作用于本次存储过程内,属于局部变量。同时需要对变量定义字段属性,调用时直接用声明了的变量名调用即可。
变量的传递
[*]IN:将变量传入存储过程
[*]OUT:将存储过程内的变量传出到MySQL变量中
[*]INOUT:变量进入存储过程又出来到MySQL变量中
示例IN
DELIMITER :
CREATE PROCEDURE getone(in id INT)
BEGIN
SELECT bname,bid FROM books WHERE bid=id;
END :
DELIMITER ;
CALL getone(1);
[*]这里的变量是IN进行传递到存储过程之中,并定义了变量类型为整数。
[*]这里将参数传入存储过程,所以在调用的时候需要给到传入参数。
示例OUT
DELIMITER :
CREATE PROCEDURE outone(OUT nu INT)
BEGIN
SET nu=1;
SELECT nu;
END:
DELIMITER ;
CALL outone(@num);
SELECT @num;这里将内部参数传递到了外部,并用num进行了接收。
示例INOUT
DELIMITER :
CREATE PROCEDURE inoutone(IN bookid INT,OUT bookname VARCHAR(255))
BEGIN
SELECT bname into bookname FROM learn.books WHERE bid=bookid;
END:
DELIMITER ;
CALL inoutone(1,@a); INOUT的参数传递需要注意调用跟输入的对应关系
存储过程的循环
WHILE 循环
[*]创建测试表
create table pwhile(id int);
[*]在存储过程中while的结构为
WHILE DO ... END WHILE
[*]示例
DELIMITER :
CREATE PROCEDURE pwhile()
BEGIN
DECLARE i INT;
SET i = 0;
WHILE i < 10 DO
INSERT INTO test VALUES(i) ;
SET i = i + 1;
END WHILE;
SELECT * FROM test;
END :
DELIMITER ;
CALL pwhile();REPEAT循环
[*]创建测试表
`create table ptest(id int
[*]REPEAT循环结构为
REPEAT...UNTIL...END REPEAT
[*]示例
DELIMITER :
CREATE PROCEDURE arepeat()
BEGIN
DECLARE i INT;
SET i = 0;
REPEAT
INSERT INTO ptest VALUES(i) ;
SET i = i + 1;
UNTIL i > 10 END REPEAT;
SELECT * FROM ptest;
END :
DELIMITER ;
CALL arepeat();loop循环
[*]创建测试表
cerate table ltest
[*]loop循环结构
lp:loop ... if ... end if ... end loop
[*]示例
DELIMITER :
CREATE PROCEDURE tloop()
BEGIN
DECLARE i INT;
SET i = 0;
LP:LOOP
INSERT INTO ltest VALUES(i) ;
SET i = i + 1;
IF i > 10 THEN
LEAVE LP
END LOOP
SELECT * FROM ltest;
END :
DELIMITER ;
CALL tloop();LP:LOOP 是对LOOP取了一个别名为LP
查询&删除 存储过程
指定库名称查询SELECT
SELECT name FROM mysql.proc WHERE db='[数据库名]' AND type='procedure';指定存储过程的名称查询SHOW
SHOW procedure STATUS LIKE '[存储过程名称]' \G删除存储过程DROP
DROP PROCEDURE IF EXISTS [存储过程名称];MySQL触发器 TRIGRRER
概念
触发器是一个特殊的存储过程,区别点在于存储过程需要使用CALL语句来调用,触发器的执行不需要,也不需要手动启动,只需要一个预定义事件就会被MySQL自动调用。
场景
主要用于保护数据,尤其是多表相互链接的时候,触发器能够让被链接的表之间保持一致性。
预定义事件
即是指触发触发器的场景,能够触发触发器的事件,有且只有 INSERT/UPDATE/DELETE 操作时才能触发。
触发器种类
根据预定义事件的分类,触发也分为以下三种:
[*]INSERT 触发器
[*]UPDATE 触发器
[*]DELETE 触发器
此三类触发器可指定在对应命令执行前或后激活触发器。
创建触发器
语法
CREATE TRIGGER <触发器名称>
<触发时机 AFTER|BEFORE>
<触发事件 UPDATE|DELETE|INSERT>
ON <需触发的表 路径> FOR EACH ROW <触发器主体>;补充点
[*]同一个表中的不能有触发时机和触发事件相同的触发器
[*]触发器关联的表要有永久性,不能将触发器与临时表、视图关联。
[*]FOR EACH ROW:行级触发
[*]触发器主体:指的是触发器激活时执行的语句,要执行多个语句时,可以使用BEGIN END复合语句结构。
查看触发器
SHOW TRIGGERS;触发器中NEW和OLD
关于NEW和OLD,可以理解为一个特殊的表,定位了发生变化的数据类型。
在INSER中
NEW 用来表示将要(BEFORE)或已经(AFTER)插入的新数据;
在UPDATE中
NEW 用来表示将要(BEFORE)或已经(AFTER)插入的新数据;
在DELETE中
OLD 用来表示将要或已经被删除的原数据;
创建触发器
简单触发器
触发器的创建过程其实也是一个存储过程的创建
DELIMITER :
CREATE TRIGGER delcategory AFTER DELETE ON category FOR EACH ROW
DELETE FROM books WHERE btypeid=3;
:
DELIMITER ;
NEW/OLD 的触发器
OLD 示例
当category表发生删除操作后,books表也会根据btypeid进行删除操作
DELIMITER :
CREATE TRIGGER cbook AFTER DELETE
ON category FOR EACH ROW
BEGIN
DELETE FROM books WHERE books.btypeid=old.btypeid;
END :
DELIMITER ;old.btypeid 表示触发器所在表category中发生删除的btypeid
NEW 示例
阻止对students表的更新操作,但删除跟插入不受影响。
DELIMITER :
CREATE TRIGGER ubook BEFORE UPDATE
ON students FOR EACH ROW
BEGIN
IF old.name is NOT NULL THEN
SET new.name=old.name,new.id=old.id,new.age=old.age;
END IF
END :
DELIMITER ;IF old.name is NOT NULL 如果 old表中name字段不为空
[*]此处new.name 是一个并不存在的值,而 old.name 是一个只读的数据存在于原数据中,即是将发生更新前的数据赋值给到了old.name
利用Pessimistic Lock的触发器
CREATE TRIGGER tr_MyTrigger ON tablename FOR UPDATE AS
UPDATE table1 SET column1 = data1
INNER JOIN deleted d ON table1.id = d.id
UPDATE table2 SET column2 = data2
INNER JOIN deleted d ON table2.id = d.id
UPDATE table3 SET column3 = data3
INNER JOIN deleted d ON table3.id = d.id
/* 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)
指的是事务执行完成后,所存储的数据应该存储在数据库中,即使系统发送故障数据本身不受影响。
创建一个简单事务
创建一个提交的
SELECT bname FROM books WHERE bid=1 OR bid=2;
SET AUTOCOMMIT=0;
DELIMITER :
START TRANSACTION;
UPDATE books SET bname="cc" WHERE bid=1;
UPDATE books SET bname="dd" WHERE bid=2;
COMMIT;
:
DELIMITER ;SET AUTOCOMMIT=0; 关闭自动提交事务
COMMIT; 提交事务
测试回滚
回滚命令:rollback
当引擎不符合时无法回滚
SHOW CREATE TABLE books\G
SELECT bname WHERE bid=1 or bid=2;
ROLLBACK;
SELECT bname WHERE bid=1 or bid=2;
修改引擎后重新提交事务
引擎符合时
ALTER TABLE books ENGINE=INNODB;
SHOW CREATE TABLE books\G
SELECT bname WHERE bid=1 or bid=2;
DELIMITER :
START TRANSACTION;
UPDATE books SET bname="cc" WHERE bid=1;
UPDATE books SET bname="dd" WHERE bid=2;
:
DELIMITER ;
ROLLBACK;
SELECT bname WHERE bid=1 or bid=2;回滚成功
来源:https://www.cnblogs.com/lhang/p/17400981.html
免责声明:由于采集信息均来自互联网,如果侵犯了您的权益,请联系我们【E-Mail:cb@itdo.tech】 我们会及时删除侵权内容,谢谢合作!
页:
[1]