[学会MySql系列] 第九篇：游标和触发器

13. 游标

MySQL中的检索结果其实就是筛选出来的满足过滤条件的数据行（ROW）的集合，那么怎么在这些集合上面进行复杂的操作，比如针对检索出的不同行采取不同操作，这个时候就需要定义游标（cursor）。

13.1 创建、打开、关闭游标

游标的创建句法如下

DECLEAR cursorname CURSOR
FOR SELECT columnname FROM tablename;

创建游标一定要紧跟指定检索内容。
当使用前需要打开游标，使用完关闭游标，句法如下

OPEN cursorname;
#打开游标
CLOSE cursorname;
#关闭游标

13.2 游标的使用

游标类似于搜索结果集合中的行指针，可以通过FETCH子句将行的值赋给变量，例如

OPEN cursorname;
FETCH cursorname INTO variable1,variable2;
CLOSE cursorname;

但是需要注意的一点是游标的机制：
1.自动从第一行开始（行号为0）
2.每次执行自动指向下一行
3.游标本身并不检测是否为最后一行，只有当游标溢出时会引发NOT FOUND错误，所以在使用时需要有捕捉NOT FOUND错误的语句。

DECLARE continue HANDLER for NOT FOUND SET done = 1

13.3 范例：游标

对于以下

mysql> select * from product;
+---------+-----------+----------+------------+-------------+
| prod_id | prod_name | quantity | item_price | supplier_id |
+---------+-----------+----------+------------+-------------+
|    1001 | A         |      100 |        100 |         101 |
|    1002 | B         |       50 |        300 |         101 |
|    1003 | C         |       55 |        100 |         102 |
+---------+-----------+----------+------------+-------------+
3 rows in set (0.00 sec)

我希望得到单个商品的总价，创建过程，并在其中使用游标，如下

DELIMITER $$
CREATE PROCEDURE price()
BEGIN
	DECLARE price FLOAT DEFAULT 0;
	DECLARE prodquantity INT;
	DECLARE proditemprice FLOAT;
	DECLARE done BOOLEAN DEFAULT 0;
	#变量声明必须在游标和handler之前
	DECLARE prodprice CURSOR 
	FOR SELECT quantity, item_price FROM product;
	DECLARE continue HANDLER FOR NOT FOUND SET done = 1;
	
	CREATE TABLE sumprice(sum);
	OPEN prodprice;
	looplabel:loop
	FETCH prodprice INTO prodquantity, proditemprice;
		IF done then
			leave looplabel;
		END IF;
		price = proditemprice*prodquantity;
		INSERT INTO sumprice(sum) VALUES(price);
	END LOOP;
END $$
DELIMITER ;

调用过程并且显示sumprice表中的内容。

mysql> call price();
Query OK, 0 rows affected (0.03 sec)
mysql> select * from sumprice;
+-------+
| sum   |
+-------+
| 10000 |
| 15000 |
|  5500 |
+-------+
3 rows in set (0.00 sec)

• 注：想要在过程中返回数组的一个方法是，在过程中创建表，然后在调用完过程，结果就保存再表中了，在外面也是可用的。

14.触发器

触发器简单的说就是对表进行某种操作（删除、插入、更新）时，同时执行若干SQL语句的一种结构。
比如
1、网购过程中，当客户提交商品订单时，触发店家商品表的库存相应减少。
2、在客户确认支付时，触发验证卡上余额。

14.1 创建、删除触发器

触发器的创建例如

DELIMITER $$
CREATE TRIGGER triggername AFTER INSERT ON products
FOR EACH ROW 
BEGIN
	SELECT 'Product added' INTO @p;
END $$
DELIMITER ;

触发器包括以下结构：

1. create trigger子句：用来定义触发器
2. triggername：唯一的触发器名
3. after：何时执行
4. insert：响应的操作
5. on tablename：指定触发器对应的表
6. for each row：指定针对操作的每一行，这里时插入的每一行
7. begin-end：触发操作

删除触发器使用drop关键词，例如

DROP TRIGGER triggername;

14.2 使用触发器

14.1 INSERT 触发器

insert触发器有以下注意点：
1.在触发器内，能够引用表new的虚拟表，访问被插入的行
2.如果是BEFORE INSERT触发器，表new的值也是可以更新
3.对于AUTO_INCREMENT列，在insert之后才会包含新的值

例如

DELIMITER $$
CREATE TRIGGER neworder AFTER INSERT ON orders
FOR EACH ROW
BEGIN
	SELECT new.order_date INTO @p;
END $$
DELIMITER ;

• 注意：MySQL5.0以后已经不支持从触发器返回结果集，想要在触发器保存中间结果，需要用变量收集中间结果。不然会引发以下错误：
  ERROR 1415 (0A000): Not allowed to return a result set from a trigger。
  运行结果如下：

mysql> CREATE TRIGGER neworder AFTER INSERT ON orders
    -> FOR EACH ROW
    -> BEGIN
    -> SELECT new.order_date INTO @p;
    -> END $$
Query OK, 0 rows affected (0.01 sec)

mysql> DELIMITER ;
mysql> insert into orders(order_id,order_date,cust_id)
    -> values(101,now(),101);
Query OK, 1 row affected (0.00 sec)

mysql> select @p ;
+---------------------+
| @p                  |
+---------------------+
| 2019-08-07 16:05:14 |
+---------------------+
1 row in set (0.00 sec)

14.2 DELETER 触发器

delete触发器有以下注意点：

1. 在触发器内可以引用表old，访问被删除的行
2. 表old中的值都是只读的。

DELIMITER $$
CREATE TRIGGER deleteorder BEFORE DELETE ON orders
FOR EACH ROW
BEGIN
	INSERT INTO archive_orders(order_id,order_date,cust_id)
	VALUES(old.order_id, old.order_date, old.cust_id);
END $$
DELIMITER ;

14.3 UPDATE 触发器

UPDATE触发器有以下注意点：

1. 在触发器内，能够引用一个表old来访问更新前的值，引用一个表new，来访问更新后的值
2. BEFORE UPDATE触发器中表new也能被更新
3. 表old是只读的。

DELIMITER $$
CREATE TRIGGER updateorder BEFORE UPDATE ON orders
FOR EACH ROW
BEGIN
	SET new.cust_id = new.cust_id +10000;
END $$
DELIMITER ;