Лекція 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.

Приклад.

Результат виконання:
Етап 1
Етап 2
Виникла помилка
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 при створенні процедури.