SQL-Ex blog
В языке SQL курсоры служат указателями, которые позволяют языку написания приложений обрабатывать результаты запросов построчно. В данной статье вкратце объясняется концепция и показывается, как объявить курсор, открыть, извлечь из него данные, а затем закрыть.
Курсоры SQL
Данные в реляционной базе данных рассматриваются как множества. Как следствие, результаты запроса возвращаются операторами SELECT в виде результирующих наборов. Эти результирующие наборы есть не что иное, как одна или более строк и столбцов, извлеченных из одной или более таблиц. Вы можете пролистать результирующие наборы, чтобы вытащить необходимую вам информацию. Возвращаемые элементы данных используются языками программирования типа Java или любыми другими в конкретных целях приложения. Однако здесь имеется проблема, обусловленная несоответствием структур модели базы данных и моделью языка программирования.
- столбцы (или атрибуты) и их типы данных;
- строки (записи или кортежи);
- таблицы (коллекция записей).
Таким образом, курсор служит указателем, который позволяет языку программирования обрабатывать результаты запроса по одной записи за раз. Курсор может перемещаться по всем строкам результата запроса, позиционируясь всякий раз на отдельной строке. Рассмотрим следующий запрос SQL:
Вышеприведенный оператор вернет информацию о тех сотрудниках, чей день рождения попадает на текущие день и месяц. Результат может содержать много строк, но базовый язык приложения может обработать одновременно только одну строку. В результате, курсор объявляется как встроенный в соответствующий язык программирования оператор SQL. Затем курсор открывается подобно файлу, и извлекается отдельная строка из результата запроса. Остальные строки извлекаются последовательно до тех пор, пока курсор не будет закрыт.
Объявление курсора
Курсоры объявляются во многом подобно переменной. Курсору дается имя, имеются операторы для открытия курсора, извлечения результатов запроса и, наконец, закрытия курсора. Отметим, что различные реализации SQL поддерживают использование курсоров по-разному. Но здесь имеется общее соглашение как должен записываться курсор.
Мы должны использовать несколько операторов SQL для полной реализации функциональности курсора, т.к. одно лишь объявление курсора недостаточно для извлечения данных из базы данных. Вот 4 основных шага для работы с курсором:
DECLARE CURSOR: Объявление начинается с задания имени курсору и присвоения ему выражения запроса, которое будет вызываться при открытии курсора.
OPEN: Оператор open выполняет выражение запроса курсора и подготавливает результат запроса для последующего обхода (FETCH).
FETCH: Извлекает значения данных в переменные, которые затем могут передаваться базовому языку программирования или другим встроенным операторам SQL.
CLOSE: Курсор закрывается, и больше не может извлекать результаты запроса.
Основной частью объявления курсора является:
Необязательная часть, такая как [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.
Итак, простой курсор может быть объявлен следующим образом:
Курсоры в MySQL
В MySQL имеется два основных типа курсоров: только на чтение (read-only) и только вперед (forward-only). Эти курсоры могут использоваться в хранимых процедурах MySQL. Они помогают нам итерационно обходить результаты запроса по одной строке за раз и извлекать значения в переменные для последующей обработки. Имеется возможность объявлять более одного курсора и вкладывать их в циклы. Заметим, что курсоры являются курсорами только на чтение, поскольку они для итераций используют временные таблицы. Обычно курсор выполняет запрос при его открытии.
Одной из проблем, связанных с курсорами в MySQL, является их отрицательное влияние на производительность из-за дополнительных операций ввода/вывода, которые они производят. Это особенно справедливо для больших типов данных, таких как BLOB и TEXT. Т.к. курсоры работают с временными таблицами, эти типы не поддерживаются в таблицах в памяти (in-memory). Следовательно, при работе с этими типами данных MySQL должна создавать временные таблицы на диске, что требует множества операций ввода/вывода, что особенно плохо для таких медленных устройств, которыми являются диски. Это основная причина низкой производительности курсоров.
MySQL также не поддерживает курсоров на стороне клиента, однако клиентское API может эмулировать их при необходимости. Но тогда это не сильно отличается от извлечения результата в массив на языке программирования типа Java, и манипулирования этим массивом вместо использования курсора.
Вот пример написания курсора в MySQL:
Вызвать хранимую процедуру можно так:
Процедура извлекает те строки из таблицы с именем employee, для которых дата рождения совпадает с текущим днем и месяцем, в курсор с именем mycursor и просто печатает их с помощью оператора SELECT.
Курсоры в Mysql.
/*данные о банке */
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 .
Допустим нам нужно получать по очереди каждый банк и производить с ним какие то действия, помочь в этом нам мог бы такой вот запрос
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 .
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 .
Всем дочитавшим до этого места спасибо, надеюсь это статься покажется кому то полезной.
Курсоры: принципы работы
Запрос к реляционной базе данных обычно возвращает несколько рядов (записей) данных, но приложение за один раз обрабатывает лишь одну запись . Даже если оно имеет дело одновременно с несколькими рядами (например, выводит данные в форме электронных таблиц), их количество по-прежнему ограничено. Кроме того, при модификации, удалении или добавлении данных рабочей единицей является ряд. В этой ситуации на первый план выступает концепция курсора , и в таком контексте курсор – указатель на ряд.
Курсор в 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 . В курсоре видно самое последнее состояние данных.
Статические курсоры обеспечивают стабильный взгляд на данные. Они хороши для систем «складирования» информации: приложений для систем отчетности или для статистических и аналитических целей. Кроме того, статический курсор лучше других справляется с выборкой большого количества данных. Напротив, в системах электронных покупок или резервирования билетов необходимо динамическое восприятие обновляемой информации по мере внесения изменений. В таких случаях используется динамический курсор . В этих приложениях объем передаваемых данных, как правило, невелик, а доступ к ним осуществляется на уровне рядов (отдельных записей). Групповой доступ встречается очень редко.
SQL-Ex blog
В своем коде T-SQL я всегда использую теоретико-множественные операции. Мне говорили, что SQL Server ориентирован на обработку операций этого типа, и они будут выполняться быстрей, чем последовательная обработка. Я знаю, что существуют курсоры, но я не знаю, как их использовать. Можете дать мне несколько примеров курсора? Можете указать мне, когда использовать курсоры? Я предполагаю, что Microsoft включил их в SQL Server намеренно, поэтому они должны иметь свою нишу, где могут быть использованы эффективно.
Решение
- Рассмотрим пример курсора
- Разобьем курсор на компоненты
- Дадим дополнительные примеры курсора
- Проанализируем аргументы за и против использования курсоров
Пример курсора SQL Server
В качестве примера курсора возьмем простой скрипт для создания резервных копий всех баз данных SQL Server, в котором бэкапы создаются в последовательном стиле:
Что такое курсор в SQL Server
Курсор SQL Server — это набор логики T-SQL, повторяемой в цикле для заданного количества строк по одной за раз. В курсоре может может выполняться обновление одной строки за раз или административный процесс, подобный последовательному созданию резервных копий баз данных. Курсоры используются при разработке, в административных процессах и процессах ETL.