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 的区别...谢谢~!
解决方案 »
- Status字段用那种数据类型好,Number还是char(1)
- Oracle分组查询
- Oracle中如何实现以表名作为参数的存储过程?
- MSSQL下这种查询语句赋值ORACLE怎么写呢?
- 有没有一种格式可以在insert失败后执行update的sql语句
- 急,有点难度,高手进来帮忙,有关列转行并统计...
- 安装ORACLE9I,试图执行GETVALUE时,没有找到指定的键
- 在 where 条件中, in (xxx,xxx,...) ,其中,xxx 可以有多少项,也就是in 可以有多少值?
- 我是新手,请各位大虾教教我...
- 求一个最佳的查询语句
- java delete DB2 问题,高手进
- 一个比较难的更新问题
如果同时满足多个条件,会对多表进行插入
要看楼主的需求。
意思就是 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都会有数据