--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
oracle insert一些好玩功能
最新推荐文章于 2021-04-10 03:46:46 发布