DEKLAROWANIE @local_variable (Transact-SQL)

Dotyczy:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics Platform System (PDW)Punkt końcowy analizy SQL w usłudze Microsoft FabricMagazyn w usłudze Microsoft FabricBaza danych SQL w usłudze Microsoft Fabric (wersja zapoznawcza)

Zmienne są deklarowane w treści partii lub procedury z instrukcją DECLARE i są przypisywane wartości przy użyciu instrukcji SET lub SELECT. Zmienne kursora można zadeklarować za pomocą tej instrukcji i używać z innymi instrukcjami powiązanymi z kursorem. Po deklaracji wszystkie zmienne są inicjowane jako NULL, chyba że wartość jest podana jako część deklaracji.

Transact-SQL konwencje składni

Syntax

Następująca składnia dotyczy programów SQL Server i Azure SQL Database:

DECLARE
{
  { @local_variable [AS] data_type [ = value ] }
  | { @cursor_variable_name CURSOR }
} [ ,...n ]
| { @table_variable_name [AS] <table_type_definition> }

<table_type_definition> ::=
    TABLE ( { <column_definition> | <table_constraint> | <table_index> } } [ ,...n ] )

<column_definition> ::=
    column_name { scalar_data_type | AS computed_column_expression }
    [ COLLATE collation_name ]
    [ [ DEFAULT constant_expression ] | IDENTITY [ (seed, increment ) ] ]
    [ ROWGUIDCOL ]
    [ <column_constraint> ]
    [ <column_index> ]

<column_constraint> ::=
{
    [ NULL | NOT NULL ]
    { PRIMARY KEY | UNIQUE }
      [ CLUSTERED | NONCLUSTERED ]
      [ WITH FILLFACTOR = fillfactor
        | WITH ( < index_option > [ ,...n ] )
      [ ON { filegroup | "default" } ]
  | [ CHECK ( logical_expression ) ] [ ,...n ]
}

<column_index> ::=
    INDEX index_name [ CLUSTERED | NONCLUSTERED ]
    [ WITH ( <index_option> [ ,... n ] ) ]
    [ ON { partition_scheme_name (column_name )
         | filegroup_name
         | default
         }
    ]
    [ FILESTREAM_ON { filestream_filegroup_name | partition_scheme_name | "NULL" } ]

<table_constraint> ::=
{
    { PRIMARY KEY | UNIQUE }
      [ CLUSTERED | NONCLUSTERED ]
      ( column_name [ ASC | DESC ] [ ,...n ]
        [ WITH FILLFACTOR = fillfactor
        | WITH ( <index_option> [ ,...n ] )
  | [ CHECK ( logical_expression ) ] [ ,...n ]
}

<table_index> ::=
{
    {
      INDEX index_name  [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ]
         (column_name [ ASC | DESC ] [ ,... n ] )
    | INDEX index_name CLUSTERED COLUMNSTORE
    | INDEX index_name [ NONCLUSTERED ] COLUMNSTORE ( column_name [ ,... n ] )
    }
    [ WITH ( <index_option> [ ,... n ] ) ]
    [ ON { partition_scheme_name ( column_name )
         | filegroup_name
         | default
         }
    ]
    [ FILESTREAM_ON { filestream_filegroup_name | partition_scheme_name | "NULL" } ]
}

<index_option> ::=
{
  PAD_INDEX = { ON | OFF }
  | FILLFACTOR = fillfactor
  | IGNORE_DUP_KEY = { ON | OFF }
  | STATISTICS_NORECOMPUTE = { ON | OFF }
  | STATISTICS_INCREMENTAL = { ON | OFF }
  | ALLOW_ROW_LOCKS = { ON | OFF }
  | ALLOW_PAGE_LOCKS = { ON | OFF }
  | OPTIMIZE_FOR_SEQUENTIAL_KEY = { ON | OFF }
  | COMPRESSION_DELAY = { 0 | delay [ Minutes ] }
  | DATA_COMPRESSION = { NONE | ROW | PAGE | COLUMNSTORE | COLUMNSTORE_ARCHIVE }
       [ ON PARTITIONS ( { partition_number_expression | <range> }
       [ , ...n ] ) ]
  | XML_COMPRESSION = { ON | OFF }
      [ ON PARTITIONS ( { <partition_number_expression> | <range> }
      [ , ...n ] ) ] ]
}

Następująca składnia dotyczy usług Azure Synapse Analytics i Parallel Data Warehouse i Microsoft Fabric:

DECLARE
{ { @local_variable [AS] data_type } [ = value [ COLLATE <collation_name> ] ] } [ ,...n ]

Arguments

@ local_variable

Nazwa zmiennej. Nazwy zmiennych muszą zaczynać się od znaku at (@). Nazwy zmiennych lokalnych muszą być zgodne z regułami dotyczącymi identyfikatorów.

data_type
Dowolny typ tabeli zdefiniowany przez użytkownika w środowisku uruchomieniowym języka wspólnego (CLR) dostarczany przez system lub typ danych aliasu. Zmienna nie może być typu danych tekstowych, ntekstowych ani obrazów .

Aby uzyskać więcej informacji na temat typów danych systemowych, zobacz Typy danych (Transact-SQL). Aby uzyskać więcej informacji o typach zdefiniowanych przez użytkownika lub typach danych aliasu CLR, zobacz CREATE TYPE (Transact-SQL).

= value
Przypisuje wartość do zmiennej w wierszu. Wartość może być stałą lub wyrażeniem, ale musi być zgodna z typem deklaracji zmiennej lub niejawnie konwertowana na ten typ. Aby uzyskać więcej informacji, zobacz Expressions (Transact-SQL).

@ cursor_variable_name

Nazwa zmiennej kursora. Nazwy zmiennych kursora muszą zaczynać się od znaku (@) i być zgodne z regułami dotyczącymi identyfikatorów.

CURSOR
Określa, że zmienna jest lokalną zmienną kursora.

@ table_variable_name
Nazwa zmiennej typu tabela. Nazwy zmiennych muszą zaczynać się od znaku (@) i być zgodne z regułami dla identyfikatorów.

<table_type_definition>
Definiuje typ danych tabeli . Deklaracja tabeli zawiera definicje kolumn, nazwy, typy danych i ograniczenia. Jedynymi dozwolonymi typami ograniczeń są KLUCZ PODSTAWOWY, UNIKATOWY, NULL i CHECK. Typ danych aliasu nie może być używany jako typ danych skalarnych kolumn, jeśli reguła lub definicja domyślna jest powiązana z typem.

<table_type_definition>

Podzbiór informacji używany do definiowania tabeli w tabeli CREATE TABLE. Elementy i podstawowe definicje znajdują się tutaj. Aby uzyskać więcej informacji, zobacz CREATE TABLE (Transact-SQL).

n
Symbol zastępczy wskazujący, że można określić i przypisać wartości wielu zmiennych. Podczas deklarowania zmiennych tabeli zmienna tabeli musi być jedyną zmienną zadeklarowaną w instrukcji DECLARE.

column_name

Nazwa kolumny w tabeli.

scalar_data_type
Określa, że kolumna jest typem danych skalarnych.

computed_column_expression
Wyrażenie definiujące wartość obliczonej kolumny. Jest on obliczany na podstawie wyrażenia przy użyciu innych kolumn w tej samej tabeli. Na przykład kolumna obliczona może mieć wartość kosztu AS definicji * ilość. Wyrażenie może być niekompilowaną nazwą kolumny, stałą, wbudowaną funkcją, zmienną lub dowolną kombinacją tych połączonych przez co najmniej jeden operator. Wyrażenie nie może być podzapytaniem ani funkcją zdefiniowaną przez użytkownika. Wyrażenie nie może odwoływać się do typu zdefiniowanego przez użytkownika CLR.

[ COLLATE collation_name ]

Określa sortowanie dla kolumny. collation_name może być nazwą sortowania systemu Windows lub nazwą sortowania SQL i ma zastosowanie tylko dla kolumn char, varchar, text, nchar, nvarchar i ntext danych. Jeśli nie zostanie określony, kolumna zostanie przypisana do sortowania typu danych zdefiniowanego przez użytkownika (jeśli kolumna jest typu danych zdefiniowanym przez użytkownika) lub sortowania bieżącej bazy danych.

Aby uzyskać więcej informacji na temat nazw sortowania systemu Windows i SQL, zobacz COLLATE (Transact-SQL).

DEFAULT

Określa wartość podaną dla kolumny, gdy wartość nie jest jawnie podana podczas wstawiania. Definicje DOMYŚLNE można stosować do dowolnych kolumn, z wyjątkiem tych zdefiniowanych jako znacznik czasu lub tych z właściwością IDENTITY. Definicje DOMYŚLNE są usuwane, gdy tabela zostanie porzucona. Tylko stała wartość, taka jak ciąg znaków; funkcja systemowa, taka jak SYSTEM_USER(); wartość null może być używana jako wartość domyślna. Aby zachować zgodność z wcześniejszymi wersjami programu SQL Server, do domyślnej nazwy ograniczenia można przypisać nazwę ograniczenia.

constant_expression
Stała, NULL lub funkcja systemowa używana jako wartość domyślna kolumny.

IDENTITY

Wskazuje, że nowa kolumna jest kolumną tożsamości. Po dodaniu nowego wiersza do tabeli program SQL Server udostępnia unikatową wartość przyrostową dla kolumny. Kolumny tożsamości są często używane z ograniczeniami KLUCZA PODSTAWOWEgo, aby służyć jako unikatowy identyfikator wiersza dla tabeli. Właściwość IDENTITY można przypisać do kolumn tinyint, smallint, int, decimal(p,0) lub numerycznych(p,0). Dla tabeli można utworzyć tylko jedną kolumnę tożsamości. Nie można używać powiązanych wartości domyślnych i ograniczeń DOMYŚLNYch z kolumną tożsamości. Należy określić zarówno nasion, jak i przyrost, lub nie. Jeśli żadna z nich nie zostanie określona, wartość domyślna to (1,1).

seed
Wartość użyta dla pierwszego wiersza załadowanego do tabeli.

increment
Wartość przyrostowa dodana do wartości tożsamości poprzedniego wiersza, który został załadowany.

ROWGUIDCOL

Wskazuje, że nowa kolumna jest globalną kolumną unikatowego identyfikatora wiersza. Jako kolumnę ROWGUIDCOL można wyznaczyć tylko jedną unikatową kolumnę na tabelę. Właściwość ROWGUIDCOL można przypisać tylko do kolumny uniqueidentifier .

NULL | NIE NULL

Wskazuje, czy wartość null jest dozwolona w zmiennej. Wartość domyślna to NULL.

PRIMARY KEY

Ograniczenie wymuszające integralność jednostki dla danej kolumny lub kolumn za pośrednictwem unikatowego indeksu. Dla tabeli można utworzyć tylko jedno ograniczenie klucza podstawowego.

UNIQUE

Ograniczenie zapewniające integralność jednostki dla danej kolumny lub kolumn za pośrednictwem unikatowego indeksu. Tabela może mieć wiele unikatowych ograniczeń.

KLASTROWANE | NIEKLASTROWANE

Wskazuje, że indeks klastrowany lub nieklastrowany jest tworzony dla klucza PODSTAWOWEgo lub unikatowego ograniczenia. Ograniczenia KLUCZA PODSTAWOWEgo korzystają z klastra, a OGRANICZENIA UNIKATOWE używają WARTOŚCI NONCLUSTERED.

KLASTERED można określić tylko dla jednego ograniczenia. Jeśli określono parametr CLUSTERED dla ograniczenia UNIKATOWEGO, a ograniczenie KLUCZA PODSTAWOWEgo jest również określone, KLUCZ PODSTAWOWY używa WARTOŚCI NONCLUSTERED.

CHECK

Ograniczenie wymuszające integralność domeny przez ograniczenie możliwych wartości, które można wprowadzić w kolumnie lub kolumnach.

logical_expression
Wyrażenie logiczne zwracające wartość TRUE lub FALSE.

<index_option>

Określa co najmniej jedną opcję indeksu. Nie można jawnie tworzyć indeksów w zmiennych tabeli, a żadne statystyki nie są przechowywane w zmiennych tabeli. Począwszy od programu SQL SQL Server 2014 (12.x), wprowadzono nową składnię, która umożliwia tworzenie określonych typów indeksów w tekście przy użyciu definicji tabeli. Korzystając z tej nowej składni, można utworzyć indeksy zmiennych tabeli w ramach definicji tabeli. W niektórych przypadkach wydajność może poprawić się przy użyciu tabel tymczasowych, które zapewniają pełną obsługę indeksów i statystyki.

Pełny opis tych opcji można znaleźć w temacie CREATE TABLE (TWORZENIE TABELI).

Zmienne tabeli i szacunki wierszy

Zmienne tabeli nie mają statystyk dystrybucji. W wielu przypadkach optymalizator utworzy plan zapytania przy założeniu, że zmienna tabeli ma zero wierszy lub jeden wiersz. Aby uzyskać więcej informacji, przejrzyj typ danych tabeli — ograniczenia i ograniczenia.

Z tego powodu należy zachować ostrożność przy użyciu zmiennej tabeli, jeśli spodziewasz się większej liczby wierszy (więcej niż 100). Rozważ następujące alternatywy:

• Tabele tymczasowe mogą być lepszym rozwiązaniem niż zmienne tabeli, gdy istnieje możliwość, aby wiersz był większy (większy niż 100).
• W przypadku zapytań, które łączą zmienną tabeli z innymi tabelami, użyj wskazówki RECOMPILE, co spowoduje, że optymalizator będzie używać poprawnej kardynalności dla zmiennej tabeli.
• W usłudze Azure SQL Database i począwszy od programu SQL Server 2019 (15.x) funkcja kompilacji odroczonej zmiennej tabeli będzie propagować oszacowania kardynalności oparte na rzeczywistych liczbach wierszy zmiennych tabeli, zapewniając dokładniejszą liczbę wierszy do optymalizacji planu wykonania. Aby uzyskać więcej informacji, zobacz Inteligentne przetwarzanie zapytań w bazach danych SQL.

Remarks

Zmienne są często używane w partii lub procedurze jako liczniki while, LOOP lub IF... Blok ELSE.

Zmienne mogą być używane tylko w wyrażeniach, a nie zamiast nazw obiektów lub słów kluczowych. Aby utworzyć dynamiczne instrukcje SQL, użyj polecenia EXECUTE.

Zakres zmiennej lokalnej to partia, w której jest zadeklarowana.

Zmienna tabeli nie musi być rezydentem pamięci. Pod ciśnieniem pamięci strony należące do zmiennej tabeli można wypchnąć do tempdb.

Indeks wbudowany można zdefiniować w zmiennej tabeli.

Zmienna kursora, która obecnie ma przypisany kursor, można odwoływać się jako źródło w:

• CLOSE statement
• DEALLOCATE statement
• FETCH statement
• OPEN statement
• Instrukcja POSITIONED DELETE lub UPDATE
• SET CURSOR, instrukcja zmiennej (po prawej stronie)

We wszystkich tych instrukcjach program SQL Server zgłasza błąd, jeśli istnieje zmienna kursora, do których występuje odwołanie, ale nie ma aktualnie przydzielonego kursora. Jeśli przywoływany zmienna kursora nie istnieje, program SQL Server zgłasza ten sam błąd zgłoszony dla zmiennej niezadeklarowanej innego typu.

Zmienna kursora:

• Może być obiektem docelowym typu kursora lub innej zmiennej kursora. Aby uzyskać więcej informacji, zobacz SET @local_variable (Transact-SQL).

• Można odwoływać się jako element docelowy parametru kursora wyjściowego w instrukcji EXECUTE, jeśli zmienna kursora nie ma aktualnie przypisanego kursora.

• Należy traktować jako wskaźnik do kursora.

Examples

A. Using DECLARE

W poniższym przykładzie użyto zmiennej lokalnej o nazwie @find , aby pobrać informacje kontaktowe dla wszystkich nazwisk rozpoczynających się od Man.

USE AdventureWorks2022;
GO
DECLARE @find VARCHAR(30);
/* Also allowed:
DECLARE @find VARCHAR(30) = 'Man%';
*/
SET @find = 'Man%';
SELECT p.LastName, p.FirstName, ph.PhoneNumber
FROM Person.Person AS p
JOIN Person.PersonPhone AS ph ON p.BusinessEntityID = ph.BusinessEntityID
WHERE LastName LIKE @find;

Oto zestaw wyników.

LastName            FirstName               Phone
------------------- ----------------------- -------------------------
Manchepalli         Ajay                    1 (11) 500 555-0174
Manek               Parul                   1 (11) 500 555-0146
Manzanares          Tomas                   1 (11) 500 555-0178

(3 row(s) affected)

B. Używanie funkcji DECLARE z dwiema zmiennymi

Poniższy przykład pobiera nazwy przedstawicieli sprzedaży Adventure Works Cycles, którzy znajdują się na terytorium sprzedaży w Ameryce Północnej i mają co najmniej 2000 000 USD sprzedaży w ciągu roku.

USE AdventureWorks2022;
GO
SET NOCOUNT ON;
GO
DECLARE @Group nvarchar(50), @Sales MONEY;
SET @Group = N'North America';
SET @Sales = 2000000;
SET NOCOUNT OFF;
SELECT FirstName, LastName, SalesYTD
FROM Sales.vSalesPerson
WHERE TerritoryGroup = @Group and SalesYTD >= @Sales;

C. Deklarowanie zmiennej typu tabeli

Poniższy przykład tworzy zmienną table , która przechowuje wartości określone w klauzuli OUTPUT instrukcji UPDATE. Dwa SELECT instrukcje są zgodne z instrukcjami, które zwracają wartości w @MyTableVar tabeli i wyniki operacji aktualizacji.Employee Wyniki w INSERTED.ModifiedDate kolumnie różnią się od wartości w ModifiedDate kolumnie Employee w tabeli. Dzieje się tak, ponieważ AFTER UPDATE wyzwalacz, który aktualizuje wartość ModifiedDate bieżącej daty, jest zdefiniowany w Employee tabeli. Jednak kolumny zwrócone z OUTPUT odzwierciedlają dane przed wyzwoleniem wyzwalaczy. Aby uzyskać więcej informacji, zobacz OUTPUT Clause (Transact-SQL).

USE AdventureWorks2022;
GO
DECLARE @MyTableVar TABLE (
    EmpID INT NOT NULL,
    OldVacationHours INT,
    NewVacationHours INT,
    ModifiedDate DATETIME);
UPDATE TOP (10) HumanResources.Employee
SET VacationHours = VacationHours * 1.25
OUTPUT INSERTED.BusinessEntityID,
       DELETED.VacationHours,
       INSERTED.VacationHours,
       INSERTED.ModifiedDate
INTO @MyTableVar;
--Display the result set of the table variable.
SELECT EmpID, OldVacationHours, NewVacationHours, ModifiedDate
FROM @MyTableVar;
GO
--Display the result set of the table.
--Note that ModifiedDate reflects the value generated by an
--AFTER UPDATE trigger.
SELECT TOP (10) BusinessEntityID, VacationHours, ModifiedDate
FROM HumanResources.Employee;
GO

D. Deklarowanie zmiennej typu tabeli z wbudowanymi indeksami

W poniższym przykładzie tworzona jest zmienna table z indeksem wbudowanym klastra i dwoma indeksami wbudowanymi, które nie są klasterowane.

DECLARE @MyTableVar TABLE (
    EmpID INT NOT NULL,
    PRIMARY KEY CLUSTERED (EmpID),
    UNIQUE NONCLUSTERED (EmpID),
    INDEX CustomNonClusteredIndex NONCLUSTERED (EmpID)
);
GO

Poniższe zapytanie zwraca informacje o indeksach utworzonych w poprzednim zapytaniu.

SELECT *
FROM tempdb.sys.indexes
WHERE object_id < 0;
GO

E. Deklarowanie zmiennej typu tabeli zdefiniowanej przez użytkownika

W poniższym przykładzie jest tworzony parametr wartości tabeli lub zmienna tabeli o nazwie @LocationTVP. Wymaga to odpowiedniego typu tabeli zdefiniowanej przez użytkownika o nazwie LocationTableType. Aby uzyskać więcej informacji na temat tworzenia typu tabeli zdefiniowanej przez użytkownika, zobacz CREATE TYPE (Transact-SQL). Aby uzyskać więcej informacji na temat parametrów wartości tabeli, zobacz Używanie parametrów Table-Valued (aparat bazy danych).

DECLARE @LocationTVP
AS LocationTableType;

Przykłady: Azure Synapse Analytics and Analytics Platform System (PDW)

F. Using DECLARE

W poniższym przykładzie użyto zmiennej lokalnej o nazwie @find , aby pobrać informacje kontaktowe dla wszystkich nazwisk rozpoczynających się od Walt.

-- Uses AdventureWorks

DECLARE @find VARCHAR(30);
/* Also allowed:
DECLARE @find VARCHAR(30) = 'Man%';
*/
SET @find = 'Walt%';

SELECT LastName, FirstName, Phone
FROM DimEmployee
WHERE LastName LIKE @find;

G. Używanie funkcji DECLARE z dwiema zmiennymi

Poniższy przykład pobiera zmienne, aby określić imię i nazwisko pracowników w DimEmployee tabeli.

-- Uses AdventureWorks

DECLARE @lastName VARCHAR(30), @firstName VARCHAR(30);

SET @lastName = 'Walt%';
SET @firstName = 'Bryan';

SELECT LastName, FirstName, Phone
FROM DimEmployee
WHERE LastName LIKE @lastName AND FirstName LIKE @firstName;

See also

• EXECUTE (Transact-SQL)
• Wbudowane funkcje (Transact-SQL)
• SELECT (Transact-SQL)
• table (Transact-SQL)
• Porównaj typizowane XML z nietypizowanym XML