Лекція 13. Оператори керування ходом виконання та збережувані процедури в мові SQL
1. Оператори керування ходом виконання
- IF ELSE
- CASE
- GOTO
- WHILE
- WAITFOR
- TRY/CATCH
1. Умовний оператор IF.
IF < умова > < SQL-оператор >
[ ELSE < SQL-оператор > ]
Для позначення блоку SQL-операторів використовують конструкцію
BEGIN
…
END
Приклад. Вивести інформацію про кількість стрічок у таблицях PC та Laptop.
USE Labor_SQL
DECLARE @C int
SELECT @C = COUNT(*) FROM PC
IF @C > 0
BEGIN
PRINT 'таблиця PC'
PRINT CAST(@C AS NCHAR) + 'стрічок'
END
ELSE
PRINT 'таблиця PC пуста'
SELECT @C = COUNT(*) FROM Laptop
IF EXISTS (SELECT * FROM Laptop)
BEGIN
PRINT 'таблиця Laptop'
PRINT CAST(@C AS NCHAR) + 'стрічок'
END
ELSE
PRINT 'таблиця Laptop пуста'
2. Оператор WHILE.
WHILE <умова>
<SQL-оператор> | [BEGIN
<блок SQL-операторів>
[BREAK]
<блок SQL-операторів>
[CONTINUE]
END]
Оператор BREAK дає можливість вийти з циклу, не чекаючи поки буде виконаний прохід до кінця циклу та відбудеться повторна перевірка умови.
Оператор CONTINUE завершує прохід та передає керування на виконання наступного етапу циклу.
3. Оператор WAITFOR.
WAITFOR
DELAY <'час'> | TIME <'час'>
Параметр DELAY вказує проміжок часу очікування. Максимальна затримка 24 години.
WAITFOR DELAY '01:00' -- затримка на 1 годину
Параметр TIME забезпечує очікування до конкретного часу доби. Дозволяється вказувати час лише у 24-годинному форматі.
WAITFOR TIME '14:00' -- очікування до другої години дня
4. Оператор GOTO.
мітка:
GOTO мітка
Переводить виконання на мітку.
DECLARE @value int
SELECT @value = MIN(speed) FROM PC
IF @value < 0 GOTO label1
IF @value = 0 GOTO label2
IF @value > 0 GOTO label3
label1:
...
label2:
...
label3:
...
5. Оператор RETURN.
RETURN Завершує виконання роботи пакета.
6. Блок TRY/CATCH.
Блок оброблення помилок мовою T-SQL за своєю функціональністю подібних до блоків оброблення виключних ситуацій мовою С++.
BEGIN TRY
<SQL-оператори>
END TRY BEGIN CATCH
[< SQL-оператори >]
END CATCH
Блок CATCH повинен слідувати одразу після блоку TRY, присутність будь-яких операторів між END TRY та BEGIN CATCH є недопустимою.
Рівні серйозності помилок:
0-10: Інформаційні повідомлення або несерйозні помилки.
11-16: Помилки, що можуть бути виправлені користувачем. 11 – об’єкт не існує; 13 – взаємоблокування транзакцій; 14 – помилки, що пов’язані з безпекою; 15 – синтаксичні помилки.
17-19: Програмні помилки, що не можуть бути виправлені користувачем. 17 – нехватка ресурсів.
20-25: Системні проблеми та критичні помилки. Виконання сценарію одразу ж припиняється та розривається зв’язок. 22 – таблиця чи індекс пошкоджені; 24 – несправність носія.
* Системні повідомлення про помилки містяться в таблиці sys.messages.
Конструкція TRY … CATCH перехоплює всі помилки виконання з рівнем серйозності від 11 до 19. При цьому виконання в блоці TRY припиняється, та одразу ж виконуються SQL-оператори блоку CATCH. Якщо помилка з рівнем серйозності 11 та вище не виявлена в блоці TRY, то блок CATCH пропускається та продовжується виконуватися SQL-код після END CATCH.
Приклад.
CREATE
DATABASE Base2
DECLARE @value int
SET @value = 7
BEGIN TRY
PRINT 'етап 1'
CREATE DATABASE Base1
PRINT 'етап 2'
IF @value = 7
CREATE DATABASE Base2
PRINT 'етап 3'
CREATE DATABASE Base3
END TRY
BEGIN CATCH
PRINT 'Виникла помилка'
END CATCH
Створяться бази даних Base1 та Base2. При спробі повторно створити Base2 згенерується помилка та передається керування блоку CATCH. Конструкції TRY … CATCH можуть бути вкладеними, наприклад у блоці CATCH можна обробляти помилки для SQL-операторів, що там виконуються.
BEGIN TRY
…
BEGIN TRY
PRINT 'Hello'
END TRY
BEGIN CATCH
END CATCH
END TRY
BEGIN CATCH
…
BEGIN TRY
PRINT 'Goodbye'
END TRY
BEGIN CATCH
END CATCH
END CATCH
Зауваження: оператор GOTO не може використовуватися для входу в блоки TRY чи CATCH, а лише для переходу до мітки усередині блоку TRY чи CATCH, або для виходу з цих блоків назовні.
Отримання інформації про помилку
В області блоку CATCH можемо отримати інформацію про помилку в блоці TRY за допомогою таких системних функцій:
ERROR_NUMBER() Повертає номер помилки.
ERROR_SEVERITY() Повертає рівень серйозності помилки. ERROR_PROCEDURE() Повертає ім’я збережуваної процедури чи тригера, в якому трапилася помилка. ERROR_LINE() Повертає номер стрічки підпрограми, в якій трапилася помилка.
ERROR_MESSAGE() Повертає текст повідомлення про помилку.
Зауваження: за межами блоку CATCH ці функції повертають NULL.
Приклад.
CREATE DATABASE Base
BEGIN TRY
CREATE DATABASE Base
END TRY
BEGIN CATCH
SELECT ERROR_NUMBER(), ERROR_SEVERITY(), ERROR_MESSAGE()
SELECT * FROM sys.message
WHERE message_id = ERROR_NUMBER()
END CATCH
|
|
|
1801 |
16 |
Database 'Base' already exists |
message_id |
language_id |
severity |
is_event_logged |
text |
1801 |
1033 |
16 |
0 |
Datebase ‘%.*Is’ already exists |
2. Збережувані процедури
Збережувана процедура – це поіменована збережена сукупність SQL-операторів, яка може приймати та повертати параметри. Такі процедури діють на рівні конкретної БД та призначені для багатократного використання.
CREATE PROC | PROCEDURE <ім’я процедури>
[@<ім’я параметра 1> <тип даних> [=<знач. за замовч.>] [OUT | OUTPUT]
[,@<ім’я параметра 2> … ]]
[WITH {RECOMPILE | ENCRYPTION | RECOMPILE, ENCRYPTION}]
AS
<SQL-код>
Спрощення:
PROC від PROCEDURE; OUT від OUTPUT.
Приклад: процедура для вибірки усіх даних з таблиці PC (без параметрів)
USE Labor_SQL
GO
CREATE PROCEDURE SelectPC
AS
BEGIN
SET NOCOUNT ON -- накладає заборону на вивід к-сті стрічок
SELECT * FROM PC
END
GO
EXEC SelectPC -- виклик процедури
SELECT * FROM PC
-- видалення процедури
-- DROP PROCEDURE SelectPC
Результати: двічі виведеться вміст таблиці PC.
Повідомлення: виведе лише один раз про кількість стрічок. (стрічок опрацьовано 11)
Зауваження:
1) Будь-які системні налаштування за допомогою оператора SET вводяться в дію лише на час виконання процедури; за межами процедури приймаються назад попередні налаштування.
2) Рівень вкладеності процедур (коли одна процедура викликає іншу) може досягати 32.
3) Виклик процедури на виконання здійснюється за допомогою ключового слова EXEC (EXECUTE).
4) Конструкцію CREATE PROCEDURE не можна поєднувати з іншими конструкціями в межах одного пакета.
Приклад2: використання параметрів.
-- Окремий сценарій для створення процедури
USE Labor_SQL
GO
CREATE PROCEDURE ProductMaker
@model varchar(10),
@maker varchar(50) OUTPUT,
@maker2 varchar(50) OUTPUT,
@maker3 varchar(50) OUTPUT 111
AS
BEGIN
SET @maker2 = @maker
SELECT @maker = maker FROM Product
WHERE model = @model
SET @maker3 = @maker
END
GO
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
Сценарій з використанням процедури
USE Labor_SQL
GO
DECLARE @mm varchar(50)
DECLARE @mm2 varchar(50)
DECLARE @mm3 varchar(50)
SET @mm = 'w'
EXEC ProductMaker 1401, @mm, @mm2 OUT, @mm3 OUT
SELECT @mm AS mm, @mm2 AS mm2, @mm3 AS mm3
Результат:
Зауваження:
1) Щоб параметр повертав значення з процедури, OUTPUT має також вказуватися і при її виклику; при відсутності OUTPUT після значення параметра, останній лише приймає значення, але не повертає нічого.
2) Для передачі значень параметрам процедури у довільному порядку необхідно вказувати імена параметрів, яким вони присвоюються.
EXEC ProductMaker @maker3 = @mm3 OUT, @maker2 = @mm2 OUT, @maker = @mm, @model = 1401
Результат буде ідентичний попередньому.
3) Кожна процедура повертає цілочисельне значення, яке визначається або неявно, або явно за допомогою оператора RETURN.
Неявно:
Додамо до попереднього сценарію такі три стрічки
DECLARE @ret int
EXEC @ret = ProductMaker 1401, @mm, @mm2, @mm3
SELECT @ret AS ReturnProcedure
ReturnProcedure |
0 |
Примітка: якщо оператор RETURN не використовується в процедурі, то вона завжди повертає 0.
Явно:
USE Labor_SQL
GO
CREATE PROCEDURE Pr
@par nchar(1),
AS
BEGIN
IF @par BETWEEN 'A' AND 'Z' RETURN 1
ELSE RETURN -1
CREATE DATABASE DDD -- ніколи не створиться!
END
GO
-- Виконання процедур
DECLARE @ret int
EXEC @ret = Pr 'Vova' -- довші значення обрізуються до 'V'
SELECT @ret
-- -- -- -- -- -- -- -- -- --
EXEC @ret = Pr 'Z'
SELECT @ret
-- -- -- -- -- -- -- -- -- --
EXEC @ret = Pr '+'
SELECT @ret
Результати:
Опції
RECOMPILE Вказує, що не потрібно зберігати план виконання, компіляція буде виконуватися при кожному виконанні.
ENCRYPTION Текст процедури шифрується.
Переваги процедур
1) Реалізація у вигляді об'єктів БД.
2) Забезпечення захисту.
3) Підвищення продуктивності.
1. Процедуру не потрібно підвантажувати кожного разу із файлу вона вже зберігається як окремий об'єкт БД. Процедури можуть викликати інші процедури аж на глибину до 32 рівня. Процедури можуть багатократно викликатися, що робить код сценарію меншим та покращує його читання.
2. Процедури дають можливість користувачам отримувати набір стрічок з таблиці, навіть не маючи доступу до таблиці. Тобто, користувач може виконувати лише ті дії, що передбачені в процедурі.
3. Підвищення продуктивності процедури досягається за рахунок кешування її плану виконання. Під час створення процедури виконується синтаксичний аналіз коду процедури. При цьому потрібно враховувати, що в операторі CREATE PROCEDURE може використовуватися так зване відкладене перетворення імен, тобто можна посилатися на об'єкти, які ще не створені. Після першого виклику на виконання процедури здійснюється її оптимізація, після чого в системі компілюється та кешується план виконання. Цей кешований план використовується у наступних викликах процедури. Варто зазначити, що оптимізація виконується або перед першим викликом процедури на виконання, або після обновлення статистичних даних, що відносяться до таблиці, яка використовується в процедурі.
Зниження продуктивності: якщо запит є динамічний (формується безпосередньо перед викликом на виконання), то оптимізація виконується для першого виклику процедури. У наступні рази динамічно сформується інший запит і при цьому буде використовуватися неправильний план виконання запиту. Це ж стосується при використанні операторів IF … ELSE для відбору необхідного запиту. У цих випадках слід використовувати опцію RECOMPILE при створенні процедури.