Курсоры в Mysql.

По долгу службы мне пришлось сталкиваться с курсорами. Хотелось бы рассказать, что это такое и о некоторых особенностях работы с ними. Официальная документация тут — dev.mysql.com/doc/refman/5.1/en/cursors.html Википедия даёт такое определение курсору курсор:

Курсор — ссылка на контекстную область памяти. В некоторых реализациях языка программирования SQL (Oracle, Microsoft SQL Server) — получаемый при выполнении запроса результирующий набор и связанный с ним указатель текущей записи. бы сказал, что курсор — это виртуальная таблица которая представляет собой альтернативное хранилище данных. При этом курсор, позволяет обращаться к своим данным, как к данным обычного массива.
Используются курсоры в хранимых процедурах. Довольно теории давайте рассмотрим пример:
У нас есть база данных (база немного не хорошая, это одна из моих лабораторных работ, но наш препод по базам данных настаивал на такой структуре )

/*данные о банке */
CREATE TABLE `bank` (
 `BankId` INTEGER(11) NOT NULL,
 `BankName` VARCHAR(50) COLLATE utf8_bin NOT NULL DEFAULT '',
 `Address` VARCHAR(50) COLLATE utf8_bin NOT NULL DEFAULT '',
 `Phone` VARCHAR(50) COLLATE utf8_bin NOT NULL DEFAULT '',
 PRIMARY KEY (`BankId`)

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

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

* This source code was highlighted with Source Code Highlighter.

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

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
  FETCH BankCursor INTO vBankId,vBankName,vAddress,vPhone;
  делаем нужные нам действия
END WHILE;
/*закрытие курсора */
Close BankCursor;
END;

* This source code was highlighted with Source Code Highlighter.

Поясним теперь подробнее. Сначала HANDLER, он нужен для обработки исключения — что делать когда данные закончатся ( то есть курсор будет пустым ). Таким образом когда данные закончатся, не с генерируется сообщение об ошибке, а значение переменной done выставиться в 1, изначально done = 0; подробнее об SQLSTATE читаем тут — dev.mysql.com/doc/refman/5.1/en/error-messages-server.html;

Error: 1329 SQLSTATE: 02000 (ER_SP_FETCH_NO_DATA)

Message: No data — zero rows fetched, selected, or processed

SQLSTATE: 02000 срабатывает когда достигнут конец курсора, или когда select или update возвращяет пустую строку.

Следующей строкой мы объявили курсор DECLARE cursor_name CURSOR FOR select_statement;
Открываем курсор Open cursor_name;
Дальше пока не достигаем конец курсора (WHILE done = 0 DO ) извлекаем данные и обрабатываем их.
Перед выходом из хранимой процедуры необходимо курсор закрыть. Close cursor_name;

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

WHILE done = 0 DO
  FETCH BankCursor INTO vBankId,vBankName,vAddress,vPhone;
  /* извлечем для банка сумму любого из его вкладов */
  Select (ContributeAmount) INTO vContributeAmountSUM FROM bankdistribution where BankId = vBankId limit 1;
  делаем какие то действия
END WHILE;

* This source code was highlighted with Source Code Highlighter.

Всё хорошо, и правильно с точки зрения синтаксиса. Но с логической точки зрения нет. Может случиться так что вкладчики не открыли счетов в каком то банке, тогда для 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;
  /* извлечем для банка сумму любого из его вкладов */
  Select Сount(ContributeAmount) INTO vContributeAmountSUM FROM bankdistribution where BankId = vBankId limit 1;
  /* проверим действительно ли есть вклады в этом банке */
  if (vContributeAmountSUM > 0) then
    /* извлечем для банка сумму любого из его вкладов */
    Select ContributeAmount INTO vContributeAmountSUM FROM bankdistribution where BankId = vBankId limit 1;
  end if;
  делаем какие то действия
END WHILE;

* This source code was highlighted with Source Code Highlighter.

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

level up

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

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

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

* This source code was highlighted with Source Code Highlighter.

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

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

* This source code was highlighted with Source Code Highlighter.

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