insert first when 条件 then into A else into B ......
Most INSERT statements are the single-table variety, but Oracle also supports a multiple-table INSERT statement. With a multitable insert, you can make a single pass through the source data and load the data into more than one table. [ ALL | FIRST ] WHEN condition THEN insert_into_clause [values_clause] [insert_into_clause [values_clause]]... [WHEN condition THEN insert_into_clause [values_clause] [insert_into_clause [values_clause]]... ]... [ELSE insert_into_clause [values_clause] [insert_into_clause [values_clause]]... ] If a WHEN condition evaluates to TRUE, the corresponding INTO clause is executed. If no WHEN condition evaluates to TRUE, the ELSE clause is executed. The keyword ALL tells the database to check each WHEN condition. On the other hand, the keyword FIRST tells the database to stop checking WHEN conditions after finding the first TRUE condition.
貌似不行吧!用动态SQL语句?用存储过程将逻辑判断和处理封装起来。
insert first when 条件 then into A else into B select --这个语法必须要用select ......
过程会写,一个sql语句不会 等高人 create or replace procedure testPro(flag in varchar2) is p_flag varchar2(10); begin p_flag:=flag; if p_flag='a' then insert into a select * from emp; else insert into b select * from emp; end if; end;
SQL> truncate table test;
Table truncated
SQL> truncate table test1;
Table truncated
SQL> SQL> insert first 2 when 1=1 then into test(id) 3 else into test1(seq_no) 4 select 1 from dual 5 ;
1 row inserted
SQL> select * from test;
ID NAME ----------- -------------------------------------------------------------------------------- 1
SQL> select * from test1;
SEQ_NO TYPE1 TYPE2 AMT MARK ----------- ----- ----- ------------ ------
insert first
when 条件 then into A
else into B
......
but Oracle also supports a multiple-table INSERT statement.
With a multitable insert, you can make a single pass
through the source data and load the data into more than one table. [ ALL | FIRST ]
WHEN condition THEN insert_into_clause [values_clause]
[insert_into_clause [values_clause]]...
[WHEN condition THEN insert_into_clause [values_clause]
[insert_into_clause [values_clause]]...
]...
[ELSE insert_into_clause [values_clause]
[insert_into_clause [values_clause]]...
] If a WHEN condition evaluates to TRUE, the corresponding INTO clause is executed.
If no WHEN condition evaluates to TRUE, the ELSE clause is executed.
The keyword ALL tells the database to check each WHEN condition. On the other hand,
the keyword FIRST tells the database to stop checking WHEN conditions after finding the first TRUE condition.
insert first
when 条件 then into A
else into B
select --这个语法必须要用select
......
等高人
create or replace procedure testPro(flag in varchar2)
is
p_flag varchar2(10);
begin
p_flag:=flag;
if p_flag='a' then
insert into a select * from emp;
else
insert into b select * from emp;
end if;
end;
Table truncated
SQL> truncate table test1;
Table truncated
SQL>
SQL> insert first
2 when 1=1 then into test(id)
3 else into test1(seq_no)
4 select 1 from dual
5 ;
1 row inserted
SQL> select * from test;
ID NAME
----------- --------------------------------------------------------------------------------
1
SQL> select * from test1;
SEQ_NO TYPE1 TYPE2 AMT MARK
----------- ----- ----- ------------ ------
SQL>
--一楼提供的方法不错
SQL> select * from a;未选定行SQL> select * from b;未选定行SQL> insert
2 when (select count(*) from dept)<5 then into a
3 else into b
4 select * from emp;已创建14行。SQL> select * from a;未选定行SQL> select * from b; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-12月-80 800 900 20
7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30
7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30
7566 JONES MANAGER 7839 02-4月 -81 2975 900 20
7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30
7698 BLAKE MANAGER 7839 01-5月 -81 2850 30
7782 CLARK MANAGER 7839 09-6月 -81 2450 10
7788 SCOTT ANALYST 7566 19-4月 -87 3000 900 20
7839 KING PRESIDENT 17-11月-81 5000 10
7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30
7876 ADAMS CLERK 7788 23-5月 -87 1100 900 20
7900 JAMES CLERK 7698 03-12月-81 950 30
7902 FORD ANALYST 7566 03-12月-81 3000 900 20
7934 MILLER CLERK 7782 23-1月 -82 1300 10已选择14行。
when 条件 then into A values(...)
else into B values(....)
select * from .....
--就用insert when ...else...就可以了
insert all
when 条件 then into A values(...)
else into B values(....)
select * from .....