數(shù)據(jù)庫應用與開發(fā)課件第6章

第6章T-SQL程序設計

主要學習內(nèi)容變量邏輯控制語句循環(huán)語句批處理語句

學習目標了解變量的定義和種類熟練掌握邏輯控制語句熟練掌握循環(huán)語句熟練掌握批處理語句

導入案例程序設計語言擁有變量、流程結(jié)構設計等等，這使得程序設計有明顯的算法塊，應用非常靈活。微軟為了使得SQLServer的T-SQL語言不僅擁有標準SQL語言的定義，還能擁有靈活方便的程序設計能力，使得有程序設計語言基礎的數(shù)據(jù)庫使用人員，能夠迅速開發(fā)出性能良好的算法。使得SQLServer數(shù)據(jù)庫的存儲和管理數(shù)據(jù)能力更加強大，應用程序開發(fā)人員的程序前臺開發(fā)工作與后臺開發(fā)分離，加快開發(fā)速度，提高開發(fā)效率，更加易于管理。

6.1變量SQLServer的Transact-SQL（簡稱T-SQL）語言的特點之一是加入了變量的使用，這使得T-SQL程序設計非常靈活。變量是SQLServer中由系統(tǒng)或用戶定義并可對其賦值的實體，變量分為局部變量(LocalVariable)和全局變量(GlobalVariable)。

6.1.1全局變量全局變量又稱系統(tǒng)變量，是由SQLServer系統(tǒng)提供并賦值的變量，用來記錄SQLServer服務器活動狀態(tài)數(shù)據(jù)

常用變量表變量含義@@ERROR最后一個T-SQL錯誤的錯誤號@@IDENTITY最后一次插入的標識值@@LANGUAGE當前使用的語言的名稱@@MAX_CONNECTIONS可以創(chuàng)建的同時連接的最大數(shù)目@@ROWCOUNT 受上一個SQL語句影響的行數(shù)@@SERVERNAME本地服務器的名稱@@TRANSCOUNT 當前連接打開的事務數(shù)@@VERSION SQLSERVER的版本信息

常用變量表注意：全局變量必須以標記@@作為前綴，如@@version。全局變量由系統(tǒng)定義和維護，我們只能讀取，不能修改全局變量的值。

全局變量【例6.1】演示全局變量的使用？USEStudentprint'SQLServer的版本'+@@VERSIONprint'服務器的名稱:'+@@SERVERNAMEINSERTINTOstuInfo(stuName,stuNo,stuSex,stuAge)VALUES('劉樂樂','2019000005','男',23)--如果大于0表示上一條語句執(zhí)行有錯誤print'當前錯誤號'+convert(varchar(5),@@ERROR)print'剛才報名的學員，座位號為:'+convert(varchar(5),@@IDENTITY)UPDATEstuinfoSETstuAge=25WHEREstuName='劉樂樂'print'當前錯誤號'+convert(varchar(5),@@ERROR)GO

全局變量注：Print語句表示把字符串打印在屏幕上。后面必須跟字符串，而@@ERROR是整型變量，所以要把它強制轉(zhuǎn)換成字符串類型。

6.1.2局部變量局部變量是用戶自定義的變量。它不像全局變量那樣由系統(tǒng)提供，用戶可以隨時隨地直接使用，而必須由用戶自己定義，定義后賦初值了，才能使用，而且生命周期僅限于用戶定義它的那個程序塊，出了這個程序塊，它的生命就結(jié)束了。局部變量必須以標記@作為前綴，如@age。

局部變量聲明局部變量

DECLARE@變量名數(shù)據(jù)類型例如：DECLARE@namevarchar(8)DECLARE@seatint局部變量的賦值

SET@變量名=值

SELECT@變量名=值例如：SET@name=‘張三’SELECT@name=stuNameFROMstuInfoWHEREstuNo=‘2019000001’

局部變量注：1.先聲明再賦值；2.賦值有兩種方式：

1）使用set2）使用selectset用于普通的賦值。

select用于從表中查詢數(shù)據(jù)并賦值。3.使用select語句賦值時，必須保證篩選的記錄只有1條，否則取最后一條。所以T－SQL語句后面一般接WHERE篩選條件。4.變量定義的同時可以初始化例：declare@nint=5

局部變量【例6.2】編寫T-SQL在學生信息表中查找劉樂樂同學的同桌？使用數(shù)據(jù)庫Student中的數(shù)據(jù)表StuInfo(stuNo,stuName,stuAge,stuSex,stuSeat,stuAddress):具體表結(jié)構信息如下：

USE[student]CREATETABLE[dbo].[stuInfo]( [stuNo][varchar](10)primarykey, [stuName][varchar](10)NOTNULL, [stuSex][varchar](2)NOTNULL, [stuSeat][int]IDENTITY(1,1)NOTNULL, [stuAge][int]NULL, [stuAddress][varchar](50)NULL,)GO

插入記錄

insertintostuInfo(stuNo,stuName,stuSex,stuAge)select'2019000001','x','男',20unionselect'2019000005','劉樂樂','女',25unionselect'2019000006','y','女',20go

局部變量分析：同桌，即是上課時坐在左右的同學。座位號是橫向排列的，所以可以通過座位號來定位同桌。第一步，找出劉樂樂的座位號；第二步，劉樂樂的座位號減1或加1即是他的左右同學了。

局部變量UseStudent/*--查找劉樂樂的信息--*/DECLARE@namevarchar(10)--學員姓名SET@name='劉樂樂'--使用SET賦值SELECT*FROMstuInfoWHEREstuName=@name/*--查找劉樂樂的左右同桌--*/DECLARE@seatint--座位號SELECT@seat=stuSeatFROMstuInfo--使用SELECT賦值

WHEREstuName=@nameSELECT*FROMstuInfoWHERE(stuSeat=@seat+1)OR(stuSeat=@seat-1)GO

6.2邏輯控制語句在T-SQL程序設計中，邏輯控制語句有單分支條件語句IF-ELSE和多分支條件語句CASE-END。

6.2.1IF－ELSE語句SQL中的IF-ELSE語句語法如下：IF（條件）

BEGIN

語句1

語句2……ENDELSEBEGIN

語句1；語句2；

……END注：ELSE是可選部分；如果有多條語句，才需要BEGIN-END語句塊。

IF－ELSE語句【例6.3】統(tǒng)計并班級考試情況，如果班級平均分在85以上，顯示“成績優(yōu)秀”，并顯示前三名學員的考試信息；如果在85以下，顯示“本人成績較差”，并顯示后三名學員的考試信息。數(shù)據(jù)庫為jsj2019數(shù)據(jù)表為stuInfo、courseInfo和scores原數(shù)據(jù)

分析：第一步，統(tǒng)計平均成績存入臨時變量；第二步，用IF－ELSE判斷；答：declare@avgfloatselect@avg=avg(score)fromscoresif@avg>=85beginprint'成績優(yōu)秀'selecttop3stuInfo.stuNo,stuName,cName,scorefromstuInfoinnerjoinscoresonstuInfo.stuNo=scores.stuNoinnerjoincourseInfoonscores.cNo=courseInfo.cNoorderbyscoredescendelsebeginprint'成績較差'selecttop3stuInfo.stuNo,stuName,cName,scorefromstuInfoinnerjoinscoresonstuInfo.stuNo=scores.stuNoinnerjoincourseInfoonscores.cNo=courseInfo.cNoorderbyscoreendgo案例執(zhí)行

6.2.2CASE－END多分支語句T-SQL中CASE語句的語法如下：CASEWHEN條件1THEN結(jié)果1WHEN條件2THEN結(jié)果2……ELSE其他結(jié)果END

CASE－END多分支語句【例6.4】采用美國的ABCDE五級打分制來顯示筆試成績。A級:90分以上

B級：80－89分

C級:70－79分

D級：60－69分

E級：60分以下

CASE－END多分支語句答：print'ABCDE五級顯示成績?nèi)缦拢?SELECTstuNo,cNo,

成績=CASEWHENscore<60THEN'E'WHENscoreBETWEEN60AND69THEN'D'WHENscoreBETWEEN70AND79THEN'C'ElSE'A'ENDFROMSCORES

CASE－END多分支語句注：CASE－END的語法比較難于理解，與C語言中switch-case還是有較大區(qū)別的：1.CASE－END作為一個整體使用，它是有唯一返回值的，如：SELECTstuNo,cNo,成績=某個值FROMscores。2.成績是別名，等價使用AS。3.成績后的值不確定，它的值由CASE－END多分支決定。CASE語句根據(jù)每條記錄中的成績，進行判斷，結(jié)果是A還是B等。

CASE－END多分支語句【例6.5】請根據(jù)平均分和下面的評分規(guī)則，編寫T-SQL語句查詢學員的成績優(yōu)：90分以上良：80－89分中：70－79分差：60－69分不及格：60分以下數(shù)據(jù)表：stuMarks(stuNo,ExamNo,writtenExam,labExam)答：

CASE－END多分支語句USEstudentDBSelect*fromstuInfoSelect*fromstuMarksSELECT考號=ExamNo,學號=stuNo,筆試=writtenExam,機試=labExam,

平均分=(writtenExam+labExam)/2,

等級=CASEWHEN(writtenExam+labExam)/2<60THEN'不及格'WHEN(writtenExam+labExam)/2BETWEEN60AND69THEN'差'WHEN(writtenExam+labExam)/2BETWEEN70AND79THEN'中'WHEN(writtenExam+labExam)/2BETWEEN80AND89THEN'良'ElSE'優(yōu)'ENDFROMstuMarksGO

6.3循環(huán)語句SQL中的WHILE語句的語法如下：WHILE（條件）

BEGIN

語句1

語句2……BREAKEND注：BREAK表示退出循環(huán)，如果有多條語句，才需要BEGIN-END語句塊。

循環(huán)語句【例6.6】某次考試成績較差，假定要提分，確保每人筆試都通過。提分規(guī)則很簡單，先每人都加2分，看是否都通過，如果沒有全部通過，每人再加2分，再看是否都通過，如此反復提分，直到所有人都通過為止。數(shù)據(jù)表：stuMarks(stuNo,ExamNo,writtenExam,labExam)分析：第一步，統(tǒng)計沒通過的人數(shù)；

第二步，如果有人沒通過，加分；

第三步，循環(huán)判斷。

循環(huán)語句答：USEstudentDB---加分前----select*fromstuMarksDECLARE@nintWHILE(1=1)--條件永遠成立

BEGINSELECT@n=COUNT(*)FROMstuMarksWHEREwrittenExam<60--統(tǒng)計不及格人數(shù)

IF(@n>0)UPDATEstuMarks--每人加2分

SETwrittenExam=writtenExam+2ELSEBREAK--退出循環(huán)

ENDSELECT*FROMstuMarks--加分后的成績?nèi)缦拢?-

6.4批處理語句批處理是包含一個或多個SQL語句的組，從應用程序一次性地發(fā)送到SQLServer執(zhí)行。SQLServer將批處理語句編譯成一個可執(zhí)行單元，此單元稱為執(zhí)行計劃。執(zhí)行計劃中的語句每次執(zhí)行一條。

批處理語句批處理語句語法如下：語句1語句2……GO注：GO是批處理的標志，表示SQLServer將這些T-SQL語句編譯為一個執(zhí)行單元，提高執(zhí)行效率。

批處理語句假設SQLServer是網(wǎng)絡數(shù)據(jù)庫，一臺服務器可能有很多遠程客戶端，如果在客戶端一次發(fā)送1條SQL語句，然后客戶返回結(jié)果；然后再發(fā)送1條SQL語句，再返回，效率太低。因此批處理語句可提高效率。一般是將一些邏輯相關的業(yè)務操作語句，放置在同一批中，這完全由業(yè)務需求和代碼編寫者決定SQLServer規(guī)定，如果是建庫、建表語句、以及我們后面學習的存儲過程和視圖等，則必須在語句末尾添加GO批處理標志。

批處理語句【例6.7】根據(jù)如下規(guī)則對機試成績進行反復加分，直到平均分超過85分為止。請編寫批處理語句實現(xiàn)。數(shù)據(jù)表：stuMarks(stuNo,ExamNo,writtenExam,labExam)90分以上：不加分80－89分：加1分70－79分：加2分60－69分：加3分60分以下：加5分

SELECT*FROMstuMarks--原始成績SELECT上機平均分=avg(labExam)FROMstuMarks--原始平均分DECLARE@labAvgINTWHILE(1=1)BEGINUPDATEstuMarks