CREATE FUNCTION

Dotyczy do:Punktu końcowego SQL Analytics w Microsoft Fabric oraz Warehouse w Microsoft Fabric

CREATE FUNCTION tworzy funkcje tabelarne w linii oraz funkcje skalarne.

Uwaga / Notatka

Zewnętrzne UDF są funkcją podglądową w Fabric Data Warehouse.

Ten artykuł dotyczy Microsoft Fabric Data Warehouse oraz endpointu analityki SQL Fabric elementów. W przypadku innych platform zobacz CREATE FUNCTION (Transact-SQL).

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 Warehouse, 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ładniowe

Składnia

Składnia funkcji skalarnych

CREATE [ OR ALTER ] 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 [ OR ALTER ] FUNCTION [ schema_name. ] function_name
( [ { @parameter_name [ AS ] parameter_data_type
    [ = default ] }
    [ ,...n ]
  ]
)
RETURNS TABLE
    [ WITH SCHEMABINDING ]
    [ AS ]
    RETURN [ ( ] select_stmt [ ) ]
[ ; ]

Składnia funkcji zewnętrznych

Zewnętrzny UDF to funkcja odwołująca się do zewnętrznej funkcji danych użytkownika Fabric.

CREATE FUNCTION [ schema_name. ] function_name
[ RETURNS return_data_type ]
AS EXTERNAL FUNCTION external_function_set_name.external_function_name
[ ; ]

Uwaga / Notatka

Zewnętrzne UDF są funkcją podglądową w Fabric Data Warehouse.

Instrukcja CREATE FUNCTION automatycznie wyprowadza typ return, parametry i typy parametrów z definicji odwołanej zewnętrznej funkcji danych użytkownika Fabric. Jeśli sygnatura zewnętrznej funkcji danych użytkownika Fabric się zmieni, musisz odtworzyć tę funkcję, aby zsynchronizować jej definicję z zaktualizowaną sygnaturą.

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.

Dla natywnych definicji funkcji skalarnych T-SQL i wartości tabelarnych w linii dodaj nawiasy po nazwie funkcji, nawet jeśli nie ma parametrów. Definicje funkcji zewnętrznych używają osobnej AS EXTERNAL FUNCTION składni.

@ 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ę, musisz podać wartość dla każdego zadeklarowanego parametru, chyba że zdefiniujesz domyślną wartość parametru.

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ć tylko stałe. Nie można ich używać zamiast nazw tabel, kolumn ani nazw innych obiektów bazy danych.

ANSI_WARNINGS nie jest respektowane, gdy przekazujesz parametry w procedurze składowanej, funkcji zdefiniowanej przez użytkownika lub gdy deklarujesz i ustawiasz zmienne 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.

Dla funkcji w Fabric Data Warehouse możesz używać wszystkich typów danych opróczznacznikaczasu wersji/ wiersza. Typy nieskalarne, takie jak tabele , nie są dozwolone.

W przypadku zewnętrznych UDF (Podgląd) klauzula RETURNS jest opcjonalna. Jeśli zostanie pominięty, typ zwrotu jest automatycznie wywnioskowany z typu zwrotu odwołanej funkcji danych użytkownika Fabric. Możesz określić klauzulę RETURNS , która nadpisuje wywnioskowany typ return, gdy jest to potrzebne.

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 wieloinstrukcyjne (IF/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 oraz odwołania do tabel, widoków i funkcji w wierszu o wartości tabeli
  • Instrukcje kontroli przepływu, w tym IF/ELSE, WHILE, BREAK, CONTINUE, oraz RETURN

W przypadku zewnętrznych UDF-ów (Podgląd) nie można określić function_body, ponieważ implementacja funkcji jest zdefiniowana zewnętrznie w powiązanej Fabric User Data Function. Baza danych przechowuje tylko metadane funkcji. Logika pliku wykonywalnego znajduje się w definicji funkcji zewnętrznej.

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 wpisanej w tabelę nie ma ciała funkcji. Tabela jest zbiorem wyników pojedynczego SELECT zalecenia.

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ą:

LINIOWY = 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.

Aby uzyskać obsługiwany samodzielny wzorzec wywołań, zobacz Tworzenie skalarnego UDF do samodzielnego wykonywania.

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 W PRZYPADKU DANYCH WEJŚCIOWYCH O WARTOŚCI NULL | WYWOŁYWANE PRZY DANYCH WEJŚCIOWYCH 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.

JAKO ZEWNĘTRZNY FUNCTION

Odnosi się do funkcji danych użytkownika Fabric (UDF). Po wywołaniu funkcji wykonanie jest delegowane do odwołanej funkcji danych użytkownika Fabric, gdzie logika jest wykonywana, a wynik zwracany jest wywoływającemu T-SQL.

Ta klauzula akceptuje następujące parametry:

  • Nazwa zestawu funkcji: Nazwa zestawu funkcji zawierającego funkcję danych użytkownika Fabric. Każda funkcja danych użytkownika Fabric musi należeć do zestawu funkcji.
  • Nazwa funkcji: Nazwa funkcji danych użytkownika Fabric do odwołania.

Poniższa składnia pokazuje, jak te parametry są określane:

CREATE FUNCTION [schema_name.]function_name
AS EXTERNAL FUNCTION <<functionset_name>>.<<python_function_name>>

Ponieważ funkcja jest wykonywana zdalnie, wywołanie funkcji zewnętrznej zazwyczaj wiąże się z większym opóźnieniem niż wykonanie natywnej funkcji użytkownika T-SQL. Używaj funkcji zewnętrznych, gdy wymagana logika biznesowa jest zaimplementowana w Fabric i nie może być bezpośrednio wyrażona w T-SQL.

Instrukcja CREATE FUNCTION automatycznie wyprowadza typ return, parametry i typy parametrów z definicji odwołanej zewnętrznej funkcji danych użytkownika Fabric. Jeśli sygnatura zewnętrznej funkcji danych użytkownika Fabric się zmieni, musisz odtworzyć tę funkcję, aby zsynchronizować jej definicję z zaktualizowaną sygnaturą.

Najlepsze rozwiązania

  • 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.

  • 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.

  • Kiedy to możliwe, implementuj swoją logikę jako funkcję T-SQL zdefiniowaną przez użytkownika (UDF). Używaj zewnętrznego UDF tylko do funkcji, których nie da się zaimplementować w języku T-SQL. Takie podejście pomaga zminimalizować narzut zdalnego wykonania i generalnie zapewnia lepszą wydajność zapytań.

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

  • Gdy skalarny UDF nie jest inlineowalny, CREATE instrukcja OR ALTER odrzuca polecenie DDL z niestandardowym komunikatem o błędzie.

  • Funkcję nieliniową można utworzyć, określając INLINE=AUTO opcję funkcji. Funkcja nieinlineowalna nie może być użyta w zapytaniu SELECT ... FROM w tabeli użytkownika. INLINE=AUTO Nie sprawia, że funkcja jest liniowa.

  • 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.

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 zdefiniowanych przez użytkownika oraz informacje o możliwości inline. 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 w funkcjach zdefiniowanych przez użytkownika. Poniższy przykład wykorzystuje sys.parameters widok katalogowy do wyświetlania sygnatur funkcji, w tym typów zwrotów i typów danych parametrów:

     WITH metadata AS
     (
         SELECT  o.schema_id, o.object_id, o.name, p.parameter_id, p.name AS parameter_name, t.name AS type_name
         FROM sys.objects o JOIN sys.parameters p ON p.object_id = o.object_id JOIN sys.types t ON t.user_type_id = p.user_type_id
         WHERE o.type IN ('FN', 'XF')
     ),
     signature AS (
     SELECT SCHEMA_NAME(schema_id) as schema_name, name, ANY_VALUE(CASE WHEN parameter_id = 0 THEN type_name END) return_type, CONCAT(
         SCHEMA_NAME(schema_id), '.', name, '(',STRING_AGG(CASE WHEN parameter_id > 0 THEN CONCAT(parameter_name, ' ', type_name) END, ', ' ) WITHIN GROUP (ORDER BY parameter_id),
         ') -> ',  MAX(CASE WHEN parameter_id = 0 THEN type_name END)
     ) AS signature
     FROM metadata GROUP BY schema_id, object_id, name
     )
     SELECT *
     FROM signature;
    
  • sys.sql_expression_dependencies: Wyświetla obiekty bazowe odwoływane przez funkcję.

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 automatycznie wprowadza kwalifikujące się skalarne UDF podczas kompilacji zapytań.

Inline bloku wyrażeń kompiluje instrukcje funkcji, w tym gałęzie i pętle, w wyrażenie skalarne, które wykonuje się jako część zapytania wywołującego. Skalarne inline UDF przekształca ciało funkcji w wyrażenia lub podzapytania jako część zapytania wywołującego.

Funkcja może spełniać wymagania obu technik, ale silnik wybiera jedną na podstawie jej definicji. Różne funkcje w tym samym zapytaniu mogą korzystać z różnych technik.

W miarę jak Microsoft Fabric Data Warehouse rozszerza swoje możliwości inline, istniejące funkcje automatycznie je zyskują.

Obsługiwane konstrukcje funkcji

Poniższa tabela porównuje obsługiwane konstrukcje funkcji-ciała.

Konstrukcja funkcja-ciało Inline bloku wyrażenia Scalar UDF inlining (Podkreślenie funkcji UDF skalarnych)
Wyrażenia skalarne, zmienne lokalne oraz IF/ELSE Supported Supported
WHILE pętle, w tym BREAK i CONTINUE Supported Nieobsługiwane
Wiele RETURN deklaracji Supported Nieobsługiwane
NEWID() Supported Nieobsługiwane
Funkcje zależne od czasu (na przykład GETDATE() oraz SYSDATETIME()) Supported Nieobsługiwane
Dostęp do tabeli, widoku lub w linii TVF Nieobsługiwane Supported
Wywołania skalarnych UDF* Nieobsługiwane Supported

* Funkcje zdefiniowane przez użytkownika mogą być zagnieżdżone: jeden UDF może wywołać drugi. Poziom zagnieżdżania wzrasta po uruchomieniu wywołanej funkcji i maleje po jej zakończeniu. Microsoft Fabric Data Warehouse obsługuje do czterech poziomów zagnieżdżania, gdy ciało UDF odwołuje się do tabeli, widoku lub inline TVF, lub do 32 poziomów w innych przypadkach. Przekroczenie limitu zagnieżdżania powoduje awarię łańcucha funkcji wywołujących.

Wbudowane funkcje

Obsługa funkcji wbudowanych, w tym funkcji systemowych, różni się między tymi dwoma technikami inline.

Na przykład tworzenie UDF, który używa @@ROWCOUNT błędu bez INLINE = AUTO zwraca 19841. Komunikat o błędzie zawiera następujący tekst:

Function <function_name> cannot be inlined.
Expression block: Unsupported built-in @@ROWCOUNT found.
Scalar UDF inlining: Unsupported built-in @@ROWCOUNT found.

Aby uzyskać konkretną definicję funkcji, zobacz Sprawdź, czy skalarny UDF można inline.

Obsługiwane zapytania wywoławcze

Poniższa tabela porównuje obsługę zapytań wywoławczych dla skalarnych UDF inlineowalnych.

Funkcja kwerendy Inline bloku wyrażenia Scalar UDF inlining (Podkreślenie funkcji UDF skalarnych)
Wywołania UDF na liście SELECT , WHERE, lub JOIN ... ON Supported Supported
Bezpośrednie wywołania UDF lub GROUP BYHAVING Supported Nieobsługiwane
Bezpośrednie połączenia UDF ORDER BY Supported Nieobsługiwane
CTE w zapytaniu wywołującym Supported Nieobsługiwane
ROLLUP, CUBElub GROUPING SETS Nieobsługiwane Nieobsługiwane

Jeśli jakiekolwiek wywołanie UDF opiera się na inlineingu Scalar UDF, całe zapytanie musi spełniać wymagania tej techniki dotyczące calling-query.

Przy skalarnym UDF inline, wynik UDF jest wyświetlany na SELECT liście i uporządkowany według aliasu kolumny lub porządku porządkowego zamiast wywoływać UDF bezpośrednio w .ORDER BY Przykłady przepisywania zapytań znajdziesz w sekcji Adaptacja zapytania wywołującego dla inline'owania Scalar UDF. Inne limity wykonania znajdziesz w sekcji Ograniczenia.

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

Widok sys.sql_modules katalogu opisuje uprawnienia na podstawie definicji funkcji. is_inlineable jest 1 dla definicji inline-owalnej i 0 w innych przypadkach. inline_eligibility_mask Identyfikuje kwalifikujące się techniki:

inline_eligibility_mask Uprawnienia definicji
0 Żadna z tych technik
1 Scalar UDF inlining (Podkreślenie funkcji UDF skalarnych)
2 Inline bloku wyrażenia
3 Obie metody

Wartości te opisują definicję funkcji, a nie plan wykonania konkretnego zapytania.

Użyj poniższego przykładowego zapytania, aby sprawdzić, czy skalarny UDF jest inlineowalny:

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

Ograniczenia

  • Wywołanie skalarnego UDF w nieobsługiwanym kontekście zapytania może zwrócić błąd Scalar UDF execution is currently unavailable in this context.
  • Zapytanie oparte na inline Scalar UDF może zakończyć się niepowodzeniem, jeśli zawiera więcej niż 10 wywołań UDF.

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) = '1*******9';

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 Warehouse możesz także wywoływać skalarne UDF z instrukcji manipulacji danymi, takich jak INSERT, UPDATE, lub DELETE. Te przykłady modyfikacji danych nie dotyczą punktu końcowego SQL analytics.

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) = '1*******9';

D. Stwórz funkcję zewnętrzną

Poniższy przykład tworzy zewnętrzną funkcję geohash nazwaną w schemiedbo, która odnosi się do geohash funkcji Fabric User Data w zbiorze spatial_functions funkcji. Ponieważ definicje parametrów i typ zwrotu są wywnioskowane z referencyjnej funkcji Fabric User Data, nie muszą być one wyraźnie określone w CREATE FUNCTION instrukcji.

CREATE FUNCTION dbo.geohash
AS EXTERNAL FUNCTION spatial_functions.geohash;

Po utworzeniu funkcja zewnętrzna może być używana w zapytaniach T-SQL tak samo jak każda inna funkcja zdefiniowana przez użytkownika lub wbudowana. Może być odwołany wszędzie tam, gdzie obsługiwane są wywołania funkcji, co umożliwia płynną integrację funkcji danych użytkowników Fabric z kodem T-SQL.

E. Utwórz skalarny UDF do samodzielnego wykonywania

Poniższa konfiguracja tworzenia tabel i ładowania danych wymaga Warehouse. Na endpointzie SQL Analytics użyj istniejącej tabeli źródłowej z wymaganymi kolumnami i danymi.

CREATE TABLE dbo.Customer
(
    CustomerID INT NOT NULL,
    CustomerName VARCHAR(100) NOT NULL,
    CustomerType VARCHAR(20) NOT NULL,
    MemberSince DATE NOT NULL,
    YearToDateRevenue DECIMAL(18, 2) NOT NULL
);
GO

INSERT INTO dbo.Customer
(
    CustomerID,
    CustomerName,
    CustomerType,
    MemberSince,
    YearToDateRevenue
)
VALUES
    (1, 'Customer 1', 'Regular', '2020-01-15', 12000.00),
    (2, 'Customer 2', 'Premium', '2022-06-01', 24000.00),
    (3, 'Customer 3', 'VIP',     '2024-03-10', 36000.00);
GO

Poniższa funkcja łączy dostęp do tabeli z GETUTCDATE(). Dostęp do tabeli zapobiega inlineowaniu bloków wyrażeń oraz GETUTCDATE() zapobiega inlinerowaniu UDF skalarnym.

Bez WITH INLINE = AUTO, definicja jest odrzucana przy tworzeniu lub zmianie. Błąd informuje, że funkcja nie może być inlineowana i podaje powody odrzucenia dla obu technik. Określ, WITH INLINE = AUTO aby zezwolić na tworzenie:

CREATE OR ALTER FUNCTION dbo.GetCustomerTenureDays (@CustomerID INT)
RETURNS INT
WITH INLINE = AUTO
AS
BEGIN
    DECLARE @MemberSince DATE;

    SELECT @MemberSince = MemberSince
    FROM dbo.Customer
    WHERE CustomerID = @CustomerID;

    RETURN DATEDIFF(DAY, @MemberSince, GETUTCDATE());
END;
GO

Funkcja jest tworzona, ale pozostaje nieliniowalna: is_inlineable i inline_eligibility_mask obie 0są . Samodzielne połączenie zakończy się sukcesem:

DECLARE @udf_output int;
SET @udf_output = dbo.GetCustomerTenureDays(1);
SELECT @udf_output as 'udf_output';

Wywoływanie funkcji w zapytaniu nad tabelą użytkownika nie jest obsługiwane:

SELECT
    CustomerID,
    dbo.GetCustomerTenureDays(CustomerID) AS TenureDays
FROM dbo.Customer;

To zapytanie zwraca Scalar UDF execution is currently unavailable in this context.

Wskazówka

Aby użyć tej logiki w zapytaniu nad tabelami użytkownika, ocenij wartość current-time w wywołującym i przekaż ją jako parametr. W przypadku przepisanej funkcji i zapytania wywołującego zobacz Przekazywanie wartości zależnej od czasu do UDF dostępu do danych.

Dotyczy do:Azure Synapse Analytics

Tworzy funkcję zdefiniowaną przez użytkownika (UDF) w Azure Synapse Analytics. 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.

Wskazówka

Składnia w Fabric Data Warehouse dostępna jest w wersji Fabric Data WarehouseCREATE FUNCTION.

  • 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ładniowe

Składnia

Składnia funkcji skalarnych

-- Transact-SQL Scalar Function Syntax (in dedicated pools in Azure Synapse Analytics)
-- 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.

Uwaga / Notatka

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ę, musisz podać wartość dla każdego zadeklarowanego parametru, chyba że zdefiniujesz domyślną wartość parametru.

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.

Uwaga / Notatka

ANSI_WARNINGS nie jest respektowane, gdy przekazujesz parametry w procedurze składowanej, funkcji zdefiniowanej przez użytkownika lub gdy deklarujesz i ustawiasz zmienne 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 INSERT instrukcja or UPDATE odnosi sukces.

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ę Transact-SQL funkcji zdefiniowanych przez użytkownika. 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 w funkcjach zdefiniowanych przez użytkownika.

  • sys.sql_expression_dependencies: Wyświetla obiekty bazowe odwoływane przez funkcję.

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';  

Uwaga / Notatka

Funkcje skalarne nie są dostępne w bezserwerowych pulach SQL.

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');

Uwaga / Notatka

Funkcje tabelowe w linii są dostępne w bezserwerowych pulach SQL, ale w trybie podglądu w dedykowanych pulach SQL.

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

Uwaga / Notatka

Funkcje tabelowe w linii są dostępne w bezserwerowych pulach SQL, ale w trybie podglądu w dedykowanych pulach SQL.

Następny krok