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.
Solution
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:
- Look at an example cursor
- Break down the components of the cursor
- Provide additional cursor examples
- Analyze the pros and cons of cursor usage
How to Create a SQL Server Cursor
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:
- First, you declare your variables that you need in the logic.
- Second you declare cursor with a specific name that you will use throughout the logic. This is immediately followed by opening the cursor.
- Third, you fetch a record from cursor to begin the data processing.
- Fourth, is the data process that is unique to each set of logic. This could be inserting, updating, deleting, etc. for each row of data that was fetched. This is the most important set of logic during this process that is performed on each row.
- Fifth, you fetch the next record from cursor as you did in step 3 and then step 4 is repeated again by processing the selected data.
- Sixth, once all of the data has been processed, then you close cursor.
- As a final and important step, you need to deallocate the cursor to release all of the internal resources SQL Server is holding.
From here, check out the examples below to get started on knowing when to use SQL Server cursors and how to do so.
Example SQL Server Cursor
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
SQL Server Cursor Components
Based on the example above, cursors include these components:
- DECLARE statements - Declare variables used in the code block
- SET\SELECT statements - Initialize the variables to a specific value
- DECLARE CURSOR statement - Populate the cursor with values that will be
evaluated
- NOTE - There are an equal number of variables in the DECLARE CURSOR FOR statement as there are in the SELECT statement. This could be 1 or many variables and associated columns.
- OPEN statement - Open the cursor to begin data processing
- FETCH NEXT statements - Assign the specific values from the cursor to the
variables
- NOTE - This logic is used for the initial population before the WHILE statement and then again during each loop in the process as a portion of the WHILE statement
- WHILE statement - Condition to begin and continue data processing
- BEGIN...END statement - Start and end of the code block
- NOTE - Based on the data processing multiple BEGIN...END statements can be used
- Data processing - In this example, this logic is to backup a database to a specific path and file name, but this could be just about any DML or administrative logic
- CLOSE statement - Releases the current data and associated locks, but permits the cursor to be re-opened
- DEALLOCATE statement - Destroys the cursor
Recommended Reading
Learn more about SQL Server Cursors and alternatives:
Additional SQL Server Cursor Examples
In the example above backups are issued via a cursor, check out these other tips that leverage cursor-based logic:
- Script to create commands to disable, enable, drop and recreate Foreign Key constraints in SQL Server
SQL Server Cursor Analysis
The analysis below is intended to serve as insight into various scenarios where cursor-based logic may or may not be beneficial:
- Online Transaction Processing (OLTP) - In most OLTP environments, SET based logic makes the most sense for short transactions. Our team has run into a third-party application that uses cursors for all of its processing, which has caused issues, but this has been a rare occurrence. Typically, SET based logic is more than feasible and cursors are rarely needed.
- Reporting - Based on the design of the reports and the underlying design, cursors are typically not needed. However, our team has run into reporting requirements where referential integrity does not exist on the underlying database and it is necessary to use a cursor to correctly calculate the reporting values. We have had the same experience when needing to aggregate data for downstream processes, a cursor-based approach was quick to develop and performed in an acceptable manner to meet the need.
- Serialized processing - If you have a need to complete a process in serialized manner, cursors are a viable option.
- Administrative tasks - Many administrative tasks need to be executed in a serial manner, which fits nicely into cursor-based logic, but other system-based objects exist to fulfill the need. In some of those circumstances, cursors are used to complete the process.
- Large data sets - With large data sets you could run into any one or more
of the following:
- Cursor based logic may not scale to meet the processing needs.
- With large set-based operations on servers with a minimal amount of memory, the data may be paged or monopolize the SQL Server which is time consuming can cause contention and memory issues. As such, a cursor-based approach may meet the need.
- Some tools inherently cache the data to a file under the covers, so processing the data in memory may or may not actually be the case.
- If the data can be processed in a staging SQL Server database the impacts to the production environment are only when the final data is processed. All of the resources on the staging server can be used for the ETL processes then the final data can be imported.
- SSIS supports batching sets of data which may resolve the overall need to break-up a large data set into more manageable sizes and perform better than a row by row approach with a cursor.
- Depending on how the cursor or SSIS logic is coded, it may be possible to restart at the point of failure based on a
- Repeat a batch with the GO command
Next Steps
- When you are faced with a data processing decision determine where you stand with SQL Server cursor usage. They may or may not have a place in your application or operational processes. There are many ways to complete a task, so using a cursor could be a reasonable alternative or not. You be the judge.
- If you run into issues with another coding technique and need to get something done quickly, using a cursor may be a viable alternative. It may take longer to process the data, but the coding time might be much less. If you have a one-time process or nightly processing, this could do the trick.
- If cursors are shunned in your environment, be sure to select another viable alternative. Just be sure the process will not cause other issues. As an example, if a cursor is used and millions of rows are processed will this potentially flush all of the data from cache and cause further contention? Or with a large data set will the data be paged to disk or written to a temporary directory?
- As you evaluate a cursor-based approach versus other alternatives make a fair comparison of the techniques in terms of time, contention and resources needed. Hopefully these factors will drive you to the proper technique.
Курсор в SQL – это область в памяти базы данных, которая предназначена для хранения последнего оператора SQL. Если текущий оператор – запрос к базе данных, в памяти сохраняется и строка данных запроса, называемая текущим значением, или текущей строкой курсора . Указанная область в памяти поименована и доступна для прикладных программ.
В соответствии со стандартом SQL при работе с курсорами можно выделить следующие основные действия :
- создание или объявление курсора ;
- открытие курсора, т.е. наполнение его данными, которые сохраняются в многоуровневой памяти;
- выборка из курсора и изменение с его помощью строк данных;
- закрытие курсора , после чего он становится недоступным для пользовательских программ;
- освобождение курсора , т.е. удаление курсора как объекта, поскольку его закрытие необязательно освобождает ассоциированную с ним память.
SQL Server поддерживает три вида курсоров :
- курсоры SQL применяются в основном внутри триггеров, хранимых процедур и сценариев;
- курсоры сервера действуют на сервере и реализуют программный интерфейс приложений для ODBC, OLE DB, DB_Library;
- курсоры клиента реализуются на самом клиенте. Они выбирают весь результирующий набор строк из сервера и сохраняют его локально, что позволяет ускорить операции обработки данных за счет снижения потерь времени на выполнение сетевых операций.
Управление курсором в среде MS SQL Server
Управление курсором реализуется путем выполнения следующих команд:
- DECLARE – создание или объявление курсора ;
- OPEN – открытие курсора , т.е. наполнение его данными;
- FETCH – выборка из курсора и изменение строк данных с помощью курсора;
- CLOSE – закрытие курсора ;
- DEALLOCATE – освобождение курсора , т.е. удаление курсора как объекта.
Объявление курсора
В стандарте SQL для создания курсора предусмотрена следующая команда:
<создание_курсора>::= DECLARE имя_курсора CURSOR FOR SELECT_оператор ]}]
При использовании ключевого слова INSENSITIVE будет создан статический курсор . Изменения данных не разрешаются, кроме того, не отображаютсяизменения , сделанные другими пользователями. Если ключевое слово INSENSITIVE отсутствует, создается динамический курсор .
При указании ключевого слова SCROLL созданный курсор можно прокручивать в любом направлении, что позволяет применять любые команды выборки . Если этот аргумент опускается, то курсор окажется последовательным , т.е. его просмотр будет возможен только в одном направлении – от начала к концу.
SELECT-оператор задает тело запроса SELECT, с помощью которого определяется результирующий набор строк курсора .
При указании аргумента FOR READ_ONLY создается курсор "только для чтения", и никакие модификации данных не разрешаются. Он отличается отстатического , хотя последний также не позволяет менять данные. В качестве курсора "только для чтения" может быть объявлен динамический курсор , что позволит отображать изменения , сделанные другим пользователем.
Создание курсора с аргументом FOR UPDATE позволяет выполнять в курсоре изменение данных либо в указанных столбцах, либо, при отсутствии аргумента OF имя_столбца, во всех столбцах.
В среде MS SQL Server принят следующий синтаксис команды создания курсора :
<создание_курсора>::= DECLARE имя_курсора CURSOR FOR SELECT_оператор ]]
При использовании ключевого слова LOCAL будет создан локальный курсор , который виден только в пределах создавшего его пакета, триггера, хранимой процедуры или пользовательской функции. По завершении работы пакета, триггера, процедуры или функции курсор неявно уничтожается. Чтобы передать содержимое курсора за пределы создавшей его конструкции, необходимо присвоить его параметру аргумент OUTPUT.
Если указано ключевое слово GLOBAL, создается глобальный курсор ; он существует до закрытия текущего соединения.
При указании FORWARD_ONLY создается последовательный курсор ; выборку данных можно осуществлять только в направлении от первой строки к последней.
При указании SCROLL создается прокручиваемый курсор ; обращаться к данным можно в любом порядке и в любом направлении.
При указании STATIC создается статический курсор .
При указании KEYSET создается ключевой курсор.
При указании DYNAMIC создается динамический курсор .
Если для курсора READ_ONLY указать аргумент FAST_FORWARD, то созданный курсор будет оптимизирован для быстрого доступа к данным. Этот аргумент не может быть использован совместно с аргументами FORWARD_ONLY и OPTIMISTIC.
В курсоре , созданном с указанием аргумента OPTIMISTIC, запрещается изменение и удаление строк , которые были изменены после открытия курсора .
При указании аргумента TYPE_WARNING сервер будет информировать пользователя о неявном изменении типа курсора , если он несовместим с запросомSELECT.
Открытие курсора
Для открытия курсора и наполнения его данными из указанного при создании курсора запроса SELECT используется следующая команда:
OPEN {{имя_курсора } |@имя_переменной_курсора}
После открытия курсора происходит выполнение связанного с ним оператора SELECT, выходные данные которого сохраняются в многоуровневой памяти.
Выборка данных из курсора
Сразу после открытия курсора можно выбрать его содержимое (результат выполнения соответствующего запроса) посредством следующей команды:
FETCH [ FROM ]{{имя_курсора }| @имя_переменной_курсора } ]
При указании FIRST будет возвращена самая первая строка полного результирующего набора курсора , которая становится текущей строкой.
При указании LAST возвращается самая последняя строка курсора . Она же становится текущей строкой.
При указании NEXT возвращается строка, находящаяся в полном результирующем наборе сразу же после текущей. Теперь она становится текущей. По умолчанию команда FETCH использует именно этот способ выборки строк.
Ключевое слово PRIOR возвращает строку, находящуюся перед текущей. Она и становится текущей.
Аргумент ABSOLUTE {номер_строки | @переменная_номера_строки} возвращает строку по ее абсолютному порядковому номеру в полном результирующем наборе курсора . Номер строки можно задать с помощью константы или как имя переменной, в которой хранится номер строки. Переменная должна иметь целочисленный тип данных. Указываются как положительные, так и отрицательные значения. При указании положительного значения строка отсчитывается от начала набора, отрицательного – от конца. Выбранная строка становится текущей. Если указано нулевое значение, строка не возвращается.
Аргумент RELATIVE {кол_строки | @переменная_кол_строки} возвращает строку, находящуюся через указанное количество строк после текущей. Если указать отрицательное значение числа строк, то будет возвращена строка, находящаяся за указанное количество строк перед текущей. При указании нулевого значения возвратится текущая строка. Возвращенная строка становится текущей.
Чтобы открыть глобальный курсор , перед его именем требуется указать ключевое слово GLOBAL. Имя курсора также может быть указано с помощью переменной.
В конструкции INTO @имя_переменной [,...n] задается список переменных, в которых будут сохранены соответствующие значения столбцов возвращаемой строки. Порядок указания переменных должен соответствовать порядку столбцов в курсоре , а тип данных переменной – типу данных в столбце курсора . Если конструкция INTO не указана, то поведение команды FETCH будет напоминать поведение команды SELECT – данные выводятся на экран.
Курсор – это объект, позволяющий по отдельности обрабатывать строки из результирующего набора, возвращенного оператором 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:
О – действие выполнено успешно;
- -1 – выполнение оператора завершилось неудачно, или строка оказалась вне пределов результирующего набора (курсор закончился);
- -2 – выбираемая строка отсутствует, например если за время работы с курсором "чувствительного к изменениям" типа текущая запись была удалена из БД.
Оператор 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
Я получил целый ряд комментариев. В одном из них читатель попросил меня уделить больше внимания курсорам, одному из важных элементов хранимых процедур.
Так как курсоры являются частью хранимой процедуры, то в этой статье мы еще детальнее рассмотрим ХП. В частности, как извлечь из ХП набор данных.
Что такое курсор?
Курсор не может использоваться в 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"]);
} Выведенные на экран результаты будут выглядеть приблизительно следующим образом. Сегодня будем рассматривать очень много чего интересного, например как запустить уже созданную процедуру, которая принимает параметры, массово, т.е. не только со статическим параметрами, а с параметрами, которые будут меняться, например, на основе какой-нибудь таблицы, как обычная функция, и в этом нам помогут как раз курсоры и циклы
, и как это все реализовать сейчас будем смотреть. Как Вы поняли, курсоры и циклы мы будем рассматривать применимо к конкретной задачи. А какой задачи, сейчас расскажу. Существует процедура, которая выполняет какие-то действия, которые не может выполнить обычная функция 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 В нее необходимо вставлять данные, на основе каких-то расчетов, которые будет выполнять процедура 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
Как и ожидалось результат такой же, но уже без использования курсоров. Какой вариант использовать решать Вам, первый вариант хорош, тем, что в принципе не нужна нумерация, но как Вы знаете, курсоры работают достаточно долго, если строк в курсоре будет много, а второй вариант хорош тем, что отработает, как мне кажется быстрей, опять же таки, если строк будет много, но нужна нумерация, лично мне нравится вариант с курсором, а вообще решать Вам может Вы сами придумаете что-то более удобное, я всего лишь показал основы того, как можно реализовать поставленную задачу. Удачи!Используем курсор и цикл в процедуре
Используем только цикл в процедуре
