【Oracle】多表插入

Oracle 多表同时插入

---

先创建两个测试表

SQL> 
SQL> CREATE TABLE test_1 AS SELECT '01' ID, 'TOM' NAME, 20 AGE, 'C#' LANGUAGE FROM DUAL;
 
Table created
SQL> CREATE TABLE test_2 AS SELECT '01' ID, 'JERRY' NAME, 21 AGE, 'SHENZHEN' ADDRESS FROM DUAL;
 
Table created
 
SQL> SELECT * FROM test_1;
 
ID NAME        AGE LANGUAGE
-- ---- ---------- --------
01 TOM          20 C#
 
SQL> SELECT * FROM test_2;
 
ID NAME         AGE ADDRESS
-- ----- ---------- --------
01 JERRY         21 SHENZHEN

1、无条件 Insert

/* 由于测试表是动态创建 其字段值的长度动态生成 所以新增数据可能需要手动扩充长度 */
SQL> INSERT ALL
  2  INTO TEST_1 (ID, NAME, AGE, LANGUAGE) VALUES (no_, name_, age_, language_)
  3  INTO TEST_2 (ID, NAME, AGE, ADDRESS) VALUES (no_, name_, age_, address_)
  4  SELECT * FROM (
  5  SELECT '03' no_, 'candy' name_, 5 age_, 'JAVA' language_, 'NANJING' address_ FROM dual
  6  UNION
  7  SELECT '04' no_, 'lucy' name_, 16 age_, 'Python' language_, 'CHENGDU' address_ FROM dual
  8  UNION
  9  SELECT '05' no_, 'bob' name_, 26 age_, 'C++' language_, 'LANZHOU' address_ FROM dual
 10  );
 
6 rows inserted
 
SQL> 
SQL> SELECT * FROM test_1;
 
ID                   NAME                                  AGE LANGUAGE
-------------------- ------------------------------ ---------- --------------------
01                   TOM                                    20 C#
03                   candy                                   5 JAVA
04                   lucy                                   16 Python
05                   bob                                    26 C++
SQL> SELECT * FROM test_2;
 
ID                   NAME                                                      AGE ADDRESS
-------------------- -------------------------------------------------- ---------- --------------------------------------------------------------------------------
01                   JERRY                                                      21 SHENZHEN
03                   candy                                                       5 NANJING
04                   lucy                                                       16 CHENGDU
05                   bob                                                        26 LANZHOU
 
SQL> 

2、有条件 Insert

/* 为了明显的看出数据变化 清空两张测试表 */
SQL> DELETE test_1;
 
4 rows deleted
SQL> DELETE test_2;
 
4 rows deleted
 
SQL> INSERT ALL
  2  WHEN no_ = 5 THEN
  3  INTO TEST_1 (ID, NAME, AGE, LANGUAGE) VALUES (no_, name_, age_, language_)
  4  WHEN address_ = 'CHENGDU' THEN
  5  INTO TEST_2 (ID, NAME, AGE, ADDRESS) VALUES (no_, name_, age_, address_)
  6  SELECT * FROM (
  7  SELECT '03' no_, 'candy' name_, 5 age_, 'JAVA' language_, 'NANJING' address_ FROM dual
  8  UNION
  9  SELECT '04' no_, 'lucy' name_, 16 age_, 'Python' language_, 'CHENGDU' address_ FROM dual
 10  UNION
 11  SELECT '05' no_, 'bob' name_, 26 age_, 'C++' language_, 'LANZHOU' address_ FROM dual
 12  );
 
2 rows inserted
 
SQL> 
SQL> SELECT * FROM test_1;
 
ID                   NAME                                  AGE LANGUAGE
-------------------- ------------------------------ ---------- --------------------
05                   bob                                    26 C++
SQL> SELECT * FROM test_2;
 
ID                   NAME                                                      AGE ADDRESS
-------------------- -------------------------------------------------- ---------- --------------------------------------------------------------------------------
04                   lucy                                                       16 CHENGDU