MySQL基于实例sales创造自定义函数、视图、存储过程及触发器
实例：数据库sales
1.客户表（Customer）
| 客户编号(CusNo) | 姓名(CusName) | 地址(Address) | 电话(Tel) |
| C001 | 杨婷 | 北京 | 010-5328953 |
| C002 | 李和平 | 上海 | 021-62359651 |
| C003 | 叶新 | 成都 | 024-3222781 |
| C004 | 冯辰诚 | 上海 | 021-87235965 |
2.产品表(Product)
| 产品编号(ProNo) | 品名(ProName) | 单价(price) | 库存数量(Stocks) |
| P0001 | 液晶电视 | 5600.00 | 800 |
| P0002 | 空调 | 2390.00 | 460 |
| P0003 | 洗衣机 | 3700.00 | 600 |
| P0004 | 电热水器 | 890.00 | 120 |
3.销售表(ProOut)
| 销售日期(SaleDate) | 客户编号(CusNo) | 产品编号(ProNo) | 销售数量(Quantity) |
| 2007-10-27 | C001 | P0001 | 3 |
| 2007-11-06 | C004 | P0003 | 40 |
| 2007-12-27 | C001 | P0003 | 5 |
| 2008-3-15 | C002 | P0002 | 12 |
| 2008-05-02 | C003 | P0002 | 21 |
| 2008-05-02 | C003 | P0001 | 9 |
| 2008-09-21 | C004 | P0001 | 30 |
| 2008-11-21 | C004 | P0001 | 73 |
一、创建一自定义函数sumMoney，要求能够利用该函数计算出销售金额，并进行测试，利用该函数计算出每种产品(ProNo)的销售金额。
1 DELIMITER $$
2 CREATE FUNCTION sumMoney( pno VARCHAR(10)) -- 输入产品编号
3 RETURNS DOUBLE(10,2) -- 返回金额数据类型
4 BEGIN -- 函数体（返回销售金额=产品单价*销售数）
5 RETURN
6 (SELECT SUM(quantity) FROM proout po,product pr WHERE po.prono=pr.prono AND pr.prono=pno GROUP BY po.prono) --销售数
7 *(SELECT pr.price FROM product pr WHERE pr.prono=pno ); --单价
8 END$$
9 DELIMITER ;
10
11 测试：SELECT sumMoney('P0001');
二、创建视图viewPro，要求显示每种产品的销售量和销售金额。
1 DELIMITER $$
2 CREATE FUNCTION sumMoney( pno VARCHAR(10)) -- 输入产品编号
3 RETURNS DOUBLE(10,2) -- 返回金额数据类型
4 BEGIN -- 函数体（返回销售金额=产品单价*销售数）
5 RETURN
6 (SELECT SUM(quantity) FROM proout po,product pr WHERE po.prono=pr.prono AND pr.prono=pno GROUP BY po.prono) --销售数
7 *(SELECT pr.price FROM product pr WHERE pr.prono=pno ); --单价
8 END$$
9 DELIMITER ;
10
11 测试：SELECT sumMoney('P0001');
三、创建存储过程p_Pro, 要求能够根据指定的客户编号，统计该客户购买每种产品的产品号、数量。
1 DELIMITER $$
2 CREATE
3 PROCEDURE p_Pro(cno VARCHAR(10)) -- 创建存储过程PROCEDURE，名称 p_Pro，参数名称及参数类型（cno VARCHAR(10)）
4 BEGIN
5 SELECT po.prono AS'产品号',SUM(po.quantity)AS'数量'
6 FROM proout po WHERE po.cusno=cno GROUP BY po.prono; -- 存储的内容
7 END$$
8 DELIMITER ;
9
10 测试：CALL p_Pro('C004'); -- 使用CALL关键字
四、创建一个触发器t_Stocks，要求当插入销售表(ProOut)的销售记录时，根据销售数量(Quantity)的变化，能更新产品表(Product)中相应的库存数量 (Stocks)。
这里需要注意的是new和old的用在after和before时有不同，如下：
1 DELIMITER $$
2 CREATE TRIGGER t_Stocks AFTER INSERT
3 ON proout FOR EACH ROW
4 BEGIN
5 DECLARE num INT ; -- 定义变量，关键字DECLARE
6 SET num =
7 (SELECT stocks FROM product WHERE prono = new.prono);
8 IF num < new.quantity
9 THEN SET new.quantity = num ;
10 END IF ; -- IF 条件表达式 THEN 执行语句 END IF;
11 /*假设给的销售数大于库存数，那新的库存数将为负数，这与实际不相符，所以对销售数进行判断，如果大于库存数，重新赋值销售数=库存数*/
12 UPDATE product SET stocks = stocks - new.quantity WHERE prono = new.prono ;
13 END $$
14 DELIMITER ;
运行结果：出现错误 Error Code :1362
Updating of NEW row is not allowed in after trigger
原因是什么呢？是因为：
AFTER是先完成数据的INSERT/UPDATE/DELETE，再触发，触发的语句晚于监视的增删改操作，无法影响前面的INSERT/UPDATE/DELETE动作。
也就是说在AFTER中对new数据进行重新赋值不能影响前面的INSERT/UPDATE/DELETE动作，也就变得没有意义，因此在AFTER中不能对new数据进行 赋值，只能读取。
BEFORE是先完成触发，再进行INSERT/UPDATE/DELETE，触发的语句先于监视的INSERT/UPDATE/DELETE，也就是有机会判断、修改INSERT /UPDATE/DELETE操作，因此对new数据赋值要放在BEFORE中。
修改后语句：
1 DELIMITER $$
2 CREATE TRIGGER t_Stocks BEFORE INSERT
3 ON proout FOR EACH ROW
4 BEGIN
5 DECLARE num INT ;
6 SET num =
7 (SELECT stocks FROM product WHERE prono = new.prono);
8 IF num < new.quantity
9 THEN SET new.quantity = num ;
10 END IF ;
11 UPDATE product SET stocks = stocks - new.quantity WHERE prono = new.prono ;
12 END $$
13 DELIMITER ;
14
15 测试：INSERT INTO proout VALUES('2009-02-35','C002','P0001',900); -- 原来Stocks是800
五、在查询的基础上创建一张新表Cus，要求显示客户“C003”在2008年购买的产品号、数量。
MySQL提供的方法和SQL Server的 select （查询） into [新表] from [源表]方法不同，使用的是Create table [表名] as (查询)的方法。
复制整个表为 CREATE TABLE [新表] SELECT * FEOM [源表];
1 DROP TABLE IF EXISTS cus; 2 CREATE TABLE cus AS 3 SELECT po.prono,SUM(po.quantity) 4 FROM proout po 5 WHERE po.cusno='c003' AND YEAR(po.saledate)=2008 GROUP BY prono;