MySQL入门（3）_mysql

• 六、MySQL存储过程
• 七、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 中是可以的。