MySQL入门（三），mysql入门
本学习笔记参考《MySQL必知必会》和官方手册MySQL 5.6 Reference Manual
MySQL入门（一）
MySQL入门（二）
本文内容：
- MySQL存储过程
- MySQL游标
- MySQL触发器
六、MySQL存储过程
6.1 什么是存储过程
简单来说，就是为以后的使用而保存的一条或多条MySQL语句的集合。
6.2 使用存储过程
(1) 创建存储过程
mysql> DELIMITER //
mysql> CREATE PROCEDURE productpricing( )
-> BEGIN
-> SELECT Avg(prod_price) AS priceaverage
-> FROM products;
-> END //
Query OK, 0 rows affected (0.09 sec)
mysql> DELIMITER ;上面的语句创建了一个名为 productpricing 的存储过程，productpricing( ) 的括号里可以加入参数列表，BEGIN 和 END 之间为过程体。由于 MySQL 语句的分隔符为 ; ，而 mysql 命令行实用程序的分隔符也为 ; ，为了避免存储过程体里的 ; 不被 mysql 实用程序解释，解决办法是临时更改命令行实用程序的语句分隔符。DELIMITER // 语句重新定义分隔符为 // ，在创建完存储过程后再用 DELIMITER ; 把分隔符改回来。
(2) 使用存储过程
mysql> CALL productpricing( );+————–+
| priceaverage |
+————–+
| 16.133571 |
+————–+
(3) 删除存储过程
存储过程在创建之后，就被保存在服务器上以供使用，直至被删除，删除命令如下：
mysql> DROP PROCEDURE productpricing;注意：存储过程名后面没有括号。
如果指定要删除的存储过程存在则删除，如果不存在就会出错。为了使在不存在时也不至于出错可使用这样的语句：
mysql> DROP PROCEDURE IF EXISTS productpricing;(4) 使用参数
mysql> DELIMITER //
mysql> CREATE PROCEDURE productpricing(
-> OUT pl DECIMAL(8, 2),
-> OUT ph DECIMAL(8, 2),
-> OUT pa DECIMAL(8, 2)
-> )
-> BEGIN
-> SELECT Min(prod_price)
-> INTO pl
-> FROM products;
-> SELECT Max(prod_price)
-> INTO ph
-> FROM products;
-> SELECT Avg(prod_price)
-> INTO pa
-> FROM products;
-> END //
Query OK, 0 rows affected (0.07 sec)
mysql> DELIMITER ;此存储过程接收3个参数：pl 存储产品的最低价格，ph 存储产品的最高价格，pa 存储产品的平均价格。每个参数必须有指定的类型，这里使用十进制。关键字 OUT 指出相应的参数用来从存储过程传出一个值（返回给调用者）。MySQL 支持 IN （传递给存储过程）、OUT （从存储过程传出）和 INOUT （对存储过程传入和传出）类型的参数。
调用这个存储过程：
mysql> CALL productpricing(@pricelow, @pricehigh, @priceaverage);
Query OK, 1 row affected, 1 warning (0.04 sec)
mysql> SELECT @pricelow, @pricehigh, @priceaverage;+———–+————+——————–+
| @pricelow | @pricehigh | @priceaverage |
+———–+————+——————–+
| 2.50 | 55.00 | 16.13 |
+———–+————+——————–+
所有 MySQL 变量都必须以 @ 开始。
另外一个例子：
mysql> DELIMITER //
mysql> CREATE PROCEDURE ordertotal(
-> IN onumber INT,
-> OUT ototal DECIMAL(8, 2)
-> )
-> BEGIN
-> SELECT Sum(item_price * quantity)
-> FROM orderitems
-> WHERE order_num = onumber
-> INTO ototal;
-> END //onumber 定义为 IN ，因为订单号被传入存储过程。ototal 定义为 OUT ，因为要从存储过程返回合计。
mysql> CALL ordertotal(20005, @total);
mysql> SELECT @total;+——–+
| @total |
+——–+
| 149.87 |
+——–+
(5) 建立智能存储过程
-- create_procedure.sql
-- Name: ordertotal
-- Parameters: onumber = order number
-- taxable = 0 if not taxable, 1 if taxable
-- ototal = order total variable
DELIMITER //
CREATE PROCEDURE ordertotal(
IN onumber INT,
IN taxable BOOLEAN,
OUT ototal DECIMAL(8, 2))
COMMENT 'Obtain order total, optionally adding tax'
BEGIN
-- Declare variable for total
DECLARE total DECIMAL(8, 2);
-- Declare tax percentage
DECLARE taxrate INT DEFAULT 6;
-- Get the order total
SELECT Sum(item_price * quantity) FROM orderitems where order_num = onumber
INTO total;
-- Is this taxable?
IF taxable THEN
-- Yes, so add taxrate to the total
SELECT total + (total / 100 * taxrate) INTO total;
END IF;
-- And finally, save to out variable
SELECT total INTO ototal;
END//
DELIMITER ;此存储过程有很大的变动。首先，增加了注释（前面放置 –）。添加了另外一个参数 taxable，它是一个布尔值（如果要增加税则为真，否则为假）。在存储过程体中，用 DECLARE 语句定义了两个局部变量。DECLARE 要求指定变量名和数据类型，它也支持可选的默认值。
COMMENT 关键字是可选的，如果给出，将在 SHOW PROCEDURE STATUS 的结果中显示。
mysql> CALL ordertotal(20005, 0, @total);
mysql> SELECT @total;+——–+
| @total |
+——–+
| 149.87 |
+——–+
mysql> CALL ordertotal(20005, 0, @total);
mysql> SELECT @total;+——–+
| @total |
+——–+
| 158.86 |
+——–+
(6) 检查存储过程
检查创建存储过程的SQL语句：
SHOW CREATE PROCEDURE ordertotal;
如果想获得详细信息使用：
SHOW PROCEDURE STATUS; // 列出所有的存储过程的详细信息
可以使用 LIKE 起到过滤的作用：
SHOW PROCEDURE STATUS LIKE 'ordertotal';
七、MySQL游标
7.1 什么是游标
有时，需要在检索出来的行中前进或后退一行或多行。游标（cursor）是一个存储在MySQL服务器上的数据库查询，它不是一条 SELECT 语句，而是被该语句检索出来的结果集。在存储了游标之后，应用程序可以根据需要滚动或浏览器中的数据。
7.2 使用游标
步骤：
1) 在能够使用游标前，必须声明(定义)它，这个过程实际上没有检索数据，它只是定义要使用的 SELECT 语句。
2) 一旦声明后，必须打开游标以供使用。这个过程用前面定义的 SELECT 语句把数据实际检索出来。
3) 对于填有数据的游标，根据需要取出各行。
4）在结束游标使用时，必须关闭游标。
(1) 创建游标
-- create_cursor.sql
DELIMITER //
CREATE PROCEDURE processorder( )
BEGIN
-- Declare the cursor
DECLARE ordernumbers CURSOR
FOR
SELECT order_num FROM orders;
-- Open the cursor
OPEN ordernumbers;
-- Close the cursor
CLOSE ordernumbers;
END//
DELIMITER ;MySQL中的游标只能用于存储过程，DECLARE 语句用来定义和命名游标，这里为 ordernumbers，在存储过程处理完成后，游标就会消失，因为它局限于存储过程。该存储过程只是打开和关闭了游标，并没有使用里面的数据。CLOSE 释放游标使用的内存资源，因此在每个游标不再需要时都应该关闭。如果你不明确关闭游标，MySQL 将会在到达END语句时自动关闭它。
(2) 使用游标
-- use_cursor.sql
DELIMITER //
CREATE PROCEDURE processorder( )
BEGIN
-- Declare local variables
DECLARE done BOOLEAN DEFAULT 0;
DECLARE onumber INT ；
DECLARE t DECIMAL(8, 2);
-- Declare the cursor
DECLARE ordernumbers CURSOR
FOR
SELECT order_num FROM orders;
-- Declare continue handler
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
-- Create a table to store the results
CREATE TABLE IF NOT EXISTS ordertotals
(order_num INT, total DECIMAL(8, 2));
-- Open the cursor
OPEN ordernumbers;
-- Loop through all rows
REPEAT
-- Get order number
FETCH ordernumbers INTO onumber;
-- Get the total for this order
CALL ordertotal(onumber, 1, t);
-- Insert order and total into ordertotals
INSERT INTO ordertotals(order_num, total) VALUES(onumber, t);
-- End of loop
UNTIL done END REPEAT;
-- Close the sursor
CLOSE ordernumbers;
END//
DELIMITER ; 在一个游标被打开后，可以使用 FETCH 语句分别访问它的每一行。FETCH 取出检索的数据同时它还向前移动游标中的内部行指针。该例子中的 FETCH 是在 REPEAT 内，因此它反复执行直到 done 为真（由 UNTIL done END REPEAT; 实现）。结束循环条件的语句为：
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
这条语句定义了一个 CONTINUE HANDLER，它是在条件出现时被执行的代码。这里，它指出当 SQLSTATE ‘02000’ 出现时，SET done = 1。SQLSTATE ‘02000’ 是一个未找到条件，当 REPEAT 由于没有更多的行共循环时，出现这个条件。
该存储过程，计算出每个订单号的带税的合计，并新建一个表，把这些数据插入到新建的表中。
mysql> source ./work/MySQL/use_cursor.sql;
mysql> CALL processorder( );
mysql> SELECT * FROM ordertotals;+————+———–+
| order_num | total |
+————+———–+
| 20005 | 158.86 |
| 20009 | 40.78 |
| 20006 | 58.30 |
| 20007 | 1060.00 |
| 20008 | 132.50 |
| 20008 | 132.50 |
+————+———–+
八、MySQL触发器
8.1 什么是触发器
如果你先让某些语句在事件发生时自动执行，就需要用到触发器。触发器是 MySQL 响应以下任意语句而自动执行的一条 MySQL 语句：
DELETE; INSERT; UPDATE;
其它MySQL语句不支持触发器。
8.2 创建触发器
mysql> CREATE TRIGGER newproduct AFTER INSERT ON ordertotals
> FOR EACH ROW SELECT 'Product added' INTO @q;创建触发器的语法：
CREATE
[DEFINER = { user | CURRENT_USER }]
TRIGGER trigger_name
trigger_time trigger_event
ON tbl_name FOR EACH ROW
trigger_body
trigger_time: { BEFORE | AFTER }
trigger_event: { INSERT | UPDATE | DELETE }注意：MySQL5.6 中的触发器不能返回结果集； 只有表支持触发器，视图和临时表都不支持。
这个触发器，在每次向表 ordertotals 插入数据时（对于每行）都会执行 SELECT ‘Product added’ INTO @q 。
8.3 删除触发器
mysql> DROP TRIGGER newproduct;触发器不能更新或覆盖，为了修改一个触发器，必须先删除它然后重新创建。
8.4 使用触发器
(1) INSERT触发器
在 INSERT 触发器代码内，可引用一个名为 NEW 的虚拟表，访问被插入的行；
在 BEFORE INSERT 触发器中， NEW 中的值也可以被更新（允许更改将要被插入的值）；
对于 AUTO_INCREMENT 列，NEW在 INSERT 之前包含0，在INSERT 之后包含新的自动生成值。
mysql> CREATE TRIGGER neworder AFTER INSERT ON orders
-> FOR EACH ROW SELECT NEW.order_num INTO @o_num;
mysql> INSERT INTO orders(order_date, cust_id)
-> VALUES(Now(), 10001);
mysql> SELECT @o_num;+———-+
| @o_num |
+———-+
| 20010 |
+———-+
(2) DELETE触发器
在 DELETE 触发器代码内，你可以引用一个名为 OLD 的虚拟表，访问被删除的行；
OLD 中的值全部都是只读的，不能更新；
mysql> DELIMITER //
mysql> CREATE TRIGGER deletemytable BEFORE DELETE ON mytable
-> FOR EACH ROW
-> BEGIN
-> SELECT OLD.name INTO @n;
-> INSERT INTO mytable_new(name, myphone) VALUES(OLD.name, OLD.phone);
-> END//
mysql> DELETE FROM mytable WHERE name = 'Joy';
mysql> SELECT * FROM mytable_new;
mysql> SELECT @n;+——+
| @n |
+——+
| Joy |
+——+
在触发器中使用 BEGIN END 块的好处是触发器能容纳多条SQL语句。
(3) UPDATE触发器
在 UPDATE 触发器代码中，你可以引用一个名为 OLD 的虚拟表访问以前（UPDATE语句执行前）的值，引用一个名为 NEW 的虚拟表访问新更新的值；
在 BERFORE UPDATE 触发器中，NEW 中的值可能也被更新（允许更改将要用于UPDATE语句中的值）；
OLD中的值全都是只读的，不能更新。
mysql> CREATE TRIGGER updatemytable BEFORE UPDATE ON mytable
-> FOR EACH ROW SET NEW.name = Upper(NEW.name);
mysql> UPDATE mytable SET name = 'John' WHERE myid = 1004;
mysql> SELECT name FROM mytable WHERE myid = 1004;+——+
| name |
+——+
| JOHN |
+——+
早期版本（具体哪个版本开始可以不知）不允许在触发器代码中使用 CALL 调用存储过程，在 MySQL 5.6 中是可以的。