Oracle查询优化改写技巧与案例 第四章 插入、更新与删除
4.1插入新纪录
我们先建立测试表，各列都有默认值。
新增数据如下：
请大家注意以下几点。
如果INSERT语句中没有含默认值的列，则会添加默认值，如C4列。
如果包含有默认值的列，需要用DEFAULT关键字，才会添加默认值，如C1列。
如果已显示设定了NULL或其他值，则不会再生成默认值，如C2,C3列。
建立表时，有时明明设定了默认值，可生成的数据还是NULL，原因在于我们在代码中不知不觉地加入了NULL。
4.2阻止对某几列插入
或许有读者已注意到，我们建立的表中C4列默认值为SYSDATE，这种列一般是为了记录数据生成的时间，不允许手动录入。那么系统控制不到位，或因管理不到位，经常会有手动录入的情况发生，怎么办？
我们可以建立一个不包含C4列的VIEW，新增数据是通过这个VIEW就可以了。
注意，通过VIEW新增数据，不能再使用关键字DEFAULT。
4.3复制表的定义及数据
我们可以用一下语句复制表T4_1:
也可以先复制表的定义，在新增数据：
注意：复制的表不包含默认值等约束信息，使用这种方式复制表后，需要重建默认值及索引和约束等信息。
复制表定义后就可以新增数据了：
4.4用WITH CHECK OPTION限制数据输入
当约束条件比较简单是，可以直接加在表中，如工资必须大于0：
但有些复杂或特殊的约束条件是不能这样放在表里的，如雇佣日期大于当前日期：
这时我们可以使用加了WITH CHECK OPTION关键字的VIEW来达到目的。
下面的实例中，我们限制了不符合内联视图条件的数据（SYSDATE+1）：
语句（SELECT empno，ename，hiredate FROM emp WHERE hiredate <= SYSDATE WITH CHECK OPTION）被当做一个视图处理。
因为里面有关键字"WITH CHECK OPTION"，所以INSERT的数据不符合其中的条件（hiredate<=SYSDATE）时，就不允许利用INSERT。
当规则较复杂，无法用约束实现时，这种限制方式就比较有用。
4.5多表插入语句
多表插入语句分为一下四种。
无条件INSERT。
有条件INSERT ALL。
转置INSERT。
有条件INSERT FIRST。
首先建立两个测试用表：
无条件INSERT：
因为没有加条件，所以会同时向两个表插入数据，且两个表中插入的条数一样。
有条件INSERT ALL：
当增加条件后，就会按条件插入。如EMPNO=7654等数据在两个表中都有。
INSERT FIRST就不一样：
在INSERT FIRST语句中，当第一个表符合条件后，第二个表将不再插入对应的行，表emp2中不再有与表emp1相同的数据“EMPNO=7654”，这就是INSERT FIRST与INSERT ALL的不同之处。
转置INSERT与其说是一个分类，不如算作“INSERT ALL”的一个用法。
可以看到，转置INSERT的实质就是把不同列的数据插入到同一表的不同行中。
转置INSERT的等价语句如下：
4.6用其他表中的值更新
我们对表emp新增字段dname，然后把dept.dname更新至emp中：
为了便于讲解，在此只更新部门（10:ACCOUNTING,20:RESEARCH）的数据。其他未更新的部门（如30:SALES）名称应该保持为为‘noname’不变。
初学Oracle的人常把语句直接写为：
可以看到，这个语句是对全表做更新，而不是需求所说的部门 (10:ACCOUNTING,20RESEARCH)，而且因为部门 (30:SALES)没有匹配到的数据，dname均被更新为NULL值。
可以想象，在生产环境中，大量的数据被清空或改错是多严重的行为！原因在于该语句中少了必要的过滤条件。
以上UPDATE语句的结果及错误用查询语句描述如下：
正确的思路是要加上限定条件：
同样，正确的UPDATE语句应如下：
除10、20两个部门之外，其他dname均应保持原值“noname”。
更新数据除了上述方法，还可以使用可更新VIEW：
使用这个语句或许会遇到下面这个错误：
这时在表dept中增加唯一索引或主键，再执行上述语句即可。
第三种方法是用MERGE改写：
在此建议大家在做多表关联更新时使用或更改为这种方式，因为MERGE INTO语句只访问一次DEPT。
而UPDATE...WHERE...语句则访问了两次DEPT。
4.7合并记录
前面介绍了MERGE INTO的好处，那么怎么使用MERGE INTO呢？下面简单介绍一下。
首先建立测试用表：
语句及解释如下：
这里有一下几个要点：
语句时MERGE INTO bonuses，所以在这个语句里只能更改bonuses的数据，不能改USING后面那些表的数据。
更新、插入两个操作是同事进行的，不分先后。
在MERGE INTO语句里不能更新JOIN列。
ON后面的条件一定要放在括号里，否则报错。
改MERGE语句就相当于同时执行以下两条DML语句：
4.8删除违反参照完整性的记录
首先建立测试环境。注意，如果前面未创建dept的主键，则需要先创建。
在emp表中增加一条数据（要另外复制一张emp表，不要直接用SCOTT.EMP）：
当我们增加如下外键时，会因数据违反完整性而报错：
这种提示在处理业务时会经常遇到，是因为子表中的数据（DEPTNO:99）与主表不一致(主表中没有DEPTNO:99)引起的。
这时就要处理违反完整性的数据，要根据情况选择在主表中加入数据，或删除子表中的数据。下面选择删除子表中的数据（注意，删除前后要核对数据后再提交，严格地说，应该要先备份表中的数据，再做删除操作）：
删除子表行或新增主表行后数据就一致了，重新执行上面的外键语句即可：
4.9删除名称重复的记录
因是手动录入程序，所以经常会产生重复的数据，这时就需要删除多余的数据，示例如下：
可以看到，（'DYNAMITE' 、'SEA SHELLS' ）中这两个人的数据重复，现在要求表中name重复的数据只保留一行，其他的删除。
删除数据有好几种方法，下面只介绍三种方法（处理数据需谨慎，要确认更改结果后再提交）。
方法一：通过name相同、id不同的方式来判断。
利用这种方式删除数据时需要建组合索引：
方法二：用ROWID来代替其中的id。
因为不需要关联id列，我们只需要建立单列索引：
方法三：通过分析函数根据name分组生成序号，然后删除序号大于1的数据。
我们也可以用分析函数取出重复的数据后删除。下面先看生成额序号：
取出序号后，再删除seq>1的语句就可以了：
当然，还有其他写法，读者可继续研究。
