суббота, 14 сентября 2013 г.

INSERT для нескольких таблиц в Oracle SQL. Часть II

В первой части статьи была рассмотрена работа многотабличной безусловной команды 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
Ярлыки: ,

Комментариев нет:

Отправить комментарий

Подписаться на: Комментарии к сообщению (Atom)