Notatka
Dostęp do tej strony wymaga autoryzacji. Może spróbować zalogować się lub zmienić katalogi.
Dostęp do tej strony wymaga autoryzacji. Możesz spróbować zmienić katalogi.
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, orazDELETE. - 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/ELSEiBEGIN/ENDbloki) - Zmienne lokalne
- Wywołania wbudowanych funkcji SQL dostępnych
- Wywołania do innych funkcji zdefiniowanych przez użytkownika
-
SELECTinstrukcje 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, orazRETURN
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
ALTERSCHEMABINDINGtę 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 FUNCTIONma 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 SCHEMABINDINGmoment 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,
CREATEinstrukcja ORALTERodrzuca polecenie DDL z niestandardowym komunikatem o błędzie.Funkcję nieliniową można utworzyć, określając
INLINE=AUTOopcję funkcji. Funkcja nieinlineowalna nie może być użyta w zapytaniuSELECT ... FROMw tabeli użytkownika.INLINE=AUTONie 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...CATCHinstrukcji iGOTOinstrukcji. -
DECLAREinstrukcje 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.parameterswidok 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.
- Inline bloku wyrażeń nie obsługuje funkcji AI, funkcji agregacyjnych, funkcji metadanych ani funkcji zabezpieczeń w ciele funkcji.
- Skalarne inline UDF ma wbudowane ograniczenia funkcji opisane w wymaganiach dotyczących inline Scalar UDF.
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.
Treści powiązane
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 FUNCTIONmoż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 FUNCTIONale 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
SELECTW 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
ALTERSCHEMABINDINGtę 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 FUNCTIONma 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.