數(shù)據(jù)庫實(shí)驗(yàn)報(bào)告三

1、數(shù)據(jù)庫原理實(shí)驗(yàn)報(bào)告實(shí)驗(yàn)五： 觸發(fā)器、存儲過程和函數(shù)實(shí)驗(yàn)六： ODBC/JDBC數(shù)據(jù)庫編程學(xué)號姓名班級日期2013302534楊添文100113032015.10.31實(shí)驗(yàn)五:觸發(fā)器、存儲過程和函數(shù)一、實(shí)驗(yàn)內(nèi)容1. 使用系統(tǒng)存儲過程(sp_rename)將視圖“V_SPJ”更名為“V_SPJ_三建”。（5分）（1）在原有數(shù)據(jù)庫SPJ中，建立如下語句：exec sp_rename 'V_SPJ','V_SPJ三建'（2）結(jié)果為： 2. 針對SPJ數(shù)據(jù)庫，創(chuàng)建并執(zhí)行如下的存儲過程：（共計(jì)35分）(1) 創(chuàng)建一個(gè)帶參數(shù)的存儲過程jsearch。該存儲過程的作用是：當(dāng)任意

2、輸入一個(gè)工程代號時(shí)，將返回供應(yīng)該工程零件的供應(yīng)商的名稱(SNAME)和零件的名稱(PNAME)以及工程的名稱(JNAME)。執(zhí)行jsearch存儲過程，查詢“J1”對應(yīng)的信息。（10分）(1)存儲過程為：create procedure jsearch(searchingfor_jno nchar(20)asbeginselect J.JNAME,S.SNAME,P.PNAMEfrom S,P,J,SPJwhereSPJ.JNO = searchingfor_jno and SPJ.JNO=J.JNO and SPJ.SNO=S.SNO and SPJ.PNO=P.PNOEnduseSPJgo

3、(2)執(zhí)行存儲過程如下：declare solution intexec solution = dbo.jsearchsearchingfor_jno = N'J1'select 'solution' = solutiongo(3)結(jié)果：(2) 使用S表，為其創(chuàng)建一個(gè)加密的存儲過程jmsearch。該存儲過程的作用是：當(dāng)執(zhí)行該存儲過程時(shí)，將返回北京供應(yīng)商的所有信息。（10分）create procedure jmsearchwith encryptionasbeginselect *from Swhere CITY = '北京'end(3) 使用

4、系統(tǒng)存儲過程sp_helptext查看jsearch, jmsearch的文本信息。（5分）(1)當(dāng)輸入為：exec sp_helptext 'jsearch'結(jié)果是：(2)當(dāng)輸入為：exec sp_helptext 'jmsearch'結(jié)果是：(4) 執(zhí)行jmsearch存儲過程，查看北京供應(yīng)商的情況。（5分）(1)執(zhí)行存儲過程：useSPJgodeclare solution intexec solution = dbo.jmsearchselect 'solution' = solutiongo(2)結(jié)果為： (5) 刪除jmsearch存

5、儲過程。（5分）drop procedure jmsearch3. 針對Student數(shù)據(jù)庫，創(chuàng)建和執(zhí)行如下的觸發(fā)器：（共計(jì)40分）(1) 刪除SC表上的外鍵約束，針對SC表創(chuàng)建一個(gè)名為insert_s的INSERT觸發(fā)器。該觸發(fā)器的功能：當(dāng)用戶向SC表中插入記錄時(shí)，如果插入的cno值不是C表中Cno的已有值，則提示用戶“不能插入記錄這樣的紀(jì)錄”，否則提示“記錄插入成功”。觸發(fā)器創(chuàng)建成功之后，向SC表插入記錄，驗(yàn)證觸發(fā)器是否正常工作。(5分)(1)創(chuàng)建觸發(fā)器：use studentgocreate trigger insert_son scafter insertasif(exists(sel

6、ect * from inserted where cno != '1' or cno != '2' or cno != '3' or cno != '6')begin print '不能插入記錄這樣的紀(jì)錄'rollback transactionendelseprint '記錄插入成功'（2）驗(yàn)證：use studentgoinsert intosc(sno,cno,grade)values('95001','5','88')結(jié)果：(2) 為S表創(chuàng)建

7、一個(gè)名為dele_s1的DELETE觸發(fā)器，該觸發(fā)器的作用是禁止刪除S表中的記錄。觸發(fā)器創(chuàng)建成功之后，刪除S表中的記錄，驗(yàn)證觸發(fā)器是否正常工作。(5分)(1)創(chuàng)建觸發(fā)器：use studentgocreate trigger dele_s1on safter deleteasrollback transactionprint '不能刪除表中紀(jì)錄'go(2)驗(yàn)證： 當(dāng)要?jiǎng)h除表中記錄時(shí)，出現(xiàn)如下圖所示情況 (3) 為S表創(chuàng)建一個(gè)名為dele_s2的DELETE觸發(fā)器，該觸發(fā)器的作用是刪除S表中的記錄時(shí)刪除SC表中該學(xué)生的選課紀(jì)錄。觸發(fā)器創(chuàng)建成功之后，刪除S表中的記錄，驗(yàn)證觸發(fā)器是否

8、正常工作(SC表中的數(shù)據(jù)被正常刪除)。(5分) (1)創(chuàng)建觸發(fā)器：use studentgocreate trigger dele_s2on sfor deleteasdelete from sc where sc.sno in(select sno from deleted)(2)驗(yàn)證：（前提：刪除觸發(fā)器dele_s1,否則不能刪除S表記錄）delete from swhere s.sno='95001'select * from sc 結(jié)果為： (4) 為S表創(chuàng)建一個(gè)名為update_s的UPDATE觸發(fā)器，該觸發(fā)器的作用是禁止更新S表中“sdept”字段的內(nèi)容。觸發(fā)器創(chuàng)建

9、成功之后，更新S表中“sdept”字段的內(nèi)容，驗(yàn)證觸發(fā)器是否正常工作。(5分)(1)創(chuàng)建觸發(fā)器：use studentgocreate trigger update_son safter updateasif update(sdept)beginraiserror('sdept不能被更改',10,1)rollback transactionend(2)驗(yàn)證： 當(dāng)要進(jìn)行數(shù)據(jù)更改時(shí)，出現(xiàn)如下圖所示情況： (5) 禁用update_s觸發(fā)器。禁用之后，更新S表中的“sdept”字段的內(nèi)容，驗(yàn)證觸發(fā)器是否還繼續(xù)正常工作。(5分)(1)禁用觸發(fā)器：use studentgodisable

10、 trigger update_son sgo (2)驗(yàn)證： 已經(jīng)將“MA”更改為“IS”，如下圖所示： (6) 刪除update_s觸發(fā)器。(5分)use studentgodrop trigger update_sgo(7) 創(chuàng)建一個(gè)新的課程成績統(tǒng)計(jì)表 CAvgGrade(Cno, Snum, examSNum, avgGrade)，分別表示課號，選該課程的學(xué)生人數(shù)，參加考試人數(shù)，該門課程的平均成績。利用觸發(fā)器實(shí)現(xiàn)如下的功能：當(dāng)SC表中有記錄插入、刪除或者更新時(shí)，自動更新表CAvgGrade。注意SC表中的grade為NULL時(shí)表明該學(xué)生還未參加考試，計(jì)算平均成績時(shí)不需要計(jì)算該成績，但是g

11、rade為0即考試成績?yōu)?時(shí)，需要計(jì)算該成績。(10分)(1)創(chuàng)建統(tǒng)計(jì)表CAvgGrade：create table CAvgGrade( cno char(10) primary key, snum int, examsnum int, avggrade float)(2)創(chuàng)建觸發(fā)器：use studentgocreate trigger upd_ins_dele_CAvgGradeon scafter update,insert,deleteasbegindeclare cno char(10)declare snum intdeclare examsum intdeclare avggr

12、ade intselect cno=cno from insertedselect cno=cno from deletedselect snum = COUNT (*) from sc where cno=cnoselect examsum = COUNT(*) from sc where cno=cno and grade>=0select avggrade = AVG(grade) from sc where cno=cno and grade>=0update CAvgGradesetsnum=snum,cno=cno,examsnum=examsum,avggrade=a

13、vggradewhere cno=cnoend (3)測試：A、（測試插入）use studentgoinsert into scvalues('95005','1',88) 結(jié)果為： （CAvgGrade表） （sc表） B、（測試刪除）use studentgodelete from scwhere cno='1'結(jié)果為： （CAvgGrade表） （sc表） C、（測試更改）update scset grade=99where cno='1' 結(jié)果為： （CAvgGrade表） （sc表） 4. 創(chuàng)建一個(gè)works數(shù)據(jù)庫，其

14、中包含員工表empoyee(eID, eName, salary)，假設(shè)該表中有1000條員工數(shù)據(jù)，完成下列要求（總計(jì)20分，每題10分）。(1) 為了協(xié)助本題自動生成1000條員工數(shù)據(jù)，創(chuàng)建一個(gè)自動生成員工ID的用戶自定義函數(shù)generateEID。其中員工ID要求是一個(gè)8位的數(shù)字，前四位表示插入員工數(shù)據(jù)的當(dāng)前年份，后四位按照從0001到9999的順序增長。例如2015年插入的第一條數(shù)據(jù)是20050001，所有1000條員工ID分別是20150001-20151000。調(diào)用該函數(shù)實(shí)現(xiàn)自動插入1000條數(shù)據(jù)。（注意插入數(shù)據(jù)的時(shí)候員工姓名可以為任意值，工資是2000-5000之間的數(shù)字）(1)自

15、定義：use worksgocreate procedure generateEIDasbegindeclare for intset for = 0while(for < 1000)begininsert into employee values(20050001+for,'name'+CAST(for as NCHAR(20),2000+CAST(FLOOR(rand()*3001) as int)set for=for+1endenduse worksgoDECLAREreturn_value intEXECreturn_value = dbo.generateEI

16、DSELECT'Return Value' = return_valueGO (2)結(jié)果： (2) 該公司計(jì)劃為員工按照一定的規(guī)則漲工資，請使用游標(biāo)創(chuàng)建一個(gè)存儲過程，執(zhí)行該存儲過程完成本次工資調(diào)整：工資增長規(guī)則如下：l 工資在3000元以下，每月漲300元；l 工資在3000-4000元之間，每月漲200元；l 工資大于或者等于4000元，每月漲50元； (1)語句如下：declare mycursor cursor for select salary from empoyeeopen mycursordeclare salary intfetch next from mycu

17、rsor into salarywhile FETCH_STATUS=0beginif(salary<3000)beginupdate empoyee set salary=salary+300 where current of mycursorendelse if(salary<4000)beginupdate empoyee set salary=salary+200 where current of mycursorendelsebeginupdate empoyee set salary=salary+50 where current of mycursorendfetch

18、 next from mycursor into salaryendclose mycursordeallocate mycursor (2)結(jié)果如下： 二、實(shí)驗(yàn)反思觸發(fā)器是對表進(jìn)行插入、更新、刪除的時(shí)候會自動執(zhí)行的特殊存儲過程，本章學(xué)習(xí)的觸發(fā)器類型是after觸發(fā)器，即只有當(dāng)執(zhí)行update,delete,insert的時(shí)候這個(gè)觸發(fā)器才會被使用到，觸發(fā)器的使用為數(shù)據(jù)庫操作真的提供了很多便利。實(shí)驗(yàn)六: ODBC/JDBC數(shù)據(jù)庫編程一、實(shí)驗(yàn)內(nèi)容1. ODBC配置以及程序調(diào)試：（50分）(1) 配置一個(gè)ODBC數(shù)據(jù)源，要求數(shù)據(jù)源名稱：student，其中包含s(學(xué)生信息)表。1、 設(shè)置屬性：2、 更改默認(rèn)數(shù)據(jù)庫：3、 測試數(shù)據(jù)源：4、 測試成功：5、 結(jié)果為：(2) 閱讀并運(yùn)行實(shí)驗(yàn)給出的例子程序，理解ODBC編程，要求簡單寫出自己對這段程序的理解或者流程圖，并且請給出程序運(yùn)行結(jié)果示例圖。一、對程序的流程的理解以下面的步驟展開：1、 定義句柄跟變量。其中句柄有環(huán)境句柄、連接句柄、語句句柄；2、 初始化環(huán)境（1） 分配環(huán)境句柄；（2） 設(shè)置管理環(huán)境屬性；（3） 連接數(shù)據(jù)庫。3、 建立連接（1） 分配連接句柄；（2） 連