Язык манипулирования данными

5. Язык манипулирования данными ЛЕКЦИЯ № 14 5. ЯЗЫК МАНИПУЛИРОВАНИЯ ДАННЫМИ.............................................1 5.1. Добавление новых данных........................................1 5.1.1. Однострочный запрос INSERT.........................5 5.1.2. Многострочный запрос INSERT.......................7 5.2. Обновление существующих данных......................10 5.2.1. Неявный запрос UPDATE...............................14 5.2.2. Запрос UPDATE с подзапросом......................16 5.3. Обобщенное обновление и добавление данных....24 5. ЯЗЫК МАНИПУЛИРОВАНИЯ ДАННЫМИ В гл. 4 были изучены возможности SQL по определению структур таблиц и представлений БД. В настоящей главе рассматривается, каким образом осуществляется манипулирование данными в БД. Изменение данных в БД выполняется с помощью запросов языка манипулирования данными или DML (Data Manipulation Language). В SQL СУБД Firebird существуют следующие запросы на изменение данных:  INSERT (вставка данных);  UPDATE (обновление существующих данных);  UPDATE OR INSERT (обновление или вставка данных);  MERGE (обновление или вставка данных таблицы на основе строк, отобранных из другой таблицы);  DELETE (удаление данных). Запросы DML иногда называют запросами действия (action queries), поскольку они изменяют информацию, записанную в таблицах [45]. При внесении изменений в данные объектов БД следует учитывать правила ссылочной целостности. Эти правила влияют на изменение (запрет изменения) первичных и внешних ключей связанных объектов. К составлению запросов DML следует относиться внимательно, так как запросы-действия изменяют данные таблиц, и при неправильной формулировке эти изменения могут нанести непоправимый урон и привести к потере данных. Использование вышеуказанных запросов по отношению к базовым таблицам является предметом данной главы. Также в этой главе приводится описание правил манипулирования данными обновляемых представлений. 5.1. ДОБАВЛЕНИЕ НОВЫХ ДАННЫХ Добавление данных в определенную таблицу или представление БД осуществляется с помощью запроса-действия INSERT. Существует 2 вида этого запроса: однострочный и многострочный. 1 5. Язык манипулирования данными Независимо от того, какая СУБД используется, запрос INSERT можно представить в общем виде. Указывается таблица, список столбцов и список добавляемых в эти столбцы значений. Однострочный запрос выглядит так: INSERT INTO <таблица> [(<список столбцов>)] VALUES (<список значений>);, а многострочный запрос содержит подзапрос SELECT: INSERT INTO <таблица> [(<список столбцов>)] (SELECT <список значений> FROM <откуда> WHERE <список условий>); . В случае многострочного запроса в указанную таблицу (в перечисленные через запятую столбцы) выполняется копирование выбранного с помощью запроса SELECT массива данных. Естественно, что количество и тип столбцов в запросе SELECT должны соответствовать количеству и типу столбцов таблицы, в которую осуществляется вставка. В Firebird запрос INSERT можно представить в обобщенном виде: INSERT INTO { базовая_таблица | представление} [(столбец1 [, столбец2] ...)] {VALUES ({значение1 | переменная1 | NULL| (<скалярный_подзапрос1>)} [,{значение2 | переменная2 | NULL| (<скалярный_подзапрос2>)}]…) |<запрос_SELECT> | DEFAULT VALUES } [RETURNING <список_столбцов> [INTO <список_переменных>]];, где <значение>::= { столбец | константа | <выражение> | функция}. Секция INTO определяет таблицу или представление и столбцы, в которые будут вставлены строки. В секции VALUES задаются значения, которые будут присвоены столбцам добавляемых строк. В качестве значения могут использоваться и контекстные переменные (например, CURRENT_USER в контексте текущего соединения клиента). Также можно установить для столбца NULL-маркер. При однократном использовании однострочного запроса INSERT в таблицу или представление можно вставить только одну строку. Если применить запрос INSERT с вложенным запросом SELECT (многострочный запрос INSERT), то с помощью одного запроса можно вставить в таблицу или представление сразу несколько строк из другой таблицы. Секция DEFAULT VALUES позволяет вставлять строки вообще без указания значения – ни напрямую, ни через запрос SELECT. Это возможно только, если каждый столбец таблицы с проверкой NOT NULL или CHECK имеет объявленное значение по умолчанию или получает значение в триггере до вставки (BEFORE INSERT). Секция RETURNING позволяет запоминать значения необходимых столбцов в изменяемой строке. Её использование во всех запросах модификации данных (INSERT, UPDATE OR INSERT, UPDATE, DELETE, MERGE) будет рассмотрено при изучении процедурного языка (см. п. 6.2.4). Реализация запроса INSERT в Oracle позволяет вставлять данные в указанную таблицу, представление, раздел, подраздел или объектную таблицу, а также поддерживаются дополнительные расширения, такие, как 2 5. Язык манипулирования данными вставка строк сразу в несколько таблиц и вставка по условию [46, 47]. Синтаксис стандартного запроса имеет вид: INSERT [INTO] <целевой_объект> [псевдоним] [(столбец1 [, столбец2] ...)] {VALUES ( {выражение1 | DEFAULT} [,{выражение2 | DEFAULT}]…) | <запрос_SELECT>} [RETURNING <список_столбцов> [INTO <список_переменных>]];, где <целевой_объект> :: = { таблица [[SUB]PARTITION (имя_раздела)] | представление | (<запрос_SELECT> [WITH {READ ONLY | CHECK OPTION [CONSTRAINT имя_ограничения]}]) | TABLE (коллекция) [(+)] }. Синтаксис запроса INSERT с условием: INSERT {[ALL | FIRST]} WHEN <условие1> THEN INTO <целевой_объект1> [псевдоним] [(столбец1 [, столбец2] ...)] VALUES ( {выражение1 | DEFAULT} [,{выражение2 | DEFAULT}]…) [WHEN <условие2> THEN INTO <целевой_объект2> [псевдоним] [(столбец1 [, столбец2]...)] VALUES ( {выражение1 | DEFAULT} [,{выражение2 | DEFAULT}]…) ]… ELSE INTO <целевой_объект3> [псевдоним] [(столбец1 [, столбец2] ...)] VALUES ( {выражение1 | DEFAULT} [,{выражение2 | DEFAULT}]…) <запрос_SELECT>; Секция INTO определяет целевой объект, его псевдоним и столбцы, куда будут вставлены строки. Если строки вставляются в секционированную таблицу Oracle (см. п. 2.5.4), то может быть указан раздел или подраздел в конструкции PARTITION или SUBPARTITION соответственно. Один из способов реализации многотабличной вставки в Oracle – указание в секции INTO подзапроса SELECT. Все столбцы, определяемые в подзапросе, во всех его таблицах, должны иметь соответствующее значение для вставки, в противном случае возникнет ошибка. По сути, с помощью подзапроса создается представление «на ходу», и запрос вставки данных в подзапрос аналогичен запросу вставки данных в представление. Опция WITH READ ONLY показывает, что таблицу, к которой обращается подзапрос, нельзя обновлять до его завершения. Опция WITH CHECK OPTION [CONSTRAINT имя_ограничения] показывает, что в таблицу или представление нельзя вставлять строки, не удовлетворяющие указанному проверочному (CHECK) ограничению. Платформа Oracle позволяет работать с объектными таблицами, которые допускают хранение неатомарных (нескалярных) значений в полях, а именно объекта в смысле объектного подхода. Для вставки данных в такие таблицы используется конструкция TABLE (коллекция) [(+)]. 3 5. Язык манипулирования данными Поскольку определение целевого объекта для вставки данных в секции INTO может быть очень длинным, есть возможность дополнительно указывать псевдонимы. Псевдонимы нельзя использовать при многотабличной вставке. Также при многотабличной вставке не используется секция RETURNING. Секция VALUES предполагает определение вставляемых значений в виде выражения, которое может включать константы, переменные либо представлять собой скалярный подзапрос SELECT, или в виде DEFAULT (значения по умолчанию для столбца). Реализация запроса INSERT с условием позволяет выполнять в Oracle операции многотабличной вставки данных (в несколько таблиц) по заданному условию. Опция ALL применяется только с подзапросами. Если не указана секция WHEN, все данные, возвращаемые подзапросом, без всяких условий вставляются в указанные таблицы. При использовании опции ALL с секцией WHEN выполняются условные операции вставки, и все секции WHEN проверяются независимо от результатов других операций WHEN. Если условие WHEN возвращает значение true, Oracle выполняет соответствующую секцию INTO. Многотабличную вставку по условию нельзя сделать параллельной в таблицах с индексом на основе битовых карт. Такая вставка вообще не разрешена в следующих ситуациях: - целью является представление или материализованное представление; - целью является таблица на удаленном сервере; - в INSERТ используется выражение, в котором содержится коллекция таблиц; - в таблице требуется произвести вставку более чем в 999 столбцов; - в подзапросе используется последовательность. Таким образом, возможности запроса INSERT в Oracle значительно шире, чем в Firebird. В MS SQL Server запрос на вставку данных имеет такой формат [23]: [ <предложение обобщенного табличного выражения> ] INSERT [ TOP (<выражение>) [ PERCENT ] ] [ INTO ] { [<сервер>.][[<база данных>.]<схема>.] { <таблица> | <представление> } | { <функция OPENQUERY> | <функция OPENROWSET> } } [ WITH (<подсказка таблицы> [, <подсказка таблицы>...]) ] [ (<список столбцов>) ] [ <секция_OUTPUT> ] { VALUES ( { DEFAULT | NULL | <выражение> } [, { DEFAULT | NULL | <выражение> }]...) [, ( { DEFAULT | NULL | <выражение> } [, { DEFAULT | NULL | <выражение> }]...)]... | <запрос_SELECT> | <оператор_EXECUTE> | DEFAULT VALUES};, где 4 5. Язык манипулирования данными <предложение обобщенного табличного выражения> ::= WITH <обобщенное табличное выражение> [, <обобщенное табличное выражение>] ... ; <обобщенное табличное выражение> ::= <имя_выражения> [ (<столбец1> [, <столбец2>] ...) ] AS (<запрос_SELECT>). Обобщенное табличное выражение задает временный именованный набор данных, по этому имени к набору данных можно обращаться в запросе INSERT. Набор данных является временным, он существует только в процессе выполнения запроса на вставку данных. Обобщенные табличные выражения были рассмотрены ранее в п. 3.3.2.2 при изучении подзапросов в секции WITH. Опция TOP позволяет указать количество либо процент (ключевое слово PERCENT) добавляемых строк, тем самым ограничивая количество вставляемых запросом INSERT данных. После необязательного ключевого слова INTO указывается, куда именно добавляются строки. Особенностью MS SQL Server является то, что запрос INSERT позволяет добавлять данные не только в таблицы БД, принадлежащей текущему экземпляру сервера, но также в таблицы баз данных, находящихся в других экземплярах сервера и даже в других СУБД при задании имени связанного сервера или при использовании функций OPENQUERY и OPENROWSET. При использовании запроса INSERT в процедурном языке в качестве таблицы может быть указана заранее объявленная табличная переменная (см. п. 6.1.2). Необязательная секция OUTPUT может использоваться для возврата данных всех строк, изменившихся в результате выполнения запроса. Позволяет поместить все добавленные запросом строки либо в локальную переменную с типом данных TABLE, либо в указанную таблицу БД (обычную или временную). Сами значения, помещаемые в столбцы новой строки таблицы, могут быть заданы одним из четырех способов: - в секции VALUES с перечислением значений; - с помощью запроса SELECT, который возвращает одну или более строк таблицы; - при вызове хранимой процедуры оператором EXECUTE; - присваиванием значений по умолчанию всем столбцам (DEFAULT VALUES). В случае задания секции VALUES можно в одном запросе добавлять множество строк, повторяя через запятую список значений в скобках. 5.1.1. Однострочный запрос INSERT Формат однострочного запроса-действия INSERT в Firebird имеет вид: INSERT INTO { базовая_таблица | представление} [(столбец1 [, столбец2] ...)] {VALUES ({значение1 | переменная1 | NULL| (<скалярный_подзапрос1>)} [,{значение2 | переменная2 | NULL| (<скалярный_подзапрос2>)}]…) | DEFAULT VALUES } 5 5. Язык манипулирования данными [RETURNING <список_столбцов> [INTO <список_переменных>]]; В таблицу или представление вставляется строка со значениями столбцов, указанными в перечне секции VALUES, причем i-е значение соответствует iму столбцу в списке столбцов. Столбцы, не указанные в списке, заполняются значениями по умолчанию или NULL, если ограничение, наложенное на столбец, позволяет вставку NULL. Если допустимы NULL-маркеры и определено значение по умолчанию, вставлено будет значение по умолчанию. Если NULL недопустимы и не определено значение по умолчанию, то запрос на вставку без явного указания значения такого столбца выполнен не будет. Примечание. Если NULL недопустимы, то можно ввести, например, пустую строку для символьного столбца, однако такой подмены делать не рекомендуется. В Oracle формат однострочного запроса INSERT имеет вид: INSERT [INTO] <целевой_объект> [псевдоним] [(столбец1 [, столбец2] ...)] VALUES ( {выражение1 | DEFAULT} [,{выражение2 | DEFAULT}]…) [RETURNING <список_столбцов> [INTO <список_переменных>]]; MS SQL Server поддерживает однострочный запрос INSERT в формате: INSERT [ INTO ] { [<сервер>.][[<база данных>.]<схема>.] { <таблица> | <представление> } | { <функция OPENQUERY> | <функция OPENROWSET> } } [ WITH (<подсказка таблицы> [, <подсказка таблицы>...]) ] [ (<список столбцов>) ] [ <секция_OUTPUT> ] { VALUES ( { DEFAULT | NULL | <выражение1> } [, { DEFAULT | NULL | <выражение2> }]...) | <оператор_EXECUTE> | DEFAULT VALUES}; Рассмотрим вставку новой строки в таблицу при полном указании списка ее столбцов. Например, для вставки строки в таблицу Abonent можно использовать запрос: INSERT INTO Abonent (AccountCD, StreetCD, HouseNo, FlatNo, Fio, Phone) VALUES ('500000', 8, 1, 1, 'Плитов Е.Д.', '556787'); Если в списке опции VALUES указаны значения для всех столбцов модифицируемой таблицы и порядок их перечисления соответствует порядку столбцов в описании таблицы (как в приведенном примере), то список столбцов в секции INTO можно опустить. Например, предыдущий запрос на вставку строки в таблицу Abonent можно записать так: INSERT INTO Abonent VALUES ('500000', 8, 1, 1, 'Плитов Е.Д. ', '556787'); Если требуется ввести NULL-маркер, например, в столбец Phone (номер телефона абонента неизвестен), то он вводится точно так же, как и обычное значение, например: INSERT INTO Abonent VALUES ('500000', 8, 1, 1, 'Плитов Е.Д.', NULL); . В примерах значения, помещаемые в таблицу, располагаются в том порядке, в котором они были созданы запросом CREATE TABLE. Но в 6 5. Язык манипулирования данными некоторых случаях требуется поменять порядок вводимых значений или вводить значения не во все столбцы в таблице. Чтобы удовлетворить этому условию, требуется явно указывать имена и порядок столбцов. Примером этому может служить запрос INSERT INTO Abonent (AccountCD, StreetCD, Fio) VALUES ('500000', 8, 'Плитов Е.Д.'); Использовать результат скалярного подзапроса в качестве значения можно, например, так: INSERT INTO Abonent VALUES ('500000', 8, 1, 1, 'Плитов Е.Д.', (SELECT Phone FROM Abonent WHERE AccountCD='005488')); Все приведенные запросы INSERT корректно работают в любой из рассматриваемых в учебнике СУБД. 5.1.2. Многострочный запрос INSERT Многострочный запрос-действие INSERT позволяет вставить в таблицу или представление сразу несколько строк. В Firebird многострочный запрос INSERT реализуется путем задания вместо секции VALUES подзапроса SELECT, который позволяет вставить в одну таблицу или представление несколько строк из другой таблицы. Формат запроса INSERT с подзапросом SELECT в Firebird имеет вид: INSERT INTO { базовая_таблица | представление} [(столбец1 [, столбец2] ...)] <запрос_SELECT> [RETURNING <список_столбцов> [INTO <список_переменных>]]; В этом формате запроса INSERT сначала выполняется запрос SELECT, с помощью которого в памяти формируется рабочая таблица, а затем строки этой рабочей таблицы загружаются в модифицируемую таблицу или представление. При этом i-й столбец рабочей таблицы соответствует i-му столбцу в списке столбцов модифицируемой таблицы или представления. При использовании многострочного запроса INSERT требуется следить за тем, чтобы тип и количество значений, возвращаемых запросом SELECT, совпадали с типом и количеством столбцов в модифицируемой таблице. В Oracle многострочный запрос INSERT реализуется также путем использования подзапроса SELECT: INSERT [INTO] <целевой_объект> [псевдоним] [(столбец1 [, столбец2] ...)] <запрос_SELECT> [RETURNING <список_столбцов> [INTO <список_переменных>]]; Помимо этого, запрос с условием в Oracle (синтаксис приведен был ранее в п. 5.1) – это также многострочный INSERT. В MS SQL Server многострочный запрос INSERT может быть реализован не только путем использования подзапроса SELECT вместо секции VALUES, но также путем использования подзапроса как обобщенного табличного выражения или путем множественного задания списка значений в секции VALUES. Формат многострочного запроса в MS SQL Server: [ <предложение обобщенного табличного выражения> ] INSERT [ TOP (<выражение>) [ PERCENT ] ] 7 5. Язык манипулирования данными [ INTO ] { [<сервер>.][[<база данных>.]<схема>.] { <таблица> | <представление> } | { <функция OPENQUERY> | <функция OPENROWSET> } } [ WITH (<подсказка таблицы> [, <подсказка таблицы>...]) ] [ (<список столбцов>) ] [ <секция_OUTPUT> ] { VALUES ( { DEFAULT | NULL | <выражение> } [, { DEFAULT | NULL | <выражение> }]...), ( { DEFAULT | NULL | <выражение> } [, { DEFAULT | NULL | <выражение> }]...)... | <запрос_SELECT>}; Рассмотрим примеры использования запросов INSERT с подзапросом SELECT в Firebird. Пусть имеется пустая таблица Fio со столбцами Abonent_Name и Executor_Name, определенными на типе данных VARCHAR(20). В эту таблицу необходимо поместить ФИО абонентов и исполнителей каждой из ремонтных заявок. Для этого можно применить запрос: INSERT INTO Fio (Abonent_Name, Executor_Name) SELECT A.Fio, E.Fio FROM Abonent A, Executor E, Request R WHERE R.AccountCD = A.AccountCD AND R.ExecutorCD = E.ExecutorCD; В секции FROM запроса SELECT может быть указана не базовая таблица, а представление. Таким образом, можно вставлять данные из представления в базовую таблицу. Например, если ранее было создано представление Abonent_Executor со столбцами Abonent_Name и Executor_Name, то предыдущий запрос можно переписать: INSERT INTO Fio SELECT Abonent_Name, Executor_Name FROM Abonent_Executor; Содержание таблицы Fio после вставки будет совпадать с данными, приведенными ранее на рис. 4.10, так как фактически в таблицу Fio внесены все данные ранее созданного представления Abonent_Executor. Приведенные выше запросы INSERT в Oracle и MS SQL Server реализуются аналогично. В подзапросах, используемых в многострочном запросе INSERT, можно использовать оператор UNION. Пусть имеется таблица Phone со столбцами Abonent_Fio, Old_Tel и New_Tel, определенными на типе данных соответственно VARCHAR(20), VARCHAR(15) и VARCHAR(15). Известно, что номера телефонов, которые начинались с «25», теперь начинаются с «14», а «68» сменились на «57». В таблицу Phone необходимо поместить ФИО абонентов, у которых изменился номер телефона, а также старые и новые номера телефонов (рис. 5.1). Для этого можно применить запрос: INSERT INTO Phone (Abonent_Fio, Old_Tel, New_Tel) SELECT Fio, Phone, '14' || TRIM (LEADING '25' FROM Phone) FROM Abonent WHERE SUBSTRING(Phone FROM 1 FOR 2)= '25' UNION SELECT Fio, Phone, '57' || TRIM (LEADING '68' FROM Phone) FROM Abonent WHERE SUBSTRING(Phone FROM 1 FOR 2)= '68'; 8 5. Язык манипулирования данными Данный запрос в Oracle и MS SQL Server реализуется аналогично, с использованием функций, свойственных для конкретной СУБД. Например, в Oracle: INSERT INTO Phone (Abonent_Fio, Old_Tel, New_Tel) SELECT Fio, Phone, '14' || LTRIM (PHONE,'25') FROM Abonent WHERE SUBSTR(PHONE,1,2)= '25' UNION SELECT Fio, Phone, '57' || LTRIM (PHONE,'68') FROM Abonent WHERE SUBSTR(PHONE,1,2)= '68'; Abonent_Fio Old_Tel New_Tel Денисова Е.К. 680305 570305 Лукашина Р.М. 254417 144417 Маркова В.П. 683301 573301 Стародубцев Е.В. 683014 573014 Шубина Т. П. 257842 147842 Рис. 5.1. Таблица Phone после вставки Ограничения 1. В подзапросе SELECT не следует использовать секцию ORDER BY, поскольку нeцелесообразно сортировать таблицу результатов запроса, так как она добавляется в таблицу, которая, как и все остальные, не упорядочена. 2. В качестве значений, возвращаемых подзапросом SELECT, можно использовать агрегатные функции, но при этом нужно обеспечить совпадение типа и количества значений, возвращаемых запросом. 3. В секции FROM подзапроса SELECT нельзя использовать ту же самую таблицу, в которую запросом INSERT производится вставка строк. Рассмотрим использование запроса INSERT с условием в Oracle. Пусть имеются три таблицы SmallNach, MediumNach, BigNach с набором столбцов, идентичным столбцам таблицы NachislSumma. Допустим, необходимо разделить все начисления из таблицы NachislSumma по величине начисленных сумм в три эти таблицы, а также увеличить год начисления на 1 для данных, вставляемых в таблицу BigNach. Запрос с условием можно записать так: INSERT ALL WHEN NachislSum < 100 THEN INTO SMALLNACH VALUES (NachislFactCD, AccountCD, ServiceCD, NachislSum, NachislMonth, NachislYear) WHEN NachislSum > = 100 AND NachislSum < 500 THEN INTO MEDIUMNACH VALUES (NachislFactCD, AccountCD, ServiceCD, NachislSum, NachislMonth, NachislYear) 9 5. Язык манипулирования данными ELSE INTO BigNach VALUES (NachislFactCD, AccountCD, ServiceCD, NachislSum, NachislMonth, NachislYear + 1) SELECT NachislFactCD, AccountCD, ServiceCD, NachislSum, NachislMonth, NachislYear FROM NachislSumma; Содержание таблицы BigNach после вставки представлено на рис. 5.2. NACHISLFA ACCOUN SERVIC NACHISL NACHISLM NACHISL CTCD TCD ECD SUM ONTH YEAR 66 015527 4 580,1 7 2013 67 015527 4 611,3 10 2014 71 115705 4 553,85 1 2013 75 136169 4 528,44 10 2014 79 443690 4 500,13 9 2013 Рис. 5.2. Результат выполнения запроса INSERT с условием в Oracle Рассмотрим пример множественного задания списка значений в секции VALUES запроса INSERT в MS SQL Server. Допустим, необходимо добавить сразу несколько строк в таблицу Abonent. В MS SQL Server для этого не требуется создавать несколько запросов INSERT, можно добавить все строки одним запросом: INSERT INTO Abonent VALUES ('500010', 8, 1, 5, 'Иванов В.И.', '551189'), ('500011', 8, 1, 6, 'Воськина Л.Г. ', NULL), ('500012', 8, 1, 7, 'Мартынов И.Р. ', '550303'), ('500013', 8, 1, 8, 'Федькин А.А.', '551102'); 5.2. ОБНОВЛЕНИЕ СУЩЕСТВУЮЩИХ ДАННЫХ Обновление значений как отдельных строк, так и всей таблицы или представления БД осуществляется запросом действия UPDATE. В общем виде запрос UPDATE указывает, где и что необходимо изменить, а также критерии отбора обновляемых строк: UPDATE <таблица> SET {<поле> = <выражение>} [WHERE <список условий>]; или UPDATE <таблица> SET {<поле> = (SELECT <значение> FROM <откуда> WHERE <список условий>)} [WHERE <список условий>]; . Обновление с помощью запроса UPDATE может быть позиционированным (выполняется только над одной строкой) и поисковым (выполняется над нулевым или большим количеством строк). Позиционированное изменение может появиться только в контексте текущей операции с курсором в модуле на PSQL, в то время как поисковое изменение появляется во всех остальных случаях. Поисковое изменение может эмулировать позиционированное изменение, если в секции WHERE задано 10 5. Язык манипулирования данными условие, уникально определяющее строку (например, использующее первичный ключ таблицы). Запрос UPDATE может анализировать информацию из других таблиц БД, используя запрос SELECT (вложенный запрос). В Firebird обобщенный формат запроса UPDATE следующий: UPDATE { базовая_таблица | представление} [[AS] псевдоним] SET { столбец1 = {<значение1> | NULL | (<подзапрос1>)} [, столбец2 = {<значение2> | NULL | (<подзапрос2>)} ] ...} [WHERE { <условие_поиска> | CURRENT OF имя_курсора } ] [PLAN <список_пунктов_плана>] [ORDER BY <элемент_сортировки1> [, <элемент_сортировки 2>]…] [ROWS k [TO r]] [RETURNING <список_столбцов> [INTO <список_переменных>]];, где <значение>::= { столбец | константа | <выражение> | функция}. Запрос UPDATE содержит секцию SET, в которой указывается на изменение, которое нужно сделать для определенного столбца. Секция SET может включать столбцы лишь из обновляемой таблицы, т. е. значение одного или нескольких столбцов модифицируемой таблицы может быть заменено другим значением. В качестве значений могут использоваться контекстные переменные (например, в контексте текущего соединения клиента может использоваться контекстная переменная CURRENT_USER). Также можно установить для столбца NULL-маркер. Допускается использование вложенного запроса SELECT в секции SET. Секция WHERE используется для отбора изменяемых строк. Условие поиска может использовать вложенный запрос SELECT. При отсутствии секции WHERE обновляются значения указанных столбцов во всех строках модифицируемой таблицы. Секцию ORDER BY, задающую сортировку обрабатываемого набора данных, в запросе UPDATE целесообразно использовать только в сочетании с секцией ROWS, которое ограничивает количество обновляемых строк на указанное число. Запрос UPDATE, обновляющий только одну строку, может дополнительно включать секцию RETURNING для возврата значений обновляемой строки. В Oracle запрос UPDATE представлен в следующем формате: UPDATE { <целевой_объект> | ONLY (<целевой_объект>) } [псевдоним] SET { столбец1 = {<значение1> | DEFAULT | (<подзапрос1>)} [, столбец2 = {<значение2> | DEFAULT | (<подзапрос2>)} ] ... | (столбец1 [,столбец2 …]) = (<подзапрос>) | VALUE (псевдоним) = {<выражение> | (<подзапрос>)} } [WHERE <условие_поиска>] [RETURN[ING] <список_столбцов> [INTO <список_переменных>]];, где <целевой_объект> :: = 11 5. Язык манипулирования данными { таблица [[SUB]PARTITION { (имя_раздела) | FOR (ключ_раздела [, ключ_раздела …]) }] | представление | (<запрос_SELECT> [WITH {READ ONLY | CHECK OPTION} [CONSTRAINT имя_ограничения]]) | TABLE (коллекция) [(+)] }. Формат указания целевого объекта в запросе UPDATE немного отличается от аналогичного синтаксиса в запросе INSERT (см. п.5.1), если выполняется обновление секционированной таблицы. В конструкции [SUB]PARTITION можно задавать не имя раздела таблицы, а набор ключевых столбцов после слова FOR. Ключевое слово ONLY используется только при обновлении представлений в случае, если целевое представление имеет зависимые представления, а требуется обновить только указанное представление. В секции SET можно установить для столбца значение по умолчанию DEFAULT. Подзапрос в секции SET может представлять собой <скалярный_подзапрос>, если указан для обновления конкретного столбца, либо <подзапрос_столбца> (<подзапрос_строки>?), если указан для списка столбцов. Конструкция VALUE (псевдоним) в секции SET используется для обновления объектной таблицы Oracle. В MS SQL Server запрос UPDATE имеет такой синтаксис [23]: [ <предложение обобщенного табличного выражения> ] UPDATE [ TOP (<выражение>) [ PERCENT ] ] { [<сервер>.][[<база данных>.]<схема>.] { <таблица> | <представление> } | { <функция OPENQUERY> | <функция OPENROWSET> } } [ WITH (<подсказка таблицы> [, <подсказка таблицы>...]) ] SET <изменяемое значение> [, <изменяемое значение>]... [<секция_OUTPUT> ] [FROM (<таблица-источник> [, <таблица-источник>]...) ] [WHERE { <условие_поиска> | CURRENT OF имя_курсора } ] [OPTION (<подсказка запроса> [, <подсказка запроса>]...) ];, где <изменяемое значение> ::= { <столбец> = { DEFAULT | NULL | <выражение> | (подзапрос) } | <столбец>.WRITE(<выражение>, @<смещение>, @<размер>) | @<переменная> [ = <столбец> ] <операция> <выражение> }, <операция> ::= += | -= | *= | /= | %= | &= | ^= | |= | = . В MS SQL Server секция SET, в которой задаются изменения, имеет особый формат. Существует несколько вариантов указания столбцов и новых их значений: 12 5. Язык манипулирования данными - в обычном варианте после имени столбца и знака равенства записывается константа, выражение, ключевое слово DEFAULT или NULL, скалярный подзапрос SELECT; - вариант, когда после имени столбца задается обращение к функции .WRITE, может быть использован для столбцов с типом данных VARCHAR(MAX), NVARCHAR(MAX) и VARBINARY(MAX). Функция позволяет заменить данные в столбце, начиная с позиции, заданной локальной переменной @<смещение>, размером, заданным локальной переменной @<размер>, на значение выражения, указанного первым параметром функции; - в третьем варианте используются локальные переменные. Им присваиваются конкретные значения. Эти же значения могут быть присвоены и столбцам изменяемой строки таблицы, когда конструкция представлена в виде: @<переменная> [=<столбец>] <операция> <выражение>. Вначале столбцу таблицы присваивается значение, полученное в результате применения указанной операции к выражению, затем это же значение присваивается локальной переменной. Если в конструкции отсутствует имя столбца, то значение присваивается только локальной переменной. Операция может иметь следующие значения:  = (обычное присваивание значения);  += (выполняется сложение указанного значения, полученного в результате вычисления выражения, с существующим значением столбца, и результат присваивается переменной (и столбцу));  -= (из значения вычитается полученное значение, и выполняется присваивание);  *= (выполняется умножение и присваивание);  /= (выполняется деление и присваивание);  %= (получение остатка от деления и присваивание);  &= (побитовая операция И и присваивание);  ^= (побитовая операция исключающее ИЛИ и присваивание);  |= (побитовая операция ИЛИ и присваивание). Конструкция MS SQL Server @<переменная> [= <столбец>] <операция> <выражение> заменяет аналогичную конструкцию RETURNING <список_столбцов> [INTO <список_переменных>] в Firebird и Oracle. Секция OUTPUT возвращает измененные строки таблицы. Секция FROM предоставляет возможность создать для указания обновляемых строк высокоизбирательный критерий на основе соединения. Секция FROM позволяет использовать секцию JOIN, что значительно упрощает обновление строк целевой таблицы путем связывания строк из таблиц, указанных в секции FROM, со строками таблицы, обновляемой через UPDATE имя_таблицы. Секция FROM не нужна, если при указании строк используется только одна таблица – целевая. Необязательная секция OPTION содержит подсказки оптимизатору запроса. Эти подсказки оказывают влияние на порядок выборки строк таблиц. 13 5. Язык манипулирования данными 5.2.1. Неявный запрос UPDATE Неявный запрос выбирает строки для изменения без использования подзапроса в секции WHERE. Неявный запрос-действие UPDATE в Firebird имеет формат: UPDATE { базовая_таблица | представление} SET столбец1 = {<значение1> | NULL} [, столбец2 = {<значение2> | NULL} ] ... [WHERE <условие_поиска> | WHERE CURRENT OF имя_курсора] [PLAN <список_пунктов_плана>] [ORDER BY <элемент_сортировки1> [, <элемент_сортировки 2>]…] [ROWS k [TO r]] [RETURNING <список_столбцов> [INTO <список_переменных>]]; В данном случае в секции SET не используется подзапрос, секция WHERE используется для отбора изменяемых строк без использования подзапроса, т.е. неявно. В Oracle неявный запрос UPDATE имеет формат: UPDATE { <целевой_объект> | ONLY (<целевой_объект>) } [псевдоним] SET { столбец1 = {<значение1> | DEFAULT } [, столбец2 = {<значение2> | DEFAULT } ] ... | VALUE (псевдоним) = <выражение> } [WHERE <условие_поиска>] [RETURN[ING] <список_столбцов> [INTO <список_переменных>]]; В MS SQL Server неявный запрос UPDATE имеет формат: UPDATE [ TOP (<выражение>) [ PERCENT ] ] { [<сервер>.][[<база данных>.]<схема>.] { <таблица> | <представление> } | { <функция OPENQUERY> | <функция OPENROWSET> } } [WITH (<подсказка таблицы> [, <подсказка таблицы>...]) ] SET { столбец1 = {DEFAULT | NULL | <выражение>} [, столбец2 = {DEFAULT | NULL | <выражение>} ] ... | столбец1.WRITE(<выражение>, @<смещение>, @<размер>) [, столбец2.WRITE(<выражение>, @<смещение>, @<размер>) ] ... | @<переменная> [ = <столбец1> ] <операция> <выражение> [, @<переменная> [ = <столбец2> ] <операция> <выражение> ] ... } [<секция_OUTPUT> ] [FROM (<таблица-источник> [, <таблица-источник>]...) ] [WHERE { <условие_поиска> | CURRENT OF имя_курсора } ] [OPTION (<подсказка запроса> [, <подсказка запроса>]...) ]; Рассмотрим примеры работы неявного запроса-действия UPDATE. Например, чтобы установить для всех абонентов номер телефона '982223', необходимо выполнить запрос: UPDATE Abonent SET Phone = '982223'; Чтобы изменить не все строки, а лишь те, которые удовлетворяют определенным условиям, необходимо задать это условие в секции WHERE запроса UPDATE. Предположим, что абонент c ФИО Шубина Т. П. (номер лицевого счета равен '080047') вышла замуж и у нее изменилась фамилия на 14 5. Язык манипулирования данными Серова Т. П. Для внесения в таблицу Abonent соответствующих изменений нужно выполнить запрос: UPDATE Abonent SET Fio = 'Серова Т. П.' WHERE AccountCD = '080047'; Таблица Abonent после обновления представлена на рис. 5.3. ACCOU NTCD 005488 115705 015527 443690 136159 443069 136160 126112 136169 080613 080047 080270 STREE HOUS FLAT FIO PHONE TCD ENO NO 3 4 1 Аксенов С.А. 556893 3 1 82 Мищенко Е.В. 769975 3 1 65 Конюхов В.С. 761699 7 5 1 Тулупова М.И. 214833 7 39 1 Свирина З.А. NULL 4 51 55 Стародубцев Е.В. 683014 4 9 15 Шмаков С.В. NULL 4 7 11 Маркова В.П. 683301 4 7 13 Денисова Е.К. 680305 8 35 11 Лукашина Р.М. 254417 8 39 36 Серова Т. П. 257842 6 35 6 Тимошкина Н.Г. 321002 Рис. 5.3. Таблица Abonent после обновления Операция CASE может применяться не только в запросе SELECT. Она также очень полезна при изменении значений столбцов на основе условий, задаваемых полями строки, относящимися к другим столбцам. Примером такого использования операции CASE может быть запрос UPDATE NachislSumma SET NachislSum= CASE WHEN NachislYear=2013 THEN NachislSum*1.1 WHEN NachislYear=2014 THEN NachislSum*1.2 END WHERE ServiceCD=1; , изменяющий значения начислений за услугу с кодом 1 за 2013 и 2014 годы на 10 и 20 процентов соответственно. Приведенные выше неявные запросы UPDATE корректно работают в любой из рассматриваемых в учебнике СУБД. Аргумент DEFAULT в MS SQL Server и Oracle можно также использовать для изменения значения столбца на NULL, если для него не определено значение по умолчанию и разрешены признаки NULL. Например, запросом UPDATE Abonent SET Phone = DEFAULT; номера телефонов у всех абонентов становятся неопределенными. Рассмотрим ограничение количества обновляемых строк в запросе UPDATE. Допустим, необходимо установить номер телефона '982223' не для 15 5. Язык манипулирования данными всех абонентов, а только для первых 5 абонентов с наименьшим номером лицевого счета. В Firebird запрос можно записать кратко: UPDATE Abonent SET Phone = '982223' ORDER BY AccountCD ROWS 1 TO 5; MS SQL Server позволяет ограничивать количество строк, однако результат выполнения запроса вида UPDATE TOP 5 Abonent SET Phone = '982223'; не будет совпадать с результатом работы запроса в Firebird, так как MS SQL Server не позволяет задавать сортировку. Аналогичный запрос в MS SQL Server может быть реализован только с использованием подзапроса в секции WHERE (см. п. 5.2.2). В Oracle также не удастся реализовать аналогичный запрос без использования подзапроса. Позиционированные обновления, использующие секцию WHERE CURRENT OF имя_курсора, будут подробно рассмотрены при изучении процедурного SQL (гл. 6). 5.2.2. Запрос UPDATE с подзапросом В секции WHERE запроса UPDATE можно использовать вложенные запросы. Также допускается использование вложенного запроса в секции SET. Запрос-действие UPDATE с подзапросом в Firebird имеет формат: UPDATE {базовая_таблица | представление} SET столбец1 = (<подзапрос1>) [, столбец2 = (<подзапрос2>) ] ... столбец1 [,столбец2 …] = (<подзапрос>) [WHERE { <условие_поиска_с_подзапросом> | CURRENT OF имя_курсора }] [PLAN <список_пунктов_плана>] [ORDER BY <элемент_сортировки1> [, <элемент_сортировки 2>]…] [ROWS k [TO r]] [RETURNING <список_столбцов> [INTO <список_переменных>]]; В секции SET <подзапрос> указывается для обновления определенного столбца и представляет собой <скалярный_подзапрос>. В <условие_поиска_с_подзапросом> секции WHERE подзапрос может представлять собой <скалярный_подзапрос>, <подзапрос_столбца> или <табличный_подзапрос> в зависимости от вида условия поиска (синтаксис условия поиска совпадает с описанным ранее при изучении вложенных запросов). В Oracle подзапрос в запросе UPDATE, помимо секций SET и WHERE, может быть использован для указания целевого объекта для обновления. Формат запроса UPDATE с подзапросом в Oracle: UPDATE { <целевой_объект> | ONLY (<целевой_объект>) } [псевдоним] SET { столбец1 = (<подзапрос1>) [, столбец2 = (<подзапрос2>) ] ... | (столбец1 [,столбец2 …]) = (<подзапрос>) | VALUE (псевдоним) = (<подзапрос>) } [WHERE <условие_поиска_с_подзапросом>] [RETURN[ING] <список_столбцов> [INTO <список_переменных>]]; 16 5. Язык манипулирования данными В секции SET <подзапрос> представляет собой <скалярный_подзапрос>, если указан для обновления конкретного столбца, и <подзапрос_столбца> (<подзапрос_строки>?), если указан для списка из нескольких столбцов. В MS SQL Server запрос UPDATE имеет такой синтаксис [23]: [ <предложение обобщенного табличного выражения> ] UPDATE [ TOP (<выражение>) [ PERCENT ] ] { [<сервер>.][[<база данных>.]<схема>.] { <таблица> | <представление> } | { <функция OPENQUERY> | <функция OPENROWSET> } } [ WITH (<подсказка таблицы> [, <подсказка таблицы>...]) ] SET столбец1 = (<подзапрос>) [, столбец2 = (<подзапрос>) ] ... [<секция_OUTPUT> ] [FROM (<таблица-источник> [, <таблица-источник>]...) ] [WHERE { <условие_поиска_с_подзапросом> | CURRENT OF имя_курсора } ] [OPTION (<подсказка запроса> [, <подсказка запроса>]...) ]; В секции SET <подзапрос> представляет собой <скалярный_подзапрос>, в секции WHERE – в зависимости от условия поиска. Рассмотрим использование подзапроса в секции WHERE запроса UPDATE, которое дает возможность отбирать строки для обновления на основе информации из других таблиц. Например, для уменьшения на 10 значений начислений за услугу с кодом 2 абонентам, имеющим не более одной заявки на ремонт газового оборудования, нужно выполнить запрос: UPDATE NachislSumma N SET NachislSum = NachislSum - 10 WHERE 2 > (SELECT COUNT (*) FROM Request R WHERE R.AccountCD = N.AccountCD) AND ServiceCD=2; Фрагмент таблицы NachislSumma после обновления представлен на рис. 5.4. Подзапрос, использованный в данном запросе UPDATE, является связанным, так как в нем используется столбец AccountCD внешней обновляемой таблицы (условие R.AccountCD = N.AccountCD). При выполнении обновления сначала выбирается строка из таблицы NachislSumma, затем выполняется вложенный запрос, возвращая количество ремонтных заявок для выбранного абонента, а уже затем, в случае удовлетворения условию в секции WHERE запроса UPDATE, происходит обновление текущей строки таблицы NachislSumma. Обновление происходит последовательно для каждой строки таблицы NachislSumma без возврата к извлечению уже просмотренных строк. В Oracle приведенный запрос может быть записан без изменений, в MS SQL Server запрос будет выглядеть так: UPDATE NachislSumma SET NachislSum = NachislSum - 10 WHERE 2 > (SELECT COUNT (*) FROM Request R WHERE R.AccountCD = NachislSumma.AccountCD) AND ServiceCD=2; 17 5. Язык манипулирования данными NACHISLFA ACCO SERVIC NACH NACHISL NACHISLY CTCD UNTC ECD ISLSU MONTH EAR D M … … … … … … 10 080613 2 46,00 6 2011 … … … … … … 20 015527 1 28,32 7 2012 … … … … ... … 22 080613 1 10,60 9 2012 … … … … … … 24 015527 1 38,32 4 2013 … … … … … … 26 080613 1 12,60 8 2010 … … … … … … 29 136159 1 8,30 8 2013 … … … … … … 32 443690 1 17,80 6 2012 … … … … … … 34 126112 1 15,30 8 2010 … … … … … … 36 080613 1 12,60 4 2012 … … … … … … 40 015527 1 18,32 2 2012 41 443690 1 21,67 3 2013 42 080613 1 22,86 4 2010 … … … … … … 46 126112 1 15,30 8 2011 … … … … … … 48 136159 1 8,30 10 2012 Рис. 5.4. Таблица NachislSumma после уменьшения значений начислений Приведем еще один пример с использованием соотнесенного вложенного запроса. Пусть требуется уменьшить на 10 значения начислений за сентябрь 2011 г. тем абонентам, которые до 15 сентября 2011 г. заплатили за август 2011 г. сумму, меньшую среднего значения всех своих платежей. Для решения этой задачи можно использовать запрос: UPDATE NachislSumma SET NachislSum = NachislSum - 10 WHERE NachislYear = 2011 AND NachislMonth = 9 AND AccountCD IN (SELECT AccountCD FROM PaySumma A WHERE PayYear = 2011 AND PayMonth = 8 AND PayDate < '15.09.2011' AND PaySum < (SELECT AVG(PaySum) FROM PaySumma B GROUP BY AccountCD HAVING A.AccountCD = B.AccountCD)); 18 5. Язык манипулирования данными Фрагмент таблицы NachislSumma после обновления представлен на рис. 5.5. NACHISLF ACCOU SERVICE NACHIS NACHISL NACHISLYEAR ACTCD NTCD CD LSUM MONTH ... ... …. ... …. …… 5 115705 2 240,00 9 2011 ... ... ... ... ... …… 17 443069 2 70,00 9 2011 ... ... ... ... ... …… 47 443069 1 28,32 9 2011 ... ... ... ... ... …… Рис. 5.5. Содержание таблицы NachislSumma после обновления В секции FROM подзапроса в запросе UPDATE может присутствовать имя обновляемой таблицы. В этом случае предполагается, что используется то состояние обновляемой таблицы, которое она имела до обновления. Например, требуется уменьшить в 2 раза значения начислений за услугу с кодом 3, которые превышают среднее значение начислений за апрель 2012 г. по всем абонентам за эту же услугу. Для этого необходимо применить запрос UPDATE с подзапросом: UPDATE NachislSumma SET NachislSum = NachislSum / 2 WHERE NachislSum > (SELECT AVG (NachislSum) FROM NachislSumma WHERE NachislMonth = 4 AND NachislYear = 2012 AND ServiceCD=3) AND ServiceCD=3; Фрагмент таблицы NachislSumma после обновления представлен на рис. 5.6. 19 5. Язык манипулирования данными NACHISLF ACCO SERVIC NACHI NACHIS NACHISLYEAR ACTCD UNTC ECD SLSU LMONT D M H … … … … … … 51 005488 3 139,90 5 2012 52 005488 3 133,35 2 2013 53 015527 3 171,68 11 2013 54 080047 3 135,80 2 2013 55 080270 3 139,12 11 2013 56 080613 3 127,20 7 2011 57 080613 3 129,40 2 2013 58 080613 3 119,66 5 2013 59 126112 3 179,90 4 2012 60 136159 3 90,06 9 2013 61 136160 3 119,40 3 2010 62 136160 3 118,69 3 2011 63 136169 3 174,59 6 2012 64 136169 3 173,09 7 2012 65 443690 3 145,16 3 2013 … … … … … … Рис. 5.6. Таблица NachislSumma после обновления В предыдущем примере сначала выполнится подзапрос, возвратив среднее значение сумм начислений за апрель 2012 г., а затем произойдет обновление таблицы NachislSumma. В результате выполнения запроса в таблице NachislSumma 14 строк были обновлены. Аналогично возможно использование соотнесенного вложенного запроса, относящегося к той же самой таблице, которая указана в качестве обновляемой. Рассмотрим пример такого соотнесенного подзапроса в секции SET. Например, для замены дат выполнения ремонтных заявок, относящихся к каждому абоненту, на наиболее позднюю дату их регистрации можно использовать запрос: UPDATE Request SET ExecutionDate = (SELECT MAX (R.IncomingDate) FROM Request R WHERE R.AccountCD = Request.AccountCD); Первые 10 строк таблицы Request после обновления представлены на рис. 5.7. 20 5. Язык манипулирования данными REQU ACCO EXEC FAIL INCOM EXRCUTIONDATE ESTC UNTC UTOR UREC INGDA D D CD D TE 1 005488 1 1 17.12.20 04.04.2013 11 2 115705 3 1 07.08.20 28.12.2012 11 3 015527 1 12 28.02.20 28.02.2012 12 5 080270 4 1 31.12.20 31.12.2011 11 6 080613 1 6 16.06.20 16.06.2011 11 7 080047 3 2 20.10.20 11.10.2012 12 9 136169 2 1 06.11.20 06.11.2011 11 10 136159 3 12 01.04.20 01.04.2011 11 11 136160 1 6 12.01.20 18.05.2013 13 12 443069 5 2 08.08.20 13.09.2011 11 Рис. 5.7. Таблица Request после обновления EXECUTED True True False False True True True False True True После выполнения обновления у каждого абонента стала одинаковой дата выполнения всех ремонтных заявок (она стала совпадать с датой поступления последней заявки, поданной этим абонентом). Например, от абонента с номером лицевого счета '005488' поступило 3 ремонтных заявки: первая – 04.09.2010, вторая – 17.12.2011 и последняя – 04.04.2013. Дата выполнения всех трех заявок после выполнения запроса стала равной 04.04.2013. Вышеприведенные 3 запроса UPDATE корректны для всех анализируемых в учебнике СУБД. Рассмотрим некоторые особенности конкретных СУБД в плане реализации запросов обновления с подзапросами. В Oracle в секции SET можно использовать подзапрос не только для обновления значения конкретного столбца, но и для обновления значений сразу списка столбцов. Например, предыдущий запрос в Oracle можно расширить для замены не только дат выполнения ремонтных заявок, относящихся к каждому абоненту, на наиболее позднюю дату их регистрации, но также и дат поступления заявок на наиболее раннюю дату их регистрации: UPDATE Request SET (IncomingDate, ExecutionDate) = (SELECT MIN (R.IncomingDate), MAX (R.IncomingDate) FROM Request R WHERE R.AccountCD = Request.AccountCD); 21 5. Язык манипулирования данными В Firebird и MS SQL Server такой запрос выполнить не удастся. Главное расширение, которое вводит MS SQL Server в запрос UPDATE стандарта ANSI, – секция FROM. В следующем примере показано обновление данных в Firebird с помощью запроса UPDATE с довольно громоздким подзапросом, а потом – обновление с использованием секции FROM в запросе MS SQL Server. Допустим, необходимо обновить данные в таблице Abonent, установив код улицы = 1 тем абонентам, на чьи ремонтные заявки был назначен исполнитель Школьников С.М. В Firebird запрос может быть записан так: UPDATE Abonent SET StreetCD=1 WHERE AccountCD IN (SELECT AccountCD FROM Request WHERE ExecutorCD IN (SELECT ExecutorCD FROM Executor WHERE FIO='Школьников С.М.')); В MS SQL Server запрос может быть записан проще: UPDATE Abonent SET StreetCD=1 FROM Executor AS e JOIN Request AS r ON e.ExecutorCD=r.ExecutorCD WHERE r.AccountCD = Abonent.AccountCD AND e.FIO='Школьников С.М.'; Оба запроса выполняют одно и то же действие, но по-разному. Выполнение обновления в MS SQL Server сводится к соединению двух таблиц – Executor и Request – с таблицей Abonent. Для выполнения той же самой операции в запросе Firebird нужно сначала найти значение ExecutorCD в таблице Executor и передать его в таблицу Request, а затем нужно найти значение AccountCD и передать его в основную инструкцию UPDATE. Таким образом, в MS SQL Server секция FROM значительно упрощает реализацию запроса UPDATE. Однако не всегда секция FROM упрощает реализацию запроса обновления. Некоторые запросы могут быть проще реализованы в Firebird даже и при использовании запроса UPDATE с секцией FROM в MS SQL Server. В предыдущем пункте 5.2.1 был рассмотрен пример простого запроса UPDATE в Firebird для установки номера телефона '982223' для первых 5 абонентов с наименьшим номером лицевого счета. В MS SQL Server аналогичный запрос может быть записан только с использованием подзапроса: UPDATE Abonent SET Phone = '982223' FROM (SELECT TOP 5 * FROM Abonent ORDER BY AccountCD) AS A1 WHERE Abonent.AccountCD =A1.AccountCD; Как правило, довольно сложно обновить первые N строк с помощью запроса UPDATE, если нет какой-нибудь явной последовательности строк, которую можно определить с помощью секции WHERE. В данном случае для получения первых 5 строк таблицы Abonent с наименьшим номером лицевого счета используется вложенный табличный подзапрос в секции FROM. 22 5. Язык манипулирования данными Следует отметить, что аналогичный запрос в Oracle также реализуется только с использованием подзапроса: UPDATE Abonent A SET A.Phone = '982223' WHERE A.AccountCD IN (SELECT A1.AccountCD FROM (SELECT A2.AccountCD FROM Abonent A2 ORDER BY A2.AccountCD) A1 WHERE ROWNUM <=5); 5.3. ОБОБЩЕННОЕ ОБНОВЛЕНИЕ И ДОБАВЛЕНИЕ ДАННЫХ Помимо запросов UPDATE и INSERT, существует обобщенный запрос UPDATE OR INSERT, который предоставляет возможность изменять или вставлять строку в зависимости от того, существует она в целевой таблице или нет. Запрос UPDATE OR INSERT доступен для использования только в СУБД Firebird. Такой запрос имеет синтаксис: UPDATE OR INSERT INTO { базовая_таблица | представление} [(<список_столбцов>)] VALUES ({константа1 | переменная1} [, {константа2 | переменная2}] ...) [MATCHING <список_столбцов>] [RETURNING <список_столбцов> [INTO <список_переменных>]]; Например, требуется добавить исполнителя ремонтных заявок с ФИО Петров А.С. с кодом 1, причем если исполнитель с таким кодом уже существует, то следует изменить его ФИО на Петров А.С. (рис. 5.8). Запрос будет выглядеть так: UPDATE OR INSERT INTO Executor VALUES (1,’ Петров А.С.'); EXECUTORCD 1 3 4 FIO Петров А.С. Шубин В.Г. Шлюков М.К. 5 Школьников С.М. Рис. 5.8. Результат работы запроса UPDATE OR INSERT В случае если бы исполнителя с кодом 1 не существовало в таблице Executor, в нее была бы вставлена новая строка. Секция MATCHING используется для указания столбцов в таблице, значения в которых следует сопоставить с соответствующими значениями в секции VALUES. По умолчанию сопоставляются значения в столбцах первичных ключей. 1. Примечание. Если секция MATCHING не используется, то в целевой таблице должен обязательно существовать первичный ключ. Например, предыдущий запрос с секцией MATCHING может быть записан как UPDATE OR INSERT INTO Executor VALUES (1,'Петров А.С.') MATCHING (ExecutorCD);, 23 5. Язык манипулирования данными что подразумевается по умолчанию. Рассмотрим пример, когда может быть полезным использование секции MATCHING. Например, требуется внести информацию о ремонтной заявке с кодом 25, поданной 17 декабря 2011 г. абонентом с номером лицевого счета '005488' и выполненной 22 декабря 2011 г. Запрос может выглядеть таким образом: UPDATE OR INSERT INTO Request (RequestCD, AccountCD, IncomingDate, ExecutionDate) VALUES (25,'005488','17.12.2011','22.12.2011'); В процессе выполнения запроса происходит сравнение значения 25 со значениями столбца RequestCD (первичный ключ) в таблице Request. Совпадения не обнаруживается, поэтому происходит вставка новой строки в таблицу Request. Фрагмент таблицы Request после выполнения запроса представлен на рис. 5.9. REQUES ACCO EXEC TCD UNTC UTOR D CD … … … 25 005488 NULL FAIL INCOM EXRCU EXECUTED UREC INGDA TIONDA D TE TE … … … … NULL 17.12.20 22.12.201 NULL 11 1 Рис. 5.9. Последняя строка в таблице Request после добавления Предположим теперь, что вносятся данные по погашенной ремонтной заявке с кодом 26, поданной 28 февраля 2012 г. абонентом с номером лицевого счета '015527' и выполненной 10 марта 2012 г. При этом следует учесть, что если информация о заявке абонента с лицевым счетом '015527' от 28 февраля 2012 г. уже зарегистрирована, то необходимо обновить соответствующую строку. Для этого следует использовать запрос: UPDATE OR INSERT INTO Request (RequestCD, AccountCD, IncomingDate, ExecutionDate, Executed) VALUES (26,'015527','28.02.2012','10.03.2012',True) MATCHING (AccountCD, IncomingDate); Фрагмент таблицы Request после выполнения запроса представлен на рис. 5.10. REQUE ACCOU EXECUT FAILUR INCOMIN EXRCUTI EXECUTED STCD NTCD ORCD ECD GDATE ONDATE 26 015527 1 12 28.02.2012 10.03.2012 True … … … … … … … Рис. 5.10. Обновленная строка в таблице Request В процессе выполнения этого запроса значения первичных ключей не сравниваются, так как в секции MATCHING явно указано, что надо сравнить значения в столбцах AccountCD и IncomingDate с соответствующими значениями из списка VALUES. Обнаруживается совпадение в строке таблицы Request с кодом заявки, равным 3, и происходит ее обновление. 24 5. Язык манипулирования данными В списке значений секции VALUES могут быть указаны не константы, а переменные, если запрос используется в модуле процедурного SQL. Для выполнения данного запроса у пользователя должны быть права как на вставку, так и на изменение строк в таблице или представлении (управление доступом к ресурсам будет подробно описано далее). 25