數(shù)據(jù)庫應(yīng)用實(shí)驗(yàn)四(姓名：繆王冰 班級(jí)：計(jì)科10-2班 學(xué)號(hào)：08103339)

1、中國礦業(yè)大學(xué)數(shù)據(jù)庫應(yīng)用實(shí)驗(yàn)報(bào)告姓 名：繆王冰 學(xué) 號(hào)：08103339 學(xué) 院：計(jì)算機(jī)科學(xué)與技術(shù) 專 業(yè)：計(jì)算機(jī)科學(xué)與技術(shù) 課程名稱：數(shù)據(jù)庫應(yīng)用 專 題：數(shù)據(jù)庫應(yīng)用（實(shí)驗(yàn)四） 指導(dǎo)教師：李政偉 目 錄1.第八章 存儲(chǔ)過程與觸發(fā)器31.1.實(shí)驗(yàn)?zāi)康?1.2.實(shí)驗(yàn)準(zhǔn)備31.3.實(shí)驗(yàn)內(nèi)容和步驟32.第九章 事務(wù)與游標(biāo)92.1.實(shí)驗(yàn)?zāi)康?2.2.實(shí)驗(yàn)準(zhǔn)備92.3.實(shí)驗(yàn)內(nèi)容和步驟103.實(shí)驗(yàn)體會(huì)161.第八章 存儲(chǔ)過程與觸發(fā)器1.1.實(shí)驗(yàn)?zāi)康?）理解存儲(chǔ)過程和觸發(fā)器的概念；2）學(xué)會(huì)使用對(duì)象資源管理器和T-SQL創(chuàng)建存儲(chǔ)過程；3）學(xué)會(huì)使用對(duì)象資源管理器和T-SQL創(chuàng)建觸發(fā)器；4）學(xué)會(huì)存儲(chǔ)過程觸發(fā)器的管理方

2、法。1.2.實(shí)驗(yàn)準(zhǔn)備1）在服務(wù)器上創(chuàng)建用戶數(shù)據(jù)庫student。2）在用戶數(shù)據(jù)庫student中創(chuàng)建學(xué)生基本信息表（stud_info）、教師基本信息表（teacher_info）、學(xué)生成績(jī)表（stud_grade）、課程信息表（lesson_info）、課程安排表（teach_schedule）、院系代碼表（dept_code）、專業(yè)代碼表（specialty_code）、教研室信息表（staffroom_info）和教室信息表（classroom_info）。3）通過對(duì)象資源管理器或SQL編輯器向各個(gè)數(shù)據(jù)表輸入實(shí)驗(yàn)數(shù)據(jù)。1.3.實(shí)驗(yàn)內(nèi)容和步驟1）創(chuàng)建和執(zhí)行存儲(chǔ)過程；針對(duì)教師基本信息表tea

3、cher_info，創(chuàng)建一個(gè)名稱為teacher_select的存儲(chǔ)過程，執(zhí)行存儲(chǔ)過程將從數(shù)據(jù)表teacher_info中根據(jù)編號(hào)檢索某一教師的姓名和職稱。use student gocreate procedure teacher_select(no char(6),nam nvarchar(4) output,title nchar(5) output)asselect nam=name,title=tech_title from teacher_info where teacher_id=no執(zhí)行已經(jīng)定義的存儲(chǔ)過程：declare nam varchar(6)declare title

4、char(10)execute teacher_select 010104,nam output,title outputselect 姓名：,nam,職稱：,title結(jié)果：建立一個(gè)插入教師記錄的存儲(chǔ)過程teacher_insert，在SQL編輯器中輸入該存儲(chǔ)過程的代碼并執(zhí)行。use student gocreate procedure teacher_insert(no char(6),nam nvarchar(4),sex nchar(1),age int,title nchar(5),tel varchar(12),sala decimal(7),num char(10)asinser

5、t into teacher_info values(no,nam,sex,age,title,tel,sala,num)goexecute teacher_insert 010119,N聶子楓,N男,40,N研究員,02034367219,250.0,0401010113go在對(duì)象資源管理器中檢查新紀(jì)錄是否已經(jīng)插入至教師基本信息表中。結(jié)果：建立一個(gè)修改教師記錄的存儲(chǔ)過程teacher_update，并執(zhí)行之。use studentgocreate procedure teacher_update (no char(6),nam nvarchar(4),sex nchar(1),age int

6、,title nchar(5),tel varchar(12),sala decimal(7),num char(10) as begin update teacher_info set name=nam, gender=sex, age=age, tech_title=title, telephone=tel, salary=sala, course_id=num where teacher_id=no endgo在SQL編輯器執(zhí)行EXEC語句調(diào)用存儲(chǔ)過程teacher_update，修改一個(gè)具體的教師記錄。execute teacher_update 010119,N聶風(fēng),N男,40,N研

7、究員,02034367219,250.0,0401010113go結(jié)果：建立一個(gè)刪除教師信息表中記錄的存儲(chǔ)過程teacher_delete，并執(zhí)行之。use student gocreate procedure teacher_delete(no char(6)asdelete from teacher_info where teacher_id=nogo在SQL編輯器中執(zhí)行EXEC語句調(diào)用存儲(chǔ)過程teacher_delete，刪除teacher_info中的teacher_id為“010119”的教師記錄。execute teacher_delete 010119go結(jié)果：獨(dú)立實(shí)踐：編寫一個(gè)

8、存儲(chǔ)過程來對(duì)student數(shù)據(jù)庫中的表stud_info進(jìn)行檢索、插入、修改、刪除的操作，然后調(diào)用這個(gè)存儲(chǔ)過程。use studentgocreate procedure stud_info_select_insert_alter_delet(id char(10),nam nvarchar(4),birth datetime,sex nchar(1),addr nvarchar(10),tel char(12),zip char(6),marke decimal(3,0)as select id=stud_id,nam=name,birth= birthday ,sex=gender ,ad

9、dr=address ,tel=telcode ,zip=zipcode ,marke=mark from stud_infowhere stud_id=idinsert into stud_infovalues(id,nam,birth,sex,addr,tel,zip,marke )update stud_infoset stud_id=id,name=nam,birthday=birth ,gender=sex ,address=addr ,telcode=tel ,zipcode=zip ,mark=markewhere stud_id=id delete from stud_info

10、where stud_id=id2）創(chuàng)建觸發(fā)器；使用CREATE TRIGGER 語句創(chuàng)建一個(gè)觸發(fā)器teacher_tri1，當(dāng)向表teacher_info中插入一條記錄時(shí)，自動(dòng)顯示表teacher_info中的記錄。在SQL編輯器中輸入觸發(fā)器的代碼并執(zhí)行。use studentgocreate trigger teacher_tri1on teacher_infofor insertas select * from teacher_infogo 觸發(fā)器建立完畢后，當(dāng)執(zhí)行下列插入操作時(shí)，觸發(fā)器將會(huì)激活而顯示數(shù)據(jù)表teacher_info中的全部記錄。insert into teacher_in

11、fovalues(010119,N聶曉基,N男,40,N講師,02034360338,250.0,0401010108)結(jié)果：使用系統(tǒng)存儲(chǔ)過程sp_helptext查看觸發(fā)器teacher_tri1的定義文本信息。參考語句如下：use studentexec sp_helptext teacher_tri1go在“SQL編輯器”的查詢窗口中執(zhí)行上面的語句，在結(jié)果窗格中將返回觸發(fā)器teacher_tri1的定義信息。結(jié)果：在數(shù)據(jù)庫student中的teacher_info 表上建立delete觸發(fā)器teacher_tri2，使得在刪除表teacher_info中記錄的同時(shí)，自動(dòng)檢查課程安排表te

12、ach_schedule中是否有該教師的記錄，如果存在該教師記錄，則取消刪除。參考語句如下：use studentgocreate trigger teacher_tri2on teacher_infofor deleteasif(select count(*) from teach_schedule inner join deleted on teach_schedule.teacher_id=deleted.teacher_id)0)begin raiserror(you can not delete the teacher_info with the order record. the

13、transaction will be cancelled,10,1) rollback transactionend在SQL編輯器中輸入上述語句后，可以使用下述語句加以驗(yàn)證。delete from teacher_info where teacher_id=010101結(jié)果：獨(dú)立實(shí)踐：編寫一個(gè)觸發(fā)器，在對(duì)student數(shù)據(jù)庫中的數(shù)據(jù)表stud_info執(zhí)行插入、更新和刪除三種操作后作出相應(yīng)提示。use studentgocreate trigger teacher_tri3on stud_infofor insert,update,deleteasif(select count(*) fro

14、m inserted inner join deleted on inserted.stud_id=deleted.stud_id)0) printupdate successfullyif(select count(*) from deleted)0) printdelete successfullyif(select count(*)from inserted)0) printinsert successfully2.第九章 事務(wù)與游標(biāo)2.1.實(shí)驗(yàn)?zāi)康?）了解SQL Server程序設(shè)計(jì)的方法；2）學(xué)會(huì)批處理、流程控制、事務(wù)處理、鎖及游標(biāo)的使用方法。2.2.實(shí)驗(yàn)準(zhǔn)備 1）安裝和配置SQL

15、Server2008數(shù)據(jù)庫管理系統(tǒng)；2）創(chuàng)建學(xué)生管理數(shù)據(jù)庫student；3）創(chuàng)建學(xué)生管理數(shù)據(jù)庫student中的各個(gè)數(shù)據(jù)表。 2.3.實(shí)驗(yàn)內(nèi)容和步驟1)事務(wù)處理；運(yùn)用事務(wù)處理將student數(shù)據(jù)庫中課程信息表lesson_info中的多媒體技術(shù)課程編號(hào)course_id由0401010106修改為0401010116。因?yàn)檎n程編號(hào)出現(xiàn)在課程信息表（lesson_info）、課程安排表（teach_schedule）、學(xué)生成績(jī)表（stud_grade）、教師基本信息表（teacher_info）中，所以修改四個(gè)表中的課程編號(hào)，而不能只修改其中的一個(gè)表。用戶必須知道DBMS，通知的方法是將下面的

16、四個(gè)UPDATE語句定義成一個(gè)事務(wù)。通過事務(wù)來保證student數(shù)據(jù)庫中的lesson_info表、teach_schedule表、stud_grade表、teacher_info表的所有課程號(hào)course_id由0401010106修改為0401010116，已達(dá)到數(shù)據(jù)保持一致性的目的。use student go begin tran stud_transaction-開始一個(gè)事務(wù) update lesson_info set course_id=0401010106where course_id=0401010116 save tran stud_savepoint-保存能回滾的點(diǎn) up

17、date teach_schedule set course_id=0401010106where course_id=0401010116 update stud_grade set course_id=0401010106where course_id=0401010116 update teacher_info set course_id=0401010106where course_id=0401010116 if(ERROR=1)rollback tran stud_savepoint-回滾本次事務(wù)至保存點(diǎn) elsecommit tran stud_transaction 在事務(wù)st

18、ud_transaction中，設(shè)置了中間回滾點(diǎn)，當(dāng)提交事務(wù)時(shí)由于執(zhí)行了回滾點(diǎn)stud_savepoint的回滾語句，所以只有對(duì)lesson_info的course_id進(jìn)行了修改，而表teach_schedule、stud_grade、teacher_info中相似的修改沒有發(fā)生。如果將回滾語句改寫為ROLLBACK TRAN或ROLLBACK TRAN stud_savepoint和ROLLBACK TRAN stud_transaction，即可將lesson_info，teach_schedule，stud_grade，teacher_info的所有課程號(hào)course_id由04010

19、10106修改為0401010116，實(shí)現(xiàn)數(shù)據(jù)的一致性。2）制造死鎖；在SQL編輯器中輸入并執(zhí)行下列語句：use student set deadlock_priority low-指定當(dāng)前會(huì)話為死鎖首選犧牲品begin transactionupdate stud_info set mark=565 where stud_id=0401030101 -第一個(gè)事務(wù)單擊工具欄上的“新建查詢”圖標(biāo)，新建一個(gè)SQL編輯器窗口，在新的SQL編輯器中輸入并執(zhí)行下列語句：use student begin transactionupdate stud_grade set grade=67 where st

20、ud_id=0401040112 -第二個(gè)事務(wù)切換到第一個(gè)SQL編輯器窗口，輸入并執(zhí)行下列語句：update stud_grade set grade=67 where stud_id=0401040112 -第一個(gè)事務(wù)此時(shí)，事務(wù)1和事務(wù)2都鎖住了stud_grade表中stud_id=0401040112的記錄，所以第一個(gè)事務(wù)被第二個(gè)事務(wù)阻止，但還沒有發(fā)生死鎖。切換到第二個(gè)SQL編輯器窗口，輸入并執(zhí)行下列語句：update stud_grade set grade=568 where stud_id=0401030101-第二個(gè)事務(wù)此時(shí)，由于事務(wù)1等待事務(wù)2釋放stud_grade表中stu

21、d_id=0401040112的記錄的鎖，而事務(wù)2又等待事務(wù)1釋放stud_info表中stud_id=0401030101的記錄的鎖，這樣互相等待而造成死鎖。因?yàn)榈谝粋€(gè)事務(wù)的deadlock_priority選項(xiàng)設(shè)置為low,SQL Server能自動(dòng)檢測(cè)并能將其解除，所以第一個(gè)事務(wù)立即終止。3）游標(biāo)的使用；定義一個(gè)游標(biāo)stud_cursor，逐行讀取stud_info表中的數(shù)據(jù)。use student go select * from stud_info-顯示表中所有數(shù)據(jù) go declare stud_cursor cursor-聲明一個(gè)游標(biāo) for select * from stud

22、_info open stud_cursor-打開游標(biāo) fetch next from stud_cursor-讀取該游標(biāo)中的第一行數(shù)據(jù) /*檢查fetch_status的值以確保新位置的有效性*/ while fetch_status=0 begin fetch next from stud_cursor end close stud_cursor deallocate stud_cursor結(jié)果：定義一個(gè)游標(biāo)stud_cursor，刪除表stud_info中的第一行數(shù)據(jù)。use student go select * from stud_info go declare stud_cursor cursor for select *from stud_info open stud_cursor fetch next from stud_cursor delete from stud_info where current of stud_cursor close stud_cursor deallocate stud_cursor go select *from stud_info 結(jié)果：定義一個(gè)游標(biāo)stud_cursor，更新表stud_info中的數(shù)據(jù)。use student go selec