Компьютер 

Ms sql курсоры пример. Курсоры в встроенном SQL


1) Понятие курсора
В интерактивном SQL однострочные и многострочные запросы не различаются. Во встроенном SQL эти запросы выполняются по-разному. Однострочные запросы возвращают одну строку и нами уже рассмотрены. Когда результатом выполнения запроса является не одна строка, встроенный SQL должен обеспечить прикладной программе возможность построчного получения результатов запроса. Для этого используются курсоры. Под курсором понимается переменная, связанная с запросом. Ее значением является каждая строка, удовлетворяющая запросу. Как и переменные, курсоры должны быть описаны перед их использованием. В отличие от представлений курсоры предназначены для построчной обработки.

2) Объявление курсора

DECLARE [{}] [[NO ] SCROLL ] CURSOR [{WITH|WITHOUT } HOLD ] FOR [FOR {READ ONLY|UPDATE [OF ]}]

3) Ключевые слова
. SENSITIVE|INSENSITIVE|ASENSITIVE – изменения в результирующем наборе видны | запрещены (зафиксированы с помощью копии набора данных)|СУБД само принимает решение о необходимости сделать копию(действует по умолчанию).
. WITH|WITHOUT HOLD – оставляет открытым | закрывает курсор, если встретился оператор COMMIT.
. SCROLL – [запрещает] извлекать строки результата в произвольном порядке.
. FOR READ ONLY – определяет курсор только для чтения.
. FOR UPDATE OF – блокирует от обновления только указанные столбцы.

4) Объявление курсора в SQL Server

DECLARE CURSOR [LOCAL|GLOBAL ] [FORWARD_ONLY|SCROLL ] [STATIC|KEYSET|DYNAMIC|FAST_FORWARD ] [READ_ONLY|SCROLL_LOCKS|OPTIMISTIC ] FOR [FOR UPDATE [OF ]]

. STATIC – Определяет курсор, который создает временную копию данных для использования курсором. Все запросы к курсору обращаются к указанной временной таблице в базе данных tempdb, поэтому изменения базовых таблиц не влияют на данные, возвращаемые выборками для данного курсора, а сам курсор не позволяет производить изменения.
. KEYSET – Указывает, что членство или порядок строк в курсоре не изменяются после его открытия. Набор ключей, однозначно определяющих строки, встроен в таблицу в базе данных tempdb с именем keyset.
. DYNAMIC – Определяет курсор, отображающий все изменения данных, сделанные в строках результирующего набора при просмотре этого курсора. Значения данных, порядок, а также членство строк в каждой выборке могут меняться. Параметр выборки ABSOLUTE динамическими курсорами не поддерживается.
. FAST_FORWARD – Указывает курсор FORWARD_ONLY, READ_ONLY, для которого включена оптимизация производительности. Параметр FAST_FORWARD не может указываться вместе с параметрами SCROLL или FOR_UPDATE.
. SCROLL_LOCKS – Указывает, что позиционированные обновления или удаления, осуществленные через курсор, гарантированно будут успешными. SQL Server блокирует строки по мере считывания в курсор для обеспечения их доступности для последующих изменений. Параметр SCROLL_LOCKS не может указываться вместе с параметром FAST_FORWARD или STATIC.
. OPTIMISTIC – Указывает, что позиционированные обновления или удаления, осуществленные через курсор, не будут выполнены, если строка была обновлена со времени считывания в курсор. SQL Server не блокирует строки по мере их считывания в курсор. Вместо этого, чтобы определить, изменялась ли строка после считывания в курсор, выполняется сравнение значений столбца timestamp (или контрольных сумм, если в таблице нет столбца timestamp). Если строка была изменена, то ее позиционированное изменение или удаление невозможно. Параметр OPTIMISTIC не может указываться вместе с параметром FAST_FORWARD.

5) Открытие курсора

6) Извлечение строк из курсора

FETCH [{NEXT|PRIOR|FIRST|LAST |{ABSOLUTE|RELATIVE }}]
FROM INTO

7) Варианты позиционирования курсора
. NEXT|PRIOR|FIRST|LAST – на следующую|предыдущую|первую|последнюю строку результирующего множества.
. RELATIVE ±N – на строку с положительным или отрицательным смещением относительно текущей строки.
. ABSOLUTE ±N – на строку с явно указанным абсолютным номером позиции от начала или конца курсора.

Примечание : В SQL Server вместо N допускается целочисленная переменная @N.

8) Закрытие курсора

9) Примечания по курсорам
. Если курсор содержит более одной строки, необходимо организовать цикл извлечения данных из него с периодической проверкой достижения последней строки.
. В отличие от таблиц и представлений строки курсора упорядочены либо явным образом с помощью раздела ORDER BY , либо в соответствии с соглашениями, принятыми в конкретной СУБД.
. Курсоры применяются также для выборки из таблиц групп строк, которые можно обновлять или удалять по одной.
. Для того, чтобы курсор был обновляемым, он должен удовлетворять тем же критериям, что и представление, то есть не содержать разделов UNION, ORDER BY, GROUP BY, DISTINCT .

10) Пример на удаление данных из курсора

exec sql declare cursor Cur1 for select * from Customer
where Rating
// print (@f1+’ ‘+convert(Varchar(5),@f2))
exec sql delete from Customer
where current of Cur1; } – Данные для удаления взять из курсора
not_done:
exec sql close cursor Cur1; — Закрываем курсор
exit();

11) Пример на увеличение комиссионных

exec sql declare cursor CurCust for select * from SalesPeople
where SNum in (select SNum from Customer where Rating=300); — Определяем курсор
exec sql open cursor CurCust; — Выполняем курсор
while (sqlca.sqlcode==0) { — Создаем цикл для обновления данных в таблице
exec sql fetch CurCust into:Id_num, :SalesPerson, :Loc, :Comm;
exec sql update SalesPeople set Comm=Comm+.01 where current
of CurCust; } – Данные для обновления взять из курсора
exec sql close cursor CurCust; — Закрываем курсор

SELECT S.Name, MAX(S.City) AS City, SUM(O.Amt) AS Amt FROM SalesPeople S INNER JOIN Orders O ON S.SNum=O.SNum GROUP BY S.Name ORDER BY 2

DECLARE Cur1 SCROLL CURSOR FOR SELECT S.Name, MAX(S.City) AS City, SUM(O.Amt) AS Amt FROM SalesPeople S INNER JOIN Orders O ON S.SNum=O.SNum GROUP BY S.Name ORDER BY 2
OPEN Cur1
FETCH NEXT FROM Cur1
WHILE @@FETCH_STATUS=0
BEGIN
FETCH NEXT FROM Cur1
END
CLOSE Cur1
DEALLOCATE Cur1

Здравствуй, человек-читатель блогов на 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 намного роднее реляционный, а не навигационный подход.

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:

  1. First, you declare your variables that you need in the logic.
  2. Second you declare cursor with a specific name that you will use throughout the logic. This is immediately followed by opening the cursor.
  3. Third, you fetch a record from cursor to begin the data processing.
  4. 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.
  5. 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.
  6. Sixth, once all of the data has been processed, then you close cursor.
  7. 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 (Oracle, Microsoft SQL Server) - получаемый при выполнении запроса результирующий набор и связанный с ним указатель текущей записи. бы сказал, что курсор - это виртуальная таблица которая представляет собой альтернативное хранилище данных. При этом курсор, позволяет обращаться к своим данным, как к данным обычного массива.
Используются курсоры в хранимых процедурах. Довольно теории давайте рассмотрим пример:
У нас есть база данных (база немного не хорошая, это одна из моих лабораторных работ, но наш препод по базам данных настаивал на такой структуре)
/*данные о банке */
CREATE TABLE `bank` (

`BankName` VARCHAR (50) COLLATE utf8_bin NOT NULL DEFAULT "" ,


PRIMARY KEY (`BankId`)

)ENGINE=InnoDB
CHARACTER SET "utf8" COLLATE "utf8_bin" ;
/*данные о вкладах */
CREATE TABLE `bankdistribution` (
`BankId` INTEGER (11) NOT NULL ,
`Persent` INTEGER (11) DEFAULT NULL ,
`ContributeAmount` DECIMAL (10,0) NOT NULL ,
`ClientId` INTEGER (11) NOT NULL ,
PRIMARY KEY (`BankId`, `ClientId`),
KEY `BankId` (`BankId`),
KEY `ClientId` (`ClientId`),
CONSTRAINT `bankdistribution_fk` FOREIGN KEY (`BankId`) REFERENCES `bank` (`BankId`),
CONSTRAINT `bankdistribution_fk1` FOREIGN KEY (`ClientId`) REFERENCES `client` (`ClientId`)
)ENGINE=InnoDB
/*данные о вкладчиках*/
CREATE TABLE `client` (
`ClientId` INTEGER (3) NOT NULL AUTO_INCREMENT,
`CreditCardId` BIGINT(10) NOT NULL ,
`Surname` VARCHAR (50) COLLATE utf8_bin NOT NULL DEFAULT "" ,
`Name` VARCHAR (50) COLLATE utf8_bin NOT NULL DEFAULT "" ,
`FirstName` VARCHAR (50) COLLATE utf8_bin NOT NULL DEFAULT "" ,
`Phone` VARCHAR (50) COLLATE utf8_bin NOT NULL DEFAULT "" ,
`Address` VARCHAR (50) COLLATE utf8_bin NOT NULL DEFAULT "" ,
`SafeId` INTEGER (5) NOT NULL ,
PRIMARY KEY (`ClientId`, `CreditCardId`),
KEY `ClientId` (`ClientId`)

)ENGINE=InnoDB
AUTO_INCREMENT=11 CHARACTER SET "utf8" COLLATE "utf8_bin"

Допустим нам нужно получать по очереди каждый банк и производить с ним какие то действия, помочь в этом нам мог бы такой вот запрос

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 ;

* 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;
Открываем курсор Open cursor_name;
Дальше пока не достигаем конец курсора (WHILE done = 0 DO) извлекаем данные и обрабатываем их.
Перед выходом из хранимой процедуры необходимо курсор закрыть. Close cursor_name;

Вроде ничего сложного. Но с SQLSTATE "02000" связанно много подводных камней.

WHILE done = 0 DO
FETCH BankCursor INTO vBankId,vBankName,vAddress,vPhone;

Select (ContributeAmount) INTO vContributeAmountSUM FROM bankdistribution where BankId = vBankId limit 1;
делаем какие то действия
END WHILE ;

* This source code was highlighted with Source Code Highlighter .


Всё хорошо, и правильно с точки зрения синтаксиса. Но с логической точки зрения нет. Может случиться так что вкладчики не открыли счетов в каком то банке, тогда для Select (ContributeAmount) INTO vContributeAmountSUM FROM bankdistribution where BankId = vBankId limit 1; сработает SQLSTATE: 02000, переменная done установится в 1, и цикл while закончиться раньше чем мы ожидали. Этого можно избежать поступив следующим образом
WHILE done = 0 DO
FETCH BankCursor INTO vBankId,vBankName,vAddress,vPhone;
/* извлечем для банка сумму любого из его вкладов */


if (vContributeAmountSUM > 0) then
/* извлечем для банка сумму любого из его вкладов */

end if ;
делаем какие то действия
END WHILE ;

* This source code was highlighted with Source Code Highlighter .


первым запросом мы проверили а есть ли вклады (если их нет то vContributeAmountSUM == 0) и только если таковые имеются мы извлекаем данные.

Теперь допустим нам нужно излечь общую сумму на счетах в разных банках у каждого клиента
Declare ClientSummCursor Cursor for Select sum

Declare ClientSummCursor Cursor for Select sum (`bankdistribution`.`ContributeAmount`),`bankdistribution`.`ClientId` FROM `bankdistribution` Inner Join client on (client.ClientId = bankdistribution.`ClientId`) where 1 group by `bankdistribution`.`ClientId`;

Open ClientSummCursor;
WHILE done = 0 DO
FETCH BankCursor INTO vBankId,vBankName,vAddress,vPhone;
/* извлечем для банка сумму любого из его вкладов */
Select Сount(ContributeAmount) INTO vContributeAmountSUM FROM bankdistribution where BankId = vBankId limit 1;
/* проверим действительно ли есть вклады в этом банке */
if (vContributeAmountSUM > 0) then
/* извлечем для банка сумму любого из его вкладов */
Select ContributeAmount INTO vContributeAmountSUM FROM bankdistribution where BankId = vBankId limit 1;
end if ;


делаем какие то действия.
END WHILE ;

* This source code was highlighted with Source Code Highlighter .

Может возникнуть та же ситуация, когда данные в курсоре ClientSummCursor, закончатся раньше чем данные в BankCursor, сработает SQLSTATE: 02000, переменная done установится в 1, и цикл while закончиться раньше чем мы ожидали. Этого можно избежать поступив следующим образом

Open ClientSummCursor;
WHILE done = 0 DO
FETCH BankCursor INTO vBankId,vBankName,vAddress,vPhone;
/* извлечем для банка сумму любого из его вкладов */
Select Сount(ContributeAmount) INTO vContributeAmountSUM FROM bankdistribution where BankId = vBankId limit 1;
/* проверим действительно ли есть вклады в этом банке */
if (vContributeAmountSUM > 0) then
/* извлечем для банка сумму любого из его вкладов */
Select ContributeAmount INTO vContributeAmountSUM FROM bankdistribution where BankId = vBankId limit 1;
end if ;
/* до извлечения данных из второго курсора запомним состояние sqlstate */
SET old_status = done;
/* извлекаем нужные нам данные */
FETCH ClientSummCursor INTO vSum,vClientId;
/* проверяем были ли извлечены данные, не стработал ли sqlstate 0200 */
if (done = 0) then
делаем какие то действия.
end if ;
/* перед окончанием while восттановим значение переменной done */
set done = old_status;
END WHILE ;

* This source code was highlighted with Source Code Highlighter .

Всем дочитавшим до этого места спасибо, надеюсь это статься покажется кому то полезной.

Я получил целый ряд комментариев. В одном из них читатель попросил меня уделить больше внимания курсорам, одному из важных элементов хранимых процедур.

Так как курсоры являются частью хранимой процедуры, то в этой статье мы еще детальнее рассмотрим ХП. В частности, как извлечь из ХП набор данных.

Что такое курсор?

Курсор не может использоваться в 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

Несколько существенных замечаний к приведенному выше коду:

  1. Мы определяем самый ранний и самый поздний года для выборки из таблицы lakers ;
  2. Мы создаем временную таблицу для хранения исходящих данных с необходимой структурой (season, streak, win );
  3. В цикле мы сначала выполняем ранее созданную ХП с необходимыми параметрами (call streak(cur_year, @l, @s );), затем захватываем возвращаемые данные и вставляем их во временную таблицу (insert into yearly_streak values (cur_year, @l, @s); );
  4. Наконец, мы выбираем из временной таблицы и возвращаем набор данных, после чего делаем некоторую настройку (DROP TEMPORARY TABLE IF EXISTS yearly_streak; ).

Чтобы получить результаты, мы создаем еще один небольшой 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"]); }

Выведенные на экран результаты будут выглядеть приблизительно следующим образом.