T-SQL-自訂函數

使用者自訂函數
自訂函數不能執行一系列改變資料庫狀態的操作，可以像系統函數在查詢或預存程序等的程式中使用，也可以像相信過程一樣能過execute命令來執行。自訂函數中儲存了一個Transact - SQL常式可以返回一定的值。根據函數傳回值形式的不同，將使用者看定義函數分為三種類型：
( 1 ).標量型函數：標量型函數返回一個確定類型的標量值，其傳回值類型為除了text, ntext , image , cursor ,timestampt和table類型外的其它資料類型。函數體語句定義在begin - end語句內，其中包含了可以傳回值的Transact - SQL命令。
文法：
 create   function   [ owner_name ]  function_name
( [ {@parameter_name [as ]  scalar_parameter_data_type [ =default ] }  [ ,n ] ])
 returns  scalar_return_data_type
 [ with <function_option> [,n ] ]
 [ as ]
 begin
function_body
 return   [ scalar_expression ]
 end
其中：
 < 1 > .function_option有兩個可選值：{encryption | schemabinding}
encryption:加密選項，讓SQL Server對系統資料表中有關create function的聲明加密，以防止使用者自訂函數作為SQL Server複製的一部分被發布。
schemabinding，計劃綁定選項。將使用者自訂函數綁定到它所引用的資料庫物件，則函數所涉及的的資料庫物件從此將不能被刪除或修改，除非函數被刪除或去掉此選項。應注意的是要繫結資料庫對象必須與函數在同一資料庫中。
 < 2 > .owner_name:指定使用者自訂函數的所有者。
 < 3 > .function_name:指定使用者自訂函數的名稱。
 < 4 > .database_name.owner_name.function_name應是唯一的。
 < 5 > . @parameter_name :定義一個式多個參數的名稱，一個函數最多可以定義1024個參數，每個參數前用 @符號標明 ，參數的作用範圍是整個函數，參數只能替代常量，不能替代表名，列名或其它資料庫物件名稱，使用者自訂函數不支援輸出參數。
 < 6 > .scalar_parameter_data_type:指定標量參數的資料類型，除了text, ntext , image , cursor ,timestampt和table類型外的其它資料類型。
 < 7 > .scalar_return_data_type:指定標量傳回值的資料類型，除了text, ntext , image , cursor ,timestampt和table類型外的其它資料類型。
 < 8 > .scalar_expression:指定標量型使用者自訂函數返回的標量值運算式。
 < 9 > .function_body:指定一系列的Transact_SQL語句它們決定了函數的傳回值。

例：建立軍工齡工資計算函數
 use  taihang
 go
 -- 建立函數
 create   function  workyearwage( @hiredate   datetime , @today   datetime , @per_wage   money )
 -- hiredate表示僱傭期,today表示當前日期，par_wage表示每一年的工齡應得的工資額
 returns   money
 as   begin
 declare   @workyearwage   money
 set   @workyearwage = ( year ( @today ) - year ( @hiredate )) * @per_wage
 return ( @workyearwage )
 end -- 結束函數定義
 go
 -- 調用函數
 select  taihang.dbo.workyearwage( ' 1999-7-1 ' , getdate (), 15 )
 as  work_year_wage
注意：以上的函數體可簡寫為:
 as   begin
 return (( year ( @today ) - year ( @hiredate )) * @per_wage )
 end
 
( 2 ).內嵌資料表值函式：以表的形式返回一個傳回值，即它返回的是一個表。內嵌表值型函數沒有由begin - end語句括起來的函數體，其返回的表由一個位於return子句中的select命令段從資料庫中篩先出來。內嵌表值型函數功能相當於一個參數化的視圖。
建立函數文法：
 create   function   [ owner_name ]  function_name
( [ {@parameter_name [as ]  scalar_parameter_data_type [ =default ] }  [ ,n ] ])
 returns   table
 [ with <function_option> [,n ] ]
 [ as ]
 return  ( select - stmt)
其中：
 < 1 > . table :批定傳回值為一個表。
 < 2 > . select - stmt:單個select語句，確定返回的表的資料。
例：建立返回所有訂購某類產品的公司資訊函數
 use  taihang
 go
 create   function  orderfirms( @productid   varchar ( 30 ))
 -- productid表示產品代號
 returns   table
 as
 return ( select   *   from  products p
        where  p.p_id = @productid )
 go
 
( 3 ).多語句表值型函數：可以看作標量型和內嵌表值型函數的結合體，它的傳回值是一個表，但它和標量型函數一樣有一個用begin - end語句括起來的函數體。傳回值表中的資料是由函數體
文法：
 create   function   [ owner_name ]  function_name
( [ {@parameter_name [as ]  scalar_parameter_data_type [ =default ] }  [ ,n ] ])
 returns   @return_variable   table   < table_type_definition >
 [ with <function_option> [,n ] ]
 [ as ]
 begin
function_body
 return   end
其中：
 < 1 > . < table_type_definition > :({column_definition | table_constraint} [ ,n ] )
 < 2 > . @return_variable :一個table類型的變數，用於儲存和累積返回的表中的資料行。

 4 .修改使用者自訂函數
 alter   function :此命令文法與create function相同，相當於重建。
 5 .刪除使用者自訂函數
 drop   function { [ owner_name ] function_name}  [ ,n ]

五、函數
在Transact - SQL語言中，函數被用來執行一些特殊的運算以支援SQL Server的標準命令。
( 1 ).行集合函式：行集合函式可以在transact - SQL語句中當作表引用。
( 2 ).彙總函式：用於一組值執行計算並返回一個單一的值。
( 3 ).純量涵式：用於對傳遞給它的一個或者多個參數值進行處理和計算，並返回一個單一的值.
(一)、純量涵式的分類
 1 .配置函數：返回當前的配置資訊
 2 .遊標函數：返回有關遊標的資訊
 3 .日期和時間函數：用於對日期和時間類型的輸入值進行操作，返回一個了子符串，數字或日期和時間值
 4 .數學函數：用於對作為函數參數提供的輸入值執行操作，返回一個數字值
 5 .中繼資料函數：返回有關資料庫和對象和資訊
 6 .字串函數：對字串輸入值執行操作，並返回一個字中或數字值
 7 .系統函數：執行系統操作
 8 .系統統計函數：返回系統的統計資訊
 9 .文本和映像函數：對於文本或映像輸入值或列執行操作，返回有關這些值的資訊。

具體講解：
 1 .系統函數
用於返回有關SQL Server系統，使用者，資料庫和資料庫物件的資訊。系統函數可以讓使用者在得到資訊後，使用條件陳述式，根據返回的資訊進行不同的操作。與其它函數一樣，可以在select語句的select和where子句經及運算式中使用系統函數。
例：返回taihang資料庫的yuan表中的第二列的名稱。
 use  taihang
 select   col_name ( object_id ( ' yuan ' ), 2 )
註：col_name為系統函數， object_id :返回對象的id。

 2 .日期和時間類型
日期和時間函數用於對日期和時間資料進行各種不同的處理和運算，並返回一個字串，數字值或日期和時間值。
 dateadd ( datepart , number ,date)
 datediff ( datepart ,date1,date2)
 datename ( datepart ,date)
 datepart ( datepart ,date)
 day (date)
 getdate ()
 month (date)
 year (date)
例1：從getdate函數返回的日期中提取月份數
 select   datepart ( month , getdate ())  as   ' month number '
注:datepart為系統函數
例2：從03 / 12 / 1998中返回月份、天數和年份數
 select   month ( ' 03/12/1998 ' ), day ( ' 03/12/1998 ' ), year ( ' 03/12/1998 ' )

 3 .、字串函數
可對位元據、字串和運算式執行不同的運算，大多數字串函數只能用於char和varchar資料類型以及明確轉換成char和varchar的資料類型，少數幾個字串函數也可以用binary和varbinary資料類型。此外，果些字串還可以處理text, ntext ,image資料類型的資料。
 1 .字串函數的分類：
( 1 ).基底字元串函數： upper , lower , space , replicate , stuff , reverse , ltrim , rtrim .
( 2 ).字串尋找函數： charindex , patindex .
( 3 ).長度和分析函數： datalength , substring , right .
( 4 ).轉換函式：asch, char , str , soundex , difference
 
 4 .數學函數
用於對數字運算式進行數學運算並返回運算結果。數學函數可以對SQL Server提供的數字資料( decimal , integer , float , real , money ,samllmoney,smallint和tinyint)進行處理。
例：在一運算式中使用ceiling(向上取整), floor (向下到整), round (四捨五入，N位小數)函數。
 select   ceiling ( 13.4 ), floor ( 13.4 ), round ( 13.4567 , 3 )
結果為14， 13 ， 13.4570 。

 5 .轉換函式
一般情況下，SQL Server會自動處理某些資料類型的轉換。例如，如果比較char和datetime運算式、smallint和int運算式、或不同長度的char運算式，SQL Serve可以將它們自動轉換，這種自動轉換稱為隱性轉換。但是無法由SQL Serve自動轉換的或者是SQL Serve自動轉換的結果不符合預期結果的，就需要使用轉換函式做顯示轉換。轉換函式有兩個：convert和cast.
( 1 ). cast (expression  as  data_type)
( 2 ).couvert函數允許使用者反運算式從一鐘資料類型轉換成另一鐘資料類型，還允許反日期轉換成不同的樣式
 convert  (data_type [ length ] ,expression [ ,style ] )
例：
 use  bubs
 select  title,ytd_sales  from  titles
 where   cast (ytd_sales  as   char ( 20 ))  like ' 15% '
      and  type = ' trad_cook '