MySQL基础笔记(6) - 游标&触发器&事务

游标&触发器&事务

目录

• 游标&触发器&事务
  • • 1. 游标
    • 2. 触发器
      • 2.1 触发器的创建与删除
      • 2.2 触发器的使用
    • 3. 事务
      • 3.1 事务的使用
      • 3.2 事务的隔离级别

1. 游标

定义：有时需要在检索出来的行中前进或后退一行/多行，这就是使用游标的原因。游标是一个被存储在Mysql服务器上的数据库查询，它是被该语句检索出来的结果集。在存储了游标之后，应用程序可以根据需要滚动或游览其中的数据。Mysql的游标只能用于存储过程和函数

注意：创建语句均只能在命令行窗口执行，请先在命令行登录并转到当前库，然后再粘贴进行操作

#1.创建游标
DELIMITER //
CREATE PROCEDURE processorders()
BEGIN
    DECLARE ordernumbers CURSOR
    FOR
    SELECT order_num FROM orders;
END//

#2.开关游标
OPEN ordernumbers;
CLOSE ordernumbers;

#实例
DELIMITER //
CREATE PROCEDURE processorders()
BEGIN
	-- 定义三个变量
    DECLARE done BOOLEAN DEFAULT 0;
    DECLARE o INT;
    DECLARE t DECIMAL(8,2);
    
    
    DECLARE ordernumbers CURSOR
    FOR
    SELECT order_num FROM orders;
    -- 当没有更多的行供repeat循环的时候,将done设置为1,停止循环
    DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;
    
    -- 创建ordertotals表
    CREATE TABLE IF NOT EXISTS ordertotals
    (order_num INT,total DECIMAL(8,2));
    
    OPEN ordernumbers;
    
    -- 循环直到done不为0时停止
    REPEAT
    	-- 游标结合repeat查看表每一行并存入到变量o中
		FETCH ordernumbers INTO o;
		-- ordertotal是上一章中用来统计商品总价格的存储流程s
		CALL ordertotal(o,1,t);
		-- 将计算结果插入到新表ordertotals中
		INSERT INTO ordertotals(order_num,total)
		VALUES(o,t);
    UNTIL done END REPEAT;
    
    CLOSE ordernumbers;
END//

SELECT *
FROM ordertotals;
#本实例跨度较大，重在理解

2. 触发器

定义：触发器是Mysql响应DELETE,INSERT,UPDATE语句而自动执行的一条/一组Mysql语句

2.1 触发器的创建与删除

• 创建触发器

  语法：create trigger [触发器名] [触发时机] [触发器响应的活动] ON [触发器关联的表]

  CREATE TRIGGER newproduct AFTER INSERT ON products
  FOR EACH ROW SELECT 'Product added' INTO @asd;
  
  #只有表才能支持触发器，视图不支持
  #触发器按每个表每个事件每次地定义，每个表每个事件每次只允许拥有一个触发器。因此，每个表最多支持6个触发器(每条insert,update,delete前后)。单一触发器不能与多个事件或多个表关联
  #Mysql 5之后不允许触发器直接返回值，这里将返回值储存到了变量asd中
  

• 删除触发器

  DROP TRIGGER newproduct;
  

2.2 触发器的使用

• INSERT触发器

  • INSERT触发器代码内，可以引用一个名为NEW的虚拟表，可以由此访问那些被插入的行
  • 在BEFORE INSERT触发器中，NEW中的值也可以被更新(允许更改被插入的值)
  • 对于AUTO_INCREMENT列，NEW在INSERT执行前包含0，在执行后包含新的自动生成的值

  #实例
  #设置一个触发器,每当插入完成后,从new表中获得order_num的新数值,然后储存到asd中
  CREATE TRIGGER neworder AFTER INSERT ON orders
  FOR EACH ROW SELECT new.order_num INTO @asd;
  
  INSERT INTO orders(order_date,cust_id)
  VALUES(NOW(),10001);
  
  SELECT @asd;
  

• DELETE触发器

  • DELETE触发器代码内，可以引用一个名为OLD的虚拟表，访问被删除行

  • OLD的中的值全部都是只读的，不可更改

  #实例
  #设置一个触发器,在删除行前,将行的数据转移到另一个临时表中
  CREATE TRIGGER deleteorder BEFORE DELETE ON orders
  FOR EACH ROW
  BEGIN
  	INSERT INTO archive_orders(order_num,order_date,cust_id)
  	VALUES(OLD.order_num,OLD.order_date,OLD.cust_id);
  END;
  

• UPDATE触发器

  • UPDATE触发器代码总，可以引用OLD的虚拟表访问UPDATE之前的值，引用NEW的虚拟表访问UPDATE后的值
  • 在BEFORE UPDATE触发器中，NEW中的值可能也被更新
  • OLD中的值全都是只读的，不能更新

  #实例
  #设置一个触发器,保证州名的缩写总是大写
  CREATE TRIGGER updatevender BEFORE UPDATE ON vendors
  FOR EACH ROW SET NEW.vend_state = Upper(NEW,vend_state);
  

3. 事务

3.1 事务的使用

定义：事务是指一个或一组sql语句组成一个执行单元，这个执行单元要么全部执行，要么全部不执行。一个事务一旦执行失败，就会回滚到上一个步骤。(MyISAM引擎不支持事务处理,一般使用InnoDB引擎)

特性(ACID)：

• 原子性：一个事务不可再分割，要么都执行要么都不执行
• 一致性：一个事务执行会使数据从一个一致状态切换到另一个一致状态
• 隔离性：一个事物的执行不受其它事务的干扰
• 持久性：一个事务一旦提交，则会永久的改变数据库的数据

事务的分类：

• 隐式事务：事务没有明显的开启和结束的标记（比如insert，update，delete语句）
• 显式事务：事务具有明显的开启和结束的标记（使用前必须设置自动提交功能为禁用）

#1.开启事务
SET autocommit=0;
START TRANSACTION;
#2.编写一组事务的语句
UPDATE account SET balance = 500;
UPDATE account SET balance = 1500;
#3.结束事务
COMMIT; #提交事务
#OR
ROLLBACK; #回滚

#4.savepoint的使用:savepoint一般搭配rollback使用,用于指定回滚的位置
SET autocommit=0;
START TRANSACTION;
DELETE FROM account WHERE id=25;
SAVEPOINT a; #设置保存点
DELETE FROM account WHERE id=28;
ROLLBACK TO a; #回滚到保存点

#在事务中，回滚可以对INSERT,UPDATE,DELETE生效,但是对CREATE,DROP,TRANSACTION无效

3.2 事务的隔离级别

事务的隔离级别/并发问题	脏读	不可重复读	幻读
read uncommitted	√	√	√
read committed	×	√	√
repeatable read	×	×	√
serializable	×	×	×

查看隔离级别：select @@tx_isolation;

设置隔离级别：set session|global transaction isolation level 隔离级别;