Oracle查詢優(yōu)化改寫技巧與案例 第四章 插入、更新與刪除
4.1插入新紀(jì)錄
我們先建立測試表，各列都有默認(rèn)值。
新增數(shù)據(jù)如下：
請大家注意以下幾點。
如果INSERT語句中沒有含默認(rèn)值的列，則會添加默認(rèn)值，如C4列。
如果包含有默認(rèn)值的列，需要用DEFAULT關(guān)鍵字，才會添加默認(rèn)值，如C1列。
如果已顯示設(shè)定了NULL或其他值，則不會再生成默認(rèn)值，如C2,C3列。
建立表時，有時明明設(shè)定了默認(rèn)值，可生成的數(shù)據(jù)還是NULL，原因在于我們在代碼中不知不覺地加入了NULL。
4.2阻止對某幾列插入
或許有讀者已注意到，我們建立的表中C4列默認(rèn)值為SYSDATE，這種列一般是為了記錄數(shù)據(jù)生成的時間，不允許手動錄入。那么系統(tǒng)控制不到位，或因管理不到位，經(jīng)常會有手動錄入的情況發(fā)生，怎么辦？
我們可以建立一個不包含C4列的VIEW，新增數(shù)據(jù)是通過這個VIEW就可以了。
注意，通過VIEW新增數(shù)據(jù)，不能再使用關(guān)鍵字DEFAULT。
4.3復(fù)制表的定義及數(shù)據(jù)
我們可以用一下語句復(fù)制表T4_1:
也可以先復(fù)制表的定義，在新增數(shù)據(jù)：
注意：復(fù)制的表不包含默認(rèn)值等約束信息，使用這種方式復(fù)制表后，需要重建默認(rèn)值及索引和約束等信息。
復(fù)制表定義后就可以新增數(shù)據(jù)了：
4.4用WITH CHECK OPTION限制數(shù)據(jù)輸入
當(dāng)約束條件比較簡單是，可以直接加在表中，如工資必須大于0：
但有些復(fù)雜或特殊的約束條件是不能這樣放在表里的，如雇傭日期大于當(dāng)前日期：
這時我們可以使用加了WITH CHECK OPTION關(guān)鍵字的VIEW來達到目的。
下面的實例中，我們限制了不符合內(nèi)聯(lián)視圖條件的數(shù)據(jù)（SYSDATE+1）：
語句（SELECT empno，ename，hiredate FROM emp WHERE hiredate <= SYSDATE WITH CHECK OPTION）被當(dāng)做一個視圖處理。
因為里面有關(guān)鍵字"WITH CHECK OPTION"，所以INSERT的數(shù)據(jù)不符合其中的條件（hiredate<=SYSDATE）時，就不允許利用INSERT。
當(dāng)規(guī)則較復(fù)雜，無法用約束實現(xiàn)時，這種限制方式就比較有用。
4.5多表插入語句
多表插入語句分為一下四種。
無條件INSERT。
有條件INSERT ALL。
轉(zhuǎn)置INSERT。
有條件INSERT FIRST。
首先建立兩個測試用表：
無條件INSERT：
因為沒有加條件，所以會同時向兩個表插入數(shù)據(jù)，且兩個表中插入的條數(shù)一樣。
有條件INSERT ALL：
當(dāng)增加條件后，就會按條件插入。如EMPNO=7654等數(shù)據(jù)在兩個表中都有。
INSERT FIRST就不一樣：
在INSERT FIRST語句中，當(dāng)?shù)谝粋€表符合條件后，第二個表將不再插入對應(yīng)的行，表emp2中不再有與表emp1相同的數(shù)據(jù)“EMPNO=7654”，這就是INSERT FIRST與INSERT ALL的不同之處。
轉(zhuǎn)置INSERT與其說是一個分類，不如算作“INSERT ALL”的一個用法。
可以看到，轉(zhuǎn)置INSERT的實質(zhì)就是把不同列的數(shù)據(jù)插入到同一表的不同行中。
轉(zhuǎn)置INSERT的等價語句如下：
4.6用其他表中的值更新
我們對表emp新增字段dname，然后把dept.dname更新至emp中：
為了便于講解，在此只更新部門（10:ACCOUNTING,20:RESEARCH）的數(shù)據(jù)。其他未更新的部門（如30:SALES）名稱應(yīng)該保持為為‘noname’不變。
初學(xué)Oracle的人常把語句直接寫為：
可以看到，這個語句是對全表做更新，而不是需求所說的部門 (10:ACCOUNTING,20RESEARCH)，而且因為部門?(30:SALES)沒有匹配到的數(shù)據(jù)，dname均被更新為NULL值。
可以想象，在生產(chǎn)環(huán)境中，大量的數(shù)據(jù)被清空或改錯是多嚴(yán)重的行為！原因在于該語句中少了必要的過濾條件。
以上UPDATE語句的結(jié)果及錯誤用查詢語句描述如下：
正確的思路是要加上限定條件：
同樣，正確的UPDATE語句應(yīng)如下：
除10、20兩個部門之外，其他dname均應(yīng)保持原值“noname”。
更新數(shù)據(jù)除了上述方法，還可以使用可更新VIEW：
使用這個語句或許會遇到下面這個錯誤：
這時在表dept中增加唯一索引或主鍵，再執(zhí)行上述語句即可。
第三種方法是用MERGE改寫：
在此建議大家在做多表關(guān)聯(lián)更新時使用或更改為這種方式，因為MERGE INTO語句只訪問一次DEPT。
而UPDATE...WHERE...語句則訪問了兩次DEPT。
4.7合并記錄
前面介紹了MERGE INTO的好處，那么怎么使用MERGE INTO呢？下面簡單介紹一下。
首先建立測試用表：
語句及解釋如下：
這里有一下幾個要點：
語句時MERGE INTO bonuses，所以在這個語句里只能更改bonuses的數(shù)據(jù)，不能改USING后面那些表的數(shù)據(jù)。
更新、插入兩個操作是同事進行的，不分先后。
在MERGE INTO語句里不能更新JOIN列。
ON后面的條件一定要放在括號里，否則報錯。
改MERGE語句就相當(dāng)于同時執(zhí)行以下兩條DML語句：
4.8刪除違反參照完整性的記錄
首先建立測試環(huán)境。注意，如果前面未創(chuàng)建dept的主鍵，則需要先創(chuàng)建。
在emp表中增加一條數(shù)據(jù)（要另外復(fù)制一張emp表，不要直接用SCOTT.EMP）：
當(dāng)我們增加如下外鍵時，會因數(shù)據(jù)違反完整性而報錯：
這種提示在處理業(yè)務(wù)時會經(jīng)常遇到，是因為子表中的數(shù)據(jù)（DEPTNO:99）與主表不一致(主表中沒有DEPTNO:99)引起的。
這時就要處理違反完整性的數(shù)據(jù)，要根據(jù)情況選擇在主表中加入數(shù)據(jù)，或刪除子表中的數(shù)據(jù)。下面選擇刪除子表中的數(shù)據(jù)（注意，刪除前后要核對數(shù)據(jù)后再提交，嚴(yán)格地說，應(yīng)該要先備份表中的數(shù)據(jù)，再做刪除操作）：
刪除子表行或新增主表行后數(shù)據(jù)就一致了，重新執(zhí)行上面的外鍵語句即可：
4.9刪除名稱重復(fù)的記錄
因是手動錄入程序，所以經(jīng)常會產(chǎn)生重復(fù)的數(shù)據(jù)，這時就需要刪除多余的數(shù)據(jù)，示例如下：
可以看到，（'DYNAMITE' 、'SEA SHELLS' ）中這兩個人的數(shù)據(jù)重復(fù)，現(xiàn)在要求表中name重復(fù)的數(shù)據(jù)只保留一行，其他的刪除。
刪除數(shù)據(jù)有好幾種方法，下面只介紹三種方法（處理數(shù)據(jù)需謹(jǐn)慎，要確認(rèn)更改結(jié)果后再提交）。
方法一：通過name相同、id不同的方式來判斷。
利用這種方式刪除數(shù)據(jù)時需要建組合索引：
方法二：用ROWID來代替其中的id。
因為不需要關(guān)聯(lián)id列，我們只需要建立單列索引：
方法三：通過分析函數(shù)根據(jù)name分組生成序號，然后刪除序號大于1的數(shù)據(jù)。
我們也可以用分析函數(shù)取出重復(fù)的數(shù)據(jù)后刪除。下面先看生成額序號：
取出序號后，再刪除seq>1的語句就可以了：
當(dāng)然，還有其他寫法，讀者可繼續(xù)研究。
