Курсоры ms sql: Курсоры в Mysql. / Хабр

Содержание

НОУ ИНТУИТ | Лекция | Курсоры: принципы работы

Аннотация: Дается определение курсора. Приводится описание его типов и
поведения: статические, динамические, последовательные и ключевые
курсоры. Описываются принципы управления курсором: создание и
открытие курсора, считывание данных, закрытие курсора. Приводятся
примеры программирования курсора.

Понятие курсора

Запрос к реляционной базе данных обычно возвращает несколько рядов
(записей) данных, но приложение за один раз обрабатывает лишь одну
запись. Даже если оно имеет дело одновременно с несколькими рядами
(например, выводит данные в форме электронных таблиц), их количество
по-прежнему ограничено. Кроме того, при модификации, удалении или
добавлении данных рабочей единицей является ряд. В этой ситуации на
первый план выступает концепция курсора, и в таком контексте курсор –
указатель на ряд.

Курсор в SQL – это область в памяти базы данных, которая
предназначена для хранения последнего оператора SQL. Если текущий
оператор – запрос к базе данных, в памяти сохраняется и строка данных
запроса, называемая текущим значением, или текущей строкой курсора.
Указанная область в памяти поименована и доступна для прикладных
программ.

Обычно курсоры используются для выбора из базы данных некоторого
подмножества хранимой в ней информации. В каждый момент времени
прикладной программой может быть проверена одна строка курсора. Курсоры часто применяются в операторах SQL, встроенных в написанные
на языках процедурного типа прикладные программы. Некоторые из них
неявно создаются сервером базы данных, в то время как другие
определяются программистами.

В соответствии со стандартом SQL при работе с курсорами можно
выделить следующие основные действия:

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

В разных реализациях определение курсора может иметь некоторые
отличия. Так, например, иногда разработчик должен явным образом
освободить выделяемую для курсора память. После освобождения курсора
ассоциированная с ним память также освобождается. При этом становится
возможным повторное использование его имени. В других реализациях при закрытии курсора освобождение памяти происходит неявным образом.
Сразу после восстановления она становится доступной для других
операций: открытие другого курсора и т.д.

В некоторых случаях применение курсора неизбежно. Однако по
возможности этого следует избегать и работать со стандартными
командами обработки данных: SELECT, UPDATE, INSERT, DELETE. Помимо
того, что курсоры не позволяют проводить операции изменения над всем
объемом данных, скорость выполнения операций обработки данных
посредством курсора заметно ниже, чем у стандартных средств SQL.

Реализация курсоров в среде MS SQL Server

SQL Server поддерживает три вида курсоров:

курсоры SQL применяются в основном внутри триггеров, хранимых
процедур и сценариев;
курсоры сервера действуют на сервере и реализуют программный
интерфейс приложений для ODBC, OLE DB, DB_Library;
курсоры клиента реализуются на самом клиенте. Они выбирают весь
результирующий набор строк из сервера и сохраняют его локально, что
позволяет ускорить операции обработки данных за счет снижения потерь
времени на выполнение сетевых операций.

Различные типы многопользовательских приложений требуют и различных
типов организации параллельного доступа к данным. Некоторым
приложениям необходим немедленный доступ к информации об изменениях в
базе данных. Это характерно для систем резервирования билетов. В
других случаях, например, в системах статистической отчетности, важна
стабильность данных, ведь если они постоянно модифицируются,
программы не смогут эффективно отображать информацию. Различным
приложениям нужны разные реализации курсоров.

В среде SQL Server типы курсоров различаются по предоставляемым
возможностям. Тип курсора определяется на стадии его создания и не
может быть изменен. Некоторые типы курсоров могут обнаруживать изменения, сделанные другими пользователями в строках, включенных в
результирующий набор. Однако SQL Server отслеживает изменения таких
строк только на стадии обращения к строке и не позволяет
отслеживать изменения, когда строка уже считана.

Курсоры делятся на две категории: последовательные и прокручиваемые. Последовательные позволяют выбирать данные только в одном направлении
– от начала к концу. Прокручиваемые же курсоры предоставляют большую
свободу действий – допускается перемещение в обоих направлениях и
переход к произвольной строке результирующего набора курсора.Если
программа способна модифицировать данные, на которые указывает курсор, он называется прокручиваемым и модифицируемым. Говоря о курсорах, не следует забывать об изолированности транзакций. Когда
один пользователь модифицирует запись, другой читает ее при помощи
собственного курсора, более того, он может модифицировать ту же
запись, что делает необходимым соблюдение целостности данных.

SQL Server поддерживает курсоры статические, динамические, последовательные и управляемые набором ключей.

В схеме со статическим курсором информация читается из базы данных
один раз и хранится в виде моментального снимка (по состоянию на
некоторый момент времени), поэтому изменения, внесенные в базу данных
другим пользователем, не видны. На время открытия курсора сервер
устанавливает блокировку на все строки, включенные в его полный
результирующий набор. Статический курсор не изменяется после создания
и всегда отображает тот набор данных, который существовал на момент
его открытия.

Если другие пользователи изменят в исходной таблице включенные в курсор данные, это никак не повлияет на статический курсор.

В статический курсор внести изменения невозможно, поэтому он всегда
открывается в режиме «только для чтения».

Динамический курсор поддерживает данные в «живом» состоянии, но это
требует затрат сетевых и программных ресурсов. При использовании динамических курсоров не создается полная копия исходных данных, а
выполняется динамическая выборка из исходных таблиц только при
обращении пользователя к тем или иным данным. На время выборки сервер
блокирует строки, а все изменения, вносимые пользователем в полный
результирующий набор курсора, будут видны в курсоре. Однако если
другой пользователь внес изменения уже после выборки данных курсором,
то они не отразятся в курсоре .

Курсор, управляемый набором ключей, находится посередине между этими
крайностями. Записи идентифицируются на момент выборки, и тем самым
отслеживаются изменения . Такой тип курсора полезен при реализации
прокрутки назад – тогда добавления и удаления рядов не видны, пока
информация не обновится, а драйвер выбирает новую версию записи, если
в нее были внесены изменения.

Последовательные курсоры не разрешают выполнять выборку данных в
обратном направлении. Пользователь может выбирать строки только от
начала к концу курсора . Последовательный курсор не хранит набор всех
строк. Они считываются из базы данных, как только выбираются в курсоре, что позволяет динамически отражать все изменения, вносимые
пользователями в базу данных с помощью команд INSERT, UPDATE, DELETE.
В курсоре видно самое последнее состояние данных.

Статические курсоры обеспечивают стабильный взгляд на данные. Они
хороши для систем «складирования» информации: приложений для систем
отчетности или для статистических и аналитических целей. Кроме того, статический курсор лучше других справляется с выборкой большого
количества данных. Напротив, в системах электронных покупок или
резервирования билетов необходимо динамическое восприятие обновляемой
информации по мере внесения изменений. В таких случаях используется динамический курсор. В этих приложениях объем передаваемых данных,
как правило, невелик, а доступ к ним осуществляется на уровне рядов
(отдельных записей). Групповой доступ встречается очень редко.

T-SQL синтаксис курсоров

С курсорами связано множество команд и функций, они приведены в табл. 13.2. В
следующем разделе мы рассмотрим эти команды подробнее.

Таблица 13.2. Синтаксис Transact-SQL для работы с курсорами

Команда или функция	Предназначение
DECLARE CURSOR	Объявляет курсор
OPEN	Открывает курсор, чтобы можно было получать из него данные
FETCH	Выбирает одну запись из курсора
CLOSE	Закрывает курсор, оставляя внутренние структуры, связанные с ним
DEALLOCATE	Освобождает внутренние структуры курсора
@@CURSOR_ROWS	Возвращает количество записей в курсоре
@@FETCH_STATUS	Определяет, была ли удачна или неудачна последняя команда FETCH
CURSOR_STATUS()	Возвращает информацию о статусе курсора или курсорной переменной

DECLARE CURSOR

DECLARE CURSOR объявляет курсор. Есть две основные версии команды DECLARE
CURSOR — совместимый с ANSI /ISO SQL 92 синтаксис и расширенный синтаксис
Transact-SQL. Синтаксис ANSI /ISO выглядит так:

DECLARE name [INSENSITIVE][SCROLL] CURSOR

FOR select

[FOR {READ ONLY | UPDATE [OF column [,…n]]}]

А расширенный синтаксис Transact-SQL так:

DECLARE name CURSOR

[LOCAL | GLOBAL]

[FORWARD_ONLY | SCROLL]

[STATIC | KEYSET | DYNAMIC | FAST_FORWARD]

[READ_ONLY | SCROLL_LOCKS | OPTIMISTIC]

[TYPE_WARNING]

FOR select

[FOR {READ ONLY | UPDATE [OF column [,…n]]}]

Компонента select команды — это обыкновенный оператор SELECT, который
определяет, какие записи возвращает курсор. В нем нельзя использовать ключевые
слова COMPUTE [BY], FOR BROWSE или INTO. Компонента select влияет на то,
будет ли курсор открыт только для чтения. Например, если вы включите предложение
FOR UPDATE, но укажете select, которая по существу запрещает изменения
(например, включает GROUP BY или DISTINCT), ваш курсор будет неявно преобразован
в курсор только для чтения (или статический). Сервер преобразует курсоры к
статическим, не обновляемым по своей сути. Этот тип автоматического
преобразования известен как неявное преобразование курсоров (implicit
cursor conversions). Существует несколько критериев, которые влияют на
неявное преобразование курсоров; за более подробной информацией обратитесь к
Books Online. Для возможности изменения курсора вы не обязаны указывать FOR
UPDATE явно, если сам по себе запрос SELECT является изменяемым. И еще раз, если
не указано иначе, то будет ли курсор изменяемым, определяется характеристиками
оператора SELECT. Вот пример:

CREATE TABLE #temp (k1 int identity, c1 int NULL)

INSERT #temp DEFAULT VALUES

DECLARE c CURSOR

FOR SELECT k1, c1 FROM #temp

OPEN c

FETCH c

UPDATE #temp

SET c1=2

WHERE CURRENT OF c

SELECT * FROM #temp

CLOSE c

DEALLOCATE c

DROP TABLE #temp

k1 c1

———— ————

1 NULL

k1 c1

———— ————

1 2

2 NULL

3 NULL

4 NULL

Даже притом, что курсор не объявлен как изменяемый, он является изменяемым на
основании того факта, что его оператор SELECT изменяемый — то есть сервер может
преобразовать изменение курсора в изменение соответствующей записи таблицы. Если
вы укажете предложение FOR UPDATE и включите список столбцов, то столбцы,
которые вы изменяете, должны быть указаны в этом списке. Если вы попытаетесь
изменить столбец, которого нет в списке с помощью предложения WHERE CURRENT OF
оператора UPDATE, SQL Server отклонит изменения и сгенерирует сообщение об
ошибке. Вот пример:

CREATE TABLE #temp (k1 int identity, c1 int NULL, c2 int NULL)

INSERT #temp DEFAULT VALUES

DECLARE c CURSOR

FOR SELECT k1, c1, c2 FROM #temp

FOR UPDATE OF c1

OPEN c

FETCH c

— Плохой T-SQL — Этот UPDATE пытается изменить столбец, которого нет в списке
FOR UPDATE OF

UPDATE #temp

SET c2=2

WHERE CURRENT OF c

k1 c1 c2

———- ———— —————

1 NULL NULL

Server: Msg 16932, Level 16, State 1, Line 18

The cursor has a FOR UPDATE list and the requested column to be updated is not

in this list.

The statement has been terminated.

Если select ссылается на переменную, переменная вычисляется, когда курсор
объявляется, а не когда открывается. Это существенно, так как вы должны
присваивать значения переменным до объявления курсора, который их использует. Вы
не можете сначала объявить курсор, затем присвоить значение переменной, от
которой он зависит, и рассчитывать, что курсор будет работать правильно. Вот
пример:

— В случае, если курсор остался от предыдущего примера

DEALLOCATE c

DROP TABLE #temp

CREATE TABLE #temp (k1 int identity, c1 int NULL)

INSERT #temp DEFAULT VALUES

DECLARE @k1 int

DECLARE c CURSOR

FOR SELECT k1, c1 FROM #temp WHERE k1<@k1 — Не будет работать — @k1 здесь
равно NULL

SET @k1=3 — Это надо переместить перед DECLARE CURSOR

OPEN c

FETCH c

UPDATE #temp

SET c1=2

WHERE CURRENT OF c

SELECT * FROM #temp

CLOSE c

DEALLOCATE c

DROP TABLE #temp

k1 c1

———— ————

Server: Msg 16930, Level 16, State 1, Line 18

The requested row is not in the fetch buffer.

The statement has been terminated.

k1 c1

———— ————

1 NULL

2 NULL

3 NULL

4 NULL

Глобальные и локальные курсоры

Глобальные курсоры видимы вне пакета, хранимой процедуры или триггера,
создавшего его, и существуют до тех пор, пока явно не будут освобождены или пока
соединение, создавшее его. Локальный курсор видим только программному модулю,
который его создал, если только курсор не возвращен с помощью выходного
параметра. Локальные курсоры неявно освобождаются, когда выходят из области
видимости. Для совместимости с предыдущими версиями, SQL Server по умолчанию
создает глобальные курсоры, но вы можете отменить поведение по умолчанию, явно
указав ключевое слово GLOBAL или LOCAL, когда объявляете курсор. Заметьте, что
вы можете иметь глобальные и локальные курсоры с одинаковыми именами, хотя это
довольно сомнительная практика кодирования. Например, этот код выполняется без
ошибки:

DECLARE Darryl CURSOR — My brother Darryl

LOCAL

FOR SELECT stor_id, title_id, qty FROM sales

DECLARE Darryl CURSOR — My other brother Darryl

GLOBAL

FOR SELECT au_lname, au_fname FROM authors

OPEN GLOBAL Darryl

OPEN Darryl

FETCH GLOBAL Darryl

FETCH Darryl

CLOSE GLOBAL Darryl

CLOSE Darryl

DEALLOCATE GLOBAL Darryl

DEALLOCATE Darryl

au_lname au_fname

—————————————- ———————

White Johnson

stor_id title_id qty

———- ———— ———

6380 BU1032 5

Мы можете изменить, будет ли SQL Server создавать глобальные курсоры, если не
указана область видимости, с помощью системной хранимой процедуры sp_dboption
(смотрите следующий раздел «Конфигурирование курсоров» за более подробной
информацией).

OPEN

OPEN делает записи курсора доступными с помощью FETCH. Если курсор INSENSITIVE
или STATIC, OPEN копирует все результирующее множество во временную таблицу.
Если это KEYSET-курсор, OPEN копирует множество уникальных значений (или все
множество потенциальных ключей, если не существует уникального ключа) во
временную таблицу. В OPEN можно указать область видимости курсора, если включить
опциональное ключевое слово GLOBAL. Если существуют и локальный и глобальный
курсор с одинаковым именем (вы должны по возможности избегать этого), применяйте
GLOBAL, чтобы указать курсор, который вы хотите открыть. (Опция базы данных
default to local cursor определяет, получите ли вы глобальный или локальный
курсор, когда ни то, ни другое явно не указано. Для более подробной информации
смотрите следующий раздел, посвященный конфигурированию курсоров).

Используйте автоматическую переменную @@CURSOR_ROWS, чтобы определить, сколько
записей в курсоре. Вот простой пример OPEN:

CREATE TABLE #temp (k1 int identity PRIMARY KEY, c1 int NULL)

INSERT #temp DEFAULT VALUES

DECLARE GlobalCursor CURSOR STATIC — Объявляем глобальный курсор

GLOBAL

FOR SELECT k1, c1 FROM #temp

DECLARE LocalCursor CURSOR STATIC — Объявляем локальный курсор

LOCAL

FOR SELECT k1, c1 FROM #temp WHERE k1<4 — Возвращает только три записи

OPEN GLOBAL GlobalCursor

SELECT @@CURSOR_ROWS AS NumberOfGLOBALCursorRows

OPEN LocalCursor

SELECT @@CURSOR_ROWS AS NumberOfLOCALCursorRows

CLOSE GLOBAL GlobalCursor

DEALLOCATE GLOBAL GlobalCursor

CLOSE LocalCursor

DEALLOCATE LocalCursor

DROP TABLE #temp

NumberOfGLOBALCursorRows

————————

NumberOfLOCALCursorRows

————————

Для динамических курсоров @@CURSOR_ROWS возвращает –1, так как добавление новых
записей может в любое время изменить количество записей, возвращенных курсором.
Если курсор заполняется асинхронно, (смотрите раздел «Конфигурирование
курсоров»), @@CURSOR_ROWS возвращает отрицательное значение, абсолютное значение
которого показывает, сколько записей в настоящий момент в курсоре.

FETCH

FETCH — способ, с помощью которого вы получаете данные из курсора. Можете
считать его специальным оператором SELECT, который возвращает только одну запись
из предопределенного результирующего множества. Обычно FETCH вызывается в цикле,
который использует @@FETCH_STATUS в качестве контролирующей переменной, каждый
удачный вызов FETCH возвращает следующую запись курсора.

Курсоры с возможностью прокрутки (DYNAMIC, STATIC и KEYSET-курсоры, или те,
которые объявлены с опцией SCROLL) позволяют FETCH получать не только следующие
записи курсора. В дополнение к получению следующей записи прокручиваемые курсоры
позволяют с помощью FETCH получить предыдущую запись, первую запись, последнюю
запись, запись по ее номеру, и запись относительно текущей. Вот простой пример:

SET NOCOUNT ON

CREATE TABLE #cursortest (k1 int identity)

INSERT #cursortest DEFAULT VALUES

DECLARE c CURSOR SCROLL

FOR SELECT * FROM #cursortest

OPEN c

FETCH c — Получаем первую запись

FETCH ABSOLUTE 4 FROM c — Получаем 4-ю запись

FETCH RELATIVE -1 FROM c – Получаем 3-ю запись

FETCH LAST FROM c — Получаем последнюю запись

FETCH FIRST FROM c — Получаем первую запись

CLOSE c

DEALLOCATE c

DROP TABLE #cursortest

————

FETCH можно использовать для получения результирующего множества, но обычно эта
команда служит для заполнения локальных переменных данными из таблицы.
Предложение INTO команды FETCH позволяет присваивать полученные значения
локальным переменным. Вот пример:

SET NOCOUNT ON

CREATE TABLE #cursortest (k1 int identity)

INSERT #cursortest DEFAULT VALUES

DECLARE c CURSOR SCROLL

FOR SELECT * FROM #cursortest

DECLARE @k int

OPEN c

FETCH c INTO @k

WHILE (@@FETCH_STATUS=0) BEGIN

SELECT @k

FETCH c INTO @k

END

CLOSE c

DEALLOCATE c

DROP TABLE #cursortest

————

NEXT — операция для выбора по умолчанию, так что, если вы не укажете какой тип
выбора вам нужен, вы получите следующую запись курсора. Для операций выбора,
отличных от NEXT, ключевое слово FROM является обязательным.

FETCH RELATIVE 0 можно использовать для обновления текущей записи. Это позволяет
учитывать изменения текущей записи при прохождении курсора. Вот пример:

USE pubs

SET CURSOR_CLOSE_ON_COMMIT OFF — На тот случай, если было включено

SET NOCOUNT ON

DECLARE c CURSOR SCROLL

FOR SELECT title_id, qty FROM sales ORDER BY qty

OPEN c

BEGIN TRAN — Чтобы можно было отменить наши имзменения

PRINT ‘Before image’

FETCH c

UPDATE sales

SET qty=4

WHERE qty=3 — Мы знаем, что этому соответствует только одна запись, первая

PRINT ‘After image’

FETCH RELATIVE 0 FROM c

ROLLBACK TRAN — Отменяем UPDATE

CLOSE c

DEALLOCATE c

Before image

title_id qty

——— ——

PS2091 3

After image

title_id qty

——— ——

PS2091 4

CLOSE

CLOSE освобождает текущее результирующее множество курсора, и снимает любые
блокировки, наложенные курсором. (До версии 7.0, SQL Server оставлял все
блокировки до окончания транзакции, включая блокировки курсоров. Начиная с
версии 7.0, блокировки курсоров обрабатываются независимо от других типов
блокировок). Структуры данных курсора остаются нетронутыми, так что если
понадобится, курсор можно открыть снова. Для закрытия глобального курсора
указывайте ключевое слово GLOBAL.

DEALLOCATE

После того, как вы закончили работу с курсором, необходимо освободить его.
Курсор занимает место в процедурном кэше, которое можно использовать для других
целей, если оно вам больше не нужно. Даже при том, что освобождение курсора
автоматически закрывает его, считается плохим тоном освобождение курсора без
предварительного его закрытия с помощью команды CLOSE.

Конфигурирование курсоров

В дополнение к конфигурированию курсоров с помощью опций при объявлении,
Transact-SQL предоставляет команды и опции конфигурации, которые также могут
изменять поведение курсоров. Процедуры sp_configure и sp_dboption, команда SET
могут быть использованы для конфигурирования того, как курсоры создаются и как
они себя ведут, после того как созданы.

Асинхронные курсоры

По умолчанию, SQL Server генерирует все наборы ключевых значений синхронно — то
есть вызов OPEN не закончится, пока результирующее множество курсора не будет
полностью создано. Это может быть неоптимально для больших множеств, и вы можете
изменить это поведение с помощью опции конфигурации sp_configure ‘cursor
threshold’ (cursor threshold является дополнительной опцией; включите
дополнительные опции с помощью sp_configure ‘show advanced options’, чтобы
получить к ней доступ). Вот пример, который показывает отличия использования
асинхронного курсора:

— Включаем дополнительные опции, чтобы можно было изменить ‘cursor threshold’

EXEC sp_configure ‘show advanced options’,1

RECONFIGURE WITH OVERRIDE

USE northwind

DECLARE c CURSOR STATIC — Заставляем записи копироваться в tempdb

FOR SELECT OrderID, ProductID FROM [Order Details]

DECLARE @start datetime

SET @start=getdate()

— Сначала попробуем с помощью синхронного курсора

OPEN c

PRINT CHAR(13) — Для красивого вывода

SELECT DATEDIFF(ms,@start,getdate()) AS [Milliseconds elapsed for Synchronous
cursor]

SELECT @@CURSOR_ROWS AS [Number of rows in Synchronous cursor]

CLOSE c

— Теперь изменим ‘cursor threshold’, чтобы заставить сервер использовать
асинхронные курсоры

EXEC sp_configure ‘cursor threshold’, 1000 — Асинхронно для курсоров, в которых
> 1000 записей

RECONFIGURE WITH OVERRIDE

PRINT CHAR(13) — Для красивого вывода

SET @start=getdate()

OPEN c — Открываем асинхронный курсор, так как в таблице больше 1000 записей

— OPEN возвращается немедленно, так как курсор заполняется асинхронно

SELECT DATEDIFF(ms,@start,getdate()) AS [Milliseconds elapsed for Asynchronous
cursor]

SELECT @@CURSOR_ROWS AS [Number of rows in Asynchronous cursor]

CLOSE c

DEALLOCATE c

EXEC sp_configure ‘cursor threshold’, -1 — Возвращаем синхронные курсоры

RECONFIGURE WITH OVERRIDE

DBCC execution completed. If DBCC printed error messages, contact your system
administrator.

Configuration option changed. Run the RECONFIGURE statement to install.

Milliseconds elapsed for Synchronous cursor

——————————————-

Number of rows in Synchronous cursor

————————————

2155

DBCC execution completed. If DBCC printed error messages, contact your system
administrator.

Configuration option changed. Run the RECONFIGURE statement to install.

Milliseconds elapsed for Asynchronous cursor

———————————————

Number of rows in Asynchronous cursor

————————————-

-1

DBCC execution completed. If DBCC printed error messages, contact your system
administrator.

Configuration option changed. Run the RECONFIGURE statement to install.

ANSI/ISO автоматическое закрытие курсоров

Спецификация ANSI/ISO SQL-92 определяет, что курсоры должны автоматически
закрываться при фиксации транзакции. В этом нет большого смысла для приложений,
в которых курсоры используются очень часто (те, которые задействуют
прокручиваемые формы, например), так что в этом смысле SQL Server не
соответствует стандарту. По умолчанию, курсоры SQL Server остаются открытыми,
пока не будут явно закрыты, или, пока соединение, создавшее их, не отсоединится.
Чтобы заставить SQL Server закрывать курсоры при фиксации транзакции,
используйте команду SET CURSOR_CLOSE_ON_COMMIT. Вот пример:

CREATE TABLE #temp (k1 int identity PRIMARY KEY, c1 int NULL)

INSERT #temp DEFAULT VALUES

DECLARE c CURSOR DYNAMIC

FOR SELECT k1, c1 FROM #temp

OPEN c

SET CURSOR_CLOSE_ON_COMMIT ON

BEGIN TRAN

UPDATE #temp

SET c1=2

WHERE k1=1

COMMIT TRAN

— Эти FETCH’и будут неудачны, так как курсор закрыт командой COMMIT

FETCH c

FETCH LAST FROM c

— Этот CLOSE будет неудачен, так курсор закрыт командой COMMIT

CLOSE c

DEALLOCATE c

DROP TABLE #temp

SET CURSOR_CLOSE_ON_COMMIT OFF

Server: Msg 16917, Level 16, State 2, Line 0

Cursor is not open.

Server: Msg 16917, Level 16, State 2, Line 26

Cursor is not open.

Server: Msg 16917, Level 16, State 1, Line 29

Cursor is not open.

Вопреки Books Online, откат транзакции не закрывает изменяемые курсоры,
когда CLOSE_CURSOR_ON_COMMIT отключена. Фактическое поведение ROLLBACK
значительно отличается от описанного в документации и больше соответствует тому,
которое происходит при фиксации транзакции. В общем, ROLLBACK не закрывает
курсоры, если только не была включена опция CLOSE_CURSOR_ON_COMMIT. Вот пример:

USE pubs

SET CURSOR_CLOSE_ON_COMMIT ON

BEGIN TRAN

DECLARE c CURSOR DYNAMIC

FOR SELECT qty FROM sales

OPEN c

FETCH c

UPDATE sales

SET qty=qty+1

WHERE CURRENT OF c

ROLLBACK TRAN

— Эти команды FETCH будут неудачны, так как курсор был закрыт командой ROLLBACK

FETCH c

FETCH LAST FROM c

— Эта команда CLOSE будет неудачна, так как курсор был закрыт командой ROLLBACK

CLOSE c

DEALLOCATE c

SET CURSOR_CLOSE_ON_COMMIT OFF

qty

——

Server: Msg 16917, Level 16, State 2, Line 21

Cursor is not open.

Server: Msg 16917, Level 16, State 2, Line 22

Cursor is not open.

Server: Msg 16917, Level 16, State 1, Line 25

Cursor is not open.

Теперь давайте отключим CURSOR_CLOSE_ON_COMMIT и снова выполним запрос:

SET CURSOR_CLOSE_ON_COMMIT OFF

BEGIN TRAN

DECLARE c CURSOR DYNAMIC

FOR SELECT qty FROM sales

OPEN c

FETCH c

UPDATE sales

SET qty=qty+1

WHERE CURRENT OF c

ROLLBACK TRAN

— Эти команды FETCH выполнятся, так как курсор был оставлен открытым несмотря
на ROLLBACK

FETCH c

FETCH LAST FROM c

— Эта команда CLOSE выполнится, поскольку курсор был оставлен открытым,
несмотря на ROLLBACK

CLOSE c

DEALLOCATE c

qty

——

qty

——

qty

——

Несмотря на тот факт, что транзакция былаотменена, в то время как наш
динамический курсор был открыт, это не повлияло на курсор. Это противоречит
поведению сервера, описанному в документации.

Конфигурирование создания по умолчанию глобальных или локальных курсоров

SQL Server по умолчанию создает глобальные курсоры. Это делается для
совместимости с предыдущими версиями сервера, которые не поддерживали локальные
курсоры. Если вам необходимо это изменить, установите опцию базы данных default
to local cursor database в true с помощью sp_dboption.

Модифицируемые курсоры

Предложения WHERE CURRENT OF команд UPDATE и DELETE позволяют модифицировать и
удалять записи с помощью курсора. Модификация или удаление с помощью курсора
известны как позиционная модификация. Вот пример:

USE pubs

SET CURSOR_CLOSE_ON_COMMIT OFF

SET NOCOUNT ON

DECLARE C CURSOR DYNAMIC

FOR SELECT * FROM sales

OPEN c

FETCH c

BEGIN TRAN — Начинает транзакцию, чтобы можно было отменить наши изменения

— Позиционный UPDATE

UPDATE sales SET qty=qty+1 WHERE CURRENT OF c

FETCH RELATIVE 0 FROM c

FETCH c

— Позиционный DELETE

DELETE sales WHERE CURRENT OF c

SELECT * FROM sales WHERE qty=3

ROLLBACK TRAN — Отменяем наши изменения

SELECT * FROM sales WHERE qty=3 — Удаленные записи восстанавливаются

CLOSE c

DEALLOCATE c

stor_id ord_num ord_date qty payterms title_id

——— ———— ——————————— —— ————-
————

6380 6871 1994-09-14 00:00:00.000 5 Net 60 BU1032

stor_id ord_num ord_date qty payterms title_id

——— ———— ——————————— —— ————-
————

6380 6871 1994-09-14 00:00:00.000 6 Net 60 BU1032

stor_id ord_num ord_date qty payterms title_id

——— ———— ——————————— —— ————-
————

6380 722a 1994-09-13 00:00:00.000 3 Net 60 PS2091

stor_id ord_num ord_date qty payterms title_id

——— ———— ——————————— —— ————-
————

stor_id ord_num ord_date qty payterms title_id

——— ———— ——————————— —— ————-
————

6380 722a 1994-09-13 00:00:00.000 3 Net 60 PS2091

Курсорные переменные

Transact-SQL позволяет определять переменные, которые содержат указатели на
курсоры, с помощью типа данных cursor. В командах OPEN, FETCH, CLOSE и
DEALLOCATE можно использовать курсорные переменные, также как имена курсоров. Вы
можете создавать переменные в хранимых процедурах, в которых заданы описания
курсоров, и возвращать курсоры, созданные в хранимой процедуре, с помощью
выходных параметров. Несколько процедур самого SQL Server используют эту
возможность, чтобы возвращать результаты эффективным модульным способом
(например, sp_cursor_list, sp_describe_cursor, sp_fulltext_tables_cursor).
Заметьте, что вы не можете передавать курсор в процедуру с помощью входного
параметра — вы можете только возвращать курсоры с помощью выходных параметров.
Также нельзя определять столбцы таблицы курсорного типа — разрешены только
переменные — также вы не можете присваивать значение курсорной переменной с
помощью оператора SELECT (как в случае скалярных переменных) — для этого вы
должны задействовать SET.

Выходные параметры типа cursor представляют собой усовершенствование по
сравнению с традиционными результирующими множествами, поскольку дают вызывающей
стороне больше контроля над обработкой записей, которые возвращает процедура. Вы
можете обрабатывать курсоры сразу же, если хотите — обрабатывая их как обычное
результирующее множество — или оставить его для дальнейшего использования. До
изобретения курсорных переменных единственным способом достижения такой гибкости
было сохранение результатов хранимой процедуры в таблицу с последующей ее
обработкой. Это хорошо работает с простыми, небольшими результирующими
множествами, но может быть проблематично для больших.

Вы можете использовать функцию CURSOR_STATUS(), чтобы проверить, ссылается ли
выходной курсорный параметр на открытый курсор и чтобы определить, сколько
записей в нем находится. Вот пример, который показывает использование курсорных
переменных, выходных параметров и функцию CURSOR_STATUS():

CREATE PROC listsales_cur @title_id tid, @salescursor cursor varying OUT

— Объявляем локальный курсор, чтобы он был автоматически особожден

— когда выйдет из области видимости

DECLARE c CURSOR DYNAMIC

LOCAL

FOR SELECT * FROM sales WHERE title_id LIKE @title_id

DECLARE @sc cursor — Локальная курсорная переменная

SET @sc=c — Теперь у нас есть две ссылки на курсор

OPEN c

FETCH @sc

SET @salescursor=@sc — Возвращаем курсор с помощью выходного параметра

RETURN 0

SET NOCOUNT ON

— Объявляем локальную курсорную переменную для получения выходного параметра

DECLARE @mycursor cursor

EXEC listsales_cur ‘BU1032’, @mycursor OUT — Вызываем процедуру

— Убедимся, что курсор открыт и в нем есть по крайне мере одна запись

IF (CURSOR_STATUS(‘variable’,’@mycursor’)=1) BEGIN

FETCH @mycursor

WHILE (@@FETCH_STATUS=0) BEGIN

FETCH @mycursor

END

CLOSE @mycursor

DEALLOCATE @mycursor

stor_id ord_num ord_date qty payterms title_id

——— ————— ———————————- —— —————
————

6380 6871 1994-09-14 00:00:00.000 5 Net 60 BU1032

stor_id ord_num ord_date qty payterms title_id

——— ————— ——————————— —— —————-
——-

8042 423LL930 1994-09-14 00:00:00.000 10 ON invoice BU1032

stor_id ord_num ord_date qty payterms title_id

——— ———— ——————————— —— ————-
————

8042 QA879.1 1999-06-24 19:13:26.230 30 Net 30 BU1032

stor_id ord_num ord_date qty payterms title_id

——— ———— ——————————— —— ————-
————

Обратите внимание на то, как этот код ссылается на курсор с помощью трех
различных переменных, а также имени курсора. Для каждой команды, за исключением
DEALLOCATE, ссылка на курсор с помощью курсорной переменной — аналог ссылки на
курсор по имени. Если вы открываете курсор с помощью команды OPEN, независимо от
того, ссылаетесь вы на курсор по имени или с помощью курсорной переменной,
курсор будет открыт, и вы можете выбирать из него записи с помощью команды
FETCH, используя любую переменную, которая ссылается на него. DEALLOCATE
отличается в том смысле, что эта команда на самом деле не освобождает курсор,
если только это не последняя ссылка на него. Это, однако, действительно
предотвращает доступ к нему с помощью указанного идентификатора. Так что если у
вас есть курсор с именем foo и курсорная переменная с названием foovar, которой
было присвоено значение foo, освобождение foo лишь запретит доступ к курсору с
помощью foo — foovar останется неизменной.

Хранимые процедуры для работы с курсорами

SQL Server предоставляет множество хранимых процедур, связанных с курсорами, с
которыми вы должны быть знакомы, если собираетесь много работать с курсорами. В
табл. 13.3 приведен их краткий список, вместе с описанием каждой из них. Каждая
из этих процедур возвращает результат с помощью курсорного выходного параметра,
так что вы должны объявить локальную курсорную переменную, чтобы обрабатывать
их.

Таблица 13.3. Хранимые процедуры, связанные с триггерами

Процедура	Выполняемые функции
sp_cursor_list	Возвращает список курсоров, открытых соединением, а также их атрибуты
sp_describe_cursor	Возвращает атрибуты отдельного курсора
sp_describe_cursor_columns	Возвращает столбцы курсора (и их атрибуты)
sp_describe_cursor_tables	Возвращает список таблиц, использованных в курсоре

Оптимизация производительности курсоров

Лучший способ оптимизации курсоров — вообще не использовать их, когда это
возможно. Как я уже говорил, SQL Server лучше работает с множествами данных, чем
с отдельными записями. Он представляет собой реляционную СУБД, а работа с
отдельными записями никогда не была сильно стороной реляционных СУБД. Хотя
иногда без курсоров не обойтись, так что вот несколько советов по их
оптимизации:

Не используйте статические/нечувствительные курсоры без необходимости. При
открытии статического курсора все его записи копируются во временную
таблицу. Вот почему они нечувствительны к изменениям — он на самом деле
ссылается на копию таблицы в tempdb. Естественно, чем больше результирующее
множество, тем более вероятно, что использование статического курсора
вызовет проблемы с ресурсами в tempdb.
Используйте keyset-курсоры, если только они вам действительно необходимы.
Как и в случае статических курсоров, открытие keyset-курсора создает
временную таблицу. Хотя эта таблица содержит только значения ключей основной
таблицы (если, конечно, существует уникальный ключ), она может все равно
иметь существенный размер при работе с большими результирующими множествами.
Используйте опцию FAST_FORWARD вместо FORWARD_ONLY при работе с
однонаправленными множествами только для чтения. При использовании
FAST_FORWARD объявляется FORWARD_ONLY, READ_ONLY курсор с некоторыми
внутренними оптимизациями производительности.
Объявляйте курсоры только для чтения с помощью ключевого слова READ_ONLY.
Это препятствует случайным изменениям, кроме того, сервер будет знать, что
курсор не изменит записи, по которым проходит.
Будьте осторожны при модификации множества записей с помощью цикла по
курсору, который находится внутри транзакции. В зависимости от уровня
изоляции транзакции эти записи могут остаться заблокированными до тех пор,
пока транзакция не будет зафиксирована или откачена, а это может привести к
проблемам с ресурсами сервера.
Будьте осторожны с модифицируемыми динамическими курсорами, особенно с теми,
которые созданы по таблицам с неуникальными кластерными индексами, так как
они могут вызвать «проблему Хэллоуина» —повторные, ошибочные обновления
одной и той же строки или строк. Так как SQL Server внутренне делает
неуникальные ключи кластерных индексов уникальными, добавляя к ним
порядковый номер, возможно, что вы измените значение ключа записи на такое,
которое уже существует и заставите сервер добавить суффикс, из-за которого
запись переместится дальше в результирующем множестве. При выборе
оставшегося результирующего множества вы опять наткнетесь на эту запись, и
процесс повторится, что приведет к бесконечному циклу. Вот пример,
иллюстрирующий данную проблему:

— Этот код создает курсор, который представляет Проблему Хэллоуина.

— Не запускайте его, если только вам не нравятся бесконечные циклы.

SET NOCOUNT ON

CREATE TABLE #temp (k1 int identity, c1 int NULL)

CREATE CLUSTERED INDEX c1 ON #temp(c1)

INSERT #temp VALUES (8)

INSERT #temp VALUES (6)

INSERT #temp VALUES (7)

INSERT #temp VALUES (5)

INSERT #temp VALUES (3)

INSERT #temp VALUES (0)

INSERT #temp VALUES (9)

DECLARE c CURSOR DYNAMIC

FOR SELECT k1, c1 FROM #temp

OPEN c

FETCH c

WHILE (@@FETCH_STATUS=0) BEGIN

UPDATE #temp

SET c1=c1+1

WHERE CURRENT OF c

FETCH c

SELECT * FROM #temp ORDER BY k1

END

CLOSE c

DEALLOCATE c

DROP TABLE #temp

Используйте асинхронные курсоры при работе с большими множествами, чтобы
возвращать управление вызывающей стороне как можно быстрее. Асинхронные
курсоры особенно полезны при возвращении результирующего множества
значительного размера прокручиваемой форме, так как они позволяют приложению
начать отображение практически немедленно.

Заключение

Курсоры —о не лучший способ решения большинства задач, они могут вызывать
серьезные проблемы с производительностью, если их неправильно использовать.

НОУ ИНТУИТ | Лекция | Курсоры в Transact-SQL

Аннотация: Курсор – это особый временный объект 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 вернет четыре строки, как показано на рис. 27.1. Если в процессе использования вами курсора кто-либо добавит значение Description для элемента Bergamot, либо добавит строку для элемента Bayberry, что произойдет с множеством строк, на которое указывает ваш курсор?

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

Прокрутка

Второй характеристикой курсора является способность осуществления прокрутки как вперед, так и назад, либо только вперед. Здесь имеет место извечная для программирования дилемма: скорость против гибкости. Последовательные курсоры (forward-only) работают значительно быстрее, но имеют меньшую гибкость.

Обновление

Последней характеристикой, используемой для классификации курсоров, является возможность обновления строк курсором. Опять же, курсоры «только чтение» обычно более производительны, но имеют меньшую гибкость.

НОУ ИНТУИТ | Лекция | Курсоры в Transact-SQL

Вы научитесь:

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

Понятие о курсорах

НОУ ИНТУИТ | Лекция | Курсоры в Transact-SQL

Вы научитесь:

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

Понятие о курсорах

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

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

Отражение изменений

SELECT * FROM Oils
WHERE Left(OilName, 1) = 'B'

Прокрутка

Обновление

НОУ ИНТУИТ | Лекция | Курсоры в Transact-SQL

Вы научитесь:

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

Понятие о курсорах

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

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

Отражение изменений

SELECT * FROM Oils
WHERE Left(OilName, 1) = 'B'

Прокрутка

Обновление

Оптимизация SQL Server при работе с курсорами

Здравствуй, человек-читатель блогов на Community.

Хочу рассказать о своем недавнем опыте оптимизации курсора в SQL Server.
Первое, что нужно знать, курсор – это не хорошо, а плохо. Где есть возможность заменить курсор на INSERT SELECT или на использование временной таблицы, там нужно это сделать (за редким исключением). Курсор – это почти всегда дополнительные ресурсы сервера и резкое падение производительности по сравнению с другими решениями.
Второе, иногда без курсора не обойтись – там где не обойтись без построчного прохода по результату выборки. Вот в таких случаях очень важно правильно создать нужный тип курсора – тот, который соответствует решаемой задаче. Общий синтаксис объявления курсора имеет вид:

Остановлюсь на первых трех строчках ключевых параметров.
    LOCAL или GLOBAL:    если хотим, чтобы курсор был доступен другим процедурам, функциям, пакетам в рамках нашей сессии, то GLOBAL – в этом случае за удалением курсора следим сами (команда DEALLOCATE). Во всех остальных случаях (т.е. в подавляющем своем большинстве) – LOCAL. Внимание, по умолчанию создается именно GLOBAL курсор!
    FORWARD_ONLY или SCROLL: если хотим ходить по курсору, как ненормальные, туда-сюда, то SCROLL, иначе – FORWARD_ONLY. Внимание, по умолчанию создается SCROLL курсор!
    STATIC или KEYSET, DYNAMIC, FAST_FORWARD: если хотим, чтобы при проходе по курсору отображалась актуальная информация из таблицы (т.е., если после открытия курсора, мы поменяли информацию в одном из полей таблицы и хотим, чтобы при проходе по курсору в нужной строчке курсора была уже обновленная информация), то используем или KEYSET (если КАЖДАЯ таблица, участвующая в выборке, имеет уникальный индекс) или DYNAMIC (самый медленный тип). Если же нам нужен снимок результата выборки после открытия курсора – STATIC (самый быстрый тип – копия результата выборки копируется в базу tempdb и работаем уже с ней). FAST_FORWARD = KEYSET+FORWARD_ONLY+READ_ONLY – пацаны из инета пишут, что STATIC дольше открывается (т.к. создается копия в tempdb), но быстрее работает, а FAST_FORWARD – наоборот. Так что если количество записей велико (насколько большое показывает практика), то применяем STATIC, иначе – FAST_FORWARD. Внимание, по умолчанию создается DYNAMIC курсор.

Таким образом, для большого кол-ва записей в большинстве случаев мой выбор:
DECLARE cursor_name CURSOR LOCAL FORWARD_ONLY STATIC FOR
select_statement

для небольшого кол-ва записей:
DECLARE cursor_name CURSOR LOCAL FAST_FORWARD FOR
select_statement

Теперь перейдем к практике (что собственно и подтолкнуло меня к писанине сего).
Испокон веков при объявлении курсора я применял конструкцию DECLARE … CURSOR LOCAL FOR…
При разработке интеграции с одной очень нехорошей базой, в которой нет ни одного индекса и не одного ключа, я применил тот же подход при объявлении курсоров, что и всегда. Выборка одного курсора содержала 225 000 записей. В результате процесс импорта данных из такой базы занял 15 часов 14 минут !!! И хотя импорт и был первичный (т.е. одноразовый), но даже для нормального тестирования такого импорта потребовалось бы несколько суток! После замены вышеназванной конструкции при объявлении курсора на DECLARE .. CURSOR LOCAL FORWARD_ONLY STATIC FOR.. весь процесс импорта занял … внимание … 10 минут 5 секунд !!! Так что игра точно стоит свеч.
Хочу повториться, что идеальный вариант — это все же не использовать курсоры вообще — для СУБД MS SQL намного роднее реляционный, а не навигационный подход.

Курсоры

в SQL Server

Курсор — это объект базы данных для извлечения данных из набора результатов по одной строке за раз, вместо команд T-SQL, которые работают со всеми строками в наборе результатов одновременно. Мы используем курсор, когда нам нужно обновлять записи в таблице базы данных одноэлементным способом, то есть строка за строкой.

Жизненный цикл курсора

Объявление курсора
Курсор объявляется путем определения оператора SQL, который возвращает набор результатов.
Открыть
Курсор открывается и заполняется путем выполнения оператора SQL, определенного курсором.
Выборка
Когда курсор открыт, строки могут выбираться из курсора одна за другой или в блоке для обработки данных.
Закрыть
После обработки данных мы должны явно закрыть курсор.
Deallocate
Наконец, нам нужно удалить определение курсора и освободить все системные ресурсы, связанные с курсором.

Синтаксис для объявления курсора

Команда SQL для объявления курсора используется для определения курсора со многими параметрами, которые влияют на масштабируемость и поведение курсора при загрузке.Базовый синтаксис приведен ниже

 DECLARE cursor_name CURSOR
 [МЕСТНОЕ | GLOBAL] - определить область действия курсора
 [FORWARD_ONLY | SCROLL] - определение движения курсора (вперед / назад)
 [СТАТИЧЕСКИЙ | KEYSET | ДИНАМИЧЕСКИЙ | FAST_FORWARD] - основной тип курсора
 [READ_ONLY | SCROLL_LOCKS | OPTIMISTIC] - определить блокировки
 FOR select_statement --define оператор выбора SQL
 FOR UPDATE [col1, col2, ... coln] - определить столбцы, которые необходимо обновить.

Синтаксис для открытия курсора

Курсор можно открыть локально или глобально.По умолчанию он открывается локально. Базовый синтаксис для открытия курсора приведен ниже:

 OPEN [GLOBAL] имя_курсора - по умолчанию это локальный

Синтаксис для извлечения курсора

Оператор Fetch предоставляет множество опций для извлечения строк из курсора. ДАЛЕЕ — опция по умолчанию. Базовый синтаксис для выборки курсора приведен ниже:

 FETCH [NEXT | PRIOR | FIRST | LAST | ABSOLUTE n | RELATIVE n]
ОТ [GLOBAL] имя_курсора
INTO @Variable_name [1,2, .. n]

Синтаксис закрытия курсора

Оператор close явно закрывает курсор.Базовый синтаксис для закрытия курсора приведен ниже:

 CLOSE имя_курсора - после закрытия его можно открыть повторно.

Синтаксис для удаления курсора

Оператор Deallocate удаляет определение курсора и освобождает все системные ресурсы, связанные с курсором. Базовый синтаксис для закрытия курсора приведен ниже:

 DEALLOCATE имя_курсора - после освобождения его нельзя открыть повторно

SQL SERVER — Простые примеры курсоров

 CREATE TABLE Employee
(
 EmpID int ПЕРВИЧНЫЙ КЛЮЧ,
 EmpName varchar (50) NOT NULL,
 Зарплата int NOT NULL,
 Адрес varchar (200) NOT NULL,
)
ИДТИ
ВСТАВИТЬ В ЗНАЧЕНИЯ сотрудника (EmpID, EmpName, Salary, Address) (1, 'Mohan', 12000, 'Noida')
ВСТАВИТЬ В ЗНАЧЕНИЯ сотрудника (EmpID, EmpName, Salary, Address) (2, «Pavan», 25000, «Delhi»)
ВСТАВИТЬ ЗНАЧЕНИЯ сотрудника (EmpID, EmpName, Salary, Address) (3, 'Amit', 22000, 'Dehradun')
ВСТАВИТЬ В ЗНАЧЕНИЯ сотрудника (EmpID, EmpName, Salary, Address) (4, 'Sonu', 22000, 'Noida')
ВСТАВИТЬ ЗНАЧЕНИЯ сотрудника (EmpID, EmpName, Salary, Address) (5, 'Deepak', 28000, 'Gurgaon')
ИДТИ
ВЫБРАТЬ * ОТ сотрудника

 УСТАНОВИТЬ НЕТ СЧЕТ
ОБЪЯВИТЬ @Id int
ОБЪЯВИТЬ @ имя varchar (50)
ОБЪЯВИТЬ @salary int
 ОБЪЯВЛЕНИЕ КУРСОРА cur_emp
СТАТИЧЕСКИЙ ДЛЯ
ВЫБЕРИТЕ EmpID, EmpName, зарплату сотрудника
ОТКРЫТЬ cur_emp
ЕСЛИ @@ CURSOR_ROWS> 0
 НАЧАТЬ
 ВЫБРАТЬ ДАЛЕЕ ИЗ cur_emp В @ Id, @ name, @ salary
 WHILE @@ Fetch_status = 0
 НАЧАТЬ
 ПЕЧАТЬ 'ID:' + convert (varchar (20), @ Id) + ', Name:' + @ name + ', Salary:' + convert (varchar (20), @ salary)
 ВЫБРАТЬ ДАЛЕЕ ИЗ cur_emp В @ Id, @ name, @ salary
 КОНЕЦ
КОНЕЦ
ЗАКРЫТЬ cur_emp
DEALLOCATE cur_emp
SET NOCOUNT OFF

Прочитайте больше статей, относящихся к LINQ

Резюме

В этой статье я пытаюсь объяснить основы работы курсора в SQL Server на простом примере.Надеюсь, после прочтения этой статьи вы сможете понять курсоры в SQL Server. Здесь также я хотел бы поделиться соответствующей статьей Альтернативы курсора SQL Server. Я хотел бы получить отзывы от читателей моего блога. Пожалуйста, оставьте свой отзыв, вопрос или комментарий об этой статье.

Поделиться Артикул

Пройдите бесплатные тесты, чтобы оценить свои навыки!

Менее чем за 5 минут с помощью нашего теста навыков вы сможете определить свои слабые и сильные стороны в знаниях.

sql — Использование курсора с CTE

Переполнение стека

Около
Продукты
Для команд

Переполнение стека
Общественные вопросы и ответы
Переполнение стека для команд
Где разработчики и технологи делятся частными знаниями с коллегами
Вакансии
Программирование и связанные с ним технические возможности карьерного роста
Талант
Нанимайте технических специалистов и создавайте свой бренд работодателя
Реклама
Обратитесь к разработчикам и технологам со всего мира
О компании

Апрель 2024
Пн	Вт	Ср	Чт	Пт	Сб	Вс
1	2	3	4	5	6	7
8	9	10	11	12	13	14
15	16	17	18	19	20	21
22	23	24	25	26	27	28
29	30

Курсоры ms sql: Курсоры в Mysql. / Хабр

НОУ ИНТУИТ | Лекция | Курсоры: принципы работы

Понятие курсора

Реализация курсоров в среде MS SQL Server

T-SQL синтаксис курсоров

T-SQL синтаксис курсоров

DECLARE CURSOR

Глобальные и локальные курсоры

OPEN

FETCH

CLOSE

DEALLOCATE

Конфигурирование курсоров

Асинхронные курсоры

ANSI/ISO автоматическое закрытие курсоров

Конфигурирование создания по умолчанию глобальных или локальных курсоров

Модифицируемые курсоры

Курсорные переменные

Хранимые процедуры для работы с курсорами

Оптимизация производительности курсоров

Заключение

НОУ ИНТУИТ | Лекция | Курсоры в Transact-SQL

Вы научитесь:

Понятие о курсорах

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

НОУ ИНТУИТ | Лекция | Курсоры в Transact-SQL

Вы научитесь:

Понятие о курсорах

НОУ ИНТУИТ | Лекция | Курсоры в Transact-SQL

Вы научитесь:

Понятие о курсорах

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

НОУ ИНТУИТ | Лекция | Курсоры в Transact-SQL

Вы научитесь:

Понятие о курсорах

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

Оптимизация SQL Server при работе с курсорами

в SQL Server

Жизненный цикл курсора

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

Открыть

Выборка

Закрыть

Deallocate

Синтаксис для объявления курсора

Синтаксис для открытия курсора

Синтаксис для извлечения курсора

Синтаксис закрытия курсора

Синтаксис для удаления курсора

SQL SERVER — Простые примеры курсоров

Прочитайте больше статей, относящихся к LINQ

Резюме

Поделиться Артикул

Пройдите бесплатные тесты, чтобы оценить свои навыки!

sql — Использование курсора с CTE

Добавить комментарий Отменить ответ