第3章

Transact－SQL程序設(shè)計(jì)SQLServer2005中唯一指定的編程語言是Transact-SQL語言，這是一種非過程化的編程語言。不論是基于C/S（客戶/服務(wù)器）的應(yīng)用程序，還是基于B/S（瀏覽器/服務(wù)器）的Web應(yīng)用程序，都必須通過向服務(wù)器發(fā)送Transact-SQL語言才能實(shí)現(xiàn)與SQLServer的數(shù)據(jù)庫進(jìn)行通信。使用Transact-SQL語言操作時(shí)，與SQLServer通信的所有應(yīng)用程序都是必須通過向服務(wù)器發(fā)送Transact-SQL語句來進(jìn)行，而與應(yīng)用程序的界面無關(guān)。Transact-SQL語言的特點(diǎn)：1.一體化的特點(diǎn)。Transact-SQL語言及數(shù)據(jù)定義語言、數(shù)據(jù)操縱語言、數(shù)據(jù)控制語言和附加語言元素（包括變量、運(yùn)算符、函數(shù)、流程控制語句和注釋等）為一體。2.兩種使用方式，統(tǒng)一的語法結(jié)構(gòu)。兩種使用方式即聯(lián)機(jī)交互式和嵌入式高級語言的使用方式。3.高度非過程化。Transact-SQL語言一次能處理一個(gè)或多個(gè)記錄，所有的SQL語句接受集合作為輸入，返回集合作為輸出，并允許一條SQL語句的結(jié)果作為另一條SQL語句的輸入。另外，Transact-SQL語言不要求用戶指定對數(shù)據(jù)的存放方法，所有的Transact-SQL語句使用查詢優(yōu)化器，用以指定數(shù)據(jù)以最快速度存取的手段。4.類似于人的思維習(xí)慣，容易理解和掌握。在Transact-SQL語言中對標(biāo)準(zhǔn)的SQL語句暢通無阻。Transact-SQL對SQL語言做了許多擴(kuò)充，具體分類如下：變量說明：用來說明變量的命令。數(shù)據(jù)定義語言（DDL，DataDefinitionLanguage）：用來建立數(shù)據(jù)庫、數(shù)據(jù)庫對象和定義其列，大部分是以CREATE開頭的命令，如：CREATETABLE、CREATEVIEW、DROPTABLE等。數(shù)據(jù)操控語言（DML，DataManipulationLanguage）：用來操縱數(shù)據(jù)庫中的數(shù)據(jù)的命令，如：SELECT、INSERT、UPDATE、DELETE、CURSOR等。數(shù)據(jù)控制語言（DCL，DataControlLanguage）：用來控制數(shù)據(jù)庫組件的存取許可、存取權(quán)限等的命令。如GRANT、REVOKE等。流程控制語言（FlowControlLanguage）：用于設(shè)計(jì)應(yīng)用程序的語句如IF、WHILE、CASE等。函數(shù)：執(zhí)行特殊運(yùn)算的命令。其他命令：嵌于命令中使用的標(biāo)準(zhǔn)函數(shù)。

3.1常量和變量

SQLServer2005中的Transact-SQL與其它程序設(shè)計(jì)語言一樣，在進(jìn)行程序設(shè)計(jì)時(shí)會(huì)用到一些數(shù)據(jù)，數(shù)據(jù)有兩個(gè)屬性：型和值，型是指數(shù)據(jù)的分類，即數(shù)據(jù)類型；值是指數(shù)據(jù)的具體表示，即數(shù)值。這些數(shù)據(jù)的值可能會(huì)是確定的，也可能是不確定的，甚至數(shù)據(jù)的值在程序的運(yùn)行過程中也會(huì)不斷的發(fā)生變化，這就是所謂的常量和變量，本節(jié)主要講解Transact-SQL語言中的常量和變量的使用及其語法規(guī)則，并通過一些簡單易懂的實(shí)例，來幫助讀者加快理解常量和變量的實(shí)質(zhì)。3.1.1案例實(shí)現(xiàn)過程【技術(shù)要點(diǎn)】通過“開始—程序”菜單找到MicrosoftSQLServer2005的子菜單，點(diǎn)擊SQLServerManagementStudio并打開其主窗口，此時(shí)會(huì)彈出一個(gè)連接服務(wù)器的對話框，點(diǎn)擊對話框上的“連接”按鈕，從而實(shí)現(xiàn)數(shù)據(jù)庫的連接。在本案例中將采用如下步驟來逐步闡述常量和變量的使用。（1）Transact-SQL語言程序設(shè)計(jì)，需要在查詢編輯器主窗口中進(jìn)行，查詢編輯器是SQLServer2005專為Transact-SQL語言提供的集編輯、調(diào)試、結(jié)果打印輸出等功能的窗口部件。點(diǎn)擊SQLServerManagementStudio主窗口中的“文件”菜單下面的“新建查詢”按鈕，如圖3.2所示，打開查詢編輯器主窗口。

圖3.2“新建查詢”對話框2.執(zhí)行步驟（1）后會(huì)打開一個(gè)新的查詢編輯器窗口，即可進(jìn)行Transact-SQL編程；當(dāng)然也可通過快捷鍵“Alt+N”打開一個(gè)新的查詢編輯器窗口。如圖3.3所示。

圖3.3查詢編輯器主窗口

（3）查詢編輯器主窗口中由一個(gè)光標(biāo)在閃爍，此時(shí)就可以在打開的查詢編輯器主窗口中就進(jìn)行編寫代碼。

【例3.1】使用Transact-SQL語句，查詢課程表中前三條記錄，并把它們的學(xué)分減1，要求打印輸出結(jié)果。 Transact-SQL代碼清單如下。 USE教學(xué)管理系統(tǒng) --打開數(shù)據(jù)庫 GO --批處理結(jié)束命令 SELECTTOP(3)*FROM課程表 --查詢課程表中前三條記錄 SELECTTOP(3)課程編號,課程名,學(xué)分-1AS學(xué)分FROM課程表 --查詢學(xué)分減1后課程表中前三條記錄

在【例3.1】中第1個(gè)SELECT作用是查詢課程表中的前三條記錄，第2個(gè)SELECT查詢的是課程表中的前三條記錄，并在原來的基礎(chǔ)上，把學(xué)分減1，這里的“1”即為整型常量?！纠?.1】代碼執(zhí)行后的結(jié)果如圖3.4所示。圖3.4常量的使用

【例3.2】使用Transact-SQL語句，實(shí)現(xiàn)查詢教師表中計(jì)算機(jī)系教師的全部信息，要求打印輸出結(jié)果。 Transact-SQL代碼清單如下。 USE教學(xué)管理系統(tǒng)

--打開數(shù)據(jù)庫 GO

--批處理結(jié)束命令 DECLARE@AAchar(8)

--聲明局部變量 SET@AA='計(jì)算機(jī)系'

--初始化變量 SELECT*FROM教師表WHERE

所在部門=@AA

--SQL語句

在【例3.2】中定義了一個(gè)局部變量@AA，在SELECT查詢中引用了此

局部變量。從此例中可以看出局部變量在使用時(shí)必須在其前面冠以“@”符號?！纠?.2】代碼執(zhí)行后，運(yùn)行的結(jié)果如圖3.5所示。

圖3.5局部變量的使用

而全局變量的使用與局部變量有所不同，其最大的特點(diǎn)是必須在全局變量前面冠以“@@”。

例如，@@VERSION全局變量表示返回當(dāng)前安裝的SQLServer的日期、版本、處理器，而@@COMMECTIONS全局變量表示自SQLServer最近一次啟動(dòng)以來，連接或企圖連接到SQLServer的連接數(shù)目。使用SELECT執(zhí)行全局變量@@COMMECTIONS和@@CONNECTIONS即可返回其值。如圖3.6所示

圖3.6全局變量的使用3.1.2相關(guān)知識及注意事項(xiàng)1.常量

常量是在程序運(yùn)行中值不發(fā)生變化的量，常量的格式取決于它所表示的數(shù)據(jù)類型。根據(jù)常見的數(shù)據(jù)類型，分為字符串常量、整型常量、實(shí)型常量、貨幣常量和日期時(shí)間常量。下面舉例說明一些常見的常量的使用方法。

1)字符串常量

（1）ASCII字符串常量

（2）Unicode字符串常量

2)整型常量

整型常量由數(shù)字0~9以及+/-號組成的有意義的串。如123、-9811、+89。

3)實(shí)型常量

實(shí)型常量有定點(diǎn)和浮點(diǎn)兩種表示形式。

（1）定點(diǎn)實(shí)型常量 由數(shù)字0~9、小數(shù)點(diǎn)和正負(fù)號組成有意義的串。如1.0、3467.584。

（2）浮點(diǎn)實(shí)型常量 由數(shù)字0~9、小數(shù)點(diǎn)、正負(fù)號和字母E組成有意義的串。如0.68E+3、1E10、-23E2。

4)貨幣常量

貨幣常量是以＄符號為前綴的整型或?qū)嵭统Ａ?，用來表示貨幣值，精度?位小數(shù)。如＄100、-＄52、＄16.37。

5)日期時(shí)間常量

日期常量是用單引號括起來的有效的日期和時(shí)間組成的字符串。

（1）常見的日期格式

'September1、2007'、'2007年9月1號'、'2007-09-01'、'09/01/2007'、'20070901'

（2）常見的時(shí)間格式

'14:12:00'、'02:12PM'2.變量

變量是程序設(shè)計(jì)語言中必不可少的組成部分，在Transact-SQL語言中，變量被分為兩種形式：局部變量（LocalVariable）和全局變量（Globlevariable），局部變量是由用戶定義和維護(hù)，而全局變量則是由系統(tǒng)提供的。

1）局部變量

局部變量是一個(gè)能夠擁有特定數(shù)據(jù)類型的對象，它的作用范圍僅限制在一定的范圍之內(nèi)，通常用來存儲從表中查詢到的數(shù)據(jù)，或當(dāng)作程序執(zhí)行過程中的暫存變量。2）全局變量

全局變量是SQLServer系統(tǒng)內(nèi)部使用的變量，其作用范圍并不僅僅局限于某一程序或處理，而是任何程序均可隨時(shí)調(diào)用，全局變量的值不能使用SET或者SELECT語句來改變。全局變量通常存儲一些SQLServer的配置設(shè)定值和效能統(tǒng)計(jì)數(shù)據(jù)，可以將全局變量的值賦給局部變量，以便保存和處理。用戶可在程序中用全局變量來測試系統(tǒng)的設(shè)定值或Transact-SQL命令執(zhí)行后的狀態(tài)值。

在SQLServer2005中，全局變量可以看成是一組特殊的函數(shù)，它們的名稱以@@開頭，而且不需要任何參數(shù)，在調(diào)用時(shí)無需在函數(shù)名后面加上一對圓括號，這些函數(shù)也稱為無參函數(shù)。

全局變量是SQLServer管理的變量，用戶雖然可以查看全局變量的值，但不能建立全局變量，更不能改變?nèi)肿兞康闹怠?/p>

全局變量分為兩類：一類反映SQLServer系統(tǒng)的全局變量，另一類反映與一個(gè)連接有關(guān)的全局變量。

3.2數(shù)據(jù)類型和運(yùn)算符

數(shù)據(jù)類型是數(shù)據(jù)的一個(gè)屬性，決定著數(shù)據(jù)的存儲方式和使用的方法，即限制了數(shù)據(jù)庫表中一個(gè)字段所能保存的數(shù)值，以及參數(shù)或變量中所能存儲的信息的類型。例如，兩個(gè)實(shí)數(shù)可以進(jìn)行乘法運(yùn)算，但兩個(gè)字符型數(shù)據(jù)卻不能做乘法運(yùn)算。

運(yùn)算符是一些符號，用于在常量、變量和函數(shù)之間進(jìn)行某種操作。他們能夠用來執(zhí)行算術(shù)運(yùn)算、字符串連接、賦值以及在字段、常量和變量之間進(jìn)行比較。將數(shù)據(jù)類型指定的數(shù)值與相應(yīng)的運(yùn)算符結(jié)合起來構(gòu)成表達(dá)。大多數(shù)Transact-SQL語句并不明確指定數(shù)據(jù)類型，但SQL語句中各對象數(shù)據(jù)類型的作用結(jié)果，將直接影響各個(gè)語句的運(yùn)行結(jié)果，從而可能導(dǎo)致與程序設(shè)計(jì)時(shí)的預(yù)期有偏差。【案例說明】

在SQLServer2005中，每個(gè)字段（即列值）、局部變量、表達(dá)式和參數(shù)都具有一個(gè)相關(guān)的數(shù)據(jù)類型，并且通過與運(yùn)算符的連接，執(zhí)行相應(yīng)的運(yùn)算之后，得到一個(gè)表達(dá)式的結(jié)果。

【例3.5】使用Transact-SQL語句，實(shí)現(xiàn)查詢學(xué)生信息表中的學(xué)生的學(xué)號、姓名、年齡、專業(yè)等信息，并打印輸出相關(guān)信息。Transact-SQL代碼清單如下。

/*文件名：Demo1-1.sql

作者：陳潔

日期：2010-04-18*/

USE教學(xué)管理系統(tǒng)--打開數(shù)據(jù)庫

GO

SELECT學(xué)號,姓名,性別,年齡=DATEDIFF(year,出生日期,GETDATE()),專業(yè)

from學(xué)生表

--DATEDIFF()函數(shù)用來計(jì)算年齡 在【例3.5】中放在“/*”和“*/”內(nèi)的文本為塊注釋，以“--”開始的文本為行內(nèi)注釋。DATEDIFF()函數(shù)帶有三個(gè)參數(shù)，其中第三個(gè)參數(shù)獲得當(dāng)前的時(shí)間，并與第二個(gè)參數(shù)相減，第一個(gè)參數(shù)year表示截取第二個(gè)參數(shù)與第三個(gè)參數(shù)運(yùn)算結(jié)果中的年部分的數(shù)據(jù)，從而實(shí)現(xiàn)了年齡的計(jì)算，運(yùn)行結(jié)果如圖3.9所示。

在【例3.5】中使用到了DATEDIFF()函數(shù)，對于函數(shù)的具體使用方法，可參閱本書的有關(guān)附錄。

圖3.9注釋符的使用

將【例3.5】中代碼進(jìn)行修改為【例3.7】代碼中代碼，執(zhí)行后的結(jié)果如【例3.7】中的代碼所示。

【例3.7】使用Transact-SQL語句，實(shí)現(xiàn)把學(xué)生信息表中的學(xué)生的年齡全部加1，并打印輸出相關(guān)信息。

Transact-SQL代碼清單如下。

/*文件名：Demo1-1.sql

作者：陳潔

日期：2010-04-18*/

USE教學(xué)管理系統(tǒng)--打開數(shù)據(jù)庫

GO

--批處理結(jié)束語句

DECLARE@numint

--定義局部變量

SELECT學(xué)號,姓名,性別,年齡=(DATEDIFF(year,出生日期,GETDATE()))

+1,專業(yè)from學(xué)生表

--查詢學(xué)生表，并把學(xué)生年齡都加1

SET@num=(SELECTcount(學(xué)號)from學(xué)生表)

--查詢學(xué)生表中的學(xué)生個(gè)數(shù)，并把它賦局部給變量@num

/*用字符串連接運(yùn)算符’+‘，把中文字符串常量和CAST函數(shù)轉(zhuǎn)換過來的數(shù)字字符串連接起來，并用PRINT語句輸出運(yùn)算后的字符串*/

PRINT'共有'+CAST(@numASchar(2))+'個(gè)學(xué)生年齡加1！'

【例3.7】代碼運(yùn)行后效果如圖3.10所示，圖左邊的是SELECT查詢語句運(yùn)行的結(jié)果，圖右邊的是消息標(biāo)簽下的內(nèi)容，即PRINT語句輸出的結(jié)果。圖3.10運(yùn)算符的使用3.2.2相關(guān)知識及注意事項(xiàng)1．?dāng)?shù)據(jù)類型1）精確數(shù)字類型

a.整數(shù)類型

int數(shù)據(jù)類型是SQLServer2005中的主要整形數(shù)據(jù)類型，bigint數(shù)據(jù)類型用于整數(shù)數(shù)值可能超過int數(shù)據(jù)類型支持范圍的情況。實(shí)際使用中，要根據(jù)所存儲數(shù)據(jù)的最大范圍來選擇。使用整型的精確數(shù)字類型如表3-3所示。

表3-3使用整型的精確數(shù)字類型數(shù)據(jù)類型范圍存儲bigint-2^63(-9223372036854775808)~2^63-1(9223372036854775807)8ByteInt-2^31(-2147483648)~2^31-1(2147483647)4Bytesmallint-2^15(-32768)~2^15-1(32767)2Byte續(xù)表數(shù)據(jù)類型范圍存儲tinyint0~2551Byte數(shù)據(jù)類型范圍存儲Money-922337203685477.5808~922337203685477.58078ByteSmallmoney-214748.3648~214748.36474Byte

b.邏輯數(shù)據(jù)類型

bit是可以取值為1、0或NULL的整型數(shù)據(jù)類型。SQLServer2005優(yōu)化了bit列的存儲。如果表中的列為8bit或更少，則這些列作為1Byte存儲。如果列為9到16bit，則這列作為2Byte存儲，依此類推?？蓪⒅挥袃蓚€(gè)值的數(shù)據(jù)定義為bit類型，即只存儲1或0的整數(shù)數(shù)據(jù)。c.decimal和numeric

decimal和numeric為帶固定精度和小數(shù)位數(shù)的數(shù)值數(shù)據(jù)類型。使用最大精度時(shí)，有效值從-10^38+1~10^38-1，decimal在SQL-92中的同義詞為dec和dec（p，s）。numeric在功能上等價(jià)于decimal。d.money和smallmoney

貨幣數(shù)據(jù)類型用于存儲貨幣值，在使用貨幣類型時(shí)，應(yīng)在數(shù)據(jù)前加上貨幣符號系統(tǒng)才能辨識其為哪國的貨幣，如果不加上貨幣符號，則默認(rèn)為“￥”。

Money和smallmoney代表貨幣或貨幣值的數(shù)據(jù)類型，數(shù)據(jù)類型精確到它們所代表的貨幣單位的萬分之一，即貨幣類型數(shù)據(jù)實(shí)際上是帶有4位小數(shù)的Decimal類型數(shù)據(jù)。在Money和smallmoney類型的字段中輸入貨幣數(shù)據(jù)時(shí)必須在數(shù)值前加一個(gè)貨幣符號(如:美圓$)。輸入負(fù)值時(shí)應(yīng)當(dāng)在貨幣符號后面加“-”，輸入過程中不需要每3位添加一個(gè)逗號來分隔，但是打印的時(shí)候會(huì)自動(dòng)添加。表3-5為貨幣數(shù)據(jù)類型表示的范圍以及存儲的字節(jié)數(shù)。表3-5貨幣數(shù)據(jù)類型

2）近似數(shù)字類型

用于表示浮點(diǎn)數(shù)值數(shù)據(jù)的類型為大數(shù)值數(shù)據(jù)類型。浮點(diǎn)數(shù)據(jù)為近似值，因此，并非數(shù)據(jù)類型范圍內(nèi)的所有值都能精確的表示。3）日期和時(shí)間類型日期和時(shí)間的數(shù)據(jù)類型分為datetime和smalldatetime兩種，分別用于表示某天的日期和時(shí)間4）字符數(shù)據(jù)類型字符數(shù)據(jù)類型是使用最多的數(shù)據(jù)類型，它可以用來存儲各種字母、數(shù)字符號、特殊符號。5）二進(jìn)制數(shù)據(jù)類型二進(jìn)制數(shù)據(jù)類型是用來存儲固定長度的binary數(shù)據(jù)類型。6）其他類型

（1）游標(biāo)cursor

（2）timestamp

（3）sql_variant

（4）table

（5）xml

7）用戶自定義類型

可以從基本數(shù)據(jù)類型創(chuàng)建別名類型數(shù)據(jù)。這使程序員能更容易地理解該數(shù)據(jù)類型的用途。例如CREATETYPEbirthdayFROMdatetimeNULL，接下來就可以使用brithday來定義數(shù)據(jù)對象了。2．運(yùn)算符

在SQLServer2005中，運(yùn)算符主要有六大類：算術(shù)運(yùn)算符、賦值運(yùn)算符、位運(yùn)算符、比較運(yùn)算符、邏輯運(yùn)算符、字符串串聯(lián)運(yùn)算符、

3.

注釋符1）行內(nèi)注釋

行內(nèi)注釋符用“--”表示。2）塊注釋符

塊注釋符用“/*”開始，以“*/”結(jié)尾，而且必須是成對出現(xiàn)。4.通配符

通配符指“%”、“_”、“[]”和“[^]”5.表達(dá)式

表達(dá)式是標(biāo)識符、值和運(yùn)算符的組合，可以對其求值以獲取結(jié)果。

表達(dá)式可以是下列任何一種：常量、函數(shù)、列名、變量、子查詢、CASE、NULLIF或COALESCE，還可以用運(yùn)算符對這些實(shí)體進(jìn)行組合以生成表達(dá)式。

3.3流程控制語句

Transact-SQL提供了控制語句，用于控制Transact-SQL語句、語句塊和存儲過程等程序的執(zhí)行和流程分支的命令。Transact-SQL控制流語言的使用與其他高級程序設(shè)計(jì)語言基本相似，如條件控制語句和循環(huán)語句。使用這些語句命令，可以使程序更具有結(jié)構(gòu)性和邏輯性，并可完成較復(fù)雜的操作。Transact-SQL的控制流語句不能跨越多個(gè)批處理或存儲過程。2.2.2相關(guān)知識及注意事項(xiàng)

在Transact-SQL語言中，流程控制語句主要有語句塊、條件選擇語句、分支處理語句、循環(huán)語句、返回語句、轉(zhuǎn)移語句、打印輸出語句等，表3-12列出了常用流程控制語句及其功能能。語句功能BEGIN…END定義語句塊IF…ELSE條件選擇語句，條件成立執(zhí)行IF后面語句，否則執(zhí)行ELSE后面的語句CASE語句分支處理語句，表達(dá)式可根據(jù)條件返回不同的值WHILE語句循環(huán)語句，重復(fù)執(zhí)行命令行或程序塊WAITFOR設(shè)置語句執(zhí)行的時(shí)間或者延遲執(zhí)行的時(shí)間BREAK跳出循環(huán)，即終止循環(huán)C