→ Використання курсорів у SQL. Використання курсорів та циклів у Transact-SQL

Використання курсорів у SQL. Використання курсорів та циклів у Transact-SQL

Ребека М. Ріордан "Курсори в Transact-SQL"

Інтернет-університет інформаційних технологій

http://www.INTUIT.ru

Навчальний курс: "Програмування в Microsoft SQL Server 2000"

Курсор – це особливий тимчасовий об'єкт SQL, призначений для використання в програмах і процедурах, що зберігаються. З його допомогою можна в циклі пройти результуючим набором рядків запиту, окремо зчитуючи і обробляючи кожний його рядок. У процедурах, що зберігаються, за допомогою курсорів можна виконувати складні обчислення, які важко виразити за допомогою синтаксису інструкції SELECT. Великий теоретичний матеріал уроку доповнено дуже добрими прикладами. Зокрема, розглядається застосування функції CURSOR_STATUS, опис змінних @@CURSOR_ROWS і @@FETCH_STATUS, та багато іншого.

Ви навчитеся:

  • оголошувати курсор;
  • відкривати курсор;
  • закривати курсор;
  • звільняти курсор;
  • використовувати просту команду FETCH;
  • здійснювати вибірку рядка у змінні;
  • здійснювати вибірку рядка з її абсолютної позиції;
  • здійснювати вибірку рядка щодо її відносної позиції;
  • виконувати позиційну модифікацію;
  • виконувати позиційне видалення;
  • використовувати глобальну змінну @@CURSOR_ROWS для визначення кількості рядків у багатьох курсорах;
  • використовувати глобальну змінну @@FETCH_STATUS для визначення результатів виконання команди FETCH;
  • використовувати CURSOR_STATUS для запиту статусу курсору.

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

Наприклад, оскільки немає можливості вказати на певний рядок у безлічі, подання користувачеві рядків по одному може викликати труднощі. Навіть незважаючи на те, що розширення, що надаються Transact-SQL до стандартної мови SQL, дозволяють реалізувати набагато більші можливості для програмування, проте залишаються операції, які важко, трудомістко або навіть взагалі неможливо виконати на основі принципів роботи з множинами.

Щоб впоратися з подібними ситуаціями, SQL передбачені курсори. Курсор є об'єктом, який вказує на певний рядок у безлічі. Залежно від суті створеного вами курсору, ви можете переміщати курсор всередині множини та модифікувати чи видаляти дані.

Поняття курсорів

Microsoft SQL Server реально підтримує два різних типи курсорів: курсори Transact-SQL та курсори API (курсори програмного інтерфейсу додатків). Курсори API створюються всередині програми, що використовує об'єкти Microsoft ActiveX Data Objects (ADO), OLE DB, ODBC або DB-Library. Кожна з цих API підтримує кілька функціональних можливостей і використовує різний синтаксис. Тут ми не детально обговорюватимемо курсори API; якщо ви плануєте використовувати їх, зверніться до відповідної документації на API та мови програмування, яку ви збираєтеся застосувати.

Курсори Transact-SQL створюються за допомогою DECLARE CURSOR. Як об'єкт курсора, і безліч рядків, яку він вказує, повинні існувати на сервері. Подібні курсори називають серверними курсорами. Якщо ви використовуєте серверний курсор із програми, з'єднаної з SQL Server через мережу, кожна операція з курсором потребує двосторонньої мережної взаємодії. Бібліотеки API-курсорів, що підтримують серверні курсори, також підтримують клієнтський курсор, який існує в клієнтській системі і кешує рядки, які він обробляє на клієнті.

Безліч рядків, на які вказує курсор, визначається за допомогою команди SELECT. При створенні курсору Transact-SQL команду SELECT накладаються кілька обмежень:

команда SELECT не може повертати кілька результуючих множин;

команда SELECT не може містити фразу INTO для створення нової таблиці;

команда SELECT не може містити фразу COMPUTE або COMPUTE BY, які використовуються для агрегування результатів. (Однак вона може містити функції агрегування, наприклад, AVG.)

Характеристики курсорів

Transact-SQL підтримує кілька типів курсорів. З'ясування різних характеристик кожного з курсорів є досить стомлюючим завданням, але її можна полегшити, якщо взяти до уваги для кожного типу курсора три більш менш незалежні характеристики: здатність відображати зміни у вихідних даних, здатність здійснювати прокручування в безлічі рядків, а також здатність модифікувати безліч рядків.

Відображення змін

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

SELECT * FROM Oils WHERE Left(OilName, 1) = "B" База даних Aromatherapy поверне чотири рядки, як показано на рис.1. Якщо в процесі використання вами курсору хтось додасть значення Description для елемента Bergamot, або додасть рядок для елемента Bayberry, що станеться з безліччю рядків, на які вказує курсор?

Мал. 1. База даних Aromatherapy містить чотири рядки, що починаються з літери B.

При створенні вашого курсору можуть бути незалежно визначені два види чутливості: зміни яких рядків включаються до множини (членство множини) та відображення змін у вихідних рядках.

Прокручування

Другий характеристикою курсора є здатність здійснення прокручування як уперед, і назад, або лише вперед. Тут має місце споконвічна для програмування дилема: швидкість проти гнучкості. Послідовні курсори (forward-only) працюють значно швидше, але мають меншу гнучкість.

Оновлення

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

Типи курсорів

Transact-SQL підтримує чотири різні типи курсорів: статичні, ключові, динамічні та курсори швидкого доступу, або "пожежні" (firehose). Кожен тип курсору зберігає різні дані щодо рядків, куди він вказує, крім того, кожному типу курсору властиві різні поєднання характеристик, розглянутих у попередньому розділі.

Статичні курсори

Статичний курсор робить моментальний знімок даних, що задаються оператором SELECT, і зберігає їх у базі даних tempdb. Він "не відчуває" змін у структурі або значеннях даних, а оскільки будь-які модифікації будуть відображені тільки в копії, цей курсор завжди відкривається в режимі "тільки читання". Статичні курсори, однак, можуть бути оголошені як послідовні або прокручуються.

Ключові курсори

Ключовий курсор копіює в базу tempdb тільки стовпці, які унікально ідентифікують кожен рядок. Щоб мати можливість оголосити ключовий курсор, кожна таблиця, що входить у визначення оператора SELECT, повинна мати унікальний індекс, який задає набір, що копіюється - ключ.

Ключові курсори можуть бути модифікованими, так і мати режим "тільки для читання". Вони також можуть бути прокручуються або послідовними.

Членство у ключовому курсорі фіксується на момент оголошення курсору. Якщо в процесі відкритого стану курсору додається рядок, що відповідає умові відбору, вона не буде додана в безліч. У нашому попередньому прикладі, де як умова відбору використовувався LEFT(OilName, 1) = "B", новий рядок зі значенням поля OilName "Bayberry" не буде доданий до рядків, що належать до області дії курсору.

Аналогічно, якщо зміна вноситься в рядок, який після цього не задовольнятиме умові членства в множині, наприклад, заміна "Basil" на "Kumquat", рядок все ж таки залишиться членом множини. Навіть якщо рядок видаляється, він залишається членом множини, але SQL Server повертає NULL для всіх значень стовпців.

Хоча членство в багатьох курсорах після відкриття курсору залишається фіксованим, проте зміни значень даних, що вносяться у вихідні таблиці, знаходять відображення. Наприклад, зміна значення поля Description для рядка Bergamot буде повернуто курсором. Проте зміни значень безлічі ключів відбиваються у курсорах лише тому випадку, якщо вони здійснюються всередині курсора. Протягом попереднього прикладу, якщо значення поля OilName було змінено з "Basil" на "Kumquat" всередині курсору, курсор поверне "Kumquat". Якщо зміна була внесена іншим користувачем, курсор, як і раніше, повертатиме "Basil".

Порада. Як ми побачимо в наступному розділі, створення курсору та відкриття курсору є різними операціями. Щоб оновити вміст ключового курсору, можна закрити та повторно відкрити його.

Динамічні курсори

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

Для динамічних курсорів діє одне обмеження: оператор SELECT, що використовується для визначення курсора, може містити фразу ORDER BY тільки в тому випадку, якщо є індекс, що включає стовпці, що використовуються у фразі ORDER BY. Якщо ви оголошуєте ключовий курсор з використанням фрази ORDER BY, яка не оперує індексом, SQL Server перетворює курсор на ключовий курсор.

Курсори швидкого доступу

SQL Server підтримує спеціальну оптимізовану форму курсора, що не прокручується, що допускає тільки читання. Цей вид курсору оголошується з використанням ключового слова FAST_FORWARD і найчастіше його називають "пожежним" курсором (firehose).

"Пожежні" курсори дуже ефективні, але при їх використанні є два важливі обмеження. По-перше, якщо в операторі визначення SELECT курсора ви використовували стовпці з типом даних text, ntext або image, а також фразу TOP, SQL Server перетворює курсор на ключовий.

По-друге, якщо оператор SELECT, який ви використовували для визначення курсору, містить таблиці, що мають тригери, та таблиці, що не мають тригерів, курсор перетворюється на статичний. Тригери є сценаріями Transact-SQL, які автоматично виконуються сервером при виконанні для таблиці операторів Data Manipulation Language (DML). Докладніше ми розглянемо тригери в уроці 29, зараз звернемо увагу на наступний момент: якщо хтось додає тригер до однієї з таблиць, що використовуються курсором, ваш додаток може раптово зупинити виконання, оскільки SQL Server перетворить швидший курсор на менш швидкий.

Використання курсорів

Використання курсорів подібне до використання локальних змінних – ви оголошуєте їх, встановлюєте значення, а потім використовуєте. Однак, на відміну від локальних змінних, які автоматично знищуються при виході за межі області дії, ви повинні явно звільняти рядки, що використовуються курсором, а потім знищувати курсор.

Створення курсорів

Першим кроком у використанні курсору є його створення. Курсори Transact-SQL створюються за допомогою оператора DECLARE CURSOR.

Увага! SQL Server підтримує два різні методи створення курсорів: з використанням синтаксису SQL-92 та з використанням синтаксису Transact-SQL. Синтаксис SQL-92 відповідає стандарту ANSI, але має менші функціональні можливості, ніж синтаксис Transact-SQL, який розглядається тут.

Оператор DECLARE CURSOR має наступний синтаксис:

DECLARE ім'я_курсора CURSOR

[видимість]

[прокручування]

[блокування]

FOR оператор_вибірки

Зверніть увагу, що всі параметри, що визначають характеристики курсору – видимість, тип і т.д. – є необов'язковими. Значення за промовчанням для цих параметрів є складними і можуть вказувати або не вказувати способи взаємодії з вихідними записами або уявленнями, а також опції роботи з базою даних. Щоб зробити сприйняття оператора зручнішим, краще явно задавати всі необхідні вам параметри. При цьому ви точно знатимете, що отримаєте.

Вигляд курсора визначається за допомогою ключових слів LOCAL або GLOBAL, які мають той же ефект, що і ключові слова @local_table або @@global_table при оголошенні тимчасових таблиць.

Порада. SQL Server закриватиме і звільнятиме локальний курсор при виході за межі його області дії (видимості), але краще завжди робити це явно.

Параметр прокручування дозволяє використовувати ключові слова FORWARD_ONLY і SCROLL, які задають, відповідно, можливість переміщення тільки від початку до кінця, або в будь-якому напрямку.

Параметр тип визначає тип створюваного курсору. Тут допустимі ключові слова STATIC, KEYSET, DYNAMIC та FAST_FORWARD. Параметр типу FAST_FORWARD та параметр прокручування FORWARD_ONLY взаємно виключають.

Параметр блокування визначає, чи можуть рядки модифікуватися курсором, і якщо так, то можуть їх модифікувати інші користувачі. Якщо використовується ключове слово READ_ONLY, курсор не може вносити жодних змін у вихідні дані. Тим не менш, інші користувачі можуть модифікувати дані або це можете робити ви самі за допомогою оператора UPDATE. Якщо в якості параметра блокування встановлено SCROLL_LOCKS, оновлення можуть бути виконані лише курсором. Всі інші оператори UPDATE, як усередині цього ж пакета, так і інші користувачі, що надаються, виконуватися не будуть.

Остання опція блокування, OPTIMISTIC, дозволяє здійснювати оновлення рядків як усередині курсору, так і поза ним. Це найбільш гнучка опція, але при цьому завжди існує ймовірність, що модифікація, виконана курсором, закінчиться невдачею, якщо рядок був змінений після читання курсором.

Параметр TYPE_WARNING пропонує SQL Server відправляти попереджувальне повідомлення клієнту, якщо тип курсору перетворюється від заданого до іншого типу. Це можливо, якщо ви повідомляєте курсор, який не підтримує заданий оператор SELECT.

Параметр оператора_вибірки, що вказується у фразі FOR, є обов'язковим. Він задає рядки, які будуть включені до множини курсору.

Фраза FOR UPDATE є необов'язковою. За замовчуванням курсори модифікуються, якщо не заданий параметр READ_ONLY, однак і в цьому випадку краще все-таки використовувати цю фразу, щоб бути впевненим в отриманому результаті. Ви можете використовувати розділ OF стовпців, щоб вказати певні рядки, для яких ви допускаєте модифікацію. Якщо ви опускаєте розділ OF стовпців, модифікація може бути виконана для всіх стовпців, зазначених в операторі SELECT.

Курсорні змінні

Transact-SQL дозволяє оголошувати змінні типу CURSOR. І тут стандартний синтаксис DECLARE не створює курсор; ви повинні явно встановити змінну курсору за допомогою ключового слова SET.

DECLARE myCursor CURSOR

FOR SELECT OilName FROM Oils

DECLARE @myCursorVariable CURSOR

SET @myCursorVariable = myCursor

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

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

DECLARE @myCursorVariable CURSOR SET @myCursorVariable = CURSOR LOCAL FAST_FORWARD FOR SELECT OilName FROM Oils

При використанні такого синтаксису курсор не має ідентифікатора і посилання на нього може бути здійснене тільки через змінну.

Відкриття курсору

Оголошення курсору створює об'єкт курсору, але не створює набір записів, якими курсор маніпулюватиме (безліч курсора). Безліч курсору не створюється, доки ви не відкриєте курсор. Після досить складного синтаксису оператора DECLARE CURSOR синтаксис оператора здається цілком прозорим:

OPEN курсор_або_змінна

Ключове слово GLOBAL допомагає уникнути конфліктів: якщо курсор, оголошений із ключовим словом LOCAL, та курсор, оголошений із ключовим словом GLOBAL, мають однаковий ідентифікатор, посилання на курсор будуть за замовчуванням віднесені до локального курсора, якщо ви не використовували ключове слово GLOBAL. Як і в інших подібних випадках, краще явно вказувати ключове слово, якщо ви відкриваєте глобальний курсор.

Закриття курсору

Закінчивши використання курсору, ви маєте його закрити. Оператор CLOSE звільняє ресурси, що використовуються для обслуговування безлічі курсорів, а також звільняє всі блокування, накладені на рядки, якщо ви використовували параметр SCROLLOCKS в операторі DECLARE. Синтаксис команди CLOSE майже ідентичний синтаксису оператора OPEN – змінюється лише ключове слово:

CLOSE курсор_або_змінна

Звільнення курсору

Останнім оператором у послідовності дій, пов'язаних із створенням курсору, є оператор DEALLOCATE. Синтаксис його також простий:

DEALLOCATE курсор_або_змінна

Однак тут є одна тонкість: оператор DEALLOCATE видаляє ідентифікатор або змінну курсорну, але він не обов'язково видаляє сам курсор. Сам курсор не видаляється до тих пір, поки всі ідентифікатори, що посилаються на нього, будуть або звільнені, або перестануть діяти (при виході за межі області дії). Розглянемо такі приклади:

Створення курсору DECLARE myCursor CURSOR KEYSET READ_ONLY FOR SELECT * FROM Oils -- Створення курсорної змінної DECLARE @cursorVariable CURSOR -- Створення безлічі записів курсору OPEN myCursor -- Призначення змінної курсору SET @cursorVariable = myCursor

Після звільнення курсору ідентифікатор myCursor більше не асоціюється з безліччю курсору, але оскільки на безліч курсору ще посилається змінна @cursorVariable, курсор і безліч курсору не звільняються. Якщо ви явно не звільните також і курсорну змінну, курсор і безліч курсору будуть існувати, доки змінна не втратить свою дію.

Маніпулювання рядками за допомогою курсору

Курсори власними силами не викликали б жодного інтересу, якби ви могли здійснювати з допомогою певні дії. Transact-SQL підтримує три різні команди для роботи з курсорами: FETCH, UPDATE та DELETE.

Команда FETCH витягує зазначений рядок з множини рядків курсору. У своєму найпростішому варіанті команда FETCH має наступний синтаксис:

FETCH курсор_або_змінна

У цьому форматі запису повертається рядок у позиції курсору (поточний рядок).

Використовуйте просту команду FETCH

  1. Перейдіть до папки SQL 2000 Step by Step у кореневій директорії, виділіть сценарій з ім'ям SimpleCursor і натисніть кнопку Open (Відкрити).
  2. Query Analyzer завантажить сценарій у вікно Query (Запит).

Порада.Можливо, ви звернули увагу на те, що цей сценарій виконується довше, ніж відповідний оператор SELECT. Справа в тому, що створення та відкриття курсору потребує додаткового часу. Ніколи не використовуйте курсор, якщо для виконання завдання достатньо оператора SELECT.

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

FETCH курсор_або_змінна INTO список_змінних

Список_змінних є перелік імен, що розділяються комами, змінних. Перед виконанням команди FETCH ви маєте оголосити змінні. Список змінних повинен містити змінну для кожного стовпця, що фігурує в операторі SELECT, який визначає курсор. Тип змінної повинен або збігатися, або бути сумісним з типом даних стовпця.

Виберіть рядки із записом її у змінні

У всіх попередніх прикладах оператор FETCH використовувався для повернення поточного рядка. Синтаксис оператора FETCH також передбачає низку ключових слів для вказівки іншого рядка. При використанні цих ключових слів оператор FETCH повертатиме заданий рядок і робитиме його поточним.

Ці ключові слова дають можливість задавати абсолютну позицію в багатьох курсорах. Ключові слова FIRST і LAST повертають перший і останній рядки відповідно, у той час як ABSOLUTE n задає рядок, що віддаляється на n рядків від початку (якщо n позитивно) або від кінця (якщо n негативно) множини записів курсору. Значення n може бути виражене як константи (3) чи вигляді змінної (@theRow).

Виберіть рядки з їхньої абсолютної позиції

  1. Перейдіть до сценарію з ім'ям FetchAbsolute і натисніть кнопку Open (Відкрити). Query Analyzer завантажить сценарій у вікно Query (Запит).

Крім ключових слів, що дають можливість витягувати рядки за їх абсолютною позицією, оператор FETCH передбачає три ключові слова, що дозволяють витягувати рядки за їх позицією щодо поточного рядка. Оператор FETCH NEXT повертає наступний рядок, оператор FETCH PRIOR повертає попередній рядок, а оператор FETCH RELATIVE n повертає рядок, віддалений на n рядків від поточного. Подібно до оператора FETCH ABSOLUTE n, оператор FETCH RELATIVE n може задавати рядки, що передують поточному, якщо n негативно, і рядки, що йдуть за поточним, якщо n позитивно.

Виберіть рядки щодо їх відносної позиції

  1. Виділіть сценарій з ім'ям FetchRelative та натисніть кнопку Open (Відкрити). Query Analyzer завантажить сценарій у вікно Query (Запит).

Якщо курсор має тип FORWARD_ONLY або PAST_FORWARD, для вказівки позиції може бути використане лише ключове слово NEXT. Насправді, якщо курсор відноситься до одного з цих типів, ключове слово NEXT не потрібне. SQL Server передбачає, кожен оператор FETCH фактично є оператор FETCH NEXT.

Використовуйте оператор FETCH NEXT для швидкодіючого курсору

  1. Перейдіть до сценарію з ім'ям FetchFirehose і натисніть кнопку Open (Відкрити). Query Analyzer завантажить сценарій у вікно Query (Запит).

Натисніть кнопку Run Query (Виконати запит) на панелі інструментів аналізатора запитів Query Analyzer. Query Analyzer виконає запит.

Модифікація та видалення рядків через курсори

Якщо ваш курсор модифікується. Зміна вихідних значень у багатьох курсорах виконується досить просто. Передбачено спеціальну форму фрази WHERE, яка підтримує модифікацію через курсор:

UPDATE таблиця_або_подання SET список_модифікації WHERE CURRENT OF курсор_або_змінна

Це називається позиційним оновленням. Transact-SQL також підтримує позиційне видалення, яке має таку форму запису:

DELETE таблиця_або_подання WHERE CURRENT OF курсор_або_змінна

Виконайте позиційне оновлення

  1. Перейдіть до сценарію з ім'ям PositionedUpdate і натисніть кнопку Open (Відкрити). Query Analyzer завантажить сценарій у вікно Query (Запит).

Натисніть кнопку Execute Query (Виконати запит) на панелі інструментів аналізатора запитів Query Analyzer. Query Analyzer виконає запит. Зверніть увагу, що з'являються дві панелі сітки. Перша створюється оператором FETCH і містить початковий вміст стовпців. Друга є результатом виконання оператора SELECT та містить значення поля Description після модифікації.

Моніторинг курсорів Transact-SQL

Transact-SQL надає дві глобальні змінні та функцію, які допомагають вам контролювати роботу та стан вашого курсору. Змінна @@CURSOR_ROWS повертає кількість рядків у множині останнього курсору, відкритого в з'єднанні. Значення, що повертаються @@CURSOR_ROWS, представлені у таблиці 1.

Змінна @@FETCH_STATUS повертає інформацію про виконання останньої команди FETCH. У таблиці 2 представлені значення, що повертаються змінною @@FETCH_STATUS.

Нарешті Transact-SQL надає функцію CURSOR_STATUS. Ця функція має наступний синтаксис:

CURSOR_STATUS(тип, курсор_або_змінна) Тип може мати значення "local", "global" або "variable", а курсор_або_змінна - це ідентифікатор курсору або курсорної змінної, інформацію про який потрібно отримати. Результати, що повертаються функцією CURSOR_STATUS, представлені у таблиці 3.

Використовуйте функції моніторингу курсором

  1. Перейдіть до сценарію StatusFunctions і натисніть кнопку Open (Відкрити). Query Analyzer завантажить сценарій у вікно Query (Запит).

Версія для друку

Команда DECLARE CURSOR дозволяє витягувати записи з таблиці для маніпулювання. Це дозволяє проводити рядкову обробку замість традиційної обробки наборами даних, яку здійснює SQL.

У першому наближенні під час роботи з курсором використовуються такі кроки.

Курсор створюється командою DECLARE. Курсор відкривається командою OPEN.

Операції з курсором здійснюються за допомогою команди FETCH. Курсор закривається командою CLOSE.

У команді DECLARE CURSOR вказується інструкція SELECT. Кожен рядок, що повертається інструкцією SELECT, можна отримувати та обробляти індивідуально. У наступному прикладі для Oracle курсор оголошується у блоці оголошень разом із кількома іншими змінними. Після цього в наступному блоці BEGIN ... END курсор відкривається, по ньому проводиться вибірка, і курсор закривається.

CURSOR title_price_cursor IS SELECT title, price FROM titles

WHERE price IS NOT NULL; title_price_val title_price_cursor ROWTYPE; new_price NUMBER(10.2);

OPEN title_price_Cursor;

FETCH title_price_cur-sor INTO title_price_val;

new_price:= "title_price_val.price" * 1.25 INSERT INTO new_title_price VALUES

(title_price_val.title, new_price) CLOSE title_price_cursor; END;

Оскільки в цьому прикладі використовується PL/SQL, більшу частину коду ми в цій книзі не пояснюватимемо. Однак у блоці DECLARE ясно видно оголошення курсору. У блоці PL/SQL, що виконується, курсор ініціалізується командою OPEN, значення витягуються командою FETCH і, нарешті, курсор закривається командою CLOSE.

Інструкція SELECT- це основа курсору, так що гарною практикою є її ретельне тестування перед включенням до інструкції DECLARE CURSOR. Інструкція SELECT може працювати з базовою таблицею або поданням. Тому курсори «тільки для читання» можуть працювати з поновленнями. Інструкція SELECT може містити такі пропозиції, як ORDER BY, GROUP BY та HAVING, якщо ці пропозиції не оновлюють вихідну таблицю. Якщо курсор визначено як FOR UPDATE, рекомендується видаляти такі пропозиції з інструкції SELECT.

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

У наступному простому прикладі для DB2 ми оголосимо курсор, який переглядає номери департаментів, назви департаментів та номери менеджерів у admin_group "ХО1".

DECLARE dept_cursor CURSOR

FOR SELECT dept_nbr, dept_name, mgr_nbr

WHERE admin_group="X01"

ORDER BY d'ept_name ASC, dept_nbr DESC, mgr_nbr DESC;

У наступному прикладі Microsoft SQL Server оголошується і відкривається курсор для таблиці publishers. Курсор відбирає з таблиці publishers перший запис, що відповідає інструкції SELECT, і вставляє її в іншу таблицю. Потім він переходить до наступного запису, потім до наступного - доти, доки всі записи не будуть оброблені. І нарешті, курсор закривається і вивільняє пам'ять (команда DEALLOCATE використовується лише у Microsoft SQL Server).

DECLARE @publisher_name VARCHAR(20)

DECLARE pub_cursor CURSOR FOR SELECT pub_name FROM publishers WHERE country "USA"

FETCH NEXT FROM pub_cursor INTO publisher_name

WHILE @s>FETCH_STATUS=0

INSERT INTO foreign_publishers VALUES(«j>publisher_name)

CLOSE pub_cursor DEALLOCATE pub_cursor

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

У My T-SQL code I в будь-якому випадку використовується set based operations. Я повинен бути такими типами операцій, які SQL Server is designed to process and it should be quicker than serial processing. I know cursors exist but I am not sure how to use them. Can you provide some cursor examples? Can you give any guidance on when to use cursors? I assume Microsoft включає в себе SQL Server для відповіді з тим, що вони повинні бути в місці, де вони можуть бути використані в ефективному manner.

Solution

У кількох кількостях курсори не можуть бути використані, в інших місцях вони існують в останній зоні і в інших групах вони регулярно використовуються. Має місце в особливих circumstances і не в інших. started let's do the following:

  • Look at an example cursor
  • Break down the components of the cursor
  • Provide additional cursor examples
  • Analyze the pros and cons of cursor usage

How to Create a SQL Server Cursor

Створення SQL Server cursor є послідовним процесом, тому що ви усвідомлюєте ці кроки ви можете бути можливим для того, щоб дуплікувати їх з різними параметрами логіки до проміжку часу. Let's walk through the steps:

  1. Перший, ви знайдете ваші параметри, які ви потребуєте в логіці.
  2. У другій половині часу ви знайдете кнопки з конкретним ім'ям, що ви будете використовувати через логічний. Це невдовзі випливає з використанням cursor.
  3. Тридцять, ви збираєтеся записати з cursor до початку процесу процесування.
  4. Fourth, is the data process that is unique to all set of logic. Це можна be inserting, updating, deleting, etc. для кожного ряду даних, які були виконані. Це є важливим набором логіки під час цього процесу, що працює на кожному рівні.
  5. Fifth, you fetch the next record from cursor as you did in step 3 and then step 4 is repeated again by processing the selected data.
  6. Sixth, once all of the data has been processed, then you close cursor.
  7. Як кінець і важливий крок, ви повинні розробити курсор для виконання всіх національних ресурсів SQL Server is holding.

Від того, виходити з прикладу нижче, щоб отримати, щоб дізнатися, коли використовує SQL Server курсори і як це зробити.

Example SQL Server Cursor

Тут є прикладом кнопки з tipу Докладніше про те, щоб завантажити всі SQL Server databases, де backups issued in a serial manner:

DECLARE @name VARCHAR(50) -- database name DECLARE @path VARCHAR(256) -- path for backup files DECLARE @fileName VARCHAR(256) -- filename for backup DECLARE @fileDate VARCHAR(20) -- використаний для file name SET @path = "C:\Backup\" SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112) DECLARE db_cursor CURSOR FOR SELECT name FROM MASTER.dbo.sysdatabases WHERE name NOT IN ("master","model ","msdb","tempdb") OPEN db_cursor FETCH NEXT FROM db_cursor INTO @name WHILE @@FETCH_STATUS = 0 BEGIN SET @fileName = @path + @name + "_" + @fileDate + ".BAK" BACKUP DATABASE @ name TO DISK = @fileName FETCH NEXT FROM db_cursor INTO @name END CLOSE db_cursor DEALLOCATE db_cursor

SQL Server Cursor Components

Базовані на те, що описуються, курсори включають ці компоненти:

  • DECLARE statements - Declare variables використовується в коді блоку
  • SET\SELECT statements - Initialize variables to a specific value
  • DECLARE CURSOR statement - Populate the cursor with values ​​that will be evaluated
    • NOTE - Там є один рядок параметрів в DECLARE CURSOR FOR statement as there are in the SELECT statement. Це може бути 1 або багато variables і поєднаних columns.
  • OPEN statement - Open cursor to begin data processing
  • FETCH NEXT statements - Assign the specific values ​​from the cursor to the variables
    • NOTE - Ця логіка використовується для початкової population перед WHILE statement and then again during each loop in the process as portion of the WHILE statement
  • WHILE statement - Condition to begin and continue data processing
  • BEGIN...END statement - Start and end of the code block
    • NOTE - Базований на поточному процесі процесування BEGIN...END statements can be used
  • Data processing - У цьому викладі, цей логічний backup a database до конкретних файлів і файлів name, але цей звичай може бути протягом одного року DML або адміністративний логічний
  • CLOSE statement - Releases the current data and associated locks, but permits the cursor to be re-opened
  • DEALLOCATE statement - Destroys the cursor

Recommended Reading

Learn more about SQL Server Cursors and alternatives:

Additional SQL Server Cursor Examples

У прикладі вище backups є введені за допомогою cursor, виконати ці інші типи, що рядок cursor-based logic:

  • Script to create commands to disable, enable, drop and recreate Foreign Key constraints in SQL Server

SQL Server Cursor Analysis

Analysis below is intended to service as insight in different scenarios, where cursor-based logic may or may not be beneficial:

  • Online Transaction Processing (OLTP) - в найбільш OLTP середовищах, SET засновані на логічним макетах most sin for short transactions. У нашій команді виконає третю частину застосування, що використовує курсори для всіх його процесів, які мають викликати їх, але це буде бути певним випадком. Typically, SET based logic is more than feasible and cursors are rarely needed.
  • Reporting - Базований на дизайні reports і underlying design, cursors є типово не потрібна. However, наше команда збирається в reporting requirements, де конкретні в'язності не існує на підземному 데이터베이스 і це необхідно для використання cursor, щоб правильно оцінити reporting values. Він мав такий самий досвід, коли потребує введення даних для висхідних процесів, а курсор-базований інструмент був швидкий для розвитку і виконаний в прийнятному manager до потреби.
  • Serialized processing - Якщо ви повинні скористатися процесами в послідовному manner, cursors є viable option.
  • Administrative tasks - Багато administrative tasks потрібна для виконання в серійному manner, який fits nicely в cursor-based logic, але інші системи-базовані об'єкти існують до fulfill the need. У кількох кількох circumstances, курсори є використані для повного процесу.
  • Великі data sets - З великими data sets ви можете пройти в будь-який час або більше з наступного:
    • Cursor базується на логічному не може штурмувати процесування потреб.
    • З великим набором базових операцій на серверах з мінімальним обсягом пам'яті, дані можуть бути захищені або монополісувати SQL Server, який є натисканням часу, може призвести до вмісту і пам'яті. Як такий, a cursor-based approach може заощаджувати.
    • Деякі інструменти вручну реєструвати ваші дані до файлу під заголовками, так що процесування даних в пам'яті може або не може не бути в будь-якому випадку.
    • Якщо дані можуть бути затверджені в штампування SQL Server, вхідних даних до впливу на продуктивність природи є тільки тоді, коли результати завершені. Всі ресурси на сценарійні сервери можуть бути використані для процесів ETL, щоб завершити дані можна імпортувати.
    • SSIS supports batching sets of data which mai resolve the overall need to break-up a large data set in more manageable sizes and perform better that a row by row approach with a cursor.
    • Залежно від того, як курсор або SSIS логічно є кодованим, він може бути можливим для повторного запуску в пункті дії основи на
    • Repeat a batch with the GO command
    Next Steps
    • Якщо ви будете виконані з вашим процесом очікування часу, де ви тримаєте SQL Server cursor usage. Вони можуть або не мають місця в вашому застосуванні або операційних процесах. Там є багато способів, щоб зробити все, щоб використовувати cursor можна було б reasonable alternative or not. You be the judge.
    • Якщо ви збираєтеся в іссуе з іншим кодуванням техніки і потребує отримання деякого часу, використовуючи курсор може бути viable альтернативним. Це може бути довгий час, щоб перевірити ці дані, але терміни очікування миттєво. Якщо ви маєте один-двох процесів або щойно processing, це could do the trick.
    • Якщо курсори є shunned в нашому середовищі, буде очевидно, щоб вибрати інший viable alternative. Just be sure the process will not cause other issues. Як приклад, якщо курсор використовується і мільйони рядків, які будуть затверджені, це потенційно пульсує всі ці дані від cache і cause further contention? Або з великими даними вибираються ваші дані, щоб paged to disk, або написані в temporary directory?
    • Як ви оцінюєте, що курсор-базується на основі проти інших варіантів, щоб зробити певну відповідність технологій в термінах часу, content and resources needed. Hopefully ці factory будуть drive you to the property technique.

застосовується до: SQL Server (починаючи з 2008) База даних SQL AzureСховище даних SQL AzureParallel Data Warehouse

Визначає такі атрибути серверного курсору мови Transact-SQL, як властивості перегляду та запит, який використовується для побудови результуючого набору, на якому працює курсор. Інструкція DECLARE CURSOR підтримує синтаксис стандарту ISO, так і синтаксис, що використовує набір розширень мови Transact-SQL.

ISO Syntax DECLARE cursor_name [ INSENSITIVE ] [ SCROLL ] CURSOR FOR select_statement [ FOR ( READ ONLY | UPDATE [ OF column_name [ ,...n ] ] ) ] ] [ ;] Transact-SQL Extended Syntax DECLARE cursor_name CURSOR [LOCAL | GLOBAL] [FORWARD_ONLY | SCROLL] [STATIC | KEYSET | DYNAMIC | FAST_FORWARD ] [ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ] [ TYPE_WARNING ] FOR select_statement [ FOR UPDATE [ OF column_name [ ,...n ] ] ] [;]

cursor_name
cursor_name

INSENSITIVE
tempdb; таким чином, зміни базових таблиць не відображаються у повернутих вибірками цього курсору даних, і цей курсор не допускає зміни. При використанні синтаксису ISO, якщо не вказано параметр INSENSITIVE, зафіксовані оновлення та видалення, зроблені в базових таблицях, відображаються у наступних вибірках.

SCROLL
Вказує, що всі параметри вибірки (FIRST, LAST, PRIOR, NEXT, RELATIVE, ABSOLUTE) доступні. Якщо в інструкції DECLARE CURSOR стандарту ISO не вказано параметр SCROLL, підтримується лише параметр вибірки NEXT. Параметр SCROLL не може вказуватися разом із параметром FAST_FORWARD.

select_statement
Стандартна інструкція SELECT, яка визначає результуючий набір курсору. Ключові слова FOR BROWSE та INTO неприпустимі в select_statementоголошення курсору.

select_statementконфлікт із курсором запитаного типу.

READ ONLY

Оновлення ]
column_name [, .. .n] вказано, лише перелічені стовпці дозволяють вносити зміни. Якщо інструкція UPDATE використовується без списку стовпців, оновлення може бути можливим для всіх стовпців.

cursor_name
Ім'я Transact-SQL певного серверного курсору. cursor_nameповинні відповідати правилам для ідентифікаторів.

LOCAL
Вказує, що курсор є локальним по відношенню до пакету, процедури, що зберігається, або тригеру, в якому він був створений. Ім'я курсора допустиме лише всередині цієї області. На курсор можуть посилатися локальні змінні пакети, процедури, що зберігаються, тригери або вихідний параметр збереженої процедури. Параметр OUTPUT використовується для передачі локального курсору пакету, що зберігається, процедурі або тригеру, який потім може привласнити параметр змінної курсору з метою подальшого звернення до курсора після завершення збереженої процедури. Курсор неявно звільняється після завершення виконання пакета, процедури, що зберігається, або тригера, за винятком випадку, коли курсор був переданий параметру OUTPUT. Якщо курсор був переданий параметру OUTPUT, то курсор звільняється при звільненні всіх змінних, що посилаються на нього, або при виході з області видимості.

GLOBAL
Вказує, що курсор є глобальним по відношенню до з'єднання. Ім'я курсора може використовуватися будь-якою процедурою, що зберігається, або пакетом, які виконуються в з'єднанні. Курсор неявно звільняється лише у разі розриву з'єднання.

FORWARD_ONLY
Вказує, що курсор може переглядатися лише від першого рядка до останнього. Підтримується лише параметр вибірки FETCH NEXT. Якщо параметр FORWARD_ONLY вказано без ключових слів STATIC, KEYSET або DYNAMIC, то курсор працює як курсор DYNAMIC. Якщо не вказано ні аргументу FORWARD_ONLY, ні аргументу SCROLL, за замовчуванням використовується аргумент FORWARD_ONLY, якщо немає ключових слів STATIC, KEYSET або DYNAMIC. Курсори STATIC, KEYSET та DYNAMIC мають значення за замовчуванням SCROLL. На відміну від таких інтерфейсів API баз даних, як ODBC та ADO, режим FORWARD_ONLY підтримується такими курсорами мови Transact-SQL: STATIC, KEYSET та DYNAMIC.

STATIC
Визначає курсор, який створює тимчасову копію даних для курсором. Всі запити до курсора звертаються до зазначеної тимчасової таблиці tempdb; таким чином, зміни базових таблиць не відображаються у повернутих вибірками цього курсору даних, і цей курсор не допускає зміни.

KEYSET
Вказує, що членство чи порядок рядків курсору незмінні під час його відкритті. Набір ключів, що однозначно визначають рядки, вбудований в таблицю в tempdbназивається ключів.

Зміни неключових значень у базових таблицях, зроблені власником курсору або зафіксовані іншими користувачами, відображаються під час перегляду курсору власником. Зміни, зроблені іншими користувачами, не відображаються (зміни не можуть бути зроблені за допомогою серверного курсору Transact-SQL). Якщо рядок видаляється, спроба вибірки рядків повертає @@FETCH_STATUS -2. Оновлення значень ключа з-за меж курсору аналогічно видаленню старого рядка з наступною вставкою нового рядка. Рядок з новими значеннями не видно і спроби вибірки рядка зі старими значеннями повертають @@FETCH_STATUS -2. Оновлення видно відразу, якщо вони зроблені через курсор за допомогою пропозиції WHERE CURRENT OF.

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

FAST_FORWARD
Вказує курсор FORWARD_ONLY, READ_ONLY, для якого включено оптимізацію продуктивності. Параметр FAST_FORWARD не може вказуватися разом із параметрами SCROLL або FOR_UPDATE.

READ_ONLY
Запобігає змінам, зробленим через цей курсор. Пропозиція WHERE CURRENT OF не може мати посилання курсор в інструкції UPDATE або DELETE. Цей параметр має перевагу над встановленим за умовчанням можливістю оновлення курсору.

SCROLL_LOCKS
Вказує, що позиціоновані оновлення або видалення, які здійснюються за допомогою курсору, гарантовано будуть виконані успішно. SQL Server блокує рядки в міру їх зчитування курсором для забезпечення доступності цих рядків для подальших змін. Параметр SCROLL_LOCKS не може вказуватися разом із параметром FAST_FORWARD або STATIC.

OPTIMISTIC
Вказує, що позиціоновані оновлення або видалення, які здійснюються за допомогою курсору, не будуть виконані, якщо з моменту зчитування в курсор рядок було оновлено. SQL Server не блокує рядки в міру їхнього зчитування в курсор. Натомість використовуються порівняння timestampзначення стовпця або контрольних сум, якщо у таблиці немає timestampстовпець, щоб визначити, чи змінювався рядок після зчитування в курсор. Якщо рядок було змінено, спроби позиціонованого оновлення або видалення будуть безрезультатними. Параметр OPTIMISTIC не може вказуватися разом із параметром FAST_FORWARD.

TYPE_WARNING
Вказує, що клієнту буде відправлено попередження, якщо курсор неявно буде перетворено з одного типу, що запитується, в інший.

select_statement
Стандартна інструкція SELECT, яка визначає результуючий набір курсору. Ключові слова COMPUTE, COMPUTE BY, FOR BROWSE та INTO неприпустимі в select_statementоголошення курсору.

SQL Serverнеявно перетворює курсор на інший тип, якщо пропозиції в select_statementконфлікт із курсором запитаного типу. Для отримання додаткових відомостей див. «Неявні перетворення курсору».

для оновлення ]
Визначає стовпці, що оновлюються в курсорі. If OF column_name [, ... n] надається лише перелічені стовпці дозволяють вносити зміни. Якщо інструкція UPDATE використовується без списку стовпців, то оновлення можливе для всіх стовпців, за винятком випадку, коли вказано параметр паралелізму READ_ONLY.

Інструкція DECLARE CURSOR визначає такі атрибути серверного курсору мови Transact-SQL, як властивості перегляду та запит, що використовується для побудови результуючого набору, на якому працює курсор. Інструкція OPEN заповнює результуючий набір, а оператор FETCH повертає рядок. Інструкція CLOSE очищає поточний набір, пов'язаний з курсором. Інструкція DEALLOCATE звільняє ресурси, які використовуються курсором.

Перша форма інструкції DECLARE CURSOR використовує синтаксис ISO для встановлення параметрів курсору. Друга форма інструкції DECLARE CURSOR використовує розширення мови Transact-SQL, що дозволяють визначати курсори за допомогою таких типів, як типи, що використовуються в курсорних функціях API баз даних, таких як ODBC і ADO.

Не можна змішувати дві ці форми. Якщо вказати SCROLL або без ОБЛІКУ ключові слова перед ключовим словом CURSOR, не можна використовувати ключові слова між курсором, а також для select_statementключові слова. При вказівці ключові слова між КУРСОРА, а також для select_statementключові слова не можна вказати SCROLL або INSENSITIVE перед ключовим словом CURSOR.

Якщо при використанні синтаксису мови Transact-SQL для інструкції DECLARE CURSOR не вказуються параметри READ_ONLY, OPTIMISTIC або SCROLL_LOCKS, то приймається наступне значення за промовчанням.

    Якщо інструкція SELECT не підтримує оновлення (або недостатньо дозволів, або при доступі до віддалених таблиць, що не підтримують оновлення тощо), то курсору надається параметр READ_ONLY.

    Курсори STATIC та FAST_FORWARD за замовчуванням мають значення READ_ONLY.

    Курсори DYNAMIC та KEYSET за замовчуванням мають значення OPTIMISTIC.

Посилання на курсори можуть виконуватися лише іншими інструкціями мови Transact-SQL. Функції API баз даних не можуть посилатися на курсори. Наприклад, після оголошення курсору функції та методи OLE DB, ODBC або ADO не можуть посилатися на його ім'я. Рядки курсору не можуть бути вибрані за допомогою відповідних функцій та методів API; Для цього необхідно використовувати інструкції FETCH мови Transact-SQL.

Наведені нижче процедури, що зберігаються, можуть бути використані для визначення властивостей курсору після його оголошення.

Змінні можуть використовуватись як частина select_statement, У якому оголошено курсор. Значення змінних курсорів після його оголошення не змінюються.

За замовчуванням дозволи DECLARE CURSOR надаються всім користувачам, які мають роздільну здатність SELECT для використовуваних курсором уявлень, таблиць та стовпців.

Не можна використовувати курсори або тригери у таблиці з кластеризованим індексом columnstore. Це обмеження не застосовується до некластеризованих індексів; можна використовувати курсори та тригери у таблиці з некластеризованим індексом columnstore.

A. Використання простого курсору та синтаксису

Результуючий набір, створюваний при відкритті даного курсору, включає всі рядки і стовпці таблиці. Цей курсор можна оновлювати, всі оновлення та видалення представлені у вибірці для цього курсору. FETCH``NEXT доступна лише вибірка, оскільки SCROLL не було вказано параметр.

DECLARE vend_cursor CURSOR FOR SELECT * FROM Purchasing.Vendor OPEN vend_cursor FETCH NEXT FROM vend_cursor;

Б. Використання вкладених курсорів для виведення звіту

У прикладі вкладені курсори використовуються висновку складного звіту. Кожен постачальник оголошує внутрішній курсор.

SET NOCOUNT ON; DECLARE @vendor_id int, @vendor_name nvarchar (50), @ message varchar (80), @product nvarchar (50); PRINT " -------- Vendor Products Report --------"; DECLARE vendor_cursor CURSOR FOR SELECT VendorID, Name FROM Purchasing.Vendor WHERE PreferredVendorStatus = 1 ORDER BY VendorID; OPEN vendor_cursor FETCH NEXT FROM vendor_cursor INTO @vendor_id, @vendor_name WHILE @@FETCH_STATUS = 0 BEGIN PRINT " " SELECT @message = "----- Products From Vendor: "+ @vendor_name PRINT @message -- Declare an inner cursor based -- on vendor_id from the outer cursor. DECLARE product_cursor CURSOR FOR SELECT v.Name FROM Purchasing.ProductVendor pv, Production.Product v WHERE pv.ProductID = v.ProductID AND pv.VendorID = @vendor_id -- Variable value from the outer cursor OPEN product_cursor FETCH NEXT FROM product_cursor INTO @product IF @@FETCH_STATUS<>0 PRINT "<>" WHILE @@FETCH_STATUS = 0 BEGIN SELECT @message = " " + @product PRINT @message FETCH NEXT FROM product_cursor INTO @product END CLOSE product_cursor DEALLOCATE product_cursor -- Get the next vendor. END CLOSE vendor_cursor; DEALLOCATE vendor_cursor;

Сьогодні розглядатимемо дуже багато чого цікавого, наприклад, як запустити вже створену процедуру, яка приймає параметри, масово, тобто. не тільки зі статичними параметрами, а з параметрами, які змінюватимуться, наприклад, на основі якоїсь таблиці, як звичайна функція, і в цьому нам допоможуть якраз курсори та цикли, і як це все реалізувати зараз дивитимемося.

Як Ви зрозуміли, курсори та цикли ми будемо розглядати застосовно до конкретної задачі. А яке завдання зараз розповім.

Існує процедура, яка виконує якісь дії, які не може виконати звичайна функція SQL наприклад, розрахунки та insert на основі цих розрахунків. І Ви її запускаєте, наприклад:

EXEC test_PROCEDURE par1, par2

Тобто Ви запускаєте її тільки з тими параметрами, які були вказані, але якщо Вам необхідно запустити цю процедуру скажімо 100, 200 або ще більше разів, то погодьтеся це не дуже зручно. довго. Було б набагато простіше, якби ми взяли та запускали процедуру як звичайну функцію у запиті select , наприклад:

SELECT my_fun(id) FROM test_table

Тобто функція відпрацює на кожен запис таблиці test_table, але як Ви знаєте процедуру так використовувати не можна. Але існує спосіб, який допоможе нам здійснити задумане, точніше навіть два способи – перший це з використанням курсору та циклу і другий – це просто з використанням циклу, але вже без курсору. Обидва варіанти мають на увазі, що ми створюватимемо додаткову процедуру, яку надалі ми запускатимемо.

Примітка!Всі приклади писатимемо в СУБД MSSql 2008, використовуючи Management Studio. Також усі нижче перелічені дії вимагають необхідних знань у SQL, а точніше у програмуванні на Transact-SQL. Можу порадити для початку ознайомитись з наступним матеріалом:

І так почнемо, і перед тим як писати процедуру, давайте розглянемо вихідні дані нашого прикладу.

Допустимо, є таблиця test_table

CREATE TABLE .( (18, 0) NULL, (50) NULL, (50) NULL) ON GO

До неї необхідно вставляти дані, на основі якихось розрахунків, які виконуватиме процедура my_proc_test, в даному випадку вона просто вставляє дані, але на практиці Ви можете використовувати свою процедуру, яка може виконувати багато розрахунків, тому в нашому випадку саме ця процедура не важлива, вона лише для прикладу. Ну, давайте створимо її:

CREATE PROCEDURE. (@number numeric, @pole1 varchar(50), @pole2 varchar(50)) AS BEGIN INSERT INTO dbo.test_table (number, pole1, pole2) VALUES (@number, @pole1, @pole2) END GO

Вона просто приймає три параметри та вставляє їх у таблицю.

І припустимо цю процедуру, нам необхідно запустити стільки разів, скільки рядків в якійсь таблиці або поданні (VIEWS), тобто запустити її масово для кожного рядка джерела.

І наприклад створимо таке джерело, у нас це буде проста таблиця test_table_vrem, а у Вас це може бути, як я вже сказав своє джерело, наприклад часова таблиця або представлення:

CREATE TABLE .( (18, 0) NULL, (50) NULL, (50) NULL) ON GO

Заповнимо її тестовими даними:

І тепер нашу процедуру необхідно запустити кожного рядка, тобто. тричі з різними параметрами. Як Ви розумієте значення цих полів і є наші параметри, тобто, якби ми запускали нашу процедуру вручну, це виглядало ось так:

exec my_proc_test 1, 'pole1_str1', 'pole2_str1'

І так ще тричі, з відповідними параметрами.

Але нам так не полювання, тому ми напишемо ще одну додаткову процедуру, яка і запускатиме нашу основну процедуру стільки разів, скільки нам потрібно.

Перший варіант.

Використовуємо курсор та цикл у процедурі

Перейдемо відразу до справи та напишемо процедуру ( my_proc_test_all), код я як завжди прокоментував:

CREATE PROCEDURE. AS --оголошуємо змінні DECLARE @number bigint DECLARE @pole1 varchar(50) DECLARE @pole2 varchar(50) --оголошуємо курсор DECLARE my_cur CURSOR FOR SELECT number, pole1, pole2 FROM test_table_vrem --відкриваємо курсор рядки в наші змінні FETCH NEXT FROM my_cur INTO @number, @pole1, @pole2 --якщо дані в курсорі є, то заходимо в цикл --і крутимося там до тих пір, поки не закінчаться рядки в курсорі WHILE @@FETCH_STATUS = 0 BEGIN - на кожну ітерацію циклу запускаємо нашу основну процедуру з потрібними параметрами exec dbo.my_proc_test @number, @pole1, @pole2 -зчитуємо наступний рядок курсору FETCH NEXT FROM my_cur CLOSE my_cur DEALLOCATE my_cur GO

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

До виконання процедури SELECT * FROM test_table --виклик процедури EXEC dbo.my_proc_test_all --після виконання процедури SELECT * FROM test_table

Як бачите, все у нас відпрацювало як слід, тобто процедура my_proc_test спрацювала всі три рази, а ми лише один раз запустили додаткову процедуру.

Другий варіант.

Використовуємо лише цикл у процедурі

Відразу скажу, що тут потрібна нумерація рядків у таблиці, тобто. кожен рядок має бути пронумерований, наприклад 1, 2, 3 таким полем у нас у тимчасовій таблиці служить number.

Пишемо процедуру my_proc_test_all_v2

CREATE PROCEDURE. AS --оголошуємо змінні DECLARE @number bigint DECLARE @pole1 varchar(50) DECLARE @pole2 varchar(50) DECLARE @cnt int DECLARE @i int --дізнаємо кількість рядків у часовій таблиці SELECT @cnt=count(*) FROM test_table_vrem -задаємо початкове значення ідентифікатора SET @i=1 WHILE @cnt >= @i BEGIN --надаємо значення нашим параметрам SELECT @number=number, @pole1= pole1, @pole2=pole2 FROM test_table_vrem WHERE number = @I --на кожну ітерацію циклу запускаємо нашу основну процедуру з потрібними параметрами EXEC dbo.my_proc_test @number, @pole1, @pole2 --збільшуємо крок set @i= @i+1 END GO

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

Очистимо таблицю DELETE test_table --до виконання процедури SELECT * FROM test_table --виклик процедури EXEC dbo.my_proc_test_all_v2 --після виконання процедури SELECT * FROM test_table

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

 

 
Це цікаво: