Triggers Stored Procedures

Transact-SQL розширення

Змінні, оператори, цикли та інші конструкції T-SQL для програмування в БД

Transact-SQL розширення

Що таке Transact-SQL?

Transact-SQL (T-SQL) — це розширений діалект SQL, розроблений Microsoft для SQL Server та Sybase для Sybase ASE.

Базовий SQL для роботи з даними:

-- Стандартний SELECT
SELECT * FROM Customers WHERE City = 'Kyiv';

-- Стандартний INSERT
INSERT INTO Orders (CustomerId, Total) VALUES (1, 500);

Обмеження: Тільки запити, не можна писати складну логіку.

SQL + програмування:

-- Змінні
DECLARE @city NVARCHAR(50) = 'Kyiv';

-- Умови
IF EXISTS (SELECT 1 FROM Customers WHERE City = @city)
BEGIN
    PRINT 'Є клієнти з Києва';
END

-- Цикли
WHILE @counter < 10
BEGIN
    -- Логіка
    SET @counter = @counter + 1;
END

Можливості: Повноцінне програмування в базі даних!

T-SQL додає до SQL:

Змінні (локальні та глобальні)
Умовні оператори (IF...ELSE, CASE)
Цикли (WHILE, BREAK, CONTINUE)
Обробка помилок (TRY...CATCH)
Функції та процедури
Курсори та динамічний SQL

Змінні

Локальні змінні

Локальні змінні починаються з @ і існують тільки в межах пакета (batch).

-- Оголошення
DECLARE @firstName NVARCHAR(50);
DECLARE @age INT = 25;  -- З ініціалізацією
DECLARE @price DECIMAL(10,2) = 299.99, @quantity INT = 5;  -- Кілька відразу

-- Присвоєння через SET
SET @firstName = 'Іван';

-- Присвоєння через SELECT
SELECT @firstName = FirstName FROM Students WHERE Id = 1;

-- Виведення
PRINT @firstName;
SELECT @firstName AS Name, @age AS Age;

Глобальні змінні

Глобальні змінні починаються з @@ і надається SQL Server (тільки для читання).

Змінна	Опис
`@@ROWCOUNT`	Кількість рядків, оброблених останнім запитом
`@@ERROR`	Номер останньої помилки (0 = немає)
`@@IDENTITY`	Останнє значення IDENTITY
`@@TRANCOUNT`	Кількість активних транзакцій
`@@VERSION`	Версія SQL Server
`@@SERVERNAME`	Ім'я сервера

INSERT INTO Books (Title, Price) VALUES ('Test', 100);

SELECT 
    @@ROWCOUNT AS RowsAffected,
    @@IDENTITY AS LastInsertedId,
    @@TRANCOUNT AS ActiveTransactions;

Табличні змінні

Змінні типу TABLE для збер збереження таблиць в пам'яті.

-- Створення табличної змінної
DECLARE @tempBooks TABLE (
    Id INT NOT NULL,
    Title NVARCHAR(200),
    Price DECIMAL(10,2)
);

-- Заповнення
INSERT INTO @tempBooks
SELECT TOP 5 Id, NameBook, Price
FROM book.Books;

-- Використання
SELECT * FROM @tempBooks WHERE Price > 100;

Табличні змінні vs Тимчасові таблиці (#temp):

Характеристика	Табличні змінні	Тимчасові таблиці
Область дії	Batch / процедура	Сесія (##=глобальна)
Індекси	Тільки PRIMARY KEY	Будь-які
Статистика	Немає	Є
Транзакції	Не відміняються при ROLLBACK	Відміняються

Використання: Табличні змінні — для невеликих даних (<1000 рядків).

Оператори виведення

PRINT

Виводить рядок ASCII в Messages (не Result Set).

PRINT 'Hello World';

DECLARE @name NVARCHAR(50) = 'Іван';
PRINT 'Привіт, ' + @name + '!';

-- З датою
DECLARE @msg NVARCHAR(100) = N'Сьогодні ' + CAST(GETDATE() AS NVARCHAR(30));
PRINT @msg;

Обмеження PRINT:

НЕ можна конкатинувати з виразами безпосередньо (використовуйте змінну)
Максимум 8000 символів
Не підходить для форматованого виводу (краще SELECT)

RAISERROR

Виводить повідомлення про помилку (можна форматувати як printf в C).

DECLARE @count INT = 5;

RAISERROR('Оброблено %d рядків', 0, 1, @count);
-- Результат: "Оброблено 5 рядків"

-- Форматування
RAISERROR('User: %s, ID: %d, Price: %.2f', 10, 1, 'Іван', 123, 299.99);

Специфікатори формату: -%d, %i — цілі числа

%s — рядки
%f — дійсні числа (не підтримується напряму, використовуйте CAST)

Рівні важливості:

0-10 — інформаційні повідомлення
11-16 — помилки користувача
17-25 — критичні помилки (тільки sysadmin)

Умовні оператори

IF...ELSE

DECLARE @age INT = 20;

IF @age >= 18
BEGIN
    PRINT 'Дорослий';
END
ELSE
BEGIN
    PRINT 'Неповнолітній';
END

З SELECT (обов'язкові дужки!):

IF (SELECT COUNT(*) FROM Students) > 100
BEGIN
    PRINT 'Більше 100 студентів';
END

-- З EXISTS (найшвидший спосіб перевірки)
IF EXISTS (SELECT 1 FROM Students WHERE Age > 20)
BEGIN
    PRINT 'є студенти старше 20';
END

CASE

Проста форма (порівняння з константою):

SELECT 
    NameBook,
    CASE ThemeId
        WHEN 1 THEN 'Програмування'
        WHEN 2 THEN 'Бази даних'
        WHEN 3 THEN 'Веб-розробка'
        ELSE 'Інше'
    END AS ThemeName
FROM book.Books;

З пошуком (умовні вирази):

SELECT 
    NameBook,
    Price,
    CASE
        WHEN Price < 100 THEN 'Дешева'
        WHEN Price BETWEEN 100 AND 500 THEN 'Середня'
        ELSE 'Дорога'
    END AS PriceCategory
FROM book.Books;

CASE — це ФУНКЦІЯ, не команда! Використовується тільки в SELECT, UPDATE, WHERE.vs IF: IF — для контролю потоку, CASE — для повернення значення.

Цикли

WHILE

Єдиний тип циклу в T-SQL.

DECLARE @counter INT = 1;

WHILE @counter <= 10
BEGIN
    PRINT 'Ітерація: ' + CAST(@counter AS NVARCHAR);
    SET @counter = @counter + 1;
END

BREAK та CONTINUE

DECLARE @i INT = 1;

WHILE @i < 20
BEGIN
    SET @i = @i + 1;
    
    IF @i = 5
        CONTINUE;  -- Пропустити ітерацію
    
    IF @i > 10
        BREAK;  -- Вийти з циклу
    
    PRINT @i;
END
-- Виведе: 2, 3, 4, 6, 7, 8, 9, 10

Приклад: Збільшення цін до середньої ціни 200:

WHILE (SELECT AVG(Price) FROM book.Books) < 200
BEGIN
    UPDATE book.Books
    SET Price = Price * 1.1;  -- Збільшити на 10%
END

SELECT AVG(Price) AS AveragePrice FROM book.Books;

GOTO (Не рекомендовано!)

Безумовний перехід до мітки.

DECLARE @error INT = 0;

-- Спроба операції
INSERT INTO Books (Title) VALUES ('Test');
SET @error = @@ERROR;

IF @error <> 0
    GOTO ErrorHandler;

PRINT 'Успіх';
GOTO EndScript;

ErrorHandler:
    PRINT 'Помилка!';

EndScript:
    PRINT 'Кінець';

Уникайте GOTO!Робить код нечитабельним і важко налагоджуваним. Використовуйте:

TRY...CATCH для обробки помилок
IF...ELSE для логіки

Common Table Expressions (CTE)

Віртуальні представлення для використання в межах одного запиту.

Базовий CTE

WITH AvgPriceByAuthor AS
(
    SELECT 
        a.Id AS AuthorId,
        a.LastName + ' ' + a.FirstName AS AuthorName,
        AVG(b.Price) AS AvgPrice
    FROM book.Authors a
    INNER JOIN book.Books b ON a.Id = b.AuthorId
    GROUP BY a.Id, a.LastName, a.FirstName
)
SELECT 
    b.NameBook,
    b.Price,
    ap.AuthorName,
    ap.AvgPrice
FROM book.Books b
INNER JOIN AvgPriceByAuthor ap ON b.AuthorId = ap.AuthorId
WHERE b.Price > ap.AvgPrice;

Переваги CTE:

Читабельніший код (замість підзапитів)
Можна посилатися кілька разів
Підтримує рекурсію

Рекурсивний CTE

Для ієрархічних даних (дерева, графи).

-- Приклад: послідовність чисел 1-10
WITH Numbers AS
(
    -- Початкова вибірка (якір)
    SELECT 1 AS Num
    
    UNION ALL
    
    -- Рекурсивна частина
    SELECT Num + 1
    FROM Numbers
    WHERE Num < 10
)
SELECT * FROM Numbers;
-- Результат: 1, 2, 3, ..., 10

Обмеження рекурсивних CTE:

НЕ можна: SELECT DISTINCT, GROUP BY, HAVING, TOP, OUTER JOIN
Максимум 100 рівнів рекурації (за замовчуванням)

Корисні функції

COALESCE

Повертає перше NOT NULL значення.

SELECT 
    COALESCE(RetailPrice, TradePrice, 0) AS FinalPrice
FROM book.Books;

-- Еквівалентно:
CASE
    WHEN RetailPrice IS NOT NULL THEN RetailPrice
    WHEN TradePrice IS NOT NULL THEN TradePrice
    ELSE 0
END

NULLIF

Повертає NULL, якщо два вирази рівні.

SELECT 
    NameBook,
    NULLIF(DrawingOfBook, 0) AS Drawing  -- Якщо 0 → NULL
FROM book.Books;

-- Використання з COALESCE
SELECT COALESCE(NULLIF(DrawingOfBook, 0), 1000) AS DefaultDrawing
FROM book.Books;

IIF (SQL Server 2012+)

Скорочена форма IF...ELSE (повертає значення).

SELECT 
    NameBook,
    Price,
    IIF(Price > 100, 'Дорога', 'Дешева') AS PriceCategory
FROM book.Books;

-- Еквівалентно:
CASE WHEN Price > 100 THEN 'Дорога' ELSE 'Дешева' END

Практичні завдання

Умова: Виведіть парні числа від 1 до 20.

💡 Розв'язок

DECLARE @num INT = 1;

WHILE @num <= 20
BEGIN
    IF @num % 2 = 0  -- Парне число
        PRINT @num;
    
    SET @num = @num + 1;
END

Умова: Знайдіть книги, ціна яких вище середньої за своєю тематикою.

💡 Розв'язок

WITH ThemeAvgPrice AS
(
    SELECT 
        ThemeId,
        AVG(Price) AS AvgPrice
    FROM book.Books
    GROUP BY ThemeId
)
SELECT 
    b.NameBook,
    b.Price,
    t.NameTheme,
    tap.AvgPrice AS ThemeAvgPrice
FROM book.Books b
INNER JOIN book.Themes t ON b.ThemeId = t.Id
INNER JOIN ThemeAvgPrice tap ON b.ThemeId = tap.ThemeId
WHERE b.Price > tap.AvgPrice
ORDER BY b.Price DESC;

Умова: Виберіть назву книги та її тираж. Якщо тираж 0 або NULL → показати 'Невідомо'.

💡 Розв'язок

SELECT 
    NameBook AS Title,
    COALESCE(
        NULLIF(CAST(DrawingOfBook AS NVARCHAR), '0'),
        'Невідомо'
    ) AS Drawing
FROM book.Books;

Резюме

Ключові моменти T-SQL розширень:

Змінні: @local (локальні), @@global (глобальні), табличні
IF...ELSE: Умовні оператори для контролю потоку
CASE: Функція для повернення значення на основі умов
WHILE: Єдиний тип циклу (з BREAK/CONTINUE)
PRINT/RAISERROR: Виведення повідомлень
CTE: Віртуальні представлення для читабельності
COALESCE/NULLIF/IIF: Робота з NULL та умовами

Best Practices:

Уникайте GOTO
Використовуйте CTE замість вкладених підзапитів
Табличні змінні — для невеликих даних
EXISTS швидше ніж COUNT(*) > 0
APPLY замість курсорів де можливо

Наступний крок: Застосуйте ці знання в DML-тригерах та зберігаємих процедурах для складної бізнес-логіки!

Edit this pageorReport an issue

DDL-тригери

Контроль змін структури бази даних та події рівня сервера

Транзакції

Забезпечення цілісності даних через ACID властивості