Transact-SQL (Transact-SQL)

Transact-SQL (T-SQL) — процедурное расширение языка SQL, созданное компанией Microsoft (для Microsoft SQL Server) и Sybase (для Sybase ASE).

SQL был расширен такими дополнительными возможностями как:

• управляющие операторы,
• локальные и глобальные переменные,
• различные дополнительные функции для обработки строк, дат, математики и т. п.,
• поддержка аутентификации Microsoft Windows.

Язык Transact-SQL является ключом к использованию MS SQL Server. Все приложения, взаимодействующие с экземпляром MS SQL Server, независимо от их реализации и пользовательского интерфейса, отправляют серверу инструкции Transact-SQL.

Элементы синтаксиса

Директивы сценария

Директивы сценария — это специфические команды, которые используются только в MS SQL. Эти команды помогают серверу определять правила работы со скриптом и транзакциями. Типичные представители: GO — информирует SQL Server об окончании пакета инструкций Transact-SQL, EXEC (или EXECUTE) — выполняет процедуру или скалярную функцию.

Комментарии

Комментарии используются для создания пояснений для блоков сценариев, а также для временного отключения команд при отладке скрипта. Комментарии бывают как строковыми, так и блоковыми:

-- — строковый комментарий исключает из выполнения только одну строку, перед которой стоят два минуса.

/* */ — блоковый комментарий исключает из выполнения целый блок команд, заключённый в указанную конструкцию. Допускает вложенные комментарии.

Типы данных

Как и в языках программирования, в SQL существуют различные типы данных для переменных:

• Числа — для хранения числовых переменных (bit, int, tinyint, smallint, bigint, numeric, decimal, money, smallmoney, float, real).
• Даты — для хранения даты и времени (datetime, smalldatetime, date, time, datetime2, datetimeoffset).
• Символы — для хранения символьных данных (char, nchar, varchar, nvarchar).
• Двоичные — для хранения бинарных данных (binary, varbinary, image).
• Большеобъемные — типы данных для хранения больших бинарных данных (text, ntext, image).
• Специальные — указатели (cursor), 16-байтовое шестнадцатеричное число, которое используется для GUID (uniqueidentifier), штамп изменения строки (timestamp), версия строки (rowversion), таблицы (table).

Примечание. Для использования русских символов (не ASCII кодировки) используются типы данных с приставкой «n» (nchar, nvarchar, ntext), которые кодируют символы двумя байтами. Иначе говоря, для работы с Unicode используются типы данных с «n» (от слова national). Строковые константы с Unicode также записываются с «n» в начале.

Примечание. Для данных переменной длины используются типы данных с приставкой «var». Типы данных без приставки «var» имеют фиксированную длину области памяти, неиспользованная часть которой заполняется пробелами или нулями.

Идентификаторы

Идентификаторы — это специальные символы, которые используются с переменными для идентифицирования их типа или для группировки слов в переменную. Типы идентификаторов:

• @ — идентификатор локальной переменной (пользовательской).
• @@ — идентификатор глобальной переменной (встроенной).
• # — идентификатор локальной таблицы или процедуры.
• ## — идентификатор глобальной таблицы или процедуры.
• [ ] — идентификатор группировки слов в переменную (работают как стандартные " ").

Переменные

Переменные используются в сценариях. Чтобы работать с переменной, её нужно объявить, при том объявление должно быть осуществлено в той транзакции или пакете инструкций, в которой выполняется команда, использующая эту переменную. Иначе говоря, после завершения транзакции, а также после команды GO, переменная уничтожается.

Объявление переменной выполняется командой DECLARE, задание значения переменной осуществляется либо командой SET, либо SELECT:

USE TestDatabase
-- Объявление переменных
DECLARE @EmpID int, @EmpName varchar(40)
-- Задание значения переменной @EmpID
SET @EmpID = 1
-- Задание значения переменной @EmpName
SELECT @EmpName = UserName FROM Users WHERE UserID = @EmpID
-- Вывод переменной @EmpName в результат запроса
SELECT @EmpName AS [Employee Name]
GO

Примечание. В этом примере используется группировка слов в лексему — конструкция [Employee Name] воспринимается как одна лексема, так как слова заключены в квадратные скобки.

Операторы

Операторы — это специальные команды, предназначенные для выполнения простых операций над переменными:

• Арифметические операторы: «*» — умножить, «/» — делить, «%» — остаток от деления, «+» — сложить, «-» — вычесть, «()» — скобки.
• Операторы сравнения: «=» — равно, «>» — больше, «<» — меньше, «>=» — больше или равно, «<=» - меньше или равно, «<>» («!=») — не равно, between (вместо «>=», «<=»).
• Операторы соединения: «+» — соединение (конкатенация) строк.
• Логические операторы: «AND» — и, «OR» — или, «NOT» — не.
• Операторы со множествами: «IN».

Системные функции

Спецификация Transact-SQL значительно расширяет стандартные возможности SQL благодаря встроенным функциям:

• Агрегативные функции — функции, которые работают с коллекциями значений и выдают одно значение. Типичные представители: AVG — среднее значение колонки, SUM — сумма колонки, MAX — максимальное значение колонки, MIN — минимальное значение колонки, COUNT — количество элементов колонки.
• Скалярные функции — это функции, которые возвращают одно значение, работая со скалярными данными или вообще без входных данных. Типичные представители: DATEDIFF — разница между датами, ABS — модуль числа, DB_NAME — имя базы данных, USER_NAME — имя текущего пользователя, LEFT — часть строки слева.
• Функции-указатели — функции, которые используются как ссылки на другие данные. Типичные представители: OPENXML — указатель на источник данных в виде XML-структуры, OPENQUERY — указатель на источник данных в виде другого запроса.

Примечание. Полный список функций можно найти в справке к SQL серверу.

Примечание. К скалярным функциям можно также отнести и глобальные переменные, которые в тексте сценария вызываются двойной собакой «@@».

Пример:

USE TestDatabase
-- Использование агрегативной функции для подсчета средней зарплаты
SELECT AVG(BaseSalary) AS [Average salary] FROM Positions
GO
-- Использование скалярной функции для получения имени базы данных
SELECT DB_NAME() AS [Database name]
GO
-- Использование скалярной функции для получения имени текущего пользователя
DECLARE @MyUser char(30)
SET @MyUser = USER_NAME()
SELECT 'The current user''s database username is: '+ @MyUser
GO
-- Использование функции-указателя для получения данных с другого сервера
SELECT * FROM OPENQUERY(OracleSvr, 'SELECT name, id FROM owner.titles')
GO

Выражения

Выражение — это комбинация операторов и символов, которая получает на вход скалярную величину, а на выходе дает другую величину или исполняет какое-то действие. В Transact-SQL выражения делятся на 3 типа: DDL, DCL и DML.

• DDL (Data Definition Language) — выражения этого типа используются для создания объектов в базе данных. Основные представители данного класса: CREATE — создание объектов, ALTER — изменение объектов, DROP — удаление объектов.
• DCL (Data Control Language) — выражения этого типа предназначены для назначения прав на объекты базы данных. Основные представители данного класса: GRANT — разрешение на объект, DENY — запрет на объект, REVOKE — отмена разрешений и запретов на объект.
• DML (Data Manipulation Language) — выражения этого типа используются для запросов и изменения данных. Основные представители данного класса: SELECT — выборка данных, INSERT — вставка данных, UPDATE — изменение данных, DELETE — удаление данных.

Пример:

USE TestDatabase
-- Использование DDL
CREATE TABLE TempUsers (UserID int, UserName nvarchar(40), DepartmentID int)
GO
-- Использование DCL
GRANT SELECT ON Users TO public
GO
-- Использование DML
SELECT UserID, UserName + ' ' + UserSurname AS [User Full Name] FROM Users
GO
-- Использование DDL
DROP TABLE TempUsers
GO

Управление выполнением сценария

В Transact-SQL существуют специальные команды, которые позволяют управлять потоком выполнения сценария, прерывая его или направляя в нужную ветвь.

• Блок группировки — структура, объединяющая список выражений в один логический блок (BEGIN … END).
• Блок условия — структура, проверяющая выполнения определённого условия (IF … ELSE).
• Блок цикла — структура, организующая повторение выполнения логического блока (WHILE … BREAK … CONTINUE).
• Переход — команда, выполняющая переход потока выполнения сценария на указанную метку (GOTO).
• Задержка — команда, задерживающая выполнение сценария (WAITFOR).
• Вызов ошибки — команда, генерирующая ошибку выполнения сценария (RAISERROR).

См. также

• PL/SQL

Литература

• Майк Гандерлой, Джозеф Джорден, Дейвид Чанц. Часть II. Язык программирования Transact-SQL // Освоение Microsoft SQL Server 2005 = Mastering Microsoft SQL Server 2005. — М.: «Диалектика», 2007. — С. 139-280. — ISBN 0-7821-4380-6.
• Роберт Виейра. Глава 3. Основные сведения о языке T-SQL // Программирование баз данных MS SQL Server 2005 для профессионалов = Professional Microsoft SQL Server 2005 Programming. — М.: «Диалектика», 2007. — С. 86-129. — 1072 с. — ISBN 978-5-8459-1329-6.
• Виталий Бочкарёв. Просто о Transact-SQL  (рус.) (25 января 2010). Архивировано 14 февраля 2012 года.
• Microsoft Corporation. Курс MS-2071 "Создание запросов в Microsoft SQL Server 2000 с использованием Transact-SQL" (англ.) (13 октября 2004). Архивировано 14 февраля 2012 года.