CREATE FUNCTION (Transact-SQL)

Sözdizimi

--Transact-SQL Scalar Function Syntax
CREATE FUNCTION [ schema_name. ] function_name 
( [ { @parameter_name [ AS ][ type_schema_name. ] parameter_data_type 
    [ = default ] [ READONLY ] } 
    [ ,...n ]
  ]
)
RETURNS return_data_type
    [ WITH <function_option> [ ,...n ] ]
    [ AS ]
    BEGIN 
        function_body 
        RETURN scalar_expression
    END
[ ; ]

--Transact-SQL Inline Table-Valued Function Syntax 
CREATE FUNCTION [ schema_name. ] function_name 
( [ { @parameter_name [ AS ] [ type_schema_name. ] parameter_data_type 
    [ = default ] [ READONLY ] } 
    [ ,...n ]
  ]
)
RETURNS TABLE
    [ WITH <function_option> [ ,...n ] ]
    [ AS ]
    RETURN [ ( ] select_stmt [ ) ]
[ ; ]

--Transact-SQL Multistatement Table-valued Function Syntax
CREATE FUNCTION [ schema_name. ] function_name 
( [ { @parameter_name [ AS ] [ type_schema_name. ] parameter_data_type 
    [ = default ] [READONLY] } 
    [ ,...n ]
  ]
)
RETURNS @return_variable TABLE <table_type_definition>
    [ WITH <function_option> [ ,...n ] ]
    [ AS ]
    BEGIN 
        function_body 
        RETURN
    END
[ ; ]

--Transact-SQL Function Clauses 
<function_option>::= 
{
    [ ENCRYPTION ]
  | [ SCHEMABINDING ]
  | [ RETURNS NULL ON NULL INPUT | CALLED ON NULL INPUT ]
  | [ EXECUTE_AS_Clause ]
}

<table_type_definition>:: = 
( { <column_definition> <column_constraint> 
  | <computed_column_definition> } 
    [ <table_constraint> ] [ ,...n ]
) 
<column_definition>::=
{
    { column_name data_type }
    [ [ DEFAULT constant_expression ] 
      [ COLLATE collation_name ] | [ ROWGUIDCOL ]
    ]
    | [ IDENTITY [ (seed , increment ) ] ]
    [ <column_constraint> [ ...n ] ] 
}

<column_constraint>::= 
{
    [ NULL | NOT NULL ] 
    { PRIMARY KEY | UNIQUE }
      [ CLUSTERED | NONCLUSTERED ] 
      [ WITH FILLFACTOR = fillfactor 
        | WITH ( < index_option > [ , ...n ] )
      [ ON { filegroup | "default" } ]
  | [ CHECK ( logical_expression ) ] [ ,...n ]
}

<computed_column_definition>::=
column_name AS computed_column_expression 

<table_constraint>::=
{ 
    { PRIMARY KEY | UNIQUE }
      [ CLUSTERED | NONCLUSTERED ] 
      ( column_name [ ASC | DESC ] [ ,...n ] )
        [ WITH FILLFACTOR = fillfactor 
        | WITH ( <index_option> [ , ...n ] )
  | [ CHECK ( logical_expression ) ] [ ,...n ]
}

<index_option>::=
{ 
    PAD_INDEX = { ON | OFF } 
  | FILLFACTOR = fillfactor 
  | IGNORE_DUP_KEY = { ON | OFF }
  | STATISTICS_NORECOMPUTE = { ON | OFF } 
  | ALLOW_ROW_LOCKS = { ON | OFF }
  | ALLOW_PAGE_LOCKS ={ ON | OFF } 
}

--CLR Scalar Function Syntax
CREATE FUNCTION [ schema_name. ] function_name 
( { @parameter_name [AS] [ type_schema_name. ] parameter_data_type 
    [ = default ] } 
    [ ,...n ]
)
RETURNS { return_data_type }
    [ WITH <clr_function_option> [ ,...n ] ]
    [ AS ] EXTERNAL NAME <method_specifier>
[ ; ]

--CLR Table-Valued Function Syntax
CREATE FUNCTION [ schema_name. ] function_name 
( { @parameter_name [AS] [ type_schema_name. ] parameter_data_type 
    [ = default ] } 
    [ ,...n ]
)
RETURNS TABLE <clr_table_type_definition> 
    [ WITH <clr_function_option> [ ,...n ] ]
    [ ORDER ( <order_clause> ) ]
    [ AS ] EXTERNAL NAME <method_specifier>
[ ; ]

--CLR Function Clauses
<order_clause> ::= 
{
   <column_name_in_clr_table_type_definition>
   [ ASC | DESC ] 
} [ ,...n] 

<method_specifier>::=
    assembly_name.class_name.method_name

<clr_function_option>::=
}
    [ RETURNS NULL ON NULL INPUT | CALLED ON NULL INPUT ]
  | [ EXECUTE_AS_Clause ]
}

<clr_table_type_definition>::= 
( { column_name data_type } [ ,...n ] )

Bağımsız değişkenler

• schema_name
  Kullanıcı tanımlı işlev ait olduğu şema addır.

• function_name
  Kullanıcı tanımlı işlevadıdır.İşlev adları için kuralları ile uymak gerekir tanımlayıcıları ve şeması için ve veritabanı içinde benzersiz olmalıdır.

  Not

  işlev adı sonra bir parametresi belirtilmemiş olsa bile, ayraçlar gereklidir.

• @parameter_name
  Kullanıcı tanımlı işlevbir parametredir.Bir veya daha fazla parametre bildirilebilir.

  Bir işlev , maksimum 2100 parametreleri olabilir.işlev çalıştırıldığında, bir varsayılan parametre için tanımlanan sürece kullanıcı tarafından bildirilen her parametre değeri sağlanmalıdır.

  Parametre adı kullanarak belirttiğiniz bir at işareti (@) ilk karakteri olarak.Parametre adı tanıtıcı kurallarına uymanız gerekir.işlevparametreleri özeldir; aynı parametre adları diğer işlevleri kullanılabilir.Parametre yerine yalnızca sabitler alabilir; Bunlar, tablo adları, sütun adları veya diğer veritabanı nesnelerinin adlarını yerine kullanılamaz.

  Not

  ANSI_WARNINGS değil ödenen bir saklı yordam, kullanıcı tanımlı işlevparametre iletmek veya size bildirmek ve toplu işdeyimiçinde değişkenleri küme . Örneğin, bir değişken olarak tanımlanır, char(3)ve sonra küme üç karakterden daha büyük bir değere, tanımlanan boyutu ve eklemek için veriler kesildi veya update deyim başarılı.

• [ type_schema_name. ] parameter_data_type
  Parametre veri türü ve isteğe bağlı olarak ait olduğu şema olur.İçin Transact-SQL İşlevler, kullanıcı tanımlı clr türleri ve kullanıcı tanımlı tablo türleri de dahil olmak üzere tüm veri türleri dışında izin timestamp veri türü.clr işlevleri için dışında clr kullanıcı tanımlı türler de dahil olmak üzere tüm veri türlerine izin verilir text, ntext, image, kullanıcı tanımlı tablo türleri ve timestamp veri türü.Nonscalar türleri, cursor ve table, de parametre veri türü olarak belirtilemez Transact-SQL veya clr işlevleri.

  type_schema_name Belirtilmezse, Veritabanı Altyapısı arar scalar_parameter_data_type aşağıdaki sırayla:

  • Adlarını içeren şema SQL Server Sistem veri türleri.

  • Geçerli veritabanında geçerli kullanıcının varsayılan şema.

  • dbo Şema geçerli veritabanı.

• [ =default ]
  Bir varsayılan parametre değeridir.Yoksa bir default değeri tanımlanır, için bir değer belirtmeden işlev çalıştırılabilirBu parametre.

  Not

  Varsayılan parametre değerleri için dışında clr işlevleri için belirtilebilir varchar(max) ve varbinary(max) veri türü.

  işlev parametre varsayılan değeri olduğunda varsayılan olmalıdır anahtar işlev varsayılan değerini almak için ne zaman denilen belirtti.Bu davranış, hangi atlama parametresi de varsayılan değer anlaşılacağı saklı yordamlar varsayılan değerleri ile parametrelerini kullanarak farklıdır.Bir Bu davranışın skaler işlev execute deyimkullanarak baþlatýrken istisnadır.execute kullanırken, varsayılan anahtar gerekli değildir.

• SALT OKUNUR
  Parametresi olamaz güncelleştirilmesi veya işlevtanımı değiştirilmiş olduğunu gösterir.Parametre türü kullanıcı tanımlı tablo türü ise, salt okunur belirtilmelidir.

• return_data_type
  skaler kullanıcı tanımlı bir işlevdönüş değeridir.İçin Transact-SQL İşlevler, kullanıcı tanımlı clr türleri de dahil olmak üzere tüm veri türleri dışında izin timestamp veri türü.clr işlevleri için dışında clr kullanıcı tanımlı türler de dahil olmak üzere tüm veri türlerine izin verilir text, ntext, image, ve timestamp veri türü.Nonscalar türleri, cursor ve table, ya da bir dönüş veri türü olarak belirtilemez Transact-SQL veya clr işlevleri.

• function_body
  Belirleyen bir dizi Transact-SQL birlikte bir tablodeğiştirme gibi bir yan etkisi üretmez, ifadeleri değeri işlevtanımlayın.function_bodyyalnızca skaler işlevleri ve çoklu deyimli tablokullanılır-değerli fonksiyonlar.

  skaler işlevleri de function_body bir dizi Transact-SQL ifadeleri, birlikte değerlendirmek için skaler bir değer.

  Çoklu deyimli tablo-değerli fonksiyonlar, function_body bir dizi Transact-SQL deyimlerini tablo doldurmak dönüş değişken.

• scalar_expression
  skaler değer döndüren skalerişlev belirtir.

• TABLO
  Belirleyen bir dönüş değeri tablo-değerli işlev bir tabloolduğundan.Yalnızca sabitler ve @local_variables tablogeçirilebilir-değerli fonksiyonlar.

  Satır içi tablo-değerli işlevler, tablo dönüş değeri, tek bir select deyimile tanımlanır.Satır içi işlevler dönüş ilişkili değişkenleri yoktur.

  Çoklu deyimli tablo-değerli fonksiyonlar, @return_variable bir tablo değişkeni değeri işlevdöndürülen satır biriken ve saklamak için kullanılan.@return_variable yalnızca belirtilen Transact-SQL işlevleri için clr işlevleri.

• select_stmt
  Satır içi tablodönüş değeri tanımlayan select deyim olan-değerli işlev.

• Sipariş (<order_clause>)
  Sırayı belirler hangi sonuçlar tablodöndürülür-değerli işlev.Bölüm "Kılavuzu üzerinde kullanarak sıralama düzeni," daha fazla bilgi için bkz: Bu konuda daha sonra.

• DIŞ ad <method_specifier> assembly_name.class_name.method_name
  ' % S'işlevini işlevkullanarak bağlamak için derleme yöntem belirtir.assembly_namebir varolan derleme eşleşmelidir SQL Server üzerinde görünürlük ile geçerli veritabanında.class_namebir geçerli olması SQL Server tanımlayıcısı ve derlemesınıf olarak bulunmalıdır.Sınıf bir dönem kullandığı bir ad alanıyla nitelenmiş ad olup olmadığını (.) ad alanı bölümlerini ayırmak için sınıf adı köşeli ayraçlar kullanılarak sınırlanması gerekir ( []) veya tırnak işaretleri (" ").  method_namebir geçerli olması SQL Server tanımlayıcısı ve belirtilen sınıfa, statik bir yöntem olarak varolması gerekir

  Not

  Varsayılan olarak, SQL Server olamaz yürütmek clr kodu.Oluşturmak, değiştirmek ve ortak dil Çalışma Zamanı Modülü başvuru veritabanı nesnelerini bırakın; Ancak, bunlar başvuran yürütmek olamaz SQL Server olanak kadar clr seçeneği etkin.Bu seçeneği etkinleştirmek için kullanın sp_configure.

• <table_type_definition> ( { <column_definition> <column_constraint> | <computed_column_definition>} <table_constraint>] ,...n ] )
  tablo veri türünü tanımlayan bir Transact-SQLişlev. bildirim tablo sütun tanımları ve sütun veya tablo kısıtlamaları içerir. tablo içinde birincil dosya grubuher zaman geçer.

• <clr_table_type_definition > ( { column_namedata_type } [ ,...n ] )
  tablo veri türleri için CLR işlevitanımlar.tablobildirim , yalnızca sütun adları ve veri türleri içerir. tablo içinde birincil dosya grubuher zaman geçer.

En iyi yöntemler

Kullanıcı tanımlı bir işlev ile yan tümceema BALAMA oluşturulmaz, alttaki nesnelere yapılan değişiklikler tanımı işlev etkiler ve çağrıldığında beklenmeyen sonuçlar ortaya.işlev temel nesneleri değişiklikler nedeniyle güncelliğini yitirmiş değil emin olmak için aşağıdaki yöntemlerden birini uygulamak öneririz:

• işlevoluştururken yan tümce ile ema BALAMA belirtin.Bu işlev tanımında başvurulan nesneler işlev de değiştirilir sürece değiştirilemez sağlar.

• Yürütme sp_refreshsqlmodulesonra işlevtanımında belirtilen herhangi bir nesneyi değiştirmeksaklı yordam .

Veri Türleri

CLR işleviparametreleri belirtilirse, olması gereken SQL Server türleri için önceden tanımlanmış gibi scalar_parameter_data_type.Karşılaştırma hakkında bilgi SQL Server clr tümleştirme veri türleri için sistem veri türleri veya .NET Framework ortak dil çalışma zamanı veri türlerini görmek clr parametre verilerini eşleme.

İçin SQL Server için doğru yöntem olduğunda aşırı bir sınıf içinde başvuru yöntem belirtilen içinde <method_specifier> aşağıdaki özelliklere sahip olmalıdır:

• Aynı sayıda parametre belirtildiği gibi alırsınız [ ,...n ].

• Değer, başvuru tarafından tüm parametreleri alırsınız.

• Belirtilen ile uyumlu olan parametre türleri kullanmak SQL Server işlev.

CLR işlevi dönüş veri türü (tablo DÖNDÜRÜR), yöntem dönüş veri türü tablo türünü belirtir, <method_specifier> türünde olmalıdır IEnumerator veya IEnumerable, ve arabirim işlevoluşturan kişi tarafından gerçekleştirilir varsayılır.Aksine Transact-SQL İşlevler, clr işlevleri içeren birincil anahtar, benzersiz, veya kontrol kısıtlamalarını <table_type_definition>.Belirtilen sütun veri türleri <table_type_definition> yöntem tarafından döndürülen sonuç kümesi karşılık gelen sütun türleri eşleşmelidir <method_specifier> yürütme saat.Bu tür denetleme işlev oluşturulan saat gerçekleştirilmez.

Program clr işlevleri hakkında daha fazla bilgi için bkz: clr kullanıcı tanımlı işlevler.

Genel açıklamalar

skaler ifadeler kullanıldığı skalar değerli işlevler çağrılabilir.Bu hesaplanan sütunlar ve onay kısıtlaması tanımlarını içerir.Skalar değerli işlevler de çalıştırılabilir kullanarak execute deyim.Skalar değerli işlevler en az işleviki bölümü adı kullanarak çağrılması gerekir.Çok parçalı adları hakkında daha fazla bilgi için bkz: Transact-SQL Sözdizimi Kuralları (Transact-SQL).from yan tümce select, INSERT, update veya delete deyimleri içinde tablo ifadelere izin burada tablo değerli işlevler çağrılabilir.Daha fazla bilgi için, bkz. Kullanıcı tanımlı işlevler (veritabanı altyapısı) yürütme.

Birlikte çalışabilirlik

Bir işlevaşağıdaki ifadeler geçerlidir:

• Atama deyimleri.

• Akış denetimi deyimleri deneyin dışında...catch deyimleri.

• Yerel veri değişkenleri ve yerel imleçler tanımlayan ifadelerden BİLDİRİN.

• Yerel değişkenler için değerler atayın ifadeleri ile seçim listelerini içeren deyimleri seçin.

• İmleç işlemlerini, açılmış, bildirilen yerel imleçler başvuran kapalı ve işlevkaldırıldı.INTO yan tümce kullanarak yerel değişkenler için değerler atayın getirme deyimleri izin verilir; istemci veri verilmeyen dönmek deyimleri GETİR.

• Ekle, update ve delete deyimlerini yerel tablo değişkenlerini değiştirme.

• execute deyimlerinin çağırma genişletilmiş saklı yordamları.

• Daha fazla bilgi için, bkz. Kullanıcı tanımlı işlevler (veritabanı altyapısı) oluşturma.

Hesaplanan sütun birlikte çalışabilirlik

Çağırma genişletilmiş saklı yordamları işlevler

Sınırlamalar ve kısıtlamalar

Kullanıcı tanımlı işlevler, veritabanı durumunu değiştir eylemleri gerçekleştirmek için kullanılamaz.

Kullanıcı tanımlı işlevler, hedefolarak bir tablo içeren bir çıkış INTO yan tümce içeremez.

Aşağıdaki Service Broker ifadeleri tanımında eklenebilir edilemez bir Transact-SQL kullanıcı tanımlı işlev:

• BAŞLANGIÇ İLETİŞİM KONUŞMA

• SON KONUŞMA

• GET KONUŞMA GRUBU

• MOVE KONUŞMA

• ALMA

• GÖNDER

Kullanıcı tanımlı işlevler iç içe olabilir; yani, bir kullanıcı tanımlı işlev başka çağırabilirsiniz.Çağrılan işlev yürütme başladığında, iç içe geçmiş düzey artırılır ve çağrılan işlev yürütme tamamlandığında indirildiği düğümlerde.Kullanıcı tanımlı işlevler 32 düzeye kadar iç içe olabilir.İç içe geçmiş nedenleri maksimum düzeyde vermesine işlev zinciri çağırma bütün aşan.yönetilen kod referans bir Transact-SQL kullanıcı tanımlı işlev sayar 32 - karşı bir düzey sınırı. iç içe geçmişdüzeyyönetilen kod içinde açılmak istenen yöntemleri bu sınırınızı sayılmaz.

clr tablo değerli işlevler sıralama düzeni kullanma

Meta veri

Aşağıdaki tablo , kullanıcı tanımlı işlevler hakkında meta veriler dönmek için kullanabileceğiniz Sistem katalog görünümleri listeler.

USE AdventureWorks2008R2;
GO
SELECT definition, type 
FROM sys.sql_modules AS m
JOIN sys.objects AS o ON m.object_id = o.object_id 
    AND type IN ('FN', 'IF', 'TF');
GO

İzinler

create FUNCTION izni veritabanında ve işlev oluşturulmaktadır şema üzerinde alter izni gerektirir.işlev bir kullanıcı tanımlı türbelirtiyorsa, türü üzerinde execute izni gerektirir.

Örnekler

A.Bir skalerkullanarak-değerli ISO hafta hesaplayan kullanıcı tanımlı işlev

USE AdventureWorks2008R2;
GO
IF OBJECT_ID (N'dbo.ISOweek', N'FN') IS NOT NULL
    DROP FUNCTION dbo.ISOweek;
GO
CREATE FUNCTION dbo.ISOweek (@DATE datetime)
RETURNS int
WITH EXECUTE AS CALLER
AS
BEGIN
     DECLARE @ISOweek int;
     SET @ISOweek= DATEPART(wk,@DATE)+1
          -DATEPART(wk,CAST(DATEPART(yy,@DATE) as CHAR(4))+'0104');
--Special cases: Jan 1-3 may belong to the previous year
     IF (@ISOweek=0) 
          SET @ISOweek=dbo.ISOweek(CAST(DATEPART(yy,@DATE)-1 
               AS CHAR(4))+'12'+ CAST(24+DATEPART(DAY,@DATE) AS CHAR(2)))+1;
--Special case: Dec 29-31 may belong to the next year
     IF ((DATEPART(mm,@DATE)=12) AND 
          ((DATEPART(dd,@DATE)-DATEPART(dw,@DATE))>= 28))
          SET @ISOweek=1;
     RETURN(@ISOweek);
END;
GO
SET DATEFIRST 1;
SELECT dbo.ISOweek(CONVERT(DATETIME,'12/26/2004',101)) AS 'ISO Week';

B.Satır içi tablooluşturma-değerli işlev

USE AdventureWorks2008R2;
GO
IF OBJECT_ID (N'Sales.ufn_SalesByStore', N'IF') IS NOT NULL
    DROP FUNCTION Sales.ufn_SalesByStore;
GO
CREATE FUNCTION Sales.ufn_SalesByStore (@storeid int)
RETURNS TABLE
AS
RETURN 
(
    SELECT P.ProductID, P.Name, SUM(SD.LineTotal) AS 'Total'
    FROM Production.Product AS P 
    JOIN Sales.SalesOrderDetail AS SD ON SD.ProductID = P.ProductID
    JOIN Sales.SalesOrderHeader AS SH ON SH.SalesOrderID = SD.SalesOrderID
    JOIN Sales.Customer AS C ON SH.CustomerID = C.CustomerID
    WHERE C.StoreID = @storeid
    GROUP BY P.ProductID, P.Name
);
GO

SELECT * FROM Sales.ufn_SalesByStore (602);

C.Multi -deyim tablooluşturma-değerli işlev

USE AdventureWorks2008R2;
GO
IF OBJECT_ID (N'dbo.ufn_FindReports', N'TF') IS NOT NULL
    DROP FUNCTION dbo.ufn_FindReports;
GO
CREATE FUNCTION dbo.ufn_FindReports (@InEmpID INTEGER)
RETURNS @retFindReports TABLE 
(
    EmployeeID int primary key NOT NULL,
    FirstName nvarchar(255) NOT NULL,
    LastName nvarchar(255) NOT NULL,
    JobTitle nvarchar(50) NOT NULL,
    RecursionLevel int NOT NULL
)
--Returns a result set that lists all the employees who report to the 
--specific employee directly or indirectly.*/
AS
BEGIN
WITH EMP_cte(EmployeeID, OrganizationNode, FirstName, LastName, JobTitle, RecursionLevel) -- CTE name and columns
    AS (
        SELECT e.BusinessEntityID, e.OrganizationNode, p.FirstName, p.LastName, e.JobTitle, 0 -- Get the initial list of Employees for Manager n
        FROM HumanResources.Employee e 
            INNER JOIN Person.Person p 
            ON p.BusinessEntityID = e.BusinessEntityID
        WHERE e.BusinessEntityID = @InEmpID
        UNION ALL
        SELECT e.BusinessEntityID, e.OrganizationNode, p.FirstName, p.LastName, e.JobTitle, RecursionLevel + 1 -- Join recursive member to anchor
        FROM HumanResources.Employee e 
            INNER JOIN EMP_cte
            ON e.OrganizationNode.GetAncestor(1) = EMP_cte.OrganizationNode
            INNER JOIN Person.Person p 
            ON p.BusinessEntityID = e.BusinessEntityID
        )
-- copy the required columns to the result of the function 
   INSERT @retFindReports
   SELECT EmployeeID, FirstName, LastName, JobTitle, RecursionLevel
   FROM EMP_cte 
   RETURN
END;
GO
-- Example invocation
SELECT EmployeeID, FirstName, LastName, JobTitle, RecursionLevel
FROM dbo.ufn_FindReports(1); 

GO

D.CLR işlevioluşturma

DECLARE @SamplesPath nvarchar(1024);
-- You may have to modify the value of this variable if you have
-- installed the sample in a location other than the default location.
SELECT @SamplesPath = REPLACE(physical_name, 'Microsoft SQL Server\MSSQL10_5.MSSQLSERVER\MSSQL\DATA\master.mdf', 'Microsoft SQL Server\90\Samples\Engine\Programmability\CLR\') 
    FROM master.sys.database_files 
    WHERE name = 'master';

CREATE ASSEMBLY [SurrogateStringFunction]
FROM @SamplesPath + 'StringManipulate\CS\StringManipulate\bin\debug\SurrogateStringFunction.dll'
WITH PERMISSION_SET = EXTERNAL_ACCESS;
GO

CREATE FUNCTION [dbo].[len_s] (@str nvarchar(4000))
RETURNS bigint
AS EXTERNAL NAME [SurrogateStringFunction].[Microsoft.Samples.SqlServer.SurrogateStringFunction].[LenS];
GO

Ayrıca bkz.

Başvuru

Diğer Kaynaklar

Değişiklik Geçmişi