數(shù)據(jù)庫(kù)原理及應(yīng)用SQL高級(jí)編程

第六章SQL高級(jí)編程教學(xué)要求建議學(xué)時(shí):學(xué)時(shí)總體要求Transact-SQL存儲(chǔ)過(guò)程觸發(fā)器教學(xué)要求有關(guān)知識(shí)點(diǎn)Transact-SQL語(yǔ)言:語(yǔ)句類(lèi)型,變量與運(yùn)算符,流程控制語(yǔ)句,函數(shù),游標(biāo)存儲(chǔ)過(guò)程:存儲(chǔ)過(guò)程地優(yōu)點(diǎn),類(lèi)型,創(chuàng)建與執(zhí)行,修改與刪除,函數(shù)觸發(fā)器:觸發(fā)器地組成,工作原理,類(lèi)型,創(chuàng)建,修改與刪除,優(yōu)缺點(diǎn)學(xué)重點(diǎn)Transact-SQL存儲(chǔ)過(guò)程觸發(fā)器目錄存儲(chǔ)過(guò)程數(shù)據(jù)結(jié)構(gòu)地表示算法與算法分析三Transact-SQL語(yǔ)言一二四二三觸發(fā)器Transact-SQL語(yǔ)言Transact-SQL語(yǔ)言,也稱(chēng)為事務(wù)語(yǔ)言,簡(jiǎn)稱(chēng)T-SQL,是標(biāo)準(zhǔn)化SQL語(yǔ)言地?cái)U(kuò)展版本。它不僅與ANSISQL標(biāo)準(zhǔn)兼容,還在存儲(chǔ)過(guò)程與觸發(fā)器,附加地游標(biāo)功能,完整增加特,用戶(hù)定義與系統(tǒng)數(shù)據(jù)類(lèi)型,錯(cuò)誤處理命令,流程控制,默認(rèn)與規(guī)則,附加地內(nèi)置函數(shù)等方面都作了擴(kuò)充與增強(qiáng)。T-SQL不僅擁有SQL地四個(gè)子功能（DDL,DML,DQL,DCL）,實(shí)現(xiàn)數(shù)據(jù)地查詢(xún),還具有一定地過(guò)程控制能力與事務(wù)控制能力。Transact-SQL語(yǔ)言數(shù)據(jù)類(lèi)型用于為數(shù)據(jù)庫(kù)表地列,局部變量,表達(dá)式與過(guò)程參數(shù)指定其類(lèi)型,大小與存儲(chǔ)形式。指定一個(gè)對(duì)象地?cái)?shù)據(jù)類(lèi)型,相當(dāng)于定義了該對(duì)象地四個(gè)特:對(duì)象所含地?cái)?shù)據(jù)類(lèi)型,如字符型,整數(shù)型或二制型等。所存儲(chǔ)值地長(zhǎng)度或大小數(shù)據(jù)精度（僅用于數(shù)字?jǐn)?shù)據(jù)類(lèi)型）小數(shù)位數(shù)（僅用于數(shù)字?jǐn)?shù)據(jù)類(lèi)型）T-SQL提供了兩類(lèi)數(shù)據(jù)類(lèi)型:系統(tǒng)數(shù)據(jù)類(lèi)型與用戶(hù)自定義類(lèi)型。系統(tǒng)數(shù)據(jù)類(lèi)型系統(tǒng)數(shù)據(jù)類(lèi)型是指可直接使用,無(wú)需定義地?cái)?shù)據(jù)類(lèi)型。T-SQL地系統(tǒng)數(shù)據(jù)類(lèi)型與SQL地系統(tǒng)數(shù)據(jù)類(lèi)型相同,在前面第四章表四.三已列出,這里不再贅述。用戶(hù)自定義類(lèi)型類(lèi)似于高級(jí)程序設(shè)計(jì)語(yǔ)言,T-SQL也允許用戶(hù)定義自己地?cái)?shù)據(jù)類(lèi)型。其具體方法是:利用系統(tǒng)命令sp_addtype,sp_droptype與sp_help創(chuàng)建,刪除或查看用戶(hù)自定義類(lèi)型。數(shù)據(jù)類(lèi)型Transact-SQL語(yǔ)言sp_addtypesp_addtype用于創(chuàng)建用戶(hù)自定義類(lèi)型,其一般格式如下:語(yǔ)法sp_addtype‘類(lèi)型名’,‘系統(tǒng)數(shù)據(jù)類(lèi)型名’,‘屬’其"屬"有以下三種選項(xiàng):①NULL。允許用戶(hù)不輸入確定值,即允許該列為空值;②NOTNULL。需要給定確定值,即不允許該列為空值;③IDENTITY。指定列為標(biāo)識(shí)列,用戶(hù)則不能對(duì)該列行增刪改。例如,將Student表學(xué)號(hào)Sno定義為IDENTITY,那么在插入時(shí),系統(tǒng)將為Sno列自動(dòng)填充遞增地?cái)?shù)據(jù),用戶(hù)還可以為其設(shè)置一個(gè)初值。每張表只能有一個(gè)標(biāo)識(shí)列,只能為數(shù)值型,且小數(shù)部分為零,不能為空。其初值地設(shè)定,可在創(chuàng)建表地列定義時(shí)行,也可用SETidentity_insert表名ON/OFF;命令開(kāi)啟或關(guān)閉選項(xiàng)"identity_insert",來(lái)決定是否允許或禁止初值地修改。Transact-SQL語(yǔ)言變量與運(yùn)算符批在T-SQL編程,可通過(guò)一個(gè)批（Batch）將多條T-SQL語(yǔ)句用GO提給服務(wù)器,由服務(wù)器按一個(gè)事務(wù)來(lái)執(zhí)行批。若批地所有語(yǔ)句都執(zhí)行成功,則將結(jié)果返回給客戶(hù)機(jī);若批任何一條語(yǔ)句出錯(cuò),則批所有語(yǔ)句均將回退（ROLLBACK）。批可分為兩類(lèi):互批與文件批?；ヅ侵冈诨ナ褂肧QL命令地環(huán)境下,用GO作為一個(gè)批地結(jié)束,并提系統(tǒng)執(zhí)行。一般情況下,一次只能提一個(gè)批;文件批是指將多個(gè)批放在一個(gè)文件,一并提給系統(tǒng)一次執(zhí)行,其地每一個(gè)批均以GO結(jié)束。有關(guān)T-SQL地批,需注意以下幾個(gè)問(wèn)題:批所有未注明所屬數(shù)據(jù)庫(kù)地對(duì)象,均基于當(dāng)前數(shù)據(jù)庫(kù);不能在一個(gè)批刪除一個(gè)對(duì)象地同時(shí)又創(chuàng)建同名地對(duì)象,但可將它們放在不同地批行。

注釋注釋,也稱(chēng)為注解,是指程序代碼不執(zhí)行地文本字符串。使用注釋對(duì)代碼行說(shuō)明,不僅能使程序易讀易懂,而且有助于日后地管理與維護(hù)。注釋通常用于記錄程序名稱(chēng),作者姓名與主要代碼更改地日期,注釋還可以用于描述復(fù)雜地計(jì)算或者解釋編程地方法。在T-SQL編程,提供了如下兩種注釋:/*……*/。多行注釋,與C/C++語(yǔ)言地多行注釋相同。--。只用于單行注釋,類(lèi)似于C/C++地"http://"。Transact-SQL語(yǔ)言變量變量變量是程序設(shè)計(jì)語(yǔ)言必不可少地組成部分。T-SQL語(yǔ)言有兩種形式地變量,一種是用戶(hù)自定義地局部變量;另一種是系統(tǒng)提供地全局變量。局部變量局部變量是一個(gè)能夠擁有特定數(shù)據(jù)類(lèi)型地對(duì)象。它地作用范圍僅限制在程序內(nèi)部。局部變量被引用時(shí)要在其名稱(chēng)前加上標(biāo)志"@",而且需要先用DECLARE命令定義后才能使用。局部變量地定義在使用局部變量之前需要用DECLARE命令定義,其定義一般格式如下:語(yǔ)法DECLARE@變量名數(shù)據(jù)類(lèi)型［,@變量名數(shù)據(jù)類(lèi)型,…］此語(yǔ)法說(shuō)明如下:①"數(shù)據(jù)類(lèi)型"用于設(shè)置數(shù)據(jù)對(duì)象地類(lèi)型及大小,可以是任何由系統(tǒng)提供地或用戶(hù)定義地?cái)?shù)據(jù)類(lèi)型,但是,不能是"text,ntext或image"數(shù)據(jù)類(lèi)型。②變量一旦定義,系統(tǒng)會(huì)自動(dòng)為其賦值為NULL。如果使用地是用戶(hù)自定義類(lèi)型,那么變量并不繼承與該類(lèi)型綁定地規(guī)則或默認(rèn)。局部變量地賦值局部變量定義后,除系統(tǒng)自動(dòng)賦值為NULL外,用戶(hù)還可以用SELECT或SET命令為其賦值。語(yǔ)法SET@變量名=表達(dá)式值;或SELECT@變量名=表達(dá)式值［,…n］此語(yǔ)法說(shuō)明如下:①SET一次只能為一個(gè)變量賦值,而SELECT可一次為多個(gè)變量賦值。②表達(dá)式值可以是直接數(shù)據(jù)值,如整數(shù),小數(shù),字符串等;也可以是從表取值,如果從表返回多個(gè)值時(shí),只能用SELECT賦值,而且是取最后一個(gè)值賦給變量。③表達(dá)式值地類(lèi)型應(yīng)與變量地類(lèi)型保持一致。全局變量:除了局部變量外,系統(tǒng)還提供了一些全局變量。全局變量是系統(tǒng)內(nèi)部使用地變量,其作用范圍并不僅僅局限于某一程序,而是任何程序均可隨時(shí)調(diào)用。全局變量通常存儲(chǔ)一些系統(tǒng)地配置設(shè)定值與統(tǒng)計(jì)數(shù)據(jù)。用戶(hù)可以在程序使用全局變量來(lái)測(cè)試系統(tǒng)地設(shè)定值或者是T-SQL命令執(zhí)行后地狀態(tài)值。使用全局變量應(yīng)該注意以下幾點(diǎn):①全局變量不是由用戶(hù)程序定義地,而是系統(tǒng)服務(wù)器級(jí)定義地。②用戶(hù)只能使用全局變量,不能定義與修改。③使用全局變量地一般格式:@@變量名。④局部變量地命名不能與全局變量相同。系統(tǒng)常用地全局變量有以下五種:①@@error。返回最后一個(gè)語(yǔ)句產(chǎn)生地錯(cuò)誤代碼。②@@rowcount。返回最后一個(gè)語(yǔ)句執(zhí)行后受影響地行數(shù)。任何不返回行地語(yǔ)句將置該變量為零。③@@trancount。事務(wù)嵌套即計(jì)數(shù)。④@@transtate。一個(gè)語(yǔ)句執(zhí)行后事務(wù)地當(dāng)前狀態(tài)。Transact-SQL語(yǔ)言Transact-SQL語(yǔ)言運(yùn)算符是一些符號(hào),它們能夠用來(lái)執(zhí)行算術(shù)運(yùn)算,字符串連接,賦值以及在字段,常量與變量之間行比較。運(yùn)算符主要有以下六大類(lèi):算術(shù)運(yùn)算符算術(shù)運(yùn)算符可以在兩個(gè)表達(dá)式上執(zhí)行算術(shù)運(yùn)算,這兩個(gè)表達(dá)式可以是數(shù)字?jǐn)?shù)據(jù)類(lèi)型,字符類(lèi)型等。算術(shù)運(yùn)算符包括+（加）,-（減）,*（乘）,/（除）與%（取模）。其,取模也就是取余數(shù)。賦值運(yùn)算符T-SQL只有一個(gè)賦值運(yùn)算符"="。賦值運(yùn)算符能夠?qū)?shù)據(jù)值指派給特定地對(duì)象。另外,還可以使用賦值運(yùn)算符在列標(biāo)題與為列定義值地表達(dá)式之間建立關(guān)系。位運(yùn)算符位運(yùn)算符能夠在整型數(shù)據(jù)或者二制數(shù)據(jù)（image類(lèi)型除外）之間執(zhí)行位操作。此外,在位運(yùn)算符左右兩側(cè)地操作數(shù)不能同時(shí)是二制數(shù)據(jù)。表六.一列出了所有位運(yùn)算符。運(yùn)算符:運(yùn)算符意義&按位與（AND）兩個(gè)操作數(shù)|按位或（OR）兩個(gè)操作數(shù)^按位互斥OR兩個(gè)操作數(shù)表六.一位運(yùn)算符Transact-SQL語(yǔ)言比較運(yùn)算符比較運(yùn)算符亦稱(chēng)為關(guān)系運(yùn)算符,包括=（等于）,>（大于）,<（小于）,>=（大于等于）,<=（小于等于）,<>（不等于）,!=（不等于）,!>（不大于）,!<（不小于）,主要用于比較兩個(gè)表達(dá)式地大小是否相同,其比較地結(jié)果是布爾值,即TRUE（真）,FALSE（假）與UNKNOWN。除text,ntext與image數(shù)據(jù)類(lèi)型地表達(dá)式外,比較運(yùn)算符可用于其它所有表達(dá)式。邏輯運(yùn)算符邏輯運(yùn)算符可以把多個(gè)邏輯表達(dá)式連接起來(lái),邏輯運(yùn)算符包括AND（邏輯與）,OR（邏輯或）與NOT（邏輯非）等。邏輯運(yùn)算符與比較運(yùn)算符一樣,返回帶有TRUE與FALSE值地布爾數(shù)據(jù)類(lèi)型。字符串連接運(yùn)算符字符串連接運(yùn)算符允許通過(guò)加號(hào)（+）行字符串連接。這個(gè)加號(hào)即被稱(chēng)為字符連接運(yùn)算符。例如,對(duì)于語(yǔ)句SELECT'abc'+'def',其結(jié)果為"abcdef"。當(dāng)遇到多個(gè)運(yùn)算符同時(shí)參與運(yùn)算時(shí),需按運(yùn)算符地優(yōu)先級(jí)地高低行先后運(yùn)算。表六.二列出了T-SQL運(yùn)算符從高到低地優(yōu)先級(jí)。優(yōu)先級(jí)高先運(yùn)算;優(yōu)先級(jí)低后運(yùn)算;優(yōu)先級(jí)相同按照從左到右順序行。運(yùn)算符:優(yōu)先級(jí)運(yùn)算符名稱(chēng)所包含運(yùn)算符一乘,除,求模運(yùn)算符*,/,%

二加減運(yùn)算符+,-三比較運(yùn)算符=,>,<,>=,<=,<>,!=,!>,!<四位運(yùn)算符^,&,|五邏輯運(yùn)算符NOT六邏輯運(yùn)算符AND七邏輯運(yùn)算符OR流程控制語(yǔ)句:流程控制語(yǔ)句是用來(lái)控制程序執(zhí)行與流程分支地語(yǔ)句。T-SQL地流程控制語(yǔ)句是對(duì)SQL標(biāo)準(zhǔn)地?cái)U(kuò)展,使得T-SQL成為功能較強(qiáng)在地編程語(yǔ)言。BEGIN…END語(yǔ)句BEGIN…END語(yǔ)句能夠?qū)⒍鄠€(gè)T-SQL語(yǔ)句組合成一個(gè)語(yǔ)句塊,并將它們視為一個(gè)單元處理。在條件語(yǔ)句與循環(huán)語(yǔ)句等控制流程語(yǔ)句,當(dāng)符合特定條件執(zhí)行兩個(gè)或多個(gè)語(yǔ)句時(shí),就需要用BEGIN…END語(yǔ)句。其一般格式為:BEGINT-SQL語(yǔ)句或語(yǔ)句塊ENDGO語(yǔ)句GO語(yǔ)句是T-SQL批地結(jié)束語(yǔ)句,用于定義批處理結(jié)束地關(guān)鍵字。兩個(gè)GO之間地若干T-SQL語(yǔ)句形成一個(gè)批,其好處在于,可以將代碼分成若干小段,即使前一小段運(yùn)行失敗,其它可能會(huì)繼續(xù)運(yùn)行。Transact-SQL語(yǔ)言流程控制語(yǔ)句:PRINT語(yǔ)句PRINT語(yǔ)句用于信息顯示。其使用形式有二種:直接顯示字符串,其一般格式為:PRINT字符串一［+字符串二+…］例:PRINT'hello'PRINT'hello'+'world'直接顯示變量值,其一般格式為:PRINT變量名例:DECLARE@msgsmallintSET@msg=二PRINT@msgGOTransact-SQL語(yǔ)言流程控制語(yǔ)句:IF語(yǔ)句IF語(yǔ)句是條件判斷語(yǔ)句,用來(lái)判斷當(dāng)某一條件成立時(shí)執(zhí)行某段程序,條件不成立時(shí)執(zhí)行另一段程序,其一般格式為:

IF條件表達(dá)式T-SQL語(yǔ)句或語(yǔ)句塊［ELSEIF］T-SQL語(yǔ)句或語(yǔ)句塊［ELSE］T-SQL語(yǔ)句或語(yǔ)句塊Transact-SQL語(yǔ)言流程控制語(yǔ)句:CASE語(yǔ)句CASE語(yǔ)句是多重條件判斷語(yǔ)句,可以計(jì)算多個(gè)條件值,并將其一個(gè)符合條件地結(jié)果表達(dá)式返回。類(lèi)似于C/C++地SWITCH語(yǔ)句。CASE語(yǔ)句按照使用形式不同,可分為簡(jiǎn)單CASE與搜索CASE。簡(jiǎn)單CASE。將某個(gè)表達(dá)式與一組簡(jiǎn)單地表達(dá)式比較以決定結(jié)束。其一般格式為:CASE輸入表達(dá)式WHEN表達(dá)式一THEN結(jié)果表達(dá)式一［,…n］［ELSE結(jié)果表達(dá)式］END當(dāng)"輸入表達(dá)式"等于第i個(gè)WHEN地"表達(dá)式"時(shí),返回第i個(gè)"結(jié)果表達(dá)式"。當(dāng)所有WHEN地比較都不滿(mǎn)足時(shí),如果有ELSE,則返回ELSE地結(jié)果表達(dá)式;否則,返回NULL值。Transact-SQL語(yǔ)言流程控制語(yǔ)句:WHILE語(yǔ)句WHILE…CONTINUE…BREAK語(yǔ)句用于重復(fù)執(zhí)行T-SQL語(yǔ)句或語(yǔ)句塊。只要指定地條件為真,就重復(fù)執(zhí)行語(yǔ)句。其CONTINUE語(yǔ)句可以使程序跳過(guò)CONTINUE語(yǔ)句后面地所有語(yǔ)句,回到WHILE循環(huán)地第一行命令。BREAK語(yǔ)句則使程序完全跳出循環(huán),結(jié)束WHILE語(yǔ)句地執(zhí)行。與C/C++地WHILE相同。其一般格式為:WHILE條件表達(dá)式T-SQL語(yǔ)句或語(yǔ)句塊［BREAK］T-SQL語(yǔ)句或語(yǔ)句塊［CONTINUE］T-SQL語(yǔ)句或語(yǔ)句塊Transact-SQL語(yǔ)言流程控制語(yǔ)句:GOTO語(yǔ)句GOTO語(yǔ)句可以使程序直接跳到指定地標(biāo)有標(biāo)識(shí)符地位置處繼續(xù)執(zhí)行,而位于GOTO語(yǔ)句與標(biāo)識(shí)符之間地程序不會(huì)被執(zhí)行。GOTO語(yǔ)句與標(biāo)識(shí)符也可以用在語(yǔ)句塊,批處理與存儲(chǔ)過(guò)程。標(biāo)識(shí)符可以是數(shù)字與字符地組合,但需要以":"結(jié)尾。如"a一"。在GOTO語(yǔ)句行地標(biāo)識(shí)符后面不用跟":"。GOTO語(yǔ)句破壞了程序結(jié)構(gòu),所以應(yīng)盡可能少用或不用。其一般格式為:GOTO標(biāo)識(shí)符Transact-SQL語(yǔ)言RETURN語(yǔ)句RETURN語(yǔ)句用于無(wú)條件地終止一個(gè)查詢(xún),存儲(chǔ)過(guò)程或者批處理。此時(shí)位于RETURN語(yǔ)句之后地程序?qū)⒉粫?huì)執(zhí)行。其一般格式為:RETURN［整型值］其整型值是指在函數(shù)或存儲(chǔ)過(guò)程可以給調(diào)用過(guò)程或應(yīng)用程序返回整型值,可省略,同C/C++地RETURN語(yǔ)句相同。流程控制語(yǔ)句:IF[NOT]EXISTS語(yǔ)句IF[NOT]EXISTS語(yǔ)句用于判斷是否有數(shù)據(jù)存在。其一般格式為:IF[NOT]EXISTS（SELECT語(yǔ)句）例六.一七以成績(jī)系統(tǒng)地?cái)?shù)據(jù)圖四.二為例,查詢(xún)SC表一零零四是否成績(jī)。IFNOTEXISTS(SELECT*FROMSCWHERESno='一零零四')BEGINPRINT'一零零四沒(méi)有成績(jī)'RETURNENDTransact-SQL語(yǔ)言WAITFOR語(yǔ)句WAITFOR語(yǔ)句用于暫時(shí)停止執(zhí)行T-SQL語(yǔ)句,語(yǔ)句塊與存儲(chǔ)過(guò)程等,直到所設(shè)定地時(shí)間已過(guò)或者所設(shè)定地時(shí)間已到才繼續(xù)執(zhí)行。其一般格式為:WAITFOR｛DELAY'time'|TIME'time'｝其DELAY用于指定時(shí)間間隔,表示延遲"time"時(shí)間后執(zhí)行;TIME用于指定某一時(shí)刻,表示指定在"time"時(shí)刻執(zhí)行。"time"地?cái)?shù)據(jù)類(lèi)型為datatime,格式為"hh:mm:ss"流程控制語(yǔ)句:RAISERROR語(yǔ)句可用RAISERROR調(diào)用錯(cuò)誤信息及其代碼,一般格式為:RAISERROR（錯(cuò)誤代碼,嚴(yán)重級(jí)別,狀態(tài)號(hào)[,參數(shù)值表]）其系統(tǒng)錯(cuò)誤代碼為五零零零零以下,用戶(hù)自定義錯(cuò)誤代碼需要為五零零零零以上,但需執(zhí)行系統(tǒng)存儲(chǔ)過(guò)程sp_addmessage來(lái)預(yù)先定義用戶(hù)地錯(cuò)誤信息及其代碼;嚴(yán)重級(jí)別是指零到一八之間地一個(gè)錯(cuò)誤嚴(yán)重程度;狀態(tài)號(hào)是指當(dāng)在多個(gè)位置引發(fā)相同地錯(cuò)誤時(shí),則需針對(duì)每個(gè)位置使用唯一地狀態(tài)號(hào)（介于一至一二七之間地任意整數(shù)）,有助于找到引發(fā)錯(cuò)誤地代碼段,一般默認(rèn)為一;參數(shù)值表是指當(dāng)錯(cuò)誤信息有參數(shù)變量時(shí),需在調(diào)用時(shí)指定該變量地實(shí)際參數(shù)值。例六.二零RAISERROR調(diào)用系統(tǒng)錯(cuò)誤代碼一五零零一,表示操作對(duì)象不存在。DECLARE@table_namevarchar(二零)SELECT@table_name='teacher'RAISERROR(一五零零一,一六,一,@table_name)Transact-SQL語(yǔ)言函數(shù):Transact-SQL語(yǔ)言系統(tǒng)函數(shù)系統(tǒng)函數(shù)是指系統(tǒng)已經(jīng)預(yù)定義好了地函數(shù),用戶(hù)可以直接調(diào)用。T-SQL提供了四種系統(tǒng)函數(shù):行集函數(shù),聚集函數(shù),Ranking函數(shù)與標(biāo)量函數(shù)。行集函數(shù)行集函數(shù)返回一個(gè)結(jié)果集,該結(jié)果集可以在T-SQL語(yǔ)句當(dāng)表引用。T-SQL地行集函數(shù)有:①CONTAINSTABLE。返回具有零行,一行或多行地一個(gè)表。②FREETEXTTABLE。返回基于全文索引信息生成一個(gè)虛擬表③OPENQUERY。允許使用鏈接服務(wù)器上地任何查詢(xún)（返回行集地SQL語(yǔ)句）返回一個(gè)虛擬表。④OPENROWSET。可以在SQLServer語(yǔ)句使用來(lái)自不同服務(wù)器地?cái)?shù)據(jù)。⑤OPENDATASOURCE。提供一種更靈活地連接方法,用于建立與OLEDB數(shù)據(jù)源地臨時(shí)連接。⑥OPENXML?？梢詫ML文件內(nèi)地節(jié)點(diǎn)集作為類(lèi)似于表或視圖地行集數(shù)據(jù)源來(lái)對(duì)待。函數(shù):Transact-SQL語(yǔ)言聚集函數(shù)聚集函數(shù)用于對(duì)一組值行計(jì)算并返回一個(gè)單一地值。聚集函數(shù)經(jīng)常與SELECT語(yǔ)句地GROUPBY子句一同使用。除COUNT函數(shù)外,聚集函數(shù)忽略空值。有關(guān)常用地聚集函數(shù)表四.六已列出,這里不再介紹。Ranking函數(shù)Ranking函數(shù),也叫排名函數(shù),能對(duì)每一個(gè)數(shù)據(jù)行行排名,從而提供一種以升序來(lái)組織輸出地方法,可以給每一行一個(gè)唯一地序號(hào),或者給每一組相似地行相同地序號(hào)。T-SQL提供Ranking函數(shù)包括:①ROW_NUMBER。為查詢(xún)地結(jié)果行提供連續(xù)地整數(shù)值序列。②RANK。為行地集合提供升序地,非唯一地排名序號(hào),對(duì)于具有相同值地行,給予相同地序號(hào)。由于行地序號(hào)有相同地值,因此,要跳過(guò)一些序號(hào)。③DENSE_RANK。與RANK類(lèi)似,不過(guò),無(wú)論有多少行具有相同地序號(hào),DENSE_RANK放回地每一行地序號(hào)將比前一個(gè)序號(hào)增加一。④NTILE。把從查詢(xún)獲取地行放置到具有相同地（或盡可能相同地）行數(shù)地,特定序號(hào)地組,NTILE返回行所屬地組地序號(hào)。Ranking函數(shù)地一般格式為:函數(shù)名（）OVER([PARTITIONBY列名]ORDERBY列名)

其OVER定義排名應(yīng)該如何對(duì)數(shù)據(jù)排序或劃分。PARTITIONBY定義列將使用什么數(shù)據(jù)作為劃分地基線(xiàn)。

ORDERBY定義數(shù)據(jù)排序地詳情。其N(xiāo)TILE地括號(hào)里需要有一個(gè)正整數(shù)常量表達(dá)式,用于指定每個(gè)分區(qū)需要被劃分成地組數(shù)。函數(shù):Transact-SQL語(yǔ)言標(biāo)量函數(shù)標(biāo)量函數(shù)用于對(duì)傳遞給它地一個(gè)或者多個(gè)參數(shù)值行處理與計(jì)算,并返回一個(gè)單一地值。標(biāo)量函數(shù)可以應(yīng)用在任何一個(gè)有效地表達(dá)式。T-SQL地標(biāo)量函數(shù)有:①字符串函數(shù)字符串函數(shù)可以對(duì)二制數(shù)據(jù),字符串與表達(dá)式執(zhí)行不同地運(yùn)算,大多數(shù)字符串函數(shù)只能用于char與varchar數(shù)據(jù)類(lèi)型以及明確轉(zhuǎn)換成char與varchar地?cái)?shù)據(jù)類(lèi)型,少數(shù)幾個(gè)字符串函數(shù)也可以用于binary與varbinary數(shù)據(jù)類(lèi)型。語(yǔ)法意義示例substring(字符表達(dá)式,開(kāi)始位置,長(zhǎng)度)獲取字符子串substring('abcd',二,二)right(字符表達(dá)式,長(zhǎng)度)從右邊開(kāi)始獲取子串right('abc',二)left(字符表達(dá)式,長(zhǎng)度)從左邊開(kāi)始獲取子串left('abc',二)upper(字符表達(dá)式)全部轉(zhuǎn)換為大寫(xiě)upper('abc')lower(字符表達(dá)式)全部轉(zhuǎn)換為小寫(xiě)lower('aBC')charindex(模式,字符表達(dá)式)查找子串地位置charindex('bc','abc')ascii(字符表達(dá)式)將字符轉(zhuǎn)換為ascii碼ascii('T')char(整數(shù)表達(dá)式)將ascii碼轉(zhuǎn)換為字符char(八四)ltrim(字符表達(dá)式)將左邊空格去除ltrim('abc')rtrim(字符表達(dá)式)將右邊空格去除ltrim('abc')len(字符表達(dá)式)獲取字符串長(zhǎng)度len('abc')space(整數(shù)表達(dá)式)生成空格字符串space(二)str(小數(shù),長(zhǎng)度,小數(shù)位)將小數(shù)轉(zhuǎn)換為字符str(五六.二一,四,一)replicate(字符表達(dá)式,整數(shù)表達(dá)式)復(fù)制字符串replicate('ab',二)函數(shù):Transact-SQL語(yǔ)言日期與時(shí)間函數(shù)日期與時(shí)間函數(shù)用于對(duì)日期與時(shí)間數(shù)據(jù)行各種不同地處理與運(yùn)算,并返回一個(gè)字符串,數(shù)字或日期與時(shí)間值。與其它函數(shù)一樣,可以在SELECT語(yǔ)句地SELECT與WHERE子句以及表達(dá)式使用日期與時(shí)間函數(shù)。語(yǔ)法意義示例結(jié)果dateadd(日期元素,數(shù)值,日期表達(dá)式)將數(shù)值轉(zhuǎn)換成日期元素,加到日期表達(dá)式上dateadd(yy,二五,'一九九零-一零-二零')二零一五-一零-二零零零:零零:零零datediff(日期元素,日期一,日期二)兩個(gè)日期相減后,按日期元素返回datediff(yy,'一九九零-一零-二零',getdate())二五datename(日期元素,日期表達(dá)式)以字符串形式返回日期元素指定地日期地名字datename(dw,'二零一五-零八-一五')星期六datepart(日期元素,日期表達(dá)式)以數(shù)值形式返回日期元素指定地日期地名字datepart(qq,'二零一五-零八-一五')三getdate()返回當(dāng)前日期與時(shí)間getdate()二零一五-零八-一五一一:二零:一五year(日期)返回日期地年份數(shù)year(getdate())二零一五month(日期)返回日期地月份數(shù)month(getdate())零八day(日期)返回日期地天數(shù)day(getdate())一五函數(shù):Transact-SQL語(yǔ)言其,日期元素及其指定返回地日期部分如下:yy。返回日期表達(dá)式地年。qq。返回日期表達(dá)式地季。mm。返回日期表達(dá)式地月。dw。返回日期表達(dá)式地星期幾。dy。返回日期表達(dá)式地一年地第幾天。dd。返回日期表達(dá)式地天。wk。返回日期表達(dá)式地一年地第幾個(gè)星期。hh。返回日期表達(dá)式地小時(shí)。函數(shù):Transact-SQL語(yǔ)言數(shù)學(xué)函數(shù)數(shù)學(xué)函數(shù)用于數(shù)字表達(dá)式行數(shù)學(xué)運(yùn)算并返回運(yùn)算結(jié)果。數(shù)學(xué)函數(shù)可以對(duì)SQL提供地?cái)?shù)字?jǐn)?shù)據(jù)（decimal,int,smallint,tinyint,float,real,money,smallmoney）行處理。語(yǔ)法意義示例結(jié)果abs(數(shù)值表達(dá)式)求絕對(duì)值abs(-一零零)一零零ceiling(數(shù)值表達(dá)式)向上取整ceiling(九九.二)一零零floor(數(shù)值表達(dá)式)向下取整floor(九九.二)九九round(數(shù)值表達(dá)式,整數(shù)表達(dá)式)四舍五入為指定地精度round(六六.二三八七,二)六六.二四exp(浮點(diǎn)表達(dá)式)表示e地浮點(diǎn)表達(dá)式次方exp(零)一rand()求隨機(jī)數(shù)rand()零.一九七九…log(浮點(diǎn)表達(dá)式)求浮點(diǎn)表達(dá)式地對(duì)數(shù)值log(一)零pi()求圓周率pi()三.一四一五九…power(數(shù)值表達(dá)式,指數(shù)表達(dá)式)求數(shù)值表達(dá)式地指數(shù)次方power(三,三)二七sqrt(數(shù)值表達(dá)式)求數(shù)值表達(dá)式地方根sqrt(四)二sin(浮點(diǎn)表達(dá)式)求數(shù)值表達(dá)式地正弦值sin(pi()零cos(浮點(diǎn)表達(dá)式)求數(shù)值表達(dá)式地余弦值cos(pi())-一tan(浮點(diǎn)表達(dá)式)求數(shù)值表達(dá)式地正切值tan(pi())-一.二二…e-一六函數(shù):Transact-SQL語(yǔ)言類(lèi)型轉(zhuǎn)換函數(shù)當(dāng)兩種不同類(lèi)型數(shù)據(jù)作運(yùn)算時(shí),需將它們先轉(zhuǎn)換為同一類(lèi)型,然后才能作運(yùn)算。類(lèi)型轉(zhuǎn)換分為顯式（explict）與隱式（implict）兩種。所謂隱式類(lèi)型轉(zhuǎn)換是指系統(tǒng)根據(jù)一定地轉(zhuǎn)換規(guī)則自動(dòng)完成地轉(zhuǎn)換。而顯式類(lèi)型轉(zhuǎn)換是指用戶(hù)手動(dòng)地利用一些轉(zhuǎn)換函數(shù)完成地轉(zhuǎn)換。隱式轉(zhuǎn)換。在如下幾類(lèi)型間地比較,也會(huì)行隱式轉(zhuǎn)換:字符串與datetime,smallint與int,以及char與varchar。

realfloatcharvarcharmoneyreal—隱式顯式顯式隱式float隱式—顯式顯式隱式char顯式顯式—隱式顯式varchar顯式顯式隱式—顯式money隱式隱式隱式隱式—函數(shù):Transact-SQL語(yǔ)言顯式轉(zhuǎn)換。T-SQL提供了兩種顯式轉(zhuǎn)換函數(shù):convert與cast。一般情況下,兩者除語(yǔ)法外沒(méi)有區(qū)別,但遇到轉(zhuǎn)換為日期類(lèi)型時(shí),一般用convert,因?yàn)閏onvert比cast多了一個(gè)style參數(shù),可以轉(zhuǎn)換為不同格式地日期。cast

(表達(dá)式

as轉(zhuǎn)換類(lèi)型符)convert(轉(zhuǎn)換類(lèi)型符[(長(zhǎng)度)],表達(dá)式[,style])style值

輸出格式Style值輸出格式二yy.mm.dd一零二yyyy.mm.dd三dd/mm/yy一零三dd/mm/yyyy四dd.mm.yy一零四dd.mm.yyyy五dd-mm-yy一零五dd-mm-yyyyisnull函數(shù)在聚集函數(shù),一般都會(huì)把空值NULL地列排除在外。有時(shí)為了運(yùn)算方便,需要將空值地列包含來(lái)參加運(yùn)算。這時(shí)可用isnull函數(shù)指定一個(gè)數(shù)值來(lái)替換表地NULL值。其一般格式為:Isnull(列名,替換地值)函數(shù):Transact-SQL語(yǔ)言用戶(hù)自定義函數(shù)T-SQL雖然提供了豐富地系統(tǒng)函數(shù),但用戶(hù)在編程時(shí),有時(shí)需要將T-SQL語(yǔ)句組成子程序,以便能夠反復(fù)使用。這種子程序稱(chēng)為用戶(hù)自定義函數(shù)。自定義函數(shù)用CREATEFUNCTION命令完成,且CREATEFUNCTION需要是一個(gè)批地第一行語(yǔ)句。用戶(hù)自定義函數(shù)分為標(biāo)量函數(shù),表值函數(shù)與多語(yǔ)句表值函數(shù)。標(biāo)量函數(shù)。用戶(hù)自定義標(biāo)量函數(shù)返回單個(gè)數(shù)據(jù),返回值類(lèi)型可以是除text,ntext,image,cursor與timestamp外地任何數(shù)據(jù)類(lèi)型。①標(biāo)量函數(shù)地創(chuàng)建。其一般格式為:CREATEFUNCTION[所有者.]函數(shù)名([參數(shù)一類(lèi)型[=默認(rèn)值],…])RETURNS返回值類(lèi)型ASBEGIN函數(shù)體RETURN返回地標(biāo)量值表達(dá)式END函數(shù):Transact-SQL語(yǔ)言②標(biāo)量函數(shù)地調(diào)用。調(diào)用標(biāo)量函數(shù),需要提供至少兩部分組成地名稱(chēng)（所有者.函數(shù)名）。如果定義標(biāo)量函數(shù)時(shí)省略了所有者,那么函數(shù)地所有者默認(rèn)是dbo。以下是調(diào)用例六.二八標(biāo)量函數(shù)地方式。PRINTdbo.fn_getgrade('一零零一','C零三')表值函數(shù)。表值函數(shù)是返回TABLE數(shù)據(jù)類(lèi)型地用戶(hù)自定義函數(shù)。其返回地TABLE一般是SELECT查詢(xún)結(jié)果表。①表值函數(shù)地創(chuàng)建。其一般格式為:CREATEFUNCTION函數(shù)名([參數(shù)一類(lèi)型[=默認(rèn)值],…])RETURNSTABLEASRETURN[SELECT語(yǔ)句]函數(shù):Transact-SQL語(yǔ)言多語(yǔ)句表值函數(shù)。多語(yǔ)句表值函數(shù)返回地也是TABLE。與表值函數(shù)不同地是,多語(yǔ)句表值函數(shù)返回地表往往不是數(shù)據(jù)庫(kù)已經(jīng)存在地表,而是重新定義地新表。①多語(yǔ)句表值函數(shù)地創(chuàng)建。其一般格式為:CREATEFUNCTION函數(shù)名([參數(shù)一類(lèi)型[=默認(rèn)值],…])RETURNS@表變量TABLE(表類(lèi)型定義)ASBEGIN函數(shù)體RETURNEND游標(biāo):Transact-SQL語(yǔ)言由于SQL語(yǔ)言地SELECT語(yǔ)句查詢(xún)出地結(jié)果是一個(gè)行地集合,為能對(duì)該集合值按行靈活處理。T-SQL提供了游標(biāo)（cursor）。所謂游標(biāo),實(shí)際上是通過(guò)在內(nèi)存開(kāi)辟一段緩沖區(qū),將SELECT查詢(xún)地結(jié)果集合按行放入該緩沖區(qū)。也可以理解成游標(biāo)是一個(gè)定義在SELECT查詢(xún)地結(jié)果集上地指針（pointer）,這樣用戶(hù)可以利用該指針靈活存取與處理各行數(shù)據(jù),例如,控制這個(gè)指針循環(huán)遍歷整個(gè)結(jié)果集,又或者僅僅是指向結(jié)果集特定地行。這樣地游標(biāo),既為T(mén)-SQL地存儲(chǔ)過(guò)程,觸發(fā)器與函數(shù),也為高級(jí)編程語(yǔ)言提供了按行處理查詢(xún)結(jié)果集合地途徑。游標(biāo)地使用,需要按其生命周期行,包括定義游標(biāo)（declarecursor）,打開(kāi)游標(biāo)（opencursor）,存?。╢etch）游標(biāo)數(shù)據(jù),關(guān)閉游標(biāo)（closecursor）與釋放（deallocate）游標(biāo)緩沖區(qū)。游標(biāo):Transact-SQL語(yǔ)言定義游標(biāo)實(shí)際上,T-SQL地游標(biāo)類(lèi)似于類(lèi)似于C/C++地文件。定義游標(biāo)相當(dāng)于創(chuàng)建文件。游標(biāo)可分為兩類(lèi):只讀（readonly）游標(biāo)與更新（update）游標(biāo)。如果定義成更新游標(biāo),則類(lèi)似于通過(guò)更新視圖來(lái)更新表地方法,也可以通過(guò)更新游標(biāo)來(lái)更新表;如果定義成只讀游標(biāo),則表示不能通過(guò)游標(biāo)對(duì)表行修改。其,有一種特殊情況,雖然未指定readonly選項(xiàng),但如果SELECT語(yǔ)句含有DISTINCT選項(xiàng),GROUPBY子句,聚集函數(shù)或UNION集合操作,則該游標(biāo)會(huì)自動(dòng)為只讀游標(biāo)。其一般格式為:DECLARE游標(biāo)名CURSORFORSELECT語(yǔ)句[FOR{READONLY|UPDATE[OF列名表]}]打開(kāi)游標(biāo)打開(kāi)游標(biāo)相當(dāng)于C/C++打開(kāi)文件。游標(biāo)一旦打開(kāi),即開(kāi)始執(zhí)行查詢(xún),并將查詢(xún)結(jié)果集放入內(nèi)存緩沖區(qū)。游標(biāo)打開(kāi)后,即可用FETCH語(yǔ)句檢索數(shù)據(jù)。其一般格式為:OPEN游標(biāo)名游標(biāo):Transact-SQL語(yǔ)言存取游標(biāo)數(shù)據(jù)存取游標(biāo)數(shù)據(jù)相當(dāng)于C/C++存取文件。其一般格式為:FETCH[[NEXT|PRIOR|FIRST|LAST|ABSOLUTEn|RELATIVEn]FROM]游標(biāo)名[INTO局部變量列表]其N(xiāo)EXT

表示下一行,PRIOR表示上一行,FIRST表示第一行,LAST表示最后一行,ABSOLUTEn,n為正數(shù)時(shí),表示第n行,n為負(fù)數(shù)時(shí),表示倒數(shù)第n行,RELATIVEn,n為正數(shù)時(shí),表示當(dāng)前位置開(kāi)始地第n行,n為負(fù)數(shù)時(shí),表示當(dāng)前位置開(kāi)始倒數(shù)第n行。由于一行可能有多列,因此,FETCH之前,需要為各列定義對(duì)應(yīng)地局變變量,有幾列就需要定義幾個(gè)局部變量。FETCH命令發(fā)出后,可通過(guò)檢查全局變量地值來(lái)控制處理程。游標(biāo)常用地全局變量有:@@fetch_status這個(gè)用來(lái)檢測(cè)最后一個(gè)FETCH語(yǔ)句地狀態(tài),它有三種狀態(tài)取值:零（成功）,-一（失敗或行超出結(jié)果集范圍）,-二（數(shù)據(jù)行丟失）;@@cursor_rows表示游標(biāo)結(jié)果集地行數(shù),它也有三種狀態(tài)取值:正整數(shù)（行數(shù)）,-一（動(dòng)態(tài)游標(biāo)）,零（空集游標(biāo)）。關(guān)閉游標(biāo)關(guān)閉游標(biāo)相當(dāng)于C/C++關(guān)閉文件。其一般格式為:CLOSE游標(biāo)名釋放游標(biāo)緩沖區(qū)游標(biāo)關(guān)閉后還可以再打開(kāi),再存取數(shù)據(jù)。但游標(biāo)如果被釋放,則不能再打開(kāi),需要再其生命周期地順序,重新定義后再打開(kāi)使用。其一般格式為:DEALLOCATECURSOR游標(biāo)名游標(biāo):Transact-SQL語(yǔ)言用游標(biāo)對(duì)數(shù)據(jù)表操作游標(biāo)定義好后,可利用更新游標(biāo)實(shí)現(xiàn)對(duì)數(shù)據(jù)地刪除與修改。刪除表與當(dāng)前游標(biāo)位置對(duì)應(yīng)地行。前提是該表具有唯一地索引。刪除后指針不動(dòng),下面地行自動(dòng)上移。其一般格式為:DELETE表名WHERECURRENTOF游標(biāo)名修改表與當(dāng)前游標(biāo)位置對(duì)應(yīng)地行。UPDATE表名SET列名=值[,…n]WHERECURRENTOF游標(biāo)名事務(wù)地游標(biāo)事務(wù)被提/回退時(shí),游標(biāo)不會(huì)自動(dòng)關(guān)閉,須用CLOSE關(guān)閉。但如果"SETCLOSEONENDTRAN"選項(xiàng)為ON時(shí),則在事務(wù)被提/回退時(shí)會(huì)自動(dòng)關(guān)閉游標(biāo)。SQLServer對(duì)該選項(xiàng)默認(rèn)為OFF。存儲(chǔ)過(guò)程存儲(chǔ)過(guò)程（storedprocedure）是一組預(yù)先編譯好地,完成特定功能地代碼,是SQL語(yǔ)句與流程控制語(yǔ)句地集合。SQL是高度非過(guò)程化地語(yǔ)言,存儲(chǔ)過(guò)程是對(duì)SQL地?cái)U(kuò)展,它結(jié)合了SQL語(yǔ)言地?cái)?shù)據(jù)操作能力與過(guò)程化語(yǔ)言地流程控制能力。任何一個(gè)設(shè)計(jì)良好地?cái)?shù)據(jù)庫(kù)應(yīng)用程序都應(yīng)該用到存儲(chǔ)過(guò)程。同時(shí),幾乎所有地關(guān)系數(shù)據(jù)庫(kù)系統(tǒng)都提供存儲(chǔ)過(guò)程功能。存儲(chǔ)過(guò)程地優(yōu)點(diǎn):存儲(chǔ)過(guò)程執(zhí)行速度快存儲(chǔ)過(guò)程在創(chuàng)建時(shí)就經(jīng)過(guò)了語(yǔ)法檢查與能優(yōu)化,因此在執(zhí)行時(shí)不必再重復(fù)這些步驟。存儲(chǔ)過(guò)程在經(jīng)過(guò)第一次調(diào)用后就駐留在內(nèi)存,不必再經(jīng)過(guò)編譯與優(yōu)化,所以執(zhí)行速度很快。增強(qiáng)系統(tǒng)地安全通過(guò)設(shè)置用戶(hù)只能通過(guò)存儲(chǔ)過(guò)程對(duì)某些關(guān)鍵數(shù)據(jù)行訪(fǎng)問(wèn),而不允許直接使用SQL語(yǔ)句對(duì)數(shù)據(jù)行訪(fǎng)問(wèn),在一定程度上增強(qiáng)了系統(tǒng)地安全。降低網(wǎng)絡(luò)數(shù)據(jù)流量存儲(chǔ)過(guò)程可以包含大量地SQL語(yǔ)句,但存儲(chǔ)過(guò)程作為一個(gè)獨(dú)立地單元來(lái)使用,在行調(diào)用時(shí),只需要使用一條語(yǔ)句就可以實(shí)現(xiàn),所以大大減少了網(wǎng)絡(luò)上地?cái)?shù)據(jù)流量。簡(jiǎn)化應(yīng)用程序設(shè)計(jì)把體現(xiàn)業(yè)務(wù)規(guī)則地程序放入數(shù)據(jù)庫(kù)服務(wù)器,當(dāng)業(yè)務(wù)規(guī)則發(fā)生變化時(shí),在服務(wù)器改變存儲(chǔ)過(guò)程即可,無(wú)需修改任何應(yīng)用程序。存儲(chǔ)過(guò)程地類(lèi)型:存儲(chǔ)過(guò)程系統(tǒng)存儲(chǔ)過(guò)程系統(tǒng)存儲(chǔ)過(guò)程是SQLServer系統(tǒng)預(yù)定義地存儲(chǔ)過(guò)程,用于行系統(tǒng)管理,登錄管理,權(quán)限設(shè)置,數(shù)據(jù)庫(kù)對(duì)象管理,數(shù)據(jù)庫(kù)復(fù)制等操作。這些存儲(chǔ)過(guò)程主要由系統(tǒng)管理員使用,有少部分可通過(guò)授權(quán)被其它用戶(hù)調(diào)用。它們一般需要從系統(tǒng)表獲取信息,從而為系統(tǒng)管理員行系統(tǒng)管理提供支持。系統(tǒng)存儲(chǔ)過(guò)程在SQLServer存放在系統(tǒng)數(shù)據(jù)庫(kù)master,并且以sp_或xp_為前綴。存儲(chǔ)過(guò)程地類(lèi)型:存儲(chǔ)過(guò)程用戶(hù)自定義存儲(chǔ)過(guò)程用戶(hù)自定義存儲(chǔ)過(guò)程是由用戶(hù)創(chuàng)建并能完成某一特定功能地代碼集。用戶(hù)可以把常用地?cái)?shù)據(jù)庫(kù)處理功能設(shè)計(jì)為存儲(chǔ)過(guò)程,并把它放在數(shù)據(jù)庫(kù)服務(wù)器,就可以在各個(gè)程序重復(fù)調(diào)用,減輕程序編寫(xiě)地工作量,也避免大量地?cái)?shù)據(jù)在網(wǎng)絡(luò)上傳輸,減少網(wǎng)絡(luò)流量。存儲(chǔ)過(guò)程可以接受參數(shù)與輸出參數(shù),并返回執(zhí)行地狀態(tài)信息,還可以嵌套使用,但不能直接在表達(dá)式使用。在SQLServer,用戶(hù)自定義存儲(chǔ)過(guò)程有兩種:T-SQL存儲(chǔ)過(guò)程與CLR（monLanguageRuntime）存儲(chǔ)過(guò)程。T-SQL存儲(chǔ)過(guò)程是指用T-SQL編寫(xiě)地存儲(chǔ)過(guò)程,它是T-SQL地集合。CLR存儲(chǔ)過(guò)程是指應(yīng)用Microsoft.Framework公語(yǔ)言運(yùn)行時(shí)方法地存儲(chǔ)過(guò)程,它在.Framework程序集是作為類(lèi)地公靜態(tài)方法實(shí)現(xiàn)地。存儲(chǔ)過(guò)程地類(lèi)型:存儲(chǔ)過(guò)程用戶(hù)自定義存儲(chǔ)過(guò)程用戶(hù)自定義存儲(chǔ)過(guò)程是由用戶(hù)創(chuàng)建并能完成某一特定功能地代碼集。用戶(hù)可以把常用地?cái)?shù)據(jù)庫(kù)處理功能設(shè)計(jì)為存儲(chǔ)過(guò)程,并把它放在數(shù)據(jù)庫(kù)服務(wù)器,就可以在各個(gè)程序重復(fù)調(diào)用,減輕程序編寫(xiě)地工作量,也避免大量地?cái)?shù)據(jù)在網(wǎng)絡(luò)上傳輸,減少網(wǎng)絡(luò)流量。存儲(chǔ)過(guò)程可以接受參數(shù)與輸出參數(shù),并返回執(zhí)行地狀態(tài)信息,還可以嵌套使用,但不能直接在表達(dá)式使用。在SQLServer,用戶(hù)自定義存儲(chǔ)過(guò)程有兩種:T-SQL存儲(chǔ)過(guò)程與CLR（monLanguageRuntime）存儲(chǔ)過(guò)程。T-SQL存儲(chǔ)過(guò)程是指用T-SQL編寫(xiě)地存儲(chǔ)過(guò)程,它是T-SQL地集合。CLR存儲(chǔ)過(guò)程是指應(yīng)用Microsoft.Framework公語(yǔ)言運(yùn)行時(shí)方法地存儲(chǔ)過(guò)程,它在.Framework程序集是作為類(lèi)地公靜態(tài)方法實(shí)現(xiàn)地。存儲(chǔ)過(guò)程地類(lèi)型:存儲(chǔ)過(guò)程擴(kuò)展存儲(chǔ)過(guò)程擴(kuò)展存儲(chǔ)過(guò)程允許使用高級(jí)編程語(yǔ)言（如C,C++語(yǔ)言等）創(chuàng)建應(yīng)用程序地外部例程,從而使得SQLServer地實(shí)例可以動(dòng)態(tài)地加載與運(yùn)行動(dòng)態(tài)鏈接庫(kù)（DLL）函數(shù)。其名稱(chēng)以xp_為前綴,使用時(shí)按照存儲(chǔ)過(guò)程地方法執(zhí)行。存儲(chǔ)過(guò)程地創(chuàng)建與執(zhí)行:存儲(chǔ)過(guò)程創(chuàng)建存儲(chǔ)過(guò)程在查詢(xún)編程器使用T-SQL語(yǔ)句CREATEPROCEDURE（可簡(jiǎn)寫(xiě)PROC）語(yǔ)句實(shí)現(xiàn)用戶(hù)自定義存儲(chǔ)過(guò)程地創(chuàng)建,其一般格式為:CREATEPROCEDURE過(guò)程名[;序號(hào)][參數(shù)一類(lèi)型[=默認(rèn)值][OUTPUT],…]AS過(guò)程體其,序號(hào)為可選地整數(shù),當(dāng)存儲(chǔ)過(guò)程出現(xiàn)同名現(xiàn)象時(shí),需為同名地存儲(chǔ)過(guò)程分別指定不同地地序號(hào),以示區(qū)別;存儲(chǔ)過(guò)程地參數(shù)可以有輸入?yún)?shù),輸出參數(shù),如果是輸出參數(shù),需要在參數(shù)后面指定output關(guān)鍵字。此外,在創(chuàng)建存儲(chǔ)過(guò)程時(shí),下面地語(yǔ)句是不能使用地。CREATEAGGREGATE,CREATEDEFAULT,CREATERULE語(yǔ)句。CREATE或ALTERVIEW,CREATE或ALTERPROCEDURE,CREATE或ALTERTRIGGER,CREATE或ALTERFUNCTION語(yǔ)句。SETPARSEONLY,SETSHOWPLAN_ALL,SETSHOWPLAN_TEXT,SETSHOWPLAN_XML語(yǔ)句。USEdatabase_name語(yǔ)句。存儲(chǔ)過(guò)程地創(chuàng)建與執(zhí)行:存儲(chǔ)過(guò)程執(zhí)行存儲(chǔ)過(guò)程T-SQL語(yǔ)言使用EXECUTE（可簡(jiǎn)寫(xiě)EXEC）語(yǔ)句執(zhí)行存儲(chǔ)過(guò)程地。其一般格式為:EXECUTE過(guò)程名[;序號(hào)]存儲(chǔ)過(guò)程地修改與刪除:存儲(chǔ)過(guò)程修改存儲(chǔ)過(guò)程T-SQL語(yǔ)言使用ALTERPROCEDURE語(yǔ)句修改存儲(chǔ)過(guò)程,其一般格式為:ALTERPROCEDURE過(guò)程名[;序號(hào)][參數(shù)一類(lèi)型[=默認(rèn)值],…]AS過(guò)程體刪除存儲(chǔ)過(guò)程T-SQL語(yǔ)言使用DROPPROCEDURE語(yǔ)句刪除存儲(chǔ)過(guò)程,其一般格式為:DROPPROCEDURE過(guò)程名[;序號(hào)]存儲(chǔ)過(guò)程與函數(shù):存儲(chǔ)過(guò)程存儲(chǔ)過(guò)程與用戶(hù)自定義函數(shù)都有類(lèi)似地功能,其目地都是為了捆綁一組SQL語(yǔ)句,并存儲(chǔ)在服務(wù)器供反復(fù)使用,以提高工作效率。它們其實(shí)本質(zhì)上并沒(méi)有區(qū)別,但兩者之間還是有一些雖然細(xì)微但很重要地差異。

存儲(chǔ)過(guò)程用戶(hù)自定義函數(shù)聲明方式關(guān)鍵字PROCEDURE關(guān)鍵字FUNCTION返回類(lèi)型不需要描述返回類(lèi)型需要描述返回類(lèi)型,且需要有一個(gè)RETURN語(yǔ)句返回值可沒(méi)有返回值需要有返回值可返回多個(gè)值,但不能返回表只能返回一個(gè)值,可返回表可以返回參數(shù)不可返回參數(shù)參數(shù)可有輸入與輸出參數(shù)只有輸入?yún)?shù)調(diào)用方式需要用EXECUTE單獨(dú)調(diào)用不能單獨(dú)調(diào)用不能賦值一個(gè)變量可以賦值一個(gè)變量SQL語(yǔ)句不可用存儲(chǔ)過(guò)程①當(dāng)函數(shù)返回標(biāo)量值時(shí),可作為SELECT語(yǔ)句地一部分②當(dāng)函數(shù)返回表值時(shí),可位于FROM關(guān)鍵字后面DML操作可作插入,修改,刪除表操作不可作插入,修改,刪除表操作限制限制較少限制較多,比如不能用臨時(shí)表等觸發(fā)器觸發(fā)器（trigger）是用戶(hù)定義在關(guān)系表上地一種特殊存儲(chǔ)過(guò)程。其特殊在于它不需要由用戶(hù)調(diào)用執(zhí)行,而是當(dāng)用戶(hù)對(duì)表地?cái)?shù)據(jù)行INSERT,UPDATE或DELETE操作時(shí)自動(dòng)觸發(fā)執(zhí)行。觸發(fā)器通常用于保證業(yè)務(wù)規(guī)則與數(shù)據(jù)完整約束。與完整約束相比,觸發(fā)器可以行更為復(fù)雜地檢查與操作,具有更精細(xì)與更強(qiáng)大地?cái)?shù)據(jù)控制能力。大多數(shù)DBMS都支持觸發(fā)器,其功能主要包括:觸發(fā)器可以通過(guò)級(jí)聯(lián)地方式對(duì)有關(guān)表行修改。通過(guò)級(jí)聯(lián)引用完整約束可以更有效地執(zhí)行這些修改。例如,某個(gè)表上地觸發(fā)器包含對(duì)另一個(gè)表地?cái)?shù)據(jù)修改,從而保證數(shù)據(jù)地一致與完整。觸發(fā)器可以檢查SQL所做地操作是否被允許,從而不允許數(shù)據(jù)庫(kù)未經(jīng)許可地特定更新與變化。觸發(fā)器可以評(píng)估數(shù)據(jù)修改前后地表狀態(tài),并根據(jù)其差異采取對(duì)策。一個(gè)表地多個(gè)同類(lèi)觸發(fā)器允許采取多個(gè)不同地對(duì)策以響應(yīng)同一個(gè)修改語(yǔ)句。觸發(fā)器可以實(shí)現(xiàn)比CHECK約束更加復(fù)雜地約束。與CHECK約束不同,觸發(fā)器可以引用其它表地列。例如,觸發(fā)器可以使用另一個(gè)表地SELECT語(yǔ)句來(lái)比較插入或更新地?cái)?shù)據(jù),以及執(zhí)行其它操作,如修改數(shù)據(jù)或顯示用戶(hù)定義地錯(cuò)誤信息。觸發(fā)器地組成:觸發(fā)器觸發(fā)器也稱(chēng)為Event-Condition-Action規(guī)則（簡(jiǎn)稱(chēng)ECA規(guī)則）,這是由于一個(gè)觸發(fā)器由三部分組成:（Event）。所允許種類(lèi)通常是對(duì)數(shù)據(jù)庫(kù)地插入,修改與刪除等操作。觸發(fā)器在這些發(fā)生時(shí)將自動(dòng)開(kāi)始運(yùn)作

。條件（Condition）。觸發(fā)器將測(cè)試條件是否成立,如果條件成立,就執(zhí)行相應(yīng)地動(dòng)作,否則什么也不做。動(dòng)作（Action）。如果觸發(fā)器滿(mǎn)足測(cè)試條件,那么就由DBMS自動(dòng)執(zhí)行這些動(dòng)作。實(shí)際上,動(dòng)作可以是任何數(shù)據(jù)庫(kù)操作序列,包括與觸發(fā)毫無(wú)并聯(lián)地操作。觸發(fā)器地工作原理:觸發(fā)器一般將觸發(fā)器所依附地基本表稱(chēng)為觸發(fā)器表。當(dāng)在觸發(fā)器表上發(fā)生插入,修改與刪除操作時(shí),DBMS會(huì)自動(dòng)生成兩個(gè)特殊地臨時(shí)表。在不同地DBMS其名稱(chēng)不一樣,如在SQLServer,這兩個(gè)特殊地表分別稱(chēng)為Inserted表與Deleted表。這兩個(gè)表地結(jié)構(gòu)與觸發(fā)器表結(jié)構(gòu)相同,而且只能由創(chuàng)建它們地觸發(fā)器引用。觸發(fā)器會(huì)對(duì)這兩張表行檢查,檢查一些數(shù)據(jù)更新地影響,為觸發(fā)器動(dòng)作設(shè)置條件,不能直接修改這兩張表地?cái)?shù)據(jù)。觸發(fā)器類(lèi)型:觸發(fā)器在SQLServer,根據(jù)激活觸發(fā)器執(zhí)行地T-SQL語(yǔ)句類(lèi)型不同,可以把觸發(fā)器分為兩類(lèi):一類(lèi)是DML觸發(fā)器;另一類(lèi)是DDL觸發(fā)器。DML觸發(fā)器DML觸發(fā)器是當(dāng)數(shù)據(jù)庫(kù)服務(wù)器發(fā)生數(shù)據(jù)操作

語(yǔ)言（DML）時(shí)執(zhí)行地存儲(chǔ)過(guò)程。DML包括在指定基本表或視圖修改數(shù)據(jù)地INSERT語(yǔ)句,UPDATE語(yǔ)句或DELETE語(yǔ)句。根據(jù)定義與應(yīng)用范圍條件,觸發(fā)時(shí)機(jī)不同,又可以把DML觸發(fā)器劃分為兩種類(lèi)型:觸發(fā)器類(lèi)型:觸發(fā)器AFTER觸發(fā)器AFTER觸發(fā)器要求只有在執(zhí)行INSERT,UPDATE或DELETE語(yǔ)句操作之后,觸發(fā)器才被激活,且只能在表上定義?？梢詾橥粋€(gè)表定義多個(gè)觸發(fā)器,也可以為針對(duì)表地同一操作定義多個(gè)觸發(fā)器。對(duì)于A(yíng)FTER觸發(fā)器,可以定義哪一個(gè)觸發(fā)器被最先激活,哪一個(gè)被最后激活,通常使用系統(tǒng)存儲(chǔ)過(guò)程sp_settriggerorder來(lái)完成此任務(wù)。使用AFTER觸發(fā)器需注意以下三點(diǎn)事項(xiàng)。①不能在視圖上定義AFTER觸發(fā)器。②如果一個(gè)INSERT,UPDATE或DELETE語(yǔ)句違反了約束,那么AFTER觸發(fā)器不會(huì)執(zhí)行,因?yàn)閷?duì)約束地檢查是在A(yíng)FTER觸發(fā)器被激活之前發(fā)生地,所以AFTER觸發(fā)器不能超越約束。觸發(fā)器類(lèi)型:觸發(fā)器INSTEADOF觸發(fā)器使用INSTEADOF觸發(fā)器可以代替通常地觸發(fā)動(dòng)作,INSTEADOF觸發(fā)器執(zhí)行時(shí)并不執(zhí)行其所定義地INSERT,UPDATE或DELETE語(yǔ)句操作,而僅是執(zhí)行觸發(fā)器本身,即INSTEADOF觸發(fā)器是在接收到執(zhí)行請(qǐng)求時(shí)被激活,之后地執(zhí)行操作權(quán)給INSTEADOF觸發(fā)器,由它去完成INSERT,UPDATE或DELETE語(yǔ)句地操作。INSTEADOF觸發(fā)器可以定義在表上,也可以定義在視圖上,但對(duì)于同一個(gè)操作,只能定義一個(gè)INSTEADOF觸發(fā)器。使用INSTEADOF觸發(fā)器需注意以下四點(diǎn)事項(xiàng)。①指定執(zhí)行觸發(fā)器而不是執(zhí)行SQL語(yǔ)句,從而替代觸發(fā)語(yǔ)句地操作。②在表或視圖上,每個(gè)INSERT,UPDATE或DELETE語(yǔ)句最多可以定義一個(gè)INSTEADOF觸發(fā)器。然而,可以在每個(gè)具有INSTEADOF觸發(fā)器地視圖上定義視圖。③INSTEADOF觸發(fā)器不能在WITHCHECKOPTION地可更新視圖上定義。如果向指定了WITHCHECKOPTION選項(xiàng)地可更新視圖添加INSTEADOF觸發(fā)器,SQLServer將產(chǎn)生一個(gè)錯(cuò)誤。用戶(hù)需要用ALTERVIEW刪除該選項(xiàng)后才能定義INSTEADOF觸發(fā)器。④INSTEADOF觸發(fā)器可以取代激活它地操作來(lái)執(zhí)行。它在Inserted表與Deleted表剛剛建立,其它任何操作還沒(méi)有發(fā)生時(shí)被執(zhí)行,因?yàn)镮NSTEADOF觸發(fā)器在約束之前執(zhí)行,所以它可以對(duì)約束行一些預(yù)處理。觸發(fā)器類(lèi)型:觸發(fā)器DDL觸發(fā)器DDL觸發(fā)器是SQLServer二零零五版以后新增地功能,是在響應(yīng)數(shù)據(jù)定義語(yǔ)言（DDL）時(shí)執(zhí)行地存儲(chǔ)過(guò)程。這些語(yǔ)句主要是以CREATE,ALTER與DROP開(kāi)關(guān)地語(yǔ)句。DDL觸發(fā)器一般用于執(zhí)行數(shù)據(jù)庫(kù)地管理任務(wù),如防止數(shù)據(jù)庫(kù)表結(jié)構(gòu)被修改等。在以下幾種情況可以使用DDL觸發(fā)器:要防止對(duì)數(shù)據(jù)庫(kù)架構(gòu)行某些更改。希望根據(jù)數(shù)據(jù)庫(kù)發(fā)生地操作以響應(yīng)數(shù)據(jù)庫(kù)架構(gòu)地更改。要記錄數(shù)據(jù)庫(kù)架構(gòu)地更改或。僅在運(yùn)行激活DDL觸發(fā)器地DDL語(yǔ)句后,DDL觸發(fā)器才會(huì)被激活,DDL觸發(fā)器無(wú)法作為INSTEADOF觸發(fā)器使用。觸發(fā)器地創(chuàng)建:觸發(fā)器在SQLServer創(chuàng)建觸發(fā)器有兩種方法:一種是使用對(duì)象資源管理器;一種是使用T-SQL語(yǔ)言。前一種將在七.三詳細(xì)介紹SQLServer時(shí)再講解,這里主要介紹T-SQL創(chuàng)建觸發(fā)器。創(chuàng)建DML觸發(fā)器在查詢(xún)編輯器使用T-SQL地CREATETRIGGER語(yǔ)句創(chuàng)建觸發(fā)器,其一般格式為:CREATETRIGGER觸發(fā)器名ON表名|視圖名{FOR|AFTER|INSTEADOF}{[INSERT][,][UPDATE][,][DELETE]}