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

實例：資料庫sales

1.客戶表（Customer）

2.產品表(Product)

3.銷售表(ProOut)

一、創建一自定義函數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;