16.4. A set of operators to manipulate data Cursor, fetch, delete and update. example cursor for mysql

The SQL / 89 standard defines a very limited set of data manipulation statements. They can be classified into groups of operators associated with the cursor; single data manipulation operators; and transaction completion statements. All these statements can be used both in SQL modules and in embedded SQL. Note that SQL / 89 does not define a set of interactive SQL statements.

16.4.1. Cursor-related operators

The operators of this group are united by the fact that they all work with a certain cursor, whose declaration must be contained in the same module or program with embedded SQL.

Cursor declaration operator

For convenience, we will repeat here the syntax for the declaration of the cursor, given earlier:

  <declare cursor> :: =
    DECLARE <cursor name> CURSOR FOR <cursor specification>
 <cursor specification> :: =
   <query expression> [<order by clause> ...]
 <query expression> :: =
   <query term>
 |  <query expression> UNION [ALL] <query term>
 <query term> :: = <query specification> |  (<query expression>)
 <order by clause> :: =
    ORDER BY <sort specification>
   [{, <sort specification>} ...]
 <sort specification> :: =
 {<unsigned integer> |  <column specification>}
   [ASC |  DESC] 

In the declaration of the cursor, you can specify queries of the most general form with the possibility of performing a UNION operation and sorting the final result. This statement is not executable; it only associates the name of the cursor with the specification of the cursor.

Cursor opening operator

The statement is described by the following syntax:

  <open statement> :: = OPEN <cursor name> 

In embedded SQL implementations, it is usually required that the cursor declaration is textually preceded by the cursor opening operator. The cursor opening operator must be the first in a series of executable statements associated with a given cursor. When this operator is executed, the cursor is prepared to work on it. In particular, at this moment, the specification of the cursor is bound to the values ​​of the variables of the main language in the case of embedded SQL or parameters in the case of a module.

In most implementations, in the case of embedded SQL, it is the execution of the cursor opening statement that leads to the compilation of the cursor specification.

The following statements can be executed in any order with an open cursor.

The operator of reading the next line of the cursor

The read statement syntax is as follows:

  <fetch statement> :: =
    FETCH <cursor name> INTO <fetch target list>
 <fetch target list> :: =
   <target specification> [{, <target specification>} ...] 

The read statement indicates the cursor name and the required INTO section containing a list of assignment specifications (a list of variable names of the main program in the case of embedded SQL or names of output parameters in the case of an SQL module). The number and data types in the assignment list must match the number and data types of the cursor specification selection list.

Any open cursor always has a position: it can be set before a certain row of the resulting table (before the first row immediately after opening the cursor), on a certain row of the result or behind the last row of the result.

If the table pointed to by the cursor is empty, or the cursor is positioned on the last row or behind it, then when the read statement is executed, the cursor is positioned after the last row, the value SQLCODE is set to 100, no values ​​are assigned to the targets identified in the INTO section .

If the cursor is positioned before the line, then it is set to this line, and the values ​​of this line are assigned to the corresponding goals.

If the cursor is on a line r other than the last line, then the cursor is set on the line immediately following the line r, and the values ​​from this next line are assigned to the corresponding goals.

A natural question arises as to how you can parameterize the cursor with an undefined value or find out that the value selected from the next line is undefined. In SQL / 89, this is achieved through the use of so-called indicator parameters and variables. If it is known that the value transmitted from the main DBMS program or received by the main program from the DBMS may be undefined, and this fact is of interest to the application programmer, then the specification of a parameter or variable in the SQL statement is: <parameter name> [INDICATOR] <parameter name > when specifying a parameter, and <embedded variable name> [INDICATOR] <embedded variable name> when specifying a variable. A negative value of an indicator parameter or indicator variable (they must be of integer type) corresponds to an undefined value of the parameter or variable.

Positional delete operator

The syntax of this statement is as follows:

  <delete statement: positioned> :: =
    DELETE FROM <table name>
    WHERE CURRENT OF <cursor name> 

If the cursor specified in the statement is opened and set to a certain row, and the cursor determines the table to be changed, the current cursor row is deleted, and it is positioned before the next row. The table specified in the FROM section of the DELETE statement must be the table specified in the outermost FROM section of the cursor specification.

Positional modification operator

The operator is described by the following syntax rules:

  <update statement: positioned> :: =
    UPDATE <table name>
    SET <set clause: positioned>
 [{, <set clause: positioned>} ...]
    WHERE CURRENT OF <cursor name>
 <set clause: positioned> :: =
   <object column: positioned> =
  {<value expression> |  Null}
 <object column: positioned> :: = <column name> 

If the cursor specified in the statement is opened and set to a certain row, and the cursor determines the table being changed, then the current cursor row is modified in accordance with the SET section. The position of the cursor does not change. The table specified in the FROM section of the DELETE statement must be the table specified in the outermost FROM section of the cursor specification.

Cursor close operator

The syntax of this statement is as follows:

  <close statement> :: = CLOSE <cursor name> 

If by the time this statement is executed, the cursor is in the open state, then the operator moves the cursor to the closed state. After that, only the OPEN operator can be executed on the cursor.

example MYSQL Cursor

The official documentation here is dev.mysql.com/doc/refman/5.1/en/cursors.html

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 leaving 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 .

16.4.2. Single data manipulation operators

Each of the operators of this group is completely independent of any other operator.

Sampling operator

For convenience, we repeat the syntax of this statement again:

  <select statement> :: =
    SELECT [ALL |  DISTINCT] <select name>
    INTO <select target list> <table expression>
 <select target list> :: =
   <target specification>
  [{, <target specification>} ...] 

Since, as we have already explained, the result of a single selector is a table consisting of no more than one row, the list of targets is specified in the statement itself.

Search Deletion Operator

The statement is described by the following syntax:

  <delete statement: searched> :: =
    DELETE FROM <table name>
    WHERE [<search condition>] 

Table T specified in the FROM clause of the DELETE statement must be mutable. The search condition is superimposed on the condition that table T should not be referenced in any nested subquery of the predicates of the WHERE clause.

In fact, the statement is performed as follows: all rows of table T are sequentially scanned, and those rows for which the result of the evaluation of the selection condition is true are deleted from table T. In the absence of the WHERE clause, all rows of table T are deleted.

Search Modification Operator

The statement has the following syntax:

  <update statement: searched> :: =
    UPDATE <table name>
    SET <set clause: searched>
  [{, <set clause: searched>} ...]
   [WHERE <search conditions>]
 <set clause: searched> :: =
   <object column: searched> =
 {<value expression> |  Null}
 <object column: searched> :: = <column name> 

Table T specified in the UPDATE statement must be mutable. The search condition is superimposed on the condition that table T should not be referenced in any nested subquery of the predicates of the WHERE clause.

The statement is actually executed as follows: table T is sequentially scanned, and each row for which the result of evaluating the search condition is true changes according to the SET section. If the arithmetic expression in the SET section contains references to the columns of table T, then the values ​​of the columns of the current row are used in the calculation of the arithmetic expression before they are modified.

Transaction end operators

The current transaction can be completed successfully (with a committed change in the database) by executing a COMMIT WORK statement or abnormally (by removing from the database changes made by the current transaction) by performing the ROLLBACK WORK statement. When executing any of these statements, all cursors that are open by the time the statement completes the transaction are closed.