Sql server курсор пример – Курсоры в MSSQL — перебор выборки в цикле. / MS SQL

Курсоры в MSSQL — перебор выборки в цикле. / MS SQL

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

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

CREATE PROCEDURE [dbo].[MyProcedure] AS

DECLARE	@ID INT
DECLARE 	@QUA INT
DECLARE	@VAL VARCHAR (500)
DECLARE	@NAM VARCHAR (500)
/*Объявляем курсор*/
DECLARE @CURSOR CURSOR
/*Заполняем курсор*/
SET @CURSOR  = CURSOR SCROLL
FOR
SELECT  INDEX, QUANTITY, VALUE,  NAME  
  FROM  My_First_Table WHERE  QUANTITY > 1
/*Открываем курсор*/
OPEN @CURSOR
/*Выбираем первую строку*/
FETCH NEXT FROM @CURSOR INTO @ID, @QUA, @VAL, @NAM
/*Выполняем в цикле перебор строк*/
WHILE @@FETCH_STATUS = 0
BEGIN

 	IF NOT EXISTS(SELECT VAL FROM My_Second_Table WHERE ID=@ID)
	BEGIN
/*Вставляем параметры в третью таблицу если условие соблюдается*/
		INSERT INTO My_Third_Table (VALUE, NAME) VALUE(@VAL, @NAM)
	END
/*Выбираем следующую строку*/
FETCH NEXT FROM @CURSOR INTO @ID, @QUA, @VAL, @NAM
END
CLOSE @CURSOR
 

kbss.ru

Использование курсоров и циклов в Transact-SQL | Info-Comp.ru

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

Как Вы поняли курсоры, и циклы мы будем рассматривать применимо к конкретной задачи. А какой задачи, сейчас расскажу.

Существует процедура, которая выполняет какие-то действия, которые не может выполнить обычная функция SQL например, расчеты и insert на основе этих расчетов. И Вы ее запускаете, например вот так:

exec test_PROCEDURE par1, par2

Другими словами Вы запускаете ее только с теми параметрами, которые были указаны, но если Вам необходимо запустить данную процедуру скажем 100, 200 или еще более раз, то согласитесь это не очень удобно, т.е. долго. Было бы намного проще, если бы мы взяли и запускали процедуру как обычную функцию в запросе select, например:

select my_fun(id) from test_table

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

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

И так приступим, и перед тем как писать процедуру, давайте рассмотрим исходные данные нашего примера.

Допустим, есть таблица test_table

CREATE TABLE [dbo].[test_table](
	[number] [numeric](18, 0) NULL,
	[pole1] [varchar](50) NULL,
	[pole2] [varchar](50) NULL
) ON [PRIMARY]

GO

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

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

Она просто принимает три параметра и вставляет их в таблицу.

И допустим эту процедуру, нам нужно запустить столько раз, сколько строк в какой-нибудь таблице или представлении (VIEWS) ,  другими словами запустить ее массово для каждой строки источника.

И для примера создадим такой источник, у нас это будет простая таблица

CREATE TABLE [dbo].[test_table_vrem](
	[number] [numeric](18, 0) NULL,
	[pole1] [varchar](50) NULL,
	[pole2] [varchar](50) NULL
) ON [PRIMARY]

GO

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

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

exec my_proc_test 1, ‘pole1_str1’, ‘pole2_str1’

И так еще три раза, с соответствующими параметрами.

Но нам так не охота, поэтому мы напишем еще одну дополнительную процедуру, которая и будет запускать нашу основную процедуру столько раз, сколько нам нужно.

Первый вариант.

Используем курсор и цикл в процедуре

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

И теперь осталось нам ее вызвать и проверить результат:

Код:

--до выполнения процедуры
select * from test_table
--вызов процедуры
exec dbo.my_proc_test_all
--после выполнения процедуры
select * from test_table 

Как видите, все у нас отработало как надо, другими словами процедура my_proc_test сработала все три раза, а мы всего лишь один раз запустили дополнительную процедуру.

Второй вариант.

Используем только цикл в процедуре

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

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

CREATE PROCEDURE [dbo].[my_proc_test_all_v2]
AS
--объявляем переменные
DECLARE @number bigint
DECLARE @pole1 varchar(50)
DECLARE @pole2 varchar(50)
DECLARE @cnt int
DECLARE @i int 
--узнаем количество строк во временной таблице
select @cnt=count(*) from test_table_vrem
--задаем начальное значение идентификатора
set @i=1
WHILE @cnt >= @i
BEGIN
	--присваиваем значения нашим параметрам
	select @number=number, @pole1= pole1, @pole2=pole2 from test_table_vrem where number = @i
	--на каждую итерацию цикла запускаем нашу основную процедуру с нужными параметрами   
	exec dbo.my_proc_test @number, @pole1, @pole2
	--увеличиваем шаг
	set @i= @i+1
	
END
GO

И проверяем результат, но для начала очистим нашу таблицу, так как мы же ее только что уже заполнили по средствам процедуры my_proc_test_all:

--очистим таблицу
delete test_table
--до выполнения процедуры
select * from test_table
--вызов процедуры
exec dbo.my_proc_test_all_v2
--после выполнения процедуры
select * from test_table

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

Похожие статьи:

info-comp.ru

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

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

Хочу рассказать о своем недавнем опыте оптимизации курсора в SQL Server.

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_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 намного роднее реляционный, а не навигационный подход.

community.terrasoft.ru

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

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

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

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

Курсор в 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. В курсоре видно самое последнее состояние данных.

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

www.intuit.ru

Создание и использование курсоров в СУБД MS SQL Server. — МегаЛекции

ALTER PROCEDURE dbo.sprocSymbolDependentAggsDriver2

    AS

    DECLARE @symbol nchar(10)
    DECLARE symbolCursor CURSOR
    STATIC FOR
    SELECT Symbol FROM tblSymbolsMain ORDER BY Symbol

    OPEN symbolCursor
    FETCH NEXT FROM symbolCursor INTO @symbol
    WHILE @@FETCH_STATUS = 0
        SET @symbol = @symbol + ': Test.'
        FETCH NEXT FROM symbolCursor INTO @symbol

    CLOSE symbolCursor
    DEALLOCATE symbolCursor

--Create somewhere to put the proc data and fill it. This will give us something tangible to query against.
CREATE TABLE #NotPaid
(
  bookingID INT ,
  totalCost DECIMAL(7, 2) ,
  bookingDate DATE ,
  paymentConfirmation VARCHAR(50) ,
  customersID INT ,
  firstname VARCHAR(50) ,
  surname VARCHAR(50) ,
  contactNum VARCHAR(50)
)

CREATE TABLE #HasPaid
(
  bookingID INT ,
  totalCost DECIMAL(7, 2) ,
  bookingDate DATE ,
  paymentConfirmation VARCHAR(50) ,
  customersID INT ,
  firstname VARCHAR(50) ,
  surname VARCHAR(50) ,
  contactNum VARCHAR(50)
)

INSERT  #NotPaid
    EXEC AdminReport1

INSERT  #HasPaid
    EXEC AdminReport2


--Variables for use in our cursor. I'm only loading one table up as a demonstration.
DECLARE @bookingID INT ,
@totalCost DECIMAL(7, 2) ,
@bookingDate DATE ,
@paymentConfirmation VARCHAR(50) ,
@customersID INT ,
@firstname VARCHAR(50) ,
@surname VARCHAR(50) ,
@contactNum VARCHAR(50)

DECLARE @Library CURSOR

SET 
@Library = CURSOR FOR
SELECT bookingID ,
totalCost ,
bookingDate ,
paymentConfirmation ,
customersID ,
firstname, 
surname ,
contactNum FROM #HasPaid

--Run the cursor and print out the variables as we loop again. Any formatting will need to be done in here. Again, I'm not sure this is the best way to achieve what you are trying to achieve.
OPEN @Library
FETCH NEXT FROM @getProductID INTO @bookingID, @totalCost, @bookingDate,
@paymentConfirmation, @customersID, @firstname, @surname, @contactNum 

PRINT 'I''m a header line'

WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @firstname + ' Hasnt paid! His booking date is ' + STR(@bookingDate)
    + '. This is the middle part'
FETCH NEXT FROM @Library INTO @bookingID, @totalCost, @bookingDate,
    @paymentConfirmation, @customersID, @firstname, @surname, @contactNum 
END
PRINT 'I''m at the bottom'

CLOSE @Library
DEALLOCATE @Library

GO