DML команды
Данные в реляционных базах данных управляются с помощью DML (Data Manipulation Language) комманд. Эти команды это INSERT, UPDATE, DELETE и (в последних версиях SQL) MERGE. В этой главе обсуждается что происходит в памяти и на диске когда выполняются команды DML – момент когда новые данные записываются на в блоки сегмента таблицы или индекса и старые данные записываются в блоки сегментов отмены изменений. В основе этих команд лежит ACID тест, которые должна проходить любая реляционная БД. Управление транзакциями с помощью команд COMMIT и ROLLBACK которые ассоциируются с DML командами также будет рассмотрено в этой главе. А также в этой главе описывается параллельный доступ к данным и уровни блокировки.
Строго говоря существует пять DML команд
- SELECT
- INSERT
- UPDATE
- DELETE
- MERGE
На практике профессионалы в области баз данных SELECT обычно не рассматривают как часть DML. Обычно SELECT рассматривается отдельно и это становится понятно когда вы увидите что следующие пять глав выделены для описания только команды SELECT. Команда MERGE тоже часто не рассматривается, не потому что это не чистая команды управления данными, а потому что результат выполнения этой команды можно достичь используя другие команды. MERGE можно рассматривать как ярлык для вызова команд INSERT и DELETE или UPDATE в зависмости от каких-либо условий. Команда часто рассматриваемая вместе с DML это команды TRUNCATE. На самом деле это DDL команда, но так как эффект для пользователей такой же как и от команды DELETE (несмотря на то что реализация абсолютно разная) то команда TRUNCATE удовлетворяет параметрам DML команд.
Команда INSERT
Oracle хранит данные в виде строк в таблицах. Таблица наполняется строками (так же как страна наполнена людьми) несколькими способами, но самый частый используемый метод это команда INSERT. SQL — это язык ориантированные на работу с наборами данных, и таким образом одна комманда может вилять на одну строку либо на набор строк. Отсюда следует что команда INSERT может добавить одну строку в одну таблицу или много строк в много таблиц. Базовая версия запроса добавляет всего одну строку, но сложные запросы могут добавлять несколько строк в несколько таблиц.
TIP
There are much faster techniques than INSERT for populating a table with large numbers of rows. These are the SQL*Loader utility, which can upload data from files produced by an external feeder system, and Data Pump, which can transfer data in bulk from one Oracle database to another, either via disk files or through a network link.
EXAM TIP
An INSERT command can insert one row, with column values specified in the command, or a set of rows created by a SELECT statement.
Простейшая форма команды INSERT добавляет одну строку в таблицу используя значения указанные в команде. Синтаксис такого запроса
INSERT INTO table [(column [,column…])] VALUES (value [,value…]);
Примеры
insert into hr.regions values (10,’Great Britain’);
insert into hr.regions (region_name, region_id) values (‘Australasia’,11);
insert into hr.regions (region_id) values (12);
insert into hr.regions values (13,null);
Первая из команд указывает значения для обоих столбцов таблицы REGIONS. Если у таблицы есть третий столбец то запрос выполнится неуспешно так как команда использует позиционное обозначение (positional notation). В команде не указывается в какой столбец необходимо вставить конкретное значение, запрос рассматривает позицию значений, их порядок в команде. Когда БД получает запрос использущий позиционное обозначение она будет сопостовлять порядок значений со порядком определения столбцов при создании. Запрос выполнится неуспешно если порядок будет неверный: БД попробует вставить данные, но типы данных столбцов разные.
Второй запрос указывает и столбцы и значения которые использовать для столбцов. Обратите внимание что теперь порядок определения столбцов в таблице не важен – важен порядок столбцов и значений в запросе.
Третий пример указывает один столбец и одно значение. Для всех остальных столцов будет использоваться значение NULL. Запрос выполнится неуспешно если столбец REGION_NAME обязательный (not null). Четвертый пример приведёт к такому же результату как и третий, но так как не были указаны столбцы в запросе – необходимо указать значения (даже NULL) явно для всех столбцов.
TIP
It is often considered good practice not to rely on positional notation and instead always to list the columns. This is more work but makes the code self-documenting (always a good idea!) and also makes the code more resilient against table structure changes. For instance, if a column is added to a table, all the INSERT statements that rely on positional notation will fail until they are rewritten to include a NULL for the new column. INSERT code that names the columns will continue to run.
Для вставки нескольких строк одним запросом значения для строк должны возвращаться запросом. Синтаксис такой команды
INSERT INTO table [column [, column…] ] subquery;INSERT INTO table [column [, column…] ] subquery;
Обратите внимание что такой синтаксис не использует ключевое слово VALUES. Если список столбцов пропущен то подзапрос должен возвращаться значения для каждого столбца таблицы. Для копирование всех строк из одной таблицы в другую если у таблиц одинаковые столбцы то команда для такой операции будет вида
insert into regions_copy select * from regions;
Такой запрос предполагает что таблица regions_copy уже существует. Подзапрос SELECT считывает все строки из таблицы-источника (REGIONS) и команда INSERT записывает все строки в таблицу-цель (REGIONS_COPY)
EXAM TIP
Any SELECT statement, specified as a subquery, can be used as the source of rows passed to an INSERT. This enables insertion of many rows. Alternatively, using the VALUES clause will insert one row. The values can be literals or prompted for as substitution variables.
В завершение рассмотрения команды INSERT необходимо упомянуть что возможно вставить строки в несколько таблиц одним запросом. Такие запросы не входят в базовый курс но для полноты картины рассмотрим пример
insert all
when 1=1 then
into emp_no_name (department_id,job_id,salary,commission_pct,hire_date)
values (department_id,job_id,salary,commission_pct,hire_date)
when department_id <> 80 then
into emp_non_sales (employee_id,department_id,salary,hire_date)
values (employee_id,department_id,salary,hire_date)
when department_id = 80 then
into emp_sales (employee_id,salary,commission_pct,hire_date)
values (employee_id,salary,commission_pct,hire_date)
select employee_id,department_id,job_id,salary,commission_pct,hire_date
from employees where hire_date > sysdate — 30;
Чтобы понять этот запрос, начинаем читать с конца. Подзапрос считывает строки из таблица EMPLOYEES где дата приёма на работу не раньше чем 30 дней назад (сотрудники нанятые за последние 30 дней). Затем возвращаемся наверх. Ключевое слово ALL обозначает что каждая строка из подзапроса рассматривается для доавления во все таблицы, не только в первую где выполняется условие. Первое условие 1=1, которое всегда возвращает значение TRUE, т.е. все строки запишутся в таблицу emp_no_name. Это копия таблицы EMPLOYOEES в которой нет столбцов для персональных данных. Затем рассматривается условие DEPARTMENT_ID<>80, т.е. создадутся строки в таблице EMP_NON_SALES для каждой строки из подзапроса где DEPARTAMENT_ID<>80; для этой таблицы нет столбца COMMISION_PCT. И третье условие создаст строки в таблице EMP_SALES для всех сотрудников у которых DEPARTAMENT_ID=80; в этой таблице не нужен столбец DEPARTMENT_ID так как у всех записей этой таблицы предполагается одно значение DEPARTAMENT_ID.
Это простой пример мультитабличной вставки, но главное понимать что с помощью такого запроса будет выполнен только один подзапрос и будет только один цикл прохода по строкам, но можно вставить данные во многие таблицы-цели. Таким образом можно существенно снижать нагрузку с БД.
Команда UPDATE
Команда UPDATE используется для изменения строк которые уже существуют – строки которые были созданы с помощью команды INSERT или возможно другими инструментами такими как Data Pump. Как и другие SQL команды, команда UPDATE может влиять на одну строку или набор строк. Размер набора данных обновляемым командой UPDATE определяется условием WHERE, точно таким же образом как и набор строк получаемый командой SELECT. Синтаксис идентичныйю Все обновляемые строки будут находиться в одной таблице; невозможно одной командой UPDATE обновлить данные в нескольких таблицах.
Когда обновляются данные команда UPDATE указывает какие столбцы набора строк обновлять. Необязательно обновлять все столбцы строки. Если обновляемые столбец уже хранит значение, оно будет заменено на новое указанное в команде UPDATE. Если в столбец не было значения – т.е. было значение NULL – то столбец будет обновлен на новое значение.
Обычное использование UPDATE это получение одной строки и обновление одного или нескольких столбцов в этой строке. Строка получается используя условие WHERE по первичному ключу, уникальному идентификатору которые гарантирует что только одна строка будет получена. Затем обновляются столбцы которые не являются столбцами первичного ключа. Обычно значение первичного ключа не изменяется. Жизненный цикл строки начинается когда она добавляется, затем может происходить несколько изменений до тех пор пока строка не удаляется и жизненный цикл не заканчивается. Во время жизни строки обычно первичный ключ не изменяется.
Для обновления набора строк используется менее строгое условие WHERE. Для обновления всех строк в таблице просто не указывается условие. Такое поведение команды немного смущает когда команда без условия WHERE выполняется по ошибке. Если вы выбираете строки не по строгому равенству значения первичного ключа то вы можете обновить несколько строк а не одну. Если же вы полностью убираете условие WHERE то будет обновлена вся таблица – возможно миллионы строк всего лишь выполнением одного запроса – когда вы хотели обновить всего одну строку.
EXAM TIP
One UPDATE statement can change rows in only one table, but it can change any number of rows in that table.
Команда UPDATE должна соблюдать все ограничения наложенные на таблицу, так же как и команда INSERT. Например невозможно обновить значение столбца с ограничением обязательности (not null) на значение NULL или обновить первичный ключ на неуникальное значение. Базовый синтаксис команды UPDATE
UPDATE table SET column=value [,column=value…] [WHERE condition];
Более сложная форма команды может использовать подразпросы для значений столбцов и для условия WHERE. На рисунке 8-1 показаны различные запросы выполненные в SQL *Plus.
Первый пример самый простой. Значение одного столбца одной строки устанавливается в значение-литерал. Так как в условии WHERE используется значение первичного ключа, то это гарантирует что обновится максимум одна строка. Если не будут найдены строка удовлетворяющая этому значению ключа то не обновится ни одна строка.
Второй пример показывает использование арифметической операции и существующего столбца для калькуляции нового значения и выборка строк для обновления происходит не по первичному ключу. Если выборка осуществляется ен по значению первичного ключа, или используются не предикаты равенства (такие как BETWEEN) то количество строк которые будут обновлены может быть больше чем один. Если условие WHERE полность пропущно – то обновление применится ко всем строкам таблицы.
Третий пример на рисунке 8-1 использует подзапрос для определения набора данных для обновления и запрос на ввода значения переменных используемых в подзапросе. В данном примере подзапрос (строки 3 и 4) вернёт всех сотрудников название департамента которых содержит подстроку IT и увеличит их зарплату на 10% (к сожалению такое редко случается в реальном мире).
Также возможно использовать подзапрос для определения значения используемого для обновления столбца, как показано в четвертом примере. В нашем примере один сотрудник (условие равенства первичного ключа в строке 5) переводится в департамент с идентификатором 80, и затем подзапрос (строки 3-4) устанавливает процент комиссии на минимальное значение для этого отдела.
Синтаксис для команды UPDATE использующей подзапросы
UPDATE table
SET column=[subquery] [,column=subquery…]
WHERE column = (subquery) [AND column=subquery…] ;
Существует жёсткое ограничение на результат возвращаемый подзапросом для определения значения: подзапрос должен возвращать скалярное значение. Скалярное значение это одно значение (одна строка, один столбец) необходимого типа данных. Если подзапрос вернёт не скалярное значение запрос не выполнится. Рассмотрим два примера
update employees
set salary=(select salary from employees where employee_id=206);
update employees
set salary=(select salary from employees where last_name=’Abel’);
Первые пример использует предикат равенства по первичному ключу и запрос всегда выполнится успешно. Даже если запрос не вернёт ни одну строку (если нет сотрудника с номером 206) запрос вернёт скалярное значение: NULL. В этом случае у всех сотрудников зарплата станет NULL – что может быть не совсем верно с точки зрения качества данных, но с точки зрения SQL здесь нет ошибки. Второй запрос использует предикат равенства для поля LAST_NAME, что не гарантирует уникальность. Запрос выполнится успешно если существует только один сотрудник с таким именем, но если в таблице больше чем одна строка с таким значение запрос вернёт ошибку “ORA-01427: single-row subquery returns more than one row.” Для надёжного кода неважно состоянии данных, важно убедиться что подзапросы всегда возвращают скалярное значение.
TIP
A common fix for making sure that queries are scalar is to use MAX or MIN. This version of the statement will always succeed:
update employees set salary=(select max(salary) from employees where last_name=’Abel’);
However, just because it will work, doesn’t necessarily mean that it does what is wanted.
Подзапросы в условии WHERE тоже должны возвращать скалярное значение если используется предикат равенства или предикат отношения > или <. Если используется предикат вхождения (IN) то подзапрос может возвращать набор строк. К примерму
update employees
set salary=10000
where department_id in (select department_id from departments where department_name like ‘%IT%’);
Результатом этого запроса будет обновление значения зарплаты всем сотрудникам название отдела которых содержит подстроку IT. Но несмотря на то что подзапрос может возвращать в таких случаях несколько строк – он всё равно должен возвращать один столбец.
EXAM TIP
The subqueries used to SET column values must be scalar subqueries. The subqueries used to select the rows must also be scalar, unless they use the IN predicate.
Команда DELETE
Ранее добавленные строки можно удалить из таблицы используя команду DELETE. Эта команда удалит одну или несколько строк из таблицы в зависимости от условия в секции WHERE. Если условие WHERE пропущено то все строки будут удалены из таблицы.
TIP
There are no “warning” prompts for any SQL commands. If you instruct the database to delete a million rows, it will do so. Immediately. There is none of that “Are you sure?” business that some environments offer.
Удаление столбцов происходит по принципу либо всё либо ничего. Нельзя указать столбец в команде DELETE. Когда строка добавляется в таблицу вы можете указать столбцы для заполнения. Когда строка обновляется вы можете выбрать столбцы для обновления. Но удаление происходит для всей строки – единственным выбором является какие строки удалять. Это делает команду DELETE легче чем другие команды с точки зрения синтаксиса. Синтаксис команды DELETE
DELETE FROM table [WHERE condition];
Это простейшая команда DML, особенно если условие WHERE будет пропущено. В этом случае все строки будут удалены. Единственным усложнением команды может быть добавление условия. К примеру условия равенства/подобия литералу
delete from employees where employee_id=206;
delete from employees where last_name like ‘S%’;
delete from employees where department_id=&Which_department;
delete from employees where department_id is null;
Первый запрос идентифицирует строку по первичному ключу. Одна строка будет удалена – или одна или ни одной, если заданное значение ключа не найдено в таблице. Второй запрос использует предикат подобия что может привести к удалению многих строк: будут удалены все сотрудники фамилия которых начинается с буквы S. Третий запрос запросит вводи значения для переменной в запросе и все сотрудники департамента будут удалены. Последний запрос удалит всех сотрудников у которых не назначен департамент (значение департамента NULL).
Условием может быть также подзапрос
delete from employees where department_id in
(select department_id from departments where location_id in
(select location_id from locations where country_id in
(select country_id from countries where region_id in
(select region_id from regions where region_name=’Europe’)
)
)
)
Этот пример испоьлзует подзапрос для выбора строк который использует географическое дерево (другие подзапросы) для удаления всех сотрудников департаменты которых базируются в Европе. Ограничение на количество строк возвращаемых подзапросом такое же как и для команды UPDATE: если условие базируется на предикате равенства, результат подзапроса должен быть скарялным значением, если используется IN то запрос может возвращать несколько строк.
Если команда DELETE не удаляет ни одной строки – это не рассматривается как ошибка. Команда вернёт сообщение “0 rows deleted’ вместо сообщения об ошибки посколько команды выполнена успешна – просто не были найдены строки для удаления.
Для удаления все строк из таблицы существует два варианта: использовать команду DELETE или команду TRUNCATE. DELETE менее кардинальная посколько удаление можно отменить когда очистку (TRUNCATE) нельзя. Также команда DELETE более управляемая так как можно использовать условие WHERE а команда TRUNCATE всегда удаляет все строки из таблицы. Но команда DELETE выполняется гораздо более медленно и загружает БД. Команда TRUNCATE выполняется практически мгновенно и без нагрузки на БД.
Команда TRUNCATE
Команда TRUNCATE это не команда DML – это команда DDL. Разница огромная. Когда DML команды работают с данными, они добавляют, изменяют или удаляют данные как часть транзакции. Рассмотрим транзакции чуть позже, пока же скажем что транзакции можно котролировать в том смысле, что изменения можно подветрждать и отменять. Это очень полезное свойство работы с данными, но его реализация заставляет БД делать много дополнительной работы которая не видна пользователю. DDL команды не управляются пользовательскими транзакциями (несмотря на то что в БД они выполняются как транзакции – но разработчик не может управлять ими), и у вас нет выбора подтвердить изменения или отменить их. Когда команда выполнена – изменения вступили в силу. Но по сравнению с DML командами – DDL команды очень быстрые.
EXAM TIP
Transactions, consisting of INSERT, UPDATE, and DELETE (or even MERGE) commands, can be made permanent (with a COMMIT) or reversed (with a ROLLBACK). A TRUNCATE command, like any other DDL command, is immediately permanent: it can never be reversed.
С точки зрения пользователя, TRUNCATE таблицы тоже самое что и DELETE всех строк. Но удаление может занять какое-то время (возможно несколько часов если достаточно много строк в таблице), а TRUNCATE отработает мгновенно вне зависимости от количества строк в таблице.
TIP
DDL commands, such as TRUNCATE, will fail if there is any DML command active on the table. A transaction will block the DDL command until the DML command is terminated with a COMMIT or a ROLLBACK.
EXAM TIP
TRUNCATE completely empties the table. There is no concept of row selection, as there is with a DELETE.
Одной из частей определения таблицы, которое хранится в словаре данных, является физическое местоположение. Когда таблица создаётся, выделяется место, фиксированного размера в файлах данных БД. Это место известное нам как экстент, выделяется и свободно для записи. Затем, когда строки добавляются в таблицу, экстент заполняется. Когда первый экстент заполнен, другие экстенты будут выделяться для таблицы автоматически. Таким образом таблица состоит из одного или нескольких экстентов в которых хранятся строки. Словарь данных отслеживает как выделенные экстенты так и как много выделенного для таблицы пространства использовано. Вводится понятие верхней границы (high water mark). Верхняя граница это последняя позиция в последнем экстенте которая когда либо использовалась для хранения данных. Все пространство до верхней границы когда либо использовалось для хранения данных, а всё пространство после никогда не использовалось для хранения данных. Обратите внимание что вполне возможно что будет много свободного места до верхней границы в текущий момент; это возможно из-за удаления строк командой DELETE. Добавление строк в таблицу поднимает верхную границу. Удаление строк оставляет верхнюю границу на той же позиции – но пространство используемое удаляемыми строками становится доступным для записи новых строк.
Команда TRUNCATE обнуляет верхнюю границу. В словаре данных позиция верхней границы смещается на начало первого экстента. Так как Oracle считает что строки после верхней границы не могут существовать то эффект от этого равнозначен удалению всех строк из таблицы. Таблица очищается и остается пустой пока последующие добавления строк не обновят значение верхней границы. Таким способом одна DDL команда которая фактически делает простое обновление в словаре данных может уничтожить миллионы строк в таблице.
Синтаксис команды TRUNCATE
TRUNCATE TABLE table;
На рисунке 8-2 показано как выбрать команду TRUNCATE в SQL Developer, но также эту команду можно выполнить и из SQL* Plus и из другого инструмента.
Команда MERGE
Часто возникает ситуация когда вам необходимо взять набор данных (источник) и интегрировать его в существующую таблицу (цель). Если строка источника уже существует в таблице-цели, вы можете хотеть обновить строку в таблице-цели, или удалить старую строку и вставить новую или вы хотите вообще не трогать такие строки. Если строка источника не существует в таблице-цели, вы хотите добавить такую строку. Команда MERGE позволяет сделать это. MERGE работает с наборами данных, для каждой строки источника пытается найти уже существующую строку в таблице-цели. Если совпадение не найдено – строка будет добавлена; если строка найдена то она может быть обновлена. Начиная с версии 10g найденная строка в таблице-цели может быть даже удалена после нахождения совпадения и обновления.
Команда MERGE не делает ничего такого что было бы невозможно сделать командами DELETE, INSERT и UPDATE – но только она может сделать это за один проход данных. Альтернативой команде MERGE будет три прохода данных, по одному для каждой команды.
Источником для команды MERGE может быть таблица или запрос. Условие для нахождения совпадения аналогично условию WHERE. Секция которая отвечает за обновление или добавление данных аналогично соответствующей команде INSERT или UPDATE. Получается что команда MERGE самая сложная из DML команд (сложно не согласиться) и самая мощная (что можно оспорить). Использование команды MERGE не входит в данный курс но для полноты картины рассмотрим простой пример
merge into employees e
using new_employees n on (e.employee_id = n.employee_id)
when matched then
update set e.salary=n.salary
when not matched then
insert (employee_id,last_name,salary)
values (n.employee_id,n.last_name,n.salary);
Данный запрос использует таблицу NEW_EMPLOYEES для обновления или добавления данных в таблицу EMPLOYEES. Команда пройдёт по данным таблицы NEW_EMPLOYEES и для каждой строки этой таблицы попробует найти строку в таблице EMPLOYEES сооветствующую заданному условию. Если строка найдена значение поля SALARY будет обновлено на новое значение из таблицы NEW_EMPLOYEES. Если строка не найдена одна новая строка будет добавлена.
Неуспешное выполнение DML команд
Запрос может выполниться неуспешно по многим причинам, включая следующие
Ошибка в синтаксисе
Ссылка на несуществующий объект или столбец
Недостаток прав
Нарушение ограничений
Проблемы с доступным местом
На рисунке 8-3 отображены несколько попыток выполнения запросов в SQL *Plus. Пользователь подключается к аккаунту SUE используя пароль sue (хороший пример плохой безопасности БД) и выполняет запросы к таблице employees. Первый запрос выполняется с ошибкой из-за обычной опечатки корректно указанной SQL *Plus. Обратите внимание что SQL *Plus никогда не будет исправлять ошибки такого плана, даже если достоверно известно что именно вы хотели написать. Возможно другие инструменты могут автоматически изменять ошибки.
Второй запрос был выполнен неуспешно с ошибкой о том что объект не существует. Это произошло потому что таблица находится не в схеме SUE, а в схеме HR. Исправив это третий запрос был выполнен успешно – но только он. Значение переданное в условии WHERE это строка ’21-APR-00’, но поле HIREDATE определено как дата, а не строка. Для выполнения запроса БД попробовала преобразовать строку в дату. В последнем запросе такое преобразование не смогло отработать, так как строка-параметр была европейского формата данных, а база настроена на американский формат: преобразовать 21 в месяц не получилось. Запрос был бы выполнен успешно если бы строка была ‘04/21/2000’.
Даже если команда синтаксически верна и объекты используемые в запросе существуют, запрос все равно может быть выполнен неуспешно из-за нехватки прав. Если пользователь попробует выполнить запрос к объектам на которые у него нет соотвествующих прав, то БД вернёт ошибку аналогичной той, которая быда бы возвращена если бы объект не существовал. Для пользователя у которого нет прав к объекту – объект не существует.
Ошибки вызванные нехваткой прав являются случаем когда команды SELECT и DML могут возвращать разные результаты: для пользователя возможно установить права просмотра данных, но не изменения (добавления, удаления). Такое распределение достаточно часто распространено. А иногда бывают случаи когда у пользователя есть права добавления строк, которые он не может просматривать, и что хуже всего даже удалять строки которые он не может ни видеть ни изменять.
Нарушение ограничений также может привести к выполнению DML команды с ошибкой. Например команда INSERT может добавлять несколько строк в таблицу и для каждой строки БД будет проверять существует ли уже такое значение первичного ключа. Это проверяется для каждой строки. И может быть что первые несколько строк (или первые несколько миллионов строк) уже добавлены без проблем, а затем команда доходит до строки со значением-дубликатом. В этот момент будет возвращена ошибка и запрос будет выполнен неуспешно. Ошибка запустит отмену добавления всех уже добавленных строк. Такое поведение стандартно для всех команд SQL: либо запрос выполнен успешно целиком, либо не выполнен. Отмена выполненных изменение это rollback.
Если запрос выполнен неуспешно из-за проблем со свободным место – результат такой же. Часть запроса может быть выполнена успешно до того как БД стало не хватать места и эта часть будет автоматически отменена в момент возникновения ошибки. Отмена частично выполненного запроса нагружает БД. Отмена запроса принуждает БД сделать много работы и обычно занимает не меньше времени чем само выполнение запроса до ошибки (а иногда и дольше).