Sql

Курсоры sql пример: DECLARE CURSOR (Transact-SQL) — SQL Server

Содержание

Курсоры (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 курсорами:

  1. Свяжите курсор с результирующий набор инструкции Transact-SQL и определите характеристики курсора, например, можно ли обновлять строки в курсоре.

  2. Выполните инструкцию Transact-SQL, чтобы заполнить курсор.

  3. Получить в курсор необходимые строки. Операция получения в курсор одной и более строк называется выборкой. Выполнение серии выборок для получения строк в прямом или обратном направлении называется прокруткой.

  4. При необходимости выполнить операции изменения (обновления или удаления) строки в текущей позиции курсора.

  5. Закрыть курсор.

См. также

Режимы работы курсоров
Способы реализации курсоров

См. также:

DECLARE CURSOR (Transact-SQL)
Курсоры (Transact-SQL)
Функции работы с курсорами (Transact-SQL)
Хранимые процедуры курсора (Transact-SQL)
SET TRANSACTION ISOLATION LEVEL (Transact-SQL)

Курсоры в 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) на уже созданной базе данных. В данном примере устан

(читать далее…)

555  

Чистка логов базы данных MSSQL

Вообще процесс чистки логов должен проходить планово, и следить за этим и настраивать должен професс (читать далее…)

226  

Пример MERGE в MSSQL T-SQL

Простой пример MERGE для TSQL. В примере подразумевается, что мы оперируем двумя одинаковыми по стру (читать далее…)

161  

MSSQL — передача таблицы или списка значений в процедуру ( C# .NET )

Часто бывает необходимость передать за один раз некоторый набор данных в процедуру, в этой публикаци (читать далее…)

139  

Получение полей таблицы в MSSQL — TSQL

Этот запрос возвращает набор полей таблицы со всеми характеристиками. Метод также применим и замечат (читать далее…)

105  

Изучите курсор 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 Интересный небольшой пример:

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

Структура таблицы следующая:

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

Результат такой:

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

ANAMEBNAME
AdamsJames
JamesAdams

 

 

 

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

 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

Интеллектуальная рекомендация

Несколько вопросов о справочных указателях 2018-06-15

Указатель по умолчанию под дугой является сильная ссылка: __ SICK & __ слабое и __ небезопасное сравнение ссылка:__strong & __weak & __ Unsafe_unreted…

Pytorch использует больше средств GPU

Использование нескольких графических процессоров в Pytorch требует инициализации заявленной модели после Декларационной модели, такой как: Затем, после запуска файла Python The Model Training, все GPU…

Как долго это так долго? Как логистические роботы не побежали на тысячи домохозяйств?

В заключенных ЦЕС Группа «Немецкая континентальная группа демонстрирует свои последние логистические роботы» — собака доставки пакетов Anymal. Для этого результат Круг медиа и технологичес…

Примечания к практическому изучению машинного обучения — алгоритм априори

Анализ ассоциаций — это задача поиска интересных взаимосвязей в крупномасштабных наборах данных. Эти отношения могут принимать две формы: Частые наборы элементов: набор элементов, которые часто появля…

Spring (4) Фреймворк заключительной главы третьей интеграции

Spring_day04 (интеграция трех основных фреймворков) 1. Три фреймворка (принцип интеграции) Бэкэнд веб-проекта разделен на три слоя. 2. Направляющий пакет (42) hibernate: hibernate/lib/required hiberna…

Вам также может понравиться

Первое понимание юнит-теста серии Python

Среду модульного тестирования unittest можно применять не только для модульного тестирования, но и для разработки и выполнения веб-автоматизированных тестовых примеров.Конструктура тестирования может …

Принцип непоследовательной загрузки изображений в процессе загрузки изображений в виде списка.

// Основная раскладка интерфейса // Вложенный макет // Основная функция MainActivity // Создать новый класс бина // Создать адаптер // Инструменты…

SpringBoot + mysql + развертывание проекта docker

Подготовка доменного имени и сервера Alibaba Cloud доменное имя: Вы можете приобрести необходимые доменные имена у основных поставщиков облачных услуг. Я приобрел доменное имя Alibaba Cloud. В облако …

Строить IPA-сервер с нуля. Реализация LDAP + Kerberos домена Проверка (Open Firewall, Command Version)

Рисунок метод конфигурации, пожалуйста, обратитесь к статье 1, экспериментальная среда: Физика хост-хост две виртуальные машины. Физический IP хост: 192.168.9.6/24 GW: 192.168.9.254 DNS: 8.8.8.8 Вирту…

Вызов клиента Центра конфигурации Apollo

Вызов клиента Центра конфигурации Apollo введение Центр конфигурации Создать проект Опубликовать пространство имен Создайте файл конфигурации локального кеша код проекта springboot Предыдущая запись: …

Курсоры (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, т. е. 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 и курсора T-SQL

    Вот пример кода курсора SQL , созданный для , выполняющий цикл по списку записей в результате запроса на выборку, который позволяет разработчику SQL выполнить хранимую процедуру для каждой строки в курсоре, которая использует значения, полученные курсор в качестве входных аргументов. Образец курсора разработан на MS SQL Server и является образцом для курсора сервера sql. Коды sql могут использовать коды t-sql, поэтому пример sql-курсора может отличаться от обычного курсора pl/sql или курсора Oracle.

    Приведенный ниже пример кода sql-курсора иллюстрирует процесс слияния повторяющихся записей клиентов , хранящихся в базе данных приложения. Предположим, что дубликат список клиентов и взаимосвязь между повторяющимися записями клиентов вставляются и хранятся в таблице с именем DuplicateCustomers, которая просто формируется из столбцы MasterCustomerId, DuplicateCustomerId и некоторые другие столбцы, такие как MergeDate, IsMerged, MergedByUserId, InsertedDate, InsertedByUserId и т. д., которые используются во время обработки некоторых деталей и полезны при составлении отчетов о результатах процесса слияния дубликатов записей.


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

    ВЫБЕРИТЕ MasterCustomerId, DuplicateCustomerId
    ИЗ DuplicateCustomers, ГДЕ IsMerged = 0

    Код

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

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

    DECLARE @MergeDate Datetime
    DECLARE @MasterId Int
    DECLARE @DuplicateId Int

    Код

    Затем выполняется определение курсора sql или код объявления курсора t-sql .

    DECLARE merge_cursor CURSOR FAST_FORWARD FOR
    SELECT MasterCustomerId, DuplicateCustomerId
    ОТ DuplicateCustomers WHERE IsMerge = 0

    Код

    В примере объявления курсора sql вы можете установить свойства курсора sql или атрибуты курсора. Обратите внимание, что в примере объявления курсора используется ключевой атрибут FAST_FORWARD для создания курсора sql с высокопроизводительным . Поскольку FAST_FORWARD указывает, что курсор FORWARD_ONLY и READ_ONLY , производительность курсора оптимизирована.

    Синтаксис t-sql команды объявления курсора DECLARE CURSOR указан в MSDN следующим образом:

    ОБЪЯВИТЬ имя_курсора CURSOR
    [ МЕСТНЫЙ | ГЛОБАЛЬНЫЙ]
    [ТОЛЬКО ПЕРЕДНИЙ | ПРОКРУТКА]
    [СТАТИЧЕСКИЙ | КЛЮЧ | ДИНАМИЧЕСКИЙ | FAST_FORWARD]
    [ТОЛЬКО ДЛЯ ЧТЕНИЯ | SCROLL_LOCKS | ОПТИМИСТИЧЕСКИЙ ]
    [TYPE_WARNING ]
    FOR select_statement
    [ FOR UPDATE [ OF column_name [ ,...n ] ] ]

    Код

    Дополнительные сведения о том, как объявить курсор t-sql и атрибуты курсора, см. в электронной документации 9.0017

    При вызове команды OPEN курсор сервера t-sql открывается, и курсор заполняется данными путем выполнения запроса выбора команды DECLARE CURSOR .

    ОТКРЫТЬ merge_cursor

    Код

    Таким образом, команда OPEN запускает или выполняет запрос выбора «SELECT MasterCustomerId, DuplicateCustomerId FROM DuplicateCustomers WHERE IsMerged = 0», определенный в команде определения DECLARE CURSOR, которая устанавливается после ключа FOR. При выполнении этого запроса на выборку курсор заполняется строками или данными, возвращенными в качестве результирующего набора запроса.

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

    FETCH NEXT FROM merge_cursor INTO @MasterId, @DuplicateId

    Код

    Синтаксис команды FETCH следующий:

    FETCH
    [ [ СЛЕДУЮЩИЙ | ПРЕД | ПЕРВЫЙ | ПОСЛЕДНИЙ
    | АБСОЛЮТНО { п | @nvar }
    | ОТНОСИТЕЛЬНЫЙ { п | @nvar }
    ]
    FROM
    ]
    { { [ GLOBAL ] имя_курсора } | @cursor_variable_name }
    [ INTO @variable_name [ ,...n ] ]

    Код

    При использовании NEXT команда FETCH NEXT возвращает следующую строку после текущая строка. Если FETCH NEXT вызывается в первый раз для курсора или мы можем сказать, что он вызывается после команды OPEN CURSOR, то первая строка в возвращаемый набор результатов извлекается или возвращается. Значения столбцов в возвращаемой строке могут быть установлены в переменные с помощью клавиши INTO и путем указания имен переменные в виде списка, разделенного запятыми, после клавиши INTO.

    Итак, для нашего примера первая строка в возвращаемом результирующем наборе курсора задается двумя переменными с именами @MasterId и @DuplicateId. Тут один важный момент первый столбец набора результатов (столбец с именем MasterCustomerId) устанавливается на первую переменную, определенную в списке, которая является переменной @MasterId. И Второй столбец с именем DuplicateCustomerId установлен на вторую переменную @DuplicateId.

    Таким образом, типы переменных должны быть тщательно объявлены в соответствии с типами столбцов выбранных строк.

    После команды FETCH вы всегда должны контролировать значение @@FETCH_STATUS . Эта переменная возвращает статус последней команды FETCH курсора в текущем соединении.

    Возможные возвращаемые значения @@FETCH_STATUS:

    0603 Выбранная строка отсутствует
    0 Оператор FETCH выполнен успешно
    -1 Оператор FETCH не выполнен или строка выходит за пределы набора результатов
    -2

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

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

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

    После обработки всех записей параметр @@FETCH_STATUS возвращает -1, поэтому теперь курсор можно закрыть с помощью команды CLOSE CURSOR . ЗАКРЫТЬ КУРСОР освобождает текущий набор результатов.

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

    Ваш адрес email не будет опубликован. Обязательные поля помечены *