北京理工大學(xué)-數(shù)據(jù)庫(kù)-實(shí)驗(yàn)_4-存儲(chǔ)過程、觸發(fā)器

1、(封皮頁(yè))北京理工大學(xué)數(shù)據(jù)庫(kù)實(shí)驗(yàn)4主講老師：趙曉林學(xué)生：李經(jīng)2012/11/214(目錄頁(yè))實(shí)驗(yàn) 4： 存儲(chǔ)過程、觸發(fā)器14.1 作業(yè)聲明14.2 實(shí)驗(yàn)?zāi)康?4.2.1 建立“學(xué)籍與成績(jī)管理系統(tǒng)”表格14.3 實(shí)驗(yàn)過程14.3.1 在“學(xué)籍與成績(jī)管理系統(tǒng)”表格中輸入不符合系統(tǒng)要求的數(shù)據(jù)14.3.2 建立存儲(chǔ)過程，計(jì)算學(xué)生的總學(xué)分、總成績(jī)，并保存在另一張表中24.3.3 查詢總成績(jī)表，并進(jìn)行排序34.3.4 在相關(guān)的表上建立觸發(fā)器，實(shí)現(xiàn)主外鍵的功能34.3.5 討論觸發(fā)器與主外鍵的異同44.3.6 在表上建立觸發(fā)器實(shí)現(xiàn)對(duì)數(shù)據(jù)錄入、修改的限制44.3.7 討論視圖、存儲(chǔ)過程、觸發(fā)器、用戶自定義函數(shù)

2、的使用范圍及優(yōu)缺點(diǎn)44.4 實(shí)驗(yàn)結(jié)論54.5 實(shí)驗(yàn)體會(huì)5實(shí)驗(yàn) 4： 存儲(chǔ)過程、觸發(fā)器4.1 作業(yè)聲明本次作業(yè)全部由本人完成，若存在抄襲或雷同現(xiàn)象，本人愿意接受老師相應(yīng)的處理4.2 實(shí)驗(yàn)?zāi)康?.2.1 建立“學(xué)籍與成績(jī)管理系統(tǒng)”表格不建立表之間的參照關(guān)系輸入數(shù)據(jù)，以便在表上進(jìn)行各種操作4.3 實(shí)驗(yàn)過程建立“學(xué)籍與成績(jī)管理系統(tǒng)”表格操作，輸入數(shù)據(jù)操作同實(shí)驗(yàn)三4.3.1 在“學(xué)籍與成績(jī)管理系統(tǒng)”表格中輸入不符合系統(tǒng)要求的數(shù)據(jù)（如學(xué)生學(xué)籍表中學(xué)號(hào)重復(fù)），建立適當(dāng)?shù)拇鎯?chǔ)過程，分別查找和刪除這些不合法的數(shù)據(jù)4.3.1.1 sql代碼insert into xs values('孫悟空',&

3、#39;2120100030','05','08111005','1990-5-1','男');-插入一行非法數(shù)據(jù)，假設(shè)學(xué)號(hào)合法范圍為1120100099create proc check_xhstart char(10),end char(10)asbegindelete from xswhere xs.xh<start or xs.xh>endendselect * from xswhere xs.xh<'1120100001'or xs.xh>'1120100099&#

4、39;exec check_xh '1120100001','1120100099' -調(diào)用存儲(chǔ)過程select * from xswhere xs.xh<'1120100001'or xs.xh>'1120100099'4.3.1.2 結(jié)果調(diào)用前后對(duì)比，非法數(shù)據(jù)已經(jīng)被刪除4.3.2 建立存儲(chǔ)過程，計(jì)算學(xué)生的總學(xué)分、總成績(jī)，并保存在另一張表中4.3.2.1 sql代碼create table cjd(xm varchar(8) not null,xf decimal(5,1),cj decimal(5,1),)crea

5、te proc cjd_showstart char(10),end char(10)asbegininsert into cjdselect xs.xm,sum(kc.xf),sum(xk.cj) from xs,kc,xkwhere xs.xh>=start and xs.xh<=endand xs.xh=xk.xhand xk.kcbh=kc.kcbhgroup by xs.xmEndexec cjd_show '1120100001','1120100099' select * from cjd4.3.2.2 結(jié)果4.3.3 查詢總成績(jī)表，并

6、進(jìn)行排序4.3.3.1 sql代碼select * from cjd order by cj4.3.3.2 結(jié)果成績(jī)表按照總成績(jī)升序排列4.3.4 在相關(guān)的表上建立觸發(fā)器，實(shí)現(xiàn)主外鍵的功能4.3.4.1 Sql代碼create trigger xdh_pk on xb -模擬主鍵after insert asif (select COUNT(*)from xb,inserted where xb.xdh=inserted.xdh)>1beginprint '插入數(shù)據(jù)重復(fù)'rollback transactionendcreate trigger xdh_fk on js

7、-模擬外鍵after insert asif (select COUNT(*)from xb,inserted where xb.xdh=inserted.xdh)=0beginprint '插入數(shù)據(jù)不合法'rollback transactionEndinsert into xb values('05','德語(yǔ)'); -插入重復(fù)數(shù)據(jù)insert into js values('姜子牙','9920100005','一級(jí)','09'); -非法數(shù)據(jù)，xdh09不存在于系表中4.3.4

8、.2 結(jié)果插入以上兩行數(shù)據(jù)時(shí)，結(jié)果分別為，系統(tǒng)顯示：“插入數(shù)據(jù)重復(fù)消息3609，級(jí)別16，狀態(tài)1，第1 行事務(wù)在觸發(fā)器中結(jié)束。批處理已中止?！币约埃骸安迦霐?shù)據(jù)不合法消息3609，級(jí)別16，狀態(tài)1，第1 行事務(wù)在觸發(fā)器中結(jié)束。批處理已中止。”可知利用觸發(fā)器實(shí)現(xiàn)主外鍵功能成功4.3.5 討論觸發(fā)器與主外鍵的異同4.3.5.1 結(jié)果1.主外鍵約束與觸發(fā)器在遇到問題時(shí)都可以返回給用戶一定的錯(cuò)誤信息。但是，觸發(fā)器可以返回?cái)?shù)據(jù)庫(kù)管理員自定義的錯(cuò)誤信息。2.用主鍵和外鍵約束來實(shí)現(xiàn)效率更高、運(yùn)算速度更快。3.用觸發(fā)器可以實(shí)現(xiàn)比主外鍵約束更為復(fù)雜的約束。4.3.6 在表上建立觸發(fā)器實(shí)現(xiàn)對(duì)數(shù)據(jù)錄入、修改的限制觸發(fā)

9、器功能：向?qū)W生表中插入數(shù)據(jù)時(shí)，出生日期不能大于今天、性別只能為男或女4.3.6.1 Sql代碼create trigger xsInfo_check on xs -模擬主鍵after insert asif (select chrq from inserted)>GETDATE()or (select xb from inserted) not in ('男','女')beginprint '插入數(shù)據(jù)不合法'rollback transactionendinsert into xs values('趙四','1120

10、100001','01','08111001','1990-1-1','仙');-性別不合法insert into xs values('趙五','1120100001','01','08111001','2052-1-1','男');-出生日期不合法insert into xs values('趙六','1120100001','01','08111001',&#

11、39;2011-1-1','男');-合法數(shù)據(jù)4.3.6.2 結(jié)果插入前兩行非法數(shù)據(jù)時(shí)，系統(tǒng)顯示：“插入數(shù)據(jù)不合法消息3609，級(jí)別16，狀態(tài)1，第1 行事務(wù)在觸發(fā)器中結(jié)束。批處理已中止?！辈迦氲谌泻戏〝?shù)據(jù)時(shí)，成功插入4.3.7 討論視圖、存儲(chǔ)過程、觸發(fā)器、用戶自定義函數(shù)的使用范圍及優(yōu)缺點(diǎn)4.3.7.1 結(jié)果視圖：使用范圍：適用于需要查看或更新數(shù)據(jù)的情況優(yōu)點(diǎn)：簡(jiǎn)化查詢，數(shù)據(jù)即時(shí)更新缺點(diǎn)：無法進(jìn)行刪除操作存儲(chǔ)過程：使用范圍：需要處理大量數(shù)據(jù)時(shí)優(yōu)點(diǎn)：存儲(chǔ)過程是已編譯過的，執(zhí)行起來速度更快；可以使用控制語(yǔ)句和變量，并且在一個(gè)存儲(chǔ)過程中可以調(diào)用其他存儲(chǔ)過程，大大提高了用戶設(shè)計(jì)程序的效率缺點(diǎn)：需要用戶自行設(shè)計(jì)安全可靠的存儲(chǔ)過程觸發(fā)器：使用范圍：通常用于實(shí)現(xiàn)強(qiáng)制業(yè)務(wù)規(guī)則和數(shù)據(jù)完整性優(yōu)點(diǎn)：支持嵌套，當(dāng)數(shù)據(jù)發(fā)生變化的時(shí)候, 自動(dòng)作處理缺點(diǎn)：可能會(huì)引起不必要的觸發(fā)邏輯 用戶自定義函數(shù)使用范圍：接受