ДОБАВЛЕНИЕ ДАННЫХ (инструкция INSERT)

 

Наименьшей единицей информации, которую можно добавить в реляционную базу данных, является одна строка. Рассмотрим три способа добавления новых строк в базу данных:

1. Однострочная инструкция INSERT позволяет добавить в таблицу одну новую строку. Она широко используется в повседневных приложениях, например в программах ввода данных.

2. Многострочная инструкция INSERT обеспечивает извлечение строк из одной части базы данных и добавление их в другую таблицу. Она обычно используется, когда “старые” строки таблицы пересылаются в неиспользуемую таблицу для сохранения.

3. Инструкция MERGE.

 

Однострочная инструкция INSERT. Однострочная инструкция INSERT, добавляет в таблицу новую строку.

 

INSERT INTO имя_таблицы [(список_столбцов)]

VALUES (список_значений_данных);

 

В предложении INTO указывается имя таблицы или представления, в которые добавляется новая строка, а в предложении VALUES содержатся значения данных для новой строки. Список столбцов определяет, какие значения, в какой столбец заносятся.

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

Как правило, в инструкции INSERT указывается простое имя таблицы, определяющее вашу собственную таблицу. Чтобы вставить данные в таблицу, созданную другим пользователем, необходимо указать ее полное имя. Помимо этого, необходимо иметь разрешение на ввод данных в эту таблицу, иначе выполнение инструкции закончится неудачей.

Список столбцов в инструкции INSERT – необязательный параметр, он служит для того, чтобы установить соответствие между значениями данных, содержащимися в предложении VALUES, и столбцами, для которых эти данные предназначены. Списки значений и столбцов должны содержать одинаковое число элементов, а тип данных каждого значения должен быть совместимым с типом соответствующего столбца. Если списка столбцов нет, то используются все столбцы в том порядке, в котором они перечислены в CREATE TABLE.

Добавление значений NULL. При добавлении в таблицу новой строки всем столбцам, имена которых отсутствуют в списке столбцов инструкции INSERT, автоматически присваивается значение NULL.

Значение NULL можно присвоить и явным образом, включив эти столбцы в список, а в списке значений задав для них ключевое слово NULL:

 

INSERT INTO staff (sno, fname, lname, adress, tel_no, position, sex, salary, dob, bno)

VALUES (6, 'Иванов', 'Иван', 'Nitova 34', '6455811', NULL, 'M', 5000, '03.05.92', 2);

 

Добавление всех столбцов.Для удобства в SQL разрешается не включать список столбцов в инструкцию INSERT. Если список столбцов опущен, он генерируется автоматически и в нем слева направо перечисляются все столбцы таблицы. При выполнении инструкции SELECT * генерируется такой же список столбцов. Пользуясь этой сокращенной формой записи, предыдущую инструкцию можно переписать таким образом:

insert into staff

values (6, 'Иванов', 'Иван ', 'Nitova 34', '6455811', NULL, 'M', 5000, '03.05.92', 2);

 

Как видно, если список столбцов опущен, то в списке значений явно указаны значения NULL, а последовательность значений данных в точности соответствует порядку столбцов в таблице.

Многострочная инструкция INSERT. Многострочная инструкция INSERT добавляет в таблицу несколько строк путем копирования множества строк одной таблицы в другую таблицу.

 

INSERT INTO имя_таблицы [(список_столбцов)]

SELECT запрос;

 

В этой разновидности инструкции INSERT значения для новых строк явно не задаются. Источником новых строк служит запрос на выборку (SELECT), содержащийся внутри инструкции.

На запрос, содержащийся внутри многострочной инструкции INSERT, накладывается несколько ограничений:

- в запрос нельзя включать предложение ORDER BY. He имеет смысла сортировать таблицу результатов запроса, поскольку она добавляется в таблицу, которая, как и все остальные, не упорядочена;

- таблица результатов запроса должна содержать количество столбцов, равное длине списка столбцов в инструкции INSERT (или полностью повторять список столбцов таблицы, если список столбцов отсутствует), а типы данных соответствующих столбцов таблицы результатов запроса и целевой таблицы должны быть совместимыми;

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

Пример:

Пусть существует таблица staff_prop_count (sno, fname, lname, prop_count), которая содержит имена работников компании и их учетные номера, а также количество объектов аренды, за которые они отвечают.

INSERT INTO staff_prop_count

SELECT s.sno, fname, lname, COUNT(p.sno)

FROM staff s, property_for_rent p

WHERE s.sno=p.sno

GROUP BY s.sno, fname, lname

UNION

SELECT s.sno, fname, lname,0

FROM staff s

WHERE s.sno NOT IN (SELECT DISTINCT p.sno

FROM property_for_rent p);

 

Отметим, что инструкция UNION в таком применении поддерживается не во всех диалектах SQL.

Инструкция MERGE. Начиная с версии Oracle 9i, появилась возможность использовать инструкцию MERGE INTO, которая объединяет в себе возможности инструкций вставки, обновления и удаления, делая их более удобными для пользователей.

MERGE INTO – это DML-оператор вставки (INSERT), обновления (UPDATE) и удаления (DELETE, начиная с Oracle Database 10g) данных при слиянии таблиц. Kогда выполняется оператор MERGE, в сервере Oracle9i Database немедленно сработывают триггеры BEFORE UPDATE и BEFORE INSERT, так как в этом операторе есть предложения WHEN MATCHED THEN UPDATE (когда совпадают, то обновить) и WHEN NOT MATCHED THENINSERT (когда не совпадают, то вставить) которые являются обязательными. В сервере Oracle Database 10g будут срабатывать триггеры BEFORE UPDATE, INSERT и/или DELETE – в зависимости от указанных в операторе MERGE операций.

 

Общий синтаксис инструкции MERGE INTO:

 

MERGE INTO destination_table dest

USING (SELECT col1, col2, col3 FROM source_table) source1

ON (dest.col1 = source1.col1)

WHEN MATCHED THEN

UPDATE SET dest.col2 = source1.col2,

dest.col3 = source1.col3

WHERE source1.col2 IS NOT NULL

DELETE source1.col2 IS NULL

WHEN NOT MATCHED THEN

INSERT (dest.col1, dest.col2, dest.col3)

VALUES (source1.col1, source1.col2, source1.col3)

WHERE source1.col2 IS NOT NULL

 

Это простое слияние таблиц destination_table и source_table,для которых проверяется условие dest.col1=source1.col1, и если оно истинно, то выполняется UPDATE, если нет – INSERT. Причем нельзя изменять поля таблицы destination_table в секции UPDATE, по которым идет связывание двух таблиц. В качестве source_table чаще всего используется подзапрос.

 

Пример: в дополнительной таблице copy_staff обновлять фамилию, имя и зарплпату сотрудников, если они уже есть в этой таблице, иначе добавлять новых сотрудников:

 

MERGE INTO copy_staff d

USING (SELECT * FROM staff) s

ON (d.sno=s.sno)

WHEN MATCHED THEN

UPDATE SET

d.fname=s.fname,

d.lname=s.lname,

d.salary=s.salary

WHEN NOT MATCHED THEN

INSERT VALUES (s.sno, s.fname, s.lname, s.adress, s.tel_no, s.position, s.sex, s.dob, s.salary, s.bno);

 

Назначение операции MERGE – ускорить обновление больших таблиц. Обратите внимание, что обновление выполняется применительно к одному состоянию БД и поэтому не сводимо к последовательному выполнению команд INSERT, UPDATE и DELETE.