存储过程、游标、触发器

1. 存储过程

1.1 引言

以下来自岗前培训：

存储过程以前用的比较多，现在用的相对少，但仍应该对其进行了解。

1. 存储过程只在创造时进行编译，以后每次执行存储过程都不需再重新编译，而一般 SQL 语句每执行一次就编译一次，所以使用存储过程可提高数据库执行速度。
2. 当对数据库进行复杂操作时（如对多个表进行 Update,Insert,Query,Delete 时），可将此复杂操作用存储过程封装起来与数据库提供的事务处理结合一起使用。这些操作，如果用程序来完成，就变成了一条条的 SQL 语句，可能要多次连接数据库。而换成存储，只需要连接一次数据库就可以了。
3. 存储过程可以重复使用，可减少数据库开发人员的工作量。
4. 安全性高，可设定只有此用户才具有对指定存储过程的使用权。

存储过程是一条或多条 MySQL 语句的集合，可将其视为批文件，虽然它们的作用不仅限于批处理。

使用存储过程有 3 个主要的好处：简单、安全、高性能（使用存储过程比使用单独 SQL 语句要快）。

MySQL 称存储过程的执行为调用，相应关键字为CALL。

1.2 相关操作

• CREATE PROCEDURE：创建名为productpricing的存储过程，用BEGIN和END语句限定存储过程体。

  CREATE PROCEDURE productpricing()
  BEGIN
  	SELECT Avg(prod_price) AS priceaverate
  	FROM products;
  END

• CALL：CALL接受存储过程的名字以及需要传递给它的任意参数。左边分别对应：

  1. 执行上述定义的存储过程

  2. 执行另一带参数的存储过程

     CALL productpricing();

     CALL productpricing(@pricelow,
                        	@pricehigh,
                        	@priceaverage);

• DROP PROCEDURE：删除存储过程

  DROP PROCEDURE productpricing;

• 使用参数

  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;

  1. 须先删除原有的同名存储过程，方可重新创建，否则会错误

  2. 其执行如上CALL行第 2 条所述，所有 MySQL 变量都必须以@开始

  3. 为了显示检索出的产品平均价格，可如下进行：

     SELECT @priceaverage;
     
     SELECT @pricehigh, @pricelow, @priceaverage;

• SHOW CREATE PROCEDURE：显示用来创建一个存储过程的CREATE语句：

  SHOW CREATE PROCEDURE ordertotal;

• SHOW PROCEDURE STATUS：获得何时、由谁创建等详细信息的存储过程列表，可使用LIKE指定一个过滤模式：

  SHOW PROCEDURE STATUS LIKE 'ordertotal';

1.3 其他

MySQL 支持 3 种类型的参数：

1. IN——传递给存储过程

2. OUT——从存储过程中传出

3. INOUT——对存储过程传入和传出

用DECLARE语句可定义局部变量。

BOOLEAN值指定1为真，0为假。实际上，非零值都考虑为真，只有0被视为假。

IF语句还支持ELSEIF和ELSE子句，ELSEIF还可使用THEN子句。

2. 游标

2.1 引入

MySQL 5 增加了对游标的支持。

游标（cursor） 是一个存储在 MySQL 服务器上的数据库查询，它不是一条SELECT语句，而是被该语句检索出来的结果集。在存储了游标之后，应用程序可以根据需要滚动或浏览其中的数据，游标主要用于交互式应用。

MySQL 游标只能用于存储过程和函数。

2.2 使用步骤

步骤：

1. 声明（定义）一个游标，DECLARE 关键字

   CREATE PROCEDURE processorders()
   BEGIN
   	DECLARE ordernumbers CURSOR
   	FOR
   	SELECT order_num FROM orders;
   END;

   这里由于游标位于存储过程中，当存储过程处理完成后，游标就消失。

2. 打开游标以供使用

   OPEN ordernumbers;

3. 使用游标

   CREATE PROCEDURE processorders()
   BEGIN
       -- Declare local variables
       DECLARE done BOOLEAN DEFAULT 0;
       DECLARE o 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 o;
   
           -- Get the total for this order
           CALL ordertotal(o, 1, t);
   
           -- Insert order and total into ordertotals
           INSERT INTO ordertotals(order_num, total)
           VALUE(o, t);
   
       -- End of loop
       UNTIL done END REPEAT;
   
       -- Close the cursor
       CLOSE ordernumbers;
   
   END

   • FETCH指定检索什么数据（所需的列），检索出来的数据存储在什么地方，同时它还向前移动游标中的内部行指针，使下一条FETCH语句检索下一行（不重复读取）。

   • DECLARE语句的发布存在特定的次序：局部变量必须在定义任意游标或句柄之前定义，句柄必须在游标之后定义。

   • REPEAT语句为一种循环语句。

   • 下面这条语句定义了一个CONTINUE HANDLER，它是在条件出现时被执行的代码，在这里，当SQLSTATE '02000'出现时，SET done=1。

     DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;

4. 在结束游标使用时，必须关闭游标

声明游标后，可根据需要频繁地打开和关闭游标。

3. 触发器

MySQL 5 增加了对触发器的支持。

触发器是 MySQL 响应以下任意语句而自动执行的一条 MySQL 语句或位于BEGIN/END语句之间的一组语句。

• DELETE
• INSERT
• UPDATE

只有表才支持触发器，视图和临时表都不支持。

触发器按每个表每个事件每次地定义，每个表每个事件每次只允许一个触发器，因此每个表最多支持 6 个触发器，即每个INSERT, UPDATE, DELETE语句的之前和之后。单一触发器不能与多个事件或多个表关联。

触发器不能更新或覆盖。若想修改一个触发器，必须先删除然后重新创建。

创建触发器流程：

1. 在该表中唯一的触发器名（但最好在数据库范围内唯一）

2. 触发器关联的表

3. 触发器应该响应的活动（DELETE, INSERT 或 UPDATE）

4. 触发器何时执行（处理之前或之后）

一些使用要点：

1. 与其他 DBMS 相比，MySQL 5 中支持的触发器相当初级

2. 应该用触发器来保证数据的一致性，如大小写、格式等

3. 触发器的一种非常有意义的使用是创建审计跟踪

4. MySQL 触发器中不支持 CALL 语句，这表示不能从触发器内调用存储过程，故所需的存储过程代码需要复制到触发器内

语法：

• CREATE TRIGGER创建触发器：

  CREATE TRIGGER newproduct AFTER INSERT ON products
  FOR EACH ROW SELECT 'Product added';

• DROP TRIGGER删除触发器：

  DROP TRIGGER newproduct;

• INSERT 触发器

  CREATE TRIGGER neworder AFTER INSERT ON orders
  FOR EACH ROW SELECT NEW.order_num;
  
  INSERT INTO orders(order_date, cust_id)
  VALUES(Now(), 10001);
  
  /*
  +-------------+
  |  order_num  |
  +-------------+
  |       20010 |
  +-------------+
  */

  • 在INSERT触发器代码内，可引用一个名为NEW的虚拟表，访问被插入的行；

  • 在BEFORE INSERT触发器中，NEW中的值也可以被更新（允许更改被插入的值）；

  • 对于AUTO_INCREMENT列，NEW在INSERT执行之前包含0，在INSERT执行之后包含新的自动生成值。

• DELETE 触发器

  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;

  • 在触发器代码内，你可以引用一个名为OLD的虚拟表，访问被删除的行；
  • OLD中的值全部是只读的，不能更新。

• UPDATE 触发器

  CREATE TRIGGER updatevendor BEFORE UPDATE ON vendors
  FOR EACH ROW SET NEW.vend_state = Upper(NEW.vend_state);

  • 在UPDATE触发器代码中，你可以引用一个名为OLD的虚拟表访问UPDATE语句前的值，引用一个名为NEW的虚拟表访问新更新的表；
  • 在BEFORE UPDATE触发器中，NEW中的值可能也被更新（允许更改将要用于UPDATE语句中的值）；
  • OLD中的值全部是只读的，不能更新。