北京理工大學-數(shù)據(jù)庫-實驗_4-存儲過程、觸發(fā)器

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

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

3、#39;2120100030','05','08111005','1990-5-1','男');-插入一行非法數(shù)據(jù)，假設(shè)學號合法范圍為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)用存儲過程select * from xswhere xs.xh<'1120100001'or xs.xh>'1120100099'4.3.1.2 結(jié)果調(diào)用前后對比，非法數(shù)據(jù)已經(jīng)被刪除4.3.2 建立存儲過程，計算學生的總學分、總成績，并保存在另一張表中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 查詢總成績表，并

6、進行排序4.3.3.1 sql代碼select * from cjd order by cj4.3.3.2 結(jié)果成績表按照總成績升序排列4.3.4 在相關(guān)的表上建立觸發(fā)器，實現(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ù)重復'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','德語'); -插入重復數(shù)據(jù)insert into js values('姜子牙','9920100005','一級','09'); -非法數(shù)據(jù)，xdh09不存在于系表中4.3.4

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

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