08 存储过程 游标 触发器

8.1 存储过程

概述

存储过程是为以后的使用而保存的一条或多条MySQL语句的集合。可以看成函数或者批文件。

使用存储过程

创建

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

注意在命令行下;本身就是语句的分隔符,为了和存储过程内部的结尾分隔符区分开,可以自定义DELIMITER

DELIMITER //
CREATE PROCEDURE productpricing()
BEGIN
SELECT Avg(prod_price) AS priceaverage
FROM products;
END //
DELIMITER ;

调用

mysql> CALL productpricing();
+--------------+
| priceaverage |
+--------------+
| 16.133571 |
+--------------+

删除

DROP PROCEDURE productpricing;

注意删除不存在的存储过程会报错,如果不确定存不存在,可以用DROP PROCEDURE IF EXISTS

指定参数

在参数前可以加三个关键字

  • IN:传递给存储过程

  • OUT:从存储过程传到外面

  • INOUT:传入和传出

DELIMITER //
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 //
DELIMITER ;

调用并显示传除的参数

mysql> CALL producpricing(
-> @pricelow,
-> @pricehigh,
-> @priceaverage
-> );
mysql> SELECT @priceaverage;
+---------------+
| @priceaverage |
+---------------+
| 16.13 |
+---------------+

在看一个IN的例子,根据我们传入的订单号,和是否加税,显示那个订单的总价格

DELIMITER //
CREATE PROCEDURE ordertotal(
IN onumber INT,
IN taxable BOOLEAN,
OUT ototal DECIMAL(8, 2)
) COMMENT 'Obtain order total, optionally adding tax'
BEGIN
DECLARE total DECIMAL(8, 2);
DECLARE taxrate INT DEFAULT 6;
SELECT Sum(item_price*quantity)
FROM orderitems
WHERE order_num = onumber
INTO total;
IF taxable THEN
SELECT total+(total / 100 * taxrate) INTO total;
END IF;
SELECT total INTO ototal;
END //
DELIMITER ;

分别不加税和加税调用存储过程,可以看到不同的结果

mysql> CALL ordertotal(20005, 0, @total);
Query OK, 1 row affected (0.01 sec)
mysql> SELECT @total;
+--------+
| @total |
+--------+
| 149.87 |
+--------+
mysql> CALL ordertotal(20005, 1, @total);
Query OK, 1 row affected (0.01 sec)
mysql> SELECT @total;
+--------+
| @total |
+--------+
| 158.86 |
+--------+

检查存储过程

检查单个存储过程

SHOW CREATE PROCEDURE procedure_name;

列出所有存储过程

SHOW PROCEDURE STATUS;

8.2 游标

使用游标,我们可以在检索出的行中进行前进或后退。不像大多数DBMS,MySQL的游标只能用于存储过程和函数。

DELIMITER //
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 ordernumbers;
REPEAT
FETCH ordernumbers INTO o;
CALL ordertotal(o, 1, t);
INSERT INTO ordertotals(order_num, total)
VALUES(o, t);
UNTIL done END REPEAT;
CLOSE ordernumbers;
END //
DELIMITER ;

SQLSTATE '02000'是一个未找到的状态,也就是说游标没有指向数据后,会停止循环。FETCH语句可以访问每一行,并且把游标向前移动一行。

结果如下

mysql> CALL processorders();
Query OK, 1 row affected (0.21 sec)
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 |
+-----------+---------+

8.3 触发器

简介

只有三个语句能使用触发器

  • DELETE

  • INSERT

  • UPDATE

每个语句有BEFORE和AFTER两种情况。因此,一个表最多能支持6个触发器。单一触发器不能与多个事件或多个表关联。只有表才支持触发器,视图不支持触发器。

INSERT触发器

几点需要注意:

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

  • BEFORE INESRT触发器中,NEW的值可以被更新

  • 对AUTO_INCREMENT的列,NEW在INSERT之前只是0,INSERT之后则是自增的那个值

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

DELETE触发器

在DELETE触发器中,可以引用一个OLD表,访问被删除的行。且OLD表只读。

下面的例子把一个被删除的行归档到一个表中

CREATE TRIGGER deleteorder BEFORE DELETE ON orders
FOR EACH ROW
BEGIN
INSERT INTO archived_orders(order_num, order_date, cust_id)
VALUES(OLD.order_num, OLD.order_date, OLD.cust_id);
END;

UPDATE触发器

UPDATE触发器中可以引用OLD和NEW两个表,分别表示更新前的的旧数据和更新后的新数据。NEW可读写,OLD只读。

下面的例子保证州名的缩写都是大写

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