Понятно, что DELETE удалит 0 строк, а UPDATE обновит 0 строк, если ни одна из строк таблицы не удовлетворит условию WHERE в предложении DELETE или UPDATE. Но может ли INSERT вставить 0 строк?
Представьте ситуацию: INSERT в цикле срабатывает 100 раз, а в таблицу добавлено 60 строк. Возможно ли это?
Оказывается, возможно. INSERT может вставить 0 строк, если это INSERT с подзапросом SELECT, который возвращает 0 строк.
-- создаю пустую таблицу
SQL> CREATE TABLE users0
AS SELECT * FROM all_users WHERE 1 != 1;
Table created
-- вставляю строку
SQL> INSERT INTO users0
SELECT * FROM all_users WHERE 1 != 1;
0 rows inserted
Это была разминка. Теперь поближе познакомимся с возможностями команды INSERT, позволяющими добавлять строки в несколько таблиц за один раз. Такие многотабличные предложения INSERT используют подзапрос SELECT для получения данных для вставки.
СУБД Oracle, начиная с версии 9, поддерживает два типа многотабличных предложений INSERT: безусловное и условное. В этой статье я экспериментирую с безусловным многотабличным предложеним INSERT.
Безусловное многотабличное предложение INSERT выполняет каждую из частей INTO для каждой строки, возвращенной подзапросом SELECT. Синтаксис такой:
INSERT ALL
INTO tab1 [(cols1)] [VALUES (col_list1)]
[INTO tab2 [(cols2)] [VALUES (col_list2)]
...]
subquery;
Ключевое слово ALL после INSERT обязательно в безусловном многотабличном предложении.
Для дальнейшего экспериментирования создаю еще одну таблицу со структурой, эквивалентной структуре системного вью all_users:
SQL> CREATE TABLE users1
AS SELECT * FROM all_users WHERE 1 != 1;
Table created
А теперь одной командой копирую 5 строк из all_users в обе таблицы, users0 и users1:
SQL> INSERT ALL
INTO users0
INTO users1
SELECT * FROM all_users WHERE rownum < 6;
10 rows inserted
И проверяю результат:
SQL> SELECT COUNT(*) FROM users0;
COUNT(*)
----------
5
SQL> SELECT COUNT(*) FROM users1;
COUNT(*)
----------
5
SQL> ROLLBACK;
Rollback complete
В части VALUES многотабличной команды INSERT можно использовать выражения из списка SELECT подзапроса, и вообще все допустимые выражения SQL:
SQL> INSERT ALL
INTO users0 (username, user_id, created)
VALUES (LOWER(username), -user_id, ROUND(created, 'MONTH'))
INTO users1
SELECT * FROM all_users WHERE rownum < 6;
10 rows inserted
SQL> SELECT * FROM users0
UNION
SELECT * FROM users1
ORDER BY 1, 2;
USERNAME USER_ID CREATED
------------------------------ ---------- -----------
APEX_040000 47 27.08.2011
APEX_040200 49 12.02.2013
APEX_PUBLIC_USER 45 27.08.2011
AY 50 15.06.2013
XS$NULL 2147483638 27.08.2011
apex_040000 -47 01.09.2011
apex_040200 -49 01.02.2013
apex_public_user -45 01.09.2011
ay -50 01.06.2013
xs$null -214748363 01.09.2011
10 rows selected
Для наглядности выведу строки, относящиеся к одному и тому же пользователю, рядом:
SQL> ALTER SESSION SET NLS_SORT = BINARY_CI;
Session altered
SQL> SELECT * FROM users0
UNION
SELECT * FROM users1
ORDER BY 1, 2;
USERNAME USER_ID CREATED
------------------------------ ---------- -----------
apex_040000 -47 01.09.2011
APEX_040000 47 27.08.2011
apex_040200 -49 01.02.2013
APEX_040200 49 12.02.2013
apex_public_user -45 01.09.2011
APEX_PUBLIC_USER 45 27.08.2011
ay -50 01.06.2013
AY 50 15.06.2013
xs$null -214748363 01.09.2011
XS$NULL 2147483638 27.08.2011
10 rows selected
SQL> ROLLBACK;
Rollback complete
Если многотабличный INSERT по какой-либо причине завершится ошибкой, то отменяются все изменения, сделанные данной командой, и ни в одну из таблиц строки не будут добавлены. Продемонстрирую это, добавляя пробелы к username до тех пор, пока для очередной вставляемой строки получившееся значение не поместится в столбец username таблицы users0:
SQL> INSERT ALL
INTO users0
INTO users1
SELECT
RPAD(username, 25 + rownum),
user_id,
created
FROM all_users;
ORA-12899: value too large for column "USERS0"."USERNAME" (actual: 31, maximum: 30)
SQL> SELECT 'users0', COUNT(*) FROM users0
UNION
SELECT 'users1', COUNT(*) FROM users1;
'USERS0' COUNT(*)
-------- ----------
users0 0
users1 0
Еще один момент, заслуживающий упоминания, это использование сиквенсов в многотабличной команде INSERT. Oracle не позволяет использовать NEXTVAL в подзапросе:
SQL> CREATE SEQUENCE s0;
Sequence created
SQL> INSERT ALL
INTO users0
INTO users1
SELECT 'qwerty', s0.NEXTVAL, SYSDATE FROM dual;
ORA-02287: sequence number not allowed here
Однако, можно использовать сиквенс в части VALUE многотабличной команды INSERT:
SQL> INSERT ALL
INTO users0 (username, user_id, created) VALUES ('qwerty0', s0.NEXTVAL, SYSDATE)
INTO users1 (username, user_id, created) VALUES ('qwerty1', s0.NEXTVAL, SYSDATE)
SELECT * FROM dual;
2 rows inserted
SQL> SELECT * FROM users0
UNION ALL
SELECT * FROM users1;
USERNAME USER_ID CREATED
------------------------------ ---------- -----------
qwerty0 1 06.11.2012
qwerty1 1 06.11.2012
Сюрприз! Несмотря на то, что выражение s0.NEXTVAL использовано дважды, в списках VALUES для таблиц users0 и users1, оно было вычислено только один раз, и полученное значение 1 было использовано при вставке в обе таблицы! Каковы бы ни были причины такого поведения, многотабличная команда INSERT ведет себя именно так.
А сколько раз выполнится NEXTVAL, если подзапрос вернет две (или больше) строки?
SQL> INSERT ALL
INTO users0 (username, user_id, created) VALUES ('qwerty0', s0.NEXTVAL, SYSDATE)
INTO users1 (username, user_id, created) VALUES ('qwerty1', s0.NEXTVAL, SYSDATE)
SELECT * FROM all_users WHERE rownum < 3;
4 rows inserted
SQL> SELECT * FROM users0
UNION ALL
SELECT * FROM users1;
USERNAME USER_ID CREATED
------------------------------ ---------- -----------
qwerty0 1 06.11.2012
qwerty0 2 06.11.2012
qwerty0 3 06.11.2012
qwerty1 1 06.11.2012
qwerty1 2 06.11.2012
qwerty1 3 06.11.2012
6 rows selected
SQL> ROLLBACK;
Rollback complete
Итак, вычисление выражения s0.NEXTVAL происходит один раз для каждой строки, возвращаемой подзапросом.
Во всех приведенных выше примерах многотабличная команда INSERT вставляет строки в таблицы с одинаковыми столбцами. Но таблицы в частях INTO вовсе не обязаны иметь одинаковую структуру. Продемонстрирую это:
SQL> CREATE TABLE names (
id NUMBER,
name VARCHAR2(50)
);
Table created
SQL> INSERT ALL
INTO users0
INTO names (id, name) VALUES (user_id, username)
SELECT * FROM all_users WHERE rownum < 6;
10 rows inserted
Завершу знакомство с многотабличной безусловной командой INSERT забавным экспериментом и удалением более не нужных объектов:
SQL> INSERT ALL
INTO users1
INTO users1 (user_id, username, created) VALUES (user_id+1000, LOWER(username), created)
SELECT * FROM all_users WHERE rownum < 6;
10 rows inserted
SQL> SELECT * FROM users1;
USERNAME USER_ID CREATED
------------------------------ ---------- -----------
XS$NULL 2147483638 27.08.2011
AY 50 15.06.2013
APEX_040200 49 12.02.2013
APEX_040000 47 27.08.2011
APEX_PUBLIC_USER 45 27.08.2011
xs$null 2147484638 26.08.2012
ay 1050 15.06.2014
apex_040200 1049 12.02.2014
apex_040000 1047 26.08.2012
apex_public_user 1045 26.08.2012
10 rows selected
SQL> DROP TABLE users0;
Table dropped
SQL> DROP TABLE users1;
Table dropped
SQL> DROP TABLE names;
Table dropped
SQL> DROP SEQUENCE s0;
Sequence dropped
Во второй части - исследование условной команды INSERT для нескольких таблиц.
Дополнение
То, как multitable insert работает с сиквенсами, позволяет одной командой вставить строки в родительскую и дочернюю таблицы:
SQL> CREATE TABLE father (
2 father_id NUMBER PRIMARY KEY,
3 descr VARCHAR2(50) NOT NULL
4 );
Table created
SQL> CREATE TABLE daughter (
2 daughter_id NUMBER PRIMARY KEY,
3 father_id NUMBER REFERENCES father,
4 descr VARCHAR2(50) NOT NULL
5 );
Table created
SQL> CREATE SEQUENCE father_seq;
Sequence created
SQL> CREATE SEQUENCE daughter_seq;
Sequence created
SQL>
SQL> INSERT ALL
2 INTO father VALUES (father_seq.nextval, 'Зевс')
3 INTO daughter VALUES (daughter_seq.nextval, father_seq.nextval, 'Афина')
4 SELECT 1 FROM dual;
2 rows inserted
SQL>
SQL> SELECT f.father_id, f.descr, d.daughter_id, d.descr
2 FROM father f, daughter d
3 WHERE f.father_id = d.father_id
4 ;
FATHER_ID DESCR DAUGHTER_ID DESCR
---------- ---------- ----------- ----------
1 Зевс 1 Афина
Комментариев нет:
Отправить комментарий