Программирование на T - SQL
Синтаксис и соглашения T-SQL
Правила формирования идентификаторов
Все объекты в SQL Server имеют имена (идентификаторы). Примерами объектов являются таблицы, представления, хранимые процедуры и т.д. Идентификаторы могут включать до 128 символов, в частности, буквы, символы _ @ $ # и цифры.
Первый символ всегда должен быть буквенным. Для переменных и временных таблиц используются специальные схемы именования. Имя объекта не может содержать пробелов и совпадать с зарезервированным ключевым словом SQL Server, независимо от используемого регистра символов. Путем заключения идентификаторов в квадратные скобки, в именах объектов можно использовать запрещенные символы.
Завершение инструкции
Стандарт ANSI SQL требует помещения в конце каждой инструкции точки с запятой. В то же время при программировании на языке T-SQL точка с запятой не обязательна.
Комментарии
Язык T-SQL допускает использование комментариев двух стилей: ANCI и языка С. Первый из них начинается с двух дефисов и заканчивается в конце строки:
Это однострочный комментарий стиля ANSI
Также комментарии стиля ANSI могут вставляться в конце строки инструкции:
SELECT CityName – извлекаемые столбцы
FROM City – исходная таблица
WHERE IdCity = 1; -- ограничение на строки
Редактор SQL может применять и удалять комментарии во всех выделенных строках. Для этого нужно выбрать соответствующие команды в меню Правка или на панели инструментов.
Комментарии стиля языка С начинаются с косой черты и звездочки (/*) и заканчиваются теми же символами в обратной последовательности. Этот тип комментариев лучше использовать для комментирования блоков строк, таких как заголовки или большие тестовые запросы.
многострочного
комментария
Одним из главных достоинств комментариев стиля С является то, что многострочные запросы в них можно выполнять, даже не раскомментируя.
Пакеты T-SQL
Запросом называют одну инструкцию T-SQL, а пакетом - их набор. Вся последовательность инструкций пакета отправляется серверу из клиентских приложений как одна цельная единица.
SQL Server рассматривает весь пакет как рабочую единицу. Наличие ошибки хотя бы в одной инструкции приведет к невозможности выполнения всего пакета. В то же время грамматический разбор не проверяет имена объектов и схем, так как сама схема может измениться в процессе выполнения инструкции.
Файл сценария SQL и окно анализатора запросов (Query Analyzer) может содержать несколько пакетов. В данном случае все пакеты разделяют ключевые слова терминаторов. По умолчанию этим ключевым словом является GO, и оно должно быть единственным в строке. Все другие символы (даже комментарии) нейтрализуют разделитель пакета.
Отладка T-SQL
Когда редактор SQL обнаруживает ошибку, он отображает ее характер и номер строки в пакете. Дважды щелкнув на ошибке, можно сразу же переместиться к соответствующей строке.
В утилиту Management Studio версии SQL Server 2005 не включен отладчик языка T-SQL, - он присутствует в пакете Visual Studio.
SQL Server предлагает несколько команд, облегчающих отладку пакетов. В частности, команда PRINT отправляет сообщение без генерации результирующего набора данных. Команду PRINT можно использовать для отслеживания хода выполнения пакета. Когда анализатор запросов находится в режиме сетки, выполните следующий пакет:
SELECT CityName
FROM City
WHERE IdCity = 1;
PRINT "Контрольная точка" ;
Результирующий набор данных отобразится в сетке и будет состоять из одной строки. В то же время во вкладке «Сообщения» отобразится следующий результат:
(строк обработано: 1)
Контрольная точка
Переменные
Переменные T-SQL создаются с помощью команды DECLARE, имеющей следующий синтаксис:
DECLARE @Имя_Переменной Тип_Данных [,
@Имя_Переменной Тип_Данных, …]
Все имена локальных переменных должны начинаться символом @. Например, для объявления локальной переменной UStr, которая хранит до 16 символов Unicode, можно использовать следующую инструкцию:
DECLARE @UStr varchar (16)
Используемые для переменных типы данных в точности совпадают с существующими в таблицах. В одной команде DECLARE через запятую может быть перечислено несколько переменных. В частности в следующем примере создаются две целочисленные переменные a и b:
DECLARE
@a int ,
@b int
Область определения переменных (т.е. срок их жизни) распространяется только на текущий пакет. По умолчанию только что созданные переменные содержат пустые значения NULL и до включения в выражения должны быть инициализированы.
Задание значений переменных
В настоящее время в языке SQL предусмотрены два способа задания значения переменной - для этой цели можно использовать оператор SELECT или SET. С точки зрения выполняемых функций эти операторы действуют почти одинаково, не считая того, что оператор SELECT позволяет получить исходное присваиваемое значение из таблицы, указанной в операторе SELECT.
Оператор SET обычно используется для задания значений переменных в такой форме, какая более часто встречается в процедурных языках. В качестве типичных примеров применения этого оператора можно указать следующие:
SET @a = 1;
SET @b = @a * 1.5
Обратите внимание на то, что во всех этих операторах непосредственно осуществляются операции присваивания, в которых используются либо явно заданные значения, либо другие переменные. С помощью оператора SET невозможно присвоить переменной значение, полученное с помощью запроса; запрос должен быть выполнен отдельно и только после этого полученный результат может быть присвоен с помощью оператора SET. Например, попытка выполнения такого оператора вызывает ошибку:
DECLARE @c int
SET @c = COUNT (*) FROM City
SELECT @c
а следующий оператор выполняется вполне успешно:
DECLARE @c int
SET @c = (SELECT COUNT (*) FROM City)
SELECT @c
Оператор SELECT обычно используется для присваивания значений переменным, если источником информации, которая должна быть сохранена в переменной, является запрос. Например, действия, осуществляемые в приведенном выше коде, гораздо чаще реализуются с помощью оператора SELECT:
DECLARE @c int
SELECT @c = COUNT (*) FROM City
SELECT @c
Обратите внимание на то, что данный код немного понятнее (в частности, он более лаконичен, хотя и выполняет те же действия).
Таким образом, можно, сформулировать следующее общепринятое соглашение по использованию того и другого оператора.
Оператор SET используется, если должна быть выполнена простая операция присваивания значения переменной, т.е. если присваиваемое значение уже задано явно в форме определенного значения или в виде какой-то другой переменной.
Использование переменных в запросах SQL
Одним из полезных свойств языка T-SQL является то, что переменные могут использоваться в запросах без необходимости создания сложных динамических строк, встраивающих переменные в программный код. Динамический SQL продолжает свое существование, но одиночное значение можно изменить проще - с помощью переменной.
Везде, где в запросе может использоваться выражение, может использоваться и переменная. В следующем примере продемонстрировано использование переменной в предложении WHERE:
DECLARE @IdProd int ;
SET @IdProd = 1;
SELECT
FROM Product
WHERE IdProd = @IdProd;
Глобальные системные переменные
В SQL Server имеется более тридцати глобальных переменных, не имеющих параметров, которые определяются и поддерживаются системой. Все глобальные переменные имеют префикс в виде двух символов @. Вы можете извлечь значение любой из них с помощью простого запроса SELECT, как в следующем примере:
SELECT @@CONNECTIONS
Здесь используется глобальная переменная @@CONNECTIONS для извлечения количества подключений к SQL Server со времени запуска программы.
Среди наиболее часто применяемых системных переменных можно отметить следующие:
! Следует отметить, что с версии SQL Server 2000 глобальные переменные принято называть функциями. Название глобальные сбивало пользователей с толку, позволяя думать, что область действия таких переменных шире, чем у локальных. Глобальным переменным часто ошибочно приписывалась возможность хранить информацию, независимо от того, включена она в пакет либо нет, что, естественно, не соответствовало действительности.
Средства управления потоком команд. Программные конструкции
В языке T-SQL предусмотрена большая часть классических процедурных средств управления ходом выполнения программы, в т.ч. условная конструкция и циклы.
Оператор IF. . . ELSE
Операторы IF. . .ELSE действуют в языке T-SQL в основном так же, как и в любых других языках программирования. Общий синтаксис этого оператора имеет следующий вид:
IF Логическое выражение
SQL инструкция I BEGIN Блок SQL инструкций END
SQL инструкция | BEGIN Блок SQL инструкций END]
В качестве логического выражения может быть задано практически любое выражение, результат вычисления которого приводит к возврату булева значения.
Следует учитывать, что выполняемым по условию считается только тот оператор, который непосредственно следует за оператором IF (ближайшим к нему). Вместо одного оператора можно предусмотреть выполнение по условию нескольких операторов, объединив их в блок кода с помощью конструкции BEGIN…END.
В приведенном ниже примере условие IF не выполняется, что предотвращает выполнение следующего за ним оператора.
IF 1 = 0
PRINT "Первая строка"
PRINT "Вторая строка"
Необязательная команда ELSE позволяет задать инструкцию, которая будет выполнена в случае, если условие IF не будет выполнено. Подобно IF, оператор ELSE управляет только непосредственно следующей за ним командой или блоком кода заключенным между BEGIN…END.
Несмотря на то, что оператор IF выглядит ограниченным, его предложение условия может включать в себя мощные функции, подобно предложению WHERE. В частности это выражения IF EXISTS().
Выражение IF EXISTS() использует в качестве условия наличие какой-либо строки, возвращенной инструкцией SELECT. Так как ищутся любые строки, список столбцов в инструкции SELECT можно заменить звездочкой. Этот метод работает быстрее, чем проверка условия @@ROWCOUNT>0, потому что не требуется подсчет общего количества строк. Как только хотя бы одна строка удовлетворяет условию IF EXISTS(), запрос может продолжать выполнение.
В следующем примере выражение IF EXISTS используется для проверки наличия у клиента с кодом 1 каких-либо заказов перед удалением его из базы. Если по данному клиенту есть информация хотя бы по одному заказу, удаление не производится.
IF EXISTS (SELECT * FROM WHERE IdCust = 1)
PRINT "Невозможно удалить клиента поскольку в базе имеются связанные с ним записи"
ELSE
WHERE IdCust = 1
PRINT "Удаление произведено успешно"
Операторы WHILE, BREAK и CONTINUE
Оператор WHILE в языке SQL действует во многом так же, как и в других языках, с которыми обычно приходится работать программисту. По сути, в этом операторе до начала каждого прохода по циклу проверяется некоторое условие. Если перед очередным проходом по циклу проверка условия приводит к получению значения TRUE, осуществляется проход по циклу, в противном случае выполнение оператора завершается.
Оператор WHILE имеет следующий синтаксис:
WHILE Логическое выражение
SQL инструкция I
Блок SQL инструкций
Безусловно, с помощью оператора WHILE можно обеспечить выполнение в цикле только одного оператора (по аналогии с тем, как обычно используется оператор IF), но на практике конструкции WHILE, за которыми не следует блок BEGIN. . .END, соответствующий полному формату оператора, встречаются редко.
Оператор BREAK позволяет немедленно выйти из цикла, не ожидая того, как будет выполнен проход до конца цикла и произойдет повторная проверка условного выражения.
Оператор CONTINUE позволяет прервать отдельную итерацию цикла. Кратко можно описать действие оператора CONTINUE так, что он обеспечивает переход в начало цикла WHILE. Сразу после обнаружения оператора CONTINUE в цикле, независимо от того, где он находится, происходит переход в начало цикла и повторное вычисление условного выражения (а если значение этого выражения больше не равно TRUE, осуществляется выход из цикла).
Следующий короткий сценарий демонстрирует использование оператора WHILE для создания цикла:
DECLARE @Temp int ;
SET @Temp = 0;
WHILE @Temp < 3
BEGIN
PRINT @Temp;
SET @Temp = @Temp + 1;
Здесь в цикле целочисленная переменная @Temp увеличивается с 0 до 3 и на каждой итерации ее значение выводится на экран.
Оператор RETURN
Оператор RETURN используется для останова выполнения пакета, а следовательно, хранимой процедуры и триггера (рассматриваются в следующих лабораторных занятиях).
Наверное, одним из первых вопросов, возникающих у начинающих программистов на T-SQL, это вопрос "А как получить выборку из таблицы, имя которой занесено в переменную?"
Т.к. в T-SQL нет возможности использовать в некоторых частях запроса значения переменных, то единственным доступным решением является использование динамического запроса. Идея очень проста: в специально определнной переменной "собирается" строка запроса, которая должна выполняться. Далее эта строка запускается на выполнение. Запуск можно осуществить двумя способами
- с помощью команды EXECUTE
- с помощью системной процедуры sp_executesql.
Выглядит это приблизительно так
I.Особенности динамического запроса
1. Динамический запрос ВСЕГДА выполняется В ТОМ-ЖЕ КОННЕКТЕ и КАК ОТДЕЛЬНЫЙ ПАКЕТ(batch). Другими словами при использовании такого запроса,
- вы ни имеете доступа к локальным переменным, объявленым до вызова динамического запроса (однако возможен доступ к cозданным ранее временным таблицам)
- локальные временые таблицы и переменные, созданные во время выполнения команды exec, будут недоступны в вызывающей процедуре, т.к. будут удалены по окончании пакета, в котором выполняется exec.
2. Динамический запрос ВСЕГДА выполняется с ПРАВАМИ ПОЛЬЗОВАТЕЛЯ, ВЫЗВАВШЕГО ПРОЦЕДУРУ, а не с правами владельца процедуры. Другими словами, если владельцем процедуры Procedure1 является User1, который имеет права к таблице Table1, то для пользователя User2 мало назначить права на выполнение процедуры Procedure1, если обращение в ней к таблице Table1 идет через динамический запрос. Придется давать ему соответствующие права и непосредственно для Table1.
3. Компиляция запроса происходят непосредственно перед его вызовом. Т.е. обо всех синтаксических ошибках вы узнаете только в этот момент.
II.Особенности использования команда exec
1. Команда exec поддерживает к качестве аргумента конкатенацю строк и/или переменных. НО не поддерживатеся конкатенация результатов выполнения функций, т.е. конструкции вида
exec
("SELECT * FROM "
+ LEFT
(@TableName, 10
))
запрещены к использованию.
2. В команде нет входных/выходных параметров.
III.Особенности использования процедуры sp_executesql
1. Процедура НЕ поддерживает в качестве параметров конкатенацию строк и/или переменных.
2. Текст запроса должен быть либо переменной типа NVARCHAR/NCHAR, либо такого же типа стринговой константой.
3. Имеется возможность передачи параметров в выполняемый скрипт и получение выходных значений
Последнее явно в документации не описано, поэтому вот несколько примеров
В данном примере в динамический запрос передаются 4 переменные, три из которых являюся выходными
declare @var1 int , @var2 varchar (100 ), @var3 varchar (100 ), @var4 int declare @mysql nvarchar (4000 ) set @mysql = "set @var1 = @var1 + @var4; set @var2 = " "CCCC" "; set @var3 = @var3 + " "dddd" "" set @var1 = 0 set @var2 = "BBBB" set @var3 = "AAAA" set @var4 = 10 select @var1, @var2, @var3 exec sp_executesql @mysql, N"@var1 int out, @var2 varchar(100) out, @var3 varchar(100) out, @var4 int" , @var1 = @var1 out , @var2 = @var2 out , @var3 = @var3 out , @var4 = @var4 select @var1, @var2, @var3
В данном примере в динамическом запросе открывается курсор, который доступен в вызывающей процедуре через выходную переменную
USE pubs declare @cur cursor exec sp_executesql N"set @curvar= cursor local for select top 10 au_id, au_lname, au_fname from authors open @curvar" , N"@curvar cursor output " , @curvar=@cur output FETCH NEXT FROM @cur WHILE @@FETCH_STATUS = 0 BEGIN FETCH NEXT FROM @cur END
Резюме(более IMHO, чем обязательные требования)
Динамический запрос очень полезная и иногда просто незаменимая вещь, НО способы его реализации и конкретно через вызов в отдельном пакете с правами пользователя, вызвавшего процедуру, принижают его практическое МАССОВОЕ применение.
Инструкция SET присваивает значение переменной времени выполнения. Эти переменные могут быть системными переменными, специфичными для платформы, или пользовательскими переменными.
переменная
Обозначает системную или пользовательскую переменную.
значение
Обозначает строковое или числовое значение, соответствующее системной или пользовательской переменной.
Значения переменных устанавливаются на время сеанса. Значения, присвоенные переменной, должны соответствовать типу данных этой переменной. Например, вы не можете присвоить строковое значение переменной, которая объявлена с числовым типом данных. Команда, с помощью которой переменная создается, на разных платформах разная. Например, в DB2, Oracle и SQL Server используется инструкция DECLARE, в которой объявляется имя и тип переменной, однако на других платформах могут использоваться другие способы создания переменных.
Значение, которое присваивается переменной, не обязательно должно быть константой. Это может быть динамически генерируемое значение, создаваемое на основе подзапроса. Например, мы можем присвоить переменной emp_id_var максимальное значение идентификатора сотрудника (emp_id)|.
DECLARE emp_icLvar CHAR(5) SET empty var=(SELECT MAX(emp_id) FROM employees WHERE type="F")
В этом примере type F обозначает, что сотрудник работает полный день (full-time) и состоит на окладе.
Инструкция SET очень легко переносится с платформы на платформу. Только в Oracle используется несколько другая схема присвоения значения переменной. В следующем примере мы объявим в SQL Server переменную с именем emp_id_var и присвоим ей значение.
DECLARE emp_id var CHAR(5) SET emp_id_var="67888";
А теперь мы выполним ту же самую операцию для сервера Oracle.
DECLARE emp_id_var CHAR(5); emp_id_var:= "67888";
Платформа DB2 поддерживает базовую форму инструкции SET для присвоения значений локальным переменным, выходным параметрам или особым регистрам. В одной инструкции SET можно присваивать сразу несколько значений. Также эта инструкция позволяет присваивать значения столбцам базовой таблицы в триггере. Нельзя присвоить значения в одной инструкции двум типам переменных.
SET переменная={значение | NULL | DEFAULT} [, …]
Синтаксические элементы в DB2 следующие.
переменная
Указывается целевая переменная. Переменные SQL должны быть объявлены до использования. Переменная также может обозначать столбец базовой таблицы триггера.
значение
Указывается значение переменной в соответствии с ее типом данных. При присвоении значений столбцам в триггере вы также можете использовать ссылки на корреляционные имена OLD и NEW. За дополнительной информацией об этом обращайтесь к подразделу, посвященному DB2, раздела «Инструкция CREATE/ ALTER TRIGGER)).
Столбцу и переменной, которые могут принимать значения NULL, присваивается значение NULL.
Столбцам, которые создавались с указанием предложения WITH DEFAULT или IDENTITY, присваивается значение по умолчанию. Это предложение также присваивает пустое значение (NULL) тем столбцам, которые принимают пустые значения и при этом не определены с предложениями DEFAULTтя IDENTITY. Присвоим значение одной переменной.
SET new var.order_qty=125;
Если вы присваиваете значения в одной инструкции сразу нескольким переменным, то количество переменных слева от знака равенства должно в точности соответствовать количеству значений справа. Присвоим значения нескольким переменным.
SET new_var. order_qty=125, new_.va г. discount =
При использовании такого варианта, как SET переменная=SELECT значения результирующего набора инструкции SELECT должны в точности соответствовать переменным по количеству, положению и типу данных. Если инструкция SELECT не возвращает никаких значений, то переменным присваиваются значения NULL. Также для присвоения значений нескольким переменным в одной инструкции вы можете использовать инструкцию SELECT…INTO.
Ключевое слово SET имеет в MySQL несколько способов использования. Во-первых, SET - это тип данных MySQL, в котором может быть несколько значений, разделенных запятыми. (За информацией об этой области применения обращайтесь к главе 2, раздел «Типы данных MySQL».) Кроме того, инструкция SET может присваивать значения пользовательской переменной. Здесь описывается именно этот способ применения. Синтаксис следующий:
SET переменная - значение […]
При присвоении в одной инструкции значений нескольким переменным эти значения отделяются друг от друга запятыми.
SET new_var.order_.aty - 125. new_var. discount - 4;
Кроме того, MySQL позволяет использовать инструкцию SELECT для присвоения значений переменным точно так же, как это описывается в разделе, посвященном стандарту ANSI. Однако у метода с использованием инструкции SELECT есть несколько слабых мест. Главная проблема состоит в том, что значения не присваиваются и инструкции SELECT немедленно. Таким образом, в следующем примере:
SELECT (_>new_var - row_id) AS а, (@new_var + 3) AS b FROM Lable_name;
переменная @new_var не получит нового значения row id + 3. Сохранится то значение, которое она имела в начале инструкции. Поэтому хорошей практикой является присвоение переменной только одного значения за раз.
Предложение SET как метод присваивания значения переменным в Oracle не поддерживается. Вместо этого пользовательским переменным значения присваиваются при помощи оператора присваивания:=. Базовый синтаксис следующий.
В PostgreSQL команда SET используется для присваивания значения переменной во время выполнения.
SET переменная{ТО | -} {значение | DEFAULT}
Переменной во время выполнения можно присвоить строковое постоянное значение. При использовании ключевого слова DEFAULT переменной времени выполнения присваивается значение по умолчанию. Платформа PostgreSQL 7.2 поддерживает следующие переменные.
CLIENT ENCODING NAMES
Устанавливается мультибайтовая кодировка для клиентских систем PostgreSQL, скомпонованных с мультибайтовой поддержкой.
DATESTYLE
Устанавливается стиль, используемый для отображения даты и времени. Поддерживаются следующие стили.
Дата и время отображаются в формате ГПТ-ММ-ДД ЧЧ:ММ:СС (заданный по умолчанию стиль ISO 8601).
Дата и время отображаются в стиле Oracle/Ingres, а не в том стиле, который предписывается стандартом ANSI SQL.
PostgreSQL
Дата и время отображаются в длинном формате PostgreSQL, но не длиннее, чем задано по умолчанию.
Дата и время отображаются в виде ДД.ММ.ГГГГ. Вы можете уточнять стили SQL и Postgresql, используя ключевые слова European, US и NonEuropean, которые придают датам форматы дд/мм/гггг, мм/дд/гггг и мм/дд/гггг соответственно. Например: SETDATESTYLE=SQL, European.
Устанавливается начальное значение для внутреннего генератора случайных чисел. Значение может представлять собой любое число с плавающей точкой в диапазоне от 0 до 1, умноженное на 231-1. Это значение также можно установить при помощи функции PostgreSQL setseed. Например:
SELECT setseed(value); SEVER ENCODING;
Устанавливается мультибайтовая кодировка для серверов, скомпонованных с мульти-байтовой поддержкой.
Ниже приводится пример установки формата даты и времени стиля Oracle и European.
SET DATESTYLE ТО sql, European;
Платформа SQL Server поддерживает присваивание значений переменным при помощи инструкции SET, если эти переменные были ранее созданы при помощи инструкции DECLARE, а также присваивание значений переменным курсоров. (Также SQL Server использует инструкцию SET для других целей, например для включения и отключения флагов сеанса командами типа SETNOCOUNT ON.) Синтаксис, специфичный для данной платформы, приводится ниже.
Данная инструкция не поддерживает ключевое слово DEFAULT, но во всех прочих отношениях поддерживает синтаксис ANSI. Значение имя_сервера должно ссылаться на соединение, указанное в предыдущей инструкции CONNECT, либо в форме константы, либо в форме переменной.
Переменные T-SQL создаются с помощью команды DECLARE, имеющей следующий синтаксис:
DECLARE@Имя_Переменной Тип_Данных [,
@Имя_Переменной Тип_Данных, …]
Все имена локальных переменных должны начинаться символом @. Например, для объявления локальной переменной UStr, которая хранит до 16 символовUnicode, можно использовать следующую инструкцию:
DECLARE @UStr varchar(16)
Используемые для переменных типы данных в точности совпадают с существующими в таблицах. В одной команде DECLAREчерез запятую может быть перечислено несколько переменных. В частности в следующем примере создаются две целочисленные переменныеaиb:
Область определения переменных (т.е. срок их жизни) распространяется только на текущий пакет. По умолчанию только что созданные переменные содержат пустые значения NULLи до включения в выражения должны быть инициализированы.
Задание значений переменных
В настоящее время в языке SQLпредусмотрены два способа задания значения переменной - для этой цели можно использовать операторSELECTилиSET. С точки зрения выполняемых функций эти операторы действуют почти одинаково, не считая того, что операторSELECTпозволяет получить исходное присваиваемое значение из таблицы, указанной в оператореSELECT.
Оператор SET обычно используется для задания значений переменных в такой форме, какая более часто встречается в процедурных языках. В качестве типичных примеров применения этого оператора можно указать следующие:
SET @b = @a * 1.5
Обратите внимание на то, что во всех этих операторах непосредственно осуществляются операции присваивания, в которых используются либо явно заданные значения, либо другие переменные. С помощью оператора SET невозможно присвоить переменной значение, полученное с помощью запроса; запрос должен быть выполнен отдельно и только после этого полученный результат может быть присвоен с помощью оператора SET. Например, попытка выполнения такого оператора вызывает ошибку:
SET @c = COUNT(*) FROM City
а следующий оператор выполняется вполне успешно:
SET @c = (SELECT COUNT(*) FROM City)
Оператор SELECT обычно используется для присваивания значений переменным, если источником информации, которая должна быть сохранена в переменной, является запрос. Например, действия, осуществляемые в приведенном выше коде, гораздо чаще реализуются с помощью оператора SELECT:
SELECT @c = COUNT(*) FROM City
Обратите внимание на то, что данный код немного понятнее (в частности, он более лаконичен, хотя и выполняет те же действия).
Таким образом, можно, сформулировать следующее общепринятое соглашение по использованию того и другого оператора.
Оператор SET используется, если должна быть выполнена простая операция присваивания значения переменной, т.е. если присваиваемое значение уже задано явно в форме определенного значения или в виде какой-то другой переменной.
Оператор SELECT применяется, если присваивание значения переменной должно быть основано на запросе.
Использование переменных в запросах SQL
Одним из полезных свойств языка T-SQL является то, что переменные могут использоваться в запросах без необходимости создания сложных динамических строк, встраивающих переменные в программный код. Динамический SQL продолжает свое существование, но одиночное значение можно изменить проще - с помощью переменной.
Везде, где в запросе может использоваться выражение, может использоваться и переменная. В следующем примере продемонстрировано использование переменной в предложении WHERE:
DECLARE @IdProd int;
SET @IdProd = 1;
SELECT
WHERE IdProd = @IdProd;
Глобальные системные переменные
В SQLServerимеется более тридцати глобальных переменных, не имеющих параметров, которые определяются и поддерживаются системой. Все глобальные переменные имеют префикс в виде двух символов @. Вы можете извлечь значение любой из них с помощью простого запросаSELECT, как в следующем примере:
SELECT @@CONNECTIONS
Здесь используется глобальная переменная @@CONNECTIONSдля извлечения количества подключений кSQLServerсо времени запуска программы.
Среди наиболее часто применяемых системных переменных можно отметить следующие:
@@ERROR- Содержит номер ошибки, возникшей при выполнении последнего оператораT-SQLв текущем соединении. Если ошибка не обнаружена, содержит 0. Значение этой системной переменной переустанавливается после выполнения каждого очередного оператора. Если требуется сохранить содержащееся в ней значение, то это значение следует переносить в локальную переменную сразу же после выполнения оператора, для которого должен быть сохранен код ошибки.
@@IDENTITY- Содержит последнее идентификационное значение, вставленное в базу данных в результате выполнения последнего оператораINSERT. Если в последнем оператореINSERTне произошла выработка идентификационного значения, системная переменная @@IDENTITYсодержитNULL. Это утверждение остается справедливым, даже если отсутствие идентификационного значения было вызвано аварийным завершением при выполнении оператора. А если с помощью одного оператора осуществляется несколько операций вставки, этой системной переменной присваивается только последнее идентификационное значение.
@@ROWCOUNT- Одна из наиболее широко используемых системных переменных. Возвращает информацию о количестве строк, затронутых последним оператором. Обычно применяется для контроля ошибок, отличных от тех, которые относятся к категории ошибок этапа прогона программы. Например, если в программе обнаруживается, что после вызова на выполнение оператораDELETEс конструкциейWHEREколичество затронутых строк равно нулю, то можно сделать вывод, что произошло нечто непредвиденное. После этого сообщение об ошибке может быть активизировано вручную.
Следует отметить, что с версии SQLServer2000 глобальные переменные принято называть функциями. Название глобальные сбивало пользователей с толку, позволяя думать, что область действия таких переменных шире, чем у локальных. Глобальным переменным часто ошибочно приписывалась возможность хранить информацию, независимо от того, включена она в пакет либо нет, что, естественно, не соответствовало действительности.
