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:
Реализация курсора в базе данных напоминает класс Java, имеющий набор данных и методы для их обработки. При этом sql cursor использует данные как обычный массив. Курсоры могут быть использованы в триггерах, хранимых процедурах и функциях.
В соответствии со стандартом SQL при работе с курсорами выполняются следующие основные действия:
В разных реализациях определение курсора может иметь некоторые отличия. Так, например, иногда необходимо явным образом освободить выделяемую для курсора память. После освобождения курсора ассоциированная с ним память также освобождается. При этом становится возможным повторное использование имени курсора. В других реализациях при закрытии курсора освобождение памяти происходит неявным образом.
В отдельных случаях без применения курсора не обойтись. Однако по возможности следует избегать использование курсора и работать со стандартными командами обработки данных: SELECT, UPDATE, INSERT, DELETE. Это связано с тем, что курсоры не позволяют проводить операции изменения над всем объемом данных и скорость выполнения операций обработки данных посредством курсора заметно ниже, чем у стандартных средств SQL.
Если программа может изменить данные, загруженные в cursor, то он называется модифицируемым. Говоря о курсорах, не следует забывать об изолированности транзакций. Один пользователь изменяет запись при помощи курсора, в то время, как другой пользователь читает эту запись при помощи собственного курсора. Более того, он может изменить ту же запись, что обуславливает необходимость соблюдения целостности данных.
Курсоры должны быть объявлены до их использования. В стандарте SQL для создания курсора используется следующий синтаксис:
Declare cursor_name cursor for select_statement ]}]
В данном выражении объявляется курсор declare cursor c именем "cursor_name".
INSENSITIVE создается статический курсор, который не разрешает вносить изменения. Кроме того, не отображаются изменения, сделанные другими пользователями. Если ключевое слово INSENSITIVE отсутствует, создается динамический курсор.
При использовании ключевого слова SCROLL созданный курсор можно прокручивать в любом направлении, что позволяет применять любые команды выборки. Если этот аргумент опускается, то cursor окажется последовательным, т.е. его просмотр будет возможен только в одном направлении – от начала к концу.
Выражение select_statement указывает на конструкцию чтения информации типа select ... from ... . Оно не должно содержать оператор into , поскольку cursor имеет свой оператор fetch для заполнения переменных данными курсора.
При указании аргумента FOR READ_ONLY будет создан курсор "только для чтения", и никакие модификации данных не разрешаются. В качестве курсора "только для чтения" может быть объявлен динамический курсор, что позволит отображать изменения, сделанные другим пользователем.
Создание курсора с аргументом FOR UPDATE позволяет выполнять в курсоре изменение данных либо в указанных столбцах, либо, при отсутствии аргумента OF имя_столбца , во всех столбцах.
В подпрограмме можно объявить несколько курсоров. Но каждый курсор должен иметь уникальное имя. Для открытия курсора необходимо использовать оператор open , который открывает ранее объявленный курсор:
В SQL определен следующий синтаксис открытия курсора "cursor open"" :
Open cursor_name;
Синтаксис чтения данных из курсора в некоторые переменные имеет следующий вид:
Fetch cursor_name into var_name [, var_name] ...;
Оператор fetch выбирает данные открытого курсора в переменные, расположенные после into и перемещает указатель курсора в следующую позицию.
Оператор close закрывает cursor. Если оператор явно не указан, то курсор закрывается автоматически при закрытии соответствующего программного блока.
Close cursor_name;
После закрытия курсор становится недоступным. При закрытии снимаются все блокировки, установленные в процессе работы курсора. Закрывать можно только открытые курсоры. Закрытый, но не освобожденный курсор может быть повторно открыт. Не допускается закрывать неоткрытый курсор.
В каждой СУБД имеются свои особенности использования курсора.
В PL/SQL имеется четыре курсорных атрибута %FOUND , %NOTFOUND , %ISOPEN и %ROWCOUNT . Атрибуты курсора объявляются подобно операторам %TYPE и %ROWTYPE, справа от имени курсора.
Атрибут %NOTFOUND является полной противоположностью %FOUND.
Атрибут %ISOPEN указывает только на то, открыт ли курсор или нет.
Атрибут %ROWCOUNT является числовым атрибутом, возвращающим число строк, считанных курсором на определенный момент времени.
Declare v_id managers.id %TYPE; v_name managers.name%TYPE; v_comm managers.comm%TYPE; crs cursor for select id, name, sum(comm) as comm from managers where data between "2014-11-01" and "2014-11-30" group by id, name; begin open crs; loop EXIT WHEN crs%NOTFOUND; FETCH crs into v_id, v_name, v_comm; insert into bonus(id, name, comm) values (crs.id, crs.name, crs.comm); end loop; commit; close crs; end;
Курсоры, используемые в MSSQL, могут быть последовательные и прокручиваемые. Последовательные позволяют выбирать данные только в одном направлении – от начала к концу. Прокручиваемые курсоры допускают перемещение в обоих направлениях и позволяют выполнять переход к произвольной строке результирующего набора курсора.
SQL Server поддерживает курсоры статические, динамические, последовательные и управляемые набором ключей.
В схеме со статическим курсором информация хранится в виде моментального снимка по состоянию на некоторый момент времени. Поэтому изменения, внесенные в базу данных другим пользователем, не видны. На время открытия курсора сервер устанавливает блокировку на все строки, включенные в его полный результирующий набор. Статический курсор не изменяется после создания и всегда отображает тот набор данных, который существовал на момент его открытия. Если другие пользователи изменят в исходной таблице включенные в курсор данные, это никак не повлияет на статический курсор. В статический курсор внести изменения невозможно, поэтому он всегда открывается в режиме "только для чтения".
Динамический курсор требует дополнительных сетевых затрат и программных ресурсов. При использовании динамических курсоров полная копия данных не создается, а выполняется выборка из исходных таблиц только при обращении пользователя к тем или иным данным. На время выборки сервер блокирует строки, а все изменения, вносимые пользователем в полный результирующий набор курсора, будут видны в курсоре. Однако после выборки данных курсором внесенные изменения другим пользователем уже не отразятся в курсоре.
Курсор, управляемый набором ключей, по свойствам располагается между статическим и динамическим. Записи идентифицируются на момент выборки, и, таким образом, отслеживаются изменения. Такой тип курсора полезен при реализации прокрутки назад. В этом случае добавления и удаления данных не видны, пока информация не обновится, а курсор выбирает новую версию записи, если в нее были внесены изменения.
Статические курсоры лучше всего использовать для систем обработки информации, т.е. для систем отчетности или для статистических и аналитических целей. Статический курсор лучше справляется с выборкой большого количества данных. В системах электронных покупок или резервирования объектов (мест, билетов) необходимо динамическое восприятие обновляемой информации по мере внесения изменений. В таких случаях используется динамический курсор. В этих приложениях объем передаваемых данных, как правило, невелик, а доступ к ним осуществляется на уровне отдельных записей.
Последовательные курсоры не позволяют выполнять выборку данных в обратном направлении, только от начала к концу курсора. Последовательный курсор не хранит набор всех строк с данными. Они считываются из базы данных, как только выполняется выборка в курсоре, что позволяет динамически отражать все изменения вносимые пользователями в базу данных с помощью команд INSERT, UPDATE, DELETE. Курсор читает самое последнее состояние данных.
Declare cursor_name cursor for SELECT_оператор ]]
При использовании ключевого слова LOCAL будет создан локальный курсор, который виден только в пределах блока, триггера, хранимой процедуры или пользовательской функции. Ключевое слово GLOBAL , определяет глобальный курсор, который существует до закрытия текущего соединения.
Оператор FORWARD_ONLY определяет последовательный курсор, позволяющий осуществлять выборку данных только в направлении от первой строки к последней. При использовании оператора SCROLL создается прокручиваемый курсор, который обеспечивает обращение к данным в любом порядке и в любом направлении.
Тип курсора определяют операторы:
Если для курсора READ_ONLY указать аргумент FAST_FORWARD , то созданный курсор будет оптимизирован для быстрого доступа к данным. Этот аргумент не может быть использован совместно с аргументами FORWARD_ONLY и OPTIMISTIC .
Если курсор создан с указанием оператора OPTIMISTIC , то запрещается изменение и удаление строк, которые были изменены после открытия курсора.
При указании аргумента TYPE_WARNING сервер будет информировать о неявном изменении типа курсора, если он несовместим с запросом SELECT.
Сразу после открытия курсора можно получить его содержимое посредством следующей команды:
При использовании оператора FIRST будет возвращена первая строка результирующего набора курсора, которая становится текущей строкой. При указании LAST будет возвращена последняя строка курсора. Она же становится текущей строкой.
При указании оператора NEXT будет возвращена строка, находящаяся в результирующем наборе сразу же после текущей. Эта строка становится текущей. По умолчанию команда FETCH использует именно этот способ выборки строк.
При указании оператора PRIOR будет возвращена строка, находящаяся перед текущей. Эта строка становится текущей.
Оператор ABSOLUTE {номер_строки | @переменная_номера_строки} возвращает строку по ее абсолютному порядковому номеру в полном результирующем наборе курсора. Номер строки можно задать с помощью константы или как имя переменной, в которой хранится номер строки. Переменная должна иметь целочисленный тип данных. Указываются как положительные, так и отрицательные значения. При указании положительного значения строка отсчитывается от начала набора, отрицательного – от конца. Выбранная строка становится текущей. Если указано нулевое значение, строка не возвращается.
Аргумент RELATIVE {кол_строки | @переменная_кол_строки} возвращает строку, находящуюся со смещением на указанное количество строк после текущей. Если указать отрицательное значение числа строк, то будет возвращена строка, находящаяся за указанное количество строк перед текущей. При указании нулевого значения возвратится текущая строка. Возвращенная строка становится текущей.
Чтобы открыть глобальный курсор, перед его именем требуется указать ключевое слово GLOBAL . Имя курсора также может быть указано с помощью переменной.
В выражении INTO @имя_переменной [,...n] определяется список переменных, в которых будут сохранены соответствующие значения столбцов возвращаемой строки. Порядок указания переменных должен соответствовать порядку столбцов в курсоре, а тип данных переменной – типу данных в столбце курсора.
Для изменения данных с помощью курсора необходимо выполнить команду UPDATE в следующем формате:
За одну операцию могут быть изменены значения нескольких столбцов текущей строки курсора, но все они должны принадлежать одной таблице.
Для удаления данных посредством курсора используется команда DELETE в следующем формате:
В результате будет удалена строка, являющаяся текущей в курсоре.
Для удаления курсора из памяти используется команда
Deallocate cursor_name;
Для определения наличия строк в курсоре следует использовать глобальную переменную @@FETCH_STATUS , которая принимает ненулевое значение, если строк в курсоре больше нет. Если же набор строк еще не исчерпан, то @@FETCH_STATUS равна нулю.
Declare @company varchar(50), @manager varchar(50), @message varchar(256); declare crs_clients cursor local for select company, manager from customers where city = "Moscow" order by company, manager; print "Список клиентов"; open crs_clients; fetch next from crs_clients into @company, @manager; while @@FETCH_STATUS = 0 begin select @message = "Компания " + @company + " менеджер " + @manager; print @message; -- переход к следующей записи fetch next from crs_clients into @company, @manager; end; close crs_clients; deallocate crs_clients;
Здравствуй, человек-читатель блогов на Community.
Хочу рассказать о своем недавнем опыте оптимизации курсора в SQL Server.
Первое, что нужно знать, курсор – это не хорошо, а плохо
. Где есть возможность заменить курсор на INSERT SELECT или на использование временной таблицы, там нужно это сделать (за редким исключением). Курсор – это почти всегда дополнительные ресурсы сервера и резкое падение производительности по сравнению с другими решениями.
Второе, иногда без курсора не обойтись – там где не обойтись без построчного прохода по результату выборки. Вот в таких случаях очень важно правильно создать нужный тип курсора – тот, который соответствует решаемой задаче. Общий синтаксис объявления курсора имеет вид:
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 ]
]
]
[
;]
Остановлюсь на первых трех строчках ключевых параметров.
LOCAL или GLOBAL: если хотим, чтобы курсор был доступен другим процедурам, функциям, пакетам в рамках нашей сессии, то GLOBAL – в этом случае за удалением курсора следим сами (команда DEALLOCATE). Во всех остальных случаях (т.е. в подавляющем своем большинстве) – LOCAL
. Внимание, по умолчанию создается именно GLOBAL курсор!
FORWARD_ONLY или SCROLL: если хотим ходить по курсору, как ненормальные, туда-сюда, то SCROLL, иначе – FORWARD_ONLY
. Внимание, по умолчанию создается SCROLL курсор!
STATIC или KEYSET, DYNAMIC, FAST_FORWARD: если хотим, чтобы при проходе по курсору отображалась актуальная информация из таблицы (т.е., если после открытия курсора, мы поменяли информацию в одном из полей таблицы и хотим, чтобы при проходе по курсору в нужной строчке курсора была уже обновленная информация), то используем или KEYSET (если КАЖДАЯ таблица, участвующая в выборке, имеет уникальный индекс) или DYNAMIC (самый медленный тип). Если же нам нужен снимок результата выборки после открытия курсора – STATIC
(самый быстрый тип – копия результата выборки копируется в базу tempdb и работаем уже с ней). FAST_FORWARD = KEYSET+FORWARD_ONLY+READ_ONLY – пацаны из инета пишут, что STATIC дольше открывается (т.к. создается копия в tempdb), но быстрее работает, а FAST_FORWARD – наоборот. Так что если количество записей велико (насколько большое показывает практика), то применяем STATIC, иначе – FAST_FORWARD. Внимание, по умолчанию создается DYNAMIC курсор.
Таким образом, для большого кол-ва записей в большинстве случаев мой выбор:
DECLARE cursor_name CURSOR LOCAL FORWARD_ONLY STATIC FOR
select_statemen
t
для небольшого кол-ва записей:
DECLARE cursor_name CURSOR LOCAL FAST_FORWARD FOR
select_statement
Теперь перейдем к практике (что собственно и подтолкнуло меня к писанине сего).
Испокон веков при объявлении курсора я применял конструкцию DECLARE ... CURSOR LOCAL FOR...
При разработке интеграции с одной очень нехорошей базой, в которой нет ни одного индекса и не одного ключа, я применил тот же подход при объявлении курсоров, что и всегда. Выборка одного курсора содержала 225 000 записей. В результате процесс импорта данных из такой базы занял 15 часов 14 минут
!!! И хотя импорт и был первичный (т.е. одноразовый), но даже для нормального тестирования такого импорта потребовалось бы несколько суток! После замены вышеназванной конструкции при объявлении курсора на DECLARE .. CURSOR LOCAL FORWARD_ONLY STATIC FOR.. весь процесс импорта занял... внимание... 10 минут 5 секунд
!!! Так что игра точно стоит свеч.
Хочу повториться, что идеальный вариант - это все же не использовать курсоры вообще - для СУБД MS SQL намного роднее реляционный, а не навигационный подход.
Сегодня будем рассматривать очень много чего интересного, например как запустить уже созданную процедуру, которая принимает параметры, массово, т.е. не только со статическим параметрами, а с параметрами, которые будут меняться, например, на основе какой-нибудь таблицы, как обычная функция, и в этом нам помогут как раз курсоры и циклы , и как это все реализовать сейчас будем смотреть.
Как Вы поняли, курсоры и циклы мы будем рассматривать применимо к конкретной задачи. А какой задачи, сейчас расскажу.
Существует процедура, которая выполняет какие-то действия, которые не может выполнить обычная функция SQL например, расчеты и insert на основе этих расчетов. И Вы ее запускаете, например вот так:
EXEC test_PROCEDURE par1, par2
Другими словами Вы запускаете ее только с теми параметрами, которые были указаны, но если Вам необходимо запустить данную процедуру скажем 100, 200 или еще более раз, то согласитесь это не очень удобно, т.е. долго. Было бы намного проще, если бы мы взяли и запускали процедуру как обычную функцию в запросе select , например:
SELECT my_fun(id) FROM test_table
Другими словами функция отработает на каждую запись таблицы test_table, но как Вы знаете процедуру так использовать нельзя. Но существует способ, который поможет нам осуществить задуманное, точнее даже два способа первый это с использованием курсора и цикла и второй это просто с использованием цикла, но уже без курсора. Оба варианта подразумевают, что мы будем создавать дополнительную процедуру, которую в дальнейшем мы будем запускать.
Примечание! Все примеры будем писать в СУБД MSSql 2008, используя Management Studio. Также все ниже перечисленные действия требуют необходимых знаний в SQL, а точнее в программировании на Transact-SQL. Могу посоветовать для начала ознакомиться со следующим материалом:
И так приступим, и перед тем как писать процедуру, давайте рассмотрим исходные данные нашего примера.
Допустим, есть таблица test_table
CREATE TABLE .( (18, 0) NULL, (50) NULL, (50) NULL) ON GOВ нее необходимо вставлять данные, на основе каких-то расчетов, которые будет выполнять процедура my_proc_test , в данном случае она просто вставляет данные, но на практике Вы можете использовать свою процедуру, которая может выполнять много расчетов, поэтому в нашем случае именно эта процедура не важна, она всего лишь для примера. Ну, давайте создадим ее:
CREATE PROCEDURE . (@number numeric, @pole1 varchar(50), @pole2 varchar(50)) AS BEGIN INSERT INTO dbo.test_table (number, pole1, pole2) VALUES (@number, @pole1, @pole2) END GO
Она просто принимает три параметра и вставляет их в таблицу.
И допустим эту процедуру, нам нужно запустить столько раз, сколько строк в какой-нибудь таблице или представлении (VIEWS) , другими словами запустить ее массово для каждой строки источника.
И для примера создадим такой источник, у нас это будет простая таблица test_table_vrem , а у Вас это может быть, как я уже сказал свой источник, например временная таблица или представление:
CREATE TABLE .( (18, 0) NULL, (50) NULL, (50) NULL) ON GO
Заполним ее тестовыми данными:
И теперь нашу процедуру необходимо запустить для каждой строки, т.е. три раза с разными параметрами. Как Вы понимаете значения этих полей и есть наши параметры, другими словами, если бы мы запускали нашу процедуру вручную, это выглядело вот так:
exec my_proc_test 1, ‘pole1_str1’, ‘pole2_str1’
И так еще три раза, с соответствующими параметрами.
Но нам так не охота, поэтому мы напишем еще одну дополнительную процедуру, которая и будет запускать нашу основную процедуру столько раз, сколько нам нужно.
Первый вариант.
Перейдем сразу к делу и напишем процедуру (my_proc_test_all ), код я как всегда прокомментировал:
CREATE PROCEDURE . AS --объявляем переменные DECLARE @number bigint DECLARE @pole1 varchar(50) DECLARE @pole2 varchar(50) --объявляем курсор DECLARE my_cur CURSOR FOR SELECT number, pole1, pole2 FROM test_table_vrem --открываем курсор OPEN my_cur --считываем данные первой строки в наши переменные FETCH NEXT FROM my_cur INTO @number, @pole1, @pole2 --если данные в курсоре есть, то заходим в цикл --и крутимся там до тех пор, пока не закончатся строки в курсоре WHILE @@FETCH_STATUS = 0 BEGIN --на каждую итерацию цикла запускаем нашу основную процедуру с нужными параметрами exec dbo.my_proc_test @number, @pole1, @pole2 --считываем следующую строку курсора FETCH NEXT FROM my_cur INTO @number, @pole1, @pole2 END --закрываем курсор CLOSE my_cur DEALLOCATE my_cur GO
И теперь осталось нам ее вызвать и проверить результат:
До выполнения процедуры SELECT * FROM test_table --вызов процедуры EXEC dbo.my_proc_test_all --после выполнения процедуры SELECT * FROM test_table
Как видите, все у нас отработало как надо, другими словами процедура my_proc_test сработала все три раза, а мы всего лишь один раз запустили дополнительную процедуру.
Второй вариант.
Сразу скажу, что здесь требуется нумерация строк во временной таблице, т.е. каждая строка должна быть пронумерована, например 1, 2, 3 таким полем у нас во временной таблице служит number.
Пишем процедуру my_proc_test_all_v2
CREATE PROCEDURE . AS --объявляем переменные DECLARE @number bigint DECLARE @pole1 varchar(50) DECLARE @pole2 varchar(50) DECLARE @cnt int DECLARE @i int --узнаем количество строк во временной таблице SELECT @cnt=count(*) FROM test_table_vrem --задаем начальное значение идентификатора SET @i=1 WHILE @cnt >= @i BEGIN --присваиваем значения нашим параметрам SELECT @number=number, @pole1= pole1, @pole2=pole2 FROM test_table_vrem WHERE number = @I --на каждую итерацию цикла запускаем нашу основную процедуру с нужными параметрами EXEC dbo.my_proc_test @number, @pole1, @pole2 --увеличиваем шаг set @i= @i+1 END GO
И проверяем результат, но для начала очистим нашу таблицу, так как мы же ее только что уже заполнили по средствам процедуры my_proc_test_all:
Очистим таблицу DELETE test_table --до выполнения процедуры SELECT * FROM test_table --вызов процедуры EXEC dbo.my_proc_test_all_v2 --после выполнения процедуры SELECT * FROM test_table
Как и ожидалось результат такой же, но уже без использования курсоров. Какой вариант использовать решать Вам, первый вариант хорош, тем, что в принципе не нужна нумерация, но как Вы знаете, курсоры работают достаточно долго, если строк в курсоре будет много, а второй вариант хорош тем, что отработает, как мне кажется быстрей, опять же таки, если строк будет много, но нужна нумерация, лично мне нравится вариант с курсором, а вообще решать Вам может Вы сами придумаете что-то более удобное, я всего лишь показал основы того, как можно реализовать поставленную задачу. Удачи!
Я получил целый ряд комментариев. В одном из них читатель попросил меня уделить больше внимания курсорам, одному из важных элементов хранимых процедур.
Так как курсоры являются частью хранимой процедуры, то в этой статье мы еще детальнее рассмотрим ХП. В частности, как извлечь из ХП набор данных.
Курсор не может использоваться в 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"]);
} Выведенные на экран результаты будут выглядеть приблизительно следующим образом.
