第8章存儲過程和觸發(fā)器

8.1T-SQL語言8.2事務8.3存儲過程8.4觸發(fā)器【小結】【習題】

【課程要求】

1．熟悉存儲過程和觸發(fā)器的概念。

2．能使用函數(shù)、變量。

3．會簡單使用T-SQL語句建立和執(zhí)行存儲過程。

4．會簡單使用T-SQL語句建立觸發(fā)器?！颈菊律婕暗男枨蠓治鰞热荨?/p>

1．使用T-SQL中變量、常量、函數(shù)、分支語句、循環(huán)語句。

2．使用存儲過程：利用帶參數(shù)、帶輸入參數(shù)、帶輸入/輸出參數(shù)的存儲過程，完成對數(shù)據的增、刪、查、改。這樣可以使應用程序代碼簡單，并隱蔽數(shù)據訪問的代碼。

3．觸發(fā)器：利用觸發(fā)器實現(xiàn)關聯(lián)表，修改一個主鍵表內容，觸發(fā)外鍵表的同時修改。【課程內容】

存儲過程是將一些固定的操作集中起來，由數(shù)據庫服務器來完成特定任務的一種方法。存儲過程可以加快系統(tǒng)操作、封裝復雜操作、實現(xiàn)代碼重用，安全性高，并能減少網絡

流量。

數(shù)據庫管理員在進行數(shù)據管理或程序員進行數(shù)據庫應用程序開發(fā)時，都希望在一個表中的數(shù)據插入或刪除后，與之關聯(lián)的另一個表也能根據業(yè)務規(guī)則自動完成插入或刪除操作。這種情況使用觸發(fā)器來完成。觸發(fā)器是保證數(shù)據完整性和實施業(yè)務規(guī)則的一種有效的方法。存儲過程是通過存儲過程名來調用的，而觸發(fā)器是通過事件進行觸發(fā)而被執(zhí)行的。

本章主要介紹變量、運算符、流程控制語句、函數(shù)，事務，存儲過程的創(chuàng)建和執(zhí)行，觸發(fā)器的創(chuàng)建和刪除。

8.1T-SQL語言

Transact-SQL語言是SQLServer2005對標準SQL語言的擴充，如引入了程序設計的思想，增強了程序的流程控制語句等。因此，在Transact-SQL語言中，標準的SQL語句暢通無阻。Transact-SQL語言最主要的用途是設計服務器端能夠在后臺執(zhí)行的程序塊，如存儲過程、觸發(fā)器等。8.1.1變量

Transact-SQL中可以使用兩種變量：局部變量和全局變量。

1.局部變量

局部變量是用戶可自定義的變量，它的作用范圍僅在程序內部。局部變量在程序中通常用來儲存從表中查詢到的當選數(shù)據，或當作程序執(zhí)行過程式中的暫存變量。局部變量必須以@開頭，而且必須先用DECLARE命令說明后才可使用。其說明形式如下：

DECLARE@變量名變量類型[,…]

其中，變量類型可以是SQLServer2005支持的所有數(shù)據類型。在Transact-SQL中不像一般的程序語言中一樣使用“變量=變量值”來給變量賦值，而使用SELECT或SET命令來設定變量的值。其語法如下：

SELECT@局部變量=變量值

SET@局部變量=變量值

例8-1

聲明一個長度為8個字符的變量id，并賦值。

DECLARE@idchar(8)

SELECT@id='10010001'

2.全局變量

全局變量是SQLServer2005系統(tǒng)內部使用的變量，其使用范圍并不局限于某一程序，可被任何程序隨時調用。全局變量通常存儲一些SQLServer2005的配置設定值和效能統(tǒng)計數(shù)據。用戶可在程序中用全局變量來測試系統(tǒng)的設定值或Transact-SQL命令執(zhí)行后的狀態(tài)值。

全局變量不是由用戶的程序定義的，而是由系統(tǒng)定義和維護的，只能使用預先說明及定義的全局變量。引用全局變量時必須以“@@”開頭。局部變量的名稱不能與全局變量的名稱相同，否則會在應用程序中出錯。常用的全局變量有以下幾個。

@@DATEFIRST：返回每周第一天的數(shù)字。

@@IDENTITY：返回最后插入的標識值。

@@LANGUAGE：返回當前使用的語言名。

@@ROWCOUNT：返回受上一語句影響的行數(shù)。

@@SERVERNAME：返回運行的本地服務器名稱。

@@TRANCOUNT：返回當前連接的活動事務數(shù)。

3.注釋符

在Transact-SQL中可以使用兩種注釋符：

(1)?ANSI標準的注釋符“--”，用于單行注釋。

(2)與C語言相同的注釋符號，即“/*……*/”，可在程序中注釋多行文字?！?*”用于注釋文字的開頭，“*/”用于注釋文字的結尾。8.1.2運算符

運算符是一種符號，用來指定要在一個或多個表達式中指定的操作。SQLServer2005中使用如下幾種運算符：算術運算符、賦值運算符、位運算符、比較運算符、邏輯運算符、字符串運算符和一元運算符。

1.算術運算符

算術運算符用來在兩個表達式上執(zhí)行數(shù)學運算，這兩個表達式可以是任意兩個數(shù)值數(shù)據類型的表達式。算術運算符包括+(加)、－(減)、*(乘)、/(除)、%(模)五個。

在Transact-SQL中，“+”包含了三個方面的意義：

(1)表示正號，即在數(shù)值前添加“+”號表示該數(shù)值是一個正數(shù)。

(2)表示算術運算的加號，能將數(shù)值類型的兩個數(shù)據相加。

(3)連接兩個字符型或binary型的數(shù)據，這時的“+”號叫做字符串串聯(lián)運算符。

2.賦值運算符

Transact-SQL有一個賦值運算符，即等號(=)。

例8-2

下面的代碼創(chuàng)建了SNo變量。然后用賦值運算符將SNo設置成一個由表達式返回的值。

DECLARE@SNochar(8)

SET@SNo='07303103'

3.位運算符

位運算符在兩個表達式之間執(zhí)行位操作，這兩個表達式可以是任意兩個整型數(shù)據類型的表達式。位運算符的符號及其含義如表8-1所示。位運算符的操作數(shù)可以是整型或二進制字符串數(shù)據類型中的任何數(shù)據類型(但image數(shù)據類型除外)，此外，兩個操作數(shù)不能同時為二進制字符串數(shù)據類型中的某種數(shù)據類型。

4.比較運算符

比較運算符用來測試兩個表達式是否相同。除了text、ntext或image數(shù)據類型的表達式外，比較運算符可以用于所有的表達式。比較運算符的符號及其含義如表8-2所示。比較運算的結果是布爾數(shù)據類型，它有三種值：TRUE、FALSE和NULL。返回布爾數(shù)據類型的表達式被稱為布爾表達式。

和其他SQLServer2005數(shù)據類型不同，不能將布爾數(shù)據類型指定為表列或變量的數(shù)據類型，也不能在結果集中返回布爾數(shù)據類型。

在WHERE子句中使用帶有布爾數(shù)據類型的表達式可以篩選出符合搜索條件的行，也可以在流程控制語言語句(例如IF和WHILE)中使用布爾表達式。

5.邏輯運算符

邏輯運算符用來對某個條件進行測試，以獲得其真實情況。邏輯運算和比較運算一樣，返回帶有TRUE或FALSE值的布爾數(shù)據類型。邏輯運算符的符號及其含義如表8-3所示。

6.一元運算符

一元運算符只對一個表達式執(zhí)行操作，這個表達式可以是數(shù)字數(shù)據類型中的任何一種數(shù)據類型，如表8-4所示。8.1.3流程控制語句

SQL語言使用的流程控制命令與常見的程序設計語言類似，主要有以下幾種控制命令。

1.?BEGIN…END

BEGIN…END的語法格式如下：

BEGIN

<命令行或程序塊>

END

BEGIN…END用來設定一個程序塊，將BEGIN…END內的所有程序視為一個單元執(zhí)行。BEGIN…END經常在條件語句(IF…ELSE)中使用。在BEGIN…END中可嵌套另外的BEGIN…END來定義另一個程序塊。

2.?IF…ELSE

IF…ELSE的語法格式如下：

IF<條件表達式>

<命令行或程序塊>

[ELSE[條件表達式]

<命令行或程序塊>]

其中，<條件表達式>可以是各種表達式的組合，但表達式的值必須是邏輯值“真”或“假”。ELSE子句是可選的，最簡單的IF語句沒有ELSE子句部分。IF…ELSE用來判斷當某一條件成立時執(zhí)行某段程序，條件不成立時執(zhí)行另一段程序。如果不使用程序塊，那么IF或ELSE只能執(zhí)行一條命令。IF…ESLE可以進行嵌套，在Transact-SQL中最多可嵌套32級。例8-3

從StudentScore數(shù)據中求出學號為‘07303103’?的同學的平均成績，如果此平均成績大于或等于60分，則輸出“pass”信息。

IF(SELECTAVG(SSScore)FROMStudentScoreWHERESNo='07303103'GROUPBYSNo)>=60

BEGIN

PRINT'pass'

END

3.?CASE

CASE命令有兩種語句格式。

1)格式1

CASE<運算式>

WHEN<運算式>THEN<運算式>

…

WHEN<運算式>THEN<運算式>

[ELSE<運算式>]

END該語句的執(zhí)行過程是：將CASE后面表達式的值與各WHEN子句中表達式的值進行比較，如果二者相等，則返回THEN后的表達式的值，然后跳出CASE語句，否則返回ELSE子句中的表達式的值。ELSE子句是可選項。當CASE語句中不包含ELSE子句時，如果所有比較失敗時，CASE語句返回NULL。例8-4

從學生表StudentInformation中，選取SNo，SSex，如果SSex為“男”則輸出“M”；如果SSex為女則輸出“F”。

USEStudentScoreManager

SELECTSNo,SSex=

CASESSex

WHEN'男'THEN'M'

WHEN'女'THEN'F'

END

FROMStudentInformation

執(zhí)行結果如圖8-1所示。圖8-1例8-4執(zhí)行結果

2)格式2

CASE

WHEN<條件表達式>THEN<運算式>

…

WHEN<條件表達式>THEN<運算式>

[ELSE<運算式>]

END

該語句的執(zhí)行過程是：首先測試WHEN后的表達式的值，如果其值為真，則返回THEN后面的表達式的值；否則測試WHEN子句中表達式的值。如果所有WHEN子句后的表達式的值都為假，則返回ELSE后的表達式的值。如果在CASE語句中沒有ELSE子句，則CASE表達式返回NULL。注意：CASE命令可以嵌套在SQL命令中。

例8-5

從StudentScore表中查詢所有同學選課成績情況，凡成績?yōu)榭照呔敵觥拔纯肌保∮?0分的輸出“不及格”，60～70分的輸出“及格”，70～90分的輸出“良好”，大于或等于90分的輸出“優(yōu)秀”。

USEStudentScoreManager

SELECTSSno,SSCID,SSScore=

CASE

WHENSSScoreISNULLTHEN'未考'

WHENSSScore<60THEN'不及格'

WHENSSScore>=60ANDSSScore<70THEN'及格'

WHENSSScore>=70ANDSSScore<90THEN'良好'

WHENSSScore>=90THEN'優(yōu)秀'

END

FROMStudentScore

執(zhí)行結果如圖8-2所示。圖8-2例8-5執(zhí)行結果

4.?WHLIE…CONTINUE…BREAK

WHILE…CONTINUE…BREAK的語法格式如下：

WHILE<表達式>

BEGIN

<命令行或程序塊>

[BREAK]

[CONTINUE]

[命令行或程序塊]

END

WHILE命令在設定的條件成立時會重復執(zhí)行命令行或程序塊；CONTINUE命令可以讓程序跳過CONTINUE命令之后的語句，回到WHILE循環(huán)的第一行，繼續(xù)進行下一次循環(huán)；BREAK命令則讓程序完全跳出循環(huán)，結束WHILE命令的執(zhí)行。WHILE語句也可以嵌套。例8-6

用WHILE語句使得StudentScore表中的平均成績在60分以上。

USEStudentScoreManager

WHILE(SELECTAVG(SSScore)FROMStudentScore)<60

BEGIN

UPDATEStudentScoreSETSSScore=SSScore+1WHERESSSCOREISNOTNULL

END

5.?WAITFOR

WAITFOR的語法格式如下：

WAITFOR{DELAY<'時間'>|TIME<'時間'>

WAITFOR命令用來暫時停止程序執(zhí)行，直到所設定的等待時間已過或所設定的時間已到，再繼續(xù)往下執(zhí)行。其中“時間”必須為datetime類型的數(shù)據，但不能包括日期。

參數(shù)說明：

DELAY：用來設定等待的時間，最多可達24小時。

TIME：用來設定等待結束的時間點。

例8-7等待1小時2分3秒后執(zhí)行SELECT語句。

WAITFORDELAY'01:02:03'

SELECT*FROMStudentScore

6.?RETURN

RETURN的語法格式如下：

RETURN([整數(shù)值])

RETURN命令用于結束當前程序的執(zhí)行，返回到上一個調用它的程序或其他程序。在括號內可指定一個返回值；如果沒有指定返回值，則SQLServer系統(tǒng)會根據程序執(zhí)行的結果返回一個內定值，如表8-5所示。如果運行過程中產生了多個錯誤，則SQLServer2005系統(tǒng)將返回絕對值最大的內定值；但如果此時定義了返回值，則返回用戶定義的值。RETURN語句不能返回NULL值。8.1.4常用函數(shù)

1．算數(shù)函數(shù)

算數(shù)函數(shù)可對數(shù)據類型為整型、浮點型、實型、貨幣型和smallmoney型的類進行操作，它的返回值是6位小數(shù)。如果使用出錯，則返回NULL值，并顯示警告信息?？梢栽赟ELECT語句的SELECT和WHERE子句及表達式中使用算數(shù)函數(shù)。Transact-SQL中的算數(shù)函數(shù)如表8-6所示。例8-8

求指定角度的正弦值。

DECLARE@anglefloat

SET@angle=45.175643

SELECT'TheSINoftheangleis:'+CONVERT(varchar,SIN(@angle))

執(zhí)行結果為：

TheSINoftheangleis:0.929607

2．字符串函數(shù)

字符串函數(shù)對二進制數(shù)據、字符串和表達式執(zhí)行不同的運算。字符串函數(shù)作用于char、varchar、binary和varbinary數(shù)據類型，以及可以隱式轉換為char或varchar的數(shù)據類型?？梢栽赟ELECT語句的SELECT和WHERE子句及表達式中使用字符串函數(shù)。常見的字符串函數(shù)有以下幾種：

1)字符轉換函數(shù)

(1)?ASCII函數(shù)：ASCII函數(shù)返回字符表達式最左端字符的ASCII碼值，其語法如下：

ASCII(<character_expression>)

在ASCII函數(shù)中，純數(shù)字的字符串可不用“‘’?”括起來，但含其他字符的字符串必須用“‘’?”括起來，否則會出錯。

例8-9

求字符A的ASCII值。

SELECTASCII('A')

執(zhí)行結果為：

65

(2)?CHAR函數(shù)：CHAR函數(shù)用于將ASCII碼轉換為字符，其語法格式如下：

CHAR(<integer_expression>)

如果沒有輸入0～255之間的ASCII碼值，則CHAR函數(shù)會返回一個NULL值。例8-10

求66對應的ASCII字符。

SELECTCHAR(66)

執(zhí)行結果為：

B

(3)?LOWER函數(shù)：LOWER函數(shù)把字符串全部轉換為小寫，其語法格式如下：

LOWER(<character_expression>)例8-11

將字符串'STUDENT'轉換為小寫。

SELECTLOWER('STUDENT')

執(zhí)行結果為：

student

(4)?UPPER函數(shù)：UPPER函數(shù)把字符串全部轉換為大寫，其語法格式如下：

UPPER(<character_expression>)例8-12

將字符串'student'轉換為大寫。

SELECTUPPER('student')

執(zhí)行結果為：

STUDENT

(5)?STR函數(shù)：STR函數(shù)把數(shù)值型數(shù)據轉換為字符型數(shù)據，其語法格式如下：

STR(<float_expression)>[,length[，<decimal>]])自變量length和decimal必須是非負值，length指定返回的字符串長度；decimal指定返回的小數(shù)位數(shù)。如果沒有指定長度，則length的缺省值為10，decimal缺省值為0。小數(shù)位數(shù)大于decimal值時，STR函數(shù)將其下一位四舍五入。指定長度應大于或等于數(shù)字的符號位數(shù)、小數(shù)點前的位數(shù)、小數(shù)點位數(shù)和小數(shù)點后的位數(shù)之和。如果<float_expression>小數(shù)點前的位數(shù)超過了指定的長度，則返回指定長度的整數(shù)。例8-13

將3124轉換為字符型數(shù)據。

SELECTSTR(3124)

執(zhí)行結果為：

3124

2)去空格函數(shù)

(1)?LTRIM函數(shù)：LTRIM函數(shù)把字符串頭部的空格去掉，其語法格式如下：

LTRIM(<character_expression>)

例8-14

使用LTRIM刪除字符變量中的前導空格。

DECLARE@string_to_trimvarchar(60)

SET@string_to_trim='Fivespacesareatthebeginningofthisstring.'

SELECT'Hereisthestringwithouttheleadingspaces:'+

LTRIM(@string_to_trim)執(zhí)行結果為：

Hereisthestringwithouttheleadingspaces:Fivespacesareatthebeginningofthisstring.

(2)?RTRIM函數(shù)：RTRIM函數(shù)把字符串尾部的空格去掉，其語法格式如下：

RTRIM(<character_expression>)

例8-15

使用RTRIM刪除字符變量中的尾隨空格。

DECLARE@string_to_trimvarchar(60)

SET@string_to_trim='Fourspacesareaftertheperiodinthissentence.'

SELECT'Hereisthestringwithouttheleadingspaces:'+CHAR(13)+RTRIM(@string_to_trim)執(zhí)行結果為：

Hereisthestringwithouttheleadingspaces:Fourspacesareaftertheperiodinthissentence.

在許多情況下，往往需要得到頭部和尾部都沒有空格字符的字符串，這時可將上兩個函數(shù)嵌套使用。

3)取字符串函數(shù)

(1)?LEFT函數(shù)：LEFT函數(shù)返回部分字符串，其語法格式如下：

LEFT(<character_expression>，<integer_expression>)

LEFT函數(shù)返回的子串是從字符串最左邊起到第integer_expression個字符的部分。若integer_expression為負值，則返回NULL值。

例8-16

取出'STUDENT'?前三個字符。

SELECTLEFT(‘STUDENT’,3)

執(zhí)行結果為：

STU

(2)?RIGHT函數(shù)：RIGHT函數(shù)返回部分字符串，其語法格式如下：

RIGHT(<character_expression>，<integer_expression>)

RIGHT函數(shù)返回的子串是從字符串右邊第integer_expression個字符起到最后一個字符的部分。若integer_expression為負值，則返回NULL值。

例8-17

取出'STUDENT'?后三個字符。

SELECTRIGHT('STUDENT',3)

執(zhí)行結果為：

ENT

(3)?SUBSTRING函數(shù)：SUBSTRING函數(shù)返回部分字符串，其語法格式如下：

SUBSTRING(<expression>，<starting_position>，length)

SUBSTRING函數(shù)返回的子串是從字符串左邊第starting_position個字符起length個字符的部分。其中表達式可以是字符串、二進制串或含字段名的表達式。SUBSTRING函數(shù)不能用于text和image數(shù)據類型。

例8-18

求出字符串'Iamastudent'?左起第8個字符起7個字符的部分。

SELECTSUBSTRING('Iamastudent',8,7)

執(zhí)行結果為：

student

4)字符串比較函數(shù)

(1)?CHARINDEX函數(shù)：CHARINDEX函數(shù)返回字符串中某個指定的子串出現(xiàn)的開始位置，其語法格式如下：

CHARINDEX(<substring_expression>，<expression>)

其中，substring_expression是所要查找的字符表達式，expression可為字符串也可以為列名表達式。如果沒有發(fā)現(xiàn)子串，則返回0值。此函數(shù)不能用于text和image數(shù)據類型。

例8-19

找出'student'?中字符'd'?出現(xiàn)的位置。

SELECTCHARINDEX('d','student')執(zhí)行結果為：

4

(2)?PATINDEX函數(shù)：PATINDEX函數(shù)返回字符串中某個指定的子串出現(xiàn)的開始位置，其語法格式如下：

PATINDEX(<%substring_expression%>，<column_name>)

其中，子串表達式前后必須有百分號“%”，否則返回值為0。

與CHARINDEX函數(shù)不同的是，PATINDEX函數(shù)的子串可以使用通配符，且此函數(shù)可用于char、varchar和text數(shù)據類型。

3．數(shù)據類型轉換函數(shù)

在一般情況下，SQLServer2005會自動完成數(shù)據類型的轉換，例如，可以直接將字符數(shù)據類型或表達式與datetime數(shù)據類型或表達式比較；當表達式中用了integer、smallint或tinyint時，SQLServer2005也可將integer數(shù)據類型或表達式轉換為smallint數(shù)據類型或表達式，這稱為隱式轉換。如果不能確定SQLServer2005是否能完成隱式轉換或者使用了不能隱式轉換的其他數(shù)據類型，那么就需要使用數(shù)據類型轉換函數(shù)做顯式轉換了。此類函數(shù)有兩個：CAST和CONVERT。

1)?CAST函數(shù)

CAST函數(shù)的語法格式如下：

CAST(<expression>AS<data_type>[length])

2)?CONVERT函數(shù)

CONVERT函數(shù)的語法格式如下:

CONVERT(<data_type>[，length]，<expression>[，style])

參數(shù)說明：

(1)?data_type為SQLServer系統(tǒng)定義的類型。用戶自定義的數(shù)據類型不能在此使用。

(2)?length用于指定數(shù)據的長度，缺省值為30。把char或varchar類型轉換為諸如int或smallint這樣的integer類型，結果必須是帶正號(+)或負號(－)數(shù)值。text類型到char或varchar類型的轉換最多為8000個字符，即char或varchar數(shù)據類型的最大長度。image類型存儲的數(shù)據轉換到binary或varbinary類型，最多為8000個字符。把整數(shù)值轉換為money或smallint類型時，按定義的國家的貨幣單位來處理，如人民幣、美元、英鎊等。bit類型的轉換把非零值轉換為1，并仍以bit類型存儲。試圖轉換到不同長度的數(shù)據類型，會截斷轉換值并在轉換后顯示“+”，以標識發(fā)生了這種截斷。

(3)用CONVERT函數(shù)的style選項能以不同的格式顯示日期和時間。style是將datetime和smalldatetime數(shù)據轉換為字符串時所選用的由SQLServer系統(tǒng)提供的轉換樣式編號，不同的樣式編號有不同的輸出格式，如表8-7所示。例8-20

將10.6486轉換為整型。

SELECTCAST(10.6496ASint)

執(zhí)行結果為：

10

例8-21

將'02-27-2009'?轉換為datetime類型。

SELECTCONVERT(datetime,'02-27-2009')

GO

執(zhí)行結果為：

2009-02-2700:00:00.000

4.日期函數(shù)

日期函數(shù)用來操作datetime和smalldatetime類型的數(shù)據執(zhí)行算術運算。與其他函數(shù)一樣，可以在SELECT語句的SELECT和WHERE子句以及表達式中使用日期函數(shù)。其使用方法如下：

日期函數(shù)(參數(shù))

其中，參數(shù)的個數(shù)隨函數(shù)的不同而不同。

1)?DAY函數(shù)

DAY函數(shù)的語法格式如下：

DAY(<date_expression>)

DAY函數(shù)返回date_expression中的日期值。

例8-22

找出'02-27-2009'?的日期值。

SELECTDAY('02-27-2009')

執(zhí)行結果為：

27

2)?MONTH函數(shù)

MONTH函數(shù)的語法格式如下：

MONTH(<date_expression>)

MONTH函數(shù)返回date_expression中的月份值。

與DAY函數(shù)不同的是，MONTH函數(shù)的參數(shù)為整數(shù)時，一律返回整數(shù)值1，即SQLServer認為它是1900年1月。

例8-23

找出'02-27-2009'?的月份值。

SELECTMONTH('02-27-2009')

執(zhí)行結果為：

2

3)?YEAR函數(shù)

YEAR函數(shù)返回date_expression中的年份值。

在使用日期函數(shù)時，其年份值應在1753～1999年之間，這是SQLServer系統(tǒng)所能識別的年份范圍，否則會出現(xiàn)錯誤。

例8-24

找出'02-27-2009'?的年份值。

SELECTYEAR('02-27-2009')

執(zhí)行結果為：

2009

4)?DATEADD函數(shù)

DATEADD函數(shù)的語法格式如下：

DATEADD(<datepart><number><date>)

DATEADD函數(shù)返回指定日期date加上指定的額外日期間隔number產生的新日期。參數(shù)“datepart”在日期函數(shù)中經常被使用，它用來指定構成日期類型數(shù)據的各組件，如年、季、月、日、星期等，其取值如表8-8所示。例8-25

求日期'02-27-2009'?兩天后的日期。

SELECTDATEADD(dayofyear,2,'02-27-2009')

執(zhí)行結果為：

2009-03-0100:00:00.000

5)?DATEDIFF函數(shù)

DATEDIFF函數(shù)語法如下：

DATEDIFF(<date_part>，<date1>，<date2>)

DATEDIFF函數(shù)返回兩個指定日期在datepart方面的不足之處，即date2超過date1的差距值，其結果是一個帶有正負號的整數(shù)值。對不同的datepart，DATEDIFF函數(shù)所允許的最大差距值不一樣，如datepart為millisecond時，DATEDIFF函數(shù)所允許的最大差距值為24天20小時30分23秒647毫秒。例8-26

求日期'02-27-2009'?和'03-01-2009'?之間相差幾天。

SELECTDATEDIFF(dayofyear,'02-27-2009','03-01-2009')

執(zhí)行結果為：

2

6)?DATENAME函數(shù)

DATENAME函數(shù)的語法格式如下：

DATENAME(<datepart>，<date>)

DATENAME函數(shù)必須以字符串的形式返回日期的指定部分，此部分由datepart指定。

例8-27

求'02-27-2009'?是該年的第幾天。

SELECTDATENAME(dayofyear,'02-27-2009')

執(zhí)行結果為：

58

7)?DATEPART函數(shù)

DATEPART函數(shù)的語法格式如下：

DATEPART(<datepart>，<date>)

DATEPART函數(shù)以整數(shù)值的形式返回日期的指定部分，此部分由datepart指定。

DATEPART(dd,date)等同于DAY(date)。

DATEPART(mm,date)等同于MONTH(date)。

DATEPART(yy,date)等同于YEAR(date)。

例8-28

分別求出'02-27-2009'?的日期、月份和年份。

SELECTDATEPART(dd,'02-27-2009')

執(zhí)行結果為：

28

SELECTDATEPART(mm,'02-27-2009')

執(zhí)行結果為：

2

SELECTDATEPART(yy,'02-27-2009')

執(zhí)行結果為：

2009

8)?GETDATE()

GETDATE函數(shù)的語法格式如下：

GETDATE()

GETDATE函數(shù)以datetime的缺省格式返回系統(tǒng)當前的日期和時間，它常作為其他函數(shù)或命令的參數(shù)使用。

例8-29

求系統(tǒng)當前時間。

SELECTGETDATE()

執(zhí)行結果為：

2009-02-2710:57:35.5775.?text函數(shù)和image函數(shù)

1)?TEXTPTR函數(shù)

TEXTPTR函數(shù)的語法格式如下：

TEXTPTR(<column>)

(1)?TEXTPTR()函數(shù)返回一個指向存儲文本的第一個數(shù)據庫頁的指針。

(2)其返回值是一個varbinary(16)類型的二進制字符串。

(3)如果數(shù)據類型為text、ntext或image的列沒有賦予初值，則TEXTPTR()函數(shù)返回一個NULL指針。

2)?TEXTVALID函數(shù)

TEXTVALID函數(shù)的語法格式如下：

TEXTVALID(<'table.column'>，<text_pointer>)

TEXTVALID函數(shù)用于檢查指定的文本指針是否有效。如果有效，則返回1；無效則返回0。如果列未被賦值，則返回NULL值。

6.用戶自定義函數(shù)

從SQLServer2000開始，用戶可以自定義函數(shù)。在SQLServer2000中用戶自定義函數(shù)作為一個數(shù)據庫對象來管理?？梢允褂肧SMS或Transact-SQL命令來創(chuàng)建、修改和刪除自定義函數(shù)。

8.2事務

事務是一個邏輯工作單元。SQLServer2005提供了幾種自動的可以通過編程來完成的機制，包括事務日志、SQL事務控制語句，以及事務處理運行過程中通過鎖定保證數(shù)據完整性的機制。當用戶對數(shù)據庫并發(fā)訪問時，為了確保事務完整性和數(shù)據庫一致性，需要使用鎖定。事務和鎖是兩個緊密聯(lián)系的概念。通過事務、批和鎖的使用，還可以監(jiān)測系統(tǒng)以及優(yōu)化物理數(shù)據庫。作業(yè)是一種多步執(zhí)行的事務。8.2.1事務概述

關系型數(shù)據庫有4個顯著的特征：安全性、完整性、檢測性和并發(fā)性。數(shù)據庫的安全性就是要保證數(shù)據庫中數(shù)據的安全，防止未授權用戶隨意修改數(shù)據庫中的數(shù)據。完整性是數(shù)據庫的一個重要特征，也是保證數(shù)據庫中的數(shù)據切實有效、防止錯誤、實現(xiàn)商業(yè)規(guī)則的一種重要機制。在數(shù)據庫中，區(qū)別所保存的數(shù)據是無用的垃圾還是有價值的信息，主要是依據數(shù)據庫的完整性是否健全，即實體完整性、域完整性和參考完整性。發(fā)現(xiàn)任何影響系統(tǒng)性能的因素和瓶頸，采取切合實際的策略，解決問題，提高系統(tǒng)的性能。并發(fā)性是用來解決多個用戶對同一數(shù)據進行操作時的問題。特別是對于網絡數(shù)據庫來說，這個特點更加突出。提高數(shù)據庫的處理速度，單單依靠提高計算機的物理速度是不夠的，還必須充分考慮數(shù)據庫的并發(fā)性問題，提高數(shù)據庫并發(fā)性的效率。那么如何保證并發(fā)性呢？在SQLServer2005中，通過使用事務和鎖機制，可以解決數(shù)據庫的并發(fā)性問題。

在SQLServer2005中，事務要求處理時必須滿足ACID原則，即原子性(A)、一致性(C)、隔離性(I)和持久性(D)。

原子性：原子性也稱為自動性，是指事務必須執(zhí)行一個完整的工作，要么執(zhí)行全部數(shù)據的操作，要么全部不執(zhí)行。

一致性：一致性是指當事務完成時，所有的數(shù)據必須具有一致的狀態(tài)。隔離性：也稱為獨立性，是指并行事務的修改必須與其他并行事務的修改相互獨立。一個事務處理的數(shù)據，要么是其他事務執(zhí)行之前的狀態(tài)，要么是其他事務執(zhí)行之后的狀態(tài)，但不能處理其他正在處理的數(shù)據。

持久性：是指當一個事務完成之后，將影響永久性地存于系統(tǒng)中，即事務的操作將寫入數(shù)據庫中。

事務的這種機制保證了一個事務或者提交后成功執(zhí)行，或者提交后失敗回滾，二者必居其一，因此，事務對數(shù)據的修改具有可恢復性，即當事務失敗時，它對數(shù)據的修改都會恢復到該事務執(zhí)行前的狀態(tài)。而使用一般的批處理，則有可能出現(xiàn)有的語句被執(zhí)行，而另一些語句沒有被執(zhí)行的情況，從而有可能造成數(shù)據不一致。事務開始之后，事務所有的操作都陸續(xù)寫到事務日志中。這些任務操作在事務日志中記錄一個標志，用于表示執(zhí)行了這種操作。當取消這種事務時，系統(tǒng)自動執(zhí)行這種操作的反操作，保證系統(tǒng)的一致性。系統(tǒng)自動生成一個檢查點機制，這個檢查點周期地發(fā)生。檢查點的周期是系統(tǒng)根據用戶定義的時間間隔和系統(tǒng)活動的頻度由系統(tǒng)自動計算出來的時間間隔。檢查點周期地檢查事物日志。如果在事務日志中，事務全部完成，那么檢查點將事務提交到數(shù)據庫中，并且在事務日志中做一個檢查點提交標記；如果在事務日志中，事務沒有完成，那么檢查點不將事務日志中的事務提交到數(shù)據庫中，并且在事務日志中做一個檢查點未提交標記。8.2.2事務的類型

根據事務的設置和用途的不同，SQLServer2005將事務分為多種類型。

1.根據系統(tǒng)的設置分類

根據系統(tǒng)的設置，SQLServer2005將事務分為兩種類型：系統(tǒng)提供的事務和用戶定義的事務，分別簡稱為系統(tǒng)事務和用戶定義事務。

1)系統(tǒng)事務

系統(tǒng)提供的事務是指在執(zhí)行某些語句時，一條語句就是一個事務。但是要明確，一條語句的對象既可能是表中的一行數(shù)據，也可能是表中的多行數(shù)據，甚至是表中的全部數(shù)據。

因此，只有一條語句構成的事務也可能包含了多行數(shù)據的處理。

系統(tǒng)提供的事務語句如下：ALTERTABLE、CREATE、DELETE、DROP、FETCH、GRANT、INSERT、OPEN、REBOKE、SELECT、UPDATE、TRUNCATETABLE，這些語句本身就構成了一個事務。例8-30

使用CREATETABLE創(chuàng)建一個表。

CREATETABLEstudent

(IDchar(10),

Namechar(6),

Sexchar(2)

)

說明：這條語句本身就構成了一個事務。這條語句由于沒有使用條件限制，那么這條語句就是創(chuàng)建包含3個列的表。要么創(chuàng)建全部成功，要么全部失敗。

2)用戶定義事務

在實際應用中，大多數(shù)的事務都是用戶定義事務。在開發(fā)應用程序時，可以使用BEGINTRANSACTION語句來定義明確的用戶定義事務。在使用用戶定義的事務時，一定要注意事務必須由明確的結束語句來結束。如果不使用明確的結束語句來結束，那么系統(tǒng)可能把從事務開始到用戶關閉連接之間的全部操作都作為一個事務來對待。事務的明確結束可以使用以下兩個語句中的一個：COMMIT語句和ROLLBACK語句。COMMIT語句是提交語句，將全部完成的語句明確地提交到數(shù)據庫中；ROLLBACK語句是取消語句，該語句將事務的操作全部取消，即表示事務操作失敗。還有一種特殊的用戶定義事務，這就是分布式事務。如上例的事務是在一個服務器上的操作，其保證的數(shù)據完整性和一致性是指一個服務器上的完整性和一致性。但是，如果是一個比較復雜的環(huán)境，可能有多臺服務器，那么要保證在多臺服務器環(huán)境中事務的完整性和一致性，就必須定義一個分布式事務。在這個分布式事務中，所有的操作都可以涉及對多個服務器的操作，當這些操作都成功時，所有這些操作都提交到相應服務器的數(shù)據庫中；如果這些操作中有一個操作失敗，那么這個分布式事務中的全部操作都將被取消。

2.根據運行模式分類

根據運行模式，SQLServer2005將事務分為4種類型：自動提交事務、顯式事務、隱式事務和批處理級事務。

1)自動提交事務

自動提交事務是指每條單獨的語句都是一個事務。

2)顯式事務

顯式事務指每個事務均以BEGINTRANSACTION語句顯式開始，以COMMIT

TRANSACTION或ROLLBACK

TRANSACTION語句顯式結束。例8-31

向StudentInformation表中添加兩條記錄，如果成功則將結果顯示出來，如果失敗則顯示錯誤信息。

USEStudentScoreManager

BEGINTRANSACTION

DECLARE@errorCountint

SET@errorCount=0

INSERTINTOStudentInformation

VALUES('07303105','aa','男')

SET@errorCount=@errorCount+@@error

INSERTINTOStudentInformation

VALUES('07303105','aa','男')

SET@errorCount=@errorCount+@@error

SELECT@errorCount

IF(@errorCount>0)

BEGIN

PRINT'插入時出錯'

ROLLBACKTRANSACTION

END

ELSE

BEGIN

SELECT*FROMStudentInformation

COMMITTRANSACTION

END

3)隱式事務

隱式事務指在前一個事務完成時新事務隱式啟動。使用SETIMPLICIT_TRANSACTIONSON語句啟動隱式事務模式，使用SETIMPLICIT_TRANSACTIONSOFF語句可以關閉隱式事務模式。使用COMMITTRANSACTION和ROLLBACKTRANSACTION語句可以結束事務。

例8-32

將姓名“aa”修改為“王五”。

USEStudentScoreManager

SETIMPLICIT_TRANSACTIONSON

DECLARE@errorCountint

SET@errorCount=0

UPDATEStudentInformation

SETSName='王五'WHERESName='aa'

SET@errorCount=@errorCount+@@error

IF(@errorCount>0)

BEGIN

PRINT'修改時出錯'

ROLLBACKTRANSACTION

END

ELSE

BEGIN

SELECT*FROMStudentInformation

COMMITTRANSACTION

END

SETIMPLICIT_TRANSACTIONSOFF

4)批處理級事務

該事務只能應用于多個活動結果集(MARS)。在MARS會話中啟動的T-SQL顯式或隱式事務變?yōu)榕幚砑壥聞?。當批處理完成時，沒有提交或回滾的批處理級事務自動由SQLServer語句集合分組后，形成單個的邏輯工作單元。

8.3存儲過程

存儲過程(StoredProcedure)是一組為了完成特定功能的SQL語句集，這些SQL語句集經編譯后存儲在數(shù)據庫中。用戶通過指定存儲過程的名字并給出參數(shù)(如果該存儲過程帶有參數(shù))來執(zhí)行它。在SQLServer的系列版本中，存儲過程分為兩類：系統(tǒng)提供的存儲過程和用戶自定義的存儲過程。系統(tǒng)提供的存儲過程主要存儲在master數(shù)據庫中，并以sp_為前綴。系統(tǒng)存儲過程主要是從系統(tǒng)表中獲取信息，從而為系統(tǒng)管理員管理SQLServer提供支持。通過系統(tǒng)存儲過程，SQLServer中的許多管理性或信息性的活動(如了解數(shù)據庫對象、數(shù)據庫信息)都可以被順利有效地完成。盡管系統(tǒng)存儲過程被放在master數(shù)據庫中，但是仍可以在其他數(shù)據庫中對其進行調用，在調用時不必在存儲過程名前加上數(shù)據庫名。當創(chuàng)建一個新數(shù)據庫時，一些系統(tǒng)存儲過程會在新數(shù)據庫中自動創(chuàng)建。用戶自定義存儲過程是由用戶創(chuàng)建并能完成某一特定功能(如查詢用戶所需數(shù)據信息)的存儲過程。本節(jié)中所涉及到的存儲過程主要是指用戶自定義存儲過程。8.3.1創(chuàng)建存儲過程

在SQLServer2005中創(chuàng)建一個存儲過程有兩種方法：一種是使用SQL命令CREATEPROCEDURE；另一種是使用圖形化管理工具SSMS。用SQL命令創(chuàng)建存儲過程是一種較為快速的方法，但對于初學者，使用SSMS更易理解，更為簡單。

創(chuàng)建存儲過程時，需要確定存儲過程的三個組成部分：

(1)所有的輸入參數(shù)以及傳給調用者的輸出參數(shù)。

(2)被執(zhí)行的針對數(shù)據庫的操作語句，包括調用其他存儲過程的語句。

(3)返回給調用者的狀態(tài)值,以指明調用是成功還是失敗。

1.用CREATEPROCEDURE命令創(chuàng)建存儲過程

用CREATEPROCEDURE命令能夠創(chuàng)建存儲過程。在創(chuàng)建存儲過程之前應該考慮到以下幾個方面：

(1)在一個批處理中，CREATEPROCEDURE語句不能與其他SQL語句合并在一起。

(2)數(shù)據庫所有者具有默認的創(chuàng)建存儲過程的權限，它可把該權限傳遞給其他的用戶。

(3)存儲過程作為數(shù)據庫對象，其命名必須符合命名規(guī)則。

(4)只能在當前數(shù)據庫中創(chuàng)建屬于當前數(shù)據庫的存儲過程。用CREATEPROCEDURE創(chuàng)建存儲過程的語法規(guī)則如下：

CREATEPROCEDUREprocedure_name[;number]

[{@parameterdata_type}

[=default][OUTPUT]

][,…n]

[WITH

{RECOMPILE|ENCRYPTION|RECOMPILE，ENCRYPTION}]

[FORREPLICATION]

[ASsql_statement]參數(shù)說明：

(1)?procedure_name是要創(chuàng)建存儲過程的名字，它后面跟一個可選項number。number是一個整數(shù)，用來區(qū)別一組同名的存儲過程。存儲過程的命名必須符合命名規(guī)則。在一個數(shù)據庫中或對其所有者而言，存儲過程的名字必須唯一。

(2)?@parameter是存儲過程的參數(shù)。在CREATEPROCEDURE語句中，用它可以聲明一個或多個參數(shù)，當有多個參數(shù)時，各參數(shù)間用逗號隔開。在調用該存儲過程時，用戶必須給出所有的參數(shù)值(除非定義了參數(shù)的缺省值)。若參數(shù)的形式以@parameter=value出現(xiàn)，則參數(shù)的次序可以不同；否則用戶給出的參數(shù)值必須與參數(shù)列表中參數(shù)的順序保持一致。若某一參數(shù)以@parameter=value的形式給出，那么其他參數(shù)也必須以該形式給出。一個存儲過程至多有1024個參數(shù)。

(3)?data_type是參數(shù)的數(shù)據類型。在存儲過程中，所有的數(shù)據類型(包括text和image)都可以被用作參數(shù)的數(shù)據類型；參數(shù)的最大數(shù)目沒有限制。

(4)?default是指參數(shù)的缺省值。如果定義了缺省值，那么即使不給出參數(shù)值，該存儲過程仍可被調用。缺省值必須是常數(shù)或者是空值。

(5)?OUTPUT表明該參數(shù)是一個返回參數(shù)。用OUTPUT參數(shù)可以向調用者返回信息。text類型參數(shù)不能用做OUTPUT參數(shù)。

(6)?RECOMPILE指明SQLServer并不保存該存儲過程的執(zhí)行計劃，該存儲過程每執(zhí)行一次都要重新編譯。

(7)?ENCRYPTION表明SQLServer加密了syscomments表，該表的text字段是包含有CREATEPROCEDURE語句的存儲過程文本。使用該關鍵字無法通過查看syscomments表來查看存儲過程內容。

(8)?FORREPLICATION選項指明為復制創(chuàng)建的存儲過程不能在訂閱服務器上執(zhí)行，只有在創(chuàng)建過濾存儲過程時(僅當進行數(shù)據復制時過濾存儲過程才被執(zhí)行)，才使用該選項。

(9)?AS指明該存儲過程將要執(zhí)行的動作。

(10)?sql_statement是包含在存儲過程中的SQL語句(數(shù)量和類型不限)。

另外，一個存儲過程的最大尺寸為128MB，用戶定義的存儲過程必須創(chuàng)建在當前數(shù)據庫中。

例8-33

在StudentScoreManagement數(shù)據庫中，創(chuàng)建一個名稱為myrpic的存儲過程，該存儲過程的功能是從數(shù)據表StudentInformation中查詢所有男同學的信息。

USEStudentScoreManager

GO

CREATEPROCEDUREmyrpicAS

SELECT*FROMStudentInformationWHERESSex=‘男’

執(zhí)行結果如圖8-3所示。圖8-3例8-33執(zhí)行結果

例8-34

定義具有參數(shù)的存儲過程。在StudentScoreManager數(shù)據庫中，創(chuàng)建一個名為insertRecord的過程，該存儲過程的功能是向StudentInformation數(shù)據表中插入一條記錄，新記錄的值由參數(shù)提供。

USEStudentScoreManager

GO

CREATEPROCEDUREinsertRecord

(

@SNochar(6),

@SNamechar(20),

@SSexchar(2)

)

AS

INSERTINTOStudentInformationVALUES('07303104','張建','男')

GO

執(zhí)行命令：

execinsertRecord@sno='07303112',@sname='張建',@ssex='男'

結果如圖8-4所示。圖8-4例8-34執(zhí)行結果例8-35

定義具有參數(shù)默認值的存儲過程。在StudentScoreManager數(shù)據庫中，創(chuàng)建一個名稱為insertRecordDefa的存儲過程，該存儲過程的功能是向ProfessionInformation數(shù)據表中插入一條記錄，新記錄的值由參數(shù)提供。如果未提供系別PID的值，則由參數(shù)的默認值代替。

USEStudentScoreManager

GO

CREATEPROCEDUREinsertRecordDefa

(

@PIDchar(2),

@PNamenvarchar(14)

)

AS

INSERTINTOProfessionInformationVALUES(@PID,@PName)

GO

執(zhí)行命令：

execinsertRecordDefa@pid=defult,@pname=‘電子商務’

結果如圖8-5所示。圖8-5例8-35執(zhí)行結果例8-36定義能夠返回值的存儲過程。在StudentScoreManager數(shù)據庫中，創(chuàng)建一個名稱為Query_Student的存儲過程。該存儲過程的功能是從數(shù)據表StudentInformation中根據學號查詢某一同學的姓名和性別。

USEStudentScoreManager

GO

CREATEPROCEDUREQuery_Student

(

@SNochar(8),

@SNamechar(50)OUTPUT,

@SSexchar(2)OUTPUT

)

AS

SELECT@SNo=SNo,@SSex=SSex

FROMStudentInformation

WHERESNo=@SNo

GO

2.使用SSMS創(chuàng)建存儲過程

使用SSMS創(chuàng)建存儲過程的步驟如下。

(1)啟動SSMS，登錄到要使用的服務器，如圖8-6所示。圖8-6啟動SSMS

(2)在SSMS的左窗格中，展開要創(chuàng)建存儲過程的數(shù)據庫文件夾，選擇【存儲過程】文件夾，此時在右窗格中顯示該數(shù)據庫的所有存儲過程，如圖8-7所示。圖8-7展開存儲過程

(3)用右鍵單擊【存儲過程】文件夾。在彈出的快捷菜單中選擇【新建存儲過程】，打開【存儲過程屬性】對話框，如圖8-8所示。

(4)在【文本】編輯框中輸入存儲過程的正文內容。

(5)單擊【執(zhí)行】按鈕，運行新建的存儲過程，并檢查語法。圖8-8新建存儲過程8.3.2執(zhí)行存儲過程

存儲過程被創(chuàng)建以后，它的名字存儲在【可編程性】中；它的源代碼也存放在【可編程性】中?？梢酝ㄟ^SQLServer提供的系統(tǒng)存儲過程來查看關于用戶創(chuàng)建的存儲過程信息。

1.使用SSMS查看存儲過程的源代碼

通過SSMS管理工具可以查看存儲過程的源代碼，其操作如下：

(1)啟動SSMS，登錄到要使用的服務器。

(2)單擊需要查看源代碼的存儲過程，單擊右鍵，選擇【修改】，如圖8-9所示。打開的存儲過程源代碼窗口如圖8-10所示。

圖8-9查看存儲過程源代碼圖8-10存儲過程源代碼

2.使用sp_helptext存儲過程查看存儲過程的源代碼

sp_helptext語法格式如下：

sp_helptext存儲過程名稱

例8-37

要查看數(shù)據庫StudentScoreManager是否是存儲過程myrtle的源代碼，則執(zhí)行：

sp_helptextmyrtle

執(zhí)行結果如圖8-11所示。圖8-11例8-37執(zhí)行結果注意：如果在創(chuàng)建存儲過程時使用了WITHENCRYPTION選項，那么無論是使用SSMS還是系統(tǒng)存儲過程sp_helptext，都無法查看到存儲過程的源代碼。

3.重新命名存儲過程

修改存儲過程的名字使用系統(tǒng)存儲過程sp_rename。其命令格式為：

sp_rename原存儲過程名,新存儲過程名

例8-38

將存儲過程insertrecord修改為newproc1，其語句為：

sp_renameinsertrecord,newproc1

執(zhí)行結果如圖8-12所示。

另外，通過SSMS也可修改存儲過程的名字，其操作過程與Windows下修改文件名字的操作類似。首先選中需修改名字的存儲過程，然后右擊鼠標，在彈出菜單中選取【重命名】選項，最后輸入新存儲過程的名字。圖8-12例8-38執(zhí)行結果

4.刪除存儲過程

刪除存儲過程使用DROP命令。DROP命令可將一個或多個存儲過程或者存儲過程組從當前數(shù)據庫中刪除。其語法規(guī)則為：

DROPPROCEDURE{procedure}[,…n]

例8-39

如將存儲過程mypro從數(shù)據庫中刪除，則執(zhí)行：

DROPPROCEDUREmypro

5.執(zhí)行存儲過程

執(zhí)行已創(chuàng)建的存儲過程使用EXECUTE命令，其語法如下：

[EXECUTE]

{[@return_status=]

{procedure_name[;number]|@procedure_name_var}

[[@parameter=]{value|@variable[OUTPUT]|[DEFAULT][,…n]

[WITHRECOMPILE]

參數(shù)說明：

@return_status：可選的整型變量，用來存儲存儲過程向調用者返回的值。

@procedure_name_var：變量名，用來代表存儲過程的名字。例8-40

根據變量學生的學號，來查詢該學生的學號、姓名、所有課程的成績及課程名，并求該學生的平均成績。

USEStudentScoreManager

GO

IFEXISTS(SELECT*FROMsysObjectsWHEREname=‘up_selStudentScoreBySno’)

DROPPROCup_selStudentScoreBySno

GO

CREATEPROCup_selStudentScoreBySno

@SNochar(8),

@avgNointOUTPUT

AS

SELECTss.SSNo,si.SName,ci.CName,ss.SSScoreFROMStudentScoreASss

INNERJOINStudentInformationASsi

ONss.SSNo=si.SNoINNERJOINCourseInformationASci

ONss.SSCID=ci.CID

WHEREss.SSNo=@SNo

SELECTAVG(SSScore)FROMStudentScoreWHERESSNo=@SNo

GO

DECLARE@SNochar(8)

DECLARE@avgNoint

SET@SNo=‘07303101’

EXECup_selStudentScoreBySno@SNo,@avgNoOUTPUT

SELECT@avgNo

8.4觸發(fā)器

觸發(fā)器是一種特殊類型的存儲過程，它不同于前面介紹的存儲過程。觸發(fā)器主要是通過事件進行觸發(fā)而被執(zhí)行的；而存儲過程可以通過存儲過程名字被直接調用。當對某一表進行諸如UPDATE、INSERT、DELETE這些操作時，SQLServer就會自動執(zhí)行觸發(fā)器所定義的SQL語句，從而確保對數(shù)據的處理必須符合由這些SQL語句所定義的規(guī)則。

觸發(fā)器的主要作用就是它能夠實現(xiàn)由主鍵和外鍵所不能保證的復雜的參照完整性和數(shù)據的一致性。除此之外，觸發(fā)器還有其他許多不同的功能：

(1)強化約束(Enforcerestriction)：觸發(fā)器能夠實現(xiàn)比CHECK語句更為復雜的約束。

(2)跟蹤變化(Auditingchanges)：觸發(fā)器可以偵測數(shù)據庫內的操作，從而不允許數(shù)據庫中未經許可的指定更新和變化。

(3)級聯(lián)運行(Cascadedoperation)：觸發(fā)器可以偵測數(shù)據庫內的操作，并自動地級聯(lián)影響整個數(shù)據庫的各項內容。例如，某個表上的觸發(fā)器中包含有對另外一個表的數(shù)據操作(如刪除、更新、插入)，而該操作又導致該表上觸發(fā)器被觸發(fā)。

(4)存儲過程的調用(Storedprocedureinvocation)：為了響應數(shù)據庫更新，觸發(fā)器可以調用一個或多個存儲過程，甚至可以通過外部過程的調用而在DBMS(數(shù)據庫管理系統(tǒng))本身之外進行操作。

由此可見，觸發(fā)器可以解決高級形式的業(yè)務規(guī)則或復雜行為限制，以及實現(xiàn)定制記錄等一些方面的問題。例如，觸發(fā)器能夠找出某一表在數(shù)據修改前后狀態(tài)發(fā)生的差異，并根據這種差異執(zhí)行一定的處理。此外，一個表的同一類型(INSERT、UPDATE、DELETE)的多個觸發(fā)器能夠對同一種數(shù)據操作采取多種不同的處理?？傮w而言，觸發(fā)器性能通常比較低。當運行觸發(fā)器時，系統(tǒng)處理的大部分時間花費在參照其他表的這一處理上，但這些表既不在內存中也不在數(shù)據庫設備上，而刪除表和插入表總是位于內存中。觸發(fā)