For select c1, c2 from table1 where c1 between 2 and 4

/*

Виконуємо команду примусового завершення транзакції. Після неї всі оголошені змінні, локальні курсори стають недоступні.

*/

Go

/*

Описуємо змінну типу курсор і ставимо їй у відповідність глобальний курсор cursor1

*/

declare @cursor cursor

set @cursor = cursor1

/*

Відкриваємо курсор.

*/

open @cursor

/*

Витягаємо 1-ий рядок з курсору (використовуючи опцію за замовчуванням NEXT).

*/

fetch @cursor

/*

Оскільки всі описані до виконання команди go змінні більше недоступні, то знову описуємо строкову змінну і поміщаємо в неї ім'я тестової таблиці. Використовуючи цю змінну і команду UPDATE, змінюємо значення стовпця c2 в обраному рядку. Рядок задається за допомогою конструкції WHERE CURRENT OF <ім'я курсору>, що вказує на той рядок таблиці, що є поточним рядком для курсору.

Після зазначеної зміни за допомогою команди SELECT виводимо всі значення стовпців c1, c2, які належать рядкам, що задовольняють наступній умові: значення поля c1 лежать на відрізку [2..4].

*/

declare @var varchar(128)

set @var = 'table1'

execute ('update ' + @var + ' SET c2 = 3 where current of cursor1')

execute ('select c1, c2 from ' + @var + ' where c1 between 2 and 4')

/*

Оскільки курсор динамічний, то він відображає всі зміни, зроблені в базовій таблиці. Таким чином, виконавши наступну команду UPDATE, що змінює значення стовпця c2 у тому рядку, де c2 був рівним 4, ми можемо в цьому переконатися, послідовно витягаючи в циклі WHILE всі рядки з описаного курсору. Цикл працює, поки функція @@FETCH_STATUS видає значення відмінні від -1, тобто рядок, що витягається, не вийшов за межі діапазону. Такий цикл застосуємо, тому що курсор динамічний (значення -2 повернуте не буде).

*/

execute ('update ' + @var + ' SET c2 = 13 where c2 = 4')

while @@FETCH_STATUS <> -1

Begin

fetch next from @cursor

End

/*

Закриваємо курсор.

*/

close @cursor

Приклад 2.

/*

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

*/

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

Deallocate global cursor2

/*

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

*/

Declare cursor2 cursor

Global

Scroll

Keyset

For select c1, c2 from table1 where c1 between 2 and 4

/*

Відкриваємо курсор.

*/

Open cursor2

/*

Оскільки всі описані до виконання команди go змінні більше недоступні, то знову описуємо строкову змінну і 3 допоміжні цілочисельні змінні і поміщаємо в строкову змінну ім'я тестової таблиці.

Використовуючи цю змінну і команду DELETE, видаляємо рядок таблиці, що має значення стовпця c2 = 4.

*/

declare @var varchar(128),

@c1 int,

@c2 int,

@fetch_res int

set @var = 'table1'

execute ('delete ' + @var + ' where c2 = 4')

/*

Цикл, що здійснював у минулому прикладі черговий вибір всіх рядків з курсору, у загальному випадку не дуже вдалий, тому що має наступні недоліки:

1. Потрібне первісне добування рядка перед циклом.

2. Розглядається тільки 1 варіант невдалої вибірки (функція повертає -1). Якщо ж буде спроба вибірки неіснуючого рядка, як у розглянутому випадку, то цей варіант відслідкований не буде, що приведе до виводу значень NULL.

3. Після вибірки останнього рядка буде ще один вибір з курсору, що дасть невдачу (-1), але буде відображений на екрані як заголовок без виведеного рядка.

Для боротьби із цими недоліками цикл був змінений таким чином, що умова продовження роботи циклу стала завжди істинною; вибір з курсору повністю перемістився в цикл і здійснюється в змінні; у випадку невдалого вибору з курсору (@@FETCH_STATUS повертає -1) відбувається вихід із циклу, а якщо вибір рядка з курсору вдалий, то відбувається вивід значень змінних на екран за допомогою команди SELECT.

*/

while (1 = 1)

Begin

fetch next from cursor2 into @c1, @c2

set @fetch_res = @@FETCH_STATUS

if (@fetch_res = -1)

Break;

if (@fetch_res <> -2)

select @c1 as c1, @c2 as c2

End

/*

Закриваємо курсор.

*/

Close cursor2

Завдання

1. Створити таблицю, що містить П.І.Б. (прізвище - повністю, ім'я та по батькові - скорочено) і групу всіх студентів, які можуть стати фахівцями (4 - ий курс, 3<= середній бал <= 4.56). При виконанні завдання використовувати курсори.

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

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

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

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

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

Створення тригерів БД

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

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

Тригер - це збережена процедура, що починає свою роботу у випадку виконання дії, на яку тригер настроєний. Тригери застосовуються для рішення завдань підтримки цілісності (коректності) даних, коли за якимись причинами неможливо (незручно) використовувати обмеження FOREIGN KEY (або обмеження на значення стовпців), і безпеки, коли, наприклад, неприпустимі які-небудь зміни в даних. Тригери бувають декількох типів: DML, DDL і LOGON. DML тригери можуть спрацьовувати при виконанні (після виконання або замість виконання) команд INSERT, UPDATE і DELETE для таблиць або подань. Цей тип тригерів був присутній і в MS SQL Server 2000. DDL тригери можуть спрацьовувати при виконанні, після виконання команд CREATE, ALTER, DROP, GRANT, DENY, REVOKE, UPDATE STATISTICS і деяких системних процедур. LOGON тригери спрацьовують після установки з'єднання з MS SQL Server. DDL і LOGON тригери можна використовувати тільки в MS SQL Server 2005, якщо рівень сумісності для БД установлений у значення 90.

Оператор створення / зміни DML тригера має вигляд:

CREATE TRIGGER [простір_імен.]назва_тригера

ON {назва_таблиці | назва_подання}

[WITH

{ ENCRYPTION |

EXEC[UTE] AS {CALLER | SELF | OWNER | ‘ім'я_користувача’}

}[,]

]

{ FOR [AFTER] | INSTEAD OF}

{[INSERT] [,] [UPDATE] [,] [DELETE]}

[WITH APPEND]

[NOT FOR REPLICATION]

AS

{

Перелік_команд_SQL |

EXTERNAL NAME ім'я_складання.ім'я_класу.назва_методу [;]

}

Слід зазначити, що під терміном «простір_імен» (schema) мається на увазі виділений простір імен, що є по суті «контейнером» для об'єктів. Користувачі можуть володіти схемами, мати права на доступ до схеми, але не є синонімом поняття схеми, на відміну від попередніх версій MS SQL Server. У випадку зміни вже існуючого тригера замість команди CREATE необхідно використовувати команду ALTER, причому при завданні імені тригера використання компоненти простір_імен стає обов'язковим.

Розглянемо загальний вид створення / зміни DDL і LOGON тригерів:

DDL тригер: