В этом материале мы с Вами рассмотрим основы программирования на языке T-SQL, узнаем, что это за язык, какими основными возможностями он обладает, какие конструкции включает и, конечно же, в процессе всего этого я буду приводить примеры кода.
И начать хотелось бы с того, что на этом сайте мы с Вами уже достаточно много материала посвятили языку SQL и в частности его расширению Transact-SQL (как Вы понимаете T-SQL – это сокращение от Transact-SQL, более подробно о том, что такое T-SQL в статье – https://self-learning.ru/what-is-t-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.
Заметка! Как сформировать и вставить в таблицу заданное количество строк на T-SQL.
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. В 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 бы отменился.
Заметка! Подробнее про транзакции можете почитать в статье – Транзакции в T-SQL – основы для новичков с примерами.
Обработка ошибок — конструкция 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 у нас возникло исключение, при котором мы просто получаем номер ошибки и ее описание.
Я думаю для основ этого достаточно, всем тем, кто только начинает свое знакомство с языком SQL, рекомендую прочитать книгу «SQL код» – это самоучитель по языку SQL, которую написал я, и в которой я подробно, и в то же время простым языком, рассказываю о языке SQL, у меня все, удачи!
Заметка! Для профессионального изучения языка T-SQL рекомендую посмотреть мои видеокурсы по T-SQL.
