Ms sql создание курсора пример. Курсоры в Mysql

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

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

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

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

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

Управление курсором в среде MS SQL Server

Управление курсором реализуется путем выполнения следующих команд:

  • DECLARE – создание или объявление курсора ;
  • OPEN – открытие курсора , т.е. наполнение его данными;
  • FETCH – выборка из курсора и изменение строк данных с помощью курсора;
  • CLOSE – закрытие курсора ;
  • DEALLOCATE – освобождение курсора , т.е. удаление курсора как объекта.

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

В стандарте SQL для создания курсора предусмотрена следующая команда:

<создание_курсора>::= DECLARE имя_курсора CURSOR FOR SELECT_оператор ]}]

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



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

SELECT-оператор задает тело запроса SELECT, с помощью которого определяется результирующий набор строк курсора .

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

Создание курсора с аргументом FOR UPDATE позволяет выполнять в курсоре изменение данных либо в указанных столбцах, либо, при отсутствии аргумента OF имя_столбца, во всех столбцах.

В среде MS SQL Server принят следующий синтаксис команды создания курсора :

<создание_курсора>::= DECLARE имя_курсора CURSOR FOR SELECT_оператор ]]

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

Если указано ключевое слово GLOBAL, создается глобальный курсор ; он существует до закрытия текущего соединения.

При указании FORWARD_ONLY создается последовательный курсор ; выборку данных можно осуществлять только в направлении от первой строки к последней.

При указании SCROLL создается прокручиваемый курсор ; обращаться к данным можно в любом порядке и в любом направлении.

При указании STATIC создается статический курсор .

При указании KEYSET создается ключевой курсор.

При указании DYNAMIC создается динамический курсор .

Если для курсора READ_ONLY указать аргумент FAST_FORWARD, то созданный курсор будет оптимизирован для быстрого доступа к данным. Этот аргумент не может быть использован совместно с аргументами FORWARD_ONLY и OPTIMISTIC.

В курсоре , созданном с указанием аргумента OPTIMISTIC, запрещается изменение и удаление строк , которые были изменены после открытия курсора .

При указании аргумента TYPE_WARNING сервер будет информировать пользователя о неявном изменении типа курсора , если он несовместим с запросомSELECT.

Открытие курсора

Для открытия курсора и наполнения его данными из указанного при создании курсора запроса SELECT используется следующая команда:

OPEN {{имя_курсора } |@имя_переменной_курсора}

После открытия курсора происходит выполнение связанного с ним оператора SELECT, выходные данные которого сохраняются в многоуровневой памяти.

Выборка данных из курсора

Сразу после открытия курсора можно выбрать его содержимое (результат выполнения соответствующего запроса) посредством следующей команды:

FETCH [ FROM ]{{имя_курсора }| @имя_переменной_курсора } ]

При указании FIRST будет возвращена самая первая строка полного результирующего набора курсора , которая становится текущей строкой.

При указании LAST возвращается самая последняя строка курсора . Она же становится текущей строкой.

При указании NEXT возвращается строка, находящаяся в полном результирующем наборе сразу же после текущей. Теперь она становится текущей. По умолчанию команда FETCH использует именно этот способ выборки строк.

Ключевое слово PRIOR возвращает строку, находящуюся перед текущей. Она и становится текущей.

Аргумент ABSOLUTE {номер_строки | @переменная_номера_строки} возвращает строку по ее абсолютному порядковому номеру в полном результирующем наборе курсора . Номер строки можно задать с помощью константы или как имя переменной, в которой хранится номер строки. Переменная должна иметь целочисленный тип данных. Указываются как положительные, так и отрицательные значения. При указании положительного значения строка отсчитывается от начала набора, отрицательного – от конца. Выбранная строка становится текущей. Если указано нулевое значение, строка не возвращается.

Аргумент RELATIVE {кол_строки | @переменная_кол_строки} возвращает строку, находящуюся через указанное количество строк после текущей. Если указать отрицательное значение числа строк, то будет возвращена строка, находящаяся за указанное количество строк перед текущей. При указании нулевого значения возвратится текущая строка. Возвращенная строка становится текущей.

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

В конструкции INTO @имя_переменной [,...n] задается список переменных, в которых будут сохранены соответствующие значения столбцов возвращаемой строки. Порядок указания переменных должен соответствовать порядку столбцов в курсоре , а тип данных переменной – типу данных в столбце курсора . Если конструкция INTO не указана, то поведение команды FETCH будет напоминать поведение команды SELECT – данные выводятся на экран.

Реализация курсора в базе данных напоминает класс Java, имеющий набор данных и методы для их обработки. При этом sql cursor использует данные как обычный массив. Курсоры могут быть использованы в триггерах, хранимых процедурах и функциях.

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

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

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

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

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

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

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

Declare cursor_name cursor for select_statement ]}]

В данном выражении объявляется курсор declare cursor c именем "cursor_name".

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

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

Выражение select_statement указывает на конструкцию чтения информации типа select ... from ... . Оно не должно содержать оператор into , поскольку cursor имеет свой оператор fetch для заполнения переменных данными курсора.

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

Создание курсора с аргументом FOR UPDATE позволяет выполнять в курсоре изменение данных либо в указанных столбцах, либо, при отсутствии аргумента OF имя_столбца , во всех столбцах.

В подпрограмме можно объявить несколько курсоров. Но каждый курсор должен иметь уникальное имя. Для открытия курсора необходимо использовать оператор open , который открывает ранее объявленный курсор:

Открытие курсора, cursor open

В SQL определен следующий синтаксис открытия курсора "cursor open"" :

Open cursor_name;

Выборка данных из курсора, cursor fetch

Синтаксис чтения данных из курсора в некоторые переменные имеет следующий вид:

Fetch cursor_name into var_name [, var_name] ...;

Оператор fetch выбирает данные открытого курсора в переменные, расположенные после into и перемещает указатель курсора в следующую позицию.

Закрытие курсора, cursor close

Оператор close закрывает cursor. Если оператор явно не указан, то курсор закрывается автоматически при закрытии соответствующего программного блока.

Close cursor_name;

После закрытия курсор становится недоступным. При закрытии снимаются все блокировки, установленные в процессе работы курсора. Закрывать можно только открытые курсоры. Закрытый, но не освобожденный курсор может быть повторно открыт. Не допускается закрывать неоткрытый курсор.

В каждой СУБД имеются свои особенности использования курсора.

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

В PL/SQL имеется четыре курсорных атрибута %FOUND , %NOTFOUND , %ISOPEN и %ROWCOUNT . Атрибуты курсора объявляются подобно операторам %TYPE и %ROWTYPE, справа от имени курсора.

Атрибут %FOUND

Атрибут %NOTFOUND

Атрибут %NOTFOUND является полной противоположностью %FOUND.

Атрибут %ISOPEN

Атрибут %ISOPEN указывает только на то, открыт ли курсор или нет.

Атрибут %ROWCOUNT

Атрибут %ROWCOUNT является числовым атрибутом, возвращающим число строк, считанных курсором на определенный момент времени.

Пример SQL курсора в СУБД Oracle

Declare v_id managers.id %TYPE; v_name managers.name%TYPE; v_comm managers.comm%TYPE; crs cursor for select id, name, sum(comm) as comm from managers where data between "2014-11-01" and "2014-11-30" group by id, name; begin open crs; loop EXIT WHEN crs%NOTFOUND; FETCH crs into v_id, v_name, v_comm; insert into bonus(id, name, comm) values (crs.id, crs.name, crs.comm); end loop; commit; close crs; end;

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

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

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

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

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

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

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

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

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

Declare cursor_name cursor for SELECT_оператор ]]

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

Оператор FORWARD_ONLY определяет последовательный курсор, позволяющий осуществлять выборку данных только в направлении от первой строки к последней. При использовании оператора SCROLL создается прокручиваемый курсор, который обеспечивает обращение к данным в любом порядке и в любом направлении.

Тип курсора определяют операторы:

  • STATIC - создание статического курсора;
  • DYNAMIC - создание динамического курсора;
  • KEYSET - создание ключевого курсора.

Если для курсора READ_ONLY указать аргумент FAST_FORWARD , то созданный курсор будет оптимизирован для быстрого доступа к данным. Этот аргумент не может быть использован совместно с аргументами FORWARD_ONLY и OPTIMISTIC .

Если курсор создан с указанием оператора OPTIMISTIC , то запрещается изменение и удаление строк, которые были изменены после открытия курсора.

При указании аргумента TYPE_WARNING сервер будет информировать о неявном изменении типа курсора, если он несовместим с запросом SELECT.

Выборка данных из курсора, fetch

Сразу после открытия курсора можно получить его содержимое посредством следующей команды:

При использовании оператора FIRST будет возвращена первая строка результирующего набора курсора, которая становится текущей строкой. При указании LAST будет возвращена последняя строка курсора. Она же становится текущей строкой.

При указании оператора NEXT будет возвращена строка, находящаяся в результирующем наборе сразу же после текущей. Эта строка становится текущей. По умолчанию команда FETCH использует именно этот способ выборки строк.

При указании оператора PRIOR будет возвращена строка, находящаяся перед текущей. Эта строка становится текущей.

Оператор ABSOLUTE {номер_строки | @переменная_номера_строки} возвращает строку по ее абсолютному порядковому номеру в полном результирующем наборе курсора. Номер строки можно задать с помощью константы или как имя переменной, в которой хранится номер строки. Переменная должна иметь целочисленный тип данных. Указываются как положительные, так и отрицательные значения. При указании положительного значения строка отсчитывается от начала набора, отрицательного – от конца. Выбранная строка становится текущей. Если указано нулевое значение, строка не возвращается.

Аргумент RELATIVE {кол_строки | @переменная_кол_строки} возвращает строку, находящуюся со смещением на указанное количество строк после текущей. Если указать отрицательное значение числа строк, то будет возвращена строка, находящаяся за указанное количество строк перед текущей. При указании нулевого значения возвратится текущая строка. Возвращенная строка становится текущей.

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

В выражении INTO @имя_переменной [,...n] определяется список переменных, в которых будут сохранены соответствующие значения столбцов возвращаемой строки. Порядок указания переменных должен соответствовать порядку столбцов в курсоре, а тип данных переменной – типу данных в столбце курсора.

Изменение и удаление данных с использованием курсора

Для изменения данных с помощью курсора необходимо выполнить команду UPDATE в следующем формате:

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

Для удаления данных посредством курсора используется команда DELETE в следующем формате:

В результате будет удалена строка, являющаяся текущей в курсоре.

Освобождение памяти, deallocate

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

Deallocate cursor_name;

Атрибут @@FETCH_STATUS

Для определения наличия строк в курсоре следует использовать глобальную переменную @@FETCH_STATUS , которая принимает ненулевое значение, если строк в курсоре больше нет. Если же набор строк еще не исчерпан, то @@FETCH_STATUS равна нулю.

Пример курсора в SQL сервере

Declare @company varchar(50), @manager varchar(50), @message varchar(256); declare crs_clients cursor local for select company, manager from customers where city = "Moscow" order by company, manager; print "Список клиентов"; open crs_clients; fetch next from crs_clients into @company, @manager; while @@FETCH_STATUS = 0 begin select @message = "Компания " + @company + " менеджер " + @manager; print @message; -- переход к следующей записи fetch next from crs_clients into @company, @manager; end; close crs_clients; deallocate crs_clients;

In my T-SQL code I always use set based operations. I have been told these types of operations are what 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 included them in SQL Server for a reason so they must have a place where they can be used in an efficient manner.

Solution

In some circle"s cursors are never used, in others they are a last resort and in other groups they are used regularly. In each of these camps they have different reasons for their stand on cursor usage. Regardless of your stand on cursors they probably have a place in particular circumstances and not in others. So, it boils down to your understanding of the coding technique then your understanding of the problem at hand to make a decision on whether or not cursor-based processing is appropriate or not. To get 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

Creating a SQL Server cursor is a consistent process, so once you learn the steps you are easily able to duplicate them with various sets of logic to loop through data. Let"s walk through the steps:

  1. First, you declare your variables that you need in the logic.
  2. Second you declare cursor with a specific name that you will use throughout the logic. This is immediately followed by opening the cursor.
  3. Third, you fetch a record from cursor to begin the data processing.
  4. Fourth, is the data process that is unique to each set of logic. This could be inserting, updating, deleting, etc. for each row of data that was fetched. This is the most important set of logic during this process that is performed on each row.
  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. As a final and important step, you need to deallocate the cursor to release all of the internal resources SQL Server is holding.

From here, check out the examples below to get started on knowing when to use SQL Server cursors and how to do so.

Example SQL Server Cursor

Here is an example cursor from tip Simple script to backup all SQL Server databases where backups are 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) -- used for 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

Based on the example above, cursors include these components:

  • DECLARE statements - Declare variables used in the code block
  • SET\SELECT statements - Initialize the variables to a specific value
  • DECLARE CURSOR statement - Populate the cursor with values that will be evaluated
    • NOTE - There are an equal number of variables in the DECLARE CURSOR FOR statement as there are in the SELECT statement. This could be 1 or many variables and associated columns.
  • OPEN statement - Open the cursor to begin data processing
  • FETCH NEXT statements - Assign the specific values from the cursor to the variables
    • NOTE - This logic is used for the initial population before the WHILE statement and then again during each loop in the process as a 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 - Based on the data processing multiple BEGIN...END statements can be used
  • Data processing - In this example, this logic is to backup a database to a specific path and file name, but this could be just about any DML or administrative logic
  • 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

In the example above backups are issued via a cursor, check out these other tips that leverage cursor-based logic:

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

SQL Server Cursor Analysis

The analysis below is intended to serve as insight into various scenarios where cursor-based logic may or may not be beneficial:

  • Online Transaction Processing (OLTP) - In most OLTP environments, SET based logic makes the most sense for short transactions. Our team has run into a third-party application that uses cursors for all of its processing, which has caused issues, but this has been a rare occurrence. Typically, SET based logic is more than feasible and cursors are rarely needed.
  • Reporting - Based on the design of the reports and the underlying design, cursors are typically not needed. However, our team has run into reporting requirements where referential integrity does not exist on the underlying database and it is necessary to use a cursor to correctly calculate the reporting values. We have had the same experience when needing to aggregate data for downstream processes, a cursor-based approach was quick to develop and performed in an acceptable manner to meet the need.
  • Serialized processing - If you have a need to complete a process in serialized manner, cursors are a viable option.
  • Administrative tasks - Many administrative tasks need to be executed in a serial manner, which fits nicely into cursor-based logic, but other system-based objects exist to fulfill the need. In some of those circumstances, cursors are used to complete the process.
  • Large data sets - With large data sets you could run into any one or more of the following:
    • Cursor based logic may not scale to meet the processing needs.
    • With large set-based operations on servers with a minimal amount of memory, the data may be paged or monopolize the SQL Server which is time consuming can cause contention and memory issues. As such, a cursor-based approach may meet the need.
    • Some tools inherently cache the data to a file under the covers, so processing the data in memory may or may not actually be the case.
    • If the data can be processed in a staging SQL Server database the impacts to the production environment are only when the final data is processed. All of the resources on the staging server can be used for the ETL processes then the final data can be imported.
    • SSIS supports batching sets of data which may resolve the overall need to break-up a large data set into more manageable sizes and perform better than a row by row approach with a cursor.
    • Depending on how the cursor or SSIS logic is coded, it may be possible to restart at the point of failure based on a
    • Repeat a batch with the GO command
    Next Steps
    • When you are faced with a data processing decision determine where you stand with SQL Server cursor usage. They may or may not have a place in your application or operational processes. There are many ways to complete a task, so using a cursor could be a reasonable alternative or not. You be the judge.
    • If you run into issues with another coding technique and need to get something done quickly, using a cursor may be a viable alternative. It may take longer to process the data, but the coding time might be much less. If you have a one-time process or nightly processing, this could do the trick.
    • If cursors are shunned in your environment, be sure to select another viable alternative. Just be sure the process will not cause other issues. As an example, if a cursor is used and millions of rows are processed will this potentially flush all of the data from cache and cause further contention? Or with a large data set will the data be paged to disk or written to a temporary directory?
    • As you evaluate a cursor-based approach versus other alternatives make a fair comparison of the techniques in terms of time, contention and resources needed. Hopefully these factors will drive you to the proper technique.

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

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

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 ] ] ] [ ;]

Остановлюсь на первых трех строчках ключевых параметров.
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_statemen t

для небольшого кол-ва записей:
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 намного роднее реляционный, а не навигационный подход.

Команда 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.)