公司管理數(shù)據(jù)庫系統(tǒng)實例

7【教學(xué)目標(biāo)】

了解SQL編程的基礎(chǔ)知識和SQLServer2005提供的常用系統(tǒng)函數(shù)，具有常用代碼的編寫能力?！尽浚?）能夠正確應(yīng)用T-SQL的表達式和基本控制語句。（2）能夠根據(jù)項目需求分析編寫簡單的T-SQL【學(xué)習(xí)導(dǎo)航】

使用T-SQL語句編寫的程序可以通過SQLServer提供的SQLServerManagementStudio查詢分析器運行，也可以存儲在數(shù)據(jù)庫服務(wù)器上運行。本項目學(xué)習(xí)T-SQL編程基礎(chǔ)知識，為后面存儲過程、觸發(fā)器的編程打好基礎(chǔ)。【工作任務(wù)】

來管理雇員的工作業(yè)績信息和實現(xiàn)訂購訂單功能，以提高系統(tǒng)的效率。公司管理數(shù)據(jù)庫系統(tǒng)實例

理論知識準(zhǔn)備

7.1.1T-SQL編程元素

Transact-SQL（簡稱T-SQL）在支持標(biāo)準(zhǔn)SQL的同時，還對其進行了擴充，引入了變量定義、流程控制和自定義存儲過程等語句，極大地擴展了SQLServer2005的功能。使用T-SQL語句編寫的程序可以通過SQLServer的查詢分析器運行，也可以存儲在數(shù)據(jù)庫服務(wù)器上運行。任何應(yīng)用程序，不管它是用什么形式的高級語言編寫的，只要目的是向SQLServer的數(shù)據(jù)庫管理系統(tǒng)發(fā)出命令以獲得數(shù)據(jù)庫管理系統(tǒng)的響應(yīng)，最終都必須體現(xiàn)為以T-SQL語句為表現(xiàn)形式的指令。因此，無論是數(shù)據(jù)庫管理員，還是數(shù)據(jù)庫應(yīng)用程序的開發(fā)人員，要想深入掌握SQLServer2005，認真學(xué)習(xí)T-SQL是必要的。1．T-SQL語法規(guī)則T-SQL的語法規(guī)則如表所示。公司管理數(shù)據(jù)庫系統(tǒng)實例書寫規(guī)則說明大寫T-SQL關(guān)鍵字斜體或小寫字母T-SQL語法中用戶提供的參數(shù)|（豎線）分隔括號或大括號內(nèi)的語法項目，只能選擇一個項目[]（方括號）可選項，不必輸入方括號{}（大括號）必選項，不要輸入大括號（）（小括號）語句的組成部分，必須輸入[，…,n]表示前面的項可重復(fù)n次，項之間由逗號分隔[…n]表示前面的項可重復(fù)n次，項之間由空格分隔加粗數(shù)據(jù)庫名、表名、列名、索引名、存儲過程、實用程序、數(shù)據(jù)類型名以及必須按所顯示的原樣輸入的文本<標(biāo)簽>::=語法塊的名稱。此規(guī)則用于對可在語句中的多個位置使用的過長語法或語法單元部分進行分組和標(biāo)記公司管理數(shù)據(jù)庫系統(tǒng)實例2．有效標(biāo)識符標(biāo)識符用來標(biāo)識服務(wù)器、數(shù)據(jù)庫和數(shù)據(jù)庫對象（如表、視圖、索引、過程等）。T-SQL的保留字不能作為標(biāo)識符。SQLServer的標(biāo)識符分為常規(guī)標(biāo)識符和分隔標(biāo)識符。（1）常規(guī)標(biāo)識符。第一個字符必須是下列字符之一：26個大小寫字母，以及來自其他語言的字母字符，還可以是下畫線（_）、@或者#。其他字符可以是大小寫字母或其他國家/地區(qū)字符中的十進制數(shù)字、@、$、#、_。常規(guī)標(biāo)識符不允許嵌入空格或其他特殊字符。

（2）分隔標(biāo)識符。用雙引號“”或者方括號[]分隔標(biāo)識符。在SQLServer中，以@符號開始的標(biāo)識符表示局部變量或者參數(shù)；以@@開始的標(biāo)識符表示全局變量或配置函數(shù)；以#開始的標(biāo)識符表示臨時表或過程；以##開始的標(biāo)識符表示全局臨時對象。標(biāo)識符的字符長度不能超過128個字符，臨時表標(biāo)識符的長度不能超過116個字符。3．注釋注釋有兩個作用：其一，對程序代碼的功能及實現(xiàn)方式進行簡要的解釋和說明，以便于將來對程序代碼進行維護；其二，可以把程序中暫時不用的語句加以注釋，使它們暫時不被執(zhí)行，等需要執(zhí)行這些語句時，再將它們恢復(fù)。T-SQL支持以下兩種類型的注釋。公司管理數(shù)據(jù)庫系統(tǒng)實例（1）多行注釋。使用“/*”和“*/”可以將連續(xù)書寫的多行語句設(shè)與注釋，如下所示。/*設(shè)置產(chǎn)品ID為外鍵，刪除主鍵表行時級聯(lián)刪除從表相應(yīng)行：*/（2）單行注釋。使用“--”可以將單行書寫的語句設(shè)與注釋，如下所示。

--聲明局部變量7.1.2常用函數(shù)函數(shù)對于任何程序設(shè)計語言都是非常關(guān)鍵的組成部分。SQLServer提供的函數(shù)分為聚合函數(shù)、配置函數(shù)、游標(biāo)函數(shù)、日期函數(shù)、數(shù)學(xué)函數(shù)、元數(shù)據(jù)函數(shù)、行集函數(shù)、安全函數(shù)、字符串函數(shù)、系統(tǒng)函數(shù)、文本和圖像函數(shù)幾類。一些函數(shù)還提供了獲得信息的快捷方法。函數(shù)有返回值，返回值的類型取決于所使用的函數(shù)。1．聚合函數(shù)聚合函數(shù)也稱為統(tǒng)計函數(shù)，它對一組值進行計算并返回一個數(shù)值。聚合函數(shù)經(jīng)常與SELECT語句子句一起使用。例7.1

統(tǒng)計employee表共有多少員工。實例分析：在employee表中的一行數(shù)據(jù)就是一個員工的信息，統(tǒng)計有多少員工就是統(tǒng)計employees表中有多少行。統(tǒng)計多少行使用count(*)的函數(shù)。在查詢分析器中執(zhí)行如下SQL語句。公司管理數(shù)據(jù)庫系統(tǒng)實例USEcompanyinfoGOSELECTCOUNT(*)AS'員工總?cè)藬?shù)'FROMemployeeGO執(zhí)行結(jié)果如圖7-2所示。例7.2

統(tǒng)計p_order表中訂購的所有產(chǎn)品的數(shù)量。實例分析：要統(tǒng)計p_order表所訂購產(chǎn)品的數(shù)量，就要對“數(shù)量”列計算總和，這需要使用SUM函數(shù)。圖7-2查詢employee表中有多少員工公司管理數(shù)據(jù)庫系統(tǒng)實例在查詢分析器中執(zhí)行如下SQL語句。USEcompanyinfoGOSELECTSUM(數(shù)量)FROMp_orderGO例7.3

統(tǒng)計p_order表中訂購量最少的產(chǎn)品。實例分析：統(tǒng)計產(chǎn)品訂購量最少的產(chǎn)品需要使用MIN（）函數(shù)。在查詢分析器中執(zhí)行如下SQL語句。USEcompanyinfoGOSELECTMIN(數(shù)量)FROMp_orderGO公司管理數(shù)據(jù)庫系統(tǒng)實例2．字符串函數(shù)字符串函數(shù)用于對字符串進行連接、截取等操作。表7-2列出了常用的字符串函數(shù)。常用的字符串函數(shù)及其功能字符串函數(shù)功能ASCⅡ（字符表達式）返回字符表達式最左邊字符的ASCⅡ碼CHAR（整型表達式）將一個ASCⅡ碼轉(zhuǎn)換為字符，ASCⅡ碼的范圍為0～255SPACE（整型表達式）返回由n個空格組成的字符串，n是整型表達式的值LEN（字符表達式）返回字符表達式的字符（而不是字節(jié)）數(shù)，不計算尾部的空格RIGHT（字符表達式，整型表達式）從字符表達式中返回最右邊的n個字符，n是整型表達式的值LEFT（字符表達式，整型表達式）從字符表達式中返回最左邊的n個字符，n是整型表達式的值SUBSTRING（字符表達式，起始點，n）返回字符串表達式中從“起始點”開始的n個字符STR（浮點表達式[，長度[，小數(shù)]])將浮點表達式轉(zhuǎn)換為給定長度的字符串，小數(shù)點后的位數(shù)由給定的“小數(shù)”確定LTRIM（字符表達式）去掉字符表達式的前導(dǎo)空格RTRIM（字符表達式）去掉字符表達式的尾部空格LOWER（字符表達式）將字符表達式的字母轉(zhuǎn)換為小寫字母公司管理數(shù)據(jù)庫系統(tǒng)實例字符串函數(shù)功能UPPER（字符表達式）將字符表達式的字母轉(zhuǎn)換為大寫字母REVERSE（字符表達式）返回字符表達式的逆序CHARINDEX（字符表達式1，字符表達式2，[開始位置]）返回字符表達式1在字符表達式2的開始位置，可從所給出的“開始位置”進行查找，如果沒指定開始位置，或者指定為負數(shù)或0，則默認從字符表達式2的開始位置查找REPLICATE（字符表達式，整型表達式）將字符表達式重復(fù)多次，整型表達式給出重復(fù)的次數(shù)STUFF（字符表達式1，start，length，字符表達式2）將字符表達式1中從“start”位置開始的“l(fā)ength”個字符換成字符表達2+將字符串進行連接公司管理數(shù)據(jù)庫系統(tǒng)實例例7.4

給出字符串“數(shù)據(jù)庫”字符串在“大型數(shù)據(jù)庫技術(shù)”中的位置。實例分析：該題需要給出“數(shù)據(jù)庫”在“大型數(shù)據(jù)庫技術(shù)”中的開始位置，使用CHARINDEX函數(shù)，字符表達式1為“信息”，字符表達式2為“計算機信息工程系”。從字符表達式2的開始位置查找。本題不使用表。在查詢分析器中執(zhí)行如下SQL語句。SELECTCHARINDEX('信息','計算機信息工程系')GO例7.5

計算字符串“SQLServer數(shù)據(jù)庫管理系統(tǒng)”的字符個數(shù)。實例分析：該題需要使用計算字符長度函數(shù)LEN，表示為：LEN(‘SQLServer數(shù)據(jù)庫管理系統(tǒng)')。在查詢分析器中執(zhí)行如下SQL語句。SELECTLEN('SQLServer數(shù)據(jù)庫管理系統(tǒng)')GO例7.6

顯示信息：將“Hello”顯示兩次，然后間隔10個空格，再將“World”顯示兩次。實例分析：本題使用REPLICATE函數(shù)，顯示兩次“Hello”表示為REPLICATE('HellO‘,2)；顯示兩次“World”表示為REPLICATE(‘World’,2)。間隔10個空格使用SPACE函數(shù)表示為SPACE(10)。公司管理數(shù)據(jù)庫系統(tǒng)實例在查詢分析器中執(zhí)行如下SQL語句。SELECTREPLICATE('HellO',2),SPACE(10),REPLICATE('World',2)GO執(zhí)行結(jié)果如圖7-3所示。圖7-3使用REPLICATE函數(shù)和SPACE函數(shù)公司管理數(shù)據(jù)庫系統(tǒng)實例3．日期函數(shù)日期函數(shù)用來顯示日期和時間的信息。它們處理dataUme和smaUdatatime的值，并對其進行算術(shù)運算。表7-3列出了常用的日期函數(shù)。日期函數(shù)功能GETDATE(

)返回服務(wù)器當(dāng)前系統(tǒng)的日期和時間DATENAME（日期元素，日期）返回指定日期的名稱，返回值為字符串DATEPART（日期元素，日期）返回指定日期的一部分，以整數(shù)返回DATEDIFF（日期元素，日期1，日期2）返回兩個日期間的差值并轉(zhuǎn)換為指定日期元素的形式DATEADD（日期元素，數(shù)值，日期）將日期元素加上日期產(chǎn)生新的日期YEAR（日期）返回年份(整數(shù))MONTH（日期）返回月份(整數(shù))DAY（日期）返回某月幾號的整數(shù)值GETUTCDATE(

)返回表示當(dāng)前UTC時間(世界時間坐標(biāo)或格林尼治標(biāo)準(zhǔn)時間)的日期值公司管理數(shù)據(jù)庫系統(tǒng)實例表7-3中日期元素的縮寫和取值范圍在表7-4中給出。日期元素及其縮寫和取值范圍日期元素縮寫取值范圍YEARYY1753～9999MONTHMM1～12DAYDD1～31DAYOFYEARDY1～366WEEKWK0～52WEEKDAYDW1～7HOURHH0～23MINUTEMI0～59QUARTERQQ1～4SECONDSS0～59MILLISECONDMS0～999公司管理數(shù)據(jù)庫系統(tǒng)實例例7.7

顯示服務(wù)器當(dāng)前系統(tǒng)的日期與時間。實例分析：該題需要使用GETDATE函數(shù)，表示為GETDATA()。在查詢分析器中執(zhí)行如下SQL語句。SELECTGETDATE()GO例7.8

顯示服務(wù)器當(dāng)前系統(tǒng)的月份和月份名稱。實例分析：顯示當(dāng)前系統(tǒng)的月份使用DATEPART(MONTH，GETDATE())，或者DATE-PART(MM，GETDATE())；顯示當(dāng)前系統(tǒng)的月份名稱使用DATENAME(MONTH，GETDATE())，或者DATENAME(MM，GETDATE())。在查詢分析器中執(zhí)行如下SQL語句SELECTDATEPART(MONTH，GETDATE())SELECTDATENAME(MONTH，GETDATE())GO公司管理數(shù)據(jù)庫系統(tǒng)實例執(zhí)行結(jié)果如圖7-4所示。圖7-4使用DATEPART函數(shù)和DATENAME函數(shù)公司管理數(shù)據(jù)庫系統(tǒng)實例例7.9

小張的生日為“1979/12/23”，使用日期函數(shù)計算小張的年齡。實例分析：本題計算服務(wù)器當(dāng)前系統(tǒng)時間GETDATE()與小張生日“1979/12/23”之差，并以日期元素YEAR(或YY)的形式返回，用函數(shù)表示為DATEDIFF(yy,'1979/12/23',GETDATE())。在查詢分析器中執(zhí)行如下SQL語句。SELECT'年齡'=DATEDIFF(yy,'1979/12/23',GETDATE())GO4．系統(tǒng)函數(shù)系統(tǒng)函數(shù)用來獲取SQL常用的Serrer的有關(guān)信息。表7-5列出了常用的系統(tǒng)函數(shù)。常用的系統(tǒng)函數(shù)系統(tǒng)函數(shù)功能APP_NAME()返回當(dāng)前會話的應(yīng)用程序名稱（如果應(yīng)用程序進行了設(shè)置）CASE表達式計算條件列表，并返回表達式的多個可能結(jié)果之一CAST(expressionASdata

type)將表達式顯式轉(zhuǎn)換為另一種數(shù)據(jù)類型CONVERT(data

type[(length)]，expression[，style])將表達式顯式轉(zhuǎn)換為另一種數(shù)據(jù)類型。CAST和CONVERT提供相似的功能COALESCE(expression[，…，n])返回列表清單中的第一個非空表達式DATALENGTH(expression)返回表達式所占用的字節(jié)數(shù)HOST_NAME()返回主機名稱ISDATE(expression)表達式為有效日期格式時返回1，否則返回0公司管理數(shù)據(jù)庫系統(tǒng)實例ISNULL(check

expression，replacement

value)表達式值為NULL時，用指定的值進行替換ISNUMERIC(expression)表達式為數(shù)值類型時返回1，否則返回0NEWID()生成全局唯一標(biāo)識符NULLIF(expression，expression)如果兩個指定的表達式相等，則返回空值例7.10

顯示主機名稱。實例[微軟用戶1]

分析：使用HOST_NAME()函數(shù)。在查詢分析器中執(zhí)行如下SQL語句。SELECTHOST_NAME()GO執(zhí)行結(jié)果為：HAND。例7.11

將字符串10．3456轉(zhuǎn)換為數(shù)字。實例分析：需要使用轉(zhuǎn)換函數(shù)CONVERT，轉(zhuǎn)換后的數(shù)據(jù)類型為Decimal(10，4)。在查詢分析器中執(zhí)行如下SQL語句。SELECTCONVERT(Decimal(10,4),'10.3456')GO執(zhí)行結(jié)果為：10.3456。公司管理數(shù)據(jù)庫系統(tǒng)實例5．?dāng)?shù)學(xué)函數(shù)數(shù)學(xué)函數(shù)用來對數(shù)值型數(shù)據(jù)進行數(shù)學(xué)運算。表7-6列出了常用的數(shù)學(xué)函數(shù)。常用的數(shù)學(xué)函數(shù)數(shù)學(xué)函數(shù)功能ABS（數(shù)值表達式）返回表達式的絕對值（正值）CEILING（數(shù)值表達式）返回大于或等于數(shù)值表達式值的最小整數(shù)FLOOR（數(shù)值表達式）返回小于或等于數(shù)值表達式值的最大整數(shù)，是CEILING的反函數(shù)PI(

)返回

的值3．1415926535897931POWER（數(shù)字表達式，冪）返回數(shù)字表達式值的指定次冪的值RAND（[整型表達式]）返回一個0～1之間的隨機十進制數(shù)ROUND（數(shù)值表達式，整型表達式）將數(shù)值表達式四舍五入為整型表達式所給定的精度SQRT（浮點表達式）返回一個浮點表達式的平方根公司管理數(shù)據(jù)庫系統(tǒng)實例例7.12

返回大于或等于134.3933的最小整數(shù)；返回小于或等于134.393的最大整數(shù)。實例分析：該題需要使用

CEILING

和

FLOOR

函數(shù)，分別表示為

CEILING(134．393)和FLOOR(134.393)。在查詢分析器中執(zhí)行如下SQL語句。

SELECTCEILING(134.393)SELECTFLOOR(134.393)GO

執(zhí)行結(jié)果為：135134

例7.13

計算34和的值。實例分析：該題使用POWER、SQRT函數(shù)，34表示為POWER(3，4)，表示為SQRT(16)。在查詢分析器中執(zhí)行如下SQL語句。SELECTPOWER(3,4)SELECTSQRT(16)GO執(zhí)行結(jié)果為：814.0。公司管理數(shù)據(jù)庫系統(tǒng)實例7.1.3常量與變量常量和變量是程序設(shè)計中不可缺少的元素，下面介紹常量和變量的具體使用方法。1．常量T-SQL的常量主要有以下幾種。

（1）字符串常量。字符串常量包含在單引號內(nèi)，由字母、數(shù)字字符（a～z、A～Z和0～9）以及特殊字符（如!、@和#）組成。例如，'SQLServer2005實例與應(yīng)用'。如果字符串常量中包含一個單引號，如I‘maStudent，可以使用兩個單引號表示這個字符串常量內(nèi)的單引號，即表示為：'I"maStudent'。在字符串常量前面加上字符N，則表明該字符串常量是Unicode字符串常量。例如，N'Mary'是Unicode字符串常量；'Mary'是字符串常量。

Unicode數(shù)據(jù)中的每個字符都使用2字節(jié)存儲。字符數(shù)據(jù)中的每個字符則都使用一字節(jié)進行存儲。

（2）數(shù)值常量。數(shù)值常量包括時間常量（datetime、smalldatetime）、整型常量（bigint、int、smallint、tinyint）、帶有精度的常量（decimal、numeric）、浮點型常量（float）、實型常量（real）和貨幣型常量（money、smallmoney）幾種。數(shù)值常量不需要使用引號。公司管理數(shù)據(jù)庫系統(tǒng)實例datetime：范圍在1753年1月1日～9999年12月31日的日期和時間數(shù)據(jù)。smalldatetime：范圍在1900年1月1日～2079年6月6日的日期和時間數(shù)據(jù)。bigint：范圍在?9223372036854775808（?263?1）～9223372036854775807（263?1）之間的整數(shù)。int：范圍在?2147483648（?231）～2147483647（231?1）之間的整數(shù)。smallint：范圍在?32768（?215）～32767（215?1）的整數(shù)數(shù)據(jù)。tinyint：0～255范圍內(nèi)的整數(shù)。decimal：范圍在?1038+1～1038?1之間可以帶有小數(shù)位的數(shù)值常量。例如，1

876.21。float：使用科學(xué)記數(shù)法表示?1.79E+308～1.79E+308范圍的數(shù)據(jù)。real：使用科學(xué)記數(shù)法表示?3.40E+38～3.40E+38范圍的數(shù)據(jù)。例如，101.5E6、

54.8El0等。money：貨幣常量，范為?226～226?1，存儲大小為8字節(jié)。以$作為前綴，它可以包含小數(shù)點。例如，$12.54、$768.32。smallmoney：范圍為?214748．3648～+214．7483647，存儲大小為4字節(jié)。（3）日期常量。使用特定格式的字符日期值表示，并用單引號括起來，例如：'19831231'、'1976/04/23'、'14:30:24'、'04:24PM'、'May04，1998'。

（4）uniqueidentifier常量。表示全局唯一標(biāo)識符（GUID）值的字符串?？梢允褂米址蚨M制字符串格式指定。例如，'6DF9B89F4513-4CAF-A5AD—CEB24CC90CA5'。公司管理數(shù)據(jù)庫系統(tǒng)實例2．變量

變量對應(yīng)內(nèi)存中的一個存儲空間，它與常量不同。變量的值在程序執(zhí)行過程中可能隨時有所改變。變量有兩種類型：局部變量和全局變量。（1）局部變量。局部變量是用戶在程序中定義的變量，一次只能保存一個值，它僅在定義的程序范圍內(nèi)有效。局部變量可以臨時存儲數(shù)據(jù)。局部變量名總是以@符號開始，最長為128個字符。使用DECLARE語句定義局部變量，內(nèi)容包括定義局部變量名、數(shù)據(jù)類型和長度。局部變量的初值為NULL（空）。需要使用SELECT語句或SET語句給局部變量賦值。SET語句一次只能給一個局部變量賦值；SELECT語句可以同時給一個或多個局部變量賦值。

例7.14

編寫計算50與60之和的程序。實例分析：首先需要定義3個局部變量，分別用來存儲兩個整數(shù)以及兩個整數(shù)之和。定義的3個局部變量分別為@i、@j、@sum，數(shù)據(jù)類型均為int（整型）。定義局部變量之后，需要使用SET語句或者SELECT語句給局部變量賦值。使用PRINT語句顯示局部變量的值。為了便于理解，給出了每行代碼的注釋。在查詢分析器中執(zhí)行如下SQL語句。公司管理數(shù)據(jù)庫系統(tǒng)實例DECLARE@iint,@jint,@sumint --定義3個局部變量SET@i=50 --給變量@i賦值SET@j=60 --給變量@j賦值SELECT@sum=@i+@j --將@i和@j的和賦值給變量@sumPRINT@sum --顯示@sum變量的內(nèi)容GO

（2）全局變量。全局變量以@@開頭，實際上它就是SQLServer的配置函數(shù)。對于全局變量只能使用，不能進行修改。7.1.4運算符T-SQL的運算符和其他高級語言的運算符類似，用于將變量、常量和函數(shù)連接起來并指定在一個或多個表達式中執(zhí)行的操作。表7-7列出了T-SQL的運算符。優(yōu)先級運算符類別所包含運算符1一元運算符+（正）、?（負）、～（取反）2算術(shù)運算符*（乘）、/（除）、%（取模）3算術(shù)字符串運算符+（加）、—（減）、+（連接）公司管理數(shù)據(jù)庫系統(tǒng)實例優(yōu)先級運算符類別所包含運算符4比較運算符=（等于）、>（大于）、>=（大于等于）、<（小于）、<=（小于等于）、<>（或!=，不等于）、!<（不小于）、!>（不大于）5按位運算符&（位與）、|（位或）、^（位異或）6邏輯運算符NOT（非）7邏輯運算符AND（與）8邏輯運算符ALL（所有）、ANY（任意一個）、BETWEEN（兩者之間）、EXISTS（存在）、IN（在范圍內(nèi)）、LIKE（匹配）、OR（或）、SOME（任意一個）9賦值運算符=（賦值）字符串連接運算符（+）用來連接字符串。例如，SELECT'SQLServer2005'+'數(shù)據(jù)庫技術(shù)'顯示結(jié)果為：SQLServer2005數(shù)據(jù)庫技術(shù)公司管理數(shù)據(jù)庫系統(tǒng)實例7.1.5批處理和流控語句1．批處理

批處理是由一個或多個T-SQL語句組成的，應(yīng)用程序?qū)⑦@些語句作為一個單元提交給SQLServer，并由SQLServer編譯成一個執(zhí)行計劃，然后作為一個整體來執(zhí)行。批處理的大小有一定的限制，批處理結(jié)束的符號或標(biāo)志是GO。批處理可以交互地運行或在一個文件中運行。提交給T-SQL的文件可以包含多個批處理，其中每個批處理以GO命令結(jié)束。批處理的使用需要遵守以下規(guī)則。（1）除了CREATEDATABASE（創(chuàng)建數(shù)據(jù)庫）、CREATETABLE（創(chuàng)建表）和CREATEINDEX（創(chuàng)建索引）之外的其他大多數(shù)的CREATE命令要單獨作為一個批處理。（2）調(diào)用（執(zhí)行）存儲過程時，如果它不是批處理中的第一個語句，則在其前面必須加上EXE-CUTE（執(zhí)行）。例7.15

創(chuàng)建一個顯示employee表信息的視圖V_Test，然后顯示課程表的信息。實例分析：創(chuàng)建顯示employee表信息的視圖V_Test的語句如下。CREATEVIEWV_TestASSELECT*FROMemployee公司管理數(shù)據(jù)庫系統(tǒng)實例

顯示雇員表的信息的語句為：SELECT*FROMemployee

在查詢分析器中執(zhí)行如下SQL語句。USEcompanyinfoCREATEVIEWV_TestASSELECT*FROMemployeeSELECT*FROMemployeeGO

執(zhí)行后顯示出錯信息為：‘CREATEVIEW’必須是查詢批次中的第一個語句。在CREATEVIEW語句前面增加GO，使得USEcompanyinfo為一個批處理，CREATEVIEW為另一個批處理的第一條語句。執(zhí)行如下SQL語句。USEcompanyinfoGOCREATEVIEWV_TestASSELECT*FROMemployeeSELECT*FROMemployeeGO公司管理數(shù)據(jù)庫系統(tǒng)實例執(zhí)行后顯示出錯信息為：關(guān)鍵字'SELECT'附近有語法錯誤。因為創(chuàng)建視圖要單獨作為一個批處理，所以應(yīng)在SELECT*FROMemployee語句前面加GO。修改后的SQL語句有3個批處理執(zhí)行如下SQL語句后，其結(jié)果是正確的。USEcompanyinfoGOCREATEVIEWV_TestASSELECT*FROMemployeeGOSELECT*FROMemployeeGO（3）將聲明一個局部變量和給該變量賦值的語句分別放在兩個批處理中，觀察系統(tǒng)給出的信息。DECLARE@MyVarINTGOSELECT@MyVar=33GO公司管理數(shù)據(jù)庫系統(tǒng)實例顯示出錯信息為：必須聲明標(biāo)量變量“@MyVar”。對一個局部變量而言，其聲明和賦值必須在同一個批處理中進行，正確的語句如下。DECLARE@MyVarINTSELECT@MyVar=33GO2．流控語句

流程控制語句簡稱流控語句，它與T-SQL語句一起使用可以控制程序的流程。在批處理中可以使用流控語句，也可以在存儲過程、腳本或特定的查詢內(nèi)部使用它。下面首先給出一個例題，然后再逐步介紹流控語句。例7.16

計算1+2+3+4+…+10

000的值，并顯示計算結(jié)果。實例分析：首先聲明兩個局部變量：@i和@sum，兩者均為int數(shù)據(jù)類型。其中，@i為計數(shù)單元，@sum用來保存計算結(jié)果。聲明局部變量的語句為：DECLARE@iint，@sumint。然后給兩個局部變量賦值，@i初值為1，@sum初值為0，賦值語句為：SELECT@i=1，@sum=0。該題需要采用循環(huán)程序結(jié)構(gòu)，循環(huán)終止條件為@i>10000。在查詢分析器中執(zhí)行如下SQL語句。公司管理數(shù)據(jù)庫系統(tǒng)實例DECLARE@iint,@sumintSELECT@i=1,@sum=0WHILE@i<=10000BEGINSELECT@sum=@sum+@iSELECT@i=@i+1ENDSELECT'l+2+3+4+…+10000的和'=@sumGO執(zhí)行結(jié)果如圖7-5所示。圖7-5l+2+3+4+…+10

000的計算結(jié)果公司管理數(shù)據(jù)庫系統(tǒng)實例

圖7-5l+2+3+4+…+10

000的計算結(jié)果從上面的兩個程序可以看出，流控語句可以實現(xiàn)程序的3種基本結(jié)構(gòu)：順序結(jié)構(gòu)、選擇結(jié)構(gòu)和循環(huán)結(jié)構(gòu)。下面介紹T-SQL的流控語句。（1）IF語句。IF語句用來實現(xiàn)程序的選擇結(jié)構(gòu)，其語法如下。IF邏輯表達式

|語句1|語句塊l|[ELSE

|語句2|語句塊2|]IF語句的功能是：如果邏輯表達式的條件成立（為真），則執(zhí)行語句1或語句塊1；否則，執(zhí)行語句2或語句塊2。語句塊由一個以上的SQL語句構(gòu)成，要用BEGIN和END將SQL語句括起來。ELSE部分在省略的情況下，當(dāng)邏輯表達式不成立（為假）時，執(zhí)行語句1或語句塊1中的SQL語句。（2）BEGIN…END語句。BEGIN和END用來定義語句塊，它們必須成對出現(xiàn)。它將多個SQL語句括起來，相當(dāng)于一個單一語句。（3）WHILE語句。WHILE語句用來實現(xiàn)循環(huán)結(jié)構(gòu)，其語法如下。WHILE邏輯表達式語句塊公司管理數(shù)據(jù)庫系統(tǒng)實例WHILE語句的功能是：當(dāng)邏輯表達式為真時，執(zhí)行循環(huán)體，直到邏輯表達式為假。（4）DECLARE語句。DECLARE語句用來聲明局部變量，聲明后的局部變量初值為NULL（空）。聲明局部變量的語法如下。DECLARE@variable_namedatatype[,@variable_namedatatype]…

使用SELECT語句或SET語句給局部變量進行賦值，SELECT語句一次性可以給多個變量賦值，SET語句一次只能給一個變量賦值。使用SELECT語句賦值的語法如下。SELECT{@local_variable=expression}[，…，n]

局部變量必須在同一個批處理或過程中被聲明和使用。（5）RETURN語句。RETURN語句實現(xiàn)無條件退出執(zhí)行批處理、存儲過程和觸發(fā)器。RETURN語句可以返回—個整數(shù)給調(diào)用它的過程或應(yīng)用程序。其語法如下。RETURN[整型表達式]（6）WAITFOR語句。WAITFOR語句用于延遲或暫緩程序的執(zhí)行，其語法如下。公司管理數(shù)據(jù)庫系統(tǒng)實例WAITFOR{DELAY'time'|TIME'time'}DELAY表示數(shù)據(jù)庫服務(wù)器一直處于等待狀態(tài)，直到經(jīng)過所指定的時間過去，最長可達24小時。TIME設(shè)置SQLServer等待的時間。例如，在20：00執(zhí)行SELECT*FROMemployee的語句如下。WAITFORTIME'20:00:00'SELECT*FROMemployee再如，等待10s，再執(zhí)行SELECT*FROMemployee[微軟用戶1]

的語句如下。WAITFORDELAY'00:00:10'SELECT*FROMemployee

（7）CASE語句。用于計算多個條件并為每個條件返回單個值，簡化SQL表達式。CASE語句的語法如下。CASEinput_expresslOnWHENwhen_expressionTHENresult_expression[…n]ELSEelse_result_expressionEND公司管理數(shù)據(jù)庫系統(tǒng)實例

例7.17

對產(chǎn)品進行分類顯示，要求顯示產(chǎn)品ID、產(chǎn)品類別、產(chǎn)品名。實例分析：該題使用product表。如果類別ID是1的，顯示產(chǎn)品類別“飲料”；如果類別ID是2的，顯示產(chǎn)品類別“計算機耗材”。因此，需要用CASE語句判斷類別ID的值，根據(jù)類別ID的取值不同，來顯示相應(yīng)的信息。在查詢分析器中執(zhí)行如下SQL語句。USEcompanyinfoGOSELECT產(chǎn)品ID,'產(chǎn)品類別'=CASE類別IDWHEN1THEN'飲料'WHEN2THEN'計算機耗材'ELSE'其他類產(chǎn)品'END,產(chǎn)品名FROMproductGO執(zhí)行結(jié)果如圖7-6所示。公司管理數(shù)據(jù)庫系統(tǒng)實例圖7-6使用CASE語句示例公司管理數(shù)據(jù)庫系統(tǒng)實例

編程獲取某雇員的工作業(yè)績信息例7.18

使用T-SQL編程，獲取“王孔若”的姓名、性別、出生年月及雇傭日期等信息；然后輸出“王孔若”所做訂單的明細表、訂單數(shù)目及訂單總金額。實例分析：由于訂單明細表具體信息分布在p_order表（訂單表）、product表（產(chǎn)品表）、customer表（客戶表）中，故需要用SELECT語句提取出這些表中的有用信息。所以，本題的完成涉及多個表操作。在查詢查詢器中執(zhí)行如下SQL語句。

編程獲取某雇員的工作業(yè)績信息USEcompanyinfo--定義SQLserver的變量，必須以@開頭命名變量，用DECLARE定義變量DECLARE@emp_IdintDECLARE@namevarchar(8)DECLARE@sexchar(2)DECLARE@date1datetimeDECLARE@date2datetime--為變量賦值SET@name='王孔若'--通過查詢語句將字段的值賦值給變量SELECT@emp_Id=雇員id,@sex=性別,@date1=出生日期,@date2=雇傭日期FROMemployeeWHERE姓名=@name--用PRINT語句結(jié)合函數(shù)格式化輸出變量的值公司管理數(shù)據(jù)庫系統(tǒng)實例PRINT'姓名:'+@name+'性別：'+@sex+'出生年月：'+convert(char(4),year(@date1))+'年'+convert(char(2),month(@date1))+'月'+convert(char(2),day(@date1))+'日'+'雇用日期：'+convert(char(4),year(@date2))+'年'+convert(char(2),month(@date2))+'月'+convert(char(2),day(@date2))+'日'--輸出雇員'王孔若'完成的訂單明細SELECTcustomer.公司名稱,customer.聯(lián)系人姓名,product.產(chǎn)品名,P_order.數(shù)量,P_order.訂貨日期,

employee.姓名,product.單價FROMemployee,customer,product,P_orderWHEREemployee.雇員id=@emp_IdandP_order.雇員id=employee.雇員idandP_order.產(chǎn)品id=product.產(chǎn)品idandP_order.客戶id=customer.客戶id--輸出雇員'王孔若'完成的訂單數(shù)目以及訂單總金額SELECTcount(*)as訂單數(shù)目,sum(P_order.數(shù)量*product.單價)as總金額FROMemployee,product,P_orderWHEREemployee.雇員id=@emp_IdandP_order.雇員id=employee.雇員idandP_order.產(chǎn)品id=product.產(chǎn)品id公司管理數(shù)據(jù)庫系統(tǒng)實例執(zhí)行結(jié)果如圖7-7所示。圖7-7顯示雇員工作業(yè)績信息公司管理數(shù)據(jù)庫系統(tǒng)實例編程實現(xiàn)訂購訂單

例7.19

雇員“王孔若”簽訂了一個供應(yīng)給“鹿城中學(xué)”50個優(yōu)盤的訂單，編程實現(xiàn)將訂單涉及的相關(guān)信息寫入數(shù)據(jù)庫中。實例分析：一條訂單會涉及產(chǎn)品、客戶及雇員的有關(guān)信息，且這些信息分別存放在不同的表中，所以，要將訂單涉及的相關(guān)信息寫入數(shù)據(jù)庫中，需要完成以下幾方面的操作。先要獲取該訂單的相關(guān)信息。然后在將這條訂單信息添加到訂單表（p_order）中的同時，還必須修改其他表中的信息，包括在客戶表（customer)中添加對應(yīng)于該訂單記錄對應(yīng)的客戶信息，在產(chǎn)品表（product)中修改產(chǎn)品的庫存量，其值必須為當(dāng)前庫存量的值減去剛增加的訂單記錄中包含的數(shù)量值。同理，如果某雇員取消了一個訂單，在各表中也必須修改或刪除相應(yīng)的記錄信息。在查詢分析器中執(zhí)行如下SQL語句。USEcompanyInfoDECLARE@userIdint--保存雇員IDDECLARE@max_cusIdint--保存客戶ID的最大值DECLARE@max_ordIdint--保存訂單ID的最大值DECLARE@storeProint--保存產(chǎn)品的庫存量公司管理數(shù)據(jù)庫系統(tǒng)實例DECLARE@id_productint--保存供應(yīng)產(chǎn)品的產(chǎn)品編號SELECT@max_cusId=max(客戶ID)FROMcustomerSELECT@max_cusId=@max_cusId+1--獲取新插入客戶的IDINSERTcustomerVALUES(@max_cusId,'鹿城中學(xué)','李漢明',

'','溫州市學(xué)院路號','325000')SELECT@storePro=庫存量,@id_product=產(chǎn)品IDFROMproductWHERE產(chǎn)品名='優(yōu)盤'SELECT@max_ordId=max(訂單ID)FROMP_orderSELECT@max_ordId=@max_ordId+1--獲取要插入的訂單信息的訂單IDSELECT@userId=雇員IDFROMemployeeWHERE姓名='王孔若'IF@storePro<50PRINT'庫存量不夠'ELSEBEGININSERTP_orderVALUES(@max_ordId,@id_product,

50,@userId,@max_cusId,getdate())UPDATEproductSET庫存量=庫存量-50WHERE產(chǎn)品ID=@id_productEND公司管理數(shù)據(jù)庫系統(tǒng)實例

實訓(xùn)7.4.1實訓(xùn)目的（1）編寫簡單的T-SQL語句進行基本語法練習(xí)。（2）根據(jù)項目需求分析編寫簡單的T-SQL語句，以提高系統(tǒng)的效率。7.4.2實訓(xùn)要求（1）能正確理解和使用SQLServer變量。（2）能正確理解和使用SQLServer函數(shù)。（3）能使用流程控制語句編寫順序結(jié)構(gòu)、選擇結(jié)構(gòu)和循環(huán)結(jié)構(gòu)的程序。7.4.3實訓(xùn)內(nèi)容與步驟

1．函數(shù)及基本語句（1）計算你來到人世的總天數(shù)。（2）計算年齡為77歲對應(yīng)的總天數(shù)。（3）計算出生日期為1983年12月21日的人的年齡。（4）將數(shù)值型數(shù)據(jù)轉(zhuǎn)換成字符型。（5）查詢課程表中課程類別是4個字的課程信息。（6）查詢課程表中課程名稱的第3個字是“信”的課程信息。（7）利用變量和打印語句顯示最大報名人數(shù)和最小報名人數(shù)。（8）求10！。公司管理數(shù)據(jù)庫系統(tǒng)實例2．綜合編程（1）對課程進行分類統(tǒng)計，要求顯示課程類別、課程名稱和報名人數(shù)，計算各類課程的平均報名人數(shù)。查詢結(jié)果要按照課程類別、報名人數(shù)升序排序。該題顯示效果如圖7-8所示。公司管理數(shù)據(jù)庫系統(tǒng)實例圖7-8顯示課程分類統(tǒng)計信息（2）編寫程序，用戶可查詢?nèi)我庹n程的報名人數(shù)，并把它返回給用戶。用戶調(diào)用該過程（分別用兩門課程“世界旅游”和“智能建筑”測試），如報名人數(shù)大于25，則顯示：“XX課程可以開班”，否則顯示：“抱歉，XX課程不能開班”。公司管理數(shù)據(jù)庫系統(tǒng)實例

小結(jié)本項目首先介紹了T-SQL的編程知識，著重介紹了批處理及流控語句。在此基礎(chǔ)上詳細介紹了兩個能夠提高公司管理效率的實用程序，從而進一步達到綜合編程的目的。公司管理數(shù)據(jù)庫系統(tǒng)實例公司管理數(shù)據(jù)庫系統(tǒng)中存儲過程的應(yīng)用項目8【教學(xué)目標(biāo)】（1）了解存儲過程的概念。（2）了解使用存儲過程的優(yōu)點。（3）了解系統(tǒng)存儲過程的特點及用途?！灸芰δ繕?biāo)】（1）能夠創(chuàng)建、管理存儲過程。（2）能夠掌握執(zhí)行存儲過程的方法。（3）能夠靈活運用存儲過程來提高系統(tǒng)工作效率?！緦W(xué)習(xí)導(dǎo)航】本項目在介紹了存儲過程的概念、優(yōu)點及其與視圖區(qū)別的基礎(chǔ)上，著重講解了利用ManagementStudio和T-SQL語句兩種方式創(chuàng)建、執(zhí)行無參數(shù)和有參數(shù)存儲過程的方法，并對存儲過程的重命名、修改和刪除進行了介紹。在圖8-1中，可以看到本項目在公司管理數(shù)據(jù)庫系統(tǒng)開發(fā)中的環(huán)節(jié)及位置。公司管理數(shù)據(jù)庫系統(tǒng)實例

圖8-1本項目在公司管理數(shù)據(jù)庫系統(tǒng)開發(fā)中的環(huán)節(jié)及位置【工作任務(wù)】為了完成公司管理數(shù)據(jù)庫系統(tǒng)的開發(fā)，需要創(chuàng)建相應(yīng)的存儲過程，以實現(xiàn)產(chǎn)品、產(chǎn)品訂單、客戶等信息的添加、刪除、修改和查詢等功能。公司管理數(shù)據(jù)庫系統(tǒng)實例

理論知識準(zhǔn)備8.1.1存儲過程的概念存儲過程是T-SQL語句和流程控制語句的集合，存儲過程能被編譯和優(yōu)化。當(dāng)首次執(zhí)行存儲過程時，SQLServer為其產(chǎn)生查詢計劃并將其保留在內(nèi)存中，以后在調(diào)用該存儲過程時就不必再進行編譯，這在一定程度上能改善系統(tǒng)的性能。當(dāng)客戶程序需要訪問服務(wù)器上的數(shù)據(jù)時，如果直接執(zhí)行T-SQL語句，一般要經(jīng)過如下幾個步驟。（1）將T-SQL語句發(fā)送到服務(wù)器。（2）服務(wù)器編譯T-SQL語句。（3）優(yōu)化產(chǎn)生查詢執(zhí)行計劃。（4）數(shù)據(jù)庫引擎執(zhí)行查詢計劃。（5）執(zhí)行結(jié)果返回客戶程序。使用存儲過程可以將一些固定的操作集中起來交給

SQLServer

數(shù)據(jù)庫服務(wù)器，以完成某個任務(wù)。存儲過程有3種：用戶自定義存儲過程、系統(tǒng)存儲過程、擴展存儲過程。公司管理數(shù)據(jù)庫系統(tǒng)實例8.1.2存儲過程的優(yōu)點（1）通過本地存儲、代碼預(yù)編譯和緩存技術(shù)實現(xiàn)高性能的數(shù)據(jù)操作。（2）通過通用編程結(jié)構(gòu)和過程重用實現(xiàn)編程框架。如果業(yè)務(wù)規(guī)則發(fā)生了變化，可以通過修改存儲過程來適應(yīng)新的業(yè)務(wù)規(guī)則，而不必修改客戶端應(yīng)用程序。這樣所有調(diào)用該存儲過程的應(yīng)用程序就會遵循新的業(yè)務(wù)規(guī)則。（3）通過隔離和加密的方法提高了數(shù)據(jù)庫的安全性。數(shù)據(jù)庫用戶可以通過獲得權(quán)限來執(zhí)行存儲過程，而不必授予用戶直接訪問數(shù)據(jù)庫對象的權(quán)限。這些對象將由存儲過程來進行操作。另外，存儲過程可以加密，這樣用戶就無法閱讀存儲過程中的T-SQL命令。這些安全特性將數(shù)據(jù)庫結(jié)構(gòu)和數(shù)據(jù)庫用戶隔離開來，進一步保證了數(shù)據(jù)的完整性和可靠性。8.1.3存儲過程與視圖的比較（1）可以在單個存儲過程中執(zhí)行一系列T-SQL語句。存儲過程可包含程序流、邏輯以及對數(shù)據(jù)庫查詢的T-SQL語句，而視圖中只包含SELECT語句。公司管理數(shù)據(jù)庫系統(tǒng)實例（2）視圖不能接收參數(shù)，只能返回結(jié)果集，而存儲過程可以接收參數(shù)，包括輸入、輸出參數(shù)，并能返回單個或多個結(jié)果集以及返回值，這樣大大提高了應(yīng)用的靈活性。一般，將經(jīng)常用到的多個表的連接查詢定義為視圖，而由存儲過程完成復(fù)雜的一系列處理，在存儲過程中也會經(jīng)常用到視圖。8.2.1利用ManagementStudio創(chuàng)建存儲過程利用ManagementStudio創(chuàng)建存儲過程的步驟如下。（1）展開“數(shù)據(jù)庫”→“可編程性”→“存儲過程”節(jié)點，用鼠標(biāo)右鍵單擊“存儲過程”選項，在彈出的快捷菜單中選擇“新建存儲過程”命令，如圖8-2所示。創(chuàng)建存儲過程圖8-2選擇“新建存儲過程”命令（2）打開“新建存儲過程”對話框，在查詢分析器中輸入定義存儲過程的T-SQL語句，如圖8-3所示。（3）單擊“執(zhí)行”按鈕或按【Alt】+【X】”組合鍵執(zhí)行存儲過程，結(jié)果如圖8-4所示。（4）用鼠標(biāo)右鍵單擊“存儲過程”選項，在彈出的快捷菜單中選擇“刷新”命令，查看存儲過程，如圖8-5所示。公司管理數(shù)據(jù)庫系統(tǒng)實例公司管理數(shù)據(jù)庫系統(tǒng)實例圖8-3編輯存儲過程窗口公司管理數(shù)據(jù)庫系統(tǒng)實例圖8-4創(chuàng)建存儲過程窗口公司管理數(shù)據(jù)庫系統(tǒng)實例圖8-5查看存儲過程公司管理數(shù)據(jù)庫系統(tǒng)實例8.2.2利用T-SQL語句創(chuàng)建存儲過程可以使用T-SQL語句中的CREATEPROCEDURE命令創(chuàng)建存儲過程，創(chuàng)建存儲過程前，應(yīng)該注意下列幾個事項?！?CREATEPROCEDURE語句不能與其他的SQL語句在單個批處理中組合使用?！?必須具有數(shù)據(jù)庫的CREATEPROCEDURE權(quán)限，以及對架構(gòu)（在其下創(chuàng)建過程）的ALTER權(quán)限?！?存儲過程是架構(gòu)作用域內(nèi)的對象，它們的名稱必須遵循標(biāo)識符的命名規(guī)則。● 只能在當(dāng)前數(shù)據(jù)庫中創(chuàng)建存儲過程。● 不要創(chuàng)建任何使用sp_作為前綴的存儲過程。SQLServer使用sp_前綴指定系統(tǒng)存儲過程。利用T-SQL語句創(chuàng)建存儲過程的基本語法如下。其中，各參數(shù)的意義如下。● schema_name：過程所屬架構(gòu)的名稱?！?procedure_name：新存儲過程的名稱。過程名稱必須遵循有關(guān)標(biāo)識符的命名規(guī)則，并且在架構(gòu)中必須唯一。公司管理數(shù)據(jù)庫系統(tǒng)實例CREATE{PROC|PROCEDURE}[schema_name.]procedure_name[{@parameter[type_schema_name.]data_type}[VARYING][=default][OUT|OUTPUT]][,...n][WITHENCRYPTION][WITHRECOMPILE]AS{<sql_statement>[;][...n]}[;]<sql_statement>::={[BEGIN]statements[END]}● @parameter：過程中的參數(shù)。在CREATEPROCEDURE語句中可以聲明一個或多個參數(shù)。除非定義了參數(shù)的默認值或者將參數(shù)設(shè)置為等于另一個參數(shù)，否則用戶必須在調(diào)用過程時為每個聲明的參數(shù)提供值。存儲過程最多可以有2

100個參數(shù)。通過將@符號用作第一個字符來指定參數(shù)名稱。參數(shù)名稱必須符合有關(guān)標(biāo)識符的命名規(guī)則。每個過程的參數(shù)僅用于該過程本身，其他過程中可以使用相同的參數(shù)名稱。默認情況下，參數(shù)只能代替常量表達式，而不能用于代替表名、列名或其他數(shù)據(jù)庫對象的名稱。公司管理數(shù)據(jù)庫系統(tǒng)實例● [type_schema_name.]data_type：參數(shù)以及所屬架構(gòu)的數(shù)據(jù)類型。除table之外的其他所有數(shù)據(jù)類型均可以用做存儲過程的參數(shù)。但是，cursor數(shù)據(jù)類型只能用于OUTPUT參數(shù)。可以為cursor數(shù)據(jù)類型指定多個輸出參數(shù)。● VARYING：指定作為輸出參數(shù)支持的結(jié)果集。該參數(shù)由存儲過程動態(tài)構(gòu)造，其內(nèi)容可能發(fā)生改變。僅適用于cursor參數(shù)?！?default：參數(shù)的默認值。如果定義了default值，則無須指定此參數(shù)的值，即可執(zhí)行過程。默認值必須是常量或NULL。如果過程使用帶LIKE關(guān)鍵字的參數(shù)，則可包含通配符：%、_、[]和[^]。● OUTPUT：指定參數(shù)是輸出參數(shù)。此選項的值可以返回給調(diào)用EXECUTE的語句。使用OUTPUT參數(shù)將值返回給過程的調(diào)用方。● ENCRYPTION：指示SQLServer將CREATEPROCEDURE語句的原始文本加密。● RECOMPILE：對存儲過程重新編譯。● <sql_statement>：要包含在過程中的一條或多條SQL語句?！纠?.1】使用T-SQL語句在companyinfo數(shù)據(jù)庫中創(chuàng)建一個名為p_product的存儲過程。該存儲過程返回product表中商品類別ID為“1”的所有記錄。在查詢分析器中執(zhí)行如下SQL語句。公司管理數(shù)據(jù)庫系統(tǒng)實例USEcompanyinfoGO CREATEPROCEDUREp_productASSELECT*FROMproductWHERE類別ID=1GO存儲過程創(chuàng)建成功后，保存在數(shù)據(jù)庫中。在SQLServer中可以使用EXECUTE

命令來直接執(zhí)行存儲過程。執(zhí)行存儲過程的基本語法如下。執(zhí)行存儲過程[[EXECUTE]]]{[@return_status=]{procedure_name|@procedure_name_var}[[@parameter=]{value|@variable[OUTPUT]|[DEFAULT]}[,…n]]}公司管理數(shù)據(jù)庫系統(tǒng)實例其中，各參數(shù)的含義如下?！?EXECUTE：執(zhí)行存儲過程的命令關(guān)鍵字，如果此語句是批處理中的第一條語句，可以省略此關(guān)鍵字。● @return_status：是一個可選的整型變量，保存存儲過程的返回狀態(tài)。這個變量在使用前，必須在批處理、存儲過程或函數(shù)中聲明。● procedure_name：指定執(zhí)行的存儲過程的名稱。● @procedure_name_var：定義局部變量名，代表存儲過程名?！?@parameter：是在創(chuàng)建存儲過程時定義的過程參數(shù)。調(diào)用時由value參數(shù)或@variable變量提供向存儲過程所傳遞的參數(shù)值，或使用DEFAULT關(guān)鍵字指定使用該參數(shù)的默認值，OUTPUT參數(shù)說明指定參數(shù)為返回參數(shù)?！纠?.2】使用T-SQL語句執(zhí)行例8.1中創(chuàng)建的存儲過程p_product。在查詢分析器中執(zhí)行如下SQL語句。USEcompanyinfoGOEXECp_product執(zhí)行完畢后，在查詢分析器的結(jié)果窗口中返回的結(jié)果如圖8-6所示，表示存儲過程創(chuàng)建成功并返回相應(yīng)存儲過程的結(jié)果。公司管理數(shù)據(jù)庫系統(tǒng)實例圖8-6存儲過程p_product的執(zhí)行結(jié)果公司管理數(shù)據(jù)庫系統(tǒng)實例存儲過程創(chuàng)建成功后，用戶還可以在ManagementStudio中修改存儲過程?！纠?.3】在ManagementStudio中查看存儲過程p_product的屬性。具體操作步驟如下。（1）在對象資源管理器中展開companyinfo數(shù)據(jù)庫。（2）展開“可編程性”→“存儲過程”節(jié)點，可以看到名為“p_product”的存儲過程。（3）用鼠標(biāo)右鍵單擊“p_product”存儲過程，在彈出的快捷菜單中選擇“修改”選項，如圖8-7所示，此時可以修改存儲過程的定義。公司管理數(shù)據(jù)庫系統(tǒng)實例在視圖中不能帶參數(shù)，對于數(shù)據(jù)行的查詢只能綁定在視圖定義中，程序不靈活；在存儲過程中可以帶輸入?yún)?shù)和輸出參數(shù)，從而可以提高系統(tǒng)開發(fā)的靈活性。在存儲過程中定義輸入?yún)?shù)、輸出參數(shù)，可以多次使用同一存儲過程并按用戶給出的要求查詢所需要的結(jié)果。8.4.1帶輸入?yún)?shù)的存儲過程輸入?yún)?shù)是指由調(diào)用程序向存儲過程傳遞的參數(shù)，在創(chuàng)建存儲過程時要定義輸入?yún)?shù)，在執(zhí)行存儲過程時要給出輸入?yún)?shù)的值。為了定義接收輸入?yún)?shù)的存儲過程，需要在CREATEPROCEDURE語句中聲明一個或多個變量作為參數(shù)。聲明存儲過程輸入?yún)?shù)的語法如下。帶參數(shù)的存儲過程CREATEPROCEDUREprocedure_name@parameter_namedatatype=[default][WITHENCRYPTION][WITHRECOMPILE]公司管理數(shù)據(jù)庫系統(tǒng)實例其中，各參數(shù)的含義如下。● @parameter_name：存儲過程的參數(shù)名，必須以符號@開始?！?datatype：參數(shù)的數(shù)據(jù)類型?！?default：參數(shù)的默認值，如果執(zhí)行存儲過程時未提供該參數(shù)的變量值，則使用default值。在例8.1中，存儲過程p_product只能查詢類別ID為“1”的產(chǎn)品信息。要使用戶能夠靈活地按照自己的需要查詢指定類別ID的產(chǎn)品信息，使存儲過程更加實用，查詢的類別ID應(yīng)該是可變的，這里就需要定義一個輸入?yún)?shù)?！纠?.4】使用T-SQL語句在companyinfo數(shù)據(jù)庫中創(chuàng)建一個名為p_ProductPara的存儲過程。該存儲過程能根據(jù)給定的產(chǎn)品ID，返回該類別ID對應(yīng)的所有產(chǎn)品信息。實例分析：在例8.1中，AS后的語句SELECT*FROMproductWHERE類別ID=1，將固定的類別ID“1”用輸入?yún)?shù)@category替代，寫為SELECT*FROMproductWHERE類別ID=@category，其中注意變量名@category要以@開頭。在查詢分析器中執(zhí)行如下SQL語句。公司管理數(shù)據(jù)庫系統(tǒng)實例USEcompanyinfoGOCREATEPROCEDUREp_ProductPara@categoryintASSELECT*FROMproductWHERE類別ID=@categoryGO8.4.2執(zhí)行帶有輸入?yún)?shù)的存儲過程1．使用參數(shù)名傳遞參數(shù)值在執(zhí)行存儲過程時，通過語句@parameter_name=value給出參數(shù)的傳遞值。當(dāng)存儲過程含有多個輸入?yún)?shù)時，參數(shù)值可以按任意順序指定，對于允許空值和具有默認值的輸入?yún)?shù)可以不給出參數(shù)的傳遞值。使用存儲過程參數(shù)名傳遞參數(shù)值的語法如下。EXECUTEprocedure_name[@parameter_name=value][,…,n]公司管理數(shù)據(jù)庫系統(tǒng)實例【例8.5】使用參數(shù)名傳遞參數(shù)值的方法執(zhí)行存儲過程p_ProductPara別查找產(chǎn)品ID為“1”、“2”的所有產(chǎn)品信息。在查詢分析器中執(zhí)行如下SQL語句。USEcompanyinfoGOEXECUTEp_ProductPara@category=1GOEXECUTEp_ProductPara@category=2GO圖8-8顯示了執(zhí)行帶不同參數(shù)時該存儲過程的返回結(jié)果?？梢钥闯?，使用參數(shù)后，用戶可以方便、靈活地根據(jù)需要查詢信息。2．按位置傳遞參數(shù)值在執(zhí)行存儲過程的語句中按照輸入?yún)?shù)的位置直接給出參數(shù)值。當(dāng)存儲過程含有多個輸入?yún)?shù)時，參數(shù)值的順序必須與存儲過程中定義的輸入?yún)?shù)的順序一致。按位置傳遞參數(shù)時，也可以忽略允許空值和具有默認值的輸入?yún)?shù)，但不能因此破壞輸入?yún)?shù)的指定順序。比如，在一個含有4個輸入?yún)?shù)的存儲過程中，用戶可以忽略第三和第四個輸入?yún)?shù)，但不能在忽略第三個輸入?yún)?shù)的情況下指定第四個輸入?yún)?shù)的輸入值。公司管理數(shù)據(jù)庫系統(tǒng)實例公司管理數(shù)據(jù)庫系統(tǒng)實例按位置傳遞存儲過程參數(shù)值的語法如下。EXECUTEprocedure_name[value1,value2,…]【例8.6】按位置傳遞參數(shù)值執(zhí)行存儲過程p_ProductPara，分別查找產(chǎn)品ID為“1”、“2”的所有產(chǎn)品信息。在查詢分析器中執(zhí)行如下SQL語句。USEcompanyinfoGOEXECUTEp_ProductPara1GOEXECUTEp_ProductPara2GO可以看出，按位置傳遞參數(shù)比按參數(shù)名傳遞參數(shù)值簡潔，它比較適合參數(shù)數(shù)量較少的情況。但按參數(shù)名傳遞參數(shù)值使程序可讀性增強，特別是在輸入?yún)?shù)數(shù)量較多時，建議使用按參數(shù)名傳遞參數(shù)值的方法，這樣的程序易讀、易維護。公司管理數(shù)據(jù)庫系統(tǒng)實例8.4.3帶輸出參數(shù)的存儲過程在需要從存儲過程中返回一個或多個值時，可以在創(chuàng)建存儲過程的語句中定義這些輸出參數(shù)，需要在CREATEPROCEDURE語句中使用OUTPUT

關(guān)鍵字說明是輸出參數(shù)。聲明輸出參數(shù)的語法如下。@parameter_namedatatype=[default]OUTPUT【例8.7】創(chuàng)建存儲過程p_ProductNum，它能夠根據(jù)用戶給定的類別ID統(tǒng)計具有該ID的產(chǎn)品名稱數(shù)，并將產(chǎn)品名稱數(shù)返回給用戶。在查詢分析器中執(zhí)行如下SQL語句。USEcompanyinfoGOCREATEPROCEEDUREp_ProductNum@categoryint,@ProductNumsmallintOUTPUTASSET@ProductNum=(SELECTCOUNT(*)FROMproductWHERE類別ID=@category)PRINT@ProductNumGO公司管理數(shù)據(jù)庫系統(tǒng)實例【例8.8】執(zhí)行存儲過程p_ProductNum。由于在存儲過程p_ProductNum中定義了輸入?yún)?shù)@category、輸出參數(shù)@ProductNum，所以在測試時需要先定義這兩個局部變量，并需要給輸入?yún)?shù)@category賦值。輸出參數(shù)@ProductNum不需要賦值，它從存儲過程中獲得返回值供用戶進一步使用。在查詢分析器中執(zhí)行如下SQL語句。USEcompanyinfoGODECLARE@categoryint,@ProductNumsmallintSET@category=1EXECp_ProductNum@category,@ProductNumOUTPUTSELECT@ProductNumGO存儲過程執(zhí)行結(jié)果如圖8-9所示。公司管理數(shù)據(jù)庫系統(tǒng)實例圖8-9帶輸出參數(shù)存儲過程p_ProductPara的執(zhí)行結(jié)果公司管理數(shù)據(jù)庫系統(tǒng)實例說明：這里是在SQLServer環(huán)境下進行測試的，在實際進行應(yīng)用系統(tǒng)開發(fā)時，局部變量的定義、賦值和使用往往都是在應(yīng)用程序中進行的。存儲過程p_ProductNum

中的PRINT@ProductNum語句只是為了在SQLServer

環(huán)境中顯示測試結(jié)果而設(shè)計的。8.5.1使用ManagementStudio修改存儲過程名稱通過ManagementStudio可以修改存儲過程的名稱。方法是：在ManagementStudio中用鼠標(biāo)右鍵單擊要修改的存儲過程，從彈出的快捷菜單中選擇“重命名”選項，如圖8-10所示。當(dāng)存儲過程名稱變成可輸入狀態(tài)時，可以直接修改該存儲過程的名稱。

重命名存儲過程8.5.2使用系統(tǒng)存儲過程修改存儲過程名稱修改存儲過程的名稱也可以使用系統(tǒng)存儲過程sp_name，其語法形式如下。sp_rename原存儲過程名稱，新存儲過程名稱【例8.9】使用系統(tǒng)存儲過程將p_product存儲過程的名稱修改為p_product_info。在查詢分析器中執(zhí)行如下SQL語句。公司管理數(shù)據(jù)庫系統(tǒng)實例8.6.1使用ManagementStudio修改存儲過程使用ManagementStudio可以很方便地修改存儲過程的定義。在ManagementStudio中展開“存儲過程”，用鼠標(biāo)右鍵單擊要修改的存儲過程，從彈出的快捷菜單中選擇“修改”選項，如圖8-11所示，則會出現(xiàn)與創(chuàng)建存儲過程時類似的窗口。在該窗口中，可以直接修改定義該存儲過程的T-SQL語句，如圖8-12所示。USEcompanyinfoGOsp_rename