Основи програмування на T-SQL

У цьому матеріалі ми з Вами розглянемо основи програмування на мові 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 у нас виникло виключення, при якому ми просто отримуємо номер помилки і її опис.

Ссылка на основную публикацию