Transact-sql, процедурні розширення

Існує кілька обмежень на включення різних інструкцій мови Transact-SQL в пакет. Найбільш важливим з них є та обставина, що якщо пакет містить інструкцію опису даних CREATE VIEW, CREATE PROCEDURE або CREATE TRIGGER, то він не може містити ніяких інших інструкцій. Іншими словами, така інструкція повинна бути єдиною інструкцією пакета. Інструкції мови опису даних поділяються за допомогою інструкції GO.

Кожне процедурне розширення мови Transact-SQL розглядається окремо в наступних розділах.

блок інструкцій

Блок інструкцій може складатися з однієї або декількох інструкцій мови Transact-SQL. Кожен блок починається з інструкції BEGIN і закінчується інструкцією END. як це показано далі:

Блок можна розмістити всередині інструкції IF, щоб в залежності від певної умови дозволити виконання однієї або декількох інструкцій.

Інструкція IF

Інструкція IF мови Transact-SQL відповідає однойменної інструкції, підтримуваної багатьма мовами програмування. Інструкція IF виконує одну або декілька складових блок інструкцій, якщо логічний вираз, наступне після ключового слова IF, повертає значення true (істина). Якщо ж інструкція IF містить оператор ELSE. то за умови, що логічне вираз повертає значення false (брехня), виконується друга група інструкцій.

Нижче показаний приклад використання умовної інструкції IF:

У цьому прикладі демонструється використання блоку інструкцій всередині інструкції IF. Наступне далі логічний вираз інструкції IF:

повертає значення true (істина) для бази даних SampleDb. Тому буде виконуватися інструкція PRINT, що входить в частину інструкції IF. Зверніть увагу на ту обставину, що в цьому прикладі використовується підзапит, щоб повернути число рядків (за допомогою агрегатної функції COUNT), що задовольняють умові пропозиції WHERE (ProjectNumber = 'p1').

Оператор ELSE інструкції IF в прикладі містить дві інструкції: PRINT і SELECT. Тому для виконання цих інструкцій їх необхідно укласти в блок між ключовими словами BEGIN і END. (Інструкція PRINT є процедурним розширенням і повертає визначається користувачем повідомлення.)

Інструкція WHILE

Інструкція WHILE виконує одну або кілька ув'язнених в блок інструкцій, протягом часу, поки (WHILE) логічне вираз повертає значення true (істина), тобто дозволяє створити цикл. Іншими словами, якщо вираз повертає true, виконується інструкція або блок інструкцій, після чого знову здійснюється перевірка виразу. Цей процес повторюється до тих пір, поки вираз не повернеться значення FALSE (неправда).

Блок всередині інструкції WHILE може містити одну або дві необов'язкових інструкцій, що застосовуються для управління виконанням інструкцій всередині блоку: BREAK або CONTINUE. Інструкція BREAK зупиняє виконання циклу і починає виконання інструкцій, наступних відразу ж після цього блоку. А інструкція CONTINUE зупиняє виконання тільки поточної ітерації циклу і починає виконувати наступну ітерацію.

У прикладі нижче показано використання інструкції WHILE:

У цьому прикладі бюджетів усіх проектів збільшуються на 10% до тих пір, поки загальна сума бюджетів не перевищить $ 500 000. Але виконання блоку припиниться, навіть якщо загальна сума бюджетів буде менше $ 500 000, якщо тільки бюджет одного з проектів перевищить $ 240. 000.

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

Локальні змінні є важливим процедурним розширенням мови Transact-SQL. Вони застосовуються для зберігання значень будь-якого типу в пакетах і підпрограма. Локальними вони називаються з тієї причини, що вони можуть бути використані тільки в тому пакеті, в якому вони були оголошені. (Компонент Database Engine також підтримує глобальні змінні, які вже були розглянуті раніше.)

Всі локальні змінні пакета оголошуються, використовуючи інструкцію DECLARE. (Синтаксис цієї інструкції наводиться в прикладі нижче) Визначення змінної складається з імені змінної і її типу даних. Імена локальних змінних в пакеті завжди починаються із префікса @. Присвоєння значень локальної змінної здійснюється:

використовуючи спеціальну форму інструкції SELECT;

використовуючи інструкцію SET;

безпосередньо в інструкції DECLARE за допомогою знака = (наприклад, @extra_budget MONEY = 1500).

Використання перших двох способів присвоєння значення локальних змінних показано в прикладі нижче:

Пакет інструкцій в цьому прикладі обчислює середнє значення бюджетів всіх проектів і порівнює отримане значення з бюджетом проекту p1. Якщо бюджет проекту p1 менше середнього значення всіх бюджетів, його значення збільшується на величину значення локальної змінної @extra_budget.

Змішані процедурні інструкції

Процедурні розширення мови Transact-SQL також містять такі інструкції:

Інструкція RETURN виконує ту ж саму функцію всередині пакету, що і інструкція BREAK всередині циклу WHILE. Іншими словами, інструкція RETURN зупиняє виконання пакету і починає виконання першої інструкції, наступної за пакетом.

Інструкція GOTO передає управління при виконанні пакета інструкції Transact-SQL всередині пакету, позначеної маркером. Інструкція RAISEERROR виводить певне користувачем повідомлення про помилку і встановлює прапор системної помилки. Номер помилки в який Ви повідомленні повинен бути більше, ніж 50 000, тому що всі номери помилок менші або рівні 50 000 визначені системою і зарезервовані компонентом Database Engine. Значення номерів помилок зберігаються в глобальній змінній @@ error.

Інструкція WAITFOR визначає затримку на період часу (з параметром DELAY) або певний час (з параметром TIME), протягом якої система повинна чекати, перш ніж виконувати наступну інструкцію пакета. Синтаксис цієї інструкції виглядає наступним чином:

Параметр DELAY вказує системі баз даних очікувати, поки не закінчиться зазначений період часу, а параметр TIME вказує точку в часі, в одному з допустимих форматів, до якої очікувати. Параметр TIMEOUT. за яким слід аргумент timeout, задає період часу в мілісекундах, протягом якого треба чекати прибуття повідомлення в чергу.

Обробка винятків за допомогою інструкцій TRY, CATCH і THROW

Винятком (exception) називається проблема (зазвичай помилка), яка не дозволяє продовжувати виконання програми. Програма, що виконується не може продовжувати виконання через недостатність інформації, необхідної для обробки помилки в даній частині програми. Тому завдання обробки помилки передається іншій частині програми.

Роль інструкції TRY полягає в перехопленні виключення. (Оскільки для реалізації цього процесу зазвичай потрібно кілька інструкцій, то зазвичай застосовується термін "блок TRY", а не "інструкція TRY".) Якщо ж в блоці TRY виникає виняток, компонент системи, який має назву оброблювачем винятків. доставляє це виняток для обробки іншій частині програми. Ця частина програми позначається ключовим словом CATCH і тому називається блоком CATCH.

Обробка винятків з використанням інструкцій TRY і CATCH є загальним методом обробки помилок, що застосовуються у сучасних мовах програмування, таких як C # і Java.

Обробка винятків за допомогою блоків TRY і CATCH надає програмісту безліч переваг, включаючи наступні:

виключення надають акуратний спосіб визначення помилок без захаращення коду додатковими інструкціями;

виключення надають механізм прямої індикації помилок, замість використання будь-яких побічних ознак;

програміст може бачити виключення і перевірити їх в процесі компіляції.

Використання інструкцій TRY, CATCH і THROW для обробки винятків показано в прикладі нижче:

В даному прикладі показано, як для обробки винятків оформляти інструкції в пакет і виконувати відкат результатів виконання всієї групи інструкцій при виникненні помилки. Спроба виконати пакет, показаний в прикладі, буде невдалою, а в результаті буде виведено наступне повідомлення:

Виконання коду в прикладі здійснюється наступним чином. Після успішного виконання першої інструкції INSERT спроба виконання другої інструкції викликає помилку порушення посилальної цілісності. Так як всі три інструкції укладені в блок TRY, виникає виключення для всього блоку і обробник виключень починає виконання блоку CATCH. Виконання коду в цьому блоці здійснює відкат виконання всіх інструкцій в блоці TRY і виводить відповідне повідомлення. Після цього інструкція THROW повертає управління виконанням викликає об'єкту. Внаслідок усього цього вміст таблиці Employee не буде змінена.

Інструкції Transact-SQL - BEGIN TRANSACTION, COMMIT TRANSACTION і ROLLBACK починають, фіксують і виконують відкат транзакцій відповідно. Предмет транзакцій, в загальному, і ці інструкції, зокрема, ми будемо розглядати в одній з наступних статей.