Курсоры в встроенном SQL. Создание и использование курсоров в субд ms sql server Использование курсоров в sql
Я получил целый ряд комментариев. В одном из них читатель попросил меня уделить больше внимания курсорам, одному из важных элементов хранимых процедур.
Так как курсоры являются частью хранимой процедуры, то в этой статье мы еще детальнее рассмотрим ХП. В частности, как извлечь из ХП набор данных.
Что такое курсор?
Курсор не может использоваться в MySQL сам по себе. Он является важным компонентом хранимых процедур. Я бы сравнил курсор с «указателем » в C / C + + или итератором в PHP-операторе foreach .
С помощью курсора мы можем перебрать набор данных и обработать каждую запись в соответствии с определенными задачами.
Такая операция по обработке записи может быть также исполнена на PHP-уровне, что значительно уменьшает объем передаваемых на PHP-уровень данных, так как мы можем просто вернуть обработанный сводный / статистический результат обратно (тем самым устраняя процесс обработки select – foreach на стороне клиента).
Поскольку курсор реализуется в хранимой процедуре, он имеет все преимущества (и недостатки), присущие ХП (контроль доступа, пре-компиляция, трудность отладки и т.д.)
Официальную документацию по курсорам вы можете найти здесь . В ней описаны четыре команды, связанные с объявлением курсора, открытием, закрытием и извлечением. Как уже упоминалось, мы также затронем некоторых другие операторы хранимых процедур. Давайте приступим.
Пример практического применения
На моем персональном сайте есть страница с результатами игр моей любимой команды НБА: Лейкерс .
Структура таблицы этой страницы довольно проста:
Рис 1. Структура таблицы результатов игр Лейкерс
Я заполняю эту таблицу с 2008 года. Некоторые из последних записей с результатами игр Лейкерс в сезоне 2013-14 приведены ниже:
Рис. 2. Данные таблицы результатов игр Лейкерс (частичные) в сезоне 2013-2014
(Я использую MySQL Workbench в качестве GUI-инструмента для управления базой данных MySQL. Вы можете использовать другой инструмент по своему выбору).
Что ж, должен признать, что баскетболисты Лейкерс в последнее время играют не очень здорово. 6 поражений подряд по состоянию на 15 января. Я определил эти «6 поражений подряд », посчитав вручную, сколько матчей подряд, начиная с текущей даты (и вниз к более ранним играм) имеют в колонке winlose значение «L » (поражение).
Это, конечно, не невыполнимая задача, однако если условия усложнятся, и таблица данных будет намного больше, то это займет больше времени, и вероятность ошибки также увеличивается.
Можем ли мы сделать то же самое с помощью одного оператора SQL? Я не являюсь экспертом SQL, потому не смог придумать, как достичь нужного результата («6 поражений подряд ») через один оператор SQL. Мнения гуру будут для меня очень ценными — оставьте их в комментариях ниже.
Можем ли мы сделать это через PHP? Да, конечно. Мы можем получить данные по играм (конкретно, столбец winlos ) этого сезона и перебрать записи для вычисления длительности текущей серии побед / поражений подряд.
Но чтобы сделать это, нам придется охватить все данные за этот год и большая часть данных будет для нас бесполезна (не слишком вероятно, что какая-то команда будет иметь серию длиннее, чем 20+ игр подряд в регулярном сезоне, который состоит из 82 матчей).
Тем не менее, мы не знаем наверняка, сколько записей должно быть извлечено в PHP для определения серии. Так что нам не обойтись без напрасного извлечения ненужных данных. И, наконец, если текущее количество выигрышей /поражений подряд это единственное, что мы хотим узнать из этой таблицы, зачем нам тогда извлекать все строки данных?
Можем ли мы сделать это другим способом? Да, это возможно. Например, мы можем создать резервную таблицу, специально предназначенную для хранения текущего значения количества побед /поражений подряд.
Добавление каждой новой записи будет автоматически обновлять и эту таблицу. Но это слишком громоздкий и чреватый ошибками способ.
Так как же можно сделать это лучше?
Использование курсора в хранимой процедуре
Как вы могли догадаться из названия нынешней статьи, лучшей альтернативой (на мой взгляд) для решения этой проблемы является использование курсора в хранимой процедуре.
Давайте создадим в MySQL Workbench первую ХП:
DELIMITER $$ CREATE DEFINER=`root`@`localhost` PROCEDURE `streak`(in cur_year int, out longeststreak int, out status char(1)) BEGIN declare current_win char(1); declare current_streak int; declare current_status char (1); declare cur cursor for select winlose from lakers where year=cur_year and winlose<>"" order by id desc; set current_streak=0; open cur; fetch cur into current_win; set current_streak = current_streak +1; start_loop: loop fetch cur into current_status; if current_status <> current_win then leave start_loop; else set current_streak=current_streak+1; end if; end loop; close cur; select current_streak into longeststreak; select current_win into `status`; END
В этой ХП у нас есть один входящий параметр и два исходящих. Это определяет подпись ХП.
В теле ХП мы также объявили несколько локальных переменных для серии результатов (выигрышей или проигрышей, current_win ), текущей серии и текущего статуса выигрыш /проигрыш конкретного матча:
Эта строка является объявлением курсора. Мы объявили курсор с именем cur и набор данных, связанных с этим курсором, который является статусом победа /поражение для тех матчей (значение столбца winlose может быть либо «W », либо «L », но не пустое) в конкретном году, которые упорядочены по идентификатору id (последние сыгранные игры будут иметь более высокий ID) в порядке убывания.
Хотя это не видно наглядно, но мы можем себе представить, что этот набор данных будет содержать последовательность значений «L » и «W ». На основании данных, приведенных на рисунке 2, она должна быть следующей: «LLLLLLWLL… » (6 значений «L », 1 «W » и т.д.)
Для расчета количества побед / поражений подряд мы начинаем с последнего (и первого в приведенном наборе данных) матча. Когда курсор открыт, он всегда начинается с первой записи в соответствующем наборе данных.
После того, как первые данные загружены, курсор перемещается к следующей записи. Таким образом, поведение курсора похоже на очередь, перебирающую набор данных по системе FIFO (First In First Out). Это именно то, что нам нужно.
После получения текущего статуса победа / поражение и количества последовательных одинаковых элементов в наборе, мы продолжаем обрабатывать по циклу (перебирать) оставшуюся часть набора данных. В каждой итерации цикла курсор будет «переходить » на следующую запись, пока мы не разорвем цикл или пока все записи не будут перебраны.
Если статус следующей записи такой же, как у текущего последовательного набора побед / поражений, это означает, что серия продолжается, тогда мы увеличиваем количество последовательных побед (или поражений) еще на 1 и продолжаем перебирать данные.
Если статус отличается, это означает, что серия прервана, и мы можем остановить цикл. Наконец, мы закрываем курсор и оставляем исходные данные. После этого выводится результат.
Чтобы проверить работу этой ХП, мы можем написать короткий PHP-скрипт:
exec("call streak(2013, @longeststreak, @status)"); $res=$cn->query("select @longeststreak, @status")->fetchAll(); var_dump($res); //Dump the output here to get a raw view of the output $win=$res["@status"]="L"?"Loss":"Win"; $streak=$res["@longeststreak"]; echo "Lakers is now $streak consecutive $win.n";
Результат обработки должен выглядеть приблизительно так, как показано на следующем рисунке:
Вывод набора данных из хранимой процедуры
Несколько раз по ходу этой статьи разговор касался того, как вывести набор данных из ХП, которая составляет набор данных из результатов обработки нескольких последовательных вызовов другой ХП.
Пользователь может захотеть получить с помощью ранее созданной нами ХП больше информации, чем просто непрерывная серия побед / поражений за год; например мы можем сформировать таблицу, в которой будут выводиться серии побед /поражений за разные годы:
| YEAR | Win/Lose | Streak |
| 2013 | L | 6 |
| 2012 | L | 4 |
| 2011 | L | 2 |
(В принципе, более полезной информацией будет длительность самой длинной серии побед или поражений в определенном сезоне. Для решения этой задачи можно легко расширить описанную ХП, поэтому я оставлю эту задачу тем читателям, кому это будет интересно. В рамках текущей статьи мы продолжим обработку текущей серии побед / поражений).
Хранимые процедуры MySQL могут возвращать только скалярные значения (целое число, строку, и т.д.), в отличие от операторов select … from … (результаты преобразуются в набор данных). Проблема в том, что таблица, в которой мы хотим получить результаты, в существующей структуре базы данных не существует, она составляется из результатов обработки хранимой процедуры.
Для решения этой проблемы нам нужна временная таблица или, если это возможно и необходимо, резервная таблица. Давайте посмотрим, как мы можем решить имеющуюся задачу с помощью временной таблицы.
Сначала мы создадим вторую ХП, код которой показан ниже:
DELIMITER $$
CREATE DEFINER=`root`@`%` PROCEDURE `yearly_streak`()
begin
declare cur_year, max_year, min_year int;
select max(year), min(year) from lakers into max_year, min_year;
DROP TEMPORARY TABLE IF EXISTS yearly_streak;
CREATE TEMPORARY TABLE yearly_streak (season int, streak int, win char(1));
set cur_year=max_year;
year_loop: loop
if cur_year Несколько существенных замечаний к приведенному выше коду: Чтобы получить результаты, мы создаем еще один небольшой PHP-скрипт, код которого показан ниже: query("call yearly_streak")->fetchAll();
foreach ($res as $r)
{
echo sprintf("In year %d, the longest W/L streaks is %d %sn", $r["season"], $r["streak"], $r["win"]);
} Выведенные на экран результаты будут выглядеть приблизительно следующим образом. )ENGINE=InnoDB )ENGINE=InnoDB Допустим нам нужно получать по очереди каждый банк и производить с ним какие то действия, помочь в этом нам мог бы такой вот запрос * This source code was highlighted with Source Code Highlighter
.
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; Вроде ничего сложного. Но с SQLSTATE "02000" связанно много подводных камней. * This source code was highlighted with Source Code Highlighter
.
* This source code was highlighted with Source Code Highlighter
.
Теперь допустим нам нужно излечь общую сумму на счетах в разных банках у каждого клиента Open
ClientSummCursor; * This source code was highlighted with Source Code Highlighter
.
Может возникнуть та же ситуация, когда данные в курсоре ClientSummCursor, закончатся раньше чем данные в BankCursor, сработает SQLSTATE: 02000, переменная done установится в 1, и цикл while закончиться раньше чем мы ожидали. Этого можно избежать поступив следующим образом * This source code was highlighted with Source Code Highlighter
.
Всем дочитавшим до этого места спасибо, надеюсь это статься покажется кому то полезной. Курсор – это объект, позволяющий по отдельности обрабатывать строки из результирующего набора, возвращенного оператором SELECT. Далее будут рассматриваться курсоры, поддерживаемые в языке Transact- SQL. Это серверные курсоры, существующие как объекты на стороне сервера БД. Бывают также клиентские курсоры, с которыми работают при создании клиентских приложений БД. В литературе отмечается, что построчная обработка набора данных с помощью курсора в подавляющем большинстве случаев выполняется существенно медленнее, чем аналогичные действия, выполняемые средствами SQL для обработки множеств строк. Поэтому курсоры рекомендуется использовать только в тех случаях, когда описание требуемых действий через операции с множествами строк явно неэффективно или вообще невозможно. Работа с курсором обычно включает следующие шаги: Объявление курсора выполняется с помощью оператора DECLARE, формат которого представлен ниже. Надо отметить, что в SQL Server этот оператор поддерживает как синтаксис стандарта ISO SQL (версия стандарта в документации нс уточняется), так и синтаксис, использующий набор расширений языка Transact-SQL CURSOR FOR select_statement Расширенный синтаксис Transact-SQL: DECLARE cursor_name CURSOR FOR select_statement ]][;] Указание ключевого слова GLOBAL означает, что объявляемый курсор доступен в любом пакете заданий, триггере или хранимой процедуре, которые выполняются в рамках текущего соединения с сервером. Курсор неявно освобождается только в случае разрыва соединения. "Локальный" курсор, создаваемый по умолчанию или при явном указании LOCAL, доступен только в пакете заданий, хранимой процедуре или триггере, в которых он был создан. Такой курсор неявно освобождается после завершения выполнения пакета, хранимой процедуры или триггера. Исключение составляет случай, когда курсор передается через выходной параметр (OUTPUT) хранимой процедуры. Тогда курсор освобождается при освобождении всех ссылающихся на него переменных или при выходе из "области видимости". FORWARD_ONLY означает, что "передвигаться" по курсору можно только вперед (доступна только команда FETCH NEXT, см. далее), т.е. каждая запись в курсоре может быть обработана не более одного раза. Если FORWARD ONLY указано без ключевых слов STATIC, KEYSET или DYNAMIC, то курсор работает как курсор DYNAMIC (см. далее). Если не указан ни один из параметров FORWARD_ONLY или SCROLL, а также не указано ни одно из ключевых слов STATIC, KEYSET или DYNAMIC, то по умолчанию задается параметр FORWARD_ONLY. SCROLL означает, что "передвигаться" по курсору можно в любом направлении (в операторе FETCH доступно FIRST, LAST, PRIOR, NEXT, RELATIVE, ABSOLUTE). Параметр SCROLL не может указываться вместе с параметром FAST_FORWARD. Курсоры STATIC, KEYSET и DYNAMIC имеют значение по умолчанию SCROLL. STATIC означает, что курсор необновляемый. Результирующий набор данных такого курсора извлекается из БД и сохраняется в базе для временных объектов tempdb. Изменения таблиц, служащих основой для курсора, после этого отображаться в курсоре не будут. KEYSET – у данного типа курсора набор значений ключей, идентифицирующих отобранные записи, сохраняется во временной таблице. При движении по курсору значения неключевых атрибутов извлекаются из соответствующих таблиц, поэтому изменения в неключевых столбцах будут видны при работе с курсором. Если попавшая в курсор строка к моменту выборки ее оператором FETCH уже удалена из таблицы, служебная переменная @@ FETCH_STATUS вернет значение -2. Строки, добавленные в таблицы после открытия курсора, в курсоре не видны. Если формирующий курсор запрос задействует хотя бы одну таблицу, не имеющую уникального индекса, курсор типа KEYSET преобразуется в тип STATIC. DYNAMIC – самый "затратный" по потребляемым ресурсам тип курсора, отображающий все изменения данных, сделанные в строках результирующего набора, включая вновь вставленные строки. Значения данных, порядок, а также членство строк в каждой выборке могут меняться. С динамическими курсорами нельзя использовать FETCH ABSOLUTE. FAST_FORWARD – самый быстродействующий тип курсора, позволяющий перемещаться от одной строки к другой только "вперед". Это тип курсора, принятый по умолчанию (когда необязательные ключевые слова опущены). Он эквивалентен курсору, объявленному с параметрами FORWARD_ONLY и READ_ONLY. READ_ONLY – определяет курсор "только для чтения": изменения в БД через подобный курсор сделать не удастся. SCROLL_LOCKS означает, что SQL Server блокирует строки по мере их считывания в курсор, что гарантирует возможность их обновления или удаления через курсор данного типа. Курсор, объявленный с ключевым словом OPTIMISTIC, не запрашивает блокировку строк и позволяет изменять данные. Если изменения в базовой таблице произошли после считывания данных в курсор, попытка модификации этих данных через курсор приводит к ошибке. TYPE_WARNING указывает, что при неявном преобразовании курсора из запрашиваемого типа к другому (например, описанное выше преобразование курсора KEYSET в STATIC при отсутствии уникального индекса в таблице), клиенту будет отправлено предупреждение. Select_statement – оператор SELECT, формирующий результирующий набор курсора. Инструкция FOR UPDATE, определяет обновляемые столбцы в курсоре. Если указано OF column_name [, . . . n], то для изменений будут доступны только перечисленные столбцы. Если списка столбцов нет, обновление возможно для всех столбцов, кроме случая объявления курсора с параметром READ_ONLY. Чтобы открыть и заполнить курсор, используется команда OPEN {{ cursor_name} I @cursor_variable) При открытии, курсор может указываться по имени (cursor_name) или через переменную типа CURSOR (@cursor_variable). Параметр GLOBAL указывает, что cursor_name – это глобальный курсор. Для перемещения по набору данных курсора и получения данных в виде значений переменных используется оператор FETCH: FETCH [ {{ cursor_name] I @cursor_variable] Команды, определяющие направление перемещения по курсору, описаны в табл. 10.10. Как уже отмечалось ранее, в зависимости от типа курсора некоторые команды для конкретного курсора могут быть неприменимы. Важно отметить, что если курсор только что был открыт, первое выполнение FETCH NEXT приводит к переходу на первую запись курсора. Таблица 10.10
Навигация по набору данных курсора
Глобальная переменная @@FETCH_STATUS позволяет узнать результат последнего выполнения оператора FETCH: О – действие выполнено успешно; Оператор CLOSE закрывает открытый курсор, освобождая память, использовавшуюся для хранения набора данных. Выборка данных и перемещение по закрытому курсору невозможны – для этого его надо повторно открыть. CLOSE {{ cursor_name}|@cursor_variable } Оператор DEALLOCATE удаляет связь между курсором и его именем или переменной. Если это последнее имя или переменная, ссылающаяся на курсор, сам курсор удаляется и освобождаются все используемые им ресурсы: DEALLOCATE {{ cursor_name] | @cursor_variable) Рассмотрим несложный пример использования курсора. Здесь из таблицы выбираются авторы и названия книг, изданных не ранее 2000 г., после чего данные в цикле выводят операторам SELECT – каждый раз одна запись с собственным заголовком. Дополнительные пояснения даются комментариями в коде: /*объявляем переменные*/ DECLARE @auth varchar(50), @title varchar(50) WHERE >= 2000 /*открываем курсор и "пробегаем" его, выводя автора и название отдельным оператором SELECT*/ FETCH NEXT FROM cursorl INTO @auth, @title WHILE SSFETCH_STATUS = 0 FETCH NEXT FROM cursorl INTO @auth, Stitle /*закрываем курсор и освобождаем его*/ DEALLOCATE cursorl Как отмечалось выше, вместо имени курсора может использоваться переменная типа CURSOR. Ниже приведен аналогичный код, использующий такие переменные: DECLARE Sauth varchar(50), Stitle varchar(50) /*объявляем переменную типа курсор*/ DECLARE Scurl CURSOR DECLARE cursorl CURSOR FAST_FORWARD SELECT Author, Title FROM dbo.Bookl WHERE >= 2000 /*присваиваем переменной типа курсор значение*/ SET Scurl = cursorl WHILE SSFETCH_STATUS = 0 FETCH NEXT FROM Scurl INTO Sauth, Stitle In my T-SQL code I always use set based operations. I have been told these
types of operations are what SQL Server is designed to process and it should be
quicker than serial processing. I know cursors exist but I am not sure how
to use them. Can you provide some cursor examples? Can you give any
guidance on when to use cursors? I assume Microsoft included them in SQL Server for a reason
so they must have a place where they can be used in an efficient manner. In some circle"s cursors are never used, in others they are a last resort and
in other groups they are used regularly. In each of these camps they have
different reasons for their stand on cursor usage. Regardless of your stand
on cursors they probably have a place in particular circumstances and not in others.
So, it boils down to your understanding of the coding technique then your understanding
of the problem at hand to make a decision on whether or not cursor-based processing
is appropriate or not. To get started let"s do the following: Creating a SQL Server cursor is a consistent process, so once you learn the
steps you are easily able to duplicate them with various sets of logic to loop
through data. Let"s walk through the steps: From here, check out the examples below to get started on knowing when to use
SQL Server cursors and how to do so. Here is an example cursor from tip
Simple script to backup all SQL Server
databases where backups are issued in a serial manner: DECLARE @name VARCHAR(50) -- database name
DECLARE @path VARCHAR(256) -- path for backup files
DECLARE @fileName VARCHAR(256) -- filename for backup
DECLARE @fileDate VARCHAR(20) -- used for file name
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 Based on the example above, cursors include these components: Learn more about SQL Server Cursors and alternatives: In the example above backups are issued via a cursor, check out these other tips
that leverage cursor-based logic: The analysis below is intended to serve as insight into various scenarios where
cursor-based logic may or may not be beneficial: ПРИМЕНЯЕТСЯ К:
SQL Server (начиная с 2008)База данных SQL AzureХранилище данных SQL AzureParallel Data Warehouse Определяет такие атрибуты серверного курсора языка Transact-SQL, как свойства просмотра и запрос, используемый для построения результирующего набора, на котором работает курсор. Инструкция DECLARE CURSOR поддерживает как синтаксис стандарта ISO, так и синтаксис, использующий набор расширений языка Transact-SQL. ISO Syntax
DECLARE
cursor_name [ INSENSITIVE ] [ SCROLL
] CURSOR
FOR
select_statement
[ FOR
{ READ
ONLY
| UPDATE
[ OF
column_name [ ,...n
] ] } ]
[;
]
Transact-SQL Extended Syntax
DECLARE
cursor_name CURSOR
[ LOCAL
| GLOBAL
]
[ FORWARD_ONLY | SCROLL
]
[ STATIC
| KEYSET | DYNAMIC | FAST_FORWARD ]
[ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ]
[ TYPE_WARNING ]
FOR
select_statement
[ FOR
UPDATE
[ OF
column_name [ ,...n
] ] ]
[;
] cursor_name
INSENSITIVE SCROLL select_statement
select_statement
конфликт с курсором запрошенного типа. READ ONLY Обновление ] cursor_name
LOCAL GLOBAL FORWARD_ONLY STATIC KEYSET Изменения неключевых значений в базовых таблицах, сделанные владельцем курсора или зафиксированные другими пользователями, отображаются при просмотре курсора владельцем. Изменения, сделанные другими пользователями, не отображаются (изменения не могут быть сделаны с помощью серверного курсора языка Transact-SQL). Если удаляется строка, попытка выборки строк возвращает @@FETCH_STATUS -2. Обновления значений ключа из-за границ курсора аналогично удалению старой строки с последующей вставкой новой строки. Строка с новыми значениями не видна и попытки выборки строки со старыми значениями возвращают @@FETCH_STATUS -2. Обновления видимы сразу, если они сделаны через курсор с помощью предложения WHERE CURRENT OF. DYNAMIC FAST_FORWARD READ_ONLY SCROLL_LOCKS OPTIMISTIC TYPE_WARNING select_statement
SQL Serverнеявно преобразует курсор в другой тип, если предложения в select_statement
конфликт с курсором запрошенного типа. Дополнительные сведения см. в разделе «Неявные преобразования курсора». ДЛЯ обновления ] Инструкция DECLARE CURSOR определяет такие атрибуты серверного курсора языка Transact-SQL, как свойства просмотра и запрос, используемый для построения результирующего набора, на котором работает курсор. Инструкция OPEN заполняет результирующий набор, а оператор FETCH возвращает из него строку. Инструкция CLOSE очищает текущий результирующий набор, связанный с курсором. Инструкция DEALLOCATE освобождает ресурсы, используемые курсором. Первая форма инструкции DECLARE CURSOR использует синтаксис ISO для задания параметров работы курсора. Вторая форма инструкции DECLARE CURSOR использует расширения языка Transact-SQL, позволяющие определять курсоры с помощью таких же типов, как типы, используемые в курсорных функциях API баз данных, таких как ODBC и ADO. Нельзя смешивать две эти формы. Если указать SCROLL или без УЧЕТА ключевые слова перед ключевым словом CURSOR, нельзя использовать ключевые слова между КУРСОРА, а также для select_statement
ключевые слова. При указании ключевые слова между КУРСОРА, а также для select_statement
ключевые слова, нельзя указать SCROLL или INSENSITIVE перед ключевым словом CURSOR. Если при использовании синтаксиса языка Transact-SQL для инструкции DECLARE CURSOR не указываются параметры READ_ONLY, OPTIMISTIC или SCROLL_LOCKS, то принимается следующее значение по умолчанию. Если инструкция SELECT не поддерживает обновления (или недостаточно разрешений, или при доступе к удаленным таблицам, не поддерживающим обновление, и т. п.), то курсору присваивается параметр READ_ONLY. Курсоры STATIC и FAST_FORWARD по умолчанию имеют значение READ_ONLY. Курсоры DYNAMIC и KEYSET по умолчанию имеют значение OPTIMISTIC. Ссылки на курсоры могут производиться только другими инструкциями языка Transact-SQL. Функции API баз данных не могут ссылаться на курсоры. Например, после объявления курсора функции и методы OLE DB, ODBC или ADO не могут ссылаться на его имя. Строки курсора не могут быть выбраны с помощью соответствующих функций и методов API; для этой цели необходимо использовать инструкции FETCH языка Transact-SQL. Приведенные ниже хранимые процедуры могут быть использованы для определения свойств курсора после его объявления. Переменные могут использоваться как часть select_statement
, в котором объявлен курсор. Значения переменных курсора после его объявления не изменяются. По умолчанию разрешения DECLARE CURSOR предоставляются всем пользователям, имеющим разрешение SELECT для используемых курсором представлений, таблиц и столбцов. Нельзя использовать курсоры или триггеры в таблице с кластеризованным индексом columnstore. Это ограничение не применяется к некластеризованные индексы; можно использовать курсоры и триггеры в таблице с некластеризованным индексом columnstore. Результирующий набор, создаваемый при открытии данного курсора, включает в себя все строки и столбцы таблицы. Этот курсор можно обновлять, все обновления и удаления представлены в выборке для этого курсора. FETCH``NEXT доступна только выборка, поскольку SCROLL не был указан параметр. DECLARE
vend_cursor CURSOR
FOR
SELECT
* FROM
Purchasing.Vendor
OPEN
vend_cursor
FETCH
NEXT
FROM
vend_cursor;
В следующем примере вложенные курсоры используются для вывода сложного отчета. Для каждого поставщика объявляется внутренний курсор. SET
NOCOUNT ON
;
DECLARE
@vendor_id int
, @vendor_name nvarchar
(50
),
@message varchar
(80
), @product nvarchar
(50
);
PRINT "-------- Vendor Products Report --------";
DECLARE
vendor_cursor CURSOR
FOR
SELECT
VendorID, Name
FROM
Purchasing.Vendor
WHERE
PreferredVendorStatus = 1
ORDER
BY
VendorID;
OPEN vendor_cursor
FETCH NEXT FROM vendor_cursor
INTO @vendor_id, @vendor_name
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT " "
SELECT
@message = "----- Products From Vendor: "
+
@vendor_name
PRINT @message
-- Declare an inner cursor based
-- on vendor_id from the outer cursor.
DECLARE
product_cursor CURSOR
FOR
SELECT
v.Name
FROM
Purchasing.ProductVendor pv, Production.Product v
WHERE
pv.ProductID = v.ProductID AND
pv.VendorID = @vendor_id -- Variable value from the outer cursor
OPEN
product_cursor
FETCH
NEXT
FROM
product_cursor INTO
@product
IF
@@FETCH_STATUS <> 0
PRINT " <
Курсор - ссылка на контекстную область памяти. В некоторых реализациях языка программирования SQL (Oracle, Microsoft SQL Server) - получаемый при выполнении запроса результирующий набор и связанный с ним указатель текущей записи. бы сказал, что курсор - это виртуальная таблица которая представляет собой альтернативное хранилище данных. При этом курсор, позволяет обращаться к своим данным, как к данным обычного массива.
Используются курсоры в хранимых процедурах. Довольно теории давайте рассмотрим пример:
У нас есть база данных (база немного не хорошая, это одна из моих лабораторных работ, но наш препод по базам данных настаивал на такой структуре) /*данные о банке */
CREATE
TABLE
`bank` (
`BankName` VARCHAR
(50) COLLATE
utf8_bin NOT
NULL
DEFAULT
""
,
PRIMARY
KEY
(`BankId`)
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`)
AUTO_INCREMENT=11 CHARACTER
SET
"utf8"
COLLATE
"utf8_bin"
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
делаем нужные нам действия
END
WHILE
;
/*закрытие курсора */
Close
BankCursor;
END
;
Открываем курсор Open cursor_name;
Дальше пока не достигаем конец курсора (WHILE done = 0 DO) извлекаем данные и обрабатываем их.
Перед выходом из хранимой процедуры необходимо курсор закрыть. Close cursor_name; WHILE
done = 0 DO
FETCH
BankCursor INTO
vBankId,vBankName,vAddress,vPhone;
Select
(ContributeAmount) INTO
vContributeAmountSUM FROM
bankdistribution where
BankId = vBankId limit
1;
делаем какие то действия
END
WHILE
;
Всё хорошо, и правильно с точки зрения синтаксиса. Но с логической точки зрения нет. Может случиться так что вкладчики не открыли счетов в каком то банке, тогда для 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;
/* извлечем для банка сумму любого из его вкладов */
if
(vContributeAmountSUM > 0) then
/* извлечем для банка сумму любого из его вкладов */
end
if
;
делаем какие то действия
END
WHILE
;
первым запросом мы проверили а есть ли вклады (если их нет то 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`;
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
;
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
;
Solution
How to Create a SQL Server Cursor
Example SQL Server Cursor
SQL Server Cursor Components
Recommended Reading
Additional SQL Server Cursor Examples
SQL Server Cursor Analysis
Next Steps
cursor_name
tempdb
; таким образом, изменения базовых таблиц не отражаются в возвращенных выборками этого курсора данных, и этот курсор не допускает изменения. При использовании синтаксиса ISO, если не указан параметр INSENSITIVE, зафиксированные обновления и удаления, сделанные в базовых таблицах, отображаются в последующих выборках.
Указывает, что доступны все параметры выборки (FIRST, LAST, PRIOR, NEXT, RELATIVE, ABSOLUTE). Если в инструкции DECLARE CURSOR стандарта ISO не указан параметр SCROLL, то поддерживается только параметр выборки NEXT. Параметр SCROLL не может указываться вместе с параметром FAST_FORWARD.
Стандартная инструкция SELECT, которая определяет результирующий набор курсора. Ключевые слова FOR BROWSE и INTO недопустимы в select_statement
объявление курсора.
column_name
[,
.. .n
] указан, только перечисленные столбцы позволяют вносить изменения. Если инструкция UPDATE используется без списка столбцов, то обновление возможно для всех столбцов.
Имя Transact-SQL определенного серверного курсора. cursor_name
должны соответствовать правилам для идентификаторов.
Указывает, что курсор является локальным по отношению к пакету, хранимой процедуре или триггеру, в котором он был создан. Имя курсора допустимо только внутри этой области. На курсор могут ссылаться локальные переменные пакета, хранимые процедуры, триггеры или выходной параметр хранимой процедуры. Параметр OUTPUT используется для передачи локального курсора вызывающему его пакету, хранимой процедуре или триггеру, который затем может присвоить параметр переменной курсора с целью последующего обращения к курсору после завершения хранимой процедуры. Курсор неявно освобождается после завершения выполнения пакета, хранимой процедуры или триггера, за исключением случая, когда курсор был передан параметру OUTPUT. Если курсор был передан параметру OUTPUT, то курсор освобождается при освобождении всех ссылающихся на него переменных или при выходе из области видимости.
Указывает, что курсор является глобальным по отношению к соединению. Имя курсора может использоваться любой хранимой процедурой или пакетом, которые выполняются в соединении. Курсор неявно освобождается только в случае разрыва соединения.
Указывает, что курсор может просматриваться только от первой строки к последней. Поддерживается только параметр выборки FETCH NEXT. Если параметр FORWARD_ONLY указан без ключевых слов STATIC, KEYSET или DYNAMIC, то курсор работает как курсор DYNAMIC. Если не указаны ни аргумент FORWARD_ONLY, ни аргумент SCROLL, по умолчанию используется аргумент FORWARD_ONLY, если нет ключевых слов STATIC, KEYSET или DYNAMIC. Курсоры STATIC, KEYSET и DYNAMIC имеют значение по умолчанию SCROLL. В отличие от таких интерфейсов API баз данных, как ODBC и ADO, режим FORWARD_ONLY поддерживается следующими курсорами языка Transact-SQL: STATIC, KEYSET и DYNAMIC.
Определяет курсор, который создает временную копию данных для использования курсором. Все запросы к курсору обращаются к указанной временной таблице в tempdb
; таким образом, изменения базовых таблиц не отражаются в возвращенных выборками этого курсора данных, и этот курсор не допускает изменения.
Указывает, что членство или порядок строк в курсоре неизменны при его открытии. Набор ключей, однозначно определяющих строки, встроен в таблицу в tempdb
называется ключей
.
Определяет курсор, отображающий все изменения данных, сделанные в строках результирующего набора при просмотре этого курсора. Значения данных, порядок и членство строк в каждой выборке могут меняться. Параметр выборки ABSOLUTE динамическими курсорами не поддерживается.
Указывает курсор FORWARD_ONLY, READ_ONLY, для которого включена оптимизация производительности. Параметр FAST_FORWARD не может указываться вместе с параметрами SCROLL или FOR_UPDATE.
Предотвращает изменения, сделанные через этот курсор. Предложение WHERE CURRENT OF не может иметь ссылку на курсор в инструкции UPDATE или DELETE. Этот параметр имеет преимущество над установленной по умолчанию возможностью обновления курсора.
Указывает, что позиционированные обновления или удаления, осуществляемые с помощью курсора, гарантированно будут выполнены успешно. SQL Server блокирует строки по мере их считывания в курсор для обеспечения доступности этих строк для последующих изменений. Параметр SCROLL_LOCKS не может указываться вместе с параметром FAST_FORWARD или STATIC.
Указывает, что позиционированные обновления или удаления, осуществляемые с помощью курсора, не будут выполнены, если с момента считывания в курсор строка была обновлена. SQL Server не блокирует строки по мере их считывания в курсор. Вместо этого используются сравнения timestamp
значения столбца или контрольных сумм, если в таблице нет timestamp
столбец, чтобы определить, изменялась ли строка после считывания в курсор. Если строка была изменена, то попытки позиционированного обновления или удаления будут безрезультатными. Параметр OPTIMISTIC не может указываться вместе с параметром FAST_FORWARD.
Указывает, что клиенту будет отправлено предупреждение, если курсор неявно будет преобразован из одного запрашиваемого типа в другой.
Стандартная инструкция SELECT, которая определяет результирующий набор курсора. Ключевые слова COMPUTE, COMPUTE BY, FOR BROWSE и INTO недопустимы в select_statement
объявление курсора.
Определяет обновляемые столбцы в курсоре. If OF column_name
[,
... n
] предоставляется, только перечисленные столбцы позволяют вносить изменения. Если инструкция UPDATE используется без списка столбцов, то обновление возможно для всех столбцов, за исключением случая, когда был указан параметр параллелизма READ_ONLY. A. Использование простого курсора и синтаксиса
Б. Использование вложенных курсоров для вывода отчета
