實驗五++存儲過程和觸發(fā)器

1、實驗六：存儲過程和觸發(fā)器實驗六：存儲過程和觸發(fā)器一、實驗目的一、實驗目的v1、理解存儲過程的概念，掌握創(chuàng)建與運行存儲過、理解存儲過程的概念，掌握創(chuàng)建與運行存儲過程的一般方法，加深對客戶程的一般方法，加深對客戶/服務器機制的理解。服務器機制的理解。v2、掌握觸發(fā)器的創(chuàng)建方法，學習觸發(fā)器的使用，、掌握觸發(fā)器的創(chuàng)建方法，學習觸發(fā)器的使用，體會觸發(fā)器執(zhí)行的時機，加深對觸發(fā)器功能和作用體會觸發(fā)器執(zhí)行的時機，加深對觸發(fā)器功能和作用的理解。的理解。v3、比較存儲過程和觸發(fā)器的異同。、比較存儲過程和觸發(fā)器的異同。v4、掌握數(shù)據(jù)庫事務處理掌握數(shù)據(jù)庫事務處理 語句二、實驗內容二、實驗內容實驗數(shù)據(jù)：實驗數(shù)據(jù)：實驗二

2、所建的數(shù)據(jù)庫及表，實驗三錄入表實驗二所建的數(shù)據(jù)庫及表，實驗三錄入表中的數(shù)據(jù)。中的數(shù)據(jù)。1、存儲過程的建立和執(zhí)行、存儲過程的建立和執(zhí)行 分別建立有沒參數(shù)、有參數(shù)（定義缺省和不定義缺分別建立有沒參數(shù)、有參數(shù)（定義缺省和不定義缺?。?、有返回值的存儲過程。存儲過程的執(zhí)行?。?、有返回值的存儲過程。存儲過程的執(zhí)行2、觸發(fā)器的創(chuàng)建與執(zhí)行、觸發(fā)器的創(chuàng)建與執(zhí)行 各類觸發(fā)器分別建立一個，再設計一組有效和無效各類觸發(fā)器分別建立一個，再設計一組有效和無效的操作數(shù)據(jù)的操作數(shù)據(jù) ，觸發(fā)各個觸發(fā)器執(zhí)行觸發(fā)器執(zhí)行3、數(shù)據(jù)庫事務處理數(shù)據(jù)庫事務處理 操作語句三、實驗重點與難點三、實驗重點與難點1、重點：、重點：（1）有參數(shù)的存儲

3、過程的建立）有參數(shù)的存儲過程的建立（2）觸發(fā)器的執(zhí)行）觸發(fā)器的執(zhí)行2、難點：、難點： 設計一系列操作觸發(fā)觸發(fā)器的執(zhí)行。設計一系列操作觸發(fā)觸發(fā)器的執(zhí)行。四、實驗步驟四、實驗步驟1、恢復上次課的數(shù)據(jù)庫表結構和數(shù)據(jù)。、恢復上次課的數(shù)據(jù)庫表結構和數(shù)據(jù)。 2、完成以下操作、完成以下操作1）存儲過程的創(chuàng)建與執(zhí)行）存儲過程的創(chuàng)建與執(zhí)行（1）建立存儲過程）建立存儲過程P1，查詢選修了某門課程的學生信，查詢選修了某門課程的學生信息（其中課程名，返回值自行設計）息（其中課程名，返回值自行設計）（2）建立存儲過程）建立存儲過程P2，將某班的所有學生的成績放在，將某班的所有學生的成績放在一個臨時表中。一個臨時表中。（

4、3）建立存儲過程）建立存儲過程P3，計算某一個專業(yè)的某一門課和，計算某一個專業(yè)的某一門課和的平均成績的平均成績,顯示出專業(yè)名稱、課程名稱和平均成績顯示出專業(yè)名稱、課程名稱和平均成績（4）建立存儲過程）建立存儲過程p1，功能：查詢成績在，功能：查詢成績在X到到Y之間之間的學生信息，設計返回值：的學生信息，設計返回值： 當輸入的值為空時：返回值當輸入的值為空時：返回值10，提示，提示“請?zhí)嵴執(zhí)峁┓秶┓秶?！”；當輸入的；當輸入的X或或Y不在不在0到到100之間時：之間時：返回值返回值11，提示，提示“輸入的值不對！輸入的值不對！”；當輸入的；當輸入的值值XY時：返回值時：返回值12，提示，提示“

5、X應小于應小于Y！”；當；當查找后沒有滿足條件的記錄時：返回值查找后沒有滿足條件的記錄時：返回值-10，提示，提示“對不起！沒有滿足條件的記錄！對不起！沒有滿足條件的記錄！”（5）運行所建立的存儲過程，觀察其執(zhí)行結果。）運行所建立的存儲過程，觀察其執(zhí)行結果。2）觸發(fā)器的建立）觸發(fā)器的建立 首先刪除各個表中的外碼約束。首先刪除各個表中的外碼約束。（1）在課程表和成績表上建立一組觸發(fā)器，實現(xiàn)課程）在課程表和成績表上建立一組觸發(fā)器，實現(xiàn)課程表的課程號與成績表的課程號之間的參照關系。表的課程號與成績表的課程號之間的參照關系。（2）在成績表上建立一個插入和修改類的觸發(fā)器，實）在成績表上建立一個插入和修改

6、類的觸發(fā)器，實現(xiàn)當修改成績時，檢查修改后的成績是否在現(xiàn)當修改成績時，檢查修改后的成績是否在0到到100之間，如果是，插入或修改成功，否則失敗。之間，如果是，插入或修改成功，否則失敗。（3）觸發(fā)器的執(zhí)行）觸發(fā)器的執(zhí)行 分別對學生表、課程表和成績表進行插入、分別對學生表、課程表和成績表進行插入、刪除和修改操作，觸發(fā)相應觸發(fā)器的執(zhí)行，觀察其刪除和修改操作，觸發(fā)相應觸發(fā)器的執(zhí)行，觀察其執(zhí)行結果。執(zhí)行結果。（1）存儲過程示例：）存儲過程示例：create proc d_s2 classno1 varchar(20) asbeginselect * into #sc1 from sc where sno

7、in (select sno from student where classno=classno1) select * from #class11end（2）觸發(fā)器示例：）觸發(fā)器示例：alter trigger in_sc on sc for insert,updateasif not exists (select student.sno from student,inserted where student.sno=inserted.sno) or not exists (select o from course ,inserted where o=o)beginprint(沒有該學生學號

8、或沒有該課程號沒有該學生學號或沒有該課程號)rollbackendv3、數(shù)據(jù)準備：執(zhí)行如下命令Select * into #temp from Customers，將Customers的數(shù)據(jù)倒入到#temp表中。啟動事務，執(zhí)行刪除后，回滾事務v選擇#temp的數(shù)據(jù)，察看記錄總數(shù)v顯式啟動事務v刪除#temp表的數(shù)據(jù)v選擇#temp數(shù)據(jù)，察看記錄總數(shù)v回滾事務v選擇#temp數(shù)據(jù)，察看記錄總數(shù)啟動事務，執(zhí)行刪除后，提交事務v選擇#temp數(shù)據(jù)，察看記錄總數(shù)v顯式啟動事務v刪除#temp表的數(shù)據(jù)v選擇#temp的數(shù)據(jù)，察看記錄總數(shù)v回滾事務v選擇#temp數(shù)據(jù)，察看記錄總數(shù)v比較這兩次執(zhí)行效果的差

9、異，為什么會有這些比較這兩次執(zhí)行效果的差異，為什么會有這些差異？差異？五、注意：五、注意：1、如果為存儲過程指定參數(shù)且沒有定義參數(shù)的缺省值，、如果為存儲過程指定參數(shù)且沒有定義參數(shù)的缺省值，必須在運行時給出參數(shù)值必須在運行時給出參數(shù)值2、對表者操作時觸發(fā)相應的觸發(fā)器執(zhí)行，因此設計操、對表者操作時觸發(fā)相應的觸發(fā)器執(zhí)行，因此設計操作時必須要設計滿足觸發(fā)器條件和不滿足條件兩種作時必須要設計滿足觸發(fā)器條件和不滿足條件兩種操作，才能體會到觸發(fā)器的作用操作，才能體會到觸發(fā)器的作用v思考與練習思考與練習1、存儲過程與觸發(fā)器的運行方式有什么不同？、存儲過程與觸發(fā)器的運行方式有什么不同？2、編寫存儲過程實現(xiàn)對表的插入、刪除與修改、編寫存儲過程實現(xiàn)對表的插入、刪除與修改v實驗要求實驗要求v1、按照實驗要求進行實驗，實驗時注每種、按照實驗要求進行實驗，實驗時注每種SQL語語句的命令格式及關鍵字的含義，做好實驗記錄。句的命令格式及關鍵字的含義，做好實驗記錄。v2、實驗后：分析實驗結果，總結實驗知識，得出、實驗后：分析實驗結果，總結實驗知識，得出結論，按格式寫出實驗報告。結論，按格式寫出實驗報告。v3、在整個實驗過程中，要獨立思考、獨立按時完、在整個實驗過程中，要獨立思考、獨立按時完成實驗任務，不懂的要虛心向教師或同學請教。成實驗任務，不懂的要虛心向教師或同