CREATE FUNCTION

Dotyczy:Punkt końcowy analizy SQL w usłudze Microsoft Fabric i magazynie w usłudze Microsoft Fabric

CREATE FUNCTION tworzy funkcje tabelowe w linii oraz funkcje skalarne.

Funkcja zdefiniowana przez użytkownika to Transact-SQL procedura, która akceptuje parametry, wykonuje akcję, taką jak obliczenia zespolone, i zwraca wynik tej akcji jako wartość. Funkcje skalarne zwracają wartość skalarną, taką jak liczba lub ciąg. Funkcje tabeli zdefiniowane przez użytkownika (TVFs) zwracają tabelę.

Użyj CREATE FUNCTION do stworzenia wielokrotnego użytku rutyny T-SQL, którą możesz wykorzystać w następujący sposób:

• W Transact-SQL zdaniach takich jak SELECT.
• W Transact-SQL instrukcjach manipulacji danymi (DML), takich jak UPDATE, INSERT, oraz DELETE.
• W aplikacjach wywołując funkcję.
• W definicji innej funkcji zdefiniowanej przez użytkownika.
• Aby zastąpić procedurę przechowywaną.

Określ CREATE OR ALTER FUNCTION utworzenie nowej funkcji, jeśli nie istnieje pod tą nazwą, lub zmodyfikuj istniejącą funkcję w jednym zaleceniu.

Transact-SQL konwencje składni

Składnia

Składnia funkcji skalarnych

CREATE FUNCTION [ schema_name. ] function_name   
( [ { @parameter_name [ AS ] parameter_data_type   
    [ = default ] }   
    [ ,...n ]  
  ]  
)  
RETURNS return_data_type  
    [ WITH <function_option> [ ,...n ] ]  
    [ AS ]  
    BEGIN   
        function_body   
        RETURN scalar_expression  
    END  
[ ; ]  

<function_option>::=   
{  
    [ INLINE = AUTO ]
  | [ SCHEMABINDING ]  
  | [ RETURNS NULL ON NULL INPUT | CALLED ON NULL INPUT ]  
}  

Składnia funkcji wbudowanych wartości tabeli

CREATE FUNCTION [ schema_name. ] function_name
( [ { @parameter_name [ AS ] parameter_data_type
    [ = default ] }
    [ ,...n ]
  ]
)
RETURNS TABLE
    [ WITH SCHEMABINDING ]
    [ AS ]
    RETURN [ ( ] select_stmt [ ) ]
[ ; ]

Argumenty (w programowaniu)

schema_name

Nazwa schematu, do którego należy funkcja zdefiniowana przez użytkownika.

function_name

Nazwa funkcji zdefiniowanej przez użytkownika. Nazwy funkcji muszą spełniać reguły dotyczące identyfikatorów i być unikalne w bazie danych oraz jej schemacie.

Musisz dodać nawiasy po nazwie funkcji, nawet jeśli nie podasz parametru.

@ parameter_name

Parametr w funkcji zdefiniowanej przez użytkownika. Możesz zadeklarować jeden lub więcej parametrów.

Funkcja może mieć do 2 100 parametrów. Gdy użytkownik lub aplikacja wywołuje funkcję, wartość każdego zadeklarowanego parametru musi być podana, chyba że parametr domyślny zostanie zdefiniowany.

Określ nazwę parametru przy użyciu znaku (@) jako pierwszego znaku. Nazwa parametru musi odpowiadać regułom dla identyfikatorów. Parametry są lokalne dla funkcji; Możesz używać tych samych nazw parametrów w innych funkcjach. Parametry mogą zastąpić jedynie stałe; Nie można ich używać zamiast nazw tabel, kolumn ani nazw innych obiektów bazy danych.

ANSI_WARNINGS nie jest honorowany podczas przekazywania parametrów w procedurze składowanej, funkcji zdefiniowanej przez użytkownika lub podczas deklarowania i ustawiania zmiennych w instrukcji wsadowej. Na przykład, jeśli zdefiniujesz zmienną jako char(3), a następnie ustawisz ją na wartość większą niż trzy znaki, dane są obcięte do zdefiniowanego rozmiaru i polecenie SQL odnosi sukces.

parameter_data_type

Typ danych parametru. W przypadku funkcji Transact-SQL dozwolone są wszystkie obsługiwane typy danych skalarnych .

[ = domyślnie ]

Wartość domyślna parametru. Jeśli zdefiniujesz wartość domyślną , możesz wykonać funkcję bez określania wartości tego parametru.

Gdy parametr funkcji ma wartość domyślną, musisz podać słowo DEFAULT kluczowe podczas wywoływania funkcji, aby uzyskać wartość domyślną. To zachowanie różni się od używania parametrów z wartościami domyślnymi w procedurach składowanych, w których pominięcie parametru również implikuje wartość domyślną.

return_data_type

Wartość zwracana przez skalarną funkcję zdefiniowaną przez użytkownika.

W przypadku funkcji w magazynie danych sieci szkieletowej wszystkie typy danych są dozwolone z wyjątkiem sygnatury czasowej rowversion/. Typy nieskalarne, takie jak tabele , nie są dozwolone.

function_body

Seria instrukcji Transact-SQL.

W funkcjach skalarnych function_body to seria instrukcji Transact-SQL, które razem oceniają wartość skalarną, która może obejmować:

• Wyrażenie pojedynczej instrukcji
• Wyrażenia z wieloma instrukcjami (IF/THEN/ELSE i BEGIN/END bloki)
• Zmienne lokalne
• Wywołania wbudowanych funkcji SQL dostępnych
• Wywołania do innych funkcji zdefiniowanych przez użytkownika
• SELECT instrukcje i odwołania do tabel, widoków i wbudowanych funkcji wartości tabeli
• Instrukcje przepływu sterowania (WHILE pętle, RETURNS)

scalar_expression

Określa wartość skalarną zwracaną przez funkcję skalarną.

select_stmt

Pojedyncza SELECT instrukcja, która definiuje wartość zwracaną funkcji tabeli wbudowanej. Dla funkcji tabelowej w linii nie ma ciała funkcji; tabela to zbiór wyników pojedynczego SELECT zapowiedzenia.

TABLE

Określa, że wartością zwracaną przez funkcję zwracającą tabelę (TVF) jest tabela. Możesz przekazywać tylko stałe i @local_variables do TVF.

W inline TVF (podgląd) definiujesz wartość zwrotną TABLE za pomocą pojedynczego SELECT wypowiedzenia. Funkcje wbudowane nie mają skojarzonych zmiennych zwracanych.

<function_option>

W Fabric Data Warehouse słowa ENCRYPTION kluczowe nie EXECUTE AS są obsługiwane.

Obsługiwane opcje funkcji obejmują:

INLINE = AUTO

Określa, czy skalarna funkcja użytkownika może być tworzona lub zmieniana niezależnie od wymagań wejścia. Klauzula jest opcjonalna INLINE . W przypadku skalarnego UDF z linią inlineowalnym określenie INLINE = AUTO nie zmienia jego inlineability ani zachowania wykonawczego.

POWIĄZANIE SCHEMATU

Określa, że funkcja jest powiązana z obiektami bazy danych, do których się odwołuje. Gdy określasz SCHEMABINDING, nie możesz modyfikować obiektów bazowych (takich jak widok czy tabela, na przykład) w sposób, który wpływałby na definicję funkcji. Najpierw musisz zmodyfikować lub usunąć definicję funkcji, aby usunąć zależności od obiektu, który chcesz zmodyfikować.

Powiązanie funkcji z obiektami, do których się odwołuje, jest usuwane tylko wtedy, gdy występuje jedna z następujących akcji:

• Rezygnuj z tej funkcji.

• Używasz instrukcji funkcji i usuwasz ALTERSCHEMABINDING tę opcję.

Możesz związać funkcję schematem tylko wtedy, gdy spełnione są następujące warunki:

• Wszystkie funkcje zdefiniowane przez użytkownika, do których funkcja się odwołuje, są również ograniczone schematem.

• Funkcja odnosi się do obiektów za pomocą nazwy dwuczęściowej.

• W treści UDF możesz odwoływać się tylko do wbudowanych funkcji i innych UDF w tej samej bazie danych.

• Użytkownik, który wykonuje zdanie, CREATE FUNCTION ma uprawnienia REFERENCJI do obiektów bazy danych, do których funkcja się odwołuje.

Aby usunąć SCHEMATBINDING, użyj polecenia ALTER.

ZWRACA WARTOŚĆ NULL DLA DANYCH WEJŚCIOWYCH O WARTOŚCI NULL | WYWOŁYWANE PRZY DANYCH WEJŚCIOWYCH O WARTOŚCI NULL

Określa OnNULLCall atrybut funkcji wartości skalarnej. Jeśli nie określisz tego atrybutu, jest CALLED ON NULL INPUT domyślnie implikowany, a ciało funkcji wykonuje się nawet jeśli NULL zostanie przekazane jako argument.

Najlepsze rozwiązania

• Jeśli nie utworzysz funkcji zdefiniowanej przez użytkownika ze schemabindingiem, zmiany w obiektach bazowych mogą wpłynąć na definicję funkcji i powodować nieoczekiwane skutki podczas wywoływania funkcji. Gdy określasz WITH SCHEMABINDING moment tworzenia funkcji, zapewniasz, że późniejsze zmiany obiektów bazowych nie mogą zmienić ani przerwać zachowania funkcji.

• Napisz funkcje zdefiniowane przez użytkownika tak, aby były nieliniowe. Więcej informacji o koncepcji inlining można znaleźć w artykule Inlining of scalar UDF. Przykłady tego, jak uczynić skalarny UDF inlineowalnym, można znaleźć w artykule Create scalar UDF in Microsoft Fabric Data Warehouse.

Współdziałanie

Wbudowane funkcje zdefiniowane przez użytkownika w tabeli

Funkcja tabelowa w linii akceptuje tylko jedno SELECT polecenie.

Skalarne funkcje zdefiniowane przez użytkownika

• Funkcja nieinlineowalna nie może być użyta w zapytaniu SELECT ... FROM w tabeli użytkownika.

• Następujące instrukcje są prawidłowe w funkcji skalarnej wartości:

  • Instrukcje przypisania.
  • Instrukcje kontroli przepływu z wyjątkiem TRY...CATCH instrukcji i GOTO instrukcji.
  • DECLARE instrukcje definiujące lokalne zmienne danych.
  • Wywołania do wbudowanych funkcji.
  • Odniesienia do tabel/widoków/iTVF-ów/innych skalarnych UDF-ów.

• Instrukcje DML nie są dozwolone w skalarnych funkcjach zdefiniowanych przez użytkownika.

• Następujące wbudowane funkcje nie są obsługiwane w treści funkcji o wartości skalarnej:

  • NEWID()
  • RAND()
  • Funkcje konfiguracji
  • DATABASEPROPERTYEX
  • OBJECTPROPERTYEX
  • SERVERPROPERTY
  • NASTĘPNA WARTOŚĆ DLA
  • COLLATIONPROPERTY
  • HAS_PERMS_BY_NAME
  • HAS_DBACCESS

Metadane

W tej sekcji wymieniono widoki wykazu systemu, których można użyć do zwracania metadanych dotyczących funkcji zdefiniowanych przez użytkownika.

• sys.sql_modules: Wyświetla definicję Transact-SQL funkcji definiowanych przez użytkownika oraz informacje o możliwości włączenia linii. Przykład:

    SELECT 
        SCHEMA_NAME(o.schema_id) AS SchemaName,
        o.name AS FunctionName,
        m.definition AS FunctionDefinition,
        m.is_inlineable AS Inlineable,
        m.inline_eligibility_mask AS InlineEligibilityMask
    FROM sys.objects o
    JOIN sys.sql_modules m ON o.object_id = m.object_id
    WHERE o.type = 'FN';
  

• sys.parameters: wyświetla informacje o parametrach zdefiniowanych przez użytkownika.

• sys.sql_expression_dependencies: wyświetla obiekty bazowe, do których odwołuje się funkcja.

Uprawnienia

Członkowie ról Administrator obszaru roboczego sieci szkieletowej, Członek i Współautor mogą tworzyć funkcje.

Inlining skalarnego UDF

Microsoft Fabric Data Warehouse wykorzystuje różne techniki inlining do kompilacji i wykonywania kodu zdefiniowanego przez użytkownika w sposób rozproszony.

Domyślnie włącza się inlining skalarnego UDF.

Niektóre składnie języka T-SQL sprawiają, że skalarna funkcja UDF jest nieliniowalna. Na przykład funkcje zawierające kombinację pętli WHILE i odwołujące się do tabeli wewnątrz ciała UDF nie mogą być inlineowane.

Sprawdzanie, czy można utworzyć wlinę skalarną funkcji zdefiniowanej przez użytkownika

sys.sql_modules Widok wykazu zawiera kolumnę , która wskazuje, czy funkcja zdefiniowanej przez is_inlineableużytkownika jest wbudowana. Właściwość ta is_inlineable pochodzi ze sprawdzenia składni wewnątrz definicji UDF. Skalarny UDF jest inlinerowany tylko podczas kompilacji.

Własność wyjaśnia, inline_eligibility_mask jaki typ inlining jest stosowany w UDF.

• Wartość oznacza 0 , że UDF nie jest inlineowalny.
• Wartość wskazuje 1 , że UDF kwalifikuje się do inliningu skalarnego UDF.
• Wartość oznacza 2 , że UDF kwalifikuje się do inlining za pomocą bloku ekspresji.
• Wartość oznacza 3 , że UDF kwalifikuje się do obu technik inline.

Użyj następującego przykładowego zapytania, aby sprawdzić, czy funkcja UDF skalarna jest wbudowana:

SELECT 
SCHEMA_NAME(b.schema_id) as function_schema_name,
    b.name as function_name,
       b.type_desc as function_type,
       a.is_inlineable
FROM sys.sql_modules AS a
     INNER JOIN sys.objects AS b
         ON a.object_id = b.object_id
WHERE b.type IN ('FN');

Jeśli funkcja skalarna nie jest inlineowalna w sys.sql_modules.is_inlineable, nadal możesz wykonać zapytanie jako osobne wywołanie, na przykład w celu ustawienia zmiennej. Funkcja skalarna nie może być częścią SELECT ... FROM zapytania w tabeli użytkownika. Przykład:

CREATE FUNCTION [dbo].[custom_SYSUTCDATETIME]()
  RETURNS datetime2(6)
  AS
  BEGIN
   RETURN SYSUTCDATETIME();
  END

Przykładowa dbo.custom_SYSUTCDATETIME skalarna funkcja zdefiniowana przez użytkownika nie jest inlineowalna, ponieważ używa niedeterministycznej funkcji systemowej, SYSUTCDATETIME(). Nie udaje się użyć w zapytaniu SELECT ... FROM na tabeli użytkownika, ale jako samodzielne wywołanie się udaje. Przykład:

DECLARE @utcdate datetime2(7);
SET @utcdate = dbo.custom_SYSUTCDATETIME();
SELECT @utcdate as 'utc_date';

Ograniczenia

• Gdy skalarny UDF jest używany w dowolnym nieobsługiwanym scenariuszu, podczas wykonywania zapytania pojawia się komunikat Scalar UDF execution is currently unavailable in this context. o błędzie.

• Skalarnego UDF nie można zalinijować za pomocą bloku ekspresyjnego , gdy:

  • Skalarny UDF nie może być inlineowany przez blok ekspresji, gdy skalarny korpus UDF zawiera odniesienia do tabel/widoków/iTVF.
  • Skalarnego UDF nie można zalinijować za pomocą bloku ekspresyjnego, gdy skalarny korpus UDF zawiera odniesienia do tych samych lub innych skalarnych UDF.
  • Skalarnego UDF nie można wyrównać za pomocą bloku wyrażeń, gdy skalarny korpus UDF zawiera wbudowaną funkcję zależną od czasu, taką jak GETDATE(). Aby uzyskać więcej informacji, zobacz funkcje deterministyczne i niedeterministyczne.
  • Skalarny UDF nie może być linijowany za pomocą bloku ekspresji, gdy skalarny korpus UDF zawiera funkcje AI, funkcje agregujące, funkcję JSON_ARRAYAGG, funkcje metadanych, funkcje bezpieczeństwa lub inne funkcje systemowe.

• Skalarny UDF nie może być inlineowany przez skalarne inlineing w następujących warunkach.

  • Skalarny UDF nie może być inlineowany przez skalarne inline'owanie UDF, gdy skalarny korpus UDF zawiera WHILE pętlę BREAK lub CONTINUE instrukcje.
  • Skalarny UDF nie może być inlineowany przez skalarne inline'owanie UDF, gdy skalarny korpus UDF zawiera wiele RETURN instrukcji.
  • Skalarny UDF nie może być inlineowany przez skalarne UDF, gdy skalarny korpus UDF zawiera wbudowaną funkcję zależną od czasu, taką jak GETDATE(). Aby uzyskać więcej informacji, zobacz funkcje deterministyczne i niedeterministyczne.
  • Skalarny UDF nie może być liniowany przez skalarne UDF, gdy skalarny korpus UDF zawiera funkcję STRING_AGG, JSON_ARRAYAGG lub inne funkcje systemowe.
  • Możesz zagnieżdżać funkcje definiowane przez użytkownika. Oznacza to, że jedna funkcja zdefiniowana przez użytkownika może wywołać inną. Poziom zagnieżdżania zwiększa się, gdy wywołana funkcja rozpoczyna wykonywanie, a maleje, gdy funkcja wywołana kończy wykonywanie. W Fabric Data Warehouse można zagnieżdżać funkcje zdefiniowane przez użytkownika do czterech poziomów, gdy ciało UDF odnosi się do tabeli, widoku lub funkcji tabelowej w linii, lub do 32 poziomów w przeciwnym przypadku. Jeśli przekroczysz maksymalne poziomy zagnieżdżania, łańcuch funkcji wywołujących przestaje działać.
  • Aby uzyskać więcej informacji, zobacz Scalar UDF inlining requirements (Wymagania dotyczące tworzenia podkreślenia funkcji UDF skalarnych).

• Skalarny UDF nie może być używany we wszystkich kształtach zapytania, w zależności od zastosowanej techniki inline.

  • Dla skalarnego inliningu UDF:
    • Skalarny UDF nie może być używany w GROUP BY i ORDER BY.
    • Skalarny UDF nie może być używany w połączeniu z CTE.
    • Zapytanie użytkownika może się nie powieść, jeśli w jednym zapytaniu wykonanych jest więcej niż 10 wywołań UDF.

• W Fabric Data Warehouse skalarny UDF nie może być użyty w ROLLUP, CUBE, ani GROUPING SETS.

Przykłady

Odp. Tworzenie wbudowanej funkcji z wartościami tabelarycznymi

Poniższy przykład tworzy funkcję tabelową w linii, która zwraca kluczowe informacje o modułach, filtrując według parametrów objectType . Zawiera domyślną wartość, która zwraca wszystkie moduły po wywołaniu funkcji z parametrem DEFAULT . Ten przykład wykorzystuje niektóre widoki katalogu systemowego wspomniane w metadanych.

CREATE FUNCTION dbo.ModulesByType (@objectType CHAR(2) = '%%')
RETURNS TABLE
AS
RETURN (
        SELECT sm.object_id AS 'Object Id',
            o.create_date AS 'Date Created',
            OBJECT_NAME(sm.object_id) AS 'Name',
            o.type AS 'Type',
            o.type_desc AS 'Type Description',
            sm.DEFINITION AS 'Module Description',
            sm.is_inlineable AS 'Inlineable'
        FROM sys.sql_modules AS sm
        INNER JOIN sys.objects AS o ON sm.object_id = o.object_id
        WHERE o.type LIKE '%' + @objectType + '%'
        );
GO

Wołaj funkcję zwracającą wszystkie funkcje tabelowe w linii (IF):

SELECT * FROM dbo.ModulesByType('IF'); -- SQL_INLINE_TABLE_VALUED_FUNCTION

Lub znajdź wszystkie funkcje skalarne (FN):

SELECT * FROM dbo.ModulesByType('FN'); -- SQL_SCALAR_FUNCTION

B. Łączenie wyników wbudowanej funkcji o wartości tabeli

Ten prosty przykład wykorzystuje wcześniej utworzony inline TVF, aby pokazać, jak można łączyć jego wyniki z innymi tabelami, używając CROSS APPLY. Tutaj wybierasz wszystkie kolumny z obu sys.objects oraz wyniki ModulesByType dla wszystkich wierszy, które pasują do tej kolumny type . Aby uzyskać więcej informacji o użyciu APPLY, zobacz klauzulę FROM plus JOIN, APPLY, PIVOT (Transact-SQL).

SELECT * 
FROM sys.objects AS o
CROSS APPLY dbo.ModulesByType(o.type);
GO

C. Tworzenie funkcji UDF skalarnej

W poniższym przykładzie jest tworzona wbudowana funkcja UDF skalarna, która maskuje tekst wejściowy.

CREATE OR ALTER FUNCTION [dbo].[cleanInput] (@InputString VARCHAR(100))
    RETURNS VARCHAR(50)
    AS
    BEGIN
        DECLARE @Result VARCHAR(50);
        DECLARE @CleanedInput VARCHAR(50);

        -- Trim whitespace
        SET @CleanedInput = LTRIM(RTRIM(@InputString));

        -- Handle empty or null input
        IF @CleanedInput = '' OR @CleanedInput IS NULL
        BEGIN
            SET @Result = '';
        END
        ELSE IF LEN(@CleanedInput) <= 2
        BEGIN
            -- If string length is 1 or 2, just return the cleaned string
            SET @Result = @CleanedInput;
        END
        ELSE
        BEGIN
            -- Construct the masked string
            SET @Result = 
                LEFT(@CleanedInput, 1) +
                REPLICATE('*', LEN(@CleanedInput) - 2) +
                RIGHT(@CleanedInput, 1);
        END

        RETURN @Result
    END

Możesz wywołać funkcję w następujący sposób:

DECLARE @input varchar(100) = '123456789';

SELECT dbo.cleanInput (@input) AS function_output;

Więcej przykładów użycia skalarnych funkcji zdefiniowanych przez użytkownika w usłudze Fabric Data Warehouse:

W instrukcji SELECT :

SELECT TOP 10 
t.id, t.name, 
dbo.cleanInput (t.name) AS function_output
FROM dbo.MyTable AS t;

W klauzuli WHERE :

 SELECT t.id, t.name, dbo.cleanInput(t.name) AS function_output
FROM dbo.MyTable AS t
WHERE dbo.cleanInput(t.name)='myvalue';

W klauzuli JOIN :

SELECT t1.id, t1.name, 
     dbo.cleanInput (t1.name) AS function_output, 
     dbo.cleanInput (t2.name) AS function_output_2
FROM dbo.MyTable1 AS t1
    INNER JOIN dbo.MyTable2 AS t2 
        ON dbo.cleanInput(t1.name)=dbo.cleanInput(t2.name);

W klauzuli ORDER BY :

SELECT  t.id, t.name, dbo.cleanInput (t.name) AS function_output
FROM dbo.MyTable AS t
ORDER BY function_output;

W instrukcjach języka manipulowania danymi (DML), takich jak INSERT, UPDATElub DELETE:

SELECT t.id, t.name, dbo.cleanInput (t.name) AS function_output 
INTO dbo.MyTable_new
FROM dbo.MyTable AS t;

UPDATE t
SET t.mycolumn_new = dbo.cleanInput (t.name)
FROM dbo.MyTable AS t;

DELETE t
FROM dbo.MyTable AS t
WHERE dbo.cleanInput (t.name) ='myvalue';

Treści powiązane

• Stwórz skalarny UDF (podgląd) w Fabric Data Warehouse
• Scalar UDF inlining (Podkreślenie funkcji UDF skalarnych)
• Narzędzie Do migracji magazynu danych sieci szkieletowej

Dotyczy:azure Synapse AnalyticsAnalytics Platform System (PDW)

Tworzy funkcję zdefiniowaną przez użytkownika (UDF) w usłudze Azure Synapse Analytics lub Analytics Platform System (PDW). Funkcja zdefiniowana przez użytkownika jest procedurą Transact-SQL, która akceptuje parametry, wykonuje akcję, taką jak złożone obliczenia i zwraca wynik tej akcji jako wartość. Funkcje tabeli zdefiniowane przez użytkownika (TVFS) zwracają typ danych tabeli.

• W systemie Platform Platform Analytics (PDW) wartość zwracana musi być wartością skalarną (pojedynczą).

• W usłudze Azure Synapse Analytics CREATE FUNCTION można zwrócić tabelę przy użyciu składni dla wbudowanych funkcji wartości tabeli (wersja zapoznawcza) lub może zwrócić pojedynczą wartość przy użyciu składni dla funkcji skalarnych.

• W bezserwerowych pulach SQL w usłudze Azure Synapse Analytics można tworzyć wbudowane funkcje wartości tabeli, CREATE FUNCTION ale nie funkcje skalarne.

  Użyj tego stwierdzenia, aby stworzyć powtarzalną rutynę, którą możesz wykorzystać w następujący sposób:

• W Transact-SQL stwierdzeniach, takich jak SELECT

• W aplikacjach, które wywołują funkcję

• W definicji innej funkcji zdefiniowanej przez użytkownika

• Aby zdefiniować ograniczenie CHECK w kolumnie

• Aby zastąpić procedurę składowaną

• Używanie funkcji wbudowanej jako predykatu filtru dla zasad zabezpieczeń

Transact-SQL konwencje składni

Składnia

Składnia funkcji skalarnych

-- Transact-SQL Scalar Function Syntax (in dedicated pools in Azure Synapse Analytics and Parallel Data Warehouse)
-- Not available in the serverless SQL pools in Azure Synapse Analytics

CREATE FUNCTION [ schema_name. ] function_name   
( [ { @parameter_name [ AS ] parameter_data_type   
    [ = default ] }   
    [ ,...n ]  
  ]  
)  
RETURNS return_data_type  
    [ WITH <function_option> [ ,...n ] ]  
    [ AS ]  
    BEGIN   
        function_body   
        RETURN scalar_expression  
    END  
[ ; ]  

<function_option>::=   
{  
    [ SCHEMABINDING ]  
  | [ RETURNS NULL ON NULL INPUT | CALLED ON NULL INPUT ]  
}  

Składnia funkcji wbudowanych wartości tabeli

-- Transact-SQL Inline Table-Valued Function Syntax
-- Preview in dedicated SQL pools in Azure Synapse Analytics
-- Available in the serverless SQL pools in Azure Synapse Analytics
CREATE FUNCTION [ schema_name. ] function_name
( [ { @parameter_name [ AS ] parameter_data_type
    [ = default ] }
    [ ,...n ]
  ]
)
RETURNS TABLE
    [ WITH SCHEMABINDING ]
    [ AS ]
    RETURN [ ( ] select_stmt [ ) ]
[ ; ]

Argumenty (w programowaniu)

schema_name

Nazwa schematu, do którego należy funkcja zdefiniowana przez użytkownika.

function_name

Nazwa funkcji zdefiniowanej przez użytkownika. Nazwy funkcji muszą spełniać reguły dotyczące identyfikatorów i być unikalne w bazie danych oraz jej schemacie.

@ parameter_name

Parametr w funkcji zdefiniowanej przez użytkownika. Możesz zadeklarować jeden lub więcej parametrów.

Funkcja może mieć do 2 100 parametrów. Gdy użytkownik lub aplikacja wywołuje funkcję, wartość każdego zadeklarowanego parametru musi być podana, chyba że parametr domyślny zostanie zdefiniowany.

Określ nazwę parametru przy użyciu znaku (@) jako pierwszego znaku. Nazwa parametru musi odpowiadać regułom dla identyfikatorów. Parametry są lokalne dla funkcji; Możesz używać tych samych nazw parametrów w innych funkcjach. Parametry mogą zastąpić jedynie stałe; Nie można ich używać zamiast nazw tabel, kolumn ani nazw innych obiektów bazy danych.

parameter_data_type

Typ danych parametru. W przypadku funkcji Transact-SQL dozwolone są wszystkie typy danych skalarnych obsługiwane w usłudze Azure Synapse Analytics. Typ danych timestamp (rowversion) nie jest obsługiwanym typem.

[ = domyślnie ]

Wartość domyślna parametru. Jeśli zdefiniujesz wartość domyślną , możesz wykonać funkcję bez określania wartości tego parametru.

Gdy parametr funkcji ma wartość domyślną, musisz podać słowo DEFAULT kluczowe podczas wywoływania funkcji, aby uzyskać wartość domyślną. To zachowanie różni się od używania parametrów z wartościami domyślnymi w procedurach składowanych, w których pominięcie parametru również implikuje wartość domyślną.

return_data_type

Wartość zwracana przez skalarną funkcję zdefiniowaną przez użytkownika. W przypadku funkcji Transact-SQL dozwolone są wszystkie typy danych skalarnych obsługiwane w usłudze Azure Synapse Analytics. Typ danychdatowy z znacznikiem czasowym/ wiersza nie jest obsługiwanym typem. Kursor i typy nieskalarne tabeli nie są dozwolone.

function_body

Seria instrukcji Transact-SQL. function_body nie może zawierać SELECT instrukcji ani odwoływać się do danych bazy danych. function_body nie może odwoływać się do tabel ani widoków. Ciało funkcji może wywoływać inne funkcje deterministyczne, ale nie może wywoływać funkcji niedeterministycznych.

W funkcjach skalarnych function_body jest serią Transact-SQL instrukcji, które razem dają w wyniku wartość skalarną.

scalar_expression

Określa wartość skalarną zwracaną przez funkcję skalarną.

select_stmt

Pojedyncza SELECT instrukcja, która definiuje wartość zwracaną funkcji tabeli wbudowanej. Dla funkcji tabelowej w linii nie ma ciała funkcji; tabela to zbiór wyników pojedynczego SELECT zapowiedzenia.

TABLE

Określa, że wartością zwracaną przez funkcję zwracającą tabelę (TVF) jest tabela. Możesz przekazywać tylko stałe i @local_variables do TVF.

W inline TVF (podgląd) definiujesz wartość zwrotną TABLE za pomocą pojedynczego SELECT wypowiedzenia. Funkcje wbudowane nie mają skojarzonych zmiennych zwracanych.

<function_option>

Określa, że funkcja ma co najmniej jedną z następujących opcji.

POWIĄZANIE SCHEMATU

Określa, że funkcja jest powiązana z obiektami bazy danych, do których się odwołuje. Gdy określasz SCHEMABINDING, nie możesz modyfikować obiektów bazowych (takich jak widok czy tabela, na przykład) w sposób, który wpływałby na definicję funkcji. Najpierw musisz zmodyfikować lub usunąć definicję funkcji, aby usunąć zależności od obiektu, który chcesz zmodyfikować.

Powiązanie funkcji z obiektami, do których się odwołuje, jest usuwane tylko wtedy, gdy występuje jedna z następujących akcji:

• Rezygnuj z tej funkcji.

• Używasz instrukcji funkcji i usuwasz ALTERSCHEMABINDING tę opcję.

Możesz związać funkcję schematem tylko wtedy, gdy spełnione są następujące warunki:

• Wszystkie funkcje zdefiniowane przez użytkownika, do których funkcja się odwołuje, są również ograniczone schematem.

• Odniesienia do funkcji używają nazw jednoczęściowych lub dwuczęściowych.

• W treści UDF możesz odwoływać się tylko do wbudowanych funkcji i innych UDF w tej samej bazie danych.

• Użytkownik, który wykonuje zdanie, CREATE FUNCTION ma uprawnienia REFERENCJI do obiektów bazy danych, do których funkcja się odwołuje.

Aby usunąć SCHEMATBINDING, użyj polecenia ALTER.

ZWRACA WARTOŚĆ NULL DLA DANYCH WEJŚCIOWYCH O WARTOŚCI NULL | WYWOŁYWANE PRZY DANYCH WEJŚCIOWYCH O WARTOŚCI NULL

Określa OnNULLCall atrybut funkcji wartości skalarnej. Jeśli nie określisz tego atrybutu, jest CALLED ON NULL INPUT domyślnie implikowany, a ciało funkcji wykonuje się nawet jeśli NULL zostanie przekazane jako argument.

Najlepsze rozwiązania

Jeśli nie utworzysz funkcji zdefiniowanej przez użytkownika z klauzulą SCHEMABINDING, zmiany w obiektach bazowych mogą wpłynąć na definicję funkcji i powodować nieoczekiwane skutki podczas jej wywołania. Określ klauzulę WITH SCHEMABINDING podczas tworzenia funkcji. Ta klauzula zapewnia, że nie możesz modyfikować obiektów cytowanych w definicji funkcji, chyba że również zmodyfikujesz samą funkcję.

Współdziałanie

Następujące instrukcje są prawidłowe w funkcji skalarnej wartości:

• Instrukcje przypisania.

• Instrukcje kontroli przepływu, z wyjątkiem TRY... Oświadczenia CATCH.

• Instrukcje DECLARE definiujące lokalne zmienne danych.

W funkcji tabelowej (podgląd) można użyć tylko jednego polecenia select.

Ograniczenia

Nie możesz używać funkcji zdefiniowanych przez użytkownika do wykonywania działań modyfikujących stan bazy danych.

Możesz zagnieżdżać funkcje definiowane przez użytkownika. Jedna funkcja zdefiniowana przez użytkownika może wywoływać inną. Poziom zagnieżdżania zwiększa się, gdy wywołana funkcja rozpoczyna wykonywanie, a maleje, gdy funkcja wywołana kończy wykonywanie. Jeśli przekroczysz maksymalne poziomy zagnieżdżania, cały łańcuch funkcji wywołujących zawodzi.

Nie możesz tworzyć obiektów, w tym funkcji, w bazie master danych swojej serwerowej puli SQL w Azure Synapse Analytics.

Metadane

W tej sekcji wymieniono widoki wykazu systemu, których można użyć do zwracania metadanych dotyczących funkcji zdefiniowanych przez użytkownika.

• sys.sql_modules: wyświetla definicję funkcji zdefiniowanych przez użytkownika Transact-SQL. Przykład:

  SELECT definition, type   
  FROM sys.sql_modules AS m  
  JOIN sys.objects AS o   
      ON m.object_id = o.object_id   
      AND type = ('FN');
  

• sys.parameters: wyświetla informacje o parametrach zdefiniowanych przez użytkownika.

• sys.sql_expression_dependencies: wyświetla obiekty bazowe, do których odwołuje się funkcja.

Uprawnienia

Wymaga CREATE FUNCTION uprawnień do bazy danych oraz ALTER do schematu, w którym funkcja jest tworzona.

Przykłady

Odp. Zmiana typu danych przy użyciu funkcji zdefiniowanej przez użytkownika o wartości skalarnej

Ta prosta funkcja przyjmuje jako wejście typ danych int i zwraca dziesiętny typ danych (10,2) jako wyjście.

CREATE FUNCTION dbo.ConvertInput (@MyValueIn int)  
RETURNS decimal(10,2)  
AS  
BEGIN
    DECLARE @MyValueOut int;  
    SET @MyValueOut= CAST( @MyValueIn AS decimal(10,2));  
    RETURN(@MyValueOut);  
END;  
GO  

SELECT dbo.ConvertInput(15) AS 'ConvertedValue';  

B. Tworzenie wbudowanej funkcji z wartościami tabelarycznymi

Poniższy przykład tworzy funkcję tabelową w linii, która zwraca kluczowe informacje o modułach, filtrując według parametrów objectType . Zawiera domyślną wartość, która zwraca wszystkie moduły po wywołaniu funkcji z parametrem DEFAULT . Ten przykład wykorzystuje niektóre widoki katalogu systemowego wspomniane w metadanych.

CREATE FUNCTION dbo.ModulesByType(@objectType CHAR(2) = '%%')
RETURNS TABLE
AS
RETURN
(
    SELECT 
        sm.object_id AS 'Object Id',
        o.create_date AS 'Date Created',
        OBJECT_NAME(sm.object_id) AS 'Name',
        o.type AS 'Type',
        o.type_desc AS 'Type Description', 
        sm.definition AS 'Module Description'
    FROM sys.sql_modules AS sm  
    JOIN sys.objects AS o ON sm.object_id = o.object_id
    WHERE o.type like '%' + @objectType + '%'
);
GO

Możesz wywołać funkcję zwracającą wszystkie obiekty view (V) z:

select * from dbo.ModulesByType('V');

C. Łączenie wyników wbudowanej funkcji o wartości tabeli

Ten prosty przykład wykorzystuje wcześniej utworzony inline TVF, aby pokazać, jak można łączyć jego wyniki z innymi tabelami, używając CROSS APPLY. W tym przykładzie wybierasz wszystkie kolumny z obu sys.objects oraz wyniki dla ModulesByType wszystkich wierszy, które pasują do kolumny type . Aby uzyskać więcej informacji o użyciu APPLY, zobacz klauzulę FROM plus JOIN, APPLY, PIVOT (Transact-SQL).

SELECT * 
FROM sys.objects o
CROSS APPLY dbo.ModulesByType(o.type);
GO

Następny krok