實驗7：存儲過程與函數(shù)的創(chuàng)建管理
一、實驗?zāi)康?/h3>
1. 理解存儲過程和函數(shù)的概念。
2. 掌握創(chuàng)建存儲過程和函數(shù)的方法。
3. 掌握執(zhí)行存儲過程和函數(shù)的方法。
4. 掌握游標(biāo)的定義、使用方法。
二、實驗內(nèi)容
1.驗證性實驗：某超市的食品管理的數(shù)據(jù)庫的Food表對其操作。
2.設(shè)計性試驗：學(xué)校教師管理數(shù)據(jù)庫中的teacherInfo表對其操作。
三、實驗步驟與實驗結(jié)果
（一）驗證性實驗
Food表的定義
| 字段名 |
字段描述 |
數(shù)據(jù)類型 |
主鍵 |
外鍵 |
非空 |
唯一 |
自增 |
| foodid |
食品編號 |
INT(4) |
是 |
否 |
是 |
是 |
是 |
| Name |
食品名稱 |
VARCHAR(20) |
否 |
否 |
是 |
否 |
否 |
| Company |
生產(chǎn)廠商 |
VARCHAR(30) |
否 |
否 |
是 |
否 |
否 |
| Price |
價格（單位：元） |
FLOAT |
否 |
否 |
是 |
否 |
否 |
| Product_time |
生產(chǎn)年份 |
YEAR |
否 |
否 |
否 |
否 |
否 |
| Validity_time |
保質(zhì)期（單位：年） |
INT(4) |
否 |
否 |
否 |
否 |
否 |
| address |
廠址 |
VARCHAR(50) |
否 |
否 |
否 |
否 |
否 |
某超市的食品管理的數(shù)據(jù)庫的Food表，F(xiàn)ood表的定義如表所示，
1.創(chuàng)建food表：
CREATE TABLE food(
foodid INT(4) NOT NULL UNIQUE auto_increment PRIMARY KEY,
name VARCHAR(20) NOT NULL,
company VARCHAR(20) NOT NULL,
price FLOAT NOT NULL,
product_time YEAR,
validity_time INT(4),
address VARCHAR(50)
)ENGINE=InnoDBDEFAULTCHARSET=utf8;?
2.向food插入數(shù)據(jù)
INSERT INTO food
VALUES
(1,'QQ餅干','QQ餅干廠',2.5,'2008',3,'北京'),
(2,'MN牛奶','MN牛奶廠',3.5,'2009',1,'河北'),
(3,'EE果凍','EE果凍廠',1.5,'2007',2,'北京'),
(4,'FF咖啡','FF咖啡廠',20,'2002',5,'天津'),
(5,'GG奶糖','GG奶糖',14,'2003',3,'廣東');
3.在food表上創(chuàng)建名為Pfood_price_count的存儲過程。其中存儲過程Pfood_price_count有3個參數(shù)。輸入?yún)?shù)為price_infol和price_info2，輸出參數(shù)為count。存儲過程的滿足：查詢food表中食品單價高于price_infol且低于price_info2的食品種數(shù)，然后由count參數(shù)來輸出，并且計算滿足條件的單價的總和。
代碼如下：
//使用“DELIMITER &&”將SQL語句的結(jié)束符號變成&&
DELIMITER &&
CREATE PROCEDURE Pfood_price_count (IN price_info1 FLOAT,IN price_info2 FLOAT, OUT count INT )
READS SQL DATA
BEGIN
//定義變量temp
DECLARE temp FLOAT;
//定義游標(biāo)match_price
DECLARE match_price CURSOR FOR SELECT price FROM food;
//定義條件處理。如果沒有遇到關(guān)閉游標(biāo)，就退出存儲過程
DECLARE EXIT HANDLER FOR NOT FOUND CLOSE match_price;
//為臨時變量sum賦值
SET @sum=0;
//用SELECT … INTO 語句來為輸出變量count賦值
SELECT COUNT(*) INTO count FROM food
WHERE price>price_info1 AND price<price_info2 ;
//打開游標(biāo)
OPEN match_price;
//執(zhí)行循環(huán)
REPEAT
//使用游標(biāo)match_price
FETCH match_price INTO temp;
//執(zhí)行條件語句
IF temp>price_info1 AND temp<price_info2
THEN SET @sum=@sum+temp;
END IF;
//結(jié)束循環(huán)
UNTIL 0 END REPEAT;
//關(guān)閉游標(biāo)
CLOSE match_price;
END &&
//將SQL語句的結(jié)束符號變成“;”
DELIMITER ;
4.使用CALL語句來調(diào)用存儲過程。查詢價格在2至18之間的食品種數(shù)。代碼如下：
CALL Pfood_price_count(2,18,@count) ;
5.使用SELECT語句查看結(jié)果。代碼如下：
SELECT @count,@sum;其中，count是存儲過程的輸出結(jié)果：sum是存儲過程中的變量，sum中的值滿足足條件的單價的總和。
6.使用DROP語句刪除存儲過程Pfood_price_count。代碼如下：
DROP PROCEDURE Pfood_price_count ;
7.使用存儲函數(shù)來實現(xiàn)（1）的要求。存儲函數(shù)的代碼如下：
DELIMITER &&
CREATE FUNCTION Pfood_price_count1(price_info1 FLOAT,price_info2 FLOAT )
RETURNS INT READS SQL DATA
BEGIN
RETURN (SELECT COUNT(*) FROM food
WHERE price>price_info1 AND price<price_info2 );
END &&
DELIMITER ;
8.調(diào)用存儲函數(shù)
SELECT Pfood_price_count1(2,18);
9.刪除存儲函數(shù)
DROP FUNCTION Pfood_price_count1;注：存儲函數(shù)只能返回一個值，所以只實現(xiàn)了計算滿足條件的食品種數(shù)。使用RETURN來將計算的食品種數(shù)返回回來。調(diào)用存儲函數(shù)與調(diào)用MySQL內(nèi)部函數(shù)的方式是一樣的。
（二）設(shè)計性試驗
學(xué)校教師管理數(shù)據(jù)庫中的teacherInfo表，其表的定義如下表所示，請完成如下操作。
| 字段名 |
字段描述 |
數(shù)據(jù)類型 |
主鍵 |
外鍵 |
非空 |
唯一 |
自增 |
| num |
教工號 |
INT(10) |
是 |
否 |
是 |
是 |
否 |
| name |
姓名 |
VARCHAR(20) |
否 |
否 |
是 |
否 |
否 |
| sex |
性別 |
VARCHAR(4) |
否 |
否 |
是 |
否 |
否 |
| birthday |
出生日期 |
DATETIME |
否 |
否 |
否 |
否 |
否 |
| address |
家庭住址 |
VARCHAR(50) |
否 |
否 |
否 |
否 |
否 |
1.創(chuàng)建teacherinfo表
CREATE TABLE teacherinfo(
num INT(10) NOT NULL UNIQUE PRIMARY KEY,
name VARCHAR(20) NOT NULL,
sex VARCHAR(4) NOT NULL,
birthday DATETIME,
address VARCHAR(50)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
2.向teacherInfo表中插入記錄：
INSERT INTO teacherinfo
VALUES
(1001,'張龍','男','1984-11-08','北京市昌平區(qū)'),
(1002,'李梅','女','1970-01-21','北京市海淀區(qū)'),
(1003,'王一豐','男','1976-10-30','北京市昌平區(qū)'),
(1004,'趙六','男','1980-06-05','北京市順義區(qū)');
3.創(chuàng)建名為teachernfo1的存儲過程。要求：存儲過程teachernfo1有3個參數(shù)。輸入?yún)?shù)為teacherid和type，輸出參數(shù)為info。滿足：根據(jù)編號(teacherid)來查詢teachernfo表中的記錄。如果type的值為1時，將姓名(name)傳給輸出參數(shù)info;如果type的值為2時，將年齡傳給輸出參數(shù)info;如果type為其他值，則返回字符串“Error”。
delimiter &&
create procedure teachernfo1(in teacherid int,in type int,out info varchar(20))
reads sql data
begin
case type
when 1 then
select name into info from teacherinfo where num=teacherid;
when 2 then
select year(now())-year(birthday) into info from teacher where num=teacherid;
else
select 'Error' into info;
end case;
end &&
delimiter ;
4.調(diào)用存儲過程，參數(shù)值teacher id為2，type為1
USE student;
CALL teachernfo1(1001,1,@info);
5.使用DROP PRODECURE語句來刪除存儲過程
DROP PROCEDURE teachernfo1
6.創(chuàng)建名為teacherinfo2的存儲函數(shù)。要求：存儲過程teacherinfo2有兩個參數(shù)：teacher id和type。滿足：根據(jù)編號(teacher id)來查詢teacher表中的記錄。如果type的值是1時，則返回姓名(name)值；如果type的值是2時，則返回年齡；如果type為其他值，則返回字符串“Error”。
delimiter &&
create function teacherinfo2(teacherid int,type int)
returns varchar(20) reads sql data
begin
declare temp varchar(20);
if type=1
then
select name into temp from teacherinfo where num=teacherid;
elseif type=2
then
select year(now())-year(birthday) into temp from teacherinfo where num=teacherid;
else
set temp='Error';
end if;
return temp;
end &&
delimiter ;
7.使用SELECT語句調(diào)用teacherinfo2存儲函數(shù)
select teacherinfo2(1002,1);
8.使用DROP FUNCTION語句來刪除teacherinfo2存儲函數(shù)。
drop function teacherinfo2;
四、觀察與思考
1.什么時候適合通過創(chuàng)建存儲過程來實現(xiàn)？
答：函數(shù)限制比較多，如不能用臨時表，只能用表變量等，而存儲過程的限制相對就比較少。
2.功能相同的存儲過程和存儲函數(shù)的不同點有哪些？
答： 存儲過程：可以使得對的管理、以及顯示關(guān)于及其用戶信息的工作容易得多。存儲過程是 SQL 語句和可選控制流語句的預(yù)編譯集合，以一個名稱存儲并作為一個單元處理。存儲過程存儲在數(shù)據(jù)庫內(nèi)，可由應(yīng)用程序通過一個調(diào)用執(zhí)行，而且允許用戶聲明變量、有條件執(zhí)行以及其它強(qiáng)大的編程功能。存儲過程可包含程序流、邏輯以及對數(shù)據(jù)庫的查詢。它們可以接受參數(shù)、輸出參數(shù)、返回單個或多個結(jié)果集以及返回值。
3.使用游標(biāo)對于數(shù)據(jù)檢索的好處有哪些？
答:游標(biāo)提供了一種對從表中檢索出的數(shù)據(jù)進(jìn)行操作的靈活手段游標(biāo)總是與一條SQL 選擇語句相關(guān)聯(lián)因為游標(biāo)由結(jié)果集和結(jié)果集中指向特定記錄的游標(biāo)位置組成。
五、實驗要求
按要求完成，詳細(xì)記錄操作步驟，書寫實驗報告。所有實驗環(huán)節(jié)均由每位學(xué)?生獨立完成，認(rèn)真記錄操作過程，嚴(yán)禁抄襲他人實驗結(jié)果。文章來源：http://www.zghlxwxcb.cn/news/detail-770835.html
六、實驗總結(jié)
通過本次實驗學(xué)習(xí)了SQL的存儲過程與函數(shù)，課有效的避免重復(fù)地編寫相同的SQL語句，方便開發(fā)人員，增強(qiáng)了SQL的功能和靈活性。文章來源地址http://www.zghlxwxcb.cn/news/detail-770835.html
到了這里，關(guān)于數(shù)據(jù)庫系統(tǒng)原理及MySQL應(yīng)用教程實驗七存儲過程與函數(shù)的創(chuàng)建管理的文章就介紹完了。如果您還想了解更多內(nèi)容，請在右上角搜索TOY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！
