項目五+進行Transact-SQL程序設(shè)計（課件）-《網(wǎng)站數(shù)據(jù)庫應(yīng)用技術(shù)-SQL+Server》

項目五

進行Transact-SQL程序設(shè)計學(xué)習(xí)目標(biāo)學(xué)習(xí)完本項目后，將能夠：掌握如何定義變量并賦值掌握如何輸出顯示數(shù)據(jù)掌握條件語句與循環(huán)語句理解SQL中批處理的應(yīng)用理解常量與變量的區(qū)別Page2/31重點如何定義變量并賦值條件語句與循環(huán)語句的使用Page3/31難點條件語句與循環(huán)語句SQL中批處理的應(yīng)用Page4/311．常規(guī)標(biāo)識符在T-SQL語句中，不使用分隔標(biāo)識符分開的標(biāo)識符為常規(guī)標(biāo)識符。常規(guī)標(biāo)識符的命名規(guī)則如下：首字符必須以字母、下劃線（_）、“at”符號（@）或者數(shù)字符號（#）開頭。標(biāo)識符不能是T-SQL的保留字。SQLServer保留其保留字的大寫和小寫形式。不允許嵌入空格或其他特殊字符?；A(chǔ)知識1：SQL標(biāo)識符2．分隔符標(biāo)識符包含在雙引號（"）或者方括號（[]）內(nèi)。符合標(biāo)識符格式規(guī)則的標(biāo)識符可以分隔，也可以不分隔。分隔符標(biāo)識符的規(guī)則如下：（1）帶分隔符的標(biāo)識符可以包含的字符數(shù)與常規(guī)標(biāo)識符相同，即可以包含1到128個字符，但不包括分隔符字符本身。本地臨時表標(biāo)識符最多可以包含116個字符。（2）標(biāo)識符的主體可以包含當(dāng)前代碼頁內(nèi)字符（分隔符本身除外）的任意組合。基礎(chǔ)知識1：SQL標(biāo)識符代字號~連字符-驚嘆號!左括號{百分號%右括號}插入號^撇號'“與”符號&句點.左圓括號（

反斜杠\右圓括號）重音符號`表5-1分隔符標(biāo)識符基礎(chǔ)知識1：SQL標(biāo)識符常量是表示特定數(shù)據(jù)值的符號。常量的格式取決于它所表示的值的數(shù)據(jù)類型。常量根據(jù)不同的數(shù)據(jù)類型分為字符串常量、二進制常量、bit常量、datetime常量、integer常量、decimal常量、float和real常量、money常量、uniqueidentifier常量?；A(chǔ)知識2：常量與變量變量是可以存儲數(shù)據(jù)值的對象?？梢允褂镁植孔兞肯騍QL語句傳遞數(shù)據(jù)。在T-SQL中執(zhí)行一批SQL語句時，可以聲明許多變量以便臨時使用。

1.局部變量局部變量的名稱必須以標(biāo)記@作為前綴。聲明局部變量的語句為：DECLAREvariable_nameDataType其中，variable_name為局部變量的名稱，DataType為數(shù)據(jù)類型。實例1：DECLARE@namevarchar(8)--聲明一個存放學(xué)生的變量name,最多可以存放8個字符DECLARE@seatint--聲明一個存放學(xué)生座位號的變量seat基礎(chǔ)知識2：變量2．全局變量變量含義@@ERROR最后一個T-SQL錯誤的錯誤號@@IDENTITY最后一次插入的標(biāo)識值@@LANGUAGE當(dāng)前使用的語言的名稱@@MAX_CONNECTIONS可以創(chuàng)建的同時連接的最大數(shù)目@@ROWCOUNT受上一個SQL語句影響的行數(shù)@@SERVERNAME本地服務(wù)器的名稱@@SERVICENAME該計算機上SQL服務(wù)器的名稱@@TIMETICKS當(dāng)前計算機上每刻度的微秒數(shù)@@TRANSCOUNT 當(dāng)前連接打開的事務(wù)數(shù)@@VERSION SQLServer的版本信息表5-2全局變量基礎(chǔ)知識：變量三、批處理批處理語句是一條或多條SQL語句的集合，SQLServer將批處理語句編譯成一個可執(zhí)行單元，此單元稱為執(zhí)行計劃。每個批處理可以編譯成單個執(zhí)行計劃，從而提供執(zhí)行效率。如果批處理包含多條SQL語句，執(zhí)行這些語句所需的所有優(yōu)化的步驟將編譯在單個執(zhí)行計劃中。批處理的主要好處就是能夠簡化數(shù)據(jù)庫的管理。學(xué)習(xí)任務(wù)1：輸出語句的使用實例1：實施步驟如下：（1）在查詢編輯器中輸入如下代碼：PRINT'本地服務(wù)器的名稱：'+@@SERVERNAMEPRINT'計算機上數(shù)據(jù)庫服務(wù)器的名稱：'+@@SERVICENAME（2）單擊工具欄的按鈕或者按F5鍵。（3）執(zhí)行后產(chǎn)生如圖5.1所示的消息。子任務(wù)1：PRINT語句的使用——輸出本地服務(wù)器的名稱和計算機上數(shù)據(jù)庫服務(wù)器的名稱子任務(wù)2：查詢語句的特殊應(yīng)用——輸出本地服務(wù)器的名稱語法：SELECT局部變量AS自定義列名該方法就是查詢語句的特殊應(yīng)用,用于輸出顯示處理的數(shù)據(jù)結(jié)果。實例：（1）SELECT@@SERVERNAMEAS'服務(wù)器名稱‘（2）單擊工具欄的按鈕或者按F5鍵。（3）查看結(jié)果，SELECT方法的結(jié)果將在網(wǎng)格窗口以表格方式顯示。子任務(wù)3：全局變量的使用實例：（1）在查詢編輯器中輸入如下代碼：USExjglxt/*定義一個局部變量@ERRORNO,保存錯誤號*/DECLARE@ERRORNOINTINSERTINTOxsxxbVALUES('201901008','謝遜','男','1999-9-9','河南省駐馬店市','xiexun@126.com','','')/*把錯誤號臨時保存起來*/SET@ERRORNO=@@ERROR/*如果插入語句出錯,輸出出錯信息及錯誤號*/IF@ERRORNO<>0PRINT'插入數(shù)據(jù)錯誤！'+'錯誤號是：'+CONVERT(varchar,@ERRORNO)PRINT'SQLServer的版本是：'+@@VERSION執(zhí)行后產(chǎn)生如圖所示的消息。子任務(wù)4局部變量的使用——在xsxxb表中根據(jù)座位號查找“趙敏”的左右同桌在查詢編輯器中輸入如下代碼：/*第一步,找出"趙敏"的座位號(zwh)*/DECLARE@namevarchar(8)--定義一個學(xué)生姓名變量SET@name='趙敏'--使用SET賦值SELECT*FROMxsxxbWHERExm=@name/*第二步,對"趙敏"的座位號加1或減1,找趙敏的左右同桌*/DECLARE@seatint--座位號SELECT@seat=zwhFROMxsxxb--使用SELECT賦值WHERExm=@nameSELECT*FROMxsxxbWHERE(zwh=@seat+1)OR(zwh=@seat-1)GO運行結(jié)果如圖所示。學(xué)習(xí)任務(wù)二：邏輯控制語句的使用IF-ELSE語句的語法為：IF（條件）

語句或語句塊ELSE

語句或語句塊子任務(wù)1：IF-ELSE條件語句的使用子任務(wù)1：IF-ELSE條件語句的使用實例1：要求：統(tǒng)計并顯示“數(shù)據(jù)庫”課程的考試平均分，如果在70以上，顯示“成績優(yōu)秀”，并顯示前3名學(xué)生的考試信息。如果在70以下，顯示“本班成績較差”，并顯示后3名學(xué)生的考試信息。分析：第一步，統(tǒng)計平均成績存入臨時變量第二步，用IF-ELSE判斷，并按條件檢索學(xué)生考試信息USExjglxt/*第一步,統(tǒng)計平均成績存入臨時變量,并輸出*/DECLARE@myavgfloatSELECT@myavg=AVG(cj)FROMcjbWHEREkcbh=(SELECTkcbhFROMkcxxbWHEREkcmc='數(shù)據(jù)庫技術(shù)')PRINT'本班平均分為：'+CONVERT(VARCHAR(5),@myavg)/*用IF-ELSE判斷,輸出結(jié)果*/IF(@myavg>70)BEGINPRINT'本班"數(shù)據(jù)庫管理"考試成績優(yōu)秀,前三名的成績?yōu)椋?SELECTTOP3*FROMcjbWHEREkcbh=(SELECTkcbhFROMkcxxbWHEREkcmc='數(shù)據(jù)庫技術(shù)')ORDERBYcjDESCENDELSEBEGINPRINT'本班"數(shù)據(jù)庫技術(shù)"考試成績較差,后三名的成績?yōu)?SELECTTOP3*FROMcjbWHEREkcbh=(SELECTkcbhFROMkcxxbWHEREkcmc='數(shù)據(jù)庫技術(shù)')ORDERBYcjEND執(zhí)行后的結(jié)果如圖所示子任務(wù)2：WHILE循環(huán)語句的使用WHILE循環(huán)語句的語法：WHILE（條件）

語句或語句塊[BREAK]子任務(wù)2：WHILE循環(huán)語句的使用實例2：本次考試成績較差，假定要提分，確保每人SQLServer都通過。提分規(guī)則很簡單，先每人都加2分，看是否都通過，如果沒有全總通過，每人再加2分，再看是否都通過，如此反復(fù)提分，直到所有人都通過為止。分析：第一步，統(tǒng)計沒通過的人數(shù)（<60）。第二步，如果有人沒通過，加分。第三步，循環(huán)判斷。子任務(wù)2：WHILE循環(huán)語句的使用在查詢編輯器中輸入如下代碼：USExjglxtDECLARE@numint/*條件永遠成立,循環(huán)判斷*/WHILE(1=1)BEGIN/*第一步,統(tǒng)計沒通過的人數(shù)(<60)*/SELECT@num=COUNT(*)FROMcjbWHEREcj<60ANDkcbh=(SELECTkcbhFROMkcxxbWHEREkcmc='數(shù)據(jù)庫技術(shù)')/*第二步,如果有人沒通過,加分*/IF(@num>0)UPDATEcjbSETcj=cj+2WHEREcj<=98ELSEBREAK--退出循環(huán)ENDPRINT‘加分后的“數(shù)據(jù)庫技術(shù)”成績?nèi)缦拢骸疭ELECT*FROMcjbWHEREkcbh=(SELECTkcbhFROMkcxxbWHEREkcmc='數(shù)據(jù)庫技術(shù)')執(zhí)行后的結(jié)果如圖

所示。子任務(wù)3：CASE表達式的使用CASE語句的語法如下：CASEWHEN條件1THEN結(jié)果1WHEN條件2THEN結(jié)果2……[ELSE其他結(jié)果]END子任務(wù)3：CASE多分支語句的使用實例3：要求：對本次“數(shù)據(jù)庫技術(shù)”課程考試成績采用ABCDE五級打分制來顯示。A級：90分以上B級：80～89分C級：70～79分D級：60～69分E級：60分以下子任務(wù)3：CASE多分支語句的使用在查詢編輯器中輸入如下代碼：USExjglxt--顯示"數(shù)據(jù)庫管理"課程的原始成績SELECTxh,cjFROMcjbWHEREkcbh=(SELECTkcbhFROMkcxxbWHEREkcmc='數(shù)據(jù)庫技術(shù)')PRINT‘采用ABCDE五級顯示的成績?nèi)缦拢骸?-把cj列的值顯示為相應(yīng)等級SELECTxh,成績=CASEWHENcj<60THEN'E'WHENcjBETWEEN60AND69THEN'D'WHENcjBETWEEN70AND79THEN'C'WHENcjBETWEEN80AND89THEN'B'ElSE'A'ENDFROMcjbWHEREkcbh=(SELECTkcbhFROMkcxxbWHEREkcmc='數(shù)據(jù)庫技術(shù)')學(xué)習(xí)任務(wù)三：批處理語句的使用SELECT*FROMxsxxbSELECT*FROMkcxxbSELECT*FROMcjbGO知識擴展：其他常用語句的使用1．GOTO語句GOTO語句可以讓程序跳到一個指定的標(biāo)簽處并執(zhí)行其后的代碼。GOTO語句和標(biāo)簽可以在過程、批處理和語句塊中的任何位置使用，也可以嵌套使用，其語法代碼如下所述。Label：GOTOlabel2．WAITFOR語句其他常用語句的使用實例1：先執(zhí)行一個查詢語句，查詢班級的信息，過1分鐘再執(zhí)行一個查詢語句，查詢學(xué)員信息，其代碼如下所述。useteachSELECT*FROMT_CLASSGOWAITFORDELAY'00:00:60'SELECT*FROMT_STUDENTGO實例2：先執(zhí)行一個查詢語句，查詢出班級信息，然后在指定的時間（如在12:00:00時）再執(zhí)行此條查詢語句，其代碼如下所述。useteachSELECT*FROMT_CLASSGOWAITFORtime'12:00:00'SELECT*FROMT_CLASSGO知識擴展：其他常用語句的使用3．RETURN語句RETURN語句用于終止當(dāng)前T-SQL語句的執(zhí)行。RETURN的執(zhí)行是即時且完全的，可在任何時候用于從過程、批處理或語句塊中退出。RETURN之后的語句是不執(zhí)行的。其語法代碼如下所述。RETURN[integer_expression]參數(shù)含義如下所述。integer_expression：返回的整數(shù)值。存儲過程可向執(zhí)行調(diào)用的過程或應(yīng)用程序返回一個整數(shù)值。4．TRY…CATCH語句知識擴展：其他常用語句的使用實例3：在Studends表中，刪除姓名為“李玲玲”的學(xué)生信息，其代碼如下所述。useStudentmanagerBEGINTRYDELETEFROMStudendsWHEREsname='李玲玲'ENDTRYBEGINCATCHPRINT'出錯信息為：'+error_message()ENDCATCH知識擴展：變量和參數(shù)的使用實例：創(chuàng)建一個小的測試表并向其填充26行。CREATETABLETestTable(colaint,colbchar(3));GOSETNOCOUNTON;GO

DECLARE@MyCounterint;SET@MyCounter=0;WHILE(@MyCounter<26)BEGIN;INSER