Cursors in Mysql.

On duty, I had to deal with cursors. I would like to tell you what it is and about some features of working with them. The official documentation here is dev.mysql.com/doc/refman/5.1/en/cursors.html Wikipedia gives this definition a cursor cursor :

The cursor is a link to the context memory area. In some implementations of the SQL programming language (Oracle, Microsoft SQL Server), the result set obtained when the query is executed and the pointer to the current record associated with it. I would say that the cursor is a virtual table which is an alternative data storage. At the same time, the cursor allows you to access your data as if it were a regular array.
Cursors are used in stored procedures. Enough theory let's look at an example:
We have a database (the database is a bit not good, this is one of my labs, but our database teacher insisted on this structure)

/* */
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 .

Suppose we need to receive each bank in turn and perform some actions with it, such a request could help us in this

Select `bank`.* FROM `bank` LIMIT ___,1


. Thus, using LIMIT NUMBER_NULL_NAM_ RECORDING, 1 we extract in cycle from the bank table each record in turn and perform the necessary actions with it, while increasing the value of NUMBER_NEW_NUM_RESIGNATION by 1. Now we will do the same but using the cursor

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 .

We now explain in more detail. First HANDLER, it is needed to handle the exception - what to do when the data runs out (i.e., the cursor will be empty). Thus, when the data runs out, the error message is not generated, and the value of the done variable is set to 1, initially done = 0; read more about SQLSTATE here - 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 fires when the end of the cursor is reached, or when a select or update returns an empty string.

The following line we declared the cursor DECLARE cursor_name CURSOR FOR select_statement;
Open the cursor Open cursor_name;
Further until we reach the end of the cursor (WHILE done = 0 DO), we retrieve the data and process it.
Before exiting the stored procedure, you must close the cursor. Close cursor_name;

It seems nothing complicated. But with SQLSTATE '02000' a lot of pitfalls are connected.

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 .

Everything is good and correct in terms of syntax. But from a logical point of view, no. It may happen that depositors did not open accounts in a bank, then for Select (ContributeAmount) INTO vContributeAmountSUM FROM bankdistribution where BankId = vBankId limit 1; SQLSTATE: 02000 will work, the done variable will be set to 1, and the while loop will end earlier than we expected. This can be avoided by doing the following:

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 .

with the first request, we checked whether there are deposits (if they are not there, then vContributeAmountSUM == 0) and only if we have any, we retrieve the data.

level up

Now let's say we need to heal the total amount on accounts in different banks for each client
Client SummCursor 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 .

the same situation may occur when the data in the ClientSummCursor cursor expire earlier than the data in BankCursor, SQLSTATE works: 02000, the done variable is set to 1, and the while loop ends earlier than we expected. This can be avoided by doing the following:

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 .

Thanks to all who read this place, I hope this will seem useful to someone.