Все про курсором

За своєю суттю курсор є покажчиком на один рядок даних Для проходження по набору даних до його кінця зазвичай використовують цикл while SQL Server підтримує стандартний синтаксис ANSI SQL-92 і розширений синтаксис Т-SQL, що пропонує додаткові можливості

Пять етапів життя курсора

Курсор створює результуючий набір даних на основі інструкції SELECT, а потім проходить по ньому порядково Пятьма етапами життя курсора є наступні

1 Оголошення курсору визначає тип і режим його роботи, а також описує інструкцію SELECT, яка постачає йому дані При оголошенні курсора ніякі дані не витягуються Це єдиний випадок, коли інструкція declare не вимагає амперсанда Курсор SQL-92 оголошується за допомогою інструкції cursor for:

DECLARE імя_курсора CURSOR FOR інструкція_БЕЬЕСТ FOR параметри_курсора

Розширений курсор T-SQL оголошується аналогічним чином:

DECLARE імя_курсopa CURSOR параметри_курсора FOR інструкція_5ЕЬЕСТ

2 При відкритті курсору витягуються дані, якими він і заповнюється:

OPEN імя_курсора

3 Курсор переміщається до наступного рядка і заповнює значеннями її стовпців локальні змінні (ці змінні повинні бути попередньо оголошені): FETCH [напрямок] імя_курсора INTO @ переменная1, @ переменная2

За замовчуванням команда FETCH переміщує курсор до наступного рядка (напрямок NEXT), однак при бажанні можна перемістити курсор до попередньої (PRIOR), першим (FIRST) і останньої (LAST) рядку Також за допомогою цієї команди можна перемістити курсор до рядка з деяким абсолютним номером (ABSOLUTE п) або змістити щодо поточної позиції на певну відстань (RELATIVE п) Проблема останнього підходу полягає в тому, що в реляційній базі даних номер рядка не має певного сенсу Якщо в коді потрібно перейти до конкретної рядку, щоб отримати логічний результат, то це має бути заздалегідь передбачено в моделі бази даних

4 Закриття курсору знімає блокування даних, але зберігає інструкцію SELECT Курсор згодом може бути відкритий в тій же точці:

CLOSE імя_курсора

5 Демонтаж курсора вивільняє відведену під нього память і анулює його визначення:

DEALLOCATE імя_курсора

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

Управління курсором

Так як курсор проходить по набору даних порядково, код Т-SQL вимагає команд постійного зміщення курсору Для управління цим циклічним процесом мову Т-SQL пропонує дві глобальні змінні, що містять інформацію про стан курсору

Мінлива @ @ cursor_rows повертає загальна кількість рядків у курсорі Якщо курсор заповнюється асинхронно, то в цієї змінної буде міститися негативне значення

Суттєвою для управління курсором є глобальна змінна @ @ fetch_ status Вона звітує про стан курсору після останньої команди FETCH Це стан важливо для управління переміщенням курсора та оцінки, чи досяг він одного з кінців набору даних Можливі значення змінної @ @ f etch_status свідчать про наступне

■ 0 – остання операція FETCH успішно витягла рядок

■ 1 – остання операція FETCH досягла кінця набору даних

■ 2 – рядок, до якої перемістився курсор, виявилася недоступною вона була вилучена

Комбінування змінної @ @ f etch_status з оператором while дозволяє створити цикли, що дозволяють успішно переміщатися по рядках набору даних

Зазвичай в пакетах створюється курсор, після чого виконується перша команда FETCH і починається цикл while, який триває доти, поки не буде досягнутий кінець набору даних У верхній частині циклу перевіряється значення змінної @ @ fetch_status для визначення, чи створений курсор У наступному прикладі продемонстровані всі пять етапів життя курсора і керування ітеративним процесом його переміщення за допомогою циклу while і змінної @ @ f etch_status:

– Етап 1

DECLARE cDetail CURSOR FAST_FORWARD

FOR SELECT DetaillD

FROM Detail

WHERE AdjAmount IS NULL – Етап 2 OPEN cDetail – Етап 3

FETCH cDetail INTO @cDetailID

EXEC CalcAdjAmount

@DetailID = @cDetailID,

@AdjustedAmount = @SprocResult OUTPUT

UPDATE Detail

SET AdjAmount = @SprocResult WHERE DetaillD = @cDetailID

WHILE @@Fetch_Status = &lt&gt1 BEGIN BEGIN

EXEC CalcAdjAmount

@DetailID = @cDetailID,

@AdjustedAmount = @SprocResult OUTPUT UPDATE Detail

SET AdjAmount = @SprocResult WHERE DetaillD = @cDetailID

END

– Ітеративний прохід по курсору

FETCH cDetail INTO @cDetailID — fetch next

END

– Етап 4 CLOSE cDetail – Етап 5

DEALLOCATE cDetail

Оновлення курсора

Оскільки курсор послідовно проходить по набору даних, SQL Server знає, яка рядок є поточною Покажчик курсору може використовуватися в пропозиціях WHERE інструкцій SQL DML (SELECT, INSERT, UPDATE і DELETE), щоб маніпулювати коректними даними

Параметр FOR UPDATE інструкції DECLARE дозволяє виконувати оновлення за допомогою курсору При цьому якщо явно вказані будь стовпці, саме вони будуть оновлені якщо ніяких стовпців не вказано, будуть оновлені всі

DECLARE cDetail CURSOR FOR SELECT DetaillD FROM Detail WHERE AdjAmount IS NULL FOR UPDATE OF AdjAmount

У тілі циклу, після того, як було виконано переміщення до потрібної рядку, інструкція DML може включити курсор в пропозицію WHERE, використовуючи синтаксис CURRENT OF У наступному прикладі, взятому з сценарію KilltheCursor sql, виконується посилання на курсор cDetail:

UPDATE Detail

SET AdjAmount = @SprocResult WHERE CURRENT OF cDetail

Область визначення курсору

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

DECLARE імя_курсора CURSOR Local або Global FOR інструкція SELECT

За замовчуванням областю визначення курсору є вся база даних Це вказується в параметрі CURSOR_DEFAULT:

ALTER DATABASE Family SET CURSOR_DEFAULT LOCAL

Для успішного виконання процедур важливо знати поточну область визначення курсору Це виконується за допомогою функції DATABASEPROPERTYEX ():

SELECT DATABASEPROPERTYEX(Family, 1IsLocalCursorsDefault)

Результат виконання функції наступний:

1

Крім параметрів global і for update курсори мають ще кілька, керуючих можливістю переміщення та оновлення даних, – static, keyset, dynamic і optimistic Мені не хочеться витрачати місце в книзі на опис цих параметрів, так як кращою стратегією таки залишається максимальне обмеження сфери дії курсорів і перехід на пакетні рішення

Курсори та транзакції

У порівнянні з пакетними рішеннями курсори практично не впливають на блокування Це можна аргументувати тим, що пакетне рішення блокує таблицю в цілому Водночас курсор блокує лише конкретну рядок, з якою працює З цієї причини, незважаючи на те що робота курсора з таблицею в мільйон рядків триватиме в пятнадцять разів довше, принаймні, він не буде блокувати інші транзакції Ви самі можете вирішити, чи вступати в дебати з цього питання

Компроміс полягає у використанні функції RowNumber () з пакетним рішенням для оновлення великих груп рядків

Однією з методик, яка часто використовується для підвищення продуктивності курсорів, є обєднання всього курсора в одну логічну транзакцію У цьому рішенні є свої за і проти Незважаючи на то що продуктивність підвищується приблизно на 50%, за це доводиться розплачуватися блокуванням, установлюваної транзакцією

Джерело: Нільсен, Пол Microsoft SQL Server 2005 Біблія користувача : Пер з англ – М: ООО ІД Вільямс , 2008 – 1232 с : Ил – Парал тит англ

Схожі статті:


Сподобалася стаття? Ви можете залишити відгук або підписатися на RSS , щоб автоматично отримувати інформацію про нові статтях.

Коментарів поки що немає.

Ваш отзыв

Поділ на параграфи відбувається автоматично, адреса електронної пошти ніколи не буде опублікований, допустимий HTML: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

*

*