В первой части статьи была рассмотрена работа многотабличной безусловной команды INSERT
. Продолжим знакомство с командой INSERT
, вставляющей строки одновременно в несколько таблиц, и поэкспериментируем с условным вариантом этой команды.
В многотабличных условных предложениях INSERT
одной или нескольким частям INTO
предшествует условие WHEN
, которое и определяет, будут ли выполнены следующие за ним части INTO
для каждой строки, возвращенной подзапросом.
Синтаксис такой:
INSERT [ALL | FIRST]
WHEN expression1 THEN
INTO tab1 [(cols1)] [VALUES (col_list1)]
[INTO tab11 [(cols11)] [VALUES (col_list11)]
...]
WHEN expression2 THEN
INTO tab2 [(cols2)] [VALUES (col_list2)]
[INTO tab21 [(cols21)] [VALUES (col_list21)]
. . .
[ELSE
INTO tab3 [(cols3)] [VALUES (col_list3)]]
subquery;
INSERT ALL
означает, что нужно проверить каждое из условий WHEN, независимо от того, оказались истинными или ложными предыдущие условия. Таким образом, одна строка, возвращенная подзапросом, может удовлетворить несколько условий WHEN
и привести к выполнению INTO
, соответствующих каждому из условий.
INSERT FIRST
означает, что для строки, возвращенной подзапросом, выполнятся только части INTO
, связанные с первым условием WHEN
, которое окажется истинным. Оставшиеся условия WHEN
проверяться не будут.
Опциональная часть ELSE
может содержать альтернативные части INTO
, которые выполнятся, если ни одно из условий WHEN
не сработает.
Для экспериментов вновь воспользуемся системным вью all_users
. Создадим несколько пустых таблиц, в которые будем вставлять строки, извлеченные из all_users
, затем выполним INSERT
:
SQL> CREATE TABLE users2 AS SELECT * FROM all_users WHERE 1 != 1;
Table created
SQL> CREATE TABLE users3 AS SELECT * FROM all_users WHERE 1 != 1;
Table created
SQL> CREATE TABLE users5 AS SELECT * FROM all_users WHERE 1 != 1;
Table created
SQL> CREATE TABLE users0 AS SELECT * FROM all_users WHERE 1 != 1;
Table created
SQL> SELECT COUNT(*) FROM all_users;
COUNT(*)
----------
14
SQL> INSERT -- ALL by default
WHEN MOD(user_id,2) = 0 THEN
INTO users2
WHEN MOD(user_id,3) = 0 THEN
INTO users3
WHEN MOD(user_id,5) = 0 THEN
INTO users5
ELSE
INTO users0
SELECT * FROM all_users;
19 rows inserted
Приведенная команда INSERT
вставляет записи, возвращенные подзапросом, в те или иные таблицы в зависимости от того, делится ли user_id
без остатка на 2, 3 или 5. Если значение user_id
без остатка делится на 2, то строка вставляется в users2, если на 3, то в users3
, если на 5, то в users5
, и если не делится ни на одно из этих чисел, то - в таблицу users0
.
Как видим, команда INSERT
вставила больше строк, чем возвращает подзапрос. Это потому, что некоторые user_id
одновременно делятся более чем на одно из чисел 2, 3 и 5. Найдем строки, вставленные более чем в одну таблицу:
SQL> SELECT username, user_id, created, COUNT(*)
FROM (
SELECT * FROM users2
UNION ALL
SELECT * FROM users3
UNION ALL
SELECT * FROM users5
UNION ALL
SELECT * FROM users0)
GROUP BY username, user_id, created
HAVING COUNT(*) > 1;
USERNAME USER_ID CREATED COUNT(*)
------------------------------ ---------- ----------- ----------
SYS 0 27.08.2011 3
MDSYS 42 27.08.2011 2
APEX_PUBLIC_USER 45 27.08.2011 2
AY 50 15.06.2013 2
SQL> ROLLBACK;
Rollback complete
Теперь выполним ту же команду с незначительным изменением, а именно, поставим после INSERT
ключевое слово FIRST
:
SQL> INSERT FIRST
WHEN MOD(user_id,2) = 0 THEN
INTO users2
WHEN MOD(user_id,3) = 0 THEN
INTO users3
WHEN MOD(user_id,5) = 0 THEN
INTO users5
ELSE
INTO users0
SELECT * FROM all_users;
14 rows inserted
Каждая из возвращенных подзапросом строк попала ровно в одну из таблиц! Посмотрим, как распределились строки по таблицам:
SQL> SELECT 'users2', u2.* FROM users2 u2
UNION ALL
SELECT 'users3', u3.* FROM users3 u3
UNION ALL
SELECT 'users5', u5.* FROM users5 u5
UNION ALL
SELECT 'users0', u0.* FROM users0 u0
ORDER BY 1;
'USERS2' USERNAME USER_ID CREATED
-------- ------------------------------ ---------- -----------
users0 APEX_040200 49 12.02.2013
users0 APEX_040000 47 27.08.2011
users0 HR 43 27.08.2011
users2 MDSYS 42 27.08.2011
users2 XDB 34 27.08.2011
users2 CTXSYS 32 27.08.2011
users2 FLOWS_FILES 44 27.08.2011
users2 AY 50 15.06.2013
users2 XS$NULL 2147483638 27.08.2011
users2 SYS 0 27.08.2011
users3 OUTLN 9 27.08.2011
users3 APEX_PUBLIC_USER 45 27.08.2011
users5 SYSTEM 5 27.08.2011
users5 ANONYMOUS 35 27.08.2011
14 rows selected
Если поменять порядок условий WHEN
в команде INSERT
, то результат выполнения команды будет другим. Например,
SQL> ROLLBACK;
Rollback complete
SQL> INSERT FIRST
WHEN MOD(user_id,5) = 0 THEN
INTO users5
WHEN MOD(user_id,3) = 0 THEN
INTO users3
WHEN MOD(user_id,2) = 0 THEN
INTO users2
ELSE
INTO users0
SELECT * FROM all_users;
14 rows inserted
SQL> SELECT 'users2', u2.* FROM users2 u2
UNION ALL
SELECT 'users3', u3.* FROM users3 u3
UNION ALL
SELECT 'users5', u5.* FROM users5 u5
UNION ALL
SELECT 'users0', u0.* FROM users0 u0
ORDER BY 1;
'USERS2' USERNAME USER_ID CREATED
-------- ------------------------------ ---------- -----------
users0 APEX_040200 49 12.02.2013
users0 APEX_040000 47 27.08.2011
users0 HR 43 27.08.2011
users2 XS$NULL 2147483638 27.08.2011
users2 CTXSYS 32 27.08.2011
users2 XDB 34 27.08.2011
users2 FLOWS_FILES 44 27.08.2011
users3 OUTLN 9 27.08.2011
users3 MDSYS 42 27.08.2011
users5 SYS 0 27.08.2011
users5 SYSTEM 5 27.08.2011
users5 ANONYMOUS 35 27.08.2011
users5 APEX_PUBLIC_USER 45 27.08.2011
users5 AY 50 15.06.2013
14 rows selected
В заключение, еще один эксперимент. Попробую вставить строки более чем в одну таблицу при выполнении / невыполнении условия WHEN
:
SQL> ROLLBACK;
Rollback complete
SQL> INSERT FIRST
WHEN MOD(user_id,5) = 0 THEN
INTO users5
INTO users3
ELSE
INTO users2
INTO users0
SELECT * FROM all_users;
28 rows inserted
Работает! Посмотрим, как распределились cтроки по таблицам:
SQL> SELECT 'users2', u2.* FROM users2 u2
UNION ALL
SELECT 'users3', u3.* FROM users3 u3
UNION ALL
SELECT 'users5', u5.* FROM users5 u5
UNION ALL
SELECT 'users0', u0.* FROM users0 u0
ORDER BY 1;
'USERS2' USERNAME USER_ID CREATED
-------- ------------------------------ ---------- -----------
users0 XS$NULL 2147483638 27.08.2011
users0 APEX_040200 49 12.02.2013
users0 APEX_040000 47 27.08.2011
users0 FLOWS_FILES 44 27.08.2011
users0 OUTLN 9 27.08.2011
users0 MDSYS 42 27.08.2011
users0 XDB 34 27.08.2011
users0 CTXSYS 32 27.08.2011
users0 HR 43 27.08.2011
users2 OUTLN 9 27.08.2011
users2 CTXSYS 32 27.08.2011
users2 XDB 34 27.08.2011
users2 MDSYS 42 27.08.2011
users2 HR 43 27.08.2011
users2 FLOWS_FILES 44 27.08.2011
users2 APEX_040000 47 27.08.2011
users2 XS$NULL 2147483638 27.08.2011
users2 APEX_040200 49 12.02.2013
users3 SYS 0 27.08.2011
users3 AY 50 15.06.2013
users3 APEX_PUBLIC_USER 45 27.08.2011
users3 ANONYMOUS 35 27.08.2011
users3 SYSTEM 5 27.08.2011
users5 SYSTEM 5 27.08.2011
users5 AY 50 15.06.2013
users5 APEX_PUBLIC_USER 45 27.08.2011
users5 ANONYMOUS 35 27.08.2011
users5 SYS 0 27.08.2011
28 rows selected
Для многотабличной условной команды INSERT
, как и для ее безусловного варианта, таблицы в частях INTO
могут быть совершенно разными по структуре. Все, сказанное в части I об использовании сиквенсов с многотабличной командой INSERT
, справедливо также для условного варианта этой команды.
В завершение, удаляю таблицы, с которыми экспериментировал:
SQL> DROP TABLE users2;
Table dropped
SQL> DROP TABLE users3;
Table dropped
SQL> DROP TABLE users5;
Table dropped
SQL> DROP TABLE users0;
Table dropped
Комментариев нет:
Отправить комментарий