Transact-SQL

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

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

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

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

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

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

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

Комментарии

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

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

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

Типы данных

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

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

Примечание. Для использования русских символов (не ASCII кодировки) испольюзуются типы данных с приставкой "n" (nchar, nvarchar, ntext), которые кодируют сиволы двумя байтами. Иначе говоря, для работы с 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] воспринимается как одна переменная, так как слова заключены в квадратные скобки.

Операторы

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

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

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

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

• Агрегативные функции – функции, которые работают с коллекциями значений и выдают одно значение. Типичные представители: AVG - среднее значение колонки, SUM - сумма колонки, MAX - максимальное значение колонки, 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 Defenition 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)

Использованная литература

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