一、實驗目的：

1. 理解存儲過程和函數的概念。
2. 掌握創建存儲過程和函數的方法。
3. 掌握執行存儲過程和函數的方法。
4. 掌握游標的定義、使用方法。

二、實驗內容

1．某超市的食品管理的數據庫的Food表，Food表的定義如表所示，
Food表的定義

各列有如下數據：
‘QQ餅干’,‘QQ餅干廠’,2.5,‘2008’,3,‘北京’
‘MN牛奶’,‘MN牛奶廠’,3.5,‘2009’,1,‘河北’
‘EE果凍’,‘EE果凍廠’,1.5,‘2007’,2,‘北京’
‘FF咖啡’,‘FF咖啡廠’,20,‘2002’,5,‘天津’
‘GG奶糖’,‘GG奶糖’,14,‘2003’,3,‘廣東’

（1） 在food表上創建名為Pfood_price_count的存儲過程。其中存儲過程Pfood_price_count有3個參數。輸入參數為price_infol和price_info2，輸出參數為count。存儲過程的滿足：查詢food表中食品單價高于price_infol且低于price_info2的食品種數，然后由count參數來輸出，并且計算滿足條件的單價的總和。

DELIMITER //
CREATE PROCEDURE Pfood_price_count(IN price_infol FLOAT, IN price_info2 FLOAT, OUT count INT)
BEGINDECLARE sum_price FLOAT DEFAULT 0;SELECT COUNT(*), SUM(price) INTO count, sum_price FROM food WHERE price > price_infol AND price < price_info2;SELECT CONCAT('滿足條件的食品種數為：', count) AS result;SELECT CONCAT('滿足條件的單價總和為：', sum_price) AS result;
END //
DELIMITER ;

(2) 使用CALL語句來調用存儲過程。查詢價格在2至18之間的食品種數。代碼如下：

CALL Pfood_price_count(2, 18, @count);

(3)使用DROP語句刪除存儲過程Pfood_price_count。代碼如下：

DROP PROCEDURE Pfood_price_count; 

(4) 使用存儲函數來實現（1）的要求。

DELIMITER //
CREATE FUNCTION Ffood_price_count(price_infol FLOAT, price_info2 FLOAT) RETURNS VARCHAR(100) DETERMINISTIC READS SQL DATA
BEGINDECLARE count INT DEFAULT 0;DECLARE sum_price FLOAT DEFAULT 0;SELECT COUNT(*), SUM(price) INTO count, sum_price FROM food WHERE price > price_infol AND price < price_info2;RETURN CONCAT('滿足條件的食品種數為：', count, '，滿足條件的單價總和為：', sum_price);
END //
DELIMITER ;

（5）調用存儲函數

SELECT Ffood_price_count(2, 18) AS result;

（6）刪除存儲函數

DROP FUNCTION Ffood_price_count;

2.學校教師管理數據庫中的teacherInfo表，其表的定義如下表所示，請完成如下操作。

向teacherInfo表中插入記錄：
1001,‘張龍’,‘男’,‘1984-11-08’,‘北京市昌平區’
1002,‘李梅’,‘女’,‘1970-01-21’,‘北京市海淀區’
1003,‘王一豐’,‘男’,‘1976-10-30’,‘北京市昌平區’
1004,‘趙六’,‘男’,‘1980-06-05’,‘北京市順義區’

（1）創建名為teachernfo1的存儲過程。要求：存儲過程teachernfo1有3個參數。輸入參數為teacherid和type，輸出參數為info。滿足：根據編號(teacherid)來查詢teachernfo表中的記錄。如果type的值為1時，將姓名(name)傳給輸出參數info;如果type的值為2時，將年齡傳給輸出參數info;如果type為其他值，則返回字符串“Error”。

DELIMITER //
CREATE PROCEDURE teachernfo1(IN teacherid INT, IN type INT, OUT info VARCHAR(50))
BEGINDECLARE name VARCHAR(20);DECLARE birthday DATETIME;DECLARE age INT;SELECT name, birthday INTO name, birthday FROM teacherInfo WHERE num = teacherid;SET age = YEAR(CURDATE()) - YEAR(birthday);IF type = 1 THENSET info = name;ELSEIF type = 2 THENSET info = age;ELSESET info = 'Error';END IF;
END //
DELIMITER ;

（2）調用存儲過程，參數值teacher id為2，type為1。

CALL teachernfo1(2, 1, @info);
SELECT @info;

（3）使用DROP PRODECURE語句來刪除存儲過程

DROP PROCEDURE teachernfo1;

（4）創建名為teacherinfo2的存儲函數。要求：存儲過程teacherinfo2有兩個參數：teacher id和type。滿足：根據編號(teacher id)來查詢teacher表中的記錄。如果type的值是1時，則返回姓名(name)值；如果type的值是2時，則返回年齡；如果type為其他值，則返回字符串“Error”。

DELIMITER //
CREATE FUNCTION teacherinfo2(teacherid INT, type INT) RETURNS VARCHAR(50)
BEGINDECLARE name VARCHAR(20);DECLARE birthday DATETIME;DECLARE age INT;SELECT name, birthday INTO name, birthday FROM teacherInfo WHERE num = teacherid;SET age = YEAR(CURDATE()) - YEAR(birthday);IF type = 1 THENRETURN name;ELSEIF type = 2 THENRETURN age;ELSERETURN 'Error';END IF;
END //
DELIMITER ;

（5）使用SELECT語句調用teacherinfo2存儲函數。

SELECT teacherinfo2(2, 1);

（6）使用DROP FUNCTION語句來刪除teacherinfo2存儲函數。

DROP FUNCTION teacherinfo2;

三、觀察與思考

(1) 什么時候適合通過創建存儲過程來實現？
mysql中適合通過創建存儲過程來實現的情況包括：執行復雜的數據庫操作，提高代碼重用性；封裝業務邏輯，簡化客戶端開發；減少網絡通信量，提升性能；實施細粒度的權限控制；確保事務的一致性和完整性。

(2) 功能相同的存儲過程和存儲函數的不同點有哪些？
mysql中存儲過程和存儲函數的主要不同點在于調用方式、返回值和語句類型。存儲過程可以執行復雜邏輯，支持輸出參數和多種sql語句類型，適合執行修改數據的操作。而存儲函數必須返回單個值，通常用于計算和返回數據，適用于select語句中，作為表達式的一部分。

(3)使用游標對于數據檢索的好處有哪些？
使用游標進行數據檢索的好處包括：可以逐行處理結果集中的數據，提供更細粒度的控制；適合復雜的數據處理邏輯，如多步驟計算或條件判斷；減少內存占用，尤其當處理大量數據時；提高代碼的靈活性和可讀性。