SQL-Ex blog
В языке SQL курсоры служат указателями, которые позволяют языку написания приложений обрабатывать результаты запросов построчно. В данной статье вкратце объясняется концепция и показывается, как объявить курсор, открыть, извлечь из него данные, а затем закрыть.
Курсоры SQL
Данные в реляционной базе данных рассматриваются как множества. Как следствие, результаты запроса возвращаются операторами SELECT в виде результирующих наборов. Эти результирующие наборы есть не что иное, как одна или более строк и столбцов, извлеченных из одной или более таблиц. Вы можете пролистать результирующие наборы, чтобы вытащить необходимую вам информацию. Возвращаемые элементы данных используются языками программирования типа Java или любыми другими в конкретных целях приложения. Однако здесь имеется проблема, обусловленная несоответствием структур модели базы данных и моделью языка программирования.
- столбцы (или атрибуты) и их типы данных;
- строки (записи или кортежи);
- таблицы (коллекция записей).
Таким образом, курсор служит указателем, который позволяет языку программирования обрабатывать результаты запроса по одной записи за раз. Курсор может перемещаться по всем строкам результата запроса, позиционируясь всякий раз на отдельной строке. Рассмотрим следующий запрос SQL:
SELECT emp_no, first_name, last_name, birth_date
FROM employees
WHERE MONTH(birth_date) = MONTH(CURRENT_DATE)
AND DAY(birth_date) = DAY(CURRENT_DATE);
Вышеприведенный оператор вернет информацию о тех сотрудниках, чей день рождения попадает на текущие день и месяц. Результат может содержать много строк, но базовый язык приложения может обработать одновременно только одну строку. В результате, курсор объявляется как встроенный в соответствующий язык программирования оператор SQL. Затем курсор открывается подобно файлу, и извлекается отдельная строка из результата запроса. Остальные строки извлекаются последовательно до тех пор, пока курсор не будет закрыт.
Объявление курсора
Курсоры объявляются во многом подобно переменной. Курсору дается имя, имеются операторы для открытия курсора, извлечения результатов запроса и, наконец, закрытия курсора. Отметим, что различные реализации SQL поддерживают использование курсоров по-разному. Но здесь имеется общее соглашение как должен записываться курсор.
Мы должны использовать несколько операторов SQL для полной реализации функциональности курсора, т.к. одно лишь объявление курсора недостаточно для извлечения данных из базы данных. Вот 4 основных шага для работы с курсором:
DECLARE CURSOR: Объявление начинается с задания имени курсору и присвоения ему выражения запроса, которое будет вызываться при открытии курсора.
OPEN: Оператор open выполняет выражение запроса курсора и подготавливает результат запроса для последующего обхода (FETCH).
FETCH: Извлекает значения данных в переменные, которые затем могут передаваться базовому языку программирования или другим встроенным операторам SQL.
CLOSE: Курсор закрывается, и больше не может извлекать результаты запроса.
DECLARE
[SENSITIVE | INSENSITIVE | ASENSITIVE]
[SCROLL | NO SCROLL] CURSOR
[ WITH HOLD | WITHOUT HOLD]
[ WITH RETURN | WITHOUT RETURN]
FOR
[ ORDER BY ]
[ FOR ]>]
Основной частью объявления курсора является:
DECLARE FOR
Необязательная часть, такая как [SENSITIVE | INSENSITIVE | ASENSITIVE] определяет, будет ли курсор чувствителен к изменениям, и будет ли отражать эти изменения в результатах запроса. SENSITIVE означает, что курсор реагирует на изменения, INSENSITIVE означает, что на курсор изменения не влияют, а ASENSITIVE означает, что изменения могут быть видны или не видны в курсоре. Если не указано, то предполагается опция ASENSITIVE.
Необязательные [SCROLL | NOSCROLL] определяют возможность пролистывания курсора. Если не указано, предполагается опция NOSCROLL.
Необязательные [ WITH HOLD | WITHOUT HOLD] определяют, сохранять ли курсор или автоматически закрывать его, когда связанная с курсором транзакция фиксируется. Если не указано, поддерживается опция WITHOUT HOLD.
Необязательные [ WITH RETURN | WITHOUT RETURN] определяют, следует ли возвращать результирующий набор курсора вызывающей стороне, такой как другая подпрограмма SQL или базовый язык. Если не указано, предполагается WITHOUT RETURN.
Предложение ORDER BY используется для сортировки возвращаемых результатов запроса.
Опция UPDATE относится к использованию операторов UPDATE или DELETE, связанных со строками, возвращаемыми оператором SELECT в определении курсора. Любая подобная модификация невозможна, если мы указываем опцию READ ONLY. Если не указана, то по умолчанию принимается опция UPDATE.
Итак, простой курсор может быть объявлен следующим образом:
DECLARE mycursor CURSOR
FOR
SELECT emp_no, first_name, last_name, birth_date
FROM employees
WHERE MONTH(birth_date) = MONTH(CURRENT_DATE)
AND DAY(birth_date) = DAY(CURRENT_DATE);
Курсоры в MySQL
В MySQL имеется два основных типа курсоров: только на чтение (read-only) и только вперед (forward-only). Эти курсоры могут использоваться в хранимых процедурах MySQL. Они помогают нам итерационно обходить результаты запроса по одной строке за раз и извлекать значения в переменные для последующей обработки. Имеется возможность объявлять более одного курсора и вкладывать их в циклы. Заметим, что курсоры являются курсорами только на чтение, поскольку они для итераций используют временные таблицы. Обычно курсор выполняет запрос при его открытии.
Одной из проблем, связанных с курсорами в MySQL, является их отрицательное влияние на производительность из-за дополнительных операций ввода/вывода, которые они производят. Это особенно справедливо для больших типов данных, таких как BLOB и TEXT. Т.к. курсоры работают с временными таблицами, эти типы не поддерживаются в таблицах в памяти (in-memory). Следовательно, при работе с этими типами данных MySQL должна создавать временные таблицы на диске, что требует множества операций ввода/вывода, что особенно плохо для таких медленных устройств, которыми являются диски. Это основная причина низкой производительности курсоров.
MySQL также не поддерживает курсоров на стороне клиента, однако клиентское API может эмулировать их при необходимости. Но тогда это не сильно отличается от извлечения результата в массив на языке программирования типа Java, и манипулирования этим массивом вместо использования курсора.
Вот пример написания курсора в MySQL:
CREATE PROCEDURE 'cursor_demo'()
BEGIN
DECLARE done INT DEFAULT FALSE;
DECLARE id INT(11);
DECLARE fn varchar(14);
DECLARE ln varchar(16);
DECLARE bdate date;
DECLARE mycursor CURSOR FOR
SELECT emp_no, first_name, last_name, birth_date
FROM employees
WHERE MONTH(birth_date)=MONTH(CURRENT_DATE)
AND DAY(birth_date)=DAY(CURRENT_DATE);
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
OPEN mycursor;
fetch_loop: LOOP
FETCH mycursor INTO id, fn, ln, bdate;
IF done THEN
LEAVE fetch_loop;
END IF;
SELECT id, fn, ln, bdate;
END LOOP;
CLOSE mycursor;
END
Вызвать хранимую процедуру можно так:
mysql> CALL cursor_demo
Процедура извлекает те строки из таблицы с именем employee, для которых дата рождения совпадает с текущим днем и месяцем, в курсор с именем mycursor и просто печатает их с помощью оператора SELECT.
Заключение
Курсоры — это всего лишь указатели на множества записей, возвращаемых запросом SQL. Указатель указывает на одну строку в каждый момент времени и может перемещаться в цикле для извлечения отдельных записей. SQL обычно используется для прямого доступа и создания объектов данных. Курсоры предоставляют технику интерактивного SQL, позволяя выполнять непосредственно SQL-операторы с помощью клиентского приложения. Механизм курсора обеспечивает модель доступа к данным, при котором операторы SQL встраиваются в базовый язык типа С, С++ или Java и т.д.
Эта статья представляет собой краткий очерк, чтобы начать работать с курсорами. Обратитесь к документации соответствующего сервера баз данных SQL за деталями по конкретной форме реализации.
Обратные ссылки
Нет обратных ссылок
Комментарии
Показывать комментарии Как список | Древовидной структурой Нет комментариев. Автор не разрешил комментировать эту запись
Курсоры SQL Server
Операции в реляционной базе данных выполняются над множеством строк. Например, набор строк, возвращаемый инструкцией 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 Server Native Client или драйвер ODBC передает запрос серверу для действия с курсором сервера API.
Клиентские курсоры
Клиентские курсоры реализуются внутренне драйвером ODBC собственного клиента SQL Server и библиотекой DLL, реализующей API ADO. Клиентские курсоры реализуются посредством кэширования всех строк результирующего набора на клиенте. Каждый раз, когда клиентское приложение вызывает функцию курсора API, драйвер ODBC собственного клиента SQL Server или библиотеку DLL ADO выполняет операцию курсора для строк результирующих наборов, кэшированных на клиенте.
Типы курсоров
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-SQL WHERE CURRENT OF . Обновления, сделанные вне курсора, не видны до момента фиксации, если только уровень изоляции транзакций с курсорами не имеет значение READ UNCOMMITTED. Дополнительные сведения об уровнях изоляции см. в разделе SET TRANSACTION ISOLATION LEVEL (Transact-SQL).
В планах динамических курсоров никогда не используются пространственные индексы.
Запрос курсора
SQL Server поддерживает два метода запроса курсора:
- Transact-SQL Язык Transact-SQL поддерживает синтаксис для использования курсоров, моделироваемых после синтаксиса ISO-курсора.
- API-функции курсоров базы данных. SQL Server поддерживает функциональные возможности курсоров этих API базы данных:
- ADO (объект данных Microsoft ActiveX)
- OLE DB
- открытый интерфейс доступа к базам данных (ODBC).
Оба этих способа никогда не должны использоваться в приложении одновременно. Приложение, использующее API для указания поведения курсоров, не должно выполнять инструкцию Transact-SQL DECLARE CURSOR, чтобы также запросить курсор Transact-SQL. Инструкция DECLARE CURSOR может использоваться только в том случае, если все атрибуты API-курсоров будут установлены в значения по умолчанию.
Если ни курсор Transact-SQL, ни курсор API не запрошен, SQL Server по умолчанию возвращает полный результирующий набор, известный как результирующий набор по умолчанию, приложению.
Обработка курсоров
Курсоры Transact-SQL и курсоры API имеют другой синтаксис, но следующий общий процесс используется со всеми курсорами SQL Server:
- Свяжите курсор с результирующий набор инструкции Transact-SQL и определите характеристики курсора, например, можно ли обновлять строки курсора.
- Выполните инструкцию Transact-SQL, чтобы заполнить курсор.
- Получить в курсор необходимые строки. Операция получения в курсор одной и более строк называется выборкой. Выполнение серии выборок для получения строк в прямом или обратном направлении называется прокруткой.
- При необходимости выполнить операции изменения (обновления или удаления) строки в текущей позиции курсора.
- Закрыть курсор.
SQL-Ex blog
В своем коде T-SQL я всегда использую теоретико-множественные операции. Мне говорили, что SQL Server ориентирован на обработку операций этого типа, и они будут выполняться быстрей, чем последовательная обработка. Я знаю, что существуют курсоры, но я не знаю, как их использовать. Можете дать мне несколько примеров курсора? Можете указать мне, когда использовать курсоры? Я предполагаю, что Microsoft включил их в SQL Server намеренно, поэтому они должны иметь свою нишу, где могут быть использованы эффективно.
Решение
- Рассмотрим пример курсора
- Разобьем курсор на компоненты
- Дадим дополнительные примеры курсора
- Проанализируем аргументы за и против использования курсоров
Пример курсора SQL Server
В качестве примера курсора возьмем простой скрипт для создания резервных копий всех баз данных SQL Server, в котором бэкапы создаются в последовательном стиле:
DECLARE @name VARCHAR(50) -- имя базы данных
DECLARE @path VARCHAR(256) -- путь для файлов бэкапа
DECLARE @fileName VARCHAR(256) -- имя файла бэкапа
DECLARE @fileDate VARCHAR(20) -- используется для имени файла
SET @path = 'C:\Backup\'
SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)
DECLARE db_cursor CURSOR FOR
SELECT name
FROM MASTER.dbo.sysdatabases
WHERE name NOT IN ('master','model','msdb','tempdb')
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
SET @fileName = @path + @name + '_' + @fileDate + '.BAK'
BACKUP DATABASE @name TO DISK = @fileName
FETCH NEXT FROM db_cursor INTO @name
END
CLOSE db_cursor
DEALLOCATE db_cursor
Что такое курсор в SQL Server
Курсор SQL Server — это набор логики T-SQL, повторяемой в цикле для заданного количества строк по одной за раз. В курсоре может может выполняться обновление одной строки за раз или административный процесс, подобный последовательному созданию резервных копий баз данных. Курсоры используются при разработке, в административных процессах и процессах ETL.
Как написать курсор в SQL Server
- Объявите ваши переменные (для имен файлов, имен баз данных, номеров счетов и т.д.), которые вам нужны для реализации логики, и присвойте им начальные значения.
Эта логика будет меняться в зависимости от задачи. - Объявите курсор с конкретным именем (как db_cursor в этом примере), которое вы будете использовать на протяжении всей логики вместе с бизнес-логикой (оператор SELECT) для наполнения курсора требуемыми записями. Имя курсора может быть осмысленным. Сразу после этого следует открытие курсора.
Эта логика будет меняться в зависимости от задачи. - Извлеките запись из курсора, чтобы начать обработку.
Замечание. Число переменных, объявленных для курсора, число столбцов в операторе SELECT и число переменных в операторе FETCH одинаково. В рассматриваемом примере имеется только одна переменная для извлечения данных из единственного столбца. Однако если должно быть пять элементов данных в курсоре, то необходимо также указать пять переменных в операторе FETCH. - Обработка данных уникальна для каждого набора логики. Это может быть вставка, обновление, удаление и т.д. для каждой извлекаемой строки данных. Это самый важный набор логики в данном процессе, который выполняется для каждой строки.
Эта логика будет меняться в зависимости от задачи. - Извлечение следующей записи из курсора, как это делалось на шаге 3, а затем шаг 4 снова повторяется при обработке выбранных данных.
- По завершению обработки всех данных курсор закрывается.
- На последнем и важном шаге вам необходимо освободить курсор, т.е. освободить все удерживаемые внутренние ресурсы SQL Server.
-- 1 - Объявление переменных
-- * ЗДЕСЬ ЗАМЕНИТЬ НА ВАШ КОД *
DECLARE @name VARCHAR(50) -- имя базы данных
DECLARE @path VARCHAR(256) -- путь в файлам резервных копий
DECLARE @fileName VARCHAR(256) -- имя файла бэкапа
DECLARE @fileDate VARCHAR(20) -- используется для имени файла
-- Инициализация переменных
-- * ЗДЕСЬ ЗАМЕНИТЬ НА ВАШ КОД *
SET @path = 'C:\Backup\'
SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)
-- 2 - Объявление курсора
DECLARE db_cursor CURSOR FOR
-- Наполнить курсор вашей логикой
-- * ЗДЕСЬ ЗАМЕНИТЬ НА ВАШ КОД *
SELECT name
FROM MASTER.dbo.sysdatabases
WHERE name NOT IN ('master','model','msdb','tempdb')
-- Открыть курсор
OPEN db_cursor
-- 3 - Извлечь следующую запись из курсора
FETCH NEXT FROM db_cursor INTO @name
-- Проверить состояние курсора
WHILE @@FETCH_STATUS = 0
BEGIN
-- 4 - Начало настраиваемой бизнес-логики
-- * ЗДЕСЬ ЗАМЕНИТЬ НА ВАШ КОД *
SET @fileName = @path + @name + '_' + @fileDate + '.BAK'
BACKUP DATABASE @name TO DISK = @fileName
-- 5 - Извлечь следующую запись из курсора
FETCH NEXT FROM db_cursor INTO @name
END
-- 6 - Закрыть курсор
CLOSE db_cursor
-- 7 - Освободить ресурсы
DEALLOCATE db_cursor
Объяснение синтаксиса курсора
- Операторы DECLARE — объявление переменных, используемых в блоке кода.
- Операторы SET\SELECT — инициализация переменных конкретными значениями.
- Оператор DECLARE CURSOR — объявление курсора.
Замечание: число переменных должно совпадать с числом столбцов в операторе SELECT для оператора DECLARE CURSOR FOR. Это может быть одна или больше переменных, связанных со столбцами в операторе SELECT. - Оператор OPEN — открывает курсор и наполняет его данными, чтобы начать обработку.
- Операторы FETCH NEXT — Связывает конкретные значения из курсора с переменными в соответствии с оператором DECLARE CURSOR FOR и SELECT
Замечание. Эта логика используется для начального наполнения до оператора WHILE, а затем повторяется на каждом шаге цикла оператора WHILE. - Оператор WHILE — условие для начала и продолжения процесса обработки.
- Операторы BEGIN. END — начало и конец блока кода.
При обработке данных может использоваться множество операторов BEGIN. END. - Обработка данных — в нашем примере логика заключается в создании резервной копии базы данных по указанному пути и имени файла, но это могут быть любые операторы DML или административные задачи.
- Оператор CLOSE — Освобождает текущие данные и связанные с ними блокировки, но оставляет возможность повторно открыть курсор.
- Оператор DEALLOCATE — уничтожает курсор.
Зачем использовать курсоры в SQL Server
- Итерационный обход данных по одной строке за раз.
- Выполнение процесса в последовательной манере, подобной созданию резервных копий баз данных.
- Обновление данных во множестве таблиц для заданной учетной записи.
- Корректировка данных с помощью предварительно определенного набора данных в качестве записей курсора.
Когда использовать курсоры
- OLTP (оперативная обработка транзакций) — в большинстве сред OLTP логика на основе множества строк (INSERT, UPDATE или DELETE) имеет наибольшее предпочтение для коротких транзакций. Наша команда выполняла стороннее приложение, которое использовало курсоры для любой обработки, что вызывало проблемы, но это случалось нечасто. Обычно логики на основе множеств более чем достаточно, и курсоры редко могут понадобиться.
- Отчеты — для проектирования отчетов курсоры обычно не нужны. Однако наша команда столкнулась с требованиями к отчетности, когда не существовало ссылочной целостности в используемой базе данных, и было необходимо использовать курсор для корректного вычисления отчетных значений. Мы имели подобный опыт при необходимости агрегировать данные для последующих процессов. Подход на основе курсора было быстро разработать и выполнить в приемлемой манере.
- Последовательная обработка — если вам необходимо выполнить процесс в последовательной манере, курсоры являются работоспособным вариантом.
- Административные задачи — многие административные задачи, подобные резервированию баз данных или проверки согласованности баз данных, требуется выполнять в последовательной манере, которая хорошо вписывается в основанную на курсорах логику. Но существуют и другие системные объекты, которые удовлетворяют эту потребность. В некоторых из этих случаев курсоры используются для завершения процесса.
- Большие наборы данных — при больших наборах данных вы можете столкнуться с одним или несколькими из следующих случаев:
- Логика на основе курсора может не масштабироваться в достаточной мере.
- Операции на основе множеств с большими объемами данных на сервере с минимальным количеством памяти могут привести к тому, что данные будут выгружаться, отнимая много времени и потенциально вызвая конфликты и проблемы с памятью. В этом случае, решение на базе курсора может оказаться приемлемым.
- Некоторые инструменты фактически кэшируют данные в файл, поэтому обработка данных в памяти может и не иметь места.
- Если данные могут быть обработаны на автономном SQL Server, то влияние на производственную среду будет оказываться только на финальной стадии обработки. Все ресурсы автономного сервера могут использоваться для процессов ETL, после чего полученные данные импортируются.
- SSIS поддерживает пакетную обработку наборов данных, которая может решить общую необходимость разбить большой набор данных на более мелкие и справиться с ними лучше, чем построчным методом на базе курсора.
- В зависимости от того, как закодирована логика курсора или SSIS, может иметься возможность перезапуска с точки сбоя на основе контрольных точек, или обработки каждой строки при помощи курсора. Однако при подходе на основе множеств это может оказаться недоступным, пока не будет обработан весь набор данных. В таком случае найти строку, которая вызвала сбой, будет более сложным.
Как избежать курсоров в SQL Server
- Логика на основе множеств
- INSERT или SELECT INTO, или INSERT. SELECT для добавления записей в таблицу за одну транзакцию.
- UPDATE для модификации одной или многих строк в одной транзакции.
- DELETE или TRUNCATE для удаления записей из таблицы.
- Ветвящаяся логика MERGE для вставки, удаления или обновления данных на основе критериев.
Что дальше
- Когда вам нужно принять решение об обработке данных, определите, где вы можете столкнуться с использованием курсоров. Это может иметь место в вашем приложении или в операционных процессах. Существует много способов решить задачу. Использование курсора может оказаться разумной альтернативой в некоторых случаях. Решать вам.
- Если вы сталкиваетесь в проблемами при другом способе кодирования, и необходимо сделать что-то быстро, использование курсора может быть надежной альтернативой. Она может привести к более продолжительной обработке данных, но время написания кода может стать значительно быстрей. Если вам требуется одноразовый процесс или процесс, выполняемый в ночное время, это может помочь.
- Если в вашей среде избегают курсоров, выберите другое надежное решение. Просто убедитесь, что этот процесс не вызовет других проблем. Например, если используется курсор и обрабатываются миллионы строк, не приведет ли это к удалению всех данных из кеша и не спровоцирует ли дальнейшие конфликты? Или при большом наборе данных не будут ли данные сброшены на диск или записаны во временную директорию?
- Оценивая подход на основе курсора по сравнению с другими альтернативами, проведите честное сравнение методов с точки зрения времени, возможности конфликтов и необходимых ресурсов. Надеюсь, что эти факторы приведут вас к правильному способу.
Обратные ссылки
Нет обратных ссылок
Комментарии
Показывать комментарии Как список | Древовидной структурой
Автор не разрешил комментировать эту запись
Курсоры в Mysql.
По долгу службы мне пришлось сталкиваться с курсорами. Хотелось бы рассказать, что это такое и о некоторых особенностях работы с ними. Официальная документация тут — dev.mysql.com/doc/refman/5.1/en/cursors.html Википедия даёт такое определение курсору курсор:
Курсор — ссылка на контекстную область памяти. В некоторых реализациях языка программирования SQL (Oracle, Microsoft SQL Server) — получаемый при выполнении запроса результирующий набор и связанный с ним указатель текущей записи. бы сказал, что курсор — это виртуальная таблица которая представляет собой альтернативное хранилище данных. При этом курсор, позволяет обращаться к своим данным, как к данным обычного массива.
Используются курсоры в хранимых процедурах. Довольно теории давайте рассмотрим пример:
У нас есть база данных (база немного не хорошая, это одна из моих лабораторных работ, но наш препод по базам данных настаивал на такой структуре )
/*данные о банке */
CREATE TABLE `bank` (
`BankId` INTEGER (11) NOT NULL ,
`BankName` VARCHAR (50) COLLATE utf8_bin NOT NULL DEFAULT » ,
`Address` VARCHAR (50) COLLATE utf8_bin NOT NULL DEFAULT » ,
`Phone` VARCHAR (50) COLLATE utf8_bin NOT NULL DEFAULT » ,
PRIMARY KEY (`BankId`)
)ENGINE=InnoDB
CHARACTER SET ‘utf8’ COLLATE ‘utf8_bin’ ;
/*данные о вкладах */
CREATE TABLE `bankdistribution` (
`BankId` INTEGER (11) NOT NULL ,
`Persent` INTEGER (11) DEFAULT NULL ,
`ContributeAmount` DECIMAL (10,0) NOT NULL ,
`ClientId` INTEGER (11) NOT NULL ,
PRIMARY KEY (`BankId`, `ClientId`),
KEY `BankId` (`BankId`),
KEY `ClientId` (`ClientId`),
CONSTRAINT `bankdistribution_fk` FOREIGN KEY (`BankId`) REFERENCES `bank` (`BankId`),
CONSTRAINT `bankdistribution_fk1` FOREIGN KEY (`ClientId`) REFERENCES `client` (`ClientId`)
)ENGINE=InnoDB
/*данные о вкладчиках*/
CREATE TABLE `client` (
`ClientId` INTEGER (3) NOT NULL AUTO_INCREMENT,
`CreditCardId` BIGINT(10) NOT NULL ,
`Surname` VARCHAR (50) COLLATE utf8_bin NOT NULL DEFAULT » ,
`Name` VARCHAR (50) COLLATE utf8_bin NOT NULL DEFAULT » ,
`FirstName` VARCHAR (50) COLLATE utf8_bin NOT NULL DEFAULT » ,
`Phone` VARCHAR (50) COLLATE utf8_bin NOT NULL DEFAULT » ,
`Address` VARCHAR (50) COLLATE utf8_bin NOT NULL DEFAULT » ,
`SafeId` INTEGER (5) NOT NULL ,
PRIMARY KEY (`ClientId`, `CreditCardId`),
KEY `ClientId` (`ClientId`)
)ENGINE=InnoDB
AUTO_INCREMENT=11 CHARACTER SET ‘utf8’ COLLATE ‘utf8_bin’
* This source code was highlighted with Source Code Highlighter .
Допустим нам нужно получать по очереди каждый банк и производить с ним какие то действия, помочь в этом нам мог бы такой вот запрос
Select `bank`.* FROM `bank` LIMIT НОМЕР_НУЖНОЙ_НАМ_ЗАПИСИ,1
Таким образом используя LIMIT НОМЕР_НУЖНОЙ_НАМ_ЗАПИСИ,1 мы извлекаем в цикле из таблицы bank по очереди каждую запись и производим с ней нужные нам действия, при этом увеличивая значение НОМЕР_НУЖНОЙ_НАМ_ЗАПИСИ на 1. Теперь сделаем то же самое но используя курсор
Begin
/* переменные куда мы извлекаем данные */
Declare vBankId integer ;
Declare vBankName VARCHAR (50);
Declare vAddress VARCHAR (50);
Declare vPhone VARCHAR (50);
/* переменная hadler — a*/
Declare done integer default 0;
/*Объявление курсора*/
Declare BankCursor Cursor for Select `bank`.`BankId`,`bank`.`BankName`,`bank`.`Address`,`bank`.`Phone`, FROM `bank` where 1;
/*HANDLER назначение, которого поясним чуть ниже*/
DECLARE CONTINUE HANDLER FOR SQLSTATE ‘02000’ SET done=1;
/* открытие курсора */
Open BankCursor;
/*извлекаем данные */
WHILE done = 0 DO
FETCH BankCursor INTO vBankId,vBankName,vAddress,vPhone;
делаем нужные нам действия
END WHILE ;
/*закрытие курсора */
Close BankCursor;
END ;
* This source code was highlighted with Source Code Highlighter .
Поясним теперь подробнее. Сначала HANDLER, он нужен для обработки исключения — что делать когда данные закончатся ( то есть курсор будет пустым ). Таким образом когда данные закончатся, не с генерируется сообщение об ошибке, а значение переменной done выставиться в 1, изначально done = 0; подробнее об SQLSTATE читаем тут — dev.mysql.com/doc/refman/5.1/en/error-messages-server.html;
Error: 1329 SQLSTATE: 02000 (ER_SP_FETCH_NO_DATA)
Message: No data — zero rows fetched, selected, or processed
SQLSTATE: 02000 срабатывает когда достигнут конец курсора, или когда select или update возвращяет пустую строку.
Следующей строкой мы объявили курсор DECLARE cursor_name CURSOR FOR select_statement;
Открываем курсор Open cursor_name;
Дальше пока не достигаем конец курсора (WHILE done = 0 DO ) извлекаем данные и обрабатываем их.
Перед выходом из хранимой процедуры необходимо курсор закрыть. Close cursor_name;
Вроде ничего сложного. Но с SQLSTATE ‘02000’ связанно много подводных камней.
WHILE done = 0 DO
FETCH BankCursor INTO vBankId,vBankName,vAddress,vPhone;
/* извлечем для банка сумму любого из его вкладов */
Select (ContributeAmount) INTO vContributeAmountSUM FROM bankdistribution where BankId = vBankId limit 1;
делаем какие то действия
END WHILE ;
* This source code was highlighted with Source Code Highlighter .
Всё хорошо, и правильно с точки зрения синтаксиса. Но с логической точки зрения нет. Может случиться так что вкладчики не открыли счетов в каком то банке, тогда для Select (ContributeAmount) INTO vContributeAmountSUM FROM bankdistribution where BankId = vBankId limit 1; сработает SQLSTATE: 02000, переменная done установится в 1, и цикл while закончиться раньше чем мы ожидали. Этого можно избежать поступив следующим образом
WHILE done = 0 DO
FETCH BankCursor INTO vBankId,vBankName,vAddress,vPhone;
/* извлечем для банка сумму любого из его вкладов */
Select Сount(ContributeAmount) INTO vContributeAmountSUM FROM bankdistribution where BankId = vBankId limit 1;
/* проверим действительно ли есть вклады в этом банке */
if (vContributeAmountSUM > 0) then
/* извлечем для банка сумму любого из его вкладов */
Select ContributeAmount INTO vContributeAmountSUM FROM bankdistribution where BankId = vBankId limit 1;
end if ;
делаем какие то действия
END WHILE ;
* This source code was highlighted with Source Code Highlighter .
первым запросом мы проверили а есть ли вклады (если их нет то vContributeAmountSUM == 0 ) и только если таковые имеются мы извлекаем данные.
теперь допустим нам нужно излечь общую сумму на счетах в разных банках у каждого клиента
Declare ClientSummCursor Cursor for Select sum
Declare ClientSummCursor Cursor for Select sum (`bankdistribution`.`ContributeAmount`),`bankdistribution`.`ClientId` FROM `bankdistribution` Inner Join client on (client.ClientId = bankdistribution.`ClientId`) where 1 group by `bankdistribution`.`ClientId`;
Open ClientSummCursor;
WHILE done = 0 DO
FETCH BankCursor INTO vBankId,vBankName,vAddress,vPhone;
/* извлечем для банка сумму любого из его вкладов */
Select Сount(ContributeAmount) INTO vContributeAmountSUM FROM bankdistribution where BankId = vBankId limit 1;
/* проверим действительно ли есть вклады в этом банке */
if (vContributeAmountSUM > 0) then
/* извлечем для банка сумму любого из его вкладов */
Select ContributeAmount INTO vContributeAmountSUM FROM bankdistribution where BankId = vBankId limit 1;
end if ;
/* извлекаем нужные нам данные */
FETCH ClientSummCursor INTO vSum,vClientId;
делаем какие то действия .
END WHILE ;
* This source code was highlighted with Source Code Highlighter .
может возникнуть та же ситуация, когда данные в курсоре ClientSummCursor, закончатся раньше чем данные в BankCursor, сработает SQLSTATE: 02000, переменная done установится в 1, и цикл while закончиться раньше чем мы ожидали. Этого можно избежать поступив следующим образом
Open ClientSummCursor;
WHILE done = 0 DO
FETCH BankCursor INTO vBankId,vBankName,vAddress,vPhone;
/* извлечем для банка сумму любого из его вкладов */
Select Сount(ContributeAmount) INTO vContributeAmountSUM FROM bankdistribution where BankId = vBankId limit 1;
/* проверим действительно ли есть вклады в этом банке */
if (vContributeAmountSUM > 0) then
/* извлечем для банка сумму любого из его вкладов */
Select ContributeAmount INTO vContributeAmountSUM FROM bankdistribution where BankId = vBankId limit 1;
end if ;
/* до извлечения данных из второго курсора запомним состояние sqlstate */
SET old_status = done;
/* извлекаем нужные нам данные */
FETCH ClientSummCursor INTO vSum,vClientId;
/* проверяем были ли извлечены данные , не стработал ли sqlstate 0200 */
if (done = 0 ) then
делаем какие то действия .
end if ;
/* перед окончанием while восттановим значение переменной done */
set done = old_status;
END WHILE ;
* This source code was highlighted with Source Code Highlighter .
Всем дочитавшим до этого места спасибо, надеюсь это статься покажется кому то полезной.