數(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中，建立如下語句：execsp_rename'V_SPJ''V_SPJE建（2）結(jié)果為：日Li-閘口數(shù)提庫關(guān)荽圉田C3表口口視圉國£莖器祖圖dboV_£PJ二建i+LJ同文詢口可麻度性ELJServiceBr

2、okerliLJ闞l+72.針對SP瞰據(jù)庫，創(chuàng)建并執(zhí)行如下白存儲過程：（共計(jì)35分）（1）創(chuàng)建一個(gè)帶參數(shù)的存儲過程一jsearcht該存儲過程的作用是：當(dāng)任意輸入一個(gè)工程代號時(shí)，將返回供應(yīng)該工程零件的供應(yīng)商的名稱（SNAME）和零件的名稱（PNAME）以及工程的名稱（JNAME）。執(zhí)行jsearch存儲過程，查詢“J1”對應(yīng)的信息。（10分）（1瓶儲過程為：createprocedurejsearcRsearchingfor_jnonchar(20)asbeginselectJJNAME,SSNAME,P.PNAMEfromSHJSPJwhereSPJJNO=searchingfor_jnoa

3、ndSPJJNO=J.JNOandSPJSNO=S.SNOandSPJPNO=P.PNOEnduseSPJgo(2腦行存儲過程如下：declaresolutionintexecsolution=dbo.jsearchsearchingfor_jno=N'J1'select'solution'=solutiongo(3)結(jié)果：n結(jié)果后消息JNAJtESKAMEPHkHl匚建j豐厝案凸輪2三窿"東方紅螺絲R?三建東方乳凸輪solution(2)使用S表，為其創(chuàng)建一個(gè)加密的存儲過程一jmsearcht該存儲過程的作用是：當(dāng)執(zhí)行該存儲過程時(shí)，將返回北京供應(yīng)商的

4、所有信息。(10分)createprocedurejmsearchwithencryptionasbeginselect*fromSwhereCITY=比京'end(3) 使用系統(tǒng)存儲過程sp_helptext查看jsearch,jmsearch的文本信息。(5分)(1)當(dāng)輸入為:execsp_helptext'jsearch'結(jié)果是:面雄果最肖息THit1 :cre4tejchi.n.g£or_jn?nch&.r.力2 as3 h也駐置4 selectJ.JUWH,S.SHAME,PrKAftt5 from&巳J,SFJ6 wh*r*7 ST

5、J.JNO=achingfor_jn«uidSTJ.JUOJJRO.Send聾詢?nèi)掌鰣?zhí)行.(2)當(dāng)輸入為:execsp_helptext'jmsearch結(jié)果是:囿消息對象訕曉虹擊的文本已力喑。(4)執(zhí)行jmsearch存儲過程，查看北京供應(yīng)商的情況。(5分)(1)執(zhí)行存儲過程：usqspjgodeclaresolutionintexecsolution=dbo.jmsearchselect'solution'=solutiongo(2)結(jié)果為:可編輯(5)刪除jmsearch存儲過程。(5分)dropprocedurejmsearch3.針對Student數(shù)

6、據(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ā)器：usestudentgocreatetriggerinsert_sonscafterinsertasif(exists(select*frominsertedwherecno!='1'orcno!='2&

7、#39;orcno!='3'orcno!='6')beginprint'不能插入記錄這樣的紀(jì)錄'rollbacktransactionendelseprint'記錄插入成功'(2)驗(yàn)證：usestudentgoinsertintosc(snQcno,gradevalueS'95001'5,'88)結(jié)果：Lj消息不靠插入記錄這樣的紀(jì)錄消息360北級別飾，狀灰匕第3行事勞在觸發(fā)器中結(jié)束口批處理已中止中(2)為S表創(chuàng)建一個(gè)名為dele_s1的DELETE觸發(fā)器，該觸發(fā)器的作用是禁止刪除S表中的記錄。觸發(fā)器創(chuàng)建成功

8、之后，刪除S表中的記錄，驗(yàn)證觸發(fā)器是否正常工作。(5分)(1冶建觸發(fā)器：usestudentgocreatetriggerdele_s1onsafterdeleteasrollbacktransactionprint'不能刪除表中紀(jì)錄go(2/證:當(dāng)要刪除表中記錄時(shí)，出現(xiàn)如下圖所示情況MicrosoftSQLServerManagem&niStuidlio未地圖£1可行.江圖惻除行4時(shí)發(fā)生問題.楂謖源：.NetSqlClientDataProviderH錯誤i淙鼠事君在觸發(fā)露白結(jié)果，微處理已中止匚K能瞰表中紀(jì)錄置邕正巖誤并鬢試班除該行r或按E"脫肖里最為S表

9、創(chuàng)建一個(gè)名為dele_s2的DELETE觸發(fā)器，該觸發(fā)器的作用是刪除S表中的記錄時(shí)刪除SC表中該學(xué)生的選課紀(jì)錄。觸發(fā)器創(chuàng)建成功之后，刪除S表中的記錄，驗(yàn)證觸發(fā)器是否正常工作（SC表中的數(shù)據(jù)被正常刪除）。（5分）（1）創(chuàng)建觸發(fā)器：usestudentgocreatetriggerdele_s2onsfordeleteasdeletefromscwherescsnoin(selectsnofromdeleted)(2)驗(yàn)證：(前提：刪除觸發(fā)器dele_s1否則不能刪除S表記錄)deletefromswheres.sno='95001'select*fromsc結(jié)果為:二結(jié)果“肖息E

10、ILOCD_0gnIIISIIIBU1JillGUI111*1|9552i273>0!BIMIB112S5O02364355003591(4)為S表創(chuàng)建一個(gè)名為update_s的UPDATE觸發(fā)器，該觸發(fā)器的作用是禁止更新S表中“sdept”字段的內(nèi)容。觸發(fā)器創(chuàng)建成功之后，更新S表中“sdept”字段的內(nèi)容，驗(yàn)證觸發(fā)器是否正常工作。(5分)(1)創(chuàng)建觸發(fā)器：usestudentgocreatetriggerupdate_sonsafterupdateasifupdatesdept)beginraiserroifsdep樂能被更改',10,1)rollbacktransaction

11、end(2)驗(yàn)證:當(dāng)要進(jìn)行數(shù)據(jù)更改時(shí)，出現(xiàn)如下圖所示情況:Microsoft3QLServerMgnagemenrtStudioX未更新任何行，未提交行3中的教指.措浣源：，NetSqlClientDartaProvider,措函肖息:事務(wù)在觸發(fā)器口給電.械那已中止.占depK建銀更及清更正造謾并垂試，搬Esc取消更布(5)禁用update_s觸發(fā)器。禁用之后，更新S表中的“sdept”字段的內(nèi)容，驗(yàn)證觸發(fā)器是否還繼續(xù)正常工作。(5分)(1)禁用觸發(fā)器：usestudentgodisabletriggerupdate_sonsgo(2啦證：已經(jīng)將“MA”更改為“IS”，如下圖所示:95003王

12、樊女1985-10-04(6)刪除update_s觸發(fā)器。(5分)usestudentgodroptriggerupdate_sgo創(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ì)算該成績，但是grade為0即考試成績?yōu)椤r(shí)，需要計(jì)算該成績。(10分)(1)創(chuàng)建統(tǒng)計(jì)表CAvgGrade：createta

13、bleCAvgGrade(cnochar(10)primarykey,snumint,examsnumint,avggradefloat)(2)創(chuàng)建觸發(fā)器：usestudentgocreatetriggerupd_ins_dele_CAvgGradeonscafterupdate,insert,deleteasbegindeclarecnochar(10)declaresnumintdeclareexamsumintdeclareavggradentselectcno=cnofrominsertedselectcno=cnofromdeletedselectsnum=COUNT(*)froms

14、cwherecno=cnoselectexamsum=COUNT(*)fromscwherecno=cnoandgrade>=0selectavggrade=AVG(gradefromscwherecno=cnoandgrade>=0updateCAvgGradesetsnum=snum1cno=cno,examsnum=examsumavggrad=avggradewherecno=cnoend測試：A、(測試插入)usestudentgoinsertintoscvalues'95005''1',88)結(jié)果為：(CAvgGrade表)ACER-YT

15、',student-dbo-.CAvgGradeIACER-YTW.student-dbo.sccrrosnumexairnsnumavqqrade11882NULLNULLNULL3NULLNULLNULLNULLNULLNULLNULL（sc表）ACERVTW.&tudentdbo.CAvgGradeACER-VTVsnocnograde950022799500238495003691950051國*MULLNULLNULLB、（測試刪除）usestudentgodeletefromscwherecno='1'結(jié)果為：（CAvgGrade表）ACER-YTW

16、.stde-nt-dbo.CAvgGradeIACER.-VTW.student-dbo.ccnosnumexamsnumavqqrade100NULL2NULLNULLNULL3NULLNULLNULLNULLNULLNULLNULL（sc表）ACER-VTW.rtudent-dbo.CAvgGradeACER-VTW刃tudsnocnogradek5C0227a950023&495003891NULLNULLNULLC、（測試更改）updatescsetgrade=99wherecno='1結(jié)果為：(CAvgGrade表)ACER-YTW.student-dbc.CAvgG

17、radeSQLQueryS.sqlenosnurneKam5numavqgradeEM11gg2NULLNULLNULL3NULLNULLNULLNULLNULLNULLNULL（sc表）ACER-VTW.rtudent-snoCF1Ograde19500227fl95DO23849500365199NULLNULLNULL4.創(chuàng)建一個(gè)works數(shù)據(jù)庫，其中包含員工表empoyee(eID,eName,salary)假設(shè)該表中有1000條員工數(shù)據(jù)，完成下列要求(總計(jì)20分，每題10分)。(1)為了協(xié)助本題自動生成1000條員工數(shù)據(jù)，創(chuàng)建一個(gè)自動生成員工ID的用戶自定義函數(shù)generateEID

18、。其中員工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)自定義：useworksgocreateproceduregenerateEIDasbegindeclareforintsetfor=0while(for<1000)begininsertintoemployeevalues(20050001+fo

19、r,'name'+CAST(forasNCHAR(20),2000+CAST(FLOOR(rand()*3001)asint)setfor=for+1endenduseworksgoDECLAREreturn_valueintEXECreturn_value=dbo.generateEIDSELECT'ReturnValue'=return_valueGO(2)結(jié)果：ACER-YTW.worksdboempoyeeX§QIQueryEsq"RelDeNamesalaryboDSDOOllnameO41002005000/namel316520

20、050003rame2249120050004name24g5i20050005name429232005000rsmeS4D4S200500Q7rameG3200£005000aname7203820050009name8309020050010name9435520050011riameW254020050012namell205120050013name1224582(1050014ramelS470620050015namel445412005D016namel5303920050017name1626302005001anamel7205720050019rame18371

21、62005D020namelS215920050021ramSO3703執(zhí)行該(2)該公司計(jì)劃為員工按照一定的規(guī)則漲工資，請使用游標(biāo)創(chuàng)建一個(gè)存儲過程,存儲過程完成本次工資調(diào)整：工資增長規(guī)則如下：工資在3000元以下，每月漲300元；工資在3000-4000元之間，每月漲200元；工資大于或者等于4000元，每月漲50元；(1)語句如下：declaremycursorcursorforselectsalaryfromempoyeeopenmycursordeclaresalaryintfetchnextfrommycursorintosalarywhileFETCH_STATUS=0begini

22、f(salary<3000)beginupdateempoyeesetsalary=salary+300wherecurrentofmycursorendelseif(salary<4000)beginupdateempoyeesetsalary=salary+200wherecurrentofmycursorendelsebeginupdateempoyeesetsalary=salary+50wherecurrentofmycursorendfetchnextfrommycursorintosalaryendclosemycursordeallocatemycursor(2)結(jié)

23、果如下：ACER-yTW.works-dbo.empoyeeXSQLQuery.sl-.RelDeNamesalaryG.1I工二區(qū)丁1nameO415020050002nannelM湖30050003ngnneS27912005(X)04nameB500120050005name4322320050006409$20050007namefi245020050003nanne?232820050009nameS329020050010name?4%520050011n日mol0264020050012namell235120050013namel2275830050014nannel34756

24、20050015name14459120050016namelS323820050017nnnelS298020050015name1723572OO5DD19nannelS蚪1620050020n君mmig245g二、實(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)容student,其中包含s（學(xué)生信息）表。1.ODBC配置以及程序調(diào)試：（50分）（1）配置一個(gè)

25、ODBC數(shù)據(jù)源，要求數(shù)據(jù)源名稱:1、設(shè)置屬性：2、更改默認(rèn)數(shù)據(jù)庫:創(chuàng)建到SQLServer的帝貧據(jù)海應(yīng)更改默認(rèn)的數(shù)據(jù)府為(口上student口附噴旗取性2舊)：國使用ANSI引用的標(biāo)酶U).區(qū)使用ANSI的空值、，真先及誓言伊卜君主SQLServsr不可用，清受月放糜褚SQLSenver(F).<上T(田T-(N)>現(xiàn)隹3、測試數(shù)據(jù)源:ODBCMicrosoftSQLServerX榭發(fā)碎配置創(chuàng)建新的ODBC數(shù)框源：MicrosoftSQLServerOt>0C韭迪序版本10.00,10240數(shù)霍派S賽：rtudent黎據(jù);厚但述：服務(wù)器；acer-YTW數(shù)據(jù)軍；student

26、語言;CDefault,)屈浮字符數(shù)亮;Yes日志No日志驅(qū)動程序統(tǒng)計(jì):No使用區(qū)域沒置工No預(yù)定義捎吾句漁典在野開時(shí)刪隱慣廿萬稿遼程變用故潼格國片器；No使用ANSI引用的標(biāo)識符;Yes使用ANSI的空值，埴先都窖百;Ye5統(tǒng)K蠶N口側(cè)潞®據(jù)源逾建朝肖4、測試成功:SQLServerOtBC球源迎試MicrosoftSQLServerODBC驅(qū)禧字場本1000,10240試國遑轟費(fèi)證選項(xiàng)設(shè)置斷開與服務(wù)祥的在接測鼠冷!ffi7=5、結(jié)果為:(2)閱讀并運(yùn)行實(shí)驗(yàn)給出的例子程序，理解ODBC編程，要求簡單寫出自己對這段程序的理解或者流程圖，并且請給出程序運(yùn)行結(jié)果示例圖。一、對程序的流程的

27、理解以下面的步驟展開：1、定義句柄跟變量。其中句柄有環(huán)境句柄、連接句柄、語句句柄;2、初始化環(huán)境(1) 分配環(huán)境句柄；(2) 設(shè)置管理環(huán)境屬性；(3) 連接數(shù)據(jù)庫。3、建立連接(1) 分配連接句柄；(2) 連接若失敗則返回錯誤值。4、初始化語句句柄。設(shè)置了語句選項(xiàng)。5、執(zhí)行語句(1) 預(yù)編譯帶有參數(shù)語句；(2) 預(yù)先聲明插入語句；(3) 執(zhí)彳TSQL語句。6、處理結(jié)果集合的同時(shí)執(zhí)行預(yù)編譯后的語句。7、中止處理(1) 釋放語句句柄；(2) 釋放環(huán)境句柄；(3) 斷開連接。、運(yùn)行結(jié)果：點(diǎn)了“執(zhí)行”之后:ol/bc_i用戶名;丫ODBCJESTXIIresultrecord1:9SG02,交退數(shù)據(jù)解

28、-漢書句確定|取消|2.參考給出的ODBC編程示例，使用ODBC技術(shù)，編寫一個(gè)簡單的程序，包含對數(shù)據(jù)庫SPJ的連接，查詢，插入，修改和刪除。(50分)(1)插入數(shù)據(jù)：代碼：insertintoSvalues('95005'劉鵬飛','男','1992-05-16','IS')結(jié)果為: r N：網(wǎng)|中 7豈S乂 1F(L(Jiip|*i-K(£t2Sfelect*i)i=3 4D" % luuppr(*ftzStflfCltl+l)= " E1 踹1taupper(»(szseioct

29、 mtouppert»(szSelectSQL1N1EGER row count 逸OBC TESTOpr-rahr 刈“七.？日是ted c«Urt iv 1就宿伊君管;皿語句:rrur(i) r Q wniL叫(2)刪除數(shù)據(jù):代碼：delete from S where Sname =劉鵬飛結(jié)果為：pwr -1.4力皿5利皓飛舟AUftii。|麗”11口中附輔版且d couml it： 1正取和至手名歸:I值可且行/3>H.k'F?匚門,3為，1口引。_1uuBu_iti.r<D/-nn1'-1'1"jj-wnrhiResu

30、ltSet ret ; 直詞培果集的對冢 用十m匚打總二返回日于百c Sc-armer 工nE/E = new Scanner tSvsteir. i.n）;5t ring Suring String Stringdriver = "com.iricrosoft. sqlseiver . jdbc. SQLServurl = "jdbc:j /127.0.0-Jl： 133;Dac.atajeern3atr;23.tryCleas, f<?Tdni5? driverl ；|二 Problems ; Javadoc fc. Declaration 日<t?&quo

31、t;min3ted> DBSqlServer Java Application C:Progr3m Files x85j'Jav3j.S.O_25binjauav.FES i-K砧乒FI E臭首，JDSC請輸入一行£QE語句，insert'n七u1?g臬作成功！工行受影響(2)刪除數(shù)據(jù):，）DBSqtServer-java舞=.=Ses-lrSeTxec;.基狎結(jié)果集E口對象性3:5.二日k二二：退回日翡吉六ScaE«rinput=newScannerfirj;匚Stringdriver="eDUk.micrasofr.aqlserver.3dbc.SQLServStringurl=njdb