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:
programu SQL Server
Aby można było użyć typu zdefiniowanego przez użytkownika (UDT) w programie SQL Server, należy go zarejestrować. Zarejestrowanie udT obejmuje zarejestrowanie zestawu i utworzenie typu w bazie danych, w której chcesz go użyć. Trasy zdefiniowane przez użytkownika są ograniczone do pojedynczej bazy danych i nie można ich używać w wielu bazach danych, chyba że identyczny zestaw i funkcja UDT są zarejestrowane w każdej bazie danych. Po zarejestrowaniu zestawu UDT i utworzeniu typu można użyć funkcji UDT w Transact-SQL i w kodzie klienta. Aby uzyskać więcej informacji, zobacz typy zdefiniowane przez użytkownika środowiska CLR.
Wdrażanie tras zdefiniowanych przez użytkownika przy użyciu programu Visual Studio
Najprostszym sposobem wdrożenia funkcji UDT jest użycie programu Visual Studio. W przypadku bardziej złożonych scenariuszy wdrażania i największej elastyczności należy jednak użyć Transact-SQL zgodnie z opisem w dalszej części tego artykułu.
Wykonaj następujące kroki, aby utworzyć i wdrożyć udT przy użyciu programu Visual Studio:
Utwórz nowy projekt
Database w węzłach językaVisual Basic lubVisual C#. Dodaj odwołanie do bazy danych programu SQL Server, która będzie zawierać udT.
Dodaj klasę typu
User-Defined. Pisanie kodu w celu zaimplementowania funkcji UDT.
Z menu kompilacji
wybierz pozycję Wdróż . Spowoduje to zarejestrowanie zestawu i utworzenie typu w bazie danych programu SQL Server.
Wdrażanie tras zdefiniowanych przez użytkownika przy użyciu Transact-SQL
Składnia Transact-SQL CREATE ASSEMBLY służy do rejestrowania zestawu w bazie danych, w której chcesz użyć funkcji UDT. Jest on przechowywany wewnętrznie w tabelach systemu bazy danych, a nie zewnętrznie w systemie plików. Jeśli funkcja UDT jest zależna od zestawów zewnętrznych, również muszą zostać załadowane do bazy danych. Instrukcja CREATE TYPE służy do tworzenia udT w bazie danych, w której ma być używana. Aby uzyskać więcej informacji, zobacz CREATE ASSEMBLY i CREATE TYPE.
Korzystanie z zestawu tworzenia
Składnia CREATE ASSEMBLY rejestruje zestaw w bazie danych, w której chcesz użyć funkcji UDT. Po zarejestrowaniu zestawu nie ma żadnych zależności.
Tworzenie wielu wersji tego samego zestawu w danej bazie danych nie jest dozwolone. Istnieje jednak możliwość utworzenia wielu wersji tego samego zestawu na podstawie kultury w danej bazie danych. Program SQL Server rozróżnia wiele wersji kultury zestawu według różnych nazw, jak zarejestrowano w wystąpieniu programu SQL Server. Aby uzyskać więcej informacji, zobacz Create and use strong-named assemblies.
Po wykonaniu CREATE ASSEMBLY z zestawami uprawnień SAFE lub EXTERNAL_ACCESS zestaw jest sprawdzany, aby upewnić się, że jest on weryfikowalny i bezpieczny typ. Jeśli pominięto określanie zestawu uprawnień, zakłada się, że SAFE. Kod z zestawem uprawnień UNSAFE nie jest zaznaczony. Aby uzyskać więcej informacji na temat zestawów uprawnień, zobacz Projektowanie zestawów.
Przykład
Poniższa instrukcja Transact-SQL rejestruje zestaw Point w programie SQL Server w bazie danych AdventureWorks2025 z zestawem uprawnień SAFE. Jeśli pominięto klauzulę WITH PERMISSION_SET, zestaw jest zarejestrowany w zestawie uprawnień SAFE.
USE AdventureWorks2022;
CREATE ASSEMBLY Point
FROM '\\ShareName\Projects\Point\bin\Point.dll'
WITH PERMISSION_SET = SAFE;
Poniższa instrukcja Transact-SQL rejestruje zestaw przy użyciu argumentu <assembly_bits> w klauzuli FROM. Ta wartość varbinary reprezentuje plik jako strumień bajtów.
USE AdventureWorks2022;
CREATE ASSEMBLY Point
FROM 0xfeac4 ... 21ac78
Użyj typu tworzenia
Po załadowaniu zestawu do bazy danych można utworzyć typ przy użyciu instrukcji Transact-SQL CREATE TYPE. Spowoduje to dodanie typu do listy dostępnych typów dla tej bazy danych. Typ ma zakres bazy danych i typ może być używany tylko w bazie danych, w której została utworzona. Jeśli funkcja UDT już istnieje w bazie danych, instrukcja CREATE TYPE kończy się niepowodzeniem z powodu błędu.
Nuta
Składnia CREATE TYPE jest również używana do tworzenia natywnych typów danych aliasów programu SQL Server i ma na celu zastąpienie sp_addtype sposobem tworzenia aliasów typów danych. Niektóre opcjonalne argumenty w składni CREATE TYPE odnoszą się do tworzenia tras zdefiniowanych przez użytkownika i nie mają zastosowania do tworzenia typów danych aliasu (takich jak typ podstawowy).
Aby uzyskać więcej informacji, zobacz CREATE TYPE.
Przykład
Poniższa instrukcja Transact-SQL tworzy typ Point.
EXTERNAL NAME jest określony przy użyciu dwuczęściowej składni nazewnictwa <assembly_name>.<udt_name>.
CREATE TYPE dbo.Point
EXTERNAL NAME Point.[Point];
Usuwanie funkcji UDT z bazy danych
Instrukcja DROP TYPE usuwa udT z bieżącej bazy danych. Po usunięciu funkcji UDT można użyć instrukcji DROP ASSEMBLY, aby usunąć zestaw z bazy danych.
Instrukcja DROP TYPE nie jest wykonywana w następujących sytuacjach:
Tabele w bazie danych zawierające kolumny zdefiniowane przy użyciu funkcji UDT.
Funkcje, procedury składowane lub wyzwalacze, które używają zmiennych lub parametrów udT utworzonych w bazie danych z klauzulą
WITH SCHEMABINDING.
Przykład
Następujące Transact-SQL muszą zostać wykonane w następującej kolejności. Najpierw należy porzucić tabelę odwołującą się do Point UDT, a następnie typ i na koniec zestaw.
DROP TABLE dbo.Points;
DROP TYPE dbo.Point;
DROP ASSEMBLY Point;
Znajdowanie zależności UDT
Jeśli istnieją obiekty zależne, takie jak tabele z definicjami kolumn UDT, instrukcja DROP TYPE kończy się niepowodzeniem. Nie powiedzie się również, jeśli istnieją funkcje, procedury składowane lub wyzwalacze utworzone w bazie danych przy użyciu klauzuli WITH SCHEMABINDING, jeśli te procedury używają zmiennych lub parametrów typu zdefiniowanego przez użytkownika. Najpierw należy usunąć wszystkie obiekty zależne, a następnie wykonać instrukcję DROP TYPE.
Poniższe zapytanie Transact-SQL lokalizuje wszystkie kolumny i parametry, które używają funkcji UDT w bazie danych AdventureWorks2025.
USE AdventureWorks2022;
SELECT o.name AS major_name,
o.type_desc AS major_type_desc,
c.name AS minor_name,
c.type_desc AS minor_type_desc,
at.assembly_class
FROM (SELECT object_id,
name,
user_type_id,
'SQL_COLUMN' AS type_desc
FROM sys.columns
UNION ALL
SELECT object_id,
name,
user_type_id,
'SQL_PROCEDURE_PARAMETER'
FROM sys.parameters) AS c
INNER JOIN sys.objects AS o
ON o.object_id = c.object_id
INNER JOIN sys.assembly_types AS at
ON at.user_type_id = c.user_type_id;
Obsługa tras zdefiniowanych przez użytkownika
Nie można zmodyfikować funkcji UDT po jej utworzeniu w bazie danych programu SQL Server, chociaż można zmienić zestaw, na którym jest oparty typ. W większości przypadków należy usunąć udT z bazy danych za pomocą instrukcji Transact-SQL DROP TYPE, wprowadzić zmiany w zestawie bazowym i ponownie załadować go przy użyciu instrukcji ALTER ASSEMBLY. Następnie należy ponownie utworzyć udT i wszystkie obiekty zależne.
Przykład
Instrukcja ALTER ASSEMBLY jest używana po wprowadzeniu zmian w kodzie źródłowym w zestawie UDT i ponownym skompilowania go. Kopiuje plik .dll do serwera i ponownie tworzy powiązanie z nowym zestawem. Pełną składnię można znaleźć w ALTER ASSEMBLY.
Poniższa instrukcja Transact-SQL ALTER ASSEMBLY ponownie ładuje zestaw Point.dll z określonej lokalizacji na dysku.
ALTER ASSEMBLY Point
FROM '\\Projects\Point\bin\Point.dll';
Dodawanie kodu źródłowego za pomocą alter assembly
Klauzula ADD FILE w składni ALTER ASSEMBLY nie jest obecna w CREATE ASSEMBLY. Można go użyć do dodawania kodu źródłowego lub innych plików skojarzonych z zestawem. Pliki są kopiowane z oryginalnych lokalizacji i przechowywane w tabelach systemowych w bazie danych. Gwarantuje to, że zawsze masz kod źródłowy lub inne pliki, jeśli kiedykolwiek musisz ponownie utworzyć lub udokumentować bieżącą wersję udT.
Poniższa instrukcja Transact-SQL ALTER ASSEMBLY dodaje kod źródłowy klasy Point.cs dla Point UDT. Spowoduje to skopiowanie tekstu zawartego w pliku Point.cs i zapisanie go w bazie danych pod nazwą PointSource.
ALTER ASSEMBLY Point
ADD FILE FROM '\\Projects\Point\Point.cs' AS PointSource;
Informacje o zestawie są przechowywane w tabeli sys.assembly_files w bazie danych, w której zainstalowano zestaw. Tabela sys.assembly_files zawiera następujące kolumny.
| Kolumna | Opis |
|---|---|
assembly_id |
Identyfikator zdefiniowany dla zestawu. Ta liczba jest przypisywana do wszystkich obiektów odnoszących się do tego samego zestawu. |
name |
Nazwa obiektu. |
file_id |
Liczba identyfikującą każdy obiekt z pierwszym obiektem skojarzonym z danym assembly_id, który otrzymuje wartość 1. Jeśli istnieje wiele obiektów skojarzonych z tym samym assembly_id, każda kolejna wartość file_id zwiększa się o 1. |
content |
Reprezentacja szesnastkowa zestawu lub pliku. |
Możesz użyć funkcji CAST lub CONVERT, aby przekonwertować zawartość kolumny content na czytelny tekst. Poniższe zapytanie konwertuje zawartość pliku Point.cs na czytelny tekst, używając nazwy w klauzuli WHERE w celu ograniczenia zestawu wyników do pojedynczego wiersza.
SELECT CAST (content AS VARCHAR (8000))
FROM sys.assembly_files
WHERE name = 'PointSource';
Jeśli skopiujesz i wklejesz wyniki do edytora tekstów, zobaczysz, że podziały wierszy i spacje, które istniały w oryginalnej wersji, zostaną zachowane.
Zarządzanie trasami zdefiniowanymi przez użytkownika i zestawami
Podczas planowania implementacji tras zdefiniowanych przez użytkownika należy wziąć pod uwagę, które metody są potrzebne w samym zestawie UDT i które metody powinny być tworzone w osobnych zestawach i implementowane jako funkcje zdefiniowane przez użytkownika lub procedury składowane. Rozdzielenie metod na oddzielne zestawy umożliwia aktualizowanie kodu bez wpływu na dane, które mogą być przechowywane w kolumnie UDT w tabeli. Zestawy UDT można modyfikować bez usuwania kolumn UDT i innych obiektów zależnych tylko wtedy, gdy nowa definicja może odczytywać poprzednie wartości, a podpis typu nie zmienia się.
Oddzielenie kodu proceduralnego, który może ulec zmianie z kodu wymaganego do zaimplementowania funkcji UDT znacznie upraszcza konserwację. Uwzględnienie tylko kodu, który jest niezbędny do działania przez funkcję UDT, i utrzymanie definicji UDT tak proste, jak to możliwe, zmniejsza ryzyko, że sam udT może być konieczne usunięcie z bazy danych poprawek kodu lub poprawek błędów.
Funkcja konwersji waluty UDT i waluty
Przykładowa baza danych Currency udT w AdventureWorks2025 udostępnia przydatny przykład zalecanego sposobu tworzenia struktury udT i skojarzonych z nią funkcji.
Currency UDT służy do obsługi pieniędzy w oparciu o system pieniężny konkretnej kultury i umożliwia przechowywanie różnych typów walut, takich jak dolary, euro i tak dalej. Klasa UDT uwidacznia nazwę kultury jako ciąg i ilość pieniędzy jako dziesiętny typ danych. Wszystkie niezbędne metody serializacji znajdują się w zestawie definiującym klasę. Funkcja, która implementuje konwersję walut z jednej kultury na inną, jest implementowana jako funkcja zewnętrzna o nazwie ConvertCurrency, a ta funkcja znajduje się w oddzielnym zestawie. Funkcja ConvertCurrency wykonuje swoją pracę, pobierając współczynnik konwersji z tabeli w bazie danych AdventureWorks2025. Jeśli źródło współczynników konwersji powinno kiedykolwiek ulec zmianie lub jeśli w istniejącym kodzie powinny wystąpić jakiekolwiek inne zmiany, zestaw można łatwo zmodyfikować bez wpływu na Currency UDT.
Listę kodu dla funkcji Currency UDT i ConvertCurrency można znaleźć, instalując przykłady środowiska uruchomieniowego języka wspólnego (CLR).
Korzystanie z tras zdefiniowanych przez użytkownika w bazach danych
Zdefiniowanie tras zdefiniowanych przez użytkownika jest ograniczone do pojedynczej bazy danych. W związku z tym funkcja UDT zdefiniowana w jednej bazie danych nie może być używana w definicji kolumny w innej bazie danych. Aby można było używać tras zdefiniowanych przez użytkownika w wielu bazach danych, należy wykonać instrukcje CREATE ASSEMBLY i CREATE TYPE w każdej bazie danych na identycznych zestawach. Zestawy są uznawane za identyczne, jeśli mają taką samą nazwę, silną nazwę, kulturę, wersję, zestaw uprawnień i zawartość binarną.
Po zarejestrowaniu i udostępnieniu funkcji UDT w obu bazach danych można przekonwertować wartość UDT z jednej bazy danych do użycia w innej. Identyczne trasy zdefiniowane przez użytkownika można używać w różnych bazach danych w następujących scenariuszach:
Wywoływanie procedury składowanej zdefiniowanej w różnych bazach danych.
Wykonywanie zapytań dotyczących tabel zdefiniowanych w różnych bazach danych.
Wybieranie danych UDT z jednej kolumny UDT tabeli bazy danych i wstawianie ich do drugiej bazy danych z identyczną kolumną UDT.
W takich sytuacjach każda konwersja wymagana przez serwer odbywa się automatycznie. Nie można jawnie wykonać konwersji przy użyciu funkcji Transact-SQL CAST lub CONVERT.
Nie musisz podejmować żadnych działań dotyczących używania tras zdefiniowanych przez użytkownika, gdy aparat bazy danych programu SQL Server tworzy tabele robocze w bazie danych systemu tempdb. Obejmuje to obsługę kursorów, zmiennych tabeli i funkcji zdefiniowanych przez użytkownika z wartościami tabel, które obejmują trasy zdefiniowane przez użytkownika i które w sposób przezroczysty korzystają z tempdb. Jeśli jednak jawnie utworzysz tabelę tymczasową w tempdb definiującą kolumnę UDT, funkcja UDT musi być zarejestrowana w tempdb tak samo jak w przypadku bazy danych użytkownika.