SQL Server 函數

函數是由一個或多個 Transact-SQL 陳述式組成的子程式，可用於封裝代碼以便重新使用。

 

系統函數 一組內建函數，對 SQL Server 中的值、對象和設定執行操作，並返回有關它們的資訊。

 

Sql Server基本函數：

1.字串函數
長度與分析用
datalength(Char_expr) 返回字串包含字元數,但不包含後面的空格
substring(expression,start,length) 不多說了,取子串
right(char_expr,int_expr) 返回字串右邊int_expr個字元

字元操作類
upper(char_expr) 轉為大寫
lower(char_expr) 轉為小寫
space(int_expr) 產生int_expr個空格
replicate(char_expr,int_expr)複製字串int_expr次
reverse(char_expr) 反轉字串
stuff(char_expr1,start,length,char_expr2) 將字串char_expr1中的從start開始的length個字元用char_expr2代替
ltrim(char_expr) rtrim(char_expr) 取掉空格
ascii(char) char(ascii) 兩函數對應,取ascii碼,根據ascii嗎取字元

字串尋找
charindex(char_expr,expression) 返回char_expr的起始位置
patindex("%pattern%",expression) 返回指定模式的起始位置,否則為0

2.數學函數
abs(numeric_expr) 求絕對值
ceiling(numeric_expr) 取大於等於指定值的最小整數
exp(float_expr) 取指數
floor(numeric_expr) 小於等於指定值得最大整數
pi() 3.1415926.........
power(numeric_expr,power) 返回power次方
rand([int_expr]) 隨機數產生器
round(numeric_expr,int_expr) 安int_expr規定的精度四捨五入
sign(int_expr) 根據正數,0,負數,,返回+1,0,-1
sqrt(float_expr) 平方根

3.日期函數
getdate() 返回日期
datename(datepart,date_expr) 返回名稱如 June
datepart(datepart,date_expr) 取日期一部份
datediff(datepart,date_expr1.dateexpr2) 日期差
dateadd(datepart,number,date_expr) 返回日期加上 number

上述函數中datepart的
寫法 取值和意義
yy 1753-9999 年份
qq 1-4 刻
mm 1-12 月
dy 1-366 日
dd 1-31 日
wk 1-54 周
dw 1-7 周幾
hh 0-23 小時
mi 0-59 分鐘
ss 0-59 秒
ms 0-999 毫秒

日期轉換
convert()

4.系統函數
suser_name() 使用者登入名稱
user_name() 使用者在資料庫中的名字
user 使用者在資料庫中的名字
show_role() 對目前使用者起作用的規則
db_name() 資料庫名
object_name(obj_id) 資料庫對象名
col_name(obj_id,col_id) 列名
col_length(objname,colname) 列長度
valid_name(char_expr) 是否是有效標識符

 

 

SQL Server 並不將使用者限制在定義為 Transact-SQL 語言一部分的內建函數上，而是允許使用者建立自己的使用者定義函數。

　　可使用 CREATE FUNCTION 語句建立、使用 ALTER FUNCTION 語句修改、以及使用 DROP FUNCTION 語句除去使用者定義函數。每個完全合法的使用者定義函數名 (database_name.owner_name.function_name) 必須唯一。

　　必須被授予 CREATE FUNCTION 許可權才能建立、修改或除去使用者定義函數。不是所有者的使用者在 Transact-SQL 陳述式中使用某個函數之前，必須先給此使用者授予該函數的適當許可權。若要建立或更改在 CHECK 條件約束、DEFAULT 子句或計算資料行定義中引用使用者定義函數的表，還必須具有函數的 REFERENCES 許可權。

　　在函數中，區別處理導致刪除語句並且繼續在諸如觸發器或預存程序等模式中的下一語句的 Transact-SQL 錯誤。在函數中，上述錯誤會導致停止執行函數。接下來該操作導致停止喚醒調用該函數的語句。

　　使用者定義函數的類型

　　SQL Server 2000 支援三種使用者定義函數：

　　純量涵式

　　內嵌資料表值函式

　　多語句資料表值函式

　　使用者定義函數採用零個或更多的輸入參數並返回標量值或表。函數最多可以有 1024 個輸入參數。當函數的參數有預設值時，調用該函數時必須指定預設 DEFAULT 關鍵字才能擷取預設值。該行為不同於在預存程序中含有預設值的參數，而在這些預存程序中省略該函數也意味著省略預設值。使用者定義函數不支援輸出參數。

　　純量涵式返回在 RETURNS 子句中定義的類型的單個資料值。可以使用所有純量資料型別，包括 bigint 和 sql_variant。不支援 timestamp 資料類型、使用者定義資料類型和非標量類型（如 table 或 cursor）。在 BEGIN...END 塊中定義的函數主體包含返回該值的 Transact-SQL 陳述式系列。傳回型別可以是除 text、ntext、image、cursor 和 timestamp 之外的任何資料類型。

　　資料表值函式返回 table。對於內嵌資料表值函式，沒有函數主體；表是單個 SELECT 語句的結果集。對於多語句資料表值函式，在 BEGIN...END 塊中定義的函數主體包含 TRANSACT-SQL 陳述式，這些語句可產生行並將行插入將返回的表中。有關內嵌資料表值函式的更多資訊，請參見內嵌使用者定義函數。有關資料表值函式的更多資訊，請參見返回 table 資料類型的使用者定義函數。

　　BEGIN...END 塊中的語句不能有任何副作用。函數副作用是指對具有函數外範圍（例如資料庫表的修改）的資源狀態的任何永久性更改。函數中的語句唯一能做的更改是對函數上的局部對象（如局部遊標或局部變數）的更改。不能在函數中執行的操作包括：對資料庫表的修改，對不在函數上的局部遊標進行操作，寄送電子郵件，嘗試修改目錄，以及產生返回至使用者的結果集。

　　函數中的有效語句類型包括：

　　DECLARE 語句，該語句可用於定義函數局部的資料變數和遊標。

　　為函數局部對象賦值，如使用 SET 給標量和表局部變數賦值。

　　遊標操作，該操作引用在函數中聲明、開啟、關閉和釋放的局部遊標。不允許使用 FETCH 語句將資料返回到用戶端。僅允許使用 FETCH 語句通過 INTO 子句給局部變數賦值。

　　控制流程語句。

　　SELECT 語句，該語句包含帶有運算式的挑選清單，其中的運算式將值賦予函數的局部變數。

　　INSERT、UPDATE 和 DELETE 語句，這些語句修改函數的局部 table 變數。

　　EXECUTE 語句，該語句調用擴充預存程序。

　　在查詢中指定的函數的實際執行次數在最佳化器產生的執行計畫間可能不同。樣本為 WHERE 子句中的子查詢喚醒調用的函數。子查詢及其函數執行的次數會因最佳化器選擇的訪問路徑而異。

　　使用者定義函數中不允許使用會對每個調用返回不同資料的內建函數。使用者定義函數中不允許使用以下內建函數：

　　@@CONNECTIONS @@PACK_SENT GETDATE

　　@@CPU_BUSY @@PACKET_ERRORS GetUTCDate

　　@@IDLE @@TIMETICKS NEWID

　　@@IO_BUSY @@TOTAL_ERRORS RAND

　　@@MAX_CONNECTIONS @@TOTAL_READ TEXTPTR

　　@@PACK_RECEIVED @@TOTAL_WRITE

　　架構綁定函數

　　CREATE FUNCTION 支援 SCHEMABINDING 子句，後者可將函數綁定到它引用的任何對象（如表、視圖和其它使用者定義函數）的架構。嘗試對架構綁定函數所引用的任何對象執行 ALTER 或 DROP 都將失敗。

　　必須滿足以下條件才能在 CREATE FUNCTION 中指定 SCHEMABINDING：

　　該函數所引用的所有視圖和使用者定義函數必須是綁定到架構的。

　　該函數所引用的所有對象必須與函數位於同一資料庫中。必須使用由一部分或兩部分構成的名稱來引用對象。

　　必須具有對該函數中引用的所有對象（表、視圖和使用者定義函數）的 REFERENCES 許可權。

　　可使用 ALTER FUNCTION 刪除架構綁定。ALTER FUNCTION 語句將通過不帶 WITH SCHEMABINDING 指定函數來重新定義函數。

　　調用使用者定義函數

　　當調用標量使用者定義函數時，必須提供至少由兩部分組成的名稱：

　　SELECT *, MyUser.MyScalarFunction()

　　FROM MyTable

　　可以使用一個部分構成的名稱調用資料表值函式：

　　SELECT *

　　FROM MyTableFunction()

　　然而，當調用返回表的 SQL Server 內建函數時，必須將首碼 :: 添加至函數名：

　　SELECT * FROM ::fn_helpcollations()

　　可在 Transact-SQL 陳述式中所允許的函數返回的相同資料類型運算式所在的任何位置引用純量涵式，包括計算資料行和 CHECK 條件約束定義。例如，下面的語句建立一個返回 decimal 的簡單函數：

　　CREATE FUNCTION CubicVolume

　　-- Input dimensions in centimeters

　　(@CubeLength decimal(4,1), @CubeWidth decimal(4,1),

　　@CubeHeight decimal(4,1) )

　　RETURNS decimal(12,3) -- Cubic Centimeters.

　　AS

　　BEGIN

　　RETURN ( @CubeLength * @CubeWidth * @CubeHeight )

　　END

　　然後可以在允許整型運算式的任何地方（如表的計算資料行中）使用該函數：

　　CREATE TABLE Bricks

　　(

　　BrickPartNmbr int PRIMARY KEY,

　　BrickColor nchar(20),

　　BrickHeight decimal(4,1),

　　BrickLength decimal(4,1),

　　BrickWidth decimal(4,1),

　　BrickVolume AS

　　(

　　dbo.CubicVolume(BrickHeight,

　　BrickLength, BrickWidth)

　　)

　　)

　　dbo.CubicVolume 是返回標量值的使用者定義函數的一個樣本。RETURNS 子句定義由該函數返回的值的純量資料型別。BEGIN...END 塊包含一個或多個執行該函數的 Transact-SQL 陳述式。該函數中的每個 RETURN 語句都必須具有一個參數，可返回具有在 RETURNS 子句中指定的資料類型（或可隱性轉換為 RETURNS 中指定類型的資料類型）的資料值。RETURN 參數的值是該函數返回的值。