第9章 T-SQL程序設(shè)計(jì)

第9章T-SQL程序設(shè)計(jì)本章概述本章的學(xué)習(xí)目標(biāo)主要內(nèi)容1概述1986年，美國國家標(biāo)準(zhǔn)化組織正式發(fā)表了編號為X3.135-1986的SQL標(biāo)準(zhǔn)，并且在1987年獲得了國際標(biāo)準(zhǔn)化組織（ISO）組織的認(rèn)可，被命名為ISO9075-1987。后來這個(gè)標(biāo)準(zhǔn)在1992、1999、2001、2003年等不斷地得到了擴(kuò)充和完善。2Transact-SQL語言Transact-SQL語言是微軟公司在MSSQLServer系統(tǒng)中使用的語言，是對SQL語言的一種擴(kuò)展形式(下文簡稱T-SQL語言)。T-SQL語言是一種交互式查詢語言，具有功能強(qiáng)大、簡單易學(xué)的特點(diǎn)。該語言既允許用戶直接查詢存儲在數(shù)據(jù)庫中的數(shù)據(jù)，也可以把語句嵌入到某種高級程序設(shè)計(jì)語言中來使用3T-SQL語言T-SQL語言有4個(gè)特點(diǎn)：一體化：集數(shù)據(jù)定義語言、數(shù)據(jù)操縱語言、數(shù)據(jù)控制語言、事務(wù)管理語言和附加語言元素為一體兩種使用方式：交互使用方式和嵌入高級語言中使用。非過程化：只需要提出“干什么”，不需要指出“如何干”，語句的操作過程由系統(tǒng)自動完成。類似于人的思維習(xí)慣，容易理解和掌握。

4T-SQL語言的特點(diǎn)和執(zhí)行方式

根據(jù)T-SQL語言的功能特點(diǎn)，可以分為5種類型數(shù)據(jù)定義語言數(shù)據(jù)操縱語言數(shù)據(jù)控制語言事務(wù)管理語言附加的語言元素SQLServer2005中，主要在SSMS中執(zhí)行T-SQL語言編寫的查詢語句。5數(shù)據(jù)定義語言數(shù)據(jù)定義語言(DataDescriptionLanguage—DDL)用于創(chuàng)建數(shù)據(jù)庫和數(shù)據(jù)庫對象，為數(shù)據(jù)庫操作提供對象。例如，數(shù)據(jù)庫以及表、觸發(fā)器、存儲過程、視圖、索引、函數(shù)、類型、用戶等都是數(shù)據(jù)庫中的對象，都需要通過定義才能使用。在DDL中，主要的語句包括CREATE語句、ALTER語句、DROP語句。6CREATE語句：用于創(chuàng)建數(shù)據(jù)庫以及數(shù)據(jù)庫中的對象，例在數(shù)據(jù)庫中創(chuàng)建表ALTER語句：用于更改數(shù)據(jù)庫以及數(shù)據(jù)庫對象的結(jié)構(gòu)。ALTER語句的對象必須已經(jīng)存在。例如，對于表對象來說，在表中增加一個(gè)新列、刪除一個(gè)列等操作都屬于對表結(jié)構(gòu)的更改。DROP語句：刪除數(shù)據(jù)庫或數(shù)據(jù)庫對象的結(jié)構(gòu)。刪除對象結(jié)構(gòu)包括刪除該對象中的所有內(nèi)容和對象本身。7數(shù)據(jù)操縱語言數(shù)據(jù)操作語言DML(DataManipulationLanguage)，通過它可以實(shí)現(xiàn)對數(shù)據(jù)庫的基本操作。如對表中數(shù)據(jù)的查詢、插入、刪除和修改DML語言包括INSERT、SELECT、UPDATE及DELETE等語句，即通常說的增、刪、改、查。8數(shù)據(jù)操縱語言創(chuàng)建表對象之后，初始狀態(tài)時(shí)該表是空的，需要使用INSERT語句向其中輸入數(shù)據(jù)。使用SELECT語句檢索表中數(shù)據(jù)如果表中數(shù)據(jù)不正確，可用UPDATE語句進(jìn)行更新也可以使用DELETE語句刪除表中的數(shù)據(jù)。注意：DELETE語句與DROP語句不同DELETE語句刪除表中的數(shù)據(jù)，但是該表對象依然存在。DROP語句則刪除了表對象，表中的數(shù)據(jù)自然也不存在了。9數(shù)據(jù)控制語言數(shù)據(jù)控制語言(DCL)主要用來執(zhí)行有關(guān)安全管理的操作，該語言主要包括GRANT語句、REVOKE語句和DENY語句。GRANT語句可以將指定的安全對象的權(quán)限授予相應(yīng)的主體REVOKE語句則刪除授予的權(quán)限D(zhuǎn)ENY語句拒絕授予主體權(quán)限，并且防止主體通過組或角色成員繼承權(quán)限。109.1.1用戶定義數(shù)據(jù)類型用戶自定義數(shù)據(jù)類型是在基本數(shù)據(jù)的基礎(chǔ)上根據(jù)需要定義的數(shù)據(jù)類型。它提供了一種加強(qiáng)數(shù)據(jù)庫內(nèi)部元素和基本數(shù)據(jù)類型之間一致性的機(jī)制，用于簡化對常用規(guī)則和默認(rèn)值的管理。當(dāng)多個(gè)表的列中要存儲同類型的數(shù)據(jù)，同時(shí)確保這些列具有完全相同的數(shù)據(jù)類型、長度、是否為空值時(shí)，可使用用戶自定義數(shù)據(jù)類型。創(chuàng)建了別名數(shù)據(jù)類型之后，可以在CREATETABLE或ALTERTABLE中使用它，也可以將默認(rèn)值和規(guī)則綁定到別名數(shù)據(jù)類型。11創(chuàng)建別名數(shù)據(jù)類型語法sp_addtype[@typename=]type,

[@phystype=]system_data_type

[,[@nulltype=]'null_type'];[@typename=]type:別名數(shù)據(jù)類型的名稱[@phystype=]system_data_type：別名數(shù)據(jù)類型所基于的物理數(shù)據(jù)類型或SQLServer提供的數(shù)據(jù)類型。12創(chuàng)建別名數(shù)據(jù)類型[@nulltype=]'null_type'指示別名數(shù)據(jù)類型處理空值的方式。null_type

的數(shù)據(jù)類型為varchar(8)，默認(rèn)值為NULL，并且必須用單引號引起來（'NULL'、'NOTNULL'或'NONULL'）。139.1.2常量與變量常量，是表示一個(gè)特定數(shù)據(jù)值的符號。常量的格式取決于它所表示的值的數(shù)據(jù)類型。字符串常量字符串常量括在單引號內(nèi)并包含字母數(shù)字字符（a-z、A-Z和0-9）以及特殊字符，如感嘆號(!)、at符(@)和數(shù)字號(#)。如果單引號中的字符串包含一個(gè)嵌入的引號，可以使用兩個(gè)單引號表示嵌入的單引號。'Cincinnati'、

'O''Brien'149.1.2常量與變量二進(jìn)制常量：具有前輟0x并且是十六進(jìn)制數(shù)字字符串。這些常量不使用引號括起。0xAE、0x12Ef

datetime

常量：使用特定格式的字符日期值來表示，并被單引號括起來。'April15,1998''980415''04/15/98'時(shí)間常量的示例：'14:30:24''04:24PM'無日期的時(shí)間值，其日期定義為1900/1/115數(shù)值常量integer常量以沒有用引號括起來并且不包含小數(shù)點(diǎn)的數(shù)字字符串來表示。decimal常量由沒有用引號括起來并且包含小數(shù)點(diǎn)的數(shù)字字符串來表示float和real常量使用科學(xué)記數(shù)法來表示16數(shù)值常量money常量以前綴為可選的小數(shù)點(diǎn)和可選的貨幣符號的數(shù)字字符串來表示，不使用引號括起。$12$542023.14uniqueidentifier

常量uniqueidentifier

常量是表示GUID的字符串。可以使用字符或二進(jìn)制字符串格式指定。17變量SQLServer2005系統(tǒng)中，變量也被稱為局部變量，是可以保存單個(gè)特定類型數(shù)據(jù)值的對象經(jīng)常在批處理和腳本中使用變量，用做作為計(jì)數(shù)器計(jì)算循環(huán)執(zhí)行的次數(shù)或控制循環(huán)執(zhí)行的次數(shù)保存數(shù)據(jù)值以供控制流語句測試保存存儲過程返回代碼要返回的數(shù)據(jù)值或函數(shù)返回值18變量T-SQL語言中，使用DECLARE語句聲明變量聲明過程為變量指定名稱，且名稱的第一個(gè)字符必須是@指定該變量的數(shù)據(jù)類型和長度將該變量值設(shè)置為NULL有兩種為變量賦值的方式，即使用SET語句為變量賦值和使用SELECT語句選擇列表中當(dāng)前所引用值來為變量賦值。19局部變量的賦值局部變量的值使用SELECT、UPDATE和SET語句進(jìn)行賦值DECLARE@int_var

intSELECT@int_var=12/*給@int_var賦值*/SELECT@int_var/*輸出到屏幕上*/在未賦值前，局部變量的值為null20局部變量的賦值在一條語句中可以同時(shí)對幾個(gè)變量進(jìn)行賦值，例如，DECLARE@LastNamechar(8),@Firstnamechar(8),@BirthDatedatetimeSELECT@LastName='Smith',@Firstname='David',@BirthDate='1985-2-20'SELECT@LastName,@Firstname,@BirthDate21局部變量的賦值例9-1使用SELECT語句從customer表中檢索出顧客編號為C0002的行，再將顧客的名字賦給變量@customer。DECLARE@customervarchar(40),@curdatedatetimeSELECT@customer=customer_name,@curdate=getdate()FROMcustomerWHEREcustomer_id='C0002'22局部變量的賦值如果SELECT返回多個(gè)結(jié)果，則只將返回的最后一行的值賦給局部變量若檢索結(jié)果為空，則局部變量的值不變23使用SET為局部變量賦值例9-3計(jì)算employee表的記錄數(shù)并賦值給局部變量@rows。DECLARE@rowsintSET@rows=(SELECTCOUNT(*)FROMemployee)SELECT@rowsSET一次只能為一個(gè)局部變量進(jìn)行賦值24全局變量某些Transact-SQL系統(tǒng)函數(shù)的名稱以兩個(gè)at符號(@@)打頭。在MicrosoftSQLServer的早期版本中，@@functions被稱為全局變量，但它們不是變量，也不具備變量的行為。@@functions是系統(tǒng)函數(shù)，它們的語法遵循函數(shù)的規(guī)則。25@@ERROR如最后的T-SQL語句執(zhí)行成功，@@ERROR系統(tǒng)函數(shù)返回0；如此語句產(chǎn)生錯(cuò)誤，@@ERROR返回錯(cuò)誤號每一個(gè)T-SQL語句完成時(shí)，@@ERROR都會得到一個(gè)新的值@@ERROR可用以下兩種方法處理：在T-SQL語句后，馬上檢測或使用@@ERROR在T-SQL語句完成后，馬上把@@ERROR存儲到一個(gè)整型變量中。此變量的值可供以后使用26@@ERRORSELECT*FROMnot_found_tableGOSELECT@@ERRORASN'第一個(gè)錯(cuò)誤號'GOSELECT5+6ASN'兩個(gè)數(shù)之和'GOSELECT@@ERRORASN'第二個(gè)錯(cuò)誤號'GO27練習(xí)：使用@@ERROR首先試圖以0作除數(shù)，這會導(dǎo)致錯(cuò)誤SELECT100/0SELECT@@ERRORSELECT@@ERROR查看“結(jié)果”和“消息”兩個(gè)選項(xiàng)卡的內(nèi)容。這個(gè)例子說明了@@ERROR變量值的生命周期28例9-4使服務(wù)器產(chǎn)生服務(wù)，并顯示錯(cuò)誤號。raiserror('miscellaneouserrormessage',16,1)/*產(chǎn)生一個(gè)錯(cuò)誤*/if@@error<>0SELECT@@erroras'lasterror'29例9-5捕捉例9-4中服務(wù)器產(chǎn)生的錯(cuò)誤號，并顯示出來。DECLARE@my_error

intRAISERROR('miscellaneouserrormessage',16,1)SELECT@my_error=@@errorIF@my_error<>0SELECT@my_erroras'lasterror'309.1.3運(yùn)算符與表達(dá)式運(yùn)算符是一種符號，用來指定要在一個(gè)或多個(gè)表達(dá)式中執(zhí)行的操作。在T-SQL語言中，表達(dá)式是標(biāo)識符、變量、常量、標(biāo)量函數(shù)、子查詢、運(yùn)算符等的組合。SQLServer2005系統(tǒng)中，表達(dá)式可以在多個(gè)不同的位置使用，這些位置包括查詢中檢索數(shù)據(jù)的一部分、搜索數(shù)據(jù)的條件等。簡單表達(dá)式：一個(gè)變量、常量、列名或標(biāo)量函數(shù)復(fù)雜表達(dá)式：由兩個(gè)或更多個(gè)簡單表達(dá)式通過使用運(yùn)算符連接起來的表達(dá)式。319.1.3運(yùn)算符與表達(dá)式在SQLServer2005系統(tǒng)中，可使用下列運(yùn)算符：算術(shù)運(yùn)算符邏輯運(yùn)算符賦值運(yùn)算符字符串串聯(lián)運(yùn)算符按位運(yùn)算符比較運(yùn)算符32運(yùn)算符算術(shù)運(yùn)算符

+，-，*，/，%（模余）

SELECT12.0/5.0,12/5,12.0/15.0,12/15,12%7例9-9使用“+”將goods表中高于9000元的商品價(jià)格增加15元。SELECTgoods_name,unit_price,(unit_price+15)ASnowpriceFROMgoodsWHEREunit_price>900033賦值運(yùn)算符T-SQL中只有一個(gè)賦值運(yùn)算符，即（=）。賦值運(yùn)算符使我們能夠?qū)?shù)據(jù)值指派給特定的對象還可以使用賦值運(yùn)算符改變查詢結(jié)果中顯示的列標(biāo)題DECLARE@EnameVARCHAR(128)SET@Ename='Cleon'GOSELECTFIRSTNAME=FirstName,LASTNAME=LastNameFROMContact34位運(yùn)算符位運(yùn)算符能夠在整型數(shù)據(jù)或者二進(jìn)制數(shù)據(jù)（image數(shù)據(jù)類型除外）之間執(zhí)行位操作。運(yùn)算符含義&（按位

AND）按位

AND（兩個(gè)操作數(shù)）|（按位

OR）按位

OR（兩個(gè)操作數(shù)）^（按位異或XOR）按位異或XOR（兩個(gè)操作數(shù)）35位運(yùn)算符20：00010100，12：0000110020&12000101000000110000000100——420|12000101000000110000011100——2836位運(yùn)算符20：00010100，12：0000110020^12000101000000110000011000——24SELECT20&12,20|12,20^1237比較運(yùn)算符比較運(yùn)算符亦稱為關(guān)系運(yùn)算符，用于比較兩個(gè)表達(dá)式的大小或是否相同，其比較的結(jié)果是布爾值，即TRUE、FALSE以及UNKNOWN。除了text，ntext

或image數(shù)據(jù)類型的表達(dá)式外，比較運(yùn)算符可以用于所有的表達(dá)式。=、>、<、>=、<=、<>、!=、!<、!>38邏輯運(yùn)算符p177USEAdventureWorksSELECT*FROMContactWHEREcontactID<10ANDTitle='Mr.'OR

FirstName='Tom'查找合同編碼小于10且稱謂為Mr.，或者FirstName為Tom的紀(jì)錄WHERE(contactID<10ANDTitle='Mr.')

OR

FirstName='Tom'39運(yùn)算符含義ALL如果一組的比較都為TRUE，那么就為TRUE。AND如果兩個(gè)布爾表達(dá)式都為TRUE，那么就為TRUE。ANY如果一組的比較中任何一個(gè)為TRUE，那么就為TRUE。BETWEEN如果操作數(shù)在某個(gè)范圍之內(nèi)，那么就為TRUE。EXISTS如果子查詢包含一些行，那么就為TRUE。IN如果操作數(shù)等于表達(dá)式列表中的一個(gè)，那么就為TRUE。LIKE如果操作數(shù)與一種模式相匹配，那么就為TRUE。NOT對任何其他布爾運(yùn)算符的值取反。OR如果兩個(gè)布爾表達(dá)式中的一個(gè)為TRUE，那么就為TRUE。SOME如果在一組比較中，有些為TRUE，那么就為TRUE。40字符串串聯(lián)運(yùn)算符+p178USEAdventureWorksSELECTFullInfo=Title+''+FirstName+''+LastName+',Telephonenumberis'+PhoneFROMperson.Contact41SQL中邏輯運(yùn)算的優(yōu)先級由低到高排列()~^&|*,/,%+,-Not->And->Or429.2函數(shù)在T-SQL語言中，函數(shù)被用來執(zhí)行一些特殊的運(yùn)算以支持SQLServer的標(biāo)準(zhǔn)命令。SQLServer包含多種不同的函數(shù)用以完成各種工作，每一個(gè)函數(shù)都有一個(gè)名稱，在名稱之后有一對小括號，如：gettime()。大部分的函數(shù)在小括號中需要一個(gè)或者多個(gè)參數(shù)。43函數(shù)的特點(diǎn)和類型函數(shù)類別描述聚合函數(shù)將多個(gè)數(shù)值合并為一個(gè)數(shù)值，例如計(jì)算合計(jì)值配置函數(shù)返回當(dāng)前配置選項(xiàng)配置的信息加密函數(shù)支持加密、解密、數(shù)字簽名和數(shù)字簽名驗(yàn)證游標(biāo)函數(shù)返回有關(guān)游標(biāo)狀態(tài)的信息日期和時(shí)間函數(shù)可以執(zhí)行與日期、時(shí)間數(shù)據(jù)相關(guān)的操作數(shù)學(xué)函數(shù)執(zhí)行對數(shù)、指數(shù)、三角函數(shù)、平方根等數(shù)學(xué)運(yùn)算元數(shù)據(jù)函數(shù)用于返回?cái)?shù)據(jù)庫和數(shù)據(jù)庫對象的屬性信息44函數(shù)的特點(diǎn)和類型函數(shù)類別描述排名函數(shù)可返回分區(qū)中的每一行的排名值行集函數(shù)可返回一可用于代替T-SQL語句中表引用的對象安全函數(shù)返回有關(guān)用戶和角色的信息字符串函數(shù)可對字符數(shù)據(jù)執(zhí)行替換、截?cái)唷⒑喜⒌炔僮飨到y(tǒng)函數(shù)對系統(tǒng)級的各種選項(xiàng)和對象進(jìn)行操作或報(bào)告系統(tǒng)統(tǒng)計(jì)函數(shù)返回有關(guān)SQLServer系統(tǒng)性能統(tǒng)計(jì)的信息文本和圖像函數(shù)用于執(zhí)行更改TEXT和IMAGE值的操作45字符串函數(shù)USEAdventureWorksSELECTFirstName,N'名稱長度'=LEN(FirstName),N'名稱前3個(gè)字母的大寫形式'=UPPER(SUBSTRING(FirstName,1,3)),N'名稱復(fù)制3遍'=REPLICATE(FirstName,3)FROMPerson.Contact要求掌握：substring,left,right46數(shù)學(xué)函數(shù)p181SELECTN'自然對數(shù)'=LOG(10),N'指數(shù)'=EXP(12),N'符號'=SIGN(2),N'圓周率'=PI(),N'正弦'=SIN(PI()/2.0),N'余弦'=COS(PI()/2.0)47數(shù)學(xué)函數(shù)例9-12用ceiling和floor函數(shù)返回大于或等于指定值的最小整數(shù)值和小于或等于指定值的最大整數(shù)值。SELECTceiling(123),floor(321),ceiling(12.3),ceiling(-32.1),floor(-32.1)例9-13round函數(shù)的使用。SELECTround(12.34512,3),round(12.34567,3),round(12.345,-2),round(154.321,-2)48日期和時(shí)間函數(shù)p182日期和時(shí)間函數(shù)用于對日期和時(shí)間數(shù)據(jù)進(jìn)行各種不同的處理和運(yùn)算，并返回一個(gè)字符串、數(shù)字值或日期和時(shí)間值。與其他函數(shù)一樣，可以在SELECT語句的SELECT和WHERE子句以及表達(dá)式中使用日期和時(shí)間函數(shù)。49日期和時(shí)間函數(shù)函數(shù)參數(shù)功能DATEADD(datepart,number,date)以datepart指定的方式，返回date加上number之和DATEDIFF(datepart,date1,date2)以datepart指定的方式，返回date2與date1之差DATENAME(datepart,date)返回日期date中datepart指定部分所對應(yīng)的字符串DATEPART(datepart,date)返回日期date中datepart指定部分所對應(yīng)的整數(shù)值50日期和時(shí)間函數(shù)SELECTGETDATE()函數(shù)參數(shù)功能DAY（date）返回指定日期的天數(shù)GETDATE（）返回當(dāng)前的日期和時(shí)間MONTH（date）返回指定日期的月份數(shù)YEAR（date）返回指定日期的年份數(shù)51例9-14使用datediff函數(shù)來確定貨物是否按時(shí)送給客戶。SELECTgoods_id,datediff(dd,send_date,arrival_date)FROMpurchase_order系統(tǒng)函數(shù)不要求p183529.2.2用戶定義函數(shù)用戶定義函數(shù)是接受參數(shù)、執(zhí)行操作并且將運(yùn)算結(jié)果以值的形式返回的例程。返回值可以是單個(gè)標(biāo)量值，也可以是一個(gè)結(jié)果集SQLServer2005中，使用CREATEFUNCTION、ALTERFUNCTION、DROPFUNCTION語句來實(shí)現(xiàn)用戶定義函數(shù)的創(chuàng)建、修改和刪除。53使用CREATEFUNCTION語句CREATEFUNCTION[所有者名稱.]函數(shù)名

[({@參數(shù)名稱[AS]數(shù)據(jù)類型[=默認(rèn)值]}[,…n])]

RETURNS返回值類型

[AS]

BEGIN

函數(shù)體SQL語句

RETURN數(shù)值表達(dá)式

END54使用CREATEFUNCTION語句SQLServer2005中，使用CREATEFUNCTION語句可以創(chuàng)建標(biāo)量函數(shù)、內(nèi)嵌表值函數(shù)、多語句表值函數(shù)如果RETURNS子句指定了一種標(biāo)量數(shù)據(jù)類型，則該函數(shù)為標(biāo)量函數(shù)如果RETURNS子句指定了TABLE，則該函數(shù)為表值函數(shù)。根據(jù)函數(shù)主體的定義方式，表值函數(shù)可以分為內(nèi)嵌函數(shù)或多語句函數(shù)。55標(biāo)量值型自定義函數(shù)標(biāo)量值型自定義函數(shù)的最大特點(diǎn)是返回單個(gè)值，即標(biāo)量值?？梢栽谠试S使用標(biāo)量表達(dá)式（scalarexpression）的地方使用標(biāo)量UDF。例如查詢、約束、計(jì)算列等。必須包含BEGIN/END塊以定義函數(shù)的主體調(diào)用時(shí)必須限定架構(gòu)調(diào)用時(shí)不允許忽略可選參數(shù)；或者為它們指定DEFAULT關(guān)鍵字56標(biāo)量值型自定義函數(shù)例9-18創(chuàng)建函數(shù)DatetoQuarter，輸入日期數(shù)據(jù)，輸出對應(yīng)的季度值。如輸入'2006-8-5'，返回'3Q2006'，表示2006年3季度。CREATEFUNCTION

DatetoQuarter(@dqdate

datetime)RETURNSchar(6)ASBEGIN

RETURN(datename(q,@dqdate)+'Q'+datename(yyyy,@dqdate))END57表值型自定義函數(shù)返回值為數(shù)據(jù)表的自定義函數(shù)，這類自定義函數(shù)通常被用于一條查詢語句的FROM子句中。可以在任何使用一個(gè)數(shù)據(jù)表(或視圖)的地方使用一個(gè)表值型自定義函數(shù)，由于表值型自定義函數(shù)可以根據(jù)需要使用相應(yīng)的參數(shù)，比數(shù)據(jù)表或視圖更具動態(tài)性。表值型自定義函數(shù)包括內(nèi)嵌(inline)和多語句(Multistatement)表值型自定義函數(shù)兩種58內(nèi)嵌表值型UDF1、返回類型為table。返回變量的格式由RETURN子句中的SELECT語句的結(jié)果集的格式設(shè)置。2、function_body

不由BEGIN和END分隔。3、RETURN子句在括號中包含單個(gè)SELECT語句。SELECT語句的結(jié)果集構(gòu)成函數(shù)所返回的表。59內(nèi)嵌表值型自定義函數(shù)例9-19創(chuàng)建用戶定義函數(shù)goodsq，返回輸入商品編號的商品名稱和庫存量。CREATEFUNCTIONgoodsq(@goods_idvarchar(30))RETURNSTABLEASRETURN(SELECTgoods_name,stock_quantityFROMgoodsWHEREgoods_id=@goods_id)60由于視圖不支持在WHERE子句的搜索條件中使用參數(shù)，內(nèi)嵌表值函數(shù)可彌補(bǔ)視圖的這一不足之處，即內(nèi)嵌表值函數(shù)可用于實(shí)現(xiàn)參數(shù)化視圖的功能。61多語句表值型自定義函數(shù)多語句表值型自定義函數(shù)是指那些需要多條查詢語句才能完成既定目標(biāo)的表值型函數(shù)。這類表值型自定義函數(shù)只能用在SELECT查詢語句中的FROM子句中。62多語句表值型自定義函數(shù)例9-20根據(jù)輸入的訂單編號，返回該訂單對應(yīng)商品的編號、名稱、類別編號、類別名稱。CREATEFUNCTION

good_info(@in_o_idvarchar(10))RETURNS@goodinfo

TABLE(o_idchar(6),g_idchar(6),g_namevarchar(50),

c_idchar(6),c_namevarchar(20))AS63BEGIN

DECLARE@g_idvarchar(10),@g_namevarchar(30)

DECLARE@c_idvarchar(10),@c_namevarchar(30)

SELECT@g_id=goods_id

FROM

sell_order

WHEREorder_id1=@in_o_id

SELECT@g_name=goods_name,@c_id=classification_id

FROMgoodsWHERE

goods_id=@g_id64

SELECT@c_name=classification_name

FROM

goods_classification

WHERE@c_id=classification_id

INSERT@goodinfo

VALUES(@in_o_id,@g_id,@g_name,@c_id,@c_name)

RETURNEND65例：在STUDENT庫中創(chuàng)建一個(gè)多語句表值函數(shù)CHENGJI，該函數(shù)可以根據(jù)輸入的課程名稱返回選修該課程的學(xué)生姓名和成績。

CREATEFUNCTIONCHENGJI(@inputkcaschar(20))RETURNS@chjiTABLE(課程名char(20),

姓名char(8),

成績tinyint)AS66ASBEGININSERT@chjiSELECTc.課程名,s.姓名,k.成績

FROM學(xué)生asSINNERJOIN選課asSCONS.學(xué)號=SC.學(xué)號innerjoin課程asConC.課程號=C.課程號

WHEREC.課程名=@inputkcRETURNENDGO67執(zhí)行用戶自定義函數(shù)P188調(diào)用方法與系統(tǒng)內(nèi)置函數(shù)相同，但要在函數(shù)名前加上架構(gòu)名。可用使用EXECUTE語句執(zhí)行標(biāo)量值函數(shù)。SELECTdbo.DatetoQuarter('2006-8-5')SELECT*FROMdbo.goodsq('G00002')SELECT*FROMdbo.good_info('S00002')689.3流程控制語句結(jié)構(gòu)化程序設(shè)計(jì)語言的基本結(jié)構(gòu)是順序、條件分支和循環(huán)。T-SQL語言中，用于控制語句流的語言被稱為控制流語言。SQLServer2005系統(tǒng)提供了8種控制流語句69控制流語句控制流語句描

述BEGIN…END定義語句塊，這些語句塊作為一組語句執(zhí)行，允許語句塊嵌套BREAK退出WHILE或IF…ELSE語句中最里面的循環(huán)。如果END關(guān)鍵字作為循環(huán)結(jié)束標(biāo)記，那么執(zhí)行BREAK語句后將執(zhí)行出現(xiàn)在END關(guān)鍵字后面的任何語句GOTO使T-SQL批處理的執(zhí)行跳至指定標(biāo)簽的語句。不執(zhí)行GOTO語句和標(biāo)簽之間的所有語句。該語句破壞了結(jié)構(gòu)化語句的結(jié)構(gòu)，盡量減少使用CONTINUE重新開始一個(gè)新的WHILE循環(huán)70控制流語句控制流語句描

述IF…ELSE指定T-SQL語句的執(zhí)行條件。如條件為TRUE，則執(zhí)行其后的T-SQL語句。當(dāng)條件為FALSE時(shí)，可以使用ELSE關(guān)鍵字指定要執(zhí)行的T-SQL語句WHILE設(shè)置重復(fù)執(zhí)行T-SQL語句或語句塊的條件。當(dāng)指定條件為真時(shí)，重復(fù)執(zhí)行循環(huán)語句。可在循環(huán)體內(nèi)設(shè)置BREAK和CONTINUE關(guān)鍵字，以控制循環(huán)語句的執(zhí)行71控制流語句控制流語句描

述RETURN無條件終止查詢、存儲過程或批處理的執(zhí)行。存儲過程或批處理中RETURN語句后面的所有語句都不再執(zhí)行。當(dāng)在存儲過程中使用RETURN語句時(shí)，可使用該語句指定返回給調(diào)用應(yīng)用程序、批處理或過程的整數(shù)值。如果RETURN語句未指定值，則存儲過程的返回值是0WAITFOR懸掛起批處理、存儲過程或事務(wù)的執(zhí)行，直到發(fā)生以下情況為止：已超過指定的時(shí)間間隔、到達(dá)一天中指定的時(shí)間、指定的RECEIVE語句至少修改一行數(shù)據(jù)。該語句是通過暫停語句的執(zhí)行而改變語句的執(zhí)行過程729.3.1語句塊和注釋BEGIN…END語句：將多個(gè)T-SQL語句組合成一個(gè)語句塊，并將它們視為一個(gè)單元處理。在條件語句(IF)和循環(huán)(WHILE)等控制流程語句中，當(dāng)符合特定條件便要執(zhí)行兩個(gè)或者多個(gè)語句時(shí)，就需要使用BEGIN…END語句。

BEGIN

{sql_statement|statement_block}END739.3.1語句塊和注釋例9-21顯示Sales數(shù)據(jù)庫中customer表的編號為C0001的聯(lián)系人姓名。DECLARE@linkman_namechar(8)BEGINSELECT@linkman_name=(SELECTlinkman_nameFROMcustomerWHEREcustomer_idLIKE'C0001')SELECT@linkman_nameEND74例9-22語句塊嵌套舉例DECLARE@errorcode

int,@nowdate

datetimeBEGIN SET@nowdate=getdate() INSERTsell_order(order_date,send_date,arriver_date,custom_id) VALUES(@nowdate,@nowdate+5,@nowdate+10,'C0002') SELECT@errorcode=@@error IF@errorcode>0 BEGIN RAISERROR('當(dāng)表sell_order插入數(shù)據(jù)時(shí)發(fā)生錯(cuò)誤!',16,1) RETURN ENDEND75注釋注釋是程序代碼中不執(zhí)行的文本字符串，用于對代碼進(jìn)行說明或暫時(shí)僅用正在進(jìn)行診斷的部分語句。單行注釋：使用兩個(gè)聯(lián)在一起的減號“--”作為注釋內(nèi)容標(biāo)記，其后為注釋內(nèi)容。多行注釋：使用“/*注釋內(nèi)容*/”格式。服務(wù)器不對注釋進(jìn)行解釋。76使用兩種注釋類型的例子/*程序名稱：創(chuàng)建ContactHead表的腳本作者：微軟公司最后一次修改日期：2005年12月10日程序描述：用戶存儲合同的基本概況信息*/CREATETABLEContacts(--表名稱ContactHeadContactIDINTNOTNULLPRIMARYKEY,--合同編碼，每一個(gè)合同只能有一個(gè)唯一性的編碼customerNameVARCHAR(128),--簽訂合同的客戶名稱salePersonNameVARCHAR(36),--簽訂合同的員工名稱issueDateDATETIME,--合同簽訂日期otherVARCHAR(128))--備注779.3.2選擇控制P191IF…ELSE語句是條件判斷語句ELSE子句是可選的SQLServer允許嵌套使用IF…ELSE語句

IF

Boolean_expression

{sql_statement|statement_block}

[ELSE

{sql_statement|statement_block}]78例如果李四的平均成績?yōu)?0分以上，顯示其成績情況，否則顯示文本：成績狀態(tài)不理想。declare@avgs

intselect@avgs=avg(grade)from

s,sc

where

s.sno=sc.sno

and

sname='李四'if(@avgs>=60)select

s.sno,sname,cno,grade

from

s,scwhere

s.sno=sc.sno

and

sname='李四'elseprint'成績不理想'79例9-23判斷表goods中supplier_id為S001的商品的平均單價(jià)是否大于9799。IF(SELECTavg(unit_price)FROMgoodsWHEREsupplier_id='S001')>$9799.0SELECT'supplier_id為S001的商品的平均單價(jià)比9799大'ELSESELECT'supplier_id為S001的商品的平均單價(jià)比9799小'80例9-24用EXISTS確定表department中是否存在“陳曉兵”。DECLARE@lnamevarchar(40),@msgvarchar(255)SELECT@lname='陳曉兵'IFEXISTS(SELECT*FROMdepartmentWHEREmanager=@lname)BEGINSELECT@msg='有人名為'+@lnameSELECT@msgEND81

ELSEBEGINSELECT@msg='沒有人名為'+@lnameSELECT@msgEND82CASE函數(shù)P193計(jì)算條件列表并返回多個(gè)可能結(jié)果表達(dá)式之一。CASE具有兩種格式：簡單CASE函數(shù)將某個(gè)表達(dá)式與一組簡單表達(dá)式進(jìn)行比較以確定結(jié)果。CASE搜索函數(shù)計(jì)算一組布爾表達(dá)式以確定結(jié)果。兩種格式都支持可選的ELSE參數(shù)。83簡單CASE函數(shù)CASEinput_expression

WHENwhen_expressionTHEN

result_expression[...n]

[ELSEelse_result_expression]ENDSearched84CASE搜索函數(shù)CASEWHENBoolean_expressionTHEN

result_expression[...n]

[ELSEelse_result_expression]END85簡單CASE函數(shù)例9-26使用簡單CASE函數(shù)將goods表中的商品分類重命名，以使之更易理解。SELECTCASEclassification_idWHEN'P001'THEN'筆記本計(jì)算機(jī)'WHEN'P002'THEN'激光打印機(jī)'WHEN'P003'THEN'噴墨打印機(jī)'WHEN'P004'THEN'交換機(jī)'ELSE'沒有這種品牌'ENDASClassification，goods_nameAS'GoodsName'，unit_priceASPriceFROMgoodsWHEREunit_priceISNOTNULL86CASE搜索函數(shù)例9-27根據(jù)goods表中庫存貨物數(shù)量與訂貨量之差，使用CASE搜索函數(shù)判斷該商品是否進(jìn)貨。SELECTgoods_nameAS商品名稱,CASEWHENstock_quantity-order_quantity<=3THEN'緊急進(jìn)貨'WHENstock_quantity-order_quantity>3andstock_quantity-order_quantity<=10THEN'暫緩進(jìn)貨'WHENstock_quantity-order_quantity>10THEN'貨物充足'ENDAS進(jìn)貨判斷FROMgoods87GOTO語句P194GOTO語句可以使程序直接跳到指定的標(biāo)有標(biāo)識符的位置處繼續(xù)執(zhí)行，而位于GOTO語句和標(biāo)識符之間的程序?qū)⒉粫粓?zhí)行。標(biāo)識符可以為數(shù)字與字符的組合，但必須以“:”結(jié)尾。如：‘a(chǎn)1:’。在GOTO語句行，標(biāo)識符后面不用跟“:”。GOTOlabel……label:88例9-28使用GOTO語句改變程序流程。

DECLARE@xintSELECT@x=1label_1：

SELECT@xSELECT@x=@x+1WHILE@x<6GOTOlabel_189RETURN語句P195RETURN語句用于無條件地終止一個(gè)查詢、存儲過程或者批處理，此時(shí)位于RETURN語句之后的程序?qū)⒉粫粓?zhí)行。RETURN語句的語法形式為：RETURN[integer_expression]其中，參數(shù)integer_expression為返回的整型值。存儲過程可以給調(diào)用過程或應(yīng)用程序返回整型值。90例9-29RETURN語句應(yīng)用示例。DECLARE@xint,@y

intSELECT@x=1,@y=2IF@x>@yRETURNELSERETURN91WAITFOR語句P195WAITFOR語句用于暫時(shí)停止執(zhí)行SQL語句、語句塊或者存儲過程等，直到所設(shè)定的時(shí)間已過或者所設(shè)定的時(shí)間已到才繼續(xù)執(zhí)行。WAITFOR{DELAY'time'|TIME'time'}DELAY用于指定時(shí)間間隔，TIME用于指定某一時(shí)刻，其數(shù)據(jù)類型為datetime，格式為‘hh:mm:ss’。92WAITFOR語句使用WAITFORTIME語句，以便在晚上10:20執(zhí)行存儲過程update_all_stats。BEGINWAITFORTIME'22:20'EXECUTEupdate_all_statsEND93WAITFOR語句WAITFORDELAY'00:01:00'SELECT*FROMgradeWAITFORTIME'23:00'BACKUPDATABASEstudentsdbTOstudentsdb_bkp949.3.3循環(huán)控制P196設(shè)置重復(fù)執(zhí)行SQL語句或語句塊的條件。只要指定的條件為真，就重復(fù)執(zhí)行語句?？梢允褂肂REAK和CONTINUE關(guān)鍵字在循環(huán)內(nèi)部控制WHILE循環(huán)中語句的執(zhí)行。WHILEBoolean_expression

{sql_statement|statement_block}[BREAK]

{sql_statement|statement_block}[CONTINUE]

{sql_statement|statement_block}959.3.3循環(huán)控制BREAK導(dǎo)致從最內(nèi)層的WHILE循環(huán)中退出。將執(zhí)行出現(xiàn)在END關(guān)鍵字（循環(huán)結(jié)束的標(biāo)記）后面的任何語句。CONTINUE使WHILE循環(huán)重新開始執(zhí)行，忽略CONTINUE關(guān)鍵字后面的任何語句。如果嵌套了兩個(gè)或多個(gè)WHILE循環(huán)，則內(nèi)層的BREAK將退出到下一個(gè)外層循環(huán)。969.3.3循環(huán)控制例9-30將goods表中庫存數(shù)最大的商品每次訂購兩件，計(jì)算如此需要多少次訂購才能使庫存數(shù)不夠一次訂購。DECLARE@countint,@maxstockidchar(6),@maxstockfloatSET@count=0SET