Zmienne języka Transact-SQL
Zmienna lokalna języka Transact-SQL jest obiektem, który może pomieścić pojedynczą wartość danych określonego typu.Zmienne w partiach i skryptach są zazwyczaj używane:
Jako licznik do policzenia, ile razy pętla jest wykonywana, lub do kontroli, ile razy pętla jest wykonywana.
Do przechowywania wartości danych do zbadania przez instrukcję sterowania przepływem.
Aby zapisać wartość danych zwracaną przez kod powrotny procedury składowanej lub zwracaną wartość funkcji.
Ostrzeżenie
Nazwy niektórych funkcji systemowych języka Transact-SQL zaczynają się dwoma znakami (@@).Chociaż w starszych wersjach programu Microsoft SQL Server funkcje @@ są nazywane zmiennymi globalnymi, nie są zmiennymi i nie mają tych samych zachowań jak zmienne.Funkcje @@ są funkcjami systemowymi, a użycie ich składni podlega regułom dla funkcji.
Poniższy skrypt tworzy małą tabelę testową i wypełnia ją 26 wierszami.Skrypt używa zmiennej, aby wykonać trzy czynności:
Kontrolować liczbę wstawianych wierszy przez kontrolowanie, ile razy pętla jest wykonywana.
Podać wartość wstawianą do kolumny wartości całkowitych.
Działać jako część wyrażenia, które generuje litery, które są wstawiane do kolumny znaków.
-- Create the table.
CREATE TABLE TestTable (cola int, colb char(3));
GO
SET NOCOUNT ON;
GO
-- Declare the variable to be used.
DECLARE @MyCounter int;
-- Initialize the variable.
SET @MyCounter = 0;
-- Test the variable to see if the loop is finished.
WHILE (@MyCounter < 26)
BEGIN;
-- Insert a row into the table.
INSERT INTO TestTable VALUES
-- Use the variable to provide the integer value
-- for cola. Also use it to generate a unique letter
-- for each row. Use the ASCII function to get the
-- integer value of 'a'. Add @MyCounter. Use CHAR to
-- convert the sum back to the character @MyCounter
-- characters after 'a'.
(@MyCounter,
CHAR( ( @MyCounter + ASCII('a') ) )
);
-- Increment the variable to count this iteration
-- of the loop.
SET @MyCounter = @MyCounter + 1;
END;
GO
SET NOCOUNT OFF;
GO
-- View the data.
SELECT cola, colb
FROM TestTable;
GO
DROP TABLE TestTable;
GO
Deklarowanie zmiennej języka Transact-SQL
Instrukcja DECLARE inicjuje zmienną języka Transact-SQL przez:
Przypisanie nazwy.Nazwa musi mieć pojedynczy @ jako pierwszy znak.
Przypisanie typu i długości danych dostarczanych przez system lub zdefiniowanych przez użytkownika.Dla zmiennych numerycznych są również przypisywane dokładność i skala.W przypadku zmiennych typu XML można opcjonalnie przypisać kolekcję schematów.
Ustawienie wartości na NULL.
Na przykład, następująca instrukcja DECLARE tworzy lokalną zmienną o nazwie @mycounter o typie danych int.
DECLARE @MyCounter int;
Aby zadeklarować więcej niż jedną zmienną lokalną, użyj przecinka po pierwszej zdefiniowanej zmiennej lokalnej, a następnie określ nazwę i typ danych następnej lokalnej zmiennej.
Na przykład, następująca instrukcja DECLARE tworzy trzy zmienne lokalne o nazwach @LastName, @FirstName i @StateProvince oraz inicjuje każdą z wartością NULL:
DECLARE @LastName nvarchar(30), @FirstName nvarchar(20), @StateProvince nchar(2);
Zakresem zmiennej jest zakres instrukcji języka Transact-SQL, które mogą odwoływać się do zmiennej.Zakres zmiennej sięga od punktu, w którym została zadeklarowana, do końca partii lub procedury składowanej, w której jest zadeklarowana.Na przykład poniższy skrypt generuje błąd składni, ponieważ zmienna jest zadeklarowana w jednej partii, a odniesienie do niej znajduje się w innej partii:
USE AdventureWorks2008R2;
GO
DECLARE @MyVariable int;
SET @MyVariable = 1;
-- Terminate the batch by using the GO keyword.
GO
-- @MyVariable has gone out of scope and no longer exists.
-- This SELECT statement generates a syntax error because it is
-- no longer legal to reference @MyVariable.
SELECT BusinessEntityID, NationalIDNumber, JobTitle
FROM HumanResources.Employee
WHERE BusinessEntityID = @MyVariable;
Zmienne mają zakres lokalny i są tylko widoczne w granicach partii lub procedury, gdzie są zdefiniowane.W następującym przykładzie zagnieżdżony zakres utworzony dla wykonywania procedury sp_executesql nie ma dostępu do zmiennej zadeklarowanej w wyższym zakresie i zwraca i błąd.
DECLARE @MyVariable int;
SET @MyVariable = 1;
EXECUTE sp_executesql N'SELECT @MyVariable'; -- this produces an error
Ustawianie wartości w zmiennej języka Transact-SQL
Gdy zmienna jest po raz pierwszy deklarowana, jej wartość jest ustawiana na NULL.Aby przypisać zmiennej wartość, użyj instrukcji SET.Jest to preferowana metoda przypisywania wartości do zmiennej.Zmienna może mieć także przypisaną wartość przez istnienie odwołania do niej na liście wyboru w instrukcji SELECT.
Aby przypisać zmiennej wartość za pomocą instrukcji SET, umieść w niej nazwę zmiennej i wartość, która ma zostać przypisana.Jest to preferowana metoda przypisywania wartości do zmiennej.Następująca partia, na przykład, deklaruje dwie zmienne, przypisuje wartości do nich, a następnie używa ich w klauzuli WHERE instrukcji SELECT:
USE AdventureWorks2008R2;
GO
-- Declare two variables.
DECLARE @FirstNameVariable nvarchar(50),
@PostalCodeVariable nvarchar(15);
-- Set their values.
SET @FirstNameVariable = N'Amy';
SET @PostalCodeVariable = N'BA5 3HX';
-- Use them in the WHERE clause of a SELECT statement.
SELECT LastName, FirstName, JobTitle, City, StateProvinceName, CountryRegionName
FROM HumanResources.vEmployee
WHERE FirstName = @FirstNameVariable
OR PostalCode = @PostalCodeVariable;
GO
Zmienna może mieć także przypisaną wartość przez istnienie odwołania do niej na liście wyboru.Jeśli odwołanie do zmiennej istnieje na liście wyboru, zmienna powinna mieć przypisaną wartość skalarną lub instrukcja SELECT powinna zwracać tylko jeden wiersz.Na przykład:
USE AdventureWorks2008R2;
GO
DECLARE @EmpIDVariable int;
SELECT @EmpIDVariable = MAX(EmployeeID)
FROM HumanResources.Employee;
GO
.gif "Przestroga") Przestroga |
|---|
Jeśli istnieje wiele klauzul przypisań w pojedynczej instrukcji SELECT, program SQL Server nie gwarantuje kolejności oceny wyrażeń.Należy zauważyć, że skutki są tylko widoczne, jeśli istnieją odwołania między przypisaniami. |
Jeśli instrukcja SELECT zwraca więcej niż jeden wiersz, a zmienna odwołuje się do wyrażenie nieskalarnego, zmienna jest ustawiana na wartość zwracaną dla wyrażenia w ostatnim wierszu zestawu wyników.Na przykład, w następującej partii zmienna @EmpIDVariable jest ustawiona na wartość BusinessEntityID ostatniego zwracanego wiersza, którą jest 1:
USE AdventureWorks2008R2;
GO
DECLARE @EmpIDVariable int;
SELECT @EmpIDVariable = BusinessEntityID
FROM HumanResources.Employee
ORDER BY BusinessEntityID DESC;
SELECT @EmpIDVariable;
GO