16.4. Набор операторов манипулирования данными Курсор, выборка, удаление и обновление. пример курсора для Mysql

Привет, мой друг, тебе интересно узнать все про набор операторов манипулирования данными курсор выборка удаление, тогда с вдохновением прочти до конца. Для того чтобы лучше понимать что такое набор операторов манипулирования данными курсор выборка удаление, обновление курсора для mysql , настоятельно рекомендую прочитать все из категории IBM System R — реляционная СУБД.

В стандарте SQL/89 определен очень ограниченный набор операторов манипулирования данными. Их можно классифицировать на группы операторов, связанных с курсором; одиночных операторов манипулирования данными; и операторов завершения транзакции. Все эти операторы можно использовать как в модулях SQL, так и во встроенном SQL. Заметим, что в SQL/89 не определен набор операторов интерактивного SQL.

16.4.1. Операторы, связанные с курсором

Операторы этой группы объединяет то, что все они работают с некоторым курсором, объявление которого должно содержаться в том же модуле или программе со встроенным SQL.

Оператор объявления курсора

Для удобства мы повторим здесь синтаксические правила объявления курсора, приводившиеся раньше:

<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]

В объявлении курсора могут задаваться запросы наиболее общего вида с возможностью выполнения операции UNION и сортировкой конечного результата. Этот оператор не является выполняемым, он только связывает имя курсора со спецификацией курсора.

Оператор открытия курсора

Оператор описывается следующим синтаксическим правилом:

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

В реализациях встроенного SQL обычно требуется, чтобы объявление курсора текстуально предшествовало оператору открытия курсора. Оператор открытия курсора должен быть первым в серии выполняемых операторов, связанных с заданным курсором. При выполнении этого оператора производится подготовка курсора к работе над ним. В частности, в этот момент производится связывание спецификации курсора со значениями переменных основного языка в случае встроенного SQL или параметров в случае модуля.

В большинстве реализаций в случае встроенного SQL именно выполнение оператора открытия курсора приводит к компиляции спецификации курсора.

Следующие операторы можно выполнять в произвольном порядке над открытым курсором.

Оператор чтения очередной строки курсора

Синтаксис оператора чтения следующий:

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

В операторе чтения указывается имя курсора и обязательный раздел INTO, содержащий список спецификаций назначения (список имен переменных основной программы в случае встроенного SQL или имен "выходных" параметров в случае модуля SQL). Число и типы данных в списке назначений должны совпадать с числом и типами данных списка выборки спецификации курсора.

Любой открытый курсор всегда имеет позицию: он может быть установлен перед некоторой строкой результирующей таблицы (перед первой строкой сразу после открытия курсора), на некоторую строку результата или за последней строкой результата.

Если таблица, на которую указывает курсор, является пустой, или курсор позиционирован на последнюю строку или за ней, то при выполнении оператора чтения курсор устанавливается в позицию после последней строки, параметру SQLCODE присваивается значение 100, никакие значения не присваиваются целям, идентифицированным в разделе INTO.

Если курсор установлен в позицию перед строкой, то он устанавливается на эту строку, и значения этой строки присваиваются соответствующим целям.

Если курсор установлен на строку r, отличную от последней строки, то курсор устанавливается на строку, непосредственно следующую за строкой r, и значения из этой следующей строки присваиваются соответствующим целям.

Возникает естественный вопрос, каким образом можно параметризовать курсор неопределенным значением или узнать, что выбранное из очередной строки значение является неопределенным. В SQL/89 это достигается за счет использования так называемых индикаторных параметров и переменных. Если известно, что значение, передаваемое из основной программы СУБД или принимаемое основной программой от СУБД, может быть неопределенным, и этот факт интересует прикладного программиста, то спецификация параметра или переменной в операторе SQL имеет вид: <parameter name>[INDICATOR]<parameter name> при спецификации параметра, и <embedded variable name>[INDICATOR]<embedded variable name> при спецификации переменной. Отрицательное значение индикаторного параметра или индикаторной переменной (они должны быть целого типа) соответствует неопределенному значению параметра или переменной.

Оператор позиционного удаления

Синтаксис этого оператора следующий:

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

Если указанный в операторе курсор открыт и установлен на некоторую строку, и курсор определяет изменяемую таблицу, то текущая строка курсора удаляется, а он позиционируется перед следующей строкой. Таблица, указанная в разделе FROM оператора DELETE, должна быть таблицей, указанной в самом внешнем разделе FROM спецификации курсора.

Оператор позиционной модификации

Оператор описывается следующими синтаксическими правилами:

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

Если указанный в операторе курсор открыт и установлен на некоторую строку, и курсор определяет изменяемую таблицу, то текущая строка курсора модифицируется в соответствии с разделом SET. Позиция курсора не изменяется. Таблица, указанная в разделе FROM оператора DELETE, должна быть таблицей, указанной в самом внешнем разделе FROM спецификации курсора.

Оператор закрытия курсора

Синтаксис этого оператора следующий:

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

Если к моменту выполнения этого оператора курсор находился в открытом состоянии, то оператор переводит курсор в закрытое состояние. После этого над курсором возможно выполнение только оператора OPEN.

пример MYSQL Cursor 

Официальная документация тут — 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


. Об этом говорит сайт https://intellect.icu . Таким образом используя 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 Selectsum(`bankdistribution`.`ContributeAmount`),`bankdistribution`.`ClientId` FROM`bankdistribution` Inner Join client on (client.ClientId = bankdistribution.`ClientId`) where 1group 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.

16.4.2. Одиночные операторы манипулирования данными

Каждый из операторов этой группы является абсолютно независимым от какого бы то ни было другого оператора.

Оператор выборки

Для удобства мы повторяем синтаксис этого оператора еще раз:

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

Поскольку, как мы уже объясняли, результатом одиночного оператора выборки является таблица, состоящая не более, чем из одной строки, список целей специфицируется в самом операторе.

Оператор поискового удаления

Оператор описывается следующим синтаксическим правилом:

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

Таблица T, указанная в разделе FROM оператора DELETE, должна быть изменяемой. На условие поиска накладывается то условие, что на таблицу T не должны содержаться ссылки ни в каком вложенном подзапросе предикатов раздела WHERE.

Фактически оператор выполняется следующим образом: последовательно просматриваются все строки таблицы T, и те строки, для которых результатом вычисления условия выборки является true, удаляются из таблицы T. При отсутствии раздела WHERE удаляются все строки таблицы T.

Оператор поисковой модификации

Оператор обладает следующим синтаксисом:

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

Таблица T, указанная в операторе UPDATE, должна быть изменяемой. На условие поиска накладывается то условие, что на таблицу T не должны содержаться ссылки ни в каком вложенном подзапросе предикатов раздела WHERE.

Оператор фактически выполняется следующим образом: таблица T последовательно просматривается, и каждая строка, для которой результатом вычисления условия поиска является true, изменяется в соответствии с разделом SET. Если арифметическое выражение в разделе SET содержит ссылки на столбцы таблицы T, то при вычислении арифметического выражения используются значения столбцов текущей строки до их модификации.

Операторы окончания транзакции

Текущая транзакция может быть завершена успешно (с фиксацией в базе данных произведенных изменений) путем выполнения оператора COMMIT WORK или аварийно (с удалением из базы данных изменений, произведенных текущей транзакцией) путем выполнения оператора ROLLBACK WORK. При выполнении любого из этих операторов производится принудительное закрытие всех курсоров, открытых к моменту выполнения оператора завершения транзакции.

Напиши свое отношение про набор операторов манипулирования данными курсор выборка удаление. Это меня вдохновит писать для тебя всё больше и больше интересного. Спасибо Надеюсь, что теперь ты понял что такое набор операторов манипулирования данными курсор выборка удаление, обновление курсора для mysql и для чего все это нужно, а если не понял, или есть замечания, то не стесняйся, пиши или спрашивай в комментариях, с удовольствием отвечу. Для того чтобы глубже понять настоятельно рекомендую изучить всю информацию из категории IBM System R — реляционная СУБД