SQL> INSERT ALL
WHEN deptno = 10 THEN INTO dept10
WHEN deptno = 20 THEN INTO dept20
WHEN deptno = 30 THEN INTO dept30
WHEN job = 'CLERK' THEN INTO clerk
ELSE INTO other
SELECT * FROM emp;
SQL> INSERT FIRST
WHEN deptno = 10 THEN INTO dept10
WHEN deptno = 20 THEN INTO dept20
WHEN deptno = 30 THEN INTO dept30
WHEN job = 'CLERK' THEN INTO clerk
ELSE INTO other
SELECT * FROM emp;
请问大侠们, INSERT ALL 与 INSERT FIRST 的区别...谢谢~!
WHEN deptno = 10 THEN INTO dept10
WHEN deptno = 20 THEN INTO dept20
WHEN deptno = 30 THEN INTO dept30
WHEN job = 'CLERK' THEN INTO clerk
ELSE INTO other
SELECT * FROM emp;
SQL> INSERT FIRST
WHEN deptno = 10 THEN INTO dept10
WHEN deptno = 20 THEN INTO dept20
WHEN deptno = 30 THEN INTO dept30
WHEN job = 'CLERK' THEN INTO clerk
ELSE INTO other
SELECT * FROM emp;
请问大侠们, INSERT ALL 与 INSERT FIRST 的区别...谢谢~!
如果同时满足多个条件,会对多表进行插入
要看楼主的需求。
意思就是 INSERT FIRST 是满足第一个条件就可以,而 INSERT ALL 是每个都需要判断对吗?应该是这样吧...
SQL> insert all
2 when idx=10 then into t2
3 when idx=10 then into t3
4 when cname='abc' then into t4
5 select * from t1;3 rows insertedSQL> select * from t2; IDX CNAME
--------------------------------------- ------------------------------
10 abcSQL> select * from t3; IDX CNAME
--------------------------------------- ------------------------------
10 abcSQL> select * from t4; IDX CNAME
--------------------------------------- ------------------------------
10 abcSQL> truncate table t2;Table truncatedSQL> truncate table t3;Table truncatedSQL> truncate table t4;Table truncatedSQL>
SQL> insert first
2 when idx=10 then into t2
3 when idx=10 then into t3
4 when cname='abc' then into t4
5 select * from t1;1 row insertedSQL> select * from t2; IDX CNAME
--------------------------------------- ------------------------------
10 abcSQL> select * from t3; IDX CNAME
--------------------------------------- ------------------------------SQL> select * from t4; IDX CNAME
--------------------------------------- ------------------------------SQL>
insert first
when idx=10 then into t2
when idx=10 then into t3
when cname='abc' then into t4
select * from t1;
第一个when idx=10 match了,insert 到t2后来t3,t4的条件也是idx=10,已经插入到t2表里面,所以就不会做insert了insert all相反,只要条件符合,统统insert,所以你看t2,t3,t4都会有数据