程式師世界 >> 數據庫知識 >> MYSQL數據庫 >> MySQL綜合教程 >> MySQL基於實例sales創建自定義函數、視圖、存儲過程及觸發器，mysqlsales

MySQL基於實例sales創建自定義函數、視圖、存儲過程及觸發器，mysqlsales

編輯：MySQL綜合教程

MySQL基於實例sales創建自定義函數、視圖、存儲過程及觸發器，mysqlsales

實例：數據庫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;