Oracle 更改数据：插入、更新、合并和删除

插入

1、用select插入

insert into test select * from test；

2、使用append提示改善插入性能

append提示将使数据库查找已经被插入表中数据的最后一个块，新的记录将从表的高水位线之上新分配的extent中的第一个块开始插入。此外，插入的数据将直接写入数据文件中，而不是先进入数据块缓存。因此，在插入期间对数据库进行的空间管理工作就很少了。于是，在使用append提示时，插入操作会进行的很快。

insert /*+append+*/ into test select * from test;

重置高水位标记的唯一方法是把表截断（truncate）。因为truncate将删除所有的记录并且不能回滚，所以应该确保在执行truncate操作之前该表有数据备份。

3、rollback、commit和autocommit命令

show autocommit；

默认是off，也可以为autocommit指定一个数值，该值将确定在几条命令后Oracle将执行commit。

rollback 回滚未被提交的工作。

4、使用savepoint

savepoint a；

insert into ...

savepoint b;

delete .....

rollback to savepoint a;

5、隐式提交

即使没有直接下达提交命令，有些操作（如quit，exit（等价于quit））以及任意数据定义语言（DDL）的命令也会强制提交发生，使用这命令会强制提交事务。

6、自动回滚

如果机器或数据库发生故障，oracle将自动回滚，完成一次数据库的恢复的清理工作。

7、多表插入

可以在一个命令中执行多个插入。可以无条件地执行所有的插入，也可以指定条件执行插入（用when子句告诉oracle怎样控制多个插入）。如果指定all，将判定所有的when子句；若指定first，则告诉oracle，在它找到一个判定结果为真的when子句后将忽略后面的when子句。还可以用else子句告诉oracle，当when子句的判定结果没有一个为真时应该进行什么操作。

insert all

into test (aa,bb,cc) values(aa,bb,'cc')

into test (aa,bb,cc) values(aa,bb,'dd')

into test (aa,bb,cc) values(aa,bb,'ee')

select aa,bb,cc from test;

-------------------------------------------------------------------------

insert first

when aa>5 then into test (aa,bb,cc) values(aa,bb,'cc')

when bb>6 then into test (aa,bb,cc) values(aa,bb,'dd')

when cc is not nulll then into test (aa,bb,cc) values(aa,bb,'ee')

else into test2

select aa,bb,cc from test;

删除delete

delete from test where aa>5;

删除记录的另一个命令是truncate，它与delete不同。delete可以提交或回滚删除操作，而truncate自动地删除表中的所有记录，truncate命令的操作结果不能被回滚或提交，截除的记录不能恢复。不能依靠闪回查询查看被截除的数据。

更新update

1、用嵌入式select进行更新

update test set (aa,bb)=(select cc,dd from test2 where mm>1) where cc=5;

2、用null更新

update test set aa = null where bb=0;

这是null与等号而不是与关键字is一起使用的唯一示例。

合并merge

可以在单个命令中使用merge命令对单个表执行insert 和update 操作。

merge into test t1

using (select aa,bb,cc from test2) c2 on (t1.aa=t2.aa and t1.bb=t2.bb)

when matched then update set cc=t2.cc

where not matched then insert (t1aa,t1.bb,t1.cc) values(t2.11,t2.bb,t2.cc);

注意：不能更新on条件子句总引用的列。

处理错误

使用错误日志表，并在命令中使用特定语法告诉数据库如何处理错误。之后oracle会自动将失败条目及它们失败的原因记录到日志中。

oracle 在dms_errlog程序包中create_error_log的过程，创建错误日志。

创建 execute dbms_errlog.create_error_log('test','errlog');

对错误进行重定向

insert into test (aa,bb,cc) select * from test2 log errors into errlog('from_order') reject limit 10;

可以使用regect limit 子句来指定在insert 命令失败之前可以记录到日志中的错误数量的上限。默认是0，也可以将它设置为unlimited。

如果在insert 执行期间遇到错误，那么这些错误将会被写到errlog表中，并按照程序清单中log errors 子句的执行，用“from_order”值标记他们。errlog表还有另外两列:

ORA_ERR_MESG$：它包含Oracle错误消息号，=和错误的消息文本。

ORA_ERR_TAG$：它包含into子句中指定的标记，如前面的程序清单所示。

不能在错误日志表中记录long、lob会对象类型列的错误。但是DML操作的目标表可以包含这些类型的列。

欢迎关注“自学Oracle”