Sql курсоры: DECLARE CURSOR (Transact-SQL) — SQL Server

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

< Лекция 12 || Лекция 13: 123 || Лекция 14 >

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

Ключевые слова: запрос, приложение, запись, курсор, указатель, базы данных, SQL, действия с курсором, объявление курсора, открытие курсора, выборка из курсора, изменение данных в курсоре, закрытие курсора, освобождение курсора, определение, память, освобождение памяти, операции, server, виды курсоров, доступ, стабильность, последовательный курсор, прокручиваемый курсор, пользователь, статический курсор, динамический курсор, сервер, курсор, управляемый набором ключей, удаление данных из курсора, информация, драйвер, управление курсором, deallocation, Prior, функция, стоимость, курсор как выходной параметр процедуры, список, вывод, печать

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

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

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

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

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

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

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

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

Реализация курсоров в среде MS SQL Server

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

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

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

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

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

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

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

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

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

В статический курсор внести изменения невозможно, поэтому он всегда открывается в режиме «только для чтения».

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

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

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

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

Дальше >>

< Лекция 12 || Лекция 13: 123 || Лекция 14 >

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

412  

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

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

207  

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

NET )

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

109  

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

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

95  

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

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

84  

Тайна SQL-курсора

Дэн Рахлис

Вы когда-нибудь задумывались, кто использовал SQL-курсор или хотя бы понимали, что это такое?

Вы когда-нибудь задумывались над этим вопросом? Скорее всего, у вас никогда не было возможности применить SQL Cursor на практике в любом из ваших проектов, связанных с работой. Недавно у меня была возможность использовать курсор SQL для проекта, и я понял, насколько он мощный и полезный. Наличие технических инструментов для манипулирования данными может быть очень полезным для эффективного управления вашим временем для предоставления результатов клиентам. Актуарии, в частности, должны постоянно совершенствовать свои технические навыки, чтобы адаптироваться к определенным потребностям проекта и своевременно предоставлять результаты внутренним и внешним клиентам. Эта статья определяет и объясняет, что такое курсор SQL, и призвана помочь вам понять и использовать его в будущем.

Давайте начнем с основ, предполагая, что вы знаете основы SQL Server как инструмента для обработки и анализа данных: Что такое курсор SQL? Курсор SQL — это управляющая структура, позволяющая перемещаться по записям в базе данных. В обычном языке курсоры используются для обработки и выполнения логики над отдельными строками, возвращаемыми запросом. Его можно рассматривать как указатель на одну строку в таблице. Курсор может ссылаться только на одну строку за раз, но может перемещаться по всем строкам таблицы. Исторически было рекомендовано использовать курсор в крайнем случае, потому что SQL не предназначался для транзакций, а больше ориентировался на наборы данных. Но это может быть очень мощным и более эффективным, чем некоторые обходные пути. Курсор можно использовать в анализаторе SQL-запросов точно так же, как обычный оператор select—from. Вам нужно будет выучить только несколько новых операторов SQL:

• Объявить курсор, определяющий набор результатов, или объявить локальную переменную, используя оператор Select
• Откройте курсор, чтобы выполнить оператор Select и заполнить результирующий набор
• Извлекать данные в локальные переменные по мере необходимости из курсора, по одной строке за раз
• Закройте курсор, когда закончите
• Освободить ресурсов после завершения

Пример курсора SQL — с правильным синтаксисом

В этом примере я собираюсь определить несколько курсоров (называемых CLAIM и LINE), которые будут использоваться для просмотра набора данных заявлений о здоровье, и перенумеровать номера строк, чтобы они были последовательными и имели трехзначный формат символов. Это из реального проекта, но я не могу сказать вам, что это такое, за исключением того, что мы должны были создавать записи требований в определенном формате (где номера строк не могли быть дубликатами и должны были состоять из трех цифр с ведущими нулями). Теперь, когда мы это прояснили, первым шагом будет создание временной таблицы из существующего набора данных для уникального набора заявок, для которых нам нужно изменить нумерацию номеров строк заявок. Если вы не знаете, временная таблица такая же, как и обычная таблица базы данных, за исключением того, что она имеет символ # перед именем и доступна для использования только тогда, когда база данных открыта.

Выбрать отдельный идентификатор_заявки
В #различное_требование_ид
Из медицинского_требования_2010

Следующий шаг — объявить несколько переменных, которые будут использоваться в курсоре. Эти переменные будут использоваться курсорами в сценарии SQL ниже. Локальная переменная SQL — это объект, который может содержать одно значение данных определенного типа. Обычно в сценариях SQL используются переменные:

• В качестве счетчика либо для подсчета количества выполнений цикла, либо для контроля количества выполнений цикла;
• Для хранения значения данных для проверки оператором управления потоком; или
• Для сохранения значения данных, которое будет возвращено кодом возврата хранимой процедуры или возвращаемым значением функции.

объявлять @claimid_in varchar(50)
объявлять @claimid_update varchar(50)
объявлять @claimline# varchar(50)
объявлять @linesequence# varchar(3)
объявлять @counter int

Следующим шагом является определение курсоров . Курсор CLAIM — это уникальный номер утверждения, который мы будем использовать для перехода по набору данных для каждого утверждения и перенумерации номера строки. Курсор LINE — это уникальный номер заявки и номер строки заявки, который используется для обновления набора данных с помощью моей переменной счетчика строк с именем @linesequence#. Переменная @counter — это мой счетчик номера строки для номера претензии, который я сейчас сбрасываю.

Объявить курсор CLAIM для выбора идентификатора_заявки из #distinct_claim_id
Объявить курсор LINE для выбора идентификатора_заявки, номера_линии_запроса
Из медицинского_требования_2010, где требование_ид=@claimid_in

Если собрать все вместе, получится запрос SQL ниже (обратите внимание на синтаксис , так как это очень важно при написании операторов SQL.) Определяются временная таблица и переменные, затем открывается курсор CLAIM и из набора данных извлекается первая запись. Первая запись результата запроса помещается в переменную с именем @claim_in. Fetch_Status=0 — это тест, чтобы увидеть, находитесь ли вы в конце файла, и он будет отключен, если больше нет записей для обработки. У каждого курсора также есть операторы Begin и End, похожие на оператор While, которые сообщают SQL, где он начинается и заканчивается.

Следующим шагом является определение курсора LINE (подумайте об этом как о вложенных запросах или циклах). Курсор LINE используется для обновления фактического основного набора данных для каждой комбинации номера строки претензии и строки претензии. Курсор LINE зависит от курсора CLAIM и переменной @claimid_in, на которую в данный момент указывает указатель. Этот курсор открывается, и первая запись извлекается и помещается в две локальные переменные с именами @claim_update и @claimline#. Затем переменные счетчика устанавливаются в ноль, а затем в раздел основного действия. Текущая запись, извлекаемая из курсора LINE, соответствует основному утверждению из курсора CLAIM. Счетчик увеличивается и переформатируется с ведущими нулями с использованием операторов условия if. Затем текущая запись обновляется с помощью оператора Update. Что здесь уникально, так это оператор «Where Current of Line», который SQL распознает как текущую запись от курсора, в данном случае LINE. Следующая запись — Fetched для претензии, которую мы сейчас обрабатываем. Когда больше нет записей (или строк заявки, как их обычно называют) для этой уникальной заявки, внутренний курсор LINE отключается и передает обработку обратно курсору CLAIM для получения следующего номера заявки. Операторы Close и Deallocate освобождают текущий набор записей и ресурсы, используемые SQL для курсора.

Результатом этого запроса будет преобразование, которое выглядит следующим образом:

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

Дэн Рахлис — специалист-мастер в чикагском офисе Deloitte Consulting LLP. С ним можно связаться по адресу drachlis@deloitte. com или по телефону 312.486.5631.

Как использовать курсоры в SQL

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

Компоненты курсора

Давайте посмотрим на код курсора. Следующий курсор называется «mycursor», и он перебирает набор записей клиентов.

объявить курсор Mycursor для

Select CustomerId из Customer

, где State = ‘TX’

Открыть Mycursor Fetch Next из Mycursor int @id

while @@ fetch_status = 0

Begin

@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

Declare Mycursor Cursor для

Select CustomerId из клиента

, где состояние = @State

Откройте MyCursor Fetch Nex @id

END

CLOSE mycursor

DEALLOCATE mycursor

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

Следующий код выведет каждого клиента со значением штата Техас.

EXEC FetchCursor ‘tx’

Рекомендации по использованию курсора

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

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

Точно настройте запросы курсора и всегда сначала проверяйте их. Оператор SELECT, который загружает ваши данные в переменные курсора, должен быть настроен так, чтобы он работал хорошо. Например, когда вы ПРИСОЕДИНЯЕТЕСЬ к таблице, вы должны использовать оператор ПРИСОЕДИНЕНИЯ к столбцам, содержащим индексы. Индексированные столбцы значительно сокращают время, необходимое для сбора данных по нескольким табличным ресурсам.