復(fù)習(xí)

1什么是存儲(chǔ)過(guò)程？2什么是觸發(fā)器？1第1頁(yè)第1頁(yè)1什么是存儲(chǔ)過(guò)程？系統(tǒng)存儲(chǔ)過(guò)程當(dāng)?shù)卮鎯?chǔ)過(guò)程例：my_procdure22第2頁(yè)第2頁(yè)程序運(yùn)營(yíng)3第3頁(yè)第3頁(yè)按位置傳遞參數(shù)4第4頁(yè)第4頁(yè)使用參數(shù)名傳送參數(shù)值5第5頁(yè)第5頁(yè)USEpubsgoCREATETRIGGERmy_trigger1ONauthorsFORINSERTASraiserror(‘你已添加了數(shù)據(jù)’,10,1)2什么是觸發(fā)器？insteadofINSERTASraiserror(‘你不能添加數(shù)據(jù)’,10,1)6第6頁(yè)第6頁(yè)觸發(fā)器作用？約束觸發(fā)器強(qiáng)制數(shù)據(jù)完整性數(shù)據(jù)類(lèi)型7第7頁(yè)第7頁(yè)第九章用戶(hù)自定義函數(shù)

8第8頁(yè)第8頁(yè)什么是函數(shù)？

是一組編譯好Transact-SQL語(yǔ)句，他們能夠帶一個(gè)或一組數(shù)值做參數(shù)，也能夠不帶參數(shù)，它返回一個(gè)數(shù)值、數(shù)值集合，或執(zhí)行一些操作。函數(shù)能夠重復(fù)執(zhí)行一些操作，從而避免不斷重寫(xiě)代碼。9第9頁(yè)第9頁(yè)函數(shù)類(lèi)型

SQLServer支持用戶(hù)定義函數(shù)和內(nèi)置函數(shù)。1內(nèi)置函數(shù)

是一組預(yù)定義函數(shù)，是Transact-SQL語(yǔ)言一部分，按Transact-SQL參考中定義方式運(yùn)營(yíng)且不能修改。10第10頁(yè)第10頁(yè)聚合函數(shù)函數(shù)功效sum(expression)計(jì)算一組數(shù)據(jù)和avg(expression)計(jì)算一組數(shù)據(jù)平均值max(expression)計(jì)算一組數(shù)據(jù)最大值min(expression)計(jì)算一組數(shù)據(jù)最小值count(expression|*)計(jì)算總行數(shù)11第11頁(yè)第11頁(yè)系統(tǒng)函數(shù)函數(shù)功效CASE表示式計(jì)算條件列表，并返回多個(gè)表示式也許結(jié)果之一CAST(expressionasdata_type)將表示式顯式轉(zhuǎn)化為另一個(gè)數(shù)據(jù)類(lèi)型CONVERT(data_type[(length)],expression[,style])將表示式顯式轉(zhuǎn)化為另一個(gè)數(shù)據(jù)類(lèi)型12第12頁(yè)第12頁(yè)日期函數(shù)函數(shù)功效GETDATE()返回服務(wù)器當(dāng)前系統(tǒng)日期和時(shí)間DATAPART(日期元素，日期)返回指定日期一部分，用整數(shù)返回YEAR(日期)返回年份(整數(shù))MONTH(日期)返回月份(整數(shù))DAY(日期)返回某月幾號(hào)整數(shù)值13第13頁(yè)第13頁(yè)14getdate()得到當(dāng)前系統(tǒng)日期時(shí)間year()得到日期參數(shù)年份14第14頁(yè)第14頁(yè)1515第15頁(yè)第15頁(yè)169.1用戶(hù)自定義函數(shù)概述2它能夠擁有零個(gè)、一個(gè)或多個(gè)參數(shù)，必須有返回值1用戶(hù)定義函數(shù)是用戶(hù)定義Transact-SQL函數(shù)3返回值能夠是單個(gè)數(shù)值（標(biāo)量值），也能夠是一個(gè)表概念

16第16頁(yè)第16頁(yè)171創(chuàng)建環(huán)節(jié)與辦法9.2創(chuàng)建用戶(hù)自定義函數(shù)17第17頁(yè)第17頁(yè)18例：創(chuàng)建一個(gè)自定義函數(shù)，計(jì)算某人到現(xiàn)在為止工齡工資思緒：工齡工資=已工作年數(shù)*工作一年工齡工資已工作年數(shù)=當(dāng)前時(shí)間–開(kāi)始參與工作時(shí)間工齡工資=(當(dāng)前時(shí)間-開(kāi)始參與工作時(shí)間)*年工齡工資18第18頁(yè)第18頁(yè)1919第19頁(yè)第19頁(yè)例：在Northwind庫(kù)上創(chuàng)建自定義函數(shù)my_function1，該函數(shù)實(shí)現(xiàn)輸入代表商品年銷(xiāo)售額money類(lèi)型參數(shù)值后返回字符串，假如年銷(xiāo)售額不小于10000，返回“熱銷(xiāo)商品”，不然返回“非熱銷(xiāo)商品”20第20頁(yè)第20頁(yè)USENorthwindgoCREATE

FUNCTION

my_function1

(@moneyinputmoney)RETURNS

nvarchar(5)返回值數(shù)據(jù)類(lèi)型21第21頁(yè)第21頁(yè)IF@moneyinput<10000SET@returnstring='非熱銷(xiāo)商品'BEGINENDDECLARE@returnstringnvarchar(5)ELSESET@returnstring='熱銷(xiāo)商品'RETURN@returnstring返回變量22第22頁(yè)第22頁(yè)執(zhí)行命令23第23頁(yè)第23頁(yè)在Northwind庫(kù)上有一個(gè)統(tǒng)計(jì)各種產(chǎn)品在1997年銷(xiāo)售額視圖，通過(guò)它來(lái)引用新建my_function1，查看哪些商品屬于熱銷(xiāo)商品視圖函數(shù)調(diào)用24第24頁(yè)第24頁(yè)25商品名稱(chēng)銷(xiāo)售額銷(xiāo)售情況25第25頁(yè)第25頁(yè)如何調(diào)用用戶(hù)自定義函數(shù)？Use數(shù)據(jù)庫(kù)goSelect函數(shù)名(詳細(xì)參數(shù)值|字段名)[from表名][where條件]26第26頁(yè)第26頁(yè)27用戶(hù)自定義函數(shù)名稱(chēng)應(yīng)是惟一一個(gè)函數(shù)最多能夠定義1024個(gè)參數(shù)，每個(gè)參數(shù)前用“@”符號(hào)標(biāo)明參數(shù)作用范圍是整個(gè)函數(shù)參數(shù)只能替換常量，不能替換表名、列名或其它數(shù)據(jù)庫(kù)對(duì)象名稱(chēng)用戶(hù)自定義函數(shù)不支持輸出參數(shù)注意：27第27頁(yè)第27頁(yè)2查看用戶(hù)自定義函數(shù)

自定義函數(shù)名稱(chēng)保留在sysobjects系統(tǒng)表中創(chuàng)建自定義函數(shù)源代碼保留在syscomments系統(tǒng)表中28第28頁(yè)第28頁(yè)（1）使用系統(tǒng)存儲(chǔ)過(guò)程查看EXECsp_help(sp_helptext)<function-name>例：用系統(tǒng)存儲(chǔ)過(guò)程sp_helptext查看用戶(hù)自定義函數(shù)my_funciton1定義文本信息USENorthwindgoEXECsp_helptextmy_function1go29第29頁(yè)第29頁(yè)30第30頁(yè)第30頁(yè)（2）使用系統(tǒng)表或系統(tǒng)信息架構(gòu)視圖在SQL

Server中存在三個(gè)信息架構(gòu)視圖匯報(bào)相關(guān)用戶(hù)自定義函數(shù)信息ROUTINESPARAMETERSROUTINE_COLUMNS這些信息架構(gòu)視圖也是基于系統(tǒng)表sysobjects和syscomments實(shí)現(xiàn)31第31頁(yè)第31頁(yè)例：使用系統(tǒng)表sysobjects查看數(shù)據(jù)庫(kù)Northwind上存在所有用戶(hù)自定義函數(shù)相關(guān)信息。32第32頁(yè)第32頁(yè)9.3用戶(hù)自定義函數(shù)類(lèi)型標(biāo)量函數(shù)自定義函數(shù)有三種類(lèi)型內(nèi)嵌表值函數(shù)多語(yǔ)句表值函數(shù)33第33頁(yè)第33頁(yè)1標(biāo)量函數(shù)標(biāo)量函數(shù)返回在RETURNS子句中定義數(shù)據(jù)類(lèi)型單個(gè)數(shù)據(jù)值標(biāo)量函數(shù)可重復(fù)調(diào)用34第34頁(yè)第34頁(yè)例：my_function135第35頁(yè)第35頁(yè)36例：創(chuàng)建標(biāo)量函數(shù)，要求將當(dāng)前系統(tǒng)日期轉(zhuǎn)化為年月日格式字符串并返回，且默認(rèn)分隔符為‘::’，并允許用戶(hù)自行定義分隔符convert(數(shù)據(jù)類(lèi)型，字段名，日期樣式編號(hào))36第36頁(yè)第36頁(yè)3737第37頁(yè)第37頁(yè)BEGINDECLARE

@returnstringnvarchar(20)CREATE

FUNCTIONmy_function2(@datedatetime,

@separatornvarchar(2)='::')RETURNSnvarchar(20)38第38頁(yè)第38頁(yè)SET@returnstring='今天是'+CONVERT(nvarchar(5),datepart(year,@date))+‘年’+@separator+CONVERT(nvarchar(5),datepart(month,@date))+‘月’+@separator+CONVERT(nvarchar(5),datepart(day,@date))+'日‘RETURN@returnstringEND39第39頁(yè)第39頁(yè)執(zhí)行程序40第40頁(yè)第40頁(yè)41第41頁(yè)第41頁(yè)2內(nèi)嵌表值函數(shù)

在內(nèi)嵌表值函數(shù)中，RETURN子句中包括有一條單獨(dú)SELECT語(yǔ)句，該語(yǔ)句結(jié)果構(gòu)成了內(nèi)嵌表值函數(shù)所返回表可替換視圖，可用在T-SQL查詢(xún)中允許表或視圖表示式地方42第42頁(yè)第42頁(yè)視圖：受限于單個(gè)SELECT語(yǔ)句，不允許包括用戶(hù)自己提供參數(shù)內(nèi)嵌表值函數(shù)：可包括附加語(yǔ)句，使函數(shù)所包括邏輯比視圖邏輯更強(qiáng)返回表內(nèi)嵌表值函數(shù)還可替換返回單個(gè)結(jié)果集存儲(chǔ)過(guò)程43第43頁(yè)第43頁(yè)例:在Northwind數(shù)據(jù)庫(kù)上建立一個(gè)能夠依據(jù)輸入都市名返回所有該都市客戶(hù)和供應(yīng)商信息內(nèi)嵌表值函my_function344第44頁(yè)第44頁(yè)45假如已有一個(gè)視圖（都市中客戶(hù)與供應(yīng)商列表）“CustomerandSuppliersbyCity”45第45頁(yè)第45頁(yè)USENorthwindGoCREATE

FUNCTIONmy_function3(@RegionParameternvarchar(25))RETURNStableas46第46頁(yè)第46頁(yè)RETURNSELECTCity,CompanyName,ContactName,'Suppliers‘FROMSuppliersWHERECity=@RegionParameter)(SELECTCity,CompanyName,ContactName,'Customers'AS

Relationship

FROMCustomersWHERECity=@RegionParameterUNION47第47頁(yè)第47頁(yè)執(zhí)行命令48第48頁(yè)第48頁(yè)49下面示例使用新建立內(nèi)嵌表值函數(shù)my_function3來(lái)獲取“巴黎”市所有客戶(hù)和供應(yīng)商信息49第49頁(yè)第49頁(yè)3多語(yǔ)句表值函數(shù)多語(yǔ)句表值函數(shù)主體中允許使用語(yǔ)句賦值控制流DECLARESELECT游標(biāo)操作INSERTUPDATEDELETEEXECUTE50第50頁(yè)第50頁(yè)51例:在Northwind庫(kù)上利用視圖Customer

andSuppliersbyCity（某一都市客戶(hù)與供應(yīng)商），建立一個(gè)能夠依據(jù)輸入都市名和用戶(hù)，要求返回所有該都市客戶(hù)或供應(yīng)商信息多語(yǔ)句表值函數(shù)my_function4，并將其與上例建立內(nèi)嵌表值函數(shù)my_function3對(duì)比51第51頁(yè)第51頁(yè)下面舉例闡明使用多語(yǔ)句表值函數(shù)my_function4分別獲取來(lái)自Paris所有客戶(hù)和供應(yīng)商信息。5252第52頁(yè)第52頁(yè)9.4修改和刪除用戶(hù)自定義函數(shù)1修改用戶(hù)自定義函數(shù)

ALTER

FUNCTION[ower-name,]function-name([{@parameter-namescalar-parameter-datetype[=default]}[,…n]])RETURNSscalar-return-datatype[WITH<function-option>[,…n]][AS]BEGINsql-statementRETURNscalar-expressionEND53第53頁(yè)第53頁(yè)例：修改在Northwind庫(kù)上創(chuàng)建自定義函數(shù)my_function1，將區(qū)別商品是否熱銷(xiāo)年銷(xiāo)售額由當(dāng)初10000提升到0ALTER

FUNCTIONmy_function1(@moneyinputmoney)RETURNSnvarchar(5)54第54頁(yè)第54頁(yè)BEGINDECLARE@returnstringnva