1、簡述
使用insert into語句進行表數據行的插入，但是oracle中有一個更好的實現方式：使用insert all語句。
insert all語句是oracle中用于批量寫數據的 。insert all分又為
- 無判斷條件插入
- 有判斷條件插入
- 有判斷條件插入分為
-
Insert all when... 子句
-
insert first when...?子句
2、表和數據準備
--創建表
sqlplus hr/hr@192.168.0.207:1521/PROD01PDB
CREATE TABLE t_students(ID NUMBER(4) primary key,NAME VARCHAR2(32),sex VARCHAR2(3)
);--刪除表
drop table t_students;
drop table t_students_01;
drop table st_student_02;--向t_students表中插入數據
INSERT INTO t_students(ID, NAME, sex) VALUES(881001, '成都', '女');
INSERT INTO t_students(ID, NAME, sex) VALUES(881002, '深圳', '男');
INSERT INTO t_students(ID, NAME, sex) VALUES(881003, '上海', '女');
commit;HR@192.168.0.207:1521/PROD01PDB> col NAME format a20
HR@192.168.0.207:1521/PROD01PDB> select * from t_students;ID NAME SEX
---------- -------------------- ---------881001 成都 女881002 深圳 男881003 上海 女--復制表結構創建表t_students_01，t_students_02
CREATE TABLE t_students_01 AS SELECT * FROM t_students WHERE 1 = 2;
CREATE TABLE t_students_02 AS SELECT * FROM t_students WHERE 1 = 2;--查詢表
select * from t_students;
select * from t_students_01;
select * from t_students_02;3、insert all無判斷條件插入
將t_students表中的數據插入t_students_01，t_students_02表中可以這樣寫
insert allinto t_students_01 values(id,name,sex)into t_students_02 values(id,name,sex)
select id,name,sex from t_students;4、insert all有判斷條件插入
有判斷條件插入又分為兩種：insert all when... 子句和insert first when...?子句
-- insert t_students
INSERT INTO t_students(ID, NAME, sex) VALUES(1004, '廣州', '女');
INSERT INTO t_students(ID, NAME, sex) VALUES(1005, '蘇州', '男');
commit;-- insert first
insert first when id>=1004 theninto t_students_01 values(id,name,sex)when id=1005 theninto t_students_02 values(id,name,sex)
select id,name,sex from t_students;
commit;-- insert all
insert all when id>=1004 theninto t_students_01 values(id,name,sex)when id=1005 theninto t_students_02 values(id,name,sex)
select id,name,sex from t_students;
commit;5、insert all 、insert first 子句的區別
insert first是依據是否滿足判斷條件來確定先后關系的，當數據滿足第一個when判斷條件又滿足第二個when判斷條件，則執行第一個then插入語句，第二個then就不插入。
反之有數據不滿足第一個when判斷條件且滿足第二個when判斷條件,則數據會插入第二個條件下對應的表中，這也正是insert first與inset all的區別。
insert all? ? 只要滿足條件，就會插入，這個會造成重復插入；
insert?first 只要有一個滿足條件，后面的條件不再判斷，不會造成重復插入。
注意：insert all 不支持序列插入，會導致兩邊不一致
6、Oracle19c OCP 考試點題目
自己在HR schmea下
select employee_id emp_id,manger_id MGR,salary SAL from EMPLOYEES;create table special_sal
as
select employee_id emp_id,salary SAL
from HR.EMPLOYEES
where 1=0;CREATE table sal_history as select employee_id emp_id,hire_date hiredate,salary SAL
from EMPLOYEES
where 1=0;CREATE table mgr_history as select employee_id emp_id,manager_id MGR,salary SAL
from EMPLOYEES
where 1=0;insert all
when SAL > 20000 theninto special_sal VALUES(emp_id,SAL)
else into sal_history VALUES(emp_id,hiredate,sal) into mgr_history values(emp_id,mgr,sal)
select employee_id emp_id,hire_date hiredate,salary SAL,manager_id MGR
from EMPLOYEES
where EMPLOYEE_ID<125;HR@192.168.0.207:1521/PROD01PDB> select * from special_sal;EMP_ID SAL
---------- ----------100 24000HR@192.168.0.207:1521/PROD01PDB> select * from sal_history;EMP_ID HIREDATE SAL
---------- ------------------- ----------101 2005-09-21 00:00:00 17000102 2001-01-13 00:00:00 17000103 2006-01-03 00:00:00 9000104 2007-05-21 00:00:00 6000105 2005-06-25 00:00:00 4800106 2006-02-05 00:00:00 4800107 2007-02-07 00:00:00 4200108 2002-08-17 00:00:00 12008109 2002-08-16 00:00:00 9000110 2005-09-28 00:00:00 8200111 2005-09-30 00:00:00 7700EMP_ID HIREDATE SAL
---------- ------------------- ----------112 2006-03-07 00:00:00 7800113 2007-12-07 00:00:00 6900114 2002-12-07 00:00:00 11000115 2003-05-18 00:00:00 3100116 2005-12-24 00:00:00 2900117 2005-07-24 00:00:00 2800118 2006-11-15 00:00:00 2600119 2007-08-10 00:00:00 2500120 2004-07-18 00:00:00 8000121 2005-04-10 00:00:00 8200122 2003-05-01 00:00:00 7900EMP_ID HIREDATE SAL
---------- ------------------- ----------123 2005-10-10 00:00:00 6500124 2007-11-16 00:00:00 580024 rows selected.HR@192.168.0.207:1521/PROD01PDB> select * from mgr_history;EMP_ID MGR SAL
---------- ---------- ----------101 100 17000102 100 17000103 102 9000104 103 6000105 103 4800106 103 4800107 103 4200108 101 12008109 108 9000110 108 8200111 108 7700EMP_ID MGR SAL
---------- ---------- ----------112 108 7800113 108 6900114 100 11000115 114 3100116 114 2900117 114 2800118 114 2600119 114 2500120 100 8000121 100 8200122 100 7900EMP_ID MGR SAL
---------- ---------- ----------123 100 6500124 100 580024 rows selected.
)
