oracle insert一些好玩功能

--1，插入数据违反约束条件发生错误时候将错误数据插入一张表
drop table t1;
create table t1 (id int,value number);
insert  into t1 values(1,1);
insert  into t1 values(2,2);
insert  into t1 values(3,10);
insert  into t1 values(4,11);
commit;

CREATE TABLE t_raises (id NUMBER, value NUMBER 
   CONSTRAINT check_sal CHECK(value > 10));
   
begin
DBMS_ERRLOG.CREATE_ERROR_LOG('t_raises', 't_errlog');
end;
-- 将错误信息记录到errlog表中
INSERT INTO t_raises
   SELECT id, value FROM t1
   LOG ERRORS INTO t_errlog ('my_bad') REJECT LIMIT 10;

SELECT * FROM t_errlog;

select * from t_raises;


-- 2，returning statement
declare 
   v_value    number;
   v_id       int;

begin
INSERT INTO t1 
      (id,value)
  values(1,100)
   RETURNING id+1,value*10 INTO v_id,v_value;
   dbms_output.put_line(v_id||' '||v_value);

end;
--3 insert all
-- 测试数据
truncate table t1;
create table t2 as select * from t1;
create table t3 as select * from t1;
create table t4 as select * from t1;

insert  into t1 values(1,1);
insert  into t1 values(2,2);
insert  into t1 values(3,10);
insert  into t1 values(4,11);
commit;

-- 没过滤条件
insert all
       into t2(id,value) values(id,value)
       into t3(id,value) values(id,value)
select id,value
from t1;
select * from t2;
select * from t3;

-- 指定条件，但是每个条件都执行所有记录
rollback;
insert all
       when id<=1 then 
            into t2(id,value) values(id,value)
       when id >1 then
            into t3(id,value) values(id,value)
select id,value
from t1;
select * from t2;
select * from t3;

rollback;
insert all
       when id<=1 then 
            into t2(id,value) values(id,value)
       when id >=1 then
            into t3(id,value) values(id,value)
       when id >=1 then
            into t4(id,value) values(id,value)
select id,value
from t1;
select * from t2;
select * from t3;
select * from t4;

rollback;
-- 如果指定first，则匹配第一次的条件，记录之匹配一次将过滤掉
insert first
       when id<=1 then 
            into t2(id,value) values(id,value)
       when id >=1 then
            into t3(id,value) values(id,value)
       when id >=1 then
            into t4(id,value) values(id,value)
select id,value
from t1;
select * from t2;
select * from t3;
select * from t4;

参考：http://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_9014.htm#SQLRF01604