復習

1什么是存儲過程？2什么是觸發器？1第1頁第1頁1什么是存儲過程？系統存儲過程當地存儲過程例：my_procdure22第2頁第2頁程序運營3第3頁第3頁按位置傳遞參數4第4頁第4頁使用參數名傳送參數值5第5頁第5頁USEpubsgoCREATETRIGGERmy_trigger1ONauthorsFORINSERTASraiserror(‘你已添加了數據’,10,1)2什么是觸發器？insteadofINSERTASraiserror(‘你不能添加數據’,10,1)6第6頁第6頁觸發器作用？約束觸發器強制數據完整性數據類型7第7頁第7頁第九章用戶自定義函數

8第8頁第8頁什么是函數？

是一組編譯好Transact-SQL語句，他們能夠帶一個或一組數值做參數，也能夠不帶參數，它返回一個數值、數值集合，或執行一些操作。函數能夠重復執行一些操作，從而避免不斷重寫代碼。9第9頁第9頁函數類型

SQLServer支持用戶定義函數和內置函數。1內置函數

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

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

FUNCTION

my_function1

(@moneyinputmoney)RETURNS

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

自定義函數名稱保留在sysobjects系統表中創建自定義函數源代碼保留在syscomments系統表中28第28頁第28頁（1）使用系統存儲過程查看EXECsp_help(sp_helptext)<function-name>例：用系統存儲過程sp_helptext查看用戶自定義函數my_funciton1定義文本信息USENorthwindgoEXECsp_helptextmy_function1go29第29頁第29頁30第30頁第30頁（2）使用系統表或系統信息架構視圖在SQL

Server中存在三個信息架構視圖匯報相關用戶自定義函數信息ROUTINESPARAMETERSROUTINE_COLUMNS這些信息架構視圖也是基于系統表sysobjects和syscomments實現31第31頁第31頁例：使用系統表sysobjects查看數據庫Northwind上存在所有用戶自定義函數相關信息。32第32頁第32頁9.3用戶自定義函數類型標量函數自定義函數有三種類型內嵌表值函數多語句表值函數33第33頁第33頁1標量函數標量函數返回在RETURNS子句中定義數據類型單個數據值標量函數可重復調用34第34頁第34頁例：my_function135第35頁第35頁36例：創建標量函數，要求將當前系統日期轉化為年月日格式字符串并返回，且默認分隔符為‘::’，并允許用戶自行定義分隔符convert(數據類型，字段名，日期樣式編號)36第36頁第36頁3737第37頁第37頁BEGINDECLARE

@returnstringnvarchar(20)CREATE

FUNCTIONmy_function2(@datedatetime,

@separatornvarchar(2)='::')RETURNSnvarchar(20)38第38頁第38頁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頁第39頁執行程序40第40頁第40頁41第41頁第41頁2內嵌表值函數

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

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

Relationship

FROMCustomersWHERECity=@RegionParameterUNION47第47頁第47頁執行命令48第48頁第48頁49下面示例使用新建立內嵌表值函數my_function3來獲取“巴黎”市所有客戶和供應商信息49第49頁第49頁3多語句表值函數多語句表值函數主體中允許使用語句賦值控制流DECLARESELECT游標操作INSERTUPDATEDELETEEXECUTE50第50頁第50頁51例:在Northwind庫上利用視圖Customer

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

ALTER

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

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