第八章數(shù)據(jù)庫(kù)編程

數(shù)據(jù)庫(kù)系統(tǒng)概論第八章數(shù)據(jù)庫(kù)編程第八章數(shù)據(jù)庫(kù)編程第一節(jié)T-SQL編程基礎(chǔ)第二節(jié)游標(biāo)第三節(jié)存儲(chǔ)過程第四節(jié)自定義函數(shù)第五節(jié)觸發(fā)器教學(xué)目標(biāo)掌握變量、運(yùn)算、系統(tǒng)函數(shù)、控制語(yǔ)句存儲(chǔ)過程、自定義函數(shù)、觸發(fā)器了解游標(biāo)重點(diǎn)存儲(chǔ)過程、自定義函數(shù)難點(diǎn)自定義函數(shù)、觸發(fā)器T-SQL編程基礎(chǔ)標(biāo)示符注釋語(yǔ)句表達(dá)式流程控制語(yǔ)句系統(tǒng)函數(shù)標(biāo)示符標(biāo)示符分類常規(guī)標(biāo)識(shí)符（嚴(yán)格遵守標(biāo)識(shí)符格式規(guī)則）界定標(biāo)識(shí)符（引號(hào)”或方括號(hào)[]）標(biāo)識(shí)符格式規(guī)則(1)字母或_、@、＃開頭的字母數(shù)字或_、@、$序列(2)不與保留字相同(3)長(zhǎng)度小于128(4)不符合規(guī)則的標(biāo)識(shí)符必須加以界定（雙引號(hào)””或方括號(hào)[]）注釋語(yǔ)句注釋語(yǔ)句是對(duì)程序代碼的說明或暫時(shí)禁用，是程序代碼中不編譯執(zhí)行的語(yǔ)句。單行注釋--例：--求‘3-105’課程的平均分多行注釋/**/例：/*作者：

創(chuàng)建時(shí)間：*/表達(dá)式數(shù)據(jù)類型整數(shù)數(shù)據(jù)、字符數(shù)據(jù)、貨幣數(shù)據(jù)、日期和時(shí)間數(shù)據(jù)、二進(jìn)制字符串等變量局部變量局部變量是用戶定義，必須以@開頭，在程序內(nèi)聲明，并只能在該程序內(nèi)使用。(1)局部變量的聲明

DECLATE@<局部變量名><數(shù)據(jù)類型>[,…n](2)局部變量的賦值

SET|SELECT@<局部變量名>=<表達(dá)式>例1-1創(chuàng)建一個(gè)@myvar變量，然后將一個(gè)字符串值放在變量中，最后輸出@myvar變量的值。DECLARE@myvarchar(20)select@myvar='Thisisatest'SELECT@myvar例1-2用SET語(yǔ)句和SELECT語(yǔ)句為局部變量賦值。DECLARE@var1datetime,@var2char(10)SET@var1=getdate()SELECT@var2=convert(char(10),@var1,102)Select@var2--顯示@var2全局變量全局變量是SQLServer系統(tǒng)內(nèi)部使用的變量，以@@開頭。全局變量不是由用戶的程序定義的，它們是在服務(wù)器級(jí)定義的。用戶只能使用預(yù)先定義的全局變量。例1-3用全局變量查看SQLServer的版本、當(dāng)前所使用的SQLServer服務(wù)器的名稱以及所使用的服務(wù)名稱等信息。print'目前所用SQLServer的版本信息如下:'print@@VERSIONprint'目前SQLServer服務(wù)器名稱為：'+@@SERVERNAMEprint'目前所用服務(wù)器為：'+@@SERVICENAME運(yùn)算符SQLServer2005的運(yùn)算符和其他高級(jí)語(yǔ)言類似，用于指定要在一個(gè)或多個(gè)表達(dá)式中執(zhí)行的操作，將變量、常量和函數(shù)連接起來(lái)。優(yōu)先級(jí)運(yùn)算符類別所包含運(yùn)算符1一元運(yùn)算符+（正）、-（負(fù)）、~（取反）2算術(shù)運(yùn)算符*（乖）、/（除）、%（取模）3算術(shù)字符串運(yùn)算符+（加）、-（減）、+（連接）4比較運(yùn)算符=（等于）、>（大于）、>=（大于等于）、<（小于）、<=（小于等于）、<>（或!=不等于）、!<（不小于）、!>（不大于）5按位運(yùn)算符&（位與）、|（位或）、^（位異或）6邏輯運(yùn)算符not（非）7邏輯運(yùn)算符and（與）8邏輯運(yùn)算符all（所有）、any（任意一個(gè)）、between（兩者之間）、exists（存在）、in（在范圍內(nèi)）、like（匹配）、or（或）、some（任意一個(gè)）9賦值運(yùn)算符=（賦值）流程控制語(yǔ)句一、選擇結(jié)構(gòu)二、循環(huán)結(jié)構(gòu)三、等待語(yǔ)句四、返回語(yǔ)句一、選擇結(jié)構(gòu)IFELSEIF…ELSE語(yǔ)句用來(lái)判斷當(dāng)某一條件成立時(shí)執(zhí)行某段程序，條件不成立時(shí)執(zhí)行另一段程序。其中，ELSE子句是可選的，SQLServer允許嵌套使用IF…ELSE語(yǔ)句，而且嵌套層數(shù)沒有限制。語(yǔ)法格式

IF<布爾表達(dá)式><SQL語(yǔ)句>|<語(yǔ)句塊>[ELSE<SQL語(yǔ)句>|<語(yǔ)句塊>]例：查找有沒有學(xué)號(hào)為200215120的學(xué)生，有的話顯示學(xué)生信息，沒有顯示沒找到。IFEXISTS(SELECT*FROMSTUDENTWHERESNO=‘200215120’)BEGINSELECT*FROMSTUDENTWHERESNO=‘200215120’ENDELSEPRINT‘沒找到！’--EXISTS子查詢?nèi)绻硬樵兘Y(jié)果非空返回真，否則返回假CASE語(yǔ)句CASE語(yǔ)句可以計(jì)算多個(gè)條件式，并將其中一個(gè)符合條件的結(jié)果表達(dá)式返回。CASE語(yǔ)句按照使用形式的不同，可以分為簡(jiǎn)單CASE語(yǔ)句和搜索CASE語(yǔ)句。簡(jiǎn)單CASE語(yǔ)句CASE表達(dá)式

WHEN表達(dá)式的值1THEN返回表達(dá)式1WHEN表達(dá)式的值2THEN返回表達(dá)式2…ELSE返回表達(dá)式nEND搜索式CASEWHEN邏輯表達(dá)式1THEN返回表達(dá)式1WHEN邏輯表達(dá)式2THEN返回表達(dá)式2…ELSE返回表達(dá)式nEND例：根據(jù)這個(gè)國(guó)家人口數(shù)據(jù)，統(tǒng)計(jì)亞洲和北美洲的人口數(shù)量。應(yīng)該得到下面這個(gè)結(jié)果【例】顯示學(xué)生的學(xué)號(hào)以及學(xué)生成績(jī)(按優(yōu)、良、中、及格、不及格、未知顯示)。

SELECTSno學(xué)號(hào),成績(jī)=CASEWHENGrade>=90THEN'優(yōu)'WHENGrade>=80THEN'良'

WHENGrade>=70THEN'中'

WHENGrade>=60THEN'及格'

WHENGrade>=0THEN'不及格'

ELSE'未知'ENDFROMsc練習(xí)‘1’代表男，‘2’代表女，請(qǐng)統(tǒng)計(jì)各個(gè)國(guó)家的男女人數(shù)二、循環(huán)結(jié)構(gòu)設(shè)置重復(fù)執(zhí)行SQL語(yǔ)句或語(yǔ)句塊的條件。只要指定的條件為真，就重復(fù)執(zhí)行語(yǔ)句?？梢允褂肂REAK和CONTINUE關(guān)鍵字在循環(huán)內(nèi)部控制WHILE循環(huán)中語(yǔ)句的執(zhí)行。WHILE邏輯表達(dá)式BeginT-SQL語(yǔ)句組

[break]/*終止整個(gè)語(yǔ)句的執(zhí)行*/[continue]/*結(jié)束一次循環(huán)體的執(zhí)行*/END例求1~10的和DECLARE@Xint,@sumintSET@X=0SET@sum=0WHILE@x<10BEGINSET@X=@X+1SET@sum=@sum+@XPRINT‘sum='+convert(char(2),@sum)--類型轉(zhuǎn)換函數(shù)convertEND三、等待語(yǔ)句等待語(yǔ)句掛起一個(gè)程序中語(yǔ)句的執(zhí)行，直到指定的某一時(shí)間點(diǎn)到來(lái)或在一定的時(shí)間間斷之后才繼續(xù)執(zhí)行。語(yǔ)法格式：其中，時(shí)間間隔以及時(shí)間均為datetime類型，格式為“hh:mm:ss”，分別說明等待的時(shí)間長(zhǎng)度和時(shí)間點(diǎn)，在time內(nèi)不能指定日期。WAITFORDELAY'<時(shí)間間隔>‘|TIME'<時(shí)間>'例1設(shè)置等待一小時(shí)后執(zhí)行查詢。beginwaitfordelay'1:00:00'select*fromsend例2設(shè)置到十點(diǎn)整執(zhí)行查詢。beginwaitfortime'10:00:00'select*fromsend四、返回語(yǔ)句RETURN語(yǔ)句RETURN語(yǔ)句用于無(wú)條件地終止一個(gè)查詢、存儲(chǔ)過程或者批處理，此時(shí)位于RETURN語(yǔ)句之后的程序?qū)⒉粫?huì)被執(zhí)行。語(yǔ)法格式：RETURN[integer_expression]系統(tǒng)函數(shù)標(biāo)量函數(shù)函數(shù)分類解釋配置函數(shù)返回當(dāng)前的配置信息游標(biāo)函數(shù)返回有關(guān)游標(biāo)的信息日期和時(shí)間函數(shù)對(duì)日期和時(shí)間輸入值進(jìn)行處理數(shù)學(xué)函數(shù)對(duì)作為函數(shù)參數(shù)提供的輸入值執(zhí)行計(jì)算元數(shù)據(jù)函數(shù)返回有關(guān)數(shù)據(jù)庫(kù)和數(shù)據(jù)庫(kù)對(duì)象的信息安全函數(shù)返回有關(guān)用戶和角色的信息字符串函數(shù)對(duì)字符串（char或

varchar）輸入值執(zhí)行操作系統(tǒng)函數(shù)執(zhí)行操作并返回有關(guān)SQLServer中的值、對(duì)象和設(shè)置的信息系統(tǒng)統(tǒng)計(jì)函數(shù)返回系統(tǒng)的統(tǒng)計(jì)信息文本和圖像函數(shù)對(duì)文本或圖像輸入值或列執(zhí)行操作，返回有關(guān)這些值的信息日期和時(shí)間函數(shù)函數(shù)參數(shù)功能DATEADD（datepart,number,date）以datepart指定的方式，返回date加上number之和DATEDIFF（datepart,date1,date2）以datepart指定的方式，返回date2與date1之差DATENAME（datepart,date）返回日期date中datepart指定部分所對(duì)應(yīng)的字符串DATEPART（datepart,date）返回日期date中datepart指定部分所對(duì)應(yīng)的整數(shù)值DAY（date）返回指定日期的天數(shù)GETDATE（）返回當(dāng)前的日期和時(shí)間MONTH（date）返回指定日期的月份數(shù)YEAR（date）返回指定日期的年份數(shù)例：查詢和學(xué)號(hào)為108的同學(xué)同年出生的所有學(xué)生的Sno、Sname和Sbirthday列selectsno,sname,sbirthdayfromstu.studentwhere

year(sbirthday)=(selectyear(sbirthday)fromstu.studentwheresno='108')selectsno,sname,sbirthdayfromstu.studentwhere

datepart(year,sbirthday)=(selectdatepart(year,sbirthday)fromstu.studentwheresno='108')字符串函數(shù)1、字符串轉(zhuǎn)換函數(shù)ASCII（字符串）函數(shù)返回字符表達(dá)式最左端字符的ASCII碼值。CHAR（整數(shù)表達(dá)式）函數(shù)用于將ASCII碼轉(zhuǎn)換為字符。LOWER（字符串）函數(shù)把字符串全部轉(zhuǎn)換為小寫。UPPER（字符串）函數(shù)把字符串全部轉(zhuǎn)換為大寫。2、去空格函數(shù)LTRIM（字符串）函數(shù)把字符串頭部的空格去掉。RTRIM（字符串）函數(shù)把字符串尾部的空格去掉。3、取子串函數(shù)LEFT(字符串,intnum)函數(shù)返回的子串是從字符串最左邊起到第num個(gè)字符的部分。若num為負(fù)值,則返回NULL值。RIGHT(字符串,intnum)函數(shù)返回的子串是從字符串最右邊起到第num個(gè)字符的部分。若num為負(fù)值,則返回NULL值。SUBSTRING(字符串,intpos,intlength）函數(shù)返回的子串是從字符串左邊第starting_position個(gè)字符起length個(gè)字符的部分。SUBSTRING（）函數(shù)不能用于TEXT和IMAGE數(shù)據(jù)類型。4、字符串比較函數(shù)CHARINDEX(<要找的子串，字符串)函數(shù)返回字符串中某個(gè)指定的子串出現(xiàn)的開始位置，如果沒有發(fā)現(xiàn)子串，則返回0值。PATINDEX(<’%substring_expression%’>，<字符串>)函數(shù)返回字符串中某個(gè)指定的子串出現(xiàn)的開始位置，其中子串表達(dá)式前后必須有百分號(hào)“%”否則返回值為0。5、字符串操作函數(shù)QUOTENAME（）函數(shù)返回被特定字符括起來(lái)的字符串REPLICATE（）函數(shù)返回一個(gè)重復(fù)character_expression指定次數(shù)的字符串REVERSE（）函數(shù)將指定的字符串的字符排列順序顛倒REPLACE（）函數(shù)返回被替換了指定子串的字符串SPACE（）函數(shù)返回一個(gè)有指定長(zhǎng)度的空白字符串STUF（）函數(shù)用另一子串替換字符串指定位置、長(zhǎng)度的子串?dāng)?shù)據(jù)類型轉(zhuǎn)換函數(shù)轉(zhuǎn)換函數(shù)有兩個(gè)：CONVERT和CAST。CAST函數(shù)允許把一個(gè)數(shù)據(jù)類型強(qiáng)制轉(zhuǎn)換為另一種數(shù)據(jù)類型，其語(yǔ)法形式為：

CAST(expressionASdata_type)CONVERT函數(shù)允許用戶把表達(dá)式從一種數(shù)據(jù)類型轉(zhuǎn)換成另一種數(shù)據(jù)類型，還允許把日期轉(zhuǎn)換成不同的樣式，其語(yǔ)法形式為：

CONVERT(data_type[(length)],expression[,style])實(shí)例：第八章數(shù)據(jù)庫(kù)編程第一節(jié)T-SQL編程基礎(chǔ)第二節(jié)游標(biāo)第三節(jié)存儲(chǔ)過程第四節(jié)自定義函數(shù)第五節(jié)觸發(fā)器第二節(jié)游標(biāo)游標(biāo)是一種能從包括多條數(shù)據(jù)記錄的結(jié)果集中每次提取一條記錄的機(jī)制。游標(biāo)的使用1.聲明游標(biāo)（變量）。2.打開游標(biāo)。3.從一個(gè)游標(biāo)中提取信息。4.關(guān)閉（釋放）游標(biāo)。1.聲明游標(biāo)語(yǔ)法格式：Insensitive指定游標(biāo)只對(duì)基本表的副本操作，游標(biāo)的任何操作不對(duì)基本表產(chǎn)生影響Scroll指定游標(biāo)推進(jìn)方向（FIRST、LAST、PRIOR、NEXT、RELATIVE、ABSOLUTE）均可用，否則只有next可用DECLARE<游標(biāo)名>[INSENSITIVE][SCROLL]CURSORFOR<SELECT語(yǔ)句>[FOR{READONLY|UPDATE[OF<列名>[,...n]]}]2.聲明游標(biāo)變量語(yǔ)法格式：例：聲明一個(gè)游標(biāo)，統(tǒng)計(jì)沒有選修課程的學(xué)生的人數(shù)DECLARE@<變量名>CURSORdeclarenum_cursorcursorfor

selectsnofromstudents.studentforREADONLY3.打開游標(biāo)游標(biāo)聲明后，如果要從游標(biāo)中讀取數(shù)據(jù)，必須打開游標(biāo)。OPEN[GLOBAL]<游標(biāo)名>|<游標(biāo)變量名>opennum_cursor;4.讀取游標(biāo)中的數(shù)據(jù)當(dāng)游標(biāo)被打開后，就可以從游標(biāo)中逐行地讀取數(shù)據(jù)。默認(rèn)情況下,指針指向第一條記錄之前

FETCH[[NEXT|PRIOR|FIRST|LAST|ABSOLUTE{n|@nvar}|RELATIVE{n|@nvar}]FROM]{{[GLOBAL]<游標(biāo)名>}|<@游標(biāo)變量>}[INTO@<變量名>[,...n]]執(zhí)行FETCH語(yǔ)句后，可通過@@FETCH_STATUS全局變量返回游標(biāo)當(dāng)前的狀態(tài)。@@FETCH_STATUS變量有三個(gè)不同的返回值:0：FETCH語(yǔ)句執(zhí)行成功。-1：FETCH語(yǔ)句執(zhí)行失敗或者行數(shù)據(jù)超出游標(biāo)數(shù)據(jù)結(jié)果集的范圍。-2：表示提取的數(shù)據(jù)不存在。declarenum_cursorcursor--聲明forselectsnofromstudents.studentforREADONLYOpennum_cursor;--打開declare@snovarchar(10),@numint--聲明變量set@num=0fetchnextfromnum_cursor--取信息into@snowhile@@fetch_status=0--檢測(cè)狀態(tài)beginifnotexists(select*fromstudents.scwheresno=@sno)set@num=@num+1fetchnextfromnum_cursorinto@snoendselect@num未選課人數(shù)CLOSEnum_cursorDEALLOCATEnum_cursor5.關(guān)閉游標(biāo)使用CLOSE命令關(guān)閉游標(biāo)處理完游標(biāo)中數(shù)據(jù)后，必須關(guān)閉游標(biāo)來(lái)釋放數(shù)據(jù)結(jié)果集和定位于數(shù)據(jù)記錄上的鎖。語(yǔ)法格式:CLOSE語(yǔ)句可以關(guān)閉游標(biāo)，但不釋放游標(biāo)的數(shù)據(jù)結(jié)構(gòu)。如果要再次使用游標(biāo)，可用OPEN命令重新打開。自動(dòng)關(guān)閉游標(biāo)CLOSE[GLOBAL]<游標(biāo)名>|@<游標(biāo)變量>6.釋放游標(biāo)用CLOSE命令關(guān)閉游標(biāo)并沒有釋放游標(biāo)占用的數(shù)據(jù)結(jié)構(gòu)。使用DEALLOCATE命令將釋放游標(biāo)占用的數(shù)據(jù)結(jié)構(gòu)，游標(biāo)使用的任何資源也隨之釋放。語(yǔ)法格式：游標(biāo)的關(guān)閉指釋放游標(biāo)的結(jié)果集所占用的資源，游標(biāo)的釋放指釋放游標(biāo)占用的所有資源，當(dāng)然也包括結(jié)果集占用的資源。DEALLOCATE[GLOBAL]<游標(biāo)名>|@<游標(biāo)變量>例：根據(jù)學(xué)生成績(jī)計(jì)算統(tǒng)計(jì)各個(gè)等級(jí)的人數(shù)：[90-100]為A,[80-89]為B[70-79]為C,[60-69]為D[0-59]為E--定義局部變量DECLARE@mygradeint,@mylevelchar(1)DECLARE@Eint,@Dint,@Cint,@Bint,@Aintselect@E=0,@D=0,@C=0,@B=0,@A=0--下面定義游標(biāo).DECLARElevel_cursorCURSORFORSELECTgradeFROMstudents.sc--下面打開游標(biāo).OPENlevel_cursor--下面從游標(biāo)中取出第一行，放到--對(duì)應(yīng)的變量中.FETCHNEXTFROMlevel_cursorINTO@mygrade--循環(huán)處理WHILE@@FETCH_STATUS=0BEGIN --計(jì)算級(jí)別.if@mygradeisnullset@E=@E+1elseif@mygrade<60set@E=@E+1elseif@mygrade<70set@D=@D+1elseif@mygrade<80set@C=@C+1elseif@mygrade<90set@B=@B+1elseset@A=@A+1 --從游標(biāo)中取下一行. FETCHNEXTFROMlevel_cursor INTO@mygradeEND--關(guān)閉游標(biāo).CLOSElevel_cursor--釋放資源DEALLOCATElevel_cursorselect@E,@D,@C,@B,@A第八章數(shù)據(jù)庫(kù)編程第一節(jié)T-SQL編程基礎(chǔ)第二節(jié)游標(biāo)第三節(jié)存儲(chǔ)過程第四節(jié)自定義函數(shù)第五節(jié)觸發(fā)器第三節(jié)存儲(chǔ)過程存儲(chǔ)過程（StoredProcedure）是一組完成特定功能的SQL語(yǔ)句集，經(jīng)編譯后存儲(chǔ)在數(shù)據(jù)庫(kù)中，用戶通過指定存儲(chǔ)過程的名字并給出參數(shù)（如果該存儲(chǔ)過程帶有參數(shù)）來(lái)執(zhí)行存儲(chǔ)過程。存儲(chǔ)過程的優(yōu)點(diǎn)：（1）存儲(chǔ)過程已在服務(wù)器注冊(cè)。（2）存儲(chǔ)過程具有安全特性。（3）存儲(chǔ)過程可以強(qiáng)制應(yīng)用程序的安全性。（4）存儲(chǔ)過程允許模塊化程序設(shè)計(jì)。（5）存儲(chǔ)過程可以減少網(wǎng)絡(luò)通信流量。第三節(jié)存儲(chǔ)過程1.創(chuàng)建存儲(chǔ)過程2.執(zhí)行存儲(chǔ)過程3.刪除存儲(chǔ)過程1.創(chuàng)建存儲(chǔ)過程語(yǔ)法：CREATE{PROC|PROCEDURE}[schema_name.]procedure_name[{@parameterdata_type}[VARYING][=default][OUTPUT]][,...n][WITHRECOMPILE|ENCRYPTION]AS{<BEGIN><sql_statement>[;][...n]<END>}例1將指定記錄插入student表createprocc_insert_student@snovarchar(10),@snamevarchar(20),@ssexvarchar(2),@sagesmallint,@sdeptvarchar(50)asbegininsertintostudents.student(sno,sname,ssex,sage,sdept)values(@sno,@sname,@ssex,@sage,@sdept)end帶輸出參數(shù)的存儲(chǔ)過程例2查詢指定學(xué)號(hào)學(xué)生的平均成績(jī)，并將平均成績(jī)返回createproc[students].[proc_avergrade]@snovarchar(10),@savgintout--輸出參數(shù)asbeginselect@savg=avg(grade)fromstudents.scwheresno=@snoend練習(xí)1、編寫一個(gè)存儲(chǔ)過程，在sc表統(tǒng)計(jì)每個(gè)學(xué)生的平均分。2、對(duì)練習(xí)1的存儲(chǔ)過程進(jìn)行改進(jìn)，添加一個(gè)輸入?yún)?shù)——學(xué)號(hào)，使存儲(chǔ)過程能根據(jù)輸入的學(xué)號(hào)計(jì)算該學(xué)生的平均分。3、在練習(xí)2的基礎(chǔ)上添加一個(gè)輸出參數(shù)——平均分，計(jì)算指定學(xué)號(hào)的平均分，然后將平均分輸出。2.執(zhí)行存儲(chǔ)過程語(yǔ)法格式：例3執(zhí)行例1的存儲(chǔ)過程例4執(zhí)行例2的存儲(chǔ)過程EXEC|EXECUTE[@return_status=][schema_name.]procedure_name[[@parameter=]{value|@variable[OUTPUT]|[DEFAULT]}][,...n]execc_insert_student'200901031','張三','男',18,'軟件學(xué)院'declare@avgintset@avg=0execc_avergrade'200215121',@avgoutselect@avg3.刪除存儲(chǔ)過程刪除存儲(chǔ)過程可以使用DROP命令，DROP命令可以將一個(gè)或者多個(gè)存儲(chǔ)過程或者存儲(chǔ)過程組從當(dāng)前數(shù)據(jù)庫(kù)中刪除，其語(yǔ)法形式如下：

dropprocedure{procedure_name}[,…n]第八章數(shù)據(jù)庫(kù)編程第一節(jié)T-SQL編程基礎(chǔ)第二節(jié)游標(biāo)第三節(jié)存儲(chǔ)過程第四節(jié)自定義函數(shù)第五節(jié)觸發(fā)器第四節(jié)自定義函數(shù)SQLServer2005支持3種類型的Transact-SQL用戶自定義函數(shù)：標(biāo)量函數(shù)、內(nèi)嵌表值函數(shù)和多語(yǔ)句表值函數(shù)。在SQLServer中使用用戶自定義函數(shù)有以下優(yōu)點(diǎn):允許模塊化程序設(shè)計(jì)。執(zhí)行速度更快。減少網(wǎng)絡(luò)流量1、標(biāo)量函數(shù)創(chuàng)建語(yǔ)法：CREATEFUNCTION[schema_name.]function_name([{@parameter_name[AS]data_type[=default]}[,...n]])RETURNSreturn_data_type[WITH<ENCRYPTION>|<SCHEMABINDING>[,...n]][AS]BEGINfunction_bodyRETURNscalar_expressionEND2、內(nèi)嵌表值函數(shù)創(chuàng)建語(yǔ)法：CREATEFUNCTION[schema_name.]function_name([{@parameter_name

data_type[=default]}[,...n]])RETURNSTABLE[WITH<function_option>[,...n]][AS]RETURN(select_stmt)3、多語(yǔ)句表值函數(shù)創(chuàng)建語(yǔ)法：CREATEFUNCTION[schema_name.]function_name([{@parameter_name

data_type[=default]}[,...n]])RETURNS@return_variableTABLE<table_type_definition>[WITH<function_option>[,...n]][AS]BEGINfunction_bodyRETURNEND例1自定義日期函數(shù)

定義一個(gè)函數(shù)返回不帶時(shí)間的日期CREATEFUNCTIONdbo.DateOnly(@datedatetime)RETURNSVARCHAR(12)ASBEGINRETURNCONVERT(VARCHAR(12),@DATE,101)ENDselectdbo.DateOnly(GETDATE())例2內(nèi)聯(lián)表值函數(shù)查看計(jì)算機(jī)系學(xué)生的成績(jī)CREATEFUNCTION[dbo].[attendance]( @sdeptvarchar(20)--系)RETURNSTABLEASRETURN(SELECTA.sno,cno,gradefromstudents.scA,students.studentBwhereA.sno=B.snoandsdept=@sdept)DECLARE@departmentCHAR(20)SET@department='is'SELECT*FROMattendance(@department)視圖、存儲(chǔ)過程和自定義函數(shù)視圖存儲(chǔ)過程自定義函數(shù)語(yǔ)句只能是SELECT語(yǔ)句可以包含程序流、邏輯以及SELECT語(yǔ)句可以包含程序流、邏輯以及SELECT語(yǔ)句輸入不能接受參數(shù)可以有輸入輸出參數(shù)有輸入?yún)?shù)返回值只能返回結(jié)果集返回值只能是整數(shù)可以返回標(biāo)量值、表典型應(yīng)用多個(gè)表格的連接查詢完成某個(gè)特定的較復(fù)雜的任務(wù)可以完成比較復(fù)雜的任務(wù)，可以出現(xiàn)在select語(yǔ)句中第五節(jié)觸發(fā)器觸發(fā)器（Trigger）是用戶定義在關(guān)系表上的一類由事件驅(qū)動(dòng)的特殊過程由服務(wù)器自動(dòng)激活可以進(jìn)行更為復(fù)雜的檢查和操作，具有更精細(xì)和更強(qiáng)大的數(shù)據(jù)控制能力

第五節(jié)觸發(fā)器SQLSERVER2005觸發(fā)器DML觸發(fā)器DDL觸發(fā)器SQLSERVER2005觸發(fā)器觸發(fā)器是一種特殊的存儲(chǔ)過程，它在執(zhí)行事件時(shí)自動(dòng)生效。SQLServer2005包括兩大類觸發(fā)器：DML觸發(fā)器和DDL觸發(fā)器。DML觸發(fā)器在數(shù)據(jù)庫(kù)中發(fā)生數(shù)據(jù)操作語(yǔ)言(DML)事件時(shí)將啟用。DML事件包括在指定表或視圖中修改數(shù)據(jù)的INSERT語(yǔ)句、UPDATE語(yǔ)句或DELETE語(yǔ)句。DML觸發(fā)器可以查詢其他表，還可以包含復(fù)雜的Transact-SQL語(yǔ)句。將觸發(fā)器和觸發(fā)它的語(yǔ)句作為可在觸發(fā)器內(nèi)回滾的單個(gè)事務(wù)對(duì)待。如果檢測(cè)到錯(cuò)誤（例如，磁盤空間不足），則整個(gè)事務(wù)即自動(dòng)回滾。DDL觸發(fā)器是SQLServer2005的新增功能。當(dāng)服務(wù)器或數(shù)據(jù)庫(kù)中發(fā)生數(shù)據(jù)定義語(yǔ)言(DDL)事件時(shí)將調(diào)用這些觸發(fā)器。觸發(fā)器的作用（1）觸發(fā)器可以對(duì)數(shù)據(jù)庫(kù)進(jìn)行級(jí)聯(lián)修改（2）實(shí)現(xiàn)比CHECK約束更為復(fù)雜的限制（3）比較數(shù)據(jù)修改前后的差別（4）強(qiáng)制表的修改要合乎業(yè)務(wù)規(guī)則DML觸發(fā)器DML觸發(fā)器是在對(duì)表進(jìn)行插入、更新或刪除操作時(shí)自動(dòng)執(zhí)行的存儲(chǔ)過程觸發(fā)器定義在特定的表上，與表相關(guān)自動(dòng)觸發(fā)執(zhí)行不能直接調(diào)用是一個(gè)事務(wù)（可回滾）分類DELETE觸發(fā)器INSERT觸發(fā)器UPDATE觸發(fā)器Inserted表和Deleted表SQLServer2005為每個(gè)觸發(fā)器都創(chuàng)建了兩個(gè)專用臨時(shí)表：Inserted表和Deleted表。這兩個(gè)表的結(jié)構(gòu)總是與被該觸發(fā)器作用的表的結(jié)構(gòu)相同，觸發(fā)器執(zhí)行完成后，與該觸發(fā)器相關(guān)的這兩個(gè)表也會(huì)被刪除。激活觸發(fā)器的動(dòng)作Inserted表Deleted表Insert存放要插入的記錄

Update存放要更新的記錄存放更新前的舊記錄Delete

存放要除的舊記錄iserted和deleted表觸發(fā)器觸發(fā)時(shí)：系統(tǒng)自動(dòng)在內(nèi)存中創(chuàng)建deleted表或inserted表只讀，不允許修改；觸發(fā)器執(zhí)行完成后，自動(dòng)刪除inserted表臨時(shí)保存了插入或更新后的記錄行可以從inserted表中檢查插入的數(shù)據(jù)是否滿足業(yè)務(wù)需求如果不滿足，則向用戶報(bào)告錯(cuò)誤消息，并回滾插入操作deleted表臨時(shí)保存了刪除或更新前的記錄行可以從deleted表中檢查被刪除的數(shù)據(jù)是否滿足業(yè)務(wù)需求如果不滿足，則向用戶報(bào)告錯(cuò)誤消息，并回滾插入操作創(chuàng)建DML觸發(fā)器

創(chuàng)建DML觸發(fā)器的語(yǔ)法格式為：CREATETRIGGER[schema_name.]trigger_nameON{table|view}[WITHENCRYPTION]{FOR|AFTER|INSTEADOF}{[INSERT][,][UPDATE][,][DELETE]}[NOTFORREPLICATION]ASbeginsql_statement[;]endSQLServer2005的語(yǔ)法：CREATETRIGGER[schema_name.]trigger_name

ON{table|view}{FOR|AFTER|INSTEADOF}{[INSERT][,][UPDATE][,][DELETE]}ASsql_statement表的擁有者才可以創(chuàng)建觸發(fā)器和其目標(biāo)表必須在同一模式下SQLServer2005的語(yǔ)法：CREATETRIGGER[schema_name.]trigger_nameON{table|view}{FOR|AFTER|INSTEADOF}{[INSERT][,][UPDATE][,][DELETE]}ASsql_statement觸發(fā)器的目標(biāo)表或視圖視圖上創(chuàng)建觸發(fā)器有更多的限制，請(qǐng)查閱資料SQLServer2005的語(yǔ)法：CREATETRIGGER[schema_name.]trigger_nameON{table|view}{FOR|AFTER|INSTEADOF}

{[INSERT][,][UPDATE][,][DELETE]}ASsql_statementAFTER：指定觸發(fā)器僅在觸發(fā)SQL語(yǔ)句成功執(zhí)行時(shí)才被激發(fā)。所有的引用級(jí)聯(lián)操作和約束檢查也必須在激發(fā)此觸發(fā)器之前成功完成。如果僅指定FOR關(guān)鍵字，則AFTER為默認(rèn)值。

AFTER觸發(fā)器只能在表上指定。對(duì)于每個(gè)觸發(fā)操作可以有多個(gè)AFTER觸發(fā)器。SQLServer2005的語(yǔ)法：CREATETRIGGER[schema_name.]trigger_nameON{table|view}{FOR|AFTER|INSTEADOF}

{[INSERT][,][UPDATE][,][DELETE]}ASsql_statementINSTEADOF：指定觸發(fā)器是“代替”SQL語(yǔ)句執(zhí)行的，因此其優(yōu)先級(jí)高于觸發(fā)語(yǔ)句的操作。對(duì)于表或視圖，每個(gè)INSERT、UPDATE或DELETE語(yǔ)句最多可定義一個(gè)INSTEADOF觸發(fā)器。SQLServer2005的語(yǔ)法：CREATETRIGGER[schema_name.]trigger_nameON{table|view}{FOR|AFTER|INSTEADOF}{[INSERT][,][UPDATE][,][DELETE]}ASsql_statement指定激活該觸發(fā)器的數(shù)據(jù)更新語(yǔ)句至少指定一個(gè)選項(xiàng)，也允許使用上述選項(xiàng)的任意順序組合SQLServer2005的語(yǔ)法：CREATETRIGGER[schema_name.]trigger_nameON{table|view}{FOR|AFTER|INSTEADOF}{[INSERT][,][UPDATE][,][DELETE]}ASsql_statement觸發(fā)動(dòng)作體使用Transact-SQL編寫使用INSERT觸發(fā)器INSERT觸發(fā)器通常被用來(lái)更新時(shí)間標(biāo)記字段，或者驗(yàn)證被觸發(fā)器監(jiān)控的字段中數(shù)據(jù)滿足要求的標(biāo)準(zhǔn)，以確保數(shù)據(jù)的完整性例：建立一個(gè)觸發(fā)器，當(dāng)向sc表中添加數(shù)據(jù)時(shí)，如果添加的數(shù)據(jù)與student表中的數(shù)據(jù)不匹配（沒有對(duì)應(yīng)的學(xué)號(hào)），則將此數(shù)據(jù)刪除。CREATETRIGGERtr_sc_insertONstudents.scFORINSERTASBEGINDECLARE@bhchar(10)Select@bh=Inserted.snofromInsertedIfnotexists(selectsnofromstudents.studentwherestudent.sno=@bh)Deletestudents.scwheresno=@bhEND插入記錄行insertedSnoCnoGrade1

1

1向inserted表中插入新行的副本，觸發(fā)insert觸發(fā)器。觸發(fā)器檢查inserted表中插入的新行數(shù)據(jù)，確定是否需要回滾或執(zhí)行其他操作例：創(chuàng)建一個(gè)觸發(fā)器，當(dāng)插入或更新成績(jī)列時(shí)，該觸發(fā)器檢查插入的數(shù)據(jù)是否處于設(shè)定的范圍內(nèi)（0，100）。CREATETRIGGERstudents.tr_sc_gradeONstudents.scAFTERINSERT,UPDATEASBEGIN

DECLARE@scoreint SELECT@score=inserted.gradefrominserted IF(@score<0or