SqlServer 使用者自訂函數

標籤：style   color   使用   io   strong   ar   資料   cti   代碼   

      在SQL Server中，使用者不僅可以使用標準的內建函數，也可以使用自己定義的函數來實現一些特殊的功能。可以使用CREATE  FUNCTION 語句建立。在建立時需要注意：函數名在資料庫中必須唯一，其可以有參數，也可以沒有參數，其參數只能是輸入參數，最多可以有1024參數。

      使用者自訂函數不能用於執行一系列改變資料庫狀態的操作，但它可以像系統函數一樣在查詢或預存程序等的程式段中使用，也可以像預存程序一樣通過EXECUTE 命令來執行。

　  自訂函數分為  純量涵式、 資料表值函式、 多語句資料表值函式 三種。

　　（1）純量涵式：對單一值操作，返回單一值。只要在能夠使用運算式的地方，就可以使用純量涵式。

      （2）資料表值函式：傳回值是一個記錄集合——表。在此函數中，return語句包含一條單獨的select語句。      

　　（3）多語句資料表值函式：傳回值是由選擇的結果構成的記錄集。

 

一：純量涵式

　　格式如下：

      CREATE FUNCTION [ owner_name.] function_name

          ( [ { @parameter_name [AS] scalar_parameter_data_type [ = default ] } [ ,...n ] ] )

            RETURNS scalar_return_data_type

            [ AS ]

                  BEGIN

                       function_body

                       RETURN scalar_expression

                  END

 

　　解釋如下：

  （1）function_name：指使用者自訂函數的名稱。其名稱必須符合標識符的命名規則，並且對其所有者來說，該名稱在資料庫中必須唯一。

  （2）@parameter_name：使用者自訂函數的參數。參數只能代替常量；而不能用於代替表名、列名或其它資料庫物件的名稱。函數執行時每個已聲明參數的值必須由使用者指定，除非該參數的預設值已經定義。如果函數的參數有預設值，在調用該函數時必須指定"default"關鍵字才能獲得預設值。

  （3）scalar_parameter_data_type：參數的資料類型。可以是 SQL Server 支援的任何純量資料型別（text、ntext、image 和 timestamp 除外）。

  （4）scalar_return_data_type：是使用者定義函數的傳回值。資料類型與（3）輸入參數的要求相同。不能有text、ntext、image 和 timestamp類型的參數。

  （5）function_body：位於begin和end之間的一系列 Transact-SQL 陳述式，其只用於純量涵式和多語句資料表值函式。

  （6）scalar_expression：使用者自訂函數中傳回值的運算式。

 

　　如果你覺得複雜，可以簡化為

      CREATE FUNCTION  function_name（函數名）

      (  @參數1名  參數1資料類型[ = default ],  @參數2名  參數2資料類型[ = default ],......)

        RETURNS  返回資料類型

        [ AS ]

        BEGIN

                 sql語句

                 RETURN scalar_expression（傳回值）

        END

 例：在STUDENT庫中建立一個使用者自訂函數XUEFEN，該函數通過輸入成績來判斷是否取得學分，當成績大於等於50時，返回取得學分，否則，返回未取得學分。其代碼如下：

   CREATE FUNCTION xuefen(@inputxf int)   --(1)  函數名為 xuefen，輸入參數為@inputxf ， 參數資料類型為int

　　RETURNS  nvarchar(10)  　　　　--(2) 函數的傳回值類型為nvarchar(10)

    BEGIN　　--(3) 將sql語句放在  BEGIN  和  END 之間，使其作為一個整體。

        declare @retrunstr nvarchar(10)　　--(4) 聲明函數傳回值的變數為@retrunstr，資料類型為nvarchar(10)；該類型要與 (2)中聲明的保持一致 

        If @inputxf >=50 

           set  @retrunstr=‘取得學分‘

        else

           set  @retrunstr=‘未取得學分‘

           return @retrunstr

   END

 

使用函數的代碼如下：

        SELECT  學號,成績,dbo.xuefen(成績)  --要在使用的時候指明函數的所有者，在本例中，必須加上dbo.

　　　　AS 學分情況  FROM 課程註冊  WHERE 課程號=‘0003‘

        GO

二：資料表值函式：

 

資料表值函式遵循的原則：

   1、RETURNS子句僅包含關鍵字table。不必定義返回變數的格式，因為它由RETURN 子句中的 SELECT 語句的結果集的格式設定。

   2、function_body 不由BEGIN和END分隔。

   3、RETURN子句在括弧中包含單個SELECT語句。SELECT語句的結果集構成函數所返回的表。內嵌資料表值函式中使用的SELECT語句受到與視圖中使用的SELECT語句相同的限制。

例：在STUDENT庫中建立一個內嵌資料表值函式XUESHENG，該函數可以根據輸入的系部代碼返回該系學生的基本資料。其代碼如下：

CREATE   FUNCTION XUESHENG(@inputxbdm nvarchar(4)) RETURNS  table  

 AS

    RETURN

   ( SELECT 學號, 姓名, 入學時間   FROM  學生 WHERE 系部代碼[email protected])

GO

建立好該內嵌資料表值函式後，就可以象使用表或視圖一樣來使用它：

SELECT  * FROM  DBO.XUESHENG(‘01‘)

GO

三、多語句資料表值函式

 

例：在STUDENT庫中建立一個多語句資料表值函式CHENGJI，該函數可以根據輸入的課程名稱返回選修該課程的學生姓名和成績。其代碼如下：

   CREATE  FUNCTION CHENGJI( @inputkc as char(20) )

    RETURNS @chji TABLE    [email protected]為表變數

      (       課程名  char(20),     姓名    char(8),      成績   tinyint    )

   AS

     BEGIN

       INSERT @chji

          SELECT c.課程名,s.姓名 ,k.成績  FROM 學生 as s INNER JOIN 課程註冊 as k  ON s.學號 =k.學號 inner join 課程 as c  on c.課程號=k.課程號  WHERE c.課程名[email protected]

      RETURN　　--直接單獨用 RETURN 即可，將表變數的資料返回

    END

 GO

 

在查詢分析器中輸入以下查詢命令：

 SELECT  * FROM  DBO.CHENGJIi(‘大學語文‘)

總結：

 

多語句函數的主體中允許使用以下語句。

  1、指派陳述式。

  2、控制流程語句。

  3、DECLARE 語句，該語句定義函數局部的資料變數和遊標。

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

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

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

  7、EXECUTE語句調用擴充預存程序。

 

SqlServer 使用者自訂函數