數(shù)據庫實驗報告三

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

2、輸入一個工程代號時，將返回供應該工程零件的供應商的名稱(SNAME)和零件的名稱(PNAME)以及工程的名稱(JNAME)。執(zhí)行jsearch存儲過程，查詢“J1”對應的信息。（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)結果：(2) 使用S表，為其創(chuàng)建一個加密的存儲過程jmsearch。該存儲過程的作用是：當執(zhí)行該存儲過程時，將返回北京供應商的所有信息。（10分）create procedure jmsearchwith encryptionasbeginselect *from Swhere CITY = '北京'end(3) 使用

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

5、儲過程。（5分）drop procedure jmsearch3. 針對Student數(shù)據庫，創(chuàng)建和執(zhí)行如下的觸發(fā)器：（共計40分）(1) 刪除SC表上的外鍵約束，針對SC表創(chuàng)建一個名為insert_s的INSERT觸發(fā)器。該觸發(fā)器的功能：當用戶向SC表中插入記錄時，如果插入的cno值不是C表中Cno的已有值，則提示用戶“不能插入記錄這樣的紀錄”，否則提示“記錄插入成功”。觸發(fā)器創(chuàng)建成功之后，向SC表插入記錄，驗證觸發(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 '不能插入記錄這樣的紀錄'rollback transactionendelseprint '記錄插入成功'（2）驗證：use studentgoinsert intosc(sno,cno,grade)values('95001','5','88')結果：(2) 為S表創(chuàng)建

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

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

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

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

11、rade為0即考試成績?yōu)?時，需要計算該成績。(10分)(1)創(chuàng)建統(tǒng)計表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) 結果為： （CAvgGrade表） （sc表） B、（測試刪除）use studentgodelete from scwhere cno='1'結果為： （CAvgGrade表） （sc表） C、（測試更改）update scset grade=99where cno='1' 結果為： （CAvgGrade表） （sc表） 4. 創(chuàng)建一個works數(shù)據庫，其

14、中包含員工表empoyee(eID, eName, salary)，假設該表中有1000條員工數(shù)據，完成下列要求（總計20分，每題10分）。(1) 為了協(xié)助本題自動生成1000條員工數(shù)據，創(chuàng)建一個自動生成員工ID的用戶自定義函數(shù)generateEID。其中員工ID要求是一個8位的數(shù)字，前四位表示插入員工數(shù)據的當前年份，后四位按照從0001到9999的順序增長。例如2015年插入的第一條數(shù)據是20050001，所有1000條員工ID分別是20150001-20151000。調用該函數(shù)實現(xiàn)自動插入1000條數(shù)據。（注意插入數(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)結果： (2) 該公司計劃為員工按照一定的規(guī)則漲工資，請使用游標創(chuàng)建一個存儲過程，執(zhí)行該存儲過程完成本次工資調整：工資增長規(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)結果如下： 二、實驗反思觸發(fā)器是對表進行插入、更新、刪除的時候會自動執(zhí)行的特殊存儲過程，本章學習的觸發(fā)器類型是after觸發(fā)器，即只有當執(zhí)行update,delete,insert的時候這個觸發(fā)器才會被使用到，觸發(fā)器的使用為數(shù)據庫操作真的提供了很多便利。實驗六: ODBC/JDBC數(shù)據庫編程一、實驗內容1. ODBC配置以及程序調試：（50分）(1) 配置一個ODBC數(shù)據源，要求數(shù)據源名稱：student，其中包含s(學生信息)表。1、 設置屬性：2、 更改默認數(shù)據庫：3、 測試數(shù)據源：4、 測試成功：5、 結果為：(2) 閱讀并運行實驗給出的例子程序，理解ODBC編程，要求簡單寫出自己對這段程序的理解或者流程圖，并且請給出程序運行結果示例圖。一、對程序的流程的理解以下面的步驟展開：1、 定義句柄跟變量。其中句柄有環(huán)境句柄、連接句柄、語句句柄；2、 初始化環(huán)境（1） 分配環(huán)境句柄；（2） 設置管理環(huán)境屬性；（3） 連接數(shù)據庫。3、 建立連接（1） 分配連接句柄；（2） 連