實(shí)驗(yàn)七-存儲(chǔ)過(guò)程及應(yīng)用

實(shí)驗(yàn)七：存儲(chǔ)過(guò)程及應(yīng)用實(shí)驗(yàn)?zāi)康模捍鎯?chǔ)過(guò)程的設(shè)計(jì)和應(yīng)用。實(shí)驗(yàn)內(nèi)容：在SQLServer環(huán)境下設(shè)計(jì)并執(zhí)行存儲(chǔ)過(guò)程。實(shí)驗(yàn)?zāi)康模豪斫夂驼莆諗?shù)據(jù)庫(kù)存儲(chǔ)過(guò)程的創(chuàng)建和調(diào)用方法。實(shí)驗(yàn)方法：在SQLServer環(huán)境下按要求創(chuàng)建和調(diào)用存儲(chǔ)過(guò)程完成指定操作。實(shí)驗(yàn)要求：在實(shí)驗(yàn)1數(shù)據(jù)庫(kù)設(shè)計(jì)的基礎(chǔ)上補(bǔ)充設(shè)計(jì)過(guò)程，并基于實(shí)驗(yàn)2建立的表和實(shí)驗(yàn)3插入的數(shù)據(jù)完成實(shí)驗(yàn)。按要求設(shè)計(jì)完成如下功能的存儲(chǔ)過(guò)程查詢平均分在X到Y(jié)范圍內(nèi)的學(xué)生信息更新操作，以學(xué)號(hào)、課程編號(hào)和考試成績(jī)作為參數(shù)更新指定學(xué)生和課程的考試成績(jī)，并返回該學(xué)生的平均成績(jī)。自行再分別設(shè)計(jì)一個(gè)完成查詢和完成操作成功的存儲(chǔ)過(guò)程在客戶端以存儲(chǔ)過(guò)程和輸入SQL語(yǔ)句的方式分別執(zhí)行相同的查詢操作，比較使用和不使用存儲(chǔ)過(guò)程的區(qū)別。實(shí)驗(yàn)如下：（1）使用存儲(chǔ)過(guò)程查詢平均分在x到y(tǒng)范圍內(nèi)的學(xué)生信息：createprocedureuspgetstu1@xsmallint,@ysmallintasselect學(xué)號(hào),姓名,院系,平均成績(jī)From學(xué)生wherebetween@xand@y可以利用存儲(chǔ)過(guò)程uspgetstu1查詢平均分在70到80范圍內(nèi)的學(xué)生信息：Executeuspgetstu1@x=70，@y=80（2）利用存儲(chǔ)過(guò)程更新操作，以學(xué)號(hào)、課程編號(hào)和考試成績(jī)作為參數(shù)更新指定學(xué)生和課程的考試成績(jī)，并返回該學(xué)生的平均成績(jī)：createprocedureuspgetstu2@snochar(8),@cnochar(8),@gradesmallintasdeclare@avgsmallintselect@avg=avg(成績(jī))from選課where學(xué)號(hào)=@snoand課程編號(hào)=@cnoand成績(jī)=@gradereturn@avg下面語(yǔ)句調(diào)用存儲(chǔ)過(guò)程uspgetstu2查詢學(xué)號(hào)為0901，課程編號(hào)為101的學(xué)生的平均成績(jī)：declare@avgsmallintexecute@avg=uspgetstu2@sno='0901',@cno='101',@grade=95print'學(xué)號(hào)為0901，課程編號(hào)為101的學(xué)生的平均成績(jī)是:'+str(@avg)（3）利用存儲(chǔ)過(guò)程uspgetstu1查詢平均分在80到90范圍內(nèi)的學(xué)生信息：createprocedureuspgetstu1@xsmallint,@ysmallintasselect學(xué)號(hào),姓名,院系,平均成績(jī)from學(xué)生where平均成績(jī)>@xand平均成績(jī)<@yExecuteuspgetstu1@x=80，@y=90利用sql語(yǔ)句查詢查詢平均成績(jī)?cè)?0到90范圍內(nèi)的學(xué)生信息：select學(xué)號(hào),姓名,院系,學(xué)習(xí)成績(jī)from學(xué)生where學(xué)習(xí)成績(jī)between80and90區(qū)別：使用存儲(chǔ)過(guò)程可以簡(jiǎn)化應(yīng)用，使得非專業(yè)人員對(duì)數(shù)據(jù)庫(kù)的訪問(wèn)變得更加方便簡(jiǎn)潔。數(shù)據(jù)庫(kù)管理員或設(shè)計(jì)者可以把一些復(fù)雜的查詢或者數(shù)據(jù)操作設(shè)計(jì)成存儲(chǔ)過(guò)程，普通用戶無(wú)需了解數(shù)指定登陸用戶MM為‘選課管理’數(shù)據(jù)庫(kù)的用戶，并指定‘選課’架構(gòu)為默認(rèn)架構(gòu)：use選課管理createuserMMwithdefault_schema=選課指定登錄用戶KK為‘選課管理‘?dāng)?shù)據(jù)庫(kù)的用戶，并指定’教師‘架構(gòu)為默認(rèn)架構(gòu)：createuserKKwithdefault_schema=教師2、將數(shù)據(jù)庫(kù)用戶MM的名稱改為YY,默認(rèn)架構(gòu)改為選課信息：alteruserMMwithname=YYdefault_schema=選課信息3、建立若干角色，部分角色指定其他用戶管理：假設(shè)有用戶MM，指定該用戶為securityadmin角色：sp_addsrvrolemember'MM','securityadmin'假設(shè)當(dāng)前數(shù)據(jù)庫(kù)‘選課管理’有用戶KK，指定該用戶為db_securityadmin角色：sp_addsrvrolemember'db_securityadmin','KK'指定用戶MM為db_creator角色：sp_addsrvrolemember'MM','db_creator'假設(shè)用戶KK是“選課管理”數(shù)據(jù)庫(kù)的數(shù)據(jù)庫(kù)管理員，由其建立角色manager：sp_addsrvrolemember'KK','db_owner'createrolemanager授權(quán)一些用戶可以創(chuàng)建表等數(shù)據(jù)庫(kù)對(duì)象，完成角色管理及其他授權(quán)管理：對(duì)架構(gòu)‘選課’的insert權(quán)限授予KK：grantinsertonschema::選課toKK在選課管理數(shù)據(jù)庫(kù)中建立角色role_op,并將用戶PP指定為該角色的成員：use選課管理createrolerole_opsp_addsrvrolemember'role_op','PP'將學(xué)生表的查詢、插入、修改和刪除的權(quán)限授予角色role_op:grantselect,insert,update,deleteon學(xué)生torole_op將院系表的查詢、插入、修改和刪除的權(quán)限授予角色role_op，并允許該角色的成員轉(zhuǎn)授這些權(quán)限：grantselect,insert,update,deleteon院系torole_opwithgrantoption用戶PP將對(duì)院系表的查詢權(quán)限、對(duì)應(yīng)院系名稱和辦公地點(diǎn)的修改權(quán)限授予用戶QQ：先用PP身份登錄，然后執(zhí)行如下命令：use選課管理grantselect,update(名稱,辦公地點(diǎn))on院系toQQasrole_op從用戶QQ收回對(duì)院系表的名稱的修改權(quán)限：revokeupdate(名稱)on院系fromQQ從角色role_op收回對(duì)院系表刪除權(quán)限的轉(zhuǎn)授權(quán)限：revokegrantoptionfordeleteon院系fromrole_opcascade從角色role_op收回對(duì)院系表update權(quán)限及其withgrantoption而產(chǎn)生的所有授權(quán)：revokeupdateon院系(名稱)fromrole_opcascade實(shí)驗(yàn)十并發(fā)事務(wù)控制實(shí)驗(yàn)名稱：并發(fā)事務(wù)管理及控制實(shí)驗(yàn)內(nèi)容：在sqlserver環(huán)境下完成封鎖、死鎖和設(shè)置隔離級(jí)別等基本實(shí)驗(yàn)。實(shí)驗(yàn)?zāi)康模豪斫夂腕w會(huì)數(shù)據(jù)庫(kù)事務(wù)管理的內(nèi)容，掌握設(shè)計(jì)并發(fā)事務(wù)的基本方法，加強(qiáng)數(shù)據(jù)庫(kù)管理系統(tǒng)的事務(wù)管理功能的認(rèn)識(shí)。實(shí)驗(yàn)方法：若干人一組在sqlserver環(huán)境下同時(shí)操作共享數(shù)據(jù)，設(shè)計(jì)各種情形進(jìn)行實(shí)驗(yàn)，深刻體會(huì)并發(fā)控制的方法和重要性。實(shí)驗(yàn)要求：若干學(xué)生一組共同完成本實(shí)驗(yàn)。設(shè)計(jì)一組操作產(chǎn)生“臟“讀的問(wèn)題，然后通過(guò)封鎖避免”臟“讀問(wèn)題。設(shè)計(jì)一組操作產(chǎn)生不可重復(fù)讀的問(wèn)題，然后通過(guò)封鎖避免不可重復(fù)讀問(wèn)題。設(shè)計(jì)一組操作產(chǎn)生丟失更新的問(wèn)題，然后通過(guò)封鎖避免丟失更新問(wèn)題。設(shè)計(jì)一組產(chǎn)生死鎖的操作，再利用相同順序法和一次封鎖法有效地避免死鎖。設(shè)計(jì)一組相對(duì)完整的應(yīng)用，使用設(shè)置隔離級(jí)別來(lái)進(jìn)行并發(fā)控制，然后不同的用戶運(yùn)行相同的程序、操作相同的數(shù)據(jù)觀察并發(fā)控制的效果。在實(shí)驗(yàn)報(bào)告中要給出具體的操作要求和過(guò)程，并針對(duì)各種情況作出具體的分析和討論，很好地體會(huì)事務(wù)的性質(zhì)和并發(fā)控制的作用。相關(guān)命令和提示：查詢一個(gè)已經(jīng)被其他事務(wù)更新、但尚未提交的元組，將會(huì)引起“臟“讀問(wèn)題；為避免該問(wèn)題應(yīng)實(shí)施共享封鎖。為避免不可重復(fù)讀問(wèn)題，應(yīng)該將共享封鎖保持到事務(wù)結(jié)束。為避免丟失更新問(wèn)題，應(yīng)該實(shí)施獨(dú)占封鎖或更新封鎖。Sqlserver的封鎖操作是在相關(guān)語(yǔ)句的“with（<table_hint>）“子句中完成的。設(shè)置隔離級(jí)別的命令是：begintransactioncreatetable院系(編號(hào)smallintprimarykey,名稱char(20))insertinto院系values('10011','管理學(xué)院')不可重復(fù)讀的問(wèn)題:use選課管理begintransaction事務(wù)1select*from院系whe