第7章2TSQL程序設(shè)計基礎(chǔ)

第7章T-SQL程序設(shè)計基礎(chǔ)案例--乘車卡消費問題你的乘車卡上有50.3元人民幣，當余額低于5元時，顯示友好提示信息“金額低于5元,請盡快充值!”，當余額低于0.9元時，顯示友情提示“余額不足，請投幣！”。該案例中包含了變量及計算等問題，那么如何實現(xiàn)呢？DECLARE@iINT,@jeDECIMAL(5,1)SET@i=0SET@je=50.3WHILE(@je>=0.9)BEGINSET@je=@je-0.9SET@i=@i+1IF(@je<5)PRINT'金額低于5元,請允值!'ENDSELECT@jeAS余額,@iAS消費次數(shù),@i*0.9AS消費額PRINT'金額不足,請投幣!'本節(jié)教學(xué)重點及要求掌握流程控制語句（包括：BEGIN…END,IF…ELSE,WHILE…BREAK…CONTINUE,WAITFOR,CASE等）。掌握游標的概念和聲明方法，以及使用游標進行數(shù)據(jù)的查詢、修改、刪除等操作。能夠根據(jù)實際需要，進行綜合運用編寫程序代碼。7.4T-SQL流程控制語句流程控制語句是指那些用來控制程序執(zhí)行和流程分支的命令。在SQLServer2005中，流程控制語句主要用來控制SQL語句、語句塊或者存儲過程的執(zhí)行流程。注釋符:語句塊：begin…end條件語句：if…else、ifexists循環(huán)語句：while、break、continue等待語句：waitfor返回語句：return轉(zhuǎn)移語句：goto（了解）1.注釋符

注釋語句:程序中不被執(zhí)行的正文，它有二個作用：說明代碼的含義，增強代碼的可讀性；可以把程序中暫時不用的語句注釋掉，使它們暫時不被執(zhí)行，等需要這些語句時再將它們恢復(fù)。有兩種方法來聲明注釋：--（兩個減號）：用于注釋單行。從此開始的整行或者行的一部分就成為了注釋，注釋在行的末尾結(jié)束。/*……*/：可以注釋大塊跨越多行的代碼，它必須用一對分隔符“/**/”將余下的其他代碼分隔開。補例1單行注釋--Thisisacomment.Wholelinewillbeignored.SELECTemployee_name,address--查詢所有姓錢的員工FROMemployeeWHEREemployee_nameLIKE'錢%'補例2多行注釋/*Thisisacommnet．Alltheselineswillbeignored.*//*Listallemployees.*/SELECT*FROMemployee注釋并沒有長度限制。SQLServer文檔禁止嵌套多行注釋，但單行注釋可以嵌套在多行注釋中。/*--Listallemployees.SELECT*FROMemployee*/2.BEGIN…END語句語法形式：BEGIN{sql_statement|statement_block}END作用可以將多條T-SQL語句封裝起來，構(gòu)成一個獨立的語句塊。Begin和end必須成對出現(xiàn)。10控制語句順序執(zhí)行程序控制語句順序執(zhí)行流程改變程序3.IF…ELSE語句語法形式：IFBoolean_expression/*條件表達式*/{sql_statement/state_block}/*條件表達式為TRUE時執(zhí)行*/ELSE/*條件表達式為FALSE時執(zhí)行*/{sql_statement/state_block}作用：按條件控制程序的執(zhí)行。當條件滿足時，則在執(zhí)行條件之后的T-SQL語句。否則，就執(zhí)行else后的T-SQL語句（若ELSE部分存在）3.IF…ELSE語句【例7-6】根據(jù)course_score表的成績狀況來判斷：如果1210024課程的平均成績大于90分，顯示“1210024課程成績優(yōu)秀”；若大于80分，顯示“1210024課程成績良好”；否則顯示“1210024課程及格”。DECLARE@vagintSELECT@vag=AVG(score)FROMcourse_scoreWHEREcourse_score.cour_id='1210024'PRINT'1210024課程平均成績?yōu)?PRINT@vagIF@vag>90PRINT'1210024課程成績優(yōu)秀'ELSEIF@vag>80PRINT'1210024課程成績良好'ELSEPRINT'1210024課程成績及格'補例3：求兩個整數(shù)的最大數(shù)。DECLARE@maxINT,@aINT,@bINTSELECT@a=32,@b=56IF(@a>@b)SELECT@max=@aELSESELECT@max=@bSELECT@maxAS最大數(shù)4.CASE函數(shù)作用：可以計算多個條件式，并將其中一個符合條件的結(jié)果表達式返回。分類：按照使用形式的不同，可以分為簡單CASE函數(shù)和搜索CASE函數(shù)。(1)簡單CASE函數(shù)將某個表達式與一組簡單表達式進行比較以確定結(jié)果。其語法形式為：CASEinput_expression

WHENwhen_expressionTHENresult_expression

[...n]

[ELSEelse_result_expression]ENDUSEjxglGOSELECTCASEcour_nameWHEN'大學(xué)語文'THEN'中文系教師講授'WHEN'馬克思主義基本原理'THEN'政治歷史系教師講授'WHEN'中國近現(xiàn)代史綱要'THEN'政治歷史系教師講授'WHEN'大學(xué)英語'THEN'外國語系教師講授'WHEN'線性代數(shù)'THEN'數(shù)學(xué)系教師講授'ELSE'本系教師講授'ENDAS'開課教師',cour_nameFROMcoursesGO【例7-7】根據(jù)cour_name列，判斷course表的各個課程由哪系的老師講授。(2)CASE搜索函數(shù)，CASE計算一組邏輯表達式以確定結(jié)果。語法：CASEWHENBoolean_expressionTHENresult_expression[...n][ELSEelse_result_expression]END注意事項：（1）按指定順序?qū)γ總€WHEN子句的邏輯表達式進行計算。返回第一個計算結(jié)果為TRUE的

result_expression。（2）如果邏輯表達式計算結(jié)果不為TRUE，則在指定ELSE子句的情況下數(shù)據(jù)庫引擎將返回else_result_expression；若沒有指定ELSE子句，則返回NULL值。USEjxglGOSELECTcour_nameCASEWHENsemesterIN(1,2)THEN'一年級開設(shè)'WHENsemesterIN(3,4)THEN'二年級開設(shè)'WHENsemesterIN(5,6)THEN'三年級開設(shè)'ELSE'四年級開設(shè)'ENDAS'開課學(xué)期'FROMcourses【例7-8】在courses表中根據(jù)semester列，判斷courses表的各個課程分別在幾年級開設(shè)，使之更易理解。USEjxglGOUPDATEteachersSETteach_telephone=CASEWHENdept_id='D001'THEN'3785947'WHENdept_id='D001'THEN'3785940'WHENdept_id='D002'THEN'3785938'WHENdept_id='D004'THEN'3785926'ELSE'3786946'ENDGO【例7-9】在UPDATE語句中使用CASE函數(shù)：根據(jù)所在的系確定teachers表的電話號碼。5.WHILE…CONTINUE…BREAK循環(huán)控制語句

語法形式為：WHILEBoolean_expression

{sql_statement|statement_block}

[BREAK]

{sql_statement|statement_block}

[CONTINUE]作用：

用于設(shè)置重復(fù)執(zhí)行SQL語句或語句塊的條件。條件為真，就重復(fù)執(zhí)行語句。

說明：

Break用于退出最內(nèi)層的while循環(huán)。

Continue用于重新開始一次while循環(huán)。USEjxglGOWHILE(SELECTmin(year(getdate())-year(stu_birth))FROMstudents)<20BEGINIF(SELECTcount(*)FROMstudents)<20BREAKELSEBEGINDELETEstudentsWHEREstu_birth>=all(SELECTstu_birthFROMstudents)ENDEND【例7-10】查詢students表，只要有年齡小于20歲的學(xué)生，就將年齡最小的那個學(xué)生刪掉，如此循環(huán)下去，直到所有的學(xué)生的年齡都不小于20歲，或是學(xué)生的總?cè)藬?shù)小于20就退出循環(huán)。DECLARE@sint,@nint,@tint,@cintSET@S=0SET@n=1WHILE@n<=10BEGINSET@c=1SET@t=1WHILE@c<=@nBEGINSET@t=@t*@cSET@c=@c+1ENDSET@s=@s+@tSET@n=@n+1ENDSELECT@sas'10階乘之和',@nas'n'補例

計算s=1!+2!+…+10!。6.GOTO語句GOTO語句可以使程序直接跳到指定的標有標識符的位置處繼續(xù)執(zhí)行。標識符可以為數(shù)字與字符的組合，但必須以“：”結(jié)尾。語法格式是：GOTOlabel；DECLARE@sumint,@countintSELECT@sum=0,@count=1label_1:SELECT@sum=@sum+@countSELECT@count=@count+1IF@count<=100GOTOlabel_1SELECT@count,@sum操作結(jié)果如下面所示：

1015050(1行受影響)【例7-10】利用GOTO語句求出從1加到100的總和。7.調(diào)度執(zhí)行語句WAITFORWAITFOR語句用于暫時停止執(zhí)行SQL語句、語句塊或者存儲過程等，直到所設(shè)定的時間已過或者所設(shè)定的時間已到才繼續(xù)執(zhí)行。語法形式為：WAITFOR{DELAY'time'|TIME'time'}（1）DELAY：指示SQLServer一直等到指定的時間過去，最長可達24小時。（2）‘time’：要等待的時間?？梢园磀atetime數(shù)據(jù)可接受的格式指定time，也可以用局部變量指定此參數(shù)，但不能指定日期。（3）TIME：指示SQLServer等待到指定時間?！纠?-12】用WAITFOR語句設(shè)置延遲操作。waitfordelay'00:00:05'print'延遲5秒執(zhí)行！'--5秒后執(zhí)行print【例7-13】用WAITFOR語句指定從何時起執(zhí)行。

waitfortime'08:57'print'08:57執(zhí)行'--到08:57的時候才會執(zhí)行print8.RETURN語句語法格式：RETURN[integer_expression]作用:無條件地從過程、批或語句塊中退出，在return之后的其他語句不會被執(zhí)行。說明:Return可以在過程、批和語句塊中的任何位置使用。Return與break很相似，不同的是return可以返回一個整數(shù)。如果沒有指定返回值，SQL

Server系統(tǒng)會根據(jù)程序執(zhí)行的結(jié)果返回一個內(nèi)定值，返回值含義如下所示:返回值

含義返回值

含義0程序執(zhí)行成功-7資源錯誤-1找不到對象-8非致使的內(nèi)部錯誤-2數(shù)據(jù)類型錯誤-9已經(jīng)達到系統(tǒng)的權(quán)限-3死鎖-10、-11致使的內(nèi)部不一致錯誤-4違反權(quán)限原則-12表或指針破壞-5語法錯誤-13數(shù)據(jù)庫破壞-6用戶造成的一般錯誤-14硬件錯誤表7-4RETURN命令返回的內(nèi)定狀態(tài)值28介紹SQL批處理批處理單個SQL

命令組成一批作為單個

執(zhí)行計劃

進行編譯輸出結(jié)果9.批處理一個批處理是—條或多條Transact-SQL語句的集合。SQLServer服務(wù)器對批處理的處理分為四個階段：分析階段，檢查語法，驗證合法性;優(yōu)化階段，確定最有效的方法；編譯階段，生成執(zhí)行計劃；運行階段，執(zhí)行語句。批處理最重要的特征就是它作為一個不可分的實體在服務(wù)器上解釋和執(zhí)行。SQLServer有以下幾種指定批處理的方法。應(yīng)用程序。存儲過程或觸發(fā)器。由EXECUTE語句執(zhí)行的字符串是一個批處理，并編譯為一個執(zhí)行計劃。例如，EXEC('SELECT*FROMteachers')由sp_executesql系統(tǒng)存儲過程執(zhí)行的字符串是一個批處理，并編譯為一個執(zhí)行計劃。例如，executesp_executesqlN'SELECT*fromjxgl.dbo.teachers'（1）

批處理的指定GO是批處理的結(jié)束標志。把GO前面的所有語句當成一個批處理來執(zhí)行。GO命令和T-SQL語句不可處在同一行上。在批處理的第一條語句后執(zhí)行任何存儲過程必須包含EXECUTE關(guān)鍵字。局部(用戶定義)變量的作用域限制在一個批處理中，不可在GO命令后引用。RETURN可在任何時候從批處理中退出，而不執(zhí)行位于RETURN之后的語句。(2)批處理的結(jié)束與退出USEjxglGO

--批處理結(jié)束標志CREATEVIEWstudents_infoASSELECT*FROMstudentsGO--CREATEVIEW語句與其他語句隔離SELECT*FROMstudents_infoGO補例

創(chuàng)建一個視圖，使用GO命令將CREATEVIEW語句與批處理中的其他語句(如USE、SELECT語句等)隔離。去掉go？declare@iintselect@i=1while@i<500beginif@i%3=0or@i%7=0print@iset@i=@i+1end課堂練習(xí)1：計算500以內(nèi)能被3或7整除的數(shù)。本節(jié)小結(jié)流程控制語句概述begin…end語句if…else、ifexists語句while、break和continue語句waitfor語句return語句習(xí)題4、57.6游標游標(Cursor)是一種處理數(shù)據(jù)的方法，為了查看或者處理結(jié)果集中的數(shù)據(jù)，游標提供了在結(jié)果集中向前或者向后瀏覽數(shù)據(jù)的能力?？梢园延螛丝闯梢环N指針，可以指向結(jié)果集中的任意位置，它允許用戶對指定位置的數(shù)據(jù)進行處理，可以把結(jié)果集中的數(shù)據(jù)放在數(shù)組、應(yīng)用程序中或其它地方。游標把作為面向集合的數(shù)據(jù)庫管理系統(tǒng)和面向行的程序設(shè)計兩者聯(lián)系起來，才使兩個數(shù)據(jù)處理方式能夠進行溝通。7.6.1游標概述根據(jù)游標的用途不同，SQLServer2005將游標分成三種類型：1.Transact_SQL游標.主要用在服務(wù)器上，不支持提取數(shù)據(jù)塊或多行數(shù)據(jù)。2.API游標：支持在OLEDB，ODBC以及DB_library中使用游標函數(shù)，主要用在服務(wù)器上。3.客戶游標：當在客戶機上緩存結(jié)果集時才使用。常常僅被用作服務(wù)器游標的輔助。API游標和Transact-SQL游標被稱為服務(wù)器游標，也被稱為后臺游標，而客戶端游標被稱為前臺游標。在本節(jié)中我們主要講述服務(wù)器（后臺）游標。7.6.1游標概述根據(jù)Transact_SQL服務(wù)器游標的處理特性，SQLServer2005將游標分為4種：2.動態(tài)游標：與靜態(tài)游標相對。當滾動游標時，動態(tài)游標反映結(jié)果集中所做的所有更改。1.靜態(tài)游標：在打開游標時在tempdb中建立SELECT結(jié)果集的快照?？偸前凑沾蜷_游標時的原樣顯示結(jié)果集，并不反應(yīng)它在數(shù)據(jù)庫中對任何結(jié)果集成員所做的更新。3.只進游標：不支持滾動，它只支持游標從頭到尾順序提取數(shù)據(jù)，游標從數(shù)據(jù)庫中提取一條記錄并進行操作，操作完畢后，再提取下一條記錄。4.鍵集游標：該游標中各行的成員身份和順序是固定的。由一組唯一標識符（鍵）控制，這組鍵稱為鍵集。鍵集是打開游標時來自符合SELECT語句要求的所有行中的一組鍵值。鍵集驅(qū)動的游標對應(yīng)的鍵集是打開該游標時在數(shù)據(jù)庫tempdb中生成的。7.6.2游標的定義與使用使用游標(CURSOR)的步驟如下。聲明游標。在使用游標之前，首先需要聲明游標。打開游標。打開一個游標意味著在游標中輸入了相關(guān)的記錄信息。獲取記錄信息。首先將游標當前指向的記錄保存到一個局部變量中，然后游標將自動移向下一條記錄。將一條記錄讀入某個局部變量后，就可以根據(jù)需要對其進行處理了。關(guān)閉游標。釋放游標鎖定的記錄集。釋放游標。釋放游標自身所占用的資源。1.聲明游標（DECLARE）：在內(nèi)存中創(chuàng)建游標結(jié)構(gòu)，是游標語句的核心。

語法格式：Declarecursor_name/*指定游標名*/[insensitive][scroll]cursor/*指定游標類型,insensitive表示靜態(tài)游標*/FORSELECT_statement/*指定查詢語句*/[for{readonly|update[ofcolumn_name[,…n]]}]表7-10Declare命令中SCROLL的取值SCROLL選項含義FIRST提取游標中的第一行數(shù)據(jù)LAST提取游標中的最后一行數(shù)據(jù)PRIOR提取游標當前位置的上一行數(shù)據(jù)NEXT提取游標當前位置的下一行數(shù)據(jù)RELATIVEn提取游標當前位置之前或之后的第n行數(shù)據(jù)(n正，往下，n負，往上)ABSULUTEn提取游標中的第n行數(shù)據(jù)【例7-32】定義游標Teach_CUR，以便查詢教師名字和所講授的課程名，學(xué)分。

DECLARETeach_CURCURSORFORSELECTteachers.teach_name,courses.cour_name,courses.creditFROMteachers,courses,course_arrangeWHEREteachers.teach_Id=course_arrange.teach_idandcourse_arrange.cour_Id=courses.cour_Id2.打開游標（OPEN），產(chǎn)生游標定義的結(jié)果集。語句格式：OPEN{{[GLOBAL]cursor_name}|@cursor_variable_name}其中：GLOBAL選項指定cursor_name為全局游標；cursor_name為游標名稱；@cursor_variable_name為游標變量名稱，該變量引用一個游標。注意：①當游標打開成功時，游標位置指向結(jié)果集的第一行之前。②只能打開已經(jīng)聲明但尚未打開的游標?！纠?-33】打開前面所創(chuàng)建的游標Teach_CUR。OPENTeach_CUR【例7-34】顯示游標結(jié)果集合中數(shù)據(jù)行數(shù)

SELECT數(shù)據(jù)行數(shù)=@@CURSOR_ROWS3.讀取游標一旦游標被成功打開，就可以從游標中逐行地讀取數(shù)據(jù)，以進行相關(guān)處理。從游標中讀取數(shù)據(jù)主要使用FETCH命令。其語法格式為：FETCH[[NEXT|PRIOR|FIRST|LAST|ABSOLUTE{n|@nvar}|RELATIVE{n|@nvar}]FROM]{{[GLOBAL]cursor_name}|cursor_variable_name}[INTO@variable_name[,...n]]表9-14@@fetch_status變量返回值描述0FETCH命令已成功執(zhí)行-1FETCH命令失敗或者行數(shù)據(jù)己超出了結(jié)果集-2所讀取的數(shù)據(jù)已經(jīng)不存在。注意：游標位置決定了結(jié)果集中哪一行的數(shù)據(jù)可以被提取，如果游標方式為FORUPDATE，則可決定哪一行數(shù)據(jù)庫可以更新或者刪除。@@FETCH_STATUS變量報告上一個FETCH語句的狀態(tài)，其取值和含義如下：OPENTeach_CURFETCHNEXTFROMTeach_CUR

/*取第一個數(shù)據(jù)行*/WHILE@@fetch_status=0/*檢查@@fetch_status是否還有數(shù)據(jù)可取*/BEGINFETCHNEXTFROMTeach_CUREND【例7-35】打開前面所創(chuàng)建的游標Teach_CUR，讀取游標中的數(shù)據(jù)。4.關(guān)閉游標（CLOSE）

格式為：CLOSE游標名

例如：關(guān)閉Teach_CUR游標如下描述：

CLOSETeach_CUR關(guān)閉已打開的游標，之后不能對游標進行讀取等操作，但可以使用OPEN語句再次打開該游標。5.刪除游標（DEALLOCATE）。DEALLOCATE語句格式：DEALLOCATE游標名作用：DEALLOCATE命令刪除游標與游標名或游標變量之間的聯(lián)系，并且釋放游標占用的所有系統(tǒng)資源。刪除后不可再用，必須重新聲明方可。例如，刪除Teach_CUR游標，DEALLOCATETeach_CUR6.利用游標修改數(shù)據(jù)。通過游標修改或刪除游標基表中的當前數(shù)據(jù)行。UPDATE語句的格式為：UPDATEtable_nameSET列名=表達式}[,…n]WHERECURRENTOFcursor_nameDELETE語句的格式為：DELETEFROMtable_nameWHERECURRENTOFcursor_name說明：CURRENTOFcursor_name：表示當前游標指針所指的當前行數(shù)據(jù)。CURRENTOF只能在UPDATE和DELETE語句中使用。注意：使用游標修改基表數(shù)據(jù)的前提是聲明的游標是可更新的。對相應(yīng)的數(shù)據(jù)庫對象（游標的基表）有修改和刪除權(quán)限?！纠?-39】通過游標將教師表teachers記錄號為3的“呂加國”老師的職稱由“講師’改為“副教授”。USEjxglGODECLARETEACH_CURSOR2SCROLLCURSORFORSELECTteach_name,teach_professionalFROMteachersFORUPDATEOFteach_professionalOPENTEACH_CURSOR2FETCHABSOLUTE3FROMTEACH_CURSOR2UPDATEteachersSETteach_professional='副教授'WHERECURRENTOFTEACH_CURSOR2SELECTteach_name,teach_professionalFROMteacherswhereteach_name='呂加國'CLOSETEACH_CURSOR2DEALLOCATETEACH_CURSOR27.6.3使用存儲過程管理游標可以使用Sp_cursor_list系統(tǒng)存儲過程來獲得對當前連接可見的游標列表，使用Sp_describe_cursor、Sp_describe_cursor_columns和Sp_describe_cursor_tables來確定游標的特性。1.Sp_describe_cursor：返回描述游標屬性（例如游標的作用域、名稱、類型、狀態(tài)和行數(shù)）的游標。

2.Sp_describe_cursor_columns：返回描述游標中每一列的屬性（例如列的名稱、位置、大小和數(shù)據(jù)類型）的游標。3.Sp_describe_cursor_tables：報告游標