Курсоры (SQL Server) — SQL Server
- Статья
- Чтение занимает 6 мин
Применимо к: SQL Server (все поддерживаемые версии) Azure SQL Управляемый экземпляр SQL Azure базы данных
Операции в реляционной базе данных выполняются над множеством строк. Например, набор строк, возвращаемый инструкцией SELECT, содержит все строки, которые удовлетворяют условиям, указанным в предложении WHERE инструкции. Такой полный набор строк, возвращаемых инструкцией, называется результирующим набором. Приложения, особенно интерактивные, не всегда эффективно работают с результирующим набором как с единым целым.
Курсоры позволяют усовершенствовать обработку результатов:
позиционируясь на отдельные строки результирующего набора;
получая одну или несколько строк от текущей позиции в результирующем наборе;
поддерживая изменение данных в строках в текущей позиции результирующего набора;
поддерживая разные уровни видимости изменений, сделанных другими пользователями для данных, представленных в результирующем наборе;
Предоставление инструкций Transact-SQL в скриптах, хранимых процедурах и триггерах доступа к данным в результирующем наборе.
Совет
В некоторых сценариях при наличии первичного ключа для таблицы цикл WHILE может использоваться вместо курсора, что позволяет избежать недостатков, присущих курсорам.
Однако существуют сценарии, когда курсоры не только неизбежны, но и действительно необходимы. В этом случае, если не требуется выполнять обновление таблиц с учетом курсора, используйте курсоры firehose, то есть курсоры быстрой прокрутки и только для чтения.
Реализации курсоров
SQL Server поддерживает три способа реализации курсоров.
курсоры Transact-SQL
Курсоры Transact-SQL основаны на синтаксисе DECLARE CURSOR и используются главным образом в скриптах Transact-SQL, хранимых процедурах и триггерах. Курсоры Transact-SQL реализуются на сервере и управляются инструкциями Transact-SQL, отправляемыми с клиента на сервер. Они также могут содержаться в пакетах, хранимых процедурах или триггерах.
Серверные курсоры интерфейса прикладного программирования (API)
Курсоры API поддерживают функции курсоров API в OLE DB и ODBC. Курсоры API реализуются на сервере. Всякий раз, когда клиентское приложение вызывает функцию курсора API, поставщик OLE DB или драйвер ODBC для собственного клиента SQL Server передает требование на сервер для выполнения действия в отношении серверного курсора API.
Клиентские курсоры
Клиентские курсоры реализуются внутренне драйвером ODBC для собственного клиента SQL Server и библиотекой DLL, реализующей API-интерфейс ADO. Клиентские курсоры реализуются посредством кэширования всех строк результирующего набора на клиенте. Каждый раз, когда клиентское приложение вызывает функцию курсора API, драйвер ODBC для собственного клиента SQL Server или ADO DLL выполняет операцию курсора на строках результирующего набора, кэшированных на клиенте.
Типы курсоров
SQL Server поддерживает четыре типа курсоров.
Примечание
Курсоры могут использовать рабочие таблицы базы данных tempdb. Как и агрегирование или операции сортировки, которые выполняют сброс, они влияют на возможности ввода-вывода и являются потенциальным узким местом производительности. Курсоры STATIC используют рабочие таблицы с начала действия. Дополнительные сведения см. в описании рабочих таблиц в руководстве по архитектуре обработки запросов.
Однонаправленный
Однонаправленный курсор указывается как FORWARD_ONLY и READ_ONLY и не поддерживает прокрутку.
Он также называется курсором firehose и поддерживает только получение строк последовательно, от начала до конца курсора. Строки нельзя получить из базы данных, пока они не будут выбраны. Результаты всех инструкций INSERT, UPDATE и DELETE, влияющих на строки результирующего набора (выполненных текущим пользователем или зафиксированных другими пользователями), отображаются как строки, выбранные из курсора.
Так как курсор не может быть прокручен назад, большинство изменений, сделанных в строках базы данных после извлечения сроки, не видны через курсор. Если значение, использованное для определения положения строки в результирующем наборе, модифицируется, например в случае обновления столбца, входящего в кластеризованный индекс, то значение видимо через курсор.
Хотя в моделях курсоров API базы данных курсор последовательного доступа рассматривается как курсор отдельного типа, в SQL Server принят другой подход. SQL Server принимает однонаправленность и возможность прокрутки курсоров как параметры, которые могут быть применены к статическим, управляемым набором ключей и динамическим курсорам.
Курсоры Transact-SQL поддерживают статические, управляемые набором ключей и динамические курсоры. Модели курсора API базы данных предполагают, что статические, управляемые набором ключей и динамические курсоры всегда могут быть прокручены. Если атрибут или свойство курсора API базы данных установлены в значение «однонаправленный», SQL Server реализует это как однонаправленный динамический курсор.
Статические
Полный результирующий набор статического курсора создается в базе данных tempdb при открытии курсора. Статический курсор всегда отображает результирующий набор точно в том виде, в котором он был при открытии курсора. Статическими курсорами обнаруживаются лишь некоторые изменения или не обнаруживаются вовсе, но при этом в процессе прокрутки такие курсоры потребляют сравнительно мало ресурсов.
Курсор не отражает изменения в базе данных, влияющие на вхождение в результирующий набор или изменяющие значения в столбцах строк, составляющих набор строк.
Статический курсор не отображает новые строки, вставленные в базу данных после открытия курсора, даже если они соответствуют критериям поиска инструкции SELECT курсора. Если входящие в результирующий набор строки обновляются другими пользователями, то новые значения данных в статическом курсоре не отображаются. Статический курсор продолжает отображать строки, удаленные из базы данных после открытия курсора. Операции UPDATE, INSERT и DELETE не отображаются в статическом курсоре (до тех пор, пока курсор не будет закрыт и открыт повторно), не отображаются даже изменения, сделанные в том же соединении, в котором был открыт курсор.
Примечание
SQL Server статические курсоры всегда доступны только для чтения.
Примечание
Так как результирующий набор статического курсора хранится в рабочей таблице базы данных tempdb, то размер строк результирующего набора не может превышать максимальный размер строк таблицы SQL Server.
Дополнительные сведения см. в описании рабочих таблиц в руководстве по архитектуре обработки запросов. Дополнительные сведения о максимальном размере строк см. в разделе Спецификации максимально допустимых параметров SQL Server.
Transact-SQL использует термин без учета для статических курсоров. Некоторые интерфейсы API баз данных называют их курсорами моментальных снимков.
Keyset
Членство и порядок строк в курсоре, управляемом набором ключей, являются фиксированными при открытии курсора. Такие курсоры управляются с помощью набора уникальных идентификаторов — ключей. Ключи создаются из набора столбцов, который уникально идентифицирует строки результирующего набора. Набор ключей — это набор ключевых значений всех строк, попадающих под действие инструкции SELECT на момент открытия курсора. Набор ключей, управляющий курсором, создается в базе данных tempdb при открытии курсора.
Динамический
Динамические курсоры — это противоположность статических курсоров.
Динамические курсоры отражают все изменения строк в результирующем наборе при прокрутке курсора. Значения типа данных, порядок и членство строк в результирующем наборе могут меняться для каждой выборки. Все инструкции UPDATE, INSERT и DELETE, выполняемые пользователями, видимы посредством курсора. Обновления отображаются немедленно, если они выполняются через курсор с помощью функции API, такой как SQLSetPos или предложение Transact-SQLWHERE CURRENT OF. Обновления, сделанные вне курсора, не видны до момента фиксации, если только уровень изоляции транзакций с курсорами не имеет значение READ UNCOMMITTED. Дополнительные сведения об уровнях изоляции см. в разделе SET TRANSACTION ISOLATION LEVEL (Transact-SQL).
Примечание
В планах динамических курсоров никогда не используются пространственные индексы.
Запрос курсора
SQL Server поддерживает два метода запроса курсоров.
Transact-SQL
Язык Transact-SQL поддерживает синтаксис для использования курсоров, смоделированных после синтаксиса ISO-курсора.
API-функции курсоров базы данных.
SQL Server поддерживает функциональность курсоров для следующих API-интерфейсов баз данных:
Оба этих способа никогда не должны использоваться в приложении одновременно. Приложение, использующее API для указания поведения курсора, не должно выполнять инструкцию Transact-SQL DECLARE CURSOR, чтобы также запросить курсор Transact-SQL. Инструкция DECLARE CURSOR может использоваться только в том случае, если все атрибуты API-курсоров будут установлены в значения по умолчанию.
Если ни курсор Transact-SQL, ни курсор API не запрошены, SQL Server по умолчанию возвращает полный результирующий набор, известный как результирующий набор по умолчанию, приложению.
Обработка курсоров
Курсоры Transact-SQL и курсоры API имеют другой синтаксис, но следующий общий процесс используется со всеми SQL Server курсорами:
Свяжите курсор с результирующий набор инструкции Transact-SQL и определите характеристики курсора, например, можно ли обновлять строки в курсоре.
Выполните инструкцию Transact-SQL, чтобы заполнить курсор.
Получить в курсор необходимые строки. Операция получения в курсор одной и более строк называется выборкой. Выполнение серии выборок для получения строк в прямом или обратном направлении называется прокруткой.
При необходимости выполнить операции изменения (обновления или удаления) строки в текущей позиции курсора.
Закрыть курсор.
См. также
Режимы работы курсоров
Способы реализации курсоров
См. также:
DECLARE CURSOR (Transact-SQL)
Курсоры (Transact-SQL)
Функции работы с курсорами (Transact-SQL)
Хранимые процедуры курсора (Transact-SQL)
SET TRANSACTION ISOLATION LEVEL (Transact-SQL)
Использование курсоров и циклов в Transact-SQL | Info-Comp.ru
Сегодня будем рассматривать очень много чего интересного, например как запустить уже созданную процедуру, которая принимает параметры, массово, т.е. не только со статическим параметрами, а с параметрами, которые будут меняться, например, на основе какой-нибудь таблицы, как обычная функция, и в этом нам помогут как раз 
Как Вы поняли, курсоры и циклы мы будем рассматривать применимо к конкретной задачи. А какой задачи, сейчас расскажу.
Существует процедура, которая выполняет какие-то действия, которые не может выполнить обычная функция SQL например, расчеты и insert на основе этих расчетов. И Вы ее запускаете, например вот так:
EXEC test_PROCEDURE par1, par2
Другими словами Вы запускаете ее только с теми параметрами, которые были указаны, но если Вам необходимо запустить данную процедуру скажем 100, 200 или еще более раз, то согласитесь это не очень удобно, т.е. долго. Было бы намного проще, если бы мы взяли и запускали процедуру как обычную функцию в запросе select, например:
SELECT my_fun(id) FROM test_table
Другими словами функция отработает на каждую запись таблицы test_table, но как Вы знаете процедуру так использовать нельзя. Но существует способ, который поможет нам осуществить задуманное, точнее даже два способа первый это с использованием курсора и цикла и второй это просто с использованием цикла, но уже без курсора.
Оба варианта подразумевают, что мы будем создавать дополнительную процедуру, которую в дальнейшем мы будем запускать.
Примечание! Все примеры будем писать в СУБД MS SQL Server 2008, используя Management Studio. Также все нижеперечисленные действия требуют определённых знаний языка SQL, а точнее Transact-SQL. Начинающим могу посоветовать посмотреть мой видеокурс по T-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, а у Вас это может быть, как я уже сказал свой источник, например временная таблица или представление:
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’
И так еще три раза, с соответствующими параметрами.
Но нам так не охота, поэтому мы напишем еще одну дополнительную процедуру, которая и будет запускать нашу основную процедуру столько раз, сколько нам нужно.
Первый вариант.
Используем курсор и цикл в процедуре
Перейдем сразу к делу и напишем процедуру (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:
--очистим таблицу DELETE test_table --до выполнения процедуры SELECT * FROM test_table --вызов процедуры EXEC dbo.
my_proc_test_all_v2
--после выполнения процедуры
SELECT * FROM test_table
Как и ожидалось результат такой же, но уже без использования курсоров. Какой вариант использовать решать Вам, первый вариант хорош, тем, что в принципе не нужна нумерация, но как Вы знаете, курсоры работают достаточно долго, если строк в курсоре будет много, а второй вариант хорош тем, что отработает, как мне кажется быстрей, опять же таки, если строк будет много, но нужна нумерация, лично мне нравится вариант с курсором, а вообще решать Вам может Вы сами придумаете что-то более удобное, я всего лишь показал основы того, как можно реализовать поставленную задачу. Удачи!
Заметка! Если Вас интересует SQL и T-SQL, рекомендую посмотреть мои видеокурсы по T-SQL, с помощью которых Вы «с нуля» научитесь работать с SQL и программировать с использованием языка T-SQL в Microsoft SQL Server.
Курсоры в MSSQL — перебор выборки в цикле.
- Новые
- Лучшие
- Все
Курсоры в MSSQL — перебор выборки в цикле.
MS SQL — по необходимости
Команды манипулирования данными SELECT, UPDATE, DELETE работают сразу с группами строк. Эти группы, вплоть до отдельных строк, можно выбрать с помощью опции WHERE. А если надо перебрать строки некоторой таблицы последовательно, одну за другой? На этот случай в языке SQL существуют курсоры. Курсор (current set of record) – временный набор строк, которые можно перебирать последовательно, с первой до последней.
При работе с курсорами используются следующие команды.
Объявление курсора:
DECLARE имя_курсора CURSOR FOR SELECT текст_запроса
Любой курсор создается на основе некоторого оператора SELECT.
Открытие курсора:
OPEN имя_курсора
Для того чтобы с помощью курсора можно было читать строки, его надо обязательно открыть.
Чтение следующей строки из курсора:
FETCH имя_курсора INTO список_переменных
Переменные в списке должны быть в том же количестве и того е типа, что и столбцы курсора.
Глобальная переменная @@FETCH_STATUS принимает ненулевое значение, если строк в курсоре больше нет. Если же набор строк еще не исчерпан, то @@FETCH_STATUS равна нулю, и оператор FETCH перепишет значения полей из текущей строки в переменные.
Закрытие курсора:
CLOSE имя_курсора
Для удаления курсора из памяти используется команда
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
Вот собственно и всё.
MSSQL курсоры
- Популярное
Установка русской кодировки на уже созданную базу данных (смена COLLATION)
Полезный пример изменения кодировки (COLLATION) на уже созданной базе данных. В данном примере устан (читать далее…)
473
Чистка логов базы данных MSSQL
Вообще процесс чистки логов должен проходить планово, и следить за этим и настраивать должен професс (читать далее…)
214
Пример MERGE в MSSQL T-SQL
Простой пример MERGE для TSQL.
В примере подразумевается, что мы оперируем двумя одинаковыми по стру (читать далее…)
164
MSSQL — передача таблицы или списка значений в процедуру ( C# .NET )
Часто бывает необходимость передать за один раз некоторый набор данных в процедуру, в этой публикаци (читать далее…)
130
Получение полей таблицы в MSSQL — TSQL
Этот запрос возвращает набор полей таблицы со всеми характеристиками. Метод также применим и замечат (читать далее…)
103
НОУ ИНТУИТ | Лекция | Курсоры: принципы работы
< Лекция 12 || Лекция 13: 123 || Лекция 14 >
Аннотация: Дается определение курсора.
Приводится описание его типов и
поведения: статические, динамические, последовательные и ключевые
курсоры. Описываются принципы управления курсором: создание и
открытие курсора, считывание данных, закрытие курсора. Приводятся
примеры программирования курсора.
Ключевые слова: запрос, приложение, запись, курсор, указатель, базы данных, SQL, действия с курсором, объявление курсора, открытие курсора, выборка из курсора, изменение данных в курсоре, закрытие курсора, освобождение курсора, определение, память, освобождение памяти, операции, server, виды курсоров, доступ, стабильность, последовательный курсор, прокручиваемый курсор, пользователь, статический курсор, динамический курсор, сервер, курсор, управляемый набором ключей, удаление данных из курсора, информация, драйвер, управление курсором, deallocation, Prior, функция, стоимость, курсор как выходной параметр процедуры, список, вывод, печать
Понятие курсора
Запрос к реляционной базе данных обычно возвращает несколько рядов
(записей) данных, но приложение за один раз обрабатывает лишь одну
запись.
Даже если оно имеет дело одновременно с несколькими рядами
(например, выводит данные в форме электронных таблиц), их количество
по-прежнему ограничено. Кроме того, при модификации, удалении или
добавлении данных рабочей единицей является ряд. В этой ситуации на
первый план выступает концепция курсора, и в таком контексте курсор –
указатель на ряд.
Курсор в SQL – это область в памяти базы данных, которая предназначена для хранения последнего оператора SQL. Если текущий оператор – запрос к базе данных, в памяти сохраняется и строка данных запроса, называемая текущим значением, или текущей строкой курсора. Указанная область в памяти поименована и доступна для прикладных программ.
Обычно курсоры используются для выбора из базы данных некоторого
подмножества хранимой в ней информации. В каждый момент времени
прикладной программой может быть проверена одна строка курсора. Курсоры часто применяются в операторах SQL, встроенных в написанные
на языках процедурного типа прикладные программы.
Некоторые из них
неявно создаются сервером базы данных, в то время как другие
определяются программистами.
В соответствии со стандартом SQL при работе с курсорами можно выделить следующие основные действия:
- создание или объявление курсора ;
- открытие курсора, т.е. наполнение его данными, которые сохраняются в многоуровневой памяти ;
- выборка из курсора и изменение с его помощью строк данных;
- закрытие курсора, после чего он становится недоступным для пользовательских программ;
- освобождение курсора, т.е. удаление курсора как объекта, поскольку его закрытие необязательно освобождает ассоциированную с ним память.
В разных реализациях определение курсора может иметь некоторые
отличия.
Так, например, иногда разработчик должен явным образом
освободить выделяемую для курсора память. После освобождения курсора
ассоциированная с ним память также освобождается. При этом становится
возможным повторное использование его имени. В других реализациях при закрытии курсора освобождение памяти происходит неявным образом.
Сразу после восстановления она становится доступной для других
операций: открытие другого курсора и т.д.
В некоторых случаях применение курсора неизбежно. Однако по возможности этого следует избегать и работать со стандартными командами обработки данных: SELECT, UPDATE, INSERT, DELETE. Помимо того, что курсоры не позволяют проводить операции изменения над всем объемом данных, скорость выполнения операций обработки данных посредством курсора заметно ниже, чем у стандартных средств SQL.
Реализация курсоров в среде MS SQL Server
SQL Server поддерживает три вида курсоров:
- курсоры сервера действуют на сервере и реализуют программный интерфейс приложений для ODBC, OLE DB, DB_Library;
- курсоры клиента реализуются на самом клиенте. Они выбирают весь результирующий набор строк из сервера и сохраняют его локально, что позволяет ускорить операции обработки данных за счет снижения потерь времени на выполнение сетевых операций.
intuit.ru/2010/edi»>
курсоры SQL применяются в основном внутри триггеров, хранимых
процедур и сценариев;Различные типы многопользовательских приложений требуют и различных
типов организации параллельного доступа к данным. Некоторым
приложениям необходим немедленный доступ к информации об изменениях в
базе данных. Это характерно для систем резервирования билетов. В
других случаях, например, в системах статистической отчетности, важна
стабильность данных, ведь если они постоянно модифицируются,
программы не смогут эффективно отображать информацию.
Различным
приложениям нужны разные реализации курсоров.
В среде SQL Server типы курсоров различаются по предоставляемым возможностям. Тип курсора определяется на стадии его создания и не может быть изменен. Некоторые типы курсоров могут обнаруживать изменения, сделанные другими пользователями в строках, включенных в результирующий набор. Однако SQL Server отслеживает изменения таких строк только на стадии обращения к строке и не позволяет отслеживать изменения, когда строка уже считана.
Курсоры делятся на две категории: последовательные и прокручиваемые. Последовательные позволяют выбирать данные только в одном направлении
– от начала к концу. Прокручиваемые же курсоры предоставляют большую
свободу действий – допускается перемещение в обоих направлениях и
переход к произвольной строке результирующего набора курсора.Если
программа способна модифицировать данные, на которые указывает курсор, он называется прокручиваемым и модифицируемым.
Говоря о курсорах, не следует забывать об изолированности транзакций. Когда
один пользователь модифицирует запись, другой читает ее при помощи
собственного курсора, более того, он может модифицировать ту же
запись, что делает необходимым соблюдение целостности данных.
SQL Server поддерживает курсоры статические, динамические, последовательные и управляемые набором ключей.
В схеме со статическим курсором информация читается из базы данных один раз и хранится в виде моментального снимка (по состоянию на некоторый момент времени), поэтому изменения, внесенные в базу данных другим пользователем, не видны. На время открытия курсора сервер устанавливает блокировку на все строки, включенные в его полный результирующий набор. Статический курсор не изменяется после создания и всегда отображает тот набор данных, который существовал на момент его открытия.
Если другие пользователи изменят в исходной таблице включенные в курсор данные, это никак не повлияет на статический курсор.
В статический курсор внести изменения невозможно, поэтому он всегда открывается в режиме «только для чтения».
Динамический курсор поддерживает данные в «живом» состоянии, но это требует затрат сетевых и программных ресурсов. При использовании динамических курсоров не создается полная копия исходных данных, а выполняется динамическая выборка из исходных таблиц только при обращении пользователя к тем или иным данным. На время выборки сервер блокирует строки, а все изменения, вносимые пользователем в полный результирующий набор курсора, будут видны в курсоре. Однако если другой пользователь внес изменения уже после выборки данных курсором, то они не отразятся в курсоре .
Курсор, управляемый набором ключей, находится посередине между этими
крайностями. Записи идентифицируются на момент выборки, и тем самым
отслеживаются изменения . Такой тип курсора полезен при реализации
прокрутки назад – тогда добавления и удаления рядов не видны, пока
информация не обновится, а драйвер выбирает новую версию записи, если
в нее были внесены изменения.
Последовательные курсоры не разрешают выполнять выборку данных в обратном направлении. Пользователь может выбирать строки только от начала к концу курсора . Последовательный курсор не хранит набор всех строк. Они считываются из базы данных, как только выбираются в курсоре, что позволяет динамически отражать все изменения, вносимые пользователями в базу данных с помощью команд INSERT, UPDATE, DELETE. В курсоре видно самое последнее состояние данных.
Статические курсоры обеспечивают стабильный взгляд на данные. Они
хороши для систем «складирования» информации: приложений для систем
отчетности или для статистических и аналитических целей. Кроме того, статический курсор лучше других справляется с выборкой большого
количества данных. Напротив, в системах электронных покупок или
резервирования билетов необходимо динамическое восприятие обновляемой
информации по мере внесения изменений. В таких случаях используется динамический курсор.
В этих приложениях объем передаваемых данных,
как правило, невелик, а доступ к ним осуществляется на уровне рядов
(отдельных записей). Групповой доступ встречается очень редко.
Дальше >>
< Лекция 12 || Лекция 13: 123 || Лекция 14 >
Изучите курсор SQL на небольшом примере
1 Что такое курсор:
Операция в реляционной базе данных будет работать со всем набором строк. Например, набор строк, возвращаемых оператором SELECT, включает все строки, которые удовлетворяют условиям в предложении WHERE оператора. Этот полный набор строк, возвращаемых оператором, называется набором результатов. Приложения, особенно интерактивные онлайн-приложения, не всегда эффективно обрабатывают весь набор результатов как единое целое. Этим приложениям необходим механизм для обработки одной строки или части строк за раз. Курсор является расширением набора результатов, обеспечивающим этот механизм.
Курсоры расширяют возможности обработки результатов следующими способами:
- Позволяет позиционировать себя в определенной строке набора результатов.
- Извлечь строку или часть строк из текущей позиции в наборе результатов.
- Поддерживает изменение данных в строке в текущей позиции в наборе результатов.
- Предоставляет различные уровни поддержки видимости для изменений, внесенных другими пользователями в данные базы данных, отображаемые в наборе результатов.
- Предоставляет операторы Transact-SQL, используемые в сценариях, хранимых процедурах и триггерах для доступа к данным в наборе результатов.
——MSDN
Нетрудно понять, что самое большое различие между курсором и другими операциями с базой данных состоит в том, что объект представляет собой отдельную запись, а не набор результатов, и обычно используется для операторов SQL, встроенных в процедурные программы. Автоматически неявно созданный курсор используется в программе службы базы данных.
2 Основное использование:
2.1 Объявление курсора
DECLARE Имя курсора CURSOR
FOR SELECT заявление
2.
2 Откройте курсор
OPEN Имя курсора
2.3 Получение данных от курсора
FETCH NEXT FROM Имя курсора [ INTO FETCH_LIST ]
Чтобы получить данные от курсора, вам нужно обратить внимание на возможность достижения конца курсора. Следующие методы решают эту проблему, чтобы избежать ошибок, когда пользователь закрывает курсор.
1 BEGIN 2 DECLARE @custname VARCHAR(20) 3 DECLARE namecursor CURSOR FOR SELECT CUST_NAME FROM TBL_CUSTOMER OPEN namecursor 4 FETCH NEXT FROM namecursor INTO @custname 5 WHILE (@@FETCH_STATUS <> -1) 6 BEGIN 7 IF (@@FETCH_STATUS <> -2) 8 BEGIN 9 --Управление переменными курсора 10 END 11 FETCH NEXT FROM namecursor INTO @custname 12 END 13 CLOSE namecursor 14 DEALLOCATE namecursor 15 END
2.4 Закройте курсор
CLOSE Имя курсора
Курсор не может быть прочитан и другие операции после его закрытия, но его можно снова открыть с помощью оператора OPEN
2.
5 Отпустить курсор
DEALLOCATE Имя курсора
Курсор удален и больше не может использоваться
3 Интересный небольшой пример:
Хотя я знаю концепцию и основы использования курсоров, все еще очень расплывчато, когда использовать курсоры, и даже ошибочно полагаю, что курсоры можно заменить подзапросами. Пока я не наткнулся на этот интересный маленький пример:
Структура таблицы следующая:
К предметным требованиям относятся:Составьте список различных комбинаций сотрудников, занятых одним и тем же видом работы, но принадлежащих к разным отделам.
Результат такой:
Поразмыслив над подзапросами, объединениями таблиц, временными таблицами и т. Д., Я обнаружил, что столкнулся с непреодолимым препятствием: нельзя исключать уникальность комбинации двух имен. То есть: результат, который я получаю, может быть следующим
| ANAME | BNAME |
| Adams | James |
| James | Adams |
Наконец, я подумал о курсоре, который только что выучил, код выглядит следующим образом
1 SELECT A.
Ename AS ANAME, B.Ename AS BNAME
2 INTO #t
3 FROM EMP A
4 JOIN EMP B
5 ON A.job = B.job AND A.deptNo <> B.deptNo and A.Ename<>b.Ename
6 ORDER BY ANAME
7
8 --DROP TABLE #t
9
10 DECLARE TEST_CURSOR CURSOR FOR
11 SELECT ANAME, BNAME FROM #t
12
13 OPEN TEST_CURSOR
14 DECLARE @ANAME VARCHAR(20)
15 DECLARE @BNAME VARCHAR(20)
16
17 FETCH NEXT FROM TEST_CURSOR INTO @ANAME, @BNAME
18 DELETE FROM #t WHERE ANAME=@BNAME AND BNAME=@ANAME
19 WHILE @@FETCH_STATUS = 0
20 BEGIN
21 FETCH NEXT FROM TEST_CURSOR INTO @ANAME, @BNAME
22 DELETE FROM #t WHERE ANAME=@BNAME AND BNAME=@ANAME
23 END
24
25 CLOSE TEST_CURSOR
26 DEALLOCATE TEST_CURSOR
27
28 SELECT * FROM #t—————————————————————————————
— Обновление 12.2:
Вдруг обнаружил, что этот вопрос можно написать очень просто, использовать курсоры просто Глупо ~~
1 SELECT A.Ename AS ANAME, B.Ename AS BNAME 2 FROM EMP A, EMP B 3 WHERE A.job = B.
job AND A.deptNo <> B.deptNo AND A.Ename < B.Ename
4 ORDER BY ANAMEСпрячь лицо и убегай ~~
Перепечатано на: https://www.cnblogs.com/NaturalSelection/p/4083101.html
Интеллектуальная рекомендация
SpringCloud: используйте сервер GIT, чтобы объединить компонент конфигурации Config-Server Center Center Center Combat (16)
Новый проект, создать конфигурацию Как и предыдущие шаги проекта, я не знаю, как смотреть фактический бой, потому что сервер не является клиентом, и вам нужно выбрать configserver 1. Обратите внимание…
layui слушает выбранные изменения
1. Сначала добавьте атрибут, предоставляемый layui, для выбора lay-filter = «level». Уровень выше определяется вами, который будет использоваться ниже 2. Форма импорта Вам не нужно добавля…
На шахматной игре, здание сайта
При добавлении шахматной игры вы должны быть подключены к … Если это так, не нужно вводить имя пользователя и пароль, но также необходимо установить его в пуле приложения: В IIS Manager найдите пул .
..
JAVAEE. Базовая часть: RowId = 11
JAVAEE. Базовая часть: RowId = 11 String Вопрос 1 Вопрос второй Вопрос третий Строковые общие методы StringTest StringBuffer Разница между StringBuffer и String Конвертировать между String и int Стати…
Строка и модель указателя первого уровня
1. Используйте символьные массивы для имитации символьных строк на языке C. Язык C не имеет строкового типа данных. 2. Строка на языке C — это массив символов, оканчивающихся на ‘\ 0’. 3. Строки на яз…
Вам также может понравиться
Обновление изображения Python Tkinter
Вызов собственной производственной библиотеки графического интерфейса Python Сначала я хотел сделать это с помощью TkinterGUIДа, в Интернете говорят, что он идет с python, результат вводится: После от…
PJSIP Windows Demo Compilation Run (PJSIP 2.9 QT Environment)
1, Ссылкаhttps://www.cnblogs.com/GYY2046/p/11455747.html После завершения завершения генерируйте libpjproject-i386-win32-vc14-debug.
lib — это все libs. 2. Создать программу программы QT Import Import …
Проблема ошибки ограничения XML:
Каталог XML реализует файл XML в соответствии с проверкой XSD в режиме реального времени: Путь: Wondow —> Предпочтения —> Каталог XML —> Добавить —> Файловая система Добавьте DTD О…
Функции высшего порядка Python и встроенные функции высшего порядка
1. Функции высшего порядка Фактический параметр — это имя функции Возвращаемое значение функции также является функцией Передаваемые параметры включают имя функции 2. Встроенная карта функций высшего …
Сводка и расширение HTML-элемента
Сводка и расширение HTML-элемента Предисловие Все больше и больше людей хотят изучить интерфейс, но они мало о нем знают. Предлагаю вам взглянутьПодробное введение в интерфейсные знания Этот блог в ос…
Подробный курсор SQLServer — Русские Блоги
Ссылка
http://technet.microsoft.com/zh-cn/library/ms181441(v=sql.105).
aspx
Во-первых, концепция курсора.
Мы знаем, что все реляционные операции в реляционных базах данных на самом деле являются операциями сбора и сбора данных. Его входными данными является коллекция, а выходными данными также является коллекция. Иногда необходимо обрабатывать набор результатов построчно. Нужно использовать курсор. Мы используем курсор в соответствии с «пятиэтапным методом»: объявить курсор -> открыть курсор -> прочитать данные -> закрыть курсор -> удалить курсор. Далее будет объяснено использование курсора на этих пяти шагах и приведены конкретные примеры.
2. Объяснение «пяти шагов»
1. Объявите курсор (DECLARE CURSOR)
(1) DECLARE CURSOR не только принимает грамматику, основанную на стандарте ISO, но также принимает набор расширенной грамматики Transact-SQL.
Скопировать код
синтаксис ISO
DECLARE cursor_name [ INSENSITIVE ] [ SCROLL ] CURSOR
FOR select_statement
[ FOR { READ ONLY | UPDATE [ OF column_name [ ,…n ] ] } ]
[;]
Расширенный синтаксис Transact-SQL
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 ] ] ]
[;]
Скопировать код
Далее представлен только «расширенный синтаксис T-SQL», основной курсор представляет область действия, направление и тип.
(2) Область действия курсора
LOCAL
Объясните, что объявленный курсор является локальным, и его область действия - это пакет, хранимая процедура или триггер, которые его создали, то есть курсор неявно освобождается системой после выполнения пакета, хранимой процедуры или триггера, который его вызывает. Однако, если курсор используется в качестве выходного параметра хранимой процедуры OUTPUT, после завершения хранимой процедуры назначьте параметры курсорной переменной, чтобы продолжить обращение к курсору. Если параметр OUTPUT передает курсор обратно, курсор будет освобожден, когда переменная, которая ссылается на него последней или покидает область видимости .
GLOBAL
Укажите область действия курсора, чтобы соединение было глобальным. В любой хранимой процедуре или пакете, выполняемом соединением, можно ссылаться на имя курсора. Курсор освобождается неявно только при разрыве соединения.
Примечание. Если не указаны ни параметры GLOBAL, ни LOCAL, значение по умолчанию контролируется настройкой параметра базы данных локального курсора по умолчанию. В SQL Server версии 7.0 для этого параметра по умолчанию установлено значение FALSE, чтобы соответствовать более ранним версиям SQL Server, в которых все курсоры являются глобальными.
В SQL Server версии 7.0 для этого параметра по умолчанию установлено значение FALSE, чтобы соответствовать более ранним версиям SQL Server, в которых все курсоры являются глобальными.
Скопировать код
объявить курсор testcur — «локальные» или «глобальные» ключевые слова не указаны в объявлении, системный курсор по умолчанию — «глобальный».
for
выберите идентификатор студента, имя из XSB
GO
open testcur
fetch next from testcur
GO
объявить курсор testcur локальным — указать ключевое слово «local» при объявлении
for
выберите идентификатор студента, имя из XSB
open testcur
fetch next from testcur
GO — после завершения пакетной обработки курсор автоматически освобождается, то есть становится недействительным.
объявить курсор testcur1 локальным — указать ключевое слово «local» при объявлении
for
выберите идентификатор студента, имя из XSB
GO — после этой пакетной обработки курсор становится недействительным. Когда курсор используется позже, отображается сообщение об ошибке «курсор с именем« testcur1 »не существует».
open testcur1
fetch next from testcur1
GO
Скопировать код
(3) Направление курсора
FORWARD_ONLY
Указывает, что курсор может прокручиваться только от первой строки до последней строки. FETCH NEXT - единственный поддерживаемый вариант извлечения. Если ключевые слова STATIC, KEYSET и DYNAMIC не указаны при указании FORWARD_ONLY, курсор работает как DYNAMIC курсор. Если не указаны ни FORWARD_ONLY, ни SCROLL, по умолчанию используется FORWARD_ONLY, если не указаны ключевые слова STATIC, KEYSET или DYNAMIC. Для курсоров STATIC, KEYSET и DYNAMIC по умолчанию используется ПРОКРУТКА. В отличие от API-интерфейсов баз данных, таких как ODBC и ADO, курсоры Transact-SQL STATIC, KEYSET и DYNAMIC поддерживают FORWARD_ONLY.
SCROLL
Укажите, что доступны все параметры извлечения (FIRST, LAST, PRIOR, NEXT, RELATIVE, ABSOLUTE). Если SCROLL не указан в ISO DECLARE CURSOR, NEXT - единственный поддерживаемый вариант извлечения. Если также указан FAST_FORWARD, нельзя указать SCROLL.
Скопировать код
объявить курсор directionCur — не указывать направление движения, по умолчанию — «forward_only»
for
выберите идентификатор студента, имя из XSB
Go
open directionCur
fetch next from directionCur
Go
fetch Prior from directionCur —error «Предыдущий тип выборки не может использоваться с курсорами только вперед».
Go
close directionCur2
deallocate directionCur2
Go
объявить направление Cur1 курсора forward_only — указать направление «forward_only»
for
выберите идентификатор студента, имя из XSB
Go
open directionCur1
fetch next from directionCur1
Go
fetch Prior from directionCur1 —error «Предыдущий тип выборки не может использоваться с курсорами только вперед».
Go
close directionCur2
deallocate directionCur2
Go
объявить направление Cur2 прокрутка курсора — указать направление «прокрутки»
for
выберите идентификатор студента, имя из XSB
Go
open directionCur2
fetch next from directionCur2
Go
fetch prior from directionCur2
Go
close directionCur2
deallocate directionCur2
Go
-Заключение: если курсор не задает никаких параметров доступа или типа, по умолчанию используется глобальный динамический курсор, работающий только вперед.
(Это результат теста на SQLServer2008 R2, в зависимости от настроек программного обеспечения)
— «Scroll» и «fast_forward» нельзя использовать вместе
объявить прокрутку курсора forwardTest fast_forward — сообщается об ошибке «Конфликт параметров курсора SCROLL и FAST_FORWARD».
for
выберите идентификатор студента, имя из XSB
Go
-В SQL Server 2000 параметры курсора FAST_FORWARD и FORWARD_ONLY являются взаимоисключающими. Если указаны оба параметра, возникнет ошибка.
— в SQL Server 2005 и более поздних версиях эти два ключевых слова можно использовать в одном операторе DECLARE CURSOR.
declare forwardTest1 cursor forward_only fast_forward
for
select * from XSB
Go
Скопировать код
(4) Тип курсора
STATIC
Определите курсор, чтобы создать временную копию данных, которые будут использоваться курсором. На все запросы к курсору отвечает эта временная таблица в базе данных tempdb; поэтому данные, возвращаемые при выборке курсора, не отражают изменения, внесенные в базовую таблицу, и курсор не может быть изменен.
И курсор доступен только для чтения.
KEYSET
Указывает, что при открытии курсора членство и порядок строк в курсоре были фиксированы. Набор ключей, который однозначно идентифицирует строку, встроен в таблицу, называемую набором ключей в базе данных tempdb.
DYNAMIC
Определите курсор, чтобы отразить все изменения данных, внесенные в строки в наборе результатов, когда курсор прокручивается. Значение данных, порядок и принадлежность строк изменяются при каждом извлечении.
FAST_FORWARD
Укажите курсоры FORWARD_ONLY и READ_ONLY с включенной оптимизацией производительности. Если указано SCROLL или FOR_UPDATE, вы также не можете указать FAST_FORWARD.
2. Откройте курсор (ОТКРЫТЬ)
Синтаксис
OPEN { { [ GLOBAL ] cursor_name } | cursor_variable_name }
пример
Скопировать код
— открыть курсор и прочитать все строки
объявить курсор XSBcur — объявить
for
Select * from XSB
Go
открыть XSBcur —Open
следующая выборка из XSBcur — получение данных
while @@FETCH_STATUS = 0
begin
fetch next from XSBcur
end
закрыть XSBcur —Закрыть
освободить XSBcur —delete
Скопировать код
глобальные переменные @@ CURSOR_ROWS
Эта переменная содержит количество строк данных в последнем открытом курсоре.
Когда ее значение равно 0, это означает, что ни один курсор не открыт; когда ее значение равно -1, это означает, что курсор является динамическим; когда его значение равно -m (m — положительное целое число ) курсор заполняется асинхронно, а m заполняется в текущем наборе ключей
Количество строк; когда его значение равно m (m — положительное целое число), курсор полностью заполнен, а m — количество строк данных в курсоре.
3. Прочтите данные
Синтаксис
Скопировать код
FETCH
[ [ NEXT | PRIOR | FIRST | LAST
| ABSOLUTE { n | @nvar }
| RELATIVE { n | @nvar }
]
FROM
]
{ { [ GLOBAL ] cursor_name } | @cursor_variable_name }
[INTO @variable_name [,… n]] —into указывает, что считанные данные курсора будут сохранены в указанной переменной
Скопировать код
пример
Скопировать код
declare stuCur cursor scroll
for
выберите идентификатор студента, имя из XSB
GO
open stuCur
Go
— начать чтение данных
fetch next from stuCur — прочитать следующую строку текущей строки и установить ее на текущую строку (курсор помещается в предыдущую строку заголовка в начале, то есть, если таблица с 0 Вначале курсор изначально находится на -1, поэтому первая строка читается впервые)
получить до из stuCur — прочитать предыдущую строку текущей строки и сделать ее текущей строкой
сначала получить из stuCur — прочитать первую строку курсора и установить ее в текущую строку (не может использоваться для курсоров, ориентированных только вперед)
получить последнюю из stuCur — прочитать последнюю строку курсора и установить ее на текущую строку (не может использоваться для курсоров только вперед)
получить абсолютное значение 2 из stuCur — считывает вторую строку от курсора до конца и обрабатывает прочитанную строку как новую строку
получить относительное значение 3 из stuCur — прочитать 3-ю строку от текущей строки до конца и использовать строку чтения как новую строку
получить relative-2 из stuCur — прочитать две предыдущие строки текущей строки и использовать прочитанную строку как новую строку
— конец чтения данных
GO
close stuCur
Go
deallocate stuCur
Go
Скопировать код
глобальная переменная @@ FETCH_STATUS
Состояние выполнения оператора FETCH хранится в глобальной переменной @@ FETCH_STATUS.
Значение 0 указывает, что последний FETCH был успешно выполнен; -1 указывает, что строка для чтения отсутствует в наборе результатов; -2 указывает, что извлеченная строка больше не существует. Был удален).
4. Закройте курсор (ЗАКРЫТЬ).
Синтаксис
CLOSE { { [ GLOBAL ] cursor_name } | cursor_variable_name }
пример
close stuCur — если курсор объявлен заранее и был открыт
Go
5. Удалите курсор (DEALLOCATE).
Оператор, работающий с курсором, использует имя курсора или переменную курсора для ссылки на курсор. DEALLOCATE удаляет связь между курсором и именем курсора или курсорной переменной. Если имя или переменная — это фамилия или переменная, которая ссылается на курсор, курсор будет освобожден, и все ресурсы, используемые курсором, будут освобождены соответственно. Блокировка прокрутки, используемая для защиты изоляции извлечения, снимается при DEALLOCATE. Блокировка транзакции, используемая для защиты обновлений (включая обновления позиционирования с помощью курсора), не снимается до конца транзакции.
грамматика
DEALLOCATE { { [ GLOBAL ] cursor_name } | @cursor_variable_name }
пример
Переменная курсора связывается с курсором с помощью одного из следующих двух методов:
Скопировать код
— объявить курсор
declare abc cursor scroll
for
select * from XSB
— (1) Используйте оператор set, чтобы установить курсор в качестве курсорной переменной по имени
declare @mycur cursor
set @mycur = abc
— (2) Вы также можете создать курсор без определения имени курсора и связать его с переменной
declare @mycursor cursor
set @mycursor = cursor local scroll for
Select * from XSB
Скопировать код
Оператор DEALLOCATE @cursor_variable_name удаляет только ссылку на переменную имени курсора. Переменная не освобождается, пока переменная не покинет область действия после завершения пакета, хранимой процедуры или триггера. После оператора DEALLOCATE @cursor_variable_name вы можете использовать оператор SET, чтобы связать переменную с другим курсором.
Курсор можно понимать как указатель.
Скопировать код
declare @mycur cursor
set @mycur = cursor local scroll for
select * from XSB
deallocate @mycur
set @mycur = cursor local scroll for
select * from XSB
GO
-Нет необходимости явно освобождать курсорную переменную. Переменные неявно освобождаются, когда они покидают область видимости.
Скопировать код
Следующий сценарий показывает, как курсор переходит к фамилии или до тех пор, пока не будут освобождены ссылающиеся на них переменные.
Скопировать код
USE AdventureWorks2008R2;
GO
– Create and open a global named cursor that
– is visible outside the batch.
DECLARE abc CURSOR GLOBAL SCROLL FOR
SELECT * FROM Sales.SalesPerson;
OPEN abc;
GO
– Reference the named cursor with a cursor variable.
DECLARE @MyCrsrRef1 CURSOR;
SET @MyCrsrRef1 = abc;
– Now deallocate the cursor reference.
DEALLOCATE @MyCrsrRef1;
– Cursor abc still exists.
FETCH NEXT FROM abc;
GO
– Reference the named cursor again.
DECLARE @MyCrsrRef2 CURSOR;
SET @MyCrsrRef2 = abc;
– Now deallocate cursor name abc.
DEALLOCATE abc;
– Cursor still exists, referenced by @MyCrsrRef2.
FETCH NEXT FROM @MyCrsrRef2;
– Cursor finally is deallocated when last referencing
– variable goes out of scope at the end of the batch.
GO
– Create an unnamed cursor.
DECLARE @MyCursor CURSOR;
SET @MyCursor = CURSOR LOCAL SCROLL FOR
SELECT * FROM Sales.SalesTerritory;
– The following statement deallocates the cursor
– because no other variables reference it.
DEALLOCATE @MyCursor;
GO
Скопировать код
3. Используемые данные
1. Скрипт для создания таблицы
Скопировать код
CREATE TABLE [dbo].[XSB](
[студенческий билет]char NOT NULL,
[имя]char NOT NULL,
[Gender] [bit] NULL,
[время рождения] [дата] NULL,
[Профессиональный]char NULL,
[Всего кредитов] [int] NULL,
[Примечания]varchar NULL,
PRIMARY KEY CLUSTERED
(
[идентификатор студента] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
Скопировать код
2.
Вставьте данные
Скопировать код
insert into XSB values
(‘081101’, ‘ ’, ‘true’, ‘1990-2-10’, ‘computer’, 50, null),
(‘081102’, ‘Chen Ping’, ‘true’, ‘1991-2-1’, ‘Computer’, 50, null),
(‘081103’, ‘’, ‘false’, ‘1989-10-6’, ‘Computer’, 50, null),
(‘081104’, ‘Wei Yanping’, ‘true’, ‘1990-8-26’, ‘Computer’, 50, null),
(‘081106’, ‘Li Fangfang’, ‘true’, ‘1990-11-20’, ‘Computer’, 50, null),
(‘081107’, ‘Li Ming’, ‘true’, ‘1990-5-1’, ‘computer’, 54, ‘завершили структуру данных заранее и получили кредиты’ ),
(«081108», «Линь Ифань», «true», «1989-8-5», «Компьютер», 52, «Курс завершен заранее»),
(‘081109’, ‘Zhang Qiangmin’, ‘true’, ‘1989-8-11’, ‘computer’, 50, null),
(«081110», «Zhang Wei», «false», «1991-7-22», «Computer», 50, «San Hao Sheng»),
(«081111», «Чжао Линь», «ложь», «1989-10-6», «Компьютер», 50, null),
(«081113», «Ян Хун», «ложь», «1989-8-11», «Компьютер», 48, «Один сбой, должен быть взят повторно»),
(‘081201’, ‘Wang Min’, ‘true’, ‘1989-6-10’, ‘Communication Engineering’, 42, null),
(‘081202’, ‘Wang Lin’, ‘true’, ‘1989-6-10’, ‘Communication Engineering’, 40, ‘один неуспешный, подлежит повторному контролю’)
GO
Скопировать код
Курсоры (SQL Server) — SQL Server
- Статья
- 7 минут на чтение
Применимо к: SQL Server (все поддерживаемые версии) База данных SQL Azure Управляемый экземпляр Azure SQL
Операции в реляционной базе данных воздействуют на полный набор строк. Например, набор строк, возвращаемый Оператор SELECT состоит из всех строк, удовлетворяющих условиям в предложении WHERE оператора. Этот полный набор строк, возвращаемый оператором, называется результирующим набором. Приложения, особенно интерактивные онлайн-приложения, не всегда могут эффективно работать со всем набором результатов как единым целым. Этим приложениям нужен механизм для работы с одной строкой или небольшим блоком строк за раз. Курсоры являются расширением наборов результатов, которые обеспечивают этот механизм.
Курсоры расширяют возможности обработки результатов:
Разрешено позиционирование в определенных строках результирующего набора.
Получение одной строки или блока строк из текущей позиции в наборе результатов.
Поддержка модификации данных для строк в текущей позиции в наборе результатов.
Поддержка различных уровней видимости изменений, внесенных другими пользователями в данные базы данных, представленные в наборе результатов.
Предоставление инструкций Transact-SQL в скриптах, хранимых процедурах и триггерах доступа к данным в результирующем наборе.
Совет
В некоторых сценариях, если в таблице есть первичный ключ, вместо курсора можно использовать цикл WHILE без дополнительных затрат на курсор.
Однако есть сценарии, в которых курсоры не только неизбежны, но и действительно необходимы. В этом случае, если нет необходимости обновлять таблицы на основе курсора, используйте firehose курсоры, то есть курсоры быстрой перемотки вперед и только для чтения.
Реализации курсора
SQL Server поддерживает три реализации курсора.
Курсоры Transact-SQL
Курсоры Transact-SQL основаны на синтаксисе DECLARE CURSOR и используются в основном в сценариях Transact-SQL, хранимых процедурах и триггерах. Курсоры Transact-SQL реализованы на сервере и управляются операторами Transact-SQL, отправляемыми клиентом на сервер. Они также могут содержаться в пакетах, хранимых процедурах или триггерах.
Курсоры сервера прикладного программирования (API)
Курсоры API поддерживают функции курсоров API в OLE DB и ODBC. Курсоры сервера API реализованы на сервере. Каждый раз, когда клиентское приложение вызывает функцию курсора API, поставщик OLE DB собственного клиента SQL ServerSQL Server или драйвер ODBC передает запрос на сервер для выполнения действия над курсором сервера API.
Клиентские курсоры
Клиентские курсоры реализуются внутри драйвера ODBC собственного клиента SQL Server и библиотеки DLL, которая реализует API ADO. Клиентские курсоры реализованы путем кэширования всех строк результирующего набора на клиенте.
Каждый раз, когда клиентское приложение вызывает функцию курсора API, драйвер ODBC собственного клиента SQL ServerSQL Server или ADO DLL выполняет операцию курсора над строками результирующего набора, кэшированными на клиенте.
Тип курсоров
SQL Server поддерживает четыре типа курсоров.
Примечание
Курсоры могут использовать рабочие таблицы tempdb. Так же, как и операции агрегации или сортировки, они связаны с вводом-выводом и являются потенциальным узким местом в производительности. STATIC Курсоры используют рабочие столы с самого начала. Дополнительные сведения см. в разделе рабочие таблицы в Руководстве по архитектуре обработки запросов.
Только вперед
Курсор только вперед указан как FORWARD_ONLY и READ_ONLY и не поддерживает прокрутку. Они также называются курсорами firehose и поддерживают только последовательную выборку строк от начала до конца курсора. Строки не извлекаются из базы данных до тех пор, пока они не будут выбраны.
Эффекты всех операторов INSERT , UPDATE и DELETE , сделанных текущим пользователем или совершенных другими пользователями, которые влияют на строки в результирующем наборе, видны, когда строки извлекаются из курсора.
Поскольку курсор нельзя прокручивать назад, большинство изменений, внесенных в строки в базе данных после того, как строка была выбрана, не видны через курсор. В случаях, когда значение, используемое для определения местоположения строки в результирующем наборе, изменяется, например, при обновлении столбца, охватываемого кластеризованным индексом, измененное значение видно через курсор.
Несмотря на то, что модели курсоров API баз данных рассматривают прямой курсор как отдельный тип курсора, SQL Server этого не делает. SQL Server рассматривает только вперед и прокрутку как параметры, которые можно применять к статическим, управляемым набором ключей и динамическим курсорам. Курсоры Transact-SQL поддерживают статические курсоры, управляемые набором ключей, и динамические курсоры только для прямой передачи.
Модели курсоров API базы данных предполагают, что статические, управляемые набором ключей и динамические курсоры всегда поддерживают прокрутку. Когда для атрибута или свойства курсора API базы данных установлено значение «только вперед», SQL Server реализует это как динамический курсор «только вперед».
Статический
Полный набор результатов статического курсора создается в базе данных tempdb при открытии курсора. Статический курсор всегда отображает набор результатов таким, каким он был при открытии курсора. Статические курсоры обнаруживают мало изменений или вообще не обнаруживают их, но потребляют относительно мало ресурсов при прокрутке.
Курсор не отражает каких-либо изменений в базе данных, которые влияют либо на членство в результирующем наборе, либо на изменения значений в столбцах строк, составляющих результирующий набор. Статический курсор не отображает новые строки, вставленные в базу данных после открытия курсора, даже если они соответствуют условиям поиска курсора Оператор SELECT .
Если строки, составляющие результирующий набор, обновляются другими пользователями, новые значения данных не отображаются в статическом курсоре. Статический курсор отображает строки, удаленные из базы данных после открытия курсора. Никакие операции UPDATE , INSERT или DELETE не отражаются в статическом курсоре (если курсор не закрывается и не открывается повторно), даже изменения, сделанные с использованием того же соединения, которое открыло курсор.
Примечание
Статические курсоры SQL Server всегда доступны только для чтения.
Примечание
Поскольку результирующий набор статического курсора хранится в рабочей таблице tempdb , размер строк в результирующем наборе не может превышать максимальный размер строки для таблицы SQL Server.
Дополнительные сведения см. в разделе рабочие таблицы в Руководстве по архитектуре обработки запросов. Дополнительные сведения о максимальном размере строки см.
в разделе Характеристики максимальной емкости для SQL Server.
Transact-SQL использует термин нечувствительный для статических курсоров. Некоторые API-интерфейсы баз данных идентифицируют их как курсоры моментальных снимков.
Набор ключей
Состав и порядок строк в курсоре, управляемом набором ключей, фиксируются при открытии курсора. Курсоры, управляемые набором ключей, управляются набором уникальных идентификаторов, ключей, известных как набор ключей. Ключи строятся из набора столбцов, которые однозначно идентифицируют строки в результирующем наборе. Набор ключей — это набор значений ключей из всех строк, которые соответствовали инструкции SELECT на момент открытия курсора. Набор ключей для курсора, управляемого набором ключей, встроен в tempdb при открытии курсора.
Динамический
Динамические курсоры противоположны статическим курсорам. Динамические курсоры отражают все изменения, внесенные в строки в их результирующем наборе при прокрутке курсора.
Значения данных, порядок и принадлежность строк в результирующем наборе могут меняться при каждой выборке. Все операторы UPDATE , INSERT и DELETE , сделанные всеми пользователями, видны через курсор. Обновления видны сразу, если они сделаны через курсор с использованием любой функции API, например SQLSetPos или предложение Transact-SQL WHERE CURRENT OF . Обновления, сделанные за пределами курсора, невидимы до тех пор, пока они не будут зафиксированы, если только уровень изоляции транзакции курсора не установлен на чтение незафиксированных. Дополнительные сведения об уровнях изоляции см. в разделе УСТАНОВКА УРОВНЯ ИЗОЛЯЦИИ ТРАНЗАКЦИИ (Transact-SQL).
Примечание
Планы динамических курсоров никогда не используют пространственные индексы.
Запрос курсора
SQL Server поддерживает два метода запроса курсора:
Transact-SQL
Язык Transact-SQL поддерживает синтаксис для использования курсоров, созданный по образцу синтаксиса курсора ISO.
Курсорные функции интерфейса прикладного программирования базы данных (API)
SQL Server поддерживает функциональные возможности курсора следующих API баз данных:
Приложение никогда не должно смешивать эти два метода запроса курсора. Приложение, которое использовало API для указания поведения курсора, не должно затем выполнять инструкцию Transact-SQL DECLARE CURSOR, чтобы также запросить курсор Transact-SQL. Приложение должно выполнять DECLARE CURSOR только в том случае, если оно вернуло все атрибуты курсора API к значениям по умолчанию.
Если ни курсор Transact-SQL, ни курсор API не были запрошены, SQL Server по умолчанию возвращает приложению полный набор результатов, известный как набор результатов по умолчанию.
Процесс курсора
Курсоры Transact-SQL и курсоры API имеют различный синтаксис, но для всех курсоров SQL Server используется следующий общий процесс: характеристики курсора, например возможность обновления строк в курсоре.
Выполните инструкцию Transact-SQL для заполнения курсора.
Получить строки в курсоре, который вы хотите увидеть. Операция извлечения одной строки или одного блока строк из курсора называется выборкой. Выполнение серии выборок для извлечения строк в прямом или обратном направлении называется прокруткой.
При необходимости выполните операции модификации (обновление или удаление) в строке в текущей позиции курсора.
Закрыть курсор.
Связанное содержимое
Поведение курсора
Как реализованы курсоры
См. также
DECLARE CURSOR (Transact-SQL)
Курсоры (Transact-SQL)
Функции курсора (Transact-SQL) Cursor Stored
УСТАНОВИТЬ УРОВЕНЬ ИЗОЛЯЦИИ ТРАНЗАКЦИИ (Transact-SQL)
SQL Server Различные типы курсоров
Курсор позволяет нам извлекать данные из результирующего набора в одиночном режиме, что означает строку за строкой. Курсор необходим, когда нам нужно обновить записи в таблице базы данных по одной строке за раз.
Я уже объяснял основы работы с курсорами в SQL Server.
Курсор влияет на производительность SQL Server, поскольку он использует память экземпляров SQL Server, уменьшает параллелизм, уменьшает пропускную способность сети и блокирует ресурсы. Следовательно, необходимо понимать типы курсоров и их функции, чтобы вы могли использовать подходящий курсор в соответствии с вашими потребностями.
Вам следует избегать использования курсора. В основном вы должны использовать альтернативы курсора, такие как цикл WHILE, подзапросы, временные таблицы и табличные переменные. Мы должны использовать курсор в том случае, когда нет другого варианта, кроме курсора.
Типы курсоров
Статические курсоры
Статический курсор заполняет набор результатов во время создания курсора, а результат запроса кэшируется на время существования курсора. Статический курсор может двигаться вперед и назад. Статический курсор работает медленнее и использует больше памяти по сравнению с другим курсором.
Следовательно, вы должны использовать его только в том случае, если требуется прокрутка, а другие типы курсоров не подходят.Никакие операции UPDATE, INSERT или DELETE не отражаются в статическом курсоре (если курсор не закрывается и не открывается повторно). По умолчанию статические курсоры прокручиваются. Статические курсоры SQL Server всегда доступны только для чтения.
Динамические курсоры
Динамический курсор позволяет вам видеть обновление, удаление и вставку данных в источнике данных, пока курсор открыт. Следовательно, динамический курсор чувствителен к любым изменениям в источнике данных и поддерживает операции обновления и удаления. По умолчанию динамические курсоры прокручиваются.
Курсоры только вперед
Курсор только вперед — это самый быстрый курсор среди всех курсоров, но он не поддерживает прокрутку назад. Вы можете обновлять, удалять данные с помощью курсора Forward Only. Он чувствителен к любым изменениям в исходном источнике данных.
Существует еще три типа курсоров Forward Only. Forward_Only KEYSET, FORWARD_ONLY STATIC и FAST_FORWARD.
A FORWARD_ONLY STATIC Курсор заполняется во время создания и кэшируется данными до времени жизни курсора. Он не чувствителен к любым изменениям в источнике данных.
A Курсор FAST_FORWARD является самым быстрым курсором и не зависит от любых изменений в источнике данных.
Курсоры, управляемые набором ключей
Курсор, управляемый набором ключей, управляется набором уникальных идентификаторов, таких как ключи в наборе ключей. Набор ключей зависит от всех строк, которые квалифицировали оператор SELECT во время открытия курсора. Курсор, управляемый набором ключей, чувствителен к любым изменениям в источнике данных и поддерживает операции обновления и удаления. По умолчанию курсоры, управляемые набором клавиш, можно прокручивать.
Следовательно, вы должны использовать его только в том случае, если требуется прокрутка, а другие типы курсоров не подходят.
SQL SERVER – Примеры курсоров
CREATE TABLE Сотрудник ( EmpID int ПЕРВИЧНЫЙ КЛЮЧ, EmpName varchar (50) НЕ NULL, Зарплата int NOT NULL, Адрес varchar (200) NOT NULL, ) ИДТИ INSERT INTO Сотрудник(EmpID,EmpName,Salary,Address) VALUES(1,'Mohan',12000,'Noida') INSERT INTO Employee(EmpID,EmpName,Salary,Address) VALUES(2,'Pavan',25000,'Delhi') INSERT INTO Employee(EmpID,EmpName,Salary,Address) VALUES(3,'Amit',22000,'Dehradun') INSERT INTO Employee(EmpID,EmpName,Salary,Address) VALUES(4,'Sonu',22000,'Noida') INSERT INTO Employee(EmpID,EmpName,Salary,Address) VALUES(5,'Deepak',28000,'Gurgaon') ИДТИ ВЫБЕРИТЕ * ОТ Сотрудника
Статический курсор — пример
SET NOCOUNT ON DECLARE @Id int ОБЪЯВИТЬ @name varchar(50) DECLARE @salary int ОБЪЯВИТЬ cur_emp КУРСОР СТАТИЧЕСКИЙ ДЛЯ ВЫБЕРИТЕ EmpID,EmpName,Зарплата от сотрудника ОТКРЫТЬ cur_emp ЕСЛИ @@CURSOR_ROWS > 0 НАЧИНАТЬ FETCH NEXT FROM cur_emp INTO @Id,@name,@salary ПОКА @@Fetch_status = 0 НАЧИНАТЬ PRINT 'ID: '+ convert(varchar(20),@Id)+', Name: '+@name+', Salary: '+convert(varchar(20),@salary) FETCH NEXT FROM cur_emp INTO @Id,@name,@salary КОНЕЦ КОНЕЦ ЗАКРЫТЬ УДАЛИТЬ cur_emp УСТАНОВИТЬ NOCOUNT ВЫКЛ.
Динамический курсор — пример
--Динамический курсор для обновления УСТАНОВИТЬ NOCOUNT ON DECLARE @Id int ОБЪЯВИТЬ @name varchar(50) ОБЪЯВИТЬ Dynamic_cur_empupdate КУРСОР ДИНАМИЧЕСКИЙ ЗА ВЫБЕРИТЕ EmpID,EmpName от сотрудника ORDER BY EmpName ОТКРЫТЬ Dynamic_cur_empupdate ЕСЛИ @@CURSOR_ROWS > 0 НАЧИНАТЬ FETCH NEXT FROM Dynamic_cur_empupdate INTO @Id,@name ПОКА @@Fetch_status = 0 НАЧИНАТЬ ЕСЛИ @name='Мохан' Обновить зарплату сотрудника SET = 15000, ГДЕ ТЕКУЩИЙ ИЗ Dynamic_cur_empupdate FETCH NEXT FROM Dynamic_cur_empupdate INTO @Id,@name КОНЕЦ КОНЕЦ ЗАКРЫТЬ DEALLOCATE Dynamic_cur_empupdate УСТАНОВИТЬ NOCOUNT ВЫКЛ. Идти Выберите * из Сотрудника
-- Динамический курсор для УДАЛЕНИЯ УСТАНОВИТЬ NOCOUNT ON DECLARE @Id int ОБЪЯВИТЬ @name varchar(50) DECLARE Dynamic_cur_empудалить CURSOR ДИНАМИЧЕСКИЙ ЗА ВЫБЕРИТЕ EmpID,EmpName от сотрудника ORDER BY EmpName ОТКРЫТЬ Dynamic_cur_empdelete ЕСЛИ @@CURSOR_ROWS > 0 НАЧИНАТЬ FETCH NEXT FROM Dynamic_cur_empdelete INTO @Id,@name ПОКА @@Fetch_status = 0 НАЧИНАТЬ ЕСЛИ @name='Диппак' УДАЛИТЬ Сотрудник, ГДЕ ТЕКУЩИЙ ИЗ Dynamic_cur_empdelete FETCH NEXT FROM Dynamic_cur_empdelete INTO @Id,@name КОНЕЦ КОНЕЦ ЗАКРЫТЬ Dynamic_cur_empdelete DEALLOCATE Dynamic_cur_empdelete УСТАНОВИТЬ NOCOUNT ВЫКЛ.
Идти
Выберите * из Сотрудника Курсор только вперед — пример
--Курсор только вперед для обновления УСТАНОВИТЬ NOCOUNT ON DECLARE @Id int ОБЪЯВИТЬ @name varchar(50) ОБЪЯВИТЬ Forward_cur_empupdate КУРСОР FORWARD_ONLY ЗА ВЫБЕРИТЕ EmpID,EmpName от сотрудника ORDER BY EmpName ОТКРЫТЬ Forward_cur_empupdate ЕСЛИ @@CURSOR_ROWS > 0 НАЧИНАТЬ FETCH NEXT FROM Forward_cur_empupdate INTO @Id,@name ПОКА @@Fetch_status = 0 НАЧИНАТЬ ЕСЛИ @name='Амит' Обновить зарплату сотрудника SET = 24000, ГДЕ ТЕКУЩИЙ ИЗ Forward_cur_empupdate FETCH NEXT FROM Forward_cur_empupdate INTO @Id,@name КОНЕЦ КОНЕЦ ЗАКРЫТЬ DEALLOCATE Forward_cur_empupdate УСТАНОВИТЬ NOCOUNT ВЫКЛ. Идти Выберите * из Сотрудника
-- Курсор только вперед для удаления УСТАНОВИТЬ NOCOUNT ON DECLARE @Id int ОБЪЯВИТЬ @name varchar(50) DECLARE Forward_cur_empудалить CURSOR FORWARD_ONLY ЗА ВЫБЕРИТЕ EmpID,EmpName от сотрудника ORDER BY EmpName ОТКРЫТЬ Forward_cur_empdelete ЕСЛИ @@CURSOR_ROWS > 0 НАЧИНАТЬ FETCH NEXT FROM Forward_cur_empdelete INTO @Id,@name ПОКА @@Fetch_status = 0 НАЧИНАТЬ ЕСЛИ @name='Сону' УДАЛИТЬ Сотрудника, ГДЕ ТЕКУЩИЙ ИЗ Forward_cur_empdelete FETCH NEXT FROM Forward_cur_empdelete INTO @Id,@name КОНЕЦ КОНЕЦ ЗАКРЫТЬ Forward_cur_empdelete DEALLOCATE Forward_cur_empdelete УСТАНОВИТЬ NOCOUNT ВЫКЛ.
Курсор, управляемый набором ключей — пример
— Курсор, управляемый набором ключей, для обновления УСТАНОВИТЬ NOCOUNT ON DECLARE @Id int ОБЪЯВИТЬ @name varchar(50) DECLARE Keyset_cur_empupdate КУРСОР НАБОР КЛЮЧЕЙ ЗА ВЫБЕРИТЕ EmpID,EmpName от сотрудника ORDER BY EmpName ОТКРЫТЬ Keyset_cur_empupdate ЕСЛИ @@CURSOR_ROWS > 0 НАЧИНАТЬ FETCH NEXT FROM Keyset_cur_empupdate INTO @Id,@name ПОКА @@Fetch_status = 0 НАЧИНАТЬ ЕСЛИ @name='Паван' Обновить зарплату сотрудника SET = 27000, ГДЕ ТЕКУЩИЙ ИЗ Keyset_cur_empupdate FETCH NEXT FROM Keyset_cur_empupdate INTO @Id,@name КОНЕЦ КОНЕЦ ЗАКРЫТЬ DEALLOCATE Keyset_cur_empupdate УСТАНОВИТЬ NOCOUNT ВЫКЛ. Идти Выберите * из Сотрудника
-- Управляемый ключом курсор для удаления УСТАНОВИТЬ NOCOUNT ON DECLARE @Id int ОБЪЯВИТЬ @name varchar(50) DECLARE Keyset_cur_empудалить CURSOR НАБОР КЛЮЧЕЙ ЗА ВЫБЕРИТЕ EmpID,EmpName от сотрудника ORDER BY EmpName ОТКРЫТЬ Keyset_cur_empdelete ЕСЛИ @@CURSOR_ROWS > 0 НАЧИНАТЬ FETCH NEXT FROM Keyset_cur_empdelete INTO @Id,@name ПОКА @@Fetch_status = 0 НАЧИНАТЬ ЕСЛИ @name='Амит' УДАЛИТЬ Сотрудника, ГДЕ ТЕКУЩИЙ ИЗ Keyset_cur_empdelete FETCH NEXT FROM Keyset_cur_empdelete INTO @Id,@name КОНЕЦ КОНЕЦ ЗАКРЫТЬ DEALLOCATE Keyset_cur_empdelete УСТАНОВИТЬ NOCOUNT ВЫКЛ.
Дополнительные статьи, относящиеся к SQL Server
Резюме
В этой статье я попытаюсь объяснить типы Cursor в SQL Server на простом примере. Я надеюсь, что после прочтения этой статьи вы сможете понять различные типы курсоров в SQL Server. Я хотел бы получить обратную связь от читателей моего блога. Оставьте свой отзыв, вопрос или комментарий к этой статье.
Простое учебное пособие по курсору с примером синтаксиса в SQL Server
В этой статье я представлю простое учебное пособие по курсору с примером синтаксиса в SQL Server.
Курсор поддерживается во всех версиях SQL Server, т. е. 2000, 2005, 2008, 2008R2, 2012 и 2014.
База данных
Я использовал следующую таблицу Customers со следующей схемой.
Я уже вставил несколько записей в таблицу.
Примечание : Вы можете загрузить таблицу базы данных SQL, щелкнув ссылку для скачивания ниже.
Скачать файл SQL
Что такое курсоры в SQL Server?
Курсор — это объект базы данных, который позволяет нам обрабатывать каждую строку и манипулировать ее данными. Курсор всегда связан с запросом выбора и будет обрабатывать каждую строку, возвращаемую запросом выбора, одну за другой.
Используя Cursor, мы можем проверить данные каждой строки, изменить их или выполнить вычисления, которые невозможно, когда мы получаем все записи сразу.
Простым примером может быть случай, когда у вас есть записи о сотрудниках, и вам нужно рассчитать зарплату каждого сотрудника после вычета налогов и отпусков.
Какой синтаксис для написания курсоров в SQL Server?
Ниже приведен синтаксис для записи курсора. Самое первое — объявить некоторые переменные на основе столбцов, которые вы выбираете в своем запросе на выборку.
Примечание . В этой статье я рассматриваю только READ_ONLY Cursors.
Затем вам нужно объявить курсор, дав ему имя и установив его тип как READ_ONLY, а вместе с ключевым словом FOR вам нужно написать запрос выбора, который вернет записи, которые вам нужно обработать.
После того, как курсор настроен, нам нужно открыть его с помощью команды OPEN, а затем первая запись будет извлечена и сохранена в переменной.
Всякий раз, когда запись извлекается, @@FETCH_STATUS имеет значение 0, и как только все записи, возвращаемые запросом выбора, извлекаются, его значение изменяется на -1.
Курсор связан с WHILE LOOP, который выполняется до тех пор, пока @@FETCH_STATUS не будет иметь значение 0.
Внутри WHILE LOOP обработка выполняется для текущей записи, а затем снова извлекается следующая запись, и этот процесс продолжается до тех пор, пока @@FETCH_STATUS не станет равным 0.
Наконец, курсор закрывается и освобождается с помощью команд CLOSE и DEALLOCATE соответственно.
Примечание : очень важно УДАЛИТЬ курсор, иначе он останется в базе данных, и когда вы снова объявите курсор с тем же именем, SQL Server выдаст ошибку: : Курсор с именем Cursor1 уже существует.
Как писать и использовать курсоры в SQL Server Хранимая процедура?
Ниже приведен пример простого курсора в SQL Server. Хранимая процедура, которая печатает все записи таблицы Customers
УСТАНОВИТЬ ANSI_NULLS НА
ГО
УСТАНОВИТЕ QUOTED_IDENTIFIER НА
ВПЕРЕД
СОЗДАТЬ ПРОЦЕДУРУ PrintCustomers_Cursor
КАК
НАЧАЛО
УСТАНОВИТЬ БЕЗ СЧЕТА;
—ОБЪЯВИТЕ ПЕРЕМЕННЫЕ ДЛЯ ХРАНЕНИЯ ДАННЫХ.
ОБЪЯВИТЬ @CustomerId INT
,@Имя VARCHAR(100)
,@Страна VARCHAR(100)
—ОБЪЯВИТЬ И УСТАНОВИТЬ СЧЕТЧИК.
ОБЪЯВИТЬ @Counter INT
НАБОР @Счетчик = 1
—ОБЪЯВИТЬ КУРСОР ДЛЯ ЗАПРОСА.
DECLARE PrintCustomers CURSOR READ_ONLY
ДЛЯ
ВЫБЕРИТЕ идентификатор клиента, имя, страну
ОТ клиентов
—ОТКРЫТЬ КУРСОР.
ОТКРЫТЬ PrintCustomers
—ИЗВЛЕЧЬ ЗАПИСЬ В ПЕРЕМЕННЫХ.
ПОЛУЧИТЬ СЛЕДУЮЩУЮ ОТ PrintCustomers В
@CustomerId, @Имя, @Страна
—LOOP ДО ЗАПИСИ ДОСТУПНЫ.
ПОКА @@FETCH_STATUS = 0
НАЧАЛО
ЕСЛИ @Счетчик = 1
НАЧАЛО
PRINT ‘CustomerID’ + CHAR(9) + ‘Имя’ + CHAR(9) + CHAR(9) + CHAR(9) + ‘Страна’
ПЕЧАТЬ ‘—————————————————‘
КОНЕЦ
—ПЕЧАТАТЬ ТЕКУЩУЮ ЗАПИСЬ.
PRINT CAST(@CustomerId AS VARCHAR(10)) + CHAR(9) + CHAR(9) + CHAR(9) + @Name + CHAR(9) + @Country
—ИНКРЕМЕНТНЫЙ СЧЕТЧИК.
НАБОР @Counter = @Counter + 1
—ИЗВЛЕЧЬ СЛЕДУЮЩУЮ ЗАПИСЬ В ПЕРЕМЕННЫХ.
ПОЛУЧИТЬ СЛЕДУЮЩУЮ ОТ PrintCustomers В
@CustomerId, @Имя, @Страна
КОНЕЦ
—ЗАКРОЙТЕ КУРСОР.
ЗАКРЫТЬ PrintCustomers
DEALLOCATE PrintCustomers
КОНЕЦ
ГО
На следующем снимке экрана показаны записи, напечатанные указанным выше курсором.
Вы также можете проверить расширенный курсор SQL Server, который отправляет электронную почту, в моей статье Автоматические уведомления по электронной почте с использованием планировщика заданий SQL Server.
Недостатки курсора
Основным недостатком Cursor является проблема с производительностью. Курсор может быть очень медленным при выполнении операций с большим количеством записей, и ваш SQL-запрос может занять несколько минут для выполнения и получения результатов.
Таким образом, вы должны мудро выбрать и выбрать правильный сценарий, в котором вы хотите использовать курсор.
Загрузки
PrintCustomers_Cursor.sql
Как использовать курсоры в SQL
Курсоры представляют собой немного более сложную концепцию в SQL, но главным образом потому, что они не рекомендуются большинством разработчиков баз данных из-за их высокого использования ресурсов. Курсоры позволяют создавать циклы в ваших хранимых процедурах, чтобы вы могли оценивать данные от записи к записи. Думайте о курсорах как о сохраненном наборе данных, который затем позволяет вам просматривать каждую запись, манипулировать или просматривать поле, а затем выполнять какую-то логику над записью на основе значения поля. В большинстве случаев вам не нужен курсор в вашем коде, но полезно понимать синтаксис курсора и то, как он работает иначе, чем другой код SQL.
Компоненты курсора
Давайте посмотрим на код курсора. Следующий курсор называется «mycursor», и он перебирает набор записей клиентов.
DECLARE mycursor CURSOR FOR
SELECT CustomerId FROM Customer
WHERE state=’tx’
OPEN mycursor FETCH NEXT FROM mycursor INTO @id
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @id
END
ЗАКРЫТЬ мой курсор
УДАЛИТЬ мой курсор
Это немного больше кода в процедуре, чем мы использовали ранее. Мы вырезали часть оператора хранимой процедуры и сосредоточились только на коде курсора.
Первый раздел кода объявляет курсор. В этом примере имя курсора — «mycursor», а набор данных — оператор SELET, который получает список полей идентификатора клиента, где клиент находится в Техасе. Механизм SQL выделяет память для курсора и заполняет ее набором данных, который вы определяете в операторе SELECT.
Следующим разделом оператора CURSOR является ключевое слово OPEN. Оператор OPEN означает, что вы готовы начать обработку данных в курсоре. Если вы не используете оператор OPEN, хранимая процедура или оператор SQL завершится ошибкой, и ваша база данных выдаст ошибку.
Если у вас есть какой-либо другой опыт программирования, оператор FETCH является частью оператора, который получает вашу следующую запись из набора данных курсора. FETCH похож на начало цикла FOR, который захватывает текущую запись, чтобы подготовить ее к вашим условиям SQL и манипулированию вашими данными. Ключевое слово INTO помещает поля в переменную SQL @id. В этом примере курсору передается только одно поле — поле CustomerId. Если бы у вас было возвращено два столбца, вам понадобятся два столбца. Столбцам присваиваются переменные в том же порядке, в котором они извлекаются в исходном операторе SELECT.
Далее следует инструкция WHILE. Определение переменной @@ до сих пор не встречалось. Объявление @@ указывает, что вы используете системную переменную SQL. Системная переменная @@FETCH_STATUS — это внутренняя ссылка SQL на текущий статус вашей выборки. Если записей больше нет, то у вас больше нет записей для обработки, и оператор WHILE завершится. Без оператора fetch вы создаете бесконечный цикл. Бесконечный цикл — это распространенная ошибка программирования, когда условие основного цикла никогда не выполняется. Если инструкция WHILE никогда не выполняется, то цикл никогда не заканчивается. Ошибка потребляет память и может привести к сбою сервера базы данных. Бесконечный цикл должен быть прерван и остановлен вашим администратором базы данных.
В этом примере оператора курсора база данных SQL просто печатает идентификатор клиента. Вы, вероятно, захотите выполнить какие-то другие действия с вашими данными, но этот оператор курсора — всего лишь пример того, как вы можете использовать курсор для манипулирования данными. Результатом является распечатка ваших идентификаторов клиентов в Техасе. Вы увидите результаты в редакторе SQL.
После завершения обработки данных необходимо очистить ресурсы памяти, которые занимает курсор. Курсор может иметь тысячи и даже миллионы выделенных записей. Вы можете себе представить, сколько ресурсов сервера требуется для хранения курсора с миллионами записей, поэтому администратор базы данных избегает использования курсоров.
Чтобы гарантировать, что курсор не забирает память с вашего сервера, вы должны закрыть курсор и освободить использование памяти. Вы можете увидеть, как очистить ресурсы в приведенном выше операторе, используя операторы курсора CLOSE и DEALLOCATE.
Вы можете поместить курсор в хранимую процедуру, чтобы запустить ее, просто используя имя процедуры. Следующий код SQL создает хранимую процедуру с именем «FetchCursor» в вашей базе данных.
CREATE PROC FetchCursor
(
@state varchar(2)
)
AS
Объявит курсор Mycursor для
SELECT CustomerID от Customer
, где состояние = @State
Открыть MyCursor Fetch Next от Mycursor в @ID
Whe @ @wetch_status = 0
141414141414141414141414141414
. @id
END
CLOSE mycursor
DEALLOCATE mycursor
Приведенный выше оператор помещает ваш курсор в хранимую процедуру и создает динамический код, так что вы можете передать курсору состояние вместо того, чтобы сделать состояние статическим Техас.
Следующий код выводит каждого клиента со значением штата Техас.
EXEC FetchCursor ‘tx’
Рекомендации по использованию курсора
Курсоры, как правило, не являются необходимой опцией при создании отчетов и программ. Обычно вы можете выполнить бизнес-требование с помощью предложения WHERE или некоторых условных операторов, прежде чем извлекать свои записи. Результатом является более быстрая программа и более эффективная обработка данных, особенно когда вы имеете дело с миллионами записей одновременно. Курсоры иногда используются для обработки данных на лету в качестве разовой сделки для разработчика. Другими словами, вы используете его один раз, а затем создаете более эффективную процедуру.
Если вам абсолютно необходимо иметь курсор, вы должны хорошо его спроектировать. Ограничьте количество записей, возвращаемых процедурой. Если вы сможете уменьшить количество обрабатываемых записей, вы ускорите свои отчеты на несколько секунд (иногда даже быстрее, если у вас есть длительные отчеты).
Точно настройте запросы курсора и всегда сначала проверяйте их. Оператор SELECT, который загружает ваши данные в переменные курсора, должен быть настроен так, чтобы он работал хорошо. Например, когда вы ПРИСОЕДИНЯЕТЕСЬ к таблице, вы должны использовать оператор ПРИСОЕДИНЕНИЯ к столбцам, содержащим индексы. Индексированные столбцы значительно сокращают время, необходимое для сбора данных по нескольким табличным ресурсам. На самом деле оператор SELECT, который выполняет JOIN для столбца таблицы без индекса, может занять на несколько минут больше времени, чем для столбцов с индексами.
Если вы не занимаетесь тяжелыми отчетами, вы, скорее всего, не будете использовать курсоры. Курсоры используются в средах отчетов, где данные переносятся на сервер отчетов, поэтому в производственной среде влияние каких-либо ресурсов сервера не ощущается.
Курсоры SQL Server: практическое руководство
Время чтения: 9 минут
Скорее всего, вам понадобится использовать возможности курсора для выполнения какой-либо задачи в Microsoft SQL Server в какой-то момент вашей карьеры. специалист по базам данных.
Курсоры – это инструмент, который мы используем при работе с большими объемами данных, когда вам необходимо выполнять итерации по этим данным для достижения какой-либо цели.
Курсоры являются наиболее распространенным применением цикла WHILE в SQL Server. Не знаете, что такое цикл WHILE? Я сделал для вас полное руководство:
SQL Server WHILE Loop: структура принятия решений, которую вы должны знать
В этом руководстве мы дадим вам пошаговое руководство по написанию курсора и как это может нам помочь. Мы рассмотрим эти темы:
- Что такое курсор и что он делает?
- Шаги по созданию и использованию курсора:
- Объявление курсора.
- Открытие курсора .
- «Выборка» данных для курсора
- Перебор курсора.
- Закрытие и освобождение курсора.
Начнем с основ:
Что такое курсор и что он делает?Курсор позволяет перебирать строки результирующего набора по одной за раз, чтобы выполнять работу над каждой строкой до тех пор, пока в результирующем наборе не останется строк.
Опять же, способ, которым мы «итерируемся» по результирующему набору, заключается в использовании цикла WHILE. Цикл WHILE — это сердце нашего курсора.
Если вы не знакомы с тем, что такое цикл WHILE, вам необходимо сначала просмотреть это руководство:
SQL Server WHILE Loop: структура принятия решений, которую вы должны знать данные.
Существует несколько этапов создания, использования и закрытия курсора, поэтому лучший способ понять их — посмотреть на примере.
Действия по созданию и использованию курсора
Итак, давайте подумаем о хорошем примере, когда нам может понадобиться использовать курсор.
Допустим, вы владелец небольшого предприятия по продаже деревянной мебели. Вы отслеживаете все свои заказы в таблице под названием Orders . Вот как теперь выглядит таблица:
Давайте воспользуемся ВНУТРЕННИМ СОЕДИНЕНИЕМ, чтобы получить более значимые данные о продуктах:
Хорошо, круто. Таким образом, этот запрос показывает нам основные сведения о каждом заказе, который был размещен для нашего бизнеса. Мы видим:
- Номер заказа
- ID покупателя, купившего товар
- Какой товар они купили
- Сколько указанного товара они купили
- Дата заказа
Отлично, так что, возможно, мы хотим получить список того, сколько всего каждого продукта было продано. Это можно сделать с помощью простого запроса GROUP BY:
Итак, теперь мы видим, сколько всего каждого товара было продано.
Здесь мы могли бы использовать курсор:Что, если бы мы захотели записать наши самые продаваемые товары в отдельную таблицу под названием «9».0015 Бестселлеры ‘? Мы говорим, что наши «бестселлеры» — это продукты, которые мы продали более 3 шт.
В основном это означает, что мы смотрим на каждую строку в нашем запросе GROUP BY и видим, какое значение имеет продукт для столбца « Количество проданных ». Если у товара номер « продано » больше, чем 3 , мы записываем его детали в таблицу BestSellers . В противном случае мы оставляем его вне таблицы.
Таблица BestSellers сейчас пуста:
Итак, мы установили, что в основном хотим перебрать каждую строку в нашем запросе GROUP BY, сверху вниз, и решить, нужно ли нам работать над строкой в этом наборе результатов.
Отличная задача для курсора!
И я знаю, это кажется пустой тратой времени для нашего небольшого набора результатов. Похоже, что именно « Small Bench » является нашим бестселлером. Но в SQL Server нам нужно думать БОЛЬШОЙ . Что делать, если вы работаете на Amazon и хотите получить список бестселлеров? С миллионами продуктов это займет у вас навсегда .
Итак, давайте подумаем, как использовать курсор для создания нашего списка бестселлеров:
1. Объявление курсора.Первым шагом к использованию любого курсора в SQL Server является объявление курсора. Этот шаг состоит из двух частей, и они очень просты.
Во-первых, нам в основном нужно дать этому итерационному процессу имя . Допустим, мы хотим назвать наш процесс INSERT_INTO_BEST_SELLERS . Синтаксис для объявления нашего курсора с этим именем следующий:
DECLARE INSERT_INTO_BEST_SELLERS CURSOR
Таким образом, вы просто используете ключевое слово DECLARE , за которым следует имя вашего курсора, за которым следует ключевое слово CURSOR .
Easy peasy
Вторая часть объявления курсора — это описание запроса, набор результатов которого вы хотите пройти. В нашем примере результирующий набор, который мы хотим пройти, следующий:
Это набор запросов/результатов, для которого будет использоваться курсор. Итак, мы включаем этот запрос как часть объявления, в основном так:
Вы просто используете ключевое слово FOR , за которым следует запрос, результаты которого вы хотите перебрать.
(Кроме того, не всем столбцам в вашем запросе нужны имена. Я назвал свой совокупный столбец «Количество проданных», но я мог бы не указывать этот псевдоним, если бы захотел. )
Хорошо, это было легко, правда?
2. Открытие курсора.Этот шаг еще проще . Если мы официально готовы начать использовать наш курсор, нам нужно открыть его.
Синтаксис прост:
Вы используете ключевое слово OPEN , за которым следует имя вашего курсора. Так просто.
3. «Выборка» данных для курсора.Хорошо, мы знали, что это не будет оставаться простым вечно, поэтому давайте поговорим о том, где все становится интереснее.
Давайте подумаем о наборе результатов нашего запроса. Вот оно:
Когда курсор перебирает этот набор результатов, он просматривает каждую строку по одной и выполняет работу с данными этой строки.
Начинается сверху, глядя на самый первый ряд. Чтобы « работал с данными этой строки », нам нужно записать данные этой строки в соответствующие переменные.
Первая строка содержит следующие данные:
ProdID = 1
ProdName = Large Bench
Количество проданных = 1
Нам нужно записать эти значения в 9.Таким образом, чтобы получить первую строку в наборе результатов, а также записать ее значения в переменные, мы используем следующий синтаксис:
Таким образом, этот блок FETCH в основном запускает процесс . Все, что он говорит, это захватить следующую строку в наборе результатов (которая в то время будет просто 9-й строкой).0053 первая строка
) и запишите значения столбца в переменные, которые мы будем использовать позже.Вот что вам нужно сделать: Вам нужно объявить эти переменные.
Хорошее место для объявления этих переменных находится за пределами курсора, например:
Итак, просто повторим, на этом этапе процесса наши переменные содержат данные для самой первой строки , которая будет:
@prodID = 1
@prodName = Большая скамья
@numberSold = 1
Мы собираемся использовать эти переменные в циклическом процессе нашего курсора (где мы « действительно работаем» ).
Хорошо, вот мясо этого .
Вернемся к тому, что мы хотим сделать с помощью этого курсора. Мы хотим просмотреть каждую строку в нашем наборе результатов, и если значение « Количество проданных » больше, чем 3 , мы запишем идентификатор и имя этого продукта в Бестселлеры стол.
Первый шаг к зацикливанию нашего результата, установленного для контура цикла WHILE . Вот как это выглядит сначала:
Нам нужно поговорить о @@FETCH_STATUS . Все, что спрашивается, это извлекла ли наша последняя операция FETCH что-то .
В этот момент в нашем курсоре мы сделали выборку для верхней строки набора результатов. Таким образом, если набор результатов содержит хотя бы одну строку, статус будет: « Что-то было успешно получено ».
Если « что-то было успешно извлечено », значение, возвращаемое @@FETCHED_STATUS, равно ноль .
Я знаю, это кажется бас назад . Обычно состояние ноль является ложным. Но в случае @@FETCH_STATUS нам нужно ноль .
Это просто одна из тех вещей, которые нужно запомнить .
Содержимое цикла WHILE
Теперь нам нужно подумать о содержимом цикла WHILE. Я не собираюсь тратить слишком много времени на обсуждение содержимого этого конкретного цикла WHILE, потому что ваш , вероятно, будет другим .
Просто знайте: В теле цикла WHILE вы выполняете свою работу, вероятно, используя некоторые или все переменные, являющиеся частью вашего курсора.
Вот тело нашего цикла WHILE:
Итак, первое, что мы делаем, это проверяем @numberSold значение для этой конкретной строки набора результатов, в которой мы находимся. Если оно больше 3, мы знаем, что может потребоваться добавить в таблицу BestSellers .
Почему может это? Потому что мы не хотим вставлять детали этого продукта, если он уже находится в таблице BestSellers .
Мы используем переменную @prodID , чтобы узнать, есть ли этот продукт уже в таблице BestSellers . Мы используем наш удобный инструмент ЕСЛИ НЕ СУЩЕСТВУЕТ, чтобы увидеть, если что-то возвращается, когда мы запрашиваем таблицу в поисках этого конкретного идентификатора продукта.
(В ситуациях, когда я хочу знать, существуют ли вообще какие-либо строки, я предпочитаю использовать « SELECT 1 ». Не нужно извлекать данные столбца, если мне на самом деле не нужны данные столбца!)
Итак, если мы знаем, что @numberSold больше 3, и товар еще не находится в таблице BestSellers , то мы, наконец, можем выполнить наш INSERT, который будет использовать @prodID и @prodName переменных.
Не забудьте переместить цикл WHILE вперед.
В цикле WHILE отсутствует кое-что очень важное.
Если вы помните наше руководство по циклу WHILE, мы говорили о том, как вам нужно убедиться, что вы перемещаете цикл вперед .
Помните, что нам нужен курсор, чтобы пройти через все строки, возвращенные нашим запросом курсора. Способ, которым мы поддерживаем цикл, заключается в том, чтобы убедиться, что мы получить следующую строку в результирующем наборе.
Вот так:
Этот загружает наших переменных данными из следующей строки набора результатов из нашего запроса курсора.
Помните, что «запрос курсора» — это в основном запрос, идентифицируемый именем курсора, которое равно INSERT_INTO_BEST_SELLERS .
Эта прекрасная симфония продолжается для всех строк, возвращаемых нашим запросом курсора. Пока этот оператор FETCH продолжает возвращать строки, мы продолжаем входить в цикл WHILE и выполнять работу.
Как только мы обработаем окончательную строку набора результатов, что будет извлечено ?
Ответ: Абсолютно НИЧЕГО .
Итак, в этот момент @@FETCH_STATUS вернет что-то отличное от ноль , что делает наше условие ложным . Это приведет к выходу из цикла!
К сожалению, когда мы выходим из цикла, курсор автоматически не закрывается. Нам нужно вручную закрыть и освободить курсор .
Поскольку нам пришлось вручную открывать курсор, не стоит удивляться, что нам также нужно вручную закрыть курсор.
Все просто:
Задача освобождения курсора может быть не такой очевидной.
За кулисами SQL Server создает структуру данных для удержания курсора и выполнения своей работы. Эта структура данных должна быть освобождена обратно в память, как только мы закончим использовать курсор.
Вам не нужно освобождать курсор, когда вы закончите с ним, но вы должны . Если вы не освободите курсор вручную, он будет освобожден естественным образом, когда выйдет из области действия .
Но, честно говоря, вы должны выработать привычку освобождать объекты, когда с ними покончено . Не следует слишком полагаться на процессы автоматической очистки. Это может привести к неприятностям, если вы будете слишком много брать на себя.
Так что сделайте себе одолжение и просто вручную освободите то, что вы создали:
Вот хорошая ссылка от Microsoft об освобождении курсоров: DEALLOCATE (Transact-SQL)
Вот отличный вопрос о переполнении стека о том, почему важно вручную освободить курсоры: что произойдет, если вы забудете закрыть и освободить курсор?
Итак, теперь, когда наш курсор готов, мы можем быстро запустить его и увидеть, что он успешно вставляет только Small Bench продукт в наш Bestsellers стол:
Приятно!
Следующие шаги:
Оставьте комментарий , если этот урок был вам полезен!
Скорее всего, вы увидите и будете использовать курсоры в хранимых процедурах . Если вам нужно краткое изложение того, что такое хранимые процедуры и как они могут нам помочь, перейдите по этой ссылке:
Хранимые процедуры: полное руководство для начинающих
Кроме того, есть чрезвычайно полезная книга, которая знакомит вас со многими T Темы SQL, включая курсоры, которые вам следует взять в руки. Называется « Основы T-SQL », Ицик Бен-Ган . Это одна из немногих книг, которые помогли мне понять многие темы SQL Server. Вы точно не пожалеете, что приобрели эту книгу, , поверьте мне, . Получите сегодня!
Большое спасибо за чтение!
Не забудьте подписаться на мою рассылку, чтобы получать специальные предложения и уведомления каждый раз, когда выходит новый учебник!
Если у вас есть вопросы, пожалуйста, оставьте комментарий . Или еще лучше, пришлите мне письмо!
Что такое курсор в SQL Server и зачем он нужен?
Курсор SQL — это объект базы данных, который используется для манипулирования данными в наборе, выполнения построчной обработки вместо команд T-SQL, которые одновременно работают со всеми строками результирующего набора.
Есть некоторые условия, когда мы хотим получить запись из одной таблицы и нам нужно вставить ее в другую с выполнением некоторого условия или логики. Курсор — это аналогичный подход к любому циклическому механизму, который можно найти в любом другом программировании.
Идея курсоров звучит прекрасно, и написание курсора кажется хорошим, но когда дело доходит до производительности, можно столкнуться с большими проблемами.
Как создать курсор SQL Server?
Рисунок 1. Жизненный цикл курсора SQL Server.Процесс использования курсора SQL (начиная с MS SQL 2008) можно описать следующим образом:
- Объявление курсора путем определения оператора SQL.
- Открытие курсора для сохранения данных, извлеченных из набора результатов.
- Строки могут быть выбраны из курсора одна за другой или в блоке для выполнения манипуляций с данными.
- Курсор должен быть явно закрыт после обработки данных.
- Курсоры должны быть освобождены, чтобы удалить определение курсора и освободить все системные ресурсы.
Синтаксис курсора
Рисунок 2 – Синтаксис курсора MS SQL Server.Какие существуют типы курсоров в SQL Server?
Microsoft SQL Server поддерживает следующие типы курсоров.
- СТАТИЧЕСКИЙ КУРСОР
Заполняет набор результатов во время создания курсора, а результат запроса кэшируется на время существования курсора. Статический курсор может двигаться вперед и назад. - FAST_FORWARD (тип курсора по умолчанию)
Он идентичен статическому, за исключением того, что вы можете прокручивать только вперед. - ДИНАМИЧЕСКИЙ
Добавления и удаления видны другим в источнике данных, пока курсор открыт. В отличие от статических курсоров, все изменения, сделанные в динамическом курсоре, будут отражать исходные данные. Вы можете использовать этот курсор для выполнения операций INSERT, DELETE и UPDATE. - KEYSET
Это похоже на динамический курсор, за исключением того, что мы не можем видеть записи, добавленные другими. Если другой пользователь удаляет запись, она становится недоступной из нашего набора записей.
Пример
В приведенном ниже примере все таблицы в образце базы данных с именем Spreadsheet переименовываются путем добавления «_Backup» к имени каждой таблицы, а также гарантируют, что таблицы, содержащие «_Backup» в своем имени, не будут переименованы снова, запустив следующий код. :
Рис. 3. Образец курсора SQL Server.Каковы ограничения курсоров?
Курсоры часто используются как неправильный инструмент для не той задачи. Они используются для быстрого программирования, когда разработчик не имеет полного понимания операций над множествами.
Проблема с курсором заключается в том, что в большинстве случаев мы можем использовать JOINS, даже предложения WHILE, пакеты SSIS или другие альтернативные методы, чтобы получить тот же результат быстрее, с меньшим влиянием на производительность и даже с написанием меньшего количества строк синтаксиса. .
Основным фактором, влияющим на скорость курсора, является количество введенных в него строк и столбцов. Пока курсор заполняется, итерируемая таблица блокируется. Выполнение операций со строками может занять много времени в зависимости от типа задачи, выполняемой для каждой строки. Пока курсор открыт, другие пользователи не могут получить доступ к таблице или обновить ее.
Курсоры можно использовать в некоторых приложениях для сериализации, но в целом их следует избегать, поскольку они отрицательно влияют на производительность, особенно при работе с большим набором данных.
Альтернативы курсорам SQL Server
Существует множество альтернатив курсорам SQL, поэтому давайте подробно рассмотрим стратегию временных таблиц. Временные таблицы используются уже давно и представляют собой отличный способ замены курсоров для больших наборов данных. Во временных таблицах может храниться результирующий набор, чтобы мы могли выполнять необходимые операции, обрабатывая его с помощью повторяющегося алгоритма, такого как цикл while.
Мы будем использовать временную таблицу для переименования всех таблиц в образце, выполнив следующие действия:
- Объявить временную таблицу;
- Сохранить имена и идентификаторы таблиц;
- Установите счетчик на 1 и получите общее количество записей из временной таблицы;
- Используйте цикл while, если значение счетчика меньше или равно общему количеству записей;
- В цикле переименовывать таблицы одну за другой, если они еще не переименованы, и увеличивать счетчик для каждой таблицы;