Kullanıcı tanımlı işlevler (veritabanı altyapısı) oluşturma

Bu konuda nasıl oluşturulacağı açıklanır bir Transact-SQLkullanıcı tanımlı bir işlev SQL Server 2012kullanarak Transact-SQL.

Bu Konuda

• Başlamadan Önce

  Sınırlamalar ve Kısıtlamalar

  Güvenlik

• Kullanıcı tanımlı bir işlev oluşturmak için:

  Skalar fonksiyon oluşturma

  Tablo değerli fonksiyon oluştur

Başlamadan Önce

Sınırlamalar ve Kısıtlamalar

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

• Kullanıcı tanımlı işlevler, hedefi bir tablo olan bir OUTPUT INTO yan tümcesi içeremez.

• Aşağıdaki Service Broker deyimleri Transact-sql kullanıcı tanımlı bir işlev tanımında yer alamaz:

  • BEGIN DIALOG CONVERSATION

  • SON KONUŞMA

  • GET CONVERSATION GROUP

  • MOVE CONVERSATION

  • RECEIVE

  • SEND

• Kullanıcı tanımlı işlevler iç içe geçebilir; yani kullanıcı tanımlı bir işlev bir başkasını çağırabilir. İç içe geçme düzeyi, çağrılan işlev yürütülmeye başladığında artırılır, işlev yürütülmeyi bitirdiğinde azaltılır. Kullanıcı tanımlı işlevler 32 düzeye kadar iç içe geçebilir. İç içe geçirme düzeyinin üst sınırının aşılması, tüm çağıran işlev zincirinin hata vermesine neden olur. Bir düzey karşı 32 düzeyinde iç içelik sınırı olarak yönetilen kod referans gelen bir Transact-sql kullanıcı tanımlı işlev sayar. Yönetilen kodun içinden çağrılan yöntemler, bu sınır sayıdan düşülmez.

Güvenlik

İzinler

Veritabanında CREATE FUNCTION işlevi ve işlevin oluşturulmakta olduğu şemada ALTER izni gerektirir. İşlev kullanıcı tanımlı bir tür belirtirse, tür üzerinde EXECUTE izni gerektirir.

[Top]

Skaler İşlevler

Aşağıdaki örnek, çoklu deyimli skalar fonksiyon oluşturur. İşlev bir girdi değeri alır bir ProductIDve bir tek veri değeri, belirtilen ürün stokta toplanan miktarı verir.

USE AdventureWorks2012;
GO
IF OBJECT_ID (N'dbo.ufnGetInventoryStock', N'FN') IS NOT NULL
    DROP FUNCTION ufnGetInventoryStock;
GO
CREATE FUNCTION dbo.ufnGetInventoryStock(@ProductID int)
RETURNS int 
AS 
-- Returns the stock level for the product.
BEGIN
    DECLARE @ret int;
    SELECT @ret = SUM(p.Quantity) 
    FROM Production.ProductInventory p 
    WHERE p.ProductID = @ProductID 
        AND p.LocationID = '6';
     IF (@ret IS NULL) 
        SET @ret = 0;
    RETURN @ret;
END;
GO

Aşağıdaki örnek ufnGetInventoryStockolan ürünler için cari stok miktarı işlevinin bir ProductModelID75 ve 80 arasında.

USE AdventureWorks2012;
GO
SELECT ProductModelID, Name, dbo.ufnGetInventoryStock(ProductID)AS CurrentSupply
FROM Production.Product
WHERE ProductModelID BETWEEN 75 and 80;
GO

[Top]

Tablo Valued işlevler

Aşağıdaki örnek, tablo değerli bir satır içi işlevi oluşturur. İşlevi bir giriş parametresi, bir müşteri (depo) kimliği alır ve sütunlar döndürür ProductID, Nameve yıl tarihi satış olarak toplam YTD Totalsaklamak için satılan her ürün için.

USE AdventureWorks2012;
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

Aşağıdaki örnek, işlevini çağırır ve müşteri Kımlığı 602 belirtir.

SELECT * FROM Sales.ufn_SalesByStore (602);

Aşağıdaki örnek, bir tablo değerli işlev oluşturur. İşlev tek bir girdi parametresini alır bir EmployeeIDve doğrudan veya dolaylı olarak belirtilen çalışana rapor tüm çalışanların bir listesini verir. İşlev, çalışan kimliği 109 belirterek sonra çağrılır.

USE AdventureWorks2012;
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

[Top]

Ayrıca bkz.

Başvuru

CREATE FUNCTION (Transact-SQL)

Kavramlar

Kullanıcı tanımlı işlevler