ГлавнаяРазноеSql переменная

Переменная таблицы Sql не существует после создания? Sql переменная


sql-server - Какая разница между переменной temp и табличной переменной в SQL Server?

Цитата взята из; Профессиональный внутренний SQL Server 2012 и устранение неполадок

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

Индексы. Вы не можете создавать индексы для переменных таблицы, хотя можете создавать ограничения. Это означает, что путем создания первичных ключей или уникальных ограничений, вы можете иметь индексы (поскольку они созданы для поддержки ограничения) на переменных таблицы. Даже если у вас есть ограничения и поэтому индексы, которые будут иметь статистику, индексы не будут используется, когда запрос скомпилирован, потому что они не существуют при компиляции времени, и они не вызовут перекомпиляции.

Модификации схемы Модификации схемы возможны на временных таблицы, но не таблицы. Хотя модификации схемы возможно на временных таблицах, избегать их использования, поскольку они вызывают перекомпиляции операторов, которые используют таблицы.

ТАБЛИЧНЫЕ ПЕРЕМЕННЫЕ НЕ СОЗДАНЫ В ПАМЯТИ

Существует распространенное заблуждение, что переменные таблицы являются структурами в памяти и как таковая будет выполняться быстрее, чем временные таблицы. Благодаря DMV называемый sys. dm _ db _ session _ space _, которое показывает использование tempdb по session, , вы можете доказать, что это не тот случай. После перезапуска SQL Server, чтобы очистить DMV, запустите следующий script, чтобы подтвердить, что ваш идентификатор сеанса _ возвращает 0 для user _ objects _ alloc _ page _ count:

SELECT session_id, database_id, user_objects_alloc_page_count FROM sys.dm_db_session_space_usage WHERE session_id > 50 ;

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

CREATE TABLE #TempTable ( ID INT ) ; INSERT INTO #TempTable ( ID ) VALUES ( 1 ) ; GO SELECT session_id, database_id, user_objects_alloc_page_count FROM sys.dm_db_session_space_usage WHERE session_id > 50 ;

Результаты на моем сервере показывают, что таблице была выделена одна страница в tempdb. Теперь запустите тот же script, но используйте переменную таблицы на этот раз:

DECLARE @TempTable TABLE ( ID INT ) ; INSERT INTO @TempTable ( ID ) VALUES ( 1 ) ; GO SELECT session_id, database_id, user_objects_alloc_page_count FROM sys.dm_db_session_space_usage WHERE session_id > 50 ;

Какой из них использовать?

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

Я видел, как клиенты разрабатывают код с использованием переменных таблицы, потому что они имели дело с небольшим количеством рядов, и это было быстрее, чем временный стол, но через несколько лет были сотни тысячи строк в переменной таблицы и производительности были ужасными, поэтому попробуйте и допустите планирование емкости, когда вы сделаете свой решение!

qaru.site

sql - Переменная MySql в задаче "where"

Предложение WHERE использует значение переменной, присутствующее перед выполнением запроса.

Попробуйте внедрить результат коррелированного подзапроса в свой собственный запрос, а затем отфильтровать его. СУРБД достаточно умна, чтобы обрабатывать только то, что нужно, как будто написанный ниже запрос никогда не существовал...

SELECT tour_id, tour_name, company_name, first_name, last_name, expire, (expire + INTERVAL 14 DAY ) AS expirediff, CURDATE() AS now, ( (expire + INTERVAL 14 DAY) = CURDATE() ) AS criteria FROM ( SELECT t.tour_id, t.tour_name, u.company_name, u.first_name, u.last_name, (SELECT DATE_ADD(tour_start_date, INTERVAL (t.tour_duration - 1) DAY) FROM travelhub_tours_instance WHERE tour_id = t.tour_id ORDER BY tour_start_date DESC LIMIT 1) AS expire FROM travelhub_tours t JOIN travelhub_users u ON t.operator_id = u.user_id ) AS sub_query WHERE (expire + INTERVAL 14 DAY) = CURDATE()

Примечание:

Предложение WHERE включает добавление 14 дней к каждому значению expiure. Возможно, вам лучше провести 14 дней с CURDATE(), это произойдет только один раз.

WHERE expire = CURDATE() - INTERVAL 14 DAY

EDIT:

Также обратите внимание, что СУБД на самом деле довольно умны. SQL, который вы пишете, не совсем то, что выполняется, он анализируется, оптимизируется, компилируется и т.д. Он заканчивается традиционным последовательным кодом. Это означает, что РСУБД может заметить, что у вас есть один и тот же подзапрос, написанный несколько раз, и знаю, что он должен выполняться только один раз, а не несколько раз...

Например, два идентичных суб-запроса здесь не будут выполняться дважды для каждой записи. СУРБД более умна, чем это. Фактически, она даже может сказать, что ей требуется только один раз, потому что результат не зависит от обрабатываемых записей.

SELECT (SELECT MAX(event_date) FROM event_table) AS max_event_date, event_date FROM event_table WHERE (SELECT MAX(event_date) FROM event_table) - INTERVAL 7 DAY <= event_date

Тем не менее, использование sub_queries, например, мой первоначальный ответ, может упростить работу с кодом (требуется только изменение в одном месте).

qaru.site

sql - Переменная таблицы Sql не существует после создания?

Я запускаю следующий запрос, но получаю сообщение о том, что мои переменные таблицы не существуют в последнем запросе. Может ли кто-нибудь понять, почему это происходит и как исправить?

DECLARE @customers Table(custName varchar(20), custCode varchar(10) ) INSERT INTO @customers select distinct JM.CustLongName, JM.CustomerCode From Jobmaster JM WHERE StartDate > '20091031' DECLARE @Oct Table(custCode varchar(10), PromiseAvg decimal(10,4), ShipAvg decimal(10,4) ) --The following statement is run 12 times with the different months of the year INSERT INTO @Oct SELECT JM.CustomerCode , isnull(AVG(Cast(DATEDIFF(dd, ISNULL(startDate, 0), ISNULL(PromiseDate, 0)) As Decimal)),0) As PromiseAvg , isnull(AVG(Cast(DATEDIFF(dd, ISNULL(startDate, 0), ISNULL(BOL.ShipDate, 0)) As Decimal)),0) As ShipAvg from jobitems JI LEFT JOIN jobmaster JM ON JI.Job_Number = JM.JobNumber LEFT JOIN dbo.NCL_BOLDetails BD ON JM.JobNumber = BD.JobNumber AND JI.Item_Code = BD.ItemNumber INNER JOIN dbo.NCL_BOLs BOL ON BD.BOLID = BOL.BOLID WHERE StartDate BETWEEN '20101001' AND '20101031' Group By JM.CustomerCode, JM.CustLongName Order By JM.CustomerCode /*....copies removed for brevity.....*/ Select [@customers].custCode, [@customers].custName , [@Nov].PromiseAvg, [@Nov].ShipDate , [@Dec].PromiseAvg, [@Dec].ShipDate , [@Jan].PromiseAvg, [@Jan].ShipDate , [@Feb].PromiseAvg, [@Feb].ShipDate , [@Mar].PromiseAvg, [@Mar].ShipDate , [@Apr].PromiseAvg, [@Apr].ShipDate , [@May].PromiseAvg, [@May].ShipDate , [@Jun].PromiseAvg, [@Jun].ShipDate , [@Jul].PromiseAvg, [@Jul].ShipDate , [@Aug].PromiseAvg, [@Aug].ShipDate , [@Sep].PromiseAvg, [@Sep].ShipDate , [@Oct].PromiseAvg, [@Oct].ShipDate FROM @customers LEFT JOIN [@Nov] ON [@customers].custCode = [@Nov].custCode LEFT JOIN [@Dec] ON [@customers].custCode = [@Dec].custCode LEFT JOIN [@Jan] ON [@customers].custCode = [@Jan].custCode LEFT JOIN [@Feb] ON [@customers].custCode = [@Feb].custCode LEFT JOIN [@Mar] ON [@customers].custCode = [@Mar].custCode LEFT JOIN [@Apr] ON [@customers].custCode = [@Apr].custCode LEFT JOIN [@May] ON [@customers].custCode = [@May].custCode LEFT JOIN [@Jun] ON [@customers].custCode = [@Jun].custCode LEFT JOIN [@Jul] ON [@customers].custCode = [@Jul].custCode LEFT JOIN [@Aug] ON [@customers].custCode = [@Aug].custCode LEFT JOIN [@Sep] ON [@customers].custCode = [@Sep].custCode LEFT JOIN [@Oct] ON [@customers].custCode = [@Oct].custCode

qaru.site

sql-server - Переменная TSQL со списком значений для позиции IN

Я хочу использовать предложение в строках "CASE WHEN... THEN 1 ELSE 0 END" в инструкции select. Трудная часть заключается в том, что мне нужно, чтобы она работала со значением "значение IN @List".

Если я жестко кодирую список, он отлично работает - и он хорошо работает:

SELECT CASE WHEN t.column_a IN ( 'value a', 'value b' ) THEN 1 ELSE 0 END AS priority , t.column_b , t.column_c FROM table AS t ORDER BY priority DESC

Что я хотел бы сделать:

-- @AvailableValues would be a list (array) of strings. DECLARE @AvailableValues ??? SELECT @AvailableValues = ??? FROM lookup_table SELECT CASE WHEN t.column_a IN @AvailableValues THEN 1 ELSE 0 END AS priority , t.column_b , t.column_c FROM table AS t ORDER BY priority DESC

К сожалению, похоже, что SQL Server этого не делает - вы не можете использовать переменную с предложением IN. Поэтому это оставляет мне другие варианты:

  • Сделайте "@AvailableValues" строку с разделителями-запятыми и используйте инструкцию LIKE. Это не работает хорошо.
  • Используйте переменную SELECT в строке 'lookup_table' вместо переменной. Опять же, не работает хорошо (я думаю), потому что он должен искать таблицу в каждой строке.
  • Запишите переменную, обернутую вокруг оператора SELECT вместо переменной. Я еще не пробовал это (попробую сейчас), но похоже, что у него будет такая же проблема, как прямое выражение SELECT.
  • ???

Есть ли другие варианты? Производительность очень важна для запроса - она ​​должна быть действительно быстрой, поскольку она подает страницу результатов поиска в реальном времени (т.е. Не кэширование) для веб-сайта.

Есть ли другие варианты? Есть ли способ улучшить производительность одного из вышеперечисленных вариантов, чтобы получить хорошую производительность?

Заранее благодарим за любую помощь!

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

ОБНОВЛЕНИЕ II: Мне пришло в голову, что предложение IN отключается от поля NVARCHAR/NCHAR (из-за причин, связанных с исторической таблицей). Если бы я должен был вносить изменения, которые касались целочисленных полей (т.е. Ограничений отношений PK/FK), это могло бы сильно повлиять на производительность?

qaru.site

Declare SQL: описание. Transact-SQL

Сегодня практически каждый современный программист знает, что такое Transact-SQL. Это расширение, которое используется в SQL Server. Данная разработка тесно интегрирована в язык Microsoft SQL и добавляет конструкторы программирования, которые изначально не предусмотрены в базах данных. T-SQL поддерживает переменные, как и в большинстве других разработках. Однако это расширение ограничивает использование переменных способами, которые не распространены в других средах.

Объявление переменных в DECLARE SQL

Для объявления переменной в T-SQL используется оператор DECLARE (<var_name>). Например, в случае объявления переменной i как целое с использованием данного оператора команда будет выглядеть так: DECLARE @i int.

Хотя Microsoft не документирует эту функцию, T-SQL также поддерживает указание ключевого слова AS между именем переменной и ее типом данных, как в следующем примере: DECLARE @i AS int. Ключевое слово AS упрощает чтение инструкции DECLARE. Единственный тип данных, который не позволяет указать ключевое слово AS, - это тип данных таблицы, который является новым в SQL Server 2000. Он дает возможность определить переменную, содержащую полную таблицу.

DECLARE SQL: описание

T-SQL поддерживает только локальные переменные, которые доступны исключительно в той партии, которая их создала. Пакет - это оператор (или группа операторов), который база данных анализирует как единицу. Каждый клиентский инструмент или интерфейс имеет свой собственный способ указания, где заканчивается пакет. Например, в Query Analyzer вы используете команду GO, чтобы указать, где заканчивается пакет. Если у вас есть синтаксическая ошибка в любом заявлении, пакет не проходит фазу разбора, поэтому клиентский инструмент не отправляет пакет на SQL Server для дальнейшей обработки. Вы можете запустить код, который объявляет переменную таблицы, а затем вставляет строку в таблицу в той же партии.

Пример SQL Declare Table:

DECLARE @mytable tablecol1 int NOT NULLINSERT INTO @mytable VALUES (1)GO

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

DECLARE @mytable tablecol1 int NOT NULLINSERT INTO @mytable VALUES (1)GO

Оператор INSERT терпит неудачу, потому что переменная таблицы выходит за пределы области видимости, и появляется следующее сообщение об ошибке:Сервер: Msg 137, уровень 15, состояние 2, строка 2.

Переменные в процедурах (инструкции DECLARE, SET)

Поддержка локальных переменных в процедурах SQL позволяет назначать и извлекать значения данных в поддержку логики процедур. Переменные в процедурах определяются с помощью оператора DECLARE SQL. Значения могут присваиваться переменным с помощью инструкции SET или в качестве значения по умолчанию при объявлении переменной. Литералам, выражениям, результатам запроса и специальным значениям регистра могут быть присвоены переменные.Значения переменных могут быть назначены параметрам процедуры, другим переменным, а также могут быть указаны как параметры в операторах SQL, выполняемых в рамках процедуры.

Алгоритм

При объявлении переменной вы можете указать значение по умолчанию, используя предложение DEFAULT. Строка показывает объявление переменной типа Boolean со значением по умолчанию FALSE. Оператор SET может использоваться для назначения одного значения переменной. Переменные также могут быть установлены путем выполнения инструкции SELECT или FETCH в сочетании с предложением INTO. Оператор VALUES INTO может использоваться для оценки функции или специального регистра и присваивать значение нескольким переменным.

Вы также можете присвоить результат оператора GET DIAGNOSTICS переменной. GET DIAGNOSTICS может использоваться для получения дескриптора количества затронутых строк (обновляется для оператора UPDATE, DELETE - для оператора DELETE) или статуса возврата только что выполненного SQL-оператора

Особенности

Строка DECLARE SQL демонстрирует, как часть логики может использоваться для определения значения, которое должно быть присвоено переменной. В этом случае, если строки были изменены как часть более раннего оператора DELETE, а выполнение GET DIAGNOSTICS привело к тому, что переменной v_rcount присвоено значение, большее нуля, переменной is_done присваивается значение TRUE.

Процедуры

Процедуры DECLARE SQL - это процедуры, полностью реализованные с использованием SQL, которые могут использоваться для инкапсуляции логики. Та же в свою очередь может быть вызвана как подпрограмма программирования.

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

Существует множество функций процедур, которые делают их мощным инструментом обработки. Прежде чем принять решение о внедрении процедуры SQL, важно понять, какие аналоги находятся в контексте подпрограмм, как они реализованы и как их можно использовать.

Создание процедур

Внедрение SQL-процедур может играть важную роль в архитектуре базы данных, разработке приложений и производительности системы. Разработка требует четкого понимания требований, возможностей и использования функций, а также знания любых ограничений. Процедуры SQL создаются по инструкции CREATE PROCEDURE. Когда создается алгоритм, запросы в теле процедуры отделяются от процедурной логики. Чтобы максимизировать производительность, SQL-запросы статически компилируются в разделы в пакете

Переменные

Локальная переменная Transact-SQL - это объект, который может содержать одно значение данных определенного типа. Обычно используются переменные в партиях и сценариях:

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

Имена ряда функций Transact-SQL начинаются со знаков (@@). Хотя в более ранних версиях Microsoft SQL Server функции @@ называются глобальными переменными. @@ - это системные функции, и их использование подчиняется правилам синтаксиса для функций.

Объявление переменной

Оператор DECLARE определяет переменную Transact-SQL согласно следующему алгоритму:

  • определение имени, которое должно иметь один символ @ в качестве первого символа;
  • назначение заданного или определенного пользователем типа данных и длины;
  • для числовых переменных также назначаются точность и масштаб.
  • для переменных типа XML может быть назначена дополнительная сборка схемы.
  • Установка значения в NULL. Например, оператор DECLARE в SQL-запросе создает локальную переменную с именем @mycounter с типом данных int.

Чтобы объявить несколько локальных переменных, используйте запятую после определения первой локальной переменной, а затем укажите следующее имя локальной сети и тип данных. Например, следующий оператор создает три локальные переменные с именем @LastName, @FirstName и @StateProvince и инициализирует каждый из NULL. Объем переменной - это диапазон операторов Transact-SQL, которые могут ссылаться на переменную. Объем переменной длится от той точки, которая объявляется до конца партии или хранимой процедуры, в которой она объявлена.

fb.ru

tsql - Выходная переменная из динамического SQL

У меня проблема, я пытаюсь вывести значение из динамического запроса T-SQL внутри хранимой процедуры.

Я пытаюсь выполнить следующее и просто вывести 1, если что-то найдено:

SET NOCOUNT ON DECLARE @returnValue int DECLARE @Statement nvarchar(400) set @Statement = 'SELECT @result=''1'' FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = ''sourceTable'' AND COLUMN_NAME = @columnIN AND TABLE_SCHEMA = ''dbo''' exec sp_executesql @Statement, N'@columnIN nvarchar(60),@result INT OUTPUT', @columnIN = @column, @result=@returnValue OUTPUT select @returnValue

В настоящее время это дает NULL. Кто-нибудь знает, что мне не хватает?

Дополнительная информация: столбец, который я пытаюсь найти, - это, например, column1. Если я запустил SQL-запрос с ...AND CLOUMN_NAME = 'column1'... Я получаю 1 назад. Если я напечатаю переменную @column в SP, я получаю "column1". @column объявляется как входная переменная с nvarchar (60) в SP: PROCEDURE [dbo].[usp_checkColumn] (@column nvarchar(60), @result INT OUTPUT)

По запросу полный SP здесь:

Create PROCEDURE [dbo].[usp_checkColumn] (@column nvarchar(60), @result INT OUTPUT) AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON DECLARE @returnValue int DECLARE @Statement nvarchar(400) set @Statement = 'SELECT @result=''1'' FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = ''t_table'' AND COLUMN_NAME = @columnIN AND TABLE_SCHEMA = ''dbo''' exec sp_executesql @Statement, N'@columnIN nvarchar(60),@result INT OUTPUT', @columnIN = @column, @result=@returnValue OUTPUT select @returnValue return @returnValue END

И вот как я называю SP:

DECLARE fcursor CURSOR FOR select FieldName from t_fieldDefinition OPEN fcursor Fetch next from fcursor into @field; WHILE @@FETCH_STATUS = 0 BEGIN SET @tmpField = '''' + @field + '''' SET @field ='[' + @field + ']' set @available = 0 exec usp_checkColumn @tmpField,@available OUTPUT

Если я напечатаю переменную [@column] в usp_checkColumn, я получу правильный столбец внутри '. Если я скопирую и вставляю печать этой переменной и вставляю ее в запрос, я получаю 1 назад, если я запускаю SP, я получаю NULL (преобразованный в 0, когда NULL недопустим для переменной INT).

Здесь содержимое таблицы t_fieldDefinition:

FieldName ID Source 5 column1 6 column2 7 Client 8 asd BSX 9 bsd 10 esd 11 esx 12

И здесь определение таблицы t_table:

ID bigint Unchecked Source varchar(250) Checked column1 varchar(250) Checked column2 nvarchar(100) Checked Client varchar(10) Checked asd varchar(250) Checked [asd BSX] varchar(250) Checked

так что это означает, что он должен возвращать 1 для всех, которые находятся внутри определения таблицы, и 0 для всех остальных. Возможно ли, что поле с пробелом может быть проблемой? Хотя они работают, когда вы делаете это вручную. Это не значит, что у меня действительно есть возможность изменить его, но по крайней мере теперь я буду причиной проблемы.

qaru.site

Основы программирования на T-SQL | Info-Comp.ru

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

И начать хотелось бы с того, что на этом сайте мы с Вами уже достаточно много материала посвятили языку SQL и в частности его расширению Transact-SQL (как Вы понимаете T-SQL это сокращение от Transact-SQL). И даже составили небольшой справочник для начинающих по данному языку и, конечно же, рассмотрели множество примеров, но как таковое программирование на T-SQL там, например, переменные, условные конструкции, комментарии мы затрагивали, но не заостряли на этом внимания. Но так как у нас сайт для начинающих программистов я решил посвятить этот материал именно этим основам.

Язык программирования T-SQL

Transact-SQL (T-SQL) – расширение языка SQL от компании Microsoft и используется в SQL Server для программирования баз данных.

SQL Server включает много конструкций, компонентов, функций которые расширяют возможности языка SQL стандарта ANSI, в том числе и классическое программирование, которое отличается от обычного написания запросов.

И сегодня мы с Вами рассмотрим ту часть основ языка T-SQL, которая подразумевает написание кода для реализации некого функционала (например, в процедуре или функции), а не просто какого-то запроса к базе данных.

Примечание! Код я буду писать в окне запроса среды SQL Server Management Studio, о том, как установить SQL Server и Management Studio в редакции Express мы с Вами разговаривали вот здесь.

Переменные в T-SQL

И начнем мы с переменных, они нужны для того, чтобы хранить какие-то временные данные, которые нам необходимо на время сохранить, а затем использовать.

Существует две разновидности переменных в T-SQL - это локальные и глобальные. Локальные переменные существуют только в пределах сеанса, во время которого они были созданы, а глобальные используются для получения информации о SQL сервере или какой-то меняющейся информации в базе данных.

Локальные переменные объявляются с помощью ключевого слова DECLARE и начинаются со знака @. Как и во многих языках программирования, переменные в T-SQL должны иметь свой тип данных. Типов данных в SQL сервере достаточно много мы их подробно рассмотрели в справочнике, который я упоминал чуть выше.

Для присвоения значения переменной можно использовать команды SET или Select.

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

  • @@ROWCOUNT – хранит количество записей, обработанных предыдущей командой;
  • @@ERROR – возвращает код ошибки для последней команды;
  • @@SERVERNAME - имя локального SQL сервера;
  • @@VERSION - номер версии SQL Server;
  • @@IDENTITY - последнее значение счетчика, используемое в операции вставки (insert).

Теперь для примера давайте создадим две переменной с типом данных INT, присвоим им значения, первой с помощью команды SET, а второй с помощью команды Select, затем просто выведем на экран эти значения, а также выведем и значение переменной @@VERSION, т.е. узнаем версию SQL сервера.

DECLARE @TestVar1 INT DECLARE @TestVar2 INT SET @TestVar1 = 1 SELECT @TestVar2 = 2 SELECT @TestVar1 AS [Переменная 1], @TestVar2 AS [Переменная 2], @@VERSION AS [Версия SQL Server]

Пакеты

Пакет в T-SQL - это команды или инструкции SQL, которые объединены в одну группу и при этом SQL сервер будет компилировать, и выполнять их как одно целое.

Для того чтобы дать понять SQL серверу, что Вы передаете пакет команд необходимо указывать ключевое слово GO после всех команд, которые Вы хотите объединить в пакет.

Локальные переменные будут видны только в пределах того пакета, в котором они были созданы, т.е. обратиться к переменной после завершения пакета Вы уже не сможете.

Допустим, если пример, который мы использовали выше, объединить в пакет, а потом попробовать получить значение переменных, то у нас получится следующее:

Т.е. мы видим, что у нас вышла ошибка, связанная с тем, что переменная @TestVar1 у нас не объявлена.

Условные конструкции

Эти конструкции подразумевают ветвление, т.е. в зависимости от выполнения или невыполнения определенных условий инструкции T-SQL будут менять свое направление.

IF...ELSE

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

DECLARE @TestVar1 INT DECLARE @TestVar2 VARCHAR(20) SET @TestVar1 = 5 IF @TestVar1 > 0 SET @TestVar2 = 'Больше 0' ELSE SET @TestVar2 = 'Меньше 0' SELECT @TestVar2 AS [Значение TestVar1]

IF EXISTS

Данная конструкция позволяет определить наличие записей определенных условием. Например, мы хотим знать есть ли в таблице те или иные записи и при обнаружении первого совпадения обработка команды прекращается. По сути это то же самое, что и COUNT(*) > 0.

К примеру, мы хотим проверить есть ли записи со значение id >=0 в таблице test_table, и на основе этого мы будем принимать решение, как действовать дальше

DECLARE @TestVar VARCHAR(20) IF EXISTS(SELECT * FROM test_table WHERE id > = 0) SET @TestVar = 'Записи есть' ELSE SET @TestVar = 'Записей нет' SELECT @TestVar AS [Наличие записей]

CASE

Данная конструкция используется совместно с оператором select и предназначена она для замены многократного использования конструкции IF. Она полезна в тех случаях, когда необходимо проверять переменную (или поле) на наличие определенных значений.

DECLARE @TestVar1 INT DECLARE @TestVar2 VARCHAR(20) SET @TestVar1 = 1 SELECT @TestVar2 = CASE @TestVar1 WHEN 1 THEN 'Один' WHEN 2 THEN 'Два' ELSE 'Неизвестное' END SELECT @TestVar2 AS [Число]

BEGIN...END

Эта конструкция необходима для создания блока команд, т.е. например, если бы мы хотели выполнить не одну команду после блока IF, а несколько, то нам бы пришлось писать все команды внутри блока BEGIN...END.

Давайте модифицируем наш предыдущий пример (про IF EXISTS) так, чтобы при наличии записей id > = 0 в таблице test_table, мы помимо присвоения значения переменной @TestVar, выполним еще и update, т.е. обновление неких данных в этой же таблице, а также выведем количество строк, которые мы обновили, используя глобальную переменную @@ROWCOUNT.

DECLARE @TestVar1 VARCHAR(20) DECLARE @TestVar2 INT SET @TestVar2 = 0 IF EXISTS(SELECT * FROM test_table WHERE id > = 0) BEGIN SET @TestVar1 = 'Записи есть' UPDATE test_table SET column1 = 5 WHERE id > = 0 SET @TestVar2 = @@ROWCOUNT END ELSE SET @TestVar1 = 'Записей нет' SELECT @TestVar1 AS [Наличие записей], @TestVar2 AS [Затронуто строк:]

Циклы T-SQL

Если говорить в общем о циклах, то они нужны для многократного повторения выполнения команд. В языке T-SQL есть один цикл WHILE с предусловием, это означает, что команды начнутся, и будут повторяться до тех пор, пока выполняется условие перед началом цикла, также выполнение цикла можно контролировать с помощью ключевых слов BREAK и CONTINUE.

DECLARE @Cnt INT = 1, @result INT = 0, @CountRow INT SELECT @CountRow = COUNT(*) FROM test_table WHILE @Cnt <= @CountRow BEGIN SET @Cnt += 1 SET @result += 1 IF @Cnt = 20 BREAK ELSE CONTINUE END SELECT @result AS [Количество выполнений цикла:]

В данном примере мы сначала, конечно же, объявляем переменные (Cnt и result мы сразу инициализируем, таким способом можно задавать значения переменных, начиная с SQL Server 2008). Затем узнаем, сколько строк в таблице test_table и после этого проверяем, если количество строк в таблице больше или равно нашему счетчику, то входим в наш тестовый цикл. В цикле мы увеличиваем значение счетчика, записываем результат и снова проверяем, если наш счетчик достиг уже значения 20, то мы его принудительно завершим, если нет, то пусть работает дальше, до того как значение счетчика станет больше или равно количеству строк в таблице или до 20, если в таблице строк больше.

Комментарии

Они нужны для того, чтобы пояснять, делать заметки в коде, так как если код большой и сложный, то через некоторое время Вы можете просто забыть, почему именно так и для чего Вы написали тот или иной участок кода. В языке T-SQL бывают однострочные (--Текст) и многострочные комментарии (/*Текст*/).

Команды T-SQL

GOTO

С помощью этой команды можно перемещаться по коду к указанной метке. Например, ее можно использовать тогда когда Вы хотите сделать своего рода цикл, но без while.

DECLARE @Cnt INT = 0 Metka: --Устанавливаем метку SET @Cnt += 1 --Прибавляем к переменной 1 if @Cnt < 10 GOTO Metka --Если значение меньше 10, то переходим к метке SELECT @Cnt AS [Значение Cnt =]

WAITFOR

Команда может приостановить выполнение кода на время или до наступления заданного времени. Параметр DELAY делает паузу заданной длины, а TIME приостанавливает процесс до указанного времени. Значение параметров задается в формате hh:mi:ss

DECLARE @TimeStart time, @TimeEnd time SET @TimeStart = CONVERT (time, GETDATE())--Узнаем время WAITFOR DELAY '00:00:05'--Пауза на 5 секунд SET @TimeEnd = CONVERT (time, GETDATE())--Снова узнаем время --Узнаем, сколько прошло времени в секундах SELECT DATEDIFF(ss, @TimeStart, @TimeEnd) AS [Прошло Секунд:]

RETURN

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

DECLARE @Cnt INT = 1, @result varchar(15) /*Если значение Cnt меньше 0, то следующие команды не выполнятся, и Вы не увидите колонку [Результат :]*/ IF @Cnt < 0 RETURN SET @result = 'Cnt больше 0' SELECT @result AS [Результат:]

PRINT

Для передачи служебного сообщения можно использовать команду PRINT. В Management Studio это сообщение отобразится на вкладке «Сообщения» (Messages).

DECLARE @Cnt INT = 10, @TestVar varchar(100) IF @Cnt > 0 SET @TestVar = 'Значение переменной Cnt больше 0 и равняется ' + CAST(@Cnt AS VARCHAR(10)) ElSE SET @TestVar = 'Значение переменной Cnt меньше 0 и равняется ' + CAST(@Cnt AS VARCHAR(10)) PRINT @TestVar

Транзакции

Транзакция – это команда или блок команд, которые успешно завершаются или отменяются как единое целое. Другими словами, если одна команда или инструкция внутри транзакции завершилась с ошибкой, то все что было отработано перед ней, также отменяется, даже если предыдущие команды завершились успешно.

Этот механизм необходим для того, чтобы обеспечить целостность данных, т.е. допустим, у Вас есть процедура, которая перечисляет деньги с одного счета на другой, но может возникнуть ситуация при которой деньги снялись со счета, но не поступили на другой счет. К примеру, SQL инструкция, которая осуществляет снятие денег, отработала, а при выполнении инструкции, которая зачисляет деньги, возникла ошибка, другими словами, деньги снялись и просто потерялись. Чтобы этого не допускать, все SQL инструкции пишут внутри транзакции и тогда если наступит такая ситуация все изменения будут отменены, т.е. деньги вернутся на счет обратно.

--Узнаем что у нас в таблице (id = IDENTITY) SELECT * FROM test_table --Начинаем транзакцию BEGIN TRAN --Сначала обновим все данные UPDATE test_table SET column1 = column1 - 5 --Затем просто добавим строки с новыми значениями INSERT INTO test_table SELECT column1 FROM test_table --Если ошибка, то все отменяем IF @@error != 0 BEGIN ROLLBACK TRAN RETURN END COMMIT TRAN --Смотрим что получилось SELECT * FROM test_table

В этом примере, если бы у нас в момент добавления данных (INSERT) возникла ошибка, то UPDATE бы отменился.

Обработка ошибок - конструкция TRY...CATCH

В процессе выполнения T-SQL кода может возникнуть непредвиденная ситуация, т.е. ошибка, которую необходимо обработать. В SQL сервере, начиная с SQL Server 2005, существует такая конструкция как TRY...CATCH, которая может отследить ошибку.

BEGIN TRY DECLARE @TestVar1 INT = 10, @TestVar2 INT = 0, @result INT SET @result = @TestVar1 / @TestVar2 END TRY BEGIN CATCH SELECT ERROR_NUMBER() AS [Номер ошибки], ERROR_MESSAGE() AS [Описание ошибки] END CATCH

В этом примере возникла ситуация что происходит деление на ноль (как Вы знаете делить на 0 нельзя) и так как наш блок кода был помещен в конструкцию TRY у нас возникло исключение, при котором мы просто получаем номер ошибки и ее описание.

Я думаю для основ этого достаточно, если Вы хотите более подробно изучить все конструкции языка T-SQL, то рекомендую прочитать мою книгу «Путь программиста T-SQL», в которой уже более подробно рассмотрен язык T-SQL, у меня все, удачи!

Похожие статьи:

info-comp.ru