你的位置:首页 > 数据库

[数据库]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.00800
P0002空调2390.00460
P0003洗衣机3700.00600
P0004电热水器890.00120

3.销售表(ProOut)

销售日期(SaleDate)客户编号(CusNo)产品编号(ProNo)销售数量(Quantity)
2007-10-27C001P00013
2007-11-06C004P000340
2007-12-27C001P00035
2008-3-15C002P000212
2008-05-02C003P000221
2008-05-02C003P00019
2008-09-21C004P000130
2008-11-21C004P000173

一、创建一自定义函数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
复制代码

          

    

时间:2016-07-25 17:48:01

 梅