第5章 SQL程序設(shè)計(jì)與開(kāi)發(fā)

數(shù)據(jù)庫(kù)原理與設(shè)計(jì)第5章SQL程序設(shè)計(jì)與開(kāi)發(fā)批處理與腳本SQL程序設(shè)計(jì)基礎(chǔ)流程控制語(yǔ)句游標(biāo)SQL程序的調(diào)試與錯(cuò)誤處理SQL程序?qū)嵗?章SQL程序設(shè)計(jì)與開(kāi)發(fā)在數(shù)據(jù)庫(kù)應(yīng)用的客戶(hù)端適當(dāng)使用批處理具有以下優(yōu)點(diǎn)：減少數(shù)據(jù)庫(kù)服務(wù)器與客戶(hù)端之間的數(shù)據(jù)傳輸次數(shù)，消除過(guò)多的網(wǎng)絡(luò)流量。減少數(shù)據(jù)庫(kù)服務(wù)器與客戶(hù)端之間傳輸?shù)臄?shù)據(jù)量?？s短完成邏輯任務(wù)或事務(wù)所需的時(shí)間。較短的事務(wù)不會(huì)長(zhǎng)期占有數(shù)據(jù)庫(kù)資源，能盡快釋放鎖，有效避免死鎖。增加邏輯任務(wù)處理的模塊化，提高代碼的可復(fù)用度，減少維護(hù)工作量。批處理與腳本將一個(gè)批發(fā)送給SQLServer2000

服務(wù)器用戶(hù)執(zhí)行過(guò)程語(yǔ)句引擎將SQL語(yǔ)句發(fā)送給SQL語(yǔ)句執(zhí)行器SQLServer服務(wù)器PL/SQL引擎SQL

語(yǔ)句

執(zhí)行器過(guò)程語(yǔ)句

執(zhí)行器執(zhí)行SQL語(yǔ)句將結(jié)果發(fā)送給用戶(hù)批處理的執(zhí)行只要批處理中的語(yǔ)句沒(méi)有任何語(yǔ)法錯(cuò)誤，就可以經(jīng)過(guò)編譯建立執(zhí)行計(jì)劃。

（1）不能建立執(zhí)行計(jì)劃的批處理在下面的示例中，批處理中存在語(yǔ)法錯(cuò)誤，不能建立執(zhí)行計(jì)劃，其中Pubs是SQLServer自帶的測(cè)試數(shù)據(jù)庫(kù)。

USEpubsCREATETABLETestBatch(ColaINTPRIMARYKEY,ColbCHAR(3))INSERTINTOTestBatchVALUES(1,'aaa')INSERTINTOTestBatchVALUES(2,'bbb')INSERTINTOTestBatchVALUSE(3,'ccc')/*語(yǔ)法錯(cuò)誤，VALUES拼寫(xiě)錯(cuò)誤*/SELECT*FROMTestBatchGO批處理的執(zhí)行下面的示例沒(méi)有語(yǔ)法錯(cuò)誤，可以建立執(zhí)行計(jì)劃。在執(zhí)行過(guò)程中，由于第3個(gè)INSERT語(yǔ)句產(chǎn)生主鍵重復(fù)的錯(cuò)誤，因此該INSERT語(yǔ)句與之后的SELECT語(yǔ)句不能被執(zhí)行。由于前兩個(gè)INSERT語(yǔ)句成功地執(zhí)行并且提交，因此它們?cè)诎l(fā)生運(yùn)行時(shí)錯(cuò)誤之后被保留下來(lái)。

USEpubsCREATETABLETestBatch(ColaINTPRIMARYKEY,ColbCHAR(3))INSERTINTOTestBatchVALUES(1,'aaa')INSERTINTOTestBatchVALUES(2,'bbb')INSERTINTOTestBatchVALUES(1,'ccc')/*主鍵重復(fù)*/SELECT*FROMTestBatch/*返回行1和2的記錄*/GO編寫(xiě)批處理的規(guī)則①不能在同一個(gè)批處理中更改表，然后引用新列。②不能在刪除一個(gè)對(duì)象之后，立即在同一個(gè)批處理中引用該對(duì)象。③不能在定義一個(gè)CHECK約束后，立即在同一個(gè)批處理中使用該約束。④CREATEDEFAULT、CREATEPROCEDURE、CREATERULE、CREATETRIGGER和CREATEVIEW語(yǔ)句，在一個(gè)批處理中只能提交一個(gè)。⑤如果批處理中的第一句是執(zhí)行某些存儲(chǔ)過(guò)程的EXECUTE語(yǔ)句，則EXECUTE關(guān)鍵字可以省略不寫(xiě)。如果EXECUTE語(yǔ)句不是批處理中的第一條語(yǔ)句，則需要EXECUTE關(guān)鍵字。腳本

Transact-SQL語(yǔ)句的集合稱(chēng)為腳本。

Transact-SQL腳本存儲(chǔ)為文件，帶有sql擴(kuò)展名。把編寫(xiě)好的SQL語(yǔ)句（例如，創(chuàng)建數(shù)據(jù)庫(kù)對(duì)象、調(diào)試通過(guò)的SQL語(yǔ)句集合）保存起來(lái)，以便下一次執(zhí)行同樣（或類(lèi)似）操作時(shí)，調(diào)用這些語(yǔ)句集合。這樣可以省去重新編寫(xiě)調(diào)試SQL語(yǔ)句的麻煩，提高工作效率。腳本文件可以調(diào)入查詢(xún)分析器查看內(nèi)容或再次被執(zhí)行，也可以通過(guò)記事本等瀏覽器查看內(nèi)容。SQL程序設(shè)計(jì)基礎(chǔ)

1．SQL程序基本成分

2．SQL程序編寫(xiě)規(guī)范變量Transact-SQL中的變量分為局部變量和全局變量。局部變量的聲明格式為：

DECLARE@local_variable

data_type[,@local_variable

data_type…..]如：

DECLARE@empidvarINTSET@empidvar=1234SELECT*FROMEmployeesWHEREEmployeeid=@empidvarDECLARE@pub_idCHAR(4),@hire_dateDATETIMESET@pub_id='0877'SET@hire_date='1/01/93'SELECT@pub_id='0877',@hire_date='1/01/93'/*使用SELECT賦值也可以*/SELECTFname,LnameFROMEmployeeWHEREPub_id=@pub_idANDHire_date>=@hire_date

運(yùn)算符SQLServer提供賦值運(yùn)算符、算術(shù)運(yùn)算、邏輯運(yùn)算、位運(yùn)算、比較運(yùn)算、字符串連接運(yùn)算符等。賦值運(yùn)算符“=”用于將表達(dá)式的值賦給某個(gè)變量。算術(shù)運(yùn)算符在兩個(gè)表達(dá)式上執(zhí)行數(shù)學(xué)運(yùn)算，包括加法（+）、減法（?）、乘法（*）、除法（/）、取模（%）等運(yùn)算，加減運(yùn)算也可用于datetime和smalldatetime日期類(lèi)型。位運(yùn)算符可以在兩個(gè)表達(dá)式之間執(zhí)行位操作，包括按位與（&）、按位或（|）、按位異或（^）。表達(dá)式的數(shù)據(jù)類(lèi)型可以是整型或與整型兼容的數(shù)據(jù)類(lèi)型。比較運(yùn)算符用于測(cè)試兩個(gè)表達(dá)式之間值的關(guān)系，包括=、>、<、>=、<=、<>、！<、！>，比較運(yùn)算的結(jié)果是布爾類(lèi)型。邏輯運(yùn)算符用于對(duì)某個(gè)條件進(jìn)行測(cè)試。字符串連接運(yùn)算符（+）可以進(jìn)行字符串連接，例如，‘ABC’+‘DEF’的運(yùn)算結(jié)果為‘ABCDEF’。當(dāng)一個(gè)復(fù)雜表達(dá)式包含若干運(yùn)算符時(shí)，運(yùn)算符按照優(yōu)先級(jí)順序執(zhí)行，先執(zhí)行優(yōu)先級(jí)高的運(yùn)算符，后執(zhí)行優(yōu)先級(jí)低的運(yùn)算符。函數(shù)SQLServer在標(biāo)準(zhǔn)SQL的基礎(chǔ)上，提供了豐富的函數(shù)，包括日期函數(shù)、字符串函數(shù)、數(shù)學(xué)函數(shù)、聚合函數(shù)、系統(tǒng)函數(shù)等。例如可以使用下面的語(yǔ)句得到當(dāng)前日期、并進(jìn)行日期處理。

SELECTcurr_date=GETDATE(?),cur_mon=MONTH(GETDATE(?)),cur_day=DAY(getdate(?)),pre_date=DATEADD(day,-1,GETDATE(?)),next_date=DATEADD(day,1,GETDATE(?))SELECTLTRIM('fivespacesareatthebeginningofthisstring.')--去掉左面空格SELECTpub_id,SUBSTRING(pr_info,1,10)ASpr_infoFROMpub_infoWHEREpub_id='1756'SELECTSTUFF('abcdef',2,3,'ijklmn')自己定義函數(shù)（1）

CREATEFUNCTIONCubicVolume(@CubeLengthNUMERIC(4,1),@CubeWidthNUMERIC(4,1),@CubeHeightNUMERIC(4,1))/*輸入?yún)?shù)長(zhǎng)、寬、高及其數(shù)據(jù)類(lèi)型*/RETURNSNUMERIC(12,3)/*函數(shù)返回參數(shù)類(lèi)型*/ASBEGINRETURN(@CubeLength*@CubeWidth*@CubeHeight)/*函數(shù)返回值*/END自己定義函數(shù)（2）

CREATEFUNCTIONCUBICVOLUME_2(@CubelengthNUMERIC(4,1),@CubewidthNUMERIC(4,1),@CubeheightNUMERIC(4,1))RETURNSNUMERIC(12,3)ASBEGINDECLARE@VolumeNUMERIC(12,3);/*定義變量*/IF((@CubeLength>0)AND(@CubeWidth>0)AND(@CubeHeight>0))SELECT@Volume=@CubeLength*@CubeWidth*@CubeHeight

ELSESELECT@Volume=-1;/*條件分支流程*/RETURN(@Volume)ENDSQL程序編寫(xiě)規(guī)范對(duì)變量和數(shù)據(jù)庫(kù)對(duì)象等標(biāo)識(shí)符采用有意義的命名編寫(xiě)代碼時(shí)養(yǎng)成合理的大小寫(xiě)習(xí)慣對(duì)存儲(chǔ)過(guò)程、游標(biāo)等數(shù)據(jù)庫(kù)對(duì)象命名時(shí)，采用適當(dāng)?shù)那熬Y和后綴代碼采用縮進(jìn)方式在程序中增加適當(dāng)?shù)淖⑨屃鞒炭刂普Z(yǔ)句關(guān)

鍵

字描

述BEGIN...END定義語(yǔ)句塊BREAK退出最內(nèi)層的WHILE循環(huán)CONTINUE重新開(kāi)始WHILE循環(huán)GOTOlabel從label所定義的label之后的語(yǔ)句處繼續(xù)進(jìn)行處理IF...ELSE定義條件以及當(dāng)一個(gè)條件為FALSE時(shí)的操作RETURN無(wú)條件退出WAITFOR為語(yǔ)句的執(zhí)行設(shè)置延遲WHILE當(dāng)特定條件為T(mén)RUE時(shí)重復(fù)語(yǔ)句語(yǔ)句塊：BEGIN…END

USEpubsIF(SELECTCOUNT(*)FROMdeleted,salesWHEREsales.title_id=deleted.title_id)>0BEGINROLLBACKTRANSACTIONPRINT'Youcan'tdeleteatitlewithsales.'ENDBEGIN…END關(guān)鍵字之間封裝了一系列的SQL語(yǔ)句，形成一個(gè)語(yǔ)句塊，代表一組一起執(zhí)行的SQL語(yǔ)句。BEGIN…END的語(yǔ)法結(jié)構(gòu)如下：

BEGINSQL語(yǔ)句1SQL語(yǔ)句2……END條件執(zhí)行：IF...ELSE語(yǔ)句IF...ELSE語(yǔ)句的語(yǔ)法結(jié)構(gòu)如下：

IF布爾表達(dá)式

{SQL語(yǔ)句|SQL語(yǔ)句塊}[ELSE

{SQL語(yǔ)句|SQL語(yǔ)句塊}]IF...ELSE語(yǔ)句允許嵌套，可以在其他IF之后或在ELSE下面，嵌套另一個(gè)IF語(yǔ)句，嵌套層數(shù)沒(méi)有限制。

條件執(zhí)行：IF...ELSE語(yǔ)句（2）

USEpubsIF(SELECTAVG(price)FROMtitlesWHEREtype='mod_cook')<$15BEGINPRINT'thefollowingtitlesareexcellentmod_cookbooks:'PRINT''SELECTSUBSTRING(title,1,35)ASTitleFROMtitlesWHEREtype='mod_cook'

ENDELSEPRINT'Averagetitlepriceismorethan$15.'條件執(zhí)行：IF...ELSE語(yǔ)句（3）USEpubsIF(SELECTAVG(price)FROMtitlesWHEREtype='mod_cook')<$15BEGINPRINT'Thefollowingtitlesareexcellentmod_cookbooks:'PRINT''SELECTSUBSTRING(title,1,35)ASTitleFROMtitlesWHEREtype='mod_cook'ENDELSEIF(SELECTAVG(price)FROMtitlesWHEREtype='mod_cook')>$15BEGINPRINT'Thefollowingtitlesareexpensivemod_cookbooks:'PRINT''SELECTSUBSTRING(title,1,35)ASTitleFROMtitlesWHEREtype='mod_cook'END多分支CASE表達(dá)式簡(jiǎn)單CASE函數(shù)將某個(gè)表達(dá)式與一組簡(jiǎn)單表達(dá)式進(jìn)行比較以確定結(jié)果。CASE搜索函數(shù)計(jì)算一組布爾表達(dá)式以確定結(jié)果。簡(jiǎn)單CASE表達(dá)式的語(yǔ)法結(jié)構(gòu)如下：

CASE表達(dá)式

WHEN表達(dá)式THEN表達(dá)式

......

[ELSE表達(dá)式]

ENDCASE搜索函數(shù)的語(yǔ)法結(jié)構(gòu)如下：

CASE

WHEN布爾表達(dá)式THEN表達(dá)式

......

[ELSE表達(dá)式]

END多分支CASE表達(dá)式

USEpubsGOSELECTCategory=CASEtypeWHEN'popular_comp'THEN'PopularComputing'WHEN'mod_cook'THEN'ModernCooking'WHEN'business'THEN'Business'WHEN'psychology'THEN'Psychology'WHEN'trad_cook'THEN'TraditionalCooking'ELSE'Notyetcategorized'END,CAST(titleASvarchar(25))AS'ShortenedTitle',priceASPriceFROMtitlesWHEREpriceISNOTNULLORDERBYtype,priceCOMPUTEAVG(price)BYtype--CAST函數(shù)的功能是將某種數(shù)據(jù)類(lèi)型的表達(dá)式顯式轉(zhuǎn)換為另一種數(shù)據(jù)類(lèi)型循環(huán)：WHILE語(yǔ)句WHILE語(yǔ)句的語(yǔ)法結(jié)構(gòu)如下：

WHILE布爾表達(dá)式

{SQL語(yǔ)句|SQL語(yǔ)句塊}

[BREAK]

{SQL語(yǔ)句|SQL語(yǔ)句塊}

[CONTINUE]WHILE語(yǔ)句也允許嵌套。BREAK語(yǔ)句使程序從最內(nèi)層的WHILE循環(huán)中退出。CONTINUE語(yǔ)句使WHILE循環(huán)重新開(kāi)始執(zhí)行，忽略CONTINUE關(guān)鍵字后的語(yǔ)句。循環(huán)：WHILE語(yǔ)句（2）USEpubsGOWHILE(SELECTAVG(price)FROMtitles)<$30BEGINUPDATEtitlesSETprice=price*2SELECTMAX(price)FROMtitlesIF(SELECTMAX(price)FROMtitles)>$50BREAKELSECONTINUEENDPRINT'Toomuchforthemarkettobear'循環(huán)：WHILE語(yǔ)句（3）

DECLARE@countersmallintSET@counter=1WHILE@counter<5BEGINSELECTRAND(@counter)Random_NumberSETNOCOUNTONSET@counter=@counter+1SETNOCOUNTOFFENDGO非條件執(zhí)行：GOTO語(yǔ)句GOTO語(yǔ)句的語(yǔ)法結(jié)構(gòu)如下：

標(biāo)簽: --定義標(biāo)簽

GOTO標(biāo)簽 --改變執(zhí)行

USEpubsGODECLARE@tablename

sysnameSET@tablename=N'authors'

table_loop: --定義標(biāo)簽

IF(@@FETCH_STATUS<>-2)BEGINSELECT@tablename=RTRIM(UPPER(@tablename))EXEC("SELECT"""+@tablename+"""=COUNT(*)FROM"+@tablename)PRINT""ENDFETCHNEXTFROMtnames_cursorINTO@tablenameIF(@@FETCH_STATUS<>-1)GOTOtable_loop--改變執(zhí)行

GO調(diào)度執(zhí)行：WAITFORBEGINWAITFORTIME'22:20'EXECUTEupdate_all_statsEND。語(yǔ)法結(jié)構(gòu)如下：

WAITFOR{DELAY'時(shí)間'|TIME'時(shí)間'}其中DELAY指定等待的時(shí)間間隔，最長(zhǎng)可達(dá)24小時(shí)；TIME指定等待到的時(shí)間點(diǎn)，即觸發(fā)的具體時(shí)間；時(shí)間可以是datetime

數(shù)據(jù)類(lèi)型，格式為hh:mm:ss，不指定日期。在晚上10:20執(zhí)行存儲(chǔ)過(guò)程update_all_stats。游標(biāo)SELECT語(yǔ)句返回所有滿(mǎn)足條件的完整記錄集，在數(shù)據(jù)庫(kù)應(yīng)用程序中常常需要處理結(jié)果集的一行或多行。游標(biāo)（CURSOR）是結(jié)果集的邏輯擴(kuò)展，可以看作是指向結(jié)果集的一個(gè)指針，通過(guò)使用游標(biāo)，應(yīng)用程序可以逐行訪(fǎng)問(wèn)并處理結(jié)果集。使用游標(biāo)時(shí)，應(yīng)先聲明，然后打開(kāi)，接著使用；使用完后關(guān)閉、釋放資源。游標(biāo)聲明游標(biāo)：DECLARECURSOR語(yǔ)句打開(kāi)游標(biāo)：OPEN語(yǔ)句讀取數(shù)據(jù)：FETCH語(yǔ)句關(guān)閉游標(biāo)：CLOSE語(yǔ)句釋放游標(biāo)：DEALLOCATE語(yǔ)句游標(biāo)使用實(shí)例

USEMSBEGINDECLARE@CnoVARCHAR(5)

--變量：課程編號(hào)

DECLARE@ClnameVARCHAR(30) --變量：班級(jí)名稱(chēng)

DECLARE@clnoVARCHAR(6) --變量：班級(jí)編號(hào)

DECLARE@avgscoreNUMERIC(10,2) --變量：平均成績(jī)

DECLARE@CtermINT --變量：學(xué)期

DECLAREclass_cursorCURSORFORSELECTclname,clno,dbo.termConvert('2005-2006/2',clno)FROMclass --聲明班級(jí)游標(biāo)

/*其中termConvert函數(shù)是自定義函數(shù)，可以將如“2006-2007/2”的學(xué)期表述的字符串方式轉(zhuǎn)換為如1、2、3等表述的數(shù)字方式。如2005年入學(xué)的同學(xué)的“2006-2007/2”學(xué)期是其在校的第4學(xué)期*/OPENclass_cursor --打開(kāi)班級(jí)游標(biāo)

FETCHNEXTFROMclass_cursorINTO@CLname,@clno,@Cterm--讀取游標(biāo)數(shù)據(jù)

WHILE@@FETCH_STATUS=0--檢測(cè)游標(biāo)數(shù)據(jù)是否讀取完，如果還有數(shù)據(jù)，繼續(xù)循環(huán)

BEGINSET@avgscore=(SELECTISNULL(avg(score),0)FROMsca,studentb,classc,coursedWHEREa.sno=b.snoANDb.clno=c.clnoANDb.clno=@clnoANDo=oANDd.Cterm=@Cterm)

游標(biāo)使用實(shí)例（2）IF@avgscore>0/*根據(jù)班級(jí)平均成績(jī)是否為0判斷，該班的成績(jī)是否登記，如果為0，表明沒(méi)有登記該班的在2005-2006/2學(xué)期的成績(jī)*/BEGINPRINT'2005-2006/2'+'學(xué)期'+@CLname+'各門(mén)課總平均成績(jī)?yōu)?+str(@avgscore,5,1)--每個(gè)學(xué)生的平均成績(jī)和獲得的學(xué)分

PRINT'該班每個(gè)學(xué)生的平均成績(jī)?nèi)缦拢?SELECTe.sname,d.avgscore,totalCreditFROM(SELECTa.sno,AVG(score)avgscore,SUM(dbo.CreditConvert(score,CCredits))totalCreditFROMstudenta,scb,coursecWHEREa.sno=b.snoANDo=oANDc.cterm=@CtermGROUPBYa.sno)d,studente,classfWHEREe.sno=d.snoANDe.clno=f.clnoANDf.clno=@clnoENDELSEPRINT'2005-2006/2'+'學(xué)期'+@CLname+'成績(jī)沒(méi)有登記'FETCHNEXTFROMclass_cursorINTO@CLname,@clno,@CtermENDCLOSEclass_cursorDEALLOCATEclass_cursor

ENDSQL程序的錯(cuò)誤類(lèi)型語(yǔ)法錯(cuò)誤是指不符合Transact-SQL規(guī)范的錯(cuò)誤，這類(lèi)錯(cuò)誤會(huì)導(dǎo)致SQL語(yǔ)句不能編譯。不熟悉語(yǔ)法規(guī)范經(jīng)常會(huì)導(dǎo)致這類(lèi)錯(cuò)誤的發(fā)生。還有一些錯(cuò)誤雖然沒(méi)有語(yǔ)法錯(cuò)誤，也不會(huì)使SQL語(yǔ)句執(zhí)行失敗，但確實(shí)不能實(shí)現(xiàn)預(yù)設(shè)的功能，這是一種邏輯錯(cuò)誤。邏輯錯(cuò)誤不僅會(huì)發(fā)生在程序開(kāi)發(fā)階段，也常常會(huì)出現(xiàn)在程序?qū)嶋H運(yùn)行階段。只有非常清楚程序需要實(shí)現(xiàn)的功能，才能避免此類(lèi)錯(cuò)誤。如果發(fā)生了邏輯錯(cuò)誤，可以跟蹤程序的執(zhí)行流程，逐行、逐段地檢查程序執(zhí)行結(jié)果，發(fā)現(xiàn)產(chǎn)生問(wèn)題的原因。運(yùn)行時(shí)錯(cuò)誤是指SQL程序在執(zhí)行過(guò)程中出現(xiàn)的意想不到的錯(cuò)誤，如由于鎖表影響對(duì)數(shù)據(jù)表的更改、修改數(shù)據(jù)庫(kù)對(duì)象時(shí)違反數(shù)據(jù)完整性與一致性的約束等。這類(lèi)錯(cuò)誤導(dǎo)致了一些SQL語(yǔ)句執(zhí)行失敗。采取必要的邊界處理和錯(cuò)誤處理，可以減少此類(lèi)錯(cuò)誤的發(fā)生次數(shù)。SQL程序的錯(cuò)誤處理1．定位錯(cuò)誤發(fā)生的位置2．判定錯(cuò)誤原因3．簡(jiǎn)化程序以便調(diào)試學(xué)分轉(zhuǎn)換函數(shù)①功能要求：將學(xué)生考試成績(jī)轉(zhuǎn)換成學(xué)分的功能。如果考試通過(guò)獲得該課程的學(xué)分，否則獲得學(xué)分為0。②入口參數(shù)：成績(jī)和課程學(xué)分，③返回：返回應(yīng)得學(xué)分。

CREATEFUNCTIONCreditConvert(@scoreNUMERIC(3,1),@CCreditsNUMERIC(3,1))--@score：考試成績(jī)

--@CCredits：課程規(guī)定學(xué)分

RETURNSNUMERIC(5,2)--應(yīng)得學(xué)分

ASBEGINRETURNCASESIGN(@score-60)WHEN1THEN@CCreditsWHEN0then@CCreditsWHEN-1then0ENDEND學(xué)期轉(zhuǎn)換函數(shù)②入口參數(shù)：學(xué)年和入學(xué)年份③返回：數(shù)字表示的學(xué)期。

--termConvert

功能：學(xué)期轉(zhuǎn)換

CREATEfunctiontermConvert(@tremCHAR(11),@clnoCHAR(6))--@trem

學(xué)年，格式如：2006-2007/2--@clno

班級(jí)編號(hào)，格式如：020001，前2位代表入學(xué)年份

RETURNSINT--在校第幾學(xué)期

ASBEGINRETURNCONVERT(NUMERIC,SUBSTRING(@trem,1,4))-CONVERT(NUMERIC,'20'+SUBSTRING(@clno,1,2)))*2+CONVERT(NUMERIC,SUBSTRING(@trem,11,1))END統(tǒng)計(jì)平均成績(jī)

CREATEPROCEDUREp_AverageScore@termvarchar(11)--入口參數(shù)：學(xué)期

--學(xué)期的格式為：XXXX-XXXX/X。

--前9位標(biāo)別學(xué)年，最后一位表示本學(xué)年的第幾學(xué)期。如2005-2006/2表示2005-2006學(xué)年的第2學(xué)期。

ASBEGINDECLARE@CnoVARCHAR(5)--變量：課程編號(hào)

DECLARE@ClnameVARCHAR(30)--變量：班級(jí)名稱(chēng)

DECLARE@clnoVARCHAR(6)--變量：班級(jí)編號(hào)

DECLARE@avgscoreNUMERIC(10,2)--變量：平均成績(jī)

DECLARE@CtermINT--變量：學(xué)期

DECLAREclass_cursorCURSORFORSELECTCLname,CLno,dbo.termConvert(@term,clno)FROMclass--聲明班級(jí)游標(biāo)

/*其中termConvert函數(shù)是自定義函數(shù)，可以將如“2006-2007/2”的學(xué)期表述的字符串方式轉(zhuǎn)換為如1、2、3等表述的數(shù)字方式。如2005年入學(xué)的同學(xué)的“2006-2007/2”學(xué)期是其在校的第4學(xué)期*/

統(tǒng)計(jì)平均成績(jī)

OPENclass_cursor--打開(kāi)班級(jí)游標(biāo)

FETCHNEXTFROMclass_cursorINTO@CLname,@clno,@Cterm--讀取游標(biāo)數(shù)據(jù)

WHILE@@FETCH_STATUS=0--檢測(cè)游標(biāo)數(shù)據(jù)是否讀取完，如果還有數(shù)據(jù)，繼續(xù)循環(huán)

BEGINSET@avgscore=(SELECTISNULL(avg(Score),0)FROMSCa,Studentb,Classc,CoursedWHEREa.SNo=b.SNoANDb.CLno=c.CLnoANDb.CLno=@clnoANDa.CNo=d.CNoANDd.Cterm=@Cterm)IF@avgscore>0/*根據(jù)班級(jí)平均成績(jī)是否為0判斷該班的成績(jī)是否登記，如果為0，表明沒(méi)有登記該班的在本學(xué)期的成績(jī)*/BEGINPRINT@term+'學(xué)期'+@CLname+'各門(mén)課總平均成績(jī)?yōu)?+str(@avgscore,5,1)--每個(gè)學(xué)生的平均成績(jī)和獲得的學(xué)分

PRINT'該班每個(gè)學(xué)生的平均成績(jī)?nèi)缦拢?SELECTe.SName,d.avgscore,totalCreditFROM(SELECTa.SNo,AVG(score)avgscore,SUM(dbo.CreditConvert(score,CCredits))totalCreditFROMStudenta,SCb,CoursecWHEREa.SNo=b.SNoANDb.CNo=c.CNoANDc.Cterm=@CtermGROUPBYa.SNo)d,Studente,ClassfWHEREe.SNo=d.SNoANDe.CLno=f.CLnoANDf.CLno=@clnoENDELSEPRINT@term+'學(xué)期'+@CLname+'成績(jī)沒(méi)有登記'FETCHNEXTFROMclass_cursorINTO@CLname,@clno,@CtermENDCLOSEclass_cursor

DEALLOCATEclass_cursor

END統(tǒng)計(jì)不同分?jǐn)?shù)段的人數(shù)和平均成績(jī)

CREATEPROCEDUREp_SatSore

@cnoCHAR(5) --入口參數(shù)：班級(jí)編號(hào)

@clnoCHAR(6) --入口參數(shù)：課程編號(hào)

ASBEGINDECLARE@socre1INT --待統(tǒng)計(jì)分?jǐn)?shù)段上限

DECLARE@socre2INT --待統(tǒng)計(jì)分?jǐn)?shù)段下限

DECLARE@numINT --待統(tǒng)計(jì)分?jǐn)?shù)段人數(shù)

DECLARE@CLNAMEVARCHAR(30) --班級(jí)名稱(chēng)

DECLARE@CNAMEVARCHAR(50) --課程名稱(chēng)

--查詢(xún)課程名稱(chēng)和班級(jí)名稱(chēng)

SET@CLNAME=(SELECTCLNAMEFROMCLASSWHERECLNO=@clno)SET@CNAME=(SELECTCNAMEFROMCOURSEWHERECNO=@cno)PRINT@CLNAME+'<'+@CNAME+'>'+'考試成績(jī)按照分?jǐn)?shù)段統(tǒng)計(jì)情況'--設(shè)置被統(tǒng)計(jì)分?jǐn)?shù)段的初值

SET@socre1=100SET@socre2=90WHILE(@socre1>=60)BEGINSET@num=(SELECTcount(*)FROMSCa,Classb,StudentcWHEREb.CLno=c.CLnoANDa.SNo=c.SNoANDb.C