菜单

MySQL基于实例sales创建自定义函数、视图、存储过程及触发器

2020年1月29日 - 计算机数据
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

 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'); 

图片 2

              
图片 3

二、创建视图viewPro,要求显示每种产品的销售量和销售金额。  

图片 4

 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'); 

图片 5

            
图片 6

三、创建存储过程p_Pro,
要求能够根据指定的客户编号,统计该客户购买每种产品的产品号、数量。

图片 7

 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关键字

图片 8

         
图片 9

四、创建一个触发器t_Stocks,要求当插入销售表(ProOut)的销售记录时,根据销售数量(Quantity)的变化,能更新产品表(Product)中相应的库存数量   (Stocks)。 

  这里需要注意的是new和old的用在after和before时有不同,如下:

图片 10

 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 ;

图片 11

  运行结果:出现错误 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中。

   修改后语句:

图片 12

 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

图片 13

          
图片 14

    图片 15

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

 梅

相关文章

发表评论

电子邮件地址不会被公开。 必填项已用*标注

网站地图xml地图