Использование курсоров и циклов в Transact-SQL. Sql cursor пример
Курсоры в MSSQL - перебор выборки в цикле. / MS SQL
Для иллюстрации использования курсора создадим процедуру, которая будет выбирать данные из одной таблицы, перебирать их в курсоре анализируя, есть ли такие данные во второй таблице и вставлять в третью таблицу, если данные записи удовлетворяют определённым критериям.
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 Вот собственно и всё.Использование курсоров и циклов в 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) , другими словами запустить ее массово для каждой строки источника.
И для примера создадим такой источник, у нас это будет простая таблица test_table_vrem, а у Вас это может быть, как я уже сказал свой источник, например временная таблица или представление:
Заполним ее тестовыми данными:
И теперь нашу процедуру необходимо запустить для каждой строки, т.е. три раза с разными параметрами. Как Вы понимаете значения этих полей и есть наши параметры, другими словами, если бы мы запускали нашу процедуру вручную, это выглядело вот так:
exec my_proc_test 1, 'pole1_str1', 'pole2_str1'
И так еще три раза, с соответствующими параметрами.
Но нам так не охота, поэтому мы напишем еще одну дополнительную процедуру, которая и будет запускать нашу основную процедуру столько раз, сколько нам нужно.
Первый вариант.
Используем курсор и цикл в процедуре
Перейдем сразу к делу и напишем процедуру (my_proc_test_all), код я как всегда прокомментировал:
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:
Как и ожидалось результат такой же, но уже без использования курсоров. Какой вариант использовать решать Вам, первый вариант хорош, тем, что в принципе не нужна нумерация, но как Вы знаете, курсоры работают достаточно долго, если строк в курсоре будет много, а второй вариант хорош тем, что отработает, как мне кажется быстрей, опять же таки, если строк будет много, но нужна нумерация, лично мне нравится вариант с курсором, а вообще решать Вам может Вы сами придумаете что-то более удобное, я всего лишь показал основы того, как можно реализовать поставленную задачу. Удачи!
Похожие статьи:
info-comp.ru
T-SQL синтаксис курсоров - стр.24
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
INSERT #temp DEFAULT VALUES
INSERT #temp DEFAULT VALUES
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
GO
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
INSERT #temp DEFAULT VALUES
INSERT #temp DEFAULT VALUES
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
GO
CREATE TABLE #temp (k1 int identity, c1 int NULL)
INSERT #temp DEFAULT VALUES
INSERT #temp DEFAULT VALUES
INSERT #temp DEFAULT VALUES
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
GO
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
INSERT #temp DEFAULT VALUES
INSERT #temp DEFAULT VALUES
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
GO
DROP TABLE #temp
NumberOfGLOBALCursorRows
------------------------
4
NumberOfLOCALCursorRows
-----------------------
3
Для динамических курсоров @@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
INSERT #cursortest DEFAULT VALUES
INSERT #cursortest DEFAULT VALUES
INSERT #cursortest DEFAULT VALUES
INSERT #cursortest DEFAULT VALUES
INSERT #cursortest DEFAULT VALUES
INSERT #cursortest DEFAULT VALUES
INSERT #cursortest DEFAULT VALUES
INSERT #cursortest DEFAULT VALUES
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
GO
DROP TABLE #cursortest
k1
-----------
1
k1
-----------
4
k1
-----------
3
k1
-----------
10
k1
-----------
1
FETCH можно использовать для получения результирующего множества, но обычно эта команда служит для заполнения локальных переменных данными из таблицы. Предложение INTO команды FETCH позволяет присваивать полученные значения локальным переменным. Вот пример:
SET NOCOUNT ON
CREATE TABLE #cursortest (k1 int identity)
INSERT #cursortest DEFAULT VALUES
INSERT #cursortest DEFAULT VALUES
INSERT #cursortest DEFAULT VALUES
INSERT #cursortest DEFAULT VALUES
INSERT #cursortest DEFAULT VALUES
INSERT #cursortest DEFAULT VALUES
INSERT #cursortest DEFAULT VALUES
INSERT #cursortest DEFAULT VALUES
INSERT #cursortest DEFAULT VALUES
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
GO
DROP TABLE #cursortest
-----------
1
-----------
2
-----------
3
-----------
4
-----------
5
-----------
6
-----------
7
-----------
8
-----------
9
-----------
10
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
GO
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
-------------------------------------------
70
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
--------------------------------------------
0
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
INSERT #temp DEFAULT VALUES
INSERT #temp DEFAULT VALUES
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
GO
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
GO
SET CURSOR_CLOSE_ON_COMMIT OFF
qty
------
5
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
------
5
qty
------
3
qty
------
30
Несмотря на тот факт, что транзакция былаотменена, в то время как наш динамический курсор был открыт, это не повлияло на курсор. Это противоречит поведению сервера, описанному в документации.
Конфигурирование создания по умолчанию глобальных или локальных курсоров
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
textarchive.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
ОБЪЯВЛЕНИЕ КУРСОРА | Oracle PL/SQL •MySQL •SQL Server
Это учебное пособие объясняет, как объявить курсор в Oracle/PLSQL c синтаксисом и примерами.
Описание
Курсор это определенный оператор SELECT, который объявлен в PLSQL коде. Рассмотрим три различных синтаксиса объявления курсора.
КУРСОР БЕЗ ПАРАМЕТРОВ (ПРОСТОЙ)
Объявление курсора без каких-либо параметров, простой курсор.
Синтаксис
CURSOR cursor_nameISSELECT_statement;
Например, вы можете определить курсор под названием c1, как показано ниже.
CURSOR c1 IS SELECT course_number FROM courses_tbl WHERE course_name = name_in;
CURSOR c1 IS SELECT course_number FROM courses_tbl WHERE course_name = name_in; |
Результирующий набор этого курсора все course_numbers, у которых course_name соответствует переменной name_in.Ниже приведена функция, которая использует этот курсор.
CREATE OR REPLACE Function FindCourse ( name_in IN varchar2 ) RETURN number IS cnumber number; CURSOR c1 IS SELECT course_number FROM courses_tbl WHERE course_name = name_in; BEGIN OPEN c1; FETCH c1 INTO cnumber; if c1%notfound then cnumber := 9999; end if; CLOSE c1; RETURN cnumber; END;
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
CREATE OR REPLACE Function FindCourse ( name_in IN varchar2 ) RETURN number IS cnumber number; CURSOR c1 IS SELECT course_number FROM courses_tbl WHERE course_name = name_in; BEGIN OPEN c1; FETCH c1 INTO cnumber; if c1%notfound then cnumber := 9999; end if; CLOSE c1; RETURN cnumber; END; |
КУРСОР С ПАРАМЕТРАМИ
Объявим курсор с параметрами.
Синтаксис
CURSOR cursor_name (parameter_list)ISSELECT_statement;
Например, вы можете определить курсор под названием c2, как показано ниже.
CURSOR c2 (subject_id_in IN varchar2) IS SELECT course_number FROM courses_tbl WHERE subject_id = subject_id_in;
CURSOR c2 (subject_id_in IN varchar2) IS SELECT course_number FROM courses_tbl WHERE subject_id = subject_id_in; |
Результирующий набор этого курсора все course_numbers, у которых subject_id соответствует subject_id полученные курсором с помощью параметра subject_id_in.
КУРСОР С ВОЗВРАЩАЮЩИМ УСЛОВИЕМ
Наконец, мы можем объявить курсор с возвращающим условием.
Синтаксис
CURSOR cursor_nameRETURN field%ROWTYPEISSELECT_statement;
Например, вы можете определить курсор под названием c3, как показано ниже.
CURSOR c3 RETURN courses_tbl%ROWTYPE IS SELECT * FROM courses_tbl WHERE subject = 'Mathematics';
CURSOR c3 RETURN courses_tbl%ROWTYPE IS SELECT * FROM courses_tbl WHERE subject = 'Mathematics'; |
Результирующим набором этого курсора будут все столбцы course_tbl, у которых subject будет Mathematics.
oracleplsql.ru
Основы SQL. Лекция 13. (intuit.ru) - Основы SQL - - Каталог статей
Понятие курсора
Запрос к реляционной базе данных обычно возвращает несколько рядов (записей) данных, но приложение за один раз обрабатывает лишь одну запись. Даже если оно имеет дело одновременно с несколькими рядами (например, выводит данные в форме электронных таблиц), их количество по-прежнему ограничено. Кроме того, при модификации, удалении или добавлении данных рабочей единицей является ряд. В этой ситуации на первый план выступает концепция курсора, и в таком контексте курсор – указатель на ряд.
Курсор в 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. В курсоре видно самое последнее состояние данных.
Статические курсоры обеспечивают стабильный взгляд на данные. Они хороши для систем "складирования" информации: приложений для систем отчетности или для статистических и аналитических целей. Кроме того, статический курсор лучше других справляется с выборкой большого количества данных. Напротив, в системах электронных покупок или резервирования билетов необходимо динамическое восприятие обновляемой информации по мере внесения изменений. В таких случаях используется динамический курсор. В этих приложениях объем передаваемых данных, как правило, невелик, а доступ к ним осуществляется на уровне рядов (отдельных записей). Групповой доступ встречается очень редко.
Управление курсором в среде MS SQL Server
Управление курсором реализуется путем выполнения следующих команд:
- DECLARE – создание или объявление курсора;
- OPEN – открытие курсора, т.е. наполнение его данными;
- FETCH – выборка из курсора и изменение строк данных с помощью курсора;
- CLOSE – закрытие курсора;
- DEALLOCATE – освобождение курсора, т.е. удаление курсора как объекта.
Объявление курсора
В стандарте SQL для создания курсора предусмотрена следующая команда:
<создание_курсора>::=DECLARE имя_курсора [INSENSITIVE][SCROLL] CURSORFOR SELECT_оператор[FOR { READ_ONLY | UPDATE [OF имя_столбца[,...n]]}]При использовании ключевого слова INSENSITIVE будет создан статический курсор. Изменения данных не разрешаются, кроме того, не отображаются изменения, сделанные другими пользователями. Если ключевое слово INSENSITIVE отсутствует, создается динамический курсор.
При указании ключевого слова SCROLL созданный курсор можно прокручивать в любом направлении, что позволяет применять любые команды выборки. Если этот аргумент опускается, то курсор окажется последовательным, т.е. его просмотр будет возможен только в одном направлении – от начала к концу.
SELECT-оператор задает тело запроса SELECT, с помощью которого определяется результирующий набор строк курсора.
При указании аргумента FOR READ_ONLY создается курсор "только для чтения", и никакие модификации данных не разрешаются. Он отличается от статического, хотя последний также не позволяет менять данные. В качестве курсора "только для чтения" может быть объявлен динамический курсор, что позволит отображать изменения, сделанные другим пользователем.
Создание курсора с аргументом FOR UPDATE позволяет выполнять в курсоре изменение данных либо в указанных столбцах, либо, при отсутствии аргумента OF имя_столбца, во всех столбцах.
В среде MS SQL Server принят следующий синтаксис команды создания курсора:
<создание_курсора>::=DECLARE имя_курсора CURSOR [LOCAL | GLOBAL][FORWARD_ONLY | SCROLL][STATIC | KEYSET | DYNAMIC | FAST_FORWARD][READ_ONLY | SCROLL_LOCKS | OPTIMISTIC][TYPE_WARNING]FOR SELECT_оператор[FOR UPDATE [OF имя_столбца[,...n]]]При использовании ключевого слова LOCAL будет создан локальный курсор, который виден только в пределах создавшего его пакета, триггера, хранимой процедуры или пользовательской функции. По завершении работы пакета, триггера, процедуры или функции курсор неявно уничтожается. Чтобы передать содержимое курсора за пределы создавшей его конструкции, необходимо присвоить его параметру аргумент OUTPUT.
Если указано ключевое слово GLOBAL, создается глобальный курсор; он существует до закрытия текущего соединения.
При указании FORWARD_ONLY создается последовательный курсор; выборку данных можно осуществлять только в направлении от первой строки к последней.
При указании SCROLL создается прокручиваемый курсор; обращаться к данным можно в любом порядке и в любом направлении.
При указании STATIC создается статический курсор.
При указании KEYSET создается ключевой курсор.
При указании DYNAMIC создается динамический курсор.
Если для курсора READ_ONLY указать аргумент FAST_FORWARD, то созданный курсор будет оптимизирован для быстрого доступа к данным. Этот аргумент не может быть использован совместно с аргументами FORWARD_ONLY и OPTIMISTIC.
В курсоре, созданном с указанием аргумента OPTIMISTIC, запрещается изменение и удаление строк, которые были изменены после открытия курсора.
При указании аргумента TYPE_WARNING сервер будет информировать пользователя о неявном изменении типа курсора, если он несовместим с запросом SELECT.
Открытие курсора
Для открытия курсора и наполнения его данными из указанного при создании курсора запроса SELECT используется следующая команда:
OPEN {{[GLOBAL]имя_курсора }|@имя_переменной_курсора}После открытия курсора происходит выполнение связанного с ним оператора SELECT, выходные данные которого сохраняются в многоуровневой памяти.
Выборка данных из курсора
Сразу после открытия курсора можно выбрать его содержимое (результат выполнения соответствующего запроса) посредством следующей команды:
FETCH [[NEXT | PRIOR | FIRST | LAST| ABSOLUTE {номер_строки| @переменная_номера_строки}| RELATIVE {номер_строки | @переменная_номера_строки}]FROM ]{{[GLOBAL ]имя_курсора }|@имя_переменной_курсора }[INTO @имя_переменной [,...n]]При указании FIRST будет возвращена самая первая строка полного результирующего набора курсора, которая становится текущей строкой.
При указании LAST возвращается самая последняя строка курсора. Она же становится текущей строкой.
При указании NEXT возвращается строка, находящаяся в полном результирующем наборе сразу же после текущей. Теперь она становится текущей. По умолчанию команда FETCH использует именно этот способ выборки строк.
Ключевое слово PRIOR возвращает строку, находящуюся перед текущей. Она и становится текущей.
Аргумент ABSOLUTE {номер_строки | @переменная_номера_строки} возвращает строку по ее абсолютному порядковому номеру в полном результирующем наборе курсора. Номер строки можно задать с помощью константы или как имя переменной, в которой хранится номер строки. Переменная должна иметь целочисленный тип данных. Указываются как положительные, так и отрицательные значения. При указании положительного значения строка отсчитывается от начала набора, отрицательного – от конца. Выбранная строка становится текущей. Если указано нулевое значение, строка не возвращается.
Аргумент RELATIVE {кол_строки | @переменная_кол_строки} возвращает строку, находящуюся через указанное количество строк после текущей. Если указать отрицательное значение числа строк, то будет возвращена строка, находящаяся за указанное количество строк перед текущей. При указании нулевого значения возвратится текущая строка. Возвращенная строка становится текущей.
Чтобы открыть глобальный курсор, перед его именем требуется указать ключевое слово GLOBAL. Имя курсора также может быть указано с помощью переменной.
В конструкции INTO @имя_переменной [,...n] задается список переменных, в которых будут сохранены соответствующие значения столбцов возвращаемой строки. Порядок указания переменных должен соответствовать порядку столбцов в курсоре, а тип данных переменной – типу данных в столбце курсора. Если конструкция INTO не указана, то поведение команды FETCH будет напоминать поведение команды SELECT – данные выводятся на экран.
Изменение и удаление данных
Для выполнения изменений с помощью курсора необходимо выполнить команду UPDATE в следующем формате:
UPDATE имя_таблицы SET {имя_столбца={DEFAULT | NULL | выражение}}[,...n]WHERE CURRENT OF {{[GLOBAL] имя_курсора}|@имя_переменной_курсора}За одну операцию могут быть изменены несколько столбцов текущей строки курсора, но все они должны принадлежать одной таблице.
Для удаления данных посредством курсора используется команда DELETE в следующем формате:
DELETE имя_таблицыWHERE CURRENT OF {{[GLOBAL] имя_курсора}|@имя_переменной_курсора}В результате будет удалена строка, установленная текущей в курсоре.
Закрытие курсора
CLOSE {имя_курсора | @имя_переменной_курсора}После закрытия курсор становится недоступным для пользователей программы. При закрытии снимаются все блокировки, установленные в процессе его работы. Закрытие может применяться только к открытым курсорам. Закрытый, но не освобожденный курсор может быть повторно открыт. Не допускается закрывать неоткрытый курсор.
Освобождение курсора
Закрытие курсора необязательно освобождает ассоциированную с ним память. В некоторых реализациях нужно явным образом освободить ее с помощью оператора DEALLOCATE. После освобождения курсора освобождается и память, при этом становится возможным повторное использование имени курсора.
DEALLOCATE { имя_курсора | @имя_переменной_курсора }Для контроля достижения конца курсора рекомендуется применять функцию: @@FETCH_STATUS
Функция @@FETCH_STATUS возвращает:
0, если выборка завершилась успешно;
-1, если выборка завершилась неудачно вследствие попытки выборки строки, находящейся за пределами курсора;
-2, если выборка завершилась неудачно вследствие попытки обращения к удаленной или измененной строке.
DECLARE abc CURSOR SCROLL FORSELECT * FROM Клиент Пример 13.1. Объявление курсора.DECLARE @MyCursor CURSORSET @MyCursor=CURSOR LOCAL SCROLL FORSELECT * FROM Клиент Пример 13.2. Использование переменной для объявления курсора.DECLARE abc CURSOR GLOBAL SCROLL FORSELECT * FROM КлиентOPEN abc Пример 13.3. Объявление и открытие курсора.DECLARE @MyCursor CURSORSET @MyCursor=abc Пример 13.4. Использование переменной для переприсваивания курсора.Пример 13.5. Разработать курсор для вывода списка фирм и клиентов из Москвы.
DECLARE @firm VARCHAR(50),@fam VARCHAR(50),@message VARCHAR(80)PRINT ' Список клиентов'DECLARE klient_cursor CURSOR LOCAL FORSELECT Фирма, ФамилияFROM КлиентWHERE Город='Москва'ORDER BY Фирма, ФамилияOPEN klient_cursorFETCH NEXT FROM klient_cursor INTO @firm, @famWHILE @@FETCH_STATUS=0BEGINSELECT @message='Клиент '+@fam+' Фирма '+ @firmPRINT @message
-- переход к следующему клиенту--
FETCH NEXT FROM klient_cursor INTO @firm, @famENDCLOSE klient_cursorDEALLOCATE klient_cursor
Пример 13.5. Курсор для вывода списка фирм и клиентов из Москвы.Пример 13.6. Разработать курсор для вывода списка приобретенных клиентами из Москвы товаров и их общей стоимости. В один курсор заносятся все московские клиенты, затем для каждой строки курсора, т.е. для каждого клиента, определяется и распечатывается другой курсор – его покупки. Подсчитывается общая стоимость покупок клиента.
DECLARE @id_kl INT,@firm VARCHAR(50),@fam VARCHAR(50), @message VARCHAR(80),@nam VARCHAR(50),@d DATETIME,@p INT,@s INTSET @s=0PRINT ' Список покупок'DECLARE klient_cursor CURSOR LOCAL FORSELECT КодКлиента, Фирма, ФамилияFROM КлиентWHERE Город='Москва'ORDER BY Фирма, ФамилияOPEN klient_cursorFETCH NEXT FROM klient_cursorINTO @id_kl, @firm, @famWHILE @@FETCH_STATUS=0BEGINSELECT @message='Клиент '+@fam+' Фирма '+ @firmPRINT @messageSELECT @message='Наименование товара Датапокупки Стоимость'PRINT @messageDECLARE tovar_cursor CURSOR FORSELECT Товар.Название, Сделка.Дата,Товар.Цена*Сделка.Количество ASСтоимостьFROM Товар INNER JOIN Сделка ON Товар.КодТовара=Сделка.КодТовараWHERE Сделка.КодКлиента=@id_kl
OPEN tovar_cursorFETCH NEXT FROM tovar_cursor INTO @nam, @d, @pIF @@FETCH_STATUS<>0PRINT ' Нет покупок'WHILE @@FETCH_STATUS=0BEGINSELECT @message=' '+@nam+' '+CAST(@d AS CHAR(12))+' '+CAST(@p AS CHAR(6))PRINT @messageSET @s=@s+@pFETCH NEXT FROM tovar_cursorINTO @nam, @d, @p ENDCLOSE tovar_cursorDEALLOCATE tovar_cursor
SELECT @message='Общая стоимость '+CAST(@s AS CHAR(6))PRINT @message
-- переход к следующему клиенту--
FETCH NEXT FROM klient_cursor INTO @id_kl, @firm, @famENDCLOSE klient_cursorDEALLOCATE klient_cursor
Пример 13.6. Курсор для вывода списка приобретенных клиентами из Москвы товаров и их общей стоимости.Пример 13.7. Разработать прокручиваемый курсор для клиентов из Москвы. Если номер телефона начинается на 1, удалить клиента с таким номером и в первой записи курсора заменить первую цифру в номере телефона на 4.
DECLARE @firm VARCHAR(50),@fam VARCHAR(50),@tel VARCHAR(8), @message VARCHAR(80)PRINT ' Список клиентов'DECLARE klient_cursor CURSOR GLOBAL SCROLLKEYSET FORSELECT Фирма, Фамилия, ТелефонFROM КлиентWHERE Город='Москва'ORDER BY Фирма, ФамилияFOR UPDATEOPEN klient_cursorFETCH NEXT FROM klient_cursor INTO @firm, @fam, @telWHILE @@FETCH_STATUS=0BEGIN SELECT @message='Клиент '+@fam+' Фирма '+@firm ' Телефон '+ @telPRINT @message-- если номер телефона начинается на 1, -- удалить клиента с таким номеромIF @tel LIKE ‘1%’DELETE КлиентWHERE CURRENT OF klient_cursorELSE
-- переход к следующему клиенту
FETCH NEXT FROM klient_cursor INTO @firm, @fam, @telEND
FETCH ABSOLUTE 1 FROM klient_cursor INTO @firm, @fam, @tel
-- в первой записи заменить первую цифру в-- номере телефона на 4
UPDATE Клиент SET Телефон=’4’ + RIGHT(@tel,LEN(@tel)-1))WHERE CURRENT OF klient_cursorSELECT @message='Клиент '+@fam+' Фирма '+@firm ' Телефон '+ @telPRINT @messageCLOSE klient_cursorDEALLOCATE klient_cursor
Пример 13.7. Прокручиваемый курсор для клиентов из Москвы.Пример 13.8. Использование курсора как выходного параметра процедуры. Процедура возвращает набор данных – список товаров.
CREATE PROC my_proc@cur CURSOR VARYING OUTPUTASSET @cur=CURSOR FORWARD_ONLY STATIC FORSELECT Название FROM ТоварOPEN @cur Пример 13.8. Использование курсора как выходного параметра процедуры.Вызов процедуры и вывод на печать данных из выходного курсора осуществляется следующим образом:
DECLARE @my_cur CURSORDECLARE @n VARCHAR(20)EXEC my_proc @cur=@my_cur OUTPUTFETCH NEXT FROM @my_cur INTO @nSELECT @nWHILE (@@FETCH_STATUS=0)BEGINFETCH NEXT FROM @my_cur INTO @nSELECT @nENDCLOSE @my_curDEALLOCATE @my_curfit141.ucoz.ru
Оптимизация SQL Server при работе с курсорами
Здравствуй, человек-читатель блогов на 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 FORselect_statement
для небольшого кол-ва записей:DECLARE cursor_name CURSOR LOCAL FAST_FORWARD FORselect_statement
Теперь перейдем к практике (что собственно и подтолкнуло меня к писанине сего).Испокон веков при объявлении курсора я применял конструкцию DECLARE ... CURSOR LOCAL FOR...При разработке интеграции с одной очень нехорошей базой, в которой нет ни одного индекса и не одного ключа, я применил тот же подход при объявлении курсоров, что и всегда. Выборка одного курсора содержала 225 000 записей. В результате процесс импорта данных из такой базы занял 15 часов 14 минут !!! И хотя импорт и был первичный (т.е. одноразовый), но даже для нормального тестирования такого импорта потребовалось бы несколько суток! После замены вышеназванной конструкции при объявлении курсора на DECLARE .. CURSOR LOCAL FORWARD_ONLY STATIC FOR.. весь процесс импорта занял ... внимание ... 10 минут 5 секунд !!! Так что игра точно стоит свеч.Хочу повториться, что идеальный вариант - это все же не использовать курсоры вообще - для СУБД MS SQL намного роднее реляционный, а не навигационный подход.
community.terrasoft.ru
