FROM (SELECT c1, c2 FROM table1) as t1

PIVOT( avg(c2) FOR c1

IN([1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12], [13])) as tt1

Результати роботи команди:

Якщо цю множину рядків потрібно було б відразу записати в таблицю, то це можна було б зробити за допомогою такої команди SELECT:

SELECT * INTO PVT FROM

(

SELECT 'AverC2' as C1_VAL, [1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12], [13]

FROM (SELECT c1, c2 FROM table1) as t1

PIVOT( avg(c2) FOR c1

IN([1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12], [13])) as tt1

) as tt2

Для демонстрації можливостей оператора UNPIVOT для таблиці PVT одержимо традиційну (у вигляді набору рядків) розбивки на групи, що не має значень NULL. Цього можна досягти наступною командою SELECT:

SELECT c1, c2 from (SELECT * FROM pvt) as t1

UNPIVOT( c2 FOR c1

IN([1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12], [13])) as tt1

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

Приклад 6.

Використання секцій WHERE, GROUP BY і HAVING.

Розглянемо приклад найпростішої команди SELECT, що формує групи на підставі стовпця c1, причому для кожної групи формується середнє значення по полю c2:

SELECT c1, avg(c2) as AVG_C2 FROM table1 WHERE c1 <> 2 GROUP BY ALL c1

Результат виконання команди:

Оскільки використана опція ALL, то з'являється рядок зі значеннями (2, NULL), що відсівається секцією WHERE. Однак, якщо модифікувати комнаду, додавши в неї таку секцію HAVING:

SELECT c1, avg(c2) as AVG_C2 FROM table1 WHERE c1 <> 2 GROUP BY ALL c1

HAVING c1 <> 2

то рядок (2, NULL) пропаде з результуючої множини.

Розглянемо приклад використання опції WITH CUBE:

SELECT c1, c2 FROM table1 GROUP BY c1, c2 WITH CUBE

Результат виконання команди буде наступним:

Тепер розглянемо приклад використання оператора GROUP BY c опцією WITH ROLLUP:

SELECT c1, c2 FROM table1 GROUP BY c1, c2 WITH ROLLUP

Результат виконання команди буде наступним:

Приклад 7.

Горизонтальні об'єднання.

UNION:

SELECT * FROM table1 WHERE c2 <=2

UNION

SELECT * FROM table2 WHERE c2 > 3

Результат:

EXCEPT:

SELECT * FROM table1 WHERE c2 = 2

EXCEPT

SELECT * FROM table2 WHERE c2 = 2

Результат:

INTERSECT:

SELECT * FROM table1 WHERE c2 = 2

INTERSECT

SELECT * FROM table2 WHERE c2 = 2

Результат:

Приклад 8.

Використання секції COMPUTE.

SELECT * FROM table1 WHERE c1 <= 6 ORDER BY c1, c2, c3 COMPUTE avg(c1), sum(c1)

Результат:

Тепер розглянемо приклад команди SELECT секцією COMPUTE ... BY:

SELECT * FROM table1 WHERE c1 <= 6 ORDER BY c1, c2, c3

COMPUTE avg(c1), sum(c1) BY c1

Результат:

Приклад 9.

Секція WITH і застосування common table expressions (CTE).

Найпростіший приклад використання CTE (CTE виступає в ролі звичайного подання):

WITH cte_1

AS

(

SELECT c1, c2 FROM table1

INTERSECT

SELECT c1, c2 FROM table2

)

SELECT * FROM cte_1 WHERE c1 < 6

Результат запиту:

Приклад використання рекурсивного CTE:

WITH cte_2(c1, c2, cc1, cc2, cnt)

AS

(

SELECT DISTINCT c1, c2, c1, c2, 0 FROM table1 where c2 = 2

UNION

SELECT DISTINCT c1, c2, c1, c2, 0 FROM table2 where c2 = 4

UNION ALL

SELECT t1.c1, t1.c2, cte_2.c1, cte_2.c2, cte_2.cnt + 1 FROM table1 t1, cte_2

WHERE cte_2.c2 < 10 and cte_2.c2 = t1.c2 - 1

)

SELECT * FROM cte_2

Результат:

Мета цього запиту – показати кількість рекурсивних виконань команди SELECT при формуванні CTE (поле cnt). Рекурсивна частина CTE формується за принципом: поле c2 таблиці table1 на 1 більше, ніж поле cc2 CTE з ім'ям cte_2.

4. Розробити SQL запити, які виконують наступні дії:

· Додати обмеження для значень, що вводяться, у поле форма навчання з таблиці groups з використанням команди ALTER TABLE.

· Вивести на екран П.І.Б. (прізвище - повністю, ім'я та по батькові - скорочено) всіх студентів, що живуть у гуртожитку.

· Вивести на екран П.І.Б. (прізвище - повністю, ім'я та по батькові - скорочено) всіх військовозобов'язаних студентів (чоловіка, 18<= вік < 25, придатні до військової служби).

· Вивести на екран П.І.Б. (прізвище - повністю, ім'я та по батькові - скорочено) і № курсу всіх студентів, які вчаться на спеціальності KS.

· Вивести на екран П.І.Б. (прізвище - повністю, ім'я та по батькові - скорочено) і групу всіх студентів, що мають друковані праці (статті та тези), а також назви цих друкованих праць і їх тип (стаття або тези).

· Вивести перелік предметів і оцінки по них для зазначеного студента (студент вибирається по номеру залікової).

· Використовуючи common table expressions одержати наступні дані: назва предмета, код спеціальності, номер курсу.

Лабораторна робота № 5

Ітеративна обробка результату запита

ітеративно

Ціль роботи:познайомитися з поняттям «курсор» мови SQL, типами курсорів, вивчити процес створення й застосування курсорів.Розглянути приклади використання курсорів при створенні SQL запитів.

Теоретична частина

У реляційних БД операції виконуються над наборами рядків, які можуть бути отримані за допомогою команди SELECT. Іноді виникає необхідність зберегти отриманий набір для його наступної обробки. Для цих цілей при програмуванні мовою TSQL можна використовувати курсори, які дозволяють зберігати набори рядків та ітеративно витягати з нього зазначені рядки. Такий підхід не вимагає багаторазового виконання однієї і тієї ж команди SELECT, що приводить до підвищення ефективності програм.

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

1. Оголошення курсору за допомогою DECLARE або DECLARE і SET.

2. Відкриття курсору (OPEN).

3. Добування даних з курсору (FETCH).

4. Закриття курсору (CLOSE).

5. Видалення посилання на курсор. (DEALLOCATE). Може застосовуватися без CLOSE.

Розглянемо етапи схеми роботи з курсором.

DECLARE

У синтаксисі мови TSQL курсор описується так:

DECLARE cursor_name CURSOR

[LOCAL | GLOBAL]

[FORWARD_ONLY | SCROLL]

[STATIC | KEYSET | DYNAMIC | FAST_FORWARD]

[READ_ONLY | SCROLL_LOCKS | OPTIMISTIC]

[TYPE_WARNING]

FOR <команда SELECT>

[FOR UPDATE [OF список_імен_стовпців_розділених_комами]]

1. [LOCAL | GLOBAL] - необов'язкова складова опису, що показує область дії (видимості) курсору: LOCAL - для пакета, процедури; GLOBAL - для з'єднання в цілому. Значення за замовчуванням визначається опцією використовуваної БД default to local cursor, що, як правило, установлена в FALSE.

2. [FORWARD_ONLY | SCROLL] - необов'язкова складова опису, що показує порядок добування даних з курсору. FORWARD_ONLY - припускає послідовне добування всіх рядків курсору від 1-ого до останнього (FETCH NEXT). SCROLL - припускає можливість використання всіх 6 способів добування (FETCH FIRST, FETCH LAST, FETCH PRIOR, FETCH NEXT, FETCH RELATIVE, FETCH ABSOLUTE) рядків з курсору.

3. [STATIC | KEYSET | DYNAMIC | FAST_FORWARD] - необов'язкова складова опису, що визначає властивості курсору по зв'язаності з вихідною таблицею (таблицями).

· STATIC - описує курсор, при створенні якого робиться тимчасова копія даних, що містяться в курсорі.

· KEYSET - визначає послідовність рядків, що належать курсору, що фіксується під час відкриття курсору. При цьому множини ключів, що однозначно визначають кожний рядок курсору, зберігаються в тимчасовий БД tempdb. Зміна неключових значень у вихідній таблиці (таблицях) відображається для курсору. Вставка нових рядків у вихідну таблицю (таблиці)- не відображається для курсору. Зміна ключових значень у вихідній таблиці (таблицях) не відображається для курсору, а добування вилученого рядка з курсору приводить до помилки (функція @@FETCH_STATUS повертає значення -2). Однак, зміни зроблені з використанням конструкції WHERE CURRENT OF відображаються для курсору.

· DYNAMIC - описує курсор, що відображає всі зміни у вихідних таблицях. Послідовність рядків, приналежність до курсору також може мінятися для кожної чергової вибірки даних з курсору. При використанні цієї опції добування даних за допомогою команди FETCH ABSOLUTE неприпустимо.

· FAST_FORWARD - курсор, що має властивості FORWARD_ONLY і READ_ONLY і використовує якусь оптимізацію при роботі. Не сумісно з FOR UPDATE, з FORWARD_ONLY і з OPTIMISTIC.

Слід зазначити, що:

· Якщо зазначено властивість FORWARD_ONLY, і не обрана жодна із властивостей [STATIC | KEYSET | DYNAMIC | FAST_FORWARD], то за замовчуванням вибирається властивість DYNAMIC.

· Якщо зазначено властивість SCROLL, і не обрана жодна із властивостей [STATIC | KEYSET | DYNAMIC | FAST_FORWARD], то за замовчуванням вибирається властивість KEYSET.

· Якщо не зазначена жодна із властивостей [FORWARD_ONLY | SCROLL] і зазначено одну із властивостей [STATIC | KEYSET | DYNAMIC], то вибирається SCROLL, інакше - FORWARD_ONLY.

· Якщо не зазначена жодна із властивостей [FORWARD_ONLY | SCROLL] і [STATIC | KEYSET | DYNAMIC | FAST_FORWARD], то за замовчуванням вибирається FORWARD_ONLY і DYNAMIC, відповідно.

· Якщо запит SELECT містить рекурсивне CTE, то курсор може мати тільки властивості (тип) STATIC або FAST_FORWARD. Якщо це не так, то тип курсору буде автоматично перетворений в STATIC.

4. [READ_ONLY | SCROLL_LOCKS | OPTIMISTIC] - необов'язкова складова опису, що визначає поводження курсору при відновленні даних з його використанням (конструкція WHERE CURRENT OF для команд UPDATE і DELETE). Значення за замовчуванням OPTIMICTIC, якщо не зазначена властивість STATIC або FAST_FORWARD, інакше - READ_ONLY.

· READ_ONLY - забороняє зміну даних вихідної таблиці (таблиць) з використанням курсору. Не сумісно з [FOR UPDATE [OF список_імен_стовпців_розділених_комами]].

· SCROLL_LOCKS - гарантує успішність зміни даних вихідної таблиці (таблиць) з використанням курсору.

· OPTIMISTIC - гарантує успішність зміни даних вихідної таблиці (таблиць) з використанням курсору, якщо рядок був змінений після створення курсору.

5. [TYPE_WARNING] - необов'язковаа тридцятилітній опису, що вказує на необхідність повідомлення клієнтові, що створив курсор, у випадку неявного перетворення типу курсору.

6. FOR <команда SELECT> - обов'язкова складова опису, що визначає запит SELECT, що наповнює курсор даними. У цьому запиті SELECT не припустимі секції COMPUTE, COMPUTE BY, FOR BROWSE, INTO.

7. [FOR UPDATE [OF список_імен_стовпців_розділених_комами]] - необов'язкова складова опису, що задає список стовпців, які можуть бути змінені через курсор у конструкції WHERE CURRENT OF. Якщо зазначено тільки FOR UPDATE, то, використовуючи курсор, можна міняти всі стовпці вихідної таблиці (таблиць). Якщо є присутнім [OF список_імен_стовпців_розділених_комами], то через курсор можна міняти тільки ті стовпці, які присутні в списку після слова OF. При використанні опцій SCROLL_LOCKS або OPTIMISTIC необов'язково використовувати FOR UPDATE без вказівки списку стовпців, тому що це передбачається за замовчуванням.

Можна також описати змінну типу курсор у такий спосіб:

DECLARE @cursor_var cursor

а надалі за допомогою команди SET привласнити цій змінній вже описаний курсор або описати курсор прямо в команді SET.

OPEN

Команда OPEN служить для відкриття курсору, а також для наповнення його даними (виконання відповідного запиту SELECT). Вона має наступний синтаксис:

OPEN {[GLOBAL] <ім'я курсору> | @cursor_variable},

де ключове слово GLOBAL показує, що курсор з іменем <ім'я курсору> є глобальним.

FETCH

Команда FETCH служить для добування рядка з курсору і має наступний синтаксис:

FETCH

[

[NEXT | PRIOR | FIRST | LAST |

ABSOLUTE {номер рядка | @var} |

RELATIVE {номер рядка | @var}

]

FROM

]

{[GLOBAL] <ім'я курсору> | @cursor_variable}

[INTO @var1, @var2, ...]

1. [NEXT | PRIOR | FIRST | LAST | ABSOLUTE {номер рядка | @var} | RELATIVE {номер рядка | @var}] - необов'язкова частина команди, що визначає рядок, що витягається з курсору, (якщо використовується, то обов'язково використовувати ключове слово FROM):

· NEXT - витягти наступний рядок після поточного і зробити його поточним. Якщо курсор тільки відкритий, то витягає 1-ий рядок і він же стає поточним. Ця опція є опцією за замовчуванням.

· PRIOR - витягти попередній рядок перед поточним і зробити його поточним. Якщо курсор тільки відкритий, то рядок не витягає.

· FIRST - витягти 1-ий рядок курсору і зробити його поточним.

· LAST - витягти останній рядок курсору і зробити його поточним.

· ABSOLUTE {номер рядка | @var} - після ключового слова ABSOLUTE вказується або номер строки, що витягається, або змінна, в якій міститься цей номер. Якщо номер позитивний, то відлік рядка ведеться від початку курсору, якщо негативний - з кінця. Якщо номер = 0, то рядок не витягає. Якщо рядок витягає, то він стає поточним.

· RELATIVE {номер рядка | @var} - після ключового слова RELATIVE вказується або номер строки, що витягається, або змінна, у якій міститься цей номер. Якщо номер позитивний, то відлік рядка ведеться від поточного рядка курсору вниз, якщо негативний - від поточного рядка курсору вгору. Якщо номер = 0, то витягає поточний рядок. Якщо при першому після відкриття виконанні команди FETCH вказується негативний номер або 0, то рядок не витягає. Якщо рядок витягає, то він стає поточним.

2. {[GLOBAL] <ім'я курсору> | @cursor_variable} - задає курсор за допомогою ім'я курсору або змінної. Ключове слово GLOBAL показує, що курсор є глобальним.

3. [INTO @var1, @var2, ...] - необов'язкова частина команди, що дозволяє виводити значення елементів строки, що витягається, не на екран, а в змінні. Кількість і типи даних змінних повинні збігатися з кількістю і типами даних стовпців, що витягаються.

CLOSE

Закриває зазначений відкритий курсор, звільняючи всі блокування даних зроблені курсором і обрану курсором множину рядків (після виконання цієї команди звертання до курсору неприпустимо, якщо не виконати повторне відкриття). Однак після виконання команди OPEN із закритим курсором знову можна працювати (витягати дані). Команда має наступний синтаксис:

CLOSE {[GLOBAL] <ім'я курсору> | @cursor_variable},

де ключове слово GLOBAL показує, що курсор з іменем <ім'я курсору> є глобальним.

DEALLOCATE

Видаляє посилання на зазначений курсор. Коли останнє посилання на курсор видаляється, то це приводить до видалення всіх структур даних, використовуваних курсором. Може застосовуватися без команди CLOSE. Ця команда має наступний синтаксис:

DEALLOCATE {[GLOBAL] <ім'я курсору> | @cursor_variable},

де ключове слово GLOBAL показує, що курсор з іменем <ім'я курсору> є глобальним.

5.6. Функції роботи з курсорами

1. @@CURSOR_ROWS - повертає кількість рядків, що містяться в останньому відкритому в поточному з'єднанні курсорі. Може повернути такі значення:

· деяке від’ємне число. Показує, що цей курсор наповнюється даними асинхронно. Може мати місце для більших KEYSET або STATIC курсорів.

· -1. Повертається для динамічного курсору (DYNAMIC). Оскільки цей курсор відображає всі зміни у вихідних даних, то кількість рядків, що містяться в ньому, невідомо.

· 0. Курсор ще не відкритий, уже закритий або жоден рядок для нього не обраний.

· Деяке позитивне число. Кількість рядків, витягнутих у курсор.

2. CURSOR_STATUS({{'local' | 'global'}, 'ім'я курсору' | 'variable', '@cursor_var'}) - повертає стан курсору. Має 2 строкових аргументи: 1-ий має значення 'local' або 'global' для курсорів, заданих ім'ям ( 2-ий параметр) або 'variable' - для курсорів заданих змінною ( 2-ий параметр). Може повертати наступні значення:

· 1. Показує, що цей курсор відкритий і має хоча б 1 рядок (для DYNAMIC курсорів 0 або більше рядків).

· 0. Курсор відкритий, але не містить рядків. Це значення не може бути повернуте для DYNAMIC курсорів.

· -1. Курсор закритий.

· -2. Може бути повернуто, якщо курсор заданий змінною. Означає, що курсор не пов'язаний зі змінною.

· -3. Такого курсору або змінної не існує або змінна існує, але не пов'язана з курсором.

3. @@FETCH_STATUS - повертає результат виконання останньої команди FETCH (ціле число):

· 0. Команда FETCH виконана успішно.

· -1. Команда FETCH не могла бути виконана успішно або заданий рядок виходить за межі наявної множини рядків.

· -2. Рядок, що витягається, відсутній.

5.7. Приклади використання курсорів

Всі приклади роботи з курсорами будуть побудовані з використанням таблиці table1, що створена в такий спосіб:

Declare

@var varchar(128)

set @var = 'table1'

if exists (select table_name from information_schema.tables where table_name = @var)

execute ('drop table ' + @var)

execute ('create table ' + @var + ' ( c1 int, c2 int, c3 int primary key identity ) ')

execute ('insert into ' + @var + '(c1, c2) values (1, 2)')

execute ('insert into ' + @var + '(c1, c2) values (1, 2)')

execute ('insert into ' + @var + '(c1, c2) values (1, 2)')

execute ('insert into ' + @var + '(c1, c2) values (1, 2)')

execute ('insert into ' + @var + '(c1, c2) values (2, 5)')

execute ('insert into ' + @var + '(c1, c2) values (6, 5)')

execute ('insert into ' + @var + '(c1, c2) values (2, 4)')

execute ('insert into ' + @var + '(c1, c2) values (2, 3)')

Go

Приклад 1.

/*

Перевіряємо існування описуваного курсору за допомогою функції cursor_status. Якщо курсор існує, то виконуємо стосовно нього команду DEALLOCATE

*/

if (cursor_status('global', 'cursor1') <> -3)

Deallocate global cursor1

/*

Створюємо глобальний курсор, що має властивості FORWARD_ONLY, DYNAMIC, OPTIMISTIC, обрані за замовчуванням. Відповідний запит SELECT вибирає значення стовпців c1, c2 з тих рядків, у яких поле c1 лежить на відрізку [2..4].

*/

Declare cursor1 cursor

Global